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                                 char* str = strdup( expr->literal );
 836                                 dbi_conn_quote_string( state->dbhandle, &str );
 837                                 if( str ) {
 838                                         buffer_add( state->sql, str );
 839                                         free( str );
 840                                 } else {
 841                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 842                                                 "Unable to format string literal \"%s\" for expression # %d",
 843                                                         expr->literal, expr->id ));
 844                                         state->error = 1;
 845                                 }
 846                         }
 847                         break;
 848                 case EXP_SUBQUERY :
 849                         if( expr->negate )
 850                                 buffer_add( state->sql, "NOT " );
 851
 852                         if( expr->subquery ) {
 853                                 buffer_add_char( state->sql, '(' );
 854                                 incr_indent( state );
 855                                 build_Query( state, expr->subquery );
 856                                 decr_indent( state );
 857                                 add_newline( state );
 858                                 buffer_add_char( state->sql, ')' );
 859                         } else {
 860                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 861                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 862                                 state->error = 1;
 863                         }
 864                         break;
 865         }
 866
 867         if( expr->parenthesize )
 868                 buffer_add_char( state->sql, ')' );
 869 }
 870
 871 /**
 872         @brief Build a function call.
 873         @param state Pointer to the query-building context.
 874         @param exp Pointer to an Expression representing a function call.
 875
 876         This function does not currently accommodate certain functions with idiosyncratic
 877         syntax, such as the absence of parentheses, or the use of certain keywords in
 878         in the parameter list.
 879 */
 880 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
 881         if( expr->negate )
 882                 buffer_add( state->sql, "NOT " );
 883
 884         // We rely on the input side to ensure that the function name is available
 885         buffer_add( state->sql, expr->function_name );
 886         buffer_add_char( state->sql, '(' );
 887
 888         // Add the parameters, if any
 889         buildSeries( state, expr->subexp_list, NULL );
 890
 891         buffer_add_char( state->sql, ')' );
 892 }
 893
 894 /**
 895         @brief Build a series of expressions separated by a specified operator, or by commas.
 896         @param state Pointer to the query-building context.
 897         @param subexp_list Pointer to the first Expression in a linked list.
 898         @param op Pointer to the operator, or NULL for commas.
 899
 900         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
 901         subsequent operators will begin on a new line.
 902 */
 903 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
 904
 905         if( !subexp_list)
 906                 return;                // List is empty
 907
 908         int comma = 0;             // Boolean; true if separator is a comma
 909         int newline_needed = 0;    // Boolean; true if operator is AND or OR
 910
 911         if( !op ) {
 912                 op = ",";
 913                 comma = 1;
 914         } else if( !strcmp( op, "," ))
 915                 comma = 1;
 916         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
 917                 newline_needed = 1;
 918
 919         int first = 1;               // Boolean; true for first item in list
 920         while( subexp_list ) {
 921                 if( first )
 922                         first = 0;   // No separator needed yet
 923                 else {
 924                         // Insert a separator
 925                         if( comma )
 926                                 buffer_add( state->sql, ", " );
 927                         else {
 928                                 if( newline_needed )
 929                                         add_newline( state );
 930                                 else
 931                                         buffer_add_char( state->sql, ' ' );
 932
 933                                 buffer_add( state->sql, op );
 934                                 buffer_add_char( state->sql, ' ' );
 935                         }
 936                 }
 937
 938                 buildExpression( state, subexp_list );
 939                 subexp_list = subexp_list->next;
 940         }
 941 }
 942
 943 /**
 944         @brief Add the value of a bind variable to an SQL statement.
 945         @param state Pointer to the query-building context.
 946         @param bind Pointer to the bind variable whose value is to be added to the SQL.
 947
 948         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
 949         the type of the bind variable.
 950 */
 951 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
 952
 953         // Decide where to get the value, if any
 954         const jsonObject* value = NULL;
 955         if( bind->actual_value )
 956                 value = bind->actual_value;
 957         else if( bind->default_value ) {
 958                 if( state->defaults_usable )
 959                         value = bind->default_value;
 960                 else {
 961                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
 962                                 bind->name );
 963                         state->error = 1;
 964                         return;
 965                 }
 966         } else if( state->values_required ) {
 967                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
 968                 state->error = 1;
 969                 return;
 970         } else {
 971                 // No value available, and that's okay.  Emit the name of the bind variable.
 972                 buffer_add_char( state->sql, ':' );
 973                 buffer_add( state->sql, bind->name );
 974                 return;
 975         }
 976
 977         // If we get to this point, we know that a value is available.  Carry on.
 978
 979         int numeric = 0;       // Boolean
 980         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
 981                 numeric = 1;
 982
 983         // Emit the value
 984         switch( bind->type ) {
 985                 case BIND_STR :
 986                 case BIND_NUM :
 987                         buildScalar( state, numeric, value );
 988                         break;
 989                 case BIND_STR_LIST :
 990                 case BIND_NUM_LIST :
 991                         if( JSON_ARRAY == value->type ) {
 992                                 // Iterate over array, emit each value
 993                                 int first = 1;   // Boolean
 994                                 unsigned long max = value->size;
 995                                 unsigned long i = 0;
 996                                 while( i < max ) {
 997                                         if( first )
 998                                                 first = 0;
 999                                         else
1000                                                 buffer_add( state->sql, ", " );
1001
1002                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1003                                         ++i;
1004                                 }
1005                         } else {
1006                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1007                                         "Invalid value for bind variable; expected a list of values" ));
1008                                 state->error = 1;
1009                         }
1010                         break;
1011                 default :
1012                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1013                                 "Internal error: invalid type for bind variable" ));
1014                         state->error = 1;
1015                         break;
1016         }
1017
1018         if( state->error )
1019                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1020                         "Unable to emit value of bind variable \"%s\"", bind->name ));
1021 }
1022
1023 /**
1024         @brief Add a number or quoted string to an SQL statement.
1025         @param state Pointer to the query-building context.
1026         @param numeric Boolean; true if the value is expected to be a number
1027         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1028 */
1029 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1030         switch( obj->type ) {
1031                 case JSON_HASH :
1032                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1033                                 "Internal error: hash value for bind variable" ));
1034                         state->error = 1;
1035                         break;
1036                 case JSON_ARRAY :
1037                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1038                                 "Internal error: array value for bind variable" ));
1039                         state->error = 1;
1040                         break;
1041                 case JSON_STRING :
1042                         if( numeric ) {
1043                                 sqlAddMsg( state,
1044                                         "Invalid value for bind variable: expected a string, found a number" );
1045                                 state->error = 1;
1046                         } else {
1047                                 char* str = jsonObjectToSimpleString( obj );
1048                                 dbi_conn_quote_string( state->dbhandle, &str );
1049                                 if( str ) {
1050                                         buffer_add( state->sql, str );
1051                                         free( str );
1052                                 } else {
1053                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1054                                                 "Unable to format string literal \"%s\" for bind variable",
1055                                                 jsonObjectGetString( obj )));
1056                                         state->error = 1;
1057                                 }
1058                         }
1059                         break;
1060                 case JSON_NUMBER :
1061                         if( numeric ) {
1062                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1063                         } else {
1064                                 sqlAddMsg( state,
1065                                         "Invalid value for bind variable: expected a number, found a string" );
1066                                 state->error = 1;
1067                         }
1068                         break;
1069                 case JSON_NULL :
1070                         buffer_add( state->sql, "NULL" );
1071                         break;
1072                 case JSON_BOOL :
1073                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1074                                 "Internal error: boolean value for bind variable" ));
1075                         state->error = 1;
1076                         break;
1077                 default :
1078                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1079                                 "Internal error: corrupted value for bind variable" ));
1080                         state->error = 1;
1081                         break;
1082         }
1083 }
1084
1085 /**
1086         @brief Start a new line in the output, with the current level of indentation.
1087         @param state Pointer to the query-building context.
1088 */
1089 static void add_newline( BuildSQLState* state ) {
1090         buffer_add_char( state->sql, '\n' );
1091
1092         // Add indentation
1093         static const char blanks[] = "                                ";   // 32 blanks
1094         static const size_t maxlen = sizeof( blanks ) - 1;
1095         const int blanks_per_level = 3;
1096         int n = state->indent * blanks_per_level;
1097         while( n > 0 ) {
1098                 size_t len = n >= maxlen ? maxlen : n;
1099                 buffer_add_n( state->sql, blanks, len );
1100                 n -= len;
1101         }
1102 }
1103
1104 /**
1105         @brief Increase the degree of indentation.
1106         @param state Pointer to the query-building context.
1107 */
1108 static inline void incr_indent( BuildSQLState* state ) {
1109         ++state->indent;
1110 }
1111
1112 /**
1113         @brief Reduce the degree of indentation.
1114         @param state Pointer to the query-building context.
1115 */
1116 static inline void decr_indent( BuildSQLState* state ) {
1117         if( state->indent )
1118                 --state->indent;
1119 }