Open-ILS/src/c-apps/buildSQL.c

   1 /**
   2         @file buildSQL.c
   3         @brief Translate an abstract representation of a query into an SQL statement.
   4 */
   5
   6 #include <stdlib.h>
   7 #include <stdio.h>
   8 #include <errno.h>
   9 #include <dbi/dbi.h>
  10 #include "opensrf/utils.h"
  11 #include "opensrf/string_array.h"
  12 #include "opensrf/osrf_hash.h"
  13 #include "opensrf/osrf_application.h"
  14 #include "openils/oils_idl.h"
  15 #include "openils/oils_sql.h"
  16 #include "openils/oils_buildq.h"
  17
  18 static void build_Query( BuildSQLState* state, const StoredQ* query );
  19 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str );
  20 static void buildSelect( BuildSQLState* state, const StoredQ* query );
  21 static void buildFrom( BuildSQLState* state, const FromRelation* core_from );
  22 static void buildJoin( BuildSQLState* state, const FromRelation* join );
  23 static void buildSelectList( BuildSQLState* state, const SelectItem* item );
  24 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list );
  25 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list );
  26 static void buildCase( BuildSQLState* state, const Expression* expr );
  27 static void buildExpression( BuildSQLState* state, const Expression* expr );
  28 static void buildFunction( BuildSQLState* state, const Expression* exp );
  29 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op );
  30 static void buildBindVar( BuildSQLState* state, const BindVar* bind );
  31 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
  32
  33 static void add_newline( BuildSQLState* state );
  34 static inline void incr_indent( BuildSQLState* state );
  35 static inline void decr_indent( BuildSQLState* state );
  36
  37 /**
  38         @brief Create a jsonObject representing the current list of bind variables.
  39         @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
  40         @return Pointer to the newly created jsonObject.
  41
  42         The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
  43         variables.  The data for each is another level of JSON_HASH with a fixed set of tags:
  44         - "label"
  45         - "type"
  46         - "description"
  47         - "default_value" (as a jsonObject)
  48         - "actual_value" (as a jsonObject)
  49
  50         Any non-existent values are represented as JSON_NULLs.
  51
  52         The calling code is responsible for freeing the returned jsonOjbect by calling
  53         jsonObjectFree().
  54 */
  55 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
  56         jsonObject* list = jsonNewObjectType( JSON_HASH );
  57
  58         if( bindvar_list && osrfHashGetCount( bindvar_list )) {
  59                 // Traverse our internal list of bind variables
  60                 BindVar* bind = NULL;
  61                 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
  62                 while(( bind = osrfHashIteratorNext( iter ))) {
  63                         // Create an hash to represent the bind variable
  64                         jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
  65
  66                         // Add an entry for each attribute
  67                         jsonObject* attr = jsonNewObject( bind->label );
  68                         jsonObjectSetKey( bind_obj, "label", attr );
  69
  70                         const char* type = NULL;
  71                         switch( bind->type ) {
  72                                 case BIND_STR :
  73                                         type = "string";
  74                                         break;
  75                                 case BIND_NUM :
  76                                         type = "number";
  77                                         break;
  78                                 case BIND_STR_LIST :
  79                                         type = "string_list";
  80                                         break;
  81                                 case BIND_NUM_LIST :
  82                                         type = "number_list";
  83                                         break;
  84                                 default :
  85                                         type = "(invalid)";
  86                                         break;
  87                         }
  88                         attr = jsonNewObject( type );
  89                         jsonObjectSetKey( bind_obj, "type", attr );
  90
  91                         attr = jsonNewObject( bind->description );
  92                         jsonObjectSetKey( bind_obj, "description", attr );
  93
  94                         attr = jsonObjectClone( bind->default_value );
  95                         jsonObjectSetKey( bind_obj, "default_value", attr );
  96
  97                         attr = jsonObjectClone( bind->actual_value );
  98                         jsonObjectSetKey( bind_obj, "actual_value", attr );
  99
 100                         // Add the bind variable to the list
 101                         jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
 102                 }
 103                 osrfHashIteratorFree( iter );
 104         }
 105
 106         return list;
 107 }
 108
 109 /**
 110         @brief Apply values to bind variables, overriding the defaults, if any.
 111         @param state Pointer to the query-building context.
 112         @param bindings A JSON_HASH of values.
 113         @return 0 if successful, or 1 if not.
 114
 115         The @a bindings parameter must be a JSON_HASH.  The keys are the names of bind variables.
 116         The values are the corresponding values for the variables.
 117 */
 118 int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) {
 119         if( !state ) {
 120                 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
 121                 return 1;
 122         } else if( !bindings ) {
 123                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 124                         "Internal error: No pointer to bindings" ));
 125                 return 1;
 126         } else if( bindings->type != JSON_HASH ) {
 127                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 128                         "Internal error: bindings parameter is not a JSON_HASH" ));
 129                 return 1;
 130         }
 131
 132         int rc = 0;
 133         jsonObject* value = NULL;
 134         jsonIterator* iter = jsonNewIterator( bindings );
 135         while(( value = jsonIteratorNext( iter ))) {
 136                 const char* var_name = iter->key;
 137                 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
 138                 if( bind ) {
 139                         // Apply or replace the value for the specified variable
 140                         if( bind->actual_value )
 141                                 jsonObjectFree( bind->actual_value );
 142                         bind->actual_value = jsonObjectClone( value );
 143                 } else {
 144                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 145                                 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
 146                         rc = 1;
 147                 }
 148         }
 149         jsonIteratorFree( iter );
 150
 151         return rc;
 152 }
 153
 154 /**
 155         @brief Build an SQL query.
 156         @param state Pointer to the query-building context.
 157         @param query Pointer to the query to be built.
 158         @return Zero if successful, or 1 if not.
 159
 160         Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
 161 */
 162 int buildSQL( BuildSQLState* state, const StoredQ* query ) {
 163         state->error  = 0;
 164         buffer_reset( state->sql );
 165         state->indent = 0;
 166         build_Query( state, query );
 167         if( ! state->error ) {
 168                 // Remove the trailing space, if there is one, and add a semicolon.
 169                 char c = buffer_chomp( state->sql );
 170                 if( c != ' ' )
 171                         buffer_add_char( state->sql, c );  // oops, not a space; put it back
 172                 buffer_add( state->sql, ";\n" );
 173         }
 174         return state->error;
 175 }
 176
 177 /**
 178         @brief Build an SQL query, appending it to what has been built so far.
 179         @param state Pointer to the query-building context.
 180         @param query Pointer to the query to be built.
 181
 182         Look at the query type and branch to the corresponding routine.
 183 */
 184 static void build_Query( BuildSQLState* state, const StoredQ* query ) {
 185         if( buffer_length( state->sql ))
 186                 add_newline( state );
 187
 188         switch( query->type ) {
 189                 case QT_SELECT :
 190                         buildSelect( state, query );
 191                         break;
 192                 case QT_UNION :
 193                         buildCombo( state, query, "UNION" );
 194                         break;
 195                 case QT_INTERSECT :
 196                         buildCombo( state, query, "INTERSECT" );
 197                         break;
 198                 case QT_EXCEPT :
 199                         buildCombo( state, query, "EXCEPT" );
 200                         break;
 201                 default :
 202                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 203                                 "Internal error: invalid query type %d in query # %d",
 204                                 query->type, query->id ));
 205                         state->error = 1;
 206                         break;
 207         }
 208 }
 209
 210 /**
 211         @brief Build a UNION, INTERSECT, or EXCEPT query.
 212         @param state Pointer to the query-building context.
 213         @param query Pointer to the query to be built.
 214         @param type_str The query type, as a string.
 215 */
 216 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) {
 217
 218         QSeq* seq = query->child_list;
 219         if( !seq ) {
 220                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 221                         "Internal error: No child queries within %s query # %d",
 222                         type_str, query->id ));
 223                 state->error = 1;
 224                 return;
 225         }
 226
 227         // Traverse the list of child queries
 228         while( seq ) {
 229                 build_Query( state, seq->child_query );
 230                 if( state->error ) {
 231                         sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
 232                                 seq->child_query->id, type_str, query->id );
 233                         return;
 234                 }
 235                 seq = seq->next;
 236                 if( seq ) {
 237                         add_newline( state );
 238                         buffer_add( state->sql, type_str );
 239                         buffer_add_char( state->sql, ' ' );
 240                         if( query->use_all )
 241                                 buffer_add( state->sql, "ALL " );
 242                 }
 243         }
 244
 245         return;
 246 }
 247
 248 /**
 249         @brief Build a SELECT statement.
 250         @param state Pointer to the query-building context.
 251         @param query Pointer to the StoredQ structure that represents the query.
 252 */
 253 static void buildSelect( BuildSQLState* state, const StoredQ* query ) {
 254
 255         FromRelation* from_clause = query->from_clause;
 256         if( !from_clause ) {
 257                 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
 258                 state->error = 1;
 259                 return;
 260         }
 261
 262         // To do: get SELECT list; just a stub here
 263         buffer_add( state->sql, "SELECT" );
 264         incr_indent( state );
 265         buildSelectList( state, query->select_list );
 266         if( state->error ) {
 267                 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
 268                 state->error = 1;
 269                 return;
 270         }
 271         decr_indent( state );
 272
 273         // Build FROM clause, if there is one
 274         if( query->from_clause ) {
 275                 buildFrom( state, query->from_clause );
 276                 if( state->error ) {
 277                         sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
 278                         state->error = 1;
 279                         return;
 280                 }
 281         }
 282
 283         // Build WHERE clause, if there is one
 284         if( query->where_clause ) {
 285                 add_newline( state );
 286                 buffer_add( state->sql, "WHERE" );
 287                 incr_indent( state );
 288                 add_newline( state );
 289                 buildExpression( state, query->where_clause );
 290                 if( state->error ) {
 291                         sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
 292                         state->error = 1;
 293                         return;
 294                 }
 295                 decr_indent( state );
 296         }
 297
 298         // Build GROUP BY clause, if there is one
 299         buildGroupBy( state, query->select_list );
 300
 301         // Build HAVING clause, if there is one
 302         if( query->having_clause ) {
 303                 add_newline( state );
 304                 buffer_add( state->sql, "HAVING" );
 305                 incr_indent( state );
 306                 add_newline( state );
 307                 buildExpression( state, query->having_clause );
 308                 if( state->error ) {
 309                         sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
 310                         state->error = 1;
 311                         return;
 312                 }
 313                 decr_indent( state );
 314         }
 315
 316         // Build ORDER BY clause, if there is one
 317         if( query->order_by_list ) {
 318                 buildOrderBy( state, query->order_by_list );
 319                 if( state->error ) {
 320                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
 321                         state->error = 1;
 322                         return;
 323                 }
 324         }
 325
 326         // To do: Build LIMIT clause, if there is one
 327
 328         // To do: Build OFFSET clause, if there is one
 329
 330         state->error = 0;
 331 }
 332
 333 /**
 334         @brief Build a FROM clause.
 335         @param Pointer to the query-building context.
 336         @param Pointer to the StoredQ query to which the FROM clause belongs.
 337 */
 338 static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) {
 339
 340         add_newline( state );
 341         buffer_add( state->sql, "FROM" );
 342         incr_indent( state );
 343         add_newline( state );
 344
 345         switch( core_from->type ) {
 346                 case FRT_RELATION : {
 347                         char* relation = core_from->table_name;
 348                         if( !relation ) {
 349                                 if( !core_from->class_name ) {
 350                                         sqlAddMsg( state, "No relation specified for core relation # %d",
 351                                                 core_from->id );
 352                                         state->error = 1;
 353                                         return;
 354                                 }
 355
 356                                 // Look up table name, view name, or source_definition in the IDL
 357                                 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
 358                                 relation = oilsGetRelation( class_hash );
 359                         }
 360
 361                         // Add table or view
 362                         buffer_add( state->sql, relation );
 363                         if( !core_from->table_name )
 364                                 free( relation );   // In this case we strdup'd it, must free it
 365                         break;
 366                 }
 367                 case FRT_SUBQUERY :
 368                         buffer_add_char( state->sql, '(' );
 369                         incr_indent( state );
 370                         build_Query( state, core_from->subquery );
 371                         decr_indent( state );
 372                         add_newline( state );
 373                         buffer_add_char( state->sql, ')' );
 374                         break;
 375                 case FRT_FUNCTION :
 376                         buildFunction( state, core_from->function_call );
 377                         if ( state->error ) {
 378                                 sqlAddMsg( state,
 379                                         "Unable to include function call # %d in FROM relation # %d",
 380                                         core_from->function_call->id, core_from->id );
 381                                 return;
 382                         }
 383                         break;
 384                 default :
 385                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 386                                 "Internal error: Invalid type # %d in FROM relation # %d",
 387                                 core_from->type, core_from->id ));
 388                         state->error = 1;
 389                         return;
 390         }
 391
 392         // Add a table alias, if possible
 393         if( core_from->table_alias ) {
 394                 buffer_add( state->sql, " AS \"" );
 395                 buffer_add( state->sql, core_from->table_alias );
 396                 buffer_add( state->sql, "\" " );
 397         }
 398         else if( core_from->class_name ) {
 399                 buffer_add( state->sql, " AS \"" );
 400                 buffer_add( state->sql, core_from->class_name );
 401                 buffer_add( state->sql, "\" " );
 402         } else
 403                 buffer_add_char( state->sql, ' ' );
 404
 405         incr_indent( state );
 406         FromRelation* join = core_from->join_list;
 407         while( join ) {
 408                 buildJoin( state, join );
 409                 if( state->error ) {
 410                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
 411                                 core_from->id );
 412                         break;
 413                 } else
 414                         join = join->next;
 415         }
 416         decr_indent( state );
 417         decr_indent( state );
 418 }
 419
 420 /**
 421         @brief Add a JOIN clause.
 422         @param state Pointer to the query-building context.
 423         @param join Pointer to the FromRelation representing the JOIN to be added.
 424 */
 425 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
 426         add_newline( state );
 427         switch( join->join_type ) {
 428                 case JT_NONE :
 429                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
 430                         state->error = 1;
 431                         return;
 432                 case JT_INNER :
 433                         buffer_add( state->sql, "INNER JOIN " );
 434                         break;
 435                 case JT_LEFT:
 436                         buffer_add( state->sql, "LEFT JOIN " );
 437                         break;
 438                 case JT_RIGHT:
 439                         buffer_add( state->sql, "RIGHT JOIN " );
 440                         break;
 441                 case JT_FULL:
 442                         buffer_add( state->sql, "FULL JOIN " );
 443                         break;
 444                 default :
 445                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
 446                         state->error = 1;
 447                         return;
 448         }
 449
 450         switch( join->type ) {
 451                 case FRT_RELATION :
 452                         // Sanity check
 453                         if( !join->table_name || ! *join->table_name ) {
 454                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
 455                                 state->error = 1;
 456                                 return;
 457                         }
 458                         buffer_add( state->sql, join->table_name );
 459                         break;
 460                 case FRT_SUBQUERY :
 461                         // Sanity check
 462                         if( !join->subquery ) {
 463                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
 464                                 state->error = 1;
 465                                 return;
 466                         } else if( !join->table_alias ) {
 467                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
 468                                         join->id );
 469                                 state->error = 1;
 470                                 return;
 471                         }
 472                         buffer_add_char( state->sql, '(' );
 473                         incr_indent( state );
 474                         build_Query( state, join->subquery );
 475                         decr_indent( state );
 476                         add_newline( state );
 477                         buffer_add_char( state->sql, ')' );
 478                         break;
 479                 case FRT_FUNCTION :
 480                         if( !join->table_name || ! *join->table_name ) {
 481                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
 482                                         join->id );
 483                                 state->error = 1;
 484                                 return;
 485                         }
 486                         break;
 487         }
 488
 489         const char* effective_alias = join->table_alias;
 490         if( !effective_alias )
 491                 effective_alias = join->class_name;
 492
 493         if( effective_alias ) {
 494                 buffer_add( state->sql, " AS \"" );
 495                 buffer_add( state->sql, effective_alias );
 496                 buffer_add_char( state->sql, '\"' );
 497         }
 498
 499         if( join->on_clause ) {
 500                 incr_indent( state );
 501                 add_newline( state );
 502                 buffer_add( state->sql, "ON " );
 503                 buildExpression( state, join->on_clause );
 504                 decr_indent( state );
 505         }
 506
 507         FromRelation* subjoin = join->join_list;
 508         while( subjoin ) {
 509                 buildJoin( state, subjoin );
 510                 if( state->error ) {
 511                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
 512                         break;
 513                 } else
 514                         subjoin = subjoin->next;
 515         }
 516 }
 517
 518 /**
 519         @brief Build a SELECT list.
 520         @param state Pointer to the query-building context.
 521         @param item Pointer to the first in a linked list of SELECT items.
 522 */
 523 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
 524
 525         int first = 1;
 526         while( item ) {
 527                 if( !first )
 528                         buffer_add_char( state->sql, ',' );
 529                 add_newline( state );
 530                 buildExpression( state, item->expression );
 531                 if( state->error ) {
 532                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
 533                         state->error = 1;
 534                         break;
 535                 }
 536
 537                 if( item->column_alias ) {
 538                         buffer_add( state->sql, " AS \"" );
 539                         buffer_add( state->sql, item->column_alias );
 540                         buffer_add_char( state->sql, '\"' );
 541                 }
 542                 first = 0;
 543                 item = item->next;
 544         };
 545         buffer_add_char( state->sql, ' ' );
 546 }
 547
 548 /**
 549         @brief Add a GROUP BY clause, if there is one, to the current query.
 550         @param state Pointer to the query-building context.
 551         @param sel_list Pointer to the first node in a linked list of SelectItems
 552
 553         We reference the GROUP BY items by number, not by repeating the expressions.
 554 */
 555 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
 556         int seq = 0;       // Sequence number of current SelectItem
 557         int first = 1;     // Boolean: true for the first GROUPed BY item
 558         while( sel_list ) {
 559                 ++seq;
 560
 561                 if( sel_list->grouped_by ) {
 562                         if( first ) {
 563                                 add_newline( state );
 564                                 buffer_add( state->sql, "GROUP BY " );
 565                                 first = 0;
 566                         }
 567                         else
 568                                 buffer_add( state->sql, ", " );
 569
 570                         buffer_fadd( state->sql, "%d", seq );
 571                 }
 572
 573                 sel_list = sel_list->next;
 574         }
 575 }
 576
 577 /**
 578         @brief Add an ORDER BY clause to the current query.
 579         @param state Pointer to the query-building context.
 580         @param ord_list Pointer to the first node in a linked list of OrderItems.
 581 */
 582 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
 583         add_newline( state );
 584         buffer_add( state->sql, "ORDER BY" );
 585         incr_indent( state );
 586
 587         int first = 1;    // boolean
 588         while( ord_list ) {
 589                 if( first )
 590                         first = 0;
 591                 else
 592                         buffer_add_char( state->sql, ',' );
 593                 add_newline( state );
 594                 buildExpression( state, ord_list->expression );
 595                 if( state->error ) {
 596                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
 597                         return;
 598                 }
 599
 600                 ord_list = ord_list->next;
 601         }
 602
 603         decr_indent( state );
 604         return;
 605 }
 606
 607 /**
 608         @brief Build an arbitrary expression.
 609         @param state Pointer to the query-building context.
 610         @param expr Pointer to the Expression representing the expression to be built.
 611 */
 612 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
 613         if( !expr ) {
 614                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 615                         "Internal error: NULL pointer to Expression" ));
 616                 state->error = 1;
 617                 return;
 618         }
 619
 620         if( expr->parenthesize )
 621                 buffer_add_char( state->sql, '(' );
 622
 623         switch( expr->type ) {
 624                 case EXP_BETWEEN :
 625                         if( expr->negate )
 626                                 buffer_add( state->sql, "NOT " );
 627
 628                         buildExpression( state, expr->left_operand );
 629                         if( state->error ) {
 630                                 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
 631                                         expr->id );
 632                                 break;
 633                         }
 634
 635                         buffer_add( state->sql, " BETWEEN " );
 636
 637                         buildExpression( state, expr->subexp_list );
 638                         if( state->error ) {
 639                                 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
 640                                         expr->id );
 641                                 break;
 642                         }
 643
 644                         buffer_add( state->sql, " AND " );
 645
 646                         buildExpression( state, expr->subexp_list->next );
 647                         if( state->error ) {
 648                                 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
 649                                         expr->id );
 650                                 break;
 651                         }
 652
 653                         break;
 654                 case EXP_BIND :
 655                         if( !expr->bind ) {     // Sanity check
 656                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 657                                         "Internal error: no variable for bind variable expression" ));
 658                                 state->error = 1;
 659                         } else
 660                                 buildBindVar( state, expr->bind );
 661                         break;
 662                 case EXP_BOOL :
 663                         if( expr->negate )
 664                                 buffer_add( state->sql, "NOT " );
 665
 666                         if( expr->literal ) {
 667                                 buffer_add( state->sql, expr->literal );
 668                                 buffer_add_char( state->sql, ' ' );
 669                         } else
 670                                 buffer_add( state->sql, "FALSE " );
 671                         break;
 672                 case EXP_CASE :
 673                         buildCase( state, expr );
 674                         if( state->error )
 675                                 sqlAddMsg( state, "Unable to build CASE expression # %d", expr->id );
 676
 677                         break;
 678                 case EXP_CAST :                   // Type cast
 679                         if( expr->negate )
 680                                 buffer_add( state->sql, "NOT " );
 681
 682                         buffer_add( state->sql, "CAST (" );
 683                         buildExpression( state, expr->left_operand );
 684                         if( state->error )
 685                                 sqlAddMsg( state, "Unable to build left operand for CAST expression # %d",
 686                                         expr->id );
 687                         else {
 688                                 buffer_add( state->sql, " AS " );
 689                                 if( expr->cast_type && expr->cast_type->datatype_name ) {
 690                                         buffer_add( state->sql, expr->cast_type->datatype_name );
 691                                         buffer_add_char( state->sql, ')' );
 692                                 } else {
 693                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 694                                                 "No datatype available for CAST expression # %d", expr->id ));
 695                                         state->error = 1;
 696                                 }
 697                         }
 698                         break;
 699                 case EXP_COLUMN :                 // Table column
 700                         if( expr->negate )
 701                                 buffer_add( state->sql, "NOT " );
 702
 703                         if( expr->table_alias ) {
 704                                 buffer_add_char( state->sql, '\"' );
 705                                 buffer_add( state->sql, expr->table_alias );
 706                                 buffer_add( state->sql, "\"." );
 707                         }
 708                         if( expr->column_name ) {
 709                                 buffer_add( state->sql, expr->column_name );
 710                         } else {
 711                                 buffer_add_char( state->sql, '*' );
 712                         }
 713                         break;
 714                 case EXP_EXIST :
 715                         if( !expr->subquery ) {
 716                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 717                                         "No subquery found for EXIST expression # %d", expr->id ));
 718                                 state->error = 1;
 719                         } else {
 720                                 if( expr->negate )
 721                                         buffer_add( state->sql, "NOT " );
 722
 723                                 buffer_add( state->sql, "EXISTS (" );
 724                                 incr_indent( state );
 725                                 build_Query( state, expr->subquery );
 726                                 decr_indent( state );
 727                                 add_newline( state );
 728                                 buffer_add_char( state->sql, ')' );
 729                         }
 730                         break;
 731                 case EXP_FUNCTION :
 732                         buildFunction( state, expr );
 733                         break;
 734                 case EXP_IN :
 735                         if( expr->left_operand ) {
 736                                 buildExpression( state, expr->left_operand );
 737                                 if( !state->error ) {
 738                                         if( expr->negate )
 739                                                 buffer_add( state->sql, "NOT " );
 740                                         buffer_add( state->sql, " IN (" );
 741
 742                                         if( expr->subquery ) {
 743                                                 incr_indent( state );
 744                                                 build_Query( state, expr->subquery );
 745                                                 if( state->error )
 746                                                         sqlAddMsg( state, "Unable to build subquery for IN condition" );
 747                                                 else {
 748                                                         decr_indent( state );
 749                                                         add_newline( state );
 750                                                         buffer_add_char( state->sql, ')' );
 751                                                 }
 752                                         } else {
 753                                                 buildSeries( state, expr->subexp_list, NULL );
 754                                                 if( state->error )
 755                                                         sqlAddMsg( state, "Unable to build IN list" );
 756                                                 else
 757                                                         buffer_add_char( state->sql, ')' );
 758                                         }
 759                                 }
 760                         }
 761                         break;
 762                 case EXP_ISNULL :
 763                         if( expr->left_operand ) {
 764                                 buildExpression( state, expr->left_operand );
 765                                 if( state->error ) {
 766                                         sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
 767                                                 expr->id );
 768                                         break;
 769                                 }
 770                         }
 771
 772                         if( expr->negate )
 773                                 buffer_add( state->sql, " IS NOT NULL" );
 774                         else
 775                                 buffer_add( state->sql, " IS NULL" );
 776                         break;
 777                 case EXP_NULL :
 778                         if( expr->negate )
 779                                 buffer_add( state->sql, "NOT " );
 780
 781                         buffer_add( state->sql, "NULL" );
 782                         break;
 783                 case EXP_NUMBER :                    // Numeric literal
 784                         if( !expr->literal ) {
 785                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 786                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 787                                 state->error = 1;
 788                         } else {
 789                                 buffer_add( state->sql, expr->literal );
 790                         }
 791                         break;
 792                 case EXP_OPERATOR :
 793                         if( expr->negate )
 794                                 buffer_add( state->sql, "NOT (" );
 795
 796                         if( expr->left_operand ) {
 797                                 buildExpression( state, expr->left_operand );
 798                                 if( state->error ) {
 799                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 800                                                 expr->id );
 801                                         break;
 802                                 }
 803                         }
 804                         buffer_add_char( state->sql, ' ' );
 805                         buffer_add( state->sql, expr->op );
 806                         buffer_add_char( state->sql, ' ' );
 807                         if( expr->right_operand ) {
 808                                 buildExpression( state, expr->right_operand );
 809                                 if( state->error ) {
 810                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 811                                                            expr->id );
 812                                         break;
 813                                 }
 814                         }
 815
 816                         if( expr->negate )
 817                                 buffer_add_char( state->sql, ')' );
 818
 819                         break;
 820                 case EXP_SERIES :
 821                         if( expr->negate )
 822                                 buffer_add( state->sql, "NOT (" );
 823
 824                         buildSeries( state, expr->subexp_list, expr->op );
 825                         if( state->error ) {
 826                                 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
 827                                         expr->op ? expr->op : "," );
 828                         }
 829                         if( expr->negate )
 830                                 buffer_add_char( state->sql, ')' );
 831
 832                         break;
 833                 case EXP_STRING :                     // String literal
 834                         if( !expr->literal ) {
 835                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 836                                         "Internal error: No string value in string expression # %d", expr->id ));
 837                                         state->error = 1;
 838                         } else {
 839                                 char* str = strdup( expr->literal );
 840                                 dbi_conn_quote_string( state->dbhandle, &str );
 841                                 if( str ) {
 842                                         buffer_add( state->sql, str );
 843                                         free( str );
 844                                 } else {
 845                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 846                                                 "Unable to format string literal \"%s\" for expression # %d",
 847                                                         expr->literal, expr->id ));
 848                                         state->error = 1;
 849                                 }
 850                         }
 851                         break;
 852                 case EXP_SUBQUERY :
 853                         if( expr->negate )
 854                                 buffer_add( state->sql, "NOT " );
 855
 856                         if( expr->subquery ) {
 857                                 buffer_add_char( state->sql, '(' );
 858                                 incr_indent( state );
 859                                 build_Query( state, expr->subquery );
 860                                 decr_indent( state );
 861                                 add_newline( state );
 862                                 buffer_add_char( state->sql, ')' );
 863                         } else {
 864                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 865                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 866                                 state->error = 1;
 867                         }
 868                         break;
 869         }
 870
 871         if( expr->parenthesize )
 872                 buffer_add_char( state->sql, ')' );
 873 }
 874
 875 /**
 876         @brief Build a CASE expression.
 877         @param state Pointer to the query-building context.
 878         @param exp Pointer to an Expression representing a CASE expression.
 879 */
 880 static void buildCase( BuildSQLState* state, const Expression* expr ) {
 881         // Sanity checks
 882         if( ! expr->left_operand ) {
 883                 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
 884                 state->error  = 1;
 885                 return;
 886         } else if( ! expr->branch_list ) {
 887                 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
 888                 state->error  = 1;
 889                 return;
 890         }
 891
 892         if( expr->negate )
 893                 buffer_add( state->sql, "NOT (" );
 894
 895         // left_operand is the expression on which we shall branch
 896         buffer_add( state->sql, "CASE " );
 897         buildExpression( state, expr->left_operand );
 898         if( state->error ) {
 899                 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
 900                 return;
 901         }
 902
 903         incr_indent( state );
 904
 905         // Emit each branch in turn
 906         CaseBranch* branch = expr->branch_list;
 907         while( branch ) {
 908                 add_newline( state );
 909
 910                 if( branch->condition ) {
 911                         // Emit a WHEN condition
 912                         buffer_add( state->sql, "WHEN " );
 913                         buildExpression( state, branch->condition );
 914                         incr_indent( state );
 915                         add_newline( state );
 916                         buffer_add( state->sql, "THEN " );
 917                 } else {
 918                         // Emit ELSE
 919                         buffer_add( state->sql, "ELSE " );
 920                         incr_indent( state );
 921                         add_newline( state );
 922                 }
 923
 924                 // Emit the THEN expression
 925                 buildExpression( state, branch->result );
 926                 decr_indent( state );
 927
 928                 branch = branch->next;
 929         }
 930
 931         decr_indent( state );
 932         add_newline( state );
 933         buffer_add( state->sql, "END" );
 934
 935         if( expr->negate )
 936                 buffer_add( state->sql, ")" );
 937 }
 938
 939 /**
 940         @brief Build a function call.
 941         @param state Pointer to the query-building context.
 942         @param exp Pointer to an Expression representing a function call.
 943
 944         This function does not currently accommodate certain functions with idiosyncratic
 945         syntax, such as the absence of parentheses, or the use of certain keywords in
 946         in the parameter list.
 947 */
 948 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
 949         if( expr->negate )
 950                 buffer_add( state->sql, "NOT " );
 951
 952         // If a subfield is specified, the function call
 953         // needs an extra layer of parentheses
 954         if( expr->column_name )
 955                 buffer_add_char( state->sql, '(' );
 956
 957         // We rely on the input side to ensure that the function name is available
 958         buffer_add( state->sql, expr->function_name );
 959         buffer_add_char( state->sql, '(' );
 960
 961         // Add the parameters, if any
 962         buildSeries( state, expr->subexp_list, NULL );
 963
 964         buffer_add_char( state->sql, ')' );
 965
 966         if( expr->column_name ) {
 967                 // Add the name of the subfield
 968                 buffer_add( state->sql, ").\"" );
 969                 buffer_add( state->sql, expr->column_name );
 970                 buffer_add_char( state->sql, '\"' );
 971         }
 972 }
 973
 974 /**
 975         @brief Build a series of expressions separated by a specified operator, or by commas.
 976         @param state Pointer to the query-building context.
 977         @param subexp_list Pointer to the first Expression in a linked list.
 978         @param op Pointer to the operator, or NULL for commas.
 979
 980         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
 981         subsequent operators will begin on a new line.
 982 */
 983 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
 984
 985         if( !subexp_list)
 986                 return;                // List is empty
 987
 988         int comma = 0;             // Boolean; true if separator is a comma
 989         int newline_needed = 0;    // Boolean; true if operator is AND or OR
 990
 991         if( !op ) {
 992                 op = ",";
 993                 comma = 1;
 994         } else if( !strcmp( op, "," ))
 995                 comma = 1;
 996         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
 997                 newline_needed = 1;
 998
 999         int first = 1;               // Boolean; true for first item in list
