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