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