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