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