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