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 static SelectItem* getSelectList( BuildSQLState* state, int query_id ) {
 775         SelectItem* select_list = NULL;
 776
 777         // The ORDER BY is in descending order so that we can build the list by adding to
 778         // the head, and it will wind up in the right order.
 779         dbi_result result = dbi_conn_queryf( state->dbhandle,
 780                 "SELECT id, stored_query, seq_no, expression, column_alias, grouped_by "
 781                 "FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
 782         if( result ) {
 783                 if( dbi_result_first_row( result ) ) {
 784                         while( 1 ) {
 785                                 SelectItem* item = constructSelectItem( state, result );
 786                                 if( item ) {
 787                                         PRINT( "Found a SELECT item\n" );
 788                                         PRINT( "\tid: %d\n", item->id );
 789                                         PRINT( "\tstored_query_id: %d\n", item->stored_query_id );
 790                                         PRINT( "\tseq_no: %d\n", item->seq_no );
 791                                         PRINT( "\tcolumn_alias: %s\n",
 792                                                         item->column_alias ? item->column_alias : "(none)" );
 793                                         PRINT( "\tgrouped_by: %d\n", item->grouped_by );
 794
 795                                         item->next = select_list;
 796                                         select_list = item;
 797                                 } else {
 798                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 799                                                 "Unable to build select list for query id #%d", query_id ));
 800                                         selectListFree( select_list );
 801                                         select_list = NULL;
 802                                         break;
 803                                 }
 804                                 if( !dbi_result_next_row( result ) )
 805                                         break;
 806                         };
 807                 }
 808         } else {
 809                 const char* msg;
 810                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 811                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 812                         "Unable to query query.select_list table: #%d %s",
 813                         errnum, msg ? msg : "No description available" ));
 814                 state->error = 1;
 815         }
 816
 817         return select_list;
 818 }
 819
 820 /**
 821         @brief Construct a SelectItem.
 822         @param Pointer to the query-building context.
 823         @param result Database cursor positioned at a row in query.select_item.
 824         @return Pointer to a newly constructed SelectItem, if successful, or NULL if not.
 825
 826         The calling code is responsible for freeing the SelectItems by calling selectListFree().
 827 */
 828 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result ) {
 829
 830         // Get the column values
 831         int id                   = dbi_result_get_int_idx( result, 1 );
 832         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
 833         int seq_no               = dbi_result_get_int_idx( result, 3 );
 834         int expression_id        = dbi_result_get_int_idx( result, 4 );
 835         const char* column_alias = dbi_result_get_string_idx( result, 5 );
 836         int grouped_by           = oils_result_get_bool_idx( result, 6 );
 837
 838         // Construct an Expression
 839         Expression* expression = getExpression( state, expression_id );
 840         if( !expression ) {
 841                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 842                         "Unable to fetch expression for id = %d", expression_id ));
 843                 state->error = 1;
 844                 return NULL;
 845         };
 846
 847         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
 848
 849         SelectItem* sel;
 850         if( free_select_item_list ) {
 851                 sel = free_select_item_list;
 852                 free_select_item_list = free_select_item_list->next;
 853         } else
 854                 sel = safe_malloc( sizeof( SelectItem ) );
 855
 856         sel->next            = NULL;
 857         sel->id              = id;
 858         sel->stored_query_id = stored_query_id;
 859         sel->seq_no          = seq_no;
 860         sel->expression      = expression;
 861         sel->column_alias    = column_alias ? strdup( column_alias ) : NULL;
 862         sel->grouped_by      = grouped_by;
 863
 864         return sel;
 865 }
 866
 867 /**
 868         @brief Free a list of SelectItems.
 869         @param sel Pointer to the first item in the list to be freed.
 870
 871         Free the column alias and expression owned by each item.  Put the entire list into a free
 872         list of SelectItems.
 873 */
 874 static void selectListFree( SelectItem* sel ) {
 875         if( !sel )
 876                 return;    // Nothing to free
 877
 878         SelectItem* first = sel;
 879         while( 1 ) {
 880                 free( sel->column_alias );
 881                 sel->column_alias = NULL;
 882                 expressionFree( sel->expression );
 883                 sel->expression = NULL;
 884
 885                 if( NULL == sel->next ) {
 886                         sel->next = free_select_item_list;
 887                         break;
 888                 } else
 889                         sel = sel->next;
 890         };
 891
 892         // Transfer the entire list to the free list
 893         free_select_item_list = first;
 894 }
 895
 896 /**
 897         @brief Given the name of a bind variable, build a corresponding BindVar.
 898         @param state Pointer to the query-building context.
 899         @param name Name of the bind variable.
 900         @return Pointer to the newly-built BindVar.
 901
 902         Since the same bind variable may appear multiple times, we load it only once for the
 903         entire query, and reference the one copy wherever needed.
 904 */
 905 static BindVar* getBindVar( BuildSQLState* state, const char* name ) {
 906         BindVar* bind = NULL;
 907         if( state->bindvar_list ) {
 908                 bind = osrfHashGet( state->bindvar_list, name );
 909                 if( bind )
 910                         return bind;   // Already loaded it...
 911         }
 912
 913         // Load a BindVar from the Database.
 914         dbi_result result = dbi_conn_queryf( state->dbhandle,
 915                 "SELECT name, type, description, default_value, label "
 916                 "FROM query.bind_variable WHERE name = \'%s\';", name );
 917         if( result ) {
 918                 if( dbi_result_first_row( result ) ) {
 919                         bind = constructBindVar( state, result );
 920                         if( bind ) {
 921                                 PRINT( "Got a bind variable for %s\n", name );
 922                         } else
 923                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 924                                         "Unable to load bind variable \"%s\"", name ));
 925                 } else {
 926                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 927                                 "No bind variable found with name \"%s\"", name ));
 928                 }
 929         } else {
 930                 const char* msg;
 931                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 932                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 933                         "Unable to query query.bind_variable table for \"%s\": #%d %s",
 934                         name, errnum, msg ? msg : "No description available" ));
 935                 state->error = 1;
 936         }
 937
 938         if( bind ) {
 939                 // Add the new bind variable to the list
 940                 if( !state->bindvar_list ) {
 941                         // Don't have a list yet?  Start one.
 942                         state->bindvar_list = osrfNewHash();
 943                         osrfHashSetCallback( state->bindvar_list, bindVarFree );
 944                 }
 945                 osrfHashSet( state->bindvar_list, bind, name );
 946         } else
 947                 state->error = 1;
 948
 949         return bind;
 950 }
 951
 952 /**
 953         @brief Construct a BindVar to represent a bind variable.
 954         @param Pointer to the query-building context.
 955         @param result Database cursor positioned at a row in query.bind_variable.
 956         @return Pointer to a newly constructed BindVar, if successful, or NULL if not.
 957
 958         The calling code is responsible for freeing the BindVar by calling bindVarFree().
 959 */
 960 static BindVar* constructBindVar( BuildSQLState* state, dbi_result result ) {
 961
 962         const char* name = dbi_result_get_string_idx( result, 1 );
 963
 964         const char* type_str = dbi_result_get_string_idx( result, 2 );
 965         BindVarType type;
 966         if( !strcmp( type_str, "string" ))
 967                 type = BIND_STR;
 968         else if( !strcmp( type_str, "number" ))
 969                 type = BIND_NUM;
 970         else if( !strcmp( type_str, "string_list" ))
 971                 type = BIND_STR_LIST;
 972         else if( !strcmp( type_str, "number_list" ))
 973                 type = BIND_NUM_LIST;;
 974
 975         const char* description = dbi_result_get_string_idx( result, 3 );
 976
 977         // The default value is encoded as JSON.  Translate it into a jsonObject.
 978         const char* default_value_str = dbi_result_get_string_idx( result, 4 );
 979         jsonObject* default_value = NULL;
 980         if( default_value_str ) {
 981                 default_value = jsonParse( default_value_str );
 982                 if( !default_value ) {
 983                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 984                                 "Unable to parse JSON string for default value of bind variable \"%s\"",
 985                                 name ));
 986                         state->error = 1;
 987                         return NULL;
 988                 }
 989         }
 990
 991         const char* label = dbi_result_get_string_idx( result, 5 );
 992
 993         // Allocate a BindVar: from the free list if possible, from the heap if necessary
 994         BindVar* bind = NULL;
 995         if( free_bindvar_list ) {
 996                 bind = free_bindvar_list;
 997                 free_bindvar_list = free_bindvar_list->next;
 998         } else
 999                 bind = safe_malloc( sizeof( BindVar ) );
