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

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