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