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