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