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                         sqlAddMsg( state, "Cast expressions not yet supported" );
 683                         state->error = 1;
 684                         break;
 685                 case EXP_COLUMN :                 // Table column
 686                         if( expr->negate )
 687                                 buffer_add( state->sql, "NOT " );
 688
 689                         if( expr->table_alias ) {
 690                                 buffer_add_char( state->sql, '\"' );
 691                                 buffer_add( state->sql, expr->table_alias );
 692                                 buffer_add( state->sql, "\"." );
 693                         }
 694                         if( expr->column_name ) {
 695                                 buffer_add( state->sql, expr->column_name );
 696                         } else {
 697                                 buffer_add_char( state->sql, '*' );
 698                         }
 699                         break;
 700                 case EXP_EXIST :
 701                         if( !expr->subquery ) {
 702                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 703                                         "No subquery found for EXIST expression # %d", expr->id ));
 704                                 state->error = 1;
 705                         } else {
 706                                 if( expr->negate )
 707                                         buffer_add( state->sql, "NOT " );
 708
 709                                 buffer_add( state->sql, "EXISTS (" );
 710                                 incr_indent( state );
 711                                 build_Query( state, expr->subquery );
 712                                 decr_indent( state );
 713                                 add_newline( state );
 714                                 buffer_add_char( state->sql, ')' );
 715                         }
 716                         break;
 717                 case EXP_FIELD :
 718                         if( expr->negate )
 719                                 buffer_add( state->sql, "NOT " );
 720
 721                         sqlAddMsg( state, "Field expressions not yet supported" );
 722                         state->error = 1;
 723                         break;
 724                 case EXP_FUNCTION :
 725                         buildFunction( state, expr );
 726                         break;
 727                 case EXP_IN :
 728                         if( expr->left_operand ) {
 729                                 buildExpression( state, expr->left_operand );
 730                                 if( !state->error ) {
 731                                         if( expr->negate )
 732                                                 buffer_add( state->sql, "NOT " );
 733                                         buffer_add( state->sql, " IN (" );
 734
 735                                         if( expr->subquery ) {
 736                                                 incr_indent( state );
 737                                                 build_Query( state, expr->subquery );
 738                                                 if( state->error )
 739                                                         sqlAddMsg( state, "Unable to build subquery for IN condition" );
 740                                                 else {
 741                                                         decr_indent( state );
 742                                                         add_newline( state );
 743                                                         buffer_add_char( state->sql, ')' );
 744                                                 }
 745                                         } else {
 746                                                 buildSeries( state, expr->subexp_list, NULL );
 747                                                 if( state->error )
 748                                                         sqlAddMsg( state, "Unable to build IN list" );
 749                                                 else
 750                                                         buffer_add_char( state->sql, ')' );
 751                                         }
 752                                 }
 753                         }
 754                         break;
 755                 case EXP_ISNULL :
 756                         if( expr->left_operand ) {
 757                                 buildExpression( state, expr->left_operand );
 758                                 if( state->error ) {
 759                                         sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
 760                                                 expr->id );
 761                                         break;
 762                                 }
 763                         }
 764
 765                         if( expr->negate )
 766                                 buffer_add( state->sql, " IS NOT NULL" );
 767                         else
 768                                 buffer_add( state->sql, " IS NULL" );
 769                         break;
 770                 case EXP_NULL :
 771                         if( expr->negate )
 772                                 buffer_add( state->sql, "NOT " );
 773
 774                         buffer_add( state->sql, "NULL" );
 775                         break;
 776                 case EXP_NUMBER :                    // Numeric literal
 777                         if( !expr->literal ) {
 778                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 779                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 780                                 state->error = 1;
 781                         } else {
 782                                 buffer_add( state->sql, expr->literal );
 783                         }
 784                         break;
 785                 case EXP_OPERATOR :
 786                         if( expr->negate )
 787                                 buffer_add( state->sql, "NOT (" );
 788
 789                         if( expr->left_operand ) {
 790                                 buildExpression( state, expr->left_operand );
 791                                 if( state->error ) {
 792                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 793                                                 expr->id );
 794                                         break;
 795                                 }
 796                         }
 797                         buffer_add_char( state->sql, ' ' );
 798                         buffer_add( state->sql, expr->op );
 799                         buffer_add_char( state->sql, ' ' );
 800                         if( expr->right_operand ) {
 801                                 buildExpression( state, expr->right_operand );
 802                                 if( state->error ) {
 803                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 804                                                            expr->id );
 805                                         break;
 806                                 }
 807                         }
 808
 809                         if( expr->negate )
 810                                 buffer_add_char( state->sql, ')' );
 811
 812                         break;
 813                 case EXP_SERIES :
 814                         if( expr->negate )
 815                                 buffer_add( state->sql, "NOT (" );
 816
 817                         buildSeries( state, expr->subexp_list, expr->op );
 818                         if( state->error ) {
 819                                 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
 820                                         expr->op ? expr->op : "," );
 821                         }
 822                         if( expr->negate )
 823                                 buffer_add_char( state->sql, ')' );
 824
 825                         break;
 826                 case EXP_STRING :                     // String literal
 827                         if( !expr->literal ) {
 828                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 829                                         "Internal error: No string value in string expression # %d", expr->id ));
 830                                         state->error = 1;
 831                         } else {
 832                                 char* str = strdup( expr->literal );
 833                                 dbi_conn_quote_string( state->dbhandle, &str );
 834                                 if( str ) {
 835                                         buffer_add( state->sql, str );
 836                                         free( str );
 837                                 } else {
 838                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 839                                                 "Unable to format string literal \"%s\" for expression # %d",
 840                                                         expr->literal, expr->id ));
 841                                         state->error = 1;
 842                                 }
 843                         }
 844                         break;
 845                 case EXP_SUBQUERY :
 846                         if( expr->negate )
 847                                 buffer_add( state->sql, "NOT " );
 848
 849                         if( expr->subquery ) {
 850                                 buffer_add_char( state->sql, '(' );
 851                                 incr_indent( state );
 852                                 build_Query( state, expr->subquery );
 853                                 decr_indent( state );
 854                                 add_newline( state );
 855                                 buffer_add_char( state->sql, ')' );
 856                         } else {
 857                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 858                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 859                                 state->error = 1;
 860                         }
 861                         break;
 862         }
 863
 864         if( expr->parenthesize )
 865                 buffer_add_char( state->sql, ')' );
 866 }
 867
 868 /**
 869         @brief Build a CASE expression.
 870         @param state Pointer to the query-building context.
 871         @param exp Pointer to an Expression representing a CASE expression.
 872 */
 873 static void buildCase( BuildSQLState* state, const Expression* expr ) {
 874         // Sanity checks
 875         if( ! expr->left_operand ) {
 876                 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
 877                 state->error  = 1;
 878                 return;
 879         } else if( ! expr->branch_list ) {
 880                 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
 881                 state->error  = 1;
 882                 return;
 883         }
 884
 885         if( expr->negate )
 886                 buffer_add( state->sql, "NOT (" );
 887
 888         // left_operand is the expression on which we shall branch
 889         buffer_add( state->sql, "CASE " );
 890         buildExpression( state, expr->left_operand );
 891         if( state->error ) {
 892                 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
 893                 return;
 894         }
 895
 896         incr_indent( state );
 897
 898         // Emit each branch in turn
 899         CaseBranch* branch = expr->branch_list;
 900         while( branch ) {
 901                 add_newline( state );
 902
 903                 if( branch->condition ) {
 904                         // Emit a WHEN condition
 905                         buffer_add( state->sql, "WHEN " );
 906                         buildExpression( state, branch->condition );
 907                         incr_indent( state );
 908                         add_newline( state );
 909                         buffer_add( state->sql, "THEN " );
 910                 } else {
 911                         // Emit ELSE
 912                         buffer_add( state->sql, "ELSE " );
 913                         incr_indent( state );
 914                         add_newline( state );
 915                 }
 916
 917                 // Emit the THEN expression
 918                 buildExpression( state, branch->result );
 919                 decr_indent( state );
 920
 921                 branch = branch->next;
 922         }
 923
 924         decr_indent( state );
 925         add_newline( state );
 926         buffer_add( state->sql, "END" );
 927
 928         if( expr->negate )
 929                 buffer_add( state->sql, ")" );
 930 }
 931
 932 /**
 933         @brief Build a function call.
 934         @param state Pointer to the query-building context.
 935         @param exp Pointer to an Expression representing a function call.
 936
 937         This function does not currently accommodate certain functions with idiosyncratic
 938         syntax, such as the absence of parentheses, or the use of certain keywords in
 939         in the parameter list.
 940 */
 941 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
 942         if( expr->negate )
 943                 buffer_add( state->sql, "NOT " );
 944
 945         // We rely on the input side to ensure that the function name is available
 946         buffer_add( state->sql, expr->function_name );
 947         buffer_add_char( state->sql, '(' );
 948
 949         // Add the parameters, if any
 950         buildSeries( state, expr->subexp_list, NULL );
 951
 952         buffer_add_char( state->sql, ')' );
 953 }
 954
 955 /**
 956         @brief Build a series of expressions separated by a specified operator, or by commas.
 957         @param state Pointer to the query-building context.
 958         @param subexp_list Pointer to the first Expression in a linked list.
 959         @param op Pointer to the operator, or NULL for commas.
 960
 961         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
 962         subsequent operators will begin on a new line.
 963 */
 964 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
 965
 966         if( !subexp_list)
 967                 return;                // List is empty
 968
 969         int comma = 0;             // Boolean; true if separator is a comma
 970         int newline_needed = 0;    // Boolean; true if operator is AND or OR
 971
 972         if( !op ) {
 973                 op = ",";
 974                 comma = 1;
 975         } else if( !strcmp( op, "," ))
 976                 comma = 1;
 977         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
 978                 newline_needed = 1;
 979
 980         int first = 1;               // Boolean; true for first item in list
 981         while( subexp_list ) {
 982                 if( first )
 983                         first = 0;   // No separator needed yet
 984                 else {
 985                         // Insert a separator
 986                         if( comma )
 987                                 buffer_add( state->sql, ", " );
 988                         else {
 989                                 if( newline_needed )
 990                                         add_newline( state );
 991                                 else
 992                                         buffer_add_char( state->sql, ' ' );
 993
 994                                 buffer_add( state->sql, op );
 995                                 buffer_add_char( state->sql, ' ' );
 996                         }
 997                 }
 998
 999                 buildExpression( state, subexp_list );
1000                 subexp_list = subexp_list->next;
1001         }
1002 }
1003
1004 /**
1005         @brief Add the value of a bind variable to an SQL statement.
1006         @param state Pointer to the query-building context.
1007         @param bind Pointer to the bind variable whose value is to be added to the SQL.
1008
1009         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1010         the type of the bind variable.
1011 */
1012 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1013
1014         // Decide where to get the value, if any
1015         const jsonObject* value = NULL;
1016         if( bind->actual_value )
1017                 value = bind->actual_value;
1018         else if( bind->default_value ) {
1019                 if( state->defaults_usable )
1020                         value = bind->default_value;
1021                 else {
1022                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1023                                 bind->name );
1024                         state->error = 1;
1025                         return;
1026                 }
1027         } else if( state->values_required ) {
1028                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1029                 state->error = 1;
1030                 return;
1031         } else {
1032                 // No value available, and that's okay.  Emit the name of the bind variable.
1033                 buffer_add_char( state->sql, ':' );
1034                 buffer_add( state->sql, bind->name );
1035                 return;
1036         }
1037
1038         // If we get to this point, we know that a value is available.  Carry on.
1039
1040         int numeric = 0;       // Boolean
1041         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1042                 numeric = 1;
1043
1044         // Emit the value
1045         switch( bind->type ) {
1046                 case BIND_STR :
1047                 case BIND_NUM :
1048                         buildScalar( state, numeric, value );
1049                         break;
1050                 case BIND_STR_LIST :
1051                 case BIND_NUM_LIST :
1052                         if( JSON_ARRAY == value->type ) {
1053                                 // Iterate over array, emit each value
1054                                 int first = 1;   // Boolean
1055                                 unsigned long max = value->size;
1056                                 unsigned long i = 0;
1057                                 while( i < max ) {
1058                                         if( first )
1059                                                 first = 0;
1060                                         else
1061                                                 buffer_add( state->sql, ", " );
1062
1063                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1064                                         ++i;
1065                                 }
1066                         } else {
1067                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1068                                         "Invalid value for bind variable; expected a list of values" ));
1069                                 state->error = 1;
1070                         }
1071                         break;
1072                 default :
1073                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1074                                 "Internal error: invalid type for bind variable" ));
1075                         state->error = 1;
1076                         break;
1077         }
1078
1079         if( state->error )
1080                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1081                         "Unable to emit value of bind variable \"%s\"", bind->name ));
1082 }
1083
1084 /**
1085         @brief Add a number or quoted string to an SQL statement.
1086         @param state Pointer to the query-building context.
1087         @param numeric Boolean; true if the value is expected to be a number
1088         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1089 */
1090 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1091         switch( obj->type ) {
1092                 case JSON_HASH :
1093                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1094                                 "Internal error: hash value for bind variable" ));
1095                         state->error = 1;
1096                         break;
1097                 case JSON_ARRAY :
1098                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1099                                 "Internal error: array value for bind variable" ));
1100                         state->error = 1;
1101                         break;
1102                 case JSON_STRING :
1103                         if( numeric ) {
1104                                 sqlAddMsg( state,
1105                                         "Invalid value for bind variable: expected a string, found a number" );
1106                                 state->error = 1;
1107                         } else {
1108                                 char* str = jsonObjectToSimpleString( obj );
1109                                 dbi_conn_quote_string( state->dbhandle, &str );
1110                                 if( str ) {
1111                                         buffer_add( state->sql, str );
1112                                         free( str );
1113                                 } else {
1114                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1115                                                 "Unable to format string literal \"%s\" for bind variable",
1116                                                 jsonObjectGetString( obj )));
1117                                         state->error = 1;
1118                                 }
1119                         }
1120                         break;
1121                 case JSON_NUMBER :
1122                         if( numeric ) {
1123                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1124                         } else {
1125                                 sqlAddMsg( state,
1126                                         "Invalid value for bind variable: expected a number, found a string" );
1127                                 state->error = 1;
1128                         }
1129                         break;
1130                 case JSON_NULL :
1131                         buffer_add( state->sql, "NULL" );
1132                         break;
1133                 case JSON_BOOL :
1134                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1135                                 "Internal error: boolean value for bind variable" ));
1136                         state->error = 1;
1137                         break;
1138                 default :
1139                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1140                                 "Internal error: corrupted value for bind variable" ));
1141                         state->error = 1;
1142                         break;
1143         }
1144 }
1145
1146 /**
1147         @brief Start a new line in the output, with the current level of indentation.
1148         @param state Pointer to the query-building context.
1149 */
1150 static void add_newline( BuildSQLState* state ) {
1151         buffer_add_char( state->sql, '\n' );
1152
1153         // Add indentation
1154         static const char blanks[] = "                                ";   // 32 blanks
1155         static const size_t maxlen = sizeof( blanks ) - 1;
1156         const int blanks_per_level = 3;
1157         int n = state->indent * blanks_per_level;
1158         while( n > 0 ) {
1159                 size_t len = n >= maxlen ? maxlen : n;
1160                 buffer_add_n( state->sql, blanks, len );
1161                 n -= len;
1162         }
1163 }
1164
1165 /**
1166         @brief Increase the degree of indentation.
1167         @param state Pointer to the query-building context.
1168 */
1169 static inline void incr_indent( BuildSQLState* state ) {
1170         ++state->indent;
1171 }
1172
1173 /**
1174         @brief Reduce the degree of indentation.
1175         @param state Pointer to the query-building context.
1176 */
1177 static inline void decr_indent( BuildSQLState* state ) {
1178         if( state->indent )
1179                 --state->indent;
1180 }