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