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                         fkey = (const char*)oilsIDLFindPath("/%s/links/%s/key", class, field);
2217                         if (!fkey) {
2218                                 osrfLogError(
2219                                         OSRF_LOG_MARK,
2220                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2221                                         MODULENAME,
2222                                         class,
2223                                         field,
2224                                         leftclass
2225                                 );
2226                                 buffer_free(join_buf);
2227                                 if(freeable_hash)
2228                                         jsonObjectFree(freeable_hash);
2229                                 jsonIteratorFree(search_itr);
2230                                 return NULL;
2231                         }
2232
2233                 } else if (!field && fkey) {
2234                         field = (const char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey );
2235                         if (!field) {
2236                                 osrfLogError(
2237                                         OSRF_LOG_MARK,
2238                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2239                                         MODULENAME,
2240                                         leftclass,
2241                                         fkey,
2242                                         class
2243                                 );
2244                                 buffer_free(join_buf);
2245                                 if(freeable_hash)
2246                                         jsonObjectFree(freeable_hash);
2247                                 jsonIteratorFree(search_itr);
2248                                 return NULL;
2249                         }
2250                         field = field;
2251
2252                 } else if (!field && !fkey) {
2253                         osrfHash* _links = oilsIDL_links( leftclass );
2254
2255                         // For each link defined for the left class:
2256                         // see if the link references the joined class
2257                         osrfHashIterator* itr = osrfNewHashIterator( _links );
2258                         osrfHash* curr_link = NULL;
2259                         while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2260                                 const char* other_class = osrfHashGet( curr_link, "class" );
2261                                 if( other_class && !strcmp( other_class, class ) ) {
2262
2263                                         // Found a link between the classes
2264                                         fkey = osrfHashIteratorKey( itr );
2265                                         field = osrfHashGet( curr_link, "key" );
2266                                         break;
2267                                 }
2268                         }
2269                         osrfHashIteratorFree( itr );
2270
2271                         if (!field || !fkey) {
2272                                 // Do another such search, with the classes reversed
2273                                 _links = oilsIDL_links( class );
2274
2275                                 // For each link defined for the joined class:
2276                                 // see if the link references the left class
2277                                 osrfHashIterator* itr = osrfNewHashIterator( _links );
2278                                 osrfHash* curr_link = NULL;
2279                                 while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2280                                         const char* other_class = osrfHashGet( curr_link, "class" );
2281                                         if( other_class && !strcmp( other_class, leftclass ) ) {
2282
2283                                                 // Found a link between the classes
2284                                                 fkey = osrfHashIteratorKey( itr );
2285                                                 field = osrfHashGet( curr_link, "key" );
2286                                                 break;
2287                                         }
2288                                 }
2289                                 osrfHashIteratorFree( itr );
2290                         }
2291
2292                         if (!field || !fkey) {
2293                                 osrfLogError(
2294                                         OSRF_LOG_MARK,
2295                                         "%s: JOIN failed.  No link defined between %s and %s",
2296                                         MODULENAME,
2297                                         leftclass,
2298                                         class
2299                                 );
2300                                 buffer_free(join_buf);
2301                                 if(freeable_hash)
2302                                         jsonObjectFree(freeable_hash);
2303                                 jsonIteratorFree(search_itr);
2304                                 return NULL;
2305                         }
2306
2307                 }
2308
2309                 const char* type = jsonObjectGetString( jsonObjectGetKeyConst( snode, "type" ) );
2310                 if (type) {
2311                         if ( !strcasecmp(type,"left") ) {
2312                                 buffer_add(join_buf, " LEFT JOIN");
2313                         } else if ( !strcasecmp(type,"right") ) {
2314                                 buffer_add(join_buf, " RIGHT JOIN");
2315                         } else if ( !strcasecmp(type,"full") ) {
2316                                 buffer_add(join_buf, " FULL JOIN");
2317                         } else {
2318                                 buffer_add(join_buf, " INNER JOIN");
2319                         }
2320                 } else {
2321                         buffer_add(join_buf, " INNER JOIN");
2322                 }
2323
2324                 char* table = getSourceDefinition(idlClass);
2325                 if( !table ) {
2326                         jsonIteratorFree( search_itr );
2327                         buffer_free( join_buf );
2328                         if( freeable_hash )
2329                                 jsonObjectFree( freeable_hash );
2330                         return NULL;
2331                 }
2332
2333                 buffer_fadd(join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s",
2334                                         table, class, class, field, leftclass, fkey);
2335                 free(table);
2336
2337                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
2338                 if (filter) {
2339                         const char* filter_op = jsonObjectGetString( jsonObjectGetKeyConst( snode, "filter_op" ) );
2340                         if ( filter_op && !strcasecmp("or",filter_op) ) {
2341                                 buffer_add( join_buf, " OR " );
2342                         } else {
2343                                 buffer_add( join_buf, " AND " );
2344                         }
2345
2346                         char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN, NULL );
2347                         if( jpred ) {
2348                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
2349                                 OSRF_BUFFER_ADD( join_buf, jpred );
2350                                 free(jpred);
2351                         } else {
2352                                 osrfLogError(
2353                                         OSRF_LOG_MARK,
2354                                         "%s: JOIN failed.  Invalid conditional expression.",
2355                                         MODULENAME
2356                                 );
2357                                 jsonIteratorFree( search_itr );
2358                                 buffer_free( join_buf );
2359                                 if( freeable_hash )
2360                                         jsonObjectFree( freeable_hash );
2361                                 return NULL;
2362                         }
2363                 }
2364
2365                 buffer_add(join_buf, " ) ");
2366
2367                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
2368                 if (join_filter) {
2369                         char* jpred = searchJOIN( join_filter, idlClass );
2370                         OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
2371                         OSRF_BUFFER_ADD( join_buf, jpred );
2372                         free(jpred);
2373                 }
2374         }
2375
2376         if(freeable_hash)
2377                 jsonObjectFree(freeable_hash);
2378         jsonIteratorFree(search_itr);
2379
2380         return buffer_release(join_buf);
2381 }
2382
2383 /*
2384
2385 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
2386 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
2387 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
2388
2389 Generate code to express a set of conditions, as for a WHERE clause.  Parameters:
2390
2391 search_hash is the JSON expression of the conditions.
2392 meta is the class definition from the IDL, for the relevant table.
2393 opjoin_type indicates whether multiple conditions, if present, should be
2394         connected by AND or OR.
2395 osrfMethodContext is loaded with all sorts of stuff, but all we do with it here is
2396         to pass it to other functions -- and all they do with it is to use the session
2397         and request members to send error messages back to the client.
2398
2399 */
2400
2401 static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type, osrfMethodContext* ctx ) {
2402
2403         osrfLogDebug(
2404         OSRF_LOG_MARK,
2405         "%s: Entering searchWHERE; search_hash addr = %p, meta addr = %p, opjoin_type = %d, ctx addr = %p",
2406         MODULENAME,
2407         search_hash,
2408         meta,
2409         opjoin_type,
2410         ctx
2411     );
2412
2413         growing_buffer* sql_buf = buffer_init(128);
2414
2415         jsonObject* node = NULL;
2416
2417         int first = 1;
2418         if ( search_hash->type == JSON_ARRAY ) {
2419                 osrfLogDebug(OSRF_LOG_MARK, "%s: In WHERE clause, condition type is JSON_ARRAY", MODULENAME);
2420                 jsonIterator* search_itr = jsonNewIterator( search_hash );
2421                 if( !jsonIteratorHasNext( search_itr ) ) {
2422                         osrfLogError(
2423                                 OSRF_LOG_MARK,
2424                                 "%s: Invalid predicate structure: empty JSON array",
2425                                 MODULENAME
2426                         );
2427                         jsonIteratorFree( search_itr );
2428                         buffer_free( sql_buf );
2429                         return NULL;
2430                 }
2431
2432                 while ( (node = jsonIteratorNext( search_itr )) ) {
2433             if (first) {
2434                 first = 0;
2435             } else {
2436                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
2437                 else buffer_add(sql_buf, " AND ");
2438             }
2439
2440             char* subpred = searchWHERE( node, meta, opjoin_type, ctx );
2441                         if( subpred ) {
2442                 buffer_fadd(sql_buf, "( %s )", subpred);
2443                 free(subpred);
2444                         } else {
2445                                 jsonIteratorFree( search_itr );
2446                                 buffer_free( sql_buf );
2447                                 return NULL;
2448                         }
2449         }
2450         jsonIteratorFree(search_itr);
2451
2452         } else if ( search_hash->type == JSON_HASH ) {
2453                 osrfLogDebug(OSRF_LOG_MARK, "%s: In WHERE clause, condition type is JSON_HASH", MODULENAME);
2454                 jsonIterator* search_itr = jsonNewIterator( search_hash );
2455                 if( !jsonIteratorHasNext( search_itr ) ) {
2456                         osrfLogError(
2457                                 OSRF_LOG_MARK,
2458                                 "%s: Invalid predicate structure: empty JSON object",
2459                                 MODULENAME
2460                         );
2461                         jsonIteratorFree( search_itr );
2462                         buffer_free( sql_buf );
2463                         return NULL;
2464                 }
2465
2466                 while ( (node = jsonIteratorNext( search_itr )) ) {
2467
2468             if (first) {
2469                 first = 0;
2470             } else {
2471                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
2472                 else buffer_add(sql_buf, " AND ");
2473             }
2474
2475                         if ( '+' == search_itr->key[ 0 ] ) {
2476                                 if ( node->type == JSON_STRING ) {
2477                                         // Intended purpose; to allow reference to a Boolean column
2478
2479                                         // Verify that the class alias is not empty
2480                                         if( '\0' == search_itr->key[ 1 ] ) {
2481                                                 osrfLogError(
2482                                                         OSRF_LOG_MARK,
2483                                                         "%s: Table alias is empty",
2484                                                         MODULENAME
2485                                                 );
2486                                                 jsonIteratorFree( search_itr );
2487                                                 buffer_free( sql_buf );
2488                                                 return NULL;
2489                                         }
2490
2491                                         // Verify that the string looks like an identifier.
2492                                         const char* subpred = jsonObjectGetString( node );
2493                                         if( ! is_identifier( subpred ) ) {
2494                                                 osrfLogError(
2495                                                          OSRF_LOG_MARK,
2496                                                         "%s: Invalid boolean identifier in WHERE clause: \"%s\"",
2497                                                         MODULENAME,
2498                                                         subpred
2499                                                 );
2500                                                 jsonIteratorFree( search_itr );
2501                                                 buffer_free( sql_buf );
2502                                                 return NULL;
2503                                         }
2504
2505                                         buffer_fadd(sql_buf, " \"%s\".%s ", search_itr->key + 1, subpred);
2506                                 } else {
2507                                         char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN, ctx );
2508                                         if( subpred ) {
2509                                                 buffer_fadd(sql_buf, "( %s )", subpred);
2510                                                 free(subpred);
2511                                         } else {
2512                                                 jsonIteratorFree( search_itr );
2513                                                 buffer_free( sql_buf );
2514                                                 return NULL;
2515                                         }
2516                                 }
2517                         } else if ( !strcasecmp("-or",search_itr->key) ) {
2518                                 char* subpred = searchWHERE( node, meta, OR_OP_JOIN, ctx );
2519                                 if( subpred ) {
2520                                         buffer_fadd(sql_buf, "( %s )", subpred);
2521                                         free( subpred );
2522                                 } else {
2523                                         buffer_free( sql_buf );
2524                                         return NULL;
2525                                 }
2526                         } else if ( !strcasecmp("-and",search_itr->key) ) {
2527                                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
2528                                 if( subpred ) {
2529                                         buffer_fadd(sql_buf, "( %s )", subpred);
2530                                         free( subpred );
2531                                 } else {
2532                                         buffer_free( sql_buf );
2533                                         return NULL;
2534                                 }
2535                         } else if ( !strcasecmp("-not",search_itr->key) ) {
2536                                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
2537                                 if( subpred ) {
2538                                         buffer_fadd(sql_buf, " NOT ( %s )", subpred);
2539                                         free( subpred );
2540                                 } else {
2541                                         buffer_free( sql_buf );
2542                                         return NULL;
2543                                 }
2544                         } else if ( !strcasecmp("-exists",search_itr->key) ) {
2545                 char* subpred = SELECT(
2546                     ctx,
2547                     jsonObjectGetKey( node, "select" ),
2548                     jsonObjectGetKey( node, "from" ),
2549                     jsonObjectGetKey( node, "where" ),
2550                     jsonObjectGetKey( node, "having" ),
2551                     jsonObjectGetKey( node, "order_by" ),
2552                     jsonObjectGetKey( node, "limit" ),
2553                     jsonObjectGetKey( node, "offset" ),
2554                     SUBSELECT
2555                 );
2556
2557                                 if( subpred ) {
2558                                         buffer_fadd(sql_buf, "EXISTS ( %s )", subpred);
2559                                         free(subpred);
2560                                 } else {
2561                                         buffer_free( sql_buf );
2562                                         return NULL;
2563                                 }
2564             } else if ( !strcasecmp("-not-exists",search_itr->key) ) {
2565                 char* subpred = SELECT(
2566                     ctx,
2567                     jsonObjectGetKey( node, "select" ),
2568                     jsonObjectGetKey( node, "from" ),
2569                     jsonObjectGetKey( node, "where" ),
2570                     jsonObjectGetKey( node, "having" ),
2571                     jsonObjectGetKey( node, "order_by" ),
2572                     jsonObjectGetKey( node, "limit" ),
2573                     jsonObjectGetKey( node, "offset" ),
2574                     SUBSELECT
2575                 );
2576
2577                                 if( subpred ) {
2578                                         buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred);
2579                                         free(subpred);
2580                                 } else {
2581                                         buffer_free( sql_buf );
2582                                         return NULL;
2583                                 }
2584
2585             } else {
2586
2587                 char* class = osrfHashGet(meta, "classname");
2588                 osrfHash* fields = osrfHashGet(meta, "fields");
2589                 osrfHash* field = osrfHashGet( fields, search_itr->key );
2590
2591
2592                 if (!field) {
2593                     char* table = getSourceDefinition(meta);
2594                                         if( !table )
2595                                                 table = strdup( "(?)" );
2596                     osrfLogError(
2597                         OSRF_LOG_MARK,
2598                         "%s: Attempt to reference non-existent column \"%s\" on %s (%s)",
2599                         MODULENAME,
2600                         search_itr->key,
2601                         table,
2602                         class ? class : "?"
2603                     );
2604                     buffer_free(sql_buf);
2605                     free(table);
2606                                         jsonIteratorFree(search_itr);
2607                                         return NULL;
2608                                 }
2609
2610                                 char* subpred = searchPredicate( class, field, node, ctx );
2611                                 if( subpred ) {
2612                                         buffer_add( sql_buf, subpred );
2613                                         free(subpred);
2614                                 } else {
2615                                         buffer_free(sql_buf);
2616                                         jsonIteratorFree(search_itr);
2617                                         return NULL;
2618                                 }
2619                         }
2620                 }
2621                 jsonIteratorFree(search_itr);
2622
2623     } else {
2624         // ERROR ... only hash and array allowed at this level
2625         char* predicate_string = jsonObjectToJSON( search_hash );
2626         osrfLogError(
2627             OSRF_LOG_MARK,
2628             "%s: Invalid predicate structure: %s",
2629             MODULENAME,
2630             predicate_string
2631         );
2632         buffer_free(sql_buf);
2633         free(predicate_string);
2634         return NULL;
2635     }
2636
2637         return buffer_release(sql_buf);
2638 }
2639
2640 char* SELECT (
2641                 /* method context */ osrfMethodContext* ctx,
2642
2643                 /* SELECT   */ jsonObject* selhash,
2644                 /* FROM     */ jsonObject* join_hash,
2645                 /* WHERE    */ jsonObject* search_hash,
2646                 /* HAVING   */ jsonObject* having_hash,
2647                 /* ORDER BY */ jsonObject* order_hash,
2648                 /* LIMIT    */ jsonObject* limit,
2649                 /* OFFSET   */ jsonObject* offset,
2650                 /* flags    */ int flags
2651 ) {
2652         const char* locale = osrf_message_get_last_locale();
2653
2654         // in case we don't get a select list
2655         jsonObject* defaultselhash = NULL;
2656
2657         // general tmp objects
2658         const jsonObject* tmp_const;
2659         jsonObject* selclass = NULL;
2660         jsonObject* selfield = NULL;
2661         jsonObject* snode = NULL;
2662         jsonObject* onode = NULL;
2663
2664         char* string = NULL;
2665         int from_function = 0;
2666         int first = 1;
2667         int gfirst = 1;
2668         //int hfirst = 1;
2669
2670         // the core search class
2671         char* core_class = NULL;
2672
2673         // metadata about the core search class
2674         osrfHash* core_meta = NULL;
2675
2676         // punt if there's no core class
2677         if (!join_hash || ( join_hash->type == JSON_HASH && !join_hash->size )) {
2678                 osrfLogError(
2679                         OSRF_LOG_MARK,
2680                         "%s: FROM clause is missing or empty",
2681                         MODULENAME
2682                 );
2683                 if( ctx )
2684                         osrfAppSessionStatus(
2685                                 ctx->session,
2686                                 OSRF_STATUS_INTERNALSERVERERROR,
2687                                 "osrfMethodException",
2688                                 ctx->request,
2689                                 "FROM clause is missing or empty in JSON query"
2690                         );
2691                 return NULL;
2692         }
2693
2694         // get the core class -- the only key of the top level FROM clause, or a string
2695         if (join_hash->type == JSON_HASH) {
2696                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
2697                 snode = jsonIteratorNext( tmp_itr );
2698
2699                 core_class = strdup( tmp_itr->key );
2700                 join_hash = snode;
2701
2702                 jsonObject* extra = jsonIteratorNext( tmp_itr );
2703
2704                 jsonIteratorFree( tmp_itr );
2705                 snode = NULL;
2706
2707                 // There shouldn't be more than one entry in join_hash
2708                 if( extra ) {
2709                         osrfLogError(
2710                                 OSRF_LOG_MARK,
2711                                 "%s: Malformed FROM clause: extra entry in JSON_HASH",
2712                                 MODULENAME
2713                         );
2714                         if( ctx )
2715                                 osrfAppSessionStatus(
2716                                         ctx->session,
2717                                         OSRF_STATUS_INTERNALSERVERERROR,
2718                                         "osrfMethodException",
2719                                         ctx->request,
2720                                         "Malformed FROM clause in JSON query"
2721                                 );
2722                         free( core_class );
2723                         return NULL;    // Malformed join_hash; extra entry
2724                 }
2725         } else if (join_hash->type == JSON_ARRAY) {
2726                 from_function = 1;
2727                 core_class = jsonObjectToSimpleString( jsonObjectGetIndex(join_hash, 0) );
2728                 selhash = NULL;
2729
2730         } else if (join_hash->type == JSON_STRING) {
2731                 core_class = jsonObjectToSimpleString( join_hash );
2732                 join_hash = NULL;
2733         }
2734         else {
2735                 osrfLogError(
2736                         OSRF_LOG_MARK,
2737                         "%s: FROM clause is unexpected JSON type: %s",
2738                         MODULENAME,
2739                         json_type( join_hash->type )
2740                 );
2741                 if( ctx )
2742                         osrfAppSessionStatus(
2743                                 ctx->session,
2744                                 OSRF_STATUS_INTERNALSERVERERROR,
2745                                 "osrfMethodException",
2746                                 ctx->request,
2747                                 "Ill-formed FROM clause in JSON query"
2748                         );
2749                 free( core_class );
2750                 return NULL;
2751         }
2752
2753         if (!from_function) {
2754                 // Get the IDL class definition for the core class
2755                 core_meta = osrfHashGet( oilsIDL(), core_class );
2756                 if( !core_meta ) {    // Didn't find it?
2757                         osrfLogError(
2758                                 OSRF_LOG_MARK,
2759                                 "%s: SELECT clause references undefined class: \"%s\"",
2760                                 MODULENAME,
2761                                 core_class
2762                         );
2763                         if( ctx )
2764                                 osrfAppSessionStatus(
2765                                         ctx->session,
2766                                         OSRF_STATUS_INTERNALSERVERERROR,
2767                                         "osrfMethodException",
2768                                         ctx->request,
2769                                         "SELECT clause references undefined class in JSON query"
2770                                 );
2771                         free( core_class );
2772                         return NULL;
2773                 }
2774
2775                 // Make sure the class isn't virtual
2776                 if( str_is_true( osrfHashGet( core_meta, "virtual" ) ) ) {
2777                         osrfLogError(
2778                                 OSRF_LOG_MARK,
2779                                 "%s: Core class is virtual: \"%s\"",
2780                                 MODULENAME,
2781                                 core_class
2782                         );
2783                         if( ctx )
2784                                 osrfAppSessionStatus(
2785                                         ctx->session,
2786                                         OSRF_STATUS_INTERNALSERVERERROR,
2787                                         "osrfMethodException",
2788                                         ctx->request,
2789                                         "FROM clause references virtual class in JSON query"
2790                                 );
2791                         free( core_class );
2792                         return NULL;
2793                 }
2794         }
2795
2796         // if the select list is empty, or the core class field list is '*',
2797         // build the default select list ...
2798         if (!selhash) {
2799                 selhash = defaultselhash = jsonNewObjectType(JSON_HASH);
2800                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2801         } else if( selhash->type != JSON_HASH ) {
2802                 osrfLogError(
2803                         OSRF_LOG_MARK,
2804                         "%s: Expected JSON_HASH for SELECT clause; found %s",
2805                         MODULENAME,
2806                         json_type( selhash->type )
2807                 );
2808
2809                 if (ctx)
2810                         osrfAppSessionStatus(
2811                                 ctx->session,
2812                                 OSRF_STATUS_INTERNALSERVERERROR,
2813                                 "osrfMethodException",
2814                                 ctx->request,
2815                                 "Malformed SELECT clause in JSON query"
2816                         );
2817                 free( core_class );
2818                 return NULL;
2819         } else if ( (tmp_const = jsonObjectGetKeyConst( selhash, core_class )) && tmp_const->type == JSON_STRING ) {
2820                 const char* _x = jsonObjectGetString( tmp_const );
2821                 if (!strncmp( "*", _x, 1 )) {
2822                         jsonObjectRemoveKey( selhash, core_class );
2823                         jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2824                 }
2825         }
2826
2827         // the query buffer
2828         growing_buffer* sql_buf = buffer_init(128);
2829
2830         // temp buffer for the SELECT list
2831         growing_buffer* select_buf = buffer_init(128);
2832         growing_buffer* order_buf = buffer_init(128);
2833         growing_buffer* group_buf = buffer_init(128);
2834         growing_buffer* having_buf = buffer_init(128);
2835
2836         int aggregate_found = 0;     // boolean
2837
2838         // Build a select list
2839         if(from_function)   // From a function we select everything
2840                 OSRF_BUFFER_ADD_CHAR( select_buf, '*' );
2841         else {
2842
2843                 // If we need to build a default list, prepare to do so
2844                 jsonObject* _tmp = jsonObjectGetKey( selhash, core_class );
2845                 if ( _tmp && !_tmp->size ) {
2846
2847                         osrfHash* core_fields = osrfHashGet( core_meta, "fields" );
2848
2849                         osrfHashIterator* field_itr = osrfNewHashIterator( core_fields );
2850                         osrfHash* field_def;
2851                         while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
2852                                 if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
2853                                         // This field is not virtual, so add it to the list
2854                                         jsonObjectPush( _tmp, jsonNewObject( osrfHashIteratorKey( field_itr ) ) );
2855                                 }
2856                         }
2857                         osrfHashIteratorFree( field_itr );
2858                 }
2859
2860                 // Now build the actual select list
2861             int sel_pos = 1;
2862             first = 1;
2863             gfirst = 1;
2864             jsonIterator* selclass_itr = jsonNewIterator( selhash );
2865             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {    // For each class
2866
2867                     // Make sure the class is defined in the IDL
2868                         const char* cname = selclass_itr->key;
2869                         osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
2870                     if (!idlClass) {
2871                                 osrfLogError(
2872                                         OSRF_LOG_MARK,
2873                                         "%s: Selected class \"%s\" not defined in IDL",
2874                                         MODULENAME,
2875                                         cname
2876                                 );
2877
2878                                 if (ctx)
2879                                         osrfAppSessionStatus(
2880                                                 ctx->session,
2881                                                 OSRF_STATUS_INTERNALSERVERERROR,
2882                                                 "osrfMethodException",
2883                                                 ctx->request,
2884                                                 "Selected class is not defined"
2885                                         );
2886                                 jsonIteratorFree( selclass_itr );
2887                                 buffer_free( sql_buf );
2888                                 buffer_free( select_buf );
2889                                 buffer_free( order_buf );
2890                                 buffer_free( group_buf );
2891                                 buffer_free( having_buf );
2892                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2893                                 free( core_class );
2894                                 return NULL;
2895                         }
2896
2897                     // Make sure the target relation is in the join tree.
2898
2899                         // At this point join_hash is a step down from the join_hash we
2900                         // received as a parameter.  If the original was a JSON_STRING,
2901                         // then json_hash is now NULL.  If the original was a JSON_HASH,
2902                         // then json_hash is now the first (and only) entry in it,
2903                         // denoting the core class.  We've already excluded the
2904                         // possibility that the original was a JSON_ARRAY, because in
2905                         // that case from_function would be non-NULL, and we wouldn't
2906                         // be here.
2907
2908                         int class_in_from_clause;    // boolean
2909
2910                     if ( ! strcmp( core_class, cname ))
2911                                 // This is the core class -- no problem
2912                                 class_in_from_clause = 1;
2913                         else {
2914                                 if (!join_hash)
2915                                         // There's only one class in the FROM clause, and this isn't it
2916                                         class_in_from_clause = 0;
2917                                 else if (join_hash->type == JSON_STRING) {
2918                                         // There's only one class in the FROM clause
2919                                         const char* str = jsonObjectGetString(join_hash);
2920                                         if ( strcmp( str, cname ) )
2921                                                 class_in_from_clause = 0;    // This isn't it
2922                                         else
2923                                                 class_in_from_clause = 1;    // This is it
2924                                 } else {
2925                                         jsonObject* found = jsonObjectFindPath(join_hash, "//%s", cname);
2926                                         if ( 0 == found->size )
2927                                                 class_in_from_clause = 0;   // Nowhere in the join tree
2928                                         else
2929                                                 class_in_from_clause = 1;   // Found it
2930                                         jsonObjectFree( found );
2931                                 }
2932                         }
2933
2934                         // If the class isn't in the FROM clause, bail out
2935                         if( ! class_in_from_clause ) {
2936                                 osrfLogError(
2937                                         OSRF_LOG_MARK,
2938                                         "%s: SELECT clause references class not in FROM clause: \"%s\"",
2939                                         MODULENAME,
2940                                         cname
2941                                 );
2942                                 if( ctx )
2943                                         osrfAppSessionStatus(
2944                                                 ctx->session,
2945                                                 OSRF_STATUS_INTERNALSERVERERROR,
2946                                                 "osrfMethodException",
2947                                                 ctx->request,
2948                                                 "Selected class not in FROM clause in JSON query"
2949                                         );
2950                                 jsonIteratorFree( selclass_itr );
2951                                 buffer_free( sql_buf );
2952                                 buffer_free( select_buf );
2953                                 buffer_free( order_buf );
2954                                 buffer_free( group_buf );
2955                                 buffer_free( having_buf );
2956                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2957                                 free( core_class );
2958                                 return NULL;
2959                         }
2960
2961                         // Look up some attributes of the current class, so that we
2962                         // don't have to look them up again for each field
2963                         osrfHash* class_field_set = osrfHashGet( idlClass, "fields" );
2964                         const char* class_pkey = osrfHashGet( idlClass, "primarykey" );
2965                         const char* class_tname = osrfHashGet( idlClass, "tablename" );
2966
2967                     // stitch together the column list ...
2968                     jsonIterator* select_itr = jsonNewIterator( selclass );
2969                     while ( (selfield = jsonIteratorNext( select_itr )) ) {   // for each SELECT column
2970
2971                                 // If we need a separator comma, add one
2972                                 if (first) {
2973                                         first = 0;
2974                                 } else {
2975                                         OSRF_BUFFER_ADD_CHAR( select_buf, ',' );
2976                                 }
2977
2978                                 // ... if it's a string, just toss it on the pile
2979                                 if (selfield->type == JSON_STRING) {
2980
2981                                         // Look up the field in the IDL
2982                                         const char* col_name = jsonObjectGetString( selfield );
2983                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
2984                                         if ( !field_def ) {
2985                                                 // No such field in current class
2986                                                 osrfLogError(
2987                                                         OSRF_LOG_MARK,
2988                                                         "%s: Selected column \"%s\" not defined in IDL for class \"%s\"",
2989                                                         MODULENAME,
2990                                                         col_name,
2991                                                         cname
2992                                                 );
2993                                                 if( ctx )
2994                                                         osrfAppSessionStatus(
2995                                                                 ctx->session,
2996                                                                 OSRF_STATUS_INTERNALSERVERERROR,
2997                                                                 "osrfMethodException",
2998                                                                 ctx->request,
2999                                                                 "Selected column not defined in JSON query"
3000                                                         );
3001                                                 jsonIteratorFree( select_itr );
3002                                                 jsonIteratorFree( selclass_itr );
3003                                                 buffer_free( sql_buf );
3004                                                 buffer_free( select_buf );
3005                                                 buffer_free( order_buf );
3006                                                 buffer_free( group_buf );
3007                                                 buffer_free( having_buf );
3008                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3009                                                 free( core_class );
3010                                                 return NULL;
3011                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
3012                                                 // Virtual field not allowed
3013                                                 osrfLogError(
3014                                                         OSRF_LOG_MARK,
3015                                                         "%s: Selected column \"%s\" for class \"%s\" is virtual",
3016                                                         MODULENAME,
3017                                                         col_name,
3018                                                         cname
3019                                                 );
3020                                                 if( ctx )
3021                                                         osrfAppSessionStatus(
3022                                                                 ctx->session,
3023                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3024                                                                 "osrfMethodException",
3025                                                                 ctx->request,
3026                                                                 "Selected column may not be virtual in JSON query"
3027                                                         );
3028                                                 jsonIteratorFree( select_itr );
3029                                                 jsonIteratorFree( selclass_itr );
3030                                                 buffer_free( sql_buf );
3031                                                 buffer_free( select_buf );
3032                                                 buffer_free( order_buf );
3033                                                 buffer_free( group_buf );
3034                                                 buffer_free( having_buf );
3035                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3036                                                 free( core_class );
3037                                                 return NULL;
3038                                         }
3039
3040                                         if (locale) {
3041                                                 const char* i18n;
3042                                                 if (flags & DISABLE_I18N)
3043                                                         i18n = NULL;
3044                                                 else
3045                                                         i18n = osrfHashGet(field_def, "i18n");
3046
3047                                                 if( str_is_true( i18n ) ) {
3048                             buffer_fadd( select_buf,
3049                                                                 " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
3050                                                                 class_tname, cname, col_name, class_pkey, cname, class_pkey, locale, col_name );
3051                         } else {
3052                                             buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, col_name );
3053                         }
3054                     } else {
3055                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, col_name );
3056                     }
3057
3058                                 // ... but it could be an object, in which case we check for a Field Transform
3059                                 } else if (selfield->type == JSON_HASH) {
3060
3061                                         const char* col_name = jsonObjectGetString( jsonObjectGetKeyConst( selfield, "column" ) );
3062
3063                                         // Get the field definition from the IDL
3064                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
3065                                         if ( !field_def ) {
3066                                                 // No such field in current class
3067                                                 osrfLogError(
3068                                                         OSRF_LOG_MARK,
3069                                                         "%s: Selected column \"%s\" is not defined in IDL for class \"%s\"",
3070                                                         MODULENAME,
3071                                                         col_name,
3072                                                         cname
3073                                                 );
3074                                                 if( ctx )
3075                                                         osrfAppSessionStatus(
3076                                                                 ctx->session,
3077                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3078                                                                 "osrfMethodException",
3079                                                                 ctx->request,
3080                                                                 "Selected column is not defined in JSON query"
3081                                                         );
3082                                                 jsonIteratorFree( select_itr );
3083                                                 jsonIteratorFree( selclass_itr );
3084                                                 buffer_free( sql_buf );
3085                                                 buffer_free( select_buf );
3086                                                 buffer_free( order_buf );
3087                                                 buffer_free( group_buf );
3088                                                 buffer_free( having_buf );
3089                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3090                                                 free( core_class );
3091                                                 return NULL;
3092                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
3093                                                 // No such field in current class
3094                                                 osrfLogError(
3095                                                         OSRF_LOG_MARK,
3096                                                         "%s: Selected column \"%s\" is virtual for class \"%s\"",
3097                                                         MODULENAME,
3098                                                         col_name,
3099                                                         cname
3100                                                 );
3101                                                 if( ctx )
3102                                                         osrfAppSessionStatus(
3103                                                                 ctx->session,
3104                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3105                                                                 "osrfMethodException",
3106                                                                 ctx->request,
3107                                                                 "Selected column is virtual in JSON query"
3108                                                         );
3109                                                 jsonIteratorFree( select_itr );
3110                                                 jsonIteratorFree( selclass_itr );
3111                                                 buffer_free( sql_buf );
3112                                                 buffer_free( select_buf );
3113                                                 buffer_free( order_buf );
3114                                                 buffer_free( group_buf );
3115                                                 buffer_free( having_buf );
3116                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3117                                                 free( core_class );
3118                                                 return NULL;
3119                                         }
3120
3121                                         // Decide what to use as a column alias
3122                                         const char* _alias;
3123                                         if ((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
3124                                                 _alias = jsonObjectGetString( tmp_const );
3125                                         } else {         // Use field name as the alias
3126                                                 _alias = col_name;
3127                                         }
3128
3129                                         if (jsonObjectGetKeyConst( selfield, "transform" )) {
3130                                                 char* transform_str = searchFieldTransform(cname, field_def, selfield);
3131                                                 if( transform_str ) {
3132                                                         buffer_fadd(select_buf, " %s AS \"%s\"", transform_str, _alias);
3133                                                         free(transform_str);
3134                                                 } else {
3135                                                         if( ctx )
3136                                                                 osrfAppSessionStatus(
3137                                                                         ctx->session,
3138                                                                         OSRF_STATUS_INTERNALSERVERERROR,
3139                                                                         "osrfMethodException",
3140                                                                         ctx->request,
3141                                                                         "Unable to generate transform function in JSON query"
3142                                                                 );
3143                                                         jsonIteratorFree( select_itr );
3144                                                         jsonIteratorFree( selclass_itr );
3145                                                         buffer_free( sql_buf );
3146                                                         buffer_free( select_buf );
3147                                                         buffer_free( order_buf );
3148                                                         buffer_free( group_buf );
3149                                                         buffer_free( having_buf );
3150                                                         if( defaultselhash ) jsonObjectFree( defaultselhash );
3151                                                         free( core_class );
3152                                                         return NULL;
3153                                                 }
3154                                         } else {
3155
3156                                                 if (locale) {
3157                                                         const char* i18n;
3158                                                         if (flags & DISABLE_I18N)
3159                                                                 i18n = NULL;
3160                                                         else
3161                                                                 i18n = osrfHashGet(field_def, "i18n");
3162
3163                                                         if( str_is_true( i18n ) ) {
3164                                                                 buffer_fadd( select_buf,
3165                                                                         " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
3166                                                                         class_tname, cname, col_name, class_pkey, cname, class_pkey, locale, _alias);
3167                                                         } else {
3168                                                                 buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, _alias);
3169                                                         }
3170                                                 } else {
3171                                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, _alias);
3172                                                 }
3173                                         }
3174                                 }
3175                                 else {
3176                                         osrfLogError(
3177                                                 OSRF_LOG_MARK,
3178                                                 "%s: Selected item is unexpected JSON type: %s",
3179                                                 MODULENAME,
3180                                                 json_type( selfield->type )
3181                                         );
3182                                         if( ctx )
3183                                                 osrfAppSessionStatus(
3184                                                         ctx->session,
3185                                                         OSRF_STATUS_INTERNALSERVERERROR,
3186                                                         "osrfMethodException",
3187                                                         ctx->request,
3188                                                         "Ill-formed SELECT item in JSON query"
3189                                                 );
3190                                         jsonIteratorFree( select_itr );
3191                                         jsonIteratorFree( selclass_itr );
3192                                         buffer_free( sql_buf );
3193                                         buffer_free( select_buf );
3194                                         buffer_free( order_buf );
3195                                         buffer_free( group_buf );
3196                                         buffer_free( having_buf );
3197                                         if( defaultselhash ) jsonObjectFree( defaultselhash );
3198                                         free( core_class );
3199                                         return NULL;
3200                                 }
3201
3202                                 const jsonObject* agg_obj = jsonObjectGetKey( selfield, "aggregate" );
3203                                 if( obj_is_true( agg_obj ) )
3204                                         aggregate_found = 1;
3205                                 else {
3206                                         // Append a comma (except for the first one)
3207                                         // and add the column to a GROUP BY clause
3208                                         if (gfirst)
3209                                                 gfirst = 0;
3210                                         else
3211                                                 OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3212
3213                                         buffer_fadd(group_buf, " %d", sel_pos);
3214                                 }
3215
3216 #if 0
3217                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
3218
3219                                         const jsonObject* aggregate_obj = jsonObjectGetKey( selfield, "aggregate" );
3220                                     if ( ! obj_is_true( aggregate_obj ) ) {
3221                                             if (gfirst) {
3222                                                     gfirst = 0;
3223                                             } else {
3224                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3225                                             }
3226
3227                                             buffer_fadd(group_buf, " %d", sel_pos);
3228
3229                                         /*
3230                                     } else if (is_agg = jsonObjectGetKey( selfield, "having" )) {
3231                                             if (gfirst) {
3232                                                     gfirst = 0;
3233                                             } else {
3234                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3235                                             }
3236
3237                                             _column = searchFieldTransform(cname, field, selfield);
3238                                                 OSRF_BUFFER_ADD_CHAR(group_buf, ' ');
3239                                                 OSRF_BUFFER_ADD(group_buf, _column);
3240                                             _column = searchFieldTransform(cname, field, selfield);
3241                                         */
3242                                     }
3243                             }
3244 #endif
3245
3246                             sel_pos++;
3247                     } // end while -- iterating across SELECT columns
3248
3249             jsonIteratorFree(select_itr);
3250             } // end while -- iterating across classes
3251
3252         jsonIteratorFree(selclass_itr);
3253     }
3254
3255
3256         char* col_list = buffer_release(select_buf);
3257         char* table = NULL;
3258         if (from_function) table = searchValueTransform(join_hash);
3259         else table = getSourceDefinition(core_meta);
3260
3261         if( !table ) {
3262                 if (ctx)
3263                         osrfAppSessionStatus(
3264                                 ctx->session,
3265                                 OSRF_STATUS_INTERNALSERVERERROR,
3266                                 "osrfMethodException",
3267                                 ctx->request,
3268                                 "Unable to identify table for core class"
3269                         );
3270                 free( col_list );
3271                 buffer_free( sql_buf );
3272                 buffer_free( order_buf );
3273                 buffer_free( group_buf );
3274                 buffer_free( having_buf );
3275                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3276                 free( core_class );
3277                 return NULL;
3278         }
3279
3280         // Put it all together
3281         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
3282         free(col_list);
3283         free(table);
3284
3285     if (!from_function) {
3286             // Now, walk the join tree and add that clause
3287             if ( join_hash ) {
3288                     char* join_clause = searchJOIN( join_hash, core_meta );
3289                         if( join_clause ) {
3290                                 buffer_add(sql_buf, join_clause);
3291                         free(join_clause);
3292                         } else {
3293                                 if (ctx)
3294                                         osrfAppSessionStatus(
3295                                                 ctx->session,
3296                                                 OSRF_STATUS_INTERNALSERVERERROR,
3297                                                 "osrfMethodException",
3298                                                 ctx->request,
3299                                                 "Unable to construct JOIN clause(s)"
3300                                         );
3301                                 buffer_free( sql_buf );
3302                                 buffer_free( order_buf );
3303                                 buffer_free( group_buf );
3304                                 buffer_free( having_buf );
3305                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3306                                 free( core_class );
3307                                 return NULL;
3308                         }
3309             }
3310
3311                 // Build a WHERE clause, if there is one
3312             if ( search_hash ) {
3313                     buffer_add(sql_buf, " WHERE ");
3314
3315                     // and it's on the WHERE clause
3316                     char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN, ctx );
3317
3318                     if (pred) {
3319                                 buffer_add(sql_buf, pred);
3320                                 free(pred);
3321                         } else {
3322                                 if (ctx) {
3323                                 osrfAppSessionStatus(
3324                                         ctx->session,
3325                                         OSRF_STATUS_INTERNALSERVERERROR,
3326                                         "osrfMethodException",
3327                                         ctx->request,
3328                                         "Severe query error in WHERE predicate -- see error log for more details"
3329                                 );
3330                             }
3331                             free(core_class);
3332                             buffer_free(having_buf);
3333                             buffer_free(group_buf);
3334                             buffer_free(order_buf);
3335                             buffer_free(sql_buf);
3336                             if (defaultselhash) jsonObjectFree(defaultselhash);
3337                             return NULL;
3338                     }
3339         }
3340
3341                 // Build a HAVING clause, if there is one
3342             if ( having_hash ) {
3343                     buffer_add(sql_buf, " HAVING ");
3344
3345                     // and it's on the the WHERE clause
3346                     char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN, ctx );
3347
3348                     if (pred) {
3349                                 buffer_add(sql_buf, pred);
3350                                 free(pred);
3351                         } else {
3352                                 if (ctx) {
3353                                 osrfAppSessionStatus(
3354                                         ctx->session,
3355                                         OSRF_STATUS_INTERNALSERVERERROR,
3356                                         "osrfMethodException",
3357                                         ctx->request,
3358                                         "Severe query error in HAVING predicate -- see error log for more details"
3359                                 );
3360                             }
3361                             free(core_class);
3362                             buffer_free(having_buf);
3363                             buffer_free(group_buf);
3364                             buffer_free(order_buf);
3365                             buffer_free(sql_buf);
3366                             if (defaultselhash) jsonObjectFree(defaultselhash);
3367                             return NULL;
3368                     }
3369             }
3370
3371                 // Build an ORDER BY clause, if there is one
3372             first = 1;
3373             jsonIterator* class_itr = jsonNewIterator( order_hash );
3374             while ( (snode = jsonIteratorNext( class_itr )) ) {
3375
3376                     if (!jsonObjectGetKeyConst(selhash,class_itr->key))
3377                             continue;
3378
3379                         if ( snode->type == JSON_HASH ) {
3380
3381                                 jsonIterator* order_itr = jsonNewIterator( snode );
3382                                 while ( (onode = jsonIteratorNext( order_itr )) ) {
3383
3384                                         if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
3385                                                 continue;
3386
3387                                         const char* direction = NULL;
3388                                         if ( onode->type == JSON_HASH ) {
3389                                                 if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
3390                                                         string = searchFieldTransform(
3391                                                                 class_itr->key,
3392                                                                 oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
3393                                                                 onode
3394                                                         );
3395                                                         if( ! string ) {
3396                                                                 osrfAppSessionStatus(
3397                                                                         ctx->session,
3398                                                                         OSRF_STATUS_INTERNALSERVERERROR,
3399                                                                         "osrfMethodException",
3400                                                                         ctx->request,
3401                                                                         "Severe query error in ORDER BY clause -- see error log for more details"
3402                                                                 );
3403                                                                 jsonIteratorFree( order_itr );
3404                                                                 jsonIteratorFree( class_itr );
3405                                                                 free(core_class);
3406                                                                 buffer_free(having_buf);
3407                                                                 buffer_free(group_buf);
3408                                                                 buffer_free(order_buf);
3409                                                                 buffer_free(sql_buf);
3410                                                                 if (defaultselhash) jsonObjectFree(defaultselhash);
3411                                                                 return NULL;
3412                                                         }
3413                                                 } else {
3414                                                         growing_buffer* field_buf = buffer_init(16);
3415                                                         buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
3416                                                         string = buffer_release(field_buf);
3417                                                 }
3418
3419                                                 if ( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
3420                                                         const char* dir = jsonObjectGetString(tmp_const);
3421                                                         if (!strncasecmp(dir, "d", 1)) {
3422                                                                 direction = " DESC";
3423                                                         } else {
3424                                                                 direction = " ASC";
3425                                                         }
3426                                                 }
3427
3428                                         } else {
3429                                                 string = strdup(order_itr->key);
3430                                                 const char* dir = jsonObjectGetString(onode);
3431                                                 if (!strncasecmp(dir, "d", 1)) {
3432                                                         direction = " DESC";
3433                                                 } else {
3434                                                         direction = " ASC";
3435                                                 }
3436                                         }
3437
3438                                     if (first) {
3439                                             first = 0;
3440                                     } else {
3441                                             buffer_add(order_buf, ", ");
3442                                     }
3443
3444                                     buffer_add(order_buf, string);
3445                                     free(string);
3446
3447                                     if (direction) {
3448                                             buffer_add(order_buf, direction);
3449                                     }
3450
3451                             } // end while
3452                 // jsonIteratorFree(order_itr);
3453
3454                     } else if ( snode->type == JSON_ARRAY ) {
3455
3456                         jsonIterator* order_itr = jsonNewIterator( snode );
3457                             while ( (onode = jsonIteratorNext( order_itr )) ) {
3458
3459                                         const char* _f = jsonObjectGetString( onode );
3460
3461                                         if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, _f))
3462                                                 continue;
3463
3464                                         if (first) {
3465                                                 first = 0;
3466                                         } else {
3467                                                 buffer_add(order_buf, ", ");
3468                                         }
3469
3470                                         buffer_add(order_buf, _f);
3471
3472                                 } // end while
3473                 // jsonIteratorFree(order_itr);
3474
3475
3476                     // IT'S THE OOOOOOOOOOOLD STYLE!
3477                     } else {
3478                             osrfLogError(OSRF_LOG_MARK, "%s: Possible SQL injection attempt; direct order by is not allowed", MODULENAME);
3479                             if (ctx) {
3480                                 osrfAppSessionStatus(
3481                                         ctx->session,
3482                                         OSRF_STATUS_INTERNALSERVERERROR,
3483                                         "osrfMethodException",
3484                                         ctx->request,
3485                                         "Severe query error -- see error log for more details"
3486                                 );
3487                             }
3488
3489                             free(core_class);
3490                             buffer_free(having_buf);
3491                             buffer_free(group_buf);
3492                             buffer_free(order_buf);
3493                             buffer_free(sql_buf);
3494                             if (defaultselhash) jsonObjectFree(defaultselhash);
3495                             jsonIteratorFree(class_itr);
3496                             return NULL;
3497                     }
3498
3499             } // end while
3500                 // jsonIteratorFree(class_itr);
3501         }
3502
3503
3504         string = buffer_release(group_buf);
3505
3506         if ( *string && ( aggregate_found || (flags & SELECT_DISTINCT) ) ) {
3507                 OSRF_BUFFER_ADD( sql_buf, " GROUP BY " );
3508                 OSRF_BUFFER_ADD( sql_buf, string );
3509         }
3510
3511         free(string);
3512
3513         string = buffer_release(having_buf);
3514
3515         if ( *string ) {
3516                 OSRF_BUFFER_ADD( sql_buf, " HAVING " );
3517                 OSRF_BUFFER_ADD( sql_buf, string );
3518         }
3519
3520         free(string);
3521
3522         string = buffer_release(order_buf);
3523
3524         if ( *string ) {
3525                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
3526                 OSRF_BUFFER_ADD( sql_buf, string );
3527         }
3528
3529         free(string);
3530
3531         if ( limit ){
3532                 const char* str = jsonObjectGetString(limit);
3533                 buffer_fadd( sql_buf, " LIMIT %d", atoi(str) );
3534         }
3535
3536         if (offset) {
3537                 const char* str = jsonObjectGetString(offset);
3538                 buffer_fadd( sql_buf, " OFFSET %d", atoi(str) );
3539         }
3540
3541         if (!(flags & SUBSELECT)) OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
3542
3543         free(core_class);
3544         if (defaultselhash) jsonObjectFree(defaultselhash);
3545
3546         return buffer_release(sql_buf);
3547
3548 }
3549
3550 static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrfHash* meta, osrfMethodContext* ctx ) {
3551
3552         const char* locale = osrf_message_get_last_locale();
3553
3554         osrfHash* fields = osrfHashGet(meta, "fields");
3555         char* core_class = osrfHashGet(meta, "classname");
3556
3557         const jsonObject* join_hash = jsonObjectGetKeyConst( order_hash, "join" );
3558
3559         jsonObject* node = NULL;
3560         jsonObject* snode = NULL;
3561         jsonObject* onode = NULL;
3562         const jsonObject* _tmp = NULL;
3563         jsonObject* selhash = NULL;
3564         jsonObject* defaultselhash = NULL;
3565
3566         growing_buffer* sql_buf = buffer_init(128);
3567         growing_buffer* select_buf = buffer_init(128);
3568
3569         if ( !(selhash = jsonObjectGetKey( order_hash, "select" )) ) {
3570                 defaultselhash = jsonNewObjectType(JSON_HASH);
3571                 selhash = defaultselhash;
3572         }
3573
3574         if ( !jsonObjectGetKeyConst(selhash,core_class) ) {
3575                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
3576                 jsonObject* flist = jsonObjectGetKey( selhash, core_class );
3577
3578                 int i = 0;
3579                 char* field;
3580
3581                 osrfStringArray* keys = osrfHashKeys( fields );
3582                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
3583                         if( ! str_is_true( osrfHashGet( osrfHashGet( fields, field ), "virtual" ) ) )
3584                                 jsonObjectPush( flist, jsonNewObject( field ) );
3585                 }
3586                 osrfStringArrayFree(keys);
3587         }
3588
3589         int first = 1;
3590         jsonIterator* class_itr = jsonNewIterator( selhash );
3591         while ( (snode = jsonIteratorNext( class_itr )) ) {
3592
3593                 char* cname = class_itr->key;
3594                 osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
3595                 if (!idlClass) continue;
3596
3597                 if (strcmp(core_class,class_itr->key)) {
3598                         if (!join_hash) continue;
3599
3600                         jsonObject* found =  jsonObjectFindPath(join_hash, "//%s", class_itr->key);
3601                         if (!found->size) {
3602                                 jsonObjectFree(found);
3603                                 continue;
3604                         }
3605
3606                         jsonObjectFree(found);
3607                 }
3608
3609                 jsonIterator* select_itr = jsonNewIterator( snode );
3610                 while ( (node = jsonIteratorNext( select_itr )) ) {
3611                         const char* item_str = jsonObjectGetString( node );
3612                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
3613                         char* fname = osrfHashGet(field, "name");
3614
3615                         if (!field) continue;
3616
3617                         if (first) {
3618                                 first = 0;
3619                         } else {
3620                                 OSRF_BUFFER_ADD_CHAR(select_buf, ',');
3621                         }
3622
3623             if (locale) {
3624                         const char* i18n;
3625                                 const jsonObject* no_i18n_obj = jsonObjectGetKey( order_hash, "no_i18n" );
3626                                 if ( obj_is_true( no_i18n_obj ) )    // Suppress internationalization?
3627                                         i18n = NULL;
3628                                 else
3629                                         i18n = osrfHashGet(field, "i18n");
3630
3631                                 if( str_is_true( i18n ) ) {
3632                         char* pkey = osrfHashGet(idlClass, "primarykey");
3633                         char* tname = osrfHashGet(idlClass, "tablename");
3634
3635                     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);
3636                 } else {
3637                                 buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
3638                 }
3639             } else {
3640                             buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
3641             }
3642                 }
3643
3644         jsonIteratorFree(select_itr);
3645         }
3646
3647     jsonIteratorFree(class_itr);
3648
3649         char* col_list = buffer_release(select_buf);
3650         char* table = getSourceDefinition(meta);
3651         if( !table )
3652                 table = strdup( "(null)" );
3653
3654         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
3655         free(col_list);
3656         free(table);
3657
3658         if ( join_hash ) {
3659                 char* join_clause = searchJOIN( join_hash, meta );
3660                 OSRF_BUFFER_ADD_CHAR(sql_buf, ' ');
3661                 OSRF_BUFFER_ADD(sql_buf, join_clause);
3662                 free(join_clause);
3663         }
3664
3665         osrfLogDebug(OSRF_LOG_MARK, "%s pre-predicate SQL =  %s",
3666                                  MODULENAME, OSRF_BUFFER_C_STR(sql_buf));
3667
3668         buffer_add(sql_buf, " WHERE ");
3669
3670         char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN, ctx );
3671         if (!pred) {
3672                 osrfAppSessionStatus(
3673                         ctx->session,
3674                         OSRF_STATUS_INTERNALSERVERERROR,
3675                                 "osrfMethodException",
3676                                 ctx->request,
3677                                 "Severe query error -- see error log for more details"
3678                         );
3679                 buffer_free(sql_buf);
3680                 if(defaultselhash) jsonObjectFree(defaultselhash);
3681                 return NULL;
3682         } else {
3683                 buffer_add(sql_buf, pred);
3684                 free(pred);
3685         }
3686
3687         if (order_hash) {
3688                 char* string = NULL;
3689                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "order_by" )) ){
3690
3691                         growing_buffer* order_buf = buffer_init(128);
3692
3693                         first = 1;
3694                         jsonIterator* class_itr = jsonNewIterator( _tmp );
3695                         while ( (snode = jsonIteratorNext( class_itr )) ) {
3696
3697                                 if (!jsonObjectGetKeyConst(selhash,class_itr->key))
3698                                         continue;
3699
3700                                 if ( snode->type == JSON_HASH ) {
3701
3702                                         jsonIterator* order_itr = jsonNewIterator( snode );
3703                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
3704
3705                                                 if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
3706                                                         continue;
3707
3708                                                 char* direction = NULL;
3709                                                 if ( onode->type == JSON_HASH ) {
3710                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
3711                                                                 string = searchFieldTransform(
3712                                                                         class_itr->key,
3713                                                                         oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
3714                                                                         onode
3715                                                                 );
3716                                                                 if( ! string ) {
3717                                                                         osrfAppSessionStatus(
3718                                                                                 ctx->session,
3719                                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3720                                                                                 "osrfMethodException",
3721                                                                                 ctx->request,
3722                                                                                 "Severe query error in ORDER BY clause -- see error log for more details"
3723                                                                         );
3724                                                                         jsonIteratorFree( order_itr );
3725                                                                         jsonIteratorFree( class_itr );
3726                                                                         buffer_free( order_buf );
3727                                                                         buffer_free( sql_buf );
3728                                                                         if( defaultselhash ) jsonObjectFree( defaultselhash );
3729                                                                         return NULL;
3730                                                                 }
3731                                                         } else {
3732                                                                 growing_buffer* field_buf = buffer_init(16);
3733                                                                 buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
3734                                                                 string = buffer_release(field_buf);
3735                                                         }
3736
3737                                                         if ( (_tmp = jsonObjectGetKeyConst( onode, "direction" )) ) {
3738                                                                 const char* dir = jsonObjectGetString(_tmp);
3739                                                                 if (!strncasecmp(dir, "d", 1)) {
3740                                                                         direction = " DESC";
3741                                                                 } else {
3742                                                                         free(direction);
3743                                                                 }
3744                                                         }
3745
3746                                                 } else {
3747                                                         string = strdup(order_itr->key);
3748                                                         const char* dir = jsonObjectGetString(onode);
3749                                                         if (!strncasecmp(dir, "d", 1)) {
3750                                                                 direction = " DESC";
3751                                                         } else {
3752                                                                 direction = " ASC";
3753                                                         }
3754                                                 }
3755
3756                                                 if (first) {
3757                                                         first = 0;
3758                                                 } else {
3759                                                         buffer_add(order_buf, ", ");
3760                                                 }
3761
3762                                                 buffer_add(order_buf, string);
3763                                                 free(string);
3764
3765                                                 if (direction) {
3766                                                         buffer_add(order_buf, direction);
3767                                                 }
3768
3769                                         }
3770
3771                     jsonIteratorFree(order_itr);
3772
3773                                 } else {
3774                                         const char* str = jsonObjectGetString(snode);
3775                                         buffer_add(order_buf, str);
3776                                         break;
3777                                 }
3778
3779                         }
3780
3781                         jsonIteratorFree(class_itr);
3782
3783                         string = buffer_release(order_buf);
3784
3785                         if ( *string ) {
3786                                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
3787                                 OSRF_BUFFER_ADD( sql_buf, string );
3788                         }
3789
3790                         free(string);
3791                 }
3792
3793                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "limit" )) ){
3794                         const char* str = jsonObjectGetString(_tmp);
3795                         buffer_fadd(
3796                                 sql_buf,
3797                                 " LIMIT %d",
3798                                 atoi(str)
3799                         );
3800                 }
3801
3802                 _tmp = jsonObjectGetKeyConst( order_hash, "offset" );
3803                 if (_tmp) {
3804                         const char* str = jsonObjectGetString(_tmp);
3805                         buffer_fadd(
3806                                 sql_buf,
3807                                 " OFFSET %d",
3808                                 atoi(str)
3809                         );
3810                 }
3811         }
3812
3813         if (defaultselhash) jsonObjectFree(defaultselhash);
3814
3815         OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
3816         return buffer_release(sql_buf);
3817 }
3818
3819 int doJSONSearch ( osrfMethodContext* ctx ) {
3820         if(osrfMethodVerifyContext( ctx )) {
3821                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
3822                 return -1;
3823         }
3824
3825         osrfLogDebug(OSRF_LOG_MARK, "Recieved query request");
3826
3827         int err = 0;
3828
3829         // XXX for now...
3830         dbhandle = writehandle;
3831
3832         jsonObject* hash = jsonObjectGetIndex(ctx->params, 0);
3833
3834         int flags = 0;
3835
3836         if ( obj_is_true( jsonObjectGetKey( hash, "distinct" ) ) )
3837                 flags |= SELECT_DISTINCT;
3838
3839         if ( obj_is_true( jsonObjectGetKey( hash, "no_i18n" ) ) )
3840                 flags |= DISABLE_I18N;
3841
3842         osrfLogDebug(OSRF_LOG_MARK, "Building SQL ...");
3843         char* sql = SELECT(
3844                         ctx,
3845                         jsonObjectGetKey( hash, "select" ),
3846                         jsonObjectGetKey( hash, "from" ),
3847                         jsonObjectGetKey( hash, "where" ),
3848                         jsonObjectGetKey( hash, "having" ),
3849                         jsonObjectGetKey( hash, "order_by" ),
3850                         jsonObjectGetKey( hash, "limit" ),
3851                         jsonObjectGetKey( hash, "offset" ),
3852                         flags
3853         );
3854
3855         if (!sql) {
3856                 err = -1;
3857                 return err;
3858         }
3859
3860         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
3861         dbi_result result = dbi_conn_query(dbhandle, sql);
3862
3863         if(result) {
3864                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
3865
3866                 if (dbi_result_first_row(result)) {
3867                         /* JSONify the result */
3868                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
3869
3870                         do {
3871                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
3872                                 osrfAppRespond( ctx, return_val );
3873                 jsonObjectFree( return_val );
3874                         } while (dbi_result_next_row(result));
3875
3876                 } else {
3877                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
3878                 }
3879
3880                 osrfAppRespondComplete( ctx, NULL );
3881
3882                 /* clean up the query */
3883                 dbi_result_free(result);
3884
3885         } else {
3886                 err = -1;
3887                 osrfLogError(OSRF_LOG_MARK, "%s: Error with query [%s]", MODULENAME, sql);
3888                 osrfAppSessionStatus(
3889                         ctx->session,
3890                         OSRF_STATUS_INTERNALSERVERERROR,
3891                         "osrfMethodException",
3892                         ctx->request,
3893                         "Severe query error -- see error log for more details"
3894                 );
3895         }
3896
3897         free(sql);
3898         return err;
3899 }
3900
3901 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* meta,
3902                 const jsonObject* params, int* err ) {
3903
3904         // XXX for now...
3905         dbhandle = writehandle;
3906
3907         osrfHash* links = osrfHashGet(meta, "links");
3908         osrfHash* fields = osrfHashGet(meta, "fields");
3909         char* core_class = osrfHashGet(meta, "classname");
3910         char* pkey = osrfHashGet(meta, "primarykey");
3911
3912         const jsonObject* _tmp;
3913         jsonObject* obj;
3914         jsonObject* search_hash = jsonObjectGetIndex(params, 0);
3915         jsonObject* order_hash = jsonObjectGetIndex(params, 1);
3916
3917         char* sql = buildSELECT( search_hash, order_hash, meta, ctx );
3918         if (!sql) {
3919                 osrfLogDebug(OSRF_LOG_MARK, "Problem building query, returning NULL");
3920                 *err = -1;
3921                 return NULL;
3922         }
3923
3924         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
3925
3926         dbi_result result = dbi_conn_query(dbhandle, sql);
3927         if( NULL == result ) {
3928                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]",
3929                         MODULENAME, osrfHashGet(meta, "fieldmapper"), sql);
3930                 osrfAppSessionStatus(
3931                         ctx->session,
3932                         OSRF_STATUS_INTERNALSERVERERROR,
3933                         "osrfMethodException",
3934                         ctx->request,
3935                         "Severe query error -- see error log for more details"
3936                 );
3937                 *err = -1;
3938                 free(sql);
3939                 return jsonNULL;
3940
3941         } else {
3942                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
3943         }
3944
3945         jsonObject* res_list = jsonNewObjectType(JSON_ARRAY);
3946         osrfHash* dedup = osrfNewHash();
3947
3948         if (dbi_result_first_row(result)) {
3949                 /* JSONify the result */
3950                 osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
3951                 do {
3952                         obj = oilsMakeFieldmapperFromResult( result, meta );
3953                         char* pkey_val = oilsFMGetString( obj, pkey );
3954                         if ( osrfHashGet( dedup, pkey_val ) ) {
3955                                 jsonObjectFree(obj);
3956                                 free(pkey_val);
3957                         } else {
3958                                 osrfHashSet( dedup, pkey_val, pkey_val );
3959                                 jsonObjectPush(res_list, obj);
3960                         }
3961                 } while (dbi_result_next_row(result));
3962         } else {
3963                 osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s",
3964                         MODULENAME, sql );
3965         }
3966
3967         osrfHashFree(dedup);
3968         /* clean up the query */
3969         dbi_result_free(result);
3970         free(sql);
3971
3972         if (res_list->size && order_hash) {
3973                 _tmp = jsonObjectGetKeyConst( order_hash, "flesh" );
3974                 if (_tmp) {
3975                         int x = (int)jsonObjectGetNumber(_tmp);
3976                         if (x == -1 || x > max_flesh_depth) x = max_flesh_depth;
3977
3978                         const jsonObject* temp_blob;
3979                         if ((temp_blob = jsonObjectGetKeyConst( order_hash, "flesh_fields" )) && x > 0) {
3980
3981                                 jsonObject* flesh_blob = jsonObjectClone( temp_blob );
3982                                 const jsonObject* flesh_fields = jsonObjectGetKeyConst( flesh_blob, core_class );
3983
3984                                 osrfStringArray* link_fields = NULL;
3985
3986                                 if (flesh_fields) {
3987                                         if (flesh_fields->size == 1) {
3988                                                 const char* _t = jsonObjectGetString( jsonObjectGetIndex( flesh_fields, 0 ) );
3989                                                 if (!strcmp(_t,"*")) link_fields = osrfHashKeys( links );
3990                                         }
3991
3992                                         if (!link_fields) {
3993                                                 jsonObject* _f;
3994                                                 link_fields = osrfNewStringArray(1);
3995                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
3996                                                 while ((_f = jsonIteratorNext( _i ))) {
3997                                                         osrfStringArrayAdd( link_fields, jsonObjectGetString( _f ) );
3998                                                 }
3999                         jsonIteratorFree(_i);
4000                                         }
4001                                 }
4002
4003                                 jsonObject* cur;
4004                                 jsonIterator* itr = jsonNewIterator( res_list );
4005                                 while ((cur = jsonIteratorNext( itr ))) {
4006
4007                                         int i = 0;
4008                                         char* link_field;
4009
4010                                         while ( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
4011
4012                                                 osrfLogDebug(OSRF_LOG_MARK, "Starting to flesh %s", link_field);
4013
4014                                                 osrfHash* kid_link = osrfHashGet(links, link_field);
4015                                                 if (!kid_link) continue;
4016
4017                                                 osrfHash* field = osrfHashGet(fields, link_field);
4018                                                 if (!field) continue;
4019
4020                                                 osrfHash* value_field = field;
4021
4022                                                 osrfHash* kid_idl = osrfHashGet(oilsIDL(), osrfHashGet(kid_link, "class"));
4023                                                 if (!kid_idl) continue;
4024
4025                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
4026                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
4027                                                 }
4028
4029                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) { // might_have
4030                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
4031                                                 }
4032
4033                                                 osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
4034
4035                                                 if (link_map->size > 0) {
4036                                                         jsonObject* _kid_key = jsonNewObjectType(JSON_ARRAY);
4037                                                         jsonObjectPush(
4038                                                                 _kid_key,
4039                                                                 jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
4040                                                         );
4041
4042                                                         jsonObjectSetKey(
4043                                                                 flesh_blob,
4044                                                                 osrfHashGet(kid_link, "class"),
4045                                                                 _kid_key
4046                                                         );
4047                                                 };
4048
4049                                                 osrfLogDebug(
4050                                                         OSRF_LOG_MARK,
4051                                                         "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
4052                                                         osrfHashGet(kid_link, "field"),
4053                                                         osrfHashGet(kid_link, "class"),
4054                                                         osrfHashGet(kid_link, "key"),
4055                                                         osrfHashGet(kid_link, "reltype")
4056                                                 );
4057
4058                                                 jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
4059                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // search hash
4060                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // order/flesh hash
4061
4062                                                 osrfLogDebug(OSRF_LOG_MARK, "Creating dummy params object...");
4063
4064                                                 const char* search_key = jsonObjectGetString(
4065                                                         jsonObjectGetIndex(
4066                                                                 cur,
4067                                                                 atoi( osrfHashGet(value_field, "array_position") )
4068                                                         )
4069                                                 );
4070
4071                                                 if (!search_key) {
4072                                                         osrfLogDebug(OSRF_LOG_MARK, "Nothing to search for!");
4073                                                         continue;
4074                                                 }
4075
4076                                                 jsonObjectSetKey(
4077                                                         jsonObjectGetIndex(fake_params, 0),
4078                                                         osrfHashGet(kid_link, "key"),
4079                                                         jsonNewObject( search_key )
4080                                                 );
4081
4082                                                 jsonObjectSetKey(
4083                                                         jsonObjectGetIndex(fake_params, 1),
4084                                                         "flesh",
4085                                                         jsonNewNumberObject( (double)(x - 1 + link_map->size) )
4086                                                 );
4087
4088                                                 if (flesh_blob)
4089                                                         jsonObjectSetKey( jsonObjectGetIndex(fake_params, 1), "flesh_fields", jsonObjectClone(flesh_blob) );
4090
4091                                                 if (jsonObjectGetKeyConst(order_hash, "order_by")) {
4092                                                         jsonObjectSetKey(
4093                                                                 jsonObjectGetIndex(fake_params, 1),
4094                                                                 "order_by",
4095                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "order_by"))
4096                                                         );
4097                                                 }
4098
4099                                                 if (jsonObjectGetKeyConst(order_hash, "select")) {
4100                                                         jsonObjectSetKey(
4101                                                                 jsonObjectGetIndex(fake_params, 1),
4102                                                                 "select",
4103                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "select"))
4104                                                         );
4105                                                 }
4106
4107                                                 jsonObject* kids = doFieldmapperSearch(ctx, kid_idl, fake_params, err);
4108
4109                                                 if(*err) {
4110                                                         jsonObjectFree( fake_params );
4111                                                         osrfStringArrayFree(link_fields);
4112                                                         jsonIteratorFree(itr);
4113                                                         jsonObjectFree(res_list);
4114                                                         jsonObjectFree(flesh_blob);
4115                                                         return jsonNULL;
4116                                                 }
4117
4118                                                 osrfLogDebug(OSRF_LOG_MARK, "Search for %s return %d linked objects", osrfHashGet(kid_link, "class"), kids->size);
4119
4120                                                 jsonObject* X = NULL;
4121                                                 if ( link_map->size > 0 && kids->size > 0 ) {
4122                                                         X = kids;
4123                                                         kids = jsonNewObjectType(JSON_ARRAY);
4124
4125                                                         jsonObject* _k_node;
4126                                                         jsonIterator* _k = jsonNewIterator( X );
4127                                                         while ((_k_node = jsonIteratorNext( _k ))) {
4128                                                                 jsonObjectPush(
4129                                                                         kids,
4130                                                                         jsonObjectClone(
4131                                                                                 jsonObjectGetIndex(
4132                                                                                         _k_node,
4133                                                                                         (unsigned long)atoi(
4134                                                                                                 osrfHashGet(
4135                                                                                                         osrfHashGet(
4136                                                                                                                 osrfHashGet(
4137                                                                                                                         osrfHashGet(
4138                                                                                                                                 oilsIDL(),
4139                                                                                                                                 osrfHashGet(kid_link, "class")
4140                                                                                                                         ),
4141                                                                                                                         "fields"
4142                                                                                                                 ),
4143                                                                                                                 osrfStringArrayGetString( link_map, 0 )
4144                                                                                                         ),
4145                                                                                                         "array_position"
4146                                                                                                 )
4147                                                                                         )
4148                                                                                 )
4149                                                                         )
4150                                                                 );
4151                                                         }
4152                                                         jsonIteratorFree(_k);
4153                                                 }
4154
4155                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_a" )) || !(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) {
4156                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
4157                                                         jsonObjectSetIndex(
4158                                                                 cur,
4159                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
4160                                                                 jsonObjectClone( jsonObjectGetIndex(kids, 0) )
4161                                                         );
4162                                                 }
4163
4164                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
4165                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
4166                                                         jsonObjectSetIndex(
4167                                                                 cur,
4168                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
4169                                                                 jsonObjectClone( kids )
4170                                                         );
4171                                                 }
4172
4173                                                 if (X) {
4174                                                         jsonObjectFree(kids);
4175                                                         kids = X;
4176                                                 }
4177
4178                                                 jsonObjectFree( kids );
4179                                                 jsonObjectFree( fake_params );
4180
4181                                                 osrfLogDebug(OSRF_LOG_MARK, "Fleshing of %s complete", osrfHashGet(kid_link, "field"));
4182                                                 osrfLogDebug(OSRF_LOG_MARK, "%s", jsonObjectToJSON(cur));
4183
4184                                         }
4185                                 }
4186                                 jsonObjectFree( flesh_blob );
4187                                 osrfStringArrayFree(link_fields);
4188                                 jsonIteratorFree(itr);
4189                         }
4190                 }
4191         }
4192
4193         return res_list;
4194 }
4195
4196
4197 static jsonObject* doUpdate(osrfMethodContext* ctx, int* err ) {
4198
4199         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
4200 #ifdef PCRUD
4201         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
4202 #else
4203         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
4204 #endif
4205
4206         if (!verifyObjectClass(ctx, target)) {
4207                 *err = -1;
4208                 return jsonNULL;
4209         }
4210
4211         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
4212                 osrfAppSessionStatus(
4213                         ctx->session,
4214                         OSRF_STATUS_BADREQUEST,
4215                         "osrfMethodException",
4216                         ctx->request,
4217                         "No active transaction -- required for UPDATE"
4218                 );
4219                 *err = -1;
4220                 return jsonNULL;
4221         }
4222
4223         // The following test is harmless but redundant.  If a class is
4224         // readonly, we don't register an update method for it.
4225         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
4226                 osrfAppSessionStatus(
4227                         ctx->session,
4228                         OSRF_STATUS_BADREQUEST,
4229                         "osrfMethodException",
4230                         ctx->request,
4231                         "Cannot UPDATE readonly class"
4232                 );
4233                 *err = -1;
4234                 return jsonNULL;
4235         }
4236
4237         dbhandle = writehandle;
4238
4239         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
4240
4241         // Set the last_xact_id
4242         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
4243         if (index > -1) {
4244                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
4245                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
4246         }
4247
4248         char* pkey = osrfHashGet(meta, "primarykey");
4249         osrfHash* fields = osrfHashGet(meta, "fields");
4250
4251         char* id = oilsFMGetString( target, pkey );
4252
4253         osrfLogDebug(
4254                 OSRF_LOG_MARK,
4255                 "%s updating %s object with %s = %s",
4256                 MODULENAME,
4257                 osrfHashGet(meta, "fieldmapper"),
4258                 pkey,
4259                 id
4260         );
4261
4262         growing_buffer* sql = buffer_init(128);
4263         buffer_fadd(sql,"UPDATE %s SET", osrfHashGet(meta, "tablename"));
4264
4265         int i = 0;
4266         int first = 1;
4267         char* field_name;
4268         osrfStringArray* field_list = osrfHashKeys( fields );
4269         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
4270
4271                 osrfHash* field = osrfHashGet( fields, field_name );
4272
4273                 if(!( strcmp( field_name, pkey ) )) continue;
4274                 if( str_is_true( osrfHashGet(osrfHashGet(fields,field_name), "virtual") ) )
4275                         continue;
4276
4277                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
4278
4279                 int value_is_numeric = 0;    // boolean
4280                 char* value;
4281                 if (field_object && field_object->classname) {
4282                         value = oilsFMGetString(
4283                                 field_object,
4284                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
4285             );
4286                 } else {
4287                         value = jsonObjectToSimpleString( field_object );
4288                         if( field_object && JSON_NUMBER == field_object->type )
4289                                 value_is_numeric = 1;
4290                 }
4291
4292                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s", osrfHashGet(meta, "fieldmapper"), field_name, value);
4293
4294                 if (!field_object || field_object->type == JSON_NULL) {
4295                         if ( !(!( strcmp( osrfHashGet(meta, "classname"), "au" ) ) && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
4296                                 if (first) first = 0;
4297                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
4298                                 buffer_fadd( sql, " %s = NULL", field_name );
4299                         }
4300
4301                 } else if ( value_is_numeric || !strcmp( get_primitive( field ), "number") ) {
4302                         if (first) first = 0;
4303                         else OSRF_BUFFER_ADD_CHAR(sql, ',');
4304
4305                         const char* numtype = get_datatype( field );
4306                         if ( !strncmp( numtype, "INT", 3 ) ) {
4307                                 buffer_fadd( sql, " %s = %ld", field_name, atol(value) );
4308                         } else if ( !strcmp( numtype, "NUMERIC" ) ) {
4309                                 buffer_fadd( sql, " %s = %f", field_name, atof(value) );
4310                         } else {
4311                                 // Must really be intended as a string, so quote it
4312                                 if ( dbi_conn_quote_string(dbhandle, &value) ) {
4313                                         buffer_fadd( sql, " %s = %s", field_name, value );
4314                                 } else {
4315                                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
4316                                         osrfAppSessionStatus(
4317                                                 ctx->session,
4318                                                 OSRF_STATUS_INTERNALSERVERERROR,
4319                                                 "osrfMethodException",
4320                                                 ctx->request,
4321                                                 "Error quoting string -- please see the error log for more details"
4322                                         );
4323                                         free(value);
4324                                         free(id);
4325                                         buffer_free(sql);
4326                                         *err = -1;
4327                                         return jsonNULL;
4328                                 }
4329                         }
4330
4331                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, numtype );
4332
4333                 } else {
4334                         if ( dbi_conn_quote_string(dbhandle, &value) ) {
4335                                 if (first) first = 0;
4336                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
4337                                 buffer_fadd( sql, " %s = %s", field_name, value );
4338
4339                         } else {
4340                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
4341                                 osrfAppSessionStatus(
4342                                         ctx->session,
4343                                         OSRF_STATUS_INTERNALSERVERERROR,
4344                                         "osrfMethodException",
4345                                         ctx->request,
4346                                         "Error quoting string -- please see the error log for more details"
4347                                 );
4348                                 free(value);
4349                                 free(id);
4350                                 buffer_free(sql);
4351                                 *err = -1;
4352                                 return jsonNULL;
4353                         }
4354                 }
4355
4356                 free(value);
4357
4358         }
4359
4360         jsonObject* obj = jsonNewObject(id);
4361
4362         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
4363                 dbi_conn_quote_string(dbhandle, &id);
4364
4365         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
4366
4367         char* query = buffer_release(sql);
4368         osrfLogDebug(OSRF_LOG_MARK, "%s: Update SQL [%s]", MODULENAME, query);
4369
4370         dbi_result result = dbi_conn_query(dbhandle, query);
4371         free(query);
4372
4373         if (!result) {
4374                 jsonObjectFree(obj);
4375                 obj = jsonNewObject(NULL);
4376                 osrfLogError(
4377                         OSRF_LOG_MARK,
4378                         "%s ERROR updating %s object with %s = %s",
4379                         MODULENAME,
4380                         osrfHashGet(meta, "fieldmapper"),
4381                         pkey,
4382                         id
4383                 );
4384         }
4385
4386         free(id);
4387
4388         return obj;
4389 }
4390
4391 static jsonObject* doDelete(osrfMethodContext* ctx, int* err ) {
4392
4393         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
4394
4395         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
4396                 osrfAppSessionStatus(
4397                         ctx->session,
4398                         OSRF_STATUS_BADREQUEST,
4399                         "osrfMethodException",
4400                         ctx->request,
4401                         "No active transaction -- required for DELETE"
4402                 );
4403                 *err = -1;
4404                 return jsonNULL;
4405         }
4406
4407         // The following test is harmless but redundant.  If a class is
4408         // readonly, we don't register a delete method for it.
4409         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
4410                 osrfAppSessionStatus(
4411                         ctx->session,
4412                         OSRF_STATUS_BADREQUEST,
4413                         "osrfMethodException",
4414                         ctx->request,
4415                         "Cannot DELETE readonly class"
4416                 );
4417                 *err = -1;
4418                 return jsonNULL;
4419         }
4420
4421         dbhandle = writehandle;
4422
4423         jsonObject* obj;
4424
4425         char* pkey = osrfHashGet(meta, "primarykey");
4426
4427         int _obj_pos = 0;
4428 #ifdef PCRUD
4429                 _obj_pos = 1;
4430 #endif
4431
4432         char* id;
4433         if (jsonObjectGetIndex(ctx->params, _obj_pos)->classname) {
4434                 if (!verifyObjectClass(ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
4435                         *err = -1;
4436                         return jsonNULL;
4437                 }
4438
4439                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
4440         } else {
4441 #ifdef PCRUD
4442         if (!verifyObjectPCRUD( ctx, NULL )) {
4443                         *err = -1;
4444                         return jsonNULL;
4445         }
4446 #endif
4447                 id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, _obj_pos));
4448         }
4449
4450         osrfLogDebug(
4451                 OSRF_LOG_MARK,
4452                 "%s deleting %s object with %s = %s",
4453                 MODULENAME,
4454                 osrfHashGet(meta, "fieldmapper"),
4455                 pkey,
4456                 id
4457         );
4458
4459         obj = jsonNewObject(id);
4460
4461         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
4462                 dbi_conn_quote_string(writehandle, &id);
4463
4464         dbi_result result = dbi_conn_queryf(writehandle, "DELETE FROM %s WHERE %s = %s;", osrfHashGet(meta, "tablename"), pkey, id);
4465
4466         if (!result) {
4467                 jsonObjectFree(obj);
4468                 obj = jsonNewObject(NULL);
4469                 osrfLogError(
4470                         OSRF_LOG_MARK,
4471                         "%s ERROR deleting %s object with %s = %s",
4472                         MODULENAME,
4473                         osrfHashGet(meta, "fieldmapper"),
4474                         pkey,
4475                         id
4476                 );
4477         }
4478
4479         free(id);
4480
4481         return obj;
4482
4483 }
4484
4485
4486 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
4487         if(!(result && meta)) return jsonNULL;
4488
4489         jsonObject* object = jsonNewObject(NULL);
4490         jsonObjectSetClass(object, osrfHashGet(meta, "classname"));
4491
4492         osrfHash* fields = osrfHashGet(meta, "fields");
4493
4494         osrfLogInternal(OSRF_LOG_MARK, "Setting object class to %s ", object->classname);
4495
4496         osrfHash* _f;
4497         time_t _tmp_dt;
4498         char dt_string[256];
4499         struct tm gmdt;
4500
4501         int fmIndex;
4502         int columnIndex = 1;
4503         int attr;
4504         unsigned short type;
4505         const char* columnName;
4506
4507         /* cycle through the column list */
4508         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
4509
4510                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
4511
4512                 fmIndex = -1; // reset the position
4513
4514                 /* determine the field type and storage attributes */
4515                 type = dbi_result_get_field_type(result, columnName);
4516                 attr = dbi_result_get_field_attribs(result, columnName);
4517
4518                 /* fetch the fieldmapper index */
4519                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
4520
4521                         if ( str_is_true( osrfHashGet(_f, "virtual") ) )
4522                                 continue;
4523
4524                         const char* pos = (char*)osrfHashGet(_f, "array_position");
4525                         if ( !pos ) continue;
4526
4527                         fmIndex = atoi( pos );
4528                         osrfLogInternal(OSRF_LOG_MARK, "... Found column at position [%s]...", pos);
4529                 } else {
4530                         continue;
4531                 }
4532
4533                 if (dbi_result_field_is_null(result, columnName)) {
4534                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(NULL) );
4535                 } else {
4536
4537                         switch( type ) {
4538
4539                                 case DBI_TYPE_INTEGER :
4540
4541                                         if( attr & DBI_INTEGER_SIZE8 )
4542                                                 jsonObjectSetIndex( object, fmIndex,
4543                                                         jsonNewNumberObject(dbi_result_get_longlong(result, columnName)));
4544                                         else
4545                                                 jsonObjectSetIndex( object, fmIndex,
4546                                                         jsonNewNumberObject(dbi_result_get_int(result, columnName)));
4547
4548                                         break;
4549
4550                                 case DBI_TYPE_DECIMAL :
4551                                         jsonObjectSetIndex( object, fmIndex,
4552                                                         jsonNewNumberObject(dbi_result_get_double(result, columnName)));
4553                                         break;
4554
4555                                 case DBI_TYPE_STRING :
4556
4557
4558                                         jsonObjectSetIndex(
4559                                                 object,
4560                                                 fmIndex,
4561                                                 jsonNewObject( dbi_result_get_string(result, columnName) )
4562                                         );
4563
4564                                         break;
4565
4566                                 case DBI_TYPE_DATETIME :
4567
4568                                         memset(dt_string, '\0', sizeof(dt_string));
4569                                         memset(&gmdt, '\0', sizeof(gmdt));
4570
4571                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
4572
4573
4574                                         if (!(attr & DBI_DATETIME_DATE)) {
4575                                                 gmtime_r( &_tmp_dt, &gmdt );
4576                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
4577                                         } else if (!(attr & DBI_DATETIME_TIME)) {
4578                                                 localtime_r( &_tmp_dt, &gmdt );
4579                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
4580                                         } else {
4581                                                 localtime_r( &_tmp_dt, &gmdt );
4582                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
4583                                         }
4584
4585                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(dt_string) );
4586
4587                                         break;
4588
4589                                 case DBI_TYPE_BINARY :
4590                                         osrfLogError( OSRF_LOG_MARK,
4591                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
4592                         }
4593                 }
4594         }
4595
4596         return object;
4597 }
4598
4599 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
4600         if(!result) return jsonNULL;
4601
4602         jsonObject* object = jsonNewObject(NULL);
4603
4604         time_t _tmp_dt;
4605         char dt_string[256];
4606         struct tm gmdt;
4607
4608         int fmIndex;
4609         int columnIndex = 1;
4610         int attr;
4611         unsigned short type;
4612         const char* columnName;
4613
4614         /* cycle through the column list */
4615         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
4616
4617                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
4618
4619                 fmIndex = -1; // reset the position
4620
4621                 /* determine the field type and storage attributes */
4622                 type = dbi_result_get_field_type(result, columnName);
4623                 attr = dbi_result_get_field_attribs(result, columnName);
4624
4625                 if (dbi_result_field_is_null(result, columnName)) {
4626                         jsonObjectSetKey( object, columnName, jsonNewObject(NULL) );
4627                 } else {
4628
4629                         switch( type ) {
4630
4631                                 case DBI_TYPE_INTEGER :
4632
4633                                         if( attr & DBI_INTEGER_SIZE8 )
4634                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_longlong(result, columnName)) );
4635                                         else
4636                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_int(result, columnName)) );
4637                                         break;
4638
4639                                 case DBI_TYPE_DECIMAL :
4640                                         jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_double(result, columnName)) );
4641                                         break;
4642
4643                                 case DBI_TYPE_STRING :
4644                                         jsonObjectSetKey( object, columnName, jsonNewObject(dbi_result_get_string(result, columnName)) );
4645                                         break;
4646
4647                                 case DBI_TYPE_DATETIME :
4648
4649                                         memset(dt_string, '\0', sizeof(dt_string));
4650                                         memset(&gmdt, '\0', sizeof(gmdt));
4651
4652                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
4653
4654
4655                                         if (!(attr & DBI_DATETIME_DATE)) {
4656                                                 gmtime_r( &_tmp_dt, &gmdt );
4657                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
4658                                         } else if (!(attr & DBI_DATETIME_TIME)) {
4659                                                 localtime_r( &_tmp_dt, &gmdt );
4660                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
4661                                         } else {
4662                                                 localtime_r( &_tmp_dt, &gmdt );
4663                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
4664                                         }
4665
4666                                         jsonObjectSetKey( object, columnName, jsonNewObject(dt_string) );
4667                                         break;
4668
4669                                 case DBI_TYPE_BINARY :
4670                                         osrfLogError( OSRF_LOG_MARK,
4671                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
4672                         }
4673                 }
4674         }
4675
4676         return object;
4677 }
4678
4679 // Interpret a string as true or false
4680 static int str_is_true( const char* str ) {
4681         if( NULL == str || strcasecmp( str, "true" ) )
4682                 return 0;
4683         else
4684                 return 1;
4685 }
4686
4687 // Interpret a jsonObject as true or false
4688 static int obj_is_true( const jsonObject* obj ) {
4689         if( !obj )
4690                 return 0;
4691         else switch( obj->type )
4692         {
4693                 case JSON_BOOL :
4694                         if( obj->value.b )
4695                                 return 1;
4696                         else
4697                                 return 0;
4698                 case JSON_STRING :
4699                         if( strcasecmp( obj->value.s, "true" ) )
4700                                 return 0;
4701                         else
4702                                 return 1;
4703                 case JSON_NUMBER :          // Support 1/0 for perl's sake
4704                         if( jsonObjectGetNumber( obj ) == 1.0 )
4705                                 return 1;
4706                         else
4707                                 return 0;
4708                 default :
4709                         return 0;
4710         }
4711 }
4712
4713 // Translate a numeric code into a text string identifying a type of
4714 // jsonObject.  To be used for building error messages.
4715 static const char* json_type( int code ) {
4716         switch ( code )
4717         {
4718                 case 0 :
4719                         return "JSON_HASH";
4720                 case 1 :
4721                         return "JSON_ARRAY";
4722                 case 2 :
4723                         return "JSON_STRING";
4724                 case 3 :
4725                         return "JSON_NUMBER";
4726                 case 4 :
4727                         return "JSON_NULL";
4728                 case 5 :
4729                         return "JSON_BOOL";
4730                 default :
4731                         return "(unrecognized)";
4732         }
4733 }
4734
4735 // Extract the "primitive" attribute from an IDL field definition.
4736 // If we haven't initialized the app, then we must be running in
4737 // some kind of testbed.  In that case, default to "string".
4738 static const char* get_primitive( osrfHash* field ) {
4739         const char* s = osrfHashGet( field, "primitive" );
4740         if( !s ) {
4741                 if( child_initialized )
4742                         osrfLogError(
4743                                 OSRF_LOG_MARK,
4744                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
4745                                 MODULENAME,
4746                                 osrfHashGet( field, "name" )
4747                         );
4748                 else
4749                         s = "string";
4750         }
4751         return s;
4752 }
4753
4754 // Extract the "datatype" attribute from an IDL field definition.
4755 // If we haven't initialized the app, then we must be running in
4756 // some kind of testbed.  In that case, default to to NUMERIC,
4757 // since we look at the datatype only for numbers.
4758 static const char* get_datatype( osrfHash* field ) {
4759         const char* s = osrfHashGet( field, "datatype" );
4760         if( !s ) {
4761                 if( child_initialized )
4762                         osrfLogError(
4763                                 OSRF_LOG_MARK,
4764                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
4765                                 MODULENAME,
4766                                 osrfHashGet( field, "name" )
4767                         );
4768                 else
4769                         s = "NUMERIC";
4770         }
4771         return s;
4772 }
4773
4774 /*
4775 If the input string is potentially a valid SQL identifier, return 1.
4776 Otherwise return 0.
4777
4778 Purpose: to prevent certain kinds of SQL injection.  To that end we
4779 don't necessarily need to follow all the rules exactly, such as requiring
4780 that the first character not be a digit.
4781
4782 We allow leading and trailing white space.  In between, we do not allow
4783 punctuation (except for underscores and dollar signs), control
4784 characters, or embedded white space.
4785
4786 More pedantically we should allow quoted identifiers containing arbitrary
4787 characters, but for the foreseeable future such quoted identifiers are not
4788 likely to be an issue.
4789 */
4790 static int is_identifier( const char* s) {
4791         if( !s )
4792                 return 0;
4793
4794         // Skip leading white space
4795         while( isspace( (unsigned char) *s ) )
4796                 ++s;
4797
4798         if( !s )
4799                 return 0;   // Nothing but white space?  Not okay.
4800
4801         // Check each character until we reach white space or
4802         // end-of-string.  Letters, digits, underscores, and
4803         // dollar signs are okay.  Control characters and other
4804         // punctuation characters are not okay.  Anything else
4805         // is okay -- it could for example be part of a multibyte
4806         // UTF8 character such as a letter with diacritical marks,
4807         // and those are allowed.
4808         do {
4809                 if( isalnum( (unsigned char) *s )
4810                         || '_' == *s
4811                         || '$' == *s )
4812                         ;  // Fine; keep going
4813                 else if(   ispunct( (unsigned char) *s )
4814                                 || iscntrl( (unsigned char) *s ) )
4815                         return 0;
4816                         ++s;
4817         } while( *s && ! isspace( (unsigned char) *s ) );
4818
4819         // If we found any white space in the above loop,
4820         // the rest had better be all white space.
4821
4822         while( isspace( (unsigned char) *s ) )
4823                 ++s;
4824
4825         if( *s )
4826                 return 0;   // White space was embedded within non-white space
4827
4828         return 1;
4829 }
4830
4831 /*
4832 Determine whether to accept a character string as a comparison operator.
4833 Return 1 if it's good, or 0 if it's bad.
4834
4835 We don't validate it for real.  We just make sure that it doesn't contain
4836 any semicolons or white space (with a special exception for the
4837 "SIMILAR TO" operator).  The idea is to block certain kinds of SQL
4838 injection.  If it has no semicolons or white space but it's still not a
4839 valid operator, then the database will complain.
4840
4841 Another approach would be to compare the string against a short list of
4842 approved operators.  We don't do that because we want to allow custom
4843 operators like ">100*", which would be difficult or impossible to
4844 express otherwise in a JSON query.
4845 */
4846 static int is_good_operator( const char* op ) {
4847         if( !op ) return 0;   // Sanity check
4848
4849         const char* s = op;
4850         while( *s ) {
4851                 if( isspace( (unsigned char) *s ) ) {
4852                         // Special exception for SIMILAR TO.  Someday we might make
4853                         // exceptions for IS DISTINCT FROM and IS NOT DISTINCT FROM.
4854                         if( !strcasecmp( op, "similar to" ) )
4855                                 return 1;
4856                         else
4857                                 return 0;
4858                 }
4859                 else if( ';' == *s )
4860                         return 0;
4861                 ++s;
4862         }
4863         return 1;
4864 }