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