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 "openils/oils_buildq.h"
  13
  14 #define PRINT if( verbose ) printf
  15
  16 struct IdNode_ {
  17         IdNode* next;
  18         int id;
  19         char* alias;
  20 };
  21
  22 static int oils_result_get_bool_idx( dbi_result result, int i );
  23
  24 static FromRelation* getFromRelation( BuildSQLState* state, int id );
  25 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result );
  26 static FromRelation* getJoinList( BuildSQLState* state, int id );
  27 static void joinListFree( FromRelation* join_list );
  28 static void fromRelationFree( FromRelation* fr );
  29
  30 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str );
  31 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result );
  32 static void freeQSeqList( QSeq* seq );
  33 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result );
  34
  35 static SelectItem* getSelectList( BuildSQLState* state, int query_id );
  36 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result );
  37 static void selectListFree( SelectItem* sel );
  38
  39 static Expression* getExpression( BuildSQLState* state, int id );
  40 static Expression* constructExpression( BuildSQLState* state, dbi_result result );
  41 static void expressionFree( Expression* exp );
  42
  43 static OrderItem* getOrderByList( BuildSQLState* state, int query_id );
  44 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result );
  45 static void orderItemListFree( OrderItem* ord );
  46
  47 static void push_id( IdNode** stack, int id, const char* alias );
  48 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias );
  49
  50 // A series of free lists to store already-allocated objects that are not in use, for
  51 // potential reuse.  This is a hack to reduce churning through malloc() and free().
  52 static StoredQ* free_storedq_list = NULL;
  53 static FromRelation* free_from_relation_list = NULL;
  54 static SelectItem* free_select_item_list = NULL;
  55 static Expression* free_expression_list = NULL;
  56 static IdNode* free_id_node_list = NULL;
  57 static QSeq* free_qseq_list = NULL;
  58 static OrderItem* free_order_item_list = NULL;
  59
  60 // Boolean; settable by call to oilsStoredQSetVerbose(), used by PRINT macro.
  61 // The idea is to allow debugging messages from a command line test driver for ease of
  62 // testing and development, but not from a real server, where messages to stdout don't
  63 // go anywhere.
  64 static int verbose = 0;
  65
  66 /**
  67         @brief Load a stored query.
  68         @param state Pointer to the query-building context.
  69         @param query_id ID of the query in query.stored_query.
  70         @return A pointer to the newly loaded StoredQ if successful, or NULL if not.
  71
  72         The calling code is responsible for freeing the StoredQ by calling storedQFree().
  73 */
  74 StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) {
  75         if( !state )
  76                 return NULL;
  77
  78         // Check the stack to see if the current query is nested inside itself.  If it is, then
  79         // abort in order to avoid infinite recursion.  If it isn't, then add it to the stack.
  80         // (Make sure to pop it off the stack before returning.)
  81         if( searchIdStack( state->query_stack, query_id, NULL )) {
  82                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
  83                         "Infinite recursion detected; query # %d is nested within itself", query_id ));
  84                 state->error = 1;
  85                 return NULL;
  86         } else
  87                 push_id( &state->query_stack, query_id, NULL );
  88
  89         StoredQ* sq = NULL;
  90         dbi_result result = dbi_conn_queryf( state->dbhandle,
  91                 "SELECT id, type, use_all, use_distinct, from_clause, where_clause, having_clause "
  92                 "FROM query.stored_query WHERE id = %d;", query_id );
  93         if( result ) {
  94                 if( dbi_result_first_row( result ) ) {
  95                         sq = constructStoredQ( state, result );
  96                         if( sq ) {
  97                                 PRINT( "Got a query row\n" );
  98                                 PRINT( "\tid: %d\n", sq->id );
  99                                 PRINT( "\ttype: %d\n", (int) sq->type );
 100                                 PRINT( "\tuse_all: %s\n", sq->use_all ? "true" : "false" );
 101                                 PRINT( "\tuse_distinct: %s\n", sq->use_distinct ? "true" : "false" );
 102                         } else
 103                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 104                                         "Unable to build a query for id = %d", query_id ));
 105                 } else {
 106                         sqlAddMsg( state, "Stored query not found for id %d", query_id );
 107                 }
 108
 109                 dbi_result_free( result );
 110         } else {
 111                 const char* msg;
 112                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 113                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 114                         "Unable to query query.stored_query table: #%d %s",
 115                         errnum, msg ? msg : "No description available" ));
 116         }
 117
 118         pop_id( &state->query_stack );
 119         return sq;
 120 }
 121
 122 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) {
 123
 124         // Get the column values from the result
 125         int id               = dbi_result_get_int_idx( result, 1 );
 126         const char* type_str = dbi_result_get_string_idx( result, 2 );
 127
 128         QueryType type;
 129         if( !strcmp( type_str, "SELECT" ))
 130                 type = QT_SELECT;
 131         else if( !strcmp( type_str, "UNION" ))
 132                 type = QT_UNION;
 133         else if( !strcmp( type_str, "INTERSECT" ))
 134                 type = QT_INTERSECT;
 135         else if( !strcmp( type_str, "EXCEPT" ))
 136                 type = QT_EXCEPT;
 137         else {
 138                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 139                         "Invalid query type \"%s\"", type_str ));
 140                 return NULL;
 141         }
 142
 143         int use_all             = oils_result_get_bool_idx( result, 3 );
 144         int use_distinct        = oils_result_get_bool_idx( result, 4 );
 145
 146         int from_clause_id;
 147         if( dbi_result_field_is_null_idx( result, 5 ) )
 148                 from_clause_id = -1;
 149         else
 150                 from_clause_id = dbi_result_get_int_idx( result, 5 );
 151
 152         int where_clause_id;
 153         if( dbi_result_field_is_null_idx( result, 6 ) )
 154                 where_clause_id = -1;
 155         else
 156                 where_clause_id = dbi_result_get_int_idx( result, 6 );
 157
 158         int having_clause_id;
 159         if( dbi_result_field_is_null_idx( result, 7 ) )
 160                 having_clause_id = -1;
 161         else
 162                 having_clause_id = dbi_result_get_int_idx( result, 7 );
 163
 164         FromRelation* from_clause = NULL;
 165         if( QT_SELECT == type ) {
 166                 // A SELECT query needs a FROM clause; go get it
 167                 if( from_clause_id != -1 ) {
 168                         from_clause = getFromRelation( state, from_clause_id );
 169                         if( !from_clause ) {
 170                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 171                                         "Unable to construct FROM clause for id = %d", from_clause_id ));
 172                                 return NULL;
 173                         }
 174                 }
 175         } else {
 176                 // Must be one of UNION, INTERSECT, or EXCEPT
 177                 if( from_clause_id != -1 )
 178                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 179                                 "FROM clause found and ignored for %s query in query #%d", type_str, id ));
 180         }
 181
 182         // If this is a SELECT query, we need a SELECT list.  Go get one.
 183         SelectItem* select_list = NULL;
 184         QSeq* child_list = NULL;
 185         if( QT_SELECT == type ) {
 186                 select_list = getSelectList( state, id );
 187                 if( !select_list ) {
 188                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 189                                 "No SELECT list found for query id = %d", id ));
 190                         fromRelationFree( from_clause );
 191                         return NULL;
 192                 }
 193         } else {
 194                 // Construct child queries of UNION, INTERSECT, or EXCEPT query
 195                 child_list = loadChildQueries( state, id, type_str );
 196                 if( !child_list ) {
 197                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 198                                 "Unable to load child queries for %s query # %d", type_str, id ));
 199                         state->error = 1;
 200                         fromRelationFree( from_clause );
 201                         return NULL;
 202                 }
 203         }
 204
 205         // Get the WHERE clause, if there is one
 206         Expression* where_clause = NULL;
 207         if( where_clause_id != -1 ) {
 208                 where_clause = getExpression( state, where_clause_id );
 209                 if( ! where_clause ) {
 210                         // shouldn't happen due to foreign key constraint
 211                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 212                                 "Unable to fetch WHERE expression for query id = %d", id ));
 213                         freeQSeqList( child_list );
 214                         fromRelationFree( from_clause );
 215                         selectListFree( select_list );
 216                         return NULL;
 217                 }
 218         }
 219
 220         // Get the ORDER BY clause, if there is one
 221         OrderItem* order_by_list = getOrderByList( state, id );
 222         if( state->error ) {
 223                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 224                         "Unable to load ORDER BY clause for query %d", id ));
 225                 expressionFree( where_clause );
 226                 freeQSeqList( child_list );
 227                 fromRelationFree( from_clause );
 228                 selectListFree( select_list );
 229                 return NULL;
 230         }
 231
 232         // Allocate a StoredQ: from the free list if possible, from the heap if necessary
 233
 234         StoredQ* sq;
 235         if( free_storedq_list ) {
 236                 sq = free_storedq_list;
 237                 free_storedq_list = free_storedq_list->next;
 238         } else
 239                 sq = safe_malloc( sizeof( StoredQ ) );
 240
 241         // Populate the StoredQ
 242         sq->next = NULL;
 243         sq->id = id;
 244
 245         sq->type = type;
 246         sq->use_all = use_all;
 247         sq->use_distinct = use_distinct;
 248         sq->from_clause = from_clause;
 249         sq->where_clause = where_clause;
 250         sq->select_list = select_list;
 251         sq->child_list = child_list;
 252         sq->order_by_list = order_by_list;
 253
 254         return sq;
 255 }
 256
 257 /**
 258         @brief Load the child queries subordinate to a UNION, INTERSECT, or EXCEPT query.
 259         @param state Pointer to the query-building context.
 260         @param parent ID of the UNION, INTERSECT, or EXCEPT query.
 261         @param type_str The type of the query ("UNION", "INTERSECT", or "EXCEPT").
 262         @return If successful, a pointer to a linked list of QSeq, each bearing a pointer to a
 263                 StoredQ; otherwise NULL.
 264
 265         The @a type_str parameter is used only for building error messages.
 266 */
 267 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) {
 268         QSeq* child_list = NULL;
 269
 270         // The ORDER BY is in descending order so that we can build the list by adding to
 271         // the head, and it will wind up in the right order.
 272         dbi_result result = dbi_conn_queryf( state->dbhandle,
 273                 "SELECT id, parent_query, seq_no, child_query "
 274                 "FROM query.query_sequence WHERE parent_query = %d ORDER BY seq_no DESC", parent_id );
 275         if( result ) {
 276                 if( dbi_result_first_row( result ) ) {
 277                         int count = 0;
 278                         while( 1 ) {
 279                                 ++count;
 280                                 QSeq* seq = constructQSeq( state, result );
 281                                 if( seq ) {
 282                                         PRINT( "Found a child query\n" );
 283                                         PRINT( "\tid: %d\n", seq->id );
 284                                         PRINT( "\tparent id: %d\n", seq->parent_query_id );
 285                                         PRINT( "\tseq_no: %d\n", seq->seq_no );
 286                                         // Add to the head of the list
 287                                         seq->next = child_list;
 288                                         child_list = seq;
 289                                 } else{
 290                                         freeQSeqList( child_list );
 291                                         return NULL;
 292                                 }
 293                                 if( !dbi_result_next_row( result ))
 294                                         break;
 295                         }
 296                         if( count < 2 ) {
 297                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 298                                         "%s query # %d has only one child query", type_str, parent_id ));
 299                                 state->error = 1;
 300                                 freeQSeqList( child_list );
 301                                 return NULL;
 302                         }
 303                 } else {
 304                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 305                                 "%s query # %d has no child queries within it", type_str, parent_id ));
 306                         state->error = 1;
 307                         return NULL;
 308                 }
 309         } else {
 310                 const char* msg;
 311                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 312                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 313                         "Unable to query query.query_sequence table: # %d %s",
 314                         errnum, msg ? msg : "No description available" ));
 315                 state->error = 1;
 316                 return NULL;
 317         }
 318
 319         return child_list;
 320 }
 321
 322 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result ) {
 323         int id = dbi_result_get_int_idx( result, 1 );
 324         int parent_query_id = dbi_result_get_int_idx( result, 2 );
 325         int seq_no = dbi_result_get_int_idx( result, 3 );
 326         int child_query_id = dbi_result_get_int_idx( result, 4 );
 327
 328         StoredQ* child_query = getStoredQuery( state, child_query_id );
 329         if( !child_query ) {
 330                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 331                         "Unable to load child query # %d for parent query %d",
 332                         child_query_id, parent_query_id ));
 333                 state->error = 1;
 334                 return NULL;
 335         }
 336
 337         // Allocate a QSeq; from the free list if possible, from the heap if necessary
 338         QSeq* seq = NULL;
 339         if( free_qseq_list ) {
 340                 seq = free_qseq_list;
 341                 free_qseq_list = free_qseq_list->next;
 342         } else
 343                 seq = safe_malloc( sizeof( QSeq ));
 344
 345         seq->next            = NULL;
 346         seq->id              = id;
 347         seq->parent_query_id = parent_query_id;
 348         seq->seq_no          = seq_no;
 349         seq->child_query     = child_query;
 350
 351         return seq;
 352 }
 353
 354 static void freeQSeqList( QSeq* seq ) {
 355         if( !seq )
 356                 return;
 357
 358         QSeq* first = seq;
 359         while( seq ) {
 360                 storedQFree( seq->child_query );
 361                 seq->child_query = NULL;
 362
 363                 if( seq->next )
 364                         seq = seq->next;
 365                 else {
 366                         seq->next = free_qseq_list;
 367                         seq = NULL;
 368                 }
 369         }
 370
 371         free_qseq_list = first;
 372 }
 373
 374 /**
 375         @brief Deallocate the memory owned by a StoredQ.
 376         @param sq Pointer to the StoredQ to be deallocated.
 377 */
 378 void storedQFree( StoredQ* sq ) {
 379         if( sq ) {
 380                 fromRelationFree( sq->from_clause );
 381                 sq->from_clause = NULL;
 382                 selectListFree( sq->select_list );
 383                 sq->select_list = NULL;
 384                 expressionFree( sq->where_clause );
 385                 sq->where_clause = NULL;
 386                 if( sq->child_list ) {
 387                         freeQSeqList( sq->child_list );
 388                         sq->child_list = NULL;
 389                 }
 390                 if( sq->order_by_list ) {
 391                         orderItemListFree( sq->order_by_list );
 392                         sq->order_by_list = NULL;
 393                 }
 394
 395                 // Stick the empty husk on the free list for potential reuse
 396                 sq->next = free_storedq_list;
 397                 free_storedq_list = sq;
 398         }
 399 }
 400
 401 static FromRelation* getFromRelation( BuildSQLState* state, int id ) {
 402         FromRelation* fr = NULL;
 403         dbi_result result = dbi_conn_queryf( state->dbhandle,
 404                 "SELECT id, type, table_name, class_name, subquery, function_call, "
 405                 "table_alias, parent_relation, seq_no, join_type, on_clause "
 406                 "FROM query.from_relation WHERE id = %d;", id );
 407         if( result ) {
 408                 if( dbi_result_first_row( result ) ) {
 409                         fr = constructFromRelation( state, result );
 410                         if( fr ) {
 411                                 PRINT( "Got a from_relation row\n" );
 412                                 PRINT( "\tid: %d\n", fr->id );
 413                                 PRINT( "\ttype: %d\n", (int) fr->type );
 414                                 PRINT( "\ttable_name: %s\n", fr->table_name ? fr->table_name : "(none)" );
 415                                 PRINT( "\tclass_name: %s\n", fr->class_name ? fr->class_name : "(none)" );
 416                                 PRINT( "\tsubquery_id: %d\n", fr->subquery_id );
 417                                 PRINT( "\tfunction_call_id: %d\n", fr->function_call_id );
 418                                 PRINT( "\ttable_alias: %s\n", fr->table_alias ? fr->table_alias : "(none)" );
 419                                 PRINT( "\tparent_relation_id: %d\n", fr->parent_relation_id );
 420                                 PRINT( "\tseq_no: %d\n", fr->seq_no );
 421                                 PRINT( "\tjoin_type = %d\n", fr->join_type );
 422                                 // Check the stack to see if the current from clause is nested inside itself.
 423                                 // If it is, then abort in order to avoid infinite recursion.  If it isn't,
 424                                 // then add it to the stack.  (Make sure to pop it off the stack before
 425                                 // returning.)
 426                                 const char* effective_alias = fr->table_alias;
 427                                 if( !effective_alias )
 428                                         effective_alias = fr->class_name;
 429                                 const IdNode* node = searchIdStack( state->from_stack, id, effective_alias );
 430                                 if( node ) {
 431                                         if( node->id == id )
 432                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 433                                                         "Infinite recursion detected; from clause # %d is nested "
 434                                                         "within itself", id ));
 435                                         else
 436                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 437                                                         "Conflicting nested table aliases \"%s\" in from clause # %d",
 438                                                         effective_alias, node->id ));
 439                                         state->error = 1;
 440                                         return NULL;
 441                                 } else
 442                                         push_id( &state->from_stack, id, effective_alias );
 443                         } else
 444                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 445                                         "Unable to build a FromRelation for id = %d", id ));
 446                 } else {
 447                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 448                                 "FROM relation not found for id = %d", id ));
 449                 }
 450                 dbi_result_free( result );
 451         } else {
 452                 const char* msg;
 453                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 454                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 455                         "Unable to query query.from_relation table: #%d %s",
 456                         errnum, msg ? msg : "No description available" ));
 457         }
 458
 459         if( fr )
 460                 pop_id( &state->from_stack );
 461
 462         return fr;
 463 }
 464
 465 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result ) {
 466         // Get the column values from the result
 467         int id                  = dbi_result_get_int_idx( result, 1 );
 468         const char* type_str    = dbi_result_get_string_idx( result, 2 );
 469
 470         FromRelationType type;
 471         if( !strcmp( type_str, "RELATION" ))
 472                 type = FRT_RELATION;
 473         else if( !strcmp( type_str, "SUBQUERY" ))
 474                 type = FRT_SUBQUERY;
 475         else if( !strcmp( type_str, "FUNCTION" ))
 476                 type = FRT_FUNCTION;
 477         else
 478                 type = FRT_RELATION;     // shouldn't happen due to database constraint
 479
 480         const char* table_name  = dbi_result_get_string_idx( result, 3 );
 481         const char* class_name  = dbi_result_get_string_idx( result, 4 );
 482
 483         int subquery_id;
 484         if( dbi_result_field_is_null_idx( result, 5 ) )
 485                 subquery_id          = -1;
 486         else
 487                 subquery_id          = dbi_result_get_int_idx( result, 5 );
 488
 489         int function_call_id;
 490         if( dbi_result_field_is_null_idx( result, 6 ) )
 491                 function_call_id     = -1;
 492         else
 493                 function_call_id     = dbi_result_get_int_idx( result, 6 );
 494
 495         const char* table_alias  = dbi_result_get_string_idx( result, 7 );
 496
 497         int parent_relation_id;
 498         if( dbi_result_field_is_null_idx( result, 8 ) )
 499                 parent_relation_id   = -1;
 500         else
 501                 parent_relation_id   = dbi_result_get_int_idx( result, 8 );
 502
 503         int seq_no               = dbi_result_get_int_idx( result, 9 );
 504
 505         JoinType join_type;
 506         const char* join_type_str = dbi_result_get_string_idx( result, 10 );
 507         if( !join_type_str )
 508                 join_type = JT_NONE;
 509         else if( !strcmp( join_type_str, "INNER" ) )
 510                 join_type = JT_INNER;
 511         else if( !strcmp( join_type_str, "LEFT" ) )
 512                 join_type = JT_LEFT;
 513         else if( !strcmp( join_type_str, "RIGHT" ) )
 514                 join_type = JT_RIGHT;
 515         else if( !strcmp( join_type_str, "FULL" ) )
 516                 join_type = JT_FULL;
 517         else
 518                 join_type = JT_NONE;     // shouldn't happen due to database constraint
 519
 520         int on_clause_id;
 521         if( dbi_result_field_is_null_idx( result, 11 ) )
 522                 on_clause_id   = -1;
 523         else
 524                 on_clause_id   = dbi_result_get_int_idx( result, 11 );
 525
 526         StoredQ* subquery = NULL;
 527
 528         switch ( type ) {
 529                 case FRT_RELATION :
 530                         break;
 531                 case FRT_SUBQUERY :
 532                         if( -1 == subquery_id ) {
 533                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 534                                         "Internal error: no subquery specified for FROM relation # %d", id ));
 535                                 state->error = 1;
 536                                 return NULL;
 537                         }
 538                         if( ! table_alias ) {
 539                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 540                                         "Subquery needs alias in FROM relation # %d", id ));
 541                                 state->error = 1;
 542                                 return NULL;
 543                         }
 544                         subquery = getStoredQuery( state, subquery_id );
 545                         if( ! subquery ) {
 546                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 547                                         "Unable to load subquery for FROM relation # %d", id ));
 548                                 state->error = 1;
 549                                 return NULL;
 550                         }
 551                         break;
 552                 case FRT_FUNCTION :
 553                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 554                                 "Functions in FROM clause not yet supported" ));
 555                         state->error = 1;
 556                         return NULL;
 557         }
 558
 559         FromRelation* join_list = getJoinList( state, id );
 560         if( state->error ) {
 561                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 562                         "Unable to load join list for FROM relation # %d", id ));
 563                 return NULL;
 564         }
 565
 566         Expression* on_clause = NULL;
 567         if( on_clause_id != -1 ) {
 568                 on_clause = getExpression( state, on_clause_id );
 569                 if( !on_clause ) {
 570                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 571                                 "Unable to load ON condition for FROM relation # %d", id ));
 572                         joinListFree( join_list );
 573                         return NULL;
 574                 }
 575                 else
 576                         PRINT( "\tGot an ON condition\n" );
 577         }
 578
 579         // Allocate a FromRelation: from the free list if possible, from the heap if necessary
 580
 581         FromRelation* fr;
 582         if( free_from_relation_list ) {
 583                 fr = free_from_relation_list;
 584                 free_from_relation_list = free_from_relation_list->next;
 585         } else
 586                 fr = safe_malloc( sizeof( FromRelation ) );
 587
 588         // Populate the FromRelation
 589
 590         fr->next = NULL;
 591         fr->id = id;
 592         fr->type = type;
 593         fr->table_name = table_name ? strdup( table_name ) : NULL;
 594         fr->class_name = class_name ? strdup( class_name ) : NULL;
 595         fr->subquery_id = subquery_id;
 596         fr->subquery = subquery;
 597         fr->function_call_id = function_call_id;
 598         fr->table_alias = table_alias ? strdup( table_alias ) : NULL;
 599         fr->parent_relation_id = parent_relation_id;
 600         fr->seq_no = seq_no;
 601         fr->join_type = join_type;
 602         fr->on_clause = on_clause;
 603         fr->join_list = join_list;
 604
 605         return fr;
 606 }
 607
 608 /**
 609         @brief Build a list of joined relations.
 610         @param state Pointer to the query-building context.
 611         @param id ID of the parent relation.
 612         @return A pointer to the first in a linked list of FromRelations, if there are any; or
 613                 NULL if there aren't any, or in case of an error.
 614
 615         Look for relations joined directly to the parent relation, and make a list of them.
 616 */
 617 static FromRelation* getJoinList( BuildSQLState* state, int id ) {
 618         FromRelation* join_list = NULL;
 619
 620         // The ORDER BY is in descending order so that we can build the list by adding to
 621         // the head, and it will wind up in the right order.
 622         dbi_result result = dbi_conn_queryf( state->dbhandle,
 623                 "SELECT id, type, table_name, class_name, subquery, function_call, "
 624                 "table_alias, parent_relation, seq_no, join_type, on_clause "
 625                 "FROM query.from_relation WHERE parent_relation = %d ORDER BY seq_no DESC", id );
 626
 627         if( result ) {
 628                 if( dbi_result_first_row( result ) ) {
 629                         while( 1 ) {
 630                                 FromRelation* relation = constructFromRelation( state, result );
 631                                 if( relation ) {
 632                                         PRINT( "Found a joined relation\n" );
 633                                         PRINT( "\tjoin_type: %d\n", relation->join_type );
 634                                         PRINT( "\ttable_name: %s\n", relation->table_name );
 635                                         relation->next = join_list;
 636                                         join_list = relation;
 637                                 } else {
 638                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 639                                                 "Unable to build join list for from relation id #%d", id ));
 640                                         joinListFree( join_list );
 641                                         join_list = NULL;
 642                                         break;
 643                                 }
 644                                 if( !dbi_result_next_row( result ) )
 645                                         break;
 646                         };
 647                 }
 648         } else {
 649                 const char* msg;
 650                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 651                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 652                         "Unable to query query.from_relation table for join list: #%d %s",
 653                         errnum, msg ? msg : "No description available" ));
 654         }
 655
 656         return join_list;
 657 }
 658
 659 /**
 660         @brief Free a list of FromRelations.
 661         @param join_list Pointer to the first FromRelation in the list.
 662 */
 663 static void joinListFree( FromRelation* join_list ) {
 664         while( join_list ) {
 665                 FromRelation* temp = join_list->next;
 666                 fromRelationFree( join_list );
 667                 join_list = temp;
 668         }
 669 }
 670
 671 /**
 672         @brief Deallocate a FromRelation.
 673         @param fr Pointer to the FromRelation to be freed.
 674
 675         Free the strings that the FromRelation owns.  The FromRelation itself goes onto a
 676         free list for potential reuse.
 677 */
 678 static void fromRelationFree( FromRelation* fr ) {
 679         if( fr ) {
 680                 free( fr->table_name );
 681                 fr->table_name = NULL;
 682                 free( fr->class_name );
 683                 fr->class_name = NULL;
 684                 if( fr->subquery ) {
 685                         storedQFree( fr->subquery );
 686                         fr->subquery = NULL;
 687                 }
 688                 free( fr->table_alias );
 689                 fr->table_alias = NULL;
 690                 if( fr->on_clause ) {
 691                         expressionFree( fr->on_clause );
 692                         fr->on_clause = NULL;
 693                 }
 694                 joinListFree( fr->join_list );
 695                 fr->join_list = NULL;
 696
 697                 fr->next = free_from_relation_list;
 698                 free_from_relation_list = fr;
 699         }
 700 }
 701
 702 static SelectItem* getSelectList( BuildSQLState* state, int query_id ) {
 703         SelectItem* select_list = NULL;
 704
 705         // The ORDER BY is in descending order so that we can build the list by adding to
 706         // the head, and it will wind up in the right order.
 707         dbi_result result = dbi_conn_queryf( state->dbhandle,
 708                 "SELECT id, stored_query, seq_no, expression, column_alias, grouped_by "
 709                 "FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
 710         if( result ) {
 711                 if( dbi_result_first_row( result ) ) {
 712                         while( 1 ) {
 713                                 SelectItem* item = constructSelectItem( state, result );
 714                                 if( item ) {
 715                                         PRINT( "Found a SELECT item\n" );
 716                                         PRINT( "\tid: %d\n", item->id );
 717                                         PRINT( "\tstored_query_id: %d\n", item->stored_query_id );
 718                                         PRINT( "\tseq_no: %d\n", item->seq_no );
 719                                         PRINT( "\tcolumn_alias: %s\n",
 720                                                         item->column_alias ? item->column_alias : "(none)" );
 721                                         PRINT( "\tgrouped_by: %d\n", item->grouped_by );
 722
 723                                         item->next = select_list;
 724                                         select_list = item;
 725                                 } else {
 726                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 727                                                 "Unable to build select list for query id #%d", query_id ));
 728                                         selectListFree( select_list );
 729                                         select_list = NULL;
 730                                         break;
 731                                 }
 732                                 if( !dbi_result_next_row( result ) )
 733                                         break;
 734                         };
 735                 }
 736         } else {
 737                 const char* msg;
 738                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 739                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 740                                           "Unable to query query.select_list table: #%d %s",
 741                                           errnum, msg ? msg : "No description available" ));
 742         }
 743
 744         return select_list;
 745 }
 746
 747 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result ) {
 748
 749         // Get the column values
 750         int id                   = dbi_result_get_int_idx( result, 1 );
 751         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
 752         int seq_no               = dbi_result_get_int_idx( result, 3 );
 753         int expression_id        = dbi_result_get_int_idx( result, 4 );
 754         const char* column_alias = dbi_result_get_string_idx( result, 5 );
 755         int grouped_by           = oils_result_get_bool_idx( result, 6 );
 756
 757         // Construct an Expression
 758         Expression* expression = getExpression( state, expression_id );
 759         if( !expression ) {
 760                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 761                         "Unable to fetch expression for id = %d", expression_id ));
 762                 return NULL;
 763         };
 764
 765         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
 766
 767         SelectItem* sel;
 768         if( free_select_item_list ) {
 769                 sel = free_select_item_list;
 770                 free_select_item_list = free_select_item_list->next;
 771         } else
 772                 sel = safe_malloc( sizeof( SelectItem ) );
 773
 774         sel->next            = NULL;
 775         sel->id              = id;
 776         sel->stored_query_id = stored_query_id;
 777         sel->seq_no          = seq_no;
 778         sel->expression      = expression;
 779         sel->column_alias    = column_alias ? strdup( column_alias ) : NULL;
 780         sel->grouped_by      = grouped_by;
 781
 782         return sel;
 783 }
 784
 785 static void selectListFree( SelectItem* sel ) {
 786         if( !sel )
 787                 return;    // Nothing to free
 788
 789         SelectItem* first = sel;
 790         while( 1 ) {
 791                 free( sel->column_alias );
 792                 sel->column_alias = NULL;
 793                 expressionFree( sel->expression );
 794                 sel->expression = NULL;
 795
 796                 if( NULL == sel->next ) {
 797                         sel->next = free_select_item_list;
 798                         break;
 799                 } else
 800                         sel = sel->next;
 801         };
 802
 803         // Transfer the entire list to the free list
 804         free_select_item_list = first;
 805 }
 806
 807 static Expression* getExpression( BuildSQLState* state, int id ) {
 808
 809         // Check the stack to see if the current expression is nested inside itself.  If it is,
 810         // then abort in order to avoid infinite recursion.  If it isn't, then add it to the
 811         // stack.  (Make sure to pop it off the stack before returning.)
 812         if( searchIdStack( state->expr_stack, id, NULL )) {
 813                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 814                         "Infinite recursion detected; expression # %d is nested within itself", id ));
 815                 state->error = 1;
 816                 return NULL;
 817         } else
 818                 push_id( &state->expr_stack, id, NULL );
 819
 820                 Expression* exp = NULL;
 821         dbi_result result = dbi_conn_queryf( state->dbhandle,
 822                 "SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, "
 823                 "column_name, left_operand, operator, right_operand, function_id, subquery, cast_type "
 824                 "FROM query.expression WHERE id = %d;", id );
 825         if( result ) {
 826                 if( dbi_result_first_row( result ) ) {
 827                         exp = constructExpression( state, result );
 828                         if( exp ) {
 829                                 PRINT( "Got an expression\n" );
 830                                 PRINT( "\tid = %d\n", exp->id );
 831                                 PRINT( "\ttype = %d\n", exp->type );
 832                                 PRINT( "\tparenthesize = %d\n", exp->parenthesize );
 833                                 PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" );
 834                         } else
 835                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 836                                         "Unable to construct an Expression for id = %d", id ));
 837                 }
 838         } else {
 839                 const char* msg;
 840                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 841                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 842                         "Unable to query query.expression table: #%d %s",
 843                         errnum, msg ? msg : "No description available" ));
 844         }
 845
 846         pop_id( &state->expr_stack );
 847         return exp;
 848 }
 849
 850 static Expression* constructExpression( BuildSQLState* state, dbi_result result ) {
 851
 852         int id = dbi_result_get_int_idx( result, 1 );
 853         const char* type_str = dbi_result_get_string_idx( result, 2 );
 854
 855         ExprType type;
 856         if( !strcmp( type_str, "xbet" ))
 857                 type = EXP_BETWEEN;
 858         else if( !strcmp( type_str, "xbool" ))
 859                 type = EXP_BOOL;
 860         else if( !strcmp( type_str, "xcase" ))
 861                 type = EXP_CASE;
 862         else if( !strcmp( type_str, "xcast" ))
 863                 type = EXP_CAST;
 864         else if( !strcmp( type_str, "xcol" ))
 865                 type = EXP_COLUMN;
 866         else if( !strcmp( type_str, "xex" ))
 867                 type = EXP_EXIST;
 868         else if( !strcmp( type_str, "xfld" ))
 869                 type = EXP_FIELD;
 870         else if( !strcmp( type_str, "xfunc" ))
 871                 type = EXP_FUNCTION;
 872         else if( !strcmp( type_str, "xin" ))
 873                 type = EXP_IN;
 874         else if( !strcmp( type_str, "xnbet" ))
 875                 type = EXP_NOT_BETWEEN;
 876         else if( !strcmp( type_str, "xnex" ))
 877                 type = EXP_NOT_EXIST;
 878         else if( !strcmp( type_str, "xnin" ))
 879                 type = EXP_NOT_IN;
 880         else if( !strcmp( type_str, "xnull" ))
 881                 type = EXP_NULL;
 882         else if( !strcmp( type_str, "xnum" ))
 883                 type = EXP_NUMBER;
 884         else if( !strcmp( type_str, "xop" ))
 885                 type = EXP_OPERATOR;
 886         else if( !strcmp( type_str, "xstr" ))
 887                 type = EXP_STRING;
 888         else if( !strcmp( type_str, "xsubq" ))
 889                 type = EXP_SUBQUERY;
 890         else
 891                 type = EXP_NULL;     // shouldn't happen due to database constraint
 892
 893         int parenthesize = oils_result_get_bool_idx( result, 3 );
 894
 895         int parent_expr_id;
 896         if( dbi_result_field_is_null_idx( result, 4 ))
 897                 parent_expr_id = -1;
 898         else
 899                 parent_expr_id = dbi_result_get_int_idx( result, 4 );
 900
 901         int seq_no = dbi_result_get_int_idx( result, 5 );
 902         const char* literal = dbi_result_get_string_idx( result, 6 );
 903         const char* table_alias = dbi_result_get_string_idx( result, 7 );
 904         const char* column_name = dbi_result_get_string_idx( result, 8 );
 905
 906         int left_operand_id;
 907         if( dbi_result_field_is_null_idx( result, 9 ))
 908                 left_operand_id = -1;
 909         else
 910                 left_operand_id = dbi_result_get_int_idx( result, 9 );
 911
 912         const char* operator = dbi_result_get_string_idx( result, 10 );
 913
 914         int right_operand_id;
 915         if( dbi_result_field_is_null_idx( result, 11 ))
 916                 right_operand_id = -1;
 917         else
 918                 right_operand_id = dbi_result_get_int_idx( result, 11 );
 919
 920         int function_id;
 921         if( dbi_result_field_is_null_idx( result, 12 ))
 922                 function_id = -1;
 923         else
 924                 function_id = dbi_result_get_int_idx( result, 12 );
 925
 926         int subquery_id;
 927         if( dbi_result_field_is_null_idx( result, 13 ))
 928                 subquery_id = -1;
 929         else
 930                 subquery_id = dbi_result_get_int_idx( result, 13 );
 931
 932         int cast_type_id;
 933         if( dbi_result_field_is_null_idx( result, 14 ))
 934                 cast_type_id = -1;
 935         else
 936                 cast_type_id = dbi_result_get_int_idx( result, 14 );
 937
 938         Expression* left_operand = NULL;
 939         Expression* right_operand = NULL;
 940         StoredQ* subquery = NULL;
 941
 942         if( EXP_OPERATOR == type ) {
 943                 // Load left and/or right operands
 944                 if( -1 == left_operand_id && -1 == right_operand_id ) {
 945                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 946                                 "Expression # %d is an operator with no operands", id ));
 947                         state->error = 1;
 948                         return NULL;
 949                 }
 950
 951                 if( left_operand_id != -1 ) {
 952                         left_operand = getExpression( state, left_operand_id );
 953                         if( !left_operand ) {
 954                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 955                                         "Unable to get left operand in expression # %d", id ));
 956                                 state->error = 1;
 957                                 return NULL;
 958                         }
 959                 }
 960
 961                 if( right_operand_id != -1 ) {
 962                         right_operand = getExpression( state, right_operand_id );
 963                         if( !right_operand ) {
 964                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 965                                         "Unable to get right operand in expression # %d", id ));
 966                                 state->error = 1;
 967                                 expressionFree( left_operand );
 968                                 return NULL;
 969                         }
 970                 }
 971         } else if( EXP_IN == type ) {
 972                 if( -1 == left_operand_id ) {
 973                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 974                                 "IN condition has no left operand in expression # %d", id ));
 975                         state->error = 1;
 976                         return NULL;
 977                 } else {
 978                         left_operand = getExpression( state, left_operand_id );
 979                         if( !left_operand ) {
 980                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 981                                         "Unable to get left operand for IN condition in expression # %d", id ));
 982                                 state->error = 1;
 983                                 return NULL;
 984                         }
 985                 }
 986
 987                 if( -1 == subquery_id ) {
 988                         // To do: load IN list of subexpressions
 989                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 990                                 "IN lists not yet supported for expression # %d", id ));
 991                         state->error = 1;
 992                         return NULL;
 993                 } else {
 994                         subquery = getStoredQuery( state, subquery_id );
 995                         if( !subquery ) {
 996                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 997                                         "Unable to load subquery for IN expression # %d", id ));
 998                                 state->error = 1;
 999                                 return NULL;
