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