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