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