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