1. Support the peculiar syntax of EXTRACT().
[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 buildGroupBy( BuildSQLState* state, const SelectItem* sel_list );
25 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list );
26 static void buildCase( BuildSQLState* state, const Expression* expr );
27 static void buildExpression( BuildSQLState* state, const Expression* expr );
28 static void buildFunction( BuildSQLState* state, const Expression* exp );
29 static void buildExtract( BuildSQLState* state, const Expression* expr );
30 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op );
31 static void buildBindVar( BuildSQLState* state, const BindVar* bind );
32 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
33
34 static void add_newline( BuildSQLState* state );
35 static inline void incr_indent( BuildSQLState* state );
36 static inline void decr_indent( BuildSQLState* state );
37
38 /**
39         @brief Create a jsonObject representing the current list of bind variables.
40         @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
41         @return Pointer to the newly created jsonObject.
42
43         The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
44         variables.  The data for each is another level of JSON_HASH with a fixed set of tags:
45         - "label"
46         - "type"
47         - "description"
48         - "default_value" (as a jsonObject)
49         - "actual_value" (as a jsonObject)
50
51         Any non-existent values are represented as JSON_NULLs.
52
53         The calling code is responsible for freeing the returned jsonOjbect by calling
54         jsonObjectFree().
55 */
56 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
57         jsonObject* list = jsonNewObjectType( JSON_HASH );
58
59         if( bindvar_list && osrfHashGetCount( bindvar_list )) {
60                 // Traverse our internal list of bind variables
61                 BindVar* bind = NULL;
62                 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
63                 while(( bind = osrfHashIteratorNext( iter ))) {
64                         // Create an hash to represent the bind variable
65                         jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
66
67                         // Add an entry for each attribute
68                         jsonObject* attr = jsonNewObject( bind->label );
69                         jsonObjectSetKey( bind_obj, "label", attr );
70
71                         const char* type = NULL;
72                         switch( bind->type ) {
73                                 case BIND_STR :
74                                         type = "string";
75                                         break;
76                                 case BIND_NUM :
77                                         type = "number";
78                                         break;
79                                 case BIND_STR_LIST :
80                                         type = "string_list";
81                                         break;
82                                 case BIND_NUM_LIST :
83                                         type = "number_list";
84                                         break;
85                                 default :
86                                         type = "(invalid)";
87                                         break;
88                         }
89                         attr = jsonNewObject( type );
90                         jsonObjectSetKey( bind_obj, "type", attr );
91
92                         attr = jsonNewObject( bind->description );
93                         jsonObjectSetKey( bind_obj, "description", attr );
94
95                         attr = jsonObjectClone( bind->default_value );
96                         jsonObjectSetKey( bind_obj, "default_value", attr );
97
98                         attr = jsonObjectClone( bind->actual_value );
99                         jsonObjectSetKey( bind_obj, "actual_value", attr );
100
101                         // Add the bind variable to the list
102                         jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
103                 }
104                 osrfHashIteratorFree( iter );
105         }
106
107         return list;
108 }
109
110 /**
111         @brief Apply values to bind variables, overriding the defaults, if any.
112         @param state Pointer to the query-building context.
113         @param bindings A JSON_HASH of values.
114         @return 0 if successful, or 1 if not.
115
116         The @a bindings parameter must be a JSON_HASH.  The keys are the names of bind variables.
117         The values are the corresponding values for the variables.
118 */
119 int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) {
120         if( !state ) {
121                 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
122                 return 1;
123         } else if( !bindings ) {
124                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
125                         "Internal error: No pointer to bindings" ));
126                 return 1;
127         } else if( bindings->type != JSON_HASH ) {
128                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
129                         "Internal error: bindings parameter is not a JSON_HASH" ));
130                 return 1;
131         }
132
133         int rc = 0;
134         jsonObject* value = NULL;
135         jsonIterator* iter = jsonNewIterator( bindings );
136         while(( value = jsonIteratorNext( iter ))) {
137                 const char* var_name = iter->key;
138                 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
139                 if( bind ) {
140                         // Apply or replace the value for the specified variable
141                         if( bind->actual_value )
142                                 jsonObjectFree( bind->actual_value );
143                         bind->actual_value = jsonObjectClone( value );
144                 } else {
145                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
146                                 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
147                         rc = 1;
148                 }
149         }
150         jsonIteratorFree( iter );
151
152         return rc;
153 }
154
155 /**
156         @brief Build an SQL query.
157         @param state Pointer to the query-building context.
158         @param query Pointer to the query to be built.
159         @return Zero if successful, or 1 if not.
160
161         Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
162 */
163 int buildSQL( BuildSQLState* state, const StoredQ* query ) {
164         state->error  = 0;
165         buffer_reset( state->sql );
166         state->indent = 0;
167         build_Query( state, query );
168         if( ! state->error ) {
169                 // Remove the trailing space, if there is one, and add a semicolon.
170                 char c = buffer_chomp( state->sql );
171                 if( c != ' ' )
172                         buffer_add_char( state->sql, c );  // oops, not a space; put it back
173                 buffer_add( state->sql, ";\n" );
174         }
175         return state->error;
176 }
177
178 /**
179         @brief Build an SQL query, appending it to what has been built so far.
180         @param state Pointer to the query-building context.
181         @param query Pointer to the query to be built.
182
183         Look at the query type and branch to the corresponding routine.
184 */
185 static void build_Query( BuildSQLState* state, const StoredQ* query ) {
186         if( buffer_length( state->sql ))
187                 add_newline( state );
188
189         switch( query->type ) {
190                 case QT_SELECT :
191                         buildSelect( state, query );
192                         break;
193                 case QT_UNION :
194                         buildCombo( state, query, "UNION" );
195                         break;
196                 case QT_INTERSECT :
197                         buildCombo( state, query, "INTERSECT" );
198                         break;
199                 case QT_EXCEPT :
200                         buildCombo( state, query, "EXCEPT" );
201                         break;
202                 default :
203                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
204                                 "Internal error: invalid query type %d in query # %d",
205                                 query->type, query->id ));
206                         state->error = 1;
207                         break;
208         }
209 }
210
211 /**
212         @brief Build a UNION, INTERSECT, or EXCEPT query.
213         @param state Pointer to the query-building context.
214         @param query Pointer to the query to be built.
215         @param type_str The query type, as a string.
216 */
217 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) {
218
219         QSeq* seq = query->child_list;
220         if( !seq ) {
221                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
222                         "Internal error: No child queries within %s query # %d",
223                         type_str, query->id ));
224                 state->error = 1;
225                 return;
226         }
227
228         // Traverse the list of child queries
229         while( seq ) {
230                 build_Query( state, seq->child_query );
231                 if( state->error ) {
232                         sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
233                                 seq->child_query->id, type_str, query->id );
234                         return;
235                 }
236                 seq = seq->next;
237                 if( seq ) {
238                         add_newline( state );
239                         buffer_add( state->sql, type_str );
240                         buffer_add_char( state->sql, ' ' );
241                         if( query->use_all )
242                                 buffer_add( state->sql, "ALL " );
243                 }
244         }
245
246         return;
247 }
248
249 /**
250         @brief Build a SELECT statement.
251         @param state Pointer to the query-building context.
252         @param query Pointer to the StoredQ structure that represents the query.
253 */
254 static void buildSelect( BuildSQLState* state, const StoredQ* query ) {
255
256         FromRelation* from_clause = query->from_clause;
257         if( !from_clause ) {
258                 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
259                 state->error = 1;
260                 return;
261         }
262
263         // To do: get SELECT list; just a stub here
264         buffer_add( state->sql, "SELECT" );
265         incr_indent( state );
266         buildSelectList( state, query->select_list );
267         if( state->error ) {
268                 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
269                 state->error = 1;
270                 return;
271         }
272         decr_indent( state );
273
274         // Build FROM clause, if there is one
275         if( query->from_clause ) {
276                 buildFrom( state, query->from_clause );
277                 if( state->error ) {
278                         sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
279                         state->error = 1;
280                         return;
281                 }
282         }
283
284         // Build WHERE clause, if there is one
285         if( query->where_clause ) {
286                 add_newline( state );
287                 buffer_add( state->sql, "WHERE" );
288                 incr_indent( state );
289                 add_newline( state );
290                 buildExpression( state, query->where_clause );
291                 if( state->error ) {
292                         sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
293                         state->error = 1;
294                         return;
295                 }
296                 decr_indent( state );
297         }
298
299         // Build GROUP BY clause, if there is one
300         buildGroupBy( state, query->select_list );
301
302         // Build HAVING clause, if there is one
303         if( query->having_clause ) {
304                 add_newline( state );
305                 buffer_add( state->sql, "HAVING" );
306                 incr_indent( state );
307                 add_newline( state );
308                 buildExpression( state, query->having_clause );
309                 if( state->error ) {
310                         sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
311                         state->error = 1;
312                         return;
313                 }
314                 decr_indent( state );
315         }
316
317         // Build ORDER BY clause, if there is one
318         if( query->order_by_list ) {
319                 buildOrderBy( state, query->order_by_list );
320                 if( state->error ) {
321                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
322                         state->error = 1;
323                         return;
324                 }
325         }
326
327         // To do: Build LIMIT clause, if there is one
328
329         // To do: Build OFFSET clause, if there is one
330
331         state->error = 0;
332 }
333
334 /**
335         @brief Build a FROM clause.
336         @param Pointer to the query-building context.
337         @param Pointer to the StoredQ query to which the FROM clause belongs.
338 */
339 static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) {
340
341         add_newline( state );
342         buffer_add( state->sql, "FROM" );
343         incr_indent( state );
344         add_newline( state );
345
346         switch( core_from->type ) {
347                 case FRT_RELATION : {
348                         char* relation = core_from->table_name;
349                         if( !relation ) {
350                                 if( !core_from->class_name ) {
351                                         sqlAddMsg( state, "No relation specified for core relation # %d",
352                                                 core_from->id );
353                                         state->error = 1;
354                                         return;
355                                 }
356
357                                 // Look up table name, view name, or source_definition in the IDL
358                                 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
359                                 relation = oilsGetRelation( class_hash );
360                         }
361
362                         // Add table or view
363                         buffer_add( state->sql, relation );
364                         if( !core_from->table_name )
365                                 free( relation );   // In this case we strdup'd it, must free it
366                         break;
367                 }
368                 case FRT_SUBQUERY :
369                         buffer_add_char( state->sql, '(' );
370                         incr_indent( state );
371                         build_Query( state, core_from->subquery );
372                         decr_indent( state );
373                         add_newline( state );
374                         buffer_add_char( state->sql, ')' );
375                         break;
376                 case FRT_FUNCTION :
377                         buildFunction( state, core_from->function_call );
378                         if ( state->error ) {
379                                 sqlAddMsg( state,
380                                         "Unable to include function call # %d in FROM relation # %d",
381                                         core_from->function_call->id, core_from->id );
382                                 return;
383                         }
384                         break;
385                 default :
386                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
387                                 "Internal error: Invalid type # %d in FROM relation # %d",
388                                 core_from->type, core_from->id ));
389                         state->error = 1;
390                         return;
391         }
392
393         // Add a table alias, if possible
394         if( core_from->table_alias ) {
395                 buffer_add( state->sql, " AS \"" );
396                 buffer_add( state->sql, core_from->table_alias );
397                 buffer_add( state->sql, "\" " );
398         }
399         else if( core_from->class_name ) {
400                 buffer_add( state->sql, " AS \"" );
401                 buffer_add( state->sql, core_from->class_name );
402                 buffer_add( state->sql, "\" " );
403         } else
404                 buffer_add_char( state->sql, ' ' );
405
406         incr_indent( state );
407         FromRelation* join = core_from->join_list;
408         while( join ) {
409                 buildJoin( state, join );
410                 if( state->error ) {
411                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
412                                 core_from->id );
413                         break;
414                 } else
415                         join = join->next;
416         }
417         decr_indent( state );
418         decr_indent( state );
419 }
420
421 /**
422         @brief Add a JOIN clause.
423         @param state Pointer to the query-building context.
424         @param join Pointer to the FromRelation representing the JOIN to be added.
425 */
426 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
427         add_newline( state );
428         switch( join->join_type ) {
429                 case JT_NONE :
430                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
431                         state->error = 1;
432                         return;
433                 case JT_INNER :
434                         buffer_add( state->sql, "INNER JOIN " );
435                         break;
436                 case JT_LEFT:
437                         buffer_add( state->sql, "LEFT JOIN " );
438                         break;
439                 case JT_RIGHT:
440                         buffer_add( state->sql, "RIGHT JOIN " );
441                         break;
442                 case JT_FULL:
443                         buffer_add( state->sql, "FULL JOIN " );
444                         break;
445                 default :
446                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
447                         state->error = 1;
448                         return;
449         }
450
451         switch( join->type ) {
452                 case FRT_RELATION :
453                         // Sanity check
454                         if( !join->table_name || ! *join->table_name ) {
455                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
456                                 state->error = 1;
457                                 return;
458                         }
459                         buffer_add( state->sql, join->table_name );
460                         break;
461                 case FRT_SUBQUERY :
462                         // Sanity check
463                         if( !join->subquery ) {
464                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
465                                 state->error = 1;
466                                 return;
467                         } else if( !join->table_alias ) {
468                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
469                                         join->id );
470                                 state->error = 1;
471                                 return;
472                         }
473                         buffer_add_char( state->sql, '(' );
474                         incr_indent( state );
475                         build_Query( state, join->subquery );
476                         decr_indent( state );
477                         add_newline( state );
478                         buffer_add_char( state->sql, ')' );
479                         break;
480                 case FRT_FUNCTION :
481                         if( !join->table_name || ! *join->table_name ) {
482                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
483                                         join->id );
484                                 state->error = 1;
485                                 return;
486                         }
487                         break;
488         }
489
490         const char* effective_alias = join->table_alias;
491         if( !effective_alias )
492                 effective_alias = join->class_name;
493
494         if( effective_alias ) {
495                 buffer_add( state->sql, " AS \"" );
496                 buffer_add( state->sql, effective_alias );
497                 buffer_add_char( state->sql, '\"' );
498         }
499
500         if( join->on_clause ) {
501                 incr_indent( state );
502                 add_newline( state );
503                 buffer_add( state->sql, "ON " );
504                 buildExpression( state, join->on_clause );
505                 decr_indent( state );
506         }
507
508         FromRelation* subjoin = join->join_list;
509         while( subjoin ) {
510                 buildJoin( state, subjoin );
511                 if( state->error ) {
512                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
513                         break;
514                 } else
515                         subjoin = subjoin->next;
516         }
517 }
518
519 /**
520         @brief Build a SELECT list.
521         @param state Pointer to the query-building context.
522         @param item Pointer to the first in a linked list of SELECT items.
523 */
524 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
525
526         int first = 1;
527         while( item ) {
528                 if( !first )
529                         buffer_add_char( state->sql, ',' );
530                 add_newline( state );
531                 buildExpression( state, item->expression );
532                 if( state->error ) {
533                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
534                         state->error = 1;
535                         break;
536                 }
537
538                 if( item->column_alias ) {
539                         buffer_add( state->sql, " AS \"" );
540                         buffer_add( state->sql, item->column_alias );
541                         buffer_add_char( state->sql, '\"' );
542                 }
543                 first = 0;
544                 item = item->next;
545         };
546         buffer_add_char( state->sql, ' ' );
547 }
548
549 /**
550         @brief Add a GROUP BY clause, if there is one, to the current query.
551         @param state Pointer to the query-building context.
552         @param sel_list Pointer to the first node in a linked list of SelectItems
553
554         We reference the GROUP BY items by number, not by repeating the expressions.
555 */
556 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
557         int seq = 0;       // Sequence number of current SelectItem
558         int first = 1;     // Boolean: true for the first GROUPed BY item
559         while( sel_list ) {
560                 ++seq;
561
562                 if( sel_list->grouped_by ) {
563                         if( first ) {
564                                 add_newline( state );
565                                 buffer_add( state->sql, "GROUP BY " );
566                                 first = 0;
567                         }
568                         else
569                                 buffer_add( state->sql, ", " );
570
571                         buffer_fadd( state->sql, "%d", seq );
572                 }
573
574                 sel_list = sel_list->next;
575         }
576 }
577
578 /**
579         @brief Add an ORDER BY clause to the current query.
580         @param state Pointer to the query-building context.
581         @param ord_list Pointer to the first node in a linked list of OrderItems.
582 */
583 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
584         add_newline( state );
585         buffer_add( state->sql, "ORDER BY" );
586         incr_indent( state );
587
588         int first = 1;    // boolean
589         while( ord_list ) {
590                 if( first )
591                         first = 0;
592                 else
593                         buffer_add_char( state->sql, ',' );
594                 add_newline( state );
595                 buildExpression( state, ord_list->expression );
596                 if( state->error ) {
597                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
598                         return;
599                 }
600
601                 ord_list = ord_list->next;
602         }
603
604         decr_indent( state );
605         return;
606 }
607
608 /**
609         @brief Build an arbitrary expression.
610         @param state Pointer to the query-building context.
611         @param expr Pointer to the Expression representing the expression to be built.
612 */
613 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
614         if( !expr ) {
615                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
616                         "Internal error: NULL pointer to Expression" ));
617                 state->error = 1;
618                 return;
619         }
620
621         if( expr->parenthesize )
622                 buffer_add_char( state->sql, '(' );
623
624         switch( expr->type ) {
625                 case EXP_BETWEEN :
626                         if( expr->negate )
627                                 buffer_add( state->sql, "NOT " );
628
629                         buildExpression( state, expr->left_operand );
630                         if( state->error ) {
631                                 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
632                                         expr->id );
633                                 break;
634                         }
635
636                         buffer_add( state->sql, " BETWEEN " );
637
638                         buildExpression( state, expr->subexp_list );
639                         if( state->error ) {
640                                 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
641                                         expr->id );
642                                 break;
643                         }
644
645                         buffer_add( state->sql, " AND " );
646
647                         buildExpression( state, expr->subexp_list->next );
648                         if( state->error ) {
649                                 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
650                                         expr->id );
651                                 break;
652                         }
653
654                         break;
655                 case EXP_BIND :
656                         if( !expr->bind ) {     // Sanity check
657                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
658                                         "Internal error: no variable for bind variable expression" ));
659                                 state->error = 1;
660                         } else
661                                 buildBindVar( state, expr->bind );
662                         break;
663                 case EXP_BOOL :
664                         if( expr->negate )
665                                 buffer_add( state->sql, "NOT " );
666
667                         if( expr->literal ) {
668                                 buffer_add( state->sql, expr->literal );
669                                 buffer_add_char( state->sql, ' ' );
670                         } else
671                                 buffer_add( state->sql, "FALSE " );
672                         break;
673                 case EXP_CASE :
674                         buildCase( state, expr );
675                         if( state->error )
676                                 sqlAddMsg( state, "Unable to build CASE expression # %d", expr->id );
677
678                         break;
679                 case EXP_CAST :                   // Type cast
680                         if( expr->negate )
681                                 buffer_add( state->sql, "NOT " );
682
683                         buffer_add( state->sql, "CAST (" );
684                         buildExpression( state, expr->left_operand );
685                         if( state->error )
686                                 sqlAddMsg( state, "Unable to build left operand for CAST expression # %d",
687                                         expr->id );
688                         else {
689                                 buffer_add( state->sql, " AS " );
690                                 if( expr->cast_type && expr->cast_type->datatype_name ) {
691                                         buffer_add( state->sql, expr->cast_type->datatype_name );
692                                         buffer_add_char( state->sql, ')' );
693                                 } else {
694                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
695                                                 "No datatype available for CAST expression # %d", expr->id ));
696                                         state->error = 1;
697                                 }
698                         }
699                         break;
700                 case EXP_COLUMN :                 // Table column
701                         if( expr->negate )
702                                 buffer_add( state->sql, "NOT " );
703
704                         if( expr->table_alias ) {
705                                 buffer_add_char( state->sql, '\"' );
706                                 buffer_add( state->sql, expr->table_alias );
707                                 buffer_add( state->sql, "\"." );
708                         }
709                         if( expr->column_name ) {
710                                 buffer_add( state->sql, expr->column_name );
711                         } else {
712                                 buffer_add_char( state->sql, '*' );
713                         }
714                         break;
715                 case EXP_EXIST :
716                         if( !expr->subquery ) {
717                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
718                                         "No subquery found for EXIST expression # %d", expr->id ));
719                                 state->error = 1;
720                         } else {
721                                 if( expr->negate )
722                                         buffer_add( state->sql, "NOT " );
723
724                                 buffer_add( state->sql, "EXISTS (" );
725                                 incr_indent( state );
726                                 build_Query( state, expr->subquery );
727                                 decr_indent( state );
728                                 add_newline( state );
729                                 buffer_add_char( state->sql, ')' );
730                         }
731                         break;
732                 case EXP_FUNCTION :
733                         buildFunction( state, expr );
734                         break;
735                 case EXP_IN :
736                         if( expr->left_operand ) {
737                                 buildExpression( state, expr->left_operand );
738                                 if( !state->error ) {
739                                         if( expr->negate )
740                                                 buffer_add( state->sql, "NOT " );
741                                         buffer_add( state->sql, " IN (" );
742
743                                         if( expr->subquery ) {
744                                                 incr_indent( state );
745                                                 build_Query( state, expr->subquery );
746                                                 if( state->error )
747                                                         sqlAddMsg( state, "Unable to build subquery for IN condition" );
748                                                 else {
749                                                         decr_indent( state );
750                                                         add_newline( state );
751                                                         buffer_add_char( state->sql, ')' );
752                                                 }
753                                         } else {
754                                                 buildSeries( state, expr->subexp_list, NULL );
755                                                 if( state->error )
756                                                         sqlAddMsg( state, "Unable to build IN list" );
757                                                 else
758                                                         buffer_add_char( state->sql, ')' );
759                                         }
760                                 }
761                         }
762                         break;
763                 case EXP_ISNULL :
764                         if( expr->left_operand ) {
765                                 buildExpression( state, expr->left_operand );
766                                 if( state->error ) {
767                                         sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
768                                                 expr->id );
769                                         break;
770                                 }
771                         }
772
773                         if( expr->negate )
774                                 buffer_add( state->sql, " IS NOT NULL" );
775                         else
776                                 buffer_add( state->sql, " IS NULL" );
777                         break;
778                 case EXP_NULL :
779                         if( expr->negate )
780                                 buffer_add( state->sql, "NOT " );
781
782                         buffer_add( state->sql, "NULL" );
783                         break;
784                 case EXP_NUMBER :                    // Numeric literal
785                         if( !expr->literal ) {
786                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
787                                         "Internal error: No numeric value in string expression # %d", expr->id ));
788                                 state->error = 1;
789                         } else {
790                                 buffer_add( state->sql, expr->literal );
791                         }
792                         break;
793                 case EXP_OPERATOR :
794                         if( expr->negate )
795                                 buffer_add( state->sql, "NOT (" );
796
797                         if( expr->left_operand ) {
798                                 buildExpression( state, expr->left_operand );
799                                 if( state->error ) {
800                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
801                                                 expr->id );
802                                         break;
803                                 }
804                         }
805                         buffer_add_char( state->sql, ' ' );
806                         buffer_add( state->sql, expr->op );
807                         buffer_add_char( state->sql, ' ' );
808                         if( expr->right_operand ) {
809                                 buildExpression( state, expr->right_operand );
810                                 if( state->error ) {
811                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
812                                                            expr->id );
813                                         break;
814                                 }
815                         }
816
817                         if( expr->negate )
818                                 buffer_add_char( state->sql, ')' );
819
820                         break;
821                 case EXP_SERIES :
822                         if( expr->negate )
823                                 buffer_add( state->sql, "NOT (" );
824
825                         buildSeries( state, expr->subexp_list, expr->op );
826                         if( state->error ) {
827                                 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
828                                         expr->op ? expr->op : "," );
829                         }
830                         if( expr->negate )
831                                 buffer_add_char( state->sql, ')' );
832
833                         break;
834                 case EXP_STRING :                     // String literal
835                         if( !expr->literal ) {
836                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
837                                         "Internal error: No string value in string expression # %d", expr->id ));
838                                         state->error = 1;
839                         } else {
840                                 char* str = strdup( expr->literal );
841                                 dbi_conn_quote_string( state->dbhandle, &str );
842                                 if( str ) {
843                                         buffer_add( state->sql, str );
844                                         free( str );
845                                 } else {
846                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
847                                                 "Unable to format string literal \"%s\" for expression # %d",
848                                                         expr->literal, expr->id ));
849                                         state->error = 1;
850                                 }
851                         }
852                         break;
853                 case EXP_SUBQUERY :
854                         if( expr->negate )
855                                 buffer_add( state->sql, "NOT " );
856
857                         if( expr->subquery ) {
858                                 buffer_add_char( state->sql, '(' );
859                                 incr_indent( state );
860                                 build_Query( state, expr->subquery );
861                                 decr_indent( state );
862                                 add_newline( state );
863                                 buffer_add_char( state->sql, ')' );
864                         } else {
865                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
866                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
867                                 state->error = 1;
868                         }
869                         break;
870         }
871
872         if( expr->parenthesize )
873                 buffer_add_char( state->sql, ')' );
874 }
875
876 /**
877         @brief Build a CASE expression.
878         @param state Pointer to the query-building context.
879         @param exp Pointer to an Expression representing a CASE expression.
880 */
881 static void buildCase( BuildSQLState* state, const Expression* expr ) {
882         // Sanity checks
883         if( ! expr->left_operand ) {
884                 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
885                 state->error  = 1;
886                 return;
887         } else if( ! expr->branch_list ) {
888                 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
889                 state->error  = 1;
890                 return;
891         }
892
893         if( expr->negate )
894                 buffer_add( state->sql, "NOT (" );
895
896         // left_operand is the expression on which we shall branch
897         buffer_add( state->sql, "CASE " );
898         buildExpression( state, expr->left_operand );
899         if( state->error ) {
900                 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
901                 return;
902         }
903
904         incr_indent( state );
905
906         // Emit each branch in turn
907         CaseBranch* branch = expr->branch_list;
908         while( branch ) {
909                 add_newline( state );
910
911                 if( branch->condition ) {
912                         // Emit a WHEN condition
913                         buffer_add( state->sql, "WHEN " );
914                         buildExpression( state, branch->condition );
915                         incr_indent( state );
916                         add_newline( state );
917                         buffer_add( state->sql, "THEN " );
918                 } else {
919                         // Emit ELSE
920                         buffer_add( state->sql, "ELSE " );
921                         incr_indent( state );
922                         add_newline( state );
923                 }
924
925                 // Emit the THEN expression
926                 buildExpression( state, branch->result );
927                 decr_indent( state );
928
929                 branch = branch->next;
930         }
931
932         decr_indent( state );
933         add_newline( state );
934         buffer_add( state->sql, "END" );
935
936         if( expr->negate )
937                 buffer_add( state->sql, ")" );
938 }
939
940 /**
941         @brief Build a function call.
942         @param state Pointer to the query-building context.
943         @param exp Pointer to an Expression representing a function call.
944
945         This function does not currently accommodate certain functions with idiosyncratic
946         syntax, such as the absence of parentheses, or the use of certain keywords in
947         in the parameter list.
948 */
949 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
950         if( expr->negate )
951                 buffer_add( state->sql, "NOT " );
952
953         // If a subfield is specified, the function call
954         // needs an extra layer of parentheses
955         if( expr->column_name )
956                 buffer_add_char( state->sql, '(' );
957
958         // First, check for some specific functions with peculiar syntax, and treat them
959         // as special exceptions.  We rely on the input side to ensure that the function
960         // name is available.
961         if( !strcasecmp( expr->function_name, "EXTRACT" )) {
962                 buildExtract( state, expr );
963         } else {
964                 // Not a special exception.
965                 buffer_add( state->sql, expr->function_name );
966                 buffer_add_char( state->sql, '(' );
967
968                 // Add the parameters, if any
969                 buildSeries( state, expr->subexp_list, NULL );
970
971                 buffer_add_char( state->sql, ')' );
972         }
973         
974         if( expr->column_name ) {
975                 // Add the name of the subfield
976                 buffer_add( state->sql, ").\"" );
977                 buffer_add( state->sql, expr->column_name );
978                 buffer_add_char( state->sql, '\"' );
979         }
980 }
981
982 /**
983         @brief Build a call to the EXTRACT function, with its peculiar syntax.
984         @param state Pointer to the query-building context.
985         @param exp Pointer to an Expression representing an EXTRACT call.
986 */
987 static void buildExtract( BuildSQLState* state, const Expression* expr ) {
988
989         const Expression* arg = expr->subexp_list;
990
991         // Sanity checks
992         if( !arg ) {
993                 sqlAddMsg( state,
994                         "No arguments supplied to EXTRACT function in expression # %d", expr->id );
995                 state->error = 1;
996                 return;
997         } else if( arg->type != EXP_STRING ) {
998                 sqlAddMsg( state,
999                         "First argument to EXTRACT is not a string in expression # %d", expr->id );
1000                 state->error = 1;
1001                 return;
1002         } else {
1003                 // check the first argument against a list of valid values
1004                 if(    strcasecmp( arg->literal, "century" )
1005                         && strcasecmp( arg->literal, "day" )
1006                         && strcasecmp( arg->literal, "decade" )
1007                         && strcasecmp( arg->literal, "dow" )
1008                         && strcasecmp( arg->literal, "doy" )
1009                         && strcasecmp( arg->literal, "epoch" )
1010                         && strcasecmp( arg->literal, "hour" )
1011                         && strcasecmp( arg->literal, "isodow" )
1012                         && strcasecmp( arg->literal, "isoyear" )
1013                         && strcasecmp( arg->literal, "microseconds" )
1014                         && strcasecmp( arg->literal, "millennium" )
1015                         && strcasecmp( arg->literal, "milliseconds" )
1016                         && strcasecmp( arg->literal, "minute" )
1017                         && strcasecmp( arg->literal, "month" )
1018                         && strcasecmp( arg->literal, "quarter" )
1019                         && strcasecmp( arg->literal, "second" )
1020                         && strcasecmp( arg->literal, "timezone" )
1021                         && strcasecmp( arg->literal, "timezone_hour" )
1022                         && strcasecmp( arg->literal, "timezone_minute" )
1023                         && strcasecmp( arg->literal, "week" )
1024                         && strcasecmp( arg->literal, "year" )) {
1025                         sqlAddMsg( state,
1026                                 "Invalid name \"%s\" as EXTRACT argument in expression # %d",
1027                                 expr->literal, expr->id );
1028                         state->error = 1;
1029                 }
1030         }
1031
1032         buffer_add( state->sql, "EXTRACT(" );
1033         buffer_add( state->sql, arg->literal );
1034         buffer_add( state->sql, " FROM " );
1035
1036         arg = arg->next;
1037         if( !arg ) {
1038                 sqlAddMsg( state,
1039                         "Only one argument supplied to EXTRACT function in expression # %d", expr->id );
1040                 state->error = 1;
1041                 return;
1042         }
1043
1044         // The second parameter must be of type timestamp, time, or interval.  We don't have
1045         // a good way of checking it here, so we rely on PostgreSQL to complain if necessary.
1046         buildExpression( state, arg );
1047         buffer_add_char( state->sql, ')' );
1048
1049         if( arg->next ) {
1050                 sqlAddMsg( state,
1051                         "Too many one arguments supplied to EXTRACT function in expression # %d", expr->id );
1052                 state->error = 1;
1053                 return;
1054         }
1055 }
1056
1057 /**
1058         @brief Build a series of expressions separated by a specified operator, or by commas.
1059         @param state Pointer to the query-building context.
1060         @param subexp_list Pointer to the first Expression in a linked list.
1061         @param op Pointer to the operator, or NULL for commas.
1062
1063         If the operator is AND or OR (in upper, lower, or mixed case), the second and all
1064         subsequent operators will begin on a new line.
1065 */
1066 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
1067
1068         if( !subexp_list)
1069                 return;                // List is empty
1070
1071         int comma = 0;             // Boolean; true if separator is a comma
1072         int newline_needed = 0;    // Boolean; true if operator is AND or OR
1073
1074         if( !op ) {
1075                 op = ",";
1076                 comma = 1;
1077         } else if( !strcmp( op, "," ))
1078                 comma = 1;
1079         else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
1080                 newline_needed = 1;
1081
1082         int first = 1;               // Boolean; true for first item in list
1083         while( subexp_list ) {
1084                 if( first )
1085                         first = 0;   // No separator needed yet
1086                 else {
1087                         // Insert a separator
1088                         if( comma )
1089                                 buffer_add( state->sql, ", " );
1090                         else {
1091                                 if( newline_needed )
1092                                         add_newline( state );
1093                                 else
1094                                         buffer_add_char( state->sql, ' ' );
1095
1096                                 buffer_add( state->sql, op );
1097                                 buffer_add_char( state->sql, ' ' );
1098                         }
1099                 }
1100
1101                 buildExpression( state, subexp_list );
1102                 subexp_list = subexp_list->next;
1103         }
1104 }
1105
1106 /**
1107         @brief Add the value of a bind variable to an SQL statement.
1108         @param state Pointer to the query-building context.
1109         @param bind Pointer to the bind variable whose value is to be added to the SQL.
1110
1111         The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1112         the type of the bind variable.
1113 */
1114 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1115
1116         // Decide where to get the value, if any
1117         const jsonObject* value = NULL;
1118         if( bind->actual_value )
1119                 value = bind->actual_value;
1120         else if( bind->default_value ) {
1121                 if( state->defaults_usable )
1122                         value = bind->default_value;
1123                 else {
1124                         sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1125                                 bind->name );
1126                         state->error = 1;
1127                         return;
1128                 }
1129         } else if( state->values_required ) {
1130                 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1131                 state->error = 1;
1132                 return;
1133         } else {
1134                 // No value available, and that's okay.  Emit the name of the bind variable.
1135                 buffer_add_char( state->sql, ':' );
1136                 buffer_add( state->sql, bind->name );
1137                 return;
1138         }
1139
1140         // If we get to this point, we know that a value is available.  Carry on.
1141
1142         int numeric = 0;       // Boolean
1143         if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1144                 numeric = 1;
1145
1146         // Emit the value
1147         switch( bind->type ) {
1148                 case BIND_STR :
1149                 case BIND_NUM :
1150                         buildScalar( state, numeric, value );
1151                         break;
1152                 case BIND_STR_LIST :
1153                 case BIND_NUM_LIST :
1154                         if( JSON_ARRAY == value->type ) {
1155                                 // Iterate over array, emit each value
1156                                 int first = 1;   // Boolean
1157                                 unsigned long max = value->size;
1158                                 unsigned long i = 0;
1159                                 while( i < max ) {
1160                                         if( first )
1161                                                 first = 0;
1162                                         else
1163                                                 buffer_add( state->sql, ", " );
1164
1165                                         buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1166                                         ++i;
1167                                 }
1168                         } else {
1169                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1170                                         "Invalid value for bind variable; expected a list of values" ));
1171                                 state->error = 1;
1172                         }
1173                         break;
1174                 default :
1175                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1176                                 "Internal error: invalid type for bind variable" ));
1177                         state->error = 1;
1178                         break;
1179         }
1180
1181         if( state->error )
1182                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1183                         "Unable to emit value of bind variable \"%s\"", bind->name ));
1184 }
1185
1186 /**
1187         @brief Add a number or quoted string to an SQL statement.
1188         @param state Pointer to the query-building context.
1189         @param numeric Boolean; true if the value is expected to be a number
1190         @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1191 */
1192 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1193         switch( obj->type ) {
1194                 case JSON_HASH :
1195                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1196                                 "Internal error: hash value for bind variable" ));
1197                         state->error = 1;
1198                         break;
1199                 case JSON_ARRAY :
1200                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1201                                 "Internal error: array value for bind variable" ));
1202                         state->error = 1;
1203                         break;
1204                 case JSON_STRING :
1205                         if( numeric ) {
1206                                 sqlAddMsg( state,
1207                                         "Invalid value for bind variable: expected a string, found a number" );
1208                                 state->error = 1;
1209                         } else {
1210                                 char* str = jsonObjectToSimpleString( obj );
1211                                 dbi_conn_quote_string( state->dbhandle, &str );
1212                                 if( str ) {
1213                                         buffer_add( state->sql, str );
1214                                         free( str );
1215                                 } else {
1216                                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1217                                                 "Unable to format string literal \"%s\" for bind variable",
1218                                                 jsonObjectGetString( obj )));
1219                                         state->error = 1;
1220                                 }
1221                         }
1222                         break;
1223                 case JSON_NUMBER :
1224                         if( numeric ) {
1225                                 buffer_add( state->sql, jsonObjectGetString( obj ));
1226                         } else {
1227                                 sqlAddMsg( state,
1228                                         "Invalid value for bind variable: expected a number, found a string" );
1229                                 state->error = 1;
1230                         }
1231                         break;
1232                 case JSON_NULL :
1233                         buffer_add( state->sql, "NULL" );
1234                         break;
1235                 case JSON_BOOL :
1236                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1237                                 "Internal error: boolean value for bind variable" ));
1238                         state->error = 1;
1239                         break;
1240                 default :
1241                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1242                                 "Internal error: corrupted value for bind variable" ));
1243                         state->error = 1;
1244                         break;
1245         }
1246 }
1247
1248 /**
1249         @brief Start a new line in the output, with the current level of indentation.
1250         @param state Pointer to the query-building context.
1251 */
1252 static void add_newline( BuildSQLState* state ) {
1253         buffer_add_char( state->sql, '\n' );
1254
1255         // Add indentation
1256         static const char blanks[] = "                                ";   // 32 blanks
1257         static const size_t maxlen = sizeof( blanks ) - 1;
1258         const int blanks_per_level = 3;
1259         int n = state->indent * blanks_per_level;
1260         while( n > 0 ) {
1261                 size_t len = n >= maxlen ? maxlen : n;
1262                 buffer_add_n( state->sql, blanks, len );
1263                 n -= len;
1264         }
1265 }
1266
1267 /**
1268         @brief Increase the degree of indentation.
1269         @param state Pointer to the query-building context.
1270 */
1271 static inline void incr_indent( BuildSQLState* state ) {
1272         ++state->indent;
1273 }
1274
1275 /**
1276         @brief Reduce the degree of indentation.
1277         @param state Pointer to the query-building context.
1278 */
1279 static inline void decr_indent( BuildSQLState* state ) {
1280         if( state->indent )
1281                 --state->indent;
1282 }