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