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