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

   1 #include "opensrf/osrf_application.h"
   2 #include "opensrf/osrf_settings.h"
   3 #include "opensrf/osrf_message.h"
   4 #include "opensrf/utils.h"
   5 #include "opensrf/osrf_json.h"
   6 #include "opensrf/log.h"
   7 #include "openils/oils_utils.h"
   8 #include <dbi/dbi.h>
   9
  10 #include <time.h>
  11 #include <stdlib.h>
  12 #include <string.h>
  13 #include <unistd.h>
  14
  15 #ifdef RSTORE
  16 #  define MODULENAME "open-ils.reporter-store"
  17 #else
  18 #  ifdef PCRUD
  19 #    define MODULENAME "open-ils.pcrud"
  20 #  else
  21 #    define MODULENAME "open-ils.cstore"
  22 #  endif
  23 #endif
  24
  25 #define SUBSELECT       4
  26 #define DISABLE_I18N    2
  27 #define SELECT_DISTINCT 1
  28 #define AND_OP_JOIN     0
  29 #define OR_OP_JOIN      1
  30
  31 int osrfAppChildInit();
  32 int osrfAppInitialize();
  33 void osrfAppChildExit();
  34
  35 static int verifyObjectClass ( osrfMethodContext*, const jsonObject* );
  36
  37 int beginTransaction ( osrfMethodContext* );
  38 int commitTransaction ( osrfMethodContext* );
  39 int rollbackTransaction ( osrfMethodContext* );
  40
  41 int setSavepoint ( osrfMethodContext* );
  42 int releaseSavepoint ( osrfMethodContext* );
  43 int rollbackSavepoint ( osrfMethodContext* );
  44
  45 int doJSONSearch ( osrfMethodContext* );
  46
  47 int dispatchCRUDMethod ( osrfMethodContext* );
  48 static jsonObject* doCreate ( osrfMethodContext*, int* );
  49 static jsonObject* doRetrieve ( osrfMethodContext*, int* );
  50 static jsonObject* doUpdate ( osrfMethodContext*, int* );
  51 static jsonObject* doDelete ( osrfMethodContext*, int* );
  52 static jsonObject* doFieldmapperSearch ( osrfMethodContext*, osrfHash*,
  53                 const jsonObject*, int* );
  54 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result, osrfHash* );
  55 static jsonObject* oilsMakeJSONFromResult( dbi_result );
  56
  57 static char* searchWriteSimplePredicate ( const char*, osrfHash*,
  58         const char*, const char*, const char* );
  59 static char* searchSimplePredicate ( const char*, const char*, osrfHash*, const jsonObject* );
  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*, jsonObject*, const char* );
  63 static char* searchBETWEENPredicate ( const char*, osrfHash*, jsonObject* );
  64 static char* searchINPredicate ( const char*, osrfHash*, const jsonObject*, const char* );
  65 static char* searchPredicate ( const char*, osrfHash*, jsonObject* );
  66 static char* searchJOIN ( const jsonObject*, osrfHash* );
  67 static char* searchWHERE ( const jsonObject*, osrfHash*, int, osrfMethodContext* );
  68 static char* buildSELECT ( jsonObject*, jsonObject*, osrfHash*, osrfMethodContext* );
  69
  70 static char* SELECT ( osrfMethodContext*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, int );
  71
  72 void userDataFree( void* );
  73 static void sessionDataFree( char*, void* );
  74 static char* getSourceDefinition( osrfHash* );
  75
  76 #ifdef PCRUD
  77 static int verifyObjectPCRUD( osrfMethodContext*, const jsonObject* );
  78 #endif
  79
  80 static dbi_conn writehandle; /* our MASTER db connection */
  81 static dbi_conn dbhandle; /* our CURRENT db connection */
  82 //static osrfHash * readHandles;
  83 static jsonObject* jsonNULL = NULL; //
  84 static int max_flesh_depth = 100;
  85
  86 /* called when this process is about to exit */
  87 void osrfAppChildExit() {
  88         osrfLogDebug(OSRF_LOG_MARK, "Child is exiting, disconnecting from database...");
  89
  90     int same = 0;
  91     if (writehandle == dbhandle) same = 1;
  92     if (writehandle) {
  93         dbi_conn_query(writehandle, "ROLLBACK;");
  94         dbi_conn_close(writehandle);
  95         writehandle = NULL;
  96     }
  97     if (dbhandle && !same)
  98         dbi_conn_close(dbhandle);
  99
 100         // XXX add cleanup of readHandles whenever that gets used
 101
 102         return;
 103 }
 104
 105 int osrfAppInitialize() {
 106
 107         osrfLogInfo(OSRF_LOG_MARK, "Initializing the CStore Server...");
 108         osrfLogInfo(OSRF_LOG_MARK, "Finding XML file...");
 109
 110         if (!oilsIDLInit( osrf_settings_host_value("/IDL") )) return 1; /* return non-zero to indicate error */
 111
 112     char* method_str = NULL;
 113         growing_buffer* method_name = buffer_init(64);
 114 #ifndef PCRUD
 115         // Generic search thingy
 116         buffer_fadd(method_name, "%s.json_query", MODULENAME);
 117         method_str = buffer_data(method_name);
 118         osrfAppRegisterMethod( MODULENAME, method_str, "doJSONSearch", "", 1, OSRF_METHOD_STREAMING );
 119         free(method_str);
 120 #endif
 121
 122         // first we register all the transaction and savepoint methods
 123         buffer_reset(method_name);
 124         buffer_fadd(method_name, "%s.transaction.begin", MODULENAME);
 125         method_str = buffer_data(method_name);
 126         osrfAppRegisterMethod( MODULENAME, method_str, "beginTransaction", "", 0, 0 );
 127         free(method_str);
 128
 129         buffer_reset(method_name);
 130         buffer_fadd(method_name, "%s.transaction.commit", MODULENAME);
 131         method_str = buffer_data(method_name);
 132         osrfAppRegisterMethod( MODULENAME, method_str, "commitTransaction", "", 0, 0 );
 133         free(method_str);
 134
 135         buffer_reset(method_name);
 136         buffer_fadd(method_name, "%s.transaction.rollback", MODULENAME);
 137         method_str = buffer_data(method_name);
 138         osrfAppRegisterMethod( MODULENAME, method_str, "rollbackTransaction", "", 0, 0 );
 139         free(method_str);
 140
 141         buffer_reset(method_name);
 142         buffer_fadd(method_name, "%s.savepoint.set", MODULENAME);
 143         method_str = buffer_data(method_name);
 144         osrfAppRegisterMethod( MODULENAME, method_str, "setSavepoint", "", 1, 0 );
 145         free(method_str);
 146
 147         buffer_reset(method_name);
 148         buffer_fadd(method_name, "%s.savepoint.release", MODULENAME);
 149         method_str = buffer_data(method_name);
 150         osrfAppRegisterMethod( MODULENAME, method_str, "releaseSavepoint", "", 1, 0 );
 151         free(method_str);
 152
 153         buffer_reset(method_name);
 154         buffer_fadd(method_name, "%s.savepoint.rollback", MODULENAME);
 155         method_str = buffer_data(method_name);
 156         osrfAppRegisterMethod( MODULENAME, method_str, "rollbackSavepoint", "", 1, 0 );
 157         free(method_str);
 158
 159         buffer_free(method_name);
 160
 161         osrfStringArray* global_methods = osrfNewStringArray(6);
 162
 163         osrfStringArrayAdd( global_methods, "create" );
 164         osrfStringArrayAdd( global_methods, "retrieve" );
 165         osrfStringArrayAdd( global_methods, "update" );
 166         osrfStringArrayAdd( global_methods, "delete" );
 167         osrfStringArrayAdd( global_methods, "search" );
 168         osrfStringArrayAdd( global_methods, "id_list" );
 169
 170         int c_index = 0;
 171         char* classname;
 172         osrfStringArray* classes = osrfHashKeys( oilsIDL() );
 173         osrfLogDebug(OSRF_LOG_MARK, "%d classes loaded", classes->size );
 174         osrfLogDebug(OSRF_LOG_MARK, "At least %d methods will be generated", classes->size * global_methods->size);
 175
 176         while ( (classname = osrfStringArrayGetString(classes, c_index++)) ) {
 177                 osrfLogInfo(OSRF_LOG_MARK, "Generating class methods for %s", classname);
 178
 179                 osrfHash* idlClass = osrfHashGet(oilsIDL(), classname);
 180
 181                 if (!osrfStringArrayContains( osrfHashGet(idlClass, "controller"), MODULENAME )) {
 182                         osrfLogInfo(OSRF_LOG_MARK, "%s is not listed as a controller for %s, moving on", MODULENAME, classname);
 183                         continue;
 184                 }
 185
 186                 char* virt = osrfHashGet(idlClass, "virtual");
 187                 if (virt && !strcmp( virt, "true")) {
 188                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 189                         continue;
 190                 }
 191
 192                 int i = 0;
 193                 char* method_type;
 194                 char* st_tmp = NULL;
 195                 char* _fm;
 196                 char* part;
 197                 osrfHash* method_meta;
 198                 while ( (method_type = osrfStringArrayGetString(global_methods, i++)) ) {
 199                         osrfLogDebug(OSRF_LOG_MARK, "Using files to build %s class methods for %s", method_type, classname);
 200
 201                         if (!osrfHashGet(idlClass, "fieldmapper")) continue;
 202
 203 #ifdef PCRUD
 204                         if (!osrfHashGet(idlClass, "permacrud")) continue;
 205                         if (!osrfHashGet( osrfHashGet(idlClass, "permacrud"), method_type )) continue;
 206 #endif
 207
 208                         char* readonly = osrfHashGet(idlClass, "readonly");
 209                         if (    readonly &&
 210                                 !strncasecmp( "true", readonly, 4) &&
 211                                 ( *method_type == 'c' || *method_type == 'u' || *method_type == 'd')
 212                         ) continue;
 213
 214                         method_meta = osrfNewHash();
 215                         osrfHashSet(method_meta, idlClass, "class");
 216
 217                         _fm = strdup( (char*)osrfHashGet(idlClass, "fieldmapper") );
 218                         part = strtok_r(_fm, ":", &st_tmp);
 219
 220                         method_name =  buffer_init(64);
 221                         buffer_fadd(method_name, "%s.direct.%s", MODULENAME, part);
 222
 223                         while ((part = strtok_r(NULL, ":", &st_tmp))) {
 224                                 buffer_fadd(method_name, ".%s", part);
 225                         }
 226                         buffer_fadd(method_name, ".%s", method_type);
 227
 228
 229                         char* method = buffer_release(method_name);
 230                         free(_fm);
 231
 232                         osrfHashSet( method_meta, method, "methodname" );
 233                         osrfHashSet( method_meta, strdup(method_type), "methodtype" );
 234
 235                         int flags = 0;
 236                         if (!(strcmp( method_type, "search" )) || !(strcmp( method_type, "id_list" ))) {
 237                                 flags = flags | OSRF_METHOD_STREAMING;
 238                         }
 239
 240                         osrfAppRegisterExtendedMethod(
 241                                 MODULENAME,
 242                                 method,
 243                                 "dispatchCRUDMethod",
 244                                 "",
 245                                 1,
 246                                 flags,
 247                                 (void*)method_meta
 248                         );
 249
 250                         free(method);
 251                 }
 252         }
 253
 254         osrfStringArrayFree( global_methods );
 255         return 0;
 256 }
 257
 258 static char* getSourceDefinition( osrfHash* class ) {
 259
 260         char* tabledef = osrfHashGet(class, "tablename");
 261
 262         if (!tabledef) {
 263                 growing_buffer* tablebuf = buffer_init(128);
 264                 tabledef = osrfHashGet(class, "source_definition");
 265                 if( !tabledef )
 266                         tabledef = "(null)";
 267                 buffer_fadd( tablebuf, "(%s)", tabledef );
 268                 tabledef = buffer_release(tablebuf);
 269         } else {
 270                 tabledef = strdup(tabledef);
 271         }
 272
 273         return tabledef;
 274 }
 275
 276 /**
 277  * Connects to the database
 278  */
 279 int osrfAppChildInit() {
 280
 281         osrfLogDebug(OSRF_LOG_MARK, "Attempting to initialize libdbi...");
 282         dbi_initialize(NULL);
 283         osrfLogDebug(OSRF_LOG_MARK, "... libdbi initialized.");
 284
 285         char* driver    = osrf_settings_host_value("/apps/%s/app_settings/driver", MODULENAME);
 286         char* user      = osrf_settings_host_value("/apps/%s/app_settings/database/user", MODULENAME);
 287         char* host      = osrf_settings_host_value("/apps/%s/app_settings/database/host", MODULENAME);
 288         char* port      = osrf_settings_host_value("/apps/%s/app_settings/database/port", MODULENAME);
 289         char* db        = osrf_settings_host_value("/apps/%s/app_settings/database/db", MODULENAME);
 290         char* pw        = osrf_settings_host_value("/apps/%s/app_settings/database/pw", MODULENAME);
 291         char* md        = osrf_settings_host_value("/apps/%s/app_settings/max_query_recursion", MODULENAME);
 292
 293         osrfLogDebug(OSRF_LOG_MARK, "Attempting to load the database driver [%s]...", driver);
 294         writehandle = dbi_conn_new(driver);
 295
 296         if(!writehandle) {
 297                 osrfLogError(OSRF_LOG_MARK, "Error loading database driver [%s]", driver);
 298                 return -1;
 299         }
 300         osrfLogDebug(OSRF_LOG_MARK, "Database driver [%s] seems OK", driver);
 301
 302         osrfLogInfo(OSRF_LOG_MARK, "%s connecting to database.  host=%s, "
 303                 "port=%s, user=%s, pw=%s, db=%s", MODULENAME, host, port, user, pw, db );
 304
 305         if(host) dbi_conn_set_option(writehandle, "host", host );
 306         if(port) dbi_conn_set_option_numeric( writehandle, "port", atoi(port) );
 307         if(user) dbi_conn_set_option(writehandle, "username", user);
 308         if(pw) dbi_conn_set_option(writehandle, "password", pw );
 309         if(db) dbi_conn_set_option(writehandle, "dbname", db );
 310
 311         if(md) max_flesh_depth = atoi(md);
 312         if(max_flesh_depth < 0) max_flesh_depth = 1;
 313         if(max_flesh_depth > 1000) max_flesh_depth = 1000;
 314
 315         free(user);
 316         free(host);
 317         free(port);
 318         free(db);
 319         free(pw);
 320
 321         const char* err;
 322         if (dbi_conn_connect(writehandle) < 0) {
 323                 sleep(1);
 324                 if (dbi_conn_connect(writehandle) < 0) {
 325                         dbi_conn_error(writehandle, &err);
 326                         osrfLogError( OSRF_LOG_MARK, "Error connecting to database: %s", err);
 327                         return -1;
 328                 }
 329         }
 330
 331         osrfLogInfo(OSRF_LOG_MARK, "%s successfully connected to the database", MODULENAME);
 332
 333         int attr;
 334         unsigned short type;
 335         int i = 0;
 336         char* classname;
 337         osrfStringArray* classes = osrfHashKeys( oilsIDL() );
 338
 339         while ( (classname = osrfStringArrayGetString(classes, i++)) ) {
 340                 osrfHash* class = osrfHashGet( oilsIDL(), classname );
 341                 osrfHash* fields = osrfHashGet( class, "fields" );
 342
 343                 char* virt = osrfHashGet(class, "virtual");
 344                 if (virt && !strcmp( virt, "true")) {
 345                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 346                         continue;
 347                 }
 348
 349                 char* tabledef = getSourceDefinition(class);
 350
 351                 growing_buffer* sql_buf = buffer_init(32);
 352                 buffer_fadd( sql_buf, "SELECT * FROM %s AS x WHERE 1=0;", tabledef );
 353
 354                 free(tabledef);
 355
 356                 char* sql = buffer_release(sql_buf);
 357                 osrfLogDebug(OSRF_LOG_MARK, "%s Investigatory SQL = %s", MODULENAME, sql);
 358
 359                 dbi_result result = dbi_conn_query(writehandle, sql);
 360                 free(sql);
 361
 362                 if (result) {
 363
 364                         int columnIndex = 1;
 365                         const char* columnName;
 366                         osrfHash* _f;
 367                         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
 368
 369                                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
 370
 371                                 /* fetch the fieldmapper index */
 372                                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
 373
 374                                         osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", (char*)columnName);
 375
 376                                         /* determine the field type and storage attributes */
 377                                         type = dbi_result_get_field_type(result, columnName);
 378                                         attr = dbi_result_get_field_attribs(result, columnName);
 379
 380                                         switch( type ) {
 381
 382                                                 case DBI_TYPE_INTEGER :
 383
 384                                                         if ( !osrfHashGet(_f, "primitive") )
 385                                                                 osrfHashSet(_f,"number", "primitive");
 386
 387                                                         if( attr & DBI_INTEGER_SIZE8 )
 388                                                                 osrfHashSet(_f,"INT8", "datatype");
 389                                                         else
 390                                                                 osrfHashSet(_f,"INT", "datatype");
 391                                                         break;
 392
 393                                                 case DBI_TYPE_DECIMAL :
 394                                                         if ( !osrfHashGet(_f, "primitive") )
 395                                                                 osrfHashSet(_f,"number", "primitive");
 396
 397                                                         osrfHashSet(_f,"NUMERIC", "datatype");
 398                                                         break;
 399
 400                                                 case DBI_TYPE_STRING :
 401                                                         if ( !osrfHashGet(_f, "primitive") )
 402                                                                 osrfHashSet(_f,"string", "primitive");
 403                                                         osrfHashSet(_f,"TEXT", "datatype");
 404                                                         break;
 405
 406                                                 case DBI_TYPE_DATETIME :
 407                                                         if ( !osrfHashGet(_f, "primitive") )
 408                                                                 osrfHashSet(_f,"string", "primitive");
 409
 410                                                         osrfHashSet(_f,"TIMESTAMP", "datatype");
 411                                                         break;
 412
 413                                                 case DBI_TYPE_BINARY :
 414                                                         if ( !osrfHashGet(_f, "primitive") )
 415                                                                 osrfHashSet(_f,"string", "primitive");
 416
 417                                                         osrfHashSet(_f,"BYTEA", "datatype");
 418                                         }
 419
 420                                         osrfLogDebug(
 421                                                 OSRF_LOG_MARK,
 422                                                 "Setting [%s] to primitive [%s] and datatype [%s]...",
 423                                                 (char*)columnName,
 424                                                 osrfHashGet(_f, "primitive"),
 425                                                 osrfHashGet(_f, "datatype")
 426                                         );
 427                                 }
 428                         }
 429                         dbi_result_free(result);
 430                 } else {
 431                         osrfLogDebug(OSRF_LOG_MARK, "No data found for class [%s]...", (char*)classname);
 432                 }
 433         }
 434
 435         osrfStringArrayFree(classes);
 436
 437         return 0;
 438 }
 439
 440 void userDataFree( void* blob ) {
 441         osrfHashFree( (osrfHash*)blob );
 442         return;
 443 }
 444
 445 static void sessionDataFree( char* key, void* item ) {
 446         if (!(strcmp(key,"xact_id"))) {
 447                 if (writehandle)
 448                         dbi_conn_query(writehandle, "ROLLBACK;");
 449                 free(item);
 450         }
 451
 452         return;
 453 }
 454
 455 int beginTransaction ( osrfMethodContext* ctx ) {
 456         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 457
 458         dbi_result result = dbi_conn_query(writehandle, "START TRANSACTION;");
 459         if (!result) {
 460                 osrfLogError(OSRF_LOG_MARK, "%s: Error starting transaction", MODULENAME );
 461                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error starting transaction" );
 462                 return -1;
 463         } else {
 464                 jsonObject* ret = jsonNewObject(ctx->session->session_id);
 465                 osrfAppRespondComplete( ctx, ret );
 466                 jsonObjectFree(ret);
 467
 468                 if (!ctx->session->userData) {
 469                         ctx->session->userData = osrfNewHash();
 470                         osrfHashSetCallback((osrfHash*)ctx->session->userData, &sessionDataFree);
 471                 }
 472
 473                 osrfHashSet( (osrfHash*)ctx->session->userData, strdup( ctx->session->session_id ), "xact_id" );
 474                 ctx->session->userDataFree = &userDataFree;
 475
 476         }
 477         return 0;
 478 }
 479
 480 int setSavepoint ( osrfMethodContext* ctx ) {
 481         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 482
 483         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 484                 osrfAppSessionStatus(
 485                         ctx->session,
 486                         OSRF_STATUS_INTERNALSERVERERROR,
 487                         "osrfMethodException",
 488                         ctx->request,
 489                         "No active transaction -- required for savepoints"
 490                 );
 491                 return -1;
 492         }
 493
 494         char* spName = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, 0));
 495
 496         dbi_result result = dbi_conn_queryf(writehandle, "SAVEPOINT \"%s\";", spName);
 497         if (!result) {
 498                 osrfLogError(
 499                         OSRF_LOG_MARK,
 500                         "%s: Error creating savepoint %s in transaction %s",
 501                         MODULENAME,
 502                         spName,
 503                         osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 504                 );
 505                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error creating savepoint" );
 506                 free(spName);
 507                 return -1;
 508         } else {
 509                 jsonObject* ret = jsonNewObject(spName);
 510                 osrfAppRespondComplete( ctx, ret );
 511                 jsonObjectFree(ret);
 512         }
 513         free(spName);
 514         return 0;
 515 }
 516
 517 int releaseSavepoint ( osrfMethodContext* ctx ) {
 518         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 519
 520         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 521                 osrfAppSessionStatus(
 522                         ctx->session,
 523                         OSRF_STATUS_INTERNALSERVERERROR,
 524                         "osrfMethodException",
 525                         ctx->request,
 526                         "No active transaction -- required for savepoints"
 527                 );
 528                 return -1;
 529         }
 530
 531         char* spName = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, 0));
 532
 533         dbi_result result = dbi_conn_queryf(writehandle, "RELEASE SAVEPOINT \"%s\";", spName);
 534         if (!result) {
 535                 osrfLogError(
 536                         OSRF_LOG_MARK,
 537                         "%s: Error releasing savepoint %s in transaction %s",
 538                         MODULENAME,
 539                         spName,
 540                         osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 541                 );
 542                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error releasing savepoint" );
 543                 free(spName);
 544                 return -1;
 545         } else {
 546                 jsonObject* ret = jsonNewObject(spName);
 547                 osrfAppRespondComplete( ctx, ret );
 548                 jsonObjectFree(ret);
 549         }
 550         free(spName);
 551         return 0;
 552 }
 553
 554 int rollbackSavepoint ( osrfMethodContext* ctx ) {
 555         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 556
 557         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 558                 osrfAppSessionStatus(
 559                         ctx->session,
 560                         OSRF_STATUS_INTERNALSERVERERROR,
 561                         "osrfMethodException",
 562                         ctx->request,
 563                         "No active transaction -- required for savepoints"
 564                 );
 565                 return -1;
 566         }
 567
 568         char* spName = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, 0));
 569
 570         dbi_result result = dbi_conn_queryf(writehandle, "ROLLBACK TO SAVEPOINT \"%s\";", spName);
 571         if (!result) {
 572                 osrfLogError(
 573                         OSRF_LOG_MARK,
 574                         "%s: Error rolling back savepoint %s in transaction %s",
 575                         MODULENAME,
 576                         spName,
 577                         osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 578                 );
 579                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error rolling back savepoint" );
 580                 free(spName);
 581                 return -1;
 582         } else {
 583                 jsonObject* ret = jsonNewObject(spName);
 584                 osrfAppRespondComplete( ctx, ret );
 585                 jsonObjectFree(ret);
 586         }
 587         free(spName);
 588         return 0;
 589 }
 590
 591 int commitTransaction ( osrfMethodContext* ctx ) {
 592         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 593
 594         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 595                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "No active transaction to commit" );
 596                 return -1;
 597         }
 598
 599         dbi_result result = dbi_conn_query(writehandle, "COMMIT;");
 600         if (!result) {
 601                 osrfLogError(OSRF_LOG_MARK, "%s: Error committing transaction", MODULENAME );
 602                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error committing transaction" );
 603                 return -1;
 604         } else {
 605                 osrfHashRemove(ctx->session->userData, "xact_id");
 606                 jsonObject* ret = jsonNewObject(ctx->session->session_id);
 607                 osrfAppRespondComplete( ctx, ret );
 608                 jsonObjectFree(ret);
 609         }
 610         return 0;
 611 }
 612
 613 int rollbackTransaction ( osrfMethodContext* ctx ) {
 614         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 615
 616         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 617                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "No active transaction to roll back" );
 618                 return -1;
 619         }
 620
 621         dbi_result result = dbi_conn_query(writehandle, "ROLLBACK;");
 622         if (!result) {
 623                 osrfLogError(OSRF_LOG_MARK, "%s: Error rolling back transaction", MODULENAME );
 624                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error rolling back transaction" );
 625                 return -1;
 626         } else {
 627                 osrfHashRemove(ctx->session->userData, "xact_id");
 628                 jsonObject* ret = jsonNewObject(ctx->session->session_id);
 629                 osrfAppRespondComplete( ctx, ret );
 630                 jsonObjectFree(ret);
 631         }
 632         return 0;
 633 }
 634
 635 int dispatchCRUDMethod ( osrfMethodContext* ctx ) {
 636         OSRF_METHOD_VERIFY_CONTEXT(ctx);
 637
 638         osrfHash* meta = (osrfHash*) ctx->method->userData;
 639         osrfHash* class_obj = osrfHashGet( meta, "class" );
 640
 641         int err = 0;
 642
 643         const char* methodtype = osrfHashGet(meta, "methodtype");
 644         jsonObject * obj = NULL;
 645
 646         if (!strcmp(methodtype, "create")) {
 647                 obj = doCreate(ctx, &err);
 648                 osrfAppRespondComplete( ctx, obj );
 649         }
 650         else if (!strcmp(methodtype, "retrieve")) {
 651                 obj = doRetrieve(ctx, &err);
 652                 osrfAppRespondComplete( ctx, obj );
 653         }
 654         else if (!strcmp(methodtype, "update")) {
 655                 obj = doUpdate(ctx, &err);
 656                 osrfAppRespondComplete( ctx, obj );
 657         }
 658         else if (!strcmp(methodtype, "delete")) {
 659                 obj = doDelete(ctx, &err);
 660                 osrfAppRespondComplete( ctx, obj );
 661         }
 662         else if (!strcmp(methodtype, "search")) {
 663
 664                 jsonObject* _p = jsonObjectClone( ctx->params );
 665 #ifdef PCRUD
 666         jsonObjectRemoveIndex(_p, 0);
 667 #endif
 668
 669                 obj = doFieldmapperSearch(ctx, class_obj, _p, &err);
 670                 if(err) return err;
 671
 672                 jsonObject* cur;
 673                 jsonIterator* itr = jsonNewIterator( obj );
 674                 while ((cur = jsonIteratorNext( itr ))) {
 675 #ifdef PCRUD
 676                 if(!verifyObjectPCRUD(ctx, cur)) continue;
 677 #endif
 678                         osrfAppRespond( ctx, cur );
 679                 }
 680                 jsonIteratorFree(itr);
 681                 osrfAppRespondComplete( ctx, NULL );
 682
 683         } else if (!strcmp(methodtype, "id_list")) {
 684
 685                 int _opt_pos = 1;
 686 #ifdef PCRUD
 687                 _opt_pos = 2;
 688 #endif
 689
 690                 jsonObject* _p = jsonObjectClone( ctx->params );
 691 #ifdef PCRUD
 692         jsonObjectRemoveIndex(_p, 0);
 693 #endif
 694
 695                 if (jsonObjectGetIndex( _p, _opt_pos )) {
 696                         jsonObjectRemoveKey( jsonObjectGetIndex( _p, _opt_pos ), "flesh" );
 697                         jsonObjectRemoveKey( jsonObjectGetIndex( _p, _opt_pos ), "flesh_columns" );
 698                 } else {
 699                         jsonObjectSetIndex( _p, _opt_pos, jsonNewObjectType(JSON_HASH) );
 700                 }
 701
 702                 growing_buffer* sel_list = buffer_init(64);
 703                 buffer_fadd(sel_list, "{ \"%s\":[\"%s\"] }", osrfHashGet( class_obj, "classname" ), osrfHashGet( class_obj, "primarykey" ));
 704                 char* _s = buffer_release(sel_list);
 705
 706                 jsonObjectSetKey( jsonObjectGetIndex( _p, _opt_pos ), "select", jsonParseString(_s) );
 707                 osrfLogDebug(OSRF_LOG_MARK, "%s: Select qualifer set to [%s]", MODULENAME, _s);
 708                 free(_s);
 709
 710                 obj = doFieldmapperSearch(ctx, class_obj, _p, &err);
 711                 jsonObjectFree(_p);
 712                 if(err) return err;
 713
 714                 jsonObject* cur;
 715                 jsonIterator* itr = jsonNewIterator( obj );
 716                 while ((cur = jsonIteratorNext( itr ))) {
 717 #ifdef PCRUD
 718                 if(!verifyObjectPCRUD(ctx, cur)) continue;
 719 #endif
 720                         osrfAppRespond(
 721                                 ctx,
 722                                 oilsFMGetObject( cur, osrfHashGet( class_obj, "primarykey" ) )
 723                         );
 724                 }
 725                 jsonIteratorFree(itr);
 726                 osrfAppRespondComplete( ctx, NULL );
 727
 728         } else {
 729                 osrfAppRespondComplete( ctx, obj );
 730         }
 731
 732         jsonObjectFree(obj);
 733
 734         return err;
 735 }
 736
 737 static int verifyObjectClass ( osrfMethodContext* ctx, const jsonObject* param ) {
 738
 739     int ret = 1;
 740         osrfHash* meta = (osrfHash*) ctx->method->userData;
 741         osrfHash* class = osrfHashGet( meta, "class" );
 742
 743         if (!param->classname || (strcmp( osrfHashGet(class, "classname"), param->classname ))) {
 744
 745                 growing_buffer* msg = buffer_init(128);
 746                 buffer_fadd(
 747                         msg,
 748                         "%s: %s method for type %s was passed a %s",
 749                         MODULENAME,
 750                         osrfHashGet(meta, "methodtype"),
 751                         osrfHashGet(class, "classname"),
 752                         param->classname
 753                 );
 754
 755                 char* m = buffer_release(msg);
 756                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", ctx->request, m );
 757
 758                 free(m);
 759
 760                 return 0;
 761         }
 762
 763 #ifdef PCRUD
 764     ret = verifyObjectPCRUD( ctx, param );
 765 #endif
 766
 767         return ret;
 768 }
 769
 770 #ifdef PCRUD
 771 static int verifyObjectPCRUD (  osrfMethodContext* ctx, const jsonObject* obj ) {
 772
 773         dbhandle = writehandle;
 774
 775         osrfHash* meta = (osrfHash*) ctx->method->userData;
 776         osrfHash* class = osrfHashGet( meta, "class" );
 777     char* method_type = strdup( osrfHashGet(meta, "methodtype") );
 778
 779     if ( ( *method_type == 's' || *method_type == 'i' ) ) {
 780         free(method_type);
 781         method_type = strdup("retrieve");
 782     }
 783
 784     osrfHash* pcrud = osrfHashGet( osrfHashGet(class, "permacrud"), method_type );
 785         free(method_type);
 786
 787     if (!pcrud) {
 788         // No permacrud for this method type on this class
 789
 790                 growing_buffer* msg = buffer_init(128);
 791                 buffer_fadd(
 792                         msg,
 793                         "%s: %s on class %s has no permacrud IDL entry",
 794                         MODULENAME,
 795                         osrfHashGet(meta, "methodtype"),
 796                         osrfHashGet(class, "classname")
 797                 );
 798
 799                 char* m = buffer_release(msg);
 800                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", ctx->request, m );
 801
 802                 free(m);
 803
 804                 return 0;
 805     }
 806
 807     //XXX turn this into a user id
 808         char* auth = jsonObjectToSimpleString( jsonObjectGetIndex( ctx->params, 0 ) );
 809     jsonObject* user = oilsUtilsQuickReq("open-ils.auth","open-ils.auth.session.retrieve", jsonNewObject(auth));
 810
 811     if (!user) {
 812         free(auth);
 813         return 0;
 814     }
 815
 816     int userid = atoi( oilsFMGetString( user, "id" ) );
 817
 818     jsonObjectFree(user);
 819     free(auth);
 820
 821     osrfStringArray* permission = osrfHashGet(pcrud, "permission");
 822     char* global_required = osrfHashGet(pcrud, "global_required");
 823     osrfStringArray* local_context = osrfHashGet(pcrud, "local_context");
 824     osrfHash* foreign_context = osrfHashGet(pcrud, "foreign_context");
 825
 826     osrfStringArray* context_org_array = osrfNewStringArray(1);
 827
 828     char* pkey_value = NULL;
 829     int err = 0;
 830     if (global_required && strcmp( "true", global_required )) {
 831         // check for perm at top of org tree
 832         jsonObject* _tmp_params = jsonParseString("{\"parent_ou\":null}");
 833                 jsonObject* _list = doFieldmapperSearch(ctx, oilsIDLFindPath("/aou"), _tmp_params, &err);
 834
 835         jsonObject* _tree_top = jsonObjectGetIndex(_list, 0);
 836
 837         if (!_tree_top) {
 838             jsonObjectFree(_tmp_params);
 839             jsonObjectFree(_list);
 840             return 0;
 841         }
 842
 843         osrfStringArrayAdd( context_org_array, oilsFMGetString( _tree_top, "id" ) );
 844
 845         jsonObjectFree(_tmp_params);
 846         jsonObjectFree(_list);
 847
 848     } else {
 849
 850         jsonObject *param = NULL;
 851         if (obj) param = jsonObjectClone(obj);
 852             if (!param) param = jsonObjectClone(jsonObjectGetIndex( ctx->params, 1 ));
 853
 854        // XXX if the object has a non-null pkey, check for object-specific perm,
 855        // else context org(s) for group perm check
 856             char* pkey = osrfHashGet(class, "primarykey");
 857
 858         if (param->classname) {
 859             pkey_value = oilsFMGetString( param, pkey );
 860
 861         } else {
 862             pkey_value = jsonObjectToSimpleString( param );
 863
 864             jsonObject* _tmp_params = jsonParseStringFmt("{\"%s\":\"%s\"}", pkey, pkey_value);
 865                 jsonObject* _list = doFieldmapperSearch(
 866                 ctx,
 867                 class,
 868                 _tmp_params,
 869                 &err
 870             );
 871
 872             jsonObjectFree(param);
 873             param = jsonObjectClone(jsonObjectGetIndex(_list, 0));
 874
 875             if (!param) {
 876                 jsonObjectFree(_tmp_params);
 877                 jsonObjectFree(_list);
 878                 return 0;
 879             }
 880
 881             jsonObjectFree(_tmp_params);
 882             jsonObjectFree(_list);
 883
 884         }
 885
 886         if (local_context->size > 0) {
 887             int i = 0;
 888             char* lcontext = NULL;
 889             while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
 890                 osrfStringArrayAdd( context_org_array, oilsFMGetString( param, lcontext ) );
 891             }
 892         }
 893
 894         osrfStringArray* class_list;
 895
 896         if (foreign_context) {
 897             class_list = osrfHashKeys( foreign_context );
 898
 899             if (class_list->size > 0) {
 900
 901                 int i = 0;
 902                 char* class_name = NULL;
 903                 while ( (class_name = osrfStringArrayGetString(class_list, i++)) ) {
 904                     osrfHash* fcontext = osrfHashGet(foreign_context, class_name);
 905
 906                     jsonObject* _tmp_params = jsonParseStringFmt(
 907                         "{\"%s\":\"%s\"}",
 908                         osrfHashGet(fcontext, "field"),
 909                         oilsFMGetString(param, osrfHashGet(fcontext, "fkey"))
 910                     );
 911
 912                         jsonObject* _list = doFieldmapperSearch(
 913                         ctx,
 914                         class,
 915                         _tmp_params,
 916                         &err
 917                     );
 918
 919
 920                     jsonObject* _fparam = jsonObjectGetIndex(_list, 0);
 921
 922                     if (!_fparam) {
 923                         jsonObjectFree(_tmp_params);
 924                         jsonObjectFree(_list);
 925                         return 0;
 926                     }
 927
 928                     jsonObjectFree(_tmp_params);
 929                     jsonObjectFree(_list);
 930
 931                     char* foreign_field = NULL;
 932                     while ( (foreign_field = osrfStringArrayGetString(osrfHashGet(fcontext,"context"), i++)) ) {
 933                         osrfStringArrayAdd( context_org_array, oilsFMGetString( _fparam, foreign_field ) );
 934                     }
 935
 936                     jsonObjectFree(_fparam);
 937                 }
 938
 939                 osrfStringArrayFree(class_list);
 940             }
 941         }
 942
 943         jsonObjectFree(param);
 944     }
 945
 946     char* context_org = NULL;
 947     char* perm = NULL;
 948     int OK = 0;
 949
 950     if (permission->size == 0) OK = 1;
 951
 952     int i = 0;
 953     while ( (perm = osrfStringArrayGetString(permission, i++)) ) {
 954         int j = 0;
 955         while ( (context_org = osrfStringArrayGetString(context_org_array, j++)) ) {
 956             dbi_result result;
 957
 958             if (pkey_value) {
 959                 result = dbi_conn_queryf(
 960                     writehandle,
 961                     "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
 962                     userid,
 963                     perm,
 964                     osrfHashGet(class, "classname"),
 965                     pkey_value,
 966                     atoi(context_org)
 967                 );
 968
 969                 if (result) {
 970                     jsonObject* return_val = oilsMakeJSONFromResult( result );
 971                     char* has_perm = jsonObjectToSimpleString( jsonObjectGetKeyConst(return_val, "has_perm") );
 972                     if ( *has_perm == 't' ) OK = 1;
 973                     free(has_perm);
 974                     jsonObjectFree(return_val);
 975                     dbi_result_free(result);
 976                     break;
 977                 }
 978             }
 979
 980             result = dbi_conn_queryf(
 981                 writehandle,
 982                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
 983                 userid,
 984                 perm,
 985                 atoi(context_org)
 986             );
 987
 988             if (result) {
 989                 jsonObject* return_val = oilsMakeJSONFromResult( result );
 990                 char* has_perm = jsonObjectToSimpleString( jsonObjectGetKeyConst(return_val, "has_perm") );
 991                 if ( *has_perm == 't' ) OK = 1;
 992                 free(has_perm);
 993                 jsonObjectFree(return_val);
 994                 dbi_result_free(result);
 995                 break;
 996             }
 997
 998         }
 999         if (OK) break;
1000     }
1001
1002     if (pkey_value) free(pkey_value);
1003     osrfStringArrayFree(context_org_array);
1004
1005     return OK;
1006 }
1007 #endif
1008
1009
1010 static jsonObject* doCreate(osrfMethodContext* ctx, int* err ) {
1011
1012         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1013 #ifdef PCRUD
1014         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
1015         jsonObject* options = jsonObjectGetIndex( ctx->params, 2 );
1016 #else
1017         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
1018         jsonObject* options = jsonObjectGetIndex( ctx->params, 1 );
1019 #endif
1020
1021         if (!verifyObjectClass(ctx, target)) {
1022                 *err = -1;
1023                 return jsonNULL;
1024         }
1025
1026         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1027
1028         if (!ctx->session || !ctx->session->userData || !osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
1029                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1030
1031                 osrfAppSessionStatus(
1032                         ctx->session,
1033                         OSRF_STATUS_BADREQUEST,
1034                         "osrfMethodException",
1035                         ctx->request,
1036                         "No active transaction -- required for CREATE"
1037                 );
1038                 *err = -1;
1039                 return jsonNULL;
1040         }
1041
1042         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
1043                 osrfAppSessionStatus(
1044                         ctx->session,
1045                         OSRF_STATUS_BADREQUEST,
1046                         "osrfMethodException",
1047                         ctx->request,
1048                         "Cannot INSERT readonly class"
1049                 );
1050                 *err = -1;
1051                 return jsonNULL;
1052         }
1053
1054
1055         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
1056
1057         // Set the last_xact_id
1058         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
1059         if (index > -1) {
1060                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
1061                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
1062         }
1063
1064         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
1065
1066         dbhandle = writehandle;
1067
1068         osrfHash* fields = osrfHashGet(meta, "fields");
1069         char* pkey = osrfHashGet(meta, "primarykey");
1070         char* seq = osrfHashGet(meta, "sequence");
1071
1072         growing_buffer* table_buf = buffer_init(128);
1073         growing_buffer* col_buf = buffer_init(128);
1074         growing_buffer* val_buf = buffer_init(128);
1075
1076         buffer_fadd(table_buf,"INSERT INTO %s", osrfHashGet(meta, "tablename"));
1077         buffer_add(col_buf,"(");
1078         buffer_add(val_buf,"VALUES (");
1079
1080
1081         int i = 0;
1082         int first = 1;
1083         char* field_name;
1084         osrfStringArray* field_list = osrfHashKeys( fields );
1085         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
1086
1087                 osrfHash* field = osrfHashGet( fields, field_name );
1088
1089                 if(!( strcmp( osrfHashGet(osrfHashGet(fields,field_name), "virtual"), "true" ) )) continue;
1090
1091                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
1092
1093                 char* value;
1094                 if (field_object && field_object->classname) {
1095                         value = oilsFMGetString(
1096                                 field_object,
1097                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
1098                         );
1099                 } else {
1100                         value = jsonObjectToSimpleString( field_object );
1101                 }
1102
1103
1104                 if (first) {
1105                         first = 0;
1106                 } else {
1107                         buffer_add(col_buf, ",");
1108                         buffer_add(val_buf, ",");
1109                 }
1110
1111                 buffer_add(col_buf, field_name);
1112
1113                 if (!field_object || field_object->type == JSON_NULL) {
1114                         buffer_add( val_buf, "DEFAULT" );
1115
1116                 } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1117                         if ( !strcmp(osrfHashGet(field, "datatype"), "INT8") ) {
1118                                 buffer_fadd( val_buf, "%lld", atoll(value) );
1119
1120                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "INT") ) {
1121                                 buffer_fadd( val_buf, "%d", atoi(value) );
1122
1123                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
1124                                 buffer_fadd( val_buf, "%f", atof(value) );
1125                         }
1126                 } else {
1127                         if ( dbi_conn_quote_string(writehandle, &value) ) {
1128                                 buffer_fadd( val_buf, "%s", value );
1129
1130                         } else {
1131                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
1132                                 osrfAppSessionStatus(
1133                                         ctx->session,
1134                                         OSRF_STATUS_INTERNALSERVERERROR,
1135                                         "osrfMethodException",
1136                                         ctx->request,
1137                                         "Error quoting string -- please see the error log for more details"
1138                                 );
1139                                 free(value);
1140                                 buffer_free(table_buf);
1141                                 buffer_free(col_buf);
1142                                 buffer_free(val_buf);
1143                                 *err = -1;
1144                                 return jsonNULL;
1145                         }
1146                 }
1147
1148                 free(value);
1149
1150         }
1151
1152
1153         buffer_add(col_buf,")");
1154         buffer_add(val_buf,")");
1155
1156         char* table_str = buffer_release(table_buf);
1157         char* col_str   = buffer_release(col_buf);
1158         char* val_str   = buffer_release(val_buf);
1159         growing_buffer* sql = buffer_init(128);
1160         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
1161         free(table_str);
1162         free(col_str);
1163         free(val_str);
1164
1165         char* query = buffer_release(sql);
1166
1167         osrfLogDebug(OSRF_LOG_MARK, "%s: Insert SQL [%s]", MODULENAME, query);
1168
1169
1170         dbi_result result = dbi_conn_query(writehandle, query);
1171
1172         jsonObject* obj = NULL;
1173
1174         if (!result) {
1175                 obj = jsonNewObject(NULL);
1176                 osrfLogError(
1177                         OSRF_LOG_MARK,
1178                         "%s ERROR inserting %s object using query [%s]",
1179                         MODULENAME,
1180                         osrfHashGet(meta, "fieldmapper"),
1181                         query
1182                 );
1183                 osrfAppSessionStatus(
1184                         ctx->session,
1185                         OSRF_STATUS_INTERNALSERVERERROR,
1186                         "osrfMethodException",
1187                         ctx->request,
1188                         "INSERT error -- please see the error log for more details"
1189                 );
1190                 *err = -1;
1191         } else {
1192
1193                 char* id = oilsFMGetString(target, pkey);
1194                 if (!id) {
1195                         unsigned long long new_id = dbi_conn_sequence_last(writehandle, seq);
1196                         growing_buffer* _id = buffer_init(10);
1197                         buffer_fadd(_id, "%lld", new_id);
1198                         id = buffer_release(_id);
1199                 }
1200
1201                 // Find quietness specification, if present
1202                 char* quiet_str = NULL;
1203                 if ( options ) {
1204                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
1205                         if( quiet_obj )
1206                                 quiet_str = jsonObjectToSimpleString( quiet_obj );
1207                 }
1208
1209                 if( quiet_str && !strcmp( quiet_str, "true" )) {  // if quietness is specified
1210                         obj = jsonNewObject(id);
1211                 }
1212                 else {
1213
1214                         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1215                         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1216
1217                         jsonObjectSetKey(
1218                                 jsonObjectGetIndex(fake_params, 0),
1219                                 pkey,
1220                                 jsonNewObject(id)
1221                         );
1222
1223                         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1224
1225                         if(*err) {
1226                                 jsonObjectFree( fake_params );
1227                                 obj = jsonNULL;
1228                         } else {
1229                                 obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1230                         }
1231
1232                         jsonObjectFree( list );
1233                         jsonObjectFree( fake_params );
1234                 }
1235
1236                 if(quiet_str) free(quiet_str);
1237                 free(id);
1238         }
1239
1240         free(query);
1241
1242         return obj;
1243
1244 }
1245
1246
1247 static jsonObject* doRetrieve(osrfMethodContext* ctx, int* err ) {
1248
1249     int id_pos = 0;
1250     int order_pos = 1;
1251
1252 #ifdef PCRUD
1253     id_pos = 1;
1254     order_pos = 2;
1255 #endif
1256
1257         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1258
1259         jsonObject* obj;
1260
1261         char* id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, id_pos));
1262         jsonObject* order_hash = jsonObjectGetIndex(ctx->params, order_pos);
1263
1264         osrfLogDebug(
1265                 OSRF_LOG_MARK,
1266                 "%s retrieving %s object with primary key value of %s",
1267                 MODULENAME,
1268                 osrfHashGet(meta, "fieldmapper"),
1269                 id
1270         );
1271
1272         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1273         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1274
1275         jsonObjectSetKey(
1276                 jsonObjectGetIndex(fake_params, 0),
1277                 osrfHashGet(meta, "primarykey"),
1278                 jsonParseString(id)
1279         );
1280
1281         free(id);
1282
1283         if (order_hash) jsonObjectPush(fake_params, jsonObjectClone(order_hash) );
1284
1285         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1286
1287         if(*err) {
1288                 jsonObjectFree( fake_params );
1289                 return jsonNULL;
1290         }
1291
1292         obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1293
1294         jsonObjectFree( list );
1295         jsonObjectFree( fake_params );
1296
1297 #ifdef PCRUD
1298         if(!verifyObjectPCRUD(ctx, obj)) {
1299         jsonObjectFree(obj);
1300                 return jsonNULL;
1301         }
1302 #endif
1303
1304         return obj;
1305 }
1306
1307 static char* jsonNumberToDBString ( osrfHash* field, const jsonObject* value ) {
1308         growing_buffer* val_buf = buffer_init(32);
1309
1310         if ( !strncmp(osrfHashGet(field, "datatype"), "INT", (size_t)3) ) {
1311                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
1312                 else {
1313                         char* val_str = jsonObjectToSimpleString(value);
1314                         buffer_fadd( val_buf, "%ld", atol(val_str) );
1315                         free(val_str);
1316                 }
1317
1318         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
1319                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%f",  jsonObjectGetNumber(value) );
1320                 else {
1321                         char* val_str = jsonObjectToSimpleString(value);
1322                         buffer_fadd( val_buf, "%f", atof(val_str) );
1323                         free(val_str);
1324                 }
1325         }
1326
1327         return buffer_release(val_buf);
1328 }
1329
1330 static char* searchINPredicate (const char* class, osrfHash* field,
1331                 const jsonObject* node, const char* op) {
1332         growing_buffer* sql_buf = buffer_init(32);
1333
1334         buffer_fadd(
1335                 sql_buf,
1336                 "\"%s\".%s ",
1337                 class,
1338                 osrfHashGet(field, "name")
1339         );
1340
1341         if (!op) {
1342                 buffer_add(sql_buf, "IN (");
1343         } else if (!(strcasecmp(op,"not in"))) {
1344                 buffer_add(sql_buf, "NOT IN (");
1345         } else {
1346                 buffer_add(sql_buf, "IN (");
1347         }
1348
1349         int in_item_index = 0;
1350         int in_item_first = 1;
1351         jsonObject* in_item;
1352         while ( (in_item = jsonObjectGetIndex(node, in_item_index++)) ) {
1353
1354                 if (in_item_first)
1355                         in_item_first = 0;
1356                 else
1357                         buffer_add(sql_buf, ", ");
1358
1359                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1360                         char* val = jsonNumberToDBString( field, in_item );
1361                         buffer_fadd( sql_buf, "%s", val );
1362                         free(val);
1363
1364                 } else {
1365                         char* key_string = jsonObjectToSimpleString(in_item);
1366                         if ( dbi_conn_quote_string(dbhandle, &key_string) ) {
1367                                 buffer_fadd( sql_buf, "%s", key_string );
1368                                 free(key_string);
1369                         } else {
1370                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, key_string);
1371                                 free(key_string);
1372                                 buffer_free(sql_buf);
1373                                 return NULL;
1374                         }
1375                 }
1376         }
1377
1378         buffer_add(
1379                 sql_buf,
1380                 ")"
1381         );
1382
1383         return buffer_release(sql_buf);
1384 }
1385
1386 static char* searchValueTransform( const jsonObject* array ) {
1387         growing_buffer* sql_buf = buffer_init(32);
1388
1389         char* val = NULL;
1390         int func_item_index = 0;
1391         int func_item_first = 2;
1392         jsonObject* func_item;
1393         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1394
1395                 val = jsonObjectToSimpleString(func_item);
1396
1397                 if (func_item_first == 2) {
1398                         buffer_fadd(sql_buf, "%s( ", val);
1399                         free(val);
1400                         func_item_first--;
1401                         continue;
1402                 }
1403
1404                 if (func_item_first)
1405                         func_item_first--;
1406                 else
1407                         buffer_add(sql_buf, ", ");
1408
1409                 if (func_item->type == JSON_NULL) {
1410                         buffer_add( sql_buf, "NULL" );
1411                 } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
1412                         buffer_fadd( sql_buf, "%s", val );
1413                 } else {
1414                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1415                         free(val);
1416                         buffer_free(sql_buf);
1417                         return NULL;
1418                 }
1419
1420                 free(val);
1421         }
1422
1423         buffer_add(
1424                 sql_buf,
1425                 " )"
1426         );
1427
1428         return buffer_release(sql_buf);
1429 }
1430
1431 static char* searchFunctionPredicate (const char* class, osrfHash* field,
1432                 const jsonObject* node, const char* node_key) {
1433         growing_buffer* sql_buf = buffer_init(32);
1434
1435         char* val = searchValueTransform(node);
1436
1437         buffer_fadd(
1438                 sql_buf,
1439                 "\"%s\".%s %s %s",
1440                 class,
1441                 osrfHashGet(field, "name"),
1442                 node_key,
1443                 val
1444         );
1445
1446         free(val);
1447
1448         return buffer_release(sql_buf);
1449 }
1450
1451 static char* searchFieldTransform (const char* class, osrfHash* field, const jsonObject* node) {
1452         growing_buffer* sql_buf = buffer_init(32);
1453
1454         char* field_transform = jsonObjectToSimpleString( jsonObjectGetKeyConst( node, "transform" ) );
1455         char* transform_subcolumn = jsonObjectToSimpleString( jsonObjectGetKeyConst( node, "result_field" ) );
1456
1457         if (field_transform) {
1458                 buffer_fadd( sql_buf, "%s(\"%s\".%s", field_transform, class, osrfHashGet(field, "name"));
1459             const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
1460
1461         if (array) {
1462                 int func_item_index = 0;
1463                 jsonObject* func_item;
1464                 while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1465
1466                         char* val = jsonObjectToSimpleString(func_item);
1467
1468                     if ( !val ) {
1469                             buffer_add( sql_buf, ",NULL" );
1470                     } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
1471                             buffer_fadd( sql_buf, ",%s", val );
1472                         } else {
1473                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1474                             free(field_transform);
1475                                         free(val);
1476                                 buffer_free(sql_buf);
1477                                 return NULL;
1478                 }
1479                                 free(val);
1480                         }
1481
1482         }
1483
1484         buffer_add(
1485                 sql_buf,
1486                 " )"
1487         );
1488
1489         } else {
1490                 buffer_fadd( sql_buf, "\"%s\".%s", class, osrfHashGet(field, "name"));
1491         }
1492
1493     if (transform_subcolumn) {
1494         char * tmp = buffer_release(sql_buf);
1495         sql_buf = buffer_init(32);
1496         buffer_fadd(
1497             sql_buf,
1498             "(%s).\"%s\"",
1499             tmp,
1500             transform_subcolumn
1501         );
1502         free(tmp);
1503     }
1504
1505         if (field_transform) free(field_transform);
1506         if (transform_subcolumn) free(transform_subcolumn);
1507
1508         return buffer_release(sql_buf);
1509 }
1510
1511 static char* searchFieldTransformPredicate (const char* class, osrfHash* field, jsonObject* node, const char* node_key) {
1512         char* field_transform = searchFieldTransform( class, field, node );
1513         char* value = NULL;
1514
1515         if (!jsonObjectGetKeyConst( node, "value" )) {
1516                 value = searchWHERE( node, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1517         } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_ARRAY) {
1518                 value = searchValueTransform(jsonObjectGetKeyConst( node, "value" ));
1519         } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_HASH) {
1520                 value = searchWHERE( jsonObjectGetKeyConst( node, "value" ), osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1521         } else if (jsonObjectGetKeyConst( node, "value" )->type != JSON_NULL) {
1522                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1523                         value = jsonNumberToDBString( field, jsonObjectGetKeyConst( node, "value" ) );
1524                 } else {
1525                         value = jsonObjectToSimpleString(jsonObjectGetKeyConst( node, "value" ));
1526                         if ( !dbi_conn_quote_string(dbhandle, &value) ) {
1527                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, value);
1528                                 free(value);
1529                                 free(field_transform);
1530                                 return NULL;
1531                         }
1532                 }
1533         }
1534
1535         growing_buffer* sql_buf = buffer_init(32);
1536
1537         buffer_fadd(
1538                 sql_buf,
1539                 "%s %s %s",
1540                 field_transform,
1541                 node_key,
1542                 value
1543         );
1544
1545         free(value);
1546         free(field_transform);
1547
1548         return buffer_release(sql_buf);
1549 }
1550
1551 static char* searchSimplePredicate (const char* orig_op, const char* class,
1552                 osrfHash* field, const jsonObject* node) {
1553
1554         char* val = NULL;
1555
1556         if (node->type != JSON_NULL) {
1557                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1558                         val = jsonNumberToDBString( field, node );
1559                 } else {
1560                         val = jsonObjectToSimpleString(node);
1561                 }
1562         }
1563
1564         char* pred = searchWriteSimplePredicate( class, field, osrfHashGet(field, "name"), orig_op, val );
1565
1566         if (val) free(val);
1567
1568         return pred;
1569 }
1570
1571 static char* searchWriteSimplePredicate ( const char* class, osrfHash* field,
1572         const char* left, const char* orig_op, const char* right ) {
1573
1574         char* val = NULL;
1575         char* op = NULL;
1576         if (right == NULL) {
1577                 val = strdup("NULL");
1578
1579                 if (strcmp( orig_op, "=" ))
1580                         op = strdup("IS NOT");
1581                 else
1582                         op = strdup("IS");
1583
1584         } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1585                 val = strdup(right);
1586                 op = strdup(orig_op);
1587
1588         } else {
1589                 val = strdup(right);
1590                 if ( !dbi_conn_quote_string(dbhandle, &val) ) {
1591                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1592                         free(val);
1593                         return NULL;
1594                 }
1595                 op = strdup(orig_op);
1596         }
1597
1598         growing_buffer* sql_buf = buffer_init(16);
1599         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class, left, op, val );
1600         free(val);
1601         free(op);
1602
1603         return buffer_release(sql_buf);
1604 }
1605
1606 static char* searchBETWEENPredicate (const char* class, osrfHash* field, jsonObject* node) {
1607
1608         char* x_string;
1609         char* y_string;
1610
1611         if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1612                 x_string = jsonNumberToDBString(field, jsonObjectGetIndex(node,0));
1613                 y_string = jsonNumberToDBString(field, jsonObjectGetIndex(node,1));
1614
1615         } else {
1616                 x_string = jsonObjectToSimpleString(jsonObjectGetIndex(node,0));
1617                 y_string = jsonObjectToSimpleString(jsonObjectGetIndex(node,1));
1618                 if ( !(dbi_conn_quote_string(dbhandle, &x_string) && dbi_conn_quote_string(dbhandle, &y_string)) ) {
1619                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]", MODULENAME, x_string, y_string);
1620                         free(x_string);
1621                         free(y_string);
1622                         return NULL;
1623                 }
1624         }
1625
1626         growing_buffer* sql_buf = buffer_init(32);
1627         buffer_fadd( sql_buf, "%s BETWEEN %s AND %s", osrfHashGet(field, "name"), x_string, y_string );
1628         free(x_string);
1629         free(y_string);
1630
1631         return buffer_release(sql_buf);
1632 }
1633
1634 static char* searchPredicate ( const char* class, osrfHash* field, jsonObject* node ) {
1635
1636         char* pred = NULL;
1637         if (node->type == JSON_ARRAY) { // equality IN search
1638                 pred = searchINPredicate( class, field, node, NULL );
1639         } else if (node->type == JSON_HASH) { // non-equality search
1640                 jsonObject* pred_node;
1641                 jsonIterator* pred_itr = jsonNewIterator( node );
1642                 while ( (pred_node = jsonIteratorNext( pred_itr )) ) {
1643                         if ( !(strcasecmp( pred_itr->key,"between" )) )
1644                                 pred = searchBETWEENPredicate( class, field, pred_node );
1645                         else if ( !(strcasecmp( pred_itr->key,"in" )) || !(strcasecmp( pred_itr->key,"not in" )) )
1646                                 pred = searchINPredicate( class, field, pred_node, pred_itr->key );
1647                         else if ( pred_node->type == JSON_ARRAY )
1648                                 pred = searchFunctionPredicate( class, field, pred_node, pred_itr->key );
1649                         else if ( pred_node->type == JSON_HASH )
1650                                 pred = searchFieldTransformPredicate( class, field, pred_node, pred_itr->key );
1651                         else
1652                                 pred = searchSimplePredicate( pred_itr->key, class, field, pred_node );
1653
1654                         break;
1655                 }
1656         jsonIteratorFree(pred_itr);
1657         } else if (node->type == JSON_NULL) { // IS NULL search
1658                 growing_buffer* _p = buffer_init(64);
1659                 buffer_fadd(
1660                         _p,
1661                         "\"%s\".%s IS NULL",
1662                         class,
1663                         osrfHashGet(field, "name")
1664                 );
1665                 pred = buffer_release(_p);
1666         } else { // equality search
1667                 pred = searchSimplePredicate( "=", class, field, node );
1668         }
1669
1670         return pred;
1671
1672 }
1673
1674
1675 /*
1676
1677 join : {
1678         acn : {
1679                 field : record,
1680                 fkey : id
1681                 type : left
1682                 filter_op : or
1683                 filter : { ... },
1684                 join : {
1685                         acp : {
1686                                 field : call_number,
1687                                 fkey : id,
1688                                 filter : { ... },
1689                         },
1690                 },
1691         },
1692         mrd : {
1693                 field : record,
1694                 type : inner
1695                 fkey : id,
1696                 filter : { ... },
1697         }
1698 }
1699
1700 */
1701
1702 static char* searchJOIN ( const jsonObject* join_hash, osrfHash* leftmeta ) {
1703
1704         const jsonObject* working_hash;
1705         jsonObject* freeable_hash = NULL;
1706
1707         if (join_hash->type == JSON_STRING) {
1708                 // create a wrapper around a copy of the original
1709                 char* _tmp = jsonObjectToSimpleString( join_hash );
1710                 freeable_hash = jsonNewObjectType(JSON_HASH);
1711                 jsonObjectSetKey(freeable_hash, _tmp, NULL);
1712                 free(_tmp);
1713                 working_hash = freeable_hash;
1714         }
1715         else
1716                 working_hash = join_hash;
1717
1718         growing_buffer* join_buf = buffer_init(128);
1719         char* leftclass = osrfHashGet(leftmeta, "classname");
1720
1721         jsonObject* snode = NULL;
1722         jsonIterator* search_itr = jsonNewIterator( working_hash );
1723         if(freeable_hash)
1724                 jsonObjectFree(freeable_hash);
1725
1726         while ( (snode = jsonIteratorNext( search_itr )) ) {
1727                 osrfHash* idlClass = osrfHashGet( oilsIDL(), search_itr->key );
1728
1729                 char* class = osrfHashGet(idlClass, "classname");
1730
1731                 char* fkey = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "fkey" ) );
1732                 char* field = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "field" ) );
1733
1734                 if (field && !fkey) {
1735                         fkey = (char*)oilsIDLFindPath("/%s/links/%s/key", class, field);
1736                         if (!fkey) {
1737                                 osrfLogError(
1738                                         OSRF_LOG_MARK,
1739                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
1740                                         MODULENAME,
1741                                         class,
1742                                         field,
1743                                         leftclass
1744                                 );
1745                                 buffer_free(join_buf);
1746                                 free(field);
1747                                 jsonIteratorFree(search_itr);
1748                                 return NULL;
1749                         }
1750                         fkey = strdup( fkey );
1751
1752                 } else if (!field && fkey) {
1753                         field = (char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey );
1754                         if (!field) {
1755                                 osrfLogError(
1756                                         OSRF_LOG_MARK,
1757                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
1758                                         MODULENAME,
1759                                         leftclass,
1760                                         fkey,
1761                                         class
1762                                 );
1763                                 buffer_free(join_buf);
1764                                 free(fkey);
1765                                 jsonIteratorFree(search_itr);
1766                                 return NULL;
1767                         }
1768                         field = strdup( field );
1769
1770                 } else if (!field && !fkey) {
1771                         osrfHash* _links = oilsIDLFindPath("/%s/links", leftclass);
1772
1773                         int i = 0;
1774                         osrfStringArray* keys = osrfHashKeys( _links );
1775                         while ( (fkey = osrfStringArrayGetString(keys, i++)) ) {
1776                                 fkey = strdup(osrfStringArrayGetString(keys, i++));
1777                                 if ( !strcmp( (char*)oilsIDLFindPath("/%s/links/%s/class", leftclass, fkey), class) ) {
1778                                         field = strdup( (char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey) );
1779                                         break;
1780                                 } else {
1781                                         free(fkey);
1782                                 }
1783                         }
1784                         osrfStringArrayFree(keys);
1785
1786                         if (!field && !fkey) {
1787                                 _links = oilsIDLFindPath("/%s/links", class);
1788
1789                                 i = 0;
1790                                 keys = osrfHashKeys( _links );
1791                                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
1792                                         field = strdup(osrfStringArrayGetString(keys, i++));
1793                                         if ( !strcmp( (char*)oilsIDLFindPath("/%s/links/%s/class", class, field), class) ) {
1794                                                 fkey = strdup( (char*)oilsIDLFindPath("/%s/links/%s/key", class, field) );
1795                                                 break;
1796                                         } else {
1797                                                 free(field);
1798                                         }
1799                                 }
1800                                 osrfStringArrayFree(keys);
1801                         }
1802
1803                         if (!field && !fkey) {
1804                                 osrfLogError(
1805                                         OSRF_LOG_MARK,
1806                                         "%s: JOIN failed.  No link defined between %s and %s",
1807                                         MODULENAME,
1808                                         leftclass,
1809                                         class
1810                                 );
1811                                 buffer_free(join_buf);
1812                                 jsonIteratorFree(search_itr);
1813                                 return NULL;
1814                         }
1815
1816                 }
1817
1818                 char* type = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "type" ) );
1819                 if (type) {
1820                         if ( !strcasecmp(type,"left") ) {
1821                                 buffer_add(join_buf, " LEFT JOIN");
1822                         } else if ( !strcasecmp(type,"right") ) {
1823                                 buffer_add(join_buf, " RIGHT JOIN");
1824                         } else if ( !strcasecmp(type,"full") ) {
1825                                 buffer_add(join_buf, " FULL JOIN");
1826                         } else {
1827                                 buffer_add(join_buf, " INNER JOIN");
1828                         }
1829                 } else {
1830                         buffer_add(join_buf, " INNER JOIN");
1831                 }
1832                 free(type);
1833
1834                 char* table = getSourceDefinition(idlClass);
1835                 buffer_fadd(join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s", table, class, class, field, leftclass, fkey);
1836                 free(table);
1837
1838                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
1839                 if (filter) {
1840                         char* filter_op = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "filter_op" ) );
1841                         if (filter_op) {
1842                                 if (!strcasecmp("or",filter_op)) {
1843                                         buffer_add( join_buf, " OR " );
1844                                 } else {
1845                                         buffer_add( join_buf, " AND " );
1846                                 }
1847                         } else {
1848                                 buffer_add( join_buf, " AND " );
1849                         }
1850
1851                         char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN, NULL );
1852                         buffer_fadd( join_buf, " %s", jpred );
1853                         free(jpred);
1854                         free(filter_op);
1855                 }
1856
1857                 buffer_add(join_buf, " ) ");
1858
1859                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
1860                 if (join_filter) {
1861                         char* jpred = searchJOIN( join_filter, idlClass );
1862                         buffer_fadd( join_buf, " %s", jpred );
1863                         free(jpred);
1864                 }
1865
1866                 free(fkey);
1867                 free(field);
1868         }
1869
1870     jsonIteratorFree(search_itr);
1871
1872         return buffer_release(join_buf);
1873 }
1874
1875 /*
1876
1877 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
1878 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
1879 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
1880
1881 */
1882
1883 static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type, osrfMethodContext* ctx ) {
1884
1885         growing_buffer* sql_buf = buffer_init(128);
1886
1887         jsonObject* node = NULL;
1888
1889     int first = 1;
1890     if ( search_hash->type == JSON_ARRAY ) {
1891         jsonIterator* search_itr = jsonNewIterator( search_hash );
1892         while ( (node = jsonIteratorNext( search_itr )) ) {
1893             if (first) {
1894                 first = 0;
1895             } else {
1896                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
1897                 else buffer_add(sql_buf, " AND ");
1898             }
1899
1900             char* subpred = searchWHERE( node, meta, opjoin_type, ctx );
1901             buffer_fadd(sql_buf, "( %s )", subpred);
1902             free(subpred);
1903         }
1904         jsonIteratorFree(search_itr);
1905
1906     } else if ( search_hash->type == JSON_HASH ) {
1907         jsonIterator* search_itr = jsonNewIterator( search_hash );
1908         while ( (node = jsonIteratorNext( search_itr )) ) {
1909
1910             if (first) {
1911                 first = 0;
1912             } else {
1913                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
1914                 else buffer_add(sql_buf, " AND ");
1915             }
1916
1917             if ( !strncmp("+",search_itr->key,1) ) {
1918                 if ( node->type == JSON_STRING ) {
1919                     char* subpred = jsonObjectToSimpleString( node );
1920                     buffer_fadd(sql_buf, " \"%s\".%s ", search_itr->key + 1, subpred);
1921                     free(subpred);
1922                 } else {
1923                     char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN, ctx );
1924                     buffer_fadd(sql_buf, "( %s )", subpred);
1925                     free(subpred);
1926                 }
1927             } else if ( !strcasecmp("-or",search_itr->key) ) {
1928                 char* subpred = searchWHERE( node, meta, OR_OP_JOIN, ctx );
1929                 buffer_fadd(sql_buf, "( %s )", subpred);
1930                 free(subpred);
1931             } else if ( !strcasecmp("-and",search_itr->key) ) {
1932                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
1933                 buffer_fadd(sql_buf, "( %s )", subpred);
1934                 free(subpred);
1935             } else if ( !strcasecmp("-exists",search_itr->key) ) {
1936                 char* subpred = SELECT(
1937                     ctx,
1938                     jsonObjectGetKey( node, "select" ),
1939                     jsonObjectGetKey( node, "from" ),
1940                     jsonObjectGetKey( node, "where" ),
1941                     jsonObjectGetKey( node, "having" ),
1942                     jsonObjectGetKey( node, "order_by" ),
1943                     jsonObjectGetKey( node, "limit" ),
1944                     jsonObjectGetKey( node, "offset" ),
1945                     SUBSELECT
1946                 );
1947
1948                 buffer_fadd(sql_buf, "EXISTS ( %s )", subpred);
1949                 free(subpred);
1950             } else if ( !strcasecmp("-not-exists",search_itr->key) ) {
1951                 char* subpred = SELECT(
1952                     ctx,
1953                     jsonObjectGetKey( node, "select" ),
1954                     jsonObjectGetKey( node, "from" ),
1955                     jsonObjectGetKey( node, "where" ),
1956                     jsonObjectGetKey( node, "having" ),
1957                     jsonObjectGetKey( node, "order_by" ),
1958                     jsonObjectGetKey( node, "limit" ),
1959                     jsonObjectGetKey( node, "offset" ),
1960                     SUBSELECT
1961                 );
1962
1963                 buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred);
1964                 free(subpred);
1965             } else {
1966
1967                 char* class = osrfHashGet(meta, "classname");
1968                 osrfHash* fields = osrfHashGet(meta, "fields");
1969                 osrfHash* field = osrfHashGet( fields, search_itr->key );
1970
1971
1972                 if (!field) {
1973                     char* table = getSourceDefinition(meta);
1974                     osrfLogError(
1975                         OSRF_LOG_MARK,
1976                         "%s: Attempt to reference non-existant column %s on %s (%s)",
1977                         MODULENAME,
1978                         search_itr->key,
1979                         table,
1980                         class
1981                     );
1982                     buffer_free(sql_buf);
1983                     free(table);
1984                                         jsonIteratorFree(search_itr);
1985                                         return NULL;
1986                 }
1987
1988                 char* subpred = searchPredicate( class, field, node );
1989                 buffer_add( sql_buf, subpred );
1990                 free(subpred);
1991             }
1992         }
1993             jsonIteratorFree(search_itr);
1994
1995     } else {
1996         // ERROR ... only hash and array allowed at this level
1997         char* predicate_string = jsonObjectToJSON( search_hash );
1998         osrfLogError(
1999             OSRF_LOG_MARK,
2000             "%s: Invalid predicate structure: %s",
2001             MODULENAME,
2002             predicate_string
2003         );
2004         buffer_free(sql_buf);
2005         free(predicate_string);
2006         return NULL;
2007     }
2008
2009
2010         return buffer_release(sql_buf);
2011 }
2012
2013 static char* SELECT (
2014                 /* method context */ osrfMethodContext* ctx,
2015
2016                 /* SELECT   */ jsonObject* selhash,
2017                 /* FROM     */ jsonObject* join_hash,
2018                 /* WHERE    */ jsonObject* search_hash,
2019                 /* HAVING   */ jsonObject* having_hash,
2020                 /* ORDER BY */ jsonObject* order_hash,
2021                 /* LIMIT    */ jsonObject* limit,
2022                 /* OFFSET   */ jsonObject* offset,
2023                 /* flags    */ int flags
2024 ) {
2025         const char* locale = osrf_message_get_last_locale();
2026
2027         // in case we don't get a select list
2028         jsonObject* defaultselhash = NULL;
2029
2030         // general tmp objects
2031         const jsonObject* tmp_const;
2032         jsonObject* _tmp = NULL;
2033         jsonObject* selclass = NULL;
2034         jsonObject* selfield = NULL;
2035         jsonObject* snode = NULL;
2036         jsonObject* onode = NULL;
2037         jsonObject* found = NULL;
2038
2039         char* string = NULL;
2040         int from_function = 0;
2041         int first = 1;
2042         int gfirst = 1;
2043         //int hfirst = 1;
2044
2045         // the core search class
2046         char* core_class = NULL;
2047
2048         // metadata about the core search class
2049         osrfHash* core_meta = NULL;
2050         osrfHash* core_fields = NULL;
2051         osrfHash* idlClass = NULL;
2052
2053         // punt if there's no core class
2054         if (!join_hash || ( join_hash->type == JSON_HASH && !join_hash->size ))
2055                 return NULL;
2056
2057         // get the core class -- the only key of the top level FROM clause, or a string
2058         if (join_hash->type == JSON_HASH) {
2059                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
2060                 snode = jsonIteratorNext( tmp_itr );
2061
2062                 core_class = strdup( tmp_itr->key );
2063                 join_hash = snode;
2064
2065                 jsonIteratorFree( tmp_itr );
2066                 snode = NULL;
2067
2068         } else if (join_hash->type == JSON_ARRAY) {
2069         from_function = 1;
2070         selhash = NULL;
2071
2072         } else if (join_hash->type == JSON_STRING) {
2073                 core_class = jsonObjectToSimpleString( join_hash );
2074                 join_hash = NULL;
2075         }
2076
2077         // punt if we don't know about the core class (and it's not a function)
2078         if (!from_function && !(core_meta = osrfHashGet( oilsIDL(), core_class ))) {
2079                 free(core_class);
2080                 return NULL;
2081         }
2082
2083         // if the select list is empty, or the core class field list is '*',
2084         // build the default select list ...
2085         if (!selhash) {
2086                 selhash = defaultselhash = jsonNewObjectType(JSON_HASH);
2087                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2088         } else if ( (tmp_const = jsonObjectGetKeyConst( selhash, core_class )) && tmp_const->type == JSON_STRING ) {
2089                 char* _x = jsonObjectToSimpleString( tmp_const );
2090                 if (!strncmp( "*", _x, 1 )) {
2091                         jsonObjectRemoveKey( selhash, core_class );
2092                         jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2093                 }
2094                 free(_x);
2095         }
2096
2097         // the query buffer
2098         growing_buffer* sql_buf = buffer_init(128);
2099
2100         // temp buffer for the SELECT list
2101         growing_buffer* select_buf = buffer_init(128);
2102         growing_buffer* order_buf = buffer_init(128);
2103         growing_buffer* group_buf = buffer_init(128);
2104         growing_buffer* having_buf = buffer_init(128);
2105
2106         if (!from_function)
2107         core_fields = osrfHashGet(core_meta, "fields");
2108
2109         // ... and if we /are/ building the default list, do that
2110         if ( (_tmp = jsonObjectGetKey(selhash,core_class)) && !_tmp->size ) {
2111
2112                 int i = 0;
2113                 char* field;
2114
2115         if (!from_function) {
2116                 osrfStringArray* keys = osrfHashKeys( core_fields );
2117                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
2118                         if ( strncasecmp( "true", osrfHashGet( osrfHashGet( core_fields, field ), "virtual" ), 4 ) )
2119                                 jsonObjectPush( _tmp, jsonNewObject( field ) );
2120                 }
2121                 osrfStringArrayFree(keys);
2122         }
2123         }
2124
2125         // Now we build the actual select list
2126         if (!from_function) {
2127             int sel_pos = 1;
2128             jsonObject* is_agg = jsonObjectFindPath(selhash, "//aggregate");
2129             first = 1;
2130             gfirst = 1;
2131             jsonIterator* selclass_itr = jsonNewIterator( selhash );
2132             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {
2133
2134                     // round trip through the idl, just to be safe
2135                     idlClass = osrfHashGet( oilsIDL(), selclass_itr->key );
2136                     if (!idlClass) continue;
2137                     char* cname = osrfHashGet(idlClass, "classname");
2138
2139                     // make sure the target relation is in the join tree
2140                     if (strcmp(core_class,cname)) {
2141                             if (!join_hash) continue;
2142
2143                             if (join_hash->type == JSON_STRING) {
2144                                     string = jsonObjectToSimpleString(join_hash);
2145                                     found = strcmp(string,cname) ? NULL : jsonParseString("{\"1\":\"1\"}");
2146                                     free(string);
2147                             } else {
2148                                     found = jsonObjectFindPath(join_hash, "//%s", cname);
2149                             }
2150
2151                             if (!found->size) {
2152                                     jsonObjectFree(found);
2153                                     continue;
2154                             }
2155
2156                             jsonObjectFree(found);
2157                     }
2158
2159                     // stitch together the column list ...
2160                     jsonIterator* select_itr = jsonNewIterator( selclass );
2161                     while ( (selfield = jsonIteratorNext( select_itr )) ) {
2162
2163                             char* __column = NULL;
2164                             char* __alias = NULL;
2165
2166                             // ... if it's a sstring, just toss it on the pile
2167                             if (selfield->type == JSON_STRING) {
2168
2169                                     // again, just to be safe
2170                                     char* _requested_col = jsonObjectToSimpleString(selfield);
2171                                     osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), _requested_col );
2172                                     free(_requested_col);
2173
2174                                     if (!field) continue;
2175                                     __column = strdup(osrfHashGet(field, "name"));
2176
2177                                     if (first) {
2178                                             first = 0;
2179                                     } else {
2180                                             buffer_add(select_buf, ",");
2181                                     }
2182
2183                     if (locale) {
2184                             char* i18n = osrfHashGet(field, "i18n");
2185                                     if (flags & DISABLE_I18N)
2186                             i18n = NULL;
2187
2188                             if ( i18n && !strncasecmp("true", i18n, 4)) {
2189                                 char* pkey = osrfHashGet(idlClass, "primarykey");
2190                                 char* tname = osrfHashGet(idlClass, "tablename");
2191
2192                             buffer_fadd(select_buf, " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"", tname, cname, __column, pkey, cname, pkey, locale, __column);
2193                         } else {
2194                                             buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, __column, __column);
2195                         }
2196                     } else {
2197                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, __column, __column);
2198                     }
2199
2200                             // ... but it could be an object, in which case we check for a Field Transform
2201                             } else {
2202
2203                                     __column = jsonObjectToSimpleString( jsonObjectGetKeyConst( selfield, "column" ) );
2204
2205                                     // again, just to be safe
2206                                     osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), __column );
2207                                     if (!field) continue;
2208                                     const char* fname = osrfHashGet(field, "name");
2209
2210                                     if (first) {
2211                                             first = 0;
2212                                     } else {
2213                                             buffer_add(select_buf, ",");
2214                                     }
2215
2216                                     if ((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
2217                                             __alias = jsonObjectToSimpleString( tmp_const );
2218                                     } else {
2219                                             __alias = strdup(__column);
2220                                     }
2221
2222                                     if (jsonObjectGetKeyConst( selfield, "transform" )) {
2223                                             free(__column);
2224                                             __column = searchFieldTransform(cname, field, selfield);
2225                                             buffer_fadd(select_buf, " %s AS \"%s\"", __column, __alias);
2226                                     } else {
2227                         if (locale) {
2228                                     char* i18n = osrfHashGet(field, "i18n");
2229                                         if (flags & DISABLE_I18N)
2230                                 i18n = NULL;
2231
2232                                     if ( i18n && !strncasecmp("true", i18n, 4)) {
2233                                     char* pkey = osrfHashGet(idlClass, "primarykey");
2234                                     char* tname = osrfHashGet(idlClass, "tablename");
2235
2236                                 buffer_fadd(select_buf, " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"", tname, cname, fname, pkey, cname, pkey, locale, __alias);
2237                             } else {
2238                                                     buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, fname, __alias);
2239                             }
2240                         } else {
2241                                                 buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, fname, __alias);
2242                         }
2243                                     }
2244                             }
2245
2246                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
2247
2248                                     if ( !(
2249                             jsonBoolIsTrue( jsonObjectGetKey( selfield, "aggregate" ) ) ||
2250                                 ((int)jsonObjectGetNumber(jsonObjectGetKey( selfield, "aggregate" ))) == 1 // support 1/0 for perl's sake
2251                          )
2252                     ) {
2253                                             if (gfirst) {
2254                                                     gfirst = 0;
2255                                             } else {
2256                                                     buffer_add(group_buf, ",");
2257                                             }
2258
2259                                             buffer_fadd(group_buf, " %d", sel_pos);
2260                                     /*
2261                                     } else if (is_agg = jsonObjectGetKey( selfield, "having" )) {
2262                                             if (gfirst) {
2263                                                     gfirst = 0;
2264                                             } else {
2265                                                     buffer_add(group_buf, ",");
2266                                             }
2267
2268                                             __column = searchFieldTransform(cname, field, selfield);
2269                                             buffer_fadd(group_buf, " %s", __column);
2270                                             __column = searchFieldTransform(cname, field, selfield);
2271                                     */
2272                                     }
2273                             }
2274
2275                             if (__column) free(__column);
2276                             if (__alias) free(__alias);
2277
2278                             sel_pos++;
2279                     }
2280
2281             // jsonIteratorFree(select_itr);
2282             }
2283
2284         // jsonIteratorFree(selclass_itr);
2285
2286             if (is_agg) jsonObjectFree(is_agg);
2287     } else {
2288         buffer_add(select_buf, "*");
2289     }
2290
2291
2292         char* col_list = buffer_release(select_buf);
2293         char* table = NULL;
2294     if (!from_function) table = getSourceDefinition(core_meta);
2295     else table = searchValueTransform(join_hash);
2296
2297         // Put it all together
2298         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
2299         free(col_list);
2300         free(table);
2301
2302     if (!from_function) {
2303             // Now, walk the join tree and add that clause
2304             if ( join_hash ) {
2305                     char* join_clause = searchJOIN( join_hash, core_meta );
2306                     buffer_add(sql_buf, join_clause);
2307                     free(join_clause);
2308             }
2309
2310             if ( search_hash ) {
2311                     buffer_add(sql_buf, " WHERE ");
2312
2313                     // and it's on the the WHERE clause
2314                     char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN, ctx );
2315
2316                     if (!pred) {
2317                             if (ctx) {
2318                                 osrfAppSessionStatus(
2319                                         ctx->session,
2320                                         OSRF_STATUS_INTERNALSERVERERROR,
2321                                         "osrfMethodException",
2322                                         ctx->request,
2323                                         "Severe query error in WHERE predicate -- see error log for more details"
2324                                 );
2325                             }
2326                             free(core_class);
2327                             buffer_free(having_buf);
2328                             buffer_free(group_buf);
2329                             buffer_free(order_buf);
2330                             buffer_free(sql_buf);
2331                             if (defaultselhash) jsonObjectFree(defaultselhash);
2332                             return NULL;
2333                     } else {
2334                             buffer_add(sql_buf, pred);
2335                             free(pred);
2336                     }
2337         }
2338
2339             if ( having_hash ) {
2340                     buffer_add(sql_buf, " HAVING ");
2341
2342                     // and it's on the the WHERE clause
2343                     char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN, ctx );
2344
2345                     if (!pred) {
2346                             if (ctx) {
2347                                 osrfAppSessionStatus(
2348                                         ctx->session,
2349                                         OSRF_STATUS_INTERNALSERVERERROR,
2350                                         "osrfMethodException",
2351                                         ctx->request,
2352                                         "Severe query error in HAVING predicate -- see error log for more details"
2353                                 );
2354                             }
2355                             free(core_class);
2356                             buffer_free(having_buf);
2357                             buffer_free(group_buf);
2358                             buffer_free(order_buf);
2359                             buffer_free(sql_buf);
2360                             if (defaultselhash) jsonObjectFree(defaultselhash);
2361                             return NULL;
2362                     } else {
2363                             buffer_add(sql_buf, pred);
2364                             free(pred);
2365                     }
2366             }
2367
2368             first = 1;
2369             jsonIterator* class_itr = jsonNewIterator( order_hash );
2370             while ( (snode = jsonIteratorNext( class_itr )) ) {
2371
2372                     if (!jsonObjectGetKeyConst(selhash,class_itr->key))
2373                             continue;
2374
2375                     if ( snode->type == JSON_HASH ) {
2376
2377                         jsonIterator* order_itr = jsonNewIterator( snode );
2378                             while ( (onode = jsonIteratorNext( order_itr )) ) {
2379
2380                                     if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
2381                                             continue;
2382
2383                                     char* direction = NULL;
2384                                     if ( onode->type == JSON_HASH ) {
2385                                             if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
2386                                                     string = searchFieldTransform(
2387                                                             class_itr->key,
2388                                                             oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
2389                                                             onode
2390                                                     );
2391                                             } else {
2392                                                     growing_buffer* field_buf = buffer_init(16);
2393                                                     buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
2394                                                     string = buffer_release(field_buf);
2395                                             }
2396
2397                                             if ( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
2398                                                     direction = jsonObjectToSimpleString(tmp_const);
2399                                                     if (!strncasecmp(direction, "d", 1)) {
2400                                                             free(direction);
2401                                                             direction = " DESC";
2402                                                     } else {
2403                                                             free(direction);
2404                                                             direction = " ASC";
2405                                                     }
2406                                             }
2407
2408                                     } else {
2409                                             string = strdup(order_itr->key);
2410                                             direction = jsonObjectToSimpleString(onode);
2411                                             if (!strncasecmp(direction, "d", 1)) {
2412                                                     free(direction);
2413                                                     direction = " DESC";
2414                                             } else {
2415                                                     free(direction);
2416                                                     direction = " ASC";
2417                                             }
2418                                     }
2419
2420                                     if (first) {
2421                                             first = 0;
2422                                     } else {
2423                                             buffer_add(order_buf, ", ");
2424                                     }
2425
2426                                     buffer_add(order_buf, string);
2427                                     free(string);
2428
2429                                     if (direction) {
2430                                             buffer_add(order_buf, direction);
2431                                     }
2432
2433                             }
2434                 // jsonIteratorFree(order_itr);
2435
2436                     } else if ( snode->type == JSON_ARRAY ) {
2437
2438                         jsonIterator* order_itr = jsonNewIterator( snode );
2439                             while ( (onode = jsonIteratorNext( order_itr )) ) {
2440
2441                                     char* _f = jsonObjectToSimpleString( onode );
2442
2443                                     if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, _f))
2444                                             continue;
2445
2446                                     if (first) {
2447                                             first = 0;
2448                                     } else {
2449                                             buffer_add(order_buf, ", ");
2450                                     }
2451
2452                                     buffer_add(order_buf, _f);
2453                                     free(_f);
2454
2455                             }
2456                 // jsonIteratorFree(order_itr);
2457
2458
2459                     // IT'S THE OOOOOOOOOOOLD STYLE!
2460                     } else {
2461                             osrfLogError(OSRF_LOG_MARK, "%s: Possible SQL injection attempt; direct order by is not allowed", MODULENAME);
2462                             if (ctx) {
2463                                 osrfAppSessionStatus(
2464                                         ctx->session,
2465                                         OSRF_STATUS_INTERNALSERVERERROR,
2466                                         "osrfMethodException",
2467                                         ctx->request,
2468                                         "Severe query error -- see error log for more details"
2469                                 );
2470                             }
2471
2472                             free(core_class);
2473                             buffer_free(having_buf);
2474                             buffer_free(group_buf);
2475                             buffer_free(order_buf);
2476                             buffer_free(sql_buf);
2477                             if (defaultselhash) jsonObjectFree(defaultselhash);
2478                             jsonIteratorFree(class_itr);
2479                             return NULL;
2480                     }
2481
2482             }
2483     }
2484
2485     // jsonIteratorFree(class_itr);
2486
2487         string = buffer_release(group_buf);
2488
2489         if (strlen(string)) {
2490                 buffer_fadd(
2491                         sql_buf,
2492                         " GROUP BY %s",
2493                         string
2494                 );
2495         }
2496
2497         free(string);
2498
2499         string = buffer_release(having_buf);
2500
2501         if (strlen(string)) {
2502                 buffer_fadd(
2503                         sql_buf,
2504                         " HAVING %s",
2505                         string
2506                 );
2507         }
2508
2509         free(string);
2510
2511         string = buffer_release(order_buf);
2512
2513         if (strlen(string)) {
2514                 buffer_fadd(
2515                         sql_buf,
2516                         " ORDER BY %s",
2517                         string
2518                 );
2519         }
2520
2521         free(string);
2522
2523         if ( limit ){
2524                 string = jsonObjectToSimpleString(limit);
2525                 buffer_fadd( sql_buf, " LIMIT %d", atoi(string) );
2526                 free(string);
2527         }
2528
2529         if (offset) {
2530                 string = jsonObjectToSimpleString(offset);
2531                 buffer_fadd( sql_buf, " OFFSET %d", atoi(string) );
2532                 free(string);
2533         }
2534
2535         if (!(flags & SUBSELECT)) buffer_add(sql_buf, ";");
2536
2537         free(core_class);
2538         if (defaultselhash) jsonObjectFree(defaultselhash);
2539
2540         return buffer_release(sql_buf);
2541
2542 }
2543
2544 static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrfHash* meta, osrfMethodContext* ctx ) {
2545
2546         const char* locale = osrf_message_get_last_locale();
2547
2548         osrfHash* fields = osrfHashGet(meta, "fields");
2549         char* core_class = osrfHashGet(meta, "classname");
2550
2551         const jsonObject* join_hash = jsonObjectGetKeyConst( order_hash, "join" );
2552
2553         jsonObject* node = NULL;
2554         jsonObject* snode = NULL;
2555         jsonObject* onode = NULL;
2556         const jsonObject* _tmp = NULL;
2557         jsonObject* selhash = NULL;
2558         jsonObject* defaultselhash = NULL;
2559
2560         growing_buffer* sql_buf = buffer_init(128);
2561         growing_buffer* select_buf = buffer_init(128);
2562
2563         if ( !(selhash = jsonObjectGetKey( order_hash, "select" )) ) {
2564                 defaultselhash = jsonNewObjectType(JSON_HASH);
2565                 selhash = defaultselhash;
2566         }
2567
2568         if ( !jsonObjectGetKeyConst(selhash,core_class) ) {
2569                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2570                 jsonObject* flist = jsonObjectGetKey( selhash, core_class );
2571
2572                 int i = 0;
2573                 char* field;
2574
2575                 osrfStringArray* keys = osrfHashKeys( fields );
2576                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
2577                         if ( strcasecmp( "true", osrfHashGet( osrfHashGet( fields, field ), "virtual" ) ) )
2578                                 jsonObjectPush( flist, jsonNewObject( field ) );
2579                 }
2580                 osrfStringArrayFree(keys);
2581         }
2582
2583         int first = 1;
2584         jsonIterator* class_itr = jsonNewIterator( selhash );
2585         while ( (snode = jsonIteratorNext( class_itr )) ) {
2586
2587                 osrfHash* idlClass = osrfHashGet( oilsIDL(), class_itr->key );
2588                 if (!idlClass) continue;
2589                 char* cname = osrfHashGet(idlClass, "classname");
2590
2591                 if (strcmp(core_class,class_itr->key)) {
2592                         if (!join_hash) continue;
2593
2594                         jsonObject* found =  jsonObjectFindPath(join_hash, "//%s", class_itr->key);
2595                         if (!found->size) {
2596                                 jsonObjectFree(found);
2597                                 continue;
2598                         }
2599
2600                         jsonObjectFree(found);
2601                 }
2602
2603                 jsonIterator* select_itr = jsonNewIterator( snode );
2604                 while ( (node = jsonIteratorNext( select_itr )) ) {
2605                         char* item_str = jsonObjectToSimpleString(node);
2606                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
2607                         free(item_str);
2608                         char* fname = osrfHashGet(field, "name");
2609
2610                         if (!field) continue;
2611
2612                         if (first) {
2613                                 first = 0;
2614                         } else {
2615                                 buffer_add(select_buf, ",");
2616                         }
2617
2618             if (locale) {
2619                         char* i18n = osrfHashGet(field, "i18n");
2620                             if ( !(
2621                         jsonBoolIsTrue( jsonObjectGetKey( order_hash, "no_i18n" ) ) ||
2622                         ((int)jsonObjectGetNumber(jsonObjectGetKey( order_hash, "no_i18n" ))) == 1 // support 1/0 for perl's sake
2623                      )
2624                 ) i18n = NULL;
2625
2626                         if ( i18n && !strncasecmp("true", i18n, 4)) {
2627                         char* pkey = osrfHashGet(idlClass, "primarykey");
2628                         char* tname = osrfHashGet(idlClass, "tablename");
2629
2630                     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);
2631                 } else {
2632                                 buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
2633                 }
2634             } else {
2635                             buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
2636             }
2637                 }
2638
2639         jsonIteratorFree(select_itr);
2640         }
2641
2642     jsonIteratorFree(class_itr);
2643
2644         char* col_list = buffer_release(select_buf);
2645         char* table = getSourceDefinition(meta);
2646
2647         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
2648         free(col_list);
2649         free(table);
2650
2651         if ( join_hash ) {
2652                 char* join_clause = searchJOIN( join_hash, meta );
2653                 buffer_fadd(sql_buf, " %s", join_clause);
2654                 free(join_clause);
2655         }
2656
2657         char* tmpsql = buffer_data(sql_buf);
2658         osrfLogDebug(OSRF_LOG_MARK, "%s pre-predicate SQL =  %s", MODULENAME, tmpsql);
2659         free(tmpsql);
2660
2661         buffer_add(sql_buf, " WHERE ");
2662
2663         char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN, ctx );
2664         if (!pred) {
2665                 osrfAppSessionStatus(
2666                         ctx->session,
2667                         OSRF_STATUS_INTERNALSERVERERROR,
2668                                 "osrfMethodException",
2669                                 ctx->request,
2670                                 "Severe query error -- see error log for more details"
2671                         );
2672                 buffer_free(sql_buf);
2673                 if(defaultselhash) jsonObjectFree(defaultselhash);
2674                 return NULL;
2675         } else {
2676                 buffer_add(sql_buf, pred);
2677                 free(pred);
2678         }
2679
2680         if (order_hash) {
2681                 char* string = NULL;
2682                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "order_by" )) ){
2683
2684                         growing_buffer* order_buf = buffer_init(128);
2685
2686                         first = 1;
2687                         jsonIterator* class_itr = jsonNewIterator( _tmp );
2688                         while ( (snode = jsonIteratorNext( class_itr )) ) {
2689
2690                                 if (!jsonObjectGetKeyConst(selhash,class_itr->key))
2691                                         continue;
2692
2693                                 if ( snode->type == JSON_HASH ) {
2694
2695                                         jsonIterator* order_itr = jsonNewIterator( snode );
2696                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
2697
2698                                                 if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
2699                                                         continue;
2700
2701                                                 char* direction = NULL;
2702                                                 if ( onode->type == JSON_HASH ) {
2703                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
2704                                                                 string = searchFieldTransform(
2705                                                                         class_itr->key,
2706                                                                         oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
2707                                                                         onode
2708                                                                 );
2709                                                         } else {
2710                                                                 growing_buffer* field_buf = buffer_init(16);
2711                                                                 buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
2712                                                                 string = buffer_release(field_buf);
2713                                                         }
2714
2715                                                         if ( (_tmp = jsonObjectGetKeyConst( onode, "direction" )) ) {
2716                                                                 direction = jsonObjectToSimpleString(_tmp);
2717                                                                 if (!strncasecmp(direction, "d", 1)) {
2718                                                                         free(direction);
2719                                                                         direction = " DESC";
2720                                                                 } else {
2721                                                                         free(direction);
2722                                                                         direction = " ASC";
2723                                                                 }
2724                                                         }
2725
2726                                                 } else {
2727                                                         string = strdup(order_itr->key);
2728                                                         direction = jsonObjectToSimpleString(onode);
2729                                                         if (!strncasecmp(direction, "d", 1)) {
2730                                                                 free(direction);
2731                                                                 direction = " DESC";
2732                                                         } else {
2733                                                                 free(direction);
2734                                                                 direction = " ASC";
2735                                                         }
2736                                                 }
2737
2738                                                 if (first) {
2739                                                         first = 0;
2740                                                 } else {
2741                                                         buffer_add(order_buf, ", ");
2742                                                 }
2743
2744                                                 buffer_add(order_buf, string);
2745                                                 free(string);
2746
2747                                                 if (direction) {
2748                                                         buffer_add(order_buf, direction);
2749                                                 }
2750
2751                                         }
2752
2753                     jsonIteratorFree(order_itr);
2754
2755                                 } else {
2756                                         string = jsonObjectToSimpleString(snode);
2757                                         buffer_add(order_buf, string);
2758                                         free(string);
2759                                         break;
2760                                 }
2761
2762                         }
2763
2764             jsonIteratorFree(class_itr);
2765
2766                         string = buffer_release(order_buf);
2767
2768                         if (strlen(string)) {
2769                                 buffer_fadd(
2770                                         sql_buf,
2771                                         " ORDER BY %s",
2772                                         string
2773                                 );
2774                         }
2775
2776                         free(string);
2777                 }
2778
2779                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "limit" )) ){
2780                         string = jsonObjectToSimpleString(_tmp);
2781                         buffer_fadd(
2782                                 sql_buf,
2783                                 " LIMIT %d",
2784                                 atoi(string)
2785                         );
2786                         free(string);
2787                 }
2788
2789                 _tmp = jsonObjectGetKeyConst( order_hash, "offset" );
2790                 if (_tmp) {
2791                         string = jsonObjectToSimpleString(_tmp);
2792                         buffer_fadd(
2793                                 sql_buf,
2794                                 " OFFSET %d",
2795                                 atoi(string)
2796                         );
2797                         free(string);
2798                 }
2799         }
2800
2801         if (defaultselhash) jsonObjectFree(defaultselhash);
2802
2803         buffer_add(sql_buf, ";");
2804         return buffer_release(sql_buf);
2805 }
2806
2807 int doJSONSearch ( osrfMethodContext* ctx ) {
2808         OSRF_METHOD_VERIFY_CONTEXT(ctx);
2809         osrfLogDebug(OSRF_LOG_MARK, "Recieved query request");
2810
2811         int err = 0;
2812
2813         // XXX for now...
2814         dbhandle = writehandle;
2815
2816         jsonObject* hash = jsonObjectGetIndex(ctx->params, 0);
2817
2818     int flags = 0;
2819
2820         if (jsonBoolIsTrue(jsonObjectGetKey( hash, "distinct" )))
2821          flags |= SELECT_DISTINCT;
2822
2823         if ( ((int)jsonObjectGetNumber(jsonObjectGetKey( hash, "distinct" ))) == 1 ) // support 1/0 for perl's sake
2824          flags |= SELECT_DISTINCT;
2825
2826         if (jsonBoolIsTrue(jsonObjectGetKey( hash, "no_i18n" )))
2827          flags |= DISABLE_I18N;
2828
2829         if ( ((int)jsonObjectGetNumber(jsonObjectGetKey( hash, "no_i18n" ))) == 1 ) // support 1/0 for perl's sake
2830          flags |= DISABLE_I18N;
2831
2832         osrfLogDebug(OSRF_LOG_MARK, "Building SQL ...");
2833         char* sql = SELECT(
2834                         ctx,
2835                         jsonObjectGetKey( hash, "select" ),
2836                         jsonObjectGetKey( hash, "from" ),
2837                         jsonObjectGetKey( hash, "where" ),
2838                         jsonObjectGetKey( hash, "having" ),
2839                         jsonObjectGetKey( hash, "order_by" ),
2840                         jsonObjectGetKey( hash, "limit" ),
2841                         jsonObjectGetKey( hash, "offset" ),
2842                         flags
2843         );
2844
2845         if (!sql) {
2846                 err = -1;
2847                 return err;
2848         }
2849
2850         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
2851         dbi_result result = dbi_conn_query(dbhandle, sql);
2852
2853         if(result) {
2854                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
2855
2856                 if (dbi_result_first_row(result)) {
2857                         /* JSONify the result */
2858                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
2859
2860                         do {
2861                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
2862                                 osrfAppRespond( ctx, return_val );
2863                 jsonObjectFree( return_val );
2864                         } while (dbi_result_next_row(result));
2865
2866                 } else {
2867                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
2868                 }
2869
2870                 osrfAppRespondComplete( ctx, NULL );
2871
2872                 /* clean up the query */
2873                 dbi_result_free(result);
2874
2875         } else {
2876                 err = -1;
2877                 osrfLogError(OSRF_LOG_MARK, "%s: Error with query [%s]", MODULENAME, sql);
2878                 osrfAppSessionStatus(
2879                         ctx->session,
2880                         OSRF_STATUS_INTERNALSERVERERROR,
2881                         "osrfMethodException",
2882                         ctx->request,
2883                         "Severe query error -- see error log for more details"
2884                 );
2885         }
2886
2887         free(sql);
2888         return err;
2889 }
2890
2891 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* meta,
2892                 const jsonObject* params, int* err ) {
2893
2894         // XXX for now...
2895         dbhandle = writehandle;
2896
2897         osrfHash* links = osrfHashGet(meta, "links");
2898         osrfHash* fields = osrfHashGet(meta, "fields");
2899         char* core_class = osrfHashGet(meta, "classname");
2900         char* pkey = osrfHashGet(meta, "primarykey");
2901
2902         const jsonObject* _tmp;
2903         jsonObject* obj;
2904         jsonObject* search_hash = jsonObjectGetIndex(params, 0);
2905         jsonObject* order_hash = jsonObjectGetIndex(params, 1);
2906
2907         char* sql = buildSELECT( search_hash, order_hash, meta, ctx );
2908         if (!sql) {
2909                 *err = -1;
2910                 return NULL;
2911         }
2912
2913         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
2914         dbi_result result = dbi_conn_query(dbhandle, sql);
2915
2916         jsonObject* res_list = jsonNewObjectType(JSON_ARRAY);
2917         if(result) {
2918                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
2919                 osrfHash* dedup = osrfNewHash();
2920
2921                 if (dbi_result_first_row(result)) {
2922                         /* JSONify the result */
2923                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
2924                         do {
2925                                 obj = oilsMakeFieldmapperFromResult( result, meta );
2926                                 char* pkey_val = oilsFMGetString( obj, pkey );
2927                                 if ( osrfHashGet( dedup, pkey_val ) ) {
2928                                         jsonObjectFree(obj);
2929                                         free(pkey_val);
2930                                 } else {
2931                                         osrfHashSet( dedup, pkey_val, pkey_val );
2932                                         jsonObjectPush(res_list, obj);
2933                                 }
2934                         } while (dbi_result_next_row(result));
2935                 } else {
2936                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
2937                 }
2938
2939                 osrfHashFree(dedup);
2940
2941                 /* clean up the query */
2942                 dbi_result_free(result);
2943
2944         } else {
2945                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]", MODULENAME, osrfHashGet(meta, "fieldmapper"), sql);
2946                 osrfAppSessionStatus(
2947                         ctx->session,
2948                         OSRF_STATUS_INTERNALSERVERERROR,
2949                         "osrfMethodException",
2950                         ctx->request,
2951                         "Severe query error -- see error log for more details"
2952                 );
2953                 *err = -1;
2954                 free(sql);
2955                 jsonObjectFree(res_list);
2956                 return jsonNULL;
2957
2958         }
2959
2960         free(sql);
2961
2962         if (res_list->size && order_hash) {
2963                 _tmp = jsonObjectGetKeyConst( order_hash, "flesh" );
2964                 if (_tmp) {
2965                         int x = (int)jsonObjectGetNumber(_tmp);
2966                         if (x == -1 || x > max_flesh_depth) x = max_flesh_depth;
2967
2968                         const jsonObject* temp_blob;
2969                         if ((temp_blob = jsonObjectGetKeyConst( order_hash, "flesh_fields" )) && x > 0) {
2970
2971                                 jsonObject* flesh_blob = jsonObjectClone( temp_blob );
2972                                 const jsonObject* flesh_fields = jsonObjectGetKeyConst( flesh_blob, core_class );
2973
2974                                 osrfStringArray* link_fields = NULL;
2975
2976                                 if (flesh_fields) {
2977                                         if (flesh_fields->size == 1) {
2978                                                 char* _t = jsonObjectToSimpleString( jsonObjectGetIndex( flesh_fields, 0 ) );
2979                                                 if (!strcmp(_t,"*")) link_fields = osrfHashKeys( links );
2980                                                 free(_t);
2981                                         }
2982
2983                                         if (!link_fields) {
2984                                                 jsonObject* _f;
2985                                                 link_fields = osrfNewStringArray(1);
2986                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
2987                                                 while ((_f = jsonIteratorNext( _i ))) {
2988                                                         osrfStringArrayAdd( link_fields, jsonObjectToSimpleString( _f ) );
2989                                                 }
2990                         jsonIteratorFree(_i);
2991                                         }
2992                                 }
2993
2994                                 jsonObject* cur;
2995                                 jsonIterator* itr = jsonNewIterator( res_list );
2996                                 while ((cur = jsonIteratorNext( itr ))) {
2997
2998                                         int i = 0;
2999                                         char* link_field;
3000
3001                                         while ( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
3002
3003                                                 osrfLogDebug(OSRF_LOG_MARK, "Starting to flesh %s", link_field);
3004
3005                                                 osrfHash* kid_link = osrfHashGet(links, link_field);
3006                                                 if (!kid_link) continue;
3007
3008                                                 osrfHash* field = osrfHashGet(fields, link_field);
3009                                                 if (!field) continue;
3010
3011                                                 osrfHash* value_field = field;
3012
3013                                                 osrfHash* kid_idl = osrfHashGet(oilsIDL(), osrfHashGet(kid_link, "class"));
3014                                                 if (!kid_idl) continue;
3015
3016                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
3017                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3018                                                 }
3019
3020                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) { // might_have
3021                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3022                                                 }
3023
3024                                                 osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
3025
3026                                                 if (link_map->size > 0) {
3027                                                         jsonObject* _kid_key = jsonNewObjectType(JSON_ARRAY);
3028                                                         jsonObjectPush(
3029                                                                 _kid_key,
3030                                                                 jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
3031                                                         );
3032
3033                                                         jsonObjectSetKey(
3034                                                                 flesh_blob,
3035                                                                 osrfHashGet(kid_link, "class"),
3036                                                                 _kid_key
3037                                                         );
3038                                                 };
3039
3040                                                 osrfLogDebug(
3041                                                         OSRF_LOG_MARK,
3042                                                         "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
3043                                                         osrfHashGet(kid_link, "field"),
3044                                                         osrfHashGet(kid_link, "class"),
3045                                                         osrfHashGet(kid_link, "key"),
3046                                                         osrfHashGet(kid_link, "reltype")
3047                                                 );
3048
3049                                                 jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
3050                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // search hash
3051                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // order/flesh hash
3052
3053                                                 osrfLogDebug(OSRF_LOG_MARK, "Creating dummy params object...");
3054
3055                                                 char* search_key =
3056                                                 jsonObjectToSimpleString(
3057                                                         jsonObjectGetIndex(
3058                                                                 cur,
3059                                                                 atoi( osrfHashGet(value_field, "array_position") )
3060                                                         )
3061                                                 );
3062
3063                                                 if (!search_key) {
3064                                                         osrfLogDebug(OSRF_LOG_MARK, "Nothing to search for!");
3065                                                         continue;
3066                                                 }
3067
3068                                                 jsonObjectSetKey(
3069                                                         jsonObjectGetIndex(fake_params, 0),
3070                                                         osrfHashGet(kid_link, "key"),
3071                                                         jsonNewObject( search_key )
3072                                                 );
3073
3074                                                 free(search_key);
3075
3076
3077                                                 jsonObjectSetKey(
3078                                                         jsonObjectGetIndex(fake_params, 1),
3079                                                         "flesh",
3080                                                         jsonNewNumberObject( (double)(x - 1 + link_map->size) )
3081                                                 );
3082
3083                                                 if (flesh_blob)
3084                                                         jsonObjectSetKey( jsonObjectGetIndex(fake_params, 1), "flesh_fields", jsonObjectClone(flesh_blob) );
3085
3086                                                 if (jsonObjectGetKeyConst(order_hash, "order_by")) {
3087                                                         jsonObjectSetKey(
3088                                                                 jsonObjectGetIndex(fake_params, 1),
3089                                                                 "order_by",
3090                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "order_by"))
3091                                                         );
3092                                                 }
3093
3094                                                 if (jsonObjectGetKeyConst(order_hash, "select")) {
3095                                                         jsonObjectSetKey(
3096                                                                 jsonObjectGetIndex(fake_params, 1),
3097                                                                 "select",
3098                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "select"))
3099                                                         );
3100                                                 }
3101
3102                                                 jsonObject* kids = doFieldmapperSearch(ctx, kid_idl, fake_params, err);
3103
3104                                                 if(*err) {
3105                                                         jsonObjectFree( fake_params );
3106                                                         osrfStringArrayFree(link_fields);
3107                                                         jsonIteratorFree(itr);
3108                                                         jsonObjectFree(res_list);
3109                                                         jsonObjectFree(flesh_blob);
3110                                                         return jsonNULL;
3111                                                 }
3112
3113                                                 osrfLogDebug(OSRF_LOG_MARK, "Search for %s return %d linked objects", osrfHashGet(kid_link, "class"), kids->size);
3114
3115                                                 jsonObject* X = NULL;
3116                                                 if ( link_map->size > 0 && kids->size > 0 ) {
3117                                                         X = kids;
3118                                                         kids = jsonNewObjectType(JSON_ARRAY);
3119
3120                                                         jsonObject* _k_node;
3121                                                         jsonIterator* _k = jsonNewIterator( X );
3122                                                         while ((_k_node = jsonIteratorNext( _k ))) {
3123                                                                 jsonObjectPush(
3124                                                                         kids,
3125                                                                         jsonObjectClone(
3126                                                                                 jsonObjectGetIndex(
3127                                                                                         _k_node,
3128                                                                                         (unsigned long)atoi(
3129                                                                                                 osrfHashGet(
3130                                                                                                         osrfHashGet(
3131                                                                                                                 osrfHashGet(
3132                                                                                                                         osrfHashGet(
3133                                                                                                                                 oilsIDL(),
3134                                                                                                                                 osrfHashGet(kid_link, "class")
3135                                                                                                                         ),
3136                                                                                                                         "fields"
3137                                                                                                                 ),
3138                                                                                                                 osrfStringArrayGetString( link_map, 0 )
3139                                                                                                         ),
3140                                                                                                         "array_position"
3141                                                                                                 )
3142                                                                                         )
3143                                                                                 )
3144                                                                         )
3145                                                                 );
3146                                                         }
3147                                                         jsonIteratorFree(_k);
3148                                                 }
3149
3150                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_a" )) || !(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) {
3151                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
3152                                                         jsonObjectSetIndex(
3153                                                                 cur,
3154                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
3155                                                                 jsonObjectClone( jsonObjectGetIndex(kids, 0) )
3156                                                         );
3157                                                 }
3158
3159                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
3160                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
3161                                                         jsonObjectSetIndex(
3162                                                                 cur,
3163                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
3164                                                                 jsonObjectClone( kids )
3165                                                         );
3166                                                 }
3167
3168                                                 if (X) {
3169                                                         jsonObjectFree(kids);
3170                                                         kids = X;
3171                                                 }
3172
3173                                                 jsonObjectFree( kids );
3174                                                 jsonObjectFree( fake_params );
3175
3176                                                 osrfLogDebug(OSRF_LOG_MARK, "Fleshing of %s complete", osrfHashGet(kid_link, "field"));
3177                                                 osrfLogDebug(OSRF_LOG_MARK, "%s", jsonObjectToJSON(cur));
3178
3179                                         }
3180                                 }
3181                                 jsonObjectFree( flesh_blob );
3182                                 osrfStringArrayFree(link_fields);
3183                                 jsonIteratorFree(itr);
3184                         }
3185                 }
3186         }
3187
3188         return res_list;
3189 }
3190
3191
3192 static jsonObject* doUpdate(osrfMethodContext* ctx, int* err ) {
3193
3194         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
3195 #ifdef PCRUD
3196         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
3197 #else
3198         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
3199 #endif
3200
3201         if (!verifyObjectClass(ctx, target)) {
3202                 *err = -1;
3203                 return jsonNULL;
3204         }
3205
3206         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
3207                 osrfAppSessionStatus(
3208                         ctx->session,
3209                         OSRF_STATUS_BADREQUEST,
3210                         "osrfMethodException",
3211                         ctx->request,
3212                         "No active transaction -- required for UPDATE"
3213                 );
3214                 *err = -1;
3215                 return jsonNULL;
3216         }
3217
3218         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
3219                 osrfAppSessionStatus(
3220                         ctx->session,
3221                         OSRF_STATUS_BADREQUEST,
3222                         "osrfMethodException",
3223                         ctx->request,
3224                         "Cannot UPDATE readonly class"
3225                 );
3226                 *err = -1;
3227                 return jsonNULL;
3228         }
3229
3230         dbhandle = writehandle;
3231
3232         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
3233
3234         // Set the last_xact_id
3235         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
3236         if (index > -1) {
3237                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
3238                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
3239         }
3240
3241         char* pkey = osrfHashGet(meta, "primarykey");
3242         osrfHash* fields = osrfHashGet(meta, "fields");
3243
3244         char* id = oilsFMGetString( target, pkey );
3245
3246         osrfLogDebug(
3247                 OSRF_LOG_MARK,
3248                 "%s updating %s object with %s = %s",
3249                 MODULENAME,
3250                 osrfHashGet(meta, "fieldmapper"),
3251                 pkey,
3252                 id
3253         );
3254
3255         growing_buffer* sql = buffer_init(128);
3256         buffer_fadd(sql,"UPDATE %s SET", osrfHashGet(meta, "tablename"));
3257
3258         int i = 0;
3259         int first = 1;
3260         char* field_name;
3261         osrfStringArray* field_list = osrfHashKeys( fields );
3262         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
3263
3264                 osrfHash* field = osrfHashGet( fields, field_name );
3265
3266                 if(!( strcmp( field_name, pkey ) )) continue;
3267                 if(!( strcmp( osrfHashGet(osrfHashGet(fields,field_name), "virtual"), "true" ) )) continue;
3268
3269                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
3270
3271                 char* value;
3272                 if (field_object && field_object->classname) {
3273                         value = oilsFMGetString(
3274                                 field_object,
3275                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
3276             );
3277                 } else {
3278                         value = jsonObjectToSimpleString( field_object );
3279                 }
3280
3281                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s", osrfHashGet(meta, "fieldmapper"), field_name, value);
3282
3283                 if (!field_object || field_object->type == JSON_NULL) {
3284                         if ( !(!( strcmp( osrfHashGet(meta, "classname"), "au" ) ) && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
3285                                 if (first) first = 0;
3286                                 else buffer_add(sql, ",");
3287                                 buffer_fadd( sql, " %s = NULL", field_name );
3288                         }
3289
3290                 } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
3291                         if (first) first = 0;
3292                         else buffer_add(sql, ",");
3293
3294                         if ( !strncmp(osrfHashGet(field, "datatype"), "INT", (size_t)3) ) {
3295                                 buffer_fadd( sql, " %s = %ld", field_name, atol(value) );
3296                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
3297                                 buffer_fadd( sql, " %s = %f", field_name, atof(value) );
3298                         }
3299
3300                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, osrfHashGet(field, "datatype"));
3301
3302                 } else {
3303                         if ( dbi_conn_quote_string(dbhandle, &value) ) {
3304                                 if (first) first = 0;
3305                                 else buffer_add(sql, ",");
3306                                 buffer_fadd( sql, " %s = %s", field_name, value );
3307
3308                         } else {
3309                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
3310                                 osrfAppSessionStatus(
3311                                         ctx->session,
3312                                         OSRF_STATUS_INTERNALSERVERERROR,
3313                                         "osrfMethodException",
3314                                         ctx->request,
3315                                         "Error quoting string -- please see the error log for more details"
3316                                 );
3317                                 free(value);
3318                                 free(id);
3319                                 buffer_free(sql);
3320                                 *err = -1;
3321                                 return jsonNULL;
3322                         }
3323                 }
3324
3325                 free(value);
3326
3327         }
3328
3329         jsonObject* obj = jsonParseString(id);
3330
3331         if ( strcmp( osrfHashGet( osrfHashGet( osrfHashGet(meta, "fields"), pkey ), "primitive" ), "number" ) )
3332                 dbi_conn_quote_string(dbhandle, &id);
3333
3334         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
3335
3336         char* query = buffer_release(sql);
3337         osrfLogDebug(OSRF_LOG_MARK, "%s: Update SQL [%s]", MODULENAME, query);
3338
3339         dbi_result result = dbi_conn_query(dbhandle, query);
3340         free(query);
3341
3342         if (!result) {
3343                 jsonObjectFree(obj);
3344                 obj = jsonNewObject(NULL);
3345                 osrfLogError(
3346                         OSRF_LOG_MARK,
3347                         "%s ERROR updating %s object with %s = %s",
3348                         MODULENAME,
3349                         osrfHashGet(meta, "fieldmapper"),
3350                         pkey,
3351                         id
3352                 );
3353         }
3354
3355         free(id);
3356
3357         return obj;
3358 }
3359
3360 static jsonObject* doDelete(osrfMethodContext* ctx, int* err ) {
3361
3362         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
3363
3364         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
3365                 osrfAppSessionStatus(
3366                         ctx->session,
3367                         OSRF_STATUS_BADREQUEST,
3368                         "osrfMethodException",
3369                         ctx->request,
3370                         "No active transaction -- required for DELETE"
3371                 );
3372                 *err = -1;
3373                 return jsonNULL;
3374         }
3375
3376         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
3377                 osrfAppSessionStatus(
3378                         ctx->session,
3379                         OSRF_STATUS_BADREQUEST,
3380                         "osrfMethodException",
3381                         ctx->request,
3382                         "Cannot DELETE readonly class"
3383                 );
3384                 *err = -1;
3385                 return jsonNULL;
3386         }
3387
3388         dbhandle = writehandle;
3389
3390         jsonObject* obj;
3391
3392         char* pkey = osrfHashGet(meta, "primarykey");
3393
3394         int _obj_pos = 0;
3395 #ifdef PCRUD
3396                 _obj_pos = 1;
3397 #endif
3398
3399         char* id;
3400         if (jsonObjectGetIndex(ctx->params, _obj_pos)->classname) {
3401                 if (!verifyObjectClass(ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
3402                         *err = -1;
3403                         return jsonNULL;
3404                 }
3405
3406                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
3407         } else {
3408 #ifdef PCRUD
3409         if (!verifyObjectPCRUD( ctx, NULL )) {
3410                         *err = -1;
3411                         return jsonNULL;
3412         }
3413 #endif
3414                 id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, _obj_pos));
3415         }
3416
3417         osrfLogDebug(
3418                 OSRF_LOG_MARK,
3419                 "%s deleting %s object with %s = %s",
3420                 MODULENAME,
3421                 osrfHashGet(meta, "fieldmapper"),
3422                 pkey,
3423                 id
3424         );
3425
3426         obj = jsonParseString(id);
3427
3428         if ( strcmp( osrfHashGet( osrfHashGet( osrfHashGet(meta, "fields"), pkey ), "primitive" ), "number" ) )
3429                 dbi_conn_quote_string(writehandle, &id);
3430
3431         dbi_result result = dbi_conn_queryf(writehandle, "DELETE FROM %s WHERE %s = %s;", osrfHashGet(meta, "tablename"), pkey, id);
3432
3433         if (!result) {
3434                 jsonObjectFree(obj);
3435                 obj = jsonNewObject(NULL);
3436                 osrfLogError(
3437                         OSRF_LOG_MARK,
3438                         "%s ERROR deleting %s object with %s = %s",
3439                         MODULENAME,
3440                         osrfHashGet(meta, "fieldmapper"),
3441                         pkey,
3442                         id
3443                 );
3444         }
3445
3446         free(id);
3447
3448         return obj;
3449
3450 }
3451
3452
3453 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
3454         if(!(result && meta)) return jsonNULL;
3455
3456         jsonObject* object = jsonNewObject(NULL);
3457         jsonObjectSetClass(object, osrfHashGet(meta, "classname"));
3458
3459         osrfHash* fields = osrfHashGet(meta, "fields");
3460
3461         osrfLogInternal(OSRF_LOG_MARK, "Setting object class to %s ", object->classname);
3462
3463         osrfHash* _f;
3464         time_t _tmp_dt;
3465         char dt_string[256];
3466         struct tm gmdt;
3467
3468         int fmIndex;
3469         int columnIndex = 1;
3470         int attr;
3471         unsigned short type;
3472         const char* columnName;
3473
3474         /* cycle through the column list */
3475         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
3476
3477                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
3478
3479                 fmIndex = -1; // reset the position
3480
3481                 /* determine the field type and storage attributes */
3482                 type = dbi_result_get_field_type(result, columnName);
3483                 attr = dbi_result_get_field_attribs(result, columnName);
3484
3485                 /* fetch the fieldmapper index */
3486                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
3487                         char* virt = (char*)osrfHashGet(_f, "virtual");
3488                         char* pos = (char*)osrfHashGet(_f, "array_position");
3489
3490                         if ( !virt || !pos || !(strcmp( virt, "true" )) ) continue;
3491
3492                         fmIndex = atoi( pos );
3493                         osrfLogInternal(OSRF_LOG_MARK, "... Found column at position [%s]...", pos);
3494                 } else {
3495                         continue;
3496                 }
3497
3498                 if (dbi_result_field_is_null(result, columnName)) {
3499                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(NULL) );
3500                 } else {
3501
3502                         switch( type ) {
3503
3504                                 case DBI_TYPE_INTEGER :
3505
3506                                         if( attr & DBI_INTEGER_SIZE8 )
3507                                                 jsonObjectSetIndex( object, fmIndex,
3508                                                         jsonNewNumberObject(dbi_result_get_longlong(result, columnName)));
3509                                         else
3510                                                 jsonObjectSetIndex( object, fmIndex,
3511                                                         jsonNewNumberObject(dbi_result_get_int(result, columnName)));
3512
3513                                         break;
3514
3515                                 case DBI_TYPE_DECIMAL :
3516                                         jsonObjectSetIndex( object, fmIndex,
3517                                                         jsonNewNumberObject(dbi_result_get_double(result, columnName)));
3518                                         break;
3519
3520                                 case DBI_TYPE_STRING :
3521
3522
3523                                         jsonObjectSetIndex(
3524                                                 object,
3525                                                 fmIndex,
3526                                                 jsonNewObject( dbi_result_get_string(result, columnName) )
3527                                         );
3528
3529                                         break;
3530
3531                                 case DBI_TYPE_DATETIME :
3532
3533                                         memset(dt_string, '\0', sizeof(dt_string));
3534                                         memset(&gmdt, '\0', sizeof(gmdt));
3535
3536                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
3537
3538
3539                                         if (!(attr & DBI_DATETIME_DATE)) {
3540                                                 gmtime_r( &_tmp_dt, &gmdt );
3541                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
3542                                         } else if (!(attr & DBI_DATETIME_TIME)) {
3543                                                 localtime_r( &_tmp_dt, &gmdt );
3544                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
3545                                         } else {
3546                                                 localtime_r( &_tmp_dt, &gmdt );
3547                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
3548                                         }
3549
3550                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(dt_string) );
3551
3552                                         break;
3553
3554                                 case DBI_TYPE_BINARY :
3555                                         osrfLogError( OSRF_LOG_MARK,
3556                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
3557                         }
3558                 }
3559         }
3560
3561         return object;
3562 }
3563
3564 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
3565         if(!result) return jsonNULL;
3566
3567         jsonObject* object = jsonNewObject(NULL);
3568
3569         time_t _tmp_dt;
3570         char dt_string[256];
3571         struct tm gmdt;
3572
3573         int fmIndex;
3574         int columnIndex = 1;
3575         int attr;
3576         unsigned short type;
3577         const char* columnName;
3578
3579         /* cycle through the column list */
3580         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
3581
3582                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
3583
3584                 fmIndex = -1; // reset the position
3585
3586                 /* determine the field type and storage attributes */
3587                 type = dbi_result_get_field_type(result, columnName);
3588                 attr = dbi_result_get_field_attribs(result, columnName);
3589
3590                 if (dbi_result_field_is_null(result, columnName)) {
3591                         jsonObjectSetKey( object, columnName, jsonNewObject(NULL) );
3592                 } else {
3593
3594                         switch( type ) {
3595
3596                                 case DBI_TYPE_INTEGER :
3597
3598                                         if( attr & DBI_INTEGER_SIZE8 )
3599                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_longlong(result, columnName)) );
3600                                         else
3601                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_int(result, columnName)) );
3602                                         break;
3603
3604                                 case DBI_TYPE_DECIMAL :
3605                                         jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_double(result, columnName)) );
3606                                         break;
3607
3608                                 case DBI_TYPE_STRING :
3609                                         jsonObjectSetKey( object, columnName, jsonNewObject(dbi_result_get_string(result, columnName)) );
3610                                         break;
3611
3612                                 case DBI_TYPE_DATETIME :
3613
3614                                         memset(dt_string, '\0', sizeof(dt_string));
3615                                         memset(&gmdt, '\0', sizeof(gmdt));
3616
3617                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
3618
3619
3620                                         if (!(attr & DBI_DATETIME_DATE)) {
3621                                                 gmtime_r( &_tmp_dt, &gmdt );
3622                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
3623                                         } else if (!(attr & DBI_DATETIME_TIME)) {
3624                                                 localtime_r( &_tmp_dt, &gmdt );
3625                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
3626                                         } else {
3627                                                 localtime_r( &_tmp_dt, &gmdt );
3628                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
3629                                         }
3630
3631                                         jsonObjectSetKey( object, columnName, jsonNewObject(dt_string) );
3632                                         break;
3633
3634                                 case DBI_TYPE_BINARY :
3635                                         osrfLogError( OSRF_LOG_MARK,
3636                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
3637                         }
3638                 }
3639         }
3640
3641         return object;
3642 }
3643