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