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