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