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