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