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

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