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                         buildFunction( state, core_from->function_call );
 376                         if ( state->error ) {
 377                                 sqlAddMsg( state,
 378                                         "Unable to include function call # %d in FROM relation # %d",
 379                                         core_from->function_call->id, core_from->id );
 380                                 return;
 381                         }
 382                         break;
 383                 default :
 384                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 385                                 "Internal error: Invalid type # %d in FROM relation # %d",
 386                                 core_from->type, core_from->id ));
 387                         state->error = 1;
 388                         return;
 389         }
 390
 391         // Add a table alias, if possible
 392         if( core_from->table_alias ) {
 393                 buffer_add( state->sql, " AS \"" );
 394                 buffer_add( state->sql, core_from->table_alias );
 395                 buffer_add( state->sql, "\" " );
 396         }
 397         else if( core_from->class_name ) {
 398                 buffer_add( state->sql, " AS \"" );
 399                 buffer_add( state->sql, core_from->class_name );
 400                 buffer_add( state->sql, "\" " );
 401         } else
 402                 buffer_add_char( state->sql, ' ' );
 403
 404         incr_indent( state );
 405         FromRelation* join = core_from->join_list;
 406         while( join ) {
 407                 buildJoin( state, join );
 408                 if( state->error ) {
 409                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
 410                                 core_from->id );
 411                         break;
 412                 } else
 413                         join = join->next;
 414         }
 415         decr_indent( state );
 416         decr_indent( state );
 417 }
 418
 419 /**
 420         @brief Add a JOIN clause.
 421         @param state Pointer to the query-building context.
 422         @param join Pointer to the FromRelation representing the JOIN to be added.
 423 */
 424 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
 425         add_newline( state );
 426         switch( join->join_type ) {
 427                 case JT_NONE :
 428                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
 429                         state->error = 1;
 430                         return;
 431                 case JT_INNER :
 432                         buffer_add( state->sql, "INNER JOIN " );
 433                         break;
 434                 case JT_LEFT:
 435                         buffer_add( state->sql, "LEFT JOIN " );
 436                         break;
 437                 case JT_RIGHT:
 438                         buffer_add( state->sql, "RIGHT JOIN " );
 439                         break;
 440                 case JT_FULL:
 441                         buffer_add( state->sql, "FULL JOIN " );
 442                         break;
 443                 default :
 444                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
 445                         state->error = 1;
 446                         return;
 447         }
 448
 449         switch( join->type ) {
 450                 case FRT_RELATION :
 451                         // Sanity check
 452                         if( !join->table_name || ! *join->table_name ) {
 453                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
 454                                 state->error = 1;
 455                                 return;
 456                         }
 457                         buffer_add( state->sql, join->table_name );
 458                         break;
 459                 case FRT_SUBQUERY :
 460                         // Sanity check
 461                         if( !join->subquery ) {
 462                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
 463                                 state->error = 1;
 464                                 return;
 465                         } else if( !join->table_alias ) {
 466                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
 467                                         join->id );
 468                                 state->error = 1;
 469                                 return;
 470                         }
 471                         buffer_add_char( state->sql, '(' );
 472                         incr_indent( state );
 473                         build_Query( state, join->subquery );
 474                         decr_indent( state );
 475                         add_newline( state );
 476                         buffer_add_char( state->sql, ')' );
 477                         break;
 478                 case FRT_FUNCTION :
 479                         if( !join->table_name || ! *join->table_name ) {
 480                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
 481                                         join->id );
 482                                 state->error = 1;
 483                                 return;
 484                         }
 485                         break;
 486         }
 487
 488         const char* effective_alias = join->table_alias;
 489         if( !effective_alias )
 490                 effective_alias = join->class_name;
 491
 492         if( effective_alias ) {
 493                 buffer_add( state->sql, " AS \"" );
 494                 buffer_add( state->sql, effective_alias );
 495                 buffer_add_char( state->sql, '\"' );
 496         }
 497
 498         if( join->on_clause ) {
 499                 incr_indent( state );
 500                 add_newline( state );
 501                 buffer_add( state->sql, "ON " );
 502                 buildExpression( state, join->on_clause );
 503                 decr_indent( state );
 504         }
 505
 506         FromRelation* subjoin = join->join_list;
 507         while( subjoin ) {
 508                 buildJoin( state, subjoin );
 509                 if( state->error ) {
 510                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
 511                         break;
 512                 } else
 513                         subjoin = subjoin->next;
 514         }
 515 }
 516
 517 /**
 518         @brief Build a SELECT list.
 519         @param state Pointer to the query-building context.
 520         @param item Pointer to the first in a linked list of SELECT items.
 521 */
 522 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
 523
 524         int first = 1;
 525         while( item ) {
 526                 if( !first )
 527                         buffer_add_char( state->sql, ',' );
 528                 add_newline( state );
 529                 buildExpression( state, item->expression );
 530                 if( state->error ) {
 531                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
 532                         state->error = 1;
 533                         break;
 534                 }
 535
 536                 if( item->column_alias ) {
 537                         buffer_add( state->sql, " AS \"" );
 538                         buffer_add( state->sql, item->column_alias );
 539                         buffer_add_char( state->sql, '\"' );
 540                 }
 541                 first = 0;
 542                 item = item->next;
 543         };
 544         buffer_add_char( state->sql, ' ' );
 545 }
 546
 547 /**
 548         @brief Add a GROUP BY clause, if there is one, to the current query.
 549         @param state Pointer to the query-building context.
 550         @param sel_list Pointer to the first node in a linked list of SelectItems
 551
 552         We reference the GROUP BY items by number, not by repeating the expressions.
 553 */
 554 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
 555         int seq = 0;       // Sequence number of current SelectItem
 556         int first = 1;     // Boolean: true for the first GROUPed BY item
 557         while( sel_list ) {
 558                 ++seq;
 559
 560                 if( sel_list->grouped_by ) {
 561                         if( first ) {
 562                                 add_newline( state );
 563                                 buffer_add( state->sql, "GROUP BY " );
 564                                 first = 0;
 565                         }
 566                         else
 567                                 buffer_add( state->sql, ", " );
 568
 569                         buffer_fadd( state->sql, "%d", seq );
 570                 }
 571
 572                 sel_list = sel_list->next;
 573         }
 574 }
 575
 576 /**
 577         @brief Add an ORDER BY clause to the current query.
 578         @param state Pointer to the query-building context.
 579         @param ord_list Pointer to the first node in a linked list of OrderItems.
 580 */
 581 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
 582         add_newline( state );
 583         buffer_add( state->sql, "ORDER BY" );
 584         incr_indent( state );
 585
 586         int first = 1;    // boolean
 587         while( ord_list ) {
 588                 if( first )
 589                         first = 0;
 590                 else
 591                         buffer_add_char( state->sql, ',' );
 592                 add_newline( state );
 593                 buildExpression( state, ord_list->expression );
 594                 if( state->error ) {
 595                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
 596                         return;
 597                 }
 598
 599                 ord_list = ord_list->next;
 600         }
 601
 602         decr_indent( state );
 603         return;
 604 }
 605
 606 /**
 607         @brief Build an arbitrary expression.
 608         @param state Pointer to the query-building context.
 609         @param expr Pointer to the Expression representing the expression to be built.
 610 */
 611 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
 612         if( !expr ) {
 613                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 614                         "Internal error: NULL pointer to Expression" ));
 615                 state->error = 1;
 616                 return;
 617         }
 618
 619         if( expr->parenthesize )
 620                 buffer_add_char( state->sql, '(' );
 621
 622         switch( expr->type ) {
 623                 case EXP_BETWEEN :
 624                         if( expr->negate )
 625                                 buffer_add( state->sql, "NOT " );
 626
 627                         buildExpression( state, expr->left_operand );
 628                         if( state->error ) {
 629                                 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
 630                                         expr->id );
 631                                 break;
 632                         }
 633
 634                         buffer_add( state->sql, " BETWEEN " );
 635
 636                         buildExpression( state, expr->subexp_list );
 637                         if( state->error ) {
 638                                 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
 639                                         expr->id );
 640                                 break;
 641                         }
 642
 643                         buffer_add( state->sql, " AND " );
 644
 645                         buildExpression( state, expr->subexp_list->next );
 646                         if( state->error ) {
 647                                 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
 648                                         expr->id );
 649                                 break;
 650                         }
 651
 652                         break;
 653                 case EXP_BIND :
 654                         if( !expr->bind ) {     // Sanity check
 655                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 656                                         "Internal error: no variable for bind variable expression" ));
 657                                 state->error = 1;
 658                         } else
 659                                 buildBindVar( state, expr->bind );
 660                         break;
 661                 case EXP_BOOL :
 662                         if( expr->negate )
 663                                 buffer_add( state->sql, "NOT " );
 664
 665                         if( expr->literal ) {
 666                                 buffer_add( state->sql, expr->literal );
 667                                 buffer_add_char( state->sql, ' ' );
 668                         } else
 669                                 buffer_add( state->sql, "FALSE " );
 670                         break;
 671                 case EXP_CASE :
 672                         if( expr->negate )
 673                                 buffer_add( state->sql, "NOT " );
 674
 675                         sqlAddMsg( state, "CASE expressions not yet supported" );
 676                         state->error = 1;
 677                         break;
 678                 case EXP_CAST :                   // Type cast
 679                         if( expr->negate )
 680                                 buffer_add( state->sql, "NOT " );
 681
 682                         sqlAddMsg( state, "Cast expressions not yet supported" );
 683                         state->error = 1;
 684                         break;
 685                 case EXP_COLUMN :                 // Table column
 686                         if( expr->negate )
 687                                 buffer_add( state->sql, "NOT " );
 688
 689                         if( expr->table_alias ) {
 690                                 buffer_add_char( state->sql, '\"' );
 691                                 buffer_add( state->sql, expr->table_alias );
 692                                 buffer_add( state->sql, "\"." );
 693                         }
 694                         if( expr->column_name ) {
 695                                 buffer_add( state->sql, expr->column_name );
 696                         } else {
 697                                 //osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 698                                 //      "Column name not present in expression # %d", expr->id ));
 699                                 //state->error = 1;
 700                                 buffer_add_char( state->sql, '*' );
 701                         }
 702                         break;
 703                 case EXP_EXIST :
 704                         if( !expr->subquery ) {
 705                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 706                                         "No subquery found for EXIST expression # %d", expr->id ));
 707                                 state->error = 1;
 708                         } else {
 709                                 if( expr->negate )
 710                                         buffer_add( state->sql, "NOT " );
 711
 712                                 buffer_add( state->sql, "EXISTS (" );
 713                                 incr_indent( state );
 714                                 build_Query( state, expr->subquery );
 715                                 decr_indent( state );
 716                                 add_newline( state );
 717                                 buffer_add_char( state->sql, ')' );
 718                         }
 719                         break;
 720                 case EXP_FIELD :
 721                         if( expr->negate )
 722                                 buffer_add( state->sql, "NOT " );
 723
 724                         sqlAddMsg( state, "Field expressions not yet supported" );
 725                         state->error = 1;
 726                         break;
 727                 case EXP_FUNCTION :
 728                         buildFunction( state, expr );
 729                         break;
 730                 case EXP_IN :
 731                         if( expr->left_operand ) {
 732                                 buildExpression( state, expr->left_operand );
 733                                 if( !state->error ) {
 734                                         if( expr->negate )
 735                                                 buffer_add( state->sql, "NOT " );
 736                                         buffer_add( state->sql, " IN (" );
 737
 738                                         if( expr->subquery ) {
 739                                                 incr_indent( state );
 740                                                 build_Query( state, expr->subquery );
 741                                                 if( state->error )
 742                                                         sqlAddMsg( state, "Unable to build subquery for IN condition" );
 743                                                 else {
 744                                                         decr_indent( state );
 745                                                         add_newline( state );
 746                                                         buffer_add_char( state->sql, ')' );
 747                                                 }
 748                                         } else {
 749                                                 buildSeries( state, expr->subexp_list, NULL );
 750                                                 if( state->error )
 751                                                         sqlAddMsg( state, "Unable to build IN list" );
 752                                                 else
 753                                                         buffer_add_char( state->sql, ')' );
 754                                         }
 755                                 }
 756                         }
 757                         break;
 758                 case EXP_ISNULL :
 759                         if( expr->left_operand ) {
 760                                 buildExpression( state, expr->left_operand );
 761                                 if( state->error ) {
 762                                         sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
 763                                                 expr->id );
 764                                         break;
 765                                 }
 766                         }
 767
 768                         if( expr->negate )
 769                                 buffer_add( state->sql, " IS NOT NULL" );
 770                         else
 771                                 buffer_add( state->sql, " IS NULL" );
 772                         break;
 773                 case EXP_NULL :
 774                         if( expr->negate )
 775                                 buffer_add( state->sql, "NOT " );
 776
 777                         buffer_add( state->sql, "NULL" );
 778                         break;
 779                 case EXP_NUMBER :                    // Numeric literal
 780                         if( !expr->literal ) {
 781                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 782                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 783                                 state->error = 1;
 784                         } else {
 785                                 buffer_add( state->sql, expr->literal );
 786                         }
 787                         break;
 788                 case EXP_OPERATOR :
 789                         if( expr->negate )
 790                                 buffer_add( state->sql, "NOT (" );
 791
 792                         if( expr->left_operand ) {
 793                                 buildExpression( state, expr->left_operand );
 794                                 if( state->error ) {
 795                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 796                                                 expr->id );
 797                                         break;
 798                                 }
 799                         }
 800                         buffer_add_char( state->sql, ' ' );
 801                         buffer_add( state->sql, expr->op );
 802                         buffer_add_char( state->sql, ' ' );
 803                         if( expr->right_operand ) {
 804                                 buildExpression( state, expr->right_operand );
 805                                 if( state->error ) {
 806                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 807                                                            expr->id );
 808                                         break;
 809                                 }
 810                         }
 811
 812                         if( expr->negate )
 813                                 buffer_add_char( state->sql, ')' );
 814
 815                         break;
 816                 case EXP_SERIES :
 817                         if( expr->negate )
 818                                 buffer_add( state->sql, "NOT (" );
 819
 820                         buildSeries( state, expr->subexp_list, expr->op );
 821                         if( state->error ) {
 822                                 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
 823                                         expr->op ? expr->op : "," );
 824                         }
 825                         if( expr->negate )
 826                                 buffer_add_char( state->sql, ')' );
 827
 828                         break;
 829                 case EXP_STRING :                     // String literal
 830                         if( !expr->literal ) {
 831                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 832                                         "Internal error: No string value in string expression # %d", expr->id ));
 833                                         state->error = 1;
 834                         } else {
 835                                 // To do: escape special characters in the string
 836                                 buffer_add_char( state->sql, '\'' );
 837                                 buffer_add( state->sql, expr->literal );
 838                                 buffer_add_char( state->sql, '\'' );
 839                         }
 840                         break;
 841                 case EXP_SUBQUERY :
 842                         if( expr->negate )
 843                                 buffer_add( state->sql, "NOT " );
 844
 845                         if( expr->subquery ) {
 846                                 buffer_add_char( state->sql, '(' );
 847                                 incr_indent( state );
 848                                 build_Query( state, expr->subquery );
 849                                 decr_indent( state );
 850                                 add_newline( state );
 851                                 buffer_add_char( state->sql, ')' );
 852                         } else {
 853                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 854                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 855                                 state->error = 1;
 856                         }
 857                         break;
 858         }
 859
 860         if( expr->parenthesize )
 861                 buffer_add_char( state->sql, ')' );
 862 }
 863
 864 /**
 865         @brief Build a function call.
 866         @param state Pointer to the query-building context.
 867         @param exp Pointer to an Expression representing a function call.
 868
 869         This function does not currently accommodate certain functions with idiosyncratic
 870         syntax, such as the absence of parentheses, or the use of certain keywords in
 871         in the parameter list.
 872 */
 873 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
 874         if( expr->negate )
 875                 buffer_add( state->sql, "NOT " );
 876
 877         // We rely on the input side to ensure that the function name is available
 878         buffer_add( state->sql, expr->function_name );
 879         buffer_add_char( state->sql, '(' );
 880
 881         // Add the parameters, if any
 882         buildSeries( state, expr->subexp_list, NULL );
 883
 884         buffer_add_char( state->sql, ')' );
 885 }
 886
 887 /**
 888         @brief Build a series of expressions separated by a specified operator, or by commas.
 889         @param state Pointer to the query-building context.
 890         @param subexp_list Pointer to the first Expression in a linked list.
 891         @param op Pointer to the operator, or NULL for commas.
 892
 893         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
 894         subsequent operators will begin on a new line.
 895 */
 896 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
 897
 898         if( !subexp_list)
 899                 return;                // List is empty
 900
 901         int comma = 0;             // Boolean; true if separator is a comma
 902         int newline_needed = 0;    // Boolean; true if operator is AND or OR
 903
 904         if( !op ) {
 905                 op = ",";
 906                 comma = 1;
 907         } else if( !strcmp( op, "," ))
 908                 comma = 1;
 909         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
 910                 newline_needed = 1;
 911
 912         int first = 1;               // Boolean; true for first item in list
 913         while( subexp_list ) {
 914                 if( first )
 915                         first = 0;   // No separator needed yet
 916                 else {
 917                         // Insert a separator
 918                         if( comma )
 919                                 buffer_add( state->sql, ", " );
 920                         else {
 921                                 if( newline_needed )
 922                                         add_newline( state );
 923                                 else
 924                                         buffer_add_char( state->sql, ' ' );
 925
 926                                 buffer_add( state->sql, op );
 927                                 buffer_add_char( state->sql, ' ' );
 928                         }
 929                 }
 930
 931                 buildExpression( state, subexp_list );
 932                 subexp_list = subexp_list->next;
 933         }
 934 }
 935
 936 /**
 937         @brief Add the value of a bind variable to an SQL statement.
 938         @param state Pointer to the query-building context.
 939         @param bind Pointer to the bind variable whose value is to be added to the SQL.
 940
 941         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
 942         the type of the bind variable.
 943 */
 944 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
 945
 946         // Decide where to get the value, if any
 947         const jsonObject* value = NULL;
 948         if( bind->actual_value )
 949                 value = bind->actual_value;
 950         else if( bind->default_value ) {
 951                 if( state->defaults_usable )
 952                         value = bind->default_value;
 953                 else {
 954                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
 955                                 bind->name );
 956                         state->error = 1;
 957                         return;
 958                 }
 959         } else if( state->values_required ) {
 960                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
 961                 state->error = 1;
 962                 return;
 963         } else {
 964                 // No value available, and that's okay.  Emit the name of the bind variable.
 965                 buffer_add_char( state->sql, ':' );
 966                 buffer_add( state->sql, bind->name );
 967                 return;
 968         }
 969
 970         // If we get to this point, we know that a value is available.  Carry on.
 971
 972         int numeric = 0;       // Boolean
 973         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
 974                 numeric = 1;
 975
 976         // Emit the value
 977         switch( bind->type ) {
 978                 case BIND_STR :
 979                 case BIND_NUM :
 980                         buildScalar( state, numeric, value );
 981                         break;
 982                 case BIND_STR_LIST :
 983                 case BIND_NUM_LIST :
 984                         if( JSON_ARRAY == value->type ) {
 985                                 // Iterate over array, emit each value
 986                                 int first = 1;   // Boolean
 987                                 unsigned long max = value->size;
 988                                 unsigned long i = 0;
 989                                 while( i < max ) {
 990                                         if( first )
 991                                                 first = 0;
 992                                         else
 993                                                 buffer_add( state->sql, ", " );
 994
 995                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
 996                                         ++i;
 997                                 }
 998                         } else {
 999                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1000                                         "Invalid value for bind variable; expected a list of values" ));
