Open-ILS/src/c-apps/oils_storedq.c

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