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