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