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