1. In oils_sql.c: make the functions is_identifier() and is_good_operator()
[working/Evergreen.git] / Open-ILS / src / c-apps / oils_storedq.c
1 /**
2         @file oils_storedq.c
3         @brief Load an abstract representation of a query from the database.
4 */
5
6 #include <stdlib.h>
7 #include <string.h>
8 #include <ctype.h>
9 #include <dbi/dbi.h>
10 #include "opensrf/utils.h"
11 #include "opensrf/log.h"
12 #include "opensrf/string_array.h"
13 #include "opensrf/osrf_hash.h"
14 #include "opensrf/osrf_application.h"
15 #include "openils/oils_sql.h"
16 #include "openils/oils_buildq.h"
17
18 #define PRINT if( verbose ) printf
19
20 struct IdNode_ {
21         IdNode* next;
22         int id;
23         char* alias;
24 };
25
26 static int oils_result_get_bool_idx( dbi_result result, int i );
27
28 static FromRelation* getFromRelation( BuildSQLState* state, int id );
29 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result );
30 static FromRelation* getJoinList( BuildSQLState* state, int id );
31 static void joinListFree( FromRelation* join_list );
32 static void fromRelationFree( FromRelation* fr );
33
34 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str );
35 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result );
36 static void freeQSeqList( QSeq* seq );
37 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result );
38
39 static SelectItem* getSelectList( BuildSQLState* state, int query_id );
40 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result );
41 static void selectListFree( SelectItem* sel );
42
43 static BindVar* getBindVar( BuildSQLState* state, const char* name );
44 static BindVar* constructBindVar( BuildSQLState* state, dbi_result result );
45 static void bindVarFree( char* name, void* p );
46
47 static CaseBranch* getCaseBranchList( BuildSQLState* state, int parent_id );
48 static CaseBranch* constructCaseBranch( BuildSQLState* state, dbi_result result );
49 static void freeBranchList( CaseBranch* branch );
50
51 static Datatype* getDatatype( BuildSQLState* state, int id );
52 static Datatype* constructDatatype( BuildSQLState* state, dbi_result result );
53 static void datatypeFree( Datatype* datatype );
54
55 static Expression* getExpression( BuildSQLState* state, int id );
56 static Expression* constructExpression( BuildSQLState* state, dbi_result result );
57 static void expressionListFree( Expression* exp );
58 static void expressionFree( Expression* exp );
59 static Expression* getExpressionList( BuildSQLState* state, int id );
60
61 static OrderItem* getOrderByList( BuildSQLState* state, int query_id );
62 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result );
63 static void orderItemListFree( OrderItem* ord );
64
65 static void push_id( IdNode** stack, int id, const char* alias );
66 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias );
67
68 // A series of free lists to store already-allocated objects that are not in use, for
69 // potential reuse.  This is a hack to reduce churning through malloc() and free().
70 static StoredQ* free_storedq_list = NULL;
71 static FromRelation* free_from_relation_list = NULL;
72 static SelectItem* free_select_item_list = NULL;
73 static BindVar* free_bindvar_list = NULL;
74 static CaseBranch* free_branch_list = NULL;
75 static Datatype* free_datatype_list = NULL;
76 static Expression* free_expression_list = NULL;
77 static IdNode* free_id_node_list = NULL;
78 static QSeq* free_qseq_list = NULL;
79 static OrderItem* free_order_item_list = NULL;
80
81 // Boolean; settable by call to oilsStoredQSetVerbose(), used by PRINT macro.
82 // The idea is to allow debugging messages from a command line test driver for ease of
83 // testing and development, but not from a real server, where messages to stdout don't
84 // go anywhere.
85 static int verbose = 0;
86
87 /**
88         @brief Build a list of column names for a specified query.
89         @param state Pointer to the query-building context.
90         @param query Pointer to the specified query.
91         @return Pointer to a newly-allocated JSON_ARRAY of column names, if successful;
92                 otherwise NULL.
93
94         The column names are those assigned by PostgreSQL, e.g.:
95                 - a column alias, if an AS clause defines one
96                 - a column name (not qualified by a table name or alias, even if the query
97                   specifies one)
98                 - where the item is a function call, the name of the function
99                 - where the item is a subquery or other expression, whatever PostgreSQL decides on,
100                   typically '?column?'
101
102         The resulting column names may include duplicates.
103
104         The calling code is responsible for freeing the list by calling jsonObjectFree().
105 */
106 jsonObject* oilsGetColNames( BuildSQLState* state, StoredQ* query ) {
107         if( !state || !query ) return NULL;
108
109         // Build SQL in the usual way (with some temporary option settings)
110         int defaults_usable = state->defaults_usable;
111         int values_required = state->values_required;
112         state->defaults_usable = 1;   // We can't execute the test query unless we
113         state->values_required = 1;   // have a value for every bind variable.
114         int rc = buildSQL( state, query );
115         state->defaults_usable = defaults_usable;
116         state->values_required = values_required;
117         if( rc ) {
118                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
119                         "Unable to build SQL statement for query id # %d", query->id ));
120                 state->error = 1;
121                 return NULL;
122         }
123
124         // Wrap it in an outer query to get the column names, but no rows
125         growing_buffer* wrapper = buffer_init( 80 + strlen( OSRF_BUFFER_C_STR( state->sql )));
126         buffer_add( wrapper, "SELECT \"phony query\".* FROM (" );
127         buffer_add( wrapper, OSRF_BUFFER_C_STR( state->sql ));
128         buffer_chomp( wrapper );    // remove the terminating newline
129         buffer_chomp( wrapper );    // remove the terminating semicolon
130         buffer_add( wrapper, ") AS \"phony query\" WHERE FALSE;" );
131
132         // Execute the wrapped query
133         dbi_result result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( wrapper ));
134         buffer_free( wrapper );
135         if( !result ) {
136                 const char* msg;
137                 int errnum = dbi_conn_error( state->dbhandle, &msg );
138                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
139                         "Unable to execute dummy query for column names: #%d %s",
140                         errnum, msg ? msg : "No description available" ));
141                 state->error = 1;
142                 return NULL;
143         }
144
145         // Examine the query result to get the column names
146
147         unsigned int num_cols = dbi_result_get_numfields( result );
148         jsonObject* cols = jsonNewObjectType( JSON_ARRAY );
149         unsigned int i;
150         for( i = 1; i <= num_cols; ++i ) {
151                 const char* fname = dbi_result_get_field_name( result, i );
152                 if( fname )
153                         jsonObjectPush( cols, jsonNewObject( fname ));
154         }
155
156         dbi_result_free( result );
157         return cols;
158 }
159
160 /**
161         @brief Load a stored query.
162         @param state Pointer to the query-building context.
163         @param query_id ID of the query in query.stored_query.
164         @return A pointer to the newly loaded StoredQ if successful, or NULL if not.
165
166         The calling code is responsible for freeing the StoredQ by calling storedQFree().
167 */
168 StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) {
169         if( !state )
170                 return NULL;
171
172         // Check the stack to see if the current query is nested inside itself.  If it is, then
173         // abort in order to avoid infinite recursion.  If it isn't, then add it to the stack.
174         // (Make sure to pop it off the stack before returning.)
175         if( searchIdStack( state->query_stack, query_id, NULL )) {
176                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
177                         "Infinite recursion detected; query # %d is nested within itself", query_id ));
178                 state->error = 1;
179                 return NULL;
180         } else
181                 push_id( &state->query_stack, query_id, NULL );
182
183         StoredQ* sq = NULL;
184         dbi_result result = dbi_conn_queryf( state->dbhandle,
185                 "SELECT id, type, use_all, use_distinct, from_clause, where_clause, having_clause "
186                 "FROM query.stored_query WHERE id = %d;", query_id );
187         if( result ) {
188                 if( dbi_result_first_row( result ) ) {
189                         sq = constructStoredQ( state, result );
190                         if( sq ) {
191                                 PRINT( "Got a query row\n" );
192                                 PRINT( "\tid: %d\n", sq->id );
193                                 PRINT( "\ttype: %d\n", (int) sq->type );
194                                 PRINT( "\tuse_all: %s\n", sq->use_all ? "true" : "false" );
195                                 PRINT( "\tuse_distinct: %s\n", sq->use_distinct ? "true" : "false" );
196                         } else
197                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
198                                         "Unable to build a query for id = %d", query_id ));
199                 } else {
200                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
201                                 "Stored query not found for id %d", query_id ));
202                         state->error = 1;
203                 }
204
205                 dbi_result_free( result );
206         } else {
207                 const char* msg;
208                 int errnum = dbi_conn_error( state->dbhandle, &msg );
209                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
210                         "Unable to query query.stored_query table: #%d %s",
211                         errnum, msg ? msg : "No description available" ));
212                 state->error = 1;
213         }
214
215         pop_id( &state->query_stack );
216         return sq;
217 }
218
219 /**
220         @brief Construct a StoredQ.
221         @param Pointer to the query-building context.
222         @param result Database cursor positioned at a row in query.stored_query.
223         @return Pointer to a newly constructed StoredQ, if successful, or NULL if not.
224
225         The calling code is responsible for freeing the StoredQ by calling storedQFree().
226 */
227 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) {
228
229         // Get the column values from the result
230         int id               = dbi_result_get_int_idx( result, 1 );
231         const char* type_str = dbi_result_get_string_idx( result, 2 );
232
233         QueryType type;
234         if( !strcmp( type_str, "SELECT" ))
235                 type = QT_SELECT;
236         else if( !strcmp( type_str, "UNION" ))
237                 type = QT_UNION;
238         else if( !strcmp( type_str, "INTERSECT" ))
239                 type = QT_INTERSECT;
240         else if( !strcmp( type_str, "EXCEPT" ))
241                 type = QT_EXCEPT;
242         else {
243                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
244                         "Invalid query type \"%s\"", type_str ));
245                 return NULL;
246         }
247
248         int use_all             = oils_result_get_bool_idx( result, 3 );
249         int use_distinct        = oils_result_get_bool_idx( result, 4 );
250
251         int from_clause_id;
252         if( dbi_result_field_is_null_idx( result, 5 ) )
253                 from_clause_id = -1;
254         else
255                 from_clause_id = dbi_result_get_int_idx( result, 5 );
256
257         int where_clause_id;
258         if( dbi_result_field_is_null_idx( result, 6 ) )
259                 where_clause_id = -1;
260         else
261                 where_clause_id = dbi_result_get_int_idx( result, 6 );
262
263         int having_clause_id;
264         if( dbi_result_field_is_null_idx( result, 7 ) )
265                 having_clause_id = -1;
266         else
267                 having_clause_id = dbi_result_get_int_idx( result, 7 );
268
269         FromRelation* from_clause = NULL;
270         if( QT_SELECT == type ) {
271                 // A SELECT query needs a FROM clause; go get it
272                 if( from_clause_id != -1 ) {
273                         from_clause = getFromRelation( state, from_clause_id );
274                         if( !from_clause ) {
275                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
276                                         "Unable to construct FROM clause for id = %d", from_clause_id ));
277                                 return NULL;
278                         }
279                 }
280         } else {
281                 // Must be one of UNION, INTERSECT, or EXCEPT
282                 if( from_clause_id != -1 )
283                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
284                                 "FROM clause found and ignored for %s query in query #%d", type_str, id ));
285         }
286
287         // If this is a SELECT query, we need a SELECT list.  Go get one.
288         SelectItem* select_list = NULL;
289         QSeq* child_list = NULL;
290         if( QT_SELECT == type ) {
291                 select_list = getSelectList( state, id );
292                 if( !select_list ) {
293                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
294                                 "No SELECT list found for query id = %d", id ));
295                         fromRelationFree( from_clause );
296                         return NULL;
297                 }
298         } else {
299                 // Construct child queries of UNION, INTERSECT, or EXCEPT query
300                 child_list = loadChildQueries( state, id, type_str );
301                 if( !child_list ) {
302                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
303                                 "Unable to load child queries for %s query # %d", type_str, id ));
304                         state->error = 1;
305                         fromRelationFree( from_clause );
306                         return NULL;
307                 }
308         }
309
310         // Get the WHERE clause, if there is one
311         Expression* where_clause = NULL;
312         if( where_clause_id != -1 ) {
313                 where_clause = getExpression( state, where_clause_id );
314                 if( ! where_clause ) {
315                         // shouldn't happen due to foreign key constraint
316                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
317                                 "Unable to fetch WHERE expression for query id = %d", id ));
318                         freeQSeqList( child_list );
319                         fromRelationFree( from_clause );
320                         selectListFree( select_list );
321                         state->error = 1;
322                         return NULL;
323                 }
324         }
325
326         Expression* having_clause = NULL;
327         if( having_clause_id != -1 ) {
328                 having_clause = getExpression( state, having_clause_id );
329                 if( ! having_clause ) {
330                         // shouldn't happen due to foreign key constraint
331                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
332                                 "Unable to fetch HAVING expression for query id = %d", id ));
333                         expressionFree( where_clause );
334                         freeQSeqList( child_list );
335                         fromRelationFree( from_clause );
336                         selectListFree( select_list );
337                         state->error = 1;
338                         return NULL;
339                 }
340         }
341
342         // Get the ORDER BY clause, if there is one
343         OrderItem* order_by_list = getOrderByList( state, id );
344         if( state->error ) {
345                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
346                         "Unable to load ORDER BY clause for query %d", id ));
347                 expressionFree( having_clause );
348                 expressionFree( where_clause );
349                 freeQSeqList( child_list );
350                 fromRelationFree( from_clause );
351                 selectListFree( select_list );
352                 return NULL;
353         }
354
355         // Allocate a StoredQ: from the free list if possible, from the heap if necessary
356
357         StoredQ* sq;
358         if( free_storedq_list ) {
359                 sq = free_storedq_list;
360                 free_storedq_list = free_storedq_list->next;
361         } else
362                 sq = safe_malloc( sizeof( StoredQ ) );
363
364         // Populate the StoredQ
365         sq->next = NULL;
366         sq->id = id;
367
368         sq->type = type;
369         sq->use_all = use_all;
370         sq->use_distinct = use_distinct;
371         sq->from_clause = from_clause;
372         sq->where_clause = where_clause;
373         sq->select_list = select_list;
374         sq->child_list = child_list;
375         sq->having_clause = having_clause;
376         sq->order_by_list = order_by_list;
377
378         return sq;
379 }
380
381 /**
382         @brief Load the child queries subordinate to a UNION, INTERSECT, or EXCEPT query.
383         @param state Pointer to the query-building context.
384         @param parent ID of the UNION, INTERSECT, or EXCEPT query.
385         @param type_str The type of the query ("UNION", "INTERSECT", or "EXCEPT").
386         @return If successful, a pointer to a linked list of QSeq, each bearing a pointer to a
387                 StoredQ; otherwise NULL.
388
389         The @a type_str parameter is used only for building error messages.
390 */
391 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) {
392         QSeq* child_list = NULL;
393
394         // The ORDER BY is in descending order so that we can build the list by adding to
395         // the head, and it will wind up in the right order.
396         dbi_result result = dbi_conn_queryf( state->dbhandle,
397                 "SELECT id, parent_query, seq_no, child_query "
398                 "FROM query.query_sequence WHERE parent_query = %d ORDER BY seq_no DESC", parent_id );
399         if( result ) {
400                 if( dbi_result_first_row( result ) ) {
401                         int count = 0;
402                         while( 1 ) {
403                                 ++count;
404                                 QSeq* seq = constructQSeq( state, result );
405                                 if( seq ) {
406                                         PRINT( "Found a child query\n" );
407                                         PRINT( "\tid: %d\n", seq->id );
408                                         PRINT( "\tparent id: %d\n", seq->parent_query_id );
409                                         PRINT( "\tseq_no: %d\n", seq->seq_no );
410                                         // Add to the head of the list
411                                         seq->next = child_list;
412                                         child_list = seq;
413                                 } else{
414                                         freeQSeqList( child_list );
415                                         return NULL;
416                                 }
417                                 if( !dbi_result_next_row( result ))
418                                         break;
419                         }
420                         if( count < 2 ) {
421                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
422                                         "%s query # %d has only one child query", type_str, parent_id ));
423                                 state->error = 1;
424                                 freeQSeqList( child_list );
425                                 return NULL;
426                         }
427                 } else {
428                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
429                                 "%s query # %d has no child queries within it", type_str, parent_id ));
430                         state->error = 1;
431                         return NULL;
432                 }
433         } else {
434                 const char* msg;
435                 int errnum = dbi_conn_error( state->dbhandle, &msg );
436                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
437                         "Unable to query query.query_sequence table: # %d %s",
438                         errnum, msg ? msg : "No description available" ));
439                 state->error = 1;
440                 return NULL;
441         }
442
443         return child_list;
444 }
445
446 /**
447         @brief Construct a QSeq.
448         @param Pointer to the query-building context.
449         @param result Database cursor positioned at a row in query.query_sequence.
450         @return Pointer to a newly constructed QSeq, if successful, or NULL if not.
451
452         The calling code is responsible for freeing QSeqs by calling freeQSeqList().
453 */
454 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result ) {
455         int id = dbi_result_get_int_idx( result, 1 );
456         int parent_query_id = dbi_result_get_int_idx( result, 2 );
457         int seq_no = dbi_result_get_int_idx( result, 3 );
458         int child_query_id = dbi_result_get_int_idx( result, 4 );
459
460         StoredQ* child_query = getStoredQuery( state, child_query_id );
461         if( !child_query ) {
462                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
463                         "Unable to load child query # %d for parent query %d",
464                         child_query_id, parent_query_id ));
465                 state->error = 1;
466                 return NULL;
467         }
468
469         // Allocate a QSeq; from the free list if possible, from the heap if necessary
470         QSeq* seq = NULL;
471         if( free_qseq_list ) {
472                 seq = free_qseq_list;
473                 free_qseq_list = free_qseq_list->next;
474         } else
475                 seq = safe_malloc( sizeof( QSeq ));
476
477         seq->next            = NULL;
478         seq->id              = id;
479         seq->parent_query_id = parent_query_id;
480         seq->seq_no          = seq_no;
481         seq->child_query     = child_query;
482
483         return seq;
484 }
485
486 /**
487         @brief Free a list of QSeq's.
488         @param seq Pointer to the first in a linked list of QSeq's to be freed.
489
490         Each QSeq goes onto a free list for potential reuse.
491 */
492 static void freeQSeqList( QSeq* seq ) {
493         if( !seq )
494                 return;
495
496         QSeq* first = seq;
497         while( seq ) {
498                 storedQFree( seq->child_query );
499                 seq->child_query = NULL;
500
501                 if( seq->next )
502                         seq = seq->next;
503                 else {
504                         seq->next = free_qseq_list;
505                         seq = NULL;
506                 }
507         }
508
509         free_qseq_list = first;
510 }
511
512 /**
513         @brief Deallocate the memory owned by a StoredQ.
514         @param sq Pointer to the StoredQ to be deallocated.
515 */
516 void storedQFree( StoredQ* sq ) {
517         if( sq ) {
518                 fromRelationFree( sq->from_clause );
519                 sq->from_clause = NULL;
520                 selectListFree( sq->select_list );
521                 sq->select_list = NULL;
522                 expressionFree( sq->where_clause );
523                 sq->where_clause = NULL;
524                 if( sq->child_list ) {
525                         freeQSeqList( sq->child_list );
526                         sq->child_list = NULL;
527                 }
528                 if( sq->order_by_list ) {
529                         orderItemListFree( sq->order_by_list );
530                         sq->order_by_list = NULL;
531                 }
532                 if( sq->having_clause )
533                         expressionFree( sq->having_clause );
534
535                 // Stick the empty husk on the free list for potential reuse
536                 sq->next = free_storedq_list;
537                 free_storedq_list = sq;
538         }
539 }
540
541 /**
542         @brief Given an id from query.from_relation, load a FromRelation.
543         @param state Pointer the the query-building context.
544         @param id Id of the FromRelation.
545         @return Pointer to a newly-created FromRelation if successful, or NULL if not.
546
547         The calling code is responsible for freeing the new FromRelation by calling
548         fromRelationFree().
549 */
550 static FromRelation* getFromRelation( BuildSQLState* state, int id ) {
551         FromRelation* fr = NULL;
552         dbi_result result = dbi_conn_queryf( state->dbhandle,
553                 "SELECT id, type, table_name, class_name, subquery, function_call, "
554                 "table_alias, parent_relation, seq_no, join_type, on_clause "
555                 "FROM query.from_relation WHERE id = %d;", id );
556         if( result ) {
557                 if( dbi_result_first_row( result ) ) {
558                         fr = constructFromRelation( state, result );
559                         if( fr ) {
560                                 PRINT( "Got a from_relation row\n" );
561                                 PRINT( "\tid: %d\n", fr->id );
562                                 PRINT( "\ttype: %d\n", (int) fr->type );
563                                 PRINT( "\ttable_name: %s\n", fr->table_name ? fr->table_name : "(none)" );
564                                 PRINT( "\tclass_name: %s\n", fr->class_name ? fr->class_name : "(none)" );
565                                 PRINT( "\tsubquery_id: %d\n", fr->subquery_id );
566                                 PRINT( "\tfunction_call_id: %d\n", fr->function_call_id );
567                                 PRINT( "\ttable_alias: %s\n", fr->table_alias ? fr->table_alias : "(none)" );
568                                 PRINT( "\tparent_relation_id: %d\n", fr->parent_relation_id );
569                                 PRINT( "\tseq_no: %d\n", fr->seq_no );
570                                 PRINT( "\tjoin_type = %d\n", fr->join_type );
571                                 // Check the stack to see if the current from clause is nested inside itself.
572                                 // If it is, then abort in order to avoid infinite recursion.  If it isn't,
573                                 // then add it to the stack.  (Make sure to pop it off the stack before
574                                 // returning.)
575                                 const char* effective_alias = fr->table_alias;
576                                 if( !effective_alias )
577                                         effective_alias = fr->class_name;
578                                 const IdNode* node = searchIdStack( state->from_stack, id, effective_alias );
579                                 if( node ) {
580                                         if( node->id == id )
581                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
582                                                         "Infinite recursion detected; from clause # %d is nested "
583                                                         "within itself", id ));
584                                         else
585                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
586                                                         "Conflicting nested table aliases \"%s\" in from clause # %d",
587                                                         effective_alias, node->id ));
588                                         state->error = 1;
589                                         return NULL;
590                                 } else
591                                         push_id( &state->from_stack, id, effective_alias );
592                         } else
593                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
594                                         "Unable to build a FromRelation for id = %d", id ));
595                 } else {
596                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
597                                 "FROM relation not found for id = %d", id ));
598                         state->error = 1;
599                 }
600                 dbi_result_free( result );
601         } else {
602                 const char* msg;
603                 int errnum = dbi_conn_error( state->dbhandle, &msg );
604                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
605                         "Unable to query query.from_relation table: #%d %s",
606                         errnum, msg ? msg : "No description available" ));
607                 state->error = 1;
608         }
609
610         if( fr )
611                 pop_id( &state->from_stack );
612
613         return fr;
614 }
615
616 /**
617         @brief Construct a FromRelation.
618         @param Pointer to the query-building context.
619         @param result Database cursor positioned at a row in query.from_relation.
620         @return Pointer to a newly constructed FromRelation, if successful, or NULL if not.
621
622         The calling code is responsible for freeing FromRelations by calling joinListFree().
623 */
624 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result ) {
625         // Get the column values from the result
626         int id                  = dbi_result_get_int_idx( result, 1 );
627         const char* type_str    = dbi_result_get_string_idx( result, 2 );
628
629         FromRelationType type;
630         if( !strcmp( type_str, "RELATION" ))
631                 type = FRT_RELATION;
632         else if( !strcmp( type_str, "SUBQUERY" ))
633                 type = FRT_SUBQUERY;
634         else if( !strcmp( type_str, "FUNCTION" ))
635                 type = FRT_FUNCTION;
636         else
637                 type = FRT_RELATION;     // shouldn't happen due to database constraint
638
639         const char* table_name   = dbi_result_get_string_idx( result, 3 );
640         const char* class_name   = dbi_result_get_string_idx( result, 4 );
641
642         int subquery_id;
643         if( dbi_result_field_is_null_idx( result, 5 ) )
644                 subquery_id           = -1;
645         else
646                 subquery_id           = dbi_result_get_int_idx( result, 5 );
647
648         int function_call_id;
649         if( dbi_result_field_is_null_idx( result, 6 ) )
650                 function_call_id      = -1;
651         else
652                 function_call_id      = dbi_result_get_int_idx( result, 6 );
653
654         Expression* function_call = NULL;
655         const char* table_alias   = dbi_result_get_string_idx( result, 7 );
656
657         int parent_relation_id;
658         if( dbi_result_field_is_null_idx( result, 8 ) )
659                 parent_relation_id    = -1;
660         else
661                 parent_relation_id    = dbi_result_get_int_idx( result, 8 );
662
663         int seq_no                = dbi_result_get_int_idx( result, 9 );
664
665         JoinType join_type;
666         const char* join_type_str = dbi_result_get_string_idx( result, 10 );
667         if( !join_type_str )
668                 join_type = JT_NONE;
669         else if( !strcmp( join_type_str, "INNER" ) )
670                 join_type = JT_INNER;
671         else if( !strcmp( join_type_str, "LEFT" ) )
672                 join_type = JT_LEFT;
673         else if( !strcmp( join_type_str, "RIGHT" ) )
674                 join_type = JT_RIGHT;
675         else if( !strcmp( join_type_str, "FULL" ) )
676                 join_type = JT_FULL;
677         else
678                 join_type = JT_NONE;     // shouldn't happen due to database constraint
679
680         int on_clause_id;
681         if( dbi_result_field_is_null_idx( result, 11 ) )
682                 on_clause_id   = -1;
683         else
684                 on_clause_id   = dbi_result_get_int_idx( result, 11 );
685
686         StoredQ* subquery = NULL;
687
688         switch ( type ) {
689                 case FRT_RELATION :
690                         if( table_name && ! is_identifier( table_name )) {
691                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
692                                         "Table name \"%s\" is not a valid identifier for FROM relation # %d",
693                                         table_name, id ));
694                                 state->error = 1;
695                                 return NULL;
696                         }
697                         break;
698                 case FRT_SUBQUERY :
699                         if( -1 == subquery_id ) {
700                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
701                                         "Internal error: no subquery specified for FROM relation # %d", id ));
702                                 state->error = 1;
703                                 return NULL;
704                         }
705                         if( ! table_alias ) {
706                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
707                                         "Subquery needs alias in FROM relation # %d", id ));
708                                 state->error = 1;
709                                 return NULL;
710                         }
711                         subquery = getStoredQuery( state, subquery_id );
712                         if( ! subquery ) {
713                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
714                                         "Unable to load subquery for FROM relation # %d", id ));
715                                 state->error = 1;
716                                 return NULL;
717                         }
718                         break;
719                 case FRT_FUNCTION :
720                         if( -1 == function_call_id ) {
721                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
722                                         "FROM clause # %d purports to reference a function; not identified", id ));
723                                 state->error = 1;
724                                 return NULL;
725                         }
726
727                         function_call = getExpression( state, function_call_id );
728                         if( !function_call ) {
729                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
730                                         "Unable to build function call # %d in FROM relation # %d",
731                                         function_call_id, id ));
732                                 state->error = 1;
733                                 return NULL;
734                         } else if( function_call->type != EXP_FUNCTION ) {
735                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
736                                         "In FROM relation # %d: supposed function call expression # %d "
737                                         "is not a function call", id, function_call_id ));
738                                 state->error = 1;
739                                 return NULL;
740                         }
741         }
742
743         FromRelation* join_list = getJoinList( state, id );
744         if( state->error ) {
745                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
746                         "Unable to load join list for FROM relation # %d", id ));
747                 return NULL;
748         }
749
750         Expression* on_clause = NULL;
751         if( on_clause_id != -1 ) {
752                 on_clause = getExpression( state, on_clause_id );
753                 if( !on_clause ) {
754                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
755                                 "Unable to load ON condition for FROM relation # %d", id ));
756                         joinListFree( join_list );
757                         state->error = 1;
758                         return NULL;
759                 }
760                 else
761                         PRINT( "\tGot an ON condition\n" );
762         }
763
764         // Allocate a FromRelation: from the free list if possible, from the heap if necessary
765
766         FromRelation* fr;
767         if( free_from_relation_list ) {
768                 fr = free_from_relation_list;
769                 free_from_relation_list = free_from_relation_list->next;
770         } else
771                 fr = safe_malloc( sizeof( FromRelation ) );
772
773         // Populate the FromRelation
774
775         fr->next = NULL;
776         fr->id = id;
777         fr->type = type;
778         fr->table_name = table_name ? strdup( table_name ) : NULL;
779         fr->class_name = class_name ? strdup( class_name ) : NULL;
780         fr->subquery_id = subquery_id;
781         fr->subquery = subquery;
782         fr->function_call_id = function_call_id;
783         fr->function_call = function_call;
784         fr->table_alias = table_alias ? strdup( table_alias ) : NULL;
785         fr->parent_relation_id = parent_relation_id;
786         fr->seq_no = seq_no;
787         fr->join_type = join_type;
788         fr->on_clause = on_clause;
789         fr->join_list = join_list;
790
791         return fr;
792 }
793
794 /**
795         @brief Build a list of joined relations.
796         @param state Pointer to the query-building context.
797         @param id ID of the parent relation.
798         @return A pointer to the first in a linked list of FromRelations, if there are any; or
799                 NULL if there aren't any, or in case of an error.
800
801         Look for relations joined directly to the parent relation, and make a list of them.
802 */
803 static FromRelation* getJoinList( BuildSQLState* state, int id ) {
804         FromRelation* join_list = NULL;
805
806         // The ORDER BY is in descending order so that we can build the list by adding to
807         // the head, and it will wind up in the right order.
808         dbi_result result = dbi_conn_queryf( state->dbhandle,
809                 "SELECT id, type, table_name, class_name, subquery, function_call, "
810                 "table_alias, parent_relation, seq_no, join_type, on_clause "
811                 "FROM query.from_relation WHERE parent_relation = %d ORDER BY seq_no DESC", id );
812
813         if( result ) {
814                 if( dbi_result_first_row( result ) ) {
815                         while( 1 ) {
816                                 FromRelation* relation = constructFromRelation( state, result );
817                                 if( relation ) {
818                                         PRINT( "Found a joined relation\n" );
819                                         PRINT( "\tjoin_type: %d\n", relation->join_type );
820                                         PRINT( "\ttable_name: %s\n", relation->table_name );
821                                         relation->next = join_list;
822                                         join_list = relation;
823                                 } else {
824                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
825                                                 "Unable to build join list for from relation id #%d", id ));
826                                         joinListFree( join_list );
827                                         join_list = NULL;
828                                         break;
829                                 }
830                                 if( !dbi_result_next_row( result ) )
831                                         break;
832                         };
833                 }
834         } else {
835                 const char* msg;
836                 int errnum = dbi_conn_error( state->dbhandle, &msg );
837                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
838                         "Unable to query query.from_relation table for join list: #%d %s",
839                         errnum, msg ? msg : "No description available" ));
840                 state->error = 1;
841         }
842
843         return join_list;
844 }
845
846 /**
847         @brief Free a list of FromRelations.
848         @param join_list Pointer to the first FromRelation in the list.
849 */
850 static void joinListFree( FromRelation* join_list ) {
851         while( join_list ) {
852                 FromRelation* temp = join_list->next;
853                 fromRelationFree( join_list );
854                 join_list = temp;
855         }
856 }
857
858 /**
859         @brief Deallocate a FromRelation.
860         @param fr Pointer to the FromRelation to be freed.
861
862         Free the strings that the FromRelation owns.  The FromRelation itself goes onto a
863         free list for potential reuse.
864 */
865 static void fromRelationFree( FromRelation* fr ) {
866         if( fr ) {
867                 free( fr->table_name );
868                 fr->table_name = NULL;
869                 free( fr->class_name );
870                 fr->class_name = NULL;
871                 if( fr->subquery ) {
872                         storedQFree( fr->subquery );
873                         fr->subquery = NULL;
874                 }
875                 if( fr->function_call ) {
876                         expressionFree( fr->function_call );
877                         fr->function_call = NULL;
878                 }
879                 free( fr->table_alias );
880                 fr->table_alias = NULL;
881                 if( fr->on_clause ) {
882                         expressionFree( fr->on_clause );
883                         fr->on_clause = NULL;
884                 }
885                 joinListFree( fr->join_list );
886                 fr->join_list = NULL;
887
888                 fr->next = free_from_relation_list;
889                 free_from_relation_list = fr;
890         }
891 }
892
893 /**
894         @brief Build a SELECT list for a given query ID.
895         @param state Pointer to the query-building context.
896         @param query_id ID of the query to which the SELECT list belongs.
897 */
898 static SelectItem* getSelectList( BuildSQLState* state, int query_id ) {
899         SelectItem* select_list = NULL;
900
901         // The ORDER BY is in descending order so that we can build the list by adding to
902         // the head, and it will wind up in the right order.
903         dbi_result result = dbi_conn_queryf( state->dbhandle,
904                 "SELECT id, stored_query, seq_no, expression, column_alias, grouped_by "
905                 "FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
906         if( result ) {
907                 if( dbi_result_first_row( result ) ) {
908                         while( 1 ) {
909                                 SelectItem* item = constructSelectItem( state, result );
910                                 if( item ) {
911                                         PRINT( "Found a SELECT item\n" );
912                                         PRINT( "\tid: %d\n", item->id );
913                                         PRINT( "\tstored_query_id: %d\n", item->stored_query_id );
914                                         PRINT( "\tseq_no: %d\n", item->seq_no );
915                                         PRINT( "\tcolumn_alias: %s\n",
916                                                         item->column_alias ? item->column_alias : "(none)" );
917                                         PRINT( "\tgrouped_by: %d\n", item->grouped_by );
918
919                                         item->next = select_list;
920                                         select_list = item;
921                                 } else {
922                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
923                                                 "Unable to build select list for query id #%d", query_id ));
924                                         selectListFree( select_list );
925                                         select_list = NULL;
926                                         break;
927                                 }
928                                 if( !dbi_result_next_row( result ) )
929                                         break;
930                         };
931                 } else {
932                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
933                                 "No SELECT list found for query # %d", query_id ));
934                         state->error = 1;
935                 }
936         } else {
937                 const char* msg;
938                 int errnum = dbi_conn_error( state->dbhandle, &msg );
939                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
940                         "Unable to query query.select_list table: #%d %s",
941                         errnum, msg ? msg : "No description available" ));
942                 state->error = 1;
943         }
944
945         return select_list;
946 }
947
948 /**
949         @brief Construct a SelectItem.
950         @param Pointer to the query-building context.
951         @param result Database cursor positioned at a row in query.select_item.
952         @return Pointer to a newly constructed SelectItem, if successful, or NULL if not.
953
954         The calling code is responsible for freeing the SelectItems by calling selectListFree().
955 */
956 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result ) {
957
958         // Get the column values
959         int id                   = dbi_result_get_int_idx( result, 1 );
960         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
961         int seq_no               = dbi_result_get_int_idx( result, 3 );
962         int expression_id        = dbi_result_get_int_idx( result, 4 );
963         const char* column_alias = dbi_result_get_string_idx( result, 5 );
964         int grouped_by           = oils_result_get_bool_idx( result, 6 );
965
966         // Construct an Expression
967         Expression* expression = getExpression( state, expression_id );
968         if( !expression ) {
969                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
970                         "Unable to fetch expression for id = %d", expression_id ));
971                 state->error = 1;
972                 return NULL;
973         };
974
975         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
976
977         SelectItem* sel;
978         if( free_select_item_list ) {
979                 sel = free_select_item_list;
980                 free_select_item_list = free_select_item_list->next;
981         } else
982                 sel = safe_malloc( sizeof( SelectItem ) );
983
984         sel->next            = NULL;
985         sel->id              = id;
986         sel->stored_query_id = stored_query_id;
987         sel->seq_no          = seq_no;
988         sel->expression      = expression;
989         sel->column_alias    = column_alias ? strdup( column_alias ) : NULL;
990         sel->grouped_by      = grouped_by;
991
992         return sel;
993 }
994
995 /**
996         @brief Free a list of SelectItems.
997         @param sel Pointer to the first item in the list to be freed.
998
999         Free the column alias and expression owned by each item.  Put the entire list into a free
1000         list of SelectItems.
1001 */
1002 static void selectListFree( SelectItem* sel ) {
1003         if( !sel )
1004                 return;    // Nothing to free
1005
1006         SelectItem* first = sel;
1007         while( 1 ) {
1008                 free( sel->column_alias );
1009                 sel->column_alias = NULL;
1010                 expressionFree( sel->expression );
1011                 sel->expression = NULL;
1012
1013                 if( NULL == sel->next ) {
1014                         sel->next = free_select_item_list;
1015                         break;
1016                 } else
1017                         sel = sel->next;
1018         };
1019
1020         // Transfer the entire list to the free list
1021         free_select_item_list = first;
1022 }
1023
1024 /**
1025         @brief Given the name of a bind variable, build a corresponding BindVar.
1026         @param state Pointer to the query-building context.
1027         @param name Name of the bind variable.
1028         @return Pointer to the newly-built BindVar.
1029
1030         Since the same bind variable may appear multiple times, we load it only once for the
1031         entire query, and reference the one copy wherever needed.
1032 */
1033 static BindVar* getBindVar( BuildSQLState* state, const char* name ) {
1034         BindVar* bind = NULL;
1035         if( state->bindvar_list ) {
1036                 bind = osrfHashGet( state->bindvar_list, name );
1037                 if( bind )
1038                         return bind;   // Already loaded it...
1039         }
1040
1041         // Load a BindVar from the Database.(after escaping any special characters)
1042         char* esc_str = strdup( name );
1043         dbi_conn_quote_string( state->dbhandle, &esc_str );
1044         if( !esc_str ) {
1045                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1046                         "Unable to format bind variable name \"%s\"", name ));
1047                 state->error = 1;
1048                 return NULL;
1049         }
1050         dbi_result result = dbi_conn_queryf( state->dbhandle,
1051                 "SELECT name, type, description, default_value, label "
1052                 "FROM query.bind_variable WHERE name = %s;", esc_str );
1053         free( esc_str );
1054         if( result ) {
1055                 if( dbi_result_first_row( result ) ) {
1056                         bind = constructBindVar( state, result );
1057                         if( bind ) {
1058                                 PRINT( "Got a bind variable for %s\n", name );
1059                         } else
1060                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1061                                         "Unable to load bind variable \"%s\"", name ));
1062                 } else {
1063                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1064                                 "No bind variable found with name \"%s\"", name ));
1065                         state->error = 1;
1066                 }
1067         } else {
1068                 const char* msg;
1069                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1070                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1071                         "Unable to query query.bind_variable table for \"%s\": #%d %s",
1072                         name, errnum, msg ? msg : "No description available" ));
1073                 state->error = 1;
1074         }
1075
1076         if( bind ) {
1077                 // Add the new bind variable to the list
1078                 if( !state->bindvar_list ) {
1079                         // Don't have a list yet?  Start one.
1080                         state->bindvar_list = osrfNewHash();
1081                         osrfHashSetCallback( state->bindvar_list, bindVarFree );
1082                 }
1083                 osrfHashSet( state->bindvar_list, bind, name );
1084         } else
1085                 state->error = 1;
1086
1087         return bind;
1088 }
1089
1090 /**
1091         @brief Construct a BindVar to represent a bind variable.
1092         @param Pointer to the query-building context.
1093         @param result Database cursor positioned at a row in query.bind_variable.
1094         @return Pointer to a newly constructed BindVar, if successful, or NULL if not.
1095
1096         The calling code is responsible for freeing the BindVar by calling bindVarFree().
1097 */
1098 static BindVar* constructBindVar( BuildSQLState* state, dbi_result result ) {
1099
1100         const char* name = dbi_result_get_string_idx( result, 1 );
1101
1102         const char* type_str = dbi_result_get_string_idx( result, 2 );
1103         BindVarType type;
1104         if( !strcmp( type_str, "string" ))
1105                 type = BIND_STR;
1106         else if( !strcmp( type_str, "number" ))
1107                 type = BIND_NUM;
1108         else if( !strcmp( type_str, "string_list" ))
1109                 type = BIND_STR_LIST;
1110         else if( !strcmp( type_str, "number_list" ))
1111                 type = BIND_NUM_LIST;
1112         else {         // Shouldn't happen due to database constraint...
1113                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1114                         "Internal error: invalid bind variable type \"%s\" for bind variable \"%s\"",
1115                         type_str, name ));
1116                 state->error = 1;
1117                 return NULL;
1118         }
1119
1120         const char* description = dbi_result_get_string_idx( result, 3 );
1121
1122         // The default value is encoded as JSON.  Translate it into a jsonObject.
1123         const char* default_value_str = dbi_result_get_string_idx( result, 4 );
1124         jsonObject* default_value = NULL;
1125         if( default_value_str ) {
1126                 default_value = jsonParse( default_value_str );
1127                 if( !default_value ) {
1128                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1129                                 "Unable to parse JSON string for default value of bind variable \"%s\"",
1130                                 name ));
1131                         state->error = 1;
1132                         return NULL;
1133                 }
1134         }
1135
1136         const char* label = dbi_result_get_string_idx( result, 5 );
1137
1138         // Allocate a BindVar: from the free list if possible, from the heap if necessary
1139         BindVar* bind = NULL;
1140         if( free_bindvar_list ) {
1141                 bind = free_bindvar_list;
1142                 free_bindvar_list = free_bindvar_list->next;
1143         } else
1144                 bind = safe_malloc( sizeof( BindVar ) );
1145
1146         bind->next = NULL;
1147         bind->name = strdup( name );
1148         bind->label = strdup( label );
1149         bind->type = type;
1150         bind->description = strdup( description );
1151         bind->default_value = default_value;
1152         bind->actual_value = NULL;
1153
1154         return bind;
1155 }
1156
1157 /**
1158         @brief Deallocate a BindVar.
1159         @param key Pointer to the bind variable name (not used).
1160         @param p Pointer to the BindVar to be deallocated, cast to a void pointer.
1161
1162         Free the strings and jsonObjects owned by the BindVar, and put the BindVar itself into a
1163         free list.
1164
1165         This function is a callback installed in an osrfHash; hence the peculiar signature.
1166 */
1167 static void bindVarFree( char* key, void* p ) {
1168         if( p ) {
1169                 BindVar* bind = p;
1170                 free( bind->name );
1171                 free( bind->label );
1172                 free( bind->description );
1173                 if( bind->default_value ) {
1174                         jsonObjectFree( bind->default_value );
1175                         bind->default_value = NULL;
1176                 }
1177                 if( bind->actual_value ) {
1178                         jsonObjectFree( bind->actual_value );
1179                         bind->actual_value = NULL;
1180                 }
1181
1182                 // Prepend to free list
1183                 bind->next = free_bindvar_list;
1184                 free_bindvar_list = bind;
1185         }
1186 }
1187
1188 /**
1189         @brief Given an id for a parent expression, build a list of CaseBranch structs.
1190         @param Pointer to the query-building context.
1191         @param id ID of a row in query.case_branch.
1192         @return Pointer to a newly-created CaseBranch if successful, or NULL if not.
1193 */
1194 static CaseBranch* getCaseBranchList( BuildSQLState* state, int parent_id ) {
1195         CaseBranch* branch_list = NULL;
1196         dbi_result result = dbi_conn_queryf( state->dbhandle,
1197                 "SELECT id, condition, result "
1198                 "FROM query.case_branch WHERE parent_expr = %d "
1199                 "ORDER BY seq_no desc;", parent_id );
1200         if( result ) {
1201                 int condition_found = 0;   // Boolean
1202                 if( dbi_result_first_row( result ) ) {
1203                         // Boolean: true for the first branch we encounter.  That's actually the last
1204                         // branch in the CASE, because we're reading them in reverse order.  The point
1205                         // is to enforce the rule that only the last branch can be an ELSE.
1206                         int first = 1;
1207                         while( 1 ) {
1208                                 CaseBranch* branch = constructCaseBranch( state, result );
1209                                 if( branch ) {
1210                                         PRINT( "Found a case branch\n" );
1211                                         branch->next = branch_list;
1212                                         branch_list  = branch;
1213                                 } else {
1214                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1215                                                 "Unable to build CASE branch for expression id #%d", parent_id ));
1216                                         freeBranchList( branch_list );
1217                                         branch_list = NULL;
1218                                         break;
1219                                 }
1220
1221                                 if( branch->condition )
1222                                         condition_found = 1;
1223                                 else if( !first ) {
1224                                         sqlAddMsg( state, "ELSE branch # %d not at end of CASE expression # %d",
1225                                                 branch->id, parent_id );
1226                                         freeBranchList( branch_list );
1227                                         branch_list = NULL;
1228                                         break;
1229                                 }
1230                                 first = 0;
1231
1232                                 if( !dbi_result_next_row( result ) )
1233                                         break;
1234                         };  // end while
1235                 } else {
1236                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1237                                 "No branches found for CASE expression %d", parent_id ));
1238                         state->error = 1;
1239                 }
1240
1241                 // Make sure that at least one branch includes a condition
1242                 if( !condition_found ) {
1243                         sqlAddMsg( state, "No conditional branch in CASE expression # %d", parent_id );
1244                         freeBranchList( branch_list );
1245                         branch_list = NULL;
1246                 }
1247         } else {
1248                 const char* msg;
1249                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1250                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1251                         "Unable to query query.case_branch table for parent expression # %d: %s",
1252                         parent_id, errnum, msg ? msg : "No description available" ));
1253                 state->error = 1;
1254         }
1255
1256         return branch_list;
1257 }
1258
1259 static CaseBranch* constructCaseBranch( BuildSQLState* state, dbi_result result ) {
1260         int id = dbi_result_get_int_idx( result, 1 );
1261
1262         int condition_id;
1263         if( dbi_result_field_is_null_idx( result, 2 ))
1264                 condition_id = -1;
1265         else
1266                 condition_id = dbi_result_get_int_idx( result, 2 );
1267
1268         Expression* condition = NULL;
1269         if( condition_id != -1 ) {   // If it's -1, we have an ELSE condition
1270                 condition = getExpression( state, condition_id );
1271                 if( ! condition ) {
1272                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1273                                 "Unable to build condition expression for case branch # %d", id ));
1274                         state->error = 1;
1275                         return NULL;
1276                 }
1277         }
1278
1279         int result_id = dbi_result_get_int_idx( result, 3 );
1280
1281         Expression* result_p = NULL;
1282         if( -1 == result_id ) {
1283                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1284                         "No result expression defined for case branch # %d", id ));
1285                 state->error = 1;
1286                 if( condition )
1287                         expressionFree( condition );
1288                 return NULL;
1289         } else {
1290                 result_p = getExpression( state, result_id );
1291                 if( ! result_p ) {
1292                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1293                                 "Unable to build result expression for case branch # %d", id ));
1294                         state->error = 1;
1295                         if( condition )
1296                                 expressionFree( condition );
1297                         return NULL;
1298                 }
1299         }
1300
1301         // Allocate a CaseBranch: from the free list if possible, from the heap if necessary
1302         CaseBranch* branch = NULL;
1303         if( free_branch_list ) {
1304                 branch = free_branch_list;
1305                 free_branch_list = free_branch_list->next;
1306         } else
1307                 branch = safe_malloc( sizeof( CaseBranch ) );
1308
1309         // Populate the new CaseBranch
1310         branch->id = id;
1311         branch->condition = condition;
1312         branch->result = result_p;
1313
1314         return branch;
1315 }
1316
1317 /**
1318         @brief Free a list of CaseBranches.
1319         @param branch Pointer to the first in a linked list of CaseBranches to be freed.
1320
1321         Each CaseBranch goes onto a free list for potential reuse.
1322 */
1323 static void freeBranchList( CaseBranch* branch ) {
1324         if( !branch )
1325                 return;
1326
1327         CaseBranch* first = branch;
1328         while( branch ) {
1329                 if( branch->condition ) {
1330                         expressionFree( branch->condition );
1331                         branch->condition = NULL;
1332                 }
1333                 expressionFree( branch->result );
1334                 branch->result = NULL;
1335
1336                 if( branch->next )
1337                         branch = branch->next;
1338                 else {
1339                         branch->next = free_branch_list;
1340                         branch = NULL;
1341                 }
1342         }
1343
1344         free_branch_list = first;
1345 }
1346
1347 /**
1348         @brief Given an id for a row in query.datatype, build an Datatype struct.
1349         @param Pointer to the query-building context.
1350         @param id ID of a row in query.datatype.
1351         @return Pointer to a newly-created Datatype if successful, or NULL if not.
1352 */
1353 static Datatype* getDatatype( BuildSQLState* state, int id ) {
1354         Datatype* datatype = NULL;
1355         dbi_result result = dbi_conn_queryf( state->dbhandle,
1356                 "SELECT id, datatype_name, is_numeric, is_composite "
1357                 "FROM query.datatype WHERE id = %d", id );
1358         if( result ) {
1359                 if( dbi_result_first_row( result ) ) {
1360                         datatype = constructDatatype( state, result );
1361                         if( datatype ) {
1362                                 PRINT( "Got a datatype\n" );
1363                                 PRINT( "\tid = %d\n", id );
1364                                 PRINT( "\tdatatype = %s\n", datatype->datatype_name );
1365                         } else
1366                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1367                                         "Unable to construct a Datatype for id = %d", id ));
1368                 } else {
1369                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1370                                 "No datatype found for id = %d", id ));
1371                         state->error = 1;
1372                 }
1373         } else {
1374                 const char* msg;
1375                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1376                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1377                         "Unable to query query.datatype table: #%d %s",
1378                         errnum, msg ? msg : "No description available" ));
1379                 state->error = 1;
1380         }
1381         return datatype;
1382 }
1383
1384 /**
1385         @brief Construct a Datatype.
1386         @param Pointer to the query-building context.
1387         @param result Database cursor positioned at a row in query.datatype.
1388         @return Pointer to a newly constructed Datatype, if successful, or NULL if not.
1389
1390         The calling code is responsible for freeing the Datatype by calling datatypeFree().
1391 */
1392 static Datatype* constructDatatype( BuildSQLState* state, dbi_result result ) {
1393         int id           = dbi_result_get_int_idx( result, 1 );
1394         const char* datatype_name = dbi_result_get_string_idx( result, 2 );
1395         int is_numeric   = oils_result_get_bool_idx( result, 3 );
1396         int is_composite = oils_result_get_bool_idx( result, 4 );
1397
1398         if( !datatype_name || !*datatype_name ) {
1399                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1400                         "No datatype name provided for CAST expression # %d", id ));
1401                 state->error = 1;
1402                 return NULL;
1403         }
1404
1405         // Make sure that the datatype name is composed entirely of certain approved
1406         // characters.  This check is not an attempt to validate the datatype name, but
1407         // only to prevent certain types of SQL injection.
1408         const char* p = datatype_name;
1409         while( *p ) {
1410                 unsigned char c = *p;
1411                 if( isalnum( c )
1412                         || isspace( c )
1413                         || ',' == c
1414                         || '(' == c
1415                         || ')' == c
1416                         || '[' == c
1417                         || ']' == c
1418                         || '.' == c
1419                 )
1420                         ++p;
1421                 else {
1422                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1423                                 "Invalid datatype name \"%s\" for datatype # %d; "
1424                                 "contains unexpected character \"%c\"", datatype_name, id, (char) c ));
1425                         state->error = 1;
1426                         return NULL;
1427                 }
1428         }
1429
1430         // Allocate a Datatype: from the free list if possible, from the heap if necessary
1431         Datatype* datatype = NULL;
1432         if( free_datatype_list ) {
1433                 datatype = free_datatype_list;
1434                 free_datatype_list = free_datatype_list->next;
1435         } else
1436                 datatype = safe_malloc( sizeof( Datatype ) );
1437
1438         datatype->id            = id;
1439         datatype->datatype_name = strdup( datatype_name );
1440         datatype->is_numeric    = is_numeric;
1441         datatype->is_composite  = is_composite;
1442
1443         return datatype;
1444 }
1445
1446 /**
1447         @brief Free a Datatype.
1448         @param datatype Pointer to the Datatype to be freed.
1449 */
1450 static void datatypeFree( Datatype* datatype ) {
1451         if( datatype ) {
1452                 free( datatype->datatype_name );
1453                 free( datatype );
1454         }
1455 }
1456
1457 /**
1458         @brief Given an id for a row in query.expression, build an Expression struct.
1459         @param Pointer to the query-building context.
1460         @param id ID of a row in query.expression.
1461         @return Pointer to a newly-created Expression if successful, or NULL if not.
1462 */
1463 static Expression* getExpression( BuildSQLState* state, int id ) {
1464
1465         // Check the stack to see if the current expression is nested inside itself.  If it is,
1466         // then abort in order to avoid infinite recursion.  If it isn't, then add it to the
1467         // stack.  (Make sure to pop it off the stack before returning.)
1468         if( searchIdStack( state->expr_stack, id, NULL )) {
1469                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1470                         "Infinite recursion detected; expression # %d is nested within itself", id ));
1471                 state->error = 1;
1472                 return NULL;
1473         } else
1474                 push_id( &state->expr_stack, id, NULL );
1475
1476         Expression* exp = NULL;
1477         dbi_result result = dbi_conn_queryf( state->dbhandle,
1478                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
1479                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
1480                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
1481                 "func.function_name "
1482                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
1483                 "ON (exp.function_id = func.id) "
1484                 "WHERE exp.id = %d;", id );
1485         if( result ) {
1486                 if( dbi_result_first_row( result ) ) {
1487                         exp = constructExpression( state, result );
1488                         if( exp ) {
1489                                 PRINT( "Got an expression\n" );
1490                                 PRINT( "\tid = %d\n", exp->id );
1491                                 PRINT( "\ttype = %d\n", exp->type );
1492                                 PRINT( "\tparenthesize = %d\n", exp->parenthesize );
1493                                 PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" );
1494                         } else
1495                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1496                                         "Unable to construct an Expression for id = %d", id ));
1497                 } else {
1498                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1499                                 "No expression found for id = %d", id ));
1500                         state->error = 1;
1501                 }
1502         } else {
1503                 const char* msg;
1504                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1505                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1506                         "Unable to query query.expression table: #%d %s",
1507                         errnum, msg ? msg : "No description available" ));
1508                 state->error = 1;
1509         }
1510
1511         pop_id( &state->expr_stack );
1512         return exp;
1513 }
1514
1515 /**
1516         @brief Construct an Expression.
1517         @param Pointer to the query-building context.
1518         @param result Database cursor positioned at a row in query.expression.
1519         @return Pointer to a newly constructed Expression, if successful, or NULL if not.
1520
1521         The calling code is responsible for freeing the Expression by calling expressionFree().
1522 */
1523 static Expression* constructExpression( BuildSQLState* state, dbi_result result ) {
1524
1525         int id = dbi_result_get_int_idx( result, 1 );
1526         const char* type_str = dbi_result_get_string_idx( result, 2 );
1527
1528         ExprType type;
1529         if( !strcmp( type_str, "xbet" ))
1530                 type = EXP_BETWEEN;
1531         else if( !strcmp( type_str, "xbind" ))
1532                 type = EXP_BIND;
1533         else if( !strcmp( type_str, "xbool" ))
1534                 type = EXP_BOOL;
1535         else if( !strcmp( type_str, "xcase" ))
1536                 type = EXP_CASE;
1537         else if( !strcmp( type_str, "xcast" ))
1538                 type = EXP_CAST;
1539         else if( !strcmp( type_str, "xcol" ))
1540                 type = EXP_COLUMN;
1541         else if( !strcmp( type_str, "xex" ))
1542                 type = EXP_EXIST;
1543         else if( !strcmp( type_str, "xfld" ))
1544                 type = EXP_FIELD;
1545         else if( !strcmp( type_str, "xfunc" ))
1546                 type = EXP_FUNCTION;
1547         else if( !strcmp( type_str, "xin" ))
1548                 type = EXP_IN;
1549         else if( !strcmp( type_str, "xisnull" ))
1550                 type = EXP_ISNULL;
1551         else if( !strcmp( type_str, "xnull" ))
1552                 type = EXP_NULL;
1553         else if( !strcmp( type_str, "xnum" ))
1554                 type = EXP_NUMBER;
1555         else if( !strcmp( type_str, "xop" ))
1556                 type = EXP_OPERATOR;
1557         else if( !strcmp( type_str, "xser" ))
1558                 type = EXP_SERIES;
1559         else if( !strcmp( type_str, "xstr" ))
1560                 type = EXP_STRING;
1561         else if( !strcmp( type_str, "xsubq" ))
1562                 type = EXP_SUBQUERY;
1563         else
1564                 type = EXP_NULL;     // shouldn't happen due to database constraint
1565
1566         int parenthesize = oils_result_get_bool_idx( result, 3 );
1567
1568         int parent_expr_id;
1569         if( dbi_result_field_is_null_idx( result, 4 ))
1570                 parent_expr_id = -1;
1571         else
1572                 parent_expr_id = dbi_result_get_int_idx( result, 4 );
1573
1574         int seq_no = dbi_result_get_int_idx( result, 5 );
1575         const char* literal = dbi_result_get_string_idx( result, 6 );
1576         const char* table_alias = dbi_result_get_string_idx( result, 7 );
1577         const char* column_name = dbi_result_get_string_idx( result, 8 );
1578
1579         int left_operand_id;
1580         if( dbi_result_field_is_null_idx( result, 9 ))
1581                 left_operand_id = -1;
1582         else
1583                 left_operand_id = dbi_result_get_int_idx( result, 9 );
1584
1585         const char* operator = dbi_result_get_string_idx( result, 10 );
1586
1587         int right_operand_id;
1588         if( dbi_result_field_is_null_idx( result, 11 ))
1589                 right_operand_id = -1;
1590         else
1591                 right_operand_id = dbi_result_get_int_idx( result, 11 );
1592
1593         int subquery_id;
1594         if( dbi_result_field_is_null_idx( result, 12 ))
1595                 subquery_id = -1;
1596         else
1597                 subquery_id = dbi_result_get_int_idx( result, 12 );
1598
1599         int cast_type_id;
1600         if( dbi_result_field_is_null_idx( result, 13 ))
1601                 cast_type_id = -1;
1602         else
1603                 cast_type_id = dbi_result_get_int_idx( result, 13 );
1604
1605         int negate = oils_result_get_bool_idx( result, 14 );
1606         const char* bind_variable = dbi_result_get_string_idx( result, 15 );
1607         const char* function_name = dbi_result_get_string_idx( result, 16 );
1608
1609         Expression* left_operand = NULL;
1610         Expression* right_operand = NULL;
1611         StoredQ* subquery = NULL;
1612         Datatype* cast_type = NULL;
1613         BindVar* bind = NULL;
1614         CaseBranch* branch_list = NULL;
1615         Expression* subexp_list = NULL;
1616
1617         if( EXP_BETWEEN == type ) {
1618                 // Get the left operand
1619                 if( -1 == left_operand_id ) {
1620                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1621                                 "No left operand defined for BETWEEN expression # %d", id ));
1622                         state->error = 1;
1623                         return NULL;
1624                 } else {
1625                         left_operand = getExpression( state, left_operand_id );
1626                         if( !left_operand ) {
1627                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1628                                         "Unable to get left operand in BETWEEN expression # %d", id ));
1629                                 state->error = 1;
1630                                 return NULL;
1631                         }
1632                 }
1633
1634                 // Get the end points of the BETWEEN range
1635                 subexp_list = getExpressionList( state, id );
1636                 if( state->error ) {
1637                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1638                                 "Unable to get subexpressions for BETWEEN expression # %d", id ));
1639                         expressionFree( left_operand );
1640                         return NULL;
1641                 } else if( !subexp_list ) {
1642                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1643                                 "BETWEEN range is empty in expression # %d", id ));
1644                         state->error = 1;
1645                         expressionFree( left_operand );
1646                         return NULL;
1647                 } else if( !subexp_list->next ) {
1648                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1649                                 "BETWEEN range has only one end point in expression # %d", id ));
1650                         state->error = 1;
1651                         expressionListFree( subexp_list );
1652                         expressionFree( left_operand );
1653                         return NULL;
1654                 } else if( subexp_list->next->next ) {
1655                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1656                                 "BETWEEN range has more than two subexpressions in expression # %d", id ));
1657                         state->error = 1;
1658                         expressionListFree( subexp_list );
1659                         expressionFree( left_operand );
1660                         return NULL;
1661                 }
1662
1663         } else if( EXP_BIND == type ) {
1664                 if( bind_variable ) {
1665                         // To do: Build a BindVar
1666                         bind = getBindVar( state, bind_variable );
1667                         if( ! bind ) {
1668                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1669                                         "Unable to load bind variable \"%s\" for expression # %d",
1670                 bind_variable, id ));
1671                                 state->error = 1;
1672                                 return NULL;
1673                         }
1674                         PRINT( "\tBind variable is \"%s\"\n", bind_variable );
1675                 } else {
1676                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1677                                 "No variable specified for bind variable expression # %d",
1678            bind_variable, id ));
1679                         state->error = 1;
1680                         return NULL;
1681                 }
1682                 if( right_operand_id != -1 ) {
1683                         right_operand = getExpression( state, right_operand_id );
1684                         if( !right_operand ) {
1685                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1686                                         "Unable to get right operand in expression # %d", id ));
1687                                 state->error = 1;
1688                                 expressionFree( left_operand );
1689                                 return NULL;
1690                         }
1691                 }
1692
1693         } else if( EXP_CASE == type ) {
1694                 // Get the left operand
1695                 if( -1 == left_operand_id ) {
1696                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1697                                 "No left operand defined for CASE expression # %d", id ));
1698                         state->error = 1;
1699                         return NULL;
1700                 } else {
1701                         left_operand = getExpression( state, left_operand_id );
1702                         if( !left_operand ) {
1703                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1704                                         "Unable to get left operand in CASE expression # %d", id ));
1705                                 state->error = 1;
1706                                 return NULL;
1707                         }
1708                 }
1709
1710                 branch_list = getCaseBranchList( state, id );
1711                 if( ! branch_list ) {
1712                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1713                                 "Unable to build branches for CASE expression # %d", id ));
1714                         state->error = 1;
1715                         return NULL;
1716                 }
1717
1718         } else if( EXP_CAST == type ) {
1719                 // Get the left operand
1720                 if( -1 == left_operand_id ) {
1721                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1722                                 "No left operand defined for CAST expression # %d", id ));
1723                         state->error = 1;
1724                         return NULL;
1725                 } else {
1726                         left_operand = getExpression( state, left_operand_id );
1727                         if( !left_operand ) {
1728                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1729                                         "Unable to get left operand for CAST expression # %d", id ));
1730                                 state->error = 1;
1731                                 return NULL;
1732                         }
1733                 }
1734
1735                 if( -1 == cast_type_id ) {
1736                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1737                                 "No datatype specified for CAST expression # %d", id ));
1738                         state->error = 1;
1739                         return NULL;
1740                 } else {
1741                         cast_type = getDatatype( state, cast_type_id );
1742                         if( !cast_type ) {
1743                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1744                                         "Unable to get datatype for CAST expression # %d", id ));
1745                                 state->error = 1;
1746                                 return NULL;
1747                         }
1748                 }
1749
1750         } else if( EXP_COLUMN == type ) {
1751                 if( !column_name ) {
1752                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1753                                 "No column name for column expression # %d", id ));
1754                         state->error = 1;
1755                         return NULL;
1756                 } else if( !is_identifier( column_name )) {
1757                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1758                                 "Column name \"%s\" is invalid identifier for expression # %d",
1759                                 column_name, id ));
1760                         state->error = 1;
1761                         return NULL;
1762                 }
1763
1764         } else if( EXP_EXIST == type ) {
1765                 if( -1 == subquery_id ) {
1766                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1767                                 "Internal error: No subquery found for EXIST expression # %d", id ));
1768                         state->error = 1;
1769                         return NULL;
1770                 } else {
1771                         subquery = getStoredQuery( state, subquery_id );
1772                         if( !subquery ) {
1773                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1774                                         "Unable to load subquery for EXIST expression # %d", id ));
1775                                 state->error = 1;
1776                                 return NULL;
1777                         }
1778                 }
1779
1780         } else if( EXP_FUNCTION == type ) {
1781                 if( !function_name ) {
1782                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1783                                 "Function call expression # %d provides no function name", id ));
1784                         state->error = 1;
1785                         return NULL;
1786                 } else if( !is_identifier( function_name )) {
1787                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1788                                 "Function call expression # %d contains an invalid function name \"%s\"",
1789                                 id, function_name ));
1790                         state->error = 1;
1791                         return NULL;
1792                 } else {
1793                         subexp_list = getExpressionList( state, id );
1794                         if( state->error ) {
1795                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1796                                         "Unable to get parameter list for function expression # %d", id ));
1797                                 return NULL;
1798                         }
1799                 }
1800
1801         } else if( EXP_IN == type ) {
1802                 if( -1 == left_operand_id ) {
1803                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1804                                 "IN condition has no left operand in expression # %d", id ));
1805                         state->error = 1;
1806                         return NULL;
1807                 } else {
1808                         left_operand = getExpression( state, left_operand_id );
1809                         if( !left_operand ) {
1810                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1811                                         "Unable to get left operand for IN condition in expression # %d", id ));
1812                                 state->error = 1;
1813                                 return NULL;
1814                         }
1815                 }
1816
1817                 if( -1 == subquery_id ) {
1818                         // Load an IN list of subexpressions
1819                         subexp_list = getExpressionList( state, id );
1820                         if( state->error ) {
1821                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1822                                         "Unable to get subexpressions for IN list" ));
1823                                 return NULL;
1824                         } else if( !subexp_list ) {
1825                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1826                                         "IN list is empty in expression # %d", id ));
1827                                 state->error = 1;
1828                                 return NULL;
1829                         }
1830                 } else {
1831                         // Load a subquery
1832                         subquery = getStoredQuery( state, subquery_id );
1833                         if( !subquery ) {
1834                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1835                                         "Unable to load subquery for IN expression # %d", id ));
1836                                 state->error = 1;
1837                                 return NULL;
1838                         }
1839                 }
1840
1841         } else if( EXP_ISNULL == type ) {
1842                 if( -1 == left_operand_id ) {
1843                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1844                                 "Expression # %d IS NULL has no left operand", id ));
1845                         state->error = 1;
1846                         return NULL;
1847                 }
1848
1849                 if( left_operand_id != -1 ) {
1850                         left_operand = getExpression( state, left_operand_id );
1851                         if( !left_operand ) {
1852                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1853                                         "Unable to get left operand in expression # %d", id ));
1854                                 state->error = 1;
1855                                 return NULL;
1856                         }
1857                 }
1858
1859         } else if( EXP_NUMBER == type ) {
1860                 if( !literal ) {
1861                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1862                                 "Numeric expression # %d provides no numeric value", id ));
1863                         state->error = 1;
1864                         return NULL;
1865                 } else if( !jsonIsNumeric( literal )) {
1866                         // Purported number is not numeric.  We use JSON rules here to determine what
1867                         // a valid number is, just because we already have a function lying around that
1868                         // can do that validation.  PostgreSQL probably doesn't use the same exact rules.
1869                         // If that's a problem, we can write a separate validation routine to follow
1870                         // PostgreSQL's rules, once we figure out what those rules are.
1871                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1872                                 "Numeric expression # %d is not a valid number: \"%s\"", id, literal ));
1873                         state->error = 1;
1874                         return NULL;
1875                 }
1876
1877         } else if( EXP_OPERATOR == type ) {
1878                 // Make sure we have a plausible operator
1879                 if( !operator ) {
1880                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1881                                 "Operator expression # %d has no operator", id ));
1882                         state->error = 1;
1883                         return NULL;
1884                 } else if( !is_good_operator( operator )) {
1885                         // The specified operator contains one or more characters that aren't allowed
1886                         // in an operator.  This isn't a true validation; it's just a protective
1887                         // measure to prevent certain kinds of sql injection.
1888                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1889                                 "Operator expression # %d contains invalid operator \"%s\"", id, operator ));
1890                         state->error = 1;
1891                         return NULL;
1892                 }
1893
1894                 // Load left and/or right operands
1895                 if( -1 == left_operand_id && -1 == right_operand_id ) {
1896                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1897                                 "Expression # %d is an operator with no operands", id ));
1898                         state->error = 1;
1899                         return NULL;
1900                 }
1901
1902                 if( left_operand_id != -1 ) {
1903                         left_operand = getExpression( state, left_operand_id );
1904                         if( !left_operand ) {
1905                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1906                                         "Unable to get left operand in expression # %d", id ));
1907                                 state->error = 1;
1908                                 return NULL;
1909                         }
1910                 }
1911
1912                 if( right_operand_id != -1 ) {
1913                         right_operand = getExpression( state, right_operand_id );
1914                         if( !right_operand ) {
1915                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1916                                         "Unable to get right operand in expression # %d", id ));
1917                                 state->error = 1;
1918                                 return NULL;
1919                         }
1920                 }
1921
1922         } else if( EXP_SERIES == type ) {
1923                 subexp_list = getExpressionList( state, id );
1924                 if( state->error ) {
1925                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1926                                 "Unable to get subexpressions for expression series using operator \"%s\"",
1927                                         operator ? operator : "," ));
1928                         return NULL;
1929                 } else if( !subexp_list ) {
1930                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1931                                 "Series expression is empty in expression # %d", id ));
1932                         state->error = 1;
1933                         return NULL;
1934                 }
1935
1936         } else if( EXP_STRING == type ) {
1937                 if( !literal ) {
1938                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1939                                 "String expression # %d provides no string value", id ));
1940                         state->error = 1;
1941                         return NULL;
1942                 }
1943
1944         } else if( EXP_SUBQUERY == type ) {
1945                 if( -1 == subquery_id ) {
1946                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1947                                 "Subquery expression # %d has no query id", id ));
1948                         state->error = 1;
1949                         return NULL;
1950                 } else {
1951                         // Load a subquery, if there is one
1952                         subquery = getStoredQuery( state, subquery_id );
1953                         if( !subquery ) {
1954                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1955                                         "Unable to load subquery for expression # %d", id ));
1956                                 state->error = 1;
1957                                 return NULL;
1958                         }
1959                         if( subquery->select_list && subquery->select_list->next ) {
1960                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1961                                         "Subquery # %d as expression returns more than one column", subquery_id ));
1962                                 state->error = 1;
1963                                 return NULL;
1964                         }
1965                         PRINT( "\tExpression is subquery %d\n", subquery_id );
1966                 }
1967         }
1968
1969         // Allocate an Expression: from the free list if possible, from the heap if necessary
1970         Expression* exp = NULL;
1971         if( free_expression_list ) {
1972                 exp = free_expression_list;
1973                 free_expression_list = free_expression_list->next;
1974         } else
1975                 exp = safe_malloc( sizeof( Expression ) );
1976
1977         // Populate the Expression
1978         exp->next = NULL;
1979         exp->id = id;
1980         exp->type = type;
1981         exp->parenthesize = parenthesize;
1982         exp->parent_expr_id = parent_expr_id;
1983         exp->seq_no = seq_no;
1984         exp->literal = literal ? strdup( literal ) : NULL;
1985         exp->table_alias = table_alias ? strdup( table_alias ) : NULL;
1986         exp->column_name = column_name ? strdup( column_name ) : NULL;
1987         exp->left_operand = left_operand;
1988         exp->op = operator ? strdup( operator ) : NULL;
1989         exp->right_operand = right_operand;
1990         exp->subquery_id = subquery_id;
1991         exp->subquery = subquery;
1992         exp->cast_type = cast_type;
1993         exp->negate = negate;
1994         exp->bind = bind;
1995         exp->branch_list = branch_list;
1996         exp->subexp_list = subexp_list;
1997         exp->function_name = function_name ? strdup( function_name ) : NULL;
1998
1999         return exp;
2000 }
2001
2002 /**
2003         @brief Free all the Expressions in a linked list of Expressions.
2004         @param exp Pointer to the first Expression in the list.
2005 */
2006 static void expressionListFree( Expression* exp ) {
2007         while( exp ) {
2008                 Expression* next = exp->next;
2009                 expressionFree( exp );
2010                 exp = next;
2011         }
2012 }
2013
2014 /**
2015         @brief Deallocate an Expression.
2016         @param exp Pointer to the Expression to be deallocated.
2017
2018         Free the strings owned by the Expression.  Put the Expression itself, and any
2019         subexpressions that it owns, into a free list.
2020 */
2021 static void expressionFree( Expression* exp ) {
2022         if( exp ) {
2023                 free( exp->literal );
2024                 exp->literal = NULL;
2025                 free( exp->table_alias );
2026                 exp->table_alias = NULL;
2027                 free( exp->column_name );
2028                 exp->column_name = NULL;
2029                 if( exp->left_operand ) {
2030                         expressionFree( exp->left_operand );
2031                         exp->left_operand = NULL;
2032                 }
2033                 if( exp->op ) {
2034                         free( exp->op );
2035                         exp->op = NULL;
2036                 }
2037                 if( exp->right_operand ) {
2038                         expressionFree( exp->right_operand );
2039                         exp->right_operand = NULL;
2040                 }
2041                 if( exp->subquery ) {
2042                         storedQFree( exp->subquery );
2043                         exp->subquery = NULL;
2044                 }
2045                 if( exp->cast_type ) {
2046                         datatypeFree( exp->cast_type );
2047                         exp->cast_type = NULL;
2048                 }
2049
2050                 // We don't free the bind member here because the Expression doesn't own it;
2051                 // the bindvar_list hash owns it, so that multiple Expressions can reference it.
2052
2053                 if( exp->subexp_list ) {
2054                         // Free the linked list of subexpressions
2055                         expressionListFree( exp->subexp_list );
2056                         exp->subexp_list = NULL;
2057                 }
2058
2059                 if( exp->branch_list ) {
2060                         freeBranchList( exp->branch_list );
2061                         exp->branch_list = NULL;
2062                 }
2063
2064                 if( exp->function_name ) {
2065                         free( exp->function_name );
2066                         exp->function_name = NULL;
2067                 }
2068
2069                 // Prepend to the free list
2070                 exp->next = free_expression_list;
2071                 free_expression_list = exp;
2072         }
2073 }
2074
2075 /**
2076         @brief Build a list of subexpressions.
2077         @param state Pointer to the query-building context.
2078         @param id ID of the parent Expression.
2079         @return A pointer to the first in a linked list of Expressions, if there are any; or
2080                 NULL if there aren't any, or in case of an error.
2081 */
2082 static Expression* getExpressionList( BuildSQLState* state, int id ) {
2083         Expression* exp_list = NULL;
2084
2085         // The ORDER BY is in descending order so that we can build the list by adding to
2086         // the head, and it will wind up in the right order.
2087         dbi_result result = dbi_conn_queryf( state->dbhandle,
2088                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
2089                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
2090                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
2091                 "func.function_name "
2092                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
2093                 "ON (exp.function_id = func.id) "
2094                 "WHERE exp.parent_expr = %d "
2095                 "ORDER BY exp.seq_no desc;", id );
2096
2097         if( result ) {
2098                 if( dbi_result_first_row( result ) ) {
2099                         while( 1 ) {
2100                                 Expression* exp = constructExpression( state, result );
2101                                 if( exp ) {
2102                                         PRINT( "Found a subexpression\n" );
2103                                         exp->next = exp_list;
2104                                         exp_list  = exp;
2105                                 } else {
2106                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2107                                                 "Unable to build subexpression list for expression id #%d", id ));
2108                                         expressionListFree( exp_list );
2109                                         exp_list = NULL;
2110                                         break;
2111                                 }
2112                                 if( !dbi_result_next_row( result ) )
2113                                         break;
2114                         };
2115                 }
2116         } else {
2117                 const char* msg;
2118                 int errnum = dbi_conn_error( state->dbhandle, &msg );
2119                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2120                         "Unable to query query.expression table for expression list: #%d %s",
2121                         errnum, msg ? msg : "No description available" ));
2122                 state->error = 1;
2123         }
2124
2125         return exp_list;
2126 }
2127
2128 /**
2129         @brief Build a list of ORDER BY items as a linked list of OrderItems.
2130         @param state Pointer to the query-building context.
2131         @param query_id ID for the query to which the ORDER BY belongs.
2132         @return Pointer to the first node in a linked list of OrderItems.
2133
2134         The calling code is responsible for freeing the list by calling orderItemListFree().
2135 */
2136 static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) {
2137         OrderItem* ord_list = NULL;
2138
2139         // The ORDER BY is in descending order so that we can build the list by adding to
2140         // the head, and it will wind up in the right order.
2141         dbi_result result = dbi_conn_queryf( state->dbhandle,
2142                 "SELECT id, stored_query, seq_no, expression "
2143                 "FROM query.order_by_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
2144         if( result ) {
2145                 if( dbi_result_first_row( result ) ) {
2146                         while( 1 ) {
2147                                 OrderItem* item = constructOrderItem( state, result );
2148                                 if( item ) {
2149                                         PRINT( "Found an ORDER BY item\n" );
2150
2151                                         item->next = ord_list;
2152                                         ord_list = item;
2153                                 } else {
2154                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2155                                                 "Unable to build ORDER BY item for query id #%d", query_id ));
2156                                         orderItemListFree( ord_list );
2157                                         ord_list = NULL;
2158                                         break;
2159                                 }
2160                                 if( !dbi_result_next_row( result ) )
2161                                         break;
2162                         };
2163                 }
2164         }  else {
2165                 const char* msg;
2166                 int errnum = dbi_conn_error( state->dbhandle, &msg );
2167                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2168                         "Unable to query query.order_by_list table: #%d %s",
2169                         errnum, msg ? msg : "No description available" ));
2170                 state->error = 1;
2171         }
2172
2173         return ord_list;
2174 }
2175
2176 /**
2177         @brief Construct an OrderItem.
2178         @param Pointer to the query-building context.
2179         @param result Database cursor positioned at a row in query.order_by_item.
2180         @return Pointer to a newly constructed OrderItem, if successful, or NULL if not.
2181
2182         The calling code is responsible for freeing the OrderItems by calling orderItemListFree().
2183 */
2184 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ) {
2185         int id                   = dbi_result_get_int_idx( result, 1 );
2186         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
2187         int seq_no               = dbi_result_get_int_idx( result, 3 );
2188         int expression_id        = dbi_result_get_int_idx( result, 4 );
2189         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
2190
2191         // Construct an Expression
2192         Expression* expression = getExpression( state, expression_id );
2193         if( !expression ) {
2194                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2195                         "Unable to fetch ORDER BY expression for id = %d", expression_id ));
2196                 state->error = 1;
2197                 return NULL;
2198         };
2199
2200         // Allocate an OrderItem; from the free list if possible, or from the heap if necessary.
2201         OrderItem* ord;
2202         if( free_order_item_list ) {
2203                 ord = free_order_item_list;
2204                 free_order_item_list = free_order_item_list->next;
2205         } else
2206                 ord = safe_malloc( sizeof( OrderItem ));
2207
2208         ord->next            = NULL;
2209         ord->id              = id;
2210         ord->stored_query_id = stored_query_id;
2211         ord->seq_no          = seq_no;
2212         ord->expression      = expression;
2213
2214         return ord;
2215 }
2216
2217 /**
2218         @brief Deallocate a linked list of OrderItems.
2219         @param exp Pointer to the first OrderItem in the list to be deallocated.
2220
2221         Deallocate the memory owned by the OrderItems.  Put the items themselves into a free list.
2222 */
2223 static void orderItemListFree( OrderItem* ord ) {
2224         if( !ord )
2225                 return;    // Nothing to free
2226
2227         OrderItem* first = ord;
2228         while( 1 ) {
2229                 expressionFree( ord->expression );
2230                 ord->expression = NULL;
2231
2232                 if( NULL == ord->next ) {
2233                         ord->next = free_order_item_list;
2234                         break;
2235                 } else
2236                         ord = ord->next;
2237         };
2238
2239         // Transfer the entire list to the free list
2240         free_order_item_list = first;
2241 }
2242
2243 /**
2244         @brief Push an IdNode onto a stack of IdNodes.
2245         @param stack Pointer to the stack.
2246         @param id Id of the new node.
2247         @param alias Alias, if any, of the new node.
2248 */
2249 static void push_id( IdNode** stack, int id, const char* alias ) {
2250
2251         if( stack ) {
2252                 // Allocate a node; from the free list if possible, from the heap if necessary.
2253                 IdNode* node = NULL;
2254                 if( free_id_node_list ) {
2255                         node = free_id_node_list;
2256                         free_id_node_list = free_id_node_list->next;
2257                 } else
2258                         node = safe_malloc( sizeof( IdNode ));
2259
2260                 // Populate it
2261                 node->next = *stack;
2262                 node->id = id;
2263                 if( alias )
2264                         node->alias = strdup( alias );
2265                 else
2266                         node->alias = NULL;
2267
2268                 // Reseat the stack
2269                 *stack = node;
2270         }
2271 }
2272
2273 /**
2274         @brief Remove the node at the top of an IdNode stack.
2275         @param stack Pointer to the IdNode stack.
2276 */
2277 void pop_id( IdNode** stack ) {
2278         if( stack ) {
2279                 IdNode* node = *stack;
2280                 *stack = node->next;
2281
2282                 if( node->alias ) {
2283                         free( node->alias );
2284                         node->alias = NULL;
2285                 }
2286
2287                 node->next = free_id_node_list;
2288                 free_id_node_list = node;
2289         }
2290 }
2291
2292 /**
2293         @brief Search a stack of IDs for a match by either ID or, optionally, by alias.
2294         @param stack Pointer to the stack.
2295         @param id The id to search for.
2296         @param alias (Optional) the alias to search for.
2297         @return A pointer to the matching node if one is found, or NULL if not.
2298
2299         This search is used to detect cases where a query, expression, or FROM clause is nested
2300         inside itself, in order to avoid infinite recursion; or in order to avoid conflicting
2301         table references in a FROM clause.
2302 */
2303 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias ) {
2304         if( stack ) {
2305                 const IdNode* node = stack;
2306                 while( node ) {
2307                         if( node->id == id )
2308                                 return node;        // Matched on id
2309                         else if( alias && node->alias && !strcmp( alias, node->alias ))
2310                                 return node;        // Matched on alias
2311                         else
2312                                 node = node->next;
2313                 }
2314         }
2315         return NULL;   // No match found
2316 }
2317
2318 /**
2319         @brief Free up any resources held by the StoredQ module.
2320 */
2321 void storedQCleanup( void ) {
2322
2323         // Free all the nodes in the free state list
2324         StoredQ* sq = free_storedq_list;
2325         while( sq ) {
2326                 free_storedq_list = sq->next;
2327                 free( sq );
2328                 sq = free_storedq_list;
2329         }
2330
2331         // Free all the nodes in the free from_relation list
2332         FromRelation* fr = free_from_relation_list;
2333         while( fr ) {
2334                 free_from_relation_list = fr->next;
2335                 free( fr );
2336                 fr = free_from_relation_list;
2337         }
2338
2339         // Free all the nodes in the free expression list
2340         Expression* exp = free_expression_list;
2341         while( exp ) {
2342                 free_expression_list = exp->next;
2343                 free( exp );
2344                 exp = free_expression_list;
2345         }
2346
2347         // Free all the nodes in the free select item list
2348         SelectItem* sel = free_select_item_list;
2349         while( sel ) {
2350                 free_select_item_list = sel->next;
2351                 free( sel );
2352                 sel = free_select_item_list;
2353         }
2354
2355         // Free all the nodes in the free select item list
2356         IdNode* node = free_id_node_list;
2357         while( node ) {
2358                 free_id_node_list = node->next;
2359                 free( node );
2360                 node = free_id_node_list;
2361         }
2362
2363         // Free all the nodes in the free query sequence list
2364         QSeq* seq = free_qseq_list;
2365         while( seq ) {
2366                 free_qseq_list = seq->next;
2367                 free( seq );
2368                 seq = free_qseq_list;
2369         }
2370
2371         // Free all the nodes in the free order item list
2372         OrderItem* ord = free_order_item_list;
2373         while( ord ) {
2374                 free_order_item_list = ord->next;
2375                 free( ord );
2376                 ord = free_order_item_list;
2377         }
2378
2379         // Free all the nodes in the bind variable free list
2380         BindVar* bind = free_bindvar_list;
2381         while( bind ) {
2382                 free_bindvar_list = bind->next;
2383                 free( bind );
2384                 bind = free_bindvar_list;
2385         }
2386
2387         // Free all the nodes in the case branch free list
2388         CaseBranch* branch = free_branch_list;
2389         while( branch ) {
2390                 free_branch_list = branch->next;
2391                 free( branch );
2392                 branch = free_branch_list;
2393         }
2394
2395         // Free all the nodes in the datatype free list
2396         Datatype* datatype = free_datatype_list;
2397         while( datatype ) {
2398                 free_datatype_list = datatype->next;
2399                 free( datatype );
2400                 datatype = free_datatype_list;
2401         }
2402 }
2403
2404 /**
2405         @brief Return a boolean value from a database result.
2406         @param result The database result.
2407         @param i Index of the column in the result, starting with 1 );
2408         @return 1 if true, or 0 for false.
2409
2410         Null values and error conditions are interpreted as FALSE.
2411 */
2412 static int oils_result_get_bool_idx( dbi_result result, int i ) {
2413         if( result ) {
2414                 const char* str = dbi_result_get_string_idx( result, i );
2415                 return (str && *str == 't' ) ? 1 : 0;
2416         } else
2417                 return 0;
2418 }
2419
2420 /**
2421         @brief Enable verbose messages.
2422
2423         The messages are written to standard output, which for a server is /dev/null.  Hence this
2424         option is useful only for a non-server.  It is intended only as a convenience for
2425         development and debugging.
2426 */
2427 void oilsStoredQSetVerbose( void ) {
2428         verbose = 1;
2429 }