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

   1 /**
   2         @file oils_sql.c
   3         @brief Utility routines for translating JSON into SQL.
   4 */
   5
   6 #include <stdlib.h>
   7 #include <string.h>
   8 #include <time.h>
   9 #include <ctype.h>
  10 #include <dbi/dbi.h>
  11 #include "opensrf/utils.h"
  12 #include "opensrf/log.h"
  13 #include "opensrf/osrf_application.h"
  14 #include "openils/oils_utils.h"
  15 #include "openils/oils_sql.h"
  16
  17 // The next four macros are OR'd together as needed to form a set
  18 // of bitflags.  SUBCOMBO enables an extra pair of parentheses when
  19 // nesting one UNION, INTERSECT or EXCEPT inside another.
  20 // SUBSELECT tells us we're in a subquery, so don't add the
  21 // terminal semicolon yet.
  22 #define SUBCOMBO    8
  23 #define SUBSELECT   4
  24 #define DISABLE_I18N    2
  25 #define SELECT_DISTINCT 1
  26
  27 #define AND_OP_JOIN     0
  28 #define OR_OP_JOIN      1
  29
  30 struct ClassInfoStruct;
  31 typedef struct ClassInfoStruct ClassInfo;
  32
  33 #define ALIAS_STORE_SIZE 16
  34 #define CLASS_NAME_STORE_SIZE 16
  35
  36 struct ClassInfoStruct {
  37         char* alias;
  38         char* class_name;
  39         char* source_def;
  40         osrfHash* class_def;      // Points into IDL
  41         osrfHash* fields;         // Points into IDL
  42         osrfHash* links;          // Points into IDL
  43
  44         // The remaining members are private and internal.  Client code should not
  45         // access them directly.
  46
  47         ClassInfo* next;          // Supports linked list of joined classes
  48         int in_use;               // boolean
  49
  50         // We usually store the alias and class name in the following arrays, and
  51         // point the corresponding pointers at them.  When the string is too big
  52         // for the array (which will probably never happen in practice), we strdup it.
  53
  54         char alias_store[ ALIAS_STORE_SIZE + 1 ];
  55         char class_name_store[ CLASS_NAME_STORE_SIZE + 1 ];
  56 };
  57
  58 struct QueryFrameStruct;
  59 typedef struct QueryFrameStruct QueryFrame;
  60
  61 struct QueryFrameStruct {
  62         ClassInfo core;
  63         ClassInfo* join_list;  // linked list of classes joined to the core class
  64         QueryFrame* next;      // implements stack as linked list
  65         int in_use;            // boolean
  66 };
  67
  68 static int timeout_needs_resetting;
  69 static time_t time_next_reset;
  70
  71 static int verifyObjectClass ( osrfMethodContext*, const jsonObject* );
  72
  73 static void setXactId( osrfMethodContext* ctx );
  74 static inline const char* getXactId( osrfMethodContext* ctx );
  75 static inline void clearXactId( osrfMethodContext* ctx );
  76
  77 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* class_meta,
  78                 jsonObject* where_hash, jsonObject* query_hash, int* err );
  79 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result, osrfHash* );
  80 static jsonObject* oilsMakeJSONFromResult( dbi_result );
  81
  82 static char* searchSimplePredicate ( const char* op, const char* class_alias,
  83                                 osrfHash* field, const jsonObject* node );
  84 static char* searchFunctionPredicate ( const char*, osrfHash*, const jsonObject*, const char* );
  85 static char* searchFieldTransform ( const char*, osrfHash*, const jsonObject* );
  86 static char* searchFieldTransformPredicate ( const ClassInfo*, osrfHash*, const jsonObject*,
  87                 const char* );
  88 static char* searchBETWEENPredicate ( const char*, osrfHash*, const jsonObject* );
  89 static char* searchINPredicate ( const char*, osrfHash*,
  90                                                                  jsonObject*, const char*, osrfMethodContext* );
  91 static char* searchPredicate ( const ClassInfo*, osrfHash*, jsonObject*, osrfMethodContext* );
  92 static char* searchJOIN ( const jsonObject*, const ClassInfo* left_info );
  93 static char* searchWHERE ( const jsonObject* search_hash, const ClassInfo*, int, osrfMethodContext* );
  94 static char* buildSELECT( const jsonObject*, jsonObject* rest_of_query,
  95         osrfHash* meta, osrfMethodContext* ctx );
  96 static char* buildOrderByFromArray( osrfMethodContext* ctx, const jsonObject* order_array );
  97
  98 char* buildQuery( osrfMethodContext* ctx, jsonObject* query, int flags );
  99
 100 char* SELECT ( osrfMethodContext*, jsonObject*, const jsonObject*, const jsonObject*,
 101         const jsonObject*, const jsonObject*, const jsonObject*, const jsonObject*, int );
 102
 103 static osrfStringArray* getPermLocationCache( osrfMethodContext*, const char* );
 104 static void setPermLocationCache( osrfMethodContext*, const char*, osrfStringArray* );
 105
 106 void userDataFree( void* );
 107 static void sessionDataFree( char*, void* );
 108 static void pcacheFree( char*, void* );
 109 static int obj_is_true( const jsonObject* obj );
 110 static const char* json_type( int code );
 111 static const char* get_primitive( osrfHash* field );
 112 static const char* get_datatype( osrfHash* field );
 113 static void pop_query_frame( void );
 114 static void push_query_frame( void );
 115 static int add_query_core( const char* alias, const char* class_name );
 116 static inline ClassInfo* search_alias( const char* target );
 117 static ClassInfo* search_all_alias( const char* target );
 118 static ClassInfo* add_joined_class( const char* alias, const char* classname );
 119 static void clear_query_stack( void );
 120
 121 static const jsonObject* verifyUserPCRUD( osrfMethodContext* );
 122 static int verifyObjectPCRUD( osrfMethodContext*, osrfHash*, const jsonObject*, int );
 123 static const char* org_tree_root( osrfMethodContext* ctx );
 124 static jsonObject* single_hash( const char* key, const char* value );
 125
 126 static int child_initialized = 0;   /* boolean */
 127
 128 static dbi_conn writehandle; /* our MASTER db connection */
 129 static dbi_conn dbhandle; /* our CURRENT db connection */
 130 //static osrfHash * readHandles;
 131
 132 // The following points to the top of a stack of QueryFrames.  It's a little
 133 // confusing because the top level of the query is at the bottom of the stack.
 134 static QueryFrame* curr_query = NULL;
 135
 136 static dbi_conn writehandle; /* our MASTER db connection */
 137 static dbi_conn dbhandle; /* our CURRENT db connection */
 138 //static osrfHash * readHandles;
 139
 140 static int max_flesh_depth = 100;
 141
 142 static int perm_at_threshold = 5;
 143 static int enforce_pcrud = 0;     // Boolean
 144 static char* modulename = NULL;
 145
 146 /**
 147         @brief Connect to the database.
 148         @return A database connection if successful, or NULL if not.
 149 */
 150 dbi_conn oilsConnectDB( const char* mod_name ) {
 151
 152         osrfLogDebug( OSRF_LOG_MARK, "Attempting to initialize libdbi..." );
 153         if( dbi_initialize( NULL ) == -1 ) {
 154                 osrfLogError( OSRF_LOG_MARK, "Unable to initialize libdbi" );
 155                 return NULL;
 156         } else
 157                 osrfLogDebug( OSRF_LOG_MARK, "... libdbi initialized." );
 158
 159         char* driver = osrf_settings_host_value( "/apps/%s/app_settings/driver", mod_name );
 160         char* user   = osrf_settings_host_value( "/apps/%s/app_settings/database/user", mod_name );
 161         char* host   = osrf_settings_host_value( "/apps/%s/app_settings/database/host", mod_name );
 162         char* port   = osrf_settings_host_value( "/apps/%s/app_settings/database/port", mod_name );
 163         char* db     = osrf_settings_host_value( "/apps/%s/app_settings/database/db", mod_name );
 164         char* pw     = osrf_settings_host_value( "/apps/%s/app_settings/database/pw", mod_name );
 165
 166         osrfLogDebug( OSRF_LOG_MARK, "Attempting to load the database driver [%s]...", driver );
 167         dbi_conn handle = dbi_conn_new( driver );
 168
 169         if( !handle ) {
 170                 osrfLogError( OSRF_LOG_MARK, "Error loading database driver [%s]", driver );
 171                 return NULL;
 172         }
 173         osrfLogDebug( OSRF_LOG_MARK, "Database driver [%s] seems OK", driver );
 174
 175         osrfLogInfo(OSRF_LOG_MARK, "%s connecting to database.  host=%s, "
 176                 "port=%s, user=%s, db=%s", mod_name, host, port, user, db );
 177
 178         if( host ) dbi_conn_set_option( handle, "host", host );
 179         if( port ) dbi_conn_set_option_numeric( handle, "port", atoi( port ));
 180         if( user ) dbi_conn_set_option( handle, "username", user );
 181         if( pw )   dbi_conn_set_option( handle, "password", pw );
 182         if( db )   dbi_conn_set_option( handle, "dbname", db );
 183
 184         free( user );
 185         free( host );
 186         free( port );
 187         free( db );
 188         free( pw );
 189
 190         if( dbi_conn_connect( handle ) < 0 ) {
 191                 sleep( 1 );
 192                 if( dbi_conn_connect( handle ) < 0 ) {
 193                         const char* msg;
 194                         dbi_conn_error( handle, &msg );
 195                         osrfLogError( OSRF_LOG_MARK, "Error connecting to database: %s",
 196                                 msg ? msg : "(No description available)" );
 197                         return NULL;
 198                 }
 199         }
 200
 201         osrfLogInfo( OSRF_LOG_MARK, "%s successfully connected to the database", mod_name );
 202
 203         return handle;
 204 }
 205
 206 /**
 207         @brief Select some options.
 208         @param module_name: Name of the server.
 209         @param do_pcrud: Boolean.  True if we are to enforce PCRUD permissions.
 210
 211         This source file is used (at this writing) to implement three different servers:
 212         - open-ils.reporter-store
 213         - open-ils.pcrud
 214         - open-ils.cstore
 215
 216         These servers behave mostly the same, but they implement different combinations of
 217         methods, and open-ils.pcrud enforces a permissions scheme that the other two don't.
 218
 219         Here we use the server name in messages to identify which kind of server issued them.
 220         We use do_crud as a boolean to control whether or not to enforce the permissions scheme.
 221 */
 222 void oilsSetSQLOptions( const char* module_name, int do_pcrud, int flesh_depth ) {
 223         if( !module_name )
 224                 module_name = "open-ils.cstore";   // bulletproofing with a default
 225
 226         if( modulename )
 227                 free( modulename );
 228
 229         modulename = strdup( module_name );
 230         enforce_pcrud = do_pcrud;
 231         max_flesh_depth = flesh_depth;
 232 }
 233
 234 /**
 235         @brief Install a database connection.
 236         @param conn Pointer to a database connection.
 237
 238         In some contexts, @a conn may merely provide a driver so that we can process strings
 239         properly, without providing an open database connection.
 240 */
 241 void oilsSetDBConnection( dbi_conn conn ) {
 242         dbhandle = writehandle = conn;
 243 }
 244
 245 /**
 246         @brief Determine whether a database connection is alive.
 247         @param handle Handle for a database connection.
 248         @return 1 if the connection is alive, or zero if it isn't.
 249 */
 250 int oilsIsDBConnected( dbi_conn handle ) {
 251         // Do an innocuous SELECT.  If it succeeds, the database connection is still good.
 252         dbi_result result = dbi_conn_query( handle, "SELECT 1;" );
 253         if( result ) {
 254                 dbi_result_free( result );
 255                 return 1;
 256         } else {
 257                 // This is a terrible, horrible, no good, very bad kludge.
 258                 // Sometimes the SELECT 1 query fails, not because the database connection is dead,
 259                 // but because (due to a previous error) the database is ignoring all commands,
 260                 // even innocuous SELECTs, until the current transaction is rolled back.  The only
 261                 // known way to detect this condition via the dbi library is by looking at the error
 262                 // message.  This approach will break if the language or wording of the message ever
 263                 // changes.
 264                 // Note: the dbi_conn_ping function purports to determine whether the database
 265                 // connection is live, but at this writing this function is unreliable and useless.
 266                 static const char* ok_msg = "ERROR:  current transaction is aborted, commands "
 267                         "ignored until end of transaction block\n";
 268                 const char* msg;
 269                 dbi_conn_error( handle, &msg );
 270                 if( strcmp( msg, ok_msg )) {
 271                         osrfLogError( OSRF_LOG_MARK, "Database connection isn't working" );
 272                         return 0;
 273                 } else
 274                         return 1;   // ignoring SELECT due to previous error; that's okay
 275         }
 276 }
 277
 278 /**
 279         @brief Get a table name, view name, or subquery for use in a FROM clause.
 280         @param class Pointer to the IDL class entry.
 281         @return A table name, a view name, or a subquery in parentheses.
 282
 283         In some cases the IDL defines a class, not with a table name or a view name, but with
 284         a SELECT statement, which may be used as a subquery.
 285 */
 286 char* oilsGetRelation( osrfHash* classdef ) {
 287
 288         char* source_def = NULL;
 289         const char* tabledef = osrfHashGet( classdef, "tablename" );
 290
 291         if( tabledef ) {
 292                 source_def = strdup( tabledef );   // Return the name of a table or view
 293         } else {
 294                 tabledef = osrfHashGet( classdef, "source_definition" );
 295                 if( tabledef ) {
 296                         // Return a subquery, enclosed in parentheses
 297                         source_def = safe_malloc( strlen( tabledef ) + 3 );
 298                         source_def[ 0 ] = '(';
 299                         strcpy( source_def + 1, tabledef );
 300                         strcat( source_def, ")" );
 301                 } else {
 302                         // Not found: return an error
 303                         const char* classname = osrfHashGet( classdef, "classname" );
 304                         if( !classname )
 305                                 classname = "???";
 306                         osrfLogError(
 307                                 OSRF_LOG_MARK,
 308                                 "%s ERROR No tablename or source_definition for class \"%s\"",
 309                                 modulename,
 310                                 classname
 311                         );
 312                 }
 313         }
 314
 315         return source_def;
 316 }
 317
 318 /**
 319         @brief Add datatypes from the database to the fields in the IDL.
 320         @param handle Handle for a database connection
 321         @return Zero if successful, or 1 upon error.
 322
 323         For each relevant class in the IDL: ask the database for the datatype of every field.
 324         In particular, determine which fields are text fields and which fields are numeric
 325         fields, so that we know whether to enclose their values in quotes.
 326 */
 327 int oilsExtendIDL( dbi_conn handle ) {
 328         osrfHashIterator* class_itr = osrfNewHashIterator( oilsIDL() );
 329         osrfHash* class = NULL;
 330         growing_buffer* query_buf = buffer_init( 64 );
 331         int results_found = 0;   // boolean
 332
 333         // For each class in the IDL...
 334         while( (class = osrfHashIteratorNext( class_itr ) ) ) {
 335                 const char* classname = osrfHashIteratorKey( class_itr );
 336                 osrfHash* fields = osrfHashGet( class, "fields" );
 337
 338                 // If the class is virtual, ignore it
 339                 if( str_is_true( osrfHashGet(class, "virtual") ) ) {
 340                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 341                         continue;
 342                 }
 343
 344                 char* tabledef = oilsGetRelation( class );
 345                 if( !tabledef )
 346                         continue;   // No such relation -- a query of it would be doomed to failure
 347
 348                 buffer_reset( query_buf );
 349                 buffer_fadd( query_buf, "SELECT * FROM %s AS x WHERE 1=0;", tabledef );
 350
 351                 free(tabledef );
 352
 353                 osrfLogDebug( OSRF_LOG_MARK, "%s Investigatory SQL = %s",
 354                                 modulename, OSRF_BUFFER_C_STR( query_buf ) );
 355
 356                 dbi_result result = dbi_conn_query( handle, OSRF_BUFFER_C_STR( query_buf ) );
 357                 if( result ) {
 358
 359                         results_found = 1;
 360                         int columnIndex = 1;
 361                         const char* columnName;
 362                         while( (columnName = dbi_result_get_field_name(result, columnIndex)) ) {
 363
 364                                 osrfLogInternal( OSRF_LOG_MARK, "Looking for column named [%s]...",
 365                                                 columnName );
 366
 367                                 /* fetch the fieldmapper index */
 368                                 osrfHash* _f = osrfHashGet(fields, columnName);
 369                                 if( _f ) {
 370
 371                                         osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", columnName);
 372
 373                                         /* determine the field type and storage attributes */
 374
 375                                         switch( dbi_result_get_field_type_idx( result, columnIndex )) {
 376
 377                                                 case DBI_TYPE_INTEGER : {
 378
 379                                                         if( !osrfHashGet(_f, "primitive") )
 380                                                                 osrfHashSet(_f, "number", "primitive");
 381
 382                                                         int attr = dbi_result_get_field_attribs_idx( result, columnIndex );
 383                                                         if( attr & DBI_INTEGER_SIZE8 )
 384                                                                 osrfHashSet( _f, "INT8", "datatype" );
 385                                                         else
 386                                                                 osrfHashSet( _f, "INT", "datatype" );
 387                                                         break;
 388                                                 }
 389                                                 case DBI_TYPE_DECIMAL :
 390                                                         if( !osrfHashGet( _f, "primitive" ))
 391                                                                 osrfHashSet( _f, "number", "primitive" );
 392
 393                                                         osrfHashSet( _f, "NUMERIC", "datatype" );
 394                                                         break;
 395
 396                                                 case DBI_TYPE_STRING :
 397                                                         if( !osrfHashGet( _f, "primitive" ))
 398                                                                 osrfHashSet( _f, "string", "primitive" );
 399
 400                                                         osrfHashSet( _f,"TEXT", "datatype" );
 401                                                         break;
 402
 403                                                 case DBI_TYPE_DATETIME :
 404                                                         if( !osrfHashGet( _f, "primitive" ))
 405                                                                 osrfHashSet( _f, "string", "primitive" );
 406
 407                                                         osrfHashSet( _f, "TIMESTAMP", "datatype" );
 408                                                         break;
 409
 410                                                 case DBI_TYPE_BINARY :
 411                                                         if( !osrfHashGet( _f, "primitive" ))
 412                                                                 osrfHashSet( _f, "string", "primitive" );
 413
 414                                                         osrfHashSet( _f, "BYTEA", "datatype" );
 415                                         }
 416
 417                                         osrfLogDebug(
 418                                                 OSRF_LOG_MARK,
 419                                                 "Setting [%s] to primitive [%s] and datatype [%s]...",
 420                                                 columnName,
 421                                                 osrfHashGet( _f, "primitive" ),
 422                                                 osrfHashGet( _f, "datatype" )
 423                                         );
 424                                 }
 425                                 ++columnIndex;
 426                         } // end while loop for traversing columns of result
 427                         dbi_result_free( result  );
 428                 } else {
 429                         const char* msg;
 430                         int errnum = dbi_conn_error( handle, &msg );
 431                         osrfLogDebug( OSRF_LOG_MARK, "No data found for class [%s]: %d, %s", classname,
 432                                 errnum, msg ? msg : "(No description available)" );
 433                         // We don't check the database connection here.  It's routine to get failures at
 434                         // this point; we routinely try to query tables that don't exist, because they
 435                         // are defined in the IDL but not in the database.
 436                 }
 437         } // end for each class in IDL
 438
 439         buffer_free( query_buf );
 440         osrfHashIteratorFree( class_itr );
 441         child_initialized = 1;
 442
 443         if( !results_found ) {
 444                 osrfLogError( OSRF_LOG_MARK,
 445                         "No results found for any class -- bad database connection?" );
 446                 return 1;
 447         } else if( ! oilsIsDBConnected( handle )) {
 448                 osrfLogError( OSRF_LOG_MARK,
 449                         "Unable to extend IDL: database connection isn't working" );
 450                 return 1;
 451         }
 452         else
 453                 return 0;
 454 }
 455
 456 /**
 457         @brief Free an osrfHash that stores a transaction ID.
 458         @param blob A pointer to the osrfHash to be freed, cast to a void pointer.
 459
 460         This function is a callback, to be called by the application session when it ends.
 461         The application session stores the osrfHash via an opaque pointer.
 462
 463         If the osrfHash contains an entry for the key "xact_id", it means that an
 464         uncommitted transaction is pending.  Roll it back.
 465 */
 466 void userDataFree( void* blob ) {
 467         osrfHash* hash = (osrfHash*) blob;
 468         if( osrfHashGet( hash, "xact_id" ) && writehandle ) {
 469                 if( !dbi_conn_query( writehandle, "ROLLBACK;" )) {
 470                         const char* msg;
 471                         int errnum = dbi_conn_error( writehandle, &msg );
 472                         osrfLogWarning( OSRF_LOG_MARK, "Unable to perform rollback: %d %s",
 473                                 errnum, msg ? msg : "(No description available)" );
 474                 };
 475         }
 476
 477         osrfHashFree( hash );
 478 }
 479
 480 /**
 481         @name Managing session data
 482         @brief Maintain data stored via the userData pointer of the application session.
 483
 484         Currently, session-level data is stored in an osrfHash.  Other arrangements are
 485         possible, and some would be more efficient.  The application session calls a
 486         callback function to free userData before terminating.
 487
 488         Currently, the only data we store at the session level is the transaction id.  By this
 489         means we can ensure that any pending transactions are rolled back before the application
 490         session terminates.
 491 */
 492 /*@{*/
 493
 494 /**
 495         @brief Free an item in the application session's userData.
 496         @param key The name of a key for an osrfHash.
 497         @param item An opaque pointer to the item associated with the key.
 498
 499         We store an osrfHash as userData with the application session, and arrange (by
 500         installing userDataFree() as a different callback) for the session to free that
 501         osrfHash before terminating.
 502
 503         This function is a callback for freeing items in the osrfHash.  Currently we store
 504         two things:
 505         - Transaction id of a pending transaction; a character string.  Key: "xact_id".
 506         - Authkey; a character string.  Key: "authkey".
 507         - User object from the authentication server; a jsonObject.  Key: "user_login".
 508
 509         If we ever store anything else in userData, we will need to revisit this function so
 510         that it will free whatever else needs freeing.
 511 */
 512 static void sessionDataFree( char* key, void* item ) {
 513         if( !strcmp( key, "xact_id" ) || !strcmp( key, "authkey" ) || !strncmp( key, "rs_size_", 8) )
 514                 free( item );
 515         else if( !strcmp( key, "user_login" ) )
 516                 jsonObjectFree( (jsonObject*) item );
 517         else if( !strcmp( key, "pcache" ) )
 518                 osrfHashFree( (osrfHash*) item );
 519 }
 520
 521 static void pcacheFree( char* key, void* item ) {
 522         osrfStringArrayFree( (osrfStringArray*) item );
 523 }
 524
 525 /**
 526         @brief Initialize session cache.
 527         @param ctx Pointer to the method context.
 528
 529         Create a cache for the session by making the session's userData member point
 530         to an osrfHash instance.
 531 */
 532 static void initSessionCache( osrfMethodContext* ctx ) {
 533         ctx->session->userData = osrfNewHash();
 534         osrfHashSetCallback( (osrfHash*) ctx->session->userData, &sessionDataFree );
 535         ctx->session->userDataFree = &userDataFree;
 536 }
 537
 538 /**
 539         @brief Save a transaction id.
 540         @param ctx Pointer to the method context.
 541
 542         Save the session_id of the current application session as a transaction id.
 543 */
 544 static void setXactId( osrfMethodContext* ctx ) {
 545         if( ctx && ctx->session ) {
 546                 osrfAppSession* session = ctx->session;
 547
 548                 osrfHash* cache = session->userData;
 549
 550                 // If the session doesn't already have a hash, create one.  Make sure
 551                 // that the application session frees the hash when it terminates.
 552                 if( NULL == cache )
 553                         initSessionCache( ctx );
 554
 555                 // Save the transaction id in the hash, with the key "xact_id"
 556                 osrfHashSet( cache, strdup( session->session_id ), "xact_id" );
 557         }
 558 }
 559
 560 /**
 561         @brief Get the transaction ID for the current transaction, if any.
 562         @param ctx Pointer to the method context.
 563         @return Pointer to the transaction ID.
 564
 565         The return value points to an internal buffer, and will become invalid upon issuing
 566         a commit or rollback.
 567 */
 568 static inline const char* getXactId( osrfMethodContext* ctx ) {
 569         if( ctx && ctx->session && ctx->session->userData )
 570                 return osrfHashGet( (osrfHash*) ctx->session->userData, "xact_id" );
 571         else
 572                 return NULL;
 573 }
 574
 575 /**
 576         @brief Clear the current transaction id.
 577         @param ctx Pointer to the method context.
 578 */
 579 static inline void clearXactId( osrfMethodContext* ctx ) {
 580         if( ctx && ctx->session && ctx->session->userData )
 581                 osrfHashRemove( ctx->session->userData, "xact_id" );
 582 }
 583 /*@}*/
 584
 585 /**
 586         @brief Stash the location for a particular perm in the sessionData cache
 587         @param ctx Pointer to the method context.
 588         @param perm Name of the permission we're looking at
 589         @param array StringArray of perm location ids
 590 */
 591 static void setPermLocationCache( osrfMethodContext* ctx, const char* perm, osrfStringArray* locations ) {
 592         if( ctx && ctx->session ) {
 593                 osrfAppSession* session = ctx->session;
 594
 595                 osrfHash* cache = session->userData;
 596
 597                 // If the session doesn't already have a hash, create one.  Make sure
 598                 // that the application session frees the hash when it terminates.
 599                 if( NULL == cache )
 600                         initSessionCache( ctx );
 601
 602                 osrfHash* pcache = osrfHashGet(cache, "pcache");
 603
 604                 if( NULL == pcache ) {
 605                         pcache = osrfNewHash();
 606                         osrfHashSetCallback( pcache, &pcacheFree );
 607                         osrfHashSet( cache, pcache, "pcache" );
 608                 }
 609
 610                 if( perm && locations )
 611                         osrfHashSet( pcache, locations, strdup(perm) );
 612         }
 613 }
 614
 615 /**
 616         @brief Grab stashed location for a particular perm in the sessionData cache
 617         @param ctx Pointer to the method context.
 618         @param perm Name of the permission we're looking at
 619 */
 620 static osrfStringArray* getPermLocationCache( osrfMethodContext* ctx, const char* perm ) {
 621         if( ctx && ctx->session ) {
 622                 osrfAppSession* session = ctx->session;
 623                 osrfHash* cache = session->userData;
 624                 if( cache ) {
 625                         osrfHash* pcache = osrfHashGet(cache, "pcache");
 626                         if( pcache ) {
 627                                 return osrfHashGet( pcache, perm );
 628                         }
 629                 }
 630         }
 631
 632         return NULL;
 633 }
 634
 635 /**
 636         @brief Save the user's login in the userData for the current application session.
 637         @param ctx Pointer to the method context.
 638         @param user_login Pointer to the user login object to be cached (we cache the original,
 639         not a copy of it).
 640
 641         If @a user_login is NULL, remove the user login if one is already cached.
 642 */
 643 static void setUserLogin( osrfMethodContext* ctx, jsonObject* user_login ) {
 644         if( ctx && ctx->session ) {
 645                 osrfAppSession* session = ctx->session;
 646
 647                 osrfHash* cache = session->userData;
 648
 649                 // If the session doesn't already have a hash, create one.  Make sure
 650                 // that the application session frees the hash when it terminates.
 651                 if( NULL == cache )
 652                         initSessionCache( ctx );
 653
 654                 if( user_login )
 655                         osrfHashSet( cache, user_login, "user_login" );
 656                 else
 657                         osrfHashRemove( cache, "user_login" );
 658         }
 659 }
 660
 661 /**
 662         @brief Get the user login object for the current application session, if any.
 663         @param ctx Pointer to the method context.
 664         @return Pointer to the user login object if found; otherwise NULL.
 665
 666         The user login object was returned from the authentication server, and then cached so
 667         we don't have to call the authentication server again for the same user.
 668 */
 669 static const jsonObject* getUserLogin( osrfMethodContext* ctx ) {
 670         if( ctx && ctx->session && ctx->session->userData )
 671                 return osrfHashGet( (osrfHash*) ctx->session->userData, "user_login" );
 672         else
 673                 return NULL;
 674 }
 675
 676 /**
 677         @brief Save a copy of an authkey in the userData of the current application session.
 678         @param ctx Pointer to the method context.
 679         @param authkey The authkey to be saved.
 680
 681         If @a authkey is NULL, remove the authkey if one is already cached.
 682 */
 683 static void setAuthkey( osrfMethodContext* ctx, const char* authkey ) {
 684         if( ctx && ctx->session && authkey ) {
 685                 osrfAppSession* session = ctx->session;
 686                 osrfHash* cache = session->userData;
 687
 688                 // If the session doesn't already have a hash, create one.  Make sure
 689                 // that the application session frees the hash when it terminates.
 690                 if( NULL == cache )
 691                         initSessionCache( ctx );
 692
 693                 // Save the transaction id in the hash, with the key "xact_id"
 694                 if( authkey && *authkey )
 695                         osrfHashSet( cache, strdup( authkey ), "authkey" );
 696                 else
 697                         osrfHashRemove( cache, "authkey" );
 698         }
 699 }
 700
 701 /**
 702         @brief Reset the login timeout.
 703         @param authkey The authentication key for the current login session.
 704         @param now The current time.
 705         @return Zero if successful, or 1 if not.
 706
 707         Tell the authentication server to reset the timeout so that the login session won't
 708         expire for a while longer.
 709
 710         We could dispense with the @a now parameter by calling time().  But we just called
 711         time() in order to decide whether to reset the timeout, so we might as well reuse
 712         the result instead of calling time() again.
 713 */
 714 static int reset_timeout( const char* authkey, time_t now ) {
 715         jsonObject* auth_object = jsonNewObject( authkey );
 716
 717         // Ask the authentication server to reset the timeout.  It returns an event
 718         // indicating success or failure.
 719         jsonObject* result = oilsUtilsQuickReq( "open-ils.auth",
 720                 "open-ils.auth.session.reset_timeout", auth_object );
 721         jsonObjectFree( auth_object );
 722
 723         if( !result || result->type != JSON_HASH ) {
 724                 osrfLogError( OSRF_LOG_MARK,
 725                          "Unexpected object type receieved from open-ils.auth.session.reset_timeout" );
 726                 jsonObjectFree( result );
 727                 return 1;       // Not the right sort of object returned
 728         }
 729
 730         const jsonObject* ilsevent = jsonObjectGetKeyConst( result, "ilsevent" );
 731         if( !ilsevent || ilsevent->type != JSON_NUMBER ) {
 732                 osrfLogError( OSRF_LOG_MARK, "ilsevent is absent or malformed" );
 733                 jsonObjectFree( result );
 734                 return 1;    // Return code from method not available
 735         }
 736
 737         if( jsonObjectGetNumber( ilsevent ) != 0.0 ) {
 738                 const char* desc = jsonObjectGetString( jsonObjectGetKeyConst( result, "desc" ));
 739                 if( !desc )
 740                         desc = "(No reason available)";    // failsafe; shouldn't happen
 741                 osrfLogInfo( OSRF_LOG_MARK, "Failure to reset timeout: %s", desc );
 742                 jsonObjectFree( result );
 743                 return 1;
 744         }
 745
 746         // Revise our local proxy for the timeout deadline
 747         // by a smallish fraction of the timeout interval
 748         const char* timeout = jsonObjectGetString( jsonObjectGetKeyConst( result, "payload" ));
 749         if( !timeout )
 750                 timeout = "1";   // failsafe; shouldn't happen
 751         time_next_reset = now + atoi( timeout ) / 15;
 752
 753         jsonObjectFree( result );
 754         return 0;     // Successfully reset timeout
 755 }
 756
 757 /**
 758         @brief Get the authkey string for the current application session, if any.
 759         @param ctx Pointer to the method context.
 760         @return Pointer to the cached authkey if found; otherwise NULL.
 761
 762         If present, the authkey string was cached from a previous method call.
 763 */
 764 static const char* getAuthkey( osrfMethodContext* ctx ) {
 765         if( ctx && ctx->session && ctx->session->userData ) {
 766                 const char* authkey = osrfHashGet( (osrfHash*) ctx->session->userData, "authkey" );
 767         // LFW recent changes mean the userData hash gets set up earlier, but
 768         // doesn't necessarily have an authkey yet
 769         if (!authkey)
 770             return NULL;
 771
 772                 // Possibly reset the authentication timeout to keep the login alive.  We do so
 773                 // no more than once per method call, and not at all if it has been only a short
 774                 // time since the last reset.
 775
 776                 // Here we reset explicitly, if at all.  We also implicitly reset the timeout
 777                 // whenever we call the "open-ils.auth.session.retrieve" method.
 778                 if( timeout_needs_resetting ) {
 779                         time_t now = time( NULL );
 780                         if( now >= time_next_reset && reset_timeout( authkey, now ) )
 781                                 authkey = NULL;    // timeout has apparently expired already
 782                 }
 783
 784                 timeout_needs_resetting = 0;
 785                 return authkey;
 786         }
 787         else
 788                 return NULL;
 789 }
 790
 791 /**
 792         @brief Implement the transaction.begin method.
 793         @param ctx Pointer to the method context.
 794         @return Zero if successful, or -1 upon error.
 795
 796         Start a transaction.  Save a transaction ID for future reference.
 797
 798         Method parameters:
 799         - authkey (PCRUD only)
 800
 801         Return to client: Transaction ID
 802 */
 803 int beginTransaction( osrfMethodContext* ctx ) {
 804         if(osrfMethodVerifyContext( ctx )) {
 805                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 806                 return -1;
 807         }
 808
 809         if( enforce_pcrud ) {
 810                 timeout_needs_resetting = 1;
 811                 const jsonObject* user = verifyUserPCRUD( ctx );
 812                 if( !user )
 813                         return -1;
 814         }
 815
 816         dbi_result result = dbi_conn_query( writehandle, "START TRANSACTION;" );
 817         if( !result ) {
 818                 const char* msg;
 819                 int errnum = dbi_conn_error( writehandle, &msg );
 820                 osrfLogError( OSRF_LOG_MARK, "%s: Error starting transaction: %d %s",
 821                         modulename, errnum, msg ? msg : "(No description available)" );
 822                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 823                         "osrfMethodException", ctx->request, "Error starting transaction" );
 824                 if( !oilsIsDBConnected( writehandle ))
 825                         osrfAppSessionPanic( ctx->session );
 826                 return -1;
 827         } else {
 828                 dbi_result_free( result );
 829                 setXactId( ctx );
 830                 jsonObject* ret = jsonNewObject( getXactId( ctx ) );
 831                 osrfAppRespondComplete( ctx, ret );
 832                 jsonObjectFree( ret );
 833                 return 0;
 834         }
 835 }
 836
 837 /**
 838         @brief Implement the savepoint.set method.
 839         @param ctx Pointer to the method context.
 840         @return Zero if successful, or -1 if not.
 841
 842         Issue a SAVEPOINT to the database server.
 843
 844         Method parameters:
 845         - authkey (PCRUD only)
 846         - savepoint name
 847
 848         Return to client: Savepoint name
 849 */
 850 int setSavepoint( osrfMethodContext* ctx ) {
 851         if(osrfMethodVerifyContext( ctx )) {
 852                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 853                 return -1;
 854         }
 855
 856         int spNamePos = 0;
 857         if( enforce_pcrud ) {
 858                 spNamePos = 1;
 859                 timeout_needs_resetting = 1;
 860                 const jsonObject* user = verifyUserPCRUD( ctx );
 861                 if( !user )
 862                         return -1;
 863         }
 864
 865         // Verify that a transaction is pending
 866         const char* trans_id = getXactId( ctx );
 867         if( NULL == trans_id ) {
 868                 osrfAppSessionStatus(
 869                         ctx->session,
 870                         OSRF_STATUS_INTERNALSERVERERROR,
 871                         "osrfMethodException",
 872                         ctx->request,
 873                         "No active transaction -- required for savepoints"
 874                 );
 875                 return -1;
 876         }
 877
 878         // Get the savepoint name from the method params
 879         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 880
 881         dbi_result result = dbi_conn_queryf( writehandle, "SAVEPOINT \"%s\";", spName );
 882         if( !result ) {
 883                 const char* msg;
 884                 int errnum = dbi_conn_error( writehandle, &msg );
 885                 osrfLogError(
 886                         OSRF_LOG_MARK,
 887                         "%s: Error creating savepoint %s in transaction %s: %d %s",
 888                         modulename,
 889                         spName,
 890                         trans_id,
 891                         errnum,
 892                         msg ? msg : "(No description available)"
 893                 );
 894                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 895                         "osrfMethodException", ctx->request, "Error creating savepoint" );
 896                 if( !oilsIsDBConnected( writehandle ))
 897                         osrfAppSessionPanic( ctx->session );
 898                 return -1;
 899         } else {
 900                 dbi_result_free( result );
 901                 jsonObject* ret = jsonNewObject( spName );
 902                 osrfAppRespondComplete( ctx, ret );
 903                 jsonObjectFree( ret  );
 904                 return 0;
 905         }
 906 }
 907
 908 /**
 909         @brief Implement the savepoint.release method.
 910         @param ctx Pointer to the method context.
 911         @return Zero if successful, or -1 if not.
 912
 913         Issue a RELEASE SAVEPOINT to the database server.
 914
 915         Method parameters:
 916         - authkey (PCRUD only)
 917         - savepoint name
 918
 919         Return to client: Savepoint name
 920 */
 921 int releaseSavepoint( osrfMethodContext* ctx ) {
 922         if(osrfMethodVerifyContext( ctx )) {
 923                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 924                 return -1;
 925         }
 926
 927         int spNamePos = 0;
 928         if( enforce_pcrud ) {
 929                 spNamePos = 1;
 930                 timeout_needs_resetting = 1;
 931                 const jsonObject* user = verifyUserPCRUD( ctx );
 932                 if(  !user )
 933                         return -1;
 934         }
 935
 936         // Verify that a transaction is pending
 937         const char* trans_id = getXactId( ctx );
 938         if( NULL == trans_id ) {
 939                 osrfAppSessionStatus(
 940                         ctx->session,
 941                         OSRF_STATUS_INTERNALSERVERERROR,
 942                         "osrfMethodException",
 943                         ctx->request,
 944                         "No active transaction -- required for savepoints"
 945                 );
 946                 return -1;
 947         }
 948
 949         // Get the savepoint name from the method params
 950         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 951
 952         dbi_result result = dbi_conn_queryf( writehandle, "RELEASE SAVEPOINT \"%s\";", spName );
 953         if( !result ) {
 954                 const char* msg;
 955                 int errnum = dbi_conn_error( writehandle, &msg );
 956                 osrfLogError(
 957                         OSRF_LOG_MARK,
 958                         "%s: Error releasing savepoint %s in transaction %s: %d %s",
 959                         modulename,
 960                         spName,
 961                         trans_id,
 962                         errnum,
 963                         msg ? msg : "(No description available)"
 964                 );
 965                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 966                         "osrfMethodException", ctx->request, "Error releasing savepoint" );
 967                 if( !oilsIsDBConnected( writehandle ))
 968                         osrfAppSessionPanic( ctx->session );
 969                 return -1;
 970         } else {
 971                 dbi_result_free( result );
 972                 jsonObject* ret = jsonNewObject( spName );
 973                 osrfAppRespondComplete( ctx, ret );
 974                 jsonObjectFree( ret );
 975                 return 0;
 976         }
 977 }
 978
 979 /**
 980         @brief Implement the savepoint.rollback method.
 981         @param ctx Pointer to the method context.
 982         @return Zero if successful, or -1 if not.
 983
 984         Issue a ROLLBACK TO SAVEPOINT to the database server.
 985
 986         Method parameters:
 987         - authkey (PCRUD only)
 988         - savepoint name
 989
 990         Return to client: Savepoint name
 991 */
 992 int rollbackSavepoint( osrfMethodContext* ctx ) {
 993         if(osrfMethodVerifyContext( ctx )) {
 994                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 995                 return -1;
 996         }
 997
 998         int spNamePos = 0;
 999         if( enforce_pcrud ) {
1000                 spNamePos = 1;
1001                 timeout_needs_resetting = 1;
1002                 const jsonObject* user = verifyUserPCRUD( ctx );
1003                 if( !user )
1004                         return -1;
1005         }
1006
1007         // Verify that a transaction is pending
1008         const char* trans_id = getXactId( ctx );
1009         if( NULL == trans_id ) {
1010                 osrfAppSessionStatus(
1011                         ctx->session,
1012                         OSRF_STATUS_INTERNALSERVERERROR,
1013                         "osrfMethodException",
1014                         ctx->request,
1015                         "No active transaction -- required for savepoints"
1016                 );
1017                 return -1;
1018         }
1019
1020         // Get the savepoint name from the method params
1021         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
1022
1023         dbi_result result = dbi_conn_queryf( writehandle, "ROLLBACK TO SAVEPOINT \"%s\";", spName );
1024         if( !result ) {
1025                 const char* msg;
1026                 int errnum = dbi_conn_error( writehandle, &msg );
1027                 osrfLogError(
1028                         OSRF_LOG_MARK,
1029                         "%s: Error rolling back savepoint %s in transaction %s: %d %s",
1030                         modulename,
1031                         spName,
1032                         trans_id,
1033                         errnum,
1034                         msg ? msg : "(No description available)"
1035                 );
1036                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1037                         "osrfMethodException", ctx->request, "Error rolling back savepoint" );
1038                 if( !oilsIsDBConnected( writehandle ))
1039                         osrfAppSessionPanic( ctx->session );
1040                 return -1;
1041         } else {
1042                 dbi_result_free( result );
1043                 jsonObject* ret = jsonNewObject( spName );
1044                 osrfAppRespondComplete( ctx, ret );
1045                 jsonObjectFree( ret );
1046                 return 0;
1047         }
1048 }
1049
1050 /**
1051         @brief Implement the transaction.commit method.
1052         @param ctx Pointer to the method context.
1053         @return Zero if successful, or -1 if not.
1054
1055         Issue a COMMIT to the database server.
1056
1057         Method parameters:
1058         - authkey (PCRUD only)
1059
1060         Return to client: Transaction ID.
1061 */
1062 int commitTransaction( osrfMethodContext* ctx ) {
1063         if(osrfMethodVerifyContext( ctx )) {
1064                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1065                 return -1;
1066         }
1067
1068         if( enforce_pcrud ) {
1069                 timeout_needs_resetting = 1;
1070                 const jsonObject* user = verifyUserPCRUD( ctx );
1071                 if( !user )
1072                         return -1;
1073         }
1074
1075         // Verify that a transaction is pending
1076         const char* trans_id = getXactId( ctx );
1077         if( NULL == trans_id ) {
1078                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1079                                 "osrfMethodException", ctx->request, "No active transaction to commit" );
1080                 return -1;
1081         }
1082
1083         dbi_result result = dbi_conn_query( writehandle, "COMMIT;" );
1084         if( !result ) {
1085                 const char* msg;
1086                 int errnum = dbi_conn_error( writehandle, &msg );
1087                 osrfLogError( OSRF_LOG_MARK, "%s: Error committing transaction: %d %s",
1088                         modulename, errnum, msg ? msg : "(No description available)" );
1089                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1090                         "osrfMethodException", ctx->request, "Error committing transaction" );
1091                 if( !oilsIsDBConnected( writehandle ))
1092                         osrfAppSessionPanic( ctx->session );
1093                 return -1;
1094         } else {
1095                 dbi_result_free( result );
1096                 jsonObject* ret = jsonNewObject( trans_id );
1097                 osrfAppRespondComplete( ctx, ret );
1098                 jsonObjectFree( ret );
1099                 clearXactId( ctx );
1100                 return 0;
1101         }
1102 }
1103
1104 /**
1105         @brief Implement the transaction.rollback method.
1106         @param ctx Pointer to the method context.
1107         @return Zero if successful, or -1 if not.
1108
1109         Issue a ROLLBACK to the database server.
1110
1111         Method parameters:
1112         - authkey (PCRUD only)
1113
1114         Return to client: Transaction ID
1115 */
1116 int rollbackTransaction( osrfMethodContext* ctx ) {
1117         if( osrfMethodVerifyContext( ctx )) {
1118                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
1119                 return -1;
1120         }
1121
1122         if( enforce_pcrud ) {
1123                 timeout_needs_resetting = 1;
1124                 const jsonObject* user = verifyUserPCRUD( ctx );
1125                 if( !user )
1126                         return -1;
1127         }
1128
1129         // Verify that a transaction is pending
1130         const char* trans_id = getXactId( ctx );
1131         if( NULL == trans_id ) {
1132                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1133                                 "osrfMethodException", ctx->request, "No active transaction to roll back" );
1134                 return -1;
1135         }
1136
1137         dbi_result result = dbi_conn_query( writehandle, "ROLLBACK;" );
1138         if( !result ) {
1139                 const char* msg;
1140                 int errnum = dbi_conn_error( writehandle, &msg );
1141                 osrfLogError( OSRF_LOG_MARK, "%s: Error rolling back transaction: %d %s",
1142                         modulename, errnum, msg ? msg : "(No description available)" );
1143                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1144                         "osrfMethodException", ctx->request, "Error rolling back transaction" );
1145                 if( !oilsIsDBConnected( writehandle ))
1146                         osrfAppSessionPanic( ctx->session );
1147                 return -1;
1148         } else {
1149                 dbi_result_free( result );
1150                 jsonObject* ret = jsonNewObject( trans_id );
1151                 osrfAppRespondComplete( ctx, ret );
1152                 jsonObjectFree( ret );
1153                 clearXactId( ctx );
1154                 return 0;
1155         }
1156 }
1157
1158 /**
1159         @brief Implement the "search" method.
1160         @param ctx Pointer to the method context.
1161         @return Zero if successful, or -1 if not.
1162
1163         Method parameters:
1164         - authkey (PCRUD only)
1165         - WHERE clause, as jsonObject
1166         - Other SQL clause(s), as a JSON_HASH: joins, SELECT list, LIMIT, etc.
1167
1168         Return to client: rows of the specified class that satisfy a specified WHERE clause.
1169         Optionally flesh linked fields.
1170 */
1171 int doSearch( osrfMethodContext* ctx ) {
1172         if( osrfMethodVerifyContext( ctx )) {
1173                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1174                 return -1;
1175         }
1176
1177         if( enforce_pcrud )
1178                 timeout_needs_resetting = 1;
1179
1180         jsonObject* where_clause;
1181         jsonObject* rest_of_query;
1182
1183         if( enforce_pcrud ) {
1184                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1185                 rest_of_query = jsonObjectGetIndex( ctx->params, 2 );
1186         } else {
1187                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1188                 rest_of_query = jsonObjectGetIndex( ctx->params, 1 );
1189         }
1190
1191         if( !where_clause ) {
1192                 osrfLogError( OSRF_LOG_MARK, "No WHERE clause parameter supplied" );
1193                 return -1;
1194         }
1195
1196         // Get the class metadata
1197         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1198         osrfHash* class_meta = osrfHashGet( method_meta, "class" );
1199
1200         // Do the query
1201         int err = 0;
1202         jsonObject* obj = doFieldmapperSearch( ctx, class_meta, where_clause, rest_of_query, &err );
1203         if( err ) {
1204                 osrfAppRespondComplete( ctx, NULL );
1205                 return -1;
1206         }
1207
1208         // doFieldmapperSearch() now takes care of our responding for us
1209 //      // Return each row to the client
1210 //      jsonObject* cur = 0;
1211 //      unsigned long res_idx = 0;
1212 //
1213 //      while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1214 //              // We used to discard based on perms here, but now that's
1215 //              // inside doFieldmapperSearch()
1216 //              osrfAppRespond( ctx, cur );
1217 //      }
1218
1219         jsonObjectFree( obj );
1220
1221         osrfAppRespondComplete( ctx, NULL );
1222         return 0;
1223 }
1224
1225 /**
1226         @brief Implement the "id_list" method.
1227         @param ctx Pointer to the method context.
1228         @param err Pointer through which to return an error code.
1229         @return Zero if successful, or -1 if not.
1230
1231         Method parameters:
1232         - authkey (PCRUD only)
1233         - WHERE clause, as jsonObject
1234         - Other SQL clause(s), as a JSON_HASH: joins, LIMIT, etc.
1235
1236         Return to client: The primary key values for all rows of the relevant class that
1237         satisfy a specified WHERE clause.
1238
1239         This method relies on the assumption that every class has a primary key consisting of
1240         a single column.
1241 */
1242 int doIdList( osrfMethodContext* ctx ) {
1243         if( osrfMethodVerifyContext( ctx )) {
1244                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1245                 return -1;
1246         }
1247
1248         if( enforce_pcrud )
1249                 timeout_needs_resetting = 1;
1250
1251         jsonObject* where_clause;
1252         jsonObject* rest_of_query;
1253
1254         // We use the where clause without change.  But we need to massage the rest of the
1255         // query, so we work with a copy of it instead of modifying the original.
1256
1257         if( enforce_pcrud ) {
1258                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1259                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 2 ) );
1260         } else {
1261                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1262                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 1 ) );
1263         }
1264
1265         if( !where_clause ) {
1266                 osrfLogError( OSRF_LOG_MARK, "No WHERE clause parameter supplied" );
1267                 return -1;
1268         }
1269
1270         // Eliminate certain SQL clauses, if present.
1271         if( rest_of_query ) {
1272                 jsonObjectRemoveKey( rest_of_query, "select" );
1273                 jsonObjectRemoveKey( rest_of_query, "no_i18n" );
1274                 jsonObjectRemoveKey( rest_of_query, "flesh" );
1275                 jsonObjectRemoveKey( rest_of_query, "flesh_fields" );
1276         } else {
1277                 rest_of_query = jsonNewObjectType( JSON_HASH );
1278         }
1279
1280         jsonObjectSetKey( rest_of_query, "no_i18n", jsonNewBoolObject( 1 ) );
1281
1282         // Get the class metadata
1283         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1284         osrfHash* class_meta = osrfHashGet( method_meta, "class" );
1285
1286         // Build a SELECT list containing just the primary key,
1287         // i.e. like { "classname":["keyname"] }
1288         jsonObject* col_list_obj = jsonNewObjectType( JSON_ARRAY );
1289
1290         // Load array with name of primary key
1291         jsonObjectPush( col_list_obj, jsonNewObject( osrfHashGet( class_meta, "primarykey" ) ) );
1292         jsonObject* select_clause = jsonNewObjectType( JSON_HASH );
1293         jsonObjectSetKey( select_clause, osrfHashGet( class_meta, "classname" ), col_list_obj );
1294
1295         jsonObjectSetKey( rest_of_query, "select", select_clause );
1296
1297         // Do the query
1298         int err = 0;
1299         jsonObject* obj =
1300                 doFieldmapperSearch( ctx, class_meta, where_clause, rest_of_query, &err );
1301
1302         jsonObjectFree( rest_of_query );
1303         if( err ) {
1304                 osrfAppRespondComplete( ctx, NULL );
1305                 return -1;
1306         }
1307
1308         // Return each primary key value to the client
1309         jsonObject* cur;
1310         unsigned long res_idx = 0;
1311         while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1312                 // We used to discard based on perms here, but now that's
1313                 // inside doFieldmapperSearch()
1314                 osrfAppRespond( ctx,
1315                         oilsFMGetObject( cur, osrfHashGet( class_meta, "primarykey" ) ) );
1316         }
1317
1318         jsonObjectFree( obj );
1319         osrfAppRespondComplete( ctx, NULL );
1320         return 0;
1321 }
1322
1323 /**
1324         @brief Verify that we have a valid class reference.
1325         @param ctx Pointer to the method context.
1326         @param param Pointer to the method parameters.
1327         @return 1 if the class reference is valid, or zero if it isn't.
1328
1329         The class of the method params must match the class to which the method id devoted.
1330         For PCRUD there are additional restrictions.
1331 */
1332 static int verifyObjectClass ( osrfMethodContext* ctx, const jsonObject* param ) {
1333
1334         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1335         osrfHash* class = osrfHashGet( method_meta, "class" );
1336
1337         // Compare the method's class to the parameters' class
1338         if( !param->classname || (strcmp( osrfHashGet(class, "classname"), param->classname ))) {
1339
1340                 // Oops -- they don't match.  Complain.
1341                 growing_buffer* msg = buffer_init( 128 );
1342                 buffer_fadd(
1343                         msg,
1344                         "%s: %s method for type %s was passed a %s",
1345                         modulename,
1346                         osrfHashGet( method_meta, "methodtype" ),
1347                         osrfHashGet( class, "classname" ),
1348                         param->classname ? param->classname : "(null)"
1349                 );
1350
1351                 char* m = buffer_release( msg );
1352                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
1353                                 ctx->request, m );
1354                 free( m );
1355
1356                 return 0;
1357         }
1358
1359         if( enforce_pcrud )
1360                 return verifyObjectPCRUD( ctx, class, param, 1 );
1361         else
1362                 return 1;
1363 }
1364
1365 /**
1366         @brief (PCRUD only) Verify that the user is properly logged in.
1367         @param ctx Pointer to the method context.
1368         @return If the user is logged in, a pointer to the user object from the authentication
1369         server; otherwise NULL.
1370 */
1371 static const jsonObject* verifyUserPCRUD( osrfMethodContext* ctx ) {
1372
1373         // Get the authkey (the first method parameter)
1374         const char* auth = jsonObjectGetString( jsonObjectGetIndex( ctx->params, 0 ) );
1375
1376         // See if we have the same authkey, and a user object,
1377         // locally cached from a previous call
1378         const char* cached_authkey = getAuthkey( ctx );
1379         if( cached_authkey && !strcmp( cached_authkey, auth ) ) {
1380                 const jsonObject* cached_user = getUserLogin( ctx );
1381                 if( cached_user )
1382                         return cached_user;
1383         }
1384
1385         // We have no matching authentication data in the cache.  Authenticate from scratch.
1386         jsonObject* auth_object = jsonNewObject( auth );
1387
1388         // Fetch the user object from the authentication server
1389         jsonObject* user = oilsUtilsQuickReq( "open-ils.auth", "open-ils.auth.session.retrieve",
1390                         auth_object );
1391         jsonObjectFree( auth_object );
1392
1393         if( !user->classname || strcmp(user->classname, "au" )) {
1394
1395                 growing_buffer* msg = buffer_init( 128 );
1396                 buffer_fadd(
1397                         msg,
1398                         "%s: permacrud received a bad auth token: %s",
1399                         modulename,
1400                         auth
1401                 );
1402
1403                 char* m = buffer_release( msg );
1404                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_UNAUTHORIZED, "osrfMethodException",
1405                                 ctx->request, m );
1406
1407                 free( m );
1408                 jsonObjectFree( user );
1409                 user = NULL;
1410         }
1411
1412         setUserLogin( ctx, user );
1413         setAuthkey( ctx, auth );
1414
1415         // Allow ourselves up to a second before we have to reset the login timeout.
1416         // It would be nice to use some fraction of the timeout interval enforced by the
1417         // authentication server, but that value is not readily available at this point.
1418         // Instead, we use a conservative default interval.
1419         time_next_reset = time( NULL ) + 1;
1420
1421         return user;
1422 }
1423
1424 /**
1425         @brief For PCRUD: Determine whether the current user may access the current row.
1426         @param ctx Pointer to the method context.
1427         @param class Same as ctx->method->userData's item for key "class" except when called in recursive doFieldmapperSearch
1428         @param obj Pointer to the row being potentially accessed.
1429         @return 1 if access is permitted, or 0 if it isn't.
1430
1431         The @a obj parameter points to a JSON_HASH of column values, keyed on column name.
1432 */
1433 static int verifyObjectPCRUD ( osrfMethodContext* ctx, osrfHash *class, const jsonObject* obj, int rs_size ) {
1434
1435         dbhandle = writehandle;
1436
1437         // Figure out what class and method are involved
1438         osrfHash* method_metadata = (osrfHash*) ctx->method->userData;
1439         const char* method_type = osrfHashGet( method_metadata, "methodtype" );
1440
1441         if (!rs_size) {
1442                 int *rs_size_from_hash = osrfHashGetFmt( (osrfHash *) ctx->session->userData, "rs_size_req_%d", ctx->request );
1443                 if (rs_size_from_hash) {
1444                         rs_size = *rs_size_from_hash;
1445                         osrfLogDebug(OSRF_LOG_MARK, "used rs_size from request-scoped hash: %d", rs_size);
1446                 }
1447         }
1448
1449         // Set fetch to 1 in all cases except for inserts, meaning that for local or foreign
1450         // contexts we will do another lookup of the current row, even if we already have a
1451         // previously fetched row image, because the row image in hand may not include the
1452         // foreign key(s) that we need.
1453
1454         // This is a quick fix with a bludgeon.  There are ways to avoid the extra lookup,
1455         // but they aren't implemented yet.
1456
1457         int fetch = 0;
1458         if( *method_type == 's' || *method_type == 'i' ) {
1459                 method_type = "retrieve"; // search and id_list are equivalent to retrieve for this
1460                 fetch = 1;
1461         } else if( *method_type == 'u' || *method_type == 'd' ) {
1462                 fetch = 1; // MUST go to the db for the object for update and delete
1463         }
1464
1465         // Get the appropriate permacrud entry from the IDL, depending on method type
1466         osrfHash* pcrud = osrfHashGet( osrfHashGet( class, "permacrud" ), method_type );
1467         if( !pcrud ) {
1468                 // No permacrud for this method type on this class
1469
1470                 growing_buffer* msg = buffer_init( 128 );
1471                 buffer_fadd(
1472                         msg,
1473                         "%s: %s on class %s has no permacrud IDL entry",
1474                         modulename,
1475                         osrfHashGet( method_metadata, "methodtype" ),
1476                         osrfHashGet( class, "classname" )
1477                 );
1478
1479                 char* m = buffer_release( msg );
1480                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_FORBIDDEN,
1481                                 "osrfMethodException", ctx->request, m );
1482
1483                 free( m );
1484
1485                 return 0;
1486         }
1487
1488         // Get the user id, and make sure the user is logged in
1489         const jsonObject* user = verifyUserPCRUD( ctx );
1490         if( !user )
1491                 return 0;    // Not logged in?  No access.
1492
1493         int userid = atoi( oilsFMGetStringConst( user, "id" ) );
1494
1495         // Get a list of permissions from the permacrud entry.
1496         osrfStringArray* permission = osrfHashGet( pcrud, "permission" );
1497         if( permission->size == 0 ) {
1498                 osrfLogDebug(
1499                         OSRF_LOG_MARK,
1500                         "No permissions required for this action (class %s), passing through",
1501                         osrfHashGet(class, "classname")
1502                 );
1503                 return 1;
1504         }
1505
1506         // Build a list of org units that own the row.  This is fairly convoluted because there
1507         // are several different ways that an org unit may own the row, as defined by the
1508         // permacrud entry.
1509
1510         // Local context means that the row includes a foreign key pointing to actor.org_unit,
1511         // identifying an owning org_unit..
1512         osrfStringArray* local_context = osrfHashGet( pcrud, "local_context" );
1513
1514         // Foreign context adds a layer of indirection.  The row points to some other row that
1515         // an org unit may own.  The "jump" attribute, if present, adds another layer of
1516         // indirection.
1517         osrfHash* foreign_context = osrfHashGet( pcrud, "foreign_context" );
1518
1519         // The following string array stores the list of org units.  (We don't have a thingie
1520         // for storing lists of integers, so we fake it with a list of strings.)
1521         osrfStringArray* context_org_array = osrfNewStringArray( 1 );
1522
1523         int err = 0;
1524         const char* pkey_value = NULL;
1525         if( str_is_true( osrfHashGet(pcrud, "global_required") ) ) {
1526                 // If the global_required attribute is present and true, then the only owning
1527                 // org unit is the root org unit, i.e. the one with no parent.
1528                 osrfLogDebug( OSRF_LOG_MARK,
1529                                 "global-level permissions required, fetching top of the org tree" );
1530
1531                 // check for perm at top of org tree
1532                 const char* org_tree_root_id = org_tree_root( ctx );
1533                 if( org_tree_root_id ) {
1534                         osrfStringArrayAdd( context_org_array, org_tree_root_id );
1535                         osrfLogDebug( OSRF_LOG_MARK, "top of the org tree is %s", org_tree_root_id );
1536                 } else  {
1537                         osrfStringArrayFree( context_org_array );
1538                         return 0;
1539                 }
1540
1541         } else {
1542                 // If the global_required attribute is absent or false, then we look for
1543                 // local and/or foreign context.  In order to find the relevant foreign
1544                 // keys, we must either read the relevant row from the database, or look at
1545                 // the image of the row that we already have in memory.
1546
1547                 // Even if we have an image of the row in memory, that image may not include the
1548                 // foreign key column(s) that we need.  So whenever possible, we do a fresh read
1549                 // of the row to make sure that we have what we need.
1550
1551             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions not required, "
1552                                 "fetching context org ids" );
1553             const char* pkey = osrfHashGet( class, "primarykey" );
1554                 jsonObject *param = NULL;
1555
1556                 if( !pkey ) {
1557                         // There is no primary key, so we can't do a fresh lookup.  Use the row
1558                         // image that we already have.  If it doesn't have everything we need, too bad.
1559                         fetch = 0;
1560                         param = jsonObjectClone( obj );
1561                         osrfLogDebug( OSRF_LOG_MARK, "No primary key; using clone of object" );
1562                 } else if( obj->classname ) {
1563                         pkey_value = oilsFMGetStringConst( obj, pkey );
1564                         if( !fetch )
1565                                 param = jsonObjectClone( obj );
1566                         osrfLogDebug( OSRF_LOG_MARK, "Object supplied, using primary key value of %s",
1567                                 pkey_value );
1568                 } else {
1569                         pkey_value = jsonObjectGetString( obj );
1570                         fetch = 1;
1571                         osrfLogDebug( OSRF_LOG_MARK, "Object not supplied, using primary key value "
1572                                 "of %s and retrieving from the database", pkey_value );
1573                 }
1574
1575                 if( fetch ) {
1576                         // Fetch the row so that we can look at the foreign key(s)
1577                         osrfHashSet((osrfHash*) ctx->session->userData, "1", "inside_verify");
1578                         jsonObject* _tmp_params = single_hash( pkey, pkey_value );
1579                         jsonObject* _list = doFieldmapperSearch( ctx, class, _tmp_params, NULL, &err );
1580                         jsonObjectFree( _tmp_params );
1581                         osrfHashSet((osrfHash*) ctx->session->userData, "0", "inside_verify");
1582
1583                         param = jsonObjectExtractIndex( _list, 0 );
1584                         jsonObjectFree( _list );
1585                 }
1586
1587                 if( !param ) {
1588                         // The row doesn't exist.  Complain, and deny access.
1589                         osrfLogDebug( OSRF_LOG_MARK,
1590                                         "Object not found in the database with primary key %s of %s",
1591                                         pkey, pkey_value );
1592
1593                         growing_buffer* msg = buffer_init( 128 );
1594                         buffer_fadd(
1595                                 msg,
1596                                 "%s: no object found with primary key %s of %s",
1597                                 modulename,
1598                                 pkey,
1599                                 pkey_value
1600                         );
1601
1602                         char* m = buffer_release( msg );
1603                         osrfAppSessionStatus(
1604                                 ctx->session,
1605                                 OSRF_STATUS_INTERNALSERVERERROR,
1606                                 "osrfMethodException",
1607                                 ctx->request,
1608                                 m
1609                         );
1610
1611                         free( m );
1612                         return 0;
1613                 }
1614
1615                 if( local_context && local_context->size > 0 ) {
1616                         // The IDL provides a list of column names for the foreign keys denoting
1617                         // local context, i.e. columns identifying owing org units directly.  Look up
1618                         // the value of each one, and if it isn't null, add it to the list of org units.
1619                         osrfLogDebug( OSRF_LOG_MARK, "%d class-local context field(s) specified",
1620                                 local_context->size );
1621                         int i = 0;
1622                         const char* lcontext = NULL;
1623                         while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
1624                                 const char* fkey_value = oilsFMGetStringConst( param, lcontext );
1625                                 if( fkey_value ) {    // if not null
1626                                         osrfStringArrayAdd( context_org_array, fkey_value );
1627                                         osrfLogDebug(
1628                                                 OSRF_LOG_MARK,
1629                                                 "adding class-local field %s (value: %s) to the context org list",
1630                                                 lcontext,
1631                                                 osrfStringArrayGetString( context_org_array, context_org_array->size - 1 )
1632                                         );
1633                                 }
1634                         }
1635                 }
1636
1637                 if( foreign_context ) {
1638                         unsigned long class_count = osrfHashGetCount( foreign_context );
1639                         osrfLogDebug( OSRF_LOG_MARK, "%d foreign context classes(s) specified", class_count );
1640
1641                         if( class_count > 0 ) {
1642
1643                                 // The IDL provides a list of foreign key columns pointing to rows that
1644                                 // an org unit may own.  Follow each link, identify the owning org unit,
1645                                 // and add it to the list.
1646                                 osrfHash* fcontext = NULL;
1647                                 osrfHashIterator* class_itr = osrfNewHashIterator( foreign_context );
1648                                 while( (fcontext = osrfHashIteratorNext( class_itr )) ) {
1649                                         // For each class to which a foreign key points:
1650                                         const char* class_name = osrfHashIteratorKey( class_itr );
1651                                         osrfHash* fcontext = osrfHashGet( foreign_context, class_name );
1652
1653                                         osrfLogDebug(
1654                                                 OSRF_LOG_MARK,
1655                                                 "%d foreign context fields(s) specified for class %s",
1656                                                 ((osrfStringArray*)osrfHashGet(fcontext,"context"))->size,
1657                                                 class_name
1658                                         );
1659
1660                                         // Get the name of the key field in the foreign table
1661                                         const char* foreign_pkey = osrfHashGet( fcontext, "field" );
1662
1663                                         // Get the value of the foreign key pointing to the foreign table
1664                                         char* foreign_pkey_value =
1665                                                         oilsFMGetString( param, osrfHashGet( fcontext, "fkey" ));
1666                                         if( !foreign_pkey_value )
1667                                                 continue;    // Foreign key value is null; skip it
1668
1669                                         // Look up the row to which the foreign key points
1670                                         jsonObject* _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1671
1672                                         osrfHashSet((osrfHash*) ctx->session->userData, "1", "inside_verify");
1673                                         jsonObject* _list = doFieldmapperSearch(
1674                                                 ctx, osrfHashGet( oilsIDL(), class_name ), _tmp_params, NULL, &err );
1675                                         osrfHashSet((osrfHash*) ctx->session->userData, "0", "inside_verify");
1676
1677                                         jsonObject* _fparam = NULL;
1678                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1679                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1680
1681                                         jsonObjectFree( _tmp_params );
1682                                         jsonObjectFree( _list );
1683
1684                                         // At this point _fparam either points to the row identified by the
1685                                         // foreign key, or it's NULL (no such row found).
1686
1687                                         osrfStringArray* jump_list = osrfHashGet( fcontext, "jump" );
1688
1689                                         const char* bad_class = NULL;  // For noting failed lookups
1690                                         if( ! _fparam )
1691                                                 bad_class = class_name;    // Referenced row not found
1692                                         else if( jump_list ) {
1693                                                 // Follow a chain of rows, linked by foreign keys, to find an owner
1694                                                 const char* flink = NULL;
1695                                                 int k = 0;
1696                                                 while ( (flink = osrfStringArrayGetString(jump_list, k++)) && _fparam ) {
1697                                                         // For each entry in the jump list.  Each entry (i.e. flink) is
1698                                                         // the name of a foreign key column in the current row.
1699
1700                                                         // From the IDL, get the linkage information for the next jump
1701                                                         osrfHash* foreign_link_hash =
1702                                                                         oilsIDLFindPath( "/%s/links/%s", _fparam->classname, flink );
1703
1704                                                         // Get the class metadata for the class
1705                                                         // to which the foreign key points
1706                                                         osrfHash* foreign_class_meta = osrfHashGet( oilsIDL(),
1707                                                                         osrfHashGet( foreign_link_hash, "class" ));
1708
1709                                                         // Get the name of the referenced key of that class
1710                                                         foreign_pkey = osrfHashGet( foreign_link_hash, "key" );
1711
1712                                                         // Get the value of the foreign key pointing to that class
1713                                                         free( foreign_pkey_value );
1714                                                         foreign_pkey_value = oilsFMGetString( _fparam, flink );
1715                                                         if( !foreign_pkey_value )
1716                                                                 break;    // Foreign key is null; quit looking
1717
1718                                                         // Build a WHERE clause for the lookup
1719                                                         _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1720
1721                                                         // Do the lookup
1722                                                         _list = doFieldmapperSearch( ctx, foreign_class_meta,
1723                                                                         _tmp_params, NULL, &err );
1724
1725                                                         // Get the resulting row
1726                                                         jsonObjectFree( _fparam );
1727                                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1728                                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1729                                                         else {
1730                                                                 // Referenced row not found
1731                                                                 _fparam = NULL;
1732                                                                 bad_class = osrfHashGet( foreign_link_hash, "class" );
1733                                                         }
1734
1735                                                         jsonObjectFree( _tmp_params );
1736                                                         jsonObjectFree( _list );
1737                                                 }
1738                                         }
1739
1740                                         if( bad_class ) {
1741
1742                                                 // We had a foreign key pointing to such-and-such a row, but then
1743                                                 // we couldn't fetch that row.  The data in the database are in an
1744                                                 // inconsistent state; the database itself may even be corrupted.
1745                                                 growing_buffer* msg = buffer_init( 128 );
1746                                                 buffer_fadd(
1747                                                         msg,
1748                                                         "%s: no object of class %s found with primary key %s of %s",
1749                                                         modulename,
1750                                                         bad_class,
1751                                                         foreign_pkey,
1752                                                         foreign_pkey_value ? foreign_pkey_value : "(null)"
1753                                                 );
1754
1755                                                 char* m = buffer_release( msg );
1756                                                 osrfAppSessionStatus(
1757                                                         ctx->session,
1758                                                         OSRF_STATUS_INTERNALSERVERERROR,
1759                                                         "osrfMethodException",
1760                                                         ctx->request,
1761                                                         m
1762                                                 );
1763
1764                                                 free( m );
1765                                                 osrfHashIteratorFree( class_itr );
1766                                                 free( foreign_pkey_value );
1767                                                 jsonObjectFree( param );
1768
1769                                                 return 0;
1770                                         }
1771
1772                                         free( foreign_pkey_value );
1773
1774                                         if( _fparam ) {
1775                                                 // Examine each context column of the foreign row,
1776                                                 // and add its value to the list of org units.
1777                                                 int j = 0;
1778                                                 const char* foreign_field = NULL;
1779                                                 osrfStringArray* ctx_array = osrfHashGet( fcontext, "context" );
1780                                                 while ( (foreign_field = osrfStringArrayGetString( ctx_array, j++ )) ) {
1781                                                         osrfStringArrayAdd( context_org_array,
1782                                                                 oilsFMGetStringConst( _fparam, foreign_field ));
1783                                                         osrfLogDebug( OSRF_LOG_MARK,
1784                                                                 "adding foreign class %s field %s (value: %s) "
1785                                                                         "to the context org list",
1786                                                                 class_name,
1787                                                                 foreign_field,
1788                                                                 osrfStringArrayGetString(
1789                                                                         context_org_array, context_org_array->size - 1 )
1790                                                         );
1791                                                 }
1792
1793                                                 jsonObjectFree( _fparam );
1794                                         }
1795                                 }
1796
1797                                 osrfHashIteratorFree( class_itr );
1798                         }
1799                 }
1800
1801                 jsonObjectFree( param );
1802         }
1803
1804         const char* context_org = NULL;
1805         const char* perm = NULL;
1806         int OK = 0;
1807
1808         // For every combination of permission and context org unit: call a stored procedure
1809         // to determine if the user has this permission in the context of this org unit.
1810         // If the answer is yes at any point, then we're done, and the user has permission.
1811         // In other words permissions are additive.
1812         int i = 0;
1813         while( (perm = osrfStringArrayGetString(permission, i++)) ) {
1814                 dbi_result result;
1815
1816         osrfStringArray* pcache = NULL;
1817         if (rs_size > perm_at_threshold) { // grab and cache locations of user perms
1818                         pcache = getPermLocationCache(ctx, perm);
1819
1820                         if (!pcache) {
1821                         pcache = osrfNewStringArray(0);
1822
1823                                 result = dbi_conn_queryf(
1824                                         writehandle,
1825                                         "SELECT permission.usr_has_perm_at_all(%d, '%s') AS at;",
1826                                         userid,
1827                                         perm
1828                                 );
1829
1830                                 if( result ) {
1831                                         osrfLogDebug(
1832                                                 OSRF_LOG_MARK,
1833                                                 "Received a result for permission [%s] for user %d",
1834                                                 perm,
1835                                                 userid
1836                                         );
1837
1838                                         if( dbi_result_first_row( result )) {
1839                             do {
1840                                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
1841                                                 osrfStringArrayAdd( pcache, jsonObjectGetString( jsonObjectGetKeyConst( return_val, "at" ) ) );
1842                                 jsonObjectFree( return_val );
1843                                             } while( dbi_result_next_row( result ));
1844
1845                                                 setPermLocationCache(ctx, perm, pcache);
1846                                         }
1847
1848                                         dbi_result_free( result );
1849                     }
1850                         }
1851         }
1852
1853                 int j = 0;
1854                 while( (context_org = osrfStringArrayGetString( context_org_array, j++ )) ) {
1855
1856             if (rs_size > perm_at_threshold) {
1857                 if (osrfStringArrayContains( pcache, context_org )) {
1858                     OK = 1;
1859                     break;
1860                 }
1861             }
1862
1863                         if( pkey_value ) {
1864                                 osrfLogDebug(
1865                                         OSRF_LOG_MARK,
1866                                         "Checking object permission [%s] for user %d "
1867                                                         "on object %s (class %s) at org %d",
1868                                         perm,
1869                                         userid,
1870                                         pkey_value,
1871                                         osrfHashGet( class, "classname" ),
1872                                         atoi( context_org )
1873                                 );
1874
1875                                 result = dbi_conn_queryf(
1876                                         writehandle,
1877                                         "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
1878                                         userid,
1879                                         perm,
1880                                         osrfHashGet( class, "classname" ),
1881                                         pkey_value,
1882                                         atoi( context_org )
1883                                 );
1884
1885                                 if( result ) {
1886                                         osrfLogDebug(
1887                                                 OSRF_LOG_MARK,
1888                                                 "Received a result for object permission [%s] "
1889                                                                 "for user %d on object %s (class %s) at org %d",
1890                                                 perm,
1891                                                 userid,
1892                                                 pkey_value,
1893                                                 osrfHashGet( class, "classname" ),
1894                                                 atoi( context_org )
1895                                         );
1896
1897                                         if( dbi_result_first_row( result )) {
1898                                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
1899                                                 const char* has_perm = jsonObjectGetString(
1900                                                                 jsonObjectGetKeyConst( return_val, "has_perm" ));
1901
1902                                                 osrfLogDebug(
1903                                                         OSRF_LOG_MARK,
1904                                                         "Status of object permission [%s] for user %d "
1905                                                                         "on object %s (class %s) at org %d is %s",
1906                                                         perm,
1907                                                         userid,
1908                                                         pkey_value,
1909                                                         osrfHashGet(class, "classname"),
1910                                                         atoi(context_org),
1911                                                         has_perm
1912                                                 );
1913
1914                                                 if( *has_perm == 't' )
1915                                                         OK = 1;
1916                                                 jsonObjectFree( return_val );
1917                                         }
1918
1919                                         dbi_result_free( result );
1920                                         if( OK )
1921                                                 break;
1922                                 } else {
1923                                         const char* msg;
1924                                         int errnum = dbi_conn_error( writehandle, &msg );
1925                                         osrfLogWarning( OSRF_LOG_MARK,
1926                                                 "Unable to call check object permissions: %d, %s",
1927                                                 errnum, msg ? msg : "(No description available)" );
1928                                         if( !oilsIsDBConnected( writehandle ))
1929                                                 osrfAppSessionPanic( ctx->session );
1930                                 }
1931                         }
1932
1933             if (rs_size > perm_at_threshold) break;
1934
1935                         osrfLogDebug( OSRF_LOG_MARK,
1936                                         "Checking non-object permission [%s] for user %d at org %d",
1937                                         perm, userid, atoi(context_org) );
1938                         result = dbi_conn_queryf(
1939                                 writehandle,
1940                                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
1941                                 userid,
1942                                 perm,
1943                                 atoi( context_org )
1944                         );
1945
1946                         if( result ) {
1947                                 osrfLogDebug( OSRF_LOG_MARK,
1948                                         "Received a result for permission [%s] for user %d at org %d",
1949                                         perm, userid, atoi( context_org ));
1950                                 if( dbi_result_first_row( result )) {
1951                                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1952                                         const char* has_perm = jsonObjectGetString(
1953                                                 jsonObjectGetKeyConst( return_val, "has_perm" ));
1954                                         osrfLogDebug( OSRF_LOG_MARK,
1955                                                 "Status of permission [%s] for user %d at org %d is [%s]",
1956                                                 perm, userid, atoi( context_org ), has_perm );
1957                                         if( *has_perm == 't' )
1958                                                 OK = 1;
1959                                         jsonObjectFree( return_val );
1960                                 }
1961
1962                                 dbi_result_free( result );
1963                                 if( OK )
1964                                         break;
1965                         } else {
1966                                 const char* msg;
1967                                 int errnum = dbi_conn_error( writehandle, &msg );
1968                                 osrfLogWarning( OSRF_LOG_MARK, "Unable to call user object permissions: %d, %s",
1969                                         errnum, msg ? msg : "(No description available)" );
1970                                 if( !oilsIsDBConnected( writehandle ))
1971                                         osrfAppSessionPanic( ctx->session );
1972                         }
1973
1974                 }
1975
1976                 if( OK )
1977                         break;
1978         }
1979
1980         osrfStringArrayFree( context_org_array );
1981
1982         return OK;
1983 }
1984
1985 /**
1986         @brief Look up the root of the org_unit tree.
1987         @param ctx Pointer to the method context.
1988         @return The id of the root org unit, as a character string.
1989
1990         Query actor.org_unit where parent_ou is null, and return the id as a string.
1991
1992         This function assumes that there is only one root org unit, i.e. that we
1993         have a single tree, not a forest.
1994
1995         The calling code is responsible for freeing the returned string.
1996 */
1997 static const char* org_tree_root( osrfMethodContext* ctx ) {
1998
1999         static char cached_root_id[ 32 ] = "";  // extravagantly large buffer
2000         static time_t last_lookup_time = 0;
2001         time_t current_time = time( NULL );
2002
2003         if( cached_root_id[ 0 ] && ( current_time - last_lookup_time < 3600 ) ) {
2004                 // We successfully looked this up less than an hour ago.
2005                 // It's not likely to have changed since then.
2006                 return strdup( cached_root_id );
2007         }
2008         last_lookup_time = current_time;
2009
2010         int err = 0;
2011         jsonObject* where_clause = single_hash( "parent_ou", NULL );
2012         jsonObject* result = doFieldmapperSearch(
2013                 ctx, osrfHashGet( oilsIDL(), "aou" ), where_clause, NULL, &err );
2014         jsonObjectFree( where_clause );
2015
2016         jsonObject* tree_top = jsonObjectGetIndex( result, 0 );
2017
2018         if( !tree_top ) {
2019                 jsonObjectFree( result );
2020
2021                 growing_buffer* msg = buffer_init( 128 );
2022                 OSRF_BUFFER_ADD( msg, modulename );
2023                 OSRF_BUFFER_ADD( msg,
2024                                 ": Internal error, could not find the top of the org tree (parent_ou = NULL)" );
2025
2026                 char* m = buffer_release( msg );
2027                 osrfAppSessionStatus( ctx->session,
2028                                 OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, m );
2029                 free( m );
2030
2031                 cached_root_id[ 0 ] = '\0';
2032                 return NULL;
2033         }
2034
2035         const char* root_org_unit_id = oilsFMGetStringConst( tree_top, "id" );
2036         osrfLogDebug( OSRF_LOG_MARK, "Top of the org tree is %s", root_org_unit_id );
2037
2038         strcpy( cached_root_id, root_org_unit_id );
2039         jsonObjectFree( result );
2040         return cached_root_id;
2041 }
2042
2043 /**
2044         @brief Create a JSON_HASH with a single key/value pair.
2045         @param key The key of the key/value pair.
2046         @param value the value of the key/value pair.
2047         @return Pointer to a newly created jsonObject of type JSON_HASH.
2048
2049         The value of the key/value is either a string or (if @a value is NULL) a null.
2050 */
2051 static jsonObject* single_hash( const char* key, const char* value ) {
2052         // Sanity check
2053         if( ! key ) key = "";
2054
2055         jsonObject* hash = jsonNewObjectType( JSON_HASH );
2056         jsonObjectSetKey( hash, key, jsonNewObject( value ) );
2057         return hash;
2058 }
2059
2060
2061 int doCreate( osrfMethodContext* ctx ) {
2062         if(osrfMethodVerifyContext( ctx )) {
2063                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
2064                 return -1;
2065         }
2066
2067         if( enforce_pcrud )
2068                 timeout_needs_resetting = 1;
2069
2070         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
2071         jsonObject* target = NULL;
2072         jsonObject* options = NULL;
2073
2074         if( enforce_pcrud ) {
2075                 target = jsonObjectGetIndex( ctx->params, 1 );
2076                 options = jsonObjectGetIndex( ctx->params, 2 );
2077         } else {
2078                 target = jsonObjectGetIndex( ctx->params, 0 );
2079                 options = jsonObjectGetIndex( ctx->params, 1 );
2080         }
2081
2082         if( !verifyObjectClass( ctx, target )) {
2083                 osrfAppRespondComplete( ctx, NULL );
2084                 return -1;
2085         }
2086
2087         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
2088
2089         const char* trans_id = getXactId( ctx );
2090         if( !trans_id ) {
2091                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
2092
2093                 osrfAppSessionStatus(
2094                         ctx->session,
2095                         OSRF_STATUS_BADREQUEST,
2096                         "osrfMethodException",
2097                         ctx->request,
2098                         "No active transaction -- required for CREATE"
2099                 );
2100                 osrfAppRespondComplete( ctx, NULL );
2101                 return -1;
2102         }
2103
2104         // The following test is harmless but redundant.  If a class is
2105         // readonly, we don't register a create method for it.
2106         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
2107                 osrfAppSessionStatus(
2108                         ctx->session,
2109                         OSRF_STATUS_BADREQUEST,
2110                         "osrfMethodException",
2111                         ctx->request,
2112                         "Cannot INSERT readonly class"
2113                 );
2114                 osrfAppRespondComplete( ctx, NULL );
2115                 return -1;
2116         }
2117
2118         // Set the last_xact_id
2119         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
2120         if( index > -1 ) {
2121                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
2122                         trans_id, target->classname, index);
2123                 jsonObjectSetIndex( target, index, jsonNewObject( trans_id ));
2124         }
2125
2126         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
2127
2128         dbhandle = writehandle;
2129
2130         osrfHash* fields = osrfHashGet( meta, "fields" );
2131         char* pkey       = osrfHashGet( meta, "primarykey" );
2132         char* seq        = osrfHashGet( meta, "sequence" );
2133
2134         growing_buffer* table_buf = buffer_init( 128 );
2135         growing_buffer* col_buf   = buffer_init( 128 );
2136         growing_buffer* val_buf   = buffer_init( 128 );
2137
2138         OSRF_BUFFER_ADD( table_buf, "INSERT INTO " );
2139         OSRF_BUFFER_ADD( table_buf, osrfHashGet( meta, "tablename" ));
2140         OSRF_BUFFER_ADD_CHAR( col_buf, '(' );
2141         buffer_add( val_buf,"VALUES (" );
2142
2143
2144         int first = 1;
2145         osrfHash* field = NULL;
2146         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
2147         while( (field = osrfHashIteratorNext( field_itr ) ) ) {
2148
2149                 const char* field_name = osrfHashIteratorKey( field_itr );
2150
2151                 if( str_is_true( osrfHashGet( field, "virtual" ) ) )
2152                         continue;
2153
2154                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
2155
2156                 char* value;
2157                 if( field_object && field_object->classname ) {
2158                         value = oilsFMGetString(
2159                                 field_object,
2160                                 (char*)oilsIDLFindPath( "/%s/primarykey", field_object->classname )
2161                         );
2162                 } else if( field_object && JSON_BOOL == field_object->type ) {
2163                         if( jsonBoolIsTrue( field_object ) )
2164                                 value = strdup( "t" );
2165                         else
2166                                 value = strdup( "f" );
2167                 } else {
2168                         value = jsonObjectToSimpleString( field_object );
2169                 }
2170
2171                 if( first ) {
2172                         first = 0;
2173                 } else {
2174                         OSRF_BUFFER_ADD_CHAR( col_buf, ',' );
2175                         OSRF_BUFFER_ADD_CHAR( val_buf, ',' );
2176                 }
2177
2178                 buffer_add( col_buf, field_name );
2179
2180                 if( !field_object || field_object->type == JSON_NULL ) {
2181                         buffer_add( val_buf, "DEFAULT" );
2182
2183                 } else if( !strcmp( get_primitive( field ), "number" )) {
2184                         const char* numtype = get_datatype( field );
2185                         if( !strcmp( numtype, "INT8" )) {
2186                                 buffer_fadd( val_buf, "%lld", atoll( value ));
2187
2188                         } else if( !strcmp( numtype, "INT" )) {
2189                                 buffer_fadd( val_buf, "%d", atoi( value ));
2190
2191                         } else if( !strcmp( numtype, "NUMERIC" )) {
2192                                 buffer_fadd( val_buf, "%f", atof( value ));
2193                         }
2194                 } else {
2195                         if( dbi_conn_quote_string( writehandle, &value )) {
2196                                 OSRF_BUFFER_ADD( val_buf, value );
2197
2198                         } else {
2199                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value );
2200                                 osrfAppSessionStatus(
2201                                         ctx->session,
2202                                         OSRF_STATUS_INTERNALSERVERERROR,
2203                                         "osrfMethodException",
2204                                         ctx->request,
2205                                         "Error quoting string -- please see the error log for more details"
2206                                 );
2207                                 free( value );
2208                                 buffer_free( table_buf );
2209                                 buffer_free( col_buf );
2210                                 buffer_free( val_buf );
2211                                 osrfAppRespondComplete( ctx, NULL );
2212                                 return -1;
2213                         }
2214                 }
2215
2216                 free( value );
2217         }
2218
2219         osrfHashIteratorFree( field_itr );
2220
2221         OSRF_BUFFER_ADD_CHAR( col_buf, ')' );
2222         OSRF_BUFFER_ADD_CHAR( val_buf, ')' );
2223
2224         char* table_str = buffer_release( table_buf );
2225         char* col_str   = buffer_release( col_buf );
2226         char* val_str   = buffer_release( val_buf );
2227         growing_buffer* sql = buffer_init( 128 );
2228         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
2229         free( table_str );
2230         free( col_str );
2231         free( val_str );
2232
2233         char* query = buffer_release( sql );
2234
2235         osrfLogDebug( OSRF_LOG_MARK, "%s: Insert SQL [%s]", modulename, query );
2236
2237         jsonObject* obj = NULL;
2238         int rc = 0;
2239
2240         dbi_result result = dbi_conn_query( writehandle, query );
2241         if( !result ) {
2242                 obj = jsonNewObject( NULL );
2243                 const char* msg;
2244                 int errnum = dbi_conn_error( writehandle, &msg );
2245                 osrfLogError(
2246                         OSRF_LOG_MARK,
2247                         "%s ERROR inserting %s object using query [%s]: %d %s",
2248                         modulename,
2249                         osrfHashGet(meta, "fieldmapper"),
2250                         query,
2251                         errnum,
2252                         msg ? msg : "(No description available)"
2253                 );
2254                 osrfAppSessionStatus(
2255                         ctx->session,
2256                         OSRF_STATUS_INTERNALSERVERERROR,
2257                         "osrfMethodException",
2258                         ctx->request,
2259                         "INSERT error -- please see the error log for more details"
2260                 );
2261                 if( !oilsIsDBConnected( writehandle ))
2262                         osrfAppSessionPanic( ctx->session );
2263                 rc = -1;
2264         } else {
2265                 dbi_result_free( result );
2266
2267                 char* id = oilsFMGetString( target, pkey );
2268                 if( !id ) {
2269                         unsigned long long new_id = dbi_conn_sequence_last( writehandle, seq );
2270                         growing_buffer* _id = buffer_init( 10 );
2271                         buffer_fadd( _id, "%lld", new_id );
2272                         id = buffer_release( _id );
2273                 }
2274
2275                 // Find quietness specification, if present
2276                 const char* quiet_str = NULL;
2277                 if( options ) {
2278                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
2279                         if( quiet_obj )
2280                                 quiet_str = jsonObjectGetString( quiet_obj );
2281                 }
2282
2283                 if( str_is_true( quiet_str )) {  // if quietness is specified
2284                         obj = jsonNewObject( id );
2285                 }
2286                 else {
2287
2288                         // Fetch the row that we just inserted, so that we can return it to the client
2289                         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2290                         jsonObjectSetKey( where_clause, pkey, jsonNewObject( id ));
2291
2292                         int err = 0;
2293                         jsonObject* list = doFieldmapperSearch( ctx, meta, where_clause, NULL, &err );
2294                         if( err )
2295                                 rc = -1;
2296                         else
2297                                 obj = jsonObjectClone( jsonObjectGetIndex( list, 0 ));
2298
2299                         jsonObjectFree( list );
2300                         jsonObjectFree( where_clause );
2301                 }
2302
2303                 free( id );
2304         }
2305
2306         free( query );
2307         osrfAppRespondComplete( ctx, obj );
2308         jsonObjectFree( obj );
2309         return rc;
2310 }
2311
2312 /**
2313         @brief Implement the retrieve method.
2314         @param ctx Pointer to the method context.
2315         @param err Pointer through which to return an error code.
2316         @return If successful, a pointer to the result to be returned to the client;
2317         otherwise NULL.
2318
2319         From the method's class, fetch a row with a specified value in the primary key.  This
2320         method relies on the database design convention that a primary key consists of a single
2321         column.
2322
2323         Method parameters:
2324         - authkey (PCRUD only)
2325         - value of the primary key for the desired row, for building the WHERE clause
2326         - a JSON_HASH containing any other SQL clauses: select, join, etc.
2327
2328         Return to client: One row from the query.
2329 */
2330 int doRetrieve( osrfMethodContext* ctx ) {
2331         if(osrfMethodVerifyContext( ctx )) {
2332                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
2333                 return -1;
2334         }
2335
2336         if( enforce_pcrud )
2337                 timeout_needs_resetting = 1;
2338
2339         int id_pos = 0;
2340         int order_pos = 1;
2341
2342         if( enforce_pcrud ) {
2343                 id_pos = 1;
2344                 order_pos = 2;
2345         }
2346
2347         // Get the class metadata
2348         osrfHash* class_def = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
2349
2350         // Get the value of the primary key, from a method parameter
2351         const jsonObject* id_obj = jsonObjectGetIndex( ctx->params, id_pos );
2352
2353         osrfLogDebug(
2354                 OSRF_LOG_MARK,
2355                 "%s retrieving %s object with primary key value of %s",
2356                 modulename,
2357                 osrfHashGet( class_def, "fieldmapper" ),
2358                 jsonObjectGetString( id_obj )
2359         );
2360
2361         // Build a WHERE clause based on the key value
2362         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2363         jsonObjectSetKey(
2364                 where_clause,
2365                 osrfHashGet( class_def, "primarykey" ),  // name of key column
2366                 jsonObjectClone( id_obj )                // value of key column
2367         );
2368
2369         jsonObject* rest_of_query = jsonObjectGetIndex( ctx->params, order_pos );
2370
2371         // Do the query
2372         int err = 0;
2373         jsonObject* list = doFieldmapperSearch( ctx, class_def, where_clause, rest_of_query, &err );
2374
2375         jsonObjectFree( where_clause );
2376         if( err ) {
2377                 osrfAppRespondComplete( ctx, NULL );
2378                 return -1;
2379         }
2380
2381         jsonObject* obj = jsonObjectExtractIndex( list, 0 );
2382         jsonObjectFree( list );
2383
2384         if( enforce_pcrud ) {
2385                 // no result, skip this entirely
2386                 if(NULL != obj && !verifyObjectPCRUD( ctx, class_def, obj, 1 )) {
2387                         jsonObjectFree( obj );
2388
2389                         growing_buffer* msg = buffer_init( 128 );
2390                         OSRF_BUFFER_ADD( msg, modulename );
2391                         OSRF_BUFFER_ADD( msg, ": Insufficient permissions to retrieve object" );
2392
2393                         char* m = buffer_release( msg );
2394                         osrfAppSessionStatus( ctx->session, OSRF_STATUS_NOTALLOWED, "osrfMethodException",
2395                                         ctx->request, m );
2396                         free( m );
2397
2398                         osrfAppRespondComplete( ctx, NULL );
2399                         return -1;
2400                 }
2401         }
2402
2403         // doFieldmapperSearch() now does the responding for us
2404         //osrfAppRespondComplete( ctx, obj );
2405         osrfAppRespondComplete( ctx, NULL );
2406
2407         jsonObjectFree( obj );
2408         return 0;
2409 }
2410
2411 /**
2412         @brief Translate a numeric value to a string representation for the database.
2413         @param field Pointer to the IDL field definition.
2414         @param value Pointer to a jsonObject holding the value of a field.
2415         @return Pointer to a newly allocated string.
2416
2417         The input object is typically a JSON_NUMBER, but it may be a JSON_STRING as long as
2418         its contents are numeric.  A non-numeric string is likely to result in invalid SQL,
2419         or (what is worse) valid SQL that is wrong.
2420
2421         If the datatype of the receiving field is not numeric, wrap the value in quotes.
2422
2423         The calling code is responsible for freeing the resulting string by calling free().
2424 */
2425 static char* jsonNumberToDBString( osrfHash* field, const jsonObject* value ) {
2426         growing_buffer* val_buf = buffer_init( 32 );
2427         const char* numtype = get_datatype( field );
2428
2429         // For historical reasons the following contains cruft that could be cleaned up.
2430         if( !strncmp( numtype, "INT", 3 ) ) {
2431                 if( value->type == JSON_NUMBER )
2432                         //buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
2433                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2434                 else {
2435                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2436                 }
2437
2438         } else if( !strcmp( numtype, "NUMERIC" )) {
2439                 if( value->type == JSON_NUMBER )
2440                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2441                 else {
2442                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2443                 }
2444
2445         } else {
2446                 // Presumably this was really intended to be a string, so quote it
2447                 char* str = jsonObjectToSimpleString( value );
2448                 if( dbi_conn_quote_string( dbhandle, &str )) {
2449                         OSRF_BUFFER_ADD( val_buf, str );
2450                         free( str );
2451                 } else {
2452                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]", modulename, str );
2453                         free( str );
2454                         buffer_free( val_buf );
2455                         return NULL;
2456                 }
2457         }
2458
2459         return buffer_release( val_buf );
2460 }
2461
2462 static char* searchINPredicate( const char* class_alias, osrfHash* field,
2463                 jsonObject* node, const char* op, osrfMethodContext* ctx ) {
2464         growing_buffer* sql_buf = buffer_init( 32 );
2465
2466         buffer_fadd(
2467                 sql_buf,
2468                 "\"%s\".%s ",
2469                 class_alias,
2470                 osrfHashGet( field, "name" )
2471         );
2472
2473         if( !op ) {
2474                 buffer_add( sql_buf, "IN (" );
2475         } else if( !strcasecmp( op,"not in" )) {
2476                 buffer_add( sql_buf, "NOT IN (" );
2477         } else {
2478                 buffer_add( sql_buf, "IN (" );
2479         }
2480
2481         if( node->type == JSON_HASH ) {
2482                 // subquery predicate
2483                 char* subpred = buildQuery( ctx, node, SUBSELECT );
2484                 if( ! subpred ) {
2485                         buffer_free( sql_buf );
2486                         return NULL;
2487                 }
2488
2489                 buffer_add( sql_buf, subpred );
2490                 free( subpred );
2491
2492         } else if( node->type == JSON_ARRAY ) {
2493                 // literal value list
2494                 int in_item_index = 0;
2495                 int in_item_first = 1;
2496                 const jsonObject* in_item;
2497                 while( (in_item = jsonObjectGetIndex( node, in_item_index++ )) ) {
2498
2499                         if( in_item_first )
2500                                 in_item_first = 0;
2501                         else
2502                                 buffer_add( sql_buf, ", " );
2503
2504                         // Sanity check
2505                         if( in_item->type != JSON_STRING && in_item->type != JSON_NUMBER ) {
2506                                 osrfLogError( OSRF_LOG_MARK,
2507                                                 "%s: Expected string or number within IN list; found %s",
2508                                                 modulename, json_type( in_item->type ) );
2509                                 buffer_free( sql_buf );
2510                                 return NULL;
2511                         }
2512
2513                         // Append the literal value -- quoted if not a number
2514                         if( JSON_NUMBER == in_item->type ) {
2515                                 char* val = jsonNumberToDBString( field, in_item );
2516                                 OSRF_BUFFER_ADD( sql_buf, val );
2517                                 free( val );
2518
2519                         } else if( !strcmp( get_primitive( field ), "number" )) {
2520                                 char* val = jsonNumberToDBString( field, in_item );
2521                                 OSRF_BUFFER_ADD( sql_buf, val );
2522                                 free( val );
2523
2524                         } else {
2525                                 char* key_string = jsonObjectToSimpleString( in_item );
2526                                 if( dbi_conn_quote_string( dbhandle, &key_string )) {
2527                                         OSRF_BUFFER_ADD( sql_buf, key_string );
2528                                         free( key_string );
2529                                 } else {
2530                                         osrfLogError( OSRF_LOG_MARK,
2531                                                         "%s: Error quoting key string [%s]", modulename, key_string );
2532                                         free( key_string );
2533                                         buffer_free( sql_buf );
2534                                         return NULL;
2535                                 }
2536                         }
2537                 }
2538
2539                 if( in_item_first ) {
2540                         osrfLogError(OSRF_LOG_MARK, "%s: Empty IN list", modulename );
2541                         buffer_free( sql_buf );
2542                         return NULL;
2543                 }
2544         } else {
2545                 osrfLogError( OSRF_LOG_MARK, "%s: Expected object or array for IN clause; found %s",
2546                         modulename, json_type( node->type ));
2547                 buffer_free( sql_buf );
2548                 return NULL;
2549         }
2550
2551         OSRF_BUFFER_ADD_CHAR( sql_buf, ')' );
2552
2553         return buffer_release( sql_buf );
2554 }
2555
2556 // Receive a JSON_ARRAY representing a function call.  The first
2557 // entry in the array is the function name.  The rest are parameters.
2558 static char* searchValueTransform( const jsonObject* array ) {
2559
2560         if( array->size < 1 ) {
2561                 osrfLogError( OSRF_LOG_MARK, "%s: Empty array for value transform", modulename );
2562                 return NULL;
2563         }
2564
2565         // Get the function name
2566         jsonObject* func_item = jsonObjectGetIndex( array, 0 );
2567         if( func_item->type != JSON_STRING ) {
2568                 osrfLogError( OSRF_LOG_MARK, "%s: Error: expected function name, found %s",
2569                         modulename, json_type( func_item->type ));
2570                 return NULL;
2571         }
2572
2573         growing_buffer* sql_buf = buffer_init( 32 );
2574
2575         OSRF_BUFFER_ADD( sql_buf, jsonObjectGetString( func_item ) );
2576         OSRF_BUFFER_ADD( sql_buf, "( " );
2577
2578         // Get the parameters
2579         int func_item_index = 1;   // We already grabbed the zeroth entry
2580         while( (func_item = jsonObjectGetIndex( array, func_item_index++ )) ) {
2581
2582                 // Add a separator comma, if we need one
2583                 if( func_item_index > 2 )
2584                         buffer_add( sql_buf, ", " );
2585
2586                 // Add the current parameter
2587                 if( func_item->type == JSON_NULL ) {
2588                         buffer_add( sql_buf, "NULL" );
2589                 } else {
2590                         if( func_item->type == JSON_BOOL ) {
2591                                 if( jsonBoolIsTrue(func_item) ) {
2592                                         buffer_add( sql_buf, "TRUE" );
2593                                 } else {
2594                                         buffer_add( sql_buf, "FALSE" );
2595                                 }
2596                         } else {
2597                                 char* val = jsonObjectToSimpleString( func_item );
2598                                 if( dbi_conn_quote_string( dbhandle, &val )) {
2599                                         OSRF_BUFFER_ADD( sql_buf, val );
2600                                         free( val );
2601                                 } else {
2602                                         osrfLogError( OSRF_LOG_MARK,
2603                                                 "%s: Error quoting key string [%s]", modulename, val );
2604                                         buffer_free( sql_buf );
2605                                         free( val );
2606                                         return NULL;
2607                                 }
2608                         }
2609                 }
2610         }
2611
2612         buffer_add( sql_buf, " )" );
2613
2614         return buffer_release( sql_buf );
2615 }
2616
2617 static char* searchFunctionPredicate( const char* class_alias, osrfHash* field,
2618                 const jsonObject* node, const char* op ) {
2619
2620         if( ! is_good_operator( op ) ) {
2621                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2622                 return NULL;
2623         }
2624
2625         char* val = searchValueTransform( node );
2626         if( !val )
2627                 return NULL;
2628
2629         growing_buffer* sql_buf = buffer_init( 32 );
2630         buffer_fadd(
2631                 sql_buf,
2632                 "\"%s\".%s %s %s",
2633                 class_alias,
2634                 osrfHashGet( field, "name" ),
2635                 op,
2636                 val
2637         );
2638
2639         free( val );
2640
2641         return buffer_release( sql_buf );
2642 }
2643
2644 // class_alias is a class name or other table alias
2645 // field is a field definition as stored in the IDL
2646 // node comes from the method parameter, and may represent an entry in the SELECT list
2647 static char* searchFieldTransform( const char* class_alias, osrfHash* field,
2648                 const jsonObject* node ) {
2649         growing_buffer* sql_buf = buffer_init( 32 );
2650
2651         const char* field_transform = jsonObjectGetString(
2652                 jsonObjectGetKeyConst( node, "transform" ) );
2653         const char* transform_subcolumn = jsonObjectGetString(
2654                 jsonObjectGetKeyConst( node, "result_field" ) );
2655
2656         if( transform_subcolumn ) {
2657                 if( ! is_identifier( transform_subcolumn ) ) {
2658                         osrfLogError( OSRF_LOG_MARK, "%s: Invalid subfield name: \"%s\"\n",
2659                                         modulename, transform_subcolumn );
2660                         buffer_free( sql_buf );
2661                         return NULL;
2662                 }
2663                 OSRF_BUFFER_ADD_CHAR( sql_buf, '(' );    // enclose transform in parentheses
2664         }
2665
2666         if( field_transform ) {
2667
2668                 if( ! is_identifier( field_transform ) ) {
2669                         osrfLogError( OSRF_LOG_MARK, "%s: Expected function name, found \"%s\"\n",
2670                                         modulename, field_transform );
2671                         buffer_free( sql_buf );
2672                         return NULL;
2673                 }
2674
2675                 if( obj_is_true( jsonObjectGetKeyConst( node, "distinct" ) ) ) {
2676                         buffer_fadd( sql_buf, "%s(DISTINCT \"%s\".%s",
2677                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2678                 } else {
2679                         buffer_fadd( sql_buf, "%s(\"%s\".%s",
2680                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2681                 }
2682
2683                 const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
2684
2685                 if( array ) {
2686                         if( array->type != JSON_ARRAY ) {
2687                                 osrfLogError( OSRF_LOG_MARK,
2688                                         "%s: Expected JSON_ARRAY for function params; found %s",
2689                                         modulename, json_type( array->type ) );
2690                                 buffer_free( sql_buf );
2691                                 return NULL;
2692                         }
2693                         int func_item_index = 0;
2694                         jsonObject* func_item;
2695                         while( (func_item = jsonObjectGetIndex( array, func_item_index++ ))) {
2696
2697                                 char* val = jsonObjectToSimpleString( func_item );
2698
2699                                 if( !val ) {
2700                                         buffer_add( sql_buf, ",NULL" );
2701                                 } else if( dbi_conn_quote_string( dbhandle, &val )) {
2702                                         OSRF_BUFFER_ADD_CHAR( sql_buf, ',' );
2703                                         OSRF_BUFFER_ADD( sql_buf, val );
2704                                 } else {
2705                                         osrfLogError( OSRF_LOG_MARK,
2706                                                         "%s: Error quoting key string [%s]", modulename, val );
2707                                         free( val );
2708                                         buffer_free( sql_buf );
2709                                         return NULL;
2710                                 }
2711                                 free( val );
2712                         }
2713                 }
2714
2715                 buffer_add( sql_buf, " )" );
2716
2717         } else {
2718                 buffer_fadd( sql_buf, "\"%s\".%s", class_alias, osrfHashGet( field, "name" ));
2719         }
2720
2721         if( transform_subcolumn )
2722                 buffer_fadd( sql_buf, ").\"%s\"", transform_subcolumn );
2723
2724         return buffer_release( sql_buf );
2725 }
2726
2727 static char* searchFieldTransformPredicate( const ClassInfo* class_info, osrfHash* field,
2728                 const jsonObject* node, const char* op ) {
2729
2730         if( ! is_good_operator( op ) ) {
2731                 osrfLogError( OSRF_LOG_MARK, "%s: Error: Invalid operator %s", modulename, op );
2732                 return NULL;
2733         }
2734
2735         char* field_transform = searchFieldTransform( class_info->alias, field, node );
2736         if( ! field_transform )
2737                 return NULL;
2738         char* value = NULL;
2739         int extra_parens = 0;   // boolean
2740
2741         const jsonObject* value_obj = jsonObjectGetKeyConst( node, "value" );
2742         if( ! value_obj ) {
2743                 value = searchWHERE( node, class_info, AND_OP_JOIN, NULL );
2744                 if( !value ) {
2745                         osrfLogError( OSRF_LOG_MARK, "%s: Error building condition for field transform",
2746                                 modulename );
2747                         free( field_transform );
2748                         return NULL;
2749                 }
2750                 extra_parens = 1;
2751         } else if( value_obj->type == JSON_ARRAY ) {
2752                 value = searchValueTransform( value_obj );
2753                 if( !value ) {
2754                         osrfLogError( OSRF_LOG_MARK,
2755                                 "%s: Error building value transform for field transform", modulename );
2756                         free( field_transform );
2757                         return NULL;
2758                 }
2759         } else if( value_obj->type == JSON_HASH ) {
2760                 value = searchWHERE( value_obj, class_info, AND_OP_JOIN, NULL );
2761                 if( !value ) {
2762                         osrfLogError( OSRF_LOG_MARK, "%s: Error building predicate for field transform",
2763                                 modulename );
2764                         free( field_transform );
2765                         return NULL;
2766                 }
2767                 extra_parens = 1;
2768         } else if( value_obj->type == JSON_NUMBER ) {
2769                 value = jsonNumberToDBString( field, value_obj );
2770         } else if( value_obj->type == JSON_NULL ) {
2771                 osrfLogError( OSRF_LOG_MARK,
2772                         "%s: Error building predicate for field transform: null value", modulename );
2773                 free( field_transform );
2774                 return NULL;
2775         } else if( value_obj->type == JSON_BOOL ) {
2776                 osrfLogError( OSRF_LOG_MARK,
2777                         "%s: Error building predicate for field transform: boolean value", modulename );
2778                 free( field_transform );
2779                 return NULL;
2780         } else {
2781                 if( !strcmp( get_primitive( field ), "number") ) {
2782                         value = jsonNumberToDBString( field, value_obj );
2783                 } else {
2784                         value = jsonObjectToSimpleString( value_obj );
2785                         if( !dbi_conn_quote_string( dbhandle, &value )) {
2786                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2787                                         modulename, value );
2788                                 free( value );
2789                                 free( field_transform );
2790                                 return NULL;
2791                         }
2792                 }
2793         }
2794
2795         const char* left_parens  = "";
2796         const char* right_parens = "";
2797
2798         if( extra_parens ) {
2799                 left_parens  = "(";
2800                 right_parens = ")";
2801         }
2802
2803         const char* right_percent = "";
2804         const char* real_op       = op;
2805
2806         if( !strcasecmp( op, "startwith") ) {
2807                 real_op = "like";
2808                 right_percent = "|| '%'";
2809         }
2810
2811         growing_buffer* sql_buf = buffer_init( 32 );
2812
2813         buffer_fadd(
2814                 sql_buf,
2815                 "%s%s %s %s %s%s %s%s",
2816                 left_parens,
2817                 field_transform,
2818                 real_op,
2819                 left_parens,
2820                 value,
2821                 right_percent,
2822                 right_parens,
2823                 right_parens
2824         );
2825
2826         free( value );
2827         free( field_transform );
2828
2829         return buffer_release( sql_buf );
2830 }
2831
2832 static char* searchSimplePredicate( const char* op, const char* class_alias,
2833                 osrfHash* field, const jsonObject* node ) {
2834
2835         if( ! is_good_operator( op ) ) {
2836                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2837                 return NULL;
2838         }
2839
2840         char* val = NULL;
2841
2842         // Get the value to which we are comparing the specified column
2843         if( node->type != JSON_NULL ) {
2844                 if( node->type == JSON_NUMBER ) {
2845                         val = jsonNumberToDBString( field, node );
2846                 } else if( !strcmp( get_primitive( field ), "number" ) ) {
2847                         val = jsonNumberToDBString( field, node );
2848                 } else {
2849                         val = jsonObjectToSimpleString( node );
2850                 }
2851         }
2852
2853         if( val ) {
2854                 if( JSON_NUMBER != node->type && strcmp( get_primitive( field ), "number") ) {
2855                         // Value is not numeric; enclose it in quotes
2856                         if( !dbi_conn_quote_string( dbhandle, &val ) ) {
2857                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2858                                         modulename, val );
2859                                 free( val );
2860                                 return NULL;
2861                         }
2862                 }
2863         } else {
2864                 // Compare to a null value
2865                 val = strdup( "NULL" );
2866                 if( strcmp( op, "=" ))
2867                         op = "IS NOT";
2868                 else
2869                         op = "IS";
2870         }
2871
2872         growing_buffer* sql_buf = buffer_init( 32 );
2873         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class_alias, osrfHashGet(field, "name"), op, val );
2874         char* pred = buffer_release( sql_buf );
2875
2876         free( val );
2877
2878         return pred;
2879 }
2880
2881 static char* searchBETWEENPredicate( const char* class_alias,
2882                 osrfHash* field, const jsonObject* node ) {
2883
2884         const jsonObject* x_node = jsonObjectGetIndex( node, 0 );
2885         const jsonObject* y_node = jsonObjectGetIndex( node, 1 );
2886
2887         if( NULL == y_node ) {
2888                 osrfLogError( OSRF_LOG_MARK, "%s: Not enough operands for BETWEEN operator", modulename );
2889                 return NULL;
2890         }
2891         else if( NULL != jsonObjectGetIndex( node, 2 ) ) {
2892                 osrfLogError( OSRF_LOG_MARK, "%s: Too many operands for BETWEEN operator", modulename );
2893                 return NULL;
2894         }
2895
2896         char* x_string;
2897         char* y_string;
2898
2899         if( !strcmp( get_primitive( field ), "number") ) {
2900                 x_string = jsonNumberToDBString( field, x_node );
2901                 y_string = jsonNumberToDBString( field, y_node );
2902
2903         } else {
2904                 x_string = jsonObjectToSimpleString( x_node );
2905                 y_string = jsonObjectToSimpleString( y_node );
2906                 if( !(dbi_conn_quote_string( dbhandle, &x_string )
2907                         && dbi_conn_quote_string( dbhandle, &y_string )) ) {
2908                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]",
2909                                         modulename, x_string, y_string );
2910                         free( x_string );
2911                         free( y_string );
2912                         return NULL;
2913                 }
2914         }
2915
2916         growing_buffer* sql_buf = buffer_init( 32 );
2917         buffer_fadd( sql_buf, "\"%s\".%s BETWEEN %s AND %s",
2918                         class_alias, osrfHashGet( field, "name" ), x_string, y_string );
2919         free( x_string );
2920         free( y_string );
2921
2922         return buffer_release( sql_buf );
2923 }
2924
2925 static char* searchPredicate( const ClassInfo* class_info, osrfHash* field,
2926                                                           jsonObject* node, osrfMethodContext* ctx ) {
2927
2928         char* pred = NULL;
2929         if( node->type == JSON_ARRAY ) { // equality IN search
2930                 pred = searchINPredicate( class_info->alias, field, node, NULL, ctx );
2931         } else if( node->type == JSON_HASH ) { // other search
2932                 jsonIterator* pred_itr = jsonNewIterator( node );
2933                 if( !jsonIteratorHasNext( pred_itr ) ) {
2934                         osrfLogError( OSRF_LOG_MARK, "%s: Empty predicate for field \"%s\"",
2935                                         modulename, osrfHashGet(field, "name" ));
2936                 } else {
2937                         jsonObject* pred_node = jsonIteratorNext( pred_itr );
2938
2939                         // Verify that there are no additional predicates
2940                         if( jsonIteratorHasNext( pred_itr ) ) {
2941                                 osrfLogError( OSRF_LOG_MARK, "%s: Multiple predicates for field \"%s\"",
2942                                                 modulename, osrfHashGet(field, "name" ));
2943                         } else if( !(strcasecmp( pred_itr->key,"between" )) )
2944                                 pred = searchBETWEENPredicate( class_info->alias, field, pred_node );
2945                         else if( !(strcasecmp( pred_itr->key,"in" ))
2946                                         || !(strcasecmp( pred_itr->key,"not in" )) )
2947                                 pred = searchINPredicate(
2948                                         class_info->alias, field, pred_node, pred_itr->key, ctx );
2949                         else if( pred_node->type == JSON_ARRAY )
2950                                 pred = searchFunctionPredicate(
2951                                         class_info->alias, field, pred_node, pred_itr->key );
2952                         else if( pred_node->type == JSON_HASH )
2953                                 pred = searchFieldTransformPredicate(
2954                                         class_info, field, pred_node, pred_itr->key );
2955                         else
2956                                 pred = searchSimplePredicate( pred_itr->key, class_info->alias, field, pred_node );
2957                 }
2958                 jsonIteratorFree( pred_itr );
2959
2960         } else if( node->type == JSON_NULL ) { // IS NULL search
2961                 growing_buffer* _p = buffer_init( 64 );
2962                 buffer_fadd(
2963                         _p,
2964                         "\"%s\".%s IS NULL",
2965                         class_info->class_name,
2966                         osrfHashGet( field, "name" )
2967                 );
2968                 pred = buffer_release( _p );
2969         } else { // equality search
2970                 pred = searchSimplePredicate( "=", class_info->alias, field, node );
2971         }
2972
2973         return pred;
2974
2975 }
2976
2977
2978 /*
2979
2980 join : {
2981         acn : {
2982                 field : record,
2983                 fkey : id
2984                 type : left
2985                 filter_op : or
2986                 filter : { ... },
2987                 join : {
2988                         acp : {
2989                                 field : call_number,
2990                                 fkey : id,
2991                                 filter : { ... },
2992                         },
2993                 },
2994         },
2995         mrd : {
2996                 field : record,
2997                 type : inner
2998                 fkey : id,
2999                 filter : { ... },
3000         }
3001 }
3002
3003 */
3004
3005 static char* searchJOIN( const jsonObject* join_hash, const ClassInfo* left_info ) {
3006
3007         const jsonObject* working_hash;
3008         jsonObject* freeable_hash = NULL;
3009
3010         if( join_hash->type == JSON_HASH ) {
3011                 working_hash = join_hash;
3012         } else if( join_hash->type == JSON_STRING ) {
3013                 // turn it into a JSON_HASH by creating a wrapper
3014                 // around a copy of the original
3015                 const char* _tmp = jsonObjectGetString( join_hash );
3016                 freeable_hash = jsonNewObjectType( JSON_HASH );
3017                 jsonObjectSetKey( freeable_hash, _tmp, NULL );
3018                 working_hash = freeable_hash;
3019         } else {
3020                 osrfLogError(
3021                         OSRF_LOG_MARK,
3022                         "%s: JOIN failed; expected JSON object type not found",
3023                         modulename
3024                 );
3025                 return NULL;
3026         }
3027
3028         growing_buffer* join_buf = buffer_init( 128 );
3029         const char* leftclass = left_info->class_name;
3030
3031         jsonObject* snode = NULL;
3032         jsonIterator* search_itr = jsonNewIterator( working_hash );
3033
3034         while ( (snode = jsonIteratorNext( search_itr )) ) {
3035                 const char* right_alias = search_itr->key;
3036                 const char* class =
3037                                 jsonObjectGetString( jsonObjectGetKeyConst( snode, "class" ) );
3038                 if( ! class )
3039                         class = right_alias;
3040
3041                 const ClassInfo* right_info = add_joined_class( right_alias, class );
3042                 if( !right_info ) {
3043                         osrfLogError(
3044                                 OSRF_LOG_MARK,
3045                                 "%s: JOIN failed.  Class \"%s\" not resolved in IDL",
3046                                 modulename,
3047                                 search_itr->key
3048                         );
3049                         jsonIteratorFree( search_itr );
3050                         buffer_free( join_buf );
3051                         if( freeable_hash )
3052                                 jsonObjectFree( freeable_hash );
3053                         return NULL;
3054                 }
3055                 osrfHash* links    = right_info->links;
3056                 const char* table  = right_info->source_def;
3057
3058                 const char* fkey  = jsonObjectGetString( jsonObjectGetKeyConst( snode, "fkey" ) );
3059                 const char* field = jsonObjectGetString( jsonObjectGetKeyConst( snode, "field" ) );
3060
3061                 if( field && !fkey ) {
3062                         // Look up the corresponding join column in the IDL.
3063                         // The link must be defined in the child table,
3064                         // and point to the right parent table.
3065                         osrfHash* idl_link = (osrfHash*) osrfHashGet( links, field );
3066                         const char* reltype = NULL;
3067                         const char* other_class = NULL;
3068                         reltype = osrfHashGet( idl_link, "reltype" );
3069                         if( reltype && strcmp( reltype, "has_many" ) )
3070                                 other_class = osrfHashGet( idl_link, "class" );
3071                         if( other_class && !strcmp( other_class, leftclass ) )
3072                                 fkey = osrfHashGet( idl_link, "key" );
3073                         if( !fkey ) {
3074                                 osrfLogError(
3075                                         OSRF_LOG_MARK,
3076                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
3077                                         modulename,
3078                                         class,
3079                                         field,
3080                                         leftclass
3081                                 );
3082                                 buffer_free( join_buf );
3083                                 if( freeable_hash )
3084                                         jsonObjectFree( freeable_hash );
3085                                 jsonIteratorFree( search_itr );
3086                                 return NULL;
3087                         }
3088
3089                 } else if( !field && fkey ) {
3090                         // Look up the corresponding join column in the IDL.
3091                         // The link must be defined in the child table,
3092                         // and point to the right parent table.
3093                         osrfHash* left_links = left_info->links;
3094                         osrfHash* idl_link = (osrfHash*) osrfHashGet( left_links, fkey );
3095                         const char* reltype = NULL;
3096                         const char* other_class = NULL;
3097                         reltype = osrfHashGet( idl_link, "reltype" );
3098                         if( reltype && strcmp( reltype, "has_many" ) )
3099                                 other_class = osrfHashGet( idl_link, "class" );
3100                         if( other_class && !strcmp( other_class, class ) )
3101                                 field = osrfHashGet( idl_link, "key" );
3102                         if( !field ) {
3103                                 osrfLogError(
3104                                         OSRF_LOG_MARK,
3105                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
3106                                         modulename,
3107                                         leftclass,
3108                                         fkey,
3109                                         class
3110                                 );
3111                                 buffer_free( join_buf );
3112                                 if( freeable_hash )
3113                                         jsonObjectFree( freeable_hash );
3114                                 jsonIteratorFree( search_itr );
3115                                 return NULL;
3116                         }
3117
3118                 } else if( !field && !fkey ) {
3119                         osrfHash* left_links = left_info->links;
3120
3121                         // For each link defined for the left class:
3122                         // see if the link references the joined class
3123                         osrfHashIterator* itr = osrfNewHashIterator( left_links );
3124                         osrfHash* curr_link = NULL;
3125                         while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
3126                                 const char* other_class = osrfHashGet( curr_link, "class" );
3127                                 if( other_class && !strcmp( other_class, class ) ) {
3128
3129                                         // In the IDL, the parent class doesn't always know then names of the child
3130                                         // columns that are pointing to it, so don't use that end of the link
3131                                         const char* reltype = osrfHashGet( curr_link, "reltype" );
3132                                         if( reltype && strcmp( reltype, "has_many" ) ) {
3133                                                 // Found a link between the classes
3134                                                 fkey = osrfHashIteratorKey( itr );
3135                                                 field = osrfHashGet( curr_link, "key" );
3136                                                 break;
3137                                         }
3138                                 }
3139                         }
3140                         osrfHashIteratorFree( itr );
3141
3142                         if( !field || !fkey ) {
3143                                 // Do another such search, with the classes reversed
3144
3145                                 // For each link defined for the joined class:
3146                                 // see if the link references the left class
3147                                 osrfHashIterator* itr = osrfNewHashIterator( links );
3148                                 osrfHash* curr_link = NULL;
3149                                 while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
3150                                         const char* other_class = osrfHashGet( curr_link, "class" );
3151                                         if( other_class && !strcmp( other_class, leftclass ) ) {
3152
3153                                                 // In the IDL, the parent class doesn't know then names of the child
3154                                                 // columns that are pointing to it, so don't use that end of the link
3155                                                 const char* reltype = osrfHashGet( curr_link, "reltype" );
3156                                                 if( reltype && strcmp( reltype, "has_many" ) ) {
3157                                                         // Found a link between the classes
3158                                                         field = osrfHashIteratorKey( itr );
3159                                                         fkey = osrfHashGet( curr_link, "key" );
3160                                                         break;
3161                                                 }
3162                                         }
3163                                 }
3164                                 osrfHashIteratorFree( itr );
3165                         }
3166
3167                         if( !field || !fkey ) {
3168                                 osrfLogError(
3169                                         OSRF_LOG_MARK,
3170                                         "%s: JOIN failed.  No link defined between %s and %s",
3171                                         modulename,
3172                                         leftclass,
3173                                         class
3174                                 );
3175                                 buffer_free( join_buf );
3176                                 if( freeable_hash )
3177                                         jsonObjectFree( freeable_hash );
3178                                 jsonIteratorFree( search_itr );
3179                                 return NULL;
3180                         }
3181                 }
3182
3183                 const char* type = jsonObjectGetString( jsonObjectGetKeyConst( snode, "type" ) );
3184                 if( type ) {
3185                         if( !strcasecmp( type,"left" )) {
3186                                 buffer_add( join_buf, " LEFT JOIN" );
3187                         } else if( !strcasecmp( type,"right" )) {
3188                                 buffer_add( join_buf, " RIGHT JOIN" );
3189                         } else if( !strcasecmp( type,"full" )) {
3190                                 buffer_add( join_buf, " FULL JOIN" );
3191                         } else {
3192                                 buffer_add( join_buf, " INNER JOIN" );
3193                         }
3194                 } else {
3195                         buffer_add( join_buf, " INNER JOIN" );
3196                 }
3197
3198                 buffer_fadd( join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s",
3199                                         table, right_alias, right_alias, field, left_info->alias, fkey );
3200
3201                 // Add any other join conditions as specified by "filter"
3202                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
3203                 if( filter ) {
3204                         const char* filter_op = jsonObjectGetString(
3205                                 jsonObjectGetKeyConst( snode, "filter_op" ) );
3206                         if( filter_op && !strcasecmp( "or",filter_op )) {
3207                                 buffer_add( join_buf, " OR " );
3208                         } else {
3209                                 buffer_add( join_buf, " AND " );
3210                         }
3211
3212                         char* jpred = searchWHERE( filter, right_info, AND_OP_JOIN, NULL );
3213                         if( jpred ) {
3214                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
3215                                 OSRF_BUFFER_ADD( join_buf, jpred );
3216                                 free( jpred );
3217                         } else {
3218                                 osrfLogError(
3219                                         OSRF_LOG_MARK,
3220                                         "%s: JOIN failed.  Invalid conditional expression.",
3221                                         modulename
3222                                 );
3223                                 jsonIteratorFree( search_itr );
3224                                 buffer_free( join_buf );
3225                                 if( freeable_hash )
3226                                         jsonObjectFree( freeable_hash );
3227                                 return NULL;
3228                         }
3229                 }
3230
3231                 buffer_add( join_buf, " ) " );
3232
3233                 // Recursively add a nested join, if one is present
3234                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
3235                 if( join_filter ) {
3236                         char* jpred = searchJOIN( join_filter, right_info );
3237                         if( jpred ) {
3238                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
3239                                 OSRF_BUFFER_ADD( join_buf, jpred );
3240                                 free( jpred );
3241                         } else {
3242                                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid nested join.", modulename );
3243                                 jsonIteratorFree( search_itr );
3244                                 buffer_free( join_buf );
3245                                 if( freeable_hash )
3246                                         jsonObjectFree( freeable_hash );
3247                                 return NULL;
3248                         }
3249                 }
3250         }
3251
3252         if( freeable_hash )
3253                 jsonObjectFree( freeable_hash );
3254         jsonIteratorFree( search_itr );
3255
3256         return buffer_release( join_buf );
3257 }
3258
3259 /*
3260
3261 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
3262 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
3263 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
3264
3265 Generate code to express a set of conditions, as for a WHERE clause.  Parameters:
3266
3267 search_hash is the JSON expression of the conditions.
3268 meta is the class definition from the IDL, for the relevant table.
3269 opjoin_type indicates whether multiple conditions, if present, should be
3270         connected by AND or OR.
3271 osrfMethodContext is loaded with all sorts of stuff, but all we do with it here is
3272         to pass it to other functions -- and all they do with it is to use the session
3273         and request members to send error messages back to the client.
3274
3275 */
3276
3277 static char* searchWHERE( const jsonObject* search_hash, const ClassInfo* class_info,
3278                 int opjoin_type, osrfMethodContext* ctx ) {
3279
3280         osrfLogDebug(
3281                 OSRF_LOG_MARK,
3282                 "%s: Entering searchWHERE; search_hash addr = %p, meta addr = %p, "
3283                 "opjoin_type = %d, ctx addr = %p",
3284                 modulename,
3285                 search_hash,
3286                 class_info->class_def,
3287                 opjoin_type,
3288                 ctx
3289         );
3290
3291         growing_buffer* sql_buf = buffer_init( 128 );
3292
3293         jsonObject* node = NULL;
3294
3295         int first = 1;
3296         if( search_hash->type == JSON_ARRAY ) {
3297                 if( 0 == search_hash->size ) {
3298                         osrfLogError(
3299                                 OSRF_LOG_MARK,
3300                                 "%s: Invalid predicate structure: empty JSON array",
3301                                 modulename
3302                         );
3303                         buffer_free( sql_buf );
3304                         return NULL;
3305                 }
3306
3307                 unsigned long i = 0;
3308                 while(( node = jsonObjectGetIndex( search_hash, i++ ) )) {
3309                         if( first ) {
3310                                 first = 0;
3311                         } else {
3312                                 if( opjoin_type == OR_OP_JOIN )
3313                                         buffer_add( sql_buf, " OR " );
3314                                 else
3315                                         buffer_add( sql_buf, " AND " );
3316                         }
3317
3318                         char* subpred = searchWHERE( node, class_info, opjoin_type, ctx );
3319                         if( ! subpred ) {
3320                                 buffer_free( sql_buf );
3321                                 return NULL;
3322                         }
3323
3324                         buffer_fadd( sql_buf, "( %s )", subpred );
3325                         free( subpred );
3326                 }
3327
3328         } else if( search_hash->type == JSON_HASH ) {
3329                 osrfLogDebug( OSRF_LOG_MARK,
3330                         "%s: In WHERE clause, condition type is JSON_HASH", modulename );
3331                 jsonIterator* search_itr = jsonNewIterator( search_hash );
3332                 if( !jsonIteratorHasNext( search_itr ) ) {
3333                         osrfLogError(
3334                                 OSRF_LOG_MARK,
3335                                 "%s: Invalid predicate structure: empty JSON object",
3336                                 modulename
3337                         );
3338                         jsonIteratorFree( search_itr );
3339                         buffer_free( sql_buf );
3340                         return NULL;
3341                 }
3342
3343                 while( (node = jsonIteratorNext( search_itr )) ) {
3344
3345                         if( first ) {
3346                                 first = 0;
3347                         } else {
3348                                 if( opjoin_type == OR_OP_JOIN )
3349                                         buffer_add( sql_buf, " OR " );
3350                                 else
3351                                         buffer_add( sql_buf, " AND " );
3352                         }
3353
3354                         if( '+' == search_itr->key[ 0 ] ) {
3355
3356                                 // This plus sign prefixes a class name or other table alias;
3357                                 // make sure the table alias is in scope
3358                                 ClassInfo* alias_info = search_all_alias( search_itr->key + 1 );
3359                                 if( ! alias_info ) {
3360                                         osrfLogError(
3361                                                          OSRF_LOG_MARK,
3362                                                         "%s: Invalid table alias \"%s\" in WHERE clause",
3363                                                         modulename,
3364                                                         search_itr->key + 1
3365                                         );
3366                                         jsonIteratorFree( search_itr );
3367                                         buffer_free( sql_buf );
3368                                         return NULL;
3369                                 }
3370
3371                                 if( node->type == JSON_STRING ) {
3372                                         // It's the name of a column; make sure it belongs to the class
3373                                         const char* fieldname = jsonObjectGetString( node );
3374                                         if( ! osrfHashGet( alias_info->fields, fieldname ) ) {
3375                                                 osrfLogError(
3376                                                         OSRF_LOG_MARK,
3377                                                         "%s: Invalid column name \"%s\" in WHERE clause "
3378                                                         "for table alias \"%s\"",
3379                                                         modulename,
3380                                                         fieldname,
3381                                                         alias_info->alias
3382                                                 );
3383                                                 jsonIteratorFree( search_itr );
3384                                                 buffer_free( sql_buf );
3385                                                 return NULL;
3386                                         }
3387
3388                                         buffer_fadd( sql_buf, " \"%s\".%s ", alias_info->alias, fieldname );
3389                                 } else {
3390                                         // It's something more complicated
3391                                         char* subpred = searchWHERE( node, alias_info, AND_OP_JOIN, ctx );
3392                                         if( ! subpred ) {
3393                                                 jsonIteratorFree( search_itr );
3394                                                 buffer_free( sql_buf );
3395                                                 return NULL;
3396                                         }
3397
3398                                         buffer_fadd( sql_buf, "( %s )", subpred );
3399                                         free( subpred );
3400                                 }
3401                         } else if( '-' == search_itr->key[ 0 ] ) {
3402                                 if( !strcasecmp( "-or", search_itr->key )) {
3403                                         char* subpred = searchWHERE( node, class_info, OR_OP_JOIN, ctx );
3404                                         if( ! subpred ) {
3405                                                 jsonIteratorFree( search_itr );
3406                                                 buffer_free( sql_buf );
3407                                                 return NULL;
3408                                         }
3409
3410                                         buffer_fadd( sql_buf, "( %s )", subpred );
3411                                         free( subpred );
3412                                 } else if( !strcasecmp( "-and", search_itr->key )) {
3413                                         char* subpred = searchWHERE( node, class_info, AND_OP_JOIN, ctx );
3414                                         if( ! subpred ) {
3415                                                 jsonIteratorFree( search_itr );
3416                                                 buffer_free( sql_buf );
3417                                                 return NULL;
3418                                         }
3419
3420                                         buffer_fadd( sql_buf, "( %s )", subpred );
3421                                         free( subpred );
3422                                 } else if( !strcasecmp("-not",search_itr->key) ) {
3423                                         char* subpred = searchWHERE( node, class_info, AND_OP_JOIN, ctx );
3424                                         if( ! subpred ) {
3425                                                 jsonIteratorFree( search_itr );
3426                                                 buffer_free( sql_buf );
3427                                                 return NULL;
3428                                         }
3429
3430                                         buffer_fadd( sql_buf, " NOT ( %s )", subpred );
3431                                         free( subpred );
3432                                 } else if( !strcasecmp( "-exists", search_itr->key )) {
3433                                         char* subpred = buildQuery( ctx, node, SUBSELECT );
3434                                         if( ! subpred ) {
3435                                                 jsonIteratorFree( search_itr );
3436                                                 buffer_free( sql_buf );
3437                                                 return NULL;
3438                                         }
3439
3440                                         buffer_fadd( sql_buf, "EXISTS ( %s )", subpred );
3441                                         free( subpred );
3442                                 } else if( !strcasecmp("-not-exists", search_itr->key )) {
3443                                         char* subpred = buildQuery( ctx, node, SUBSELECT );
3444                                         if( ! subpred ) {
3445                                                 jsonIteratorFree( search_itr );
3446                                                 buffer_free( sql_buf );
3447                                                 return NULL;
3448                                         }
3449
3450                                         buffer_fadd( sql_buf, "NOT EXISTS ( %s )", subpred );
3451                                         free( subpred );
3452                                 } else {     // Invalid "minus" operator
3453                                         osrfLogError(
3454                                                          OSRF_LOG_MARK,
3455                                                         "%s: Invalid operator \"%s\" in WHERE clause",
3456                                                         modulename,
3457                                                         search_itr->key
3458                                         );
3459                                         jsonIteratorFree( search_itr );
3460                                         buffer_free( sql_buf );
3461                                         return NULL;
3462                                 }
3463
3464                         } else {
3465
3466                                 const char* class = class_info->class_name;
3467                                 osrfHash* fields = class_info->fields;
3468                                 osrfHash* field = osrfHashGet( fields, search_itr->key );
3469
3470                                 if( !field ) {
3471                                         const char* table = class_info->source_def;
3472                                         osrfLogError(
3473                                                 OSRF_LOG_MARK,
3474                                                 "%s: Attempt to reference non-existent column \"%s\" on %s (%s)",
3475                                                 modulename,
3476                                                 search_itr->key,
3477                                                 table ? table : "?",
3478                                                 class ? class : "?"
3479                                         );
3480                                         jsonIteratorFree( search_itr );
3481                                         buffer_free( sql_buf );
3482                                         return NULL;
3483                                 }
3484
3485                                 char* subpred = searchPredicate( class_info, field, node, ctx );
3486                                 if( ! subpred ) {
3487                                         buffer_free( sql_buf );
3488                                         jsonIteratorFree( search_itr );
3489                                         return NULL;
3490                                 }
3491
3492                                 buffer_add( sql_buf, subpred );
3493                                 free( subpred );
3494                         }
3495                 }
3496                 jsonIteratorFree( search_itr );
3497
3498         } else {
3499                 // ERROR ... only hash and array allowed at this level
3500                 char* predicate_string = jsonObjectToJSON( search_hash );
3501                 osrfLogError(
3502                         OSRF_LOG_MARK,
3503                         "%s: Invalid predicate structure: %s",
3504                         modulename,
3505                         predicate_string
3506                 );
3507                 buffer_free( sql_buf );
3508                 free( predicate_string );
3509                 return NULL;
3510         }
3511
3512         return buffer_release( sql_buf );
3513 }
3514
3515 /* Build a JSON_ARRAY of field names for a given table alias
3516 */
3517 static jsonObject* defaultSelectList( const char* table_alias ) {
3518
3519         if( ! table_alias )
3520                 table_alias = "";
3521
3522         ClassInfo* class_info = search_all_alias( table_alias );
3523         if( ! class_info ) {
3524                 osrfLogError(
3525                         OSRF_LOG_MARK,
3526                         "%s: Can't build default SELECT clause for \"%s\"; no such table alias",
3527                         modulename,
3528                         table_alias
3529                 );
3530                 return NULL;
3531         }
3532
3533         jsonObject* array = jsonNewObjectType( JSON_ARRAY );
3534         osrfHash* field_def = NULL;
3535         osrfHashIterator* field_itr = osrfNewHashIterator( class_info->fields );
3536         while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
3537                 const char* field_name = osrfHashIteratorKey( field_itr );
3538                 if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
3539                         jsonObjectPush( array, jsonNewObject( field_name ) );
3540                 }
3541         }
3542         osrfHashIteratorFree( field_itr );
3543
3544         return array;
3545 }
3546
3547 // Translate a jsonObject into a UNION, INTERSECT, or EXCEPT query.
3548 // The jsonObject must be a JSON_HASH with an single entry for "union",
3549 // "intersect", or "except".  The data associated with this key must be an
3550 // array of hashes, each hash being a query.
3551 // Also allowed but currently ignored: entries for "order_by" and "alias".
3552 static char* doCombo( osrfMethodContext* ctx, jsonObject* combo, int flags ) {
3553         // Sanity check
3554         if( ! combo || combo->type != JSON_HASH )
3555                 return NULL;      // should be impossible; validated by caller
3556
3557         const jsonObject* query_array = NULL;   // array of subordinate queries
3558         const char* op = NULL;     // name of operator, e.g. UNION
3559         const char* alias = NULL;  // alias for the query (needed for ORDER BY)
3560         int op_count = 0;          // for detecting conflicting operators
3561         int excepting = 0;         // boolean
3562         int all = 0;               // boolean
3563         jsonObject* order_obj = NULL;
3564
3565         // Identify the elements in the hash
3566         jsonIterator* query_itr = jsonNewIterator( combo );
3567         jsonObject* curr_obj = NULL;
3568         while( (curr_obj = jsonIteratorNext( query_itr ) ) ) {
3569                 if( ! strcmp( "union", query_itr->key ) ) {
3570                         ++op_count;
3571                         op = " UNION ";
3572                         query_array = curr_obj;
3573                 } else if( ! strcmp( "intersect", query_itr->key ) ) {
3574                         ++op_count;
3575                         op = " INTERSECT ";
3576                         query_array = curr_obj;
3577                 } else if( ! strcmp( "except", query_itr->key ) ) {
3578                         ++op_count;
3579                         op = " EXCEPT ";
3580                         excepting = 1;
3581                         query_array = curr_obj;
3582                 } else if( ! strcmp( "order_by", query_itr->key ) ) {
3583                         osrfLogWarning(
3584                                 OSRF_LOG_MARK,
3585                                 "%s: ORDER BY not supported for UNION, INTERSECT, or EXCEPT",
3586                                 modulename
3587                         );
3588                         order_obj = curr_obj;
3589                 } else if( ! strcmp( "alias", query_itr->key ) ) {
3590                         if( curr_obj->type != JSON_STRING ) {
3591                                 jsonIteratorFree( query_itr );
3592                                 return NULL;
3593                         }
3594                         alias = jsonObjectGetString( curr_obj );
3595                 } else if( ! strcmp( "all", query_itr->key ) ) {
3596                         if( obj_is_true( curr_obj ) )
3597                                 all = 1;
3598                 } else {
3599                         if( ctx )
3600                                 osrfAppSessionStatus(
3601                                         ctx->session,
3602                                         OSRF_STATUS_INTERNALSERVERERROR,
3603                                         "osrfMethodException",
3604                                         ctx->request,
3605                                         "Malformed query; unexpected entry in query object"
3606                                 );
3607                         osrfLogError(
3608                                 OSRF_LOG_MARK,
3609                                 "%s: Unexpected entry for \"%s\" in%squery",
3610                                 modulename,
3611                                 query_itr->key,
3612                                 op
3613                         );
3614                         jsonIteratorFree( query_itr );
3615                         return NULL;
3616                 }
3617         }
3618         jsonIteratorFree( query_itr );
3619
3620         // More sanity checks
3621         if( ! query_array ) {
3622                 if( ctx )
3623                         osrfAppSessionStatus(
3624                                 ctx->session,
3625                                 OSRF_STATUS_INTERNALSERVERERROR,
3626                                 "osrfMethodException",
3627                                 ctx->request,
3628                                 "Expected UNION, INTERSECT, or EXCEPT operator not found"
3629                         );
3630                 osrfLogError(
3631                         OSRF_LOG_MARK,
3632                         "%s: Expected UNION, INTERSECT, or EXCEPT operator not found",
3633                         modulename
3634                 );
3635                 return NULL;        // should be impossible...
3636         } else if( op_count > 1 ) {
3637                 if( ctx )
3638                                 osrfAppSessionStatus(
3639                                 ctx->session,
3640                                 OSRF_STATUS_INTERNALSERVERERROR,
3641                                 "osrfMethodException",
3642                                 ctx->request,
3643                                 "Found more than one of UNION, INTERSECT, and EXCEPT in same query"
3644                         );
3645                 osrfLogError(
3646                         OSRF_LOG_MARK,
3647                         "%s: Found more than one of UNION, INTERSECT, and EXCEPT in same query",
3648                         modulename
3649                 );
3650                 return NULL;
3651         } if( query_array->type != JSON_ARRAY ) {
3652                 if( ctx )
3653                                 osrfAppSessionStatus(
3654                                 ctx->session,
3655                                 OSRF_STATUS_INTERNALSERVERERROR,
3656                                 "osrfMethodException",
3657                                 ctx->request,
3658                                 "Malformed query: expected array of queries under UNION, INTERSECT or EXCEPT"
3659                         );
3660                 osrfLogError(
3661                         OSRF_LOG_MARK,
3662                         "%s: Expected JSON_ARRAY of queries for%soperator; found %s",
3663                         modulename,
3664                         op,
3665                         json_type( query_array->type )
3666                 );
3667                 return NULL;
3668         } if( query_array->size < 2 ) {
3669                 if( ctx )
3670                         osrfAppSessionStatus(
3671                                 ctx->session,
3672                                 OSRF_STATUS_INTERNALSERVERERROR,
3673                                 "osrfMethodException",
3674                                 ctx->request,
3675                                 "UNION, INTERSECT or EXCEPT requires multiple queries as operands"
3676                         );
3677                 osrfLogError(
3678                         OSRF_LOG_MARK,
3679                         "%s:%srequires multiple queries as operands",
3680                         modulename,
3681                         op
3682                 );
3683                 return NULL;
3684         } else if( excepting && query_array->size > 2 ) {
3685                 if( ctx )
3686                         osrfAppSessionStatus(
3687                                 ctx->session,
3688                                 OSRF_STATUS_INTERNALSERVERERROR,
3689                                 "osrfMethodException",
3690                                 ctx->request,
3691                                 "EXCEPT operator has too many queries as operands"
3692                         );
3693                 osrfLogError(
3694                         OSRF_LOG_MARK,
3695                         "%s:EXCEPT operator has too many queries as operands",
3696                         modulename
3697                 );
3698                 return NULL;
3699         } else if( order_obj && ! alias ) {
3700                 if( ctx )
3701                         osrfAppSessionStatus(
3702                                 ctx->session,
3703                                 OSRF_STATUS_INTERNALSERVERERROR,
3704                                 "osrfMethodException",
3705                                 ctx->request,
3706                                 "ORDER BY requires an alias for a UNION, INTERSECT, or EXCEPT"
3707                         );
3708                 osrfLogError(
3709                         OSRF_LOG_MARK,
3710                         "%s:ORDER BY requires an alias for a UNION, INTERSECT, or EXCEPT",
3711                         modulename
3712                 );
3713                 return NULL;
3714         }
3715
3716         // So far so good.  Now build the SQL.
3717         growing_buffer* sql = buffer_init( 256 );
3718
3719         // If we nested inside another UNION, INTERSECT, or EXCEPT,
3720         // Add a layer of parentheses
3721         if( flags & SUBCOMBO )
3722                 OSRF_BUFFER_ADD( sql, "( " );
3723
3724         // Traverse the query array.  Each entry should be a hash.
3725         int first = 1;   // boolean
3726         int i = 0;
3727         jsonObject* query = NULL;
3728         while( (query = jsonObjectGetIndex( query_array, i++ )) ) {
3729                 if( query->type != JSON_HASH ) {
3730                         if( ctx )
3731                                 osrfAppSessionStatus(
3732                                         ctx->session,
3733                                         OSRF_STATUS_INTERNALSERVERERROR,
3734                                         "osrfMethodException",
3735                                         ctx->request,
3736                                         "Malformed query under UNION, INTERSECT or EXCEPT"
3737                                 );
3738                         osrfLogError(
3739                                 OSRF_LOG_MARK,
3740                                 "%s: Malformed query under%s -- expected JSON_HASH, found %s",
3741                                 modulename,
3742                                 op,
3743                                 json_type( query->type )
3744                         );
3745                         buffer_free( sql );
3746                         return NULL;
3747                 }
3748
3749                 if( first )
3750                         first = 0;
3751                 else {
3752                         OSRF_BUFFER_ADD( sql, op );
3753                         if( all )
3754                                 OSRF_BUFFER_ADD( sql, "ALL " );
3755                 }
3756
3757                 char* query_str = buildQuery( ctx, query, SUBSELECT | SUBCOMBO );
3758                 if( ! query_str ) {
3759                         osrfLogError(
3760                                 OSRF_LOG_MARK,
3761                                 "%s: Error building query under%s",
3762                                 modulename,
3763                                 op
3764                         );
3765                         buffer_free( sql );
3766                         return NULL;
3767                 }
3768
3769                 OSRF_BUFFER_ADD( sql, query_str );
3770         }
3771
3772         if( flags & SUBCOMBO )
3773                 OSRF_BUFFER_ADD_CHAR( sql, ')' );
3774
3775         if( !(flags & SUBSELECT) )
3776                 OSRF_BUFFER_ADD_CHAR( sql, ';' );
3777
3778         return buffer_release( sql );
3779 }
3780
3781 // Translate a jsonObject into a SELECT, UNION, INTERSECT, or EXCEPT query.
3782 // The jsonObject must be a JSON_HASH with an entry for "from", "union", "intersect",
3783 // or "except" to indicate the type of query.
3784 char* buildQuery( osrfMethodContext* ctx, jsonObject* query, int flags ) {
3785         // Sanity checks
3786         if( ! query ) {
3787                 if( ctx )
3788                         osrfAppSessionStatus(
3789                                 ctx->session,
3790                                 OSRF_STATUS_INTERNALSERVERERROR,
3791                                 "osrfMethodException",
3792                                 ctx->request,
3793                                 "Malformed query; no query object"
3794                         );
3795                 osrfLogError( OSRF_LOG_MARK, "%s: Null pointer to query object", modulename );
3796                 return NULL;
3797         } else if( query->type != JSON_HASH ) {
3798                 if( ctx )
3799                         osrfAppSessionStatus(
3800                                 ctx->session,
3801                                 OSRF_STATUS_INTERNALSERVERERROR,
3802                                 "osrfMethodException",
3803                                 ctx->request,
3804                                 "Malformed query object"
3805                         );
3806                 osrfLogError(
3807                         OSRF_LOG_MARK,
3808                         "%s: Query object is %s instead of JSON_HASH",
3809                         modulename,
3810                         json_type( query->type )
3811                 );
3812                 return NULL;
3813         }
3814
3815         // Determine what kind of query it purports to be, and dispatch accordingly.
3816         if( jsonObjectGetKeyConst( query, "union" ) ||
3817                 jsonObjectGetKeyConst( query, "intersect" ) ||
3818                 jsonObjectGetKeyConst( query, "except" )) {
3819                 return doCombo( ctx, query, flags );
3820         } else {
3821                 // It is presumably a SELECT query
3822
3823                 // Push a node onto the stack for the current query.  Every level of
3824                 // subquery gets its own QueryFrame on the Stack.
3825                 push_query_frame();
3826
3827                 // Build an SQL SELECT statement
3828                 char* sql = SELECT(
3829                         ctx,
3830                         jsonObjectGetKey( query, "select" ),
3831                         jsonObjectGetKeyConst( query, "from" ),
3832                         jsonObjectGetKeyConst( query, "where" ),
3833                         jsonObjectGetKeyConst( query, "having" ),
3834                         jsonObjectGetKeyConst( query, "order_by" ),
3835                         jsonObjectGetKeyConst( query, "limit" ),
3836                         jsonObjectGetKeyConst( query, "offset" ),
3837                         flags
3838                 );
3839                 pop_query_frame();
3840                 return sql;
3841         }
3842 }
3843
3844 char* SELECT (
3845                 /* method context */ osrfMethodContext* ctx,
3846
3847                 /* SELECT   */ jsonObject* selhash,
3848                 /* FROM     */ const jsonObject* join_hash,
3849                 /* WHERE    */ const jsonObject* search_hash,
3850                 /* HAVING   */ const jsonObject* having_hash,
3851                 /* ORDER BY */ const jsonObject* order_hash,
3852                 /* LIMIT    */ const jsonObject* limit,
3853                 /* OFFSET   */ const jsonObject* offset,
3854                 /* flags    */ int flags
3855 ) {
3856         const char* locale = osrf_message_get_last_locale();
3857
3858         // general tmp objects
3859         const jsonObject* tmp_const;
3860         jsonObject* selclass = NULL;
3861         jsonObject* snode = NULL;
3862         jsonObject* onode = NULL;
3863
3864         char* string = NULL;
3865         int from_function = 0;
3866         int first = 1;
3867         int gfirst = 1;
3868         //int hfirst = 1;
3869
3870         osrfLogDebug(OSRF_LOG_MARK, "cstore SELECT locale: %s", locale ? locale : "(none)" );
3871
3872         // punt if there's no FROM clause
3873         if( !join_hash || ( join_hash->type == JSON_HASH && !join_hash->size )) {
3874                 osrfLogError(
3875                         OSRF_LOG_MARK,
3876                         "%s: FROM clause is missing or empty",
3877                         modulename
3878                 );
3879                 if( ctx )
3880                         osrfAppSessionStatus(
3881                                 ctx->session,
3882                                 OSRF_STATUS_INTERNALSERVERERROR,
3883                                 "osrfMethodException",
3884                                 ctx->request,
3885                                 "FROM clause is missing or empty in JSON query"
3886                         );
3887                 return NULL;
3888         }
3889
3890         // the core search class
3891         const char* core_class = NULL;
3892
3893         // get the core class -- the only key of the top level FROM clause, or a string
3894         if( join_hash->type == JSON_HASH ) {
3895                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
3896                 snode = jsonIteratorNext( tmp_itr );
3897
3898                 // Populate the current QueryFrame with information
3899                 // about the core class
3900                 if( add_query_core( NULL, tmp_itr->key ) ) {
3901                         if( ctx )
3902                                 osrfAppSessionStatus(
3903                                         ctx->session,
3904                                         OSRF_STATUS_INTERNALSERVERERROR,
3905                                         "osrfMethodException",
3906                                         ctx->request,
3907                                         "Unable to look up core class"
3908                                 );
3909                         return NULL;
3910                 }
3911                 core_class = curr_query->core.class_name;
3912                 join_hash = snode;
3913
3914                 jsonObject* extra = jsonIteratorNext( tmp_itr );
3915
3916                 jsonIteratorFree( tmp_itr );
3917                 snode = NULL;
3918
3919                 // There shouldn't be more than one entry in join_hash
3920                 if( extra ) {
3921                         osrfLogError(
3922                                 OSRF_LOG_MARK,
3923                                 "%s: Malformed FROM clause: extra entry in JSON_HASH",
3924                                 modulename
3925                         );
3926                         if( ctx )
3927                                 osrfAppSessionStatus(
3928                                         ctx->session,
3929                                         OSRF_STATUS_INTERNALSERVERERROR,
3930                                         "osrfMethodException",
3931                                         ctx->request,
3932                                         "Malformed FROM clause in JSON query"
3933                                 );
3934                         return NULL;    // Malformed join_hash; extra entry
3935                 }
3936         } else if( join_hash->type == JSON_ARRAY ) {
3937                 // We're selecting from a function, not from a table
3938                 from_function = 1;
3939                 core_class = jsonObjectGetString( jsonObjectGetIndex( join_hash, 0 ));
3940                 selhash = NULL;
3941
3942         } else if( join_hash->type == JSON_STRING ) {
3943                 // Populate the current QueryFrame with information
3944                 // about the core class
3945                 core_class = jsonObjectGetString( join_hash );
3946                 join_hash = NULL;
3947                 if( add_query_core( NULL, core_class ) ) {
3948                         if( ctx )
3949                                 osrfAppSessionStatus(
3950                                         ctx->session,
3951                                         OSRF_STATUS_INTERNALSERVERERROR,
3952                                         "osrfMethodException",
3953                                         ctx->request,
3954                                         "Unable to look up core class"
3955                                 );
3956                         return NULL;
3957                 }
3958         }
3959         else {
3960                 osrfLogError(
3961                         OSRF_LOG_MARK,
3962                         "%s: FROM clause is unexpected JSON type: %s",
3963                         modulename,
3964                         json_type( join_hash->type )
3965                 );
3966                 if( ctx )
3967                         osrfAppSessionStatus(
3968                                 ctx->session,
3969                                 OSRF_STATUS_INTERNALSERVERERROR,
3970                                 "osrfMethodException",
3971                                 ctx->request,
3972                                 "Ill-formed FROM clause in JSON query"
3973                         );
3974                 return NULL;
3975         }
3976
3977         // Build the join clause, if any, while filling out the list
3978         // of joined classes in the current QueryFrame.
3979         char* join_clause = NULL;
3980         if( join_hash && ! from_function ) {
3981
3982                 join_clause = searchJOIN( join_hash, &curr_query->core );
3983                 if( ! join_clause ) {
3984                         if( ctx )
3985                                 osrfAppSessionStatus(
3986                                         ctx->session,
3987                                         OSRF_STATUS_INTERNALSERVERERROR,
3988                                         "osrfMethodException",
3989                                         ctx->request,
3990                                         "Unable to construct JOIN clause(s)"
3991                                 );
3992                         return NULL;
3993                 }
3994         }
3995
3996         // For in case we don't get a select list
3997         jsonObject* defaultselhash = NULL;
3998
3999         // if there is no select list, build a default select list ...
4000         if( !selhash && !from_function ) {
4001                 jsonObject* default_list = defaultSelectList( core_class );
4002                 if( ! default_list ) {
4003                         if( ctx ) {
4004                                 osrfAppSessionStatus(
4005                                         ctx->session,
4006                                         OSRF_STATUS_INTERNALSERVERERROR,
4007                                         "osrfMethodException",
4008                                         ctx->request,
4009                                         "Unable to build default SELECT clause in JSON query"
4010                                 );
4011                                 free( join_clause );
4012                                 return NULL;
4013                         }
4014                 }
4015
4016                 selhash = defaultselhash = jsonNewObjectType( JSON_HASH );
4017                 jsonObjectSetKey( selhash, core_class, default_list );
4018         }
4019
4020         // The SELECT clause can be encoded only by a hash
4021         if( !from_function && selhash->type != JSON_HASH ) {
4022                 osrfLogError(
4023                         OSRF_LOG_MARK,
4024                         "%s: Expected JSON_HASH for SELECT clause; found %s",
4025                         modulename,
4026                         json_type( selhash->type )
4027                 );
4028
4029                 if( ctx )
4030                         osrfAppSessionStatus(
4031                                 ctx->session,
4032                                 OSRF_STATUS_INTERNALSERVERERROR,
4033                                 "osrfMethodException",
4034                                 ctx->request,
4035                                 "Malformed SELECT clause in JSON query"
4036                         );
4037                 free( join_clause );
4038                 return NULL;
4039         }
4040
4041         // If you see a null or wild card specifier for the core class, or an
4042         // empty array, replace it with a default SELECT list
4043         tmp_const = jsonObjectGetKeyConst( selhash, core_class );
4044         if( tmp_const ) {
4045                 int default_needed = 0;   // boolean
4046                 if( JSON_STRING == tmp_const->type
4047                         && !strcmp( "*", jsonObjectGetString( tmp_const ) ))
4048                                 default_needed = 1;
4049                 else if( JSON_NULL == tmp_const->type )
4050                         default_needed = 1;
4051
4052                 if( default_needed ) {
4053                         // Build a default SELECT list
4054                         jsonObject* default_list = defaultSelectList( core_class );
4055                         if( ! default_list ) {
4056                                 if( ctx ) {
4057                                         osrfAppSessionStatus(
4058                                                 ctx->session,
4059                                                 OSRF_STATUS_INTERNALSERVERERROR,
4060                                                 "osrfMethodException",
4061                                                 ctx->request,
4062                                                 "Can't build default SELECT clause in JSON query"
4063                                         );
4064                                         free( join_clause );
4065                                         return NULL;
4066                                 }
4067                         }
4068
4069                         jsonObjectSetKey( selhash, core_class, default_list );
4070                 }
4071         }
4072
4073         // temp buffers for the SELECT list and GROUP BY clause
4074         growing_buffer* select_buf = buffer_init( 128 );
4075         growing_buffer* group_buf  = buffer_init( 128 );
4076
4077         int aggregate_found = 0;     // boolean
4078
4079         // Build a select list
4080         if( from_function )   // From a function we select everything
4081                 OSRF_BUFFER_ADD_CHAR( select_buf, '*' );
4082         else {
4083
4084                 // Build the SELECT list as SQL
4085             int sel_pos = 1;
4086             first = 1;
4087             gfirst = 1;
4088             jsonIterator* selclass_itr = jsonNewIterator( selhash );
4089             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {    // For each class
4090
4091                         const char* cname = selclass_itr->key;
4092
4093                         // Make sure the target relation is in the FROM clause.
4094
4095                         // At this point join_hash is a step down from the join_hash we
4096                         // received as a parameter.  If the original was a JSON_STRING,
4097                         // then json_hash is now NULL.  If the original was a JSON_HASH,
4098                         // then json_hash is now the first (and only) entry in it,
4099                         // denoting the core class.  We've already excluded the
4100                         // possibility that the original was a JSON_ARRAY, because in
4101                         // that case from_function would be non-NULL, and we wouldn't
4102                         // be here.
4103
4104                         // If the current table alias isn't in scope, bail out
4105                         ClassInfo* class_info = search_alias( cname );
4106                         if( ! class_info ) {
4107                                 osrfLogError(
4108                                         OSRF_LOG_MARK,
4109                                         "%s: SELECT clause references class not in FROM clause: \"%s\"",
4110                                         modulename,
4111                                         cname
4112                                 );
4113                                 if( ctx )
4114                                         osrfAppSessionStatus(
4115                                                 ctx->session,
4116                                                 OSRF_STATUS_INTERNALSERVERERROR,
4117                                                 "osrfMethodException",
4118                                                 ctx->request,
4119                                                 "Selected class not in FROM clause in JSON query"
4120                                         );
4121                                 jsonIteratorFree( selclass_itr );
4122                                 buffer_free( select_buf );
4123                                 buffer_free( group_buf );
4124                                 if( defaultselhash )
4125                                         jsonObjectFree( defaultselhash );
4126                                 free( join_clause );
4127                                 return NULL;
4128                         }
4129
4130                         if( selclass->type != JSON_ARRAY ) {
4131                                 osrfLogError(
4132                                         OSRF_LOG_MARK,
4133                                         "%s: Malformed SELECT list for class \"%s\"; not an array",
4134                                         modulename,
4135                                         cname
4136                                 );
4137                                 if( ctx )
4138                                         osrfAppSessionStatus(
4139                                                 ctx->session,
4140                                                 OSRF_STATUS_INTERNALSERVERERROR,
4141                                                 "osrfMethodException",
4142                                                 ctx->request,
4143                                                 "Selected class not in FROM clause in JSON query"
4144                                         );
4145
4146                                 jsonIteratorFree( selclass_itr );
4147                                 buffer_free( select_buf );
4148                                 buffer_free( group_buf );
4149                                 if( defaultselhash )
4150                                         jsonObjectFree( defaultselhash );
4151                                 free( join_clause );
4152                                 return NULL;
4153                         }
4154
4155                         // Look up some attributes of the current class
4156                         osrfHash* idlClass        = class_info->class_def;
4157                         osrfHash* class_field_set = class_info->fields;
4158                         const char* class_pkey    = osrfHashGet( idlClass, "primarykey" );
4159                         const char* class_tname   = osrfHashGet( idlClass, "tablename" );
4160
4161                         if( 0 == selclass->size ) {
4162                                 osrfLogWarning(
4163                                         OSRF_LOG_MARK,
4164                                         "%s: No columns selected from \"%s\"",
4165                                         modulename,
4166                                         cname
4167                                 );
4168                         }
4169
4170                         // stitch together the column list for the current table alias...
4171                         unsigned long field_idx = 0;
4172                         jsonObject* selfield = NULL;
4173                         while(( selfield = jsonObjectGetIndex( selclass, field_idx++ ) )) {
4174
4175                                 // If we need a separator comma, add one
4176                                 if( first ) {
4177                                         first = 0;
4178                                 } else {
4179                                         OSRF_BUFFER_ADD_CHAR( select_buf, ',' );
4180                                 }
4181
4182                                 // if the field specification is a string, add it to the list
4183                                 if( selfield->type == JSON_STRING ) {
4184
4185                                         // Look up the field in the IDL
4186                                         const char* col_name = jsonObjectGetString( selfield );
4187                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
4188                                         if( !field_def ) {
4189                                                 // No such field in current class
4190                                                 osrfLogError(
4191                                                         OSRF_LOG_MARK,
4192                                                         "%s: Selected column \"%s\" not defined in IDL for class \"%s\"",
4193                                                         modulename,
4194                                                         col_name,
4195                                                         cname
4196                                                 );
4197                                                 if( ctx )
4198                                                         osrfAppSessionStatus(
4199                                                                 ctx->session,
4200                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4201                                                                 "osrfMethodException",
4202                                                                 ctx->request,
4203                                                                 "Selected column not defined in JSON query"
4204                                                         );
4205                                                 jsonIteratorFree( selclass_itr );
4206                                                 buffer_free( select_buf );
4207                                                 buffer_free( group_buf );
4208                                                 if( defaultselhash )
4209                                                         jsonObjectFree( defaultselhash );
4210                                                 free( join_clause );
4211                                                 return NULL;
4212                                         } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4213                                                 // Virtual field not allowed
4214                                                 osrfLogError(
4215                                                         OSRF_LOG_MARK,
4216                                                         "%s: Selected column \"%s\" for class \"%s\" is virtual",
4217                                                         modulename,
4218                                                         col_name,
4219                                                         cname
4220                                                 );
4221                                                 if( ctx )
4222                                                         osrfAppSessionStatus(
4223                                                                 ctx->session,
4224                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4225                                                                 "osrfMethodException",
4226                                                                 ctx->request,
4227                                                                 "Selected column may not be virtual in JSON query"
4228                                                         );
4229                                                 jsonIteratorFree( selclass_itr );
4230                                                 buffer_free( select_buf );
4231                                                 buffer_free( group_buf );
4232                                                 if( defaultselhash )
4233                                                         jsonObjectFree( defaultselhash );
4234                                                 free( join_clause );
4235                                                 return NULL;
4236                                         }
4237
4238                                         if( locale ) {
4239                                                 const char* i18n;
4240                                                 if( flags & DISABLE_I18N )
4241                                                         i18n = NULL;
4242                                                 else
4243                                                         i18n = osrfHashGet( field_def, "i18n" );
4244
4245                                                 if( str_is_true( i18n ) ) {
4246                                                         buffer_fadd( select_buf, " oils_i18n_xlate('%s', '%s', '%s', "
4247                                                                 "'%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
4248                                                                 class_tname, cname, col_name, class_pkey,
4249                                                                 cname, class_pkey, locale, col_name );
4250                                                 } else {
4251                                                         buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4252                                                                 cname, col_name, col_name );
4253                                                 }
4254                                         } else {
4255                                                 buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4256                                                                 cname, col_name, col_name );
4257                                         }
4258
4259                                 // ... but it could be an object, in which case we check for a Field Transform
4260                                 } else if( selfield->type == JSON_HASH ) {
4261
4262                                         const char* col_name = jsonObjectGetString(
4263                                                         jsonObjectGetKeyConst( selfield, "column" ) );
4264
4265                                         // Get the field definition from the IDL
4266                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
4267                                         if( !field_def ) {
4268                                                 // No such field in current class
4269                                                 osrfLogError(
4270                                                         OSRF_LOG_MARK,
4271                                                         "%s: Selected column \"%s\" is not defined in IDL for class \"%s\"",
4272                                                         modulename,
4273                                                         col_name,
4274                                                         cname
4275                                                 );
4276                                                 if( ctx )
4277                                                         osrfAppSessionStatus(
4278                                                                 ctx->session,
4279                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4280                                                                 "osrfMethodException",
4281                                                                 ctx->request,
4282                                                                 "Selected column is not defined in JSON query"
4283                                                         );
4284                                                 jsonIteratorFree( selclass_itr );
4285                                                 buffer_free( select_buf );
4286                                                 buffer_free( group_buf );
4287                                                 if( defaultselhash )
4288                                                         jsonObjectFree( defaultselhash );
4289                                                 free( join_clause );
4290                                                 return NULL;
4291                                         } else if( str_is_true( osrfHashGet( field_def, "virtual" ))) {
4292                                                 // No such field in current class
4293                                                 osrfLogError(
4294                                                         OSRF_LOG_MARK,
4295                                                         "%s: Selected column \"%s\" is virtual for class \"%s\"",
4296                                                         modulename,
4297                                                         col_name,
4298                                                         cname
4299                                                 );
4300                                                 if( ctx )
4301                                                         osrfAppSessionStatus(
4302                                                                 ctx->session,
4303                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4304                                                                 "osrfMethodException",
4305                                                                 ctx->request,
4306                                                                 "Selected column is virtual in JSON query"
4307                                                         );
4308                                                 jsonIteratorFree( selclass_itr );
4309                                                 buffer_free( select_buf );
4310                                                 buffer_free( group_buf );
4311                                                 if( defaultselhash )
4312                                                         jsonObjectFree( defaultselhash );
4313                                                 free( join_clause );
4314                                                 return NULL;
4315                                         }
4316
4317                                         // Decide what to use as a column alias
4318                                         const char* _alias;
4319                                         if((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
4320                                                 _alias = jsonObjectGetString( tmp_const );
4321                                         } else if((tmp_const = jsonObjectGetKeyConst( selfield, "result_field" ))) { // Use result_field name as the alias
4322                                                 _alias = jsonObjectGetString( tmp_const );
4323                                         } else {         // Use field name as the alias
4324                                                 _alias = col_name;
4325                                         }
4326
4327                                         if( jsonObjectGetKeyConst( selfield, "transform" )) {
4328                                                 char* transform_str = searchFieldTransform(
4329                                                         class_info->alias, field_def, selfield );
4330                                                 if( transform_str ) {
4331                                                         buffer_fadd( select_buf, " %s AS \"%s\"", transform_str, _alias );
4332                                                         free( transform_str );
4333                                                 } else {
4334                                                         if( ctx )
4335                                                                 osrfAppSessionStatus(
4336                                                                         ctx->session,
4337                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4338                                                                         "osrfMethodException",
4339                                                                         ctx->request,
4340                                                                         "Unable to generate transform function in JSON query"
4341                                                                 );
4342                                                         jsonIteratorFree( selclass_itr );
4343                                                         buffer_free( select_buf );
4344                                                         buffer_free( group_buf );
4345                                                         if( defaultselhash )
4346                                                                 jsonObjectFree( defaultselhash );
4347                                                         free( join_clause );
4348                                                         return NULL;
4349                                                 }
4350                                         } else {
4351
4352                                                 if( locale ) {
4353                                                         const char* i18n;
4354                                                         if( flags & DISABLE_I18N )
4355                                                                 i18n = NULL;
4356                                                         else
4357                                                                 i18n = osrfHashGet( field_def, "i18n" );
4358
4359                                                         if( str_is_true( i18n ) ) {
4360                                                                 buffer_fadd( select_buf,
4361                                                                         " oils_i18n_xlate('%s', '%s', '%s', '%s', "
4362                                                                         "\"%s\".%s::TEXT, '%s') AS \"%s\"",
4363                                                                         class_tname, cname, col_name, class_pkey, cname,
4364                                                                         class_pkey, locale, _alias );
4365                                                         } else {
4366                                                                 buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4367                                                                         cname, col_name, _alias );
4368                                                         }
4369                                                 } else {
4370                                                         buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4371                                                                 cname, col_name, _alias );
4372                                                 }
4373                                         }
4374                                 }
4375                                 else {
4376                                         osrfLogError(
4377                                                 OSRF_LOG_MARK,
4378                                                 "%s: Selected item is unexpected JSON type: %s",
4379                                                 modulename,
4380                                                 json_type( selfield->type )
4381                                         );
4382                                         if( ctx )
4383                                                 osrfAppSessionStatus(
4384                                                         ctx->session,
4385                                                         OSRF_STATUS_INTERNALSERVERERROR,
4386                                                         "osrfMethodException",
4387                                                         ctx->request,
4388                                                         "Ill-formed SELECT item in JSON query"
4389                                                 );
4390                                         jsonIteratorFree( selclass_itr );
4391                                         buffer_free( select_buf );
4392                                         buffer_free( group_buf );
4393                                         if( defaultselhash )
4394                                                 jsonObjectFree( defaultselhash );
4395                                         free( join_clause );
4396                                         return NULL;
4397                                 }
4398
4399                                 const jsonObject* agg_obj = jsonObjectGetKeyConst( selfield, "aggregate" );
4400                                 if( obj_is_true( agg_obj ) )
4401                                         aggregate_found = 1;
4402                                 else {
4403                                         // Append a comma (except for the first one)
4404                                         // and add the column to a GROUP BY clause
4405                                         if( gfirst )
4406                                                 gfirst = 0;
4407                                         else
4408                                                 OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4409
4410                                         buffer_fadd( group_buf, " %d", sel_pos );
4411                                 }
4412
4413 #if 0
4414                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
4415
4416                                         const jsonObject* aggregate_obj = jsonObjectGetKeyConst( elfield, "aggregate");
4417                                     if ( ! obj_is_true( aggregate_obj ) ) {
4418                                             if (gfirst) {
4419                                                     gfirst = 0;
4420                                             } else {
4421                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4422                                             }
4423
4424                                             buffer_fadd(group_buf, " %d", sel_pos);
4425
4426                                         /*
4427                                     } else if (is_agg = jsonObjectGetKeyConst( selfield, "having" )) {
4428                                             if (gfirst) {
4429                                                     gfirst = 0;
4430                                             } else {
4431                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4432                                             }
4433
4434                                             _column = searchFieldTransform(class_info->alias, field, selfield);
4435                                                 OSRF_BUFFER_ADD_CHAR(group_buf, ' ');
4436                                                 OSRF_BUFFER_ADD(group_buf, _column);
4437                                             _column = searchFieldTransform(class_info->alias, field, selfield);
4438                                         */
4439                                     }
4440                             }
4441 #endif
4442
4443                                 sel_pos++;
4444                         } // end while -- iterating across SELECT columns
4445
4446                 } // end while -- iterating across classes
4447
4448                 jsonIteratorFree( selclass_itr );
4449         }
4450
4451         char* col_list = buffer_release( select_buf );
4452
4453         // Make sure the SELECT list isn't empty.  This can happen, for example,
4454         // if we try to build a default SELECT clause from a non-core table.
4455
4456         if( ! *col_list ) {
4457                 osrfLogError( OSRF_LOG_MARK, "%s: SELECT clause is empty", modulename );
4458                 if( ctx )
4459                         osrfAppSessionStatus(
4460                                 ctx->session,
4461                                 OSRF_STATUS_INTERNALSERVERERROR,
4462                                 "osrfMethodException",
4463                                 ctx->request,
4464                                 "SELECT list is empty"
4465                 );
4466                 free( col_list );
4467                 buffer_free( group_buf );
4468                 if( defaultselhash )
4469                         jsonObjectFree( defaultselhash );
4470                 free( join_clause );
4471                 return NULL;
4472         }
4473
4474         char* table = NULL;
4475         if( from_function )
4476                 table = searchValueTransform( join_hash );
4477         else
4478                 table = strdup( curr_query->core.source_def );
4479
4480         if( !table ) {
4481                 if( ctx )
4482                         osrfAppSessionStatus(
4483                                 ctx->session,
4484                                 OSRF_STATUS_INTERNALSERVERERROR,
4485                                 "osrfMethodException",
4486                                 ctx->request,
4487                                 "Unable to identify table for core class"
4488                         );
4489                 free( col_list );
4490                 buffer_free( group_buf );
4491                 if( defaultselhash )
4492                         jsonObjectFree( defaultselhash );
4493                 free( join_clause );
4494                 return NULL;
4495         }
4496
4497         // Put it all together
4498         growing_buffer* sql_buf = buffer_init( 128 );
4499         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
4500         free( col_list );
4501         free( table );
4502
4503         // Append the join clause, if any
4504         if( join_clause ) {
4505                 buffer_add(sql_buf, join_clause );
4506                 free( join_clause );
4507         }
4508
4509         char* order_by_list = NULL;
4510         char* having_buf = NULL;
4511
4512         if( !from_function ) {
4513
4514                 // Build a WHERE clause, if there is one
4515                 if( search_hash ) {
4516                         buffer_add( sql_buf, " WHERE " );
4517
4518                         // and it's on the WHERE clause
4519                         char* pred = searchWHERE( search_hash, &curr_query->core, AND_OP_JOIN, ctx );
4520                         if( ! pred ) {
4521                                 if( ctx ) {
4522                                         osrfAppSessionStatus(
4523                                                 ctx->session,
4524                                                 OSRF_STATUS_INTERNALSERVERERROR,
4525                                                 "osrfMethodException",
4526                                                 ctx->request,
4527                                                 "Severe query error in WHERE predicate -- see error log for more details"
4528                                         );
4529                                 }
4530                                 buffer_free( group_buf );
4531                                 buffer_free( sql_buf );
4532                                 if( defaultselhash )
4533                                         jsonObjectFree( defaultselhash );
4534                                 return NULL;
4535                         }
4536
4537                         buffer_add( sql_buf, pred );
4538                         free( pred );
4539                 }
4540
4541                 // Build a HAVING clause, if there is one
4542                 if( having_hash ) {
4543
4544                         // and it's on the the WHERE clause
4545                         having_buf = searchWHERE( having_hash, &curr_query->core, AND_OP_JOIN, ctx );
4546
4547                         if( ! having_buf ) {
4548                                 if( ctx ) {
4549                                                 osrfAppSessionStatus(
4550                                                 ctx->session,
4551                                                 OSRF_STATUS_INTERNALSERVERERROR,
4552                                                 "osrfMethodException",
4553                                                 ctx->request,
4554                                                 "Severe query error in HAVING predicate -- see error log for more details"
4555                                         );
4556                                 }
4557                                 buffer_free( group_buf );
4558                                 buffer_free( sql_buf );
4559                                 if( defaultselhash )
4560                                         jsonObjectFree( defaultselhash );
4561                                 return NULL;
4562                         }
4563                 }
4564
4565                 // Build an ORDER BY clause, if there is one
4566                 if( NULL == order_hash )
4567                         ;  // No ORDER BY? do nothing
4568                 else if( JSON_ARRAY == order_hash->type ) {
4569                         order_by_list = buildOrderByFromArray( ctx, order_hash );
4570                         if( !order_by_list ) {
4571                                 free( having_buf );
4572                                 buffer_free( group_buf );
4573                                 buffer_free( sql_buf );
4574                                 if( defaultselhash )
4575                                         jsonObjectFree( defaultselhash );
4576                                 return NULL;
4577                         }
4578                 } else if( JSON_HASH == order_hash->type ) {
4579                         // This hash is keyed on class alias.  Each class has either
4580                         // an array of field names or a hash keyed on field name.
4581                         growing_buffer* order_buf = NULL;  // to collect ORDER BY list
4582                         jsonIterator* class_itr = jsonNewIterator( order_hash );
4583                         while( (snode = jsonIteratorNext( class_itr )) ) {
4584
4585                                 ClassInfo* order_class_info = search_alias( class_itr->key );
4586                                 if( ! order_class_info ) {
4587                                         osrfLogError( OSRF_LOG_MARK,
4588                                                 "%s: Invalid class \"%s\" referenced in ORDER BY clause",
4589                                                 modulename, class_itr->key );
4590                                         if( ctx )
4591                                                 osrfAppSessionStatus(
4592                                                         ctx->session,
4593                                                         OSRF_STATUS_INTERNALSERVERERROR,
4594                                                         "osrfMethodException",
4595                                                         ctx->request,
4596                                                         "Invalid class referenced in ORDER BY clause -- "
4597                                                                 "see error log for more details"
4598                                                 );
4599                                         jsonIteratorFree( class_itr );
4600                                         buffer_free( order_buf );
4601                                         free( having_buf );
4602                                         buffer_free( group_buf );
4603                                         buffer_free( sql_buf );
4604                                         if( defaultselhash )
4605                                                 jsonObjectFree( defaultselhash );
4606                                         return NULL;
4607                                 }
4608
4609                                 osrfHash* field_list_def = order_class_info->fields;
4610
4611                                 if( snode->type == JSON_HASH ) {
4612
4613                                         // Hash is keyed on field names from the current class.  For each field
4614                                         // there is another layer of hash to define the sorting details, if any,
4615                                         // or a string to indicate direction of sorting.
4616                                         jsonIterator* order_itr = jsonNewIterator( snode );
4617                                         while( (onode = jsonIteratorNext( order_itr )) ) {
4618
4619                                                 osrfHash* field_def = osrfHashGet( field_list_def, order_itr->key );
4620                                                 if( !field_def ) {
4621                                                         osrfLogError( OSRF_LOG_MARK,
4622                                                                 "%s: Invalid field \"%s\" in ORDER BY clause",
4623                                                                 modulename, order_itr->key );
4624                                                         if( ctx )
4625                                                                 osrfAppSessionStatus(
4626                                                                         ctx->session,
4627                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4628                                                                         "osrfMethodException",
4629                                                                         ctx->request,
4630                                                                         "Invalid field in ORDER BY clause -- "
4631                                                                         "see error log for more details"
4632                                                                 );
4633                                                         jsonIteratorFree( order_itr );
4634                                                         jsonIteratorFree( class_itr );
4635                                                         buffer_free( order_buf );
4636                                                         free( having_buf );
4637                                                         buffer_free( group_buf );
4638                                                         buffer_free( sql_buf );
4639                                                         if( defaultselhash )
4640                                                                 jsonObjectFree( defaultselhash );
4641                                                         return NULL;
4642                                                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4643                                                         osrfLogError( OSRF_LOG_MARK,
4644                                                                 "%s: Virtual field \"%s\" in ORDER BY clause",
4645                                                                 modulename, order_itr->key );
4646                                                         if( ctx )
4647                                                                 osrfAppSessionStatus(
4648                                                                         ctx->session,
4649                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4650                                                                         "osrfMethodException",
4651                                                                         ctx->request,
4652                                                                         "Virtual field in ORDER BY clause -- "
4653                                                                         "see error log for more details"
4654                                                         );
4655                                                         jsonIteratorFree( order_itr );
4656                                                         jsonIteratorFree( class_itr );
4657                                                         buffer_free( order_buf );
4658                                                         free( having_buf );
4659                                                         buffer_free( group_buf );
4660                                                         buffer_free( sql_buf );
4661                                                         if( defaultselhash )
4662                                                                 jsonObjectFree( defaultselhash );
4663                                                         return NULL;
4664                                                 }
4665
4666                                                 const char* direction = NULL;
4667                                                 if( onode->type == JSON_HASH ) {
4668                                                         if( jsonObjectGetKeyConst( onode, "transform" ) ) {
4669                                                                 string = searchFieldTransform(
4670                                                                         class_itr->key,
4671                                                                         osrfHashGet( field_list_def, order_itr->key ),
4672                                                                         onode
4673                                                                 );
4674                                                                 if( ! string ) {
4675                                                                         if( ctx ) osrfAppSessionStatus(
4676                                                                                 ctx->session,
4677                                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4678                                                                                 "osrfMethodException",
4679                                                                                 ctx->request,
4680                                                                                 "Severe query error in ORDER BY clause -- "
4681                                                                                 "see error log for more details"
4682                                                                         );
4683                                                                         jsonIteratorFree( order_itr );
4684                                                                         jsonIteratorFree( class_itr );
4685                                                                         free( having_buf );
4686                                                                         buffer_free( group_buf );
4687                                                                         buffer_free( order_buf);
4688                                                                         buffer_free( sql_buf );
4689                                                                         if( defaultselhash )
4690                                                                                 jsonObjectFree( defaultselhash );
4691                                                                         return NULL;
4692                                                                 }
4693                                                         } else {
4694                                                                 growing_buffer* field_buf = buffer_init( 16 );
4695                                                                 buffer_fadd( field_buf, "\"%s\".%s",
4696                                                                         class_itr->key, order_itr->key );
4697                                                                 string = buffer_release( field_buf );
4698                                                         }
4699
4700                                                         if( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
4701                                                                 const char* dir = jsonObjectGetString( tmp_const );
4702                                                                 if(!strncasecmp( dir, "d", 1 )) {
4703                                                                         direction = " DESC";
4704                                                                 } else {
4705                                                                         direction = " ASC";
4706                                                                 }
4707                                                         }
4708
4709                                                 } else if( JSON_NULL == onode->type || JSON_ARRAY == onode->type ) {
4710                                                         osrfLogError( OSRF_LOG_MARK,
4711                                                                 "%s: Expected JSON_STRING in ORDER BY clause; found %s",
4712                                                                 modulename, json_type( onode->type ) );
4713                                                         if( ctx )
4714                                                                 osrfAppSessionStatus(
4715                                                                         ctx->session,
4716                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4717                                                                         "osrfMethodException",
4718                                                                         ctx->request,
4719                                                                         "Malformed ORDER BY clause -- see error log for more details"
4720                                                                 );
4721                                                         jsonIteratorFree( order_itr );
4722                                                         jsonIteratorFree( class_itr );
4723                                                         free( having_buf );
4724                                                         buffer_free( group_buf );
4725                                                         buffer_free( order_buf );
4726                                                         buffer_free( sql_buf );
4727                                                         if( defaultselhash )
4728                                                                 jsonObjectFree( defaultselhash );
4729                                                         return NULL;
4730
4731                                                 } else {
4732                                                         string = strdup( order_itr->key );
4733                                                         const char* dir = jsonObjectGetString( onode );
4734                                                         if( !strncasecmp( dir, "d", 1 )) {
4735                                                                 direction = " DESC";
4736                                                         } else {
4737                                                                 direction = " ASC";
4738                                                         }
4739                                                 }
4740
4741                                                 if( order_buf )
4742                                                         OSRF_BUFFER_ADD( order_buf, ", " );
4743                                                 else
4744                                                         order_buf = buffer_init( 128 );
4745
4746                                                 OSRF_BUFFER_ADD( order_buf, string );
4747                                                 free( string );
4748
4749                                                 if( direction ) {
4750                                                          OSRF_BUFFER_ADD( order_buf, direction );
4751                                                 }
4752
4753                                         } // end while
4754                                         jsonIteratorFree( order_itr );
4755
4756                                 } else if( snode->type == JSON_ARRAY ) {
4757
4758                                         // Array is a list of fields from the current class
4759                                         unsigned long order_idx = 0;
4760                                         while(( onode = jsonObjectGetIndex( snode, order_idx++ ) )) {
4761
4762                                                 const char* _f = jsonObjectGetString( onode );
4763
4764                                                 osrfHash* field_def = osrfHashGet( field_list_def, _f );
4765                                                 if( !field_def ) {
4766                                                         osrfLogError( OSRF_LOG_MARK,
4767                                                                         "%s: Invalid field \"%s\" in ORDER BY clause",
4768                                                                         modulename, _f );
4769                                                         if( ctx )
4770                                                                 osrfAppSessionStatus(
4771                                                                         ctx->session,
4772                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4773                                                                         "osrfMethodException",
4774                                                                         ctx->request,
4775                                                                         "Invalid field in ORDER BY clause -- "
4776                                                                         "see error log for more details"
4777                                                                 );
4778                                                         jsonIteratorFree( class_itr );
4779                                                         buffer_free( order_buf );
4780                                                         free( having_buf );
4781                                                         buffer_free( group_buf );
4782                                                         buffer_free( sql_buf );
4783                                                         if( defaultselhash )
4784                                                                 jsonObjectFree( defaultselhash );
4785                                                         return NULL;
4786                                                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4787                                                         osrfLogError( OSRF_LOG_MARK,
4788                                                                 "%s: Virtual field \"%s\" in ORDER BY clause",
4789                                                                 modulename, _f );
4790                                                         if( ctx )
4791                                                                 osrfAppSessionStatus(
4792                                                                         ctx->session,
4793                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4794                                                                         "osrfMethodException",
4795                                                                         ctx->request,
4796                                                                         "Virtual field in ORDER BY clause -- "
4797                                                                         "see error log for more details"
4798                                                                 );
4799                                                         jsonIteratorFree( class_itr );
4800                                                         buffer_free( order_buf );
4801                                                         free( having_buf );
4802                                                         buffer_free( group_buf );
4803                                                         buffer_free( sql_buf );
4804                                                         if( defaultselhash )
4805                                                                 jsonObjectFree( defaultselhash );
4806                                                         return NULL;
4807                                                 }
4808
4809                                                 if( order_buf )
4810                                                         OSRF_BUFFER_ADD( order_buf, ", " );
4811                                                 else
4812                                                         order_buf = buffer_init( 128 );
4813
4814                                                 buffer_fadd( order_buf, "\"%s\".%s", class_itr->key, _f );
4815
4816                                         } // end while
4817
4818                                 // IT'S THE OOOOOOOOOOOLD STYLE!
4819                                 } else {
4820                                         osrfLogError( OSRF_LOG_MARK,
4821                                                 "%s: Possible SQL injection attempt; direct order by is not allowed",
4822                                                 modulename );
4823                                         if(ctx) {
4824                                                 osrfAppSessionStatus(
4825                                                         ctx->session,
4826                                                         OSRF_STATUS_INTERNALSERVERERROR,
4827                                                         "osrfMethodException",
4828                                                         ctx->request,
4829                                                         "Severe query error -- see error log for more details"
4830                                                 );
4831                                         }
4832
4833                                         free( having_buf );
4834                                         buffer_free( group_buf );
4835                                         buffer_free( order_buf );
4836                                         buffer_free( sql_buf );
4837                                         if( defaultselhash )
4838                                                 jsonObjectFree( defaultselhash );
4839                                         jsonIteratorFree( class_itr );
4840                                         return NULL;
4841                                 }
4842                         } // end while
4843                         jsonIteratorFree( class_itr );
4844                         if( order_buf )
4845                                 order_by_list = buffer_release( order_buf );
4846                 } else {
4847                         osrfLogError( OSRF_LOG_MARK,
4848                                 "%s: Malformed ORDER BY clause; expected JSON_HASH or JSON_ARRAY, found %s",
4849                                 modulename, json_type( order_hash->type ) );
4850                         if( ctx )
4851                                 osrfAppSessionStatus(
4852                                         ctx->session,
4853                                         OSRF_STATUS_INTERNALSERVERERROR,
4854                                         "osrfMethodException",
4855                                         ctx->request,
4856                                         "Malformed ORDER BY clause -- see error log for more details"
4857                                 );
4858                         free( having_buf );
4859                         buffer_free( group_buf );
4860                         buffer_free( sql_buf );
4861                         if( defaultselhash )
4862                                 jsonObjectFree( defaultselhash );
4863                         return NULL;
4864                 }
4865         }
4866
4867         string = buffer_release( group_buf );
4868
4869         if( *string && ( aggregate_found || (flags & SELECT_DISTINCT) ) ) {
4870                 OSRF_BUFFER_ADD( sql_buf, " GROUP BY " );
4871                 OSRF_BUFFER_ADD( sql_buf, string );
4872         }
4873
4874         free( string );
4875
4876         if( having_buf && *having_buf ) {
4877                 OSRF_BUFFER_ADD( sql_buf, " HAVING " );
4878                 OSRF_BUFFER_ADD( sql_buf, having_buf );
4879                 free( having_buf );
4880         }
4881
4882         if( order_by_list ) {
4883
4884                 if( *order_by_list ) {
4885                         OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
4886                         OSRF_BUFFER_ADD( sql_buf, order_by_list );
4887                 }
4888
4889                 free( order_by_list );
4890         }
4891
4892         if( limit ){
4893                 const char* str = jsonObjectGetString( limit );
4894                 buffer_fadd( sql_buf, " LIMIT %d", atoi( str ));
4895         }
4896
4897         if( offset ) {
4898                 const char* str = jsonObjectGetString( offset );
4899                 buffer_fadd( sql_buf, " OFFSET %d", atoi( str ));
4900         }
4901
4902         if( !(flags & SUBSELECT) )
4903                 OSRF_BUFFER_ADD_CHAR( sql_buf, ';' );
4904
4905         if( defaultselhash )
4906                  jsonObjectFree( defaultselhash );
4907
4908         return buffer_release( sql_buf );
4909
4910 } // end of SELECT()
4911
4912 /**
4913         @brief Build a list of ORDER BY expressions.
4914         @param ctx Pointer to the method context.
4915         @param order_array Pointer to a JSON_ARRAY of field specifications.
4916         @return Pointer to a string containing a comma-separated list of ORDER BY expressions.
4917         Each expression may be either a column reference or a function call whose first parameter
4918         is a column reference.
4919
4920         Each entry in @a order_array must be a JSON_HASH with values for "class" and "field".
4921         It may optionally include entries for "direction" and/or "transform".
4922
4923         The calling code is responsible for freeing the returned string.
4924 */
4925 static char* buildOrderByFromArray( osrfMethodContext* ctx, const jsonObject* order_array ) {
4926         if( ! order_array ) {
4927                 osrfLogError( OSRF_LOG_MARK, "%s: Logic error: NULL pointer for ORDER BY clause",
4928                         modulename );
4929                 if( ctx )
4930                         osrfAppSessionStatus(
4931                                 ctx->session,
4932                                 OSRF_STATUS_INTERNALSERVERERROR,
4933                                 "osrfMethodException",
4934                                 ctx->request,
4935                                 "Logic error: ORDER BY clause expected, not found; "
4936                                         "see error log for more details"
4937                         );
4938                 return NULL;
4939         } else if( order_array->type != JSON_ARRAY ) {
4940                 osrfLogError( OSRF_LOG_MARK,
4941                         "%s: Logic error: Expected JSON_ARRAY for ORDER BY clause, not found", modulename );
4942                 if( ctx )
4943                         osrfAppSessionStatus(
4944                         ctx->session,
4945                         OSRF_STATUS_INTERNALSERVERERROR,
4946                         "osrfMethodException",
4947                         ctx->request,
4948                         "Logic error: Unexpected format for ORDER BY clause; see error log for more details" );
4949                 return NULL;
4950         }
4951
4952         growing_buffer* order_buf = buffer_init( 128 );
4953         int first = 1;        // boolean
4954         int order_idx = 0;
4955         jsonObject* order_spec;
4956         while( (order_spec = jsonObjectGetIndex( order_array, order_idx++ ))) {
4957
4958                 if( JSON_HASH != order_spec->type ) {
4959                         osrfLogError( OSRF_LOG_MARK,
4960                                 "%s: Malformed field specification in ORDER BY clause; "
4961                                 "expected JSON_HASH, found %s",
4962                                 modulename, json_type( order_spec->type ) );
4963                         if( ctx )
4964                                 osrfAppSessionStatus(
4965                                          ctx->session,
4966                                         OSRF_STATUS_INTERNALSERVERERROR,
4967                                         "osrfMethodException",
4968                                         ctx->request,
4969                                         "Malformed ORDER BY clause -- see error log for more details"
4970                                 );
4971                         buffer_free( order_buf );
4972                         return NULL;
4973                 }
4974
4975                 const char* class_alias =
4976                         jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "class" ));
4977                 const char* field =
4978                         jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "field" ));
4979
4980                 jsonObject* compare_to = jsonObjectGetKeyConst( order_spec, "compare" );
4981
4982                 if( !field || !class_alias ) {
4983                         osrfLogError( OSRF_LOG_MARK,
4984                                 "%s: Missing class or field name in field specification of ORDER BY clause",
4985                                 modulename );
4986                         if( ctx )
4987                                 osrfAppSessionStatus(
4988                                         ctx->session,
4989                                         OSRF_STATUS_INTERNALSERVERERROR,
4990                                         "osrfMethodException",
4991                                         ctx->request,
4992                                         "Malformed ORDER BY clause -- see error log for more details"
4993                                 );
4994                         buffer_free( order_buf );
4995                         return NULL;
4996                 }
4997
4998                 const ClassInfo* order_class_info = search_alias( class_alias );
4999                 if( ! order_class_info ) {
5000                         osrfLogInternal( OSRF_LOG_MARK, "%s: ORDER BY clause references class \"%s\" "
5001                                 "not in FROM clause, skipping it", modulename, class_alias );
5002                         continue;
5003                 }
5004
5005                 // Add a separating comma, except at the beginning
5006                 if( first )
5007                         first = 0;
5008                 else
5009                         OSRF_BUFFER_ADD( order_buf, ", " );
5010
5011                 osrfHash* field_def = osrfHashGet( order_class_info->fields, field );
5012                 if( !field_def ) {
5013                         osrfLogError( OSRF_LOG_MARK,
5014                                 "%s: Invalid field \"%s\".%s referenced in ORDER BY clause",
5015                                 modulename, class_alias, field );
5016                         if( ctx )
5017                                 osrfAppSessionStatus(
5018                                         ctx->session,
5019                                         OSRF_STATUS_INTERNALSERVERERROR,
5020                                         "osrfMethodException",
5021                                         ctx->request,
5022                                         "Invalid field referenced in ORDER BY clause -- "
5023                                         "see error log for more details"
5024                                 );
5025                         free( order_buf );
5026                         return NULL;
5027                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
5028                         osrfLogError( OSRF_LOG_MARK, "%s: Virtual field \"%s\" in ORDER BY clause",
5029                                 modulename, field );
5030                         if( ctx )
5031                                 osrfAppSessionStatus(
5032                                         ctx->session,
5033                                         OSRF_STATUS_INTERNALSERVERERROR,
5034                                         "osrfMethodException",
5035                                         ctx->request,
5036                                         "Virtual field in ORDER BY clause -- see error log for more details"
5037                                 );
5038                         buffer_free( order_buf );
5039                         return NULL;
5040                 }
5041
5042                 if( jsonObjectGetKeyConst( order_spec, "transform" )) {
5043                         char* transform_str = searchFieldTransform( class_alias, field_def, order_spec );
5044                         if( ! transform_str ) {
5045                                 if( ctx )
5046                                         osrfAppSessionStatus(
5047                                                 ctx->session,
5048                                                 OSRF_STATUS_INTERNALSERVERERROR,
5049                                                 "osrfMethodException",
5050                                                 ctx->request,
5051                                                 "Severe query error in ORDER BY clause -- "
5052                                                 "see error log for more details"
5053                                         );
5054                                 buffer_free( order_buf );
5055                                 return NULL;
5056                         }
5057
5058                         OSRF_BUFFER_ADD( order_buf, transform_str );
5059                         free( transform_str );
5060                 } else if( compare_to ) {
5061                         char* compare_str = searchPredicate( order_class_info, field_def, compare_to, ctx );
5062                         if( ! compare_str ) {
5063                                 if( ctx )
5064                                         osrfAppSessionStatus(
5065                                                 ctx->session,
5066                                                 OSRF_STATUS_INTERNALSERVERERROR,
5067                                                 "osrfMethodException",
5068                                                 ctx->request,
5069                                                 "Severe query error in ORDER BY clause -- "
5070                                                 "see error log for more details"
5071                                         );
5072                                 buffer_free( order_buf );
5073                                 return NULL;
5074                         }
5075
5076                         buffer_fadd( order_buf, "(%s)", compare_str );
5077                         free( compare_str );
5078                 }
5079                 else
5080                         buffer_fadd( order_buf, "\"%s\".%s", class_alias, field );
5081
5082                 const char* direction =
5083                         jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "direction" ) );
5084                 if( direction ) {
5085                         if( direction[ 0 ] || 'D' == direction[ 0 ] )
5086                                 OSRF_BUFFER_ADD( order_buf, " DESC" );
5087                         else
5088                                 OSRF_BUFFER_ADD( order_buf, " ASC" );
5089                 }
5090         }
5091
5092         return buffer_release( order_buf );
5093 }
5094
5095 /**
5096         @brief Build a SELECT statement.
5097         @param search_hash Pointer to a JSON_HASH or JSON_ARRAY encoding the WHERE clause.
5098         @param rest_of_query Pointer to a JSON_HASH containing any other SQL clauses.
5099         @param meta Pointer to the class metadata for the core class.
5100         @param ctx Pointer to the method context.
5101         @return Pointer to a character string containing the WHERE clause; or NULL upon error.
5102
5103         Within the rest_of_query hash, the meaningful keys are "join", "select", "no_i18n",
5104         "order_by", "limit", and "offset".
5105
5106         The SELECT statements built here are distinct from those built for the json_query method.
5107 */
5108 static char* buildSELECT ( const jsonObject* search_hash, jsonObject* rest_of_query,
5109         osrfHash* meta, osrfMethodContext* ctx ) {
5110
5111         const char* locale = osrf_message_get_last_locale();
5112
5113         osrfHash* fields = osrfHashGet( meta, "fields" );
5114         const char* core_class = osrfHashGet( meta, "classname" );
5115
5116         const jsonObject* join_hash = jsonObjectGetKeyConst( rest_of_query, "join" );
5117
5118         jsonObject* selhash = NULL;
5119         jsonObject* defaultselhash = NULL;
5120
5121         growing_buffer* sql_buf = buffer_init( 128 );
5122         growing_buffer* select_buf = buffer_init( 128 );
5123
5124         if( !(selhash = jsonObjectGetKey( rest_of_query, "select" )) ) {
5125                 defaultselhash = jsonNewObjectType( JSON_HASH );
5126                 selhash = defaultselhash;
5127         }
5128
5129         // If there's no SELECT list for the core class, build one
5130         if( !jsonObjectGetKeyConst( selhash, core_class ) ) {
5131                 jsonObject* field_list = jsonNewObjectType( JSON_ARRAY );
5132
5133                 // Add every non-virtual field to the field list
5134                 osrfHash* field_def = NULL;
5135                 osrfHashIterator* field_itr = osrfNewHashIterator( fields );
5136                 while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
5137                         if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
5138                                 const char* field = osrfHashIteratorKey( field_itr );
5139                                 jsonObjectPush( field_list, jsonNewObject( field ) );
5140                         }
5141                 }
5142                 osrfHashIteratorFree( field_itr );
5143                 jsonObjectSetKey( selhash, core_class, field_list );
5144         }
5145
5146         // Build a list of columns for the SELECT clause
5147         int first = 1;
5148         const jsonObject* snode = NULL;
5149         jsonIterator* class_itr = jsonNewIterator( selhash );
5150         while( (snode = jsonIteratorNext( class_itr )) ) {        // For each class
5151
5152                 // If the class isn't in the IDL, ignore it
5153                 const char* cname = class_itr->key;
5154                 osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
5155                 if( !idlClass )
5156                         continue;
5157
5158                 // If the class isn't the core class, and isn't in the JOIN clause, ignore it
5159                 if( strcmp( core_class, class_itr->key )) {
5160                         if( !join_hash )
5161                                 continue;
5162
5163                         jsonObject* found = jsonObjectFindPath( join_hash, "//%s", class_itr->key );
5164                         if( !found->size ) {
5165                                 jsonObjectFree( found );
5166                                 continue;
5167                         }
5168
5169                         jsonObjectFree( found );
5170                 }
5171
5172                 const jsonObject* node = NULL;
5173                 jsonIterator* select_itr = jsonNewIterator( snode );
5174                 while( (node = jsonIteratorNext( select_itr )) ) {
5175                         const char* item_str = jsonObjectGetString( node );
5176                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
5177                         char* fname = osrfHashGet( field, "name" );
5178
5179                         if( !field )
5180                                 continue;
5181
5182                         if( first ) {
5183                                 first = 0;
5184                         } else {
5185                                 OSRF_BUFFER_ADD_CHAR( select_buf, ',' );
5186                         }
5187
5188                         if( locale ) {
5189                                 const char* i18n;
5190                                 const jsonObject* no_i18n_obj = jsonObjectGetKeyConst( rest_of_query, "no_i18n" );
5191                                 if( obj_is_true( no_i18n_obj ) )    // Suppress internationalization?
5192                                         i18n = NULL;
5193                                 else
5194                                         i18n = osrfHashGet( field, "i18n" );
5195
5196                                 if( str_is_true( i18n ) ) {
5197                                         char* pkey = osrfHashGet( idlClass, "primarykey" );
5198                                         char* tname = osrfHashGet( idlClass, "tablename" );
5199
5200                                         buffer_fadd( select_buf, " oils_i18n_xlate('%s', '%s', '%s', "
5201                                                         "'%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
5202                                                         tname, cname, fname, pkey, cname, pkey, locale, fname );
5203                                 } else {
5204                                         buffer_fadd( select_buf, " \"%s\".%s", cname, fname );
5205                                 }
5206                         } else {
5207                                 buffer_fadd( select_buf, " \"%s\".%s", cname, fname );
5208                         }
5209                 }
5210
5211                 jsonIteratorFree( select_itr );
5212         }
5213
5214         jsonIteratorFree( class_itr );
5215
5216         char* col_list = buffer_release( select_buf );
5217         char* table = oilsGetRelation( meta );
5218         if( !table )
5219                 table = strdup( "(null)" );
5220
5221         buffer_fadd( sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
5222         free( col_list );
5223         free( table );
5224
5225         // Clear the query stack (as a fail-safe precaution against possible
5226         // leftover garbage); then push the first query frame onto the stack.
5227         clear_query_stack();
5228         push_query_frame();
5229         if( add_query_core( NULL, core_class ) ) {
5230                 if( ctx )
5231                         osrfAppSessionStatus(
5232                                 ctx->session,
5233                                 OSRF_STATUS_INTERNALSERVERERROR,
5234                                 "osrfMethodException",
5235                                 ctx->request,
5236                                 "Unable to build query frame for core class"
5237                         );
5238                 buffer_free( sql_buf );
5239                 if( defaultselhash )
5240                         jsonObjectFree( defaultselhash );
5241                 return NULL;
5242         }
5243
5244         // Add the JOIN clauses, if any
5245         if( join_hash ) {
5246                 char* join_clause = searchJOIN( join_hash, &curr_query->core );
5247                 OSRF_BUFFER_ADD_CHAR( sql_buf, ' ' );
5248                 OSRF_BUFFER_ADD( sql_buf, join_clause );
5249                 free( join_clause );
5250         }
5251
5252         osrfLogDebug( OSRF_LOG_MARK, "%s pre-predicate SQL =  %s",
5253                 modulename, OSRF_BUFFER_C_STR( sql_buf ));
5254
5255         OSRF_BUFFER_ADD( sql_buf, " WHERE " );
5256
5257         // Add the conditions in the WHERE clause
5258         char* pred = searchWHERE( search_hash, &curr_query->core, AND_OP_JOIN, ctx );
5259         if( !pred ) {
5260                 osrfAppSessionStatus(
5261                         ctx->session,
5262                         OSRF_STATUS_INTERNALSERVERERROR,
5263                                 "osrfMethodException",
5264                                 ctx->request,
5265                                 "Severe query error -- see error log for more details"
5266                         );
5267                 buffer_free( sql_buf );
5268                 if( defaultselhash )
5269                         jsonObjectFree( defaultselhash );
5270                 clear_query_stack();
5271                 return NULL;
5272         } else {
5273                 buffer_add( sql_buf, pred );
5274                 free( pred );
5275         }
5276
5277         // Add the ORDER BY, LIMIT, and/or OFFSET clauses, if present
5278         if( rest_of_query ) {
5279                 const jsonObject* order_by = NULL;
5280                 if( ( order_by = jsonObjectGetKeyConst( rest_of_query, "order_by" )) ){
5281
5282                         char* order_by_list = NULL;
5283
5284                         if( JSON_ARRAY == order_by->type ) {
5285                                 order_by_list = buildOrderByFromArray( ctx, order_by );
5286                                 if( !order_by_list ) {
5287                                         buffer_free( sql_buf );
5288                                         if( defaultselhash )
5289                                                 jsonObjectFree( defaultselhash );
5290                                         clear_query_stack();
5291                                         return NULL;
5292                                 }
5293                         } else if( JSON_HASH == order_by->type ) {
5294                                 // We expect order_by to be a JSON_HASH keyed on class names.  Traverse it
5295                                 // and build a list of ORDER BY expressions.
5296                                 growing_buffer* order_buf = buffer_init( 128 );
5297                                 first = 1;
5298                                 jsonIterator* class_itr = jsonNewIterator( order_by );
5299                                 while( (snode = jsonIteratorNext( class_itr )) ) {  // For each class:
5300
5301                                         ClassInfo* order_class_info = search_alias( class_itr->key );
5302                                         if( ! order_class_info )
5303                                                 continue;    // class not referenced by FROM clause?  Ignore it.
5304
5305                                         if( JSON_HASH == snode->type ) {
5306
5307                                                 // If the data for the current class is a JSON_HASH, then it is
5308                                                 // keyed on field name.
5309
5310                                                 const jsonObject* onode = NULL;
5311                                                 jsonIterator* order_itr = jsonNewIterator( snode );
5312                                                 while( (onode = jsonIteratorNext( order_itr )) ) {  // For each field
5313
5314                                                         osrfHash* field_def = osrfHashGet(
5315                                                                 order_class_info->fields, order_itr->key );
5316                                                         if( !field_def )
5317                                                                 continue;    // Field not defined in IDL?  Ignore it.
5318                                                         if( str_is_true( osrfHashGet( field_def, "virtual")))
5319                                                                 continue;    // Field is virtual?  Ignore it.
5320
5321                                                         char* field_str = NULL;
5322                                                         char* direction = NULL;
5323                                                         if( onode->type == JSON_HASH ) {
5324                                                                 if( jsonObjectGetKeyConst( onode, "transform" ) ) {
5325                                                                         field_str = searchFieldTransform(
5326                                                                                 class_itr->key, field_def, onode );
5327                                                                         if( ! field_str ) {
5328                                                                                 osrfAppSessionStatus(
5329                                                                                         ctx->session,
5330                                                                                         OSRF_STATUS_INTERNALSERVERERROR,
5331                                                                                         "osrfMethodException",
5332                                                                                         ctx->request,
5333                                                                                         "Severe query error in ORDER BY clause -- "
5334                                                                                         "see error log for more details"
5335                                                                                 );
5336                                                                                 jsonIteratorFree( order_itr );
5337                                                                                 jsonIteratorFree( class_itr );
5338                                                                                 buffer_free( order_buf );
5339                                                                                 buffer_free( sql_buf );
5340                                                                                 if( defaultselhash )
5341                                                                                         jsonObjectFree( defaultselhash );
5342                                                                                 clear_query_stack();
5343                                                                                 return NULL;
5344                                                                         }
5345                                                                 } else {
5346                                                                         growing_buffer* field_buf = buffer_init( 16 );
5347                                                                         buffer_fadd( field_buf, "\"%s\".%s",
5348                                                                                 class_itr->key, order_itr->key );
5349                                                                         field_str = buffer_release( field_buf );
5350                                                                 }
5351
5352                                                                 if( ( order_by = jsonObjectGetKeyConst( onode, "direction" )) ) {
5353                                                                         const char* dir = jsonObjectGetString( order_by );
5354                                                                         if(!strncasecmp( dir, "d", 1 )) {
5355                                                                                 direction = " DESC";
5356                                                                         }
5357                                                                 }
5358                                                         } else {
5359                                                                 field_str = strdup( order_itr->key );
5360                                                                 const char* dir = jsonObjectGetString( onode );
5361                                                                 if( !strncasecmp( dir, "d", 1 )) {
5362                                                                         direction = " DESC";
5363                                                                 } else {
5364                                                                         direction = " ASC";
5365                                                                 }
5366                                                         }
5367
5368                                                         if( first ) {
5369                                                                 first = 0;
5370                                                         } else {
5371                                                                 buffer_add( order_buf, ", " );
5372                                                         }
5373
5374                                                         buffer_add( order_buf, field_str );
5375                                                         free( field_str );
5376
5377                                                         if( direction ) {
5378                                                                 buffer_add( order_buf, direction );
5379                                                         }
5380                                                 } // end while; looping over ORDER BY expressions
5381
5382                                                 jsonIteratorFree( order_itr );
5383
5384                                         } else if( JSON_STRING == snode->type ) {
5385                                                 // We expect a comma-separated list of sort fields.
5386                                                 const char* str = jsonObjectGetString( snode );
5387                                                 if( strchr( str, ';' )) {
5388                                                         // No semicolons allowed.  It is theoretically possible for a
5389                                                         // legitimate semicolon to occur within quotes, but it's not likely
5390                                                         // to occur in practice in the context of an ORDER BY list.
5391                                                         osrfLogError( OSRF_LOG_MARK, "%s: Possible attempt at SOL injection -- "
5392                                                                 "semicolon found in ORDER BY list: \"%s\"", modulename, str );
5393                                                         if( ctx ) {
5394                                                                 osrfAppSessionStatus(
5395                                                                         ctx->session,
5396                                                                         OSRF_STATUS_INTERNALSERVERERROR,
5397                                                                         "osrfMethodException",
5398                                                                         ctx->request,
5399                                                                         "Possible attempt at SOL injection -- "
5400                                                                                 "semicolon found in ORDER BY list"
5401                                                                 );
5402                                                         }
5403                                                         jsonIteratorFree( class_itr );
5404                                                         buffer_free( order_buf );
5405                                                         buffer_free( sql_buf );
5406                                                         if( defaultselhash )
5407                                                                 jsonObjectFree( defaultselhash );
5408                                                         clear_query_stack();
5409                                                         return NULL;
5410                                                 }
5411                                                 buffer_add( order_buf, str );
5412                                                 break;
5413                                         }
5414
5415                                 } // end while; looping over order_by classes
5416
5417                                 jsonIteratorFree( class_itr );
5418                                 order_by_list = buffer_release( order_buf );
5419
5420                         } else {
5421                                 osrfLogWarning( OSRF_LOG_MARK,
5422                                         "\"order_by\" object in a query is not a JSON_HASH or JSON_ARRAY;"
5423                                         "no ORDER BY generated" );
5424                         }
5425
5426                         if( order_by_list && *order_by_list ) {
5427                                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
5428                                 OSRF_BUFFER_ADD( sql_buf, order_by_list );
5429                         }
5430
5431                         free( order_by_list );
5432                 }
5433
5434                 const jsonObject* limit = jsonObjectGetKeyConst( rest_of_query, "limit" );
5435                 if( limit ) {
5436                         const char* str = jsonObjectGetString( limit );
5437                         buffer_fadd(
5438                                 sql_buf,
5439                                 " LIMIT %d",
5440                                 atoi(str)
5441                         );
5442                 }
5443
5444                 const jsonObject* offset = jsonObjectGetKeyConst( rest_of_query, "offset" );
5445                 if( offset ) {
5446                         const char* str = jsonObjectGetString( offset );
5447                         buffer_fadd(
5448                                 sql_buf,
5449                                 " OFFSET %d",
5450                                 atoi( str )
5451                         );
5452                 }
5453         }
5454
5455         if( defaultselhash )
5456                 jsonObjectFree( defaultselhash );
5457         clear_query_stack();
5458
5459         OSRF_BUFFER_ADD_CHAR( sql_buf, ';' );
5460         return buffer_release( sql_buf );
5461 }
5462
5463 int doJSONSearch ( osrfMethodContext* ctx ) {
5464         if(osrfMethodVerifyContext( ctx )) {
5465                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
5466                 return -1;
5467         }
5468
5469         osrfLogDebug( OSRF_LOG_MARK, "Received query request" );
5470
5471         int err = 0;
5472
5473         jsonObject* hash = jsonObjectGetIndex( ctx->params, 0 );
5474
5475         int flags = 0;
5476
5477         if( obj_is_true( jsonObjectGetKeyConst( hash, "distinct" )))
5478                 flags |= SELECT_DISTINCT;
5479
5480         if( obj_is_true( jsonObjectGetKeyConst( hash, "no_i18n" )))
5481                 flags |= DISABLE_I18N;
5482
5483         osrfLogDebug( OSRF_LOG_MARK, "Building SQL ..." );
5484         clear_query_stack();       // a possibly needless precaution
5485         char* sql = buildQuery( ctx, hash, flags );
5486         clear_query_stack();
5487
5488         if( !sql ) {
5489                 err = -1;
5490                 return err;
5491         }
5492
5493         osrfLogDebug( OSRF_LOG_MARK, "%s SQL =  %s", modulename, sql );
5494
5495         // XXX for now...
5496         dbhandle = writehandle;
5497
5498         dbi_result result = dbi_conn_query( dbhandle, sql );
5499
5500         if( result ) {
5501                 osrfLogDebug( OSRF_LOG_MARK, "Query returned with no errors" );
5502
5503                 if( dbi_result_first_row( result )) {
5504                         /* JSONify the result */
5505                         osrfLogDebug( OSRF_LOG_MARK, "Query returned at least one row" );
5506
5507                         do {
5508                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
5509                                 osrfAppRespond( ctx, return_val );
5510                                 jsonObjectFree( return_val );
5511                         } while( dbi_result_next_row( result ));
5512
5513                 } else {
5514                         osrfLogDebug( OSRF_LOG_MARK, "%s returned no results for query %s", modulename, sql );
5515                 }
5516
5517                 osrfAppRespondComplete( ctx, NULL );
5518
5519                 /* clean up the query */
5520                 dbi_result_free( result );
5521
5522         } else {
5523                 err = -1;
5524                 const char* msg;
5525                 int errnum = dbi_conn_error( dbhandle, &msg );
5526                 osrfLogError( OSRF_LOG_MARK, "%s: Error with query [%s]: %d %s",
5527                         modulename, sql, errnum, msg ? msg : "(No description available)" );
5528                 osrfAppSessionStatus(
5529                         ctx->session,
5530                         OSRF_STATUS_INTERNALSERVERERROR,
5531                         "osrfMethodException",
5532                         ctx->request,
5533                         "Severe query error -- see error log for more details"
5534                 );
5535                 if( !oilsIsDBConnected( dbhandle ))
5536                         osrfAppSessionPanic( ctx->session );
5537         }
5538
5539         free( sql );
5540         return err;
5541 }
5542
5543 // The last parameter, err, is used to report an error condition by updating an int owned by
5544 // the calling code.
5545
5546 // In case of an error, we set *err to -1.  If there is no error, *err is left unchanged.
5547 // It is the responsibility of the calling code to initialize *err before the
5548 // call, so that it will be able to make sense of the result.
5549
5550 // Note also that we return NULL if and only if we set *err to -1.  So the err parameter is
5551 // redundant anyway.
5552 static jsonObject* doFieldmapperSearch( osrfMethodContext* ctx, osrfHash* class_meta,
5553                 jsonObject* where_hash, jsonObject* query_hash, int* err ) {
5554
5555         // XXX for now...
5556         dbhandle = writehandle;
5557
5558         char* core_class = osrfHashGet( class_meta, "classname" );
5559         osrfLogDebug( OSRF_LOG_MARK, "entering doFieldmapperSearch() with core_class %s", core_class );
5560
5561         char* pkey = osrfHashGet( class_meta, "primarykey" );
5562
5563         if (!ctx->session->userData)
5564                 initSessionCache( ctx );
5565
5566         char *methodtype = osrfHashGet( (osrfHash *) ctx->method->userData, "methodtype" );
5567         char *inside_verify = osrfHashGet( (osrfHash*) ctx->session->userData, "inside_verify" );
5568         int need_to_verify = (inside_verify ? !atoi(inside_verify) : 1);
5569         int has_controller = osrfStringArrayContains(osrfHashGet(class_meta, "controller"), modulename);
5570
5571         int i_respond_directly = 0;
5572         int flesh_depth = 0;
5573
5574         // XXX This can be redundant with another instance of the same test that happens
5575         // within the functions that call doFieldmapperSearch(), but we have it here to
5576         // prevent any non-pcrud-controlled classes from being fleshed on.
5577         //
5578         // TODO To avoid redundancy, move this block to right before we recurse,
5579         // and change the class we're checking to the one we're /about/ to search for,
5580         // not the one we're currently searching for.
5581         if (
5582                 (!has_controller && !enforce_pcrud) // cstore client-level case: we require the controller, period
5583                 || (!has_controller && enforce_pcrud && need_to_verify) // pcrud case: we require the controller in need_to_verify mode
5584         ) {
5585                 osrfLogInfo(OSRF_LOG_MARK, "%s is not listed as a controller for %s, moving on",
5586                         modulename, core_class);
5587                 return jsonNewObjectType( JSON_ARRAY ); /* empty */
5588         }
5589
5590         char* sql = buildSELECT( where_hash, query_hash, class_meta, ctx );
5591         if( !sql ) {
5592                 osrfLogDebug( OSRF_LOG_MARK, "Problem building query, returning NULL" );
5593                 *err = -1;
5594                 return NULL;
5595         }
5596
5597         osrfLogDebug( OSRF_LOG_MARK, "%s SQL =  %s", modulename, sql );
5598
5599         dbi_result result = dbi_conn_query( dbhandle, sql );
5600         if( NULL == result ) {
5601                 const char* msg;
5602                 int errnum = dbi_conn_error( dbhandle, &msg );
5603                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]: %d %s",
5604                         modulename, osrfHashGet( class_meta, "fieldmapper" ), sql, errnum,
5605                         msg ? msg : "(No description available)" );
5606                 if( !oilsIsDBConnected( dbhandle ))
5607                         osrfAppSessionPanic( ctx->session );
5608                 osrfAppSessionStatus(
5609                         ctx->session,
5610                         OSRF_STATUS_INTERNALSERVERERROR,
5611                         "osrfMethodException",
5612                         ctx->request,
5613                         "Severe query error -- see error log for more details"
5614                 );
5615                 *err = -1;
5616                 free( sql );
5617                 return NULL;
5618
5619         } else {
5620                 osrfLogDebug( OSRF_LOG_MARK, "Query returned with no errors" );
5621         }
5622
5623         jsonObject* res_list = jsonNewObjectType( JSON_ARRAY );
5624         jsonObject* row_obj = NULL;
5625
5626         // The following two steps are for verifyObjectPCRUD()'s benefit.
5627         // 1. get the flesh depth
5628         const jsonObject* _tmp = jsonObjectGetKeyConst( query_hash, "flesh" );
5629         if( _tmp ) {
5630                 flesh_depth = (int) jsonObjectGetNumber( _tmp );
5631                 if( flesh_depth == -1 || flesh_depth > max_flesh_depth )
5632                         flesh_depth = max_flesh_depth;
5633         }
5634
5635         // 2. figure out one consistent rs_size for verifyObjectPCRUD to use
5636         // over the whole life of this request.  This means if we've already set
5637         // up a rs_size_req_%d, do nothing.
5638         //      a. Incidentally, we can also use this opportunity to set i_respond_directly
5639         int *rs_size = osrfHashGetFmt( (osrfHash *) ctx->session->userData, "rs_size_req_%d", ctx->request );
5640         if( !rs_size ) {        // pointer null, so value not set in hash
5641                 // i_respond_directly can only be true at the /top/ of a recursive search, if even that.
5642                 i_respond_directly = ( *methodtype == 'r' || *methodtype == 'i' || *methodtype == 's' );
5643
5644                 rs_size = (int *) safe_malloc( sizeof(int) );   // will be freed by sessionDataFree()
5645                 unsigned long long result_count = dbi_result_get_numrows( result );
5646                 *rs_size = (int) result_count * (flesh_depth + 1);      // yes, we could lose some bits, but come on
5647                 osrfHashSet( (osrfHash *) ctx->session->userData, rs_size, "rs_size_req_%d", ctx->request );
5648         }
5649
5650         if( dbi_result_first_row( result )) {
5651
5652                 // Convert each row to a JSON_ARRAY of column values, and enclose those objects
5653                 // in a JSON_ARRAY of rows.  If two or more rows have the same key value, then
5654                 // eliminate the duplicates.
5655                 osrfLogDebug( OSRF_LOG_MARK, "Query returned at least one row" );
5656                 osrfHash* dedup = osrfNewHash();
5657                 do {
5658                         row_obj = oilsMakeFieldmapperFromResult( result, class_meta );
5659                         char* pkey_val = oilsFMGetString( row_obj, pkey );
5660                         if( osrfHashGet( dedup, pkey_val ) ) {
5661                                 jsonObjectFree( row_obj );
5662                                 free( pkey_val );
5663                         } else {
5664                                 if( !enforce_pcrud || !need_to_verify ||
5665                                                 verifyObjectPCRUD( ctx, class_meta, row_obj, 0 /* means check user data for rs_size */ )) {
5666                                         osrfHashSet( dedup, pkey_val, pkey_val );
5667                                         jsonObjectPush( res_list, row_obj );
5668                                 }
5669                         }
5670                 } while( dbi_result_next_row( result ));
5671                 osrfHashFree( dedup );
5672
5673         } else {
5674                 osrfLogDebug( OSRF_LOG_MARK, "%s returned no results for query %s",
5675                         modulename, sql );
5676         }
5677
5678         /* clean up the query */
5679         dbi_result_free( result );
5680         free( sql );
5681
5682         // If we're asked to flesh, and there's anything to flesh, then flesh it
5683         // (formerly we would skip fleshing if in pcrud mode, but now we support
5684         // fleshing even in PCRUD).
5685         if( res_list->size ) {
5686                 jsonObject* temp_blob;  // We need a non-zero flesh depth, and a list of fields to flesh
5687                 jsonObject* flesh_fields;
5688                 jsonObject* flesh_blob = NULL;
5689                 osrfStringArray* link_fields = NULL;
5690                 osrfHash* links = NULL;
5691                 int want_flesh = 0;
5692
5693                 if( query_hash ) {
5694                         temp_blob = jsonObjectGetKey( query_hash, "flesh_fields" );
5695                         if( temp_blob && flesh_depth > 0 ) {
5696
5697                                 flesh_blob = jsonObjectClone( temp_blob );
5698                                 flesh_fields = jsonObjectGetKey( flesh_blob, core_class );
5699
5700                                 links = osrfHashGet( class_meta, "links" );
5701
5702                                 // Make an osrfStringArray of the names of fields to be fleshed
5703                                 if( flesh_fields ) {
5704                                         if( flesh_fields->size == 1 ) {
5705                                                 const char* _t = jsonObjectGetString(
5706                                                         jsonObjectGetIndex( flesh_fields, 0 ) );
5707                                                 if( !strcmp( _t, "*" ))
5708                                                         link_fields = osrfHashKeys( links );
5709                                         }
5710
5711                                         if( !link_fields ) {
5712                                                 jsonObject* _f;
5713                                                 link_fields = osrfNewStringArray( 1 );
5714                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
5715                                                 while ((_f = jsonIteratorNext( _i ))) {
5716                                                         osrfStringArrayAdd( link_fields, jsonObjectGetString( _f ) );
5717                                                 }
5718                                                 jsonIteratorFree( _i );
5719                                         }
5720                                 }
5721                                 want_flesh = link_fields ? 1 : 0;
5722                         }
5723                 }
5724
5725                 osrfHash* fields = osrfHashGet( class_meta, "fields" );
5726
5727                 // Iterate over the JSON_ARRAY of rows
5728                 jsonObject* cur;
5729                 unsigned long res_idx = 0;
5730                 while((cur = jsonObjectGetIndex( res_list, res_idx++ ) )) {
5731
5732                         int i = 0;
5733                         const char* link_field;
5734
5735                         // Iterate over the list of fleshable fields
5736                         if ( want_flesh ) {
5737                                 while( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
5738
5739                                         osrfLogDebug( OSRF_LOG_MARK, "Starting to flesh %s", link_field );
5740
5741                                         osrfHash* kid_link = osrfHashGet( links, link_field );
5742                                         if( !kid_link )
5743                                                 continue;     // Not a link field; skip it
5744
5745                                         osrfHash* field = osrfHashGet( fields, link_field );
5746                                         if( !field )
5747                                                 continue;     // Not a field at all; skip it (IDL is ill-formed)
5748
5749                                         osrfHash* kid_idl = osrfHashGet( oilsIDL(),
5750                                                 osrfHashGet( kid_link, "class" ));
5751                                         if( !kid_idl )
5752                                                 continue;   // The class it links to doesn't exist; skip it
5753
5754                                         const char* reltype = osrfHashGet( kid_link, "reltype" );
5755                                         if( !reltype )
5756                                                 continue;   // No reltype; skip it (IDL is ill-formed)
5757
5758                                         osrfHash* value_field = field;
5759
5760                                         if(    !strcmp( reltype, "has_many" )
5761                                                 || !strcmp( reltype, "might_have" ) ) { // has_many or might_have
5762                                                 value_field = osrfHashGet(
5763                                                         fields, osrfHashGet( class_meta, "primarykey" ) );
5764                                         }
5765
5766                                         osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
5767
5768                                         if( link_map->size > 0 ) {
5769                                                 jsonObject* _kid_key = jsonNewObjectType( JSON_ARRAY );
5770                                                 jsonObjectPush(
5771                                                         _kid_key,
5772                                                         jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
5773                                                 );
5774
5775                                                 jsonObjectSetKey(
5776                                                         flesh_blob,
5777                                                         osrfHashGet( kid_link, "class" ),
5778                                                         _kid_key
5779                                                 );
5780                                         };
5781
5782                                         osrfLogDebug(
5783                                                 OSRF_LOG_MARK,
5784                                                 "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
5785                                                 osrfHashGet( kid_link, "field" ),
5786                                                 osrfHashGet( kid_link, "class" ),
5787                                                 osrfHashGet( kid_link, "key" ),
5788                                                 osrfHashGet( kid_link, "reltype" )
5789                                         );
5790
5791                                         const char* search_key = jsonObjectGetString(
5792                                                 jsonObjectGetIndex( cur,
5793                                                         atoi( osrfHashGet( value_field, "array_position" ) )
5794                                                 )
5795                                         );
5796
5797                                         if( !search_key ) {
5798                                                 osrfLogDebug( OSRF_LOG_MARK, "Nothing to search for!" );
5799                                                 continue;
5800                                         }
5801
5802                                         osrfLogDebug( OSRF_LOG_MARK, "Creating param objects..." );
5803
5804                                         // construct WHERE clause
5805                                         jsonObject* where_clause  = jsonNewObjectType( JSON_HASH );
5806                                         jsonObjectSetKey(
5807                                                 where_clause,
5808                                                 osrfHashGet( kid_link, "key" ),
5809                                                 jsonNewObject( search_key )
5810                                         );
5811
5812                                         // construct the rest of the query, mostly
5813                                         // by copying pieces of the previous level of query
5814                                         jsonObject* rest_of_query = jsonNewObjectType( JSON_HASH );
5815                                         jsonObjectSetKey( rest_of_query, "flesh",
5816                                                 jsonNewNumberObject( flesh_depth - 1 + link_map->size )
5817                                         );
5818
5819                                         if( flesh_blob )
5820                                                 jsonObjectSetKey( rest_of_query, "flesh_fields",
5821                                                         jsonObjectClone( flesh_blob ));
5822
5823                                         if( jsonObjectGetKeyConst( query_hash, "order_by" )) {
5824                                                 jsonObjectSetKey( rest_of_query, "order_by",
5825                                                         jsonObjectClone( jsonObjectGetKeyConst( query_hash, "order_by" ))
5826                                                 );
5827                                         }
5828
5829                                         if( jsonObjectGetKeyConst( query_hash, "select" )) {
5830                                                 jsonObjectSetKey( rest_of_query, "select",
5831                                                         jsonObjectClone( jsonObjectGetKeyConst( query_hash, "select" ))
5832                                                 );
5833                                         }
5834
5835                                         // do the query, recursively, to expand the fleshable field
5836                                         jsonObject* kids = doFieldmapperSearch( ctx, kid_idl,
5837                                                 where_clause, rest_of_query, err );
5838
5839                                         jsonObjectFree( where_clause );
5840                                         jsonObjectFree( rest_of_query );
5841
5842                                         if( *err ) {
5843                                                 osrfStringArrayFree( link_fields );
5844                                                 jsonObjectFree( res_list );
5845                                                 jsonObjectFree( flesh_blob );
5846                                                 return NULL;
5847                                         }
5848
5849                                         osrfLogDebug( OSRF_LOG_MARK, "Search for %s return %d linked objects",
5850                                                 osrfHashGet( kid_link, "class" ), kids->size );
5851
5852                                         // Traverse the result set
5853                                         jsonObject* X = NULL;
5854                                         if( link_map->size > 0 && kids->size > 0 ) {
5855                                                 X = kids;
5856                                                 kids = jsonNewObjectType( JSON_ARRAY );
5857
5858                                                 jsonObject* _k_node;
5859                                                 unsigned long res_idx = 0;
5860                                                 while((_k_node = jsonObjectGetIndex( X, res_idx++ ) )) {
5861                                                         jsonObjectPush(
5862                                                                 kids,
5863                                                                 jsonObjectClone(
5864                                                                         jsonObjectGetIndex(
5865                                                                                 _k_node,
5866                                                                                 (unsigned long) atoi(
5867                                                                                         osrfHashGet(
5868                                                                                                 osrfHashGet(
5869                                                                                                         osrfHashGet(
5870                                                                                                                 osrfHashGet(
5871                                                                                                                         oilsIDL(),
5872                                                                                                                         osrfHashGet( kid_link, "class" )
5873                                                                                                                 ),
5874                                                                                                                 "fields"
5875                                                                                                         ),
5876                                                                                                         osrfStringArrayGetString( link_map, 0 )
5877                                                                                                 ),
5878                                                                                                 "array_position"
5879                                                                                         )
5880                                                                                 )
5881                                                                         )
5882                                                                 )
5883                                                         );
5884                                                 } // end while loop traversing X
5885                                         }
5886
5887                                         if((   !strcmp( osrfHashGet( kid_link, "reltype" ), "has_a" )
5888                                                 || !strcmp( osrfHashGet( kid_link, "reltype" ), "might_have" ))
5889                                                 && (!enforce_pcrud || JSON_NULL != jsonObjectGetIndex( kids, 0 )->type)
5890                                         ) {
5891                                                 osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s",
5892                                                         osrfHashGet( kid_link, "field" ));
5893                                                 jsonObjectSetIndex(
5894                                                         cur,
5895                                                         (unsigned long) atoi( osrfHashGet( field, "array_position" ) ),
5896                                                         jsonObjectClone( jsonObjectGetIndex( kids, 0 ))
5897                                                 );
5898                                         }
5899
5900                                         if( !strcmp( osrfHashGet( kid_link, "reltype" ), "has_many" )) {
5901                                                 // has_many
5902                                                 osrfLogDebug( OSRF_LOG_MARK, "Storing fleshed objects in %s",
5903                                                         osrfHashGet( kid_link, "field" ) );
5904                                                 jsonObjectSetIndex(
5905                                                         cur,
5906                                                         (unsigned long) atoi( osrfHashGet( field, "array_position" ) ),
5907                                                         jsonObjectClone( kids )
5908                                                 );
5909                                         }
5910
5911                                         if( X ) {
5912                                                 jsonObjectFree( kids );
5913                                                 kids = X;
5914                                         }
5915
5916                                         jsonObjectFree( kids );
5917
5918                                         osrfLogDebug( OSRF_LOG_MARK, "Fleshing of %s complete",
5919                                                 osrfHashGet( kid_link, "field" ) );
5920                                         osrfLogDebug( OSRF_LOG_MARK, "%s", jsonObjectToJSON( cur ));
5921
5922                                 } // end while loop traversing list of fleshable fields
5923                         }
5924
5925                         if( i_respond_directly ) {
5926                                 if ( *methodtype == 'i' ) {
5927                                         osrfAppRespond( ctx,
5928                                                 oilsFMGetObject( cur, osrfHashGet( class_meta, "primarykey" ) ) );
5929                                 } else {
5930                                         osrfAppRespond( ctx, cur );
5931                                 }
5932                         }
5933                 } // end while loop traversing res_list
5934                 jsonObjectFree( flesh_blob );
5935                 osrfStringArrayFree( link_fields );
5936         }
5937
5938         if( i_respond_directly ) {
5939                 jsonObjectFree( res_list );
5940                 return jsonNewObjectType( JSON_ARRAY );
5941         } else {
5942                 return res_list;
5943         }
5944 }
5945
5946
5947 int doUpdate( osrfMethodContext* ctx ) {
5948         if( osrfMethodVerifyContext( ctx )) {
5949                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
5950                 return -1;
5951         }
5952
5953         if( enforce_pcrud )
5954                 timeout_needs_resetting = 1;
5955
5956         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
5957
5958         jsonObject* target = NULL;
5959         if( enforce_pcrud )
5960                 target = jsonObjectGetIndex( ctx->params, 1 );
5961         else
5962                 target = jsonObjectGetIndex( ctx->params, 0 );
5963
5964         if(!verifyObjectClass( ctx, target )) {
5965                 osrfAppRespondComplete( ctx, NULL );
5966                 return -1;
5967         }
5968
5969         if( getXactId( ctx ) == NULL ) {
5970                 osrfAppSessionStatus(
5971                         ctx->session,
5972                         OSRF_STATUS_BADREQUEST,
5973                         "osrfMethodException",
5974                         ctx->request,
5975                         "No active transaction -- required for UPDATE"
5976                 );
5977                 osrfAppRespondComplete( ctx, NULL );
5978                 return -1;
5979         }
5980
5981         // The following test is harmless but redundant.  If a class is
5982         // readonly, we don't register an update method for it.
5983         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
5984                 osrfAppSessionStatus(
5985                         ctx->session,
5986                         OSRF_STATUS_BADREQUEST,
5987                         "osrfMethodException",
5988                         ctx->request,
5989                         "Cannot UPDATE readonly class"
5990                 );
5991                 osrfAppRespondComplete( ctx, NULL );
5992                 return -1;
5993         }
5994
5995         const char* trans_id = getXactId( ctx );
5996
5997         // Set the last_xact_id
5998         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
5999         if( index > -1 ) {
6000                 osrfLogDebug( OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
6001                                 trans_id, target->classname, index );
6002                 jsonObjectSetIndex( target, index, jsonNewObject( trans_id ));
6003         }
6004
6005         char* pkey = osrfHashGet( meta, "primarykey" );
6006         osrfHash* fields = osrfHashGet( meta, "fields" );
6007
6008         char* id = oilsFMGetString( target, pkey );
6009
6010         osrfLogDebug(
6011                 OSRF_LOG_MARK,
6012                 "%s updating %s object with %s = %s",
6013                 modulename,
6014                 osrfHashGet( meta, "fieldmapper" ),
6015                 pkey,
6016                 id
6017         );
6018
6019         dbhandle = writehandle;
6020         growing_buffer* sql = buffer_init( 128 );
6021         buffer_fadd( sql,"UPDATE %s SET", osrfHashGet( meta, "tablename" ));
6022
6023         int first = 1;
6024         osrfHash* field_def = NULL;
6025         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
6026         while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
6027
6028                 // Skip virtual fields, and the primary key
6029                 if( str_is_true( osrfHashGet( field_def, "virtual") ) )
6030                         continue;
6031
6032                 const char* field_name = osrfHashIteratorKey( field_itr );
6033                 if( ! strcmp( field_name, pkey ) )
6034                         continue;
6035
6036                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
6037
6038                 int value_is_numeric = 0;    // boolean
6039                 char* value;
6040                 if( field_object && field_object->classname ) {
6041                         value = oilsFMGetString(
6042                                 field_object,
6043                                 (char*) oilsIDLFindPath( "/%s/primarykey", field_object->classname )
6044                         );
6045                 } else if( field_object && JSON_BOOL == field_object->type ) {
6046                         if( jsonBoolIsTrue( field_object ) )
6047                                 value = strdup( "t" );
6048                         else
6049                                 value = strdup( "f" );
6050                 } else {
6051                         value = jsonObjectToSimpleString( field_object );
6052                         if( field_object && JSON_NUMBER == field_object->type )
6053                                 value_is_numeric = 1;
6054                 }
6055
6056                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s",
6057                                 osrfHashGet( meta, "fieldmapper" ), field_name, value);
6058
6059                 if( !field_object || field_object->type == JSON_NULL ) {
6060                         if( !( !( strcmp( osrfHashGet( meta, "classname" ), "au" ) )
6061                                         && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
6062                                 if( first )
6063                                         first = 0;
6064                                 else
6065                                         OSRF_BUFFER_ADD_CHAR( sql, ',' );
6066                                 buffer_fadd( sql, " %s = NULL", field_name );
6067                         }
6068
6069                 } else if( value_is_numeric || !strcmp( get_primitive( field_def ), "number") ) {
6070                         if( first )
6071                                 first = 0;
6072                         else
6073                                 OSRF_BUFFER_ADD_CHAR( sql, ',' );
6074
6075                         const char* numtype = get_datatype( field_def );
6076                         if( !strncmp( numtype, "INT", 3 ) ) {
6077                                 buffer_fadd( sql, " %s = %ld", field_name, atol( value ) );
6078                         } else if( !strcmp( numtype, "NUMERIC" ) ) {
6079                                 buffer_fadd( sql, " %s = %f", field_name, atof( value ) );
6080                         } else {
6081                                 // Must really be intended as a string, so quote it
6082                                 if( dbi_conn_quote_string( dbhandle, &value )) {
6083                                         buffer_fadd( sql, " %s = %s", field_name, value );
6084                                 } else {
6085                                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]",
6086                                                 modulename, value );
6087                                         osrfAppSessionStatus(
6088                                                 ctx->session,
6089                                                 OSRF_STATUS_INTERNALSERVERERROR,
6090                                                 "osrfMethodException",
6091                                                 ctx->request,
6092                                                 "Error quoting string -- please see the error log for more details"
6093                                         );
6094                                         free( value );
6095                                         free( id );
6096                                         osrfHashIteratorFree( field_itr );
6097                                         buffer_free( sql );
6098                                         osrfAppRespondComplete( ctx, NULL );
6099                                         return -1;
6100                                 }
6101                         }
6102
6103                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, numtype );
6104
6105                 } else {
6106                         if( dbi_conn_quote_string( dbhandle, &value ) ) {
6107                                 if( first )
6108                                         first = 0;
6109                                 else
6110                                         OSRF_BUFFER_ADD_CHAR( sql, ',' );
6111                                 buffer_fadd( sql, " %s = %s", field_name, value );
6112                         } else {
6113                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value );
6114                                 osrfAppSessionStatus(
6115                                         ctx->session,
6116                                         OSRF_STATUS_INTERNALSERVERERROR,
6117                                         "osrfMethodException",
6118                                         ctx->request,
6119                                         "Error quoting string -- please see the error log for more details"
6120                                 );
6121                                 free( value );
6122                                 free( id );
6123                                 osrfHashIteratorFree( field_itr );
6124                                 buffer_free( sql );
6125                                 osrfAppRespondComplete( ctx, NULL );
6126                                 return -1;
6127                         }
6128                 }
6129
6130                 free( value );
6131
6132         } // end while
6133
6134         osrfHashIteratorFree( field_itr );
6135
6136         jsonObject* obj = jsonNewObject( id );
6137
6138         if( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey )), "number" ))
6139                 dbi_conn_quote_string( dbhandle, &id );
6140
6141         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
6142
6143         char* query = buffer_release( sql );
6144         osrfLogDebug( OSRF_LOG_MARK, "%s: Update SQL [%s]", modulename, query );
6145
6146         dbi_result result = dbi_conn_query( dbhandle, query );
6147         free( query );
6148
6149         int rc = 0;
6150         if( !result ) {
6151                 jsonObjectFree( obj );
6152                 obj = jsonNewObject( NULL );
6153                 const char* msg;
6154                 int errnum = dbi_conn_error( dbhandle, &msg );
6155                 osrfLogError(
6156                         OSRF_LOG_MARK,
6157                         "%s ERROR updating %s object with %s = %s: %d %s",
6158                         modulename,
6159                         osrfHashGet( meta, "fieldmapper" ),
6160                         pkey,
6161                         id,
6162                         errnum,
6163                         msg ? msg : "(No description available)"
6164                 );
6165                 osrfAppSessionStatus(
6166                         ctx->session,
6167                         OSRF_STATUS_INTERNALSERVERERROR,
6168                         "osrfMethodException",
6169                         ctx->request,
6170                         "Error in updating a row -- please see the error log for more details"
6171                 );
6172                 if( !oilsIsDBConnected( dbhandle ))
6173                         osrfAppSessionPanic( ctx->session );
6174                 rc = -1;
6175         } else
6176                 dbi_result_free( result );
6177
6178         free( id );
6179         osrfAppRespondComplete( ctx, obj );
6180         jsonObjectFree( obj );
6181         return rc;
6182 }
6183
6184 int doDelete( osrfMethodContext* ctx ) {
6185         if( osrfMethodVerifyContext( ctx )) {
6186                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
6187                 return -1;
6188         }
6189
6190         if( enforce_pcrud )
6191                 timeout_needs_resetting = 1;
6192
6193         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
6194
6195         if( getXactId( ctx ) == NULL ) {
6196                 osrfAppSessionStatus(
6197                         ctx->session,
6198                         OSRF_STATUS_BADREQUEST,
6199                         "osrfMethodException",
6200                         ctx->request,
6201                         "No active transaction -- required for DELETE"
6202                 );
6203                 osrfAppRespondComplete( ctx, NULL );
6204                 return -1;
6205         }
6206
6207         // The following test is harmless but redundant.  If a class is
6208         // readonly, we don't register a delete method for it.
6209         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
6210                 osrfAppSessionStatus(
6211                         ctx->session,
6212                         OSRF_STATUS_BADREQUEST,
6213                         "osrfMethodException",
6214                         ctx->request,
6215                         "Cannot DELETE readonly class"
6216                 );
6217                 osrfAppRespondComplete( ctx, NULL );
6218                 return -1;
6219         }
6220
6221         dbhandle = writehandle;
6222
6223         char* pkey = osrfHashGet( meta, "primarykey" );
6224
6225         int _obj_pos = 0;
6226         if( enforce_pcrud )
6227                 _obj_pos = 1;
6228
6229         char* id;
6230         if( jsonObjectGetIndex( ctx->params, _obj_pos )->classname ) {
6231                 if( !verifyObjectClass( ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
6232                         osrfAppRespondComplete( ctx, NULL );
6233                         return -1;
6234                 }
6235
6236                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
6237         } else {
6238                 if( enforce_pcrud && !verifyObjectPCRUD( ctx, meta, NULL, 1 )) {
6239                         osrfAppRespondComplete( ctx, NULL );
6240                         return -1;
6241                 }
6242                 id = jsonObjectToSimpleString( jsonObjectGetIndex( ctx->params, _obj_pos ));
6243         }
6244
6245         osrfLogDebug(
6246                 OSRF_LOG_MARK,
6247                 "%s deleting %s object with %s = %s",
6248                 modulename,
6249                 osrfHashGet( meta, "fieldmapper" ),
6250                 pkey,
6251                 id
6252         );
6253
6254         jsonObject* obj = jsonNewObject( id );
6255
6256         if( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
6257                 dbi_conn_quote_string( writehandle, &id );
6258
6259         dbi_result result = dbi_conn_queryf( writehandle, "DELETE FROM %s WHERE %s = %s;",
6260                 osrfHashGet( meta, "tablename" ), pkey, id );
6261
6262         int rc = 0;
6263         if( !result ) {
6264                 rc = -1;
6265                 jsonObjectFree( obj );
6266                 obj = jsonNewObject( NULL );
6267                 const char* msg;
6268                 int errnum = dbi_conn_error( writehandle, &msg );
6269                 osrfLogError(
6270                         OSRF_LOG_MARK,
6271                         "%s ERROR deleting %s object with %s = %s: %d %s",
6272                         modulename,
6273                         osrfHashGet( meta, "fieldmapper" ),
6274                         pkey,
6275                         id,
6276                         errnum,
6277                         msg ? msg : "(No description available)"
6278                 );
6279                 osrfAppSessionStatus(
6280                         ctx->session,
6281                         OSRF_STATUS_INTERNALSERVERERROR,
6282                         "osrfMethodException",
6283                         ctx->request,
6284                         "Error in deleting a row -- please see the error log for more details"
6285                 );
6286                 if( !oilsIsDBConnected( writehandle ))
6287                         osrfAppSessionPanic( ctx->session );
6288         } else
6289                 dbi_result_free( result );
6290
6291         free( id );
6292
6293         osrfAppRespondComplete( ctx, obj );
6294         jsonObjectFree( obj );
6295         return rc;
6296 }
6297
6298 /**
6299         @brief Translate a row returned from the database into a jsonObject of type JSON_ARRAY.
6300         @param result An iterator for a result set; we only look at the current row.
6301         @param @meta Pointer to the class metadata for the core class.
6302         @return Pointer to the resulting jsonObject if successful; otherwise NULL.
6303
6304         If a column is not defined in the IDL, or if it has no array_position defined for it in
6305         the IDL, or if it is defined as virtual, ignore it.
6306
6307         Otherwise, translate the column value into a jsonObject of type JSON_NULL, JSON_NUMBER,
6308         or JSON_STRING.  Then insert this jsonObject into the JSON_ARRAY according to its
6309         array_position in the IDL.
6310
6311         A field defined in the IDL but not represented in the returned row will leave a hole
6312         in the JSON_ARRAY.  In effect it will be treated as a null value.
6313
6314         In the resulting JSON_ARRAY, the field values appear in the sequence defined by the IDL,
6315         regardless of their sequence in the SELECT statement.  The JSON_ARRAY is assigned the
6316         classname corresponding to the @a meta argument.
6317
6318         The calling code is responsible for freeing the the resulting jsonObject by calling
6319         jsonObjectFree().
6320 */
6321 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
6322         if( !( result && meta )) return NULL;
6323
6324         jsonObject* object = jsonNewObjectType( JSON_ARRAY );
6325         jsonObjectSetClass( object, osrfHashGet( meta, "classname" ));
6326         osrfLogInternal( OSRF_LOG_MARK, "Setting object class to %s ", object->classname );
6327
6328         osrfHash* fields = osrfHashGet( meta, "fields" );
6329
6330         int columnIndex = 1;
6331         const char* columnName;
6332
6333         /* cycle through the columns in the row returned from the database */
6334         while( (columnName = dbi_result_get_field_name( result, columnIndex )) ) {
6335
6336                 osrfLogInternal( OSRF_LOG_MARK, "Looking for column named [%s]...", (char*) columnName );
6337
6338                 int fmIndex = -1;  // Will be set to the IDL's sequence number for this field
6339
6340                 /* determine the field type and storage attributes */
6341                 unsigned short type = dbi_result_get_field_type_idx( result, columnIndex );
6342                 int attr            = dbi_result_get_field_attribs_idx( result, columnIndex );
6343
6344                 // Fetch the IDL's sequence number for the field.  If the field isn't in the IDL,
6345                 // or if it has no sequence number there, or if it's virtual, skip it.
6346                 osrfHash* _f = osrfHashGet( fields, (char*) columnName );
6347                 if( _f ) {
6348
6349                         if( str_is_true( osrfHashGet( _f, "virtual" )))
6350                                 continue;   // skip this column: IDL says it's virtual
6351
6352                         const char* pos = (char*) osrfHashGet( _f, "array_position" );
6353                         if( !pos )      // IDL has no sequence number for it.  This shouldn't happen,
6354                                 continue;    // since we assign sequence numbers dynamically as we load the IDL.
6355
6356                         fmIndex = atoi( pos );
6357                         osrfLogInternal( OSRF_LOG_MARK, "... Found column at position [%s]...", pos );
6358                 } else {
6359                         continue;     // This field is not defined in the IDL
6360                 }
6361
6362                 // Stuff the column value into a slot in the JSON_ARRAY, indexed according to the
6363                 // sequence number from the IDL (which is likely to be different from the sequence
6364                 // of columns in the SELECT clause).
6365                 if( dbi_result_field_is_null_idx( result, columnIndex )) {
6366                         jsonObjectSetIndex( object, fmIndex, jsonNewObject( NULL ));
6367                 } else {
6368
6369                         switch( type ) {
6370
6371                                 case DBI_TYPE_INTEGER :
6372
6373                                         if( attr & DBI_INTEGER_SIZE8 )
6374                                                 jsonObjectSetIndex( object, fmIndex,
6375                                                         jsonNewNumberObject(
6376                                                                 dbi_result_get_longlong_idx( result, columnIndex )));
6377                                         else
6378                                                 jsonObjectSetIndex( object, fmIndex,
6379                                                         jsonNewNumberObject( dbi_result_get_int_idx( result, columnIndex )));
6380
6381                                         break;
6382
6383                                 case DBI_TYPE_DECIMAL :
6384                                         jsonObjectSetIndex( object, fmIndex,
6385                                                         jsonNewNumberObject( dbi_result_get_double_idx(result, columnIndex )));
6386                                         break;
6387
6388                                 case DBI_TYPE_STRING :
6389
6390                                         jsonObjectSetIndex(
6391                                                 object,
6392                                                 fmIndex,
6393                                                 jsonNewObject( dbi_result_get_string_idx( result, columnIndex ))
6394                                         );
6395
6396                                         break;
6397
6398                                 case DBI_TYPE_DATETIME : {
6399
6400                                         char dt_string[ 256 ] = "";
6401                                         struct tm gmdt;
6402
6403                                         // Fetch the date column as a time_t
6404                                         time_t _tmp_dt = dbi_result_get_datetime_idx( result, columnIndex );
6405
6406                                         // Translate the time_t to a human-readable string
6407                                         if( !( attr & DBI_DATETIME_DATE )) {
6408                                                 gmtime_r( &_tmp_dt, &gmdt );
6409                                                 strftime( dt_string, sizeof( dt_string ), "%T", &gmdt );
6410                                         } else if( !( attr & DBI_DATETIME_TIME )) {
6411                                                 localtime_r( &_tmp_dt, &gmdt );
6412                                                 strftime( dt_string, sizeof( dt_string ), "%F", &gmdt );
6413                                         } else {
6414                                                 localtime_r( &_tmp_dt, &gmdt );
6415                                                 strftime( dt_string, sizeof( dt_string ), "%FT%T%z", &gmdt );
6416                                         }
6417
6418                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject( dt_string ));
6419
6420                                         break;
6421                                 }
6422                                 case DBI_TYPE_BINARY :
6423                                         osrfLogError( OSRF_LOG_MARK,
6424                                                 "Can't do binary at column %s : index %d", columnName, columnIndex );
6425                         } // End switch
6426                 }
6427                 ++columnIndex;
6428         } // End while
6429
6430         return object;
6431 }
6432
6433 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
6434         if( !result ) return NULL;
6435
6436         jsonObject* object = jsonNewObject( NULL );
6437
6438         time_t _tmp_dt;
6439         char dt_string[ 256 ];
6440         struct tm gmdt;
6441
6442         int fmIndex;
6443         int columnIndex = 1;
6444         int attr;
6445         unsigned short type;
6446         const char* columnName;
6447
6448         /* cycle through the column list */
6449         while(( columnName = dbi_result_get_field_name( result, columnIndex ))) {
6450
6451                 osrfLogInternal( OSRF_LOG_MARK, "Looking for column named [%s]...", (char*) columnName );
6452
6453                 fmIndex = -1; // reset the position
6454
6455                 /* determine the field type and storage attributes */
6456                 type = dbi_result_get_field_type_idx( result, columnIndex );
6457                 attr = dbi_result_get_field_attribs_idx( result, columnIndex );
6458
6459                 if( dbi_result_field_is_null_idx( result, columnIndex )) {
6460                         jsonObjectSetKey( object, columnName, jsonNewObject( NULL ));
6461                 } else {
6462
6463                         switch( type ) {
6464
6465                                 case DBI_TYPE_INTEGER :
6466
6467                                         if( attr & DBI_INTEGER_SIZE8 )
6468                                                 jsonObjectSetKey( object, columnName,
6469                                                                 jsonNewNumberObject( dbi_result_get_longlong_idx(
6470                                                                                 result, columnIndex )) );
6471                                         else
6472                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(
6473                                                                 dbi_result_get_int_idx( result, columnIndex )) );
6474                                         break;
6475
6476                                 case DBI_TYPE_DECIMAL :
6477                                         jsonObjectSetKey( object, columnName, jsonNewNumberObject(
6478                                                 dbi_result_get_double_idx( result, columnIndex )) );
6479                                         break;
6480
6481                                 case DBI_TYPE_STRING :
6482                                         jsonObjectSetKey( object, columnName,
6483                                                 jsonNewObject( dbi_result_get_string_idx( result, columnIndex )));
6484                                         break;
6485
6486                                 case DBI_TYPE_DATETIME :
6487
6488                                         memset( dt_string, '\0', sizeof( dt_string ));
6489                                         memset( &gmdt, '\0', sizeof( gmdt ));
6490
6491                                         _tmp_dt = dbi_result_get_datetime_idx( result, columnIndex );
6492
6493                                         if( !( attr & DBI_DATETIME_DATE )) {
6494                                                 gmtime_r( &_tmp_dt, &gmdt );
6495                                                 strftime( dt_string, sizeof( dt_string ), "%T", &gmdt );
6496                                         } else if( !( attr & DBI_DATETIME_TIME )) {
6497                                                 localtime_r( &_tmp_dt, &gmdt );
6498                                                 strftime( dt_string, sizeof( dt_string ), "%F", &gmdt );
6499                                         } else {
6500                                                 localtime_r( &_tmp_dt, &gmdt );
6501                                                 strftime( dt_string, sizeof( dt_string ), "%FT%T%z", &gmdt );
6502                                         }
6503
6504                                         jsonObjectSetKey( object, columnName, jsonNewObject( dt_string ));
6505                                         break;
6506
6507                                 case DBI_TYPE_BINARY :
6508                                         osrfLogError( OSRF_LOG_MARK,
6509                                                 "Can't do binary at column %s : index %d", columnName, columnIndex );
6510                         }
6511                 }
6512                 ++columnIndex;
6513         } // end while loop traversing result
6514
6515         return object;
6516 }
6517
6518 // Interpret a string as true or false
6519 int str_is_true( const char* str ) {
6520         if( NULL == str || strcasecmp( str, "true" ) )
6521                 return 0;
6522         else
6523                 return 1;
6524 }
6525
6526 // Interpret a jsonObject as true or false
6527 static int obj_is_true( const jsonObject* obj ) {
6528         if( !obj )
6529                 return 0;
6530         else switch( obj->type )
6531         {
6532                 case JSON_BOOL :
6533                         if( obj->value.b )
6534                                 return 1;
6535                         else
6536                                 return 0;
6537                 case JSON_STRING :
6538                         if( strcasecmp( obj->value.s, "true" ) )
6539                                 return 0;
6540                         else
6541                                 return 1;
6542                 case JSON_NUMBER :          // Support 1/0 for perl's sake
6543                         if( jsonObjectGetNumber( obj ) == 1.0 )
6544                                 return 1;
6545                         else
6546                                 return 0;
6547                 default :
6548                         return 0;
6549         }
6550 }
6551
6552 // Translate a numeric code into a text string identifying a type of
6553 // jsonObject.  To be used for building error messages.
6554 static const char* json_type( int code ) {
6555         switch ( code )
6556         {
6557                 case 0 :
6558                         return "JSON_HASH";
6559                 case 1 :
6560                         return "JSON_ARRAY";
6561                 case 2 :
6562                         return "JSON_STRING";
6563                 case 3 :
6564                         return "JSON_NUMBER";
6565                 case 4 :
6566                         return "JSON_NULL";
6567                 case 5 :
6568                         return "JSON_BOOL";
6569                 default :
6570                         return "(unrecognized)";
6571         }
6572 }
6573
6574 // Extract the "primitive" attribute from an IDL field definition.
6575 // If we haven't initialized the app, then we must be running in
6576 // some kind of testbed.  In that case, default to "string".
6577 static const char* get_primitive( osrfHash* field ) {
6578         const char* s = osrfHashGet( field, "primitive" );
6579         if( !s ) {
6580                 if( child_initialized )
6581                         osrfLogError(
6582                                 OSRF_LOG_MARK,
6583                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
6584                                 modulename,
6585                                 osrfHashGet( field, "name" )
6586                         );
6587
6588                 s = "string";
6589         }
6590         return s;
6591 }
6592
6593 // Extract the "datatype" attribute from an IDL field definition.
6594 // If we haven't initialized the app, then we must be running in
6595 // some kind of testbed.  In that case, default to to NUMERIC,
6596 // since we look at the datatype only for numbers.
6597 static const char* get_datatype( osrfHash* field ) {
6598         const char* s = osrfHashGet( field, "datatype" );
6599         if( !s ) {
6600                 if( child_initialized )
6601                         osrfLogError(
6602                                 OSRF_LOG_MARK,
6603                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
6604                                 modulename,
6605                                 osrfHashGet( field, "name" )
6606                         );
6607                 else
6608                         s = "NUMERIC";
6609         }
6610         return s;
6611 }
6612
6613 /**
6614         @brief Determine whether a string is potentially a valid SQL identifier.
6615         @param s The identifier to be tested.
6616         @return 1 if the input string is potentially a valid SQL identifier, or 0 if not.
6617
6618         Purpose: to prevent certain kinds of SQL injection.  To that end we don't necessarily
6619         need to follow all the rules exactly, such as requiring that the first character not
6620         be a digit.
6621
6622         We allow leading and trailing white space.  In between, we do not allow punctuation
6623         (except for underscores and dollar signs), control characters, or embedded white space.
6624
6625         More pedantically we should allow quoted identifiers containing arbitrary characters, but
6626         for the foreseeable future such quoted identifiers are not likely to be an issue.
6627 */
6628 int is_identifier( const char* s) {
6629         if( !s )
6630                 return 0;
6631
6632         // Skip leading white space
6633         while( isspace( (unsigned char) *s ) )
6634                 ++s;
6635
6636         if( !s )
6637                 return 0;   // Nothing but white space?  Not okay.
6638
6639         // Check each character until we reach white space or
6640         // end-of-string.  Letters, digits, underscores, and
6641         // dollar signs are okay. With the exception of periods
6642         // (as in schema.identifier), control characters and other
6643         // punctuation characters are not okay.  Anything else
6644         // is okay -- it could for example be part of a multibyte
6645         // UTF8 character such as a letter with diacritical marks,
6646         // and those are allowed.
6647         do {
6648                 if( isalnum( (unsigned char) *s )
6649                         || '.' == *s
6650                         || '_' == *s
6651                         || '$' == *s )
6652                         ;  // Fine; keep going
6653                 else if(   ispunct( (unsigned char) *s )
6654                                 || iscntrl( (unsigned char) *s ) )
6655                         return 0;
6656                         ++s;
6657         } while( *s && ! isspace( (unsigned char) *s ) );
6658
6659         // If we found any white space in the above loop,
6660         // the rest had better be all white space.
6661
6662         while( isspace( (unsigned char) *s ) )
6663                 ++s;
6664
6665         if( *s )
6666                 return 0;   // White space was embedded within non-white space
6667
6668         return 1;
6669 }
6670
6671 /**
6672         @brief Determine whether to accept a character string as a comparison operator.
6673         @param op The candidate comparison operator.
6674         @return 1 if the string is acceptable as a comparison operator, or 0 if not.
6675
6676         We don't validate the operator for real.  We just make sure that it doesn't contain
6677         any semicolons or white space (with special exceptions for a few specific operators).
6678         The idea is to block certain kinds of SQL injection.  If it has no semicolons or white
6679         space but it's still not a valid operator, then the database will complain.
6680
6681         Another approach would be to compare the string against a short list of approved operators.
6682         We don't do that because we want to allow custom operators like ">100*", which at this
6683         writing would be difficult or impossible to express otherwise in a JSON query.
6684 */
6685 int is_good_operator( const char* op ) {
6686         if( !op ) return 0;   // Sanity check
6687
6688         const char* s = op;
6689         while( *s ) {
6690                 if( isspace( (unsigned char) *s ) ) {
6691                         // Special exceptions for SIMILAR TO, IS DISTINCT FROM,
6692                         // and IS NOT DISTINCT FROM.
6693                         if( !strcasecmp( op, "similar to" ) )
6694                                 return 1;
6695                         else if( !strcasecmp( op, "is distinct from" ) )
6696                                 return 1;
6697                         else if( !strcasecmp( op, "is not distinct from" ) )
6698                                 return 1;
6699                         else
6700                                 return 0;
6701                 }
6702                 else if( ';' == *s )
6703                         return 0;
6704                 ++s;
6705         }
6706         return 1;
6707 }
6708
6709 /**
6710         @name Query Frame Management
6711
6712         The following machinery supports a stack of query frames for use by SELECT().
6713
6714         A query frame caches information about one level of a SELECT query.  When we enter
6715         a subquery, we push another query frame onto the stack, and pop it off when we leave.
6716
6717         The query frame stores information about the core class, and about any joined classes
6718         in the FROM clause.
6719
6720         The main purpose is to map table aliases to classes and tables, so that a query can
6721         join to the same table more than once.  A secondary goal is to reduce the number of
6722         lookups in the IDL by caching the results.
6723 */
6724 /*@{*/
6725
6726 #define STATIC_CLASS_INFO_COUNT 3
6727
6728 static ClassInfo static_class_info[ STATIC_CLASS_INFO_COUNT ];
6729
6730 /**
6731         @brief Allocate a ClassInfo as raw memory.
6732         @return Pointer to the newly allocated ClassInfo.
6733
6734         Except for the in_use flag, which is used only by the allocation and deallocation
6735         logic, we don't initialize the ClassInfo here.
6736 */
6737 static ClassInfo* allocate_class_info( void ) {
6738         // In order to reduce the number of mallocs and frees, we return a static
6739         // instance of ClassInfo, if we can find one that we're not already using.
6740         // We rely on the fact that the compiler will implicitly initialize the
6741         // static instances so that in_use == 0.
6742
6743         int i;
6744         for( i = 0; i < STATIC_CLASS_INFO_COUNT; ++i ) {
6745                 if( ! static_class_info[ i ].in_use ) {
6746                         static_class_info[ i ].in_use = 1;
6747                         return static_class_info + i;
6748                 }
6749         }
6750
6751         // The static ones are all in use.  Malloc one.
6752
6753         return safe_malloc( sizeof( ClassInfo ) );
6754 }
6755
6756 /**
6757         @brief Free any malloc'd memory owned by a ClassInfo, returning it to a pristine state.
6758         @param info Pointer to the ClassInfo to be cleared.
6759 */
6760 static void clear_class_info( ClassInfo* info ) {
6761         // Sanity check
6762         if( ! info )
6763                 return;
6764
6765         // Free any malloc'd strings
6766
6767         if( info->alias != info->alias_store )
6768                 free( info->alias );
6769
6770         if( info->class_name != info->class_name_store )
6771                 free( info->class_name );
6772
6773         free( info->source_def );
6774
6775         info->alias = info->class_name = info->source_def = NULL;
6776         info->next = NULL;
6777 }
6778
6779 /**
6780         @brief Free a ClassInfo and everything it owns.
6781         @param info Pointer to the ClassInfo to be freed.
6782 */
6783 static void free_class_info( ClassInfo* info ) {
6784         // Sanity check
6785         if( ! info )
6786                 return;
6787
6788         clear_class_info( info );
6789
6790         // If it's one of the static instances, just mark it as not in use
6791
6792         int i;
6793         for( i = 0; i < STATIC_CLASS_INFO_COUNT; ++i ) {
6794                 if( info == static_class_info + i ) {
6795                         static_class_info[ i ].in_use = 0;
6796                         return;
6797                 }
6798         }
6799
6800         // Otherwise it must have been malloc'd, so free it
6801
6802         free( info );
6803 }
6804
6805 /**
6806         @brief Populate an already-allocated ClassInfo.
6807         @param info Pointer to the ClassInfo to be populated.
6808         @param alias Alias for the class.  If it is NULL, or an empty string, use the class
6809         name for an alias.
6810         @param class Name of the class.
6811         @return Zero if successful, or 1 if not.
6812
6813         Populate the ClassInfo with copies of the alias and class name, and with pointers to
6814         the relevant portions of the IDL for the specified class.
6815 */
6816 static int build_class_info( ClassInfo* info, const char* alias, const char* class ) {
6817         // Sanity checks
6818         if( ! info ){
6819                 osrfLogError( OSRF_LOG_MARK,
6820                                           "%s ERROR: No ClassInfo available to populate", modulename );
6821                 info->alias = info->class_name = info->source_def = NULL;
6822                 info->class_def = info->fields = info->links = NULL;
6823                 return 1;
6824         }
6825
6826         if( ! class ) {
6827                 osrfLogError( OSRF_LOG_MARK,
6828                                           "%s ERROR: No class name provided for lookup", modulename );
6829                 info->alias = info->class_name = info->source_def = NULL;
6830                 info->class_def = info->fields = info->links = NULL;
6831                 return 1;
6832         }
6833
6834         // Alias defaults to class name if not supplied
6835         if( ! alias || ! alias[ 0 ] )
6836                 alias = class;
6837
6838         // Look up class info in the IDL
6839         osrfHash* class_def = osrfHashGet( oilsIDL(), class );
6840         if( ! class_def ) {
6841                 osrfLogError( OSRF_LOG_MARK,
6842                                           "%s ERROR: Class %s not defined in IDL", modulename, class );
6843                 info->alias = info->class_name = info->source_def = NULL;
6844                 info->class_def = info->fields = info->links = NULL;
6845                 return 1;
6846         } else if( str_is_true( osrfHashGet( class_def, "virtual" ) ) ) {
6847                 osrfLogError( OSRF_LOG_MARK,
6848                                           "%s ERROR: Class %s is defined as virtual", modulename, class );
6849                 info->alias = info->class_name = info->source_def = NULL;
6850                 info->class_def = info->fields = info->links = NULL;
6851                 return 1;
6852         }
6853
6854         osrfHash* links = osrfHashGet( class_def, "links" );
6855         if( ! links ) {
6856                 osrfLogError( OSRF_LOG_MARK,
6857                                           "%s ERROR: No links defined in IDL for class %s", modulename, class );
6858                 info->alias = info->class_name = info->source_def = NULL;
6859                 info->class_def = info->fields = info->links = NULL;
6860                 return 1;
6861         }
6862
6863         osrfHash* fields = osrfHashGet( class_def, "fields" );
6864         if( ! fields ) {
6865                 osrfLogError( OSRF_LOG_MARK,
6866                                           "%s ERROR: No fields defined in IDL for class %s", modulename, class );
6867                 info->alias = info->class_name = info->source_def = NULL;
6868                 info->class_def = info->fields = info->links = NULL;
6869                 return 1;
6870         }
6871
6872         char* source_def = oilsGetRelation( class_def );
6873         if( ! source_def )
6874                 return 1;
6875
6876         // We got everything we need, so populate the ClassInfo
6877         if( strlen( alias ) > ALIAS_STORE_SIZE )
6878                 info->alias = strdup( alias );
6879         else {
6880                 strcpy( info->alias_store, alias );
6881                 info->alias = info->alias_store;
6882         }
6883
6884         if( strlen( class ) > CLASS_NAME_STORE_SIZE )
6885                 info->class_name = strdup( class );
6886         else {
6887                 strcpy( info->class_name_store, class );
6888                 info->class_name = info->class_name_store;
6889         }
6890
6891         info->source_def = source_def;
6892
6893         info->class_def = class_def;
6894         info->links     = links;
6895         info->fields    = fields;
6896
6897         return 0;
6898 }
6899
6900 #define STATIC_FRAME_COUNT 3
6901
6902 static QueryFrame static_frame[ STATIC_FRAME_COUNT ];
6903
6904 /**
6905         @brief Allocate a QueryFrame as raw memory.
6906         @return Pointer to the newly allocated QueryFrame.
6907
6908         Except for the in_use flag, which is used only by the allocation and deallocation
6909         logic, we don't initialize the QueryFrame here.
6910 */
6911 static QueryFrame* allocate_frame( void ) {
6912         // In order to reduce the number of mallocs and frees, we return a static
6913         // instance of QueryFrame, if we can find one that we're not already using.
6914         // We rely on the fact that the compiler will implicitly initialize the
6915         // static instances so that in_use == 0.
6916
6917         int i;
6918         for( i = 0; i < STATIC_FRAME_COUNT; ++i ) {
6919                 if( ! static_frame[ i ].in_use ) {
6920                         static_frame[ i ].in_use = 1;
6921                         return static_frame + i;
6922                 }
6923         }
6924
6925         // The static ones are all in use.  Malloc one.
6926
6927         return safe_malloc( sizeof( QueryFrame ) );
6928 }
6929
6930 /**
6931         @brief Free a QueryFrame, and all the memory it owns.
6932         @param frame Pointer to the QueryFrame to be freed.
6933 */
6934 static void free_query_frame( QueryFrame* frame ) {
6935         // Sanity check
6936         if( ! frame )
6937                 return;
6938
6939         clear_class_info( &frame->core );
6940
6941         // Free the join list
6942         ClassInfo* temp;
6943         ClassInfo* info = frame->join_list;
6944         while( info ) {
6945                 temp = info->next;
6946                 free_class_info( info );
6947                 info = temp;
6948         }
6949
6950         frame->join_list = NULL;
6951         frame->next = NULL;
6952
6953         // If the frame is a static instance, just mark it as unused
6954         int i;
6955         for( i = 0; i < STATIC_FRAME_COUNT; ++i ) {
6956                 if( frame == static_frame + i ) {
6957                         static_frame[ i ].in_use = 0;
6958                         return;
6959                 }
6960         }
6961
6962         // Otherwise it must have been malloc'd, so free it
6963
6964         free( frame );
6965 }
6966
6967 /**
6968         @brief Search a given QueryFrame for a specified alias.
6969         @param frame Pointer to the QueryFrame to be searched.
6970         @param target The alias for which to search.
6971         @return Pointer to the ClassInfo for the specified alias, if found; otherwise NULL.
6972 */
6973 static ClassInfo* search_alias_in_frame( QueryFrame* frame, const char* target ) {
6974         if( ! frame || ! target ) {
6975                 return NULL;
6976         }
6977
6978         ClassInfo* found_class = NULL;
6979
6980         if( !strcmp( target, frame->core.alias ) )
6981                 return &(frame->core);
6982         else {
6983                 ClassInfo* curr_class = frame->join_list;
6984                 while( curr_class ) {
6985                         if( strcmp( target, curr_class->alias ) )
6986                                 curr_class = curr_class->next;
6987                         else {
6988                                 found_class = curr_class;
6989                                 break;
6990                         }
6991                 }
6992         }
6993
6994         return found_class;
6995 }
6996
6997 /**
6998         @brief Push a new (blank) QueryFrame onto the stack.
6999 */
7000 static void push_query_frame( void ) {
7001         QueryFrame* frame = allocate_frame();
7002         frame->join_list = NULL;
7003         frame->next = curr_query;
7004
7005         // Initialize the ClassInfo for the core class
7006         ClassInfo* core = &frame->core;
7007         core->alias = core->class_name = core->source_def = NULL;
7008         core->class_def = core->fields = core->links = NULL;
7009
7010         curr_query = frame;
7011 }
7012
7013 /**
7014         @brief Pop a QueryFrame off the stack and destroy it.
7015 */
7016 static void pop_query_frame( void ) {
7017         // Sanity check
7018         if( ! curr_query )
7019                 return;
7020
7021         QueryFrame* popped = curr_query;
7022         curr_query = popped->next;
7023
7024         free_query_frame( popped );
7025 }
7026
7027 /**
7028         @brief Populate the ClassInfo for the core class.
7029         @param alias Alias for the core class.  If it is NULL or an empty string, we use the
7030         class name as an alias.
7031         @param class_name Name of the core class.
7032         @return Zero if successful, or 1 if not.
7033
7034         Populate the ClassInfo of the core class with copies of the alias and class name, and
7035         with pointers to the relevant portions of the IDL for the core class.
7036 */
7037 static int add_query_core( const char* alias, const char* class_name ) {
7038
7039         // Sanity checks
7040         if( ! curr_query ) {
7041                 osrfLogError( OSRF_LOG_MARK,
7042                                           "%s ERROR: No QueryFrame available for class %s", modulename, class_name );
7043                 return 1;
7044         } else if( curr_query->core.alias ) {
7045                 osrfLogError( OSRF_LOG_MARK,
7046                                           "%s ERROR: Core class %s already populated as %s",
7047                                           modulename, curr_query->core.class_name, curr_query->core.alias );
7048                 return 1;
7049         }
7050
7051         build_class_info( &curr_query->core, alias, class_name );
7052         if( curr_query->core.alias )
7053                 return 0;
7054         else {
7055                 osrfLogError( OSRF_LOG_MARK,
7056                                           "%s ERROR: Unable to look up core class %s", modulename, class_name );
7057                 return 1;
7058         }
7059 }
7060
7061 /**
7062         @brief Search the current QueryFrame for a specified alias.
7063         @param target The alias for which to search.
7064         @return A pointer to the corresponding ClassInfo, if found; otherwise NULL.
7065 */
7066 static inline ClassInfo* search_alias( const char* target ) {
7067         return search_alias_in_frame( curr_query, target );
7068 }
7069
7070 /**
7071         @brief Search all levels of query for a specified alias, starting with the current query.
7072         @param target The alias for which to search.
7073         @return A pointer to the corresponding ClassInfo, if found; otherwise NULL.
7074 */
7075 static ClassInfo* search_all_alias( const char* target ) {
7076         ClassInfo* found_class = NULL;
7077         QueryFrame* curr_frame = curr_query;
7078
7079         while( curr_frame ) {
7080                 if(( found_class = search_alias_in_frame( curr_frame, target ) ))
7081                         break;
7082                 else
7083                         curr_frame = curr_frame->next;
7084         }
7085
7086         return found_class;
7087 }
7088
7089 /**
7090         @brief Add a class to the list of classes joined to the current query.
7091         @param alias Alias of the class to be added.  If it is NULL or an empty string, we use
7092         the class name as an alias.
7093         @param classname The name of the class to be added.
7094         @return A pointer to the ClassInfo for the added class, if successful; otherwise NULL.
7095 */
7096 static ClassInfo* add_joined_class( const char* alias, const char* classname ) {
7097
7098         if( ! classname || ! *classname ) {    // sanity check
7099                 osrfLogError( OSRF_LOG_MARK, "Can't join a class with no class name" );
7100                 return NULL;
7101         }
7102
7103         if( ! alias )
7104                 alias = classname;
7105
7106         const ClassInfo* conflict = search_alias( alias );
7107         if( conflict ) {
7108                 osrfLogError( OSRF_LOG_MARK,
7109                                           "%s ERROR: Table alias \"%s\" conflicts with class \"%s\"",
7110                                           modulename, alias, conflict->class_name );
7111                 return NULL;
7112         }
7113
7114         ClassInfo* info = allocate_class_info();
7115
7116         if( build_class_info( info, alias, classname ) ) {
7117                 free_class_info( info );
7118                 return NULL;
7119         }
7120
7121         // Add the new ClassInfo to the join list of the current QueryFrame
7122         info->next = curr_query->join_list;
7123         curr_query->join_list = info;
7124
7125         return info;
7126 }
7127
7128 /**
7129         @brief Destroy all nodes on the query stack.
7130 */
7131 static void clear_query_stack( void ) {
7132         while( curr_query )
7133                 pop_query_frame();
7134 }
7135
7136 /*@}*/