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