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                         sqlAddMsg( state, "BETWEEN expressions not yet supported" );
 581                         state->error = 1;
 582                         break;
 583                 case EXP_BIND :
 584                         if( !expr->bind ) {     // Sanity check
 585                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 586                                         "Internal error: no variable for bind variable expression" ));
 587                                 state->error = 1;
 588                         } else
 589                                 buildBindVar( state, expr->bind );
 590                         break;
 591                 case EXP_BOOL :
 592                         if( expr->negate )
 593                                 buffer_add( state->sql, "NOT " );
 594
 595                         if( expr->literal ) {
 596                                 buffer_add( state->sql, expr->literal );
 597                                 buffer_add_char( state->sql, ' ' );
 598                         } else
 599                                 buffer_add( state->sql, "FALSE " );
 600                         break;
 601                 case EXP_CASE :
 602                         if( expr->negate )
 603                                 buffer_add( state->sql, "NOT " );
 604
 605                         sqlAddMsg( state, "CASE expressions not yet supported" );
 606                         state->error = 1;
 607                         break;
 608                 case EXP_CAST :                   // Type cast
 609                         if( expr->negate )
 610                                 buffer_add( state->sql, "NOT " );
 611
 612                         sqlAddMsg( state, "Cast expressions not yet supported" );
 613                         state->error = 1;
 614                         break;
 615                 case EXP_COLUMN :                 // Table column
 616                         if( expr->negate )
 617                                 buffer_add( state->sql, "NOT " );
 618
 619                         if( expr->table_alias ) {
 620                                 buffer_add_char( state->sql, '\"' );
 621                                 buffer_add( state->sql, expr->table_alias );
 622                                 buffer_add( state->sql, "\"." );
 623                         }
 624                         if( expr->column_name ) {
 625                                 buffer_add( state->sql, expr->column_name );
 626                         } else {
 627                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 628                                         "Column name not present in expression # %d", expr->id ));
 629                                 state->error = 1;
 630                         }
 631                         break;
 632                 case EXP_EXIST :
 633                         if( !expr->subquery ) {
 634                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 635                                         "No subquery found for EXIST expression # %d", expr->id ));
 636                                 state->error = 1;
 637                         } else {
 638                                 if( expr->negate )
 639                                         buffer_add( state->sql, "NOT " );
 640
 641                                 buffer_add( state->sql, "EXISTS (" );
 642                                 incr_indent( state );
 643                                 build_Query( state, expr->subquery );
 644                                 decr_indent( state );
 645                                 add_newline( state );
 646                                 buffer_add_char( state->sql, ')' );
 647                         }
 648                         break;
 649                 case EXP_FIELD :
 650                         if( expr->negate )
 651                                 buffer_add( state->sql, "NOT " );
 652
 653                         sqlAddMsg( state, "Field expressions not yet supported" );
 654                         state->error = 1;
 655                         break;
 656                 case EXP_FUNCTION :
 657                         buildFunction( state, expr );
 658                         break;
 659                 case EXP_IN :
 660                         if( expr->left_operand ) {
 661                                 buildExpression( state, expr->left_operand );
 662                                 if( !state->error ) {
 663                                         if( expr->negate )
 664                                                 buffer_add( state->sql, "NOT " );
 665                                         buffer_add( state->sql, " IN (" );
 666
 667                                         if( expr->subquery ) {
 668                                                 incr_indent( state );
 669                                                 build_Query( state, expr->subquery );
 670                                                 if( state->error )
 671                                                         sqlAddMsg( state, "Unable to build subquery for IN condition" );
 672                                                 else {
 673                                                         decr_indent( state );
 674                                                         add_newline( state );
 675                                                         buffer_add_char( state->sql, ')' );
 676                                                 }
 677                                         } else {
 678                                                 buildSeries( state, expr->subexp_list, NULL );
 679                                                 if( state->error )
 680                                                         sqlAddMsg( state, "Unable to build IN list" );
 681                                                 else
 682                                                         buffer_add_char( state->sql, ')' );
 683                                         }
 684                                 }
 685                         }
 686                         break;
 687                 case EXP_ISNULL :
 688                         if( expr->left_operand ) {
 689                                 buildExpression( state, expr->left_operand );
 690                                 if( state->error ) {
 691                                         sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
 692                                                 expr->id );
 693                                         break;
 694                                 }
 695                         }
 696
 697                         if( expr->negate )
 698                                 buffer_add( state->sql, " IS NOT NULL" );
 699                         else
 700                                 buffer_add( state->sql, " IS NULL" );
 701                         break;
 702                 case EXP_NULL :
 703                         if( expr->negate )
 704                                 buffer_add( state->sql, "NOT " );
 705
 706                         buffer_add( state->sql, "NULL" );
 707                         break;
 708                 case EXP_NUMBER :                    // Numeric literal
 709                         if( !expr->literal ) {
 710                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 711                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 712                                 state->error = 1;
 713                         } else {
 714                                 buffer_add( state->sql, expr->literal );
 715                         }
 716                         break;
 717                 case EXP_OPERATOR :
 718                         if( expr->negate )
 719                                 buffer_add( state->sql, "NOT (" );
 720
 721                         if( expr->left_operand ) {
 722                                 buildExpression( state, expr->left_operand );
 723                                 if( state->error ) {
 724                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 725                                                 expr->id );
 726                                         break;
 727                                 }
 728                         }
 729                         buffer_add_char( state->sql, ' ' );
 730                         buffer_add( state->sql, expr->op );
 731                         buffer_add_char( state->sql, ' ' );
 732                         if( expr->right_operand ) {
 733                                 buildExpression( state, expr->right_operand );
 734                                 if( state->error ) {
 735                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 736                                                            expr->id );
 737                                         break;
 738                                 }
 739                         }
 740
 741                         if( expr->negate )
 742                                 buffer_add_char( state->sql, ')' );
 743
 744                         break;
 745                 case EXP_SERIES :
 746                         if( expr->negate )
 747                                 buffer_add( state->sql, "NOT (" );
 748
 749                         buildSeries( state, expr->subexp_list, expr->op );
 750                         if( state->error ) {
 751                                 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
 752                                         expr->op ? expr->op : "," );
 753                         }
 754                         if( expr->negate )
 755                                 buffer_add_char( state->sql, ')' );
 756
 757                         break;
 758                 case EXP_STRING :                     // String literal
 759                         if( !expr->literal ) {
 760                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 761                                         "Internal error: No string value in string expression # %d", expr->id ));
 762                                         state->error = 1;
 763                         } else {
 764                                 // To do: escape special characters in the string
 765                                 buffer_add_char( state->sql, '\'' );
 766                                 buffer_add( state->sql, expr->literal );
 767                                 buffer_add_char( state->sql, '\'' );
 768                         }
 769                         break;
 770                 case EXP_SUBQUERY :
 771                         if( expr->negate )
 772                                 buffer_add( state->sql, "NOT " );
 773
 774                         if( expr->subquery ) {
 775                                 buffer_add_char( state->sql, '(' );
 776                                 incr_indent( state );
 777                                 build_Query( state, expr->subquery );
 778                                 decr_indent( state );
 779                                 add_newline( state );
 780                                 buffer_add_char( state->sql, ')' );
 781                         } else {
 782                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 783                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 784                                 state->error = 1;
 785                         }
 786                         break;
 787         }
 788
 789         if( expr->parenthesize )
 790                 buffer_add_char( state->sql, ')' );
 791 }
 792
 793 /**
 794         @brief Build a function call.
 795         @param state Pointer to the query-building context.
 796         @param exp Pointer to an Expression representing a function call.
 797
 798         This function does not currently accommodate certain functions with idiosyncratic
 799         syntax, such as the absence of parentheses, or the use of certain keywords in
 800         in the parameter list.
 801 */
 802 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
 803         if( expr->negate )
 804                 buffer_add( state->sql, "NOT " );
 805
 806         // We rely on the input side to ensure that the function name is available
 807         buffer_add( state->sql, expr->function_name );
 808         buffer_add_char( state->sql, '(' );
 809
 810         // Add the parameters, if any
 811         buildSeries( state, expr->subexp_list, NULL );
 812
 813         buffer_add_char( state->sql, ')' );
 814 }
 815
 816 /**
 817         @brief Build a series of expressions separated by a specified operator, or by commas.
 818         @param state Pointer to the query-building context.
 819         @param subexp_list Pointer to the first Expression in a linked list.
 820         @param op Pointer to the operator, or NULL for commas.
 821
 822         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
 823         subsequent operators will begin on a new line.
 824 */
 825 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
 826
 827         if( !subexp_list)
 828                 return;                // List is empty
 829
 830         int comma = 0;             // Boolean; true if separator is a comma
 831         int newline_needed = 0;    // Boolean; true if operator is AND or OR
 832
 833         if( !op ) {
 834                 op = ",";
 835                 comma = 1;
 836         } else if( !strcmp( op, "," ))
 837                 comma = 1;
 838         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
 839                 newline_needed = 1;
 840
 841         int first = 1;               // Boolean; true for first item in list
 842         while( subexp_list ) {
 843                 if( first )
 844                         first = 0;   // No separator needed yet
 845                 else {
 846                         // Insert a separator
 847                         if( comma )
 848                                 buffer_add( state->sql, ", " );
 849                         else {
 850                                 if( newline_needed )
 851                                         add_newline( state );
 852                                 else
 853                                         buffer_add_char( state->sql, ' ' );
 854
 855                                 buffer_add( state->sql, op );
 856                                 buffer_add_char( state->sql, ' ' );
 857                         }
 858                 }
 859
 860                 buildExpression( state, subexp_list );
 861                 subexp_list = subexp_list->next;
 862         }
 863 }
 864
 865 /**
 866         @brief Add the value of a bind variable to an SQL statement.
 867         @param state Pointer to the query-building context.
 868         @param bind Pointer to the bind variable whose value is to be added to the SQL.
 869
 870         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
 871         the type of the bind variable.
 872 */
 873 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
 874
 875         // Decide where to get the value, if any
 876         const jsonObject* value = NULL;
 877         if( bind->actual_value )
 878                 value = bind->actual_value;
 879         else if( bind->default_value ) {
 880                 if( state->defaults_usable )
 881                         value = bind->default_value;
 882                 else {
 883                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
 884                                 bind->name );
 885                         state->error = 1;
 886                         return;
 887                 }
 888         } else if( state->values_required ) {
 889                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
 890                 state->error = 1;
 891                 return;
 892         } else {
 893                 // No value available, and that's okay.  Emit the name of the bind variable.
 894                 buffer_add_char( state->sql, ':' );
 895                 buffer_add( state->sql, bind->name );
 896                 return;
 897         }
 898
 899         // If we get to this point, we know that a value is available.  Carry on.
 900
 901         int numeric = 0;       // Boolean
 902         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
 903                 numeric = 1;
 904
 905         // Emit the value
 906         switch( bind->type ) {
 907                 case BIND_STR :
 908                 case BIND_NUM :
 909                         buildScalar( state, numeric, value );
 910                         break;
 911                 case BIND_STR_LIST :
 912                 case BIND_NUM_LIST :
 913                         if( JSON_ARRAY == value->type ) {
 914                                 // Iterate over array, emit each value
 915                                 int first = 1;   // Boolean
 916                                 unsigned long max = value->size;
 917                                 unsigned long i = 0;
 918                                 while( i < max ) {
 919                                         if( first )
 920                                                 first = 0;
 921                                         else
 922                                                 buffer_add( state->sql, ", " );
 923
 924                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
 925                                         ++i;
 926                                 }
 927                         } else {
 928                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 929                                         "Invalid value for bind variable; expected a list of values" ));
 930                                 state->error = 1;
 931                         }
 932                         break;
 933                 default :
 934                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 935                                 "Internal error: invalid type for bind variable" ));
 936                         state->error = 1;
 937                         break;
 938         }
 939
 940         if( state->error )
 941                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 942                         "Unable to emit value of bind variable \"%s\"", bind->name ));
 943 }
 944
 945 /**
 946         @brief Add a number or quoted string to an SQL statement.
 947         @param state Pointer to the query-building context.
 948         @param numeric Boolean; true if the value is expected to be a number
 949         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
 950 */
 951 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
 952         switch( obj->type ) {
 953                 case JSON_HASH :
 954                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 955                                 "Internal error: hash value for bind variable" ));
 956                         state->error = 1;
 957                         break;
 958                 case JSON_ARRAY :
 959                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 960                                 "Internal error: array value for bind variable" ));
 961                         state->error = 1;
 962                         break;
 963                 case JSON_STRING :
 964                         if( numeric ) {
 965                                 sqlAddMsg( state,
 966                                         "Invalid value for bind variable: expected a string, found a number" );
 967                                 state->error = 1;
 968                         } else {
 969                                 // To do: escape special characters in the string
 970                                 buffer_add_char( state->sql, '\'' );
 971                                 buffer_add( state->sql, jsonObjectGetString( obj ));
 972                                 buffer_add_char( state->sql, '\'' );
 973                         }
 974                         break;
 975                 case JSON_NUMBER :
 976                         if( numeric ) {
 977                                 buffer_add( state->sql, jsonObjectGetString( obj ));
 978                         } else {
 979                                 sqlAddMsg( state,
 980                                         "Invalid value for bind variable: expected a number, found a string" );
 981                                 state->error = 1;
 982                         }
 983                         break;
 984                 case JSON_NULL :
 985                         buffer_add( state->sql, "NULL" );
 986                         break;
 987                 case JSON_BOOL :
 988                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 989                                 "Internal error: boolean value for bind variable" ));
 990                         state->error = 1;
 991                         break;
 992                 default :
 993                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 994                                 "Internal error: corrupted value for bind variable" ));
 995                         state->error = 1;
 996                         break;
 997         }
 998 }
 999
1000 /**
1001         @brief Start a new line in the output, with the current level of indentation.
1002         @param state Pointer to the query-building context.
1003 */
1004 static void add_newline( BuildSQLState* state ) {
1005         buffer_add_char( state->sql, '\n' );
1006
1007         // Add indentation
1008         static const char blanks[] = "                                ";   // 32 blanks
1009         static const size_t maxlen = sizeof( blanks ) - 1;
1010         const int blanks_per_level = 3;
1011         int n = state->indent * blanks_per_level;
1012         while( n > 0 ) {
1013                 size_t len = n >= maxlen ? maxlen : n;
1014                 buffer_add_n( state->sql, blanks, len );
1015                 n -= len;
1016         }
1017 }
1018
1019 /**
1020         @brief Increase the degree of indentation.
1021         @param state Pointer to the query-building context.
1022 */
1023 static inline void incr_indent( BuildSQLState* state ) {
1024         ++state->indent;
1025 }
1026
1027 /**
1028         @brief Reduce the degree of indentation.
1029         @param state Pointer to the query-building context.
1030 */
1031 static inline void decr_indent( BuildSQLState* state ) {
1032         if( state->indent )
1033                 --state->indent;
1034 }