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