Open-ILS/src/c-apps/oils_cstore.c

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