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