1000
1001         bind->next = NULL;
1002         bind->name = strdup( name );
1003         bind->label = strdup( label );
1004         bind->type = type;
1005         bind->description = strdup( description );
1006         bind->default_value = default_value;
1007         bind->actual_value = NULL;
1008
1009         return bind;
1010 }
1011
1012 /**
1013         @brief Deallocate a BindVar.
1014         @param key Pointer to the bind variable name (not used).
1015         @param p Pointer to the BindVar to be deallocated, cast to a void pointer.
1016
1017         Free the strings and jsonObjects owned by the BindVar, and put the BindVar itself into a
1018         free list.
1019
1020         This function is a callback installed in an osrfHash; hence the peculiar signature.
1021 */
1022 static void bindVarFree( char* key, void* p ) {
1023         if( p ) {
1024                 BindVar* bind = p;
1025                 free( bind->name );
1026                 free( bind->label );
1027                 free( bind->description );
1028                 if( bind->default_value ) {
1029                         jsonObjectFree( bind->default_value );
1030                         bind->default_value = NULL;
1031                 }
1032                 if( bind->actual_value ) {
1033                         jsonObjectFree( bind->actual_value );
1034                         bind->actual_value = NULL;
1035                 }
1036
1037                 // Prepend to free list
1038                 bind->next = free_bindvar_list;
1039                 free_bindvar_list = bind;
1040         }
1041 }
1042
1043 /**
1044         @brief Given an id for a row in query.expression, build an Expression struct.
1045         @param Pointer to the query-building context.
1046         @param id ID of a row in query.expression.
1047         @return Pointer to a newly-created Expression if successful, or NULL if not.
1048 */
1049 static Expression* getExpression( BuildSQLState* state, int id ) {
1050
1051         // Check the stack to see if the current expression is nested inside itself.  If it is,
1052         // then abort in order to avoid infinite recursion.  If it isn't, then add it to the
1053         // stack.  (Make sure to pop it off the stack before returning.)
1054         if( searchIdStack( state->expr_stack, id, NULL )) {
1055                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1056                         "Infinite recursion detected; expression # %d is nested within itself", id ));
1057                 state->error = 1;
1058                 return NULL;
1059         } else
1060                 push_id( &state->expr_stack, id, NULL );
1061
1062         Expression* exp = NULL;
1063         dbi_result result = dbi_conn_queryf( state->dbhandle,
1064                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
1065                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
1066                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
1067                 "func.function_name, COALESCE(func.is_aggregate, false) "
1068                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
1069                 "ON (exp.function_id = func.id) "
1070                 "WHERE exp.id = %d;", id );
1071         if( result ) {
1072                 if( dbi_result_first_row( result ) ) {
1073                         exp = constructExpression( state, result );
1074                         if( exp ) {
1075                                 PRINT( "Got an expression\n" );
1076                                 PRINT( "\tid = %d\n", exp->id );
1077                                 PRINT( "\ttype = %d\n", exp->type );
1078                                 PRINT( "\tparenthesize = %d\n", exp->parenthesize );
1079                                 PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" );
1080                         } else
1081                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1082                                         "Unable to construct an Expression for id = %d", id ));
1083                 }
1084         } else {
1085                 const char* msg;
1086                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1087                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1088                         "Unable to query query.expression table: #%d %s",
1089                         errnum, msg ? msg : "No description available" ));
1090                 state->error = 1;
1091         }
1092
1093         pop_id( &state->expr_stack );
1094         return exp;
1095 }
1096
1097 /**
1098         @brief Construct an Expression.
1099         @param Pointer to the query-building context.
1100         @param result Database cursor positioned at a row in query.expression.
1101         @return Pointer to a newly constructed Expression, if successful, or NULL if not.
1102
1103         The calling code is responsible for freeing the Expression by calling expressionFree().
1104 */
1105 static Expression* constructExpression( BuildSQLState* state, dbi_result result ) {
1106
1107         int id = dbi_result_get_int_idx( result, 1 );
1108         const char* type_str = dbi_result_get_string_idx( result, 2 );
1109
1110         ExprType type;
1111         if( !strcmp( type_str, "xbet" ))
1112                 type = EXP_BETWEEN;
1113         else if( !strcmp( type_str, "xbind" ))
1114                 type = EXP_BIND;
1115         else if( !strcmp( type_str, "xbool" ))
1116                 type = EXP_BOOL;
1117         else if( !strcmp( type_str, "xcase" ))
1118                 type = EXP_CASE;
1119         else if( !strcmp( type_str, "xcast" ))
1120                 type = EXP_CAST;
1121         else if( !strcmp( type_str, "xcol" ))
1122                 type = EXP_COLUMN;
1123         else if( !strcmp( type_str, "xex" ))
1124                 type = EXP_EXIST;
1125         else if( !strcmp( type_str, "xfld" ))
1126                 type = EXP_FIELD;
1127         else if( !strcmp( type_str, "xfunc" ))
1128                 type = EXP_FUNCTION;
1129         else if( !strcmp( type_str, "xin" ))
1130                 type = EXP_IN;
1131         else if( !strcmp( type_str, "xisnull" ))
1132                 type = EXP_ISNULL;
1133         else if( !strcmp( type_str, "xnull" ))
1134                 type = EXP_NULL;
1135         else if( !strcmp( type_str, "xnum" ))
1136                 type = EXP_NUMBER;
1137         else if( !strcmp( type_str, "xop" ))
1138                 type = EXP_OPERATOR;
1139         else if( !strcmp( type_str, "xser" ))
1140                 type = EXP_SERIES;
1141         else if( !strcmp( type_str, "xstr" ))
1142                 type = EXP_STRING;
1143         else if( !strcmp( type_str, "xsubq" ))
1144                 type = EXP_SUBQUERY;
1145         else
1146                 type = EXP_NULL;     // shouldn't happen due to database constraint
1147
1148         int parenthesize = oils_result_get_bool_idx( result, 3 );
1149
1150         int parent_expr_id;
1151         if( dbi_result_field_is_null_idx( result, 4 ))
1152                 parent_expr_id = -1;
1153         else
1154                 parent_expr_id = dbi_result_get_int_idx( result, 4 );
1155
1156         int seq_no = dbi_result_get_int_idx( result, 5 );
1157         const char* literal = dbi_result_get_string_idx( result, 6 );
1158         const char* table_alias = dbi_result_get_string_idx( result, 7 );
1159         const char* column_name = dbi_result_get_string_idx( result, 8 );
1160
1161         int left_operand_id;
1162         if( dbi_result_field_is_null_idx( result, 9 ))
1163                 left_operand_id = -1;
1164         else
1165                 left_operand_id = dbi_result_get_int_idx( result, 9 );
1166
1167         const char* operator = dbi_result_get_string_idx( result, 10 );
1168
1169         int right_operand_id;
1170         if( dbi_result_field_is_null_idx( result, 11 ))
1171                 right_operand_id = -1;
1172         else
1173                 right_operand_id = dbi_result_get_int_idx( result, 11 );
1174
1175         int subquery_id;
1176         if( dbi_result_field_is_null_idx( result, 12 ))
1177                 subquery_id = -1;
1178         else
1179                 subquery_id = dbi_result_get_int_idx( result, 12 );
1180
1181         int cast_type_id;
1182         if( dbi_result_field_is_null_idx( result, 13 ))
1183                 cast_type_id = -1;
1184         else
1185                 cast_type_id = dbi_result_get_int_idx( result, 13 );
1186
1187         int negate = oils_result_get_bool_idx( result, 14 );
1188         const char* bind_variable = dbi_result_get_string_idx( result, 15 );
1189         const char* function_name = dbi_result_get_string_idx( result, 16 );
1190         int is_aggregate = oils_result_get_bool_idx( result, 17 );
1191
1192         Expression* left_operand = NULL;
1193         Expression* right_operand = NULL;
1194         StoredQ* subquery = NULL;
1195         BindVar* bind = NULL;
1196         Expression* subexp_list = NULL;
1197
1198         if( EXP_BETWEEN == type ) {
1199                 // Get the left operand
1200                 if( -1 == left_operand_id ) {
1201                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1202                                 "No left operand defined for BETWEEN expression # %d", id ));
1203                         state->error = 1;
1204                         return NULL;
1205                 } else {
1206                         left_operand = getExpression( state, left_operand_id );
1207                         if( !left_operand ) {
1208                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1209                                         "Unable to get left operand in BETWEEN expression # %d", id ));
1210                                 state->error = 1;
1211                                 return NULL;
1212                         }
1213                 }
1214
1215                 // Get the end points of the BETWEEN range
1216                 subexp_list = getExpressionList( state, id );
1217                 if( state->error ) {
1218                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1219                                 "Unable to get subexpressions for BETWEEN expression # %d", id ));
1220                         expressionFree( left_operand );
1221                         return NULL;
1222                 } else if( !subexp_list ) {
1223                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1224                                 "BETWEEN range is empty in expression # %d", id ));
1225                         state->error = 1;
1226                         expressionFree( left_operand );
1227                         return NULL;
1228                 } else if( !subexp_list->next ) {
1229                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1230                                 "BETWEEN range has only one end point in expression # %d", id ));
1231                         state->error = 1;
1232                         expressionListFree( subexp_list );
1233                         expressionFree( left_operand );
1234                         return NULL;
1235                 } else if( subexp_list->next->next ) {
1236                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1237                                 "BETWEEN range has more than two subexpressions in expression # %d", id ));
1238                         state->error = 1;
1239                         expressionListFree( subexp_list );
1240                         expressionFree( left_operand );
1241                         return NULL;
1242                 }
1243
1244         } else if( EXP_BIND == type ) {
1245                 if( bind_variable ) {
1246                         // To do: Build a BindVar
1247                         bind = getBindVar( state, bind_variable );
1248                         if( ! bind ) {
1249                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1250                                                                 "Unable to load bind variable \"%s\" for expression # %d",
1251                 bind_variable, id ));
1252                                 state->error = 1;
1253                                 return NULL;
1254                         }
1255                         PRINT( "\tBind variable is \"%s\"\n", bind_variable );
1256                 } else {
1257                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1258                                                         "No variable specified for bind variable expression # %d",
1259            bind_variable, id ));
1260                         state->error = 1;
1261                         return NULL;
1262                 }
1263                 if( right_operand_id != -1 ) {
1264                         right_operand = getExpression( state, right_operand_id );
1265                         if( !right_operand ) {
1266                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1267                                         "Unable to get right operand in expression # %d", id ));
1268                                 state->error = 1;
1269                                 expressionFree( left_operand );
1270                                 return NULL;
1271                         }
1272                 }
1273
1274         } else if( EXP_EXIST == type ) {
1275                 if( -1 == subquery_id ) {
1276                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1277                                 "Internal error: No subquery found for EXIST expression # %d", id ));
1278                         state->error = 1;
1279                         return NULL;
1280                 } else {
1281                         subquery = getStoredQuery( state, subquery_id );
1282                         if( !subquery ) {
1283                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1284                                         "Unable to load subquery for EXIST expression # %d", id ));
1285                                 state->error = 1;
1286                                 return NULL;
1287                         }
1288                 }
1289
1290         } else if( EXP_FUNCTION == type ) {
1291                 if( !function_name ) {
1292                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1293                                 "Function call expression # %d provides no function name", id ));
1294                         state->error = 1;
1295                         return NULL;
1296                 } else {
1297                         subexp_list = getExpressionList( state, id );
1298                         if( state->error ) {
1299                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1300                                         "Unable to get parameter list for function expression # %d", id ));
1301                                 return NULL;
1302                         }
1303                 }
1304
1305         } else if( EXP_IN == type ) {
1306                 if( -1 == left_operand_id ) {
1307                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1308                                 "IN condition has no left operand in expression # %d", id ));
1309                         state->error = 1;
1310                         return NULL;
1311                 } else {
1312                         left_operand = getExpression( state, left_operand_id );
1313                         if( !left_operand ) {
1314                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1315                                         "Unable to get left operand for IN condition in expression # %d", id ));
1316                                 state->error = 1;
1317                                 return NULL;
1318                         }
1319                 }
1320
1321                 if( -1 == subquery_id ) {
1322                         // Load an IN list of subexpressions
1323                         subexp_list = getExpressionList( state, id );
1324                         if( state->error ) {
1325                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1326                                         "Unable to get subexpressions for IN list" ));
1327                                 return NULL;
1328                         } else if( !subexp_list ) {
1329                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1330                                         "IN list is empty in expression # %d", id ));
1331                                 state->error = 1;
1332                                 return NULL;
1333                         }
1334                 } else {
1335                         // Load a subquery
1336                         subquery = getStoredQuery( state, subquery_id );
1337                         if( !subquery ) {
1338                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1339                                         "Unable to load subquery for IN expression # %d", id ));
1340                                 state->error = 1;
1341                                 return NULL;
1342                         }
1343                 }
1344
1345         } else if( EXP_ISNULL == type ) {
1346                 if( -1 == left_operand_id ) {
1347                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1348                                 "Expression # %d IS NULL has no left operand", id ));
1349                         state->error = 1;
1350                         return NULL;
1351                 }
1352
1353                 if( left_operand_id != -1 ) {
1354                         left_operand = getExpression( state, left_operand_id );
1355                         if( !left_operand ) {
1356                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1357                                         "Unable to get left operand in expression # %d", id ));
1358                                 state->error = 1;
1359                                 return NULL;
1360                         }
1361                 }
1362
1363         } else if( EXP_NUMBER == type ) {
1364                 if( !literal ) {
1365                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1366                                 "Numeric expression # %d provides no numeric value", id ));
1367                         state->error = 1;
1368                         return NULL;
1369                 }
1370
1371         } else if( EXP_OPERATOR == type ) {
1372                 // Load left and/or right operands
1373                 if( -1 == left_operand_id && -1 == right_operand_id ) {
1374                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1375                                 "Expression # %d is an operator with no operands", id ));
1376                         state->error = 1;
1377                         return NULL;
1378                 }
1379
1380                 if( left_operand_id != -1 ) {
1381                         left_operand = getExpression( state, left_operand_id );
1382                         if( !left_operand ) {
1383                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1384                                         "Unable to get left operand in expression # %d", id ));
1385                                 state->error = 1;
1386                                 return NULL;
1387                         }
1388                 }
1389
1390                 if( right_operand_id != -1 ) {
1391                         right_operand = getExpression( state, right_operand_id );
1392                         if( !right_operand ) {
1393                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1394                                                                 "Unable to get right operand in expression # %d", id ));
1395                                 state->error = 1;
1396                                 return NULL;
1397                         }
1398                 }
1399
1400         } else if( EXP_SERIES == type ) {
1401                 subexp_list = getExpressionList( state, id );
1402                 if( state->error ) {
1403                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1404                                 "Unable to get subexpressions for expression series using operator \"%s\"",
1405                                         operator ? operator : "," ));
1406                         return NULL;
1407                 } else if( !subexp_list ) {
1408                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1409                                 "Series expression is empty in expression # %d", id ));
1410                         state->error = 1;
1411                         return NULL;
1412                 }
1413
1414         } else if( EXP_STRING == type ) {
1415                 if( !literal ) {
1416                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1417                                 "String expression # %d provides no string value", id ));
1418                         state->error = 1;
1419                         return NULL;
1420                 }
1421
1422         } else if( EXP_SUBQUERY == type ) {
1423                 if( -1 == subquery_id ) {
1424                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1425                                 "Subquery expression # %d has no query id", id ));
1426                         state->error = 1;
1427                         return NULL;
1428                 } else {
1429                         // Load a subquery, if there is one
1430                         subquery = getStoredQuery( state, subquery_id );
1431                         if( !subquery ) {
1432                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1433                                         "Unable to load subquery for expression # %d", id ));
1434                                 state->error = 1;
1435                                 return NULL;
1436                         }
1437                         if( subquery->select_list && subquery->select_list->next ) {
1438                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1439                                         "Subquery # %d as expression returns more than one column", subquery_id ));
1440                                 state->error = 1;
1441                                 return NULL;
1442                         }
1443                         PRINT( "\tExpression is subquery %d\n", subquery_id );
1444                 }
1445         }
1446
1447         // Allocate an Expression: from the free list if possible, from the heap if necessary
1448         Expression* exp = NULL;
1449         if( free_expression_list ) {
1450                 exp = free_expression_list;
1451                 free_expression_list = free_expression_list->next;
1452         } else
1453                 exp = safe_malloc( sizeof( Expression ) );
1454
1455         // Populate the Expression
1456         exp->next = NULL;
1457         exp->id = id;
1458         exp->type = type;
1459         exp->parenthesize = parenthesize;
1460         exp->parent_expr_id = parent_expr_id;
1461         exp->seq_no = seq_no;
1462         exp->literal = literal ? strdup( literal ) : NULL;
1463         exp->table_alias = table_alias ? strdup( table_alias ) : NULL;
1464         exp->column_name = column_name ? strdup( column_name ) : NULL;
1465         exp->left_operand = left_operand;
1466         exp->op = operator ? strdup( operator ) : NULL;
1467         exp->right_operand = right_operand;
1468         exp->subquery_id = subquery_id;
1469         exp->subquery = subquery;
1470         exp->cast_type_id = subquery_id;
1471         exp->negate = negate;
1472         exp->bind = bind;
1473         exp->subexp_list = subexp_list;
1474         exp->function_name = function_name ? strdup( function_name ) : NULL;
1475         exp->is_aggregate = is_aggregate;
1476
1477         return exp;
1478 }
1479
1480 /**
1481         @brief Free all the Expressions in a linked list of Expressions.
1482         @param exp Pointer to the first Expression in the list.
1483 */
1484 static void expressionListFree( Expression* exp ) {
1485         while( exp ) {
1486                 Expression* next = exp->next;
1487                 expressionFree( exp );
1488                 exp = next;
1489         }
1490 }
1491
1492 /**
1493         @brief Deallocate an Expression.
1494         @param exp Pointer to the Expression to be deallocated.
1495
1496         Free the strings owned by the Expression.  Put the Expression itself, and any
1497         subexpressions that it owns, into a free list.
1498 */
1499 static void expressionFree( Expression* exp ) {
1500         if( exp ) {
1501                 free( exp->literal );
1502                 exp->literal = NULL;
1503                 free( exp->table_alias );
1504                 exp->table_alias = NULL;
1505                 free( exp->column_name );
1506                 exp->column_name = NULL;
1507                 if( exp->left_operand ) {
1508                         expressionFree( exp->left_operand );
1509                         exp->left_operand = NULL;
1510                 }
1511                 free( exp->op );
1512                 exp->op = NULL;
1513                 if( exp->right_operand ) {
1514                         expressionFree( exp->right_operand );
1515                         exp->right_operand = NULL;
1516                 }
1517                 if( exp->subquery ) {
1518                         storedQFree( exp->subquery );
1519                         exp->subquery = NULL;
1520                 }
1521
1522                 // We don't free the bind member here because the Expression doesn't own it;
1523                 // the bindvar_list hash owns it, so that multiple Expressions can reference it.
1524
1525                 if( exp->subexp_list ) {
1526                         // Free the linked list of subexpressions
1527                         expressionListFree( exp->subexp_list );
1528                         exp->subexp_list = NULL;
1529                 }
1530
1531                 if( exp->function_name ) {
1532                         free( exp->function_name );
1533                         exp->function_name = NULL;
1534                 }
1535
1536                 // Prepend to the free list
1537                 exp->next = free_expression_list;
1538                 free_expression_list = exp;
1539         }
1540 }
1541
1542 /**
1543         @brief Build a list of subexpressions.
1544         @param state Pointer to the query-building context.
1545         @param id ID of the parent Expression.
1546         @return A pointer to the first in a linked list of Expressions, if there are any; or
1547                 NULL if there aren't any, or in case of an error.
1548 */
1549 static Expression* getExpressionList( BuildSQLState* state, int id ) {
1550         Expression* exp_list = NULL;
1551
1552         // The ORDER BY is in descending order so that we can build the list by adding to
1553         // the head, and it will wind up in the right order.
1554         dbi_result result = dbi_conn_queryf( state->dbhandle,
1555                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
1556                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
1557                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
1558                 "func.function_name, COALESCE(func.is_aggregate, false) "
1559                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
1560                 "ON (exp.function_id = func.id) "
1561                 "WHERE exp.parent_expr = %d "
1562                 "ORDER BY exp.seq_no desc;", id );
1563
1564         if( result ) {
1565                 if( dbi_result_first_row( result ) ) {
1566                         while( 1 ) {
1567                                 Expression* exp = constructExpression( state, result );
1568                                 if( exp ) {
1569                                         PRINT( "Found a subexpression\n" );
1570                                         exp->next = exp_list;
1571                                         exp_list  = exp;
1572                                 } else {
1573                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1574                                                 "Unable to build subexpression list for expression id #%d", id ));
1575                                         expressionListFree( exp_list );
1576                                         exp_list = NULL;
1577                                         break;
1578                                 }
1579                                 if( !dbi_result_next_row( result ) )
1580                                         break;
1581                         };
1582                 }
1583         } else {
1584                 const char* msg;
1585                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1586                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1587                         "Unable to query query.expression table for expression list: #%d %s",
1588                         errnum, msg ? msg : "No description available" ));
1589                 state->error = 1;
1590         }
1591
1592         return exp_list;
1593 }
1594
1595 /**
1596         @brief Build a list of ORDER BY items as a linked list of OrderItems.
1597         @param state Pointer to the query-building context.
1598         @param query_id ID for the query to which the ORDER BY belongs.
1599         @return Pointer to the first node in a linked list of OrderItems.
1600
1601         The calling code is responsible for freeing the list by calling orderItemListFree().
1602 */
1603 static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) {
1604         OrderItem* ord_list = NULL;
1605
1606         // The ORDER BY is in descending order so that we can build the list by adding to
1607         // the head, and it will wind up in the right order.
1608         dbi_result result = dbi_conn_queryf( state->dbhandle,
1609                 "SELECT id, stored_query, seq_no, expression "
1610                 "FROM query.order_by_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
1611         if( result ) {
1612                 if( dbi_result_first_row( result ) ) {
1613                         while( 1 ) {
1614                                 OrderItem* item = constructOrderItem( state, result );
1615                                 if( item ) {
1616                                         PRINT( "Found an ORDER BY item\n" );
1617
1618                                         item->next = ord_list;
1619                                         ord_list = item;
1620                                 } else {
1621                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1622                                                 "Unable to build ORDER BY item for query id #%d", query_id ));
1623                                         orderItemListFree( ord_list );
1624                                         ord_list = NULL;
1625                                         break;
1626                                 }
1627                                 if( !dbi_result_next_row( result ) )
1628                                         break;
1629                         };
1630                 }
1631         }  else {
1632                 const char* msg;
1633                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1634                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1635                         "Unable to query query.order_by_list table: #%d %s",
1636                         errnum, msg ? msg : "No description available" ));
1637                 state->error = 1;
1638         }
1639
1640         return ord_list;
1641 }
1642
1643 /**
1644         @brief Construct an OrderItem.
1645         @param Pointer to the query-building context.
1646         @param result Database cursor positioned at a row in query.order_by_item.
1647         @return Pointer to a newly constructed OrderItem, if successful, or NULL if not.
1648
1649         The calling code is responsible for freeing the OrderItems by calling orderItemListFree().
1650 */
1651 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ) {
1652         int id                   = dbi_result_get_int_idx( result, 1 );
1653         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
1654         int seq_no               = dbi_result_get_int_idx( result, 3 );
1655         int expression_id        = dbi_result_get_int_idx( result, 4 );
1656         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
1657
1658         // Construct an Expression
1659         Expression* expression = getExpression( state, expression_id );
1660         if( !expression ) {
1661                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1662                         "Unable to fetch ORDER BY expression for id = %d", expression_id ));
1663                 state->error = 1;
1664                 return NULL;
1665         };
1666
1667         // Allocate an OrderItem; from the free list if possible, or from the heap if necessary.
1668         OrderItem* ord;
1669         if( free_order_item_list ) {
1670                 ord = free_order_item_list;
1671                 free_order_item_list = free_order_item_list->next;
1672         } else
1673                 ord = safe_malloc( sizeof( OrderItem ));
1674
1675         ord->next            = NULL;
1676         ord->id              = id;
1677         ord->stored_query_id = stored_query_id;
1678         ord->seq_no          = seq_no;
1679         ord->expression      = expression;
1680
1681         return ord;
1682 }
1683
1684 /**
1685         @brief Deallocate a linked list of OrderItems.
1686         @param exp Pointer to the first OrderItem in the list to be deallocated.
1687
1688         Deallocate the memory owned by the OrderItems.  Put the items themselves into a free list.
1689 */
1690 static void orderItemListFree( OrderItem* ord ) {
1691         if( !ord )
1692                 return;    // Nothing to free
1693
1694         OrderItem* first = ord;
1695         while( 1 ) {
1696                 expressionFree( ord->expression );
1697                 ord->expression = NULL;
1698
1699                 if( NULL == ord->next ) {
1700                         ord->next = free_order_item_list;
1701                         break;
1702                 } else
1703                         ord = ord->next;
1704         };
1705
1706         // Transfer the entire list to the free list
1707         free_order_item_list = first;
1708 }
1709
1710 /**
1711         @brief Build a list of column names for a specified query.
1712         @param state Pointer to the query-building context.
1713         @param query Pointer to the specified query.
1714         @return Pointer to a newly-allocated JSON_ARRAY of column names.
1715
1716         In the resulting array, each entry is either a JSON_STRING or (when no column name is
1717         available) a JSON_NULL.
1718
1719         The calling code is responsible for freeing the list by calling jsonObjectFree().
1720 */
1721 jsonObject* oilsGetColNames( BuildSQLState* state, StoredQ* query ) {
1722         if( !state || !query )
1723                 return NULL;
1724
1725         // Save the outermost query id for possible use in an error message
1726         int id = query->id;
1727
1728         while( query->type != QT_SELECT ) {
1729                 // If the query is a UNION, INTERSECT, or EXCEPT, there must be a SELECT in
1730                 // there somewhere.  Find the first one, and use the SELECT list from that.
1731                 QSeq* child_list = query->child_list;
1732                 if( !child_list ) {
1733                         query = NULL;
1734                         break;
1735                 } else
1736                         query = child_list->child_query;
1737         }
1738
1739         if( !query ) {
1740                 state->error = 1;
1741                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1742                         "Unable to find first SELECT in query # %d", id ));
1743                 return NULL;
1744         }
1745
1746         // Get the SELECT list for the first SELECT
1747         SelectItem* col = query->select_list;
1748         if( !col ) {
1749                 state->error = 1;
1750                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1751                         "First SELECT in query # %d has empty SELECT list", id ));
1752                         return NULL;
1753         }
1754
1755         jsonObject* col_list = jsonNewObjectType( JSON_ARRAY );
1756
1757         // Traverse the list, adding an entry for each
1758         do {
1759                 const char* alias = NULL;
1760                 if( col->column_alias )
1761                         alias = col->column_alias;
1762                 else {
1763                         Expression* expression = col->expression;
1764                         if( expression && EXP_COLUMN == expression->type && expression->column_name )
1765                                 alias = expression->column_name;
1766                 }
1767
1768                 jsonObjectPush( col_list, jsonNewObject( alias ) );
1769                 col = col->next;
1770         } while( col );
1771
1772         return col_list;
1773 }
1774
1775 /**
1776         @brief Push an IdNode onto a stack of IdNodes.
1777         @param stack Pointer to the stack.
1778         @param id Id of the new node.
1779         @param alias Alias, if any, of the new node.
1780 */
1781 static void push_id( IdNode** stack, int id, const char* alias ) {
1782
1783         if( stack ) {
1784                 // Allocate a node; from the free list if possible, from the heap if necessary.
1785                 IdNode* node = NULL;
1786                 if( free_id_node_list ) {
1787                         node = free_id_node_list;
1788                         free_id_node_list = free_id_node_list->next;
1789                 } else
1790                         node = safe_malloc( sizeof( IdNode ));
1791
1792                 // Populate it
1793                 node->next = *stack;
1794                 node->id = id;
1795                 if( alias )
1796                         node->alias = strdup( alias );
1797                 else
1798                         node->alias = NULL;
1799
1800                 // Reseat the stack
1801                 *stack = node;
1802         }
1803 }
1804
1805 /**
1806         @brief Remove the node at the top of an IdNode stack.
1807         @param stack Pointer to the IdNode stack.
1808 */
1809 void pop_id( IdNode** stack ) {
1810         if( stack ) {
1811                 IdNode* node = *stack;
1812                 *stack = node->next;
1813
1814                 if( node->alias ) {
1815                         free( node->alias );
1816                         node->alias = NULL;
1817                 }
1818
1819                 node->next = free_id_node_list;
1820                 free_id_node_list = node;
1821         }
1822 }
1823
1824 /**
1825         @brief Search a stack of IDs for a match by either ID or, optionally, by alias.
1826         @param stack Pointer to the stack.
1827         @param id The id to search for.
1828         @param alias (Optional) the alias to search for.
1829         @return A pointer to the matching node if one is found, or NULL if not.
1830
1831         This search is used to detect cases where a query, expression, or FROM clause is nested
1832         inside itself, in order to avoid infinite recursion; or in order to avoid conflicting
1833         table references in a FROM clause.
1834 */
1835 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias ) {
1836         if( stack ) {
1837                 const IdNode* node = stack;
1838                 while( node ) {
1839                         if( node->id == id )
1840                                 return node;        // Matched on id
1841                         else if( alias && node->alias && !strcmp( alias, node->alias ))
1842                                 return node;        // Matched on alias
1843                         else
1844                                 node = node->next;
1845                 }
1846         }
1847         return NULL;   // No match found
1848 }
1849
1850 /**
1851         @brief Free up any resources held by the StoredQ module.
1852 */
1853 void storedQCleanup( void ) {
1854
1855         // Free all the nodes in the free state list
1856         StoredQ* sq = free_storedq_list;
1857         while( sq ) {
1858                 free_storedq_list = sq->next;
1859                 free( sq );
1860                 sq = free_storedq_list;
1861         }
1862
1863         // Free all the nodes in the free from_relation list
1864         FromRelation* fr = free_from_relation_list;
1865         while( fr ) {
1866                 free_from_relation_list = fr->next;
1867                 free( fr );
1868                 fr = free_from_relation_list;
1869         }
1870
1871         // Free all the nodes in the free expression list
1872         Expression* exp = free_expression_list;
1873         while( exp ) {
1874                 free_expression_list = exp->next;
1875                 free( exp );
1876                 exp = free_expression_list;
1877         }
1878
1879         // Free all the nodes in the free select item list
1880         SelectItem* sel = free_select_item_list;
1881         while( sel ) {
1882                 free_select_item_list = sel->next;
1883                 free( sel );
1884                 sel = free_select_item_list;
1885         }
1886
1887         // Free all the nodes in the free select item list
1888         IdNode* node = free_id_node_list;
1889         while( node ) {
1890                 free_id_node_list = node->next;
1891                 free( node );
1892                 node = free_id_node_list;
1893         }
1894
1895         // Free all the nodes in the free query sequence list
1896         QSeq* seq = free_qseq_list;
1897         while( seq ) {
1898                 free_qseq_list = seq->next;
1899                 free( seq );
1900                 seq = free_qseq_list;
1901         }
1902
1903         // Free all the nodes in the free order item list
1904         OrderItem* ord = free_order_item_list;
1905         while( ord ) {
1906                 free_order_item_list = ord->next;
1907                 free( ord );
1908                 ord = free_order_item_list;
1909         }
1910
1911         // Free all the nodes in the bind variable free list
1912         BindVar* bind = free_bindvar_list;
1913         while( bind ) {
1914                 free_bindvar_list = bind->next;
1915                 free( bind );
1916                 bind = free_bindvar_list;
1917         }
1918 }
1919
1920 /**
1921         @brief Return a boolean value from a database result.
1922         @param result The database result.
1923         @param i Index of the column in the result, starting with 1 );
1924         @return 1 if true, or 0 for false.
1925
1926         Null values and error conditions are interpreted as FALSE.
1927 */
1928 static int oils_result_get_bool_idx( dbi_result result, int i ) {
1929         if( result ) {
1930                 const char* str = dbi_result_get_string_idx( result, i );
1931                 return (str && *str == 't' ) ? 1 : 0;
1932         } else
1933                 return 0;
1934 }
1935
1936 /**
1937         @brief Enable verbose messages.
1938
1939         The messages are written to standard output, which for a server is /dev/null.  Hence this
1940         option is useful only for a non-server.  It is intended only as a convenience for
1941         development and debugging.
1942 */
1943 void oilsStoredQSetVerbose( void ) {
1944         verbose = 1;
1945 }