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, StoredQ* query );
  19 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str );
  20 static void buildSelect( BuildSQLState* state, StoredQ* query );
  21 static void buildFrom( BuildSQLState* state, FromRelation* core_from );
  22 static void buildJoin( BuildSQLState* state, FromRelation* join );
  23 static void buildSelectList( BuildSQLState* state, SelectItem* item );
  24 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list );
  25 static void buildExpression( BuildSQLState* state, Expression* expr );
  26 static void buildBindVar( BuildSQLState* state, BindVar* bind );
  27 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
  28
  29 static void add_newline( BuildSQLState* state );
  30 static inline void incr_indent( BuildSQLState* state );
  31 static inline void decr_indent( BuildSQLState* state );
  32
  33 /**
  34         @brief Create a jsonObject representing the current list of bind variables.
  35         @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
  36         @return Pointer to the newly created jsonObject.
  37
  38         The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
  39         variables.  The data for each is another level of JSON_HASH with a fixed set of tags:
  40         - "label"
  41         - "type"
  42         - "description"
  43         - "default_value" (as a jsonObject)
  44         - "actual_value" (as a jsonObject)
  45
  46         Any non-existent values are represented as JSON_NULLs.
  47
  48         The calling code is responsible for freeing the returned jsonOjbect by calling
  49         jsonObjectFree().
  50 */
  51 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
  52         jsonObject* list = jsonNewObjectType( JSON_HASH );
  53
  54         if( bindvar_list && osrfHashGetCount( bindvar_list )) {
  55                 // Traverse our internal list of bind variables
  56                 BindVar* bind = NULL;
  57                 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
  58                 while(( bind = osrfHashIteratorNext( iter ))) {
  59                         // Create an hash to represent the bind variable
  60                         jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
  61
  62                         // Add an entry for each attribute
  63                         jsonObject* attr = jsonNewObject( bind->label );
  64                         jsonObjectSetKey( bind_obj, "label", attr );
  65
  66                         const char* type = NULL;
  67                         switch( bind->type ) {
  68                                 case BIND_STR :
  69                                         type = "string";
  70                                         break;
  71                                 case BIND_NUM :
  72                                         type = "number";
  73                                         break;
  74                                 case BIND_STR_LIST :
  75                                         type = "string_list";
  76                                         break;
  77                                 case BIND_NUM_LIST :
  78                                         type = "number_list";
  79                                         break;
  80                                 default :
  81                                         type = "(invalid)";
  82                                         break;
  83                         }
  84                         attr = jsonNewObject( type );
  85                         jsonObjectSetKey( bind_obj, "type", attr );
  86
  87                         attr = jsonNewObject( bind->description );
  88                         jsonObjectSetKey( bind_obj, "description", attr );
  89
  90                         attr = jsonObjectClone( bind->default_value );
  91                         jsonObjectSetKey( bind_obj, "default_value", attr );
  92
  93                         attr = jsonObjectClone( bind->actual_value );
  94                         jsonObjectSetKey( bind_obj, "actual_value", attr );
  95
  96                         // Add the bind variable to the list
  97                         jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
  98                 }
  99                 osrfHashIteratorFree( iter );
 100         }
 101
 102         return list;
 103 }
 104
 105 /**
 106         @brief Apply values to bind variables, overriding the defaults, if any.
 107         @param state Pointer to the query-building context.
 108         @param bindings A JSON_HASH of values.
 109         @return 0 if successful, or 1 if not.
 110
 111         The @a bindings parameter must be a JSON_HASH.  The keys are the names of bind variables.
 112         The values are the corresponding values for the variables.
 113 */
 114 int oilsApplyBindValues( BuildSQLState* state, jsonObject* bindings ) {
 115         if( !state ) {
 116                 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
 117                 return 1;
 118         } else if( !bindings ) {
 119                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 120                         "Internal error: No pointer to bindings" ));
 121                 return 1;
 122         } else if( bindings->type != JSON_HASH ) {
 123                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 124                         "Internal error: bindings parameter is not a JSON_HASH" ));
 125                 return 1;
 126         }
 127
 128         int rc = 0;
 129         jsonObject* value = NULL;
 130         jsonIterator* iter = jsonNewIterator( bindings );
 131         while(( value = jsonIteratorNext( iter ))) {
 132                 const char* var_name = iter->key;
 133                 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
 134                 if( bind ) {
 135                         // Apply or replace the value for the specified variable
 136                         if( bind->actual_value )
 137                                 jsonObjectFree( bind->actual_value );
 138                         bind->actual_value = jsonObjectClone( value );
 139                 } else {
 140                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 141                                 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
 142                         rc = 1;
 143                 }
 144         }
 145         jsonIteratorFree( iter );
 146
 147         return rc;
 148 }
 149
 150 /**
 151         @brief Build an SQL query.
 152         @param state Pointer to the query-building context.
 153         @param query Pointer to the query to be built.
 154         @return Zero if successful, or 1 if not.
 155
 156         Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
 157 */
 158 int buildSQL( BuildSQLState* state, StoredQ* query ) {
 159         state->error  = 0;
 160         buffer_reset( state->sql );
 161         state->indent = 0;
 162         build_Query( state, query );
 163         if( ! state->error ) {
 164                 // Remove the trailing space, if there is one, and add a semicolon.
 165                 char c = buffer_chomp( state->sql );
 166                 if( c != ' ' )
 167                         buffer_add_char( state->sql, c );  // oops, not a space; put it back
 168                 buffer_add( state->sql, ";\n" );
 169         }
 170         return state->error;
 171 }
 172
 173 /**
 174         @brief Build an SQL query, appending it to what has been built so far.
 175         @param state Pointer to the query-building context.
 176         @param query Pointer to the query to be built.
 177
 178         Look at the query type and branch to the corresponding routine.
 179 */
 180 static void build_Query( BuildSQLState* state, StoredQ* query ) {
 181         if( buffer_length( state->sql ))
 182                 add_newline( state );
 183
 184         switch( query->type ) {
 185                 case QT_SELECT :
 186                         buildSelect( state, query );
 187                         break;
 188                 case QT_UNION :
 189                         buildCombo( state, query, "UNION" );
 190                         break;
 191                 case QT_INTERSECT :
 192                         buildCombo( state, query, "INTERSECT" );
 193                         break;
 194                 case QT_EXCEPT :
 195                         buildCombo( state, query, "EXCEPT" );
 196                         break;
 197                 default :
 198                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 199                                 "Internal error: invalid query type %d in query # %d",
 200                                 query->type, query->id ));
 201                         state->error = 1;
 202                         break;
 203         }
 204 }
 205
 206 /**
 207         @brief Build a UNION, INTERSECT, or EXCEPT query.
 208         @param state Pointer to the query-building context.
 209         @param query Pointer to the query to be built.
 210         @param type_str The query type, as a string.
 211 */
 212 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
 213
 214         QSeq* seq = query->child_list;
 215         if( !seq ) {
 216                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 217                         "Internal error: No child queries within %s query # %d",
 218                         type_str, query->id ));
 219                 state->error = 1;
 220                 return;
 221         }
 222
 223         // Traverse the list of child queries
 224         while( seq ) {
 225                 build_Query( state, seq->child_query );
 226                 if( state->error ) {
 227                         sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
 228                                 seq->child_query->id, type_str, query->id );
 229                         return;
 230                 }
 231                 seq = seq->next;
 232                 if( seq ) {
 233                         add_newline( state );
 234                         buffer_add( state->sql, type_str );
 235                         buffer_add_char( state->sql, ' ' );
 236                         if( query->use_all )
 237                                 buffer_add( state->sql, "ALL " );
 238                 }
 239         }
 240
 241         return;
 242 }
 243
 244 /**
 245         @brief Build a SELECT statement.
 246         @param state Pointer to the query-building context.
 247         @param query Pointer to the StoredQ structure that represents the query.
 248 */
 249 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
 250
 251         FromRelation* from_clause = query->from_clause;
 252         if( !from_clause ) {
 253                 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
 254                 state->error = 1;
 255                 return;
 256         }
 257
 258         // To do: get SELECT list; just a stub here
 259         buffer_add( state->sql, "SELECT" );
 260         incr_indent( state );
 261         buildSelectList( state, query->select_list );
 262         if( state->error ) {
 263                 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
 264                 state->error = 1;
 265                 return;
 266         }
 267         decr_indent( state );
 268
 269         // Build FROM clause, if there is one
 270         if( query->from_clause ) {
 271                 buildFrom( state, query->from_clause );
 272                 if( state->error ) {
 273                         sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
 274                         state->error = 1;
 275                         return;
 276                 }
 277         }
 278
 279         // Build WHERE clause, if there is one
 280         if( query->where_clause ) {
 281                 add_newline( state );
 282                 buffer_add( state->sql, "WHERE" );
 283                 incr_indent( state );
 284                 add_newline( state );
 285                 buildExpression( state, query->where_clause );
 286                 if( state->error ) {
 287                         sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
 288                         state->error = 1;
 289                         return;
 290                 }
 291                 decr_indent( state );
 292         }
 293
 294         // To do: build GROUP BY clause, if there is one
 295
 296         // Build HAVING clause, if there is one
 297         if( query->having_clause ) {
 298                 add_newline( state );
 299                 buffer_add( state->sql, "HAVING" );
 300                 incr_indent( state );
 301                 add_newline( state );
 302                 buildExpression( state, query->having_clause );
 303                 if( state->error ) {
 304                         sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
 305                         state->error = 1;
 306                         return;
 307                 }
 308                 decr_indent( state );
 309         }
 310
 311         // Build ORDER BY clause, if there is one
 312         if( query->order_by_list ) {
 313                 buildOrderBy( state, query->order_by_list );
 314                 if( state->error ) {
 315                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
 316                         state->error = 1;
 317                         return;
 318                 }
 319         }
 320
 321         // To do: Build LIMIT clause, if there is one
 322
 323         // To do: Build OFFSET clause, if there is one
 324
 325         state->error = 0;
 326 }
 327
 328 /**
 329         @brief Build a FROM clause.
 330         @param Pointer to the query-building context.
 331         @param Pointer to the StoredQ query to which the FROM clause belongs.
 332 */
 333 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
 334
 335         add_newline( state );
 336         buffer_add( state->sql, "FROM" );
 337         incr_indent( state );
 338         add_newline( state );
 339
 340         switch( core_from->type ) {
 341                 case FRT_RELATION : {
 342                         char* relation = core_from->table_name;
 343                         if( !relation ) {
 344                                 if( !core_from->class_name ) {
 345                                         sqlAddMsg( state, "No relation specified for core relation # %d",
 346                                                 core_from->id );
 347                                         state->error = 1;
 348                                         return;
 349                                 }
 350
 351                                 // Look up table name, view name, or source_definition in the IDL
 352                                 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
 353                                 relation = oilsGetRelation( class_hash );
 354                         }
 355
 356                         // Add table or view
 357                         buffer_add( state->sql, relation );
 358                         if( !core_from->table_name )
 359                                 free( relation );   // In this case we strdup'd it, must free it
 360                         break;
 361                 }
 362                 case FRT_SUBQUERY :
 363                         buffer_add_char( state->sql, '(' );
 364                         incr_indent( state );
 365                         build_Query( state, core_from->subquery );
 366                         decr_indent( state );
 367                         add_newline( state );
 368                         buffer_add_char( state->sql, ')' );
 369                         break;
 370                 case FRT_FUNCTION :
 371                         sqlAddMsg( state, "Functions in FROM clause not yet supported" );
 372                         state->error = 1;
 373                         return;
 374         }
 375
 376         // Add a table alias, if possible
 377         if( core_from->table_alias ) {
 378                 buffer_add( state->sql, " AS \"" );
 379                 buffer_add( state->sql, core_from->table_alias );
 380                 buffer_add( state->sql, "\" " );
 381         }
 382         else if( core_from->class_name ) {
 383                 buffer_add( state->sql, " AS \"" );
 384                 buffer_add( state->sql, core_from->class_name );
 385                 buffer_add( state->sql, "\" " );
 386         } else
 387                 buffer_add_char( state->sql, ' ' );
 388
 389         incr_indent( state );
 390         FromRelation* join = core_from->join_list;
 391         while( join ) {
 392                 buildJoin( state, join );
 393                 if( state->error ) {
 394                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
 395                                 core_from->id );
 396                         break;
 397                 } else
 398                         join = join->next;
 399         }
 400         decr_indent( state );
 401         decr_indent( state );
 402 }
 403
 404 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
 405         add_newline( state );
 406         switch( join->join_type ) {
 407                 case JT_NONE :
 408                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
 409                         state->error = 1;
 410                         return;
 411                 case JT_INNER :
 412                         buffer_add( state->sql, "INNER JOIN " );
 413                         break;
 414                 case JT_LEFT:
 415                         buffer_add( state->sql, "LEFT JOIN " );
 416                         break;
 417                 case JT_RIGHT:
 418                         buffer_add( state->sql, "RIGHT JOIN " );
 419                         break;
 420                 case JT_FULL:
 421                         buffer_add( state->sql, "FULL JOIN " );
 422                         break;
 423                 default :
 424                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
 425                         state->error = 1;
 426                         return;
 427         }
 428
 429         switch( join->type ) {
 430                 case FRT_RELATION :
 431                         // Sanity check
 432                         if( !join->table_name || ! *join->table_name ) {
 433                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
 434                                 state->error = 1;
 435                                 return;
 436                         }
 437                         buffer_add( state->sql, join->table_name );
 438                         break;
 439                 case FRT_SUBQUERY :
 440                         // Sanity check
 441                         if( !join->subquery ) {
 442                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
 443                                 state->error = 1;
 444                                 return;
 445                         } else if( !join->table_alias ) {
 446                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
 447                                         join->id );
 448                                 state->error = 1;
 449                                 return;
 450                         }
 451                         buffer_add_char( state->sql, '(' );
 452                         incr_indent( state );
 453                         build_Query( state, join->subquery );
 454                         decr_indent( state );
 455                         add_newline( state );
 456                         buffer_add_char( state->sql, ')' );
 457                         break;
 458                 case FRT_FUNCTION :
 459                         if( !join->table_name || ! *join->table_name ) {
 460                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
 461                                         join->id );
 462                                 state->error = 1;
 463                                 return;
 464                         }
 465                         break;
 466         }
 467
 468         const char* effective_alias = join->table_alias;
 469         if( !effective_alias )
 470                 effective_alias = join->class_name;
 471
 472         if( effective_alias ) {
 473                 buffer_add( state->sql, " AS \"" );
 474                 buffer_add( state->sql, effective_alias );
 475                 buffer_add_char( state->sql, '\"' );
 476         }
 477
 478         if( join->on_clause ) {
 479                 incr_indent( state );
 480                 add_newline( state );
 481                 buffer_add( state->sql, "ON " );
 482                 buildExpression( state, join->on_clause );
 483                 decr_indent( state );
 484         }
 485
 486         FromRelation* subjoin = join->join_list;
 487         while( subjoin ) {
 488                 buildJoin( state, subjoin );
 489                 if( state->error ) {
 490                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
 491                         break;
 492                 } else
 493                         subjoin = subjoin->next;
 494         }
 495 }
 496
 497 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
 498
 499         int first = 1;
 500         while( item ) {
 501                 if( !first )
 502                         buffer_add_char( state->sql, ',' );
 503                 add_newline( state );
 504                 buildExpression( state, item->expression );
 505                 if( state->error ) {
 506                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
 507                         state->error = 1;
 508                         break;
 509                 }
 510
 511                 if( item->column_alias ) {
 512                         buffer_add( state->sql, " AS \"" );
 513                         buffer_add( state->sql, item->column_alias );
 514                         buffer_add_char( state->sql, '\"' );
 515                 }
 516                 first = 0;
 517                 item = item->next;
 518         };
 519         buffer_add_char( state->sql, ' ' );
 520 }
 521
 522 /**
 523         @brief Add an ORDER BY clause to the current query.
 524         @param state Pointer to the query-building context.
 525         @param ord_list Pointer to the first node in a linked list of OrderItems.
 526 */
 527 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
 528         add_newline( state );
 529         buffer_add( state->sql, "ORDER BY" );
 530         incr_indent( state );
 531
 532         int first = 1;    // boolean
 533         while( ord_list ) {
 534                 if( first )
 535                         first = 0;
 536                 else
 537                         buffer_add_char( state->sql, ',' );
 538                 add_newline( state );
 539                 buildExpression( state, ord_list->expression );
 540                 if( state->error ) {
 541                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
 542                         return;
 543                 }
 544
 545                 ord_list = ord_list->next;
 546         }
 547
 548         decr_indent( state );
 549         return;
 550 }
 551
 552 /**
 553         @brief Build an arbitrary expression.
 554         @param state Pointer to the query-building context.
 555         @param expr Pointer to the Expression representing the expression to be built.
 556 */
 557 static void buildExpression( BuildSQLState* state, Expression* expr ) {
 558         if( !expr ) {
 559                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 560                         "Internal error: NULL pointer to Expression" ));
 561                 state->error = 1;
 562                 return;
 563         }
 564
 565         if( expr->parenthesize )
 566                 buffer_add_char( state->sql, '(' );
 567
 568         switch( expr->type ) {
 569                 case EXP_BETWEEN :
 570                         if( expr->negate )
 571                                 buffer_add( state->sql, "NOT " );
 572
 573                         sqlAddMsg( state, "BETWEEN expressions not yet supported" );
 574                         state->error = 1;
 575                         break;
 576                 case EXP_BIND :
 577                         if( !expr->bind ) {     // Sanity check
 578                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 579                                         "Internal error: no variable for bind variable expression" ));
 580                                 state->error = 1;
 581                         } else
 582                                 buildBindVar( state, expr->bind );
 583                         break;
 584                 case EXP_BOOL :
 585                         if( expr->negate )
 586                                 buffer_add( state->sql, "NOT " );
 587
 588                         if( expr->literal ) {
 589                                 buffer_add( state->sql, expr->literal );
 590                                 buffer_add_char( state->sql, ' ' );
 591                         } else
 592                                 buffer_add( state->sql, "FALSE " );
 593                         break;
 594                 case EXP_CASE :
 595                         if( expr->negate )
 596                                 buffer_add( state->sql, "NOT " );
 597
 598                         sqlAddMsg( state, "CASE expressions not yet supported" );
 599                         state->error = 1;
 600                         break;
 601                 case EXP_CAST :                   // Type cast
 602                         if( expr->negate )
 603                                 buffer_add( state->sql, "NOT " );
 604
 605                         sqlAddMsg( state, "Cast expressions not yet supported" );
 606                         state->error = 1;
 607                         break;
 608                 case EXP_COLUMN :                 // Table column
 609                         if( expr->negate )
 610                                 buffer_add( state->sql, "NOT " );
 611
 612                         if( expr->table_alias ) {
 613                                 buffer_add_char( state->sql, '\"' );
 614                                 buffer_add( state->sql, expr->table_alias );
 615                                 buffer_add( state->sql, "\"." );
 616                         }
 617                         if( expr->column_name ) {
 618                                 buffer_add( state->sql, expr->column_name );
 619                         } else {
 620                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 621                                         "Column name not present in expression # %d", expr->id ));
 622                                 state->error = 1;
 623                         }
 624                         break;
 625                 case EXP_EXIST :
 626                         if( !expr->subquery ) {
 627                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 628                                         "No subquery found for EXIST expression # %d", expr->id ));
 629                                 state->error = 1;
 630                         } else {
 631                                 if( expr->negate )
 632                                         buffer_add( state->sql, "NOT " );
 633
 634                                 buffer_add( state->sql, "EXISTS (" );
 635                                 incr_indent( state );
 636                                 build_Query( state, expr->subquery );
 637                                 decr_indent( state );
 638                                 add_newline( state );
 639                                 buffer_add_char( state->sql, ')' );
 640                         }
 641                         break;
 642                 case EXP_FIELD :
 643                         if( expr->negate )
 644                                 buffer_add( state->sql, "NOT " );
 645
 646                         sqlAddMsg( state, "Field expressions not yet supported" );
 647                         state->error = 1;
 648                         break;
 649                 case EXP_FUNCTION :
 650                         if( expr->negate )
 651                                 buffer_add( state->sql, "NOT " );
 652
 653                         sqlAddMsg( state, "Function expressions not yet supported" );
 654                         state->error = 1;
 655                         break;
 656                 case EXP_IN :
 657                         if( expr->left_operand ) {
 658                                 buildExpression( state, expr->left_operand );
 659                                 if( !state->error ) {
 660                                         if( expr->negate )
 661                                                 buffer_add( state->sql, "NOT " );
 662
 663                                         if( expr->subquery ) {
 664                                                 buffer_add( state->sql, " IN (" );
 665                                                 incr_indent( state );
 666                                                 build_Query( state, expr->subquery );
 667                                                 decr_indent( state );
 668                                                 add_newline( state );
 669                                                 buffer_add_char( state->sql, ')' );
 670                                         } else {
 671                                                 sqlAddMsg( state, "IN lists not yet supported" );
 672                                                 state->error = 1;
 673                                         }
 674                                 }
 675                         }
 676                         break;
 677                 case EXP_NULL :
 678                         if( expr->negate )
 679                                 buffer_add( state->sql, "NOT " );
 680
 681                         buffer_add( state->sql, "NULL" );
 682                         break;
 683                 case EXP_NUMBER :                    // Numeric literal
 684                         if( !expr->literal ) {
 685                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 686                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 687                                 state->error = 1;
 688                         } else {
 689                                 buffer_add( state->sql, expr->literal );
 690                         }
 691                         break;
 692                 case EXP_OPERATOR :
 693                         if( expr->negate )
 694                                 buffer_add( state->sql, "NOT (" );
 695
 696                         if( expr->left_operand ) {
 697                                 buildExpression( state, expr->left_operand );
 698                                 if( state->error ) {
 699                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 700                                                 expr->id );
 701                                         break;
 702                                 }
 703                         }
 704                         buffer_add_char( state->sql, ' ' );
 705                         buffer_add( state->sql, expr->op );
 706                         buffer_add_char( state->sql, ' ' );
 707                         if( expr->right_operand ) {
 708                                 buildExpression( state, expr->right_operand );
 709                                 if( state->error ) {
 710                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 711                                                            expr->id );
 712                                         break;
 713                                 }
 714                         }
 715
 716                         if( expr->negate )
 717                                 buffer_add_char( state->sql, ')' );
 718
 719                         break;
 720                 case EXP_STRING :                     // String literal
 721                         if( !expr->literal ) {
 722                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 723                                         "Internal error: No string value in string expression # %d", expr->id ));
 724                                         state->error = 1;
 725                         } else {
 726                                 // To do: escape special characters in the string
 727                                 buffer_add_char( state->sql, '\'' );
 728                                 buffer_add( state->sql, expr->literal );
 729                                 buffer_add_char( state->sql, '\'' );
 730                         }
 731                         break;
 732                 case EXP_SUBQUERY :
 733                         if( expr->negate )
 734                                 buffer_add( state->sql, "NOT " );
 735
 736                         if( expr->subquery ) {
 737                                 buffer_add_char( state->sql, '(' );
 738                                 incr_indent( state );
 739                                 build_Query( state, expr->subquery );
 740                                 decr_indent( state );
 741                                 add_newline( state );
 742                                 buffer_add_char( state->sql, ')' );
 743                         } else {
 744                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 745                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 746                                 state->error = 1;
 747                         }
 748                         break;
 749         }
 750
 751         if( expr->parenthesize )
 752                 buffer_add_char( state->sql, ')' );
 753 }
 754
 755 /**
 756         @brief Add the value of a bind variable to an SQL statement.
 757         @param state Pointer to the query-building context.
 758         @param bind Pointer to the bind variable whose value is to be added to the SQL.
 759
 760         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
 761         the type of the bind variable.
 762 */
 763 static void buildBindVar( BuildSQLState* state, BindVar* bind ) {
 764
 765         // Decide where to get the value, if any
 766         const jsonObject* value = NULL;
 767         if( bind->actual_value )
 768                 value = bind->actual_value;
 769         else if( bind->default_value ) {
 770                 if( state->defaults_usable )
 771                         value = bind->default_value;
 772                 else {
 773                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
 774                                 bind->name );
 775                         state->error = 1;
 776                         return;
 777                 }
 778         } else if( state->values_required ) {
 779                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
 780                 state->error = 1;
 781                 return;
 782         } else {
 783                 // No value available, and that's okay.  Emit the name of the bind variable.
 784                 buffer_add_char( state->sql, ':' );
 785                 buffer_add( state->sql, bind->name );
 786                 return;
 787         }
 788
 789         // If we get to this point, we know that a value is available.  Carry on.
 790
 791         int numeric = 0;       // Boolean
 792         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
 793                 numeric = 1;
 794
 795         // Emit the value
 796         switch( bind->type ) {
 797                 case BIND_STR :
 798                 case BIND_NUM :
 799                         buildScalar( state, numeric, value );
 800                         break;
 801                 case BIND_STR_LIST :
 802                 case BIND_NUM_LIST :
 803                         if( JSON_ARRAY == value->type ) {
 804                                 // Iterate over array, emit each value
 805                                 int first = 1;   // Boolean
 806                                 unsigned long max = value->size;
 807                                 unsigned long i = 0;
 808                                 while( i < max ) {
 809                                         if( first )
 810                                                 first = 0;
 811                                         else
 812                                                 buffer_add( state->sql, ", " );
 813
 814                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
 815                                         ++i;
 816                                 }
 817                         } else {
 818                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 819                                         "Invalid value for bind variable; expected a list of values" ));
 820                                 state->error = 1;
 821                         }
 822                         break;
 823                 default :
 824                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 825                                 "Internal error: invalid type for bind variable" ));
 826                         state->error = 1;
 827                         break;
 828         }
 829
 830         if( state->error )
 831                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 832                         "Unable to emit value of bind variable \"%s\"", bind->name ));
 833 }
 834
 835 /**
 836         @brief Add a number or quoted string to an SQL statement.
 837         @param state Pointer to the query-building context.
 838         @param numeric Boolean; true if the value is expected to be a number
 839         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
 840 */
 841 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
 842         switch( obj->type ) {
 843                 case JSON_HASH :
 844                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 845                                 "Internal error: hash value for bind variable" ));
 846                         state->error = 1;
 847                         break;
 848                 case JSON_ARRAY :
 849                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 850                                 "Internal error: array value for bind variable" ));
 851                         state->error = 1;
 852                         break;
 853                 case JSON_STRING :
 854                         if( numeric ) {
 855                                 sqlAddMsg( state,
 856                                         "Invalid value for bind variable: expected a string, found a number" );
 857                                 state->error = 1;
 858                         } else {
 859                                 // To do: escape special characters in the string
 860                                 buffer_add_char( state->sql, '\'' );
 861                                 buffer_add( state->sql, jsonObjectGetString( obj ));
 862                                 buffer_add_char( state->sql, '\'' );
 863                         }
 864                         break;
 865                 case JSON_NUMBER :
 866                         if( numeric ) {
 867                                 buffer_add( state->sql, jsonObjectGetString( obj ));
 868                         } else {
 869                                 sqlAddMsg( state,
 870                                         "Invalid value for bind variable: expected a number, found a string" );
 871                                 state->error = 1;
 872                         }
 873                         break;
 874                 case JSON_NULL :
 875                         buffer_add( state->sql, "NULL" );
 876                         break;
 877                 case JSON_BOOL :
 878                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 879                                 "Internal error: boolean value for bind variable" ));
 880                         state->error = 1;
 881                         break;
 882                 default :
 883                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 884                                 "Internal error: corrupted value for bind variable" ));
 885                         state->error = 1;
 886                         break;
 887         }
 888 }
 889
 890 static void add_newline( BuildSQLState* state ) {
 891         buffer_add_char( state->sql, '\n' );
 892
 893         // Add indentation
 894         static const char blanks[] = "                                ";   // 32 blanks
 895         static const size_t maxlen = sizeof( blanks ) - 1;
 896         const int blanks_per_level = 3;
 897         int n = state->indent * blanks_per_level;
 898         while( n > 0 ) {
 899                 size_t len = n >= maxlen ? maxlen : n;
 900                 buffer_add_n( state->sql, blanks, len );
 901                 n -= len;
 902         }
 903 }
 904
 905 static inline void incr_indent( BuildSQLState* state ) {
 906         ++state->indent;
 907 }
 908
 909 static inline void decr_indent( BuildSQLState* state ) {
 910         if( state->indent )
 911                 --state->indent;
 912 }