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

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