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