1000                         }
1001                 }
1002         } else if( EXP_EXIST == type ) {
1003                 if( -1 == subquery_id ) {
1004                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1005                                 "Internal error: No subquery found for EXIST expression # %d", id ));
1006                         state->error = 1;
1007                         return NULL;
1008                 } else {
1009                         subquery = getStoredQuery( state, subquery_id );
1010                         if( !subquery ) {
1011                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1012                                         "Unable to load subquery for EXIST expression # %d", id ));
1013                                 state->error = 1;
1014                                 return NULL;
1015                         }
1016                 }
1017         } else if( EXP_SUBQUERY == type ) {
1018                 if( -1 == subquery_id ) {
1019                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1020                                 "Subquery expression # %d has no query id", id ));
1021                         state->error = 1;
1022                         return NULL;
1023                 } else {
1024                         // Load a subquery, if there is one
1025                         subquery = getStoredQuery( state, subquery_id );
1026                         if( !subquery ) {
1027                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1028                                         "Unable to load subquery for expression # %d", id ));
1029                                 state->error = 1;
1030                                 return NULL;
1031                         }
1032                         if( subquery->select_list && subquery->select_list->next ) {
1033                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1034                                         "Subquery # %d as expression returns more than one column", subquery_id ));
1035                                 state->error = 1;
1036                                 return NULL;
1037                         }
1038                         PRINT( "\tExpression is subquery %d\n", subquery_id );
1039                 }
1040         }
1041
1042         // Allocate an Expression: from the free list if possible, from the heap if necessary
1043         Expression* exp = NULL;
1044         if( free_expression_list ) {
1045                 exp = free_expression_list;
1046                 free_expression_list = free_expression_list->next;
1047         } else
1048                 exp = safe_malloc( sizeof( Expression ) );
1049
1050         // Populate the Expression
1051         exp->next = NULL;
1052         exp->id = id;
1053         exp->type = type;
1054         exp->parenthesize = parenthesize;
1055         exp->parent_expr_id = parent_expr_id;
1056         exp->seq_no = seq_no;
1057         exp->literal = literal ? strdup( literal ) : NULL;
1058         exp->table_alias = table_alias ? strdup( table_alias ) : NULL;
1059         exp->column_name = column_name ? strdup( column_name ) : NULL;
1060         exp->left_operand = left_operand;
1061         exp->op = operator ? strdup( operator ) : NULL;
1062         exp->right_operand = right_operand;
1063         exp->function_id = function_id;
1064         exp->subquery_id = subquery_id;
1065         exp->subquery = subquery;
1066         exp->cast_type_id = subquery_id;
1067
1068         return exp;
1069 }
1070
1071 /**
1072         @brief Deallocate an Expression.
1073         @param exp Pointer to the Expression to be deallocated.
1074
1075         Free the strings owned by the Expression.  Put the Expressions itself into a free list.
1076 */
1077 static void expressionFree( Expression* exp ) {
1078         if( exp ) {
1079                 free( exp->literal );
1080                 exp->literal = NULL;
1081                 free( exp->table_alias );
1082                 exp->table_alias = NULL;
1083                 free( exp->column_name );
1084                 exp->column_name = NULL;
1085                 if( exp->left_operand ) {
1086                         expressionFree( exp->left_operand );
1087                         exp->left_operand = NULL;
1088                 }
1089                 free( exp->op );
1090                 exp->op = NULL;
1091                 if( exp->right_operand ) {
1092                         expressionFree( exp->right_operand );
1093                         exp->right_operand = NULL;
1094                 }
1095                 if( exp->subquery ) {
1096                         storedQFree( exp->subquery );
1097                         exp->subquery = NULL;
1098                 }
1099
1100                 exp->next = free_expression_list;
1101                 free_expression_list = exp;
1102         }
1103 }
1104
1105 static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) {
1106         OrderItem* ord_list = NULL;
1107
1108         // The ORDER BY is in descending order so that we can build the list by adding to
1109         // the head, and it will wind up in the right order.
1110         dbi_result result = dbi_conn_queryf( state->dbhandle,
1111                 "SELECT id, stored_query, seq_no, expression "
1112                 "FROM query.order_by_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
1113         if( result ) {
1114                 if( dbi_result_first_row( result ) ) {
1115                         while( 1 ) {
1116                                 OrderItem* item = constructOrderItem( state, result );
1117                                 if( item ) {
1118                                         PRINT( "Found an ORDER BY item\n" );
1119
1120                                         item->next = ord_list;
1121                                         ord_list = item;
1122                                 } else {
1123                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1124                                                 "Unable to build ORDER BY item for query id #%d", query_id ));
1125                                         orderItemListFree( ord_list );
1126                                         ord_list = NULL;
1127                                         break;
1128                                 }
1129                                 if( !dbi_result_next_row( result ) )
1130                                         break;
1131                         };
1132                 }
1133         }  else {
1134                 const char* msg;
1135                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1136                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1137                         "Unable to query query.order_by_list table: #%d %s",
1138                         errnum, msg ? msg : "No description available" ));
1139         }
1140
1141         return ord_list;
1142 }
1143
1144 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ) {
1145         int id                   = dbi_result_get_int_idx( result, 1 );
1146         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
1147         int seq_no               = dbi_result_get_int_idx( result, 3 );
1148         int expression_id        = dbi_result_get_int_idx( result, 4 );
1149         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
1150
1151         // Construct an Expression
1152         Expression* expression = getExpression( state, expression_id );
1153         if( !expression ) {
1154                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1155                         "Unable to fetch ORDER BY expression for id = %d", expression_id ));
1156                 return NULL;
1157         };
1158
1159         // Allocate an OrderItem; from the free list if possible, or from the heap if necessary.
1160         OrderItem* ord;
1161         if( free_order_item_list ) {
1162                 ord = free_order_item_list;
1163                 free_order_item_list = free_order_item_list->next;
1164         } else
1165                 ord = safe_malloc( sizeof( OrderItem ));
1166
1167         ord->next            = NULL;
1168         ord->id              = id;
1169         ord->stored_query_id = stored_query_id;
1170         ord->seq_no          = seq_no;
1171         ord->expression      = expression;
1172
1173         return ord;
1174 }
1175
1176 /**
1177         @brief Deallocate a linked list of OrderItems.
1178         @param exp Pointer to the first OrderItem in the list to be deallocated.
1179
1180         Deallocate the memory owned by the OrderItems.  Put the items themselves into a free list.
1181 */
1182 static void orderItemListFree( OrderItem* ord ) {
1183         if( !ord )
1184                 return;    // Nothing to free
1185
1186         OrderItem* first = ord;
1187         while( 1 ) {
1188                 expressionFree( ord->expression );
1189                 ord->expression = NULL;
1190
1191                 if( NULL == ord->next ) {
1192                         ord->next = free_order_item_list;
1193                         break;
1194                 } else
1195                         ord = ord->next;
1196         };
1197
1198         // Transfer the entire list to the free list
1199         free_order_item_list = first;
1200 }
1201
1202 /**
1203         @brief Push an IdNode onto a stack of IdNodes.
1204         @param stack Pointer to the stack.
1205         @param id Id of the new node.
1206         @param alias Alias, if any, of the new node.
1207 */
1208 static void push_id( IdNode** stack, int id, const char* alias ) {
1209
1210         if( stack ) {
1211                 // Allocate a node; from the free list if possible, from the heap if necessary.
1212                 IdNode* node = NULL;
1213                 if( free_id_node_list ) {
1214                         node = free_id_node_list;
1215                         free_id_node_list = free_id_node_list->next;
1216                 } else
1217                         node = safe_malloc( sizeof( IdNode ));
1218
1219                 // Populate it
1220                 node->next = *stack;
1221                 node->id = id;
1222                 if( alias )
1223                         node->alias = strdup( alias );
1224                 else
1225                         node->alias = NULL;
1226
1227                 // Reseat the stack
1228                 *stack = node;
1229         }
1230 }
1231
1232 /**
1233         @brief Remove the node at the top of an IdNode stack.
1234         @param stack Pointer to the IdNode stack.
1235 */
1236 void pop_id( IdNode** stack ) {
1237         if( stack ) {
1238                 IdNode* node = *stack;
1239                 *stack = node->next;
1240
1241                 if( node->alias ) {
1242                         free( node->alias );
1243                         node->alias = NULL;
1244                 }
1245
1246                 node->next = free_id_node_list;
1247                 free_id_node_list = node;
1248         }
1249 }
1250
1251 /**
1252         @brief Search a stack of IDs for a match by either ID or, optionally, by alias.
1253         @param stack Pointer to the stack.
1254         @param id The id to search for.
1255         @param alias (Optional) the alias to search for.
1256         @return A pointer to the matching node if one is found, or NULL if not.
1257
1258         This search is used to detect cases where a query, expression, or FROM clause is nested
1259         inside itself, in order to avoid infinite recursion; or in order to avoid conflicting
1260         table references in a FROM clause.
1261 */
1262 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias ) {
1263         if( stack ) {
1264                 const IdNode* node = stack;
1265                 while( node ) {
1266                         if( node->id == id )
1267                                 return node;        // Matched on id
1268                         else if( alias && node->alias && !strcmp( alias, node->alias ))
1269                                 return node;        // Matched on alias
1270                         else
1271                                 node = node->next;
1272                 }
1273         }
1274         return NULL;   // No match found
1275 }
1276
1277 /**
1278         @brief Free up any resources held by the StoredQ module.
1279 */
1280 void storedQCleanup( void ) {
1281
1282         // Free all the nodes in the free state list
1283         StoredQ* sq = free_storedq_list;
1284         while( sq ) {
1285                 free_storedq_list = sq->next;
1286                 free( sq );
1287                 sq = free_storedq_list;
1288         }
1289
1290         // Free all the nodes in the free from_relation list
1291         FromRelation* fr = free_from_relation_list;
1292         while( fr ) {
1293                 free_from_relation_list = fr->next;
1294                 free( fr );
1295                 fr = free_from_relation_list;
1296         }
1297
1298         // Free all the nodes in the free expression list
1299         Expression* exp = free_expression_list;
1300         while( exp ) {
1301                 free_expression_list = exp->next;
1302                 free( exp );
1303                 exp = free_expression_list;
1304         }
1305
1306         // Free all the nodes in the free select item list
1307         SelectItem* sel = free_select_item_list;
1308         while( sel ) {
1309                 free_select_item_list = sel->next;
1310                 free( sel );
1311                 sel = free_select_item_list;
1312         }
1313
1314         // Free all the nodes in the free select item list
1315         IdNode* node = free_id_node_list;
1316         while( node ) {
1317                 free_id_node_list = node->next;
1318                 free( node );
1319                 node = free_id_node_list;
1320         }
1321
1322         // Free all the nodes in the free query sequence list
1323         QSeq* seq = free_qseq_list;
1324         while( seq ) {
1325                 free_qseq_list = seq->next;
1326                 free( seq );
1327                 seq = free_qseq_list;
1328         }
1329
1330         // Free all the nodes in the free order item list
1331         OrderItem* ord = free_order_item_list;
1332         while( ord ) {
1333                 free_order_item_list = ord->next;
1334                 free( ord );
1335                 ord = free_order_item_list;
1336         }
1337 }
1338
1339 /**
1340         @brief Return a boolean value from a database result.
1341         @param result The database result.
1342         @param i Index of the column in the result, starting with 1 );
1343         @return 1 if true, or 0 for false.
1344
1345         Null values and error conditions are interpreted as FALSE.
1346 */
1347 static int oils_result_get_bool_idx( dbi_result result, int i ) {
1348         if( result ) {
1349                 const char* str = dbi_result_get_string_idx( result, i );
1350                 return (str && *str == 't' ) ? 1 : 0;
1351         } else
1352                 return 0;
1353 }
1354
1355 void oilsStoredQSetVerbose( void ) {
1356         verbose = 1;
1357 }