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