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