1001                                 state->error = 1;
1002                         }
1003                         break;
1004                 default :
1005                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1006                                 "Internal error: invalid type for bind variable" ));
1007                         state->error = 1;
1008                         break;
1009         }
1010
1011         if( state->error )
1012                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1013                         "Unable to emit value of bind variable \"%s\"", bind->name ));
1014 }
1015
1016 /**
1017         @brief Add a number or quoted string to an SQL statement.
1018         @param state Pointer to the query-building context.
1019         @param numeric Boolean; true if the value is expected to be a number
1020         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1021 */
1022 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1023         switch( obj->type ) {
1024                 case JSON_HASH :
1025                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1026                                 "Internal error: hash value for bind variable" ));
1027                         state->error = 1;
1028                         break;
1029                 case JSON_ARRAY :
1030                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1031                                 "Internal error: array value for bind variable" ));
1032                         state->error = 1;
1033                         break;
1034                 case JSON_STRING :
1035                         if( numeric ) {
1036                                 sqlAddMsg( state,
1037                                         "Invalid value for bind variable: expected a string, found a number" );
1038                                 state->error = 1;
1039                         } else {
1040                                 // To do: escape special characters in the string
1041                                 buffer_add_char( state->sql, '\'' );
1042                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1043                                 buffer_add_char( state->sql, '\'' );
1044                         }
1045                         break;
1046                 case JSON_NUMBER :
1047                         if( numeric ) {
1048                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1049                         } else {
1050                                 sqlAddMsg( state,
1051                                         "Invalid value for bind variable: expected a number, found a string" );
1052                                 state->error = 1;
1053                         }
1054                         break;
1055                 case JSON_NULL :
1056                         buffer_add( state->sql, "NULL" );
1057                         break;
1058                 case JSON_BOOL :
1059                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1060                                 "Internal error: boolean value for bind variable" ));
1061                         state->error = 1;
1062                         break;
1063                 default :
1064                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1065                                 "Internal error: corrupted value for bind variable" ));
1066                         state->error = 1;
1067                         break;
1068         }
1069 }
1070
1071 /**
1072         @brief Start a new line in the output, with the current level of indentation.
1073         @param state Pointer to the query-building context.
1074 */
1075 static void add_newline( BuildSQLState* state ) {
1076         buffer_add_char( state->sql, '\n' );
1077
1078         // Add indentation
1079         static const char blanks[] = "                                ";   // 32 blanks
1080         static const size_t maxlen = sizeof( blanks ) - 1;
1081         const int blanks_per_level = 3;
1082         int n = state->indent * blanks_per_level;
1083         while( n > 0 ) {
1084                 size_t len = n >= maxlen ? maxlen : n;
1085                 buffer_add_n( state->sql, blanks, len );
1086                 n -= len;
1087         }
1088 }
1089
1090 /**
1091         @brief Increase the degree of indentation.
1092         @param state Pointer to the query-building context.
1093 */
1094 static inline void incr_indent( BuildSQLState* state ) {
1095         ++state->indent;
1096 }
1097
1098 /**
1099         @brief Reduce the degree of indentation.
1100         @param state Pointer to the query-building context.
1101 */
1102 static inline void decr_indent( BuildSQLState* state ) {
1103         if( state->indent )
1104                 --state->indent;
1105 }