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