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

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