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