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