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