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