1000         while( subexp_list ) {
1001                 if( first )
1002                         first = 0;   // No separator needed yet
1003                 else {
1004                         // Insert a separator
1005                         if( comma )
1006                                 buffer_add( state->sql, ", " );
1007                         else {
1008                                 if( newline_needed )
1009                                         add_newline( state );
1010                                 else
1011                                         buffer_add_char( state->sql, ' ' );
1012
1013                                 buffer_add( state->sql, op );
1014                                 buffer_add_char( state->sql, ' ' );
1015                         }
1016                 }
1017
1018                 buildExpression( state, subexp_list );
1019                 subexp_list = subexp_list->next;
1020         }
1021 }
1022
1023 /**
1024         @brief Add the value of a bind variable to an SQL statement.
1025         @param state Pointer to the query-building context.
1026         @param bind Pointer to the bind variable whose value is to be added to the SQL.
1027
1028         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1029         the type of the bind variable.
1030 */
1031 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1032
1033         // Decide where to get the value, if any
1034         const jsonObject* value = NULL;
1035         if( bind->actual_value )
1036                 value = bind->actual_value;
1037         else if( bind->default_value ) {
1038                 if( state->defaults_usable )
1039                         value = bind->default_value;
1040                 else {
1041                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1042                                 bind->name );
1043                         state->error = 1;
1044                         return;
1045                 }
1046         } else if( state->values_required ) {
1047                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1048                 state->error = 1;
1049                 return;
1050         } else {
1051                 // No value available, and that's okay.  Emit the name of the bind variable.
1052                 buffer_add_char( state->sql, ':' );
1053                 buffer_add( state->sql, bind->name );
1054                 return;
1055         }
1056
1057         // If we get to this point, we know that a value is available.  Carry on.
1058
1059         int numeric = 0;       // Boolean
1060         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1061                 numeric = 1;
1062
1063         // Emit the value
1064         switch( bind->type ) {
1065                 case BIND_STR :
1066                 case BIND_NUM :
1067                         buildScalar( state, numeric, value );
1068                         break;
1069                 case BIND_STR_LIST :
1070                 case BIND_NUM_LIST :
1071                         if( JSON_ARRAY == value->type ) {
1072                                 // Iterate over array, emit each value
1073                                 int first = 1;   // Boolean
1074                                 unsigned long max = value->size;
1075                                 unsigned long i = 0;
1076                                 while( i < max ) {
1077                                         if( first )
1078                                                 first = 0;
1079                                         else
1080                                                 buffer_add( state->sql, ", " );
1081
1082                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1083                                         ++i;
1084                                 }
1085                         } else {
1086                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1087                                         "Invalid value for bind variable; expected a list of values" ));
1088                                 state->error = 1;
1089                         }
1090                         break;
1091                 default :
1092                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1093                                 "Internal error: invalid type for bind variable" ));
1094                         state->error = 1;
1095                         break;
1096         }
1097
1098         if( state->error )
1099                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1100                         "Unable to emit value of bind variable \"%s\"", bind->name ));
1101 }
1102
1103 /**
1104         @brief Add a number or quoted string to an SQL statement.
1105         @param state Pointer to the query-building context.
1106         @param numeric Boolean; true if the value is expected to be a number
1107         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1108 */
1109 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1110         switch( obj->type ) {
1111                 case JSON_HASH :
1112                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1113                                 "Internal error: hash value for bind variable" ));
1114                         state->error = 1;
1115                         break;
1116                 case JSON_ARRAY :
1117                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1118                                 "Internal error: array value for bind variable" ));
1119                         state->error = 1;
1120                         break;
1121                 case JSON_STRING :
1122                         if( numeric ) {
1123                                 sqlAddMsg( state,
1124                                         "Invalid value for bind variable: expected a string, found a number" );
1125                                 state->error = 1;
1126                         } else {
1127                                 char* str = jsonObjectToSimpleString( obj );
1128                                 dbi_conn_quote_string( state->dbhandle, &str );
1129                                 if( str ) {
1130                                         buffer_add( state->sql, str );
1131                                         free( str );
1132                                 } else {
1133                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1134                                                 "Unable to format string literal \"%s\" for bind variable",
1135                                                 jsonObjectGetString( obj )));
1136                                         state->error = 1;
1137                                 }
1138                         }
1139                         break;
1140                 case JSON_NUMBER :
1141                         if( numeric ) {
1142                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1143                         } else {
1144                                 sqlAddMsg( state,
1145                                         "Invalid value for bind variable: expected a number, found a string" );
1146                                 state->error = 1;
1147                         }
1148                         break;
1149                 case JSON_NULL :
1150                         buffer_add( state->sql, "NULL" );
1151                         break;
1152                 case JSON_BOOL :
1153                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1154                                 "Internal error: boolean value for bind variable" ));
1155                         state->error = 1;
1156                         break;
1157                 default :
1158                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1159                                 "Internal error: corrupted value for bind variable" ));
1160                         state->error = 1;
1161                         break;
1162         }
1163 }
1164
1165 /**
1166         @brief Start a new line in the output, with the current level of indentation.
1167         @param state Pointer to the query-building context.
1168 */
1169 static void add_newline( BuildSQLState* state ) {
1170         buffer_add_char( state->sql, '\n' );
1171
1172         // Add indentation
1173         static const char blanks[] = "                                ";   // 32 blanks
1174         static const size_t maxlen = sizeof( blanks ) - 1;
1175         const int blanks_per_level = 3;
1176         int n = state->indent * blanks_per_level;
1177         while( n > 0 ) {
1178                 size_t len = n >= maxlen ? maxlen : n;
1179                 buffer_add_n( state->sql, blanks, len );
1180                 n -= len;
1181         }
1182 }
1183
1184 /**
1185         @brief Increase the degree of indentation.
1186         @param state Pointer to the query-building context.
1187 */
1188 static inline void incr_indent( BuildSQLState* state ) {
1189         ++state->indent;
1190 }
1191
1192 /**
1193         @brief Reduce the degree of indentation.
1194         @param state Pointer to the query-building context.
1195 */
1196 static inline void decr_indent( BuildSQLState* state ) {
1197         if( state->indent )
1198                 --state->indent;
1199 }