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 <ctype.h>
   9 #include <dbi/dbi.h>
  10 #include "opensrf/utils.h"
  11 #include "opensrf/log.h"
  12 #include "opensrf/string_array.h"
  13 #include "opensrf/osrf_hash.h"
  14 #include "openils/oils_buildq.h"
  15
  16 #define PRINT if( verbose ) printf
  17
  18 struct IdNode_ {
  19         IdNode* next;
  20         int id;
  21         char* alias;
  22 };
  23
  24 static int oils_result_get_bool_idx( dbi_result result, int i );
  25
  26 static FromRelation* getFromRelation( BuildSQLState* state, int id );
  27 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result );
  28 static FromRelation* getJoinList( BuildSQLState* state, int id );
  29 static void joinListFree( FromRelation* join_list );
  30 static void fromRelationFree( FromRelation* fr );
  31
  32 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str );
  33 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result );
  34 static void freeQSeqList( QSeq* seq );
  35 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result );
  36
  37 static SelectItem* getSelectList( BuildSQLState* state, int query_id );
  38 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result );
  39 static void selectListFree( SelectItem* sel );
  40
  41 static BindVar* getBindVar( BuildSQLState* state, const char* name );
  42 static BindVar* constructBindVar( BuildSQLState* state, dbi_result result );
  43 static void bindVarFree( char* name, void* p );
  44
  45 static CaseBranch* getCaseBranchList( BuildSQLState* state, int parent_id );
  46 static CaseBranch* constructCaseBranch( BuildSQLState* state, dbi_result result );
  47 static void freeBranchList( CaseBranch* branch );
  48
  49 static Datatype* getDatatype( BuildSQLState* state, int id );
  50 static Datatype* constructDatatype( BuildSQLState* state, dbi_result result );
  51 static void datatypeFree( Datatype* datatype );
  52
  53 static Expression* getExpression( BuildSQLState* state, int id );
  54 static Expression* constructExpression( BuildSQLState* state, dbi_result result );
  55 static void expressionListFree( Expression* exp );
  56 static void expressionFree( Expression* exp );
  57 static Expression* getExpressionList( BuildSQLState* state, int id );
  58
  59 static OrderItem* getOrderByList( BuildSQLState* state, int query_id );
  60 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result );
  61 static void orderItemListFree( OrderItem* ord );
  62
  63 static void push_id( IdNode** stack, int id, const char* alias );
  64 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias );
  65
  66 // A series of free lists to store already-allocated objects that are not in use, for
  67 // potential reuse.  This is a hack to reduce churning through malloc() and free().
  68 static StoredQ* free_storedq_list = NULL;
  69 static FromRelation* free_from_relation_list = NULL;
  70 static SelectItem* free_select_item_list = NULL;
  71 static BindVar* free_bindvar_list = NULL;
  72 static CaseBranch* free_branch_list = NULL;
  73 static Datatype* free_datatype_list = NULL;
  74 static Expression* free_expression_list = NULL;
  75 static IdNode* free_id_node_list = NULL;
  76 static QSeq* free_qseq_list = NULL;
  77 static OrderItem* free_order_item_list = NULL;
  78
  79 // Boolean; settable by call to oilsStoredQSetVerbose(), used by PRINT macro.
  80 // The idea is to allow debugging messages from a command line test driver for ease of
  81 // testing and development, but not from a real server, where messages to stdout don't
  82 // go anywhere.
  83 static int verbose = 0;
  84
  85 /**
  86         @brief Build a list of column names for a specified query.
  87         @param state Pointer to the query-building context.
  88         @param query Pointer to the specified query.
  89         @return Pointer to a newly-allocated JSON_ARRAY of column names, if successful;
  90                 otherwise NULL.
  91
  92         The column names are those assigned by PostgreSQL, e.g.:
  93                 - a column alias, if an AS clause defines one
  94                 - a column name (not qualified by a table name or alias, even if the query
  95                   specifies one)
  96                 - where the item is a function call, the name of the function
  97                 - where the item is a subquery or other expression, whatever PostgreSQL decides on,
  98                   typically '?column?'
  99
 100         The resulting column names may include duplicates.
 101
 102         The calling code is responsible for freeing the list by calling jsonObjectFree().
 103 */
 104 jsonObject* oilsGetColNames( BuildSQLState* state, StoredQ* query ) {
 105         if( !state || !query ) return NULL;
 106
 107         // Build SQL in the usual way (with some temporary option settings)
 108         int defaults_usable = state->defaults_usable;
 109         int values_required = state->values_required;
 110         state->defaults_usable = 1;   // We can't execute the test query unless we
 111         state->values_required = 1;   // have a value for every bind variable.
 112         int rc = buildSQL( state, query );
 113         state->defaults_usable = defaults_usable;
 114         state->values_required = values_required;
 115         if( rc ) {
 116                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 117                         "Unable to build SQL statement for query id # %d", query->id ));
 118                 state->error = 1;
 119                 return NULL;
 120         }
 121
 122         // Wrap it in an outer query to get the column names, but no rows
 123         growing_buffer* wrapper = buffer_init( 80 + strlen( OSRF_BUFFER_C_STR( state->sql )));
 124         buffer_add( wrapper, "SELECT \"phony query\".* FROM (" );
 125         buffer_add( wrapper, OSRF_BUFFER_C_STR( state->sql ));
 126         buffer_chomp( wrapper );    // remove the terminating newline
 127         buffer_chomp( wrapper );    // remove the terminating semicolon
 128         buffer_add( wrapper, ") AS \"phony query\" WHERE FALSE;" );
 129
 130         // Execute the wrapped query
 131         dbi_result result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( wrapper ));
 132         buffer_free( wrapper );
 133         if( !result ) {
 134                 const char* msg;
 135                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 136                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 137                         "Unable to execute dummy query for column names: #%d %s",
 138                         errnum, msg ? msg : "No description available" ));
 139                 state->error = 1;
 140                 return NULL;
 141         }
 142
 143         // Examine the query result to get the column names
 144
 145         unsigned int num_cols = dbi_result_get_numfields( result );
 146         jsonObject* cols = jsonNewObjectType( JSON_ARRAY );
 147         unsigned int i;
 148         for( i = 1; i <= num_cols; ++i ) {
 149                 const char* fname = dbi_result_get_field_name( result, i );
 150                 if( fname )
 151                         jsonObjectPush( cols, jsonNewObject( fname ));
 152         }
 153
 154         dbi_result_free( result );
 155         return cols;
 156 }
 157
 158 /**
 159         @brief Load a stored query.
 160         @param state Pointer to the query-building context.
 161         @param query_id ID of the query in query.stored_query.
 162         @return A pointer to the newly loaded StoredQ if successful, or NULL if not.
 163
 164         The calling code is responsible for freeing the StoredQ by calling storedQFree().
 165 */
 166 StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) {
 167         if( !state )
 168                 return NULL;
 169
 170         // Check the stack to see if the current query is nested inside itself.  If it is, then
 171         // abort in order to avoid infinite recursion.  If it isn't, then add it to the stack.
 172         // (Make sure to pop it off the stack before returning.)
 173         if( searchIdStack( state->query_stack, query_id, NULL )) {
 174                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 175                         "Infinite recursion detected; query # %d is nested within itself", query_id ));
 176                 state->error = 1;
 177                 return NULL;
 178         } else
 179                 push_id( &state->query_stack, query_id, NULL );
 180
 181         StoredQ* sq = NULL;
 182         dbi_result result = dbi_conn_queryf( state->dbhandle,
 183                 "SELECT id, type, use_all, use_distinct, from_clause, where_clause, having_clause "
 184                 "FROM query.stored_query WHERE id = %d;", query_id );
 185         if( result ) {
 186                 if( dbi_result_first_row( result ) ) {
 187                         sq = constructStoredQ( state, result );
 188                         if( sq ) {
 189                                 PRINT( "Got a query row\n" );
 190                                 PRINT( "\tid: %d\n", sq->id );
 191                                 PRINT( "\ttype: %d\n", (int) sq->type );
 192                                 PRINT( "\tuse_all: %s\n", sq->use_all ? "true" : "false" );
 193                                 PRINT( "\tuse_distinct: %s\n", sq->use_distinct ? "true" : "false" );
 194                         } else
 195                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 196                                         "Unable to build a query for id = %d", query_id ));
 197                 } else {
 198                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 199                                 "Stored query not found for id %d", query_id ));
 200                         state->error = 1;
 201                 }
 202
 203                 dbi_result_free( result );
 204         } else {
 205                 const char* msg;
 206                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 207                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 208                         "Unable to query query.stored_query table: #%d %s",
 209                         errnum, msg ? msg : "No description available" ));
 210                 state->error = 1;
 211         }
 212
 213         pop_id( &state->query_stack );
 214         return sq;
 215 }
 216
 217 /**
 218         @brief Construct a StoredQ.
 219         @param Pointer to the query-building context.
 220         @param result Database cursor positioned at a row in query.stored_query.
 221         @return Pointer to a newly constructed StoredQ, if successful, or NULL if not.
 222
 223         The calling code is responsible for freeing the StoredQ by calling storedQFree().
 224 */
 225 static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) {
 226
 227         // Get the column values from the result
 228         int id               = dbi_result_get_int_idx( result, 1 );
 229         const char* type_str = dbi_result_get_string_idx( result, 2 );
 230
 231         QueryType type;
 232         if( !strcmp( type_str, "SELECT" ))
 233                 type = QT_SELECT;
 234         else if( !strcmp( type_str, "UNION" ))
 235                 type = QT_UNION;
 236         else if( !strcmp( type_str, "INTERSECT" ))
 237                 type = QT_INTERSECT;
 238         else if( !strcmp( type_str, "EXCEPT" ))
 239                 type = QT_EXCEPT;
 240         else {
 241                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 242                         "Invalid query type \"%s\"", type_str ));
 243                 return NULL;
 244         }
 245
 246         int use_all             = oils_result_get_bool_idx( result, 3 );
 247         int use_distinct        = oils_result_get_bool_idx( result, 4 );
 248
 249         int from_clause_id;
 250         if( dbi_result_field_is_null_idx( result, 5 ) )
 251                 from_clause_id = -1;
 252         else
 253                 from_clause_id = dbi_result_get_int_idx( result, 5 );
 254
 255         int where_clause_id;
 256         if( dbi_result_field_is_null_idx( result, 6 ) )
 257                 where_clause_id = -1;
 258         else
 259                 where_clause_id = dbi_result_get_int_idx( result, 6 );
 260
 261         int having_clause_id;
 262         if( dbi_result_field_is_null_idx( result, 7 ) )
 263                 having_clause_id = -1;
 264         else
 265                 having_clause_id = dbi_result_get_int_idx( result, 7 );
 266
 267         FromRelation* from_clause = NULL;
 268         if( QT_SELECT == type ) {
 269                 // A SELECT query needs a FROM clause; go get it
 270                 if( from_clause_id != -1 ) {
 271                         from_clause = getFromRelation( state, from_clause_id );
 272                         if( !from_clause ) {
 273                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 274                                         "Unable to construct FROM clause for id = %d", from_clause_id ));
 275                                 return NULL;
 276                         }
 277                 }
 278         } else {
 279                 // Must be one of UNION, INTERSECT, or EXCEPT
 280                 if( from_clause_id != -1 )
 281                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 282                                 "FROM clause found and ignored for %s query in query #%d", type_str, id ));
 283         }
 284
 285         // If this is a SELECT query, we need a SELECT list.  Go get one.
 286         SelectItem* select_list = NULL;
 287         QSeq* child_list = NULL;
 288         if( QT_SELECT == type ) {
 289                 select_list = getSelectList( state, id );
 290                 if( !select_list ) {
 291                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 292                                 "No SELECT list found for query id = %d", id ));
 293                         fromRelationFree( from_clause );
 294                         return NULL;
 295                 }
 296         } else {
 297                 // Construct child queries of UNION, INTERSECT, or EXCEPT query
 298                 child_list = loadChildQueries( state, id, type_str );
 299                 if( !child_list ) {
 300                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 301                                 "Unable to load child queries for %s query # %d", type_str, id ));
 302                         state->error = 1;
 303                         fromRelationFree( from_clause );
 304                         return NULL;
 305                 }
 306         }
 307
 308         // Get the WHERE clause, if there is one
 309         Expression* where_clause = NULL;
 310         if( where_clause_id != -1 ) {
 311                 where_clause = getExpression( state, where_clause_id );
 312                 if( ! where_clause ) {
 313                         // shouldn't happen due to foreign key constraint
 314                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 315                                 "Unable to fetch WHERE expression for query id = %d", id ));
 316                         freeQSeqList( child_list );
 317                         fromRelationFree( from_clause );
 318                         selectListFree( select_list );
 319                         state->error = 1;
 320                         return NULL;
 321                 }
 322         }
 323
 324         Expression* having_clause = NULL;
 325         if( having_clause_id != -1 ) {
 326                 having_clause = getExpression( state, having_clause_id );
 327                 if( ! having_clause ) {
 328                         // shouldn't happen due to foreign key constraint
 329                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 330                                 "Unable to fetch HAVING expression for query id = %d", id ));
 331                         expressionFree( where_clause );
 332                         freeQSeqList( child_list );
 333                         fromRelationFree( from_clause );
 334                         selectListFree( select_list );
 335                         state->error = 1;
 336                         return NULL;
 337                 }
 338         }
 339
 340         // Get the ORDER BY clause, if there is one
 341         OrderItem* order_by_list = getOrderByList( state, id );
 342         if( state->error ) {
 343                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 344                         "Unable to load ORDER BY clause for query %d", id ));
 345                 expressionFree( having_clause );
 346                 expressionFree( where_clause );
 347                 freeQSeqList( child_list );
 348                 fromRelationFree( from_clause );
 349                 selectListFree( select_list );
 350                 return NULL;
 351         }
 352
 353         // Allocate a StoredQ: from the free list if possible, from the heap if necessary
 354
 355         StoredQ* sq;
 356         if( free_storedq_list ) {
 357                 sq = free_storedq_list;
 358                 free_storedq_list = free_storedq_list->next;
 359         } else
 360                 sq = safe_malloc( sizeof( StoredQ ) );
 361
 362         // Populate the StoredQ
 363         sq->next = NULL;
 364         sq->id = id;
 365
 366         sq->type = type;
 367         sq->use_all = use_all;
 368         sq->use_distinct = use_distinct;
 369         sq->from_clause = from_clause;
 370         sq->where_clause = where_clause;
 371         sq->select_list = select_list;
 372         sq->child_list = child_list;
 373         sq->having_clause = having_clause;
 374         sq->order_by_list = order_by_list;
 375
 376         return sq;
 377 }
 378
 379 /**
 380         @brief Load the child queries subordinate to a UNION, INTERSECT, or EXCEPT query.
 381         @param state Pointer to the query-building context.
 382         @param parent ID of the UNION, INTERSECT, or EXCEPT query.
 383         @param type_str The type of the query ("UNION", "INTERSECT", or "EXCEPT").
 384         @return If successful, a pointer to a linked list of QSeq, each bearing a pointer to a
 385                 StoredQ; otherwise NULL.
 386
 387         The @a type_str parameter is used only for building error messages.
 388 */
 389 static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) {
 390         QSeq* child_list = NULL;
 391
 392         // The ORDER BY is in descending order so that we can build the list by adding to
 393         // the head, and it will wind up in the right order.
 394         dbi_result result = dbi_conn_queryf( state->dbhandle,
 395                 "SELECT id, parent_query, seq_no, child_query "
 396                 "FROM query.query_sequence WHERE parent_query = %d ORDER BY seq_no DESC", parent_id );
 397         if( result ) {
 398                 if( dbi_result_first_row( result ) ) {
 399                         int count = 0;
 400                         while( 1 ) {
 401                                 ++count;
 402                                 QSeq* seq = constructQSeq( state, result );
 403                                 if( seq ) {
 404                                         PRINT( "Found a child query\n" );
 405                                         PRINT( "\tid: %d\n", seq->id );
 406                                         PRINT( "\tparent id: %d\n", seq->parent_query_id );
 407                                         PRINT( "\tseq_no: %d\n", seq->seq_no );
 408                                         // Add to the head of the list
 409                                         seq->next = child_list;
 410                                         child_list = seq;
 411                                 } else{
 412                                         freeQSeqList( child_list );
 413                                         return NULL;
 414                                 }
 415                                 if( !dbi_result_next_row( result ))
 416                                         break;
 417                         }
 418                         if( count < 2 ) {
 419                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 420                                         "%s query # %d has only one child query", type_str, parent_id ));
 421                                 state->error = 1;
 422                                 freeQSeqList( child_list );
 423                                 return NULL;
 424                         }
 425                 } else {
 426                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 427                                 "%s query # %d has no child queries within it", type_str, parent_id ));
 428                         state->error = 1;
 429                         return NULL;
 430                 }
 431         } else {
 432                 const char* msg;
 433                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 434                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 435                         "Unable to query query.query_sequence table: # %d %s",
 436                         errnum, msg ? msg : "No description available" ));
 437                 state->error = 1;
 438                 return NULL;
 439         }
 440
 441         return child_list;
 442 }
 443
 444 /**
 445         @brief Construct a QSeq.
 446         @param Pointer to the query-building context.
 447         @param result Database cursor positioned at a row in query.query_sequence.
 448         @return Pointer to a newly constructed QSeq, if successful, or NULL if not.
 449
 450         The calling code is responsible for freeing QSeqs by calling freeQSeqList().
 451 */
 452 static QSeq* constructQSeq( BuildSQLState* state, dbi_result result ) {
 453         int id = dbi_result_get_int_idx( result, 1 );
 454         int parent_query_id = dbi_result_get_int_idx( result, 2 );
 455         int seq_no = dbi_result_get_int_idx( result, 3 );
 456         int child_query_id = dbi_result_get_int_idx( result, 4 );
 457
 458         StoredQ* child_query = getStoredQuery( state, child_query_id );
 459         if( !child_query ) {
 460                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 461                         "Unable to load child query # %d for parent query %d",
 462                         child_query_id, parent_query_id ));
 463                 state->error = 1;
 464                 return NULL;
 465         }
 466
 467         // Allocate a QSeq; from the free list if possible, from the heap if necessary
 468         QSeq* seq = NULL;
 469         if( free_qseq_list ) {
 470                 seq = free_qseq_list;
 471                 free_qseq_list = free_qseq_list->next;
 472         } else
 473                 seq = safe_malloc( sizeof( QSeq ));
 474
 475         seq->next            = NULL;
 476         seq->id              = id;
 477         seq->parent_query_id = parent_query_id;
 478         seq->seq_no          = seq_no;
 479         seq->child_query     = child_query;
 480
 481         return seq;
 482 }
 483
 484 /**
 485         @brief Free a list of QSeq's.
 486         @param seq Pointer to the first in a linked list of QSeq's to be freed.
 487
 488         Each QSeq goes onto a free list for potential reuse.
 489 */
 490 static void freeQSeqList( QSeq* seq ) {
 491         if( !seq )
 492                 return;
 493
 494         QSeq* first = seq;
 495         while( seq ) {
 496                 storedQFree( seq->child_query );
 497                 seq->child_query = NULL;
 498
 499                 if( seq->next )
 500                         seq = seq->next;
 501                 else {
 502                         seq->next = free_qseq_list;
 503                         seq = NULL;
 504                 }
 505         }
 506
 507         free_qseq_list = first;
 508 }
 509
 510 /**
 511         @brief Deallocate the memory owned by a StoredQ.
 512         @param sq Pointer to the StoredQ to be deallocated.
 513 */
 514 void storedQFree( StoredQ* sq ) {
 515         if( sq ) {
 516                 fromRelationFree( sq->from_clause );
 517                 sq->from_clause = NULL;
 518                 selectListFree( sq->select_list );
 519                 sq->select_list = NULL;
 520                 expressionFree( sq->where_clause );
 521                 sq->where_clause = NULL;
 522                 if( sq->child_list ) {
 523                         freeQSeqList( sq->child_list );
 524                         sq->child_list = NULL;
 525                 }
 526                 if( sq->order_by_list ) {
 527                         orderItemListFree( sq->order_by_list );
 528                         sq->order_by_list = NULL;
 529                 }
 530                 if( sq->having_clause )
 531                         expressionFree( sq->having_clause );
 532
 533                 // Stick the empty husk on the free list for potential reuse
 534                 sq->next = free_storedq_list;
 535                 free_storedq_list = sq;
 536         }
 537 }
 538
 539 /**
 540         @brief Given an id from query.from_relation, load a FromRelation.
 541         @param state Pointer the the query-building context.
 542         @param id Id of the FromRelation.
 543         @return Pointer to a newly-created FromRelation if successful, or NULL if not.
 544
 545         The calling code is responsible for freeing the new FromRelation by calling
 546         fromRelationFree().
 547 */
 548 static FromRelation* getFromRelation( BuildSQLState* state, int id ) {
 549         FromRelation* fr = NULL;
 550         dbi_result result = dbi_conn_queryf( state->dbhandle,
 551                 "SELECT id, type, table_name, class_name, subquery, function_call, "
 552                 "table_alias, parent_relation, seq_no, join_type, on_clause "
 553                 "FROM query.from_relation WHERE id = %d;", id );
 554         if( result ) {
 555                 if( dbi_result_first_row( result ) ) {
 556                         fr = constructFromRelation( state, result );
 557                         if( fr ) {
 558                                 PRINT( "Got a from_relation row\n" );
 559                                 PRINT( "\tid: %d\n", fr->id );
 560                                 PRINT( "\ttype: %d\n", (int) fr->type );
 561                                 PRINT( "\ttable_name: %s\n", fr->table_name ? fr->table_name : "(none)" );
 562                                 PRINT( "\tclass_name: %s\n", fr->class_name ? fr->class_name : "(none)" );
 563                                 PRINT( "\tsubquery_id: %d\n", fr->subquery_id );
 564                                 PRINT( "\tfunction_call_id: %d\n", fr->function_call_id );
 565                                 PRINT( "\ttable_alias: %s\n", fr->table_alias ? fr->table_alias : "(none)" );
 566                                 PRINT( "\tparent_relation_id: %d\n", fr->parent_relation_id );
 567                                 PRINT( "\tseq_no: %d\n", fr->seq_no );
 568                                 PRINT( "\tjoin_type = %d\n", fr->join_type );
 569                                 // Check the stack to see if the current from clause is nested inside itself.
 570                                 // If it is, then abort in order to avoid infinite recursion.  If it isn't,
 571                                 // then add it to the stack.  (Make sure to pop it off the stack before
 572                                 // returning.)
 573                                 const char* effective_alias = fr->table_alias;
 574                                 if( !effective_alias )
 575                                         effective_alias = fr->class_name;
 576                                 const IdNode* node = searchIdStack( state->from_stack, id, effective_alias );
 577                                 if( node ) {
 578                                         if( node->id == id )
 579                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 580                                                         "Infinite recursion detected; from clause # %d is nested "
 581                                                         "within itself", id ));
 582                                         else
 583                                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 584                                                         "Conflicting nested table aliases \"%s\" in from clause # %d",
 585                                                         effective_alias, node->id ));
 586                                         state->error = 1;
 587                                         return NULL;
 588                                 } else
 589                                         push_id( &state->from_stack, id, effective_alias );
 590                         } else
 591                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 592                                         "Unable to build a FromRelation for id = %d", id ));
 593                 } else {
 594                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 595                                 "FROM relation not found for id = %d", id ));
 596                         state->error = 1;
 597                 }
 598                 dbi_result_free( result );
 599         } else {
 600                 const char* msg;
 601                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 602                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 603                         "Unable to query query.from_relation table: #%d %s",
 604                         errnum, msg ? msg : "No description available" ));
 605                 state->error = 1;
 606         }
 607
 608         if( fr )
 609                 pop_id( &state->from_stack );
 610
 611         return fr;
 612 }
 613
 614 /**
 615         @brief Construct a FromRelation.
 616         @param Pointer to the query-building context.
 617         @param result Database cursor positioned at a row in query.from_relation.
 618         @return Pointer to a newly constructed FromRelation, if successful, or NULL if not.
 619
 620         The calling code is responsible for freeing FromRelations by calling joinListFree().
 621 */
 622 static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result result ) {
 623         // Get the column values from the result
 624         int id                  = dbi_result_get_int_idx( result, 1 );
 625         const char* type_str    = dbi_result_get_string_idx( result, 2 );
 626
 627         FromRelationType type;
 628         if( !strcmp( type_str, "RELATION" ))
 629                 type = FRT_RELATION;
 630         else if( !strcmp( type_str, "SUBQUERY" ))
 631                 type = FRT_SUBQUERY;
 632         else if( !strcmp( type_str, "FUNCTION" ))
 633                 type = FRT_FUNCTION;
 634         else
 635                 type = FRT_RELATION;     // shouldn't happen due to database constraint
 636
 637         const char* table_name   = dbi_result_get_string_idx( result, 3 );
 638         const char* class_name   = dbi_result_get_string_idx( result, 4 );
 639
 640         int subquery_id;
 641         if( dbi_result_field_is_null_idx( result, 5 ) )
 642                 subquery_id           = -1;
 643         else
 644                 subquery_id           = dbi_result_get_int_idx( result, 5 );
 645
 646         int function_call_id;
 647         if( dbi_result_field_is_null_idx( result, 6 ) )
 648                 function_call_id      = -1;
 649         else
 650                 function_call_id      = dbi_result_get_int_idx( result, 6 );
 651
 652         Expression* function_call = NULL;
 653         const char* table_alias   = dbi_result_get_string_idx( result, 7 );
 654
 655         int parent_relation_id;
 656         if( dbi_result_field_is_null_idx( result, 8 ) )
 657                 parent_relation_id    = -1;
 658         else
 659                 parent_relation_id    = dbi_result_get_int_idx( result, 8 );
 660
 661         int seq_no                = dbi_result_get_int_idx( result, 9 );
 662
 663         JoinType join_type;
 664         const char* join_type_str = dbi_result_get_string_idx( result, 10 );
 665         if( !join_type_str )
 666                 join_type = JT_NONE;
 667         else if( !strcmp( join_type_str, "INNER" ) )
 668                 join_type = JT_INNER;
 669         else if( !strcmp( join_type_str, "LEFT" ) )
 670                 join_type = JT_LEFT;
 671         else if( !strcmp( join_type_str, "RIGHT" ) )
 672                 join_type = JT_RIGHT;
 673         else if( !strcmp( join_type_str, "FULL" ) )
 674                 join_type = JT_FULL;
 675         else
 676                 join_type = JT_NONE;     // shouldn't happen due to database constraint
 677
 678         int on_clause_id;
 679         if( dbi_result_field_is_null_idx( result, 11 ) )
 680                 on_clause_id   = -1;
 681         else
 682                 on_clause_id   = dbi_result_get_int_idx( result, 11 );
 683
 684         StoredQ* subquery = NULL;
 685
 686         switch ( type ) {
 687                 case FRT_RELATION :
 688                         break;
 689                 case FRT_SUBQUERY :
 690                         if( -1 == subquery_id ) {
 691                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 692                                         "Internal error: no subquery specified for FROM relation # %d", id ));
 693                                 state->error = 1;
 694                                 return NULL;
 695                         }
 696                         if( ! table_alias ) {
 697                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 698                                         "Subquery needs alias in FROM relation # %d", id ));
 699                                 state->error = 1;
 700                                 return NULL;
 701                         }
 702                         subquery = getStoredQuery( state, subquery_id );
 703                         if( ! subquery ) {
 704                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 705                                         "Unable to load subquery for FROM relation # %d", id ));
 706                                 state->error = 1;
 707                                 return NULL;
 708                         }
 709                         break;
 710                 case FRT_FUNCTION :
 711                         if( -1 == function_call_id ) {
 712                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 713                                         "FROM clause # %d purports to reference a function; not identified", id ));
 714                                 state->error = 1;
 715                                 return NULL;
 716                         }
 717
 718                         function_call = getExpression( state, function_call_id );
 719                         if( !function_call ) {
 720                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 721                                         "Unable to build function call # %d in FROM relation # %d",
 722                                         function_call_id, id ));
 723                                 state->error = 1;
 724                                 return NULL;
 725                         } else if( function_call->type != EXP_FUNCTION ) {
 726                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 727                                         "In FROM relation # %d: supposed function call expression # %d "
 728                                         "is not a function call", id, function_call_id ));
 729                                 state->error = 1;
 730                                 return NULL;
 731                         }
 732         }
 733
 734         FromRelation* join_list = getJoinList( state, id );
 735         if( state->error ) {
 736                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 737                         "Unable to load join list for FROM relation # %d", id ));
 738                 return NULL;
 739         }
 740
 741         Expression* on_clause = NULL;
 742         if( on_clause_id != -1 ) {
 743                 on_clause = getExpression( state, on_clause_id );
 744                 if( !on_clause ) {
 745                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 746                                 "Unable to load ON condition for FROM relation # %d", id ));
 747                         joinListFree( join_list );
 748                         state->error = 1;
 749                         return NULL;
 750                 }
 751                 else
 752                         PRINT( "\tGot an ON condition\n" );
 753         }
 754
 755         // Allocate a FromRelation: from the free list if possible, from the heap if necessary
 756
 757         FromRelation* fr;
 758         if( free_from_relation_list ) {
 759                 fr = free_from_relation_list;
 760                 free_from_relation_list = free_from_relation_list->next;
 761         } else
 762                 fr = safe_malloc( sizeof( FromRelation ) );
 763
 764         // Populate the FromRelation
 765
 766         fr->next = NULL;
 767         fr->id = id;
 768         fr->type = type;
 769         fr->table_name = table_name ? strdup( table_name ) : NULL;
 770         fr->class_name = class_name ? strdup( class_name ) : NULL;
 771         fr->subquery_id = subquery_id;
 772         fr->subquery = subquery;
 773         fr->function_call_id = function_call_id;
 774         fr->function_call = function_call;
 775         fr->table_alias = table_alias ? strdup( table_alias ) : NULL;
 776         fr->parent_relation_id = parent_relation_id;
 777         fr->seq_no = seq_no;
 778         fr->join_type = join_type;
 779         fr->on_clause = on_clause;
 780         fr->join_list = join_list;
 781
 782         return fr;
 783 }
 784
 785 /**
 786         @brief Build a list of joined relations.
 787         @param state Pointer to the query-building context.
 788         @param id ID of the parent relation.
 789         @return A pointer to the first in a linked list of FromRelations, if there are any; or
 790                 NULL if there aren't any, or in case of an error.
 791
 792         Look for relations joined directly to the parent relation, and make a list of them.
 793 */
 794 static FromRelation* getJoinList( BuildSQLState* state, int id ) {
 795         FromRelation* join_list = NULL;
 796
 797         // The ORDER BY is in descending order so that we can build the list by adding to
 798         // the head, and it will wind up in the right order.
 799         dbi_result result = dbi_conn_queryf( state->dbhandle,
 800                 "SELECT id, type, table_name, class_name, subquery, function_call, "
 801                 "table_alias, parent_relation, seq_no, join_type, on_clause "
 802                 "FROM query.from_relation WHERE parent_relation = %d ORDER BY seq_no DESC", id );
 803
 804         if( result ) {
 805                 if( dbi_result_first_row( result ) ) {
 806                         while( 1 ) {
 807                                 FromRelation* relation = constructFromRelation( state, result );
 808                                 if( relation ) {
 809                                         PRINT( "Found a joined relation\n" );
 810                                         PRINT( "\tjoin_type: %d\n", relation->join_type );
 811                                         PRINT( "\ttable_name: %s\n", relation->table_name );
 812                                         relation->next = join_list;
 813                                         join_list = relation;
 814                                 } else {
 815                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 816                                                 "Unable to build join list for from relation id #%d", id ));
 817                                         joinListFree( join_list );
 818                                         join_list = NULL;
 819                                         break;
 820                                 }
 821                                 if( !dbi_result_next_row( result ) )
 822                                         break;
 823                         };
 824                 }
 825         } else {
 826                 const char* msg;
 827                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 828                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 829                         "Unable to query query.from_relation table for join list: #%d %s",
 830                         errnum, msg ? msg : "No description available" ));
 831                 state->error = 1;
 832         }
 833
 834         return join_list;
 835 }
 836
 837 /**
 838         @brief Free a list of FromRelations.
 839         @param join_list Pointer to the first FromRelation in the list.
 840 */
 841 static void joinListFree( FromRelation* join_list ) {
 842         while( join_list ) {
 843                 FromRelation* temp = join_list->next;
 844                 fromRelationFree( join_list );
 845                 join_list = temp;
 846         }
 847 }
 848
 849 /**
 850         @brief Deallocate a FromRelation.
 851         @param fr Pointer to the FromRelation to be freed.
 852
 853         Free the strings that the FromRelation owns.  The FromRelation itself goes onto a
 854         free list for potential reuse.
 855 */
 856 static void fromRelationFree( FromRelation* fr ) {
 857         if( fr ) {
 858                 free( fr->table_name );
 859                 fr->table_name = NULL;
 860                 free( fr->class_name );
 861                 fr->class_name = NULL;
 862                 if( fr->subquery ) {
 863                         storedQFree( fr->subquery );
 864                         fr->subquery = NULL;
 865                 }
 866                 if( fr->function_call ) {
 867                         expressionFree( fr->function_call );
 868                         fr->function_call = NULL;
 869                 }
 870                 free( fr->table_alias );
 871                 fr->table_alias = NULL;
 872                 if( fr->on_clause ) {
 873                         expressionFree( fr->on_clause );
 874                         fr->on_clause = NULL;
 875                 }
 876                 joinListFree( fr->join_list );
 877                 fr->join_list = NULL;
 878
 879                 fr->next = free_from_relation_list;
 880                 free_from_relation_list = fr;
 881         }
 882 }
 883
 884 /**
 885         @brief Build a SELECT list for a given query ID.
 886         @param state Pointer to the query-building context.
 887         @param query_id ID of the query to which the SELECT list belongs.
 888 */
 889 static SelectItem* getSelectList( BuildSQLState* state, int query_id ) {
 890         SelectItem* select_list = NULL;
 891
 892         // The ORDER BY is in descending order so that we can build the list by adding to
 893         // the head, and it will wind up in the right order.
 894         dbi_result result = dbi_conn_queryf( state->dbhandle,
 895                 "SELECT id, stored_query, seq_no, expression, column_alias, grouped_by "
 896                 "FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
 897         if( result ) {
 898                 if( dbi_result_first_row( result ) ) {
 899                         while( 1 ) {
 900                                 SelectItem* item = constructSelectItem( state, result );
 901                                 if( item ) {
 902                                         PRINT( "Found a SELECT item\n" );
 903                                         PRINT( "\tid: %d\n", item->id );
 904                                         PRINT( "\tstored_query_id: %d\n", item->stored_query_id );
 905                                         PRINT( "\tseq_no: %d\n", item->seq_no );
 906                                         PRINT( "\tcolumn_alias: %s\n",
 907                                                         item->column_alias ? item->column_alias : "(none)" );
 908                                         PRINT( "\tgrouped_by: %d\n", item->grouped_by );
 909
 910                                         item->next = select_list;
 911                                         select_list = item;
 912                                 } else {
 913                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 914                                                 "Unable to build select list for query id #%d", query_id ));
 915                                         selectListFree( select_list );
 916                                         select_list = NULL;
 917                                         break;
 918                                 }
 919                                 if( !dbi_result_next_row( result ) )
 920                                         break;
 921                         };
 922                 } else {
 923                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 924                                 "No SELECT list found for query # %d", query_id ));
 925                         state->error = 1;
 926                 }
 927         } else {
 928                 const char* msg;
 929                 int errnum = dbi_conn_error( state->dbhandle, &msg );
 930                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 931                         "Unable to query query.select_list table: #%d %s",
 932                         errnum, msg ? msg : "No description available" ));
 933                 state->error = 1;
 934         }
 935
 936         return select_list;
 937 }
 938
 939 /**
 940         @brief Construct a SelectItem.
 941         @param Pointer to the query-building context.
 942         @param result Database cursor positioned at a row in query.select_item.
 943         @return Pointer to a newly constructed SelectItem, if successful, or NULL if not.
 944
 945         The calling code is responsible for freeing the SelectItems by calling selectListFree().
 946 */
 947 static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result ) {
 948
 949         // Get the column values
 950         int id                   = dbi_result_get_int_idx( result, 1 );
 951         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
 952         int seq_no               = dbi_result_get_int_idx( result, 3 );
 953         int expression_id        = dbi_result_get_int_idx( result, 4 );
 954         const char* column_alias = dbi_result_get_string_idx( result, 5 );
 955         int grouped_by           = oils_result_get_bool_idx( result, 6 );
 956
 957         // Construct an Expression
 958         Expression* expression = getExpression( state, expression_id );
 959         if( !expression ) {
 960                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 961                         "Unable to fetch expression for id = %d", expression_id ));
 962                 state->error = 1;
 963                 return NULL;
 964         };
 965
 966         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
 967
 968         SelectItem* sel;
 969         if( free_select_item_list ) {
 970                 sel = free_select_item_list;
 971                 free_select_item_list = free_select_item_list->next;
 972         } else
 973                 sel = safe_malloc( sizeof( SelectItem ) );
 974
 975         sel->next            = NULL;
 976         sel->id              = id;
 977         sel->stored_query_id = stored_query_id;
 978         sel->seq_no          = seq_no;
 979         sel->expression      = expression;
 980         sel->column_alias    = column_alias ? strdup( column_alias ) : NULL;
 981         sel->grouped_by      = grouped_by;
 982
 983         return sel;
 984 }
 985
 986 /**
 987         @brief Free a list of SelectItems.
 988         @param sel Pointer to the first item in the list to be freed.
 989
 990         Free the column alias and expression owned by each item.  Put the entire list into a free
 991         list of SelectItems.
 992 */
 993 static void selectListFree( SelectItem* sel ) {
 994         if( !sel )
 995                 return;    // Nothing to free
 996
 997         SelectItem* first = sel;
 998         while( 1 ) {
 999                 free( sel->column_alias );
1000                 sel->column_alias = NULL;
1001                 expressionFree( sel->expression );
1002                 sel->expression = NULL;
1003
1004                 if( NULL == sel->next ) {
1005                         sel->next = free_select_item_list;
1006                         break;
1007                 } else
1008                         sel = sel->next;
1009         };
1010
1011         // Transfer the entire list to the free list
1012         free_select_item_list = first;
1013 }
1014
1015 /**
1016         @brief Given the name of a bind variable, build a corresponding BindVar.
1017         @param state Pointer to the query-building context.
1018         @param name Name of the bind variable.
1019         @return Pointer to the newly-built BindVar.
1020
1021         Since the same bind variable may appear multiple times, we load it only once for the
1022         entire query, and reference the one copy wherever needed.
1023 */
1024 static BindVar* getBindVar( BuildSQLState* state, const char* name ) {
1025         BindVar* bind = NULL;
1026         if( state->bindvar_list ) {
1027                 bind = osrfHashGet( state->bindvar_list, name );
1028                 if( bind )
1029                         return bind;   // Already loaded it...
1030         }
1031
1032         // Load a BindVar from the Database.(after escaping any special characters)
1033         char* esc_str = strdup( name );
1034         dbi_conn_quote_string( state->dbhandle, &esc_str );
1035         if( !esc_str ) {
1036                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1037                         "Unable to format bind variable name \"%s\"", name ));
1038                 state->error = 1;
1039                 return NULL;
1040         }
1041         dbi_result result = dbi_conn_queryf( state->dbhandle,
1042                 "SELECT name, type, description, default_value, label "
1043                 "FROM query.bind_variable WHERE name = %s;", esc_str );
1044         free( esc_str );
1045         if( result ) {
1046                 if( dbi_result_first_row( result ) ) {
1047                         bind = constructBindVar( state, result );
1048                         if( bind ) {
1049                                 PRINT( "Got a bind variable for %s\n", name );
1050                         } else
1051                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1052                                         "Unable to load bind variable \"%s\"", name ));
1053                 } else {
1054                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1055                                 "No bind variable found with name \"%s\"", name ));
1056                         state->error = 1;
1057                 }
1058         } else {
1059                 const char* msg;
1060                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1061                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1062                         "Unable to query query.bind_variable table for \"%s\": #%d %s",
1063                         name, errnum, msg ? msg : "No description available" ));
1064                 state->error = 1;
1065         }
1066
1067         if( bind ) {
1068                 // Add the new bind variable to the list
1069                 if( !state->bindvar_list ) {
1070                         // Don't have a list yet?  Start one.
1071                         state->bindvar_list = osrfNewHash();
1072                         osrfHashSetCallback( state->bindvar_list, bindVarFree );
1073                 }
1074                 osrfHashSet( state->bindvar_list, bind, name );
1075         } else
1076                 state->error = 1;
1077
1078         return bind;
1079 }
1080
1081 /**
1082         @brief Construct a BindVar to represent a bind variable.
1083         @param Pointer to the query-building context.
1084         @param result Database cursor positioned at a row in query.bind_variable.
1085         @return Pointer to a newly constructed BindVar, if successful, or NULL if not.
1086
1087         The calling code is responsible for freeing the BindVar by calling bindVarFree().
1088 */
1089 static BindVar* constructBindVar( BuildSQLState* state, dbi_result result ) {
1090
1091         const char* name = dbi_result_get_string_idx( result, 1 );
1092
1093         const char* type_str = dbi_result_get_string_idx( result, 2 );
1094         BindVarType type;
1095         if( !strcmp( type_str, "string" ))
1096                 type = BIND_STR;
1097         else if( !strcmp( type_str, "number" ))
1098                 type = BIND_NUM;
1099         else if( !strcmp( type_str, "string_list" ))
1100                 type = BIND_STR_LIST;
1101         else if( !strcmp( type_str, "number_list" ))
1102                 type = BIND_NUM_LIST;
1103         else {         // Shouldn't happen due to database constraint...
1104                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1105                         "Internal error: invalid bind variable type \"%s\" for bind variable \"%s\"",
1106                         type_str, name ));
1107                 state->error = 1;
1108                 return NULL;
1109         }
1110
1111         const char* description = dbi_result_get_string_idx( result, 3 );
1112
1113         // The default value is encoded as JSON.  Translate it into a jsonObject.
1114         const char* default_value_str = dbi_result_get_string_idx( result, 4 );
1115         jsonObject* default_value = NULL;
1116         if( default_value_str ) {
1117                 default_value = jsonParse( default_value_str );
1118                 if( !default_value ) {
1119                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1120                                 "Unable to parse JSON string for default value of bind variable \"%s\"",
1121                                 name ));
1122                         state->error = 1;
1123                         return NULL;
1124                 }
1125         }
1126
1127         const char* label = dbi_result_get_string_idx( result, 5 );
1128
1129         // Allocate a BindVar: from the free list if possible, from the heap if necessary
1130         BindVar* bind = NULL;
1131         if( free_bindvar_list ) {
1132                 bind = free_bindvar_list;
1133                 free_bindvar_list = free_bindvar_list->next;
1134         } else
1135                 bind = safe_malloc( sizeof( BindVar ) );
1136
1137         bind->next = NULL;
1138         bind->name = strdup( name );
1139         bind->label = strdup( label );
1140         bind->type = type;
1141         bind->description = strdup( description );
1142         bind->default_value = default_value;
1143         bind->actual_value = NULL;
1144
1145         return bind;
1146 }
1147
1148 /**
1149         @brief Deallocate a BindVar.
1150         @param key Pointer to the bind variable name (not used).
1151         @param p Pointer to the BindVar to be deallocated, cast to a void pointer.
1152
1153         Free the strings and jsonObjects owned by the BindVar, and put the BindVar itself into a
1154         free list.
1155
1156         This function is a callback installed in an osrfHash; hence the peculiar signature.
1157 */
1158 static void bindVarFree( char* key, void* p ) {
1159         if( p ) {
1160                 BindVar* bind = p;
1161                 free( bind->name );
1162                 free( bind->label );
1163                 free( bind->description );
1164                 if( bind->default_value ) {
1165                         jsonObjectFree( bind->default_value );
1166                         bind->default_value = NULL;
1167                 }
1168                 if( bind->actual_value ) {
1169                         jsonObjectFree( bind->actual_value );
1170                         bind->actual_value = NULL;
1171                 }
1172
1173                 // Prepend to free list
1174                 bind->next = free_bindvar_list;
1175                 free_bindvar_list = bind;
1176         }
1177 }
1178
1179 /**
1180         @brief Given an id for a parent expression, build a list of CaseBranch structs.
1181         @param Pointer to the query-building context.
1182         @param id ID of a row in query.case_branch.
1183         @return Pointer to a newly-created CaseBranch if successful, or NULL if not.
1184 */
1185 static CaseBranch* getCaseBranchList( BuildSQLState* state, int parent_id ) {
1186         CaseBranch* branch_list = NULL;
1187         dbi_result result = dbi_conn_queryf( state->dbhandle,
1188                 "SELECT id, condition, result "
1189                 "FROM query.case_branch WHERE parent_expr = %d "
1190                 "ORDER BY seq_no desc;", parent_id );
1191         if( result ) {
1192                 int condition_found = 0;   // Boolean
1193                 if( dbi_result_first_row( result ) ) {
1194                         // Boolean: true for the first branch we encounter.  That's actually the last
1195                         // branch in the CASE, because we're reading them in reverse order.  The point
1196                         // is to enforce the rule that only the last branch can be an ELSE.
1197                         int first = 1;
1198                         while( 1 ) {
1199                                 CaseBranch* branch = constructCaseBranch( state, result );
1200                                 if( branch ) {
1201                                         PRINT( "Found a case branch\n" );
1202                                         branch->next = branch_list;
1203                                         branch_list  = branch;
1204                                 } else {
1205                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1206                                                 "Unable to build CASE branch for expression id #%d", parent_id ));
1207                                         freeBranchList( branch_list );
1208                                         branch_list = NULL;
1209                                         break;
1210                                 }
1211
1212                                 if( branch->condition )
1213                                         condition_found = 1;
1214                                 else if( !first ) {
1215                                         sqlAddMsg( state, "ELSE branch # %d not at end of CASE expression # %d",
1216                                                 branch->id, parent_id );
1217                                         freeBranchList( branch_list );
1218                                         branch_list = NULL;
1219                                         break;
1220                                 }
1221                                 first = 0;
1222
1223                                 if( !dbi_result_next_row( result ) )
1224                                         break;
1225                         };  // end while
1226                 } else {
1227                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1228                                 "No branches found for CASE expression %d", parent_id ));
1229                         state->error = 1;
1230                 }
1231
1232                 // Make sure that at least one branch includes a condition
1233                 if( !condition_found ) {
1234                         sqlAddMsg( state, "No conditional branch in CASE expression # %d", parent_id );
1235                         freeBranchList( branch_list );
1236                         branch_list = NULL;
1237                 }
1238         } else {
1239                 const char* msg;
1240                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1241                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1242                         "Unable to query query.case_branch table for parent expression # %d: %s",
1243                         parent_id, errnum, msg ? msg : "No description available" ));
1244                 state->error = 1;
1245         }
1246
1247         return branch_list;
1248 }
1249
1250 static CaseBranch* constructCaseBranch( BuildSQLState* state, dbi_result result ) {
1251         int id = dbi_result_get_int_idx( result, 1 );
1252
1253         int condition_id;
1254         if( dbi_result_field_is_null_idx( result, 2 ))
1255                 condition_id = -1;
1256         else
1257                 condition_id = dbi_result_get_int_idx( result, 2 );
1258
1259         Expression* condition = NULL;
1260         if( condition_id != -1 ) {   // If it's -1, we have an ELSE condition
1261                 condition = getExpression( state, condition_id );
1262                 if( ! condition ) {
1263                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1264                                 "Unable to build condition expression for case branch # %d", id ));
1265                         state->error = 1;
1266                         return NULL;
1267                 }
1268         }
1269
1270         int result_id = dbi_result_get_int_idx( result, 3 );
1271
1272         Expression* result_p = NULL;
1273         if( -1 == result_id ) {
1274                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1275                         "No result expression defined for case branch # %d", id ));
1276                 state->error = 1;
1277                 if( condition )
1278                         expressionFree( condition );
1279                 return NULL;
1280         } else {
1281                 result_p = getExpression( state, result_id );
1282                 if( ! result_p ) {
1283                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1284                                 "Unable to build result expression for case branch # %d", id ));
1285                         state->error = 1;
1286                         if( condition )
1287                                 expressionFree( condition );
1288                         return NULL;
1289                 }
1290         }
1291
1292         // Allocate a CaseBranch: from the free list if possible, from the heap if necessary
1293         CaseBranch* branch = NULL;
1294         if( free_branch_list ) {
1295                 branch = free_branch_list;
1296                 free_branch_list = free_branch_list->next;
1297         } else
1298                 branch = safe_malloc( sizeof( CaseBranch ) );
1299
1300         // Populate the new CaseBranch
1301         branch->id = id;
1302         branch->condition = condition;
1303         branch->result = result_p;
1304
1305         return branch;
1306 }
1307
1308 /**
1309         @brief Free a list of CaseBranches.
1310         @param branch Pointer to the first in a linked list of CaseBranches to be freed.
1311
1312         Each CaseBranch goes onto a free list for potential reuse.
1313 */
1314 static void freeBranchList( CaseBranch* branch ) {
1315         if( !branch )
1316                 return;
1317
1318         CaseBranch* first = branch;
1319         while( branch ) {
1320                 if( branch->condition ) {
1321                         expressionFree( branch->condition );
1322                         branch->condition = NULL;
1323                 }
1324                 expressionFree( branch->result );
1325                 branch->result = NULL;
1326
1327                 if( branch->next )
1328                         branch = branch->next;
1329                 else {
1330                         branch->next = free_branch_list;
1331                         branch = NULL;
1332                 }
1333         }
1334
1335         free_branch_list = first;
1336 }
1337
1338 /**
1339         @brief Given an id for a row in query.datatype, build an Datatype struct.
1340         @param Pointer to the query-building context.
1341         @param id ID of a row in query.datatype.
1342         @return Pointer to a newly-created Datatype if successful, or NULL if not.
1343 */
1344 static Datatype* getDatatype( BuildSQLState* state, int id ) {
1345         Datatype* datatype = NULL;
1346         dbi_result result = dbi_conn_queryf( state->dbhandle,
1347                 "SELECT id, datatype_name, is_numeric, is_composite "
1348                 "FROM query.datatype WHERE id = %d", id );
1349         if( result ) {
1350                 if( dbi_result_first_row( result ) ) {
1351                         datatype = constructDatatype( state, result );
1352                         if( datatype ) {
1353                                 PRINT( "Got a datatype\n" );
1354                                 PRINT( "\tid = %d\n", id );
1355                                 PRINT( "\tdatatype = %s\n", datatype->datatype_name );
1356                         } else
1357                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1358                                         "Unable to construct a Datatype for id = %d", id ));
1359                 } else {
1360                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1361                                 "No datatype found for id = %d", id ));
1362                         state->error = 1;
1363                 }
1364         } else {
1365                 const char* msg;
1366                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1367                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1368                         "Unable to query query.datatype table: #%d %s",
1369                         errnum, msg ? msg : "No description available" ));
1370                 state->error = 1;
1371         }
1372         return datatype;
1373 }
1374
1375 /**
1376         @brief Construct a Datatype.
1377         @param Pointer to the query-building context.
1378         @param result Database cursor positioned at a row in query.datatype.
1379         @return Pointer to a newly constructed Datatype, if successful, or NULL if not.
1380
1381         The calling code is responsible for freeing the Datatype by calling datatypeFree().
1382 */
1383 static Datatype* constructDatatype( BuildSQLState* state, dbi_result result ) {
1384         int id           = dbi_result_get_int_idx( result, 1 );
1385         const char* datatype_name = dbi_result_get_string_idx( result, 2 );
1386         int is_numeric   = oils_result_get_bool_idx( result, 3 );
1387         int is_composite = oils_result_get_bool_idx( result, 4 );
1388
1389         if( !datatype_name || !*datatype_name ) {
1390                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1391                         "No datatype name provided for CAST expression # %d", id ));
1392                 state->error = 1;
1393                 return NULL;
1394         }
1395
1396         // Make sure that the datatype name is composed entirely of certain approved
1397         // characters.  This check is not an attempt to validate the datatype name, but
1398         // only to prevent certain types of SQL injection.
1399         const char* p = datatype_name;
1400         while( *p ) {
1401                 unsigned char c = *p;
1402                 if( isalnum( c )
1403                         || isspace( c )
1404                         || ',' == c
1405                         || '(' == c
1406                         || ')' == c
1407                         || '[' == c
1408                         || ']' == c
1409                         || '.' == c
1410                 )
1411                         ++p;
1412                 else {
1413                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1414                                 "Invalid datatype name \"%s\" for datatype # %d; "
1415                                 "contains unexpected character \"%c\"", datatype_name, id, (char) c ));
1416                         state->error = 1;
1417                         return NULL;
1418                 }
1419         }
1420
1421         // Allocate a Datatype: from the free list if possible, from the heap if necessary
1422         Datatype* datatype = NULL;
1423         if( free_datatype_list ) {
1424                 datatype = free_datatype_list;
1425                 free_datatype_list = free_datatype_list->next;
1426         } else
1427                 datatype = safe_malloc( sizeof( Datatype ) );
1428
1429         datatype->id            = id;
1430         datatype->datatype_name = strdup( datatype_name );
1431         datatype->is_numeric    = is_numeric;
1432         datatype->is_composite  = is_composite;
1433
1434         return datatype;
1435 }
1436
1437 /**
1438         @brief Free a Datatype.
1439         @param datatype Pointer to the Datatype to be freed.
1440 */
1441 static void datatypeFree( Datatype* datatype ) {
1442         if( datatype ) {
1443                 free( datatype->datatype_name );
1444                 free( datatype );
1445         }
1446 }
1447
1448 /**
1449         @brief Given an id for a row in query.expression, build an Expression struct.
1450         @param Pointer to the query-building context.
1451         @param id ID of a row in query.expression.
1452         @return Pointer to a newly-created Expression if successful, or NULL if not.
1453 */
1454 static Expression* getExpression( BuildSQLState* state, int id ) {
1455
1456         // Check the stack to see if the current expression is nested inside itself.  If it is,
1457         // then abort in order to avoid infinite recursion.  If it isn't, then add it to the
1458         // stack.  (Make sure to pop it off the stack before returning.)
1459         if( searchIdStack( state->expr_stack, id, NULL )) {
1460                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1461                         "Infinite recursion detected; expression # %d is nested within itself", id ));
1462                 state->error = 1;
1463                 return NULL;
1464         } else
1465                 push_id( &state->expr_stack, id, NULL );
1466
1467         Expression* exp = NULL;
1468         dbi_result result = dbi_conn_queryf( state->dbhandle,
1469                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
1470                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
1471                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
1472                 "func.function_name "
1473                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
1474                 "ON (exp.function_id = func.id) "
1475                 "WHERE exp.id = %d;", id );
1476         if( result ) {
1477                 if( dbi_result_first_row( result ) ) {
1478                         exp = constructExpression( state, result );
1479                         if( exp ) {
1480                                 PRINT( "Got an expression\n" );
1481                                 PRINT( "\tid = %d\n", exp->id );
1482                                 PRINT( "\ttype = %d\n", exp->type );
1483                                 PRINT( "\tparenthesize = %d\n", exp->parenthesize );
1484                                 PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" );
1485                         } else
1486                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1487                                         "Unable to construct an Expression for id = %d", id ));
1488                 } else {
1489                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1490                                 "No expression found for id = %d", id ));
1491                         state->error = 1;
1492                 }
1493         } else {
1494                 const char* msg;
1495                 int errnum = dbi_conn_error( state->dbhandle, &msg );
1496                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1497                         "Unable to query query.expression table: #%d %s",
1498                         errnum, msg ? msg : "No description available" ));
1499                 state->error = 1;
1500         }
1501
1502         pop_id( &state->expr_stack );
1503         return exp;
1504 }
1505
1506 /**
1507         @brief Construct an Expression.
1508         @param Pointer to the query-building context.
1509         @param result Database cursor positioned at a row in query.expression.
1510         @return Pointer to a newly constructed Expression, if successful, or NULL if not.
1511
1512         The calling code is responsible for freeing the Expression by calling expressionFree().
1513 */
1514 static Expression* constructExpression( BuildSQLState* state, dbi_result result ) {
1515
1516         int id = dbi_result_get_int_idx( result, 1 );
1517         const char* type_str = dbi_result_get_string_idx( result, 2 );
1518
1519         ExprType type;
1520         if( !strcmp( type_str, "xbet" ))
1521                 type = EXP_BETWEEN;
1522         else if( !strcmp( type_str, "xbind" ))
1523                 type = EXP_BIND;
1524         else if( !strcmp( type_str, "xbool" ))
1525                 type = EXP_BOOL;
1526         else if( !strcmp( type_str, "xcase" ))
1527                 type = EXP_CASE;
1528         else if( !strcmp( type_str, "xcast" ))
1529                 type = EXP_CAST;
1530         else if( !strcmp( type_str, "xcol" ))
1531                 type = EXP_COLUMN;
1532         else if( !strcmp( type_str, "xex" ))
1533                 type = EXP_EXIST;
1534         else if( !strcmp( type_str, "xfld" ))
1535                 type = EXP_FIELD;
1536         else if( !strcmp( type_str, "xfunc" ))
1537                 type = EXP_FUNCTION;
1538         else if( !strcmp( type_str, "xin" ))
1539                 type = EXP_IN;
1540         else if( !strcmp( type_str, "xisnull" ))
1541                 type = EXP_ISNULL;
1542         else if( !strcmp( type_str, "xnull" ))
1543                 type = EXP_NULL;
1544         else if( !strcmp( type_str, "xnum" ))
1545                 type = EXP_NUMBER;
1546         else if( !strcmp( type_str, "xop" ))
1547                 type = EXP_OPERATOR;
1548         else if( !strcmp( type_str, "xser" ))
1549                 type = EXP_SERIES;
1550         else if( !strcmp( type_str, "xstr" ))
1551                 type = EXP_STRING;
1552         else if( !strcmp( type_str, "xsubq" ))
1553                 type = EXP_SUBQUERY;
1554         else
1555                 type = EXP_NULL;     // shouldn't happen due to database constraint
1556
1557         int parenthesize = oils_result_get_bool_idx( result, 3 );
1558
1559         int parent_expr_id;
1560         if( dbi_result_field_is_null_idx( result, 4 ))
1561                 parent_expr_id = -1;
1562         else
1563                 parent_expr_id = dbi_result_get_int_idx( result, 4 );
1564
1565         int seq_no = dbi_result_get_int_idx( result, 5 );
1566         const char* literal = dbi_result_get_string_idx( result, 6 );
1567         const char* table_alias = dbi_result_get_string_idx( result, 7 );
1568         const char* column_name = dbi_result_get_string_idx( result, 8 );
1569
1570         int left_operand_id;
1571         if( dbi_result_field_is_null_idx( result, 9 ))
1572                 left_operand_id = -1;
1573         else
1574                 left_operand_id = dbi_result_get_int_idx( result, 9 );
1575
1576         const char* operator = dbi_result_get_string_idx( result, 10 );
1577
1578         int right_operand_id;
1579         if( dbi_result_field_is_null_idx( result, 11 ))
1580                 right_operand_id = -1;
1581         else
1582                 right_operand_id = dbi_result_get_int_idx( result, 11 );
1583
1584         int subquery_id;
1585         if( dbi_result_field_is_null_idx( result, 12 ))
1586                 subquery_id = -1;
1587         else
1588                 subquery_id = dbi_result_get_int_idx( result, 12 );
1589
1590         int cast_type_id;
1591         if( dbi_result_field_is_null_idx( result, 13 ))
1592                 cast_type_id = -1;
1593         else
1594                 cast_type_id = dbi_result_get_int_idx( result, 13 );
1595
1596         int negate = oils_result_get_bool_idx( result, 14 );
1597         const char* bind_variable = dbi_result_get_string_idx( result, 15 );
1598         const char* function_name = dbi_result_get_string_idx( result, 16 );
1599
1600         Expression* left_operand = NULL;
1601         Expression* right_operand = NULL;
1602         StoredQ* subquery = NULL;
1603         Datatype* cast_type = NULL;
1604         BindVar* bind = NULL;
1605         CaseBranch* branch_list = NULL;
1606         Expression* subexp_list = NULL;
1607
1608         if( EXP_BETWEEN == type ) {
1609                 // Get the left operand
1610                 if( -1 == left_operand_id ) {
1611                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1612                                 "No left operand defined for BETWEEN expression # %d", id ));
1613                         state->error = 1;
1614                         return NULL;
1615                 } else {
1616                         left_operand = getExpression( state, left_operand_id );
1617                         if( !left_operand ) {
1618                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1619                                         "Unable to get left operand in BETWEEN expression # %d", id ));
1620                                 state->error = 1;
1621                                 return NULL;
1622                         }
1623                 }
1624
1625                 // Get the end points of the BETWEEN range
1626                 subexp_list = getExpressionList( state, id );
1627                 if( state->error ) {
1628                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1629                                 "Unable to get subexpressions for BETWEEN expression # %d", id ));
1630                         expressionFree( left_operand );
1631                         return NULL;
1632                 } else if( !subexp_list ) {
1633                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1634                                 "BETWEEN range is empty in expression # %d", id ));
1635                         state->error = 1;
1636                         expressionFree( left_operand );
1637                         return NULL;
1638                 } else if( !subexp_list->next ) {
1639                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1640                                 "BETWEEN range has only one end point in expression # %d", id ));
1641                         state->error = 1;
1642                         expressionListFree( subexp_list );
1643                         expressionFree( left_operand );
1644                         return NULL;
1645                 } else if( subexp_list->next->next ) {
1646                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1647                                 "BETWEEN range has more than two subexpressions in expression # %d", id ));
1648                         state->error = 1;
1649                         expressionListFree( subexp_list );
1650                         expressionFree( left_operand );
1651                         return NULL;
1652                 }
1653
1654         } else if( EXP_BIND == type ) {
1655                 if( bind_variable ) {
1656                         // To do: Build a BindVar
1657                         bind = getBindVar( state, bind_variable );
1658                         if( ! bind ) {
1659                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1660                                         "Unable to load bind variable \"%s\" for expression # %d",
1661                 bind_variable, id ));
1662                                 state->error = 1;
1663                                 return NULL;
1664                         }
1665                         PRINT( "\tBind variable is \"%s\"\n", bind_variable );
1666                 } else {
1667                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1668                                 "No variable specified for bind variable expression # %d",
1669            bind_variable, id ));
1670                         state->error = 1;
1671                         return NULL;
1672                 }
1673                 if( right_operand_id != -1 ) {
1674                         right_operand = getExpression( state, right_operand_id );
1675                         if( !right_operand ) {
1676                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1677                                         "Unable to get right operand in expression # %d", id ));
1678                                 state->error = 1;
1679                                 expressionFree( left_operand );
1680                                 return NULL;
1681                         }
1682                 }
1683
1684         } else if( EXP_CASE == type ) {
1685                 // Get the left operand
1686                 if( -1 == left_operand_id ) {
1687                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1688                                 "No left operand defined for CASE expression # %d", id ));
1689                         state->error = 1;
1690                         return NULL;
1691                 } else {
1692                         left_operand = getExpression( state, left_operand_id );
1693                         if( !left_operand ) {
1694                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1695                                         "Unable to get left operand in CASE expression # %d", id ));
1696                                 state->error = 1;
1697                                 return NULL;
1698                         }
1699                 }
1700
1701                 branch_list = getCaseBranchList( state, id );
1702                 if( ! branch_list ) {
1703                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1704                                 "Unable to build branches for CASE expression # %d", id ));
1705                         state->error = 1;
1706                         return NULL;
1707                 }
1708
1709         } else if( EXP_CAST == type ) {
1710                 // Get the left operand
1711                 if( -1 == left_operand_id ) {
1712                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1713                                 "No left operand defined for CAST expression # %d", id ));
1714                         state->error = 1;
1715                         return NULL;
1716                 } else {
1717                         left_operand = getExpression( state, left_operand_id );
1718                         if( !left_operand ) {
1719                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1720                                         "Unable to get left operand for CAST expression # %d", id ));
1721                                 state->error = 1;
1722                                 return NULL;
1723                         }
1724                 }
1725
1726                 if( -1 == cast_type_id ) {
1727                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1728                                 "No datatype specified for CAST expression # %d", id ));
1729                         state->error = 1;
1730                         return NULL;
1731                 } else {
1732                         cast_type = getDatatype( state, cast_type_id );
1733                         if( !cast_type ) {
1734                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1735                                         "Unable to get datatype for CAST expression # %d", id ));
1736                                 state->error = 1;
1737                                 return NULL;
1738                         }
1739                 }
1740
1741         } else if( EXP_EXIST == type ) {
1742                 if( -1 == subquery_id ) {
1743                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1744                                 "Internal error: No subquery found for EXIST expression # %d", id ));
1745                         state->error = 1;
1746                         return NULL;
1747                 } else {
1748                         subquery = getStoredQuery( state, subquery_id );
1749                         if( !subquery ) {
1750                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1751                                         "Unable to load subquery for EXIST expression # %d", id ));
1752                                 state->error = 1;
1753                                 return NULL;
1754                         }
1755                 }
1756
1757         } else if( EXP_FUNCTION == type ) {
1758                 if( !function_name ) {
1759                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1760                                 "Function call expression # %d provides no function name", id ));
1761                         state->error = 1;
1762                         return NULL;
1763                 } else {
1764                         subexp_list = getExpressionList( state, id );
1765                         if( state->error ) {
1766                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1767                                         "Unable to get parameter list for function expression # %d", id ));
1768                                 return NULL;
1769                         }
1770                 }
1771
1772         } else if( EXP_IN == type ) {
1773                 if( -1 == left_operand_id ) {
1774                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1775                                 "IN condition has no left operand in expression # %d", id ));
1776                         state->error = 1;
1777                         return NULL;
1778                 } else {
1779                         left_operand = getExpression( state, left_operand_id );
1780                         if( !left_operand ) {
1781                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1782                                         "Unable to get left operand for IN condition in expression # %d", id ));
1783                                 state->error = 1;
1784                                 return NULL;
1785                         }
1786                 }
1787
1788                 if( -1 == subquery_id ) {
1789                         // Load an IN list of subexpressions
1790                         subexp_list = getExpressionList( state, id );
1791                         if( state->error ) {
1792                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1793                                         "Unable to get subexpressions for IN list" ));
1794                                 return NULL;
1795                         } else if( !subexp_list ) {
1796                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1797                                         "IN list is empty in expression # %d", id ));
1798                                 state->error = 1;
1799                                 return NULL;
1800                         }
1801                 } else {
1802                         // Load a subquery
1803                         subquery = getStoredQuery( state, subquery_id );
1804                         if( !subquery ) {
1805                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1806                                         "Unable to load subquery for IN expression # %d", id ));
1807                                 state->error = 1;
1808                                 return NULL;
1809                         }
1810                 }
1811
1812         } else if( EXP_ISNULL == type ) {
1813                 if( -1 == left_operand_id ) {
1814                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1815                                 "Expression # %d IS NULL has no left operand", id ));
1816                         state->error = 1;
1817                         return NULL;
1818                 }
1819
1820                 if( left_operand_id != -1 ) {
1821                         left_operand = getExpression( state, left_operand_id );
1822                         if( !left_operand ) {
1823                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1824                                         "Unable to get left operand in expression # %d", id ));
1825                                 state->error = 1;
1826                                 return NULL;
1827                         }
1828                 }
1829
1830         } else if( EXP_NUMBER == type ) {
1831                 if( !literal ) {
1832                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1833                                 "Numeric expression # %d provides no numeric value", id ));
1834                         state->error = 1;
1835                         return NULL;
1836                 }
1837
1838         } else if( EXP_OPERATOR == type ) {
1839                 // Load left and/or right operands
1840                 if( -1 == left_operand_id && -1 == right_operand_id ) {
1841                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1842                                 "Expression # %d is an operator with no operands", id ));
1843                         state->error = 1;
1844                         return NULL;
1845                 }
1846
1847                 if( left_operand_id != -1 ) {
1848                         left_operand = getExpression( state, left_operand_id );
1849                         if( !left_operand ) {
1850                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1851                                         "Unable to get left operand in expression # %d", id ));
1852                                 state->error = 1;
1853                                 return NULL;
1854                         }
1855                 }
1856
1857                 if( right_operand_id != -1 ) {
1858                         right_operand = getExpression( state, right_operand_id );
1859                         if( !right_operand ) {
1860                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1861                                         "Unable to get right operand in expression # %d", id ));
1862                                 state->error = 1;
1863                                 return NULL;
1864                         }
1865                 }
1866
1867         } else if( EXP_SERIES == type ) {
1868                 subexp_list = getExpressionList( state, id );
1869                 if( state->error ) {
1870                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1871                                 "Unable to get subexpressions for expression series using operator \"%s\"",
1872                                         operator ? operator : "," ));
1873                         return NULL;
1874                 } else if( !subexp_list ) {
1875                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1876                                 "Series expression is empty in expression # %d", id ));
1877                         state->error = 1;
1878                         return NULL;
1879                 }
1880
1881         } else if( EXP_STRING == type ) {
1882                 if( !literal ) {
1883                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1884                                 "String expression # %d provides no string value", id ));
1885                         state->error = 1;
1886                         return NULL;
1887                 }
1888
1889         } else if( EXP_SUBQUERY == type ) {
1890                 if( -1 == subquery_id ) {
1891                         osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1892                                 "Subquery expression # %d has no query id", id ));
1893                         state->error = 1;
1894                         return NULL;
1895                 } else {
1896                         // Load a subquery, if there is one
1897                         subquery = getStoredQuery( state, subquery_id );
1898                         if( !subquery ) {
1899                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1900                                         "Unable to load subquery for expression # %d", id ));
1901                                 state->error = 1;
1902                                 return NULL;
1903                         }
1904                         if( subquery->select_list && subquery->select_list->next ) {
1905                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1906                                         "Subquery # %d as expression returns more than one column", subquery_id ));
1907                                 state->error = 1;
1908                                 return NULL;
1909                         }
1910                         PRINT( "\tExpression is subquery %d\n", subquery_id );
1911                 }
1912         }
1913
1914         // Allocate an Expression: from the free list if possible, from the heap if necessary
1915         Expression* exp = NULL;
1916         if( free_expression_list ) {
1917                 exp = free_expression_list;
1918                 free_expression_list = free_expression_list->next;
1919         } else
1920                 exp = safe_malloc( sizeof( Expression ) );
1921
1922         // Populate the Expression
1923         exp->next = NULL;
1924         exp->id = id;
1925         exp->type = type;
1926         exp->parenthesize = parenthesize;
1927         exp->parent_expr_id = parent_expr_id;
1928         exp->seq_no = seq_no;
1929         exp->literal = literal ? strdup( literal ) : NULL;
1930         exp->table_alias = table_alias ? strdup( table_alias ) : NULL;
1931         exp->column_name = column_name ? strdup( column_name ) : NULL;
1932         exp->left_operand = left_operand;
1933         exp->op = operator ? strdup( operator ) : NULL;
1934         exp->right_operand = right_operand;
1935         exp->subquery_id = subquery_id;
1936         exp->subquery = subquery;
1937         exp->cast_type = cast_type;
1938         exp->negate = negate;
1939         exp->bind = bind;
1940         exp->branch_list = branch_list;
1941         exp->subexp_list = subexp_list;
1942         exp->function_name = function_name ? strdup( function_name ) : NULL;
1943
1944         return exp;
1945 }
1946
1947 /**
1948         @brief Free all the Expressions in a linked list of Expressions.
1949         @param exp Pointer to the first Expression in the list.
1950 */
1951 static void expressionListFree( Expression* exp ) {
1952         while( exp ) {
1953                 Expression* next = exp->next;
1954                 expressionFree( exp );
1955                 exp = next;
1956         }
1957 }
1958
1959 /**
1960         @brief Deallocate an Expression.
1961         @param exp Pointer to the Expression to be deallocated.
1962
1963         Free the strings owned by the Expression.  Put the Expression itself, and any
1964         subexpressions that it owns, into a free list.
1965 */
1966 static void expressionFree( Expression* exp ) {
1967         if( exp ) {
1968                 free( exp->literal );
1969                 exp->literal = NULL;
1970                 free( exp->table_alias );
1971                 exp->table_alias = NULL;
1972                 free( exp->column_name );
1973                 exp->column_name = NULL;
1974                 if( exp->left_operand ) {
1975                         expressionFree( exp->left_operand );
1976                         exp->left_operand = NULL;
1977                 }
1978                 if( exp->op ) {
1979                         free( exp->op );
1980                         exp->op = NULL;
1981                 }
1982                 if( exp->right_operand ) {
1983                         expressionFree( exp->right_operand );
1984                         exp->right_operand = NULL;
1985                 }
1986                 if( exp->subquery ) {
1987                         storedQFree( exp->subquery );
1988                         exp->subquery = NULL;
1989                 }
1990                 if( exp->cast_type ) {
1991                         datatypeFree( exp->cast_type );
1992                         exp->cast_type = NULL;
1993                 }
1994
1995                 // We don't free the bind member here because the Expression doesn't own it;
1996                 // the bindvar_list hash owns it, so that multiple Expressions can reference it.
1997
1998                 if( exp->subexp_list ) {
1999                         // Free the linked list of subexpressions
2000                         expressionListFree( exp->subexp_list );
2001                         exp->subexp_list = NULL;
2002                 }
2003
2004                 if( exp->branch_list ) {
2005                         freeBranchList( exp->branch_list );
2006                         exp->branch_list = NULL;
2007                 }
2008
2009                 if( exp->function_name ) {
2010                         free( exp->function_name );
2011                         exp->function_name = NULL;
2012                 }
2013
2014                 // Prepend to the free list
2015                 exp->next = free_expression_list;
2016                 free_expression_list = exp;
2017         }
2018 }
2019
2020 /**
2021         @brief Build a list of subexpressions.
2022         @param state Pointer to the query-building context.
2023         @param id ID of the parent Expression.
2024         @return A pointer to the first in a linked list of Expressions, if there are any; or
2025                 NULL if there aren't any, or in case of an error.
2026 */
2027 static Expression* getExpressionList( BuildSQLState* state, int id ) {
2028         Expression* exp_list = NULL;
2029
2030         // The ORDER BY is in descending order so that we can build the list by adding to
2031         // the head, and it will wind up in the right order.
2032         dbi_result result = dbi_conn_queryf( state->dbhandle,
2033                 "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, "
2034                 "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, "
2035                 "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, "
2036                 "func.function_name "
2037                 "FROM query.expression AS exp LEFT JOIN query.function_sig AS func "
2038                 "ON (exp.function_id = func.id) "
2039                 "WHERE exp.parent_expr = %d "
2040                 "ORDER BY exp.seq_no desc;", id );
2041
2042         if( result ) {
2043                 if( dbi_result_first_row( result ) ) {
2044                         while( 1 ) {
2045                                 Expression* exp = constructExpression( state, result );
2046                                 if( exp ) {
2047                                         PRINT( "Found a subexpression\n" );
2048                                         exp->next = exp_list;
2049                                         exp_list  = exp;
2050                                 } else {
2051                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2052                                                 "Unable to build subexpression list for expression id #%d", id ));
2053                                         expressionListFree( exp_list );
2054                                         exp_list = NULL;
2055                                         break;
2056                                 }
2057                                 if( !dbi_result_next_row( result ) )
2058                                         break;
2059                         };
2060                 }
2061         } else {
2062                 const char* msg;
2063                 int errnum = dbi_conn_error( state->dbhandle, &msg );
2064                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2065                         "Unable to query query.expression table for expression list: #%d %s",
2066                         errnum, msg ? msg : "No description available" ));
2067                 state->error = 1;
2068         }
2069
2070         return exp_list;
2071 }
2072
2073 /**
2074         @brief Build a list of ORDER BY items as a linked list of OrderItems.
2075         @param state Pointer to the query-building context.
2076         @param query_id ID for the query to which the ORDER BY belongs.
2077         @return Pointer to the first node in a linked list of OrderItems.
2078
2079         The calling code is responsible for freeing the list by calling orderItemListFree().
2080 */
2081 static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) {
2082         OrderItem* ord_list = NULL;
2083
2084         // The ORDER BY is in descending order so that we can build the list by adding to
2085         // the head, and it will wind up in the right order.
2086         dbi_result result = dbi_conn_queryf( state->dbhandle,
2087                 "SELECT id, stored_query, seq_no, expression "
2088                 "FROM query.order_by_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
2089         if( result ) {
2090                 if( dbi_result_first_row( result ) ) {
2091                         while( 1 ) {
2092                                 OrderItem* item = constructOrderItem( state, result );
2093                                 if( item ) {
2094                                         PRINT( "Found an ORDER BY item\n" );
2095
2096                                         item->next = ord_list;
2097                                         ord_list = item;
2098                                 } else {
2099                                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2100                                                 "Unable to build ORDER BY item for query id #%d", query_id ));
2101                                         orderItemListFree( ord_list );
2102                                         ord_list = NULL;
2103                                         break;
2104                                 }
2105                                 if( !dbi_result_next_row( result ) )
2106                                         break;
2107                         };
2108                 }
2109         }  else {
2110                 const char* msg;
2111                 int errnum = dbi_conn_error( state->dbhandle, &msg );
2112                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2113                         "Unable to query query.order_by_list table: #%d %s",
2114                         errnum, msg ? msg : "No description available" ));
2115                 state->error = 1;
2116         }
2117
2118         return ord_list;
2119 }
2120
2121 /**
2122         @brief Construct an OrderItem.
2123         @param Pointer to the query-building context.
2124         @param result Database cursor positioned at a row in query.order_by_item.
2125         @return Pointer to a newly constructed OrderItem, if successful, or NULL if not.
2126
2127         The calling code is responsible for freeing the OrderItems by calling orderItemListFree().
2128 */
2129 static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ) {
2130         int id                   = dbi_result_get_int_idx( result, 1 );
2131         int stored_query_id      = dbi_result_get_int_idx( result, 2 );
2132         int seq_no               = dbi_result_get_int_idx( result, 3 );
2133         int expression_id        = dbi_result_get_int_idx( result, 4 );
2134         // Allocate a SelectItem: from the free list if possible, from the heap if necessary
2135
2136         // Construct an Expression
2137         Expression* expression = getExpression( state, expression_id );
2138         if( !expression ) {
2139                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
2140                         "Unable to fetch ORDER BY expression for id = %d", expression_id ));
2141                 state->error = 1;
2142                 return NULL;
2143         };
2144
2145         // Allocate an OrderItem; from the free list if possible, or from the heap if necessary.
2146         OrderItem* ord;
2147         if( free_order_item_list ) {
2148                 ord = free_order_item_list;
2149                 free_order_item_list = free_order_item_list->next;
2150         } else
2151                 ord = safe_malloc( sizeof( OrderItem ));
2152
2153         ord->next            = NULL;
2154         ord->id              = id;
2155         ord->stored_query_id = stored_query_id;
2156         ord->seq_no          = seq_no;
2157         ord->expression      = expression;
2158
2159         return ord;
2160 }
2161
2162 /**
2163         @brief Deallocate a linked list of OrderItems.
2164         @param exp Pointer to the first OrderItem in the list to be deallocated.
2165
2166         Deallocate the memory owned by the OrderItems.  Put the items themselves into a free list.
2167 */
2168 static void orderItemListFree( OrderItem* ord ) {
2169         if( !ord )
2170                 return;    // Nothing to free
2171
2172         OrderItem* first = ord;
2173         while( 1 ) {
2174                 expressionFree( ord->expression );
2175                 ord->expression = NULL;
2176
2177                 if( NULL == ord->next ) {
2178                         ord->next = free_order_item_list;
2179                         break;
2180                 } else
2181                         ord = ord->next;
2182         };
2183
2184         // Transfer the entire list to the free list
2185         free_order_item_list = first;
2186 }
2187
2188 /**
2189         @brief Push an IdNode onto a stack of IdNodes.
2190         @param stack Pointer to the stack.
2191         @param id Id of the new node.
2192         @param alias Alias, if any, of the new node.
2193 */
2194 static void push_id( IdNode** stack, int id, const char* alias ) {
2195
2196         if( stack ) {
2197                 // Allocate a node; from the free list if possible, from the heap if necessary.
2198                 IdNode* node = NULL;
2199                 if( free_id_node_list ) {
2200                         node = free_id_node_list;
2201                         free_id_node_list = free_id_node_list->next;
2202                 } else
2203                         node = safe_malloc( sizeof( IdNode ));
2204
2205                 // Populate it
2206                 node->next = *stack;
2207                 node->id = id;
2208                 if( alias )
2209                         node->alias = strdup( alias );
2210                 else
2211                         node->alias = NULL;
2212
2213                 // Reseat the stack
2214                 *stack = node;
2215         }
2216 }
2217
2218 /**
2219         @brief Remove the node at the top of an IdNode stack.
2220         @param stack Pointer to the IdNode stack.
2221 */
2222 void pop_id( IdNode** stack ) {
2223         if( stack ) {
2224                 IdNode* node = *stack;
2225                 *stack = node->next;
2226
2227                 if( node->alias ) {
2228                         free( node->alias );
2229                         node->alias = NULL;
2230                 }
2231
2232                 node->next = free_id_node_list;
2233                 free_id_node_list = node;
2234         }
2235 }
2236
2237 /**
2238         @brief Search a stack of IDs for a match by either ID or, optionally, by alias.
2239         @param stack Pointer to the stack.
2240         @param id The id to search for.
2241         @param alias (Optional) the alias to search for.
2242         @return A pointer to the matching node if one is found, or NULL if not.
2243
2244         This search is used to detect cases where a query, expression, or FROM clause is nested
2245         inside itself, in order to avoid infinite recursion; or in order to avoid conflicting
2246         table references in a FROM clause.
2247 */
2248 static const IdNode* searchIdStack( const IdNode* stack, int id, const char* alias ) {
2249         if( stack ) {
2250                 const IdNode* node = stack;
2251                 while( node ) {
2252                         if( node->id == id )
2253                                 return node;        // Matched on id
2254                         else if( alias && node->alias && !strcmp( alias, node->alias ))
2255                                 return node;        // Matched on alias
2256                         else
2257                                 node = node->next;
2258                 }
2259         }
2260         return NULL;   // No match found
2261 }
2262
2263 /**
2264         @brief Free up any resources held by the StoredQ module.
2265 */
2266 void storedQCleanup( void ) {
2267
2268         // Free all the nodes in the free state list
2269         StoredQ* sq = free_storedq_list;
2270         while( sq ) {
2271                 free_storedq_list = sq->next;
2272                 free( sq );
2273                 sq = free_storedq_list;
2274         }
2275
2276         // Free all the nodes in the free from_relation list
2277         FromRelation* fr = free_from_relation_list;
2278         while( fr ) {
2279                 free_from_relation_list = fr->next;
2280                 free( fr );
2281                 fr = free_from_relation_list;
2282         }
2283
2284         // Free all the nodes in the free expression list
2285         Expression* exp = free_expression_list;
2286         while( exp ) {
2287                 free_expression_list = exp->next;
2288                 free( exp );
2289                 exp = free_expression_list;
2290         }
2291
2292         // Free all the nodes in the free select item list
2293         SelectItem* sel = free_select_item_list;
2294         while( sel ) {
2295                 free_select_item_list = sel->next;
2296                 free( sel );
2297                 sel = free_select_item_list;
2298         }
2299
2300         // Free all the nodes in the free select item list
2301         IdNode* node = free_id_node_list;
2302         while( node ) {
2303                 free_id_node_list = node->next;
2304                 free( node );
2305                 node = free_id_node_list;
2306         }
2307
2308         // Free all the nodes in the free query sequence list
2309         QSeq* seq = free_qseq_list;
2310         while( seq ) {
2311                 free_qseq_list = seq->next;
2312                 free( seq );
2313                 seq = free_qseq_list;
2314         }
2315
2316         // Free all the nodes in the free order item list
2317         OrderItem* ord = free_order_item_list;
2318         while( ord ) {
2319                 free_order_item_list = ord->next;
2320                 free( ord );
2321                 ord = free_order_item_list;
2322         }
2323
2324         // Free all the nodes in the bind variable free list
2325         BindVar* bind = free_bindvar_list;
2326         while( bind ) {
2327                 free_bindvar_list = bind->next;
2328                 free( bind );
2329                 bind = free_bindvar_list;
2330         }
2331
2332         // Free all the nodes in the case branch free list
2333         CaseBranch* branch = free_branch_list;
2334         while( branch ) {
2335                 free_branch_list = branch->next;
2336                 free( branch );
2337                 branch = free_branch_list;
2338         }
2339
2340         // Free all the nodes in the datatype free list
2341         Datatype* datatype = free_datatype_list;
2342         while( datatype ) {
2343                 free_datatype_list = datatype->next;
2344                 free( datatype );
2345                 datatype = free_datatype_list;
2346         }
2347 }
2348
2349 /**
2350         @brief Return a boolean value from a database result.
2351         @param result The database result.
2352         @param i Index of the column in the result, starting with 1 );
2353         @return 1 if true, or 0 for false.
2354
2355         Null values and error conditions are interpreted as FALSE.
2356 */
2357 static int oils_result_get_bool_idx( dbi_result result, int i ) {
2358         if( result ) {
2359                 const char* str = dbi_result_get_string_idx( result, i );
2360                 return (str && *str == 't' ) ? 1 : 0;
2361         } else
2362                 return 0;
2363 }
2364
2365 /**
2366         @brief Enable verbose messages.
2367
2368         The messages are written to standard output, which for a server is /dev/null.  Hence this
2369         option is useful only for a non-server.  It is intended only as a convenience for
2370         development and debugging.
2371 */
2372 void oilsStoredQSetVerbose( void ) {
2373         verbose = 1;
2374 }