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