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         if (foreign_context->size > 0) {
 895                 osrfStringArray* class_list = osrfHashKeys( foreign_context );
 896
 897             int i = 0;
 898             char* class_name = NULL;
 899                 while ( (class_name = osrfStringArrayGetString(class_list, i++)) ) {
 900                 osrfHash* fcontext = osrfHashGet(foreign_context, class_name);
 901
 902                 jsonObject* _tmp_params = jsonParseStringFmt(
 903                     "{\"%s\":\"%s\"}",
 904                     osrfHashGet(fcontext, "field"),
 905                     oilsFMGetString(param, osrfHashGet(fcontext, "fkey"))
 906                 );
 907
 908                         jsonObject* _list = doFieldmapperSearch(
 909                     ctx,
 910                     class,
 911                     _tmp_params,
 912                     &err
 913                 );
 914
 915
 916                 jsonObject* _fparam = jsonObjectGetIndex(_list, 0);
 917
 918                 if (!_fparam) {
 919                     jsonObjectFree(_tmp_params);
 920                     jsonObjectFree(_list);
 921                     return 0;
 922                 }
 923
 924                 jsonObjectFree(_tmp_params);
 925                 jsonObjectFree(_list);
 926
 927                 char* foreign_field = NULL;
 928                 while ( (foreign_field = osrfStringArrayGetString(osrfHashGet(fcontext,"context"), i++)) ) {
 929                     osrfStringArrayAdd( context_org_array, oilsFMGetString( _fparam, foreign_field ) );
 930                 }
 931
 932                 jsonObjectFree(_fparam);
 933             }
 934
 935             osrfStringArrayFree(class_list);
 936         }
 937
 938         jsonObjectFree(param);
 939     }
 940
 941     char* context_org = NULL;
 942     char* perm = NULL;
 943     int OK = 0;
 944
 945     if (permission->size == 0) OK = 1;
 946
 947     int i = 0;
 948     while ( (perm = osrfStringArrayGetString(permission, i++)) ) {
 949         int j = 0;
 950         while ( (context_org = osrfStringArrayGetString(context_org_array, j++)) ) {
 951             dbi_result result;
 952
 953             if (pkey_value) {
 954                 result = dbi_conn_queryf(
 955                     writehandle,
 956                     "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
 957                     userid,
 958                     perm,
 959                     osrfHashGet(class, "classname"),
 960                     pkey_value,
 961                     atoi(context_org)
 962                 );
 963
 964                 if (result) {
 965                     jsonObject* return_val = oilsMakeJSONFromResult( result );
 966                     char* has_perm = jsonObjectToSimpleString( jsonObjectGetKeyConst(return_val, "has_perm") );
 967                     if ( *has_perm == 't' ) OK = 1;
 968                     free(has_perm);
 969                     jsonObjectFree(return_val);
 970                     dbi_result_free(result);
 971                     break;
 972                 }
 973             }
 974
 975             result = dbi_conn_queryf(
 976                 writehandle,
 977                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
 978                 userid,
 979                 perm,
 980                 atoi(context_org)
 981             );
 982
 983             if (result) {
 984                 jsonObject* return_val = oilsMakeJSONFromResult( result );
 985                 char* has_perm = jsonObjectToSimpleString( jsonObjectGetKeyConst(return_val, "has_perm") );
 986                 if ( *has_perm == 't' ) OK = 1;
 987                 free(has_perm);
 988                 jsonObjectFree(return_val);
 989                 dbi_result_free(result);
 990                 break;
 991             }
 992
 993         }
 994         if (OK) break;
 995     }
 996
 997     if (pkey_value) free(pkey_value);
 998     osrfStringArrayFree(context_org_array);
 999
1000     return OK;
1001 }
1002 #endif
1003
1004
1005 static jsonObject* doCreate(osrfMethodContext* ctx, int* err ) {
1006
1007         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1008 #ifdef PCRUD
1009         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
1010         jsonObject* options = jsonObjectGetIndex( ctx->params, 2 );
1011 #else
1012         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
1013         jsonObject* options = jsonObjectGetIndex( ctx->params, 1 );
1014 #endif
1015
1016         if (!verifyObjectClass(ctx, target)) {
1017                 *err = -1;
1018                 return jsonNULL;
1019         }
1020
1021         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1022
1023         if (!ctx->session || !ctx->session->userData || !osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
1024                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1025
1026                 osrfAppSessionStatus(
1027                         ctx->session,
1028                         OSRF_STATUS_BADREQUEST,
1029                         "osrfMethodException",
1030                         ctx->request,
1031                         "No active transaction -- required for CREATE"
1032                 );
1033                 *err = -1;
1034                 return jsonNULL;
1035         }
1036
1037         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
1038                 osrfAppSessionStatus(
1039                         ctx->session,
1040                         OSRF_STATUS_BADREQUEST,
1041                         "osrfMethodException",
1042                         ctx->request,
1043                         "Cannot INSERT readonly class"
1044                 );
1045                 *err = -1;
1046                 return jsonNULL;
1047         }
1048
1049
1050         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
1051
1052         // Set the last_xact_id
1053         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
1054         if (index > -1) {
1055                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
1056                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
1057         }
1058
1059         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
1060
1061         dbhandle = writehandle;
1062
1063         osrfHash* fields = osrfHashGet(meta, "fields");
1064         char* pkey = osrfHashGet(meta, "primarykey");
1065         char* seq = osrfHashGet(meta, "sequence");
1066
1067         growing_buffer* table_buf = buffer_init(128);
1068         growing_buffer* col_buf = buffer_init(128);
1069         growing_buffer* val_buf = buffer_init(128);
1070
1071         buffer_fadd(table_buf,"INSERT INTO %s", osrfHashGet(meta, "tablename"));
1072         buffer_add(col_buf,"(");
1073         buffer_add(val_buf,"VALUES (");
1074
1075
1076         int i = 0;
1077         int first = 1;
1078         char* field_name;
1079         osrfStringArray* field_list = osrfHashKeys( fields );
1080         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
1081
1082                 osrfHash* field = osrfHashGet( fields, field_name );
1083
1084                 if(!( strcmp( osrfHashGet(osrfHashGet(fields,field_name), "virtual"), "true" ) )) continue;
1085
1086                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
1087
1088                 char* value;
1089                 if (field_object && field_object->classname) {
1090                         value = oilsFMGetString(
1091                                 field_object,
1092                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
1093                         );
1094                 } else {
1095                         value = jsonObjectToSimpleString( field_object );
1096                 }
1097
1098
1099                 if (first) {
1100                         first = 0;
1101                 } else {
1102                         buffer_add(col_buf, ",");
1103                         buffer_add(val_buf, ",");
1104                 }
1105
1106                 buffer_add(col_buf, field_name);
1107
1108                 if (!field_object || field_object->type == JSON_NULL) {
1109                         buffer_add( val_buf, "DEFAULT" );
1110
1111                 } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1112                         if ( !strcmp(osrfHashGet(field, "datatype"), "INT8") ) {
1113                                 buffer_fadd( val_buf, "%lld", atoll(value) );
1114
1115                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "INT") ) {
1116                                 buffer_fadd( val_buf, "%d", atoi(value) );
1117
1118                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
1119                                 buffer_fadd( val_buf, "%f", atof(value) );
1120                         }
1121                 } else {
1122                         if ( dbi_conn_quote_string(writehandle, &value) ) {
1123                                 buffer_fadd( val_buf, "%s", value );
1124
1125                         } else {
1126                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
1127                                 osrfAppSessionStatus(
1128                                         ctx->session,
1129                                         OSRF_STATUS_INTERNALSERVERERROR,
1130                                         "osrfMethodException",
1131                                         ctx->request,
1132                                         "Error quoting string -- please see the error log for more details"
1133                                 );
1134                                 free(value);
1135                                 buffer_free(table_buf);
1136                                 buffer_free(col_buf);
1137                                 buffer_free(val_buf);
1138                                 *err = -1;
1139                                 return jsonNULL;
1140                         }
1141                 }
1142
1143                 free(value);
1144
1145         }
1146
1147
1148         buffer_add(col_buf,")");
1149         buffer_add(val_buf,")");
1150
1151         char* table_str = buffer_release(table_buf);
1152         char* col_str   = buffer_release(col_buf);
1153         char* val_str   = buffer_release(val_buf);
1154         growing_buffer* sql = buffer_init(128);
1155         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
1156         free(table_str);
1157         free(col_str);
1158         free(val_str);
1159
1160         char* query = buffer_release(sql);
1161
1162         osrfLogDebug(OSRF_LOG_MARK, "%s: Insert SQL [%s]", MODULENAME, query);
1163
1164
1165         dbi_result result = dbi_conn_query(writehandle, query);
1166
1167         jsonObject* obj = NULL;
1168
1169         if (!result) {
1170                 obj = jsonNewObject(NULL);
1171                 osrfLogError(
1172                         OSRF_LOG_MARK,
1173                         "%s ERROR inserting %s object using query [%s]",
1174                         MODULENAME,
1175                         osrfHashGet(meta, "fieldmapper"),
1176                         query
1177                 );
1178                 osrfAppSessionStatus(
1179                         ctx->session,
1180                         OSRF_STATUS_INTERNALSERVERERROR,
1181                         "osrfMethodException",
1182                         ctx->request,
1183                         "INSERT error -- please see the error log for more details"
1184                 );
1185                 *err = -1;
1186         } else {
1187
1188                 char* id = oilsFMGetString(target, pkey);
1189                 if (!id) {
1190                         unsigned long long new_id = dbi_conn_sequence_last(writehandle, seq);
1191                         growing_buffer* _id = buffer_init(10);
1192                         buffer_fadd(_id, "%lld", new_id);
1193                         id = buffer_release(_id);
1194                 }
1195
1196                 // Find quietness specification, if present
1197                 char* quiet_str = NULL;
1198                 if ( options ) {
1199                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
1200                         if( quiet_obj )
1201                                 quiet_str = jsonObjectToSimpleString( quiet_obj );
1202                 }
1203
1204                 if( quiet_str && !strcmp( quiet_str, "true" )) {  // if quietness is specified
1205                         obj = jsonNewObject(id);
1206                 }
1207                 else {
1208
1209                         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1210                         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1211
1212                         jsonObjectSetKey(
1213                                 jsonObjectGetIndex(fake_params, 0),
1214                                 pkey,
1215                                 jsonNewObject(id)
1216                         );
1217
1218                         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1219
1220                         if(*err) {
1221                                 jsonObjectFree( fake_params );
1222                                 obj = jsonNULL;
1223                         } else {
1224                                 obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1225                         }
1226
1227                         jsonObjectFree( list );
1228                         jsonObjectFree( fake_params );
1229                 }
1230
1231                 if(quiet_str) free(quiet_str);
1232                 free(id);
1233         }
1234
1235         free(query);
1236
1237         return obj;
1238
1239 }
1240
1241
1242 static jsonObject* doRetrieve(osrfMethodContext* ctx, int* err ) {
1243
1244     int id_pos = 0;
1245     int order_pos = 1;
1246
1247 #ifdef PCRUD
1248     id_pos = 1;
1249     order_pos = 2;
1250 #endif
1251
1252         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1253
1254         jsonObject* obj;
1255
1256         char* id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, id_pos));
1257         jsonObject* order_hash = jsonObjectGetIndex(ctx->params, order_pos);
1258
1259         osrfLogDebug(
1260                 OSRF_LOG_MARK,
1261                 "%s retrieving %s object with primary key value of %s",
1262                 MODULENAME,
1263                 osrfHashGet(meta, "fieldmapper"),
1264                 id
1265         );
1266
1267         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1268         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1269
1270         jsonObjectSetKey(
1271                 jsonObjectGetIndex(fake_params, 0),
1272                 osrfHashGet(meta, "primarykey"),
1273                 jsonParseString(id)
1274         );
1275
1276         free(id);
1277
1278         if (order_hash) jsonObjectPush(fake_params, jsonObjectClone(order_hash) );
1279
1280         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1281
1282         if(*err) {
1283                 jsonObjectFree( fake_params );
1284                 return jsonNULL;
1285         }
1286
1287         obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1288
1289         jsonObjectFree( list );
1290         jsonObjectFree( fake_params );
1291
1292 #ifdef PCRUD
1293         if(!verifyObjectPCRUD(ctx, obj)) {
1294         jsonObjectFree(obj);
1295                 return jsonNULL;
1296         }
1297 #endif
1298
1299         return obj;
1300 }
1301
1302 static char* jsonNumberToDBString ( osrfHash* field, const jsonObject* value ) {
1303         growing_buffer* val_buf = buffer_init(32);
1304
1305         if ( !strncmp(osrfHashGet(field, "datatype"), "INT", (size_t)3) ) {
1306                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
1307                 else {
1308                         char* val_str = jsonObjectToSimpleString(value);
1309                         buffer_fadd( val_buf, "%ld", atol(val_str) );
1310                         free(val_str);
1311                 }
1312
1313         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
1314                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%f",  jsonObjectGetNumber(value) );
1315                 else {
1316                         char* val_str = jsonObjectToSimpleString(value);
1317                         buffer_fadd( val_buf, "%f", atof(val_str) );
1318                         free(val_str);
1319                 }
1320         }
1321
1322         return buffer_release(val_buf);
1323 }
1324
1325 static char* searchINPredicate (const char* class, osrfHash* field,
1326                 const jsonObject* node, const char* op) {
1327         growing_buffer* sql_buf = buffer_init(32);
1328
1329         buffer_fadd(
1330                 sql_buf,
1331                 "\"%s\".%s ",
1332                 class,
1333                 osrfHashGet(field, "name")
1334         );
1335
1336         if (!op) {
1337                 buffer_add(sql_buf, "IN (");
1338         } else if (!(strcasecmp(op,"not in"))) {
1339                 buffer_add(sql_buf, "NOT IN (");
1340         } else {
1341                 buffer_add(sql_buf, "IN (");
1342         }
1343
1344         int in_item_index = 0;
1345         int in_item_first = 1;
1346         jsonObject* in_item;
1347         while ( (in_item = jsonObjectGetIndex(node, in_item_index++)) ) {
1348
1349                 if (in_item_first)
1350                         in_item_first = 0;
1351                 else
1352                         buffer_add(sql_buf, ", ");
1353
1354                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1355                         char* val = jsonNumberToDBString( field, in_item );
1356                         buffer_fadd( sql_buf, "%s", val );
1357                         free(val);
1358
1359                 } else {
1360                         char* key_string = jsonObjectToSimpleString(in_item);
1361                         if ( dbi_conn_quote_string(dbhandle, &key_string) ) {
1362                                 buffer_fadd( sql_buf, "%s", key_string );
1363                                 free(key_string);
1364                         } else {
1365                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, key_string);
1366                                 free(key_string);
1367                                 buffer_free(sql_buf);
1368                                 return NULL;
1369                         }
1370                 }
1371         }
1372
1373         buffer_add(
1374                 sql_buf,
1375                 ")"
1376         );
1377
1378         return buffer_release(sql_buf);
1379 }
1380
1381 static char* searchValueTransform( const jsonObject* array ) {
1382         growing_buffer* sql_buf = buffer_init(32);
1383
1384         char* val = NULL;
1385         int func_item_index = 0;
1386         int func_item_first = 2;
1387         jsonObject* func_item;
1388         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1389
1390                 val = jsonObjectToSimpleString(func_item);
1391
1392                 if (func_item_first == 2) {
1393                         buffer_fadd(sql_buf, "%s( ", val);
1394                         free(val);
1395                         func_item_first--;
1396                         continue;
1397                 }
1398
1399                 if (func_item_first)
1400                         func_item_first--;
1401                 else
1402                         buffer_add(sql_buf, ", ");
1403
1404                 if (func_item->type == JSON_NULL) {
1405                         buffer_add( sql_buf, "NULL" );
1406                 } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
1407                         buffer_fadd( sql_buf, "%s", val );
1408                 } else {
1409                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1410                         free(val);
1411                         buffer_free(sql_buf);
1412                         return NULL;
1413                 }
1414
1415                 free(val);
1416         }
1417
1418         buffer_add(
1419                 sql_buf,
1420                 " )"
1421         );
1422
1423         return buffer_release(sql_buf);
1424 }
1425
1426 static char* searchFunctionPredicate (const char* class, osrfHash* field,
1427                 const jsonObject* node, const char* node_key) {
1428         growing_buffer* sql_buf = buffer_init(32);
1429
1430         char* val = searchValueTransform(node);
1431
1432         buffer_fadd(
1433                 sql_buf,
1434                 "\"%s\".%s %s %s",
1435                 class,
1436                 osrfHashGet(field, "name"),
1437                 node_key,
1438                 val
1439         );
1440
1441         free(val);
1442
1443         return buffer_release(sql_buf);
1444 }
1445
1446 static char* searchFieldTransform (const char* class, osrfHash* field, const jsonObject* node) {
1447         growing_buffer* sql_buf = buffer_init(32);
1448
1449         char* field_transform = jsonObjectToSimpleString( jsonObjectGetKeyConst( node, "transform" ) );
1450         char* transform_subcolumn = jsonObjectToSimpleString( jsonObjectGetKeyConst( node, "result_field" ) );
1451
1452         if (field_transform) {
1453                 buffer_fadd( sql_buf, "%s(\"%s\".%s", field_transform, class, osrfHashGet(field, "name"));
1454             const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
1455
1456         if (array) {
1457                 int func_item_index = 0;
1458                 jsonObject* func_item;
1459                 while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1460
1461                         char* val = jsonObjectToSimpleString(func_item);
1462
1463                     if ( !val ) {
1464                             buffer_add( sql_buf, ",NULL" );
1465                     } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
1466                             buffer_fadd( sql_buf, ",%s", val );
1467                         } else {
1468                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1469                             free(field_transform);
1470                                         free(val);
1471                                 buffer_free(sql_buf);
1472                                 return NULL;
1473                 }
1474                                 free(val);
1475                         }
1476
1477         }
1478
1479         buffer_add(
1480                 sql_buf,
1481                 " )"
1482         );
1483
1484         } else {
1485                 buffer_fadd( sql_buf, "\"%s\".%s", class, osrfHashGet(field, "name"));
1486         }
1487
1488     if (transform_subcolumn) {
1489         char * tmp = buffer_release(sql_buf);
1490         sql_buf = buffer_init(32);
1491         buffer_fadd(
1492             sql_buf,
1493             "(%s).\"%s\"",
1494             tmp,
1495             transform_subcolumn
1496         );
1497         free(tmp);
1498     }
1499
1500         if (field_transform) free(field_transform);
1501         if (transform_subcolumn) free(transform_subcolumn);
1502
1503         return buffer_release(sql_buf);
1504 }
1505
1506 static char* searchFieldTransformPredicate (const char* class, osrfHash* field, jsonObject* node, const char* node_key) {
1507         char* field_transform = searchFieldTransform( class, field, node );
1508         char* value = NULL;
1509
1510         if (!jsonObjectGetKeyConst( node, "value" )) {
1511                 value = searchWHERE( node, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1512         } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_ARRAY) {
1513                 value = searchValueTransform(jsonObjectGetKeyConst( node, "value" ));
1514         } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_HASH) {
1515                 value = searchWHERE( jsonObjectGetKeyConst( node, "value" ), osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1516         } else if (jsonObjectGetKeyConst( node, "value" )->type != JSON_NULL) {
1517                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1518                         value = jsonNumberToDBString( field, jsonObjectGetKeyConst( node, "value" ) );
1519                 } else {
1520                         value = jsonObjectToSimpleString(jsonObjectGetKeyConst( node, "value" ));
1521                         if ( !dbi_conn_quote_string(dbhandle, &value) ) {
1522                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, value);
1523                                 free(value);
1524                                 free(field_transform);
1525                                 return NULL;
1526                         }
1527                 }
1528         }
1529
1530         growing_buffer* sql_buf = buffer_init(32);
1531
1532         buffer_fadd(
1533                 sql_buf,
1534                 "%s %s %s",
1535                 field_transform,
1536                 node_key,
1537                 value
1538         );
1539
1540         free(value);
1541         free(field_transform);
1542
1543         return buffer_release(sql_buf);
1544 }
1545
1546 static char* searchSimplePredicate (const char* orig_op, const char* class,
1547                 osrfHash* field, const jsonObject* node) {
1548
1549         char* val = NULL;
1550
1551         if (node->type != JSON_NULL) {
1552                 if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1553                         val = jsonNumberToDBString( field, node );
1554                 } else {
1555                         val = jsonObjectToSimpleString(node);
1556                 }
1557         }
1558
1559         char* pred = searchWriteSimplePredicate( class, field, osrfHashGet(field, "name"), orig_op, val );
1560
1561         if (val) free(val);
1562
1563         return pred;
1564 }
1565
1566 static char* searchWriteSimplePredicate ( const char* class, osrfHash* field,
1567         const char* left, const char* orig_op, const char* right ) {
1568
1569         char* val = NULL;
1570         char* op = NULL;
1571         if (right == NULL) {
1572                 val = strdup("NULL");
1573
1574                 if (strcmp( orig_op, "=" ))
1575                         op = strdup("IS NOT");
1576                 else
1577                         op = strdup("IS");
1578
1579         } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1580                 val = strdup(right);
1581                 op = strdup(orig_op);
1582
1583         } else {
1584                 val = strdup(right);
1585                 if ( !dbi_conn_quote_string(dbhandle, &val) ) {
1586                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1587                         free(val);
1588                         return NULL;
1589                 }
1590                 op = strdup(orig_op);
1591         }
1592
1593         growing_buffer* sql_buf = buffer_init(16);
1594         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class, left, op, val );
1595         free(val);
1596         free(op);
1597
1598         return buffer_release(sql_buf);
1599 }
1600
1601 static char* searchBETWEENPredicate (const char* class, osrfHash* field, jsonObject* node) {
1602
1603         char* x_string;
1604         char* y_string;
1605
1606         if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
1607                 x_string = jsonNumberToDBString(field, jsonObjectGetIndex(node,0));
1608                 y_string = jsonNumberToDBString(field, jsonObjectGetIndex(node,1));
1609
1610         } else {
1611                 x_string = jsonObjectToSimpleString(jsonObjectGetIndex(node,0));
1612                 y_string = jsonObjectToSimpleString(jsonObjectGetIndex(node,1));
1613                 if ( !(dbi_conn_quote_string(dbhandle, &x_string) && dbi_conn_quote_string(dbhandle, &y_string)) ) {
1614                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]", MODULENAME, x_string, y_string);
1615                         free(x_string);
1616                         free(y_string);
1617                         return NULL;
1618                 }
1619         }
1620
1621         growing_buffer* sql_buf = buffer_init(32);
1622         buffer_fadd( sql_buf, "%s BETWEEN %s AND %s", osrfHashGet(field, "name"), x_string, y_string );
1623         free(x_string);
1624         free(y_string);
1625
1626         return buffer_release(sql_buf);
1627 }
1628
1629 static char* searchPredicate ( const char* class, osrfHash* field, jsonObject* node ) {
1630
1631         char* pred = NULL;
1632         if (node->type == JSON_ARRAY) { // equality IN search
1633                 pred = searchINPredicate( class, field, node, NULL );
1634         } else if (node->type == JSON_HASH) { // non-equality search
1635                 jsonObject* pred_node;
1636                 jsonIterator* pred_itr = jsonNewIterator( node );
1637                 while ( (pred_node = jsonIteratorNext( pred_itr )) ) {
1638                         if ( !(strcasecmp( pred_itr->key,"between" )) )
1639                                 pred = searchBETWEENPredicate( class, field, pred_node );
1640                         else if ( !(strcasecmp( pred_itr->key,"in" )) || !(strcasecmp( pred_itr->key,"not in" )) )
1641                                 pred = searchINPredicate( class, field, pred_node, pred_itr->key );
1642                         else if ( pred_node->type == JSON_ARRAY )
1643                                 pred = searchFunctionPredicate( class, field, pred_node, pred_itr->key );
1644                         else if ( pred_node->type == JSON_HASH )
1645                                 pred = searchFieldTransformPredicate( class, field, pred_node, pred_itr->key );
1646                         else
1647                                 pred = searchSimplePredicate( pred_itr->key, class, field, pred_node );
1648
1649                         break;
1650                 }
1651         jsonIteratorFree(pred_itr);
1652         } else if (node->type == JSON_NULL) { // IS NULL search
1653                 growing_buffer* _p = buffer_init(64);
1654                 buffer_fadd(
1655                         _p,
1656                         "\"%s\".%s IS NULL",
1657                         class,
1658                         osrfHashGet(field, "name")
1659                 );
1660                 pred = buffer_release(_p);
1661         } else { // equality search
1662                 pred = searchSimplePredicate( "=", class, field, node );
1663         }
1664
1665         return pred;
1666
1667 }
1668
1669
1670 /*
1671
1672 join : {
1673         acn : {
1674                 field : record,
1675                 fkey : id
1676                 type : left
1677                 filter_op : or
1678                 filter : { ... },
1679                 join : {
1680                         acp : {
1681                                 field : call_number,
1682                                 fkey : id,
1683                                 filter : { ... },
1684                         },
1685                 },
1686         },
1687         mrd : {
1688                 field : record,
1689                 type : inner
1690                 fkey : id,
1691                 filter : { ... },
1692         }
1693 }
1694
1695 */
1696
1697 static char* searchJOIN ( const jsonObject* join_hash, osrfHash* leftmeta ) {
1698
1699         const jsonObject* working_hash;
1700         jsonObject* freeable_hash = NULL;
1701
1702         if (join_hash->type == JSON_STRING) {
1703                 // create a wrapper around a copy of the original
1704                 char* _tmp = jsonObjectToSimpleString( join_hash );
1705                 freeable_hash = jsonNewObjectType(JSON_HASH);
1706                 jsonObjectSetKey(freeable_hash, _tmp, NULL);
1707                 free(_tmp);
1708                 working_hash = freeable_hash;
1709         }
1710         else
1711                 working_hash = join_hash;
1712
1713         growing_buffer* join_buf = buffer_init(128);
1714         char* leftclass = osrfHashGet(leftmeta, "classname");
1715
1716         jsonObject* snode = NULL;
1717         jsonIterator* search_itr = jsonNewIterator( working_hash );
1718         if(freeable_hash)
1719                 jsonObjectFree(freeable_hash);
1720
1721         while ( (snode = jsonIteratorNext( search_itr )) ) {
1722                 osrfHash* idlClass = osrfHashGet( oilsIDL(), search_itr->key );
1723
1724                 char* class = osrfHashGet(idlClass, "classname");
1725
1726                 char* fkey = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "fkey" ) );
1727                 char* field = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "field" ) );
1728
1729                 if (field && !fkey) {
1730                         fkey = (char*)oilsIDLFindPath("/%s/links/%s/key", class, field);
1731                         if (!fkey) {
1732                                 osrfLogError(
1733                                         OSRF_LOG_MARK,
1734                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
1735                                         MODULENAME,
1736                                         class,
1737                                         field,
1738                                         leftclass
1739                                 );
1740                                 buffer_free(join_buf);
1741                                 free(field);
1742                                 jsonIteratorFree(search_itr);
1743                                 return NULL;
1744                         }
1745                         fkey = strdup( fkey );
1746
1747                 } else if (!field && fkey) {
1748                         field = (char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey );
1749                         if (!field) {
1750                                 osrfLogError(
1751                                         OSRF_LOG_MARK,
1752                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
1753                                         MODULENAME,
1754                                         leftclass,
1755                                         fkey,
1756                                         class
1757                                 );
1758                                 buffer_free(join_buf);
1759                                 free(fkey);
1760                                 jsonIteratorFree(search_itr);
1761                                 return NULL;
1762                         }
1763                         field = strdup( field );
1764
1765                 } else if (!field && !fkey) {
1766                         osrfHash* _links = oilsIDLFindPath("/%s/links", leftclass);
1767
1768                         int i = 0;
1769                         osrfStringArray* keys = osrfHashKeys( _links );
1770                         while ( (fkey = osrfStringArrayGetString(keys, i++)) ) {
1771                                 fkey = strdup(osrfStringArrayGetString(keys, i++));
1772                                 if ( !strcmp( (char*)oilsIDLFindPath("/%s/links/%s/class", leftclass, fkey), class) ) {
1773                                         field = strdup( (char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey) );
1774                                         break;
1775                                 } else {
1776                                         free(fkey);
1777                                 }
1778                         }
1779                         osrfStringArrayFree(keys);
1780
1781                         if (!field && !fkey) {
1782                                 _links = oilsIDLFindPath("/%s/links", class);
1783
1784                                 i = 0;
1785                                 keys = osrfHashKeys( _links );
1786                                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
1787                                         field = strdup(osrfStringArrayGetString(keys, i++));
1788                                         if ( !strcmp( (char*)oilsIDLFindPath("/%s/links/%s/class", class, field), class) ) {
1789                                                 fkey = strdup( (char*)oilsIDLFindPath("/%s/links/%s/key", class, field) );
1790                                                 break;
1791                                         } else {
1792                                                 free(field);
1793                                         }
1794                                 }
1795                                 osrfStringArrayFree(keys);
1796                         }
1797
1798                         if (!field && !fkey) {
1799                                 osrfLogError(
1800                                         OSRF_LOG_MARK,
1801                                         "%s: JOIN failed.  No link defined between %s and %s",
1802                                         MODULENAME,
1803                                         leftclass,
1804                                         class
1805                                 );
1806                                 buffer_free(join_buf);
1807                                 jsonIteratorFree(search_itr);
1808                                 return NULL;
1809                         }
1810
1811                 }
1812
1813                 char* type = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "type" ) );
1814                 if (type) {
1815                         if ( !strcasecmp(type,"left") ) {
1816                                 buffer_add(join_buf, " LEFT JOIN");
1817                         } else if ( !strcasecmp(type,"right") ) {
1818                                 buffer_add(join_buf, " RIGHT JOIN");
1819                         } else if ( !strcasecmp(type,"full") ) {
1820                                 buffer_add(join_buf, " FULL JOIN");
1821                         } else {
1822                                 buffer_add(join_buf, " INNER JOIN");
1823                         }
1824                 } else {
1825                         buffer_add(join_buf, " INNER JOIN");
1826                 }
1827                 free(type);
1828
1829                 char* table = getSourceDefinition(idlClass);
1830                 buffer_fadd(join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s", table, class, class, field, leftclass, fkey);
1831                 free(table);
1832
1833                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
1834                 if (filter) {
1835                         char* filter_op = jsonObjectToSimpleString( jsonObjectGetKeyConst( snode, "filter_op" ) );
1836                         if (filter_op) {
1837                                 if (!strcasecmp("or",filter_op)) {
1838                                         buffer_add( join_buf, " OR " );
1839                                 } else {
1840                                         buffer_add( join_buf, " AND " );
1841                                 }
1842                         } else {
1843                                 buffer_add( join_buf, " AND " );
1844                         }
1845
1846                         char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN, NULL );
1847                         buffer_fadd( join_buf, " %s", jpred );
1848                         free(jpred);
1849                         free(filter_op);
1850                 }
1851
1852                 buffer_add(join_buf, " ) ");
1853
1854                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
1855                 if (join_filter) {
1856                         char* jpred = searchJOIN( join_filter, idlClass );
1857                         buffer_fadd( join_buf, " %s", jpred );
1858                         free(jpred);
1859                 }
1860
1861                 free(fkey);
1862                 free(field);
1863         }
1864
1865     jsonIteratorFree(search_itr);
1866
1867         return buffer_release(join_buf);
1868 }
1869
1870 /*
1871
1872 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
1873 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
1874 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
1875
1876 */
1877
1878 static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type, osrfMethodContext* ctx ) {
1879
1880         growing_buffer* sql_buf = buffer_init(128);
1881
1882         jsonObject* node = NULL;
1883
1884     int first = 1;
1885     if ( search_hash->type == JSON_ARRAY ) {
1886         jsonIterator* search_itr = jsonNewIterator( search_hash );
1887         while ( (node = jsonIteratorNext( search_itr )) ) {
1888             if (first) {
1889                 first = 0;
1890             } else {
1891                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
1892                 else buffer_add(sql_buf, " AND ");
1893             }
1894
1895             char* subpred = searchWHERE( node, meta, opjoin_type, ctx );
1896             buffer_fadd(sql_buf, "( %s )", subpred);
1897             free(subpred);
1898         }
1899         jsonIteratorFree(search_itr);
1900
1901     } else if ( search_hash->type == JSON_HASH ) {
1902         jsonIterator* search_itr = jsonNewIterator( search_hash );
1903         while ( (node = jsonIteratorNext( search_itr )) ) {
1904
1905             if (first) {
1906                 first = 0;
1907             } else {
1908                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
1909                 else buffer_add(sql_buf, " AND ");
1910             }
1911
1912             if ( !strncmp("+",search_itr->key,1) ) {
1913                 if ( node->type == JSON_STRING ) {
1914                     char* subpred = jsonObjectToSimpleString( node );
1915                     buffer_fadd(sql_buf, " \"%s\".%s ", search_itr->key + 1, subpred);
1916                     free(subpred);
1917                 } else {
1918                     char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN, ctx );
1919                     buffer_fadd(sql_buf, "( %s )", subpred);
1920                     free(subpred);
1921                 }
1922             } else if ( !strcasecmp("-or",search_itr->key) ) {
1923                 char* subpred = searchWHERE( node, meta, OR_OP_JOIN, ctx );
1924                 buffer_fadd(sql_buf, "( %s )", subpred);
1925                 free(subpred);
1926             } else if ( !strcasecmp("-and",search_itr->key) ) {
1927                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
1928                 buffer_fadd(sql_buf, "( %s )", subpred);
1929                 free(subpred);
1930             } else if ( !strcasecmp("-exists",search_itr->key) ) {
1931                 char* subpred = SELECT(
1932                     ctx,
1933                     jsonObjectGetKey( node, "select" ),
1934                     jsonObjectGetKey( node, "from" ),
1935                     jsonObjectGetKey( node, "where" ),
1936                     jsonObjectGetKey( node, "having" ),
1937                     jsonObjectGetKey( node, "order_by" ),
1938                     jsonObjectGetKey( node, "limit" ),
1939                     jsonObjectGetKey( node, "offset" ),
1940                     SUBSELECT
1941                 );
1942
1943                 buffer_fadd(sql_buf, "EXISTS ( %s )", subpred);
1944                 free(subpred);
1945             } else if ( !strcasecmp("-not-exists",search_itr->key) ) {
1946                 char* subpred = SELECT(
1947                     ctx,
1948                     jsonObjectGetKey( node, "select" ),
1949                     jsonObjectGetKey( node, "from" ),
1950                     jsonObjectGetKey( node, "where" ),
1951                     jsonObjectGetKey( node, "having" ),
1952                     jsonObjectGetKey( node, "order_by" ),
1953                     jsonObjectGetKey( node, "limit" ),
1954                     jsonObjectGetKey( node, "offset" ),
1955                     SUBSELECT
1956                 );
1957
1958                 buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred);
1959                 free(subpred);
1960             } else {
1961
1962                 char* class = osrfHashGet(meta, "classname");
1963                 osrfHash* fields = osrfHashGet(meta, "fields");
1964                 osrfHash* field = osrfHashGet( fields, search_itr->key );
1965
1966
1967                 if (!field) {
1968                     char* table = getSourceDefinition(meta);
1969                     osrfLogError(
1970                         OSRF_LOG_MARK,
1971                         "%s: Attempt to reference non-existant column %s on %s (%s)",
1972                         MODULENAME,
1973                         search_itr->key,
1974                         table,
1975                         class
1976                     );
1977                     buffer_free(sql_buf);
1978                     free(table);
1979                                         jsonIteratorFree(search_itr);
1980                                         return NULL;
1981                 }
1982
1983                 char* subpred = searchPredicate( class, field, node );
1984                 buffer_add( sql_buf, subpred );
1985                 free(subpred);
1986             }
1987         }
1988             jsonIteratorFree(search_itr);
1989
1990     } else {
1991         // ERROR ... only hash and array allowed at this level
1992         char* predicate_string = jsonObjectToJSON( search_hash );
1993         osrfLogError(
1994             OSRF_LOG_MARK,
1995             "%s: Invalid predicate structure: %s",
1996             MODULENAME,
1997             predicate_string
1998         );
1999         buffer_free(sql_buf);
2000         free(predicate_string);
2001         return NULL;
2002     }
2003
2004
2005         return buffer_release(sql_buf);
2006 }
2007
2008 static char* SELECT (
2009                 /* method context */ osrfMethodContext* ctx,
2010
2011                 /* SELECT   */ jsonObject* selhash,
2012                 /* FROM     */ jsonObject* join_hash,
2013                 /* WHERE    */ jsonObject* search_hash,
2014                 /* HAVING   */ jsonObject* having_hash,
2015                 /* ORDER BY */ jsonObject* order_hash,
2016                 /* LIMIT    */ jsonObject* limit,
2017                 /* OFFSET   */ jsonObject* offset,
2018                 /* flags    */ int flags
2019 ) {
2020         const char* locale = osrf_message_get_last_locale();
2021
2022         // in case we don't get a select list
2023         jsonObject* defaultselhash = NULL;
2024
2025         // general tmp objects
2026         const jsonObject* tmp_const;
2027         jsonObject* _tmp = NULL;
2028         jsonObject* selclass = NULL;
2029         jsonObject* selfield = NULL;
2030         jsonObject* snode = NULL;
2031         jsonObject* onode = NULL;
2032         jsonObject* found = NULL;
2033
2034         char* string = NULL;
2035         int from_function = 0;
2036         int first = 1;
2037         int gfirst = 1;
2038         //int hfirst = 1;
2039
2040         // the core search class
2041         char* core_class = NULL;
2042
2043         // metadata about the core search class
2044         osrfHash* core_meta = NULL;
2045         osrfHash* core_fields = NULL;
2046         osrfHash* idlClass = NULL;
2047
2048         // punt if there's no core class
2049         if (!join_hash || ( join_hash->type == JSON_HASH && !join_hash->size ))
2050                 return NULL;
2051
2052         // get the core class -- the only key of the top level FROM clause, or a string
2053         if (join_hash->type == JSON_HASH) {
2054                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
2055                 snode = jsonIteratorNext( tmp_itr );
2056
2057                 core_class = strdup( tmp_itr->key );
2058                 join_hash = snode;
2059
2060                 jsonIteratorFree( tmp_itr );
2061                 snode = NULL;
2062
2063         } else if (join_hash->type == JSON_ARRAY) {
2064         from_function = 1;
2065         selhash = NULL;
2066
2067         } else if (join_hash->type == JSON_STRING) {
2068                 core_class = jsonObjectToSimpleString( join_hash );
2069                 join_hash = NULL;
2070         }
2071
2072         // punt if we don't know about the core class (and it's not a function)
2073         if (!from_function && !(core_meta = osrfHashGet( oilsIDL(), core_class ))) {
2074                 free(core_class);
2075                 return NULL;
2076         }
2077
2078         // if the select list is empty, or the core class field list is '*',
2079         // build the default select list ...
2080         if (!selhash) {
2081                 selhash = defaultselhash = jsonNewObjectType(JSON_HASH);
2082                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2083         } else if ( (tmp_const = jsonObjectGetKeyConst( selhash, core_class )) && tmp_const->type == JSON_STRING ) {
2084                 char* _x = jsonObjectToSimpleString( tmp_const );
2085                 if (!strncmp( "*", _x, 1 )) {
2086                         jsonObjectRemoveKey( selhash, core_class );
2087                         jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2088                 }
2089                 free(_x);
2090         }
2091
2092         // the query buffer
2093         growing_buffer* sql_buf = buffer_init(128);
2094
2095         // temp buffer for the SELECT list
2096         growing_buffer* select_buf = buffer_init(128);
2097         growing_buffer* order_buf = buffer_init(128);
2098         growing_buffer* group_buf = buffer_init(128);
2099         growing_buffer* having_buf = buffer_init(128);
2100
2101         if (!from_function)
2102         core_fields = osrfHashGet(core_meta, "fields");
2103
2104         // ... and if we /are/ building the default list, do that
2105         if ( (_tmp = jsonObjectGetKey(selhash,core_class)) && !_tmp->size ) {
2106
2107                 int i = 0;
2108                 char* field;
2109
2110         if (!from_function) {
2111                 osrfStringArray* keys = osrfHashKeys( core_fields );
2112                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
2113                         if ( strncasecmp( "true", osrfHashGet( osrfHashGet( core_fields, field ), "virtual" ), 4 ) )
2114                                 jsonObjectPush( _tmp, jsonNewObject( field ) );
2115                 }
2116                 osrfStringArrayFree(keys);
2117         }
2118         }
2119
2120         // Now we build the actual select list
2121         if (!from_function) {
2122             int sel_pos = 1;
2123             jsonObject* is_agg = jsonObjectFindPath(selhash, "//aggregate");
2124             first = 1;
2125             gfirst = 1;
2126             jsonIterator* selclass_itr = jsonNewIterator( selhash );
2127             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {
2128
2129                     // round trip through the idl, just to be safe
2130                     idlClass = osrfHashGet( oilsIDL(), selclass_itr->key );
2131                     if (!idlClass) continue;
2132                     char* cname = osrfHashGet(idlClass, "classname");
2133
2134                     // make sure the target relation is in the join tree
2135                     if (strcmp(core_class,cname)) {
2136                             if (!join_hash) continue;
2137
2138                             if (join_hash->type == JSON_STRING) {
2139                                     string = jsonObjectToSimpleString(join_hash);
2140                                     found = strcmp(string,cname) ? NULL : jsonParseString("{\"1\":\"1\"}");
2141                                     free(string);
2142                             } else {
2143                                     found = jsonObjectFindPath(join_hash, "//%s", cname);
2144                             }
2145
2146                             if (!found->size) {
2147                                     jsonObjectFree(found);
2148                                     continue;
2149                             }
2150
2151                             jsonObjectFree(found);
2152                     }
2153
2154                     // stitch together the column list ...
2155                     jsonIterator* select_itr = jsonNewIterator( selclass );
2156                     while ( (selfield = jsonIteratorNext( select_itr )) ) {
2157
2158                             char* __column = NULL;
2159                             char* __alias = NULL;
2160
2161                             // ... if it's a sstring, just toss it on the pile
2162                             if (selfield->type == JSON_STRING) {
2163
2164                                     // again, just to be safe
2165                                     char* _requested_col = jsonObjectToSimpleString(selfield);
2166                                     osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), _requested_col );
2167                                     free(_requested_col);
2168
2169                                     if (!field) continue;
2170                                     __column = strdup(osrfHashGet(field, "name"));
2171
2172                                     if (first) {
2173                                             first = 0;
2174                                     } else {
2175                                             buffer_add(select_buf, ",");
2176                                     }
2177
2178                     if (locale) {
2179                             char* i18n = osrfHashGet(field, "i18n");
2180                                     if (flags & DISABLE_I18N)
2181                             i18n = NULL;
2182
2183                             if ( i18n && !strncasecmp("true", i18n, 4)) {
2184                                 char* pkey = osrfHashGet(idlClass, "primarykey");
2185                                 char* tname = osrfHashGet(idlClass, "tablename");
2186
2187                             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);
2188                         } else {
2189                                             buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, __column, __column);
2190                         }
2191                     } else {
2192                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, __column, __column);
2193                     }
2194
2195                             // ... but it could be an object, in which case we check for a Field Transform
2196                             } else {
2197
2198                                     __column = jsonObjectToSimpleString( jsonObjectGetKeyConst( selfield, "column" ) );
2199
2200                                     // again, just to be safe
2201                                     osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), __column );
2202                                     if (!field) continue;
2203                                     const char* fname = osrfHashGet(field, "name");
2204
2205                                     if (first) {
2206                                             first = 0;
2207                                     } else {
2208                                             buffer_add(select_buf, ",");
2209                                     }
2210
2211                                     if ((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
2212                                             __alias = jsonObjectToSimpleString( tmp_const );
2213                                     } else {
2214                                             __alias = strdup(__column);
2215                                     }
2216
2217                                     if (jsonObjectGetKeyConst( selfield, "transform" )) {
2218                                             free(__column);
2219                                             __column = searchFieldTransform(cname, field, selfield);
2220                                             buffer_fadd(select_buf, " %s AS \"%s\"", __column, __alias);
2221                                     } else {
2222                         if (locale) {
2223                                     char* i18n = osrfHashGet(field, "i18n");
2224                                         if (flags & DISABLE_I18N)
2225                                 i18n = NULL;
2226
2227                                     if ( i18n && !strncasecmp("true", i18n, 4)) {
2228                                     char* pkey = osrfHashGet(idlClass, "primarykey");
2229                                     char* tname = osrfHashGet(idlClass, "tablename");
2230
2231                                 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);
2232                             } else {
2233                                                     buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, fname, __alias);
2234                             }
2235                         } else {
2236                                                 buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, fname, __alias);
2237                         }
2238                                     }
2239                             }
2240
2241                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
2242
2243                                     if ( !(
2244                             jsonBoolIsTrue( jsonObjectGetKey( selfield, "aggregate" ) ) ||
2245                                 ((int)jsonObjectGetNumber(jsonObjectGetKey( selfield, "aggregate" ))) == 1 // support 1/0 for perl's sake
2246                          )
2247                     ) {
2248                                             if (gfirst) {
2249                                                     gfirst = 0;
2250                                             } else {
2251                                                     buffer_add(group_buf, ",");
2252                                             }
2253
2254                                             buffer_fadd(group_buf, " %d", sel_pos);
2255                                     /*
2256                                     } else if (is_agg = jsonObjectGetKey( selfield, "having" )) {
2257                                             if (gfirst) {
2258                                                     gfirst = 0;
2259                                             } else {
2260                                                     buffer_add(group_buf, ",");
2261                                             }
2262
2263                                             __column = searchFieldTransform(cname, field, selfield);
2264                                             buffer_fadd(group_buf, " %s", __column);
2265                                             __column = searchFieldTransform(cname, field, selfield);
2266                                     */
2267                                     }
2268                             }
2269
2270                             if (__column) free(__column);
2271                             if (__alias) free(__alias);
2272
2273                             sel_pos++;
2274                     }
2275
2276             // jsonIteratorFree(select_itr);
2277             }
2278
2279         // jsonIteratorFree(selclass_itr);
2280
2281             if (is_agg) jsonObjectFree(is_agg);
2282     } else {
2283         buffer_add(select_buf, "*");
2284     }
2285
2286
2287         char* col_list = buffer_release(select_buf);
2288         char* table = NULL;
2289     if (!from_function) table = getSourceDefinition(core_meta);
2290     else table = searchValueTransform(join_hash);
2291
2292         // Put it all together
2293         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
2294         free(col_list);
2295         free(table);
2296
2297     if (!from_function) {
2298             // Now, walk the join tree and add that clause
2299             if ( join_hash ) {
2300                     char* join_clause = searchJOIN( join_hash, core_meta );
2301                     buffer_add(sql_buf, join_clause);
2302                     free(join_clause);
2303             }
2304
2305             if ( search_hash ) {
2306                     buffer_add(sql_buf, " WHERE ");
2307
2308                     // and it's on the the WHERE clause
2309                     char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN, ctx );
2310
2311                     if (!pred) {
2312                             if (ctx) {
2313                                 osrfAppSessionStatus(
2314                                         ctx->session,
2315                                         OSRF_STATUS_INTERNALSERVERERROR,
2316                                         "osrfMethodException",
2317                                         ctx->request,
2318                                         "Severe query error in WHERE predicate -- see error log for more details"
2319                                 );
2320                             }
2321                             free(core_class);
2322                             buffer_free(having_buf);
2323                             buffer_free(group_buf);
2324                             buffer_free(order_buf);
2325                             buffer_free(sql_buf);
2326                             if (defaultselhash) jsonObjectFree(defaultselhash);
2327                             return NULL;
2328                     } else {
2329                             buffer_add(sql_buf, pred);
2330                             free(pred);
2331                     }
2332         }
2333
2334             if ( having_hash ) {
2335                     buffer_add(sql_buf, " HAVING ");
2336
2337                     // and it's on the the WHERE clause
2338                     char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN, ctx );
2339
2340                     if (!pred) {
2341                             if (ctx) {
2342                                 osrfAppSessionStatus(
2343                                         ctx->session,
2344                                         OSRF_STATUS_INTERNALSERVERERROR,
2345                                         "osrfMethodException",
2346                                         ctx->request,
2347                                         "Severe query error in HAVING predicate -- see error log for more details"
2348                                 );
2349                             }
2350                             free(core_class);
2351                             buffer_free(having_buf);
2352                             buffer_free(group_buf);
2353                             buffer_free(order_buf);
2354                             buffer_free(sql_buf);
2355                             if (defaultselhash) jsonObjectFree(defaultselhash);
2356                             return NULL;
2357                     } else {
2358                             buffer_add(sql_buf, pred);
2359                             free(pred);
2360                     }
2361             }
2362
2363             first = 1;
2364             jsonIterator* class_itr = jsonNewIterator( order_hash );
2365             while ( (snode = jsonIteratorNext( class_itr )) ) {
2366
2367                     if (!jsonObjectGetKeyConst(selhash,class_itr->key))
2368                             continue;
2369
2370                     if ( snode->type == JSON_HASH ) {
2371
2372                         jsonIterator* order_itr = jsonNewIterator( snode );
2373                             while ( (onode = jsonIteratorNext( order_itr )) ) {
2374
2375                                     if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
2376                                             continue;
2377
2378                                     char* direction = NULL;
2379                                     if ( onode->type == JSON_HASH ) {
2380                                             if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
2381                                                     string = searchFieldTransform(
2382                                                             class_itr->key,
2383                                                             oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
2384                                                             onode
2385                                                     );
2386                                             } else {
2387                                                     growing_buffer* field_buf = buffer_init(16);
2388                                                     buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
2389                                                     string = buffer_release(field_buf);
2390                                             }
2391
2392                                             if ( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
2393                                                     direction = jsonObjectToSimpleString(tmp_const);
2394                                                     if (!strncasecmp(direction, "d", 1)) {
2395                                                             free(direction);
2396                                                             direction = " DESC";
2397                                                     } else {
2398                                                             free(direction);
2399                                                             direction = " ASC";
2400                                                     }
2401                                             }
2402
2403                                     } else {
2404                                             string = strdup(order_itr->key);
2405                                             direction = jsonObjectToSimpleString(onode);
2406                                             if (!strncasecmp(direction, "d", 1)) {
2407                                                     free(direction);
2408                                                     direction = " DESC";
2409                                             } else {
2410                                                     free(direction);
2411                                                     direction = " ASC";
2412                                             }
2413                                     }
2414
2415                                     if (first) {
2416                                             first = 0;
2417                                     } else {
2418                                             buffer_add(order_buf, ", ");
2419                                     }
2420
2421                                     buffer_add(order_buf, string);
2422                                     free(string);
2423
2424                                     if (direction) {
2425                                             buffer_add(order_buf, direction);
2426                                     }
2427
2428                             }
2429                 // jsonIteratorFree(order_itr);
2430
2431                     } else if ( snode->type == JSON_ARRAY ) {
2432
2433                         jsonIterator* order_itr = jsonNewIterator( snode );
2434                             while ( (onode = jsonIteratorNext( order_itr )) ) {
2435
2436                                     char* _f = jsonObjectToSimpleString( onode );
2437
2438                                     if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, _f))
2439                                             continue;
2440
2441                                     if (first) {
2442                                             first = 0;
2443                                     } else {
2444                                             buffer_add(order_buf, ", ");
2445                                     }
2446
2447                                     buffer_add(order_buf, _f);
2448                                     free(_f);
2449
2450                             }
2451                 // jsonIteratorFree(order_itr);
2452
2453
2454                     // IT'S THE OOOOOOOOOOOLD STYLE!
2455                     } else {
2456                             osrfLogError(OSRF_LOG_MARK, "%s: Possible SQL injection attempt; direct order by is not allowed", MODULENAME);
2457                             if (ctx) {
2458                                 osrfAppSessionStatus(
2459                                         ctx->session,
2460                                         OSRF_STATUS_INTERNALSERVERERROR,
2461                                         "osrfMethodException",
2462                                         ctx->request,
2463                                         "Severe query error -- see error log for more details"
2464                                 );
2465                             }
2466
2467                             free(core_class);
2468                             buffer_free(having_buf);
2469                             buffer_free(group_buf);
2470                             buffer_free(order_buf);
2471                             buffer_free(sql_buf);
2472                             if (defaultselhash) jsonObjectFree(defaultselhash);
2473                             jsonIteratorFree(class_itr);
2474                             return NULL;
2475                     }
2476
2477             }
2478     }
2479
2480     // jsonIteratorFree(class_itr);
2481
2482         string = buffer_release(group_buf);
2483
2484         if (strlen(string)) {
2485                 buffer_fadd(
2486                         sql_buf,
2487                         " GROUP BY %s",
2488                         string
2489                 );
2490         }
2491
2492         free(string);
2493
2494         string = buffer_release(having_buf);
2495
2496         if (strlen(string)) {
2497                 buffer_fadd(
2498                         sql_buf,
2499                         " HAVING %s",
2500                         string
2501                 );
2502         }
2503
2504         free(string);
2505
2506         string = buffer_release(order_buf);
2507
2508         if (strlen(string)) {
2509                 buffer_fadd(
2510                         sql_buf,
2511                         " ORDER BY %s",
2512                         string
2513                 );
2514         }
2515
2516         free(string);
2517
2518         if ( limit ){
2519                 string = jsonObjectToSimpleString(limit);
2520                 buffer_fadd( sql_buf, " LIMIT %d", atoi(string) );
2521                 free(string);
2522         }
2523
2524         if (offset) {
2525                 string = jsonObjectToSimpleString(offset);
2526                 buffer_fadd( sql_buf, " OFFSET %d", atoi(string) );
2527                 free(string);
2528         }
2529
2530         if (!(flags & SUBSELECT)) buffer_add(sql_buf, ";");
2531
2532         free(core_class);
2533         if (defaultselhash) jsonObjectFree(defaultselhash);
2534
2535         return buffer_release(sql_buf);
2536
2537 }
2538
2539 static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrfHash* meta, osrfMethodContext* ctx ) {
2540
2541         const char* locale = osrf_message_get_last_locale();
2542
2543         osrfHash* fields = osrfHashGet(meta, "fields");
2544         char* core_class = osrfHashGet(meta, "classname");
2545
2546         const jsonObject* join_hash = jsonObjectGetKeyConst( order_hash, "join" );
2547
2548         jsonObject* node = NULL;
2549         jsonObject* snode = NULL;
2550         jsonObject* onode = NULL;
2551         const jsonObject* _tmp = NULL;
2552         jsonObject* selhash = NULL;
2553         jsonObject* defaultselhash = NULL;
2554
2555         growing_buffer* sql_buf = buffer_init(128);
2556         growing_buffer* select_buf = buffer_init(128);
2557
2558         if ( !(selhash = jsonObjectGetKey( order_hash, "select" )) ) {
2559                 defaultselhash = jsonNewObjectType(JSON_HASH);
2560                 selhash = defaultselhash;
2561         }
2562
2563         if ( !jsonObjectGetKeyConst(selhash,core_class) ) {
2564                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2565                 jsonObject* flist = jsonObjectGetKey( selhash, core_class );
2566
2567                 int i = 0;
2568                 char* field;
2569
2570                 osrfStringArray* keys = osrfHashKeys( fields );
2571                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
2572                         if ( strcasecmp( "true", osrfHashGet( osrfHashGet( fields, field ), "virtual" ) ) )
2573                                 jsonObjectPush( flist, jsonNewObject( field ) );
2574                 }
2575                 osrfStringArrayFree(keys);
2576         }
2577
2578         int first = 1;
2579         jsonIterator* class_itr = jsonNewIterator( selhash );
2580         while ( (snode = jsonIteratorNext( class_itr )) ) {
2581
2582                 osrfHash* idlClass = osrfHashGet( oilsIDL(), class_itr->key );
2583                 if (!idlClass) continue;
2584                 char* cname = osrfHashGet(idlClass, "classname");
2585
2586                 if (strcmp(core_class,class_itr->key)) {
2587                         if (!join_hash) continue;
2588
2589                         jsonObject* found =  jsonObjectFindPath(join_hash, "//%s", class_itr->key);
2590                         if (!found->size) {
2591                                 jsonObjectFree(found);
2592                                 continue;
2593                         }
2594
2595                         jsonObjectFree(found);
2596                 }
2597
2598                 jsonIterator* select_itr = jsonNewIterator( snode );
2599                 while ( (node = jsonIteratorNext( select_itr )) ) {
2600                         char* item_str = jsonObjectToSimpleString(node);
2601                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
2602                         free(item_str);
2603                         char* fname = osrfHashGet(field, "name");
2604
2605                         if (!field) continue;
2606
2607                         if (first) {
2608                                 first = 0;
2609                         } else {
2610                                 buffer_add(select_buf, ",");
2611                         }
2612
2613             if (locale) {
2614                         char* i18n = osrfHashGet(field, "i18n");
2615                             if ( !(
2616                         jsonBoolIsTrue( jsonObjectGetKey( order_hash, "no_i18n" ) ) ||
2617                         ((int)jsonObjectGetNumber(jsonObjectGetKey( order_hash, "no_i18n" ))) == 1 // support 1/0 for perl's sake
2618                      )
2619                 ) i18n = NULL;
2620
2621                         if ( i18n && !strncasecmp("true", i18n, 4)) {
2622                         char* pkey = osrfHashGet(idlClass, "primarykey");
2623                         char* tname = osrfHashGet(idlClass, "tablename");
2624
2625                     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);
2626                 } else {
2627                                 buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
2628                 }
2629             } else {
2630                             buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
2631             }
2632                 }
2633
2634         jsonIteratorFree(select_itr);
2635         }
2636
2637     jsonIteratorFree(class_itr);
2638
2639         char* col_list = buffer_release(select_buf);
2640         char* table = getSourceDefinition(meta);
2641
2642         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
2643         free(col_list);
2644         free(table);
2645
2646         if ( join_hash ) {
2647                 char* join_clause = searchJOIN( join_hash, meta );
2648                 buffer_fadd(sql_buf, " %s", join_clause);
2649                 free(join_clause);
2650         }
2651
2652         char* tmpsql = buffer_data(sql_buf);
2653         osrfLogDebug(OSRF_LOG_MARK, "%s pre-predicate SQL =  %s", MODULENAME, tmpsql);
2654         free(tmpsql);
2655
2656         buffer_add(sql_buf, " WHERE ");
2657
2658         char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN, ctx );
2659         if (!pred) {
2660                 osrfAppSessionStatus(
2661                         ctx->session,
2662                         OSRF_STATUS_INTERNALSERVERERROR,
2663                                 "osrfMethodException",
2664                                 ctx->request,
2665                                 "Severe query error -- see error log for more details"
2666                         );
2667                 buffer_free(sql_buf);
2668                 if(defaultselhash) jsonObjectFree(defaultselhash);
2669                 return NULL;
2670         } else {
2671                 buffer_add(sql_buf, pred);
2672                 free(pred);
2673         }
2674
2675         if (order_hash) {
2676                 char* string = NULL;
2677                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "order_by" )) ){
2678
2679                         growing_buffer* order_buf = buffer_init(128);
2680
2681                         first = 1;
2682                         jsonIterator* class_itr = jsonNewIterator( _tmp );
2683                         while ( (snode = jsonIteratorNext( class_itr )) ) {
2684
2685                                 if (!jsonObjectGetKeyConst(selhash,class_itr->key))
2686                                         continue;
2687
2688                                 if ( snode->type == JSON_HASH ) {
2689
2690                                         jsonIterator* order_itr = jsonNewIterator( snode );
2691                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
2692
2693                                                 if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
2694                                                         continue;
2695
2696                                                 char* direction = NULL;
2697                                                 if ( onode->type == JSON_HASH ) {
2698                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
2699                                                                 string = searchFieldTransform(
2700                                                                         class_itr->key,
2701                                                                         oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
2702                                                                         onode
2703                                                                 );
2704                                                         } else {
2705                                                                 growing_buffer* field_buf = buffer_init(16);
2706                                                                 buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
2707                                                                 string = buffer_release(field_buf);
2708                                                         }
2709
2710                                                         if ( (_tmp = jsonObjectGetKeyConst( onode, "direction" )) ) {
2711                                                                 direction = jsonObjectToSimpleString(_tmp);
2712                                                                 if (!strncasecmp(direction, "d", 1)) {
2713                                                                         free(direction);
2714                                                                         direction = " DESC";
2715                                                                 } else {
2716                                                                         free(direction);
2717                                                                         direction = " ASC";
2718                                                                 }
2719                                                         }
2720
2721                                                 } else {
2722                                                         string = strdup(order_itr->key);
2723                                                         direction = jsonObjectToSimpleString(onode);
2724                                                         if (!strncasecmp(direction, "d", 1)) {
2725                                                                 free(direction);
2726                                                                 direction = " DESC";
2727                                                         } else {
2728                                                                 free(direction);
2729                                                                 direction = " ASC";
2730                                                         }
2731                                                 }
2732
2733                                                 if (first) {
2734                                                         first = 0;
2735                                                 } else {
2736                                                         buffer_add(order_buf, ", ");
2737                                                 }
2738
2739                                                 buffer_add(order_buf, string);
2740                                                 free(string);
2741
2742                                                 if (direction) {
2743                                                         buffer_add(order_buf, direction);
2744                                                 }
2745
2746                                         }
2747
2748                     jsonIteratorFree(order_itr);
2749
2750                                 } else {
2751                                         string = jsonObjectToSimpleString(snode);
2752                                         buffer_add(order_buf, string);
2753                                         free(string);
2754                                         break;
2755                                 }
2756
2757                         }
2758
2759             jsonIteratorFree(class_itr);
2760
2761                         string = buffer_release(order_buf);
2762
2763                         if (strlen(string)) {
2764                                 buffer_fadd(
2765                                         sql_buf,
2766                                         " ORDER BY %s",
2767                                         string
2768                                 );
2769                         }
2770
2771                         free(string);
2772                 }
2773
2774                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "limit" )) ){
2775                         string = jsonObjectToSimpleString(_tmp);
2776                         buffer_fadd(
2777                                 sql_buf,
2778                                 " LIMIT %d",
2779                                 atoi(string)
2780                         );
2781                         free(string);
2782                 }
2783
2784                 _tmp = jsonObjectGetKeyConst( order_hash, "offset" );
2785                 if (_tmp) {
2786                         string = jsonObjectToSimpleString(_tmp);
2787                         buffer_fadd(
2788                                 sql_buf,
2789                                 " OFFSET %d",
2790                                 atoi(string)
2791                         );
2792                         free(string);
2793                 }
2794         }
2795
2796         if (defaultselhash) jsonObjectFree(defaultselhash);
2797
2798         buffer_add(sql_buf, ";");
2799         return buffer_release(sql_buf);
2800 }
2801
2802 int doJSONSearch ( osrfMethodContext* ctx ) {
2803         OSRF_METHOD_VERIFY_CONTEXT(ctx);
2804         osrfLogDebug(OSRF_LOG_MARK, "Recieved query request");
2805
2806         int err = 0;
2807
2808         // XXX for now...
2809         dbhandle = writehandle;
2810
2811         jsonObject* hash = jsonObjectGetIndex(ctx->params, 0);
2812
2813     int flags = 0;
2814
2815         if (jsonBoolIsTrue(jsonObjectGetKey( hash, "distinct" )))
2816          flags |= SELECT_DISTINCT;
2817
2818         if ( ((int)jsonObjectGetNumber(jsonObjectGetKey( hash, "distinct" ))) == 1 ) // support 1/0 for perl's sake
2819          flags |= SELECT_DISTINCT;
2820
2821         if (jsonBoolIsTrue(jsonObjectGetKey( hash, "no_i18n" )))
2822          flags |= DISABLE_I18N;
2823
2824         if ( ((int)jsonObjectGetNumber(jsonObjectGetKey( hash, "no_i18n" ))) == 1 ) // support 1/0 for perl's sake
2825          flags |= DISABLE_I18N;
2826
2827         osrfLogDebug(OSRF_LOG_MARK, "Building SQL ...");
2828         char* sql = SELECT(
2829                         ctx,
2830                         jsonObjectGetKey( hash, "select" ),
2831                         jsonObjectGetKey( hash, "from" ),
2832                         jsonObjectGetKey( hash, "where" ),
2833                         jsonObjectGetKey( hash, "having" ),
2834                         jsonObjectGetKey( hash, "order_by" ),
2835                         jsonObjectGetKey( hash, "limit" ),
2836                         jsonObjectGetKey( hash, "offset" ),
2837                         flags
2838         );
2839
2840         if (!sql) {
2841                 err = -1;
2842                 return err;
2843         }
2844
2845         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
2846         dbi_result result = dbi_conn_query(dbhandle, sql);
2847
2848         if(result) {
2849                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
2850
2851                 if (dbi_result_first_row(result)) {
2852                         /* JSONify the result */
2853                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
2854
2855                         do {
2856                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
2857                                 osrfAppRespond( ctx, return_val );
2858                 jsonObjectFree( return_val );
2859                         } while (dbi_result_next_row(result));
2860
2861                 } else {
2862                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
2863                 }
2864
2865                 osrfAppRespondComplete( ctx, NULL );
2866
2867                 /* clean up the query */
2868                 dbi_result_free(result);
2869
2870         } else {
2871                 err = -1;
2872                 osrfLogError(OSRF_LOG_MARK, "%s: Error with query [%s]", MODULENAME, sql);
2873                 osrfAppSessionStatus(
2874                         ctx->session,
2875                         OSRF_STATUS_INTERNALSERVERERROR,
2876                         "osrfMethodException",
2877                         ctx->request,
2878                         "Severe query error -- see error log for more details"
2879                 );
2880         }
2881
2882         free(sql);
2883         return err;
2884 }
2885
2886 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* meta,
2887                 const jsonObject* params, int* err ) {
2888
2889         // XXX for now...
2890         dbhandle = writehandle;
2891
2892         osrfHash* links = osrfHashGet(meta, "links");
2893         osrfHash* fields = osrfHashGet(meta, "fields");
2894         char* core_class = osrfHashGet(meta, "classname");
2895         char* pkey = osrfHashGet(meta, "primarykey");
2896
2897         const jsonObject* _tmp;
2898         jsonObject* obj;
2899         jsonObject* search_hash = jsonObjectGetIndex(params, 0);
2900         jsonObject* order_hash = jsonObjectGetIndex(params, 1);
2901
2902         char* sql = buildSELECT( search_hash, order_hash, meta, ctx );
2903         if (!sql) {
2904                 *err = -1;
2905                 return NULL;
2906         }
2907
2908         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
2909         dbi_result result = dbi_conn_query(dbhandle, sql);
2910
2911         jsonObject* res_list = jsonNewObjectType(JSON_ARRAY);
2912         if(result) {
2913                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
2914                 osrfHash* dedup = osrfNewHash();
2915
2916                 if (dbi_result_first_row(result)) {
2917                         /* JSONify the result */
2918                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
2919                         do {
2920                                 obj = oilsMakeFieldmapperFromResult( result, meta );
2921                                 char* pkey_val = oilsFMGetString( obj, pkey );
2922                                 if ( osrfHashGet( dedup, pkey_val ) ) {
2923                                         jsonObjectFree(obj);
2924                                         free(pkey_val);
2925                                 } else {
2926                                         osrfHashSet( dedup, pkey_val, pkey_val );
2927                                         jsonObjectPush(res_list, obj);
2928                                 }
2929                         } while (dbi_result_next_row(result));
2930                 } else {
2931                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
2932                 }
2933
2934                 osrfHashFree(dedup);
2935
2936                 /* clean up the query */
2937                 dbi_result_free(result);
2938
2939         } else {
2940                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]", MODULENAME, osrfHashGet(meta, "fieldmapper"), sql);
2941                 osrfAppSessionStatus(
2942                         ctx->session,
2943                         OSRF_STATUS_INTERNALSERVERERROR,
2944                         "osrfMethodException",
2945                         ctx->request,
2946                         "Severe query error -- see error log for more details"
2947                 );
2948                 *err = -1;
2949                 free(sql);
2950                 jsonObjectFree(res_list);
2951                 return jsonNULL;
2952
2953         }
2954
2955         free(sql);
2956
2957         if (res_list->size && order_hash) {
2958                 _tmp = jsonObjectGetKeyConst( order_hash, "flesh" );
2959                 if (_tmp) {
2960                         int x = (int)jsonObjectGetNumber(_tmp);
2961                         if (x == -1 || x > max_flesh_depth) x = max_flesh_depth;
2962
2963                         const jsonObject* temp_blob;
2964                         if ((temp_blob = jsonObjectGetKeyConst( order_hash, "flesh_fields" )) && x > 0) {
2965
2966                                 jsonObject* flesh_blob = jsonObjectClone( temp_blob );
2967                                 const jsonObject* flesh_fields = jsonObjectGetKeyConst( flesh_blob, core_class );
2968
2969                                 osrfStringArray* link_fields = NULL;
2970
2971                                 if (flesh_fields) {
2972                                         if (flesh_fields->size == 1) {
2973                                                 char* _t = jsonObjectToSimpleString( jsonObjectGetIndex( flesh_fields, 0 ) );
2974                                                 if (!strcmp(_t,"*")) link_fields = osrfHashKeys( links );
2975                                                 free(_t);
2976                                         }
2977
2978                                         if (!link_fields) {
2979                                                 jsonObject* _f;
2980                                                 link_fields = osrfNewStringArray(1);
2981                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
2982                                                 while ((_f = jsonIteratorNext( _i ))) {
2983                                                         osrfStringArrayAdd( link_fields, jsonObjectToSimpleString( _f ) );
2984                                                 }
2985                         jsonIteratorFree(_i);
2986                                         }
2987                                 }
2988
2989                                 jsonObject* cur;
2990                                 jsonIterator* itr = jsonNewIterator( res_list );
2991                                 while ((cur = jsonIteratorNext( itr ))) {
2992
2993                                         int i = 0;
2994                                         char* link_field;
2995
2996                                         while ( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
2997
2998                                                 osrfLogDebug(OSRF_LOG_MARK, "Starting to flesh %s", link_field);
2999
3000                                                 osrfHash* kid_link = osrfHashGet(links, link_field);
3001                                                 if (!kid_link) continue;
3002
3003                                                 osrfHash* field = osrfHashGet(fields, link_field);
3004                                                 if (!field) continue;
3005
3006                                                 osrfHash* value_field = field;
3007
3008                                                 osrfHash* kid_idl = osrfHashGet(oilsIDL(), osrfHashGet(kid_link, "class"));
3009                                                 if (!kid_idl) continue;
3010
3011                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
3012                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3013                                                 }
3014
3015                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) { // might_have
3016                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3017                                                 }
3018
3019                                                 osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
3020
3021                                                 if (link_map->size > 0) {
3022                                                         jsonObject* _kid_key = jsonNewObjectType(JSON_ARRAY);
3023                                                         jsonObjectPush(
3024                                                                 _kid_key,
3025                                                                 jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
3026                                                         );
3027
3028                                                         jsonObjectSetKey(
3029                                                                 flesh_blob,
3030                                                                 osrfHashGet(kid_link, "class"),
3031                                                                 _kid_key
3032                                                         );
3033                                                 };
3034
3035                                                 osrfLogDebug(
3036                                                         OSRF_LOG_MARK,
3037                                                         "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
3038                                                         osrfHashGet(kid_link, "field"),
3039                                                         osrfHashGet(kid_link, "class"),
3040                                                         osrfHashGet(kid_link, "key"),
3041                                                         osrfHashGet(kid_link, "reltype")
3042                                                 );
3043
3044                                                 jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
3045                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // search hash
3046                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // order/flesh hash
3047
3048                                                 osrfLogDebug(OSRF_LOG_MARK, "Creating dummy params object...");
3049
3050                                                 char* search_key =
3051                                                 jsonObjectToSimpleString(
3052                                                         jsonObjectGetIndex(
3053                                                                 cur,
3054                                                                 atoi( osrfHashGet(value_field, "array_position") )
3055                                                         )
3056                                                 );
3057
3058                                                 if (!search_key) {
3059                                                         osrfLogDebug(OSRF_LOG_MARK, "Nothing to search for!");
3060                                                         continue;
3061                                                 }
3062
3063                                                 jsonObjectSetKey(
3064                                                         jsonObjectGetIndex(fake_params, 0),
3065                                                         osrfHashGet(kid_link, "key"),
3066                                                         jsonNewObject( search_key )
3067                                                 );
3068
3069                                                 free(search_key);
3070
3071
3072                                                 jsonObjectSetKey(
3073                                                         jsonObjectGetIndex(fake_params, 1),
3074                                                         "flesh",
3075                                                         jsonNewNumberObject( (double)(x - 1 + link_map->size) )
3076                                                 );
3077
3078                                                 if (flesh_blob)
3079                                                         jsonObjectSetKey( jsonObjectGetIndex(fake_params, 1), "flesh_fields", jsonObjectClone(flesh_blob) );
3080
3081                                                 if (jsonObjectGetKeyConst(order_hash, "order_by")) {
3082                                                         jsonObjectSetKey(
3083                                                                 jsonObjectGetIndex(fake_params, 1),
3084                                                                 "order_by",
3085                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "order_by"))
3086                                                         );
3087                                                 }
3088
3089                                                 if (jsonObjectGetKeyConst(order_hash, "select")) {
3090                                                         jsonObjectSetKey(
3091                                                                 jsonObjectGetIndex(fake_params, 1),
3092                                                                 "select",
3093                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "select"))
3094                                                         );
3095                                                 }
3096
3097                                                 jsonObject* kids = doFieldmapperSearch(ctx, kid_idl, fake_params, err);
3098
3099                                                 if(*err) {
3100                                                         jsonObjectFree( fake_params );
3101                                                         osrfStringArrayFree(link_fields);
3102                                                         jsonIteratorFree(itr);
3103                                                         jsonObjectFree(res_list);
3104                                                         jsonObjectFree(flesh_blob);
3105                                                         return jsonNULL;
3106                                                 }
3107
3108                                                 osrfLogDebug(OSRF_LOG_MARK, "Search for %s return %d linked objects", osrfHashGet(kid_link, "class"), kids->size);
3109
3110                                                 jsonObject* X = NULL;
3111                                                 if ( link_map->size > 0 && kids->size > 0 ) {
3112                                                         X = kids;
3113                                                         kids = jsonNewObjectType(JSON_ARRAY);
3114
3115                                                         jsonObject* _k_node;
3116                                                         jsonIterator* _k = jsonNewIterator( X );
3117                                                         while ((_k_node = jsonIteratorNext( _k ))) {
3118                                                                 jsonObjectPush(
3119                                                                         kids,
3120                                                                         jsonObjectClone(
3121                                                                                 jsonObjectGetIndex(
3122                                                                                         _k_node,
3123                                                                                         (unsigned long)atoi(
3124                                                                                                 osrfHashGet(
3125                                                                                                         osrfHashGet(
3126                                                                                                                 osrfHashGet(
3127                                                                                                                         osrfHashGet(
3128                                                                                                                                 oilsIDL(),
3129                                                                                                                                 osrfHashGet(kid_link, "class")
3130                                                                                                                         ),
3131                                                                                                                         "fields"
3132                                                                                                                 ),
3133                                                                                                                 osrfStringArrayGetString( link_map, 0 )
3134                                                                                                         ),
3135                                                                                                         "array_position"
3136                                                                                                 )
3137                                                                                         )
3138                                                                                 )
3139                                                                         )
3140                                                                 );
3141                                                         }
3142                                                         jsonIteratorFree(_k);
3143                                                 }
3144
3145                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_a" )) || !(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) {
3146                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
3147                                                         jsonObjectSetIndex(
3148                                                                 cur,
3149                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
3150                                                                 jsonObjectClone( jsonObjectGetIndex(kids, 0) )
3151                                                         );
3152                                                 }
3153
3154                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
3155                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
3156                                                         jsonObjectSetIndex(
3157                                                                 cur,
3158                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
3159                                                                 jsonObjectClone( kids )
3160                                                         );
3161                                                 }
3162
3163                                                 if (X) {
3164                                                         jsonObjectFree(kids);
3165                                                         kids = X;
3166                                                 }
3167
3168                                                 jsonObjectFree( kids );
3169                                                 jsonObjectFree( fake_params );
3170
3171                                                 osrfLogDebug(OSRF_LOG_MARK, "Fleshing of %s complete", osrfHashGet(kid_link, "field"));
3172                                                 osrfLogDebug(OSRF_LOG_MARK, "%s", jsonObjectToJSON(cur));
3173
3174                                         }
3175                                 }
3176                                 jsonObjectFree( flesh_blob );
3177                                 osrfStringArrayFree(link_fields);
3178                                 jsonIteratorFree(itr);
3179                         }
3180                 }
3181         }
3182
3183         return res_list;
3184 }
3185
3186
3187 static jsonObject* doUpdate(osrfMethodContext* ctx, int* err ) {
3188
3189         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
3190 #ifdef PCRUD
3191         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
3192 #else
3193         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
3194 #endif
3195
3196         if (!verifyObjectClass(ctx, target)) {
3197                 *err = -1;
3198                 return jsonNULL;
3199         }
3200
3201         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
3202                 osrfAppSessionStatus(
3203                         ctx->session,
3204                         OSRF_STATUS_BADREQUEST,
3205                         "osrfMethodException",
3206                         ctx->request,
3207                         "No active transaction -- required for UPDATE"
3208                 );
3209                 *err = -1;
3210                 return jsonNULL;
3211         }
3212
3213         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
3214                 osrfAppSessionStatus(
3215                         ctx->session,
3216                         OSRF_STATUS_BADREQUEST,
3217                         "osrfMethodException",
3218                         ctx->request,
3219                         "Cannot UPDATE readonly class"
3220                 );
3221                 *err = -1;
3222                 return jsonNULL;
3223         }
3224
3225         dbhandle = writehandle;
3226
3227         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
3228
3229         // Set the last_xact_id
3230         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
3231         if (index > -1) {
3232                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
3233                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
3234         }
3235
3236         char* pkey = osrfHashGet(meta, "primarykey");
3237         osrfHash* fields = osrfHashGet(meta, "fields");
3238
3239         char* id = oilsFMGetString( target, pkey );
3240
3241         osrfLogDebug(
3242                 OSRF_LOG_MARK,
3243                 "%s updating %s object with %s = %s",
3244                 MODULENAME,
3245                 osrfHashGet(meta, "fieldmapper"),
3246                 pkey,
3247                 id
3248         );
3249
3250         growing_buffer* sql = buffer_init(128);
3251         buffer_fadd(sql,"UPDATE %s SET", osrfHashGet(meta, "tablename"));
3252
3253         int i = 0;
3254         int first = 1;
3255         char* field_name;
3256         osrfStringArray* field_list = osrfHashKeys( fields );
3257         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
3258
3259                 osrfHash* field = osrfHashGet( fields, field_name );
3260
3261                 if(!( strcmp( field_name, pkey ) )) continue;
3262                 if(!( strcmp( osrfHashGet(osrfHashGet(fields,field_name), "virtual"), "true" ) )) continue;
3263
3264                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
3265
3266                 char* value;
3267                 if (field_object && field_object->classname) {
3268                         value = oilsFMGetString(
3269                                 field_object,
3270                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
3271             );
3272                 } else {
3273                         value = jsonObjectToSimpleString( field_object );
3274                 }
3275
3276                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s", osrfHashGet(meta, "fieldmapper"), field_name, value);
3277
3278                 if (!field_object || field_object->type == JSON_NULL) {
3279                         if ( !(!( strcmp( osrfHashGet(meta, "classname"), "au" ) ) && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
3280                                 if (first) first = 0;
3281                                 else buffer_add(sql, ",");
3282                                 buffer_fadd( sql, " %s = NULL", field_name );
3283                         }
3284
3285                 } else if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) {
3286                         if (first) first = 0;
3287                         else buffer_add(sql, ",");
3288
3289                         if ( !strncmp(osrfHashGet(field, "datatype"), "INT", (size_t)3) ) {
3290                                 buffer_fadd( sql, " %s = %ld", field_name, atol(value) );
3291                         } else if ( !strcmp(osrfHashGet(field, "datatype"), "NUMERIC") ) {
3292                                 buffer_fadd( sql, " %s = %f", field_name, atof(value) );
3293                         }
3294
3295                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, osrfHashGet(field, "datatype"));
3296
3297                 } else {
3298                         if ( dbi_conn_quote_string(dbhandle, &value) ) {
3299                                 if (first) first = 0;
3300                                 else buffer_add(sql, ",");
3301                                 buffer_fadd( sql, " %s = %s", field_name, value );
3302
3303                         } else {
3304                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
3305                                 osrfAppSessionStatus(
3306                                         ctx->session,
3307                                         OSRF_STATUS_INTERNALSERVERERROR,
3308                                         "osrfMethodException",
3309                                         ctx->request,
3310                                         "Error quoting string -- please see the error log for more details"
3311                                 );
3312                                 free(value);
3313                                 free(id);
3314                                 buffer_free(sql);
3315                                 *err = -1;
3316                                 return jsonNULL;
3317                         }
3318                 }
3319
3320                 free(value);
3321
3322         }
3323
3324         jsonObject* obj = jsonParseString(id);
3325
3326         if ( strcmp( osrfHashGet( osrfHashGet( osrfHashGet(meta, "fields"), pkey ), "primitive" ), "number" ) )
3327                 dbi_conn_quote_string(dbhandle, &id);
3328
3329         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
3330
3331         char* query = buffer_release(sql);
3332         osrfLogDebug(OSRF_LOG_MARK, "%s: Update SQL [%s]", MODULENAME, query);
3333
3334         dbi_result result = dbi_conn_query(dbhandle, query);
3335         free(query);
3336
3337         if (!result) {
3338                 jsonObjectFree(obj);
3339                 obj = jsonNewObject(NULL);
3340                 osrfLogError(
3341                         OSRF_LOG_MARK,
3342                         "%s ERROR updating %s object with %s = %s",
3343                         MODULENAME,
3344                         osrfHashGet(meta, "fieldmapper"),
3345                         pkey,
3346                         id
3347                 );
3348         }
3349
3350         free(id);
3351
3352         return obj;
3353 }
3354
3355 static jsonObject* doDelete(osrfMethodContext* ctx, int* err ) {
3356
3357         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
3358
3359         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
3360                 osrfAppSessionStatus(
3361                         ctx->session,
3362                         OSRF_STATUS_BADREQUEST,
3363                         "osrfMethodException",
3364                         ctx->request,
3365                         "No active transaction -- required for DELETE"
3366                 );
3367                 *err = -1;
3368                 return jsonNULL;
3369         }
3370
3371         if (osrfHashGet( meta, "readonly" ) && strncasecmp("true", osrfHashGet( meta, "readonly" ), 4)) {
3372                 osrfAppSessionStatus(
3373                         ctx->session,
3374                         OSRF_STATUS_BADREQUEST,
3375                         "osrfMethodException",
3376                         ctx->request,
3377                         "Cannot DELETE readonly class"
3378                 );
3379                 *err = -1;
3380                 return jsonNULL;
3381         }
3382
3383         dbhandle = writehandle;
3384
3385         jsonObject* obj;
3386
3387         char* pkey = osrfHashGet(meta, "primarykey");
3388
3389         int _obj_pos = 0;
3390 #ifdef PCRUD
3391                 _obj_pos = 1;
3392 #endif
3393
3394         char* id;
3395         if (jsonObjectGetIndex(ctx->params, _obj_pos)->classname) {
3396                 if (!verifyObjectClass(ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
3397                         *err = -1;
3398                         return jsonNULL;
3399                 }
3400
3401                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
3402         } else {
3403 #ifdef PCRUD
3404         if (!verifyObjectPCRUD( ctx, NULL )) {
3405                         *err = -1;
3406                         return jsonNULL;
3407         }
3408 #endif
3409                 id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, _obj_pos));
3410         }
3411
3412         osrfLogDebug(
3413                 OSRF_LOG_MARK,
3414                 "%s deleting %s object with %s = %s",
3415                 MODULENAME,
3416                 osrfHashGet(meta, "fieldmapper"),
3417                 pkey,
3418                 id
3419         );
3420
3421         obj = jsonParseString(id);
3422
3423         if ( strcmp( osrfHashGet( osrfHashGet( osrfHashGet(meta, "fields"), pkey ), "primitive" ), "number" ) )
3424                 dbi_conn_quote_string(writehandle, &id);
3425
3426         dbi_result result = dbi_conn_queryf(writehandle, "DELETE FROM %s WHERE %s = %s;", osrfHashGet(meta, "tablename"), pkey, id);
3427
3428         if (!result) {
3429                 jsonObjectFree(obj);
3430                 obj = jsonNewObject(NULL);
3431                 osrfLogError(
3432                         OSRF_LOG_MARK,
3433                         "%s ERROR deleting %s object with %s = %s",
3434                         MODULENAME,
3435                         osrfHashGet(meta, "fieldmapper"),
3436                         pkey,
3437                         id
3438                 );
3439         }
3440
3441         free(id);
3442
3443         return obj;
3444
3445 }
3446
3447
3448 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
3449         if(!(result && meta)) return jsonNULL;
3450
3451         jsonObject* object = jsonNewObject(NULL);
3452         jsonObjectSetClass(object, osrfHashGet(meta, "classname"));
3453
3454         osrfHash* fields = osrfHashGet(meta, "fields");
3455
3456         osrfLogInternal(OSRF_LOG_MARK, "Setting object class to %s ", object->classname);
3457
3458         osrfHash* _f;
3459         time_t _tmp_dt;
3460         char dt_string[256];
3461         struct tm gmdt;
3462
3463         int fmIndex;
3464         int columnIndex = 1;
3465         int attr;
3466         unsigned short type;
3467         const char* columnName;
3468
3469         /* cycle through the column list */
3470         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
3471
3472                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
3473
3474                 fmIndex = -1; // reset the position
3475
3476                 /* determine the field type and storage attributes */
3477                 type = dbi_result_get_field_type(result, columnName);
3478                 attr = dbi_result_get_field_attribs(result, columnName);
3479
3480                 /* fetch the fieldmapper index */
3481                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
3482                         char* virt = (char*)osrfHashGet(_f, "virtual");
3483                         char* pos = (char*)osrfHashGet(_f, "array_position");
3484
3485                         if ( !virt || !pos || !(strcmp( virt, "true" )) ) continue;
3486
3487                         fmIndex = atoi( pos );
3488                         osrfLogInternal(OSRF_LOG_MARK, "... Found column at position [%s]...", pos);
3489                 } else {
3490                         continue;
3491                 }
3492
3493                 if (dbi_result_field_is_null(result, columnName)) {
3494                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(NULL) );
3495                 } else {
3496
3497                         switch( type ) {
3498
3499                                 case DBI_TYPE_INTEGER :
3500
3501                                         if( attr & DBI_INTEGER_SIZE8 )
3502                                                 jsonObjectSetIndex( object, fmIndex,
3503                                                         jsonNewNumberObject(dbi_result_get_longlong(result, columnName)));
3504                                         else
3505                                                 jsonObjectSetIndex( object, fmIndex,
3506                                                         jsonNewNumberObject(dbi_result_get_int(result, columnName)));
3507
3508                                         break;
3509
3510                                 case DBI_TYPE_DECIMAL :
3511                                         jsonObjectSetIndex( object, fmIndex,
3512                                                         jsonNewNumberObject(dbi_result_get_double(result, columnName)));
3513                                         break;
3514
3515                                 case DBI_TYPE_STRING :
3516
3517
3518                                         jsonObjectSetIndex(
3519                                                 object,
3520                                                 fmIndex,
3521                                                 jsonNewObject( dbi_result_get_string(result, columnName) )
3522                                         );
3523
3524                                         break;
3525
3526                                 case DBI_TYPE_DATETIME :
3527
3528                                         memset(dt_string, '\0', sizeof(dt_string));
3529                                         memset(&gmdt, '\0', sizeof(gmdt));
3530
3531                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
3532
3533
3534                                         if (!(attr & DBI_DATETIME_DATE)) {
3535                                                 gmtime_r( &_tmp_dt, &gmdt );
3536                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
3537                                         } else if (!(attr & DBI_DATETIME_TIME)) {
3538                                                 localtime_r( &_tmp_dt, &gmdt );
3539                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
3540                                         } else {
3541                                                 localtime_r( &_tmp_dt, &gmdt );
3542                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
3543                                         }
3544
3545                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(dt_string) );
3546
3547                                         break;
3548
3549                                 case DBI_TYPE_BINARY :
3550                                         osrfLogError( OSRF_LOG_MARK,
3551                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
3552                         }
3553                 }
3554         }
3555
3556         return object;
3557 }
3558
3559 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
3560         if(!result) return jsonNULL;
3561
3562         jsonObject* object = jsonNewObject(NULL);
3563
3564         time_t _tmp_dt;
3565         char dt_string[256];
3566         struct tm gmdt;
3567
3568         int fmIndex;
3569         int columnIndex = 1;
3570         int attr;
3571         unsigned short type;
3572         const char* columnName;
3573
3574         /* cycle through the column list */
3575         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
3576
3577                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
3578
3579                 fmIndex = -1; // reset the position
3580
3581                 /* determine the field type and storage attributes */
3582                 type = dbi_result_get_field_type(result, columnName);
3583                 attr = dbi_result_get_field_attribs(result, columnName);
3584
3585                 if (dbi_result_field_is_null(result, columnName)) {
3586                         jsonObjectSetKey( object, columnName, jsonNewObject(NULL) );
3587                 } else {
3588
3589                         switch( type ) {
3590
3591                                 case DBI_TYPE_INTEGER :
3592
3593                                         if( attr & DBI_INTEGER_SIZE8 )
3594                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_longlong(result, columnName)) );
3595                                         else
3596                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_int(result, columnName)) );
3597                                         break;
3598
3599                                 case DBI_TYPE_DECIMAL :
3600                                         jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_double(result, columnName)) );
3601                                         break;
3602
3603                                 case DBI_TYPE_STRING :
3604                                         jsonObjectSetKey( object, columnName, jsonNewObject(dbi_result_get_string(result, columnName)) );
3605                                         break;
3606
3607                                 case DBI_TYPE_DATETIME :
3608
3609                                         memset(dt_string, '\0', sizeof(dt_string));
3610                                         memset(&gmdt, '\0', sizeof(gmdt));
3611
3612                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
3613
3614
3615                                         if (!(attr & DBI_DATETIME_DATE)) {
3616                                                 gmtime_r( &_tmp_dt, &gmdt );
3617                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
3618                                         } else if (!(attr & DBI_DATETIME_TIME)) {
3619                                                 localtime_r( &_tmp_dt, &gmdt );
3620                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
3621                                         } else {
3622                                                 localtime_r( &_tmp_dt, &gmdt );
3623                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
3624                                         }
3625
3626                                         jsonObjectSetKey( object, columnName, jsonNewObject(dt_string) );
3627                                         break;
3628
3629                                 case DBI_TYPE_BINARY :
3630                                         osrfLogError( OSRF_LOG_MARK,
3631                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
3632                         }
3633                 }
3634         }
3635
3636         return object;
3637 }
3638