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