Open-ILS/src/c-apps/buildSQL.c

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