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_BIND == type ) {
1191                 if( bind_variable ) {
1192                         // To do: Build a BindVar
1193                         bind = getBindVar( state, bind_variable );
1194                         if( ! bind ) {
1195                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1196                                                                 "Unable to load bind variable \"%s\" for expression # %d",
1197                 bind_variable, id ));
1198                                 state->error = 1;
1199                                 return NULL;
1200                         }
1201                         PRINT( "\tBind variable is \"%s\"\n", bind_variable );
1202                 } else {
1203                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1204                                                         "No variable specified for bind variable expression # %d",
1205            bind_variable, id ));
1206                         state->error = 1;
1207                         return NULL;
1208                 }
1209         } else if( EXP_OPERATOR == type ) {
1210                 // Load left and/or right operands
1211                 if( -1 == left_operand_id && -1 == right_operand_id ) {
1212                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1213                                 "Expression # %d is an operator with no operands", id ));
1214                         state->error = 1;
1215                         return NULL;
1216                 }
1217
1218                 if( left_operand_id != -1 ) {
1219                         left_operand = getExpression( state, left_operand_id );
1220                         if( !left_operand ) {
1221                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1222                                         "Unable to get left operand in expression # %d", id ));
1223                                 state->error = 1;
1224                                 return NULL;
1225                         }
1226                 }
1227
1228                 if( right_operand_id != -1 ) {
1229                         right_operand = getExpression( state, right_operand_id );
1230                         if( !right_operand ) {
1231                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1232                                         "Unable to get right operand in expression # %d", id ));
1233                                 state->error = 1;
1234                                 expressionFree( left_operand );
1235                                 return NULL;
1236                         }
1237                 }
1238         } else if( EXP_IN == type ) {
1239                 if( -1 == left_operand_id ) {
1240                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1241                                 "IN condition has no left operand in expression # %d", id ));
1242                         state->error = 1;
1243                         return NULL;
1244                 } else {
1245                         left_operand = getExpression( state, left_operand_id );
1246                         if( !left_operand ) {
1247                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1248                                         "Unable to get left operand for IN condition in expression # %d", id ));
1249                                 state->error = 1;
1250                                 return NULL;
1251                         }
1252                 }
1253
1254                 if( -1 == subquery_id ) {
1255                         // Load an IN list of subexpressions
1256                         subexp_list = getExpressionList( state, id );
1257                         if( state->error ) {
1258                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1259                                         "Unable to get subexpressions for IN list" ));
1260                                 return NULL;
1261                         } else if( !subexp_list ) {
1262                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1263                                         "IN list is empty in expression # %d", id ));
1264                                 state->error = 1;
1265                                 return NULL;
1266                         }
1267                 } else {
1268                         // Load a subquery
1269                         subquery = getStoredQuery( state, subquery_id );
1270                         if( !subquery ) {
1271                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1272                                         "Unable to load subquery for IN expression # %d", id ));
1273                                 state->error = 1;
1274                                 return NULL;
1275                         }
1276                 }
1277         } else if( EXP_ISNULL == type ) {
1278                 if( -1 == left_operand_id ) {
1279                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1280                                 "Expression # %d IS NULL has no left operand", id ));
1281                         state->error = 1;
1282                         return NULL;
1283                 }
1284
1285                 if( left_operand_id != -1 ) {
1286                         left_operand = getExpression( state, left_operand_id );
1287                         if( !left_operand ) {
1288                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1289                                         "Unable to get left operand in expression # %d", id ));
1290                                 state->error = 1;
1291                                 return NULL;
1292                         }
1293                 }
1294         } else if( EXP_EXIST == type ) {
1295                 if( -1 == subquery_id ) {
1296                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1297                                 "Internal error: No subquery found for EXIST expression # %d", id ));
1298                         state->error = 1;
1299                         return NULL;
1300                 } else {
1301                         subquery = getStoredQuery( state, subquery_id );
1302                         if( !subquery ) {
1303                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1304                                         "Unable to load subquery for EXIST expression # %d", id ));
1305                                 state->error = 1;
1306                                 return NULL;
1307                         }
1308                 }
1309         } else if( EXP_SERIES == type ) {
1310                 subexp_list = getExpressionList( state, id );
1311                 if( state->error ) {
1312                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1313                                 "Unable to get subexpressions for expression series using operator \"%s\"",
1314                                         operator ? operator : "," ));
1315                         return NULL;
1316                 } else if( !subexp_list ) {
1317                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1318                                 "Series expression is empty in expression # %d", id ));
1319                         state->error = 1;
1320                         return NULL;
1321                 }
1322
1323         } else if( EXP_SUBQUERY == type ) {
1324                 if( -1 == subquery_id ) {
1325                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1326                                 "Subquery expression # %d has no query id", id ));
1327                         state->error = 1;
1328                         return NULL;
1329                 } else {
1330                         // Load a subquery, if there is one
1331                         subquery = getStoredQuery( state, subquery_id );
1332                         if( !subquery ) {
1333                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1334                                         "Unable to load subquery for expression # %d", id ));
1335                                 state->error = 1;
1336                                 return NULL;
1337                         }
1338                         if( subquery->select_list && subquery->select_list->next ) {
1339                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1340                                         "Subquery # %d as expression returns more than one column", subquery_id ));
1341                                 state->error = 1;
1342                                 return NULL;
1343                         }
1344                         PRINT( "\tExpression is subquery %d\n", subquery_id );
1345                 }
1346         }
1347
1348         // Allocate an Expression: from the free list if possible, from the heap if necessary
1349         Expression* exp = NULL;
1350         if( free_expression_list ) {
1351                 exp = free_expression_list;
1352                 free_expression_list = free_expression_list->next;
1353         } else
1354                 exp = safe_malloc( sizeof( Expression ) );
1355
1356         // Populate the Expression
1357         exp->next = NULL;
1358         exp->id = id;
1359         exp->type = type;
1360         exp->parenthesize = parenthesize;
1361         exp->parent_expr_id = parent_expr_id;
1362         exp->seq_no = seq_no;
1363         exp->literal = literal ? strdup( literal ) : NULL;
1364         exp->table_alias = table_alias ? strdup( table_alias ) : NULL;
1365         exp->column_name = column_name ? strdup( column_name ) : NULL;
1366         exp->left_operand = left_operand;
1367         exp->op = operator ? strdup( operator ) : NULL;
1368         exp->right_operand = right_operand;
1369         exp->function_id = function_id;
1370         exp->subquery_id = subquery_id;
1371         exp->subquery = subquery;
1372         exp->cast_type_id = subquery_id;
1373         exp->negate = negate;
1374         exp->bind = bind;
1375         exp->subexp_list = subexp_list;
1376
1377         return exp;
1378 }
1379
1380 /**
1381         @brief Free all the Expressions in a linked list of Expressions.
1382         @param exp Pointer to the first Expression in the list.
1383 */
1384 static void expressionListFree( Expression* exp ) {
1385         while( exp ) {
1386                 Expression* next = exp->next;
1387                 expressionFree( exp );
1388                 exp = next;
1389         }
1390 }
1391
1392 /**
1393         @brief Deallocate an Expression.
1394         @param exp Pointer to the Expression to be deallocated.
1395
1396         Free the strings owned by the Expression.  Put the Expression itself, and any
1397         subexpressions that it owns, into a free list.
1398 */
1399 static void expressionFree( Expression* exp ) {
1400         if( exp ) {
1401                 free( exp->literal );
1402                 exp->literal = NULL;
1403                 free( exp->table_alias );
1404                 exp->table_alias = NULL;
1405                 free( exp->column_name );
1406                 exp->column_name = NULL;
1407                 if( exp->left_operand ) {
1408                         expressionFree( exp->left_operand );
1409                         exp->left_operand = NULL;
1410                 }
1411                 free( exp->op );
1412                 exp->op = NULL;
1413                 if( exp->right_operand ) {
1414                         expressionFree( exp->right_operand );
1415                         exp->right_operand = NULL;
1416                 }
1417                 if( exp->subquery ) {
1418                         storedQFree( exp->subquery );
1419                         exp->subquery = NULL;
1420                 }
1421
1422                 // We don't free the bind member here because the Expression doesn't own it;
1423                 // the bindvar_list hash owns it, so that multiple Expressions can reference it.
1424
1425                 if( exp->subexp_list ) {
1426                         // Free the linked list of subexpressions
1427                         expressionListFree( exp->subexp_list );
1428                         exp->subexp_list = NULL;
1429                 }
1430
1431                 // Prepend to the free list
1432                 exp->next = free_expression_list;
1433                 free_expression_list = exp;
1434         }
1435 }
1436
1437 /**
1438         @brief Build a list of subexpressions.
1439         @param state Pointer to the query-building context.
1440         @param id ID of the parent Expression.
1441         @return A pointer to the first in a linked list of Expressions, if there are any; or
1442                 NULL if there aren't any, or in case of an error.
1443 */
1444 static Expression* getExpressionList( BuildSQLState* state, int id ) {
1445         Expression* exp_list = NULL;
1446
1447         // The ORDER BY is in descending order so that we can build the list by adding to
1448         // the head, and it will wind up in the right order.
1449         dbi_result result = dbi_conn_queryf( state->dbhandle,
1450                 "SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, column_name, "
1451                 "left_operand, operator, right_operand, function_id, subquery, cast_type, negate, "
1452                 "bind_variable "
1453                 "FROM query.expression WHERE parent_expr = %d "
1454                 "ORDER BY seq_no desc;", id );
1455
1456         if( result ) {
1457                 if( dbi_result_first_row( result ) ) {
1458                         while( 1 ) {
1459                                 Expression* exp = constructExpression( state, result );
1460                                 if( exp ) {
1461                                         PRINT( "Found a subexpression\n" );
1462                                         exp->next = exp_list;
1463                                         exp_list  = exp;
1464                                 } else {
1465                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1466                                                 "Unable to build subexpression list for expression id #%d", id ));
1467                                         expressionListFree( exp_list );
1468                                         exp_list = NULL;
1469                                         break;
1470                                 }
1471                                 if( !dbi_result_next_row( result ) )
1472                                         break;
1473                         };
1474                 }
1475         } else {
1476                 const char* msg;
1477                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1478                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1479                         "Unable to query query.expression table for expression list: #%d %s",
1480                         errnum, msg ? msg : "No description available" ));
1481                 state->error = 1;
1482         }
1483
1484         return exp_list;
1485 }
1486
1487 /**
1488         @brief Build a list of ORDER BY items as a linked list of OrderItems.
1489         @param state Pointer to the query-building context.
1490         @param query_id ID for the query to which the ORDER BY belongs.
1491         @return Pointer to the first node in a linked list of OrderItems.
1492
1493         The calling code is responsible for freeing the list by calling orderItemListFree().
1494 */
1495 static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) {
1496         OrderItem* ord_list = NULL;
1497
1498         // The ORDER BY is in descending order so that we can build the list by adding to
1499         // the head, and it will wind up in the right order.
1500         dbi_result result = dbi_conn_queryf( state->dbhandle,
1501                 "SELECT id, stored_query, seq_no, expression "
1502                 "FROM query.order_by_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
1503         if( result ) {
1504                 if( dbi_result_first_row( result ) ) {
1505                         while( 1 ) {
1506                                 OrderItem* item = constructOrderItem( state, result );
1507                                 if( item ) {
1508                                         PRINT( "Found an ORDER BY item\n" );
1509
1510                                         item->next = ord_list;
1511                                         ord_list = item;
1512                                 } else {
1513                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1514                                                 "Unable to build ORDER BY item for query id #%d", query_id ));
1515                                         orderItemListFree( ord_list );
1516                                         ord_list = NULL;
1517                                         break;
1518                                 }
1519                                 if( !dbi_result_next_row( result ) )
1520                                         break;
1521                         };
1522                 }
1523         }  else {
1524                 const char* msg;
1525                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1526                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1527                         "Unable to query query.order_by_list table: #%d %s",
1528                         errnum, msg ? msg : "No description available" ));
1529                 state->error = 1;
1530         }
1531
1532         return ord_list;
1533 }
1534
1535 /**
1536         @brief Construct an OrderItem.
1537         @param Pointer to the query-building context.
1538         @param result Database cursor positioned at a row in query.order_by_item.
1539         @return Pointer to a newly constructed OrderItem, if successful, or NULL if not.
1540
1541         The calling code is responsible for freeing the OrderItems by calling orderItemListFree().
1542 */
1543 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ) {
1544         int id                   = dbi_result_get_int_idx( result, 1 );
1545         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
1546         int seq_no               = dbi_result_get_int_idx( result, 3 );
1547         int expression_id        = dbi_result_get_int_idx( result, 4 );
1548         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
1549
1550         // Construct an Expression
1551         Expression* expression = getExpression( state, expression_id );
1552         if( !expression ) {
1553                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1554                         "Unable to fetch ORDER BY expression for id = %d", expression_id ));
1555                 state->error = 1;
1556                 return NULL;
1557         };
1558
1559         // Allocate an OrderItem; from the free list if possible, or from the heap if necessary.
1560         OrderItem* ord;
1561         if( free_order_item_list ) {
1562                 ord = free_order_item_list;
1563                 free_order_item_list = free_order_item_list->next;
1564         } else
1565                 ord = safe_malloc( sizeof( OrderItem ));
1566
1567         ord->next            = NULL;
1568         ord->id              = id;
1569         ord->stored_query_id = stored_query_id;
1570         ord->seq_no          = seq_no;
1571         ord->expression      = expression;
1572
1573         return ord;
1574 }
1575
1576 /**
1577         @brief Deallocate a linked list of OrderItems.
1578         @param exp Pointer to the first OrderItem in the list to be deallocated.
1579
1580         Deallocate the memory owned by the OrderItems.  Put the items themselves into a free list.
1581 */
1582 static void orderItemListFree( OrderItem* ord ) {
1583         if( !ord )
1584                 return;    // Nothing to free
1585
1586         OrderItem* first = ord;
1587         while( 1 ) {
1588                 expressionFree( ord->expression );
1589                 ord->expression = NULL;
1590
1591                 if( NULL == ord->next ) {
1592                         ord->next = free_order_item_list;
1593                         break;
1594                 } else
1595                         ord = ord->next;
1596         };
1597
1598         // Transfer the entire list to the free list
1599         free_order_item_list = first;
1600 }
1601
1602 /**
1603         @brief Build a list of column names for a specified query.
1604         @param state Pointer to the query-building context.
1605         @param query Pointer to the specified query.
1606         @return Pointer to a newly-allocated JSON_ARRAY of column names.
1607
1608         In the resulting array, each entry is either a JSON_STRING or (when no column name is
1609         available) a JSON_NULL.
1610
1611         The calling code is responsible for freeing the list by calling jsonObjectFree().
1612 */
1613 jsonObject* oilsGetColNames( BuildSQLState* state, StoredQ* query ) {
1614         if( !state || !query )
1615                 return NULL;
1616
1617         // Save the outermost query id for possible use in an error message
1618         int id = query->id;
1619
1620         while( query->type != QT_SELECT ) {
1621                 // If the query is a UNION, INTERSECT, or EXCEPT, there must be a SELECT in
1622                 // there somewhere.  Find the first one, and use the SELECT list from that.
1623                 QSeq* child_list = query->child_list;
1624                 if( !child_list ) {
1625                         query = NULL;
1626                         break;
1627                 } else
1628                         query = child_list->child_query;
1629         }
1630
1631         if( !query ) {
1632                 state->error = 1;
1633                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1634                         "Unable to find first SELECT in query # %d", id ));
1635                 return NULL;
1636         }
1637
1638         // Get the SELECT list for the first SELECT
1639         SelectItem* col = query->select_list;
1640         if( !col ) {
1641                 state->error = 1;
1642                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1643                         "First SELECT in query # %d has empty SELECT list", id ));
1644                         return NULL;
1645         }
1646
1647         jsonObject* col_list = jsonNewObjectType( JSON_ARRAY );
1648
1649         // Traverse the list, adding an entry for each
1650         do {
1651                 const char* alias = NULL;
1652                 if( col->column_alias )
1653                         alias = col->column_alias;
1654                 else {
1655                         Expression* expression = col->expression;
1656                         if( expression && EXP_COLUMN == expression->type && expression->column_name )
1657                                 alias = expression->column_name;
1658                 }
1659
1660                 jsonObjectPush( col_list, jsonNewObject( alias ) );
1661                 col = col->next;
1662         } while( col );
1663
1664         return col_list;
1665 }
1666
1667 /**
1668         @brief Push an IdNode onto a stack of IdNodes.
1669         @param stack Pointer to the stack.
1670         @param id Id of the new node.
1671         @param alias Alias, if any, of the new node.
1672 */
1673 static void push_id( IdNode** stack, int id, const char* alias ) {
1674
1675         if( stack ) {
1676                 // Allocate a node; from the free list if possible, from the heap if necessary.
1677                 IdNode* node = NULL;
1678                 if( free_id_node_list ) {
1679                         node = free_id_node_list;
1680                         free_id_node_list = free_id_node_list->next;
1681                 } else
1682                         node = safe_malloc( sizeof( IdNode ));
1683
1684                 // Populate it
1685                 node->next = *stack;
1686                 node->id = id;
1687                 if( alias )
1688                         node->alias = strdup( alias );
1689                 else
1690                         node->alias = NULL;
1691
1692                 // Reseat the stack
1693                 *stack = node;
1694         }
1695 }
1696
1697 /**
1698         @brief Remove the node at the top of an IdNode stack.
1699         @param stack Pointer to the IdNode stack.
1700 */
1701 void pop_id( IdNode** stack ) {
1702         if( stack ) {
1703                 IdNode* node = *stack;
1704                 *stack = node->next;
1705
1706                 if( node->alias ) {
1707                         free( node->alias );
1708                         node->alias = NULL;
1709                 }
1710
1711                 node->next = free_id_node_list;
1712                 free_id_node_list = node;
1713         }
1714 }
1715
1716 /**
1717         @brief Search a stack of IDs for a match by either ID or, optionally, by alias.
1718         @param stack Pointer to the stack.
1719         @param id The id to search for.
1720         @param alias (Optional) the alias to search for.
1721         @return A pointer to the matching node if one is found, or NULL if not.
1722
1723         This search is used to detect cases where a query, expression, or FROM clause is nested
1724         inside itself, in order to avoid infinite recursion; or in order to avoid conflicting
1725         table references in a FROM clause.
1726 */
1727 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias ) {
1728         if( stack ) {
1729                 const IdNode* node = stack;
1730                 while( node ) {
1731                         if( node->id == id )
1732                                 return node;        // Matched on id
1733                         else if( alias && node->alias && !strcmp( alias, node->alias ))
1734                                 return node;        // Matched on alias
1735                         else
1736                                 node = node->next;
1737                 }
1738         }
1739         return NULL;   // No match found
1740 }
1741
1742 /**
1743         @brief Free up any resources held by the StoredQ module.
1744 */
1745 void storedQCleanup( void ) {
1746
1747         // Free all the nodes in the free state list
1748         StoredQ* sq = free_storedq_list;
1749         while( sq ) {
1750                 free_storedq_list = sq->next;
1751                 free( sq );
1752                 sq = free_storedq_list;
1753         }
1754
1755         // Free all the nodes in the free from_relation list
1756         FromRelation* fr = free_from_relation_list;
1757         while( fr ) {
1758                 free_from_relation_list = fr->next;
1759                 free( fr );
1760                 fr = free_from_relation_list;
1761         }
1762
1763         // Free all the nodes in the free expression list
1764         Expression* exp = free_expression_list;
1765         while( exp ) {
1766                 free_expression_list = exp->next;
1767                 free( exp );
1768                 exp = free_expression_list;
1769         }
1770
1771         // Free all the nodes in the free select item list
1772         SelectItem* sel = free_select_item_list;
1773         while( sel ) {
1774                 free_select_item_list = sel->next;
1775                 free( sel );
1776                 sel = free_select_item_list;
1777         }
1778
1779         // Free all the nodes in the free select item list
1780         IdNode* node = free_id_node_list;
1781         while( node ) {
1782                 free_id_node_list = node->next;
1783                 free( node );
1784                 node = free_id_node_list;
1785         }
1786
1787         // Free all the nodes in the free query sequence list
1788         QSeq* seq = free_qseq_list;
1789         while( seq ) {
1790                 free_qseq_list = seq->next;
1791                 free( seq );
1792                 seq = free_qseq_list;
1793         }
1794
1795         // Free all the nodes in the free order item list
1796         OrderItem* ord = free_order_item_list;
1797         while( ord ) {
1798                 free_order_item_list = ord->next;
1799                 free( ord );
1800                 ord = free_order_item_list;
1801         }
1802
1803         // Free all the nodes in the bind variable free list
1804         BindVar* bind = free_bindvar_list;
1805         while( bind ) {
1806                 free_bindvar_list = bind->next;
1807                 free( bind );
1808                 bind = free_bindvar_list;
1809         }
1810 }
1811
1812 /**
1813         @brief Return a boolean value from a database result.
1814         @param result The database result.
1815         @param i Index of the column in the result, starting with 1 );
1816         @return 1 if true, or 0 for false.
1817
1818         Null values and error conditions are interpreted as FALSE.
1819 */
1820 static int oils_result_get_bool_idx( dbi_result result, int i ) {
1821         if( result ) {
1822                 const char* str = dbi_result_get_string_idx( result, i );
1823                 return (str && *str == 't' ) ? 1 : 0;
1824         } else
1825                 return 0;
1826 }
1827
1828 /**
1829         @brief Enable verbose messages.
1830
1831         The messages are written to standard output, which for a server is /dev/null.  Hence this
1832         option is useful only for a non-server.  It is intended only as a convenience for
1833         development and debugging.
1834 */
1835 void oilsStoredQSetVerbose( void ) {
1836         verbose = 1;
1837 }