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