Open-ILS/src/c-apps/oils_cstore.c

   1 #include <ctype.h>
   2 #include "opensrf/osrf_application.h"
   3 #include "opensrf/osrf_settings.h"
   4 #include "opensrf/osrf_message.h"
   5 #include "opensrf/utils.h"
   6 #include "opensrf/osrf_json.h"
   7 #include "opensrf/log.h"
   8 #include "openils/oils_utils.h"
   9 #include <dbi/dbi.h>
  10
  11 #include <time.h>
  12 #include <stdlib.h>
  13 #include <string.h>
  14 #include <unistd.h>
  15
  16 #ifdef RSTORE
  17 #  define MODULENAME "open-ils.reporter-store"
  18 #else
  19 #  ifdef PCRUD
  20 #    define MODULENAME "open-ils.pcrud"
  21 #  else
  22 #    define MODULENAME "open-ils.cstore"
  23 #  endif
  24 #endif
  25
  26 #define SUBSELECT       4
  27 #define DISABLE_I18N    2
  28 #define SELECT_DISTINCT 1
  29 #define AND_OP_JOIN     0
  30 #define OR_OP_JOIN      1
  31
  32 int osrfAppChildInit();
  33 int osrfAppInitialize();
  34 void osrfAppChildExit();
  35
  36 static int verifyObjectClass ( osrfMethodContext*, const jsonObject* );
  37
  38 int beginTransaction ( osrfMethodContext* );
  39 int commitTransaction ( osrfMethodContext* );
  40 int rollbackTransaction ( osrfMethodContext* );
  41
  42 int setSavepoint ( osrfMethodContext* );
  43 int releaseSavepoint ( osrfMethodContext* );
  44 int rollbackSavepoint ( osrfMethodContext* );
  45
  46 int doJSONSearch ( osrfMethodContext* );
  47
  48 int dispatchCRUDMethod ( osrfMethodContext* );
  49 static jsonObject* doCreate ( osrfMethodContext*, int* );
  50 static jsonObject* doRetrieve ( osrfMethodContext*, int* );
  51 static jsonObject* doUpdate ( osrfMethodContext*, int* );
  52 static jsonObject* doDelete ( osrfMethodContext*, int* );
  53 static jsonObject* doFieldmapperSearch ( osrfMethodContext*, osrfHash*,
  54         const jsonObject*, int* );
  55 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result, osrfHash* );
  56 static jsonObject* oilsMakeJSONFromResult( dbi_result );
  57
  58 static char* searchSimplePredicate ( const char* op, const char* class,
  59                                 osrfHash* field, const jsonObject* node );
  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*, const jsonObject*, const char* );
  63 static char* searchBETWEENPredicate ( const char*, osrfHash*, const jsonObject* );
  64 static char* searchINPredicate ( const char*, osrfHash*,
  65                                                                  jsonObject*, const char*, osrfMethodContext* );
  66 static char* searchPredicate ( const char*, osrfHash*, jsonObject*, osrfMethodContext* );
  67 static char* searchJOIN ( const jsonObject*, osrfHash* );
  68 static char* searchWHERE ( const jsonObject*, osrfHash*, int, osrfMethodContext* );
  69 static char* buildSELECT ( jsonObject*, jsonObject*, osrfHash*, osrfMethodContext* );
  70
  71 char* SELECT ( osrfMethodContext*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, int );
  72
  73 void userDataFree( void* );
  74 static void sessionDataFree( char*, void* );
  75 static char* getSourceDefinition( osrfHash* );
  76 static int str_is_true( const char* str );
  77 static int obj_is_true( const jsonObject* obj );
  78 static const char* json_type( int code );
  79 static const char* get_primitive( osrfHash* field );
  80 static const char* get_datatype( osrfHash* field );
  81 static int is_identifier( const char* s);
  82 static int is_good_operator( const char* op );
  83
  84 #ifdef PCRUD
  85 static jsonObject* verifyUserPCRUD( osrfMethodContext* );
  86 static int verifyObjectPCRUD( osrfMethodContext*, const jsonObject* );
  87 #endif
  88
  89 static int child_initialized = 0;   /* boolean */
  90
  91 static dbi_conn writehandle; /* our MASTER db connection */
  92 static dbi_conn dbhandle; /* our CURRENT db connection */
  93 //static osrfHash * readHandles;
  94 static jsonObject* jsonNULL = NULL; //
  95 static int max_flesh_depth = 100;
  96
  97 /* called when this process is about to exit */
  98 void osrfAppChildExit() {
  99     osrfLogDebug(OSRF_LOG_MARK, "Child is exiting, disconnecting from database...");
 100
 101     int same = 0;
 102     if (writehandle == dbhandle) same = 1;
 103     if (writehandle) {
 104         dbi_conn_query(writehandle, "ROLLBACK;");
 105         dbi_conn_close(writehandle);
 106         writehandle = NULL;
 107     }
 108     if (dbhandle && !same)
 109         dbi_conn_close(dbhandle);
 110
 111     // XXX add cleanup of readHandles whenever that gets used
 112
 113     return;
 114 }
 115
 116 int osrfAppInitialize() {
 117
 118     osrfLogInfo(OSRF_LOG_MARK, "Initializing the CStore Server...");
 119     osrfLogInfo(OSRF_LOG_MARK, "Finding XML file...");
 120
 121     if (!oilsIDLInit( osrf_settings_host_value("/IDL") )) return 1; /* return non-zero to indicate error */
 122
 123     growing_buffer* method_name = buffer_init(64);
 124 #ifndef PCRUD
 125     // Generic search thingy
 126     buffer_add(method_name, MODULENAME);
 127         buffer_add(method_name, ".json_query");
 128         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 129                                                    "doJSONSearch", "", 1, OSRF_METHOD_STREAMING );
 130 #endif
 131
 132     // first we register all the transaction and savepoint methods
 133     buffer_reset(method_name);
 134         OSRF_BUFFER_ADD(method_name, MODULENAME);
 135         OSRF_BUFFER_ADD(method_name, ".transaction.begin");
 136         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 137                                                    "beginTransaction", "", 0, 0 );
 138
 139     buffer_reset(method_name);
 140         OSRF_BUFFER_ADD(method_name, MODULENAME);
 141         OSRF_BUFFER_ADD(method_name, ".transaction.commit");
 142         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 143                                                    "commitTransaction", "", 0, 0 );
 144
 145     buffer_reset(method_name);
 146         OSRF_BUFFER_ADD(method_name, MODULENAME);
 147         OSRF_BUFFER_ADD(method_name, ".transaction.rollback");
 148         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 149                                                    "rollbackTransaction", "", 0, 0 );
 150
 151     buffer_reset(method_name);
 152         OSRF_BUFFER_ADD(method_name, MODULENAME);
 153         OSRF_BUFFER_ADD(method_name, ".savepoint.set");
 154         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 155                                                    "setSavepoint", "", 1, 0 );
 156
 157     buffer_reset(method_name);
 158         OSRF_BUFFER_ADD(method_name, MODULENAME);
 159         OSRF_BUFFER_ADD(method_name, ".savepoint.release");
 160         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 161                                                    "releaseSavepoint", "", 1, 0 );
 162
 163     buffer_reset(method_name);
 164         OSRF_BUFFER_ADD(method_name, MODULENAME);
 165         OSRF_BUFFER_ADD(method_name, ".savepoint.rollback");
 166         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 167                                                    "rollbackSavepoint", "", 1, 0 );
 168
 169     buffer_free(method_name);
 170
 171         static const char* global_method[] = {
 172                 "create",
 173                 "retrieve",
 174                 "update",
 175                 "delete",
 176                 "search",
 177                 "id_list"
 178         };
 179         const int global_method_count
 180                 = sizeof( global_method ) / sizeof ( global_method[0] );
 181
 182     int c_index = 0;
 183     char* classname;
 184     osrfStringArray* classes = osrfHashKeys( oilsIDL() );
 185     osrfLogDebug(OSRF_LOG_MARK, "%d classes loaded", classes->size );
 186     osrfLogDebug(OSRF_LOG_MARK,
 187                 "At least %d methods will be generated", classes->size * global_method_count);
 188
 189     while ( (classname = osrfStringArrayGetString(classes, c_index++)) ) {
 190         osrfLogInfo(OSRF_LOG_MARK, "Generating class methods for %s", classname);
 191
 192         osrfHash* idlClass = osrfHashGet(oilsIDL(), classname);
 193
 194         if (!osrfStringArrayContains( osrfHashGet(idlClass, "controller"), MODULENAME )) {
 195             osrfLogInfo(OSRF_LOG_MARK, "%s is not listed as a controller for %s, moving on", MODULENAME, classname);
 196             continue;
 197         }
 198
 199                 if ( str_is_true( osrfHashGet(idlClass, "virtual") ) ) {
 200                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 201                         continue;
 202                 }
 203
 204         // Look up some other attributes of the current class
 205         const char* idlClass_fieldmapper = osrfHashGet(idlClass, "fieldmapper");
 206                 const char* readonly = osrfHashGet(idlClass, "readonly");
 207 #ifdef PCRUD
 208         osrfHash* idlClass_permacrud = osrfHashGet(idlClass, "permacrud");
 209 #endif
 210
 211         int i;
 212         for( i = 0; i < global_method_count; ++i ) {
 213             const char* method_type = global_method[ i ];
 214             osrfLogDebug(OSRF_LOG_MARK,
 215                 "Using files to build %s class methods for %s", method_type, classname);
 216
 217             if (!idlClass_fieldmapper) continue;
 218
 219 #ifdef PCRUD
 220             if (!idlClass_permacrud) continue;
 221
 222             const char* tmp_method = method_type;
 223             if ( *tmp_method == 'i' || *tmp_method == 's') {
 224                 tmp_method = "retrieve";
 225             }
 226             if (!osrfHashGet( idlClass_permacrud, tmp_method )) continue;
 227 #endif
 228
 229             if (    str_is_true( readonly ) &&
 230                     ( *method_type == 'c' || *method_type == 'u' || *method_type == 'd')
 231                ) continue;
 232
 233             osrfHash* method_meta = osrfNewHash();
 234             osrfHashSet(method_meta, idlClass, "class");
 235
 236             method_name =  buffer_init(64);
 237 #ifdef PCRUD
 238             buffer_fadd(method_name, "%s.%s.%s", MODULENAME, method_type, classname);
 239 #else
 240             char* st_tmp = NULL;
 241             char* part = NULL;
 242             char* _fm = strdup( idlClass_fieldmapper );
 243             part = strtok_r(_fm, ":", &st_tmp);
 244
 245             buffer_fadd(method_name, "%s.direct.%s", MODULENAME, part);
 246
 247             while ((part = strtok_r(NULL, ":", &st_tmp))) {
 248                                 OSRF_BUFFER_ADD_CHAR(method_name, '.');
 249                                 OSRF_BUFFER_ADD(method_name, part);
 250             }
 251                         OSRF_BUFFER_ADD_CHAR(method_name, '.');
 252                         OSRF_BUFFER_ADD(method_name, method_type);
 253             free(_fm);
 254 #endif
 255
 256             char* method = buffer_release(method_name);
 257
 258             osrfHashSet( method_meta, method, "methodname" );
 259             osrfHashSet( method_meta, strdup(method_type), "methodtype" );
 260
 261             int flags = 0;
 262             if (*method_type == 'i' || *method_type == 's') {
 263                 flags = flags | OSRF_METHOD_STREAMING;
 264             }
 265
 266             osrfAppRegisterExtendedMethod(
 267                     MODULENAME,
 268                     method,
 269                     "dispatchCRUDMethod",
 270                     "",
 271                     1,
 272                     flags,
 273                     (void*)method_meta
 274                     );
 275
 276             free(method);
 277         }
 278     }
 279
 280     return 0;
 281 }
 282
 283 static char* getSourceDefinition( osrfHash* class ) {
 284
 285         char* tabledef = osrfHashGet(class, "tablename");
 286
 287         if (tabledef) {
 288                 tabledef = strdup(tabledef);
 289         } else {
 290                 tabledef = osrfHashGet(class, "source_definition");
 291                 if( tabledef ) {
 292                         growing_buffer* tablebuf = buffer_init(128);
 293                         buffer_fadd( tablebuf, "(%s)", tabledef );
 294                         tabledef = buffer_release(tablebuf);
 295                 } else {
 296                         const char* classname = osrfHashGet( class, "classname" );
 297                         if( !classname )
 298                                 classname = "???";
 299                         osrfLogError(
 300                                 OSRF_LOG_MARK,
 301                                 "%s ERROR No tablename or source_definition for class \"%s\"",
 302                                 MODULENAME,
 303                                 classname
 304                         );
 305                 }
 306         }
 307
 308         return tabledef;
 309 }
 310
 311 /**
 312  * Connects to the database
 313  */
 314 int osrfAppChildInit() {
 315
 316     osrfLogDebug(OSRF_LOG_MARK, "Attempting to initialize libdbi...");
 317     dbi_initialize(NULL);
 318     osrfLogDebug(OSRF_LOG_MARK, "... libdbi initialized.");
 319
 320     char* driver        = osrf_settings_host_value("/apps/%s/app_settings/driver", MODULENAME);
 321     char* user  = osrf_settings_host_value("/apps/%s/app_settings/database/user", MODULENAME);
 322     char* host  = osrf_settings_host_value("/apps/%s/app_settings/database/host", MODULENAME);
 323     char* port  = osrf_settings_host_value("/apps/%s/app_settings/database/port", MODULENAME);
 324     char* db    = osrf_settings_host_value("/apps/%s/app_settings/database/db", MODULENAME);
 325     char* pw    = osrf_settings_host_value("/apps/%s/app_settings/database/pw", MODULENAME);
 326     char* md    = osrf_settings_host_value("/apps/%s/app_settings/max_query_recursion", MODULENAME);
 327
 328     osrfLogDebug(OSRF_LOG_MARK, "Attempting to load the database driver [%s]...", driver);
 329     writehandle = dbi_conn_new(driver);
 330
 331     if(!writehandle) {
 332         osrfLogError(OSRF_LOG_MARK, "Error loading database driver [%s]", driver);
 333         return -1;
 334     }
 335     osrfLogDebug(OSRF_LOG_MARK, "Database driver [%s] seems OK", driver);
 336
 337     osrfLogInfo(OSRF_LOG_MARK, "%s connecting to database.  host=%s, "
 338             "port=%s, user=%s, pw=%s, db=%s", MODULENAME, host, port, user, pw, db );
 339
 340     if(host) dbi_conn_set_option(writehandle, "host", host );
 341     if(port) dbi_conn_set_option_numeric( writehandle, "port", atoi(port) );
 342     if(user) dbi_conn_set_option(writehandle, "username", user);
 343     if(pw) dbi_conn_set_option(writehandle, "password", pw );
 344     if(db) dbi_conn_set_option(writehandle, "dbname", db );
 345
 346     if(md) max_flesh_depth = atoi(md);
 347     if(max_flesh_depth < 0) max_flesh_depth = 1;
 348     if(max_flesh_depth > 1000) max_flesh_depth = 1000;
 349
 350     free(user);
 351     free(host);
 352     free(port);
 353     free(db);
 354     free(pw);
 355
 356     const char* err;
 357     if (dbi_conn_connect(writehandle) < 0) {
 358         sleep(1);
 359         if (dbi_conn_connect(writehandle) < 0) {
 360             dbi_conn_error(writehandle, &err);
 361             osrfLogError( OSRF_LOG_MARK, "Error connecting to database: %s", err);
 362             return -1;
 363         }
 364     }
 365
 366     osrfLogInfo(OSRF_LOG_MARK, "%s successfully connected to the database", MODULENAME);
 367
 368     int attr;
 369     unsigned short type;
 370     int i = 0;
 371     char* classname;
 372     osrfStringArray* classes = osrfHashKeys( oilsIDL() );
 373
 374     while ( (classname = osrfStringArrayGetString(classes, i++)) ) {
 375         osrfHash* class = osrfHashGet( oilsIDL(), classname );
 376         osrfHash* fields = osrfHashGet( class, "fields" );
 377
 378                 if( str_is_true( osrfHashGet(class, "virtual") ) ) {
 379                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 380                         continue;
 381                 }
 382
 383         char* tabledef = getSourceDefinition(class);
 384                 if( !tabledef )
 385                         tabledef = strdup( "(null)" );
 386
 387         growing_buffer* sql_buf = buffer_init(32);
 388         buffer_fadd( sql_buf, "SELECT * FROM %s AS x WHERE 1=0;", tabledef );
 389
 390         free(tabledef);
 391
 392         char* sql = buffer_release(sql_buf);
 393         osrfLogDebug(OSRF_LOG_MARK, "%s Investigatory SQL = %s", MODULENAME, sql);
 394
 395         dbi_result result = dbi_conn_query(writehandle, sql);
 396         free(sql);
 397
 398         if (result) {
 399
 400             int columnIndex = 1;
 401             const char* columnName;
 402             osrfHash* _f;
 403             while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
 404
 405                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
 406
 407                 /* fetch the fieldmapper index */
 408                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
 409
 410                     osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", (char*)columnName);
 411
 412                     /* determine the field type and storage attributes */
 413                     type = dbi_result_get_field_type(result, columnName);
 414                     attr = dbi_result_get_field_attribs(result, columnName);
 415
 416                     switch( type ) {
 417
 418                         case DBI_TYPE_INTEGER :
 419
 420                             if ( !osrfHashGet(_f, "primitive") )
 421                                 osrfHashSet(_f,"number", "primitive");
 422
 423                             if( attr & DBI_INTEGER_SIZE8 )
 424                                 osrfHashSet(_f,"INT8", "datatype");
 425                             else
 426                                 osrfHashSet(_f,"INT", "datatype");
 427                             break;
 428
 429                         case DBI_TYPE_DECIMAL :
 430                             if ( !osrfHashGet(_f, "primitive") )
 431                                 osrfHashSet(_f,"number", "primitive");
 432
 433                             osrfHashSet(_f,"NUMERIC", "datatype");
 434                             break;
 435
 436                         case DBI_TYPE_STRING :
 437                             if ( !osrfHashGet(_f, "primitive") )
 438                                 osrfHashSet(_f,"string", "primitive");
 439                             osrfHashSet(_f,"TEXT", "datatype");
 440                             break;
 441
 442                         case DBI_TYPE_DATETIME :
 443                             if ( !osrfHashGet(_f, "primitive") )
 444                                 osrfHashSet(_f,"string", "primitive");
 445
 446                             osrfHashSet(_f,"TIMESTAMP", "datatype");
 447                             break;
 448
 449                         case DBI_TYPE_BINARY :
 450                             if ( !osrfHashGet(_f, "primitive") )
 451                                 osrfHashSet(_f,"string", "primitive");
 452
 453                             osrfHashSet(_f,"BYTEA", "datatype");
 454                     }
 455
 456                     osrfLogDebug(
 457                             OSRF_LOG_MARK,
 458                             "Setting [%s] to primitive [%s] and datatype [%s]...",
 459                             (char*)columnName,
 460                             osrfHashGet(_f, "primitive"),
 461                             osrfHashGet(_f, "datatype")
 462                             );
 463                 }
 464             }
 465             dbi_result_free(result);
 466         } else {
 467             osrfLogDebug(OSRF_LOG_MARK, "No data found for class [%s]...", (char*)classname);
 468         }
 469     }
 470
 471     osrfStringArrayFree(classes);
 472
 473         child_initialized = 1;
 474     return 0;
 475 }
 476
 477 /*
 478   This function is a sleazy hack intended *only* for testing and
 479   debugging.  Any real server process should initialize the
 480   database connection by calling osrfAppChildInit().
 481 */
 482 void set_cstore_dbi_conn( dbi_conn conn ) {
 483         dbhandle = writehandle = conn;
 484 }
 485
 486 void userDataFree( void* blob ) {
 487     osrfHashFree( (osrfHash*)blob );
 488     return;
 489 }
 490
 491 static void sessionDataFree( char* key, void* item ) {
 492     if (!(strcmp(key,"xact_id"))) {
 493         if (writehandle)
 494             dbi_conn_query(writehandle, "ROLLBACK;");
 495         free(item);
 496     }
 497
 498     return;
 499 }
 500
 501 int beginTransaction ( osrfMethodContext* ctx ) {
 502         if(osrfMethodVerifyContext( ctx )) {
 503                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 504                 return -1;
 505         }
 506
 507 #ifdef PCRUD
 508     jsonObject* user = verifyUserPCRUD( ctx );
 509     if (!user) return -1;
 510     jsonObjectFree(user);
 511 #endif
 512
 513     dbi_result result = dbi_conn_query(writehandle, "START TRANSACTION;");
 514     if (!result) {
 515         osrfLogError(OSRF_LOG_MARK, "%s: Error starting transaction", MODULENAME );
 516         osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error starting transaction" );
 517         return -1;
 518     } else {
 519         jsonObject* ret = jsonNewObject(ctx->session->session_id);
 520         osrfAppRespondComplete( ctx, ret );
 521         jsonObjectFree(ret);
 522
 523         if (!ctx->session->userData) {
 524             ctx->session->userData = osrfNewHash();
 525             osrfHashSetCallback((osrfHash*)ctx->session->userData, &sessionDataFree);
 526         }
 527
 528         osrfHashSet( (osrfHash*)ctx->session->userData, strdup( ctx->session->session_id ), "xact_id" );
 529         ctx->session->userDataFree = &userDataFree;
 530
 531     }
 532     return 0;
 533 }
 534
 535 int setSavepoint ( osrfMethodContext* ctx ) {
 536         if(osrfMethodVerifyContext( ctx )) {
 537                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 538                 return -1;
 539         }
 540
 541     int spNamePos = 0;
 542 #ifdef PCRUD
 543     spNamePos = 1;
 544     jsonObject* user = verifyUserPCRUD( ctx );
 545     if (!user) return -1;
 546     jsonObjectFree(user);
 547 #endif
 548
 549     if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 550         osrfAppSessionStatus(
 551                 ctx->session,
 552                 OSRF_STATUS_INTERNALSERVERERROR,
 553                 "osrfMethodException",
 554                 ctx->request,
 555                 "No active transaction -- required for savepoints"
 556                 );
 557         return -1;
 558     }
 559
 560         const char* spName = jsonObjectGetString(jsonObjectGetIndex(ctx->params, spNamePos));
 561
 562         dbi_result result = dbi_conn_queryf(writehandle, "SAVEPOINT \"%s\";", spName);
 563         if (!result) {
 564                 osrfLogError(
 565                         OSRF_LOG_MARK,
 566                         "%s: Error creating savepoint %s in transaction %s",
 567                         MODULENAME,
 568                         spName,
 569                         osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 570                 );
 571                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 572                                 "osrfMethodException", ctx->request, "Error creating savepoint" );
 573                 return -1;
 574         } else {
 575                 jsonObject* ret = jsonNewObject(spName);
 576                 osrfAppRespondComplete( ctx, ret );
 577                 jsonObjectFree(ret);
 578         }
 579         return 0;
 580 }
 581
 582 int releaseSavepoint ( osrfMethodContext* ctx ) {
 583         if(osrfMethodVerifyContext( ctx )) {
 584                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 585                 return -1;
 586         }
 587
 588         int spNamePos = 0;
 589 #ifdef PCRUD
 590     spNamePos = 1;
 591     jsonObject* user = verifyUserPCRUD( ctx );
 592     if (!user) return -1;
 593     jsonObjectFree(user);
 594 #endif
 595
 596     if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 597         osrfAppSessionStatus(
 598                 ctx->session,
 599                 OSRF_STATUS_INTERNALSERVERERROR,
 600                 "osrfMethodException",
 601                 ctx->request,
 602                 "No active transaction -- required for savepoints"
 603                 );
 604         return -1;
 605     }
 606
 607         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 608
 609     dbi_result result = dbi_conn_queryf(writehandle, "RELEASE SAVEPOINT \"%s\";", spName);
 610     if (!result) {
 611         osrfLogError(
 612                 OSRF_LOG_MARK,
 613                 "%s: Error releasing savepoint %s in transaction %s",
 614                 MODULENAME,
 615                 spName,
 616                 osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 617                 );
 618                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 619                                 "osrfMethodException", ctx->request, "Error releasing savepoint" );
 620         return -1;
 621     } else {
 622         jsonObject* ret = jsonNewObject(spName);
 623         osrfAppRespondComplete( ctx, ret );
 624         jsonObjectFree(ret);
 625     }
 626     return 0;
 627 }
 628
 629 int rollbackSavepoint ( osrfMethodContext* ctx ) {
 630         if(osrfMethodVerifyContext( ctx )) {
 631                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 632                 return -1;
 633         }
 634
 635         int spNamePos = 0;
 636 #ifdef PCRUD
 637     spNamePos = 1;
 638     jsonObject* user = verifyUserPCRUD( ctx );
 639     if (!user) return -1;
 640     jsonObjectFree(user);
 641 #endif
 642
 643     if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 644         osrfAppSessionStatus(
 645                 ctx->session,
 646                 OSRF_STATUS_INTERNALSERVERERROR,
 647                 "osrfMethodException",
 648                 ctx->request,
 649                 "No active transaction -- required for savepoints"
 650                 );
 651         return -1;
 652     }
 653
 654         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 655
 656     dbi_result result = dbi_conn_queryf(writehandle, "ROLLBACK TO SAVEPOINT \"%s\";", spName);
 657     if (!result) {
 658         osrfLogError(
 659                 OSRF_LOG_MARK,
 660                 "%s: Error rolling back savepoint %s in transaction %s",
 661                 MODULENAME,
 662                 spName,
 663                 osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )
 664                 );
 665                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 666                                 "osrfMethodException", ctx->request, "Error rolling back savepoint" );
 667         return -1;
 668     } else {
 669         jsonObject* ret = jsonNewObject(spName);
 670         osrfAppRespondComplete( ctx, ret );
 671         jsonObjectFree(ret);
 672     }
 673     return 0;
 674 }
 675
 676 int commitTransaction ( osrfMethodContext* ctx ) {
 677         if(osrfMethodVerifyContext( ctx )) {
 678                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 679                 return -1;
 680         }
 681
 682 #ifdef PCRUD
 683     jsonObject* user = verifyUserPCRUD( ctx );
 684     if (!user) return -1;
 685     jsonObjectFree(user);
 686 #endif
 687
 688     if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 689         osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "No active transaction to commit" );
 690         return -1;
 691     }
 692
 693     dbi_result result = dbi_conn_query(writehandle, "COMMIT;");
 694     if (!result) {
 695         osrfLogError(OSRF_LOG_MARK, "%s: Error committing transaction", MODULENAME );
 696         osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error committing transaction" );
 697         return -1;
 698     } else {
 699         osrfHashRemove(ctx->session->userData, "xact_id");
 700         jsonObject* ret = jsonNewObject(ctx->session->session_id);
 701         osrfAppRespondComplete( ctx, ret );
 702         jsonObjectFree(ret);
 703     }
 704     return 0;
 705 }
 706
 707 int rollbackTransaction ( osrfMethodContext* ctx ) {
 708         if(osrfMethodVerifyContext( ctx )) {
 709                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 710                 return -1;
 711         }
 712
 713 #ifdef PCRUD
 714     jsonObject* user = verifyUserPCRUD( ctx );
 715     if (!user) return -1;
 716     jsonObjectFree(user);
 717 #endif
 718
 719     if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
 720         osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "No active transaction to roll back" );
 721         return -1;
 722     }
 723
 724     dbi_result result = dbi_conn_query(writehandle, "ROLLBACK;");
 725     if (!result) {
 726         osrfLogError(OSRF_LOG_MARK, "%s: Error rolling back transaction", MODULENAME );
 727         osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, "Error rolling back transaction" );
 728         return -1;
 729     } else {
 730         osrfHashRemove(ctx->session->userData, "xact_id");
 731         jsonObject* ret = jsonNewObject(ctx->session->session_id);
 732         osrfAppRespondComplete( ctx, ret );
 733         jsonObjectFree(ret);
 734     }
 735     return 0;
 736 }
 737
 738 int dispatchCRUDMethod ( osrfMethodContext* ctx ) {
 739         if(osrfMethodVerifyContext( ctx )) {
 740                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 741                 return -1;
 742         }
 743
 744         osrfHash* meta = (osrfHash*) ctx->method->userData;
 745     osrfHash* class_obj = osrfHashGet( meta, "class" );
 746
 747     int err = 0;
 748
 749     const char* methodtype = osrfHashGet(meta, "methodtype");
 750     jsonObject * obj = NULL;
 751
 752     if (!strcmp(methodtype, "create")) {
 753         obj = doCreate(ctx, &err);
 754         osrfAppRespondComplete( ctx, obj );
 755     }
 756     else if (!strcmp(methodtype, "retrieve")) {
 757         obj = doRetrieve(ctx, &err);
 758         osrfAppRespondComplete( ctx, obj );
 759     }
 760     else if (!strcmp(methodtype, "update")) {
 761         obj = doUpdate(ctx, &err);
 762         osrfAppRespondComplete( ctx, obj );
 763     }
 764     else if (!strcmp(methodtype, "delete")) {
 765         obj = doDelete(ctx, &err);
 766         osrfAppRespondComplete( ctx, obj );
 767     }
 768     else if (!strcmp(methodtype, "search")) {
 769
 770         jsonObject* _p = jsonObjectClone( ctx->params );
 771 #ifdef PCRUD
 772         jsonObjectFree(_p);
 773         _p = jsonParseString("[]");
 774         jsonObjectPush(_p, jsonObjectClone(jsonObjectGetIndex(ctx->params, 1)));
 775         jsonObjectPush(_p, jsonObjectClone(jsonObjectGetIndex(ctx->params, 2)));
 776 #endif
 777
 778         obj = doFieldmapperSearch(ctx, class_obj, _p, &err);
 779
 780         jsonObjectFree(_p);
 781         if(err) return err;
 782
 783         jsonObject* cur;
 784         jsonIterator* itr = jsonNewIterator( obj );
 785         while ((cur = jsonIteratorNext( itr ))) {
 786 #ifdef PCRUD
 787             if(!verifyObjectPCRUD(ctx, cur)) continue;
 788 #endif
 789             osrfAppRespond( ctx, cur );
 790         }
 791         jsonIteratorFree(itr);
 792         osrfAppRespondComplete( ctx, NULL );
 793
 794     } else if (!strcmp(methodtype, "id_list")) {
 795
 796         jsonObject* _p = jsonObjectClone( ctx->params );
 797 #ifdef PCRUD
 798         jsonObjectFree(_p);
 799         _p = jsonParseString("[]");
 800         jsonObjectPush(_p, jsonObjectClone(jsonObjectGetIndex(ctx->params, 1)));
 801         jsonObjectPush(_p, jsonObjectClone(jsonObjectGetIndex(ctx->params, 2)));
 802 #endif
 803
 804         if (jsonObjectGetIndex( _p, 1 )) {
 805             jsonObjectRemoveKey( jsonObjectGetIndex( _p, 1 ), "select" );
 806             jsonObjectRemoveKey( jsonObjectGetIndex( _p, 1 ), "no_i18n" );
 807             jsonObjectRemoveKey( jsonObjectGetIndex( _p, 1 ), "flesh" );
 808             jsonObjectRemoveKey( jsonObjectGetIndex( _p, 1 ), "flesh_columns" );
 809         } else {
 810             jsonObjectSetIndex( _p, 1, jsonNewObjectType(JSON_HASH) );
 811         }
 812
 813                 jsonObjectSetKey( jsonObjectGetIndex( _p, 1 ), "no_i18n", jsonNewBoolObject( 1 ) );
 814
 815         jsonObjectSetKey(
 816             jsonObjectGetIndex( _p, 1 ),
 817             "select",
 818             jsonParseStringFmt(
 819                 "{ \"%s\":[\"%s\"] }",
 820                 osrfHashGet( class_obj, "classname" ),
 821                 osrfHashGet( class_obj, "primarykey" )
 822             )
 823         );
 824
 825         obj = doFieldmapperSearch(ctx, class_obj, _p, &err);
 826
 827         jsonObjectFree(_p);
 828         if(err) return err;
 829
 830         jsonObject* cur;
 831         jsonIterator* itr = jsonNewIterator( obj );
 832         while ((cur = jsonIteratorNext( itr ))) {
 833 #ifdef PCRUD
 834             if(!verifyObjectPCRUD(ctx, cur)) continue;
 835 #endif
 836             osrfAppRespond(
 837                     ctx,
 838                     oilsFMGetObject( cur, osrfHashGet( class_obj, "primarykey" ) )
 839                     );
 840         }
 841         jsonIteratorFree(itr);
 842         osrfAppRespondComplete( ctx, NULL );
 843
 844     } else {
 845         osrfAppRespondComplete( ctx, obj );
 846     }
 847
 848     jsonObjectFree(obj);
 849
 850     return err;
 851 }
 852
 853 static int verifyObjectClass ( osrfMethodContext* ctx, const jsonObject* param ) {
 854
 855     int ret = 1;
 856     osrfHash* meta = (osrfHash*) ctx->method->userData;
 857     osrfHash* class = osrfHashGet( meta, "class" );
 858
 859     if (!param->classname || (strcmp( osrfHashGet(class, "classname"), param->classname ))) {
 860
 861         growing_buffer* msg = buffer_init(128);
 862         buffer_fadd(
 863                 msg,
 864                 "%s: %s method for type %s was passed a %s",
 865                 MODULENAME,
 866                 osrfHashGet(meta, "methodtype"),
 867                 osrfHashGet(class, "classname"),
 868                 param->classname
 869                 );
 870
 871         char* m = buffer_release(msg);
 872         osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", ctx->request, m );
 873
 874         free(m);
 875
 876         return 0;
 877     }
 878
 879 #ifdef PCRUD
 880     ret = verifyObjectPCRUD( ctx, param );
 881 #endif
 882
 883     return ret;
 884 }
 885
 886 #ifdef PCRUD
 887
 888 static jsonObject* verifyUserPCRUD( osrfMethodContext* ctx ) {
 889         const char* auth = jsonObjectGetString( jsonObjectGetIndex( ctx->params, 0 ) );
 890     jsonObject* auth_object = jsonNewObject(auth);
 891     jsonObject* user = oilsUtilsQuickReq("open-ils.auth","open-ils.auth.session.retrieve", auth_object);
 892     jsonObjectFree(auth_object);
 893
 894     if (!user->classname || strcmp(user->classname, "au")) {
 895
 896         growing_buffer* msg = buffer_init(128);
 897         buffer_fadd(
 898             msg,
 899             "%s: permacrud received a bad auth token: %s",
 900             MODULENAME,
 901             auth
 902         );
 903
 904         char* m = buffer_release(msg);
 905         osrfAppSessionStatus( ctx->session, OSRF_STATUS_UNAUTHORIZED, "osrfMethodException", ctx->request, m );
 906
 907         free(m);
 908         jsonObjectFree(user);
 909         user = jsonNULL;
 910     }
 911
 912     return user;
 913
 914 }
 915
 916 static int verifyObjectPCRUD (  osrfMethodContext* ctx, const jsonObject* obj ) {
 917
 918     dbhandle = writehandle;
 919
 920     osrfHash* meta = (osrfHash*) ctx->method->userData;
 921     osrfHash* class = osrfHashGet( meta, "class" );
 922     char* method_type = strdup( osrfHashGet(meta, "methodtype") );
 923     int fetch = 0;
 924
 925     if ( ( *method_type == 's' || *method_type == 'i' ) ) {
 926         free(method_type);
 927         method_type = strdup("retrieve"); // search and id_list are equivelant to retrieve for this
 928     } else if ( *method_type == 'u' || *method_type == 'd' ) {
 929         fetch = 1; // MUST go to the db for the object for update and delete
 930     }
 931
 932     osrfHash* pcrud = osrfHashGet( osrfHashGet(class, "permacrud"), method_type );
 933     free(method_type);
 934
 935     if (!pcrud) {
 936         // No permacrud for this method type on this class
 937
 938         growing_buffer* msg = buffer_init(128);
 939         buffer_fadd(
 940             msg,
 941             "%s: %s on class %s has no permacrud IDL entry",
 942             MODULENAME,
 943             osrfHashGet(meta, "methodtype"),
 944             osrfHashGet(class, "classname")
 945         );
 946
 947         char* m = buffer_release(msg);
 948         osrfAppSessionStatus( ctx->session, OSRF_STATUS_FORBIDDEN, "osrfMethodException", ctx->request, m );
 949
 950         free(m);
 951
 952         return 0;
 953     }
 954
 955     jsonObject* user = verifyUserPCRUD( ctx );
 956     if (!user) return 0;
 957
 958     int userid = atoi( oilsFMGetString( user, "id" ) );
 959     jsonObjectFree(user);
 960
 961     osrfStringArray* permission = osrfHashGet(pcrud, "permission");
 962     osrfStringArray* local_context = osrfHashGet(pcrud, "local_context");
 963     osrfHash* foreign_context = osrfHashGet(pcrud, "foreign_context");
 964
 965     osrfStringArray* context_org_array = osrfNewStringArray(1);
 966
 967     int err = 0;
 968     char* pkey_value = NULL;
 969         if ( str_is_true( osrfHashGet(pcrud, "global_required") ) ) {
 970             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions required, fetching top of the org tree" );
 971
 972         // check for perm at top of org tree
 973         jsonObject* _tmp_params = jsonParseString("[{\"parent_ou\":null}]");
 974                 jsonObject* _list = doFieldmapperSearch(ctx, osrfHashGet( oilsIDL(), "aou" ), _tmp_params, &err);
 975
 976         jsonObject* _tree_top = jsonObjectGetIndex(_list, 0);
 977
 978         if (!_tree_top) {
 979             jsonObjectFree(_tmp_params);
 980             jsonObjectFree(_list);
 981
 982             growing_buffer* msg = buffer_init(128);
 983                         OSRF_BUFFER_ADD( msg, MODULENAME );
 984                         OSRF_BUFFER_ADD( msg,
 985                                 ": Internal error, could not find the top of the org tree (parent_ou = NULL)" );
 986
 987             char* m = buffer_release(msg);
 988             osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, m );
 989             free(m);
 990
 991             return 0;
 992         }
 993
 994         osrfStringArrayAdd( context_org_array, oilsFMGetString( _tree_top, "id" ) );
 995             osrfLogDebug( OSRF_LOG_MARK, "top of the org tree is %s", osrfStringArrayGetString(context_org_array, 0) );
 996
 997         jsonObjectFree(_tmp_params);
 998         jsonObjectFree(_list);
 999
1000     } else {
1001             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions not required, fetching context org ids" );
1002             char* pkey = osrfHashGet(class, "primarykey");
1003         jsonObject *param = NULL;
1004
1005         if (obj->classname) {
1006             pkey_value = oilsFMGetString( obj, pkey );
1007             if (!fetch) param = jsonObjectClone(obj);
1008                 osrfLogDebug( OSRF_LOG_MARK, "Object supplied, using primary key value of %s", pkey_value );
1009         } else {
1010             pkey_value = jsonObjectToSimpleString( obj );
1011             fetch = 1;
1012                 osrfLogDebug( OSRF_LOG_MARK, "Object not supplied, using primary key value of %s and retrieving from the database", pkey_value );
1013         }
1014
1015         if (fetch) {
1016             jsonObject* _tmp_params = jsonParseStringFmt("[{\"%s\":\"%s\"}]", pkey, pkey_value);
1017                 jsonObject* _list = doFieldmapperSearch(
1018                 ctx,
1019                 class,
1020                 _tmp_params,
1021                 &err
1022             );
1023
1024             param = jsonObjectClone(jsonObjectGetIndex(_list, 0));
1025
1026             jsonObjectFree(_tmp_params);
1027             jsonObjectFree(_list);
1028         }
1029
1030         if (!param) {
1031             osrfLogDebug( OSRF_LOG_MARK, "Object not found in the database with primary key %s of %s", pkey, pkey_value );
1032
1033             growing_buffer* msg = buffer_init(128);
1034             buffer_fadd(
1035                 msg,
1036                 "%s: no object found with primary key %s of %s",
1037                 MODULENAME,
1038                 pkey,
1039                 pkey_value
1040             );
1041
1042             char* m = buffer_release(msg);
1043             osrfAppSessionStatus(
1044                 ctx->session,
1045                 OSRF_STATUS_INTERNALSERVERERROR,
1046                 "osrfMethodException",
1047                 ctx->request,
1048                 m
1049             );
1050
1051             free(m);
1052             if (pkey_value) free(pkey_value);
1053
1054             return 0;
1055         }
1056
1057         if (local_context->size > 0) {
1058                 osrfLogDebug( OSRF_LOG_MARK, "%d class-local context field(s) specified", local_context->size);
1059             int i = 0;
1060             char* lcontext = NULL;
1061             while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
1062                 osrfStringArrayAdd( context_org_array, oilsFMGetString( param, lcontext ) );
1063                     osrfLogDebug(
1064                     OSRF_LOG_MARK,
1065                     "adding class-local field %s (value: %s) to the context org list",
1066                     lcontext,
1067                     osrfStringArrayGetString(context_org_array, context_org_array->size - 1)
1068                 );
1069             }
1070         }
1071
1072         osrfStringArray* class_list;
1073
1074         if (foreign_context) {
1075             class_list = osrfHashKeys( foreign_context );
1076                 osrfLogDebug( OSRF_LOG_MARK, "%d foreign context classes(s) specified", class_list->size);
1077
1078             if (class_list->size > 0) {
1079
1080                 int i = 0;
1081                 char* class_name = NULL;
1082                 while ( (class_name = osrfStringArrayGetString(class_list, i++)) ) {
1083                     osrfHash* fcontext = osrfHashGet(foreign_context, class_name);
1084
1085                         osrfLogDebug(
1086                         OSRF_LOG_MARK,
1087                         "%d foreign context fields(s) specified for class %s",
1088                         ((osrfStringArray*)osrfHashGet(fcontext,"context"))->size,
1089                         class_name
1090                     );
1091
1092                     char* foreign_pkey = osrfHashGet(fcontext, "field");
1093                     char* foreign_pkey_value = oilsFMGetString(param, osrfHashGet(fcontext, "fkey"));
1094
1095                     jsonObject* _tmp_params = jsonParseStringFmt(
1096                         "[{\"%s\":\"%s\"}]",
1097                         foreign_pkey,
1098                         foreign_pkey_value
1099                     );
1100
1101                         jsonObject* _list = doFieldmapperSearch(
1102                         ctx,
1103                         osrfHashGet( oilsIDL(), class_name ),
1104                         _tmp_params,
1105                         &err
1106                     );
1107
1108                     jsonObject* _fparam = jsonObjectClone(jsonObjectGetIndex(_list, 0));
1109                     jsonObjectFree(_tmp_params);
1110                     jsonObjectFree(_list);
1111
1112                     osrfStringArray* jump_list = osrfHashGet(fcontext, "jump");
1113
1114                     if (_fparam && jump_list) {
1115                         char* flink = NULL;
1116                         int k = 0;
1117                         while ( (flink = osrfStringArrayGetString(jump_list, k++)) && _fparam ) {
1118                             free(foreign_pkey_value);
1119
1120                             osrfHash* foreign_link_hash = oilsIDLFindPath( "/%s/links/%s", _fparam->classname, flink );
1121
1122                             foreign_pkey_value = oilsFMGetString(_fparam, flink);
1123                             foreign_pkey = osrfHashGet( foreign_link_hash, "key" );
1124
1125                             _tmp_params = jsonParseStringFmt(
1126                                 "[{\"%s\":\"%s\"}]",
1127                                 foreign_pkey,
1128                                 foreign_pkey_value
1129                             );
1130
1131                                 _list = doFieldmapperSearch(
1132                                 ctx,
1133                                 osrfHashGet( oilsIDL(), osrfHashGet( foreign_link_hash, "class" ) ),
1134                                 _tmp_params,
1135                                 &err
1136                             );
1137
1138                             _fparam = jsonObjectClone(jsonObjectGetIndex(_list, 0));
1139                             jsonObjectFree(_tmp_params);
1140                             jsonObjectFree(_list);
1141                         }
1142                     }
1143
1144
1145                     if (!_fparam) {
1146
1147                         growing_buffer* msg = buffer_init(128);
1148                         buffer_fadd(
1149                             msg,
1150                             "%s: no object found with primary key %s of %s",
1151                             MODULENAME,
1152                             foreign_pkey,
1153                             foreign_pkey_value
1154                         );
1155
1156                         char* m = buffer_release(msg);
1157                         osrfAppSessionStatus(
1158                             ctx->session,
1159                             OSRF_STATUS_INTERNALSERVERERROR,
1160                             "osrfMethodException",
1161                             ctx->request,
1162                             m
1163                         );
1164
1165                         free(m);
1166                         osrfStringArrayFree(class_list);
1167                         free(foreign_pkey_value);
1168                         jsonObjectFree(param);
1169
1170                         return 0;
1171                     }
1172
1173                     free(foreign_pkey_value);
1174
1175                     int j = 0;
1176                     char* foreign_field = NULL;
1177                     while ( (foreign_field = osrfStringArrayGetString(osrfHashGet(fcontext,"context"), j++)) ) {
1178                         osrfStringArrayAdd( context_org_array, oilsFMGetString( _fparam, foreign_field ) );
1179                             osrfLogDebug(
1180                             OSRF_LOG_MARK,
1181                             "adding foreign class %s field %s (value: %s) to the context org list",
1182                             class_name,
1183                             foreign_field,
1184                             osrfStringArrayGetString(context_org_array, context_org_array->size - 1)
1185                         );
1186                     }
1187
1188                     jsonObjectFree(_fparam);
1189                 }
1190
1191                 osrfStringArrayFree(class_list);
1192             }
1193         }
1194
1195         jsonObjectFree(param);
1196     }
1197
1198     char* context_org = NULL;
1199     char* perm = NULL;
1200     int OK = 0;
1201
1202     if (permission->size == 0) {
1203             osrfLogDebug( OSRF_LOG_MARK, "No permission specified for this action, passing through" );
1204         OK = 1;
1205     }
1206
1207     int i = 0;
1208     while ( (perm = osrfStringArrayGetString(permission, i++)) ) {
1209         int j = 0;
1210         while ( (context_org = osrfStringArrayGetString(context_org_array, j++)) ) {
1211             dbi_result result;
1212
1213             if (pkey_value) {
1214                     osrfLogDebug(
1215                     OSRF_LOG_MARK,
1216                     "Checking object permission [%s] for user %d on object %s (class %s) at org %d",
1217                     perm,
1218                     userid,
1219                     pkey_value,
1220                     osrfHashGet(class, "classname"),
1221                     atoi(context_org)
1222                 );
1223
1224                 result = dbi_conn_queryf(
1225                     writehandle,
1226                     "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
1227                     userid,
1228                     perm,
1229                     osrfHashGet(class, "classname"),
1230                     pkey_value,
1231                     atoi(context_org)
1232                 );
1233
1234                 if (result) {
1235                     osrfLogDebug(
1236                         OSRF_LOG_MARK,
1237                         "Recieved a result for object permission [%s] for user %d on object %s (class %s) at org %d",
1238                         perm,
1239                         userid,
1240                         pkey_value,
1241                         osrfHashGet(class, "classname"),
1242                         atoi(context_org)
1243                     );
1244
1245                     if (dbi_result_first_row(result)) {
1246                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1247                                                 const char* has_perm = jsonObjectGetString( jsonObjectGetKeyConst(return_val, "has_perm") );
1248
1249                             osrfLogDebug(
1250                             OSRF_LOG_MARK,
1251                             "Status of object permission [%s] for user %d on object %s (class %s) at org %d is %s",
1252                             perm,
1253                             userid,
1254                             pkey_value,
1255                             osrfHashGet(class, "classname"),
1256                             atoi(context_org),
1257                             has_perm
1258                         );
1259
1260                         if ( *has_perm == 't' ) OK = 1;
1261                         jsonObjectFree(return_val);
1262                     }
1263
1264                     dbi_result_free(result);
1265                     if (OK) break;
1266                 }
1267             }
1268
1269                 osrfLogDebug( OSRF_LOG_MARK, "Checking non-object permission [%s] for user %d at org %d", perm, userid, atoi(context_org) );
1270             result = dbi_conn_queryf(
1271                 writehandle,
1272                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
1273                 userid,
1274                 perm,
1275                 atoi(context_org)
1276             );
1277
1278                         if (result) {
1279                                 osrfLogDebug( OSRF_LOG_MARK, "Received a result for permission [%s] for user %d at org %d",
1280                                                 perm, userid, atoi(context_org) );
1281                                 if ( dbi_result_first_row(result) ) {
1282                                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1283                                         const char* has_perm = jsonObjectGetString( jsonObjectGetKeyConst(return_val, "has_perm") );
1284                                         osrfLogDebug( OSRF_LOG_MARK, "Status of permission [%s] for user %d at org %d is [%s]",
1285                                                         perm, userid, atoi(context_org), has_perm );
1286                                         if ( *has_perm == 't' ) OK = 1;
1287                                         jsonObjectFree(return_val);
1288                                 }
1289
1290                                 dbi_result_free(result);
1291                                 if (OK) break;
1292                         }
1293
1294         }
1295         if (OK) break;
1296     }
1297
1298     if (pkey_value) free(pkey_value);
1299     osrfStringArrayFree(context_org_array);
1300
1301     return OK;
1302 }
1303 #endif
1304
1305
1306 static jsonObject* doCreate(osrfMethodContext* ctx, int* err ) {
1307
1308         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1309 #ifdef PCRUD
1310         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
1311         jsonObject* options = jsonObjectGetIndex( ctx->params, 2 );
1312 #else
1313         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
1314         jsonObject* options = jsonObjectGetIndex( ctx->params, 1 );
1315 #endif
1316
1317         if (!verifyObjectClass(ctx, target)) {
1318                 *err = -1;
1319                 return jsonNULL;
1320         }
1321
1322         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1323
1324         char* trans_id = NULL;
1325         if( ctx->session && ctx->session->userData )
1326                 trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
1327
1328         if ( !trans_id ) {
1329                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1330
1331                 osrfAppSessionStatus(
1332                         ctx->session,
1333                         OSRF_STATUS_BADREQUEST,
1334                         "osrfMethodException",
1335                         ctx->request,
1336                         "No active transaction -- required for CREATE"
1337                 );
1338                 *err = -1;
1339                 return jsonNULL;
1340         }
1341
1342         // The following test is harmless but redundant.  If a class is
1343         // readonly, we don't register a create method for it.
1344         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
1345                 osrfAppSessionStatus(
1346                         ctx->session,
1347                         OSRF_STATUS_BADREQUEST,
1348                         "osrfMethodException",
1349                         ctx->request,
1350                         "Cannot INSERT readonly class"
1351                 );
1352                 *err = -1;
1353                 return jsonNULL;
1354         }
1355
1356         // Set the last_xact_id
1357         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
1358         if (index > -1) {
1359                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
1360                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
1361         }
1362
1363         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
1364
1365         dbhandle = writehandle;
1366
1367         osrfHash* fields = osrfHashGet(meta, "fields");
1368         char* pkey = osrfHashGet(meta, "primarykey");
1369         char* seq = osrfHashGet(meta, "sequence");
1370
1371         growing_buffer* table_buf = buffer_init(128);
1372         growing_buffer* col_buf = buffer_init(128);
1373         growing_buffer* val_buf = buffer_init(128);
1374
1375         OSRF_BUFFER_ADD(table_buf, "INSERT INTO ");
1376         OSRF_BUFFER_ADD(table_buf, osrfHashGet(meta, "tablename"));
1377         OSRF_BUFFER_ADD_CHAR( col_buf, '(' );
1378         buffer_add(val_buf,"VALUES (");
1379
1380
1381         int i = 0;
1382         int first = 1;
1383         char* field_name;
1384         osrfStringArray* field_list = osrfHashKeys( fields );
1385         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
1386
1387                 osrfHash* field = osrfHashGet( fields, field_name );
1388
1389                 if( str_is_true( osrfHashGet( field, "virtual" ) ) )
1390                         continue;
1391
1392                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
1393
1394                 char* value;
1395                 if (field_object && field_object->classname) {
1396                         value = oilsFMGetString(
1397                                 field_object,
1398                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
1399                         );
1400                 } else {
1401                         value = jsonObjectToSimpleString( field_object );
1402                 }
1403
1404
1405                 if (first) {
1406                         first = 0;
1407                 } else {
1408                         OSRF_BUFFER_ADD_CHAR( col_buf, ',' );
1409                         OSRF_BUFFER_ADD_CHAR( val_buf, ',' );
1410                 }
1411
1412                 buffer_add(col_buf, field_name);
1413
1414                 if (!field_object || field_object->type == JSON_NULL) {
1415                         buffer_add( val_buf, "DEFAULT" );
1416
1417                 } else if ( !strcmp(get_primitive( field ), "number") ) {
1418                         const char* numtype = get_datatype( field );
1419                         if ( !strcmp( numtype, "INT8") ) {
1420                                 buffer_fadd( val_buf, "%lld", atoll(value) );
1421
1422                         } else if ( !strcmp( numtype, "INT") ) {
1423                                 buffer_fadd( val_buf, "%d", atoi(value) );
1424
1425                         } else if ( !strcmp( numtype, "NUMERIC") ) {
1426                                 buffer_fadd( val_buf, "%f", atof(value) );
1427                         }
1428                 } else {
1429                         if ( dbi_conn_quote_string(writehandle, &value) ) {
1430                                 OSRF_BUFFER_ADD( val_buf, value );
1431
1432                         } else {
1433                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
1434                                 osrfAppSessionStatus(
1435                                         ctx->session,
1436                                         OSRF_STATUS_INTERNALSERVERERROR,
1437                                         "osrfMethodException",
1438                                         ctx->request,
1439                                         "Error quoting string -- please see the error log for more details"
1440                                 );
1441                                 free(value);
1442                                 buffer_free(table_buf);
1443                                 buffer_free(col_buf);
1444                                 buffer_free(val_buf);
1445                                 *err = -1;
1446                                 return jsonNULL;
1447                         }
1448                 }
1449
1450                 free(value);
1451
1452         }
1453
1454
1455         OSRF_BUFFER_ADD_CHAR( col_buf, ')' );
1456         OSRF_BUFFER_ADD_CHAR( val_buf, ')' );
1457
1458         char* table_str = buffer_release(table_buf);
1459         char* col_str   = buffer_release(col_buf);
1460         char* val_str   = buffer_release(val_buf);
1461         growing_buffer* sql = buffer_init(128);
1462         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
1463         free(table_str);
1464         free(col_str);
1465         free(val_str);
1466
1467         char* query = buffer_release(sql);
1468
1469         osrfLogDebug(OSRF_LOG_MARK, "%s: Insert SQL [%s]", MODULENAME, query);
1470
1471
1472         dbi_result result = dbi_conn_query(writehandle, query);
1473
1474         jsonObject* obj = NULL;
1475
1476         if (!result) {
1477                 obj = jsonNewObject(NULL);
1478                 osrfLogError(
1479                         OSRF_LOG_MARK,
1480                         "%s ERROR inserting %s object using query [%s]",
1481                         MODULENAME,
1482                         osrfHashGet(meta, "fieldmapper"),
1483                         query
1484                 );
1485                 osrfAppSessionStatus(
1486                         ctx->session,
1487                         OSRF_STATUS_INTERNALSERVERERROR,
1488                         "osrfMethodException",
1489                         ctx->request,
1490                         "INSERT error -- please see the error log for more details"
1491                 );
1492                 *err = -1;
1493         } else {
1494
1495                 char* id = oilsFMGetString(target, pkey);
1496                 if (!id) {
1497                         unsigned long long new_id = dbi_conn_sequence_last(writehandle, seq);
1498                         growing_buffer* _id = buffer_init(10);
1499                         buffer_fadd(_id, "%lld", new_id);
1500                         id = buffer_release(_id);
1501                 }
1502
1503                 // Find quietness specification, if present
1504                 const char* quiet_str = NULL;
1505                 if ( options ) {
1506                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
1507                         if( quiet_obj )
1508                                 quiet_str = jsonObjectGetString( quiet_obj );
1509                 }
1510
1511                 if( str_is_true( quiet_str ) ) {  // if quietness is specified
1512                         obj = jsonNewObject(id);
1513                 }
1514                 else {
1515
1516                         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1517                         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1518
1519                         jsonObjectSetKey(
1520                                 jsonObjectGetIndex(fake_params, 0),
1521                                 pkey,
1522                                 jsonNewObject(id)
1523                         );
1524
1525                         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1526
1527                         if(*err) {
1528                                 jsonObjectFree( fake_params );
1529                                 obj = jsonNULL;
1530                         } else {
1531                                 obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1532                         }
1533
1534                         jsonObjectFree( list );
1535                         jsonObjectFree( fake_params );
1536                 }
1537
1538                 free(id);
1539         }
1540
1541         free(query);
1542
1543         return obj;
1544
1545 }
1546
1547
1548 static jsonObject* doRetrieve(osrfMethodContext* ctx, int* err ) {
1549
1550     int id_pos = 0;
1551     int order_pos = 1;
1552
1553 #ifdef PCRUD
1554     id_pos = 1;
1555     order_pos = 2;
1556 #endif
1557
1558         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1559
1560         const char* id = jsonObjectGetString(jsonObjectGetIndex(ctx->params, id_pos));
1561         jsonObject* order_hash = jsonObjectGetIndex(ctx->params, order_pos);
1562
1563         osrfLogDebug(
1564                 OSRF_LOG_MARK,
1565                 "%s retrieving %s object with primary key value of %s",
1566                 MODULENAME,
1567                 osrfHashGet(meta, "fieldmapper"),
1568                 id
1569         );
1570
1571         jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
1572         jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH));
1573
1574         jsonObjectSetKey(
1575                 jsonObjectGetIndex(fake_params, 0),
1576                 osrfHashGet(meta, "primarykey"),
1577                 jsonObjectClone(jsonObjectGetIndex(ctx->params, id_pos))
1578         );
1579
1580
1581         if (order_hash) jsonObjectPush(fake_params, jsonObjectClone(order_hash) );
1582
1583         jsonObject* list = doFieldmapperSearch( ctx,meta, fake_params, err);
1584
1585         if(*err) {
1586                 jsonObjectFree( fake_params );
1587                 return jsonNULL;
1588         }
1589
1590         jsonObject* obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
1591
1592         jsonObjectFree( list );
1593         jsonObjectFree( fake_params );
1594
1595 #ifdef PCRUD
1596         if(!verifyObjectPCRUD(ctx, obj)) {
1597         jsonObjectFree(obj);
1598         *err = -1;
1599
1600         growing_buffer* msg = buffer_init(128);
1601                 OSRF_BUFFER_ADD( msg, MODULENAME );
1602                 OSRF_BUFFER_ADD( msg, ": Insufficient permissions to retrieve object" );
1603
1604         char* m = buffer_release(msg);
1605         osrfAppSessionStatus( ctx->session, OSRF_STATUS_NOTALLOWED, "osrfMethodException", ctx->request, m );
1606
1607         free(m);
1608
1609                 return jsonNULL;
1610         }
1611 #endif
1612
1613         return obj;
1614 }
1615
1616 static char* jsonNumberToDBString ( osrfHash* field, const jsonObject* value ) {
1617         growing_buffer* val_buf = buffer_init(32);
1618         const char* numtype = get_datatype( field );
1619
1620         if ( !strncmp( numtype, "INT", 3 ) ) {
1621                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
1622                 else {
1623                         const char* val_str = jsonObjectGetString( value );
1624                         buffer_fadd( val_buf, "%ld", atol(val_str) );
1625                 }
1626
1627         } else if ( !strcmp( numtype, "NUMERIC" ) ) {
1628                 if (value->type == JSON_NUMBER) buffer_fadd( val_buf, "%f",  jsonObjectGetNumber(value) );
1629                 else {
1630                         const char* val_str = jsonObjectGetString( value );
1631                         buffer_fadd( val_buf, "%f", atof(val_str) );
1632                 }
1633
1634         } else {
1635                 // Presumably this was really intended ot be a string, so quote it
1636                 char* str = jsonObjectToSimpleString( value );
1637                 if ( dbi_conn_quote_string(dbhandle, &str) ) {
1638                         OSRF_BUFFER_ADD( val_buf, str );
1639                         free(str);
1640                 } else {
1641                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, str);
1642                         free(str);
1643                         buffer_free(val_buf);
1644                         return NULL;
1645                 }
1646         }
1647
1648         return buffer_release(val_buf);
1649 }
1650
1651 static char* searchINPredicate (const char* class, osrfHash* field,
1652                 jsonObject* node, const char* op, osrfMethodContext* ctx ) {
1653         growing_buffer* sql_buf = buffer_init(32);
1654
1655         buffer_fadd(
1656                 sql_buf,
1657                 "\"%s\".%s ",
1658                 class,
1659                 osrfHashGet(field, "name")
1660         );
1661
1662         if (!op) {
1663                 buffer_add(sql_buf, "IN (");
1664         } else if (!(strcasecmp(op,"not in"))) {
1665                 buffer_add(sql_buf, "NOT IN (");
1666         } else {
1667                 buffer_add(sql_buf, "IN (");
1668         }
1669
1670     if (node->type == JSON_HASH) {
1671         // subquery predicate
1672         char* subpred = SELECT(
1673             ctx,
1674             jsonObjectGetKey( node, "select" ),
1675             jsonObjectGetKey( node, "from" ),
1676             jsonObjectGetKey( node, "where" ),
1677             jsonObjectGetKey( node, "having" ),
1678             jsonObjectGetKey( node, "order_by" ),
1679             jsonObjectGetKey( node, "limit" ),
1680             jsonObjectGetKey( node, "offset" ),
1681             SUBSELECT
1682         );
1683
1684         buffer_add(sql_buf, subpred);
1685         free(subpred);
1686
1687     } else if (node->type == JSON_ARRAY) {
1688         // literal value list
1689         int in_item_index = 0;
1690         int in_item_first = 1;
1691         const jsonObject* in_item;
1692         while ( (in_item = jsonObjectGetIndex(node, in_item_index++)) ) {
1693
1694                         if (in_item_first)
1695                                 in_item_first = 0;
1696                         else
1697                                 buffer_add(sql_buf, ", ");
1698
1699                         // Sanity check
1700                         if ( in_item->type != JSON_STRING && in_item->type != JSON_NUMBER ) {
1701                                 osrfLogError(OSRF_LOG_MARK, "%s: Expected string or number within IN list; found %s",
1702                                                 MODULENAME, json_type( in_item->type ) );
1703                                                                         buffer_free(sql_buf);
1704                                 return NULL;
1705                         }
1706
1707                         // Append the literal value -- quoted if not a number
1708                         if ( JSON_NUMBER == in_item->type ) {
1709                                 char* val = jsonNumberToDBString( field, in_item );
1710                                 OSRF_BUFFER_ADD( sql_buf, val );
1711                                 free(val);
1712
1713                         } else if ( !strcmp( get_primitive( field ), "number") ) {
1714                                 char* val = jsonNumberToDBString( field, in_item );
1715                                 OSRF_BUFFER_ADD( sql_buf, val );
1716                                 free(val);
1717
1718                         } else {
1719                                 char* key_string = jsonObjectToSimpleString(in_item);
1720                                 if ( dbi_conn_quote_string(dbhandle, &key_string) ) {
1721                                         OSRF_BUFFER_ADD( sql_buf, key_string );
1722                                         free(key_string);
1723                                 } else {
1724                                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, key_string);
1725                                         free(key_string);
1726                                         buffer_free(sql_buf);
1727                                         return NULL;
1728                                 }
1729                         }
1730                 }
1731
1732                 if( in_item_first ) {
1733                         osrfLogError(OSRF_LOG_MARK, "%s: Empty IN list", MODULENAME );
1734                         buffer_free( sql_buf );
1735                         return NULL;
1736                 }
1737         } else {
1738                 osrfLogError(OSRF_LOG_MARK, "%s: Expected object or array for IN clause; found %s",
1739                         MODULENAME, json_type( node->type ) );
1740                 buffer_free(sql_buf);
1741                 return NULL;
1742         }
1743
1744         OSRF_BUFFER_ADD_CHAR( sql_buf, ')' );
1745
1746         return buffer_release(sql_buf);
1747 }
1748
1749 // Receive a JSON_ARRAY representing a function call.  The first
1750 // entry in the array is the function name.  The rest are parameters.
1751 static char* searchValueTransform( const jsonObject* array ) {
1752
1753         if( array->size < 1 ) {
1754                 osrfLogError(OSRF_LOG_MARK, "%s: Empty array for value transform", MODULENAME);
1755                 return NULL;
1756         }
1757
1758         growing_buffer* sql_buf = buffer_init(32);
1759
1760         // Get the function name
1761         jsonObject* func_item = jsonObjectGetIndex( array, 0 );
1762         OSRF_BUFFER_ADD( sql_buf, jsonObjectGetString( func_item ) );
1763         OSRF_BUFFER_ADD( sql_buf, "( " );
1764
1765         // Get the parameters
1766         int func_item_index = 1;   // We already grabbed the zeroth entry
1767         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1768
1769                 // Add a separator comma, if we need one
1770                 if( func_item_index > 2 )
1771                         buffer_add( sql_buf, ", " );
1772
1773                 // Add the current parameter
1774                 if (func_item->type == JSON_NULL) {
1775                         buffer_add( sql_buf, "NULL" );
1776                 } else {
1777                         char* val = jsonObjectToSimpleString(func_item);
1778                         if ( dbi_conn_quote_string(dbhandle, &val) ) {
1779                                 OSRF_BUFFER_ADD( sql_buf, val );
1780                                 free(val);
1781                         } else {
1782                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1783                                 buffer_free(sql_buf);
1784                                 free(val);
1785                                 return NULL;
1786                         }
1787                 }
1788         }
1789
1790         buffer_add( sql_buf, " )" );
1791
1792         return buffer_release(sql_buf);
1793 }
1794
1795 static char* searchFunctionPredicate (const char* class, osrfHash* field,
1796                 const jsonObject* node, const char* op) {
1797
1798         if( ! is_good_operator( op ) ) {
1799                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", MODULENAME, op );
1800                 return NULL;
1801         }
1802
1803         char* val = searchValueTransform(node);
1804         if( !val )
1805                 return NULL;
1806
1807         growing_buffer* sql_buf = buffer_init(32);
1808         buffer_fadd(
1809                 sql_buf,
1810                 "\"%s\".%s %s %s",
1811                 class,
1812                 osrfHashGet(field, "name"),
1813                 op,
1814                 val
1815         );
1816
1817         free(val);
1818
1819         return buffer_release(sql_buf);
1820 }
1821
1822 // class is a class name
1823 // field is a field definition as stored in the IDL
1824 // node comes from the method parameter, and represents an entry in the SELECT list
1825 static char* searchFieldTransform (const char* class, osrfHash* field, const jsonObject* node) {
1826         growing_buffer* sql_buf = buffer_init(32);
1827
1828         const char* field_transform = jsonObjectGetString( jsonObjectGetKeyConst( node, "transform" ) );
1829         const char* transform_subcolumn = jsonObjectGetString( jsonObjectGetKeyConst( node, "result_field" ) );
1830
1831         if(transform_subcolumn)
1832                 OSRF_BUFFER_ADD_CHAR( sql_buf, '(' );    // enclose transform in parentheses
1833
1834         if (field_transform) {
1835                 buffer_fadd( sql_buf, "%s(\"%s\".%s", field_transform, class, osrfHashGet(field, "name"));
1836                 const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
1837
1838                 if (array) {
1839                         if( array->type != JSON_ARRAY ) {
1840                                 osrfLogError( OSRF_LOG_MARK,
1841                                         "%s: Expected JSON_ARRAY for function params; found %s",
1842                                         MODULENAME, json_type( array->type ) );
1843                                 buffer_free( sql_buf );
1844                                 return NULL;
1845                         }
1846                         int func_item_index = 0;
1847                         jsonObject* func_item;
1848                         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
1849
1850                                 char* val = jsonObjectToSimpleString(func_item);
1851
1852                                 if ( !val ) {
1853                                         buffer_add( sql_buf, ",NULL" );
1854                                 } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
1855                                         OSRF_BUFFER_ADD_CHAR( sql_buf, ',' );
1856                                         OSRF_BUFFER_ADD( sql_buf, val );
1857                                 } else {
1858                                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
1859                                         free(val);
1860                                         buffer_free(sql_buf);
1861                                         return NULL;
1862                         }
1863                                 free(val);
1864                         }
1865                 }
1866
1867                 buffer_add( sql_buf, " )" );
1868
1869         } else {
1870                 buffer_fadd( sql_buf, "\"%s\".%s", class, osrfHashGet(field, "name"));
1871         }
1872
1873         if (transform_subcolumn)
1874                 buffer_fadd( sql_buf, ").\"%s\"", transform_subcolumn );
1875
1876         return buffer_release(sql_buf);
1877 }
1878
1879 static char* searchFieldTransformPredicate (const char* class, osrfHash* field,
1880                 const jsonObject* node, const char* node_key) {
1881         char* field_transform = searchFieldTransform( class, field, node );
1882         char* value = NULL;
1883
1884         const jsonObject* value_obj = jsonObjectGetKeyConst( node, "value" );
1885         if ( ! value_obj ) {
1886                 value = searchWHERE( node, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1887                 if( !value ) {
1888                         osrfLogError(OSRF_LOG_MARK, "%s: Error building condition for field transform", MODULENAME, value);
1889                         free(field_transform);
1890                         return NULL;
1891                 }
1892         } else if ( value_obj->type == JSON_ARRAY ) {
1893                 value = searchValueTransform( value_obj );
1894                 if( !value ) {
1895                         osrfLogError(OSRF_LOG_MARK, "%s: Error building value transform for field transform",
1896                                         MODULENAME, value);
1897                         free( field_transform );
1898                         return NULL;
1899                 }
1900         } else if ( value_obj->type == JSON_HASH ) {
1901                 value = searchWHERE( value_obj, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL );
1902                 if( !value ) {
1903                         osrfLogError(OSRF_LOG_MARK, "%s: Error building predicate for field transform", MODULENAME, value);
1904                         free(field_transform);
1905                         return NULL;
1906                 }
1907         } else if ( value_obj->type == JSON_NUMBER ) {
1908                 value = jsonNumberToDBString( field, value_obj );
1909         } else if ( value_obj->type != JSON_NULL ) {
1910                 if ( !strcmp( get_primitive( field ), "number") ) {
1911                         value = jsonNumberToDBString( field, value_obj );
1912                 } else {
1913                         value = jsonObjectToSimpleString( value_obj );
1914                         if ( !dbi_conn_quote_string(dbhandle, &value) ) {
1915                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, value);
1916                                 free(value);
1917                                 free(field_transform);
1918                                 return NULL;
1919                         }
1920                 }
1921         }
1922
1923         growing_buffer* sql_buf = buffer_init(32);
1924
1925         buffer_fadd(
1926                 sql_buf,
1927                 "%s %s %s",
1928                 field_transform,
1929                 node_key,
1930                 value
1931         );
1932
1933         free(value);
1934         free(field_transform);
1935
1936         return buffer_release(sql_buf);
1937 }
1938
1939 static char* searchSimplePredicate (const char* op, const char* class,
1940                 osrfHash* field, const jsonObject* node) {
1941
1942         if( ! is_good_operator( op ) ) {
1943                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", MODULENAME, op );
1944                 return NULL;
1945         }
1946
1947         char* val = NULL;
1948
1949         // Get the value to which we are comparing the specified column
1950         if (node->type != JSON_NULL) {
1951                 if ( node->type == JSON_NUMBER ) {
1952                         val = jsonNumberToDBString( field, node );
1953                 } else if ( !strcmp( get_primitive( field ), "number" ) ) {
1954                         val = jsonNumberToDBString( field, node );
1955                 } else {
1956                         val = jsonObjectToSimpleString(node);
1957                 }
1958         }
1959
1960         if( val ) {
1961                 if( JSON_NUMBER != node->type && strcmp( get_primitive( field ), "number") ) {
1962                         // Value is not numeric; enclose it in quotes
1963                         if ( !dbi_conn_quote_string( dbhandle, &val ) ) {
1964                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val );
1965                                 free( val );
1966                                 return NULL;
1967                         }
1968                 }
1969         } else {
1970                 // Compare to a null value
1971                 val = strdup( "NULL" );
1972                 if (strcmp( op, "=" ))
1973                         op = "IS NOT";
1974                 else
1975                         op = "IS";
1976         }
1977
1978         growing_buffer* sql_buf = buffer_init(32);
1979         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class, osrfHashGet(field, "name"), op, val );
1980         char* pred = buffer_release( sql_buf );
1981
1982         free(val);
1983
1984         return pred;
1985 }
1986
1987 static char* searchBETWEENPredicate (const char* class, osrfHash* field, const jsonObject* node) {
1988
1989         const jsonObject* x_node = jsonObjectGetIndex( node, 0 );
1990         const jsonObject* y_node = jsonObjectGetIndex( node, 1 );
1991
1992         if( NULL == y_node ) {
1993                 osrfLogError( OSRF_LOG_MARK, "%s: Not enough operands for BETWEEN operator", MODULENAME );
1994                 return NULL;
1995         }
1996         else if( NULL != jsonObjectGetIndex( node, 2 ) ) {
1997                 osrfLogError( OSRF_LOG_MARK, "%s: Too many operands for BETWEEN operator", MODULENAME );
1998                 return NULL;
1999         }
2000
2001         char* x_string;
2002         char* y_string;
2003
2004         if ( !strcmp( get_primitive( field ), "number") ) {
2005                 x_string = jsonNumberToDBString(field, x_node);
2006                 y_string = jsonNumberToDBString(field, y_node);
2007
2008         } else {
2009                 x_string = jsonObjectToSimpleString(x_node);
2010                 y_string = jsonObjectToSimpleString(y_node);
2011                 if ( !(dbi_conn_quote_string(dbhandle, &x_string) && dbi_conn_quote_string(dbhandle, &y_string)) ) {
2012                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]",
2013                                         MODULENAME, x_string, y_string);
2014                         free(x_string);
2015                         free(y_string);
2016                         return NULL;
2017                 }
2018         }
2019
2020         growing_buffer* sql_buf = buffer_init(32);
2021         buffer_fadd( sql_buf, "%s BETWEEN %s AND %s", osrfHashGet(field, "name"), x_string, y_string );
2022         free(x_string);
2023         free(y_string);
2024
2025         return buffer_release(sql_buf);
2026 }
2027
2028 static char* searchPredicate ( const char* class, osrfHash* field,
2029                                                            jsonObject* node, osrfMethodContext* ctx ) {
2030
2031         char* pred = NULL;
2032         if (node->type == JSON_ARRAY) { // equality IN search
2033                 pred = searchINPredicate( class, field, node, NULL, ctx );
2034         } else if (node->type == JSON_HASH) { // non-equality search
2035                 jsonIterator* pred_itr = jsonNewIterator( node );
2036                 if( !jsonIteratorHasNext( pred_itr ) ) {
2037                         osrfLogError( OSRF_LOG_MARK, "%s: Empty predicate for field \"%s\"",
2038                                         MODULENAME, osrfHashGet(field, "name") );
2039                 } else {
2040                         jsonObject* pred_node = jsonIteratorNext( pred_itr );
2041
2042                         // Verify that there are no additional predicates
2043                         if( jsonIteratorHasNext( pred_itr ) ) {
2044                                 osrfLogError( OSRF_LOG_MARK, "%s: Multiple predicates for field \"%s\"",
2045                                                 MODULENAME, osrfHashGet(field, "name") );
2046                         } else if ( !(strcasecmp( pred_itr->key,"between" )) )
2047                                 pred = searchBETWEENPredicate( class, field, pred_node );
2048                         else if ( !(strcasecmp( pred_itr->key,"in" )) || !(strcasecmp( pred_itr->key,"not in" )) )
2049                                 pred = searchINPredicate( class, field, pred_node, pred_itr->key, ctx );
2050                         else if ( pred_node->type == JSON_ARRAY )
2051                                 pred = searchFunctionPredicate( class, field, pred_node, pred_itr->key );
2052                         else if ( pred_node->type == JSON_HASH )
2053                                 pred = searchFieldTransformPredicate( class, field, pred_node, pred_itr->key );
2054                         else
2055                                 pred = searchSimplePredicate( pred_itr->key, class, field, pred_node );
2056                 }
2057                 jsonIteratorFree(pred_itr);
2058
2059         } else if (node->type == JSON_NULL) { // IS NULL search
2060                 growing_buffer* _p = buffer_init(64);
2061                 buffer_fadd(
2062                         _p,
2063                         "\"%s\".%s IS NULL",
2064                         class,
2065                         osrfHashGet(field, "name")
2066                 );
2067                 pred = buffer_release(_p);
2068         } else { // equality search
2069                 pred = searchSimplePredicate( "=", class, field, node );
2070         }
2071
2072         return pred;
2073
2074 }
2075
2076
2077 /*
2078
2079 join : {
2080         acn : {
2081                 field : record,
2082                 fkey : id
2083                 type : left
2084                 filter_op : or
2085                 filter : { ... },
2086                 join : {
2087                         acp : {
2088                                 field : call_number,
2089                                 fkey : id,
2090                                 filter : { ... },
2091                         },
2092                 },
2093         },
2094         mrd : {
2095                 field : record,
2096                 type : inner
2097                 fkey : id,
2098                 filter : { ... },
2099         }
2100 }
2101
2102 */
2103
2104 static char* searchJOIN ( const jsonObject* join_hash, osrfHash* leftmeta ) {
2105
2106         const jsonObject* working_hash;
2107         jsonObject* freeable_hash = NULL;
2108
2109         if (join_hash->type == JSON_STRING) {
2110                 // create a wrapper around a copy of the original
2111                 const char* _tmp = jsonObjectGetString( join_hash );
2112                 freeable_hash = jsonNewObjectType(JSON_HASH);
2113                 jsonObjectSetKey(freeable_hash, _tmp, NULL);
2114                 working_hash = freeable_hash;
2115         }
2116         else {
2117                 if( join_hash->type != JSON_HASH ) {
2118                         osrfLogError(
2119                                 OSRF_LOG_MARK,
2120                                 "%s: JOIN failed; expected JSON object type not found",
2121                                 MODULENAME
2122                         );
2123                         return NULL;
2124                 }
2125                 working_hash = join_hash;
2126         }
2127
2128         growing_buffer* join_buf = buffer_init(128);
2129         const char* leftclass = osrfHashGet(leftmeta, "classname");
2130
2131         jsonObject* snode = NULL;
2132         jsonIterator* search_itr = jsonNewIterator( working_hash );
2133
2134         while ( (snode = jsonIteratorNext( search_itr )) ) {
2135                 const char* class = search_itr->key;
2136                 osrfHash* idlClass = osrfHashGet( oilsIDL(), class );
2137                 if( !idlClass ) {
2138                         osrfLogError(
2139                                 OSRF_LOG_MARK,
2140                                 "%s: JOIN failed.  No class \"%s\" defined in IDL",
2141                                 MODULENAME,
2142                                 search_itr->key
2143                         );
2144                         jsonIteratorFree( search_itr );
2145                         buffer_free( join_buf );
2146                         if( freeable_hash )
2147                                 jsonObjectFree( freeable_hash );
2148                         return NULL;
2149                 }
2150
2151                 const char* fkey = jsonObjectGetString( jsonObjectGetKeyConst( snode, "fkey" ) );
2152                 const char* field = jsonObjectGetString( jsonObjectGetKeyConst( snode, "field" ) );
2153
2154                 if (field && !fkey) {
2155                         fkey = (const char*)oilsIDLFindPath("/%s/links/%s/key", class, field);
2156                         if (!fkey) {
2157                                 osrfLogError(
2158                                         OSRF_LOG_MARK,
2159                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2160                                         MODULENAME,
2161                                         class,
2162                                         field,
2163                                         leftclass
2164                                 );
2165                                 buffer_free(join_buf);
2166                                 if(freeable_hash)
2167                                         jsonObjectFree(freeable_hash);
2168                                 jsonIteratorFree(search_itr);
2169                                 return NULL;
2170                         }
2171
2172                 } else if (!field && fkey) {
2173                         field = (const char*)oilsIDLFindPath("/%s/links/%s/key", leftclass, fkey );
2174                         if (!field) {
2175                                 osrfLogError(
2176                                         OSRF_LOG_MARK,
2177                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2178                                         MODULENAME,
2179                                         leftclass,
2180                                         fkey,
2181                                         class
2182                                 );
2183                                 buffer_free(join_buf);
2184                                 if(freeable_hash)
2185                                         jsonObjectFree(freeable_hash);
2186                                 jsonIteratorFree(search_itr);
2187                                 return NULL;
2188                         }
2189                         field = field;
2190
2191                 } else if (!field && !fkey) {
2192                         osrfHash* _links = oilsIDL_links( leftclass );
2193
2194                         // For each link defined for the left class:
2195                         // see if the link references the joined class
2196                         osrfHashIterator* itr = osrfNewHashIterator( _links );
2197                         osrfHash* curr_link = NULL;
2198                         while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2199                                 const char* other_class = osrfHashGet( curr_link, "class" );
2200                                 if( other_class && !strcmp( other_class, class ) ) {
2201
2202                                         // Found a link between the classes
2203                                         fkey = osrfHashIteratorKey( itr );
2204                                         field = osrfHashGet( curr_link, "key" );
2205                                         break;
2206                                 }
2207                         }
2208                         osrfHashIteratorFree( itr );
2209
2210                         if (!field || !fkey) {
2211                                 // Do another such search, with the classes reversed
2212                                 _links = oilsIDL_links( class );
2213
2214                                 // For each link defined for the joined class:
2215                                 // see if the link references the left class
2216                                 osrfHashIterator* itr = osrfNewHashIterator( _links );
2217                                 osrfHash* curr_link = NULL;
2218                                 while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2219                                         const char* other_class = osrfHashGet( curr_link, "class" );
2220                                         if( other_class && !strcmp( other_class, leftclass ) ) {
2221
2222                                                 // Found a link between the classes
2223                                                 fkey = osrfHashIteratorKey( itr );
2224                                                 field = osrfHashGet( curr_link, "key" );
2225                                                 break;
2226                                         }
2227                                 }
2228                                 osrfHashIteratorFree( itr );
2229                         }
2230
2231                         if (!field || !fkey) {
2232                                 osrfLogError(
2233                                         OSRF_LOG_MARK,
2234                                         "%s: JOIN failed.  No link defined between %s and %s",
2235                                         MODULENAME,
2236                                         leftclass,
2237                                         class
2238                                 );
2239                                 buffer_free(join_buf);
2240                                 if(freeable_hash)
2241                                         jsonObjectFree(freeable_hash);
2242                                 jsonIteratorFree(search_itr);
2243                                 return NULL;
2244                         }
2245
2246                 }
2247
2248                 const char* type = jsonObjectGetString( jsonObjectGetKeyConst( snode, "type" ) );
2249                 if (type) {
2250                         if ( !strcasecmp(type,"left") ) {
2251                                 buffer_add(join_buf, " LEFT JOIN");
2252                         } else if ( !strcasecmp(type,"right") ) {
2253                                 buffer_add(join_buf, " RIGHT JOIN");
2254                         } else if ( !strcasecmp(type,"full") ) {
2255                                 buffer_add(join_buf, " FULL JOIN");
2256                         } else {
2257                                 buffer_add(join_buf, " INNER JOIN");
2258                         }
2259                 } else {
2260                         buffer_add(join_buf, " INNER JOIN");
2261                 }
2262
2263                 char* table = getSourceDefinition(idlClass);
2264                 if( !table ) {
2265                         jsonIteratorFree( search_itr );
2266                         buffer_free( join_buf );
2267                         if( freeable_hash )
2268                                 jsonObjectFree( freeable_hash );
2269                         return NULL;
2270                 }
2271
2272                 buffer_fadd(join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s",
2273                                         table, class, class, field, leftclass, fkey);
2274                 free(table);
2275
2276                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
2277                 if (filter) {
2278                         const char* filter_op = jsonObjectGetString( jsonObjectGetKeyConst( snode, "filter_op" ) );
2279                         if ( filter_op && !strcasecmp("or",filter_op) ) {
2280                                 buffer_add( join_buf, " OR " );
2281                         } else {
2282                                 buffer_add( join_buf, " AND " );
2283                         }
2284
2285                         char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN, NULL );
2286                         if( jpred ) {
2287                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
2288                                 OSRF_BUFFER_ADD( join_buf, jpred );
2289                                 free(jpred);
2290                         } else {
2291                                 osrfLogError(
2292                                         OSRF_LOG_MARK,
2293                                         "%s: JOIN failed.  Invalid conditional expression.",
2294                                         MODULENAME
2295                                 );
2296                                 jsonIteratorFree( search_itr );
2297                                 buffer_free( join_buf );
2298                                 if( freeable_hash )
2299                                         jsonObjectFree( freeable_hash );
2300                                 return NULL;
2301                         }
2302                 }
2303
2304                 buffer_add(join_buf, " ) ");
2305
2306                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
2307                 if (join_filter) {
2308                         char* jpred = searchJOIN( join_filter, idlClass );
2309                         OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
2310                         OSRF_BUFFER_ADD( join_buf, jpred );
2311                         free(jpred);
2312                 }
2313         }
2314
2315         if(freeable_hash)
2316                 jsonObjectFree(freeable_hash);
2317         jsonIteratorFree(search_itr);
2318
2319         return buffer_release(join_buf);
2320 }
2321
2322 /*
2323
2324 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
2325 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
2326 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
2327
2328 Generate code to express a set of conditions, as for a WHERE clause.  Parameters:
2329
2330 search_hash is the JSON expression of the conditions.
2331 meta is the class definition from the IDL, for the relevant table.
2332 opjoin_type indicates whether multiple conditions, if present, should be
2333         connected by AND or OR.
2334 osrfMethodContext is loaded with all sorts of stuff, but all we do with it here is
2335         to pass it to other functions -- and all they do with it is to use the session
2336         and request members to send error messages back to the client.
2337
2338 */
2339
2340 static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type, osrfMethodContext* ctx ) {
2341
2342         osrfLogDebug(
2343         OSRF_LOG_MARK,
2344         "%s: Entering searchWHERE; search_hash addr = %p, meta addr = %p, opjoin_type = %d, ctx addr = %p",
2345         MODULENAME,
2346         search_hash,
2347         meta,
2348         opjoin_type,
2349         ctx
2350     );
2351
2352         growing_buffer* sql_buf = buffer_init(128);
2353
2354         jsonObject* node = NULL;
2355
2356         int first = 1;
2357         if ( search_hash->type == JSON_ARRAY ) {
2358                 osrfLogDebug(OSRF_LOG_MARK, "%s: In WHERE clause, condition type is JSON_ARRAY", MODULENAME);
2359                 jsonIterator* search_itr = jsonNewIterator( search_hash );
2360                 if( !jsonIteratorHasNext( search_itr ) ) {
2361                         osrfLogError(
2362                                 OSRF_LOG_MARK,
2363                                 "%s: Invalid predicate structure: empty JSON array",
2364                                 MODULENAME
2365                         );
2366                         jsonIteratorFree( search_itr );
2367                         buffer_free( sql_buf );
2368                         return NULL;
2369                 }
2370
2371                 while ( (node = jsonIteratorNext( search_itr )) ) {
2372             if (first) {
2373                 first = 0;
2374             } else {
2375                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
2376                 else buffer_add(sql_buf, " AND ");
2377             }
2378
2379             char* subpred = searchWHERE( node, meta, opjoin_type, ctx );
2380                         if( subpred ) {
2381                 buffer_fadd(sql_buf, "( %s )", subpred);
2382                 free(subpred);
2383                         } else {
2384                                 jsonIteratorFree( search_itr );
2385                                 buffer_free( sql_buf );
2386                                 return NULL;
2387                         }
2388         }
2389         jsonIteratorFree(search_itr);
2390
2391         } else if ( search_hash->type == JSON_HASH ) {
2392                 osrfLogDebug(OSRF_LOG_MARK, "%s: In WHERE clause, condition type is JSON_HASH", MODULENAME);
2393                 jsonIterator* search_itr = jsonNewIterator( search_hash );
2394                 if( !jsonIteratorHasNext( search_itr ) ) {
2395                         osrfLogError(
2396                                 OSRF_LOG_MARK,
2397                                 "%s: Invalid predicate structure: empty JSON object",
2398                                 MODULENAME
2399                         );
2400                         jsonIteratorFree( search_itr );
2401                         buffer_free( sql_buf );
2402                         return NULL;
2403                 }
2404
2405                 while ( (node = jsonIteratorNext( search_itr )) ) {
2406
2407             if (first) {
2408                 first = 0;
2409             } else {
2410                 if (opjoin_type == OR_OP_JOIN) buffer_add(sql_buf, " OR ");
2411                 else buffer_add(sql_buf, " AND ");
2412             }
2413
2414                         if ( '+' == search_itr->key[ 0 ] ) {
2415                                 if ( node->type == JSON_STRING ) {
2416                                         // Intended purpose; to allow reference to a Boolean column
2417
2418                                         // Verify that the class alias is not empty
2419                                         if( '\0' == search_itr->key[ 1 ] ) {
2420                                                 osrfLogError(
2421                                                         OSRF_LOG_MARK,
2422                                                         "%s: Table alias is empty",
2423                                                         MODULENAME
2424                                                 );
2425                                                 jsonIteratorFree( search_itr );
2426                                                 buffer_free( sql_buf );
2427                                                 return NULL;
2428                                         }
2429
2430                                         // Verify that the string looks like an identifier.
2431                                         const char* subpred = jsonObjectGetString( node );
2432                                         if( ! is_identifier( subpred ) ) {
2433                                                 osrfLogError(
2434                                                          OSRF_LOG_MARK,
2435                                                         "%s: Invalid boolean identifier in WHERE clause: \"%s\"",
2436                                                         MODULENAME,
2437                                                         subpred
2438                                                 );
2439                                                 jsonIteratorFree( search_itr );
2440                                                 buffer_free( sql_buf );
2441                                                 return NULL;
2442                                         }
2443
2444                                         buffer_fadd(sql_buf, " \"%s\".%s ", search_itr->key + 1, subpred);
2445                                 } else {
2446                                         char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN, ctx );
2447                                         if( subpred ) {
2448                                                 buffer_fadd(sql_buf, "( %s )", subpred);
2449                                                 free(subpred);
2450                                         } else {
2451                                                 jsonIteratorFree( search_itr );
2452                                                 buffer_free( sql_buf );
2453                                                 return NULL;
2454                                         }
2455                                 }
2456                         } else if ( !strcasecmp("-or",search_itr->key) ) {
2457                                 char* subpred = searchWHERE( node, meta, OR_OP_JOIN, ctx );
2458                                 if( subpred ) {
2459                                         buffer_fadd(sql_buf, "( %s )", subpred);
2460                                         free( subpred );
2461                                 } else {
2462                                         buffer_free( sql_buf );
2463                                         return NULL;
2464                                 }
2465                         } else if ( !strcasecmp("-and",search_itr->key) ) {
2466                                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
2467                                 if( subpred ) {
2468                                         buffer_fadd(sql_buf, "( %s )", subpred);
2469                                         free( subpred );
2470                                 } else {
2471                                         buffer_free( sql_buf );
2472                                         return NULL;
2473                                 }
2474                         } else if ( !strcasecmp("-not",search_itr->key) ) {
2475                                 char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx );
2476                                 if( subpred ) {
2477                                         buffer_fadd(sql_buf, " NOT ( %s )", subpred);
2478                                         free( subpred );
2479                                 } else {
2480                                         buffer_free( sql_buf );
2481                                         return NULL;
2482                                 }
2483                         } else if ( !strcasecmp("-exists",search_itr->key) ) {
2484                 char* subpred = SELECT(
2485                     ctx,
2486                     jsonObjectGetKey( node, "select" ),
2487                     jsonObjectGetKey( node, "from" ),
2488                     jsonObjectGetKey( node, "where" ),
2489                     jsonObjectGetKey( node, "having" ),
2490                     jsonObjectGetKey( node, "order_by" ),
2491                     jsonObjectGetKey( node, "limit" ),
2492                     jsonObjectGetKey( node, "offset" ),
2493                     SUBSELECT
2494                 );
2495
2496                 buffer_fadd(sql_buf, "EXISTS ( %s )", subpred);
2497                 free(subpred);
2498             } else if ( !strcasecmp("-not-exists",search_itr->key) ) {
2499                 char* subpred = SELECT(
2500                     ctx,
2501                     jsonObjectGetKey( node, "select" ),
2502                     jsonObjectGetKey( node, "from" ),
2503                     jsonObjectGetKey( node, "where" ),
2504                     jsonObjectGetKey( node, "having" ),
2505                     jsonObjectGetKey( node, "order_by" ),
2506                     jsonObjectGetKey( node, "limit" ),
2507                     jsonObjectGetKey( node, "offset" ),
2508                     SUBSELECT
2509                 );
2510
2511                 buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred);
2512                 free(subpred);
2513             } else {
2514
2515                 char* class = osrfHashGet(meta, "classname");
2516                 osrfHash* fields = osrfHashGet(meta, "fields");
2517                 osrfHash* field = osrfHashGet( fields, search_itr->key );
2518
2519
2520                 if (!field) {
2521                     char* table = getSourceDefinition(meta);
2522                                         if( !table )
2523                                                 table = strdup( "(?)" );
2524                     osrfLogError(
2525                         OSRF_LOG_MARK,
2526                         "%s: Attempt to reference non-existent column %s on %s (%s)",
2527                         MODULENAME,
2528                         search_itr->key,
2529                         table,
2530                         class
2531                     );
2532                     buffer_free(sql_buf);
2533                     free(table);
2534                                         jsonIteratorFree(search_itr);
2535                                         return NULL;
2536                                 }
2537
2538                                 char* subpred = searchPredicate( class, field, node, ctx );
2539                                 if( subpred ) {
2540                                         buffer_add( sql_buf, subpred );
2541                                         free(subpred);
2542                                 } else {
2543                                         buffer_free(sql_buf);
2544                                         jsonIteratorFree(search_itr);
2545                                         return NULL;
2546                                 }
2547                         }
2548                 }
2549                 jsonIteratorFree(search_itr);
2550
2551     } else {
2552         // ERROR ... only hash and array allowed at this level
2553         char* predicate_string = jsonObjectToJSON( search_hash );
2554         osrfLogError(
2555             OSRF_LOG_MARK,
2556             "%s: Invalid predicate structure: %s",
2557             MODULENAME,
2558             predicate_string
2559         );
2560         buffer_free(sql_buf);
2561         free(predicate_string);
2562         return NULL;
2563     }
2564
2565         return buffer_release(sql_buf);
2566 }
2567
2568 char* SELECT (
2569                 /* method context */ osrfMethodContext* ctx,
2570
2571                 /* SELECT   */ jsonObject* selhash,
2572                 /* FROM     */ jsonObject* join_hash,
2573                 /* WHERE    */ jsonObject* search_hash,
2574                 /* HAVING   */ jsonObject* having_hash,
2575                 /* ORDER BY */ jsonObject* order_hash,
2576                 /* LIMIT    */ jsonObject* limit,
2577                 /* OFFSET   */ jsonObject* offset,
2578                 /* flags    */ int flags
2579 ) {
2580         const char* locale = osrf_message_get_last_locale();
2581
2582         // in case we don't get a select list
2583         jsonObject* defaultselhash = NULL;
2584
2585         // general tmp objects
2586         const jsonObject* tmp_const;
2587         jsonObject* selclass = NULL;
2588         jsonObject* selfield = NULL;
2589         jsonObject* snode = NULL;
2590         jsonObject* onode = NULL;
2591
2592         char* string = NULL;
2593         int from_function = 0;
2594         int first = 1;
2595         int gfirst = 1;
2596         //int hfirst = 1;
2597
2598         // the core search class
2599         char* core_class = NULL;
2600
2601         // metadata about the core search class
2602         osrfHash* core_meta = NULL;
2603
2604         // punt if there's no core class
2605         if (!join_hash || ( join_hash->type == JSON_HASH && !join_hash->size )) {
2606                 osrfLogError(
2607                         OSRF_LOG_MARK,
2608                         "%s: FROM clause is missing or empty",
2609                         MODULENAME
2610                 );
2611                 if( ctx )
2612                         osrfAppSessionStatus(
2613                                 ctx->session,
2614                                 OSRF_STATUS_INTERNALSERVERERROR,
2615                                 "osrfMethodException",
2616                                 ctx->request,
2617                                 "FROM clause is missing or empty in JSON query"
2618                         );
2619                 return NULL;
2620         }
2621
2622         // get the core class -- the only key of the top level FROM clause, or a string
2623         if (join_hash->type == JSON_HASH) {
2624                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
2625                 snode = jsonIteratorNext( tmp_itr );
2626
2627                 core_class = strdup( tmp_itr->key );
2628                 join_hash = snode;
2629
2630                 jsonObject* extra = jsonIteratorNext( tmp_itr );
2631
2632                 jsonIteratorFree( tmp_itr );
2633                 snode = NULL;
2634
2635                 // There shouldn't be more than one entry in join_hash
2636                 if( extra ) {
2637                         osrfLogError(
2638                                 OSRF_LOG_MARK,
2639                                 "%s: Malformed FROM clause: extra entry in JSON_HASH",
2640                                 MODULENAME
2641                         );
2642                         if( ctx )
2643                                 osrfAppSessionStatus(
2644                                         ctx->session,
2645                                         OSRF_STATUS_INTERNALSERVERERROR,
2646                                         "osrfMethodException",
2647                                         ctx->request,
2648                                         "Malformed FROM clause in JSON query"
2649                                 );
2650                         free( core_class );
2651                         return NULL;    // Malformed join_hash; extra entry
2652                 }
2653         } else if (join_hash->type == JSON_ARRAY) {
2654                 from_function = 1;
2655                 core_class = jsonObjectToSimpleString( jsonObjectGetIndex(join_hash, 0) );
2656                 selhash = NULL;
2657
2658         } else if (join_hash->type == JSON_STRING) {
2659                 core_class = jsonObjectToSimpleString( join_hash );
2660                 join_hash = NULL;
2661         }
2662         else {
2663                 osrfLogError(
2664                         OSRF_LOG_MARK,
2665                         "%s: FROM clause is unexpected JSON type: %s",
2666                         MODULENAME,
2667                         json_type( join_hash->type )
2668                 );
2669                 if( ctx )
2670                         osrfAppSessionStatus(
2671                                 ctx->session,
2672                                 OSRF_STATUS_INTERNALSERVERERROR,
2673                                 "osrfMethodException",
2674                                 ctx->request,
2675                                 "Ill-formed FROM clause in JSON query"
2676                         );
2677                 free( core_class );
2678                 return NULL;
2679         }
2680
2681         if (!from_function) {
2682                 // Get the IDL class definition for the core class
2683                 core_meta = osrfHashGet( oilsIDL(), core_class );
2684                 if( !core_meta ) {    // Didn't find it?
2685                         osrfLogError(
2686                                 OSRF_LOG_MARK,
2687                                 "%s: SELECT clause references undefined class: \"%s\"",
2688                                 MODULENAME,
2689                                 core_class
2690                         );
2691                         if( ctx )
2692                                 osrfAppSessionStatus(
2693                                         ctx->session,
2694                                         OSRF_STATUS_INTERNALSERVERERROR,
2695                                         "osrfMethodException",
2696                                         ctx->request,
2697                                         "SELECT clause references undefined class in JSON query"
2698                                 );
2699                         free( core_class );
2700                         return NULL;
2701                 }
2702
2703                 // Make sure the class isn't virtual
2704                 if( str_is_true( osrfHashGet( core_meta, "virtual" ) ) ) {
2705                         osrfLogError(
2706                                 OSRF_LOG_MARK,
2707                                 "%s: Core class is virtual: \"%s\"",
2708                                 MODULENAME,
2709                                 core_class
2710                         );
2711                         if( ctx )
2712                                 osrfAppSessionStatus(
2713                                         ctx->session,
2714                                         OSRF_STATUS_INTERNALSERVERERROR,
2715                                         "osrfMethodException",
2716                                         ctx->request,
2717                                         "FROM clause references virtual class in JSON query"
2718                                 );
2719                         free( core_class );
2720                         return NULL;
2721                 }
2722         }
2723
2724         // if the select list is empty, or the core class field list is '*',
2725         // build the default select list ...
2726         if (!selhash) {
2727                 selhash = defaultselhash = jsonNewObjectType(JSON_HASH);
2728                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2729         } else if( selhash->type != JSON_HASH ) {
2730                 osrfLogError(
2731                         OSRF_LOG_MARK,
2732                         "%s: Expected JSON_HASH for SELECT clause; found %s",
2733                         MODULENAME,
2734                         json_type( selhash->type )
2735                 );
2736
2737                 if (ctx)
2738                         osrfAppSessionStatus(
2739                                 ctx->session,
2740                                 OSRF_STATUS_INTERNALSERVERERROR,
2741                                 "osrfMethodException",
2742                                 ctx->request,
2743                                 "Malformed SELECT clause in JSON query"
2744                         );
2745                 free( core_class );
2746                 return NULL;
2747         } else if ( (tmp_const = jsonObjectGetKeyConst( selhash, core_class )) && tmp_const->type == JSON_STRING ) {
2748                 const char* _x = jsonObjectGetString( tmp_const );
2749                 if (!strncmp( "*", _x, 1 )) {
2750                         jsonObjectRemoveKey( selhash, core_class );
2751                         jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
2752                 }
2753         }
2754
2755         // the query buffer
2756         growing_buffer* sql_buf = buffer_init(128);
2757
2758         // temp buffer for the SELECT list
2759         growing_buffer* select_buf = buffer_init(128);
2760         growing_buffer* order_buf = buffer_init(128);
2761         growing_buffer* group_buf = buffer_init(128);
2762         growing_buffer* having_buf = buffer_init(128);
2763
2764         int aggregate_found = 0;     // boolean
2765
2766         // Build a select list
2767         if(from_function)   // From a function we select everything
2768                 OSRF_BUFFER_ADD_CHAR( select_buf, '*' );
2769         else {
2770
2771                 // If we need to build a default list, prepare to do so
2772                 jsonObject* _tmp = jsonObjectGetKey( selhash, core_class );
2773                 if ( _tmp && !_tmp->size ) {
2774
2775                         osrfHash* core_fields = osrfHashGet( core_meta, "fields" );
2776
2777                         osrfHashIterator* field_itr = osrfNewHashIterator( core_fields );
2778                         osrfHash* field_def;
2779                         while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
2780                                 if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
2781                                         // This field is not virtual, so add it to the list
2782                                         jsonObjectPush( _tmp, jsonNewObject( osrfHashIteratorKey( field_itr ) ) );
2783                                 }
2784                         }
2785                         osrfHashIteratorFree( field_itr );
2786                 }
2787
2788                 // Now build the actual select list
2789             int sel_pos = 1;
2790             first = 1;
2791             gfirst = 1;
2792             jsonIterator* selclass_itr = jsonNewIterator( selhash );
2793             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {    // For each class
2794
2795                     // Make sure the class is defined in the IDL
2796                         const char* cname = selclass_itr->key;
2797                         osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
2798                     if (!idlClass) {
2799                                 osrfLogError(
2800                                         OSRF_LOG_MARK,
2801                                         "%s: Selected class \"%s\" not defined in IDL",
2802                                         MODULENAME,
2803                                         cname
2804                                 );
2805
2806                                 if (ctx)
2807                                         osrfAppSessionStatus(
2808                                                 ctx->session,
2809                                                 OSRF_STATUS_INTERNALSERVERERROR,
2810                                                 "osrfMethodException",
2811                                                 ctx->request,
2812                                                 "Selected class is not defined"
2813                                         );
2814                                 jsonIteratorFree( selclass_itr );
2815                                 buffer_free( sql_buf );
2816                                 buffer_free( select_buf );
2817                                 buffer_free( order_buf );
2818                                 buffer_free( group_buf );
2819                                 buffer_free( having_buf );
2820                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2821                                 free( core_class );
2822                                 return NULL;
2823                         }
2824
2825                     // Make sure the target relation is in the join tree.
2826
2827                         // At this point join_hash is a step down from the join_hash we
2828                         // received as a parameter.  If the original was a JSON_STRING,
2829                         // then json_hash is now NULL.  If the original was a JSON_HASH,
2830                         // then json_hash is now the first (and only) entry in it,
2831                         // denoting the core class.  We've already excluded the
2832                         // possibility that the original was a JSON_ARRAY, because in
2833                         // that case from_function would be non-NULL, and we wouldn't
2834                         // be here.
2835
2836                         int class_in_from_clause;    // boolean
2837
2838                     if ( ! strcmp( core_class, cname ))
2839                                 // This is the core class -- no problem
2840                                 class_in_from_clause = 1;
2841                         else {
2842                                 if (!join_hash)
2843                                         // There's only one class in the FROM clause, and this isn't it
2844                                         class_in_from_clause = 0;
2845                                 else if (join_hash->type == JSON_STRING) {
2846                                         // There's only one class in the FROM clause
2847                                         const char* str = jsonObjectGetString(join_hash);
2848                                         if ( strcmp( str, cname ) )
2849                                                 class_in_from_clause = 0;    // This isn't it
2850                                         else
2851                                                 class_in_from_clause = 1;    // This is it
2852                                 } else {
2853                                         jsonObject* found = jsonObjectFindPath(join_hash, "//%s", cname);
2854                                         if ( 0 == found->size )
2855                                                 class_in_from_clause = 0;   // Nowhere in the join tree
2856                                         else
2857                                                 class_in_from_clause = 1;   // Found it
2858                                         jsonObjectFree( found );
2859                                 }
2860                         }
2861
2862                         // If the class isn't in the FROM clause, bail out
2863                         if( ! class_in_from_clause ) {
2864                                 osrfLogError(
2865                                         OSRF_LOG_MARK,
2866                                         "%s: SELECT clause references class not in FROM clause: \"%s\"",
2867                                         MODULENAME,
2868                                         cname
2869                                 );
2870                                 if( ctx )
2871                                         osrfAppSessionStatus(
2872                                                 ctx->session,
2873                                                 OSRF_STATUS_INTERNALSERVERERROR,
2874                                                 "osrfMethodException",
2875                                                 ctx->request,
2876                                                 "Selected class not in FROM clause in JSON query"
2877                                         );
2878                                 jsonIteratorFree( selclass_itr );
2879                                 buffer_free( sql_buf );
2880                                 buffer_free( select_buf );
2881                                 buffer_free( order_buf );
2882                                 buffer_free( group_buf );
2883                                 buffer_free( having_buf );
2884                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2885                                 free( core_class );
2886                                 return NULL;
2887                         }
2888
2889                         // Look up some attributes of the current class, so that we
2890                         // don't have to look them up again for each field
2891                         osrfHash* class_field_set = osrfHashGet( idlClass, "fields" );
2892                         const char* class_pkey = osrfHashGet( idlClass, "primarykey" );
2893                         const char* class_tname = osrfHashGet( idlClass, "tablename" );
2894
2895                     // stitch together the column list ...
2896                     jsonIterator* select_itr = jsonNewIterator( selclass );
2897                     while ( (selfield = jsonIteratorNext( select_itr )) ) {   // for each SELECT column
2898
2899                                 // If we need a separator comma, add one
2900                                 if (first) {
2901                                         first = 0;
2902                                 } else {
2903                                         OSRF_BUFFER_ADD_CHAR( select_buf, ',' );
2904                                 }
2905
2906                                 // ... if it's a string, just toss it on the pile
2907                                 if (selfield->type == JSON_STRING) {
2908
2909                                         // Look up the field in the IDL
2910                                         const char* col_name = jsonObjectGetString( selfield );
2911                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
2912                                         if ( !field_def ) {
2913                                                 // No such field in current class
2914                                                 osrfLogError(
2915                                                         OSRF_LOG_MARK,
2916                                                         "%s: Selected column \"%s\" not defined in IDL for class \"%s\"",
2917                                                         MODULENAME,
2918                                                         col_name,
2919                                                         cname
2920                                                 );
2921                                                 if( ctx )
2922                                                         osrfAppSessionStatus(
2923                                                                 ctx->session,
2924                                                                 OSRF_STATUS_INTERNALSERVERERROR,
2925                                                                 "osrfMethodException",
2926                                                                 ctx->request,
2927                                                                 "Selected column not defined in JSON query"
2928                                                         );
2929                                                 jsonIteratorFree( select_itr );
2930                                                 jsonIteratorFree( selclass_itr );
2931                                                 buffer_free( sql_buf );
2932                                                 buffer_free( select_buf );
2933                                                 buffer_free( order_buf );
2934                                                 buffer_free( group_buf );
2935                                                 buffer_free( having_buf );
2936                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2937                                                 free( core_class );
2938                                                 return NULL;
2939                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
2940                                                 // Virtual field not allowed
2941                                                 osrfLogError(
2942                                                         OSRF_LOG_MARK,
2943                                                         "%s: Selected column \"%s\" for class \"%s\" is virtual",
2944                                                         MODULENAME,
2945                                                         col_name,
2946                                                         cname
2947                                                 );
2948                                                 if( ctx )
2949                                                         osrfAppSessionStatus(
2950                                                                 ctx->session,
2951                                                                 OSRF_STATUS_INTERNALSERVERERROR,
2952                                                                 "osrfMethodException",
2953                                                                 ctx->request,
2954                                                                 "Selected column may not be virtual in JSON query"
2955                                                         );
2956                                                 jsonIteratorFree( select_itr );
2957                                                 jsonIteratorFree( selclass_itr );
2958                                                 buffer_free( sql_buf );
2959                                                 buffer_free( select_buf );
2960                                                 buffer_free( order_buf );
2961                                                 buffer_free( group_buf );
2962                                                 buffer_free( having_buf );
2963                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
2964                                                 free( core_class );
2965                                                 return NULL;
2966                                         }
2967
2968                                         if (locale) {
2969                                                 const char* i18n;
2970                                                 if (flags & DISABLE_I18N)
2971                                                         i18n = NULL;
2972                                                 else
2973                                                         i18n = osrfHashGet(field_def, "i18n");
2974
2975                                                 if( str_is_true( i18n ) ) {
2976                             buffer_fadd( select_buf,
2977                                                                 " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
2978                                                                 class_tname, cname, col_name, class_pkey, cname, class_pkey, locale, col_name );
2979                         } else {
2980                                             buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, col_name );
2981                         }
2982                     } else {
2983                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, col_name );
2984                     }
2985
2986                                 // ... but it could be an object, in which case we check for a Field Transform
2987                                 } else if (selfield->type == JSON_HASH) {
2988
2989                                         const char* col_name = jsonObjectGetString( jsonObjectGetKeyConst( selfield, "column" ) );
2990
2991                                         // Get the field definition from the IDL
2992                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
2993                                         if ( !field_def ) {
2994                                                 // No such field in current class
2995                                                 osrfLogError(
2996                                                         OSRF_LOG_MARK,
2997                                                         "%s: Selected column \"%s\" is not defined in IDL for class \"%s\"",
2998                                                         MODULENAME,
2999                                                         col_name,
3000                                                         cname
3001                                                 );
3002                                                 if( ctx )
3003                                                         osrfAppSessionStatus(
3004                                                                 ctx->session,
3005                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3006                                                                 "osrfMethodException",
3007                                                                 ctx->request,
3008                                                                 "Selected column is not defined in JSON query"
3009                                                         );
3010                                                 jsonIteratorFree( select_itr );
3011                                                 jsonIteratorFree( selclass_itr );
3012                                                 buffer_free( sql_buf );
3013                                                 buffer_free( select_buf );
3014                                                 buffer_free( order_buf );
3015                                                 buffer_free( group_buf );
3016                                                 buffer_free( having_buf );
3017                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3018                                                 free( core_class );
3019                                                 return NULL;
3020                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
3021                                                 // No such field in current class
3022                                                 osrfLogError(
3023                                                         OSRF_LOG_MARK,
3024                                                         "%s: Selected column \"%s\" is virtual for class \"%s\"",
3025                                                         MODULENAME,
3026                                                         col_name,
3027                                                         cname
3028                                                 );
3029                                                 if( ctx )
3030                                                         osrfAppSessionStatus(
3031                                                                 ctx->session,
3032                                                                 OSRF_STATUS_INTERNALSERVERERROR,
3033                                                                 "osrfMethodException",
3034                                                                 ctx->request,
3035                                                                 "Selected column is virtual in JSON query"
3036                                                         );
3037                                                 jsonIteratorFree( select_itr );
3038                                                 jsonIteratorFree( selclass_itr );
3039                                                 buffer_free( sql_buf );
3040                                                 buffer_free( select_buf );
3041                                                 buffer_free( order_buf );
3042                                                 buffer_free( group_buf );
3043                                                 buffer_free( having_buf );
3044                                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3045                                                 free( core_class );
3046                                                 return NULL;
3047                                         }
3048
3049                                         // Decide what to use as a column alias
3050                                         const char* _alias;
3051                                         if ((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
3052                                                 _alias = jsonObjectGetString( tmp_const );
3053                                         } else {         // Use field name as the alias
3054                                                 _alias = col_name;
3055                                         }
3056
3057                                         if (jsonObjectGetKeyConst( selfield, "transform" )) {
3058                                                 char* transform_str = searchFieldTransform(cname, field_def, selfield);
3059                                                 if( transform_str ) {
3060                                                         buffer_fadd(select_buf, " %s AS \"%s\"", transform_str, _alias);
3061                                                         free(transform_str);
3062                                                 } else {
3063                                                         if( ctx )
3064                                                                 osrfAppSessionStatus(
3065                                                                         ctx->session,
3066                                                                         OSRF_STATUS_INTERNALSERVERERROR,
3067                                                                         "osrfMethodException",
3068                                                                         ctx->request,
3069                                                                         "Unable to generate transform function in JSON query"
3070                                                                 );
3071                                                         jsonIteratorFree( select_itr );
3072                                                         jsonIteratorFree( selclass_itr );
3073                                                         buffer_free( sql_buf );
3074                                                         buffer_free( select_buf );
3075                                                         buffer_free( order_buf );
3076                                                         buffer_free( group_buf );
3077                                                         buffer_free( having_buf );
3078                                                         if( defaultselhash ) jsonObjectFree( defaultselhash );
3079                                                         free( core_class );
3080                                                         return NULL;
3081                                                 }
3082                                         } else {
3083
3084                                                 if (locale) {
3085                                                         const char* i18n;
3086                                                         if (flags & DISABLE_I18N)
3087                                                                 i18n = NULL;
3088                                                         else
3089                                                                 i18n = osrfHashGet(field_def, "i18n");
3090
3091                                                         if( str_is_true( i18n ) ) {
3092                                                                 buffer_fadd( select_buf,
3093                                                                         " oils_i18n_xlate('%s', '%s', '%s', '%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
3094                                                                         class_tname, cname, col_name, class_pkey, cname, class_pkey, locale, _alias);
3095                                                         } else {
3096                                                                 buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, _alias);
3097                                                         }
3098                                                 } else {
3099                                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"", cname, col_name, _alias);
3100                                                 }
3101                                         }
3102                                 }
3103                                 else {
3104                                         osrfLogError(
3105                                                 OSRF_LOG_MARK,
3106                                                 "%s: Selected item is unexpected JSON type: %s",
3107                                                 MODULENAME,
3108                                                 json_type( selfield->type )
3109                                         );
3110                                         if( ctx )
3111                                                 osrfAppSessionStatus(
3112                                                         ctx->session,
3113                                                         OSRF_STATUS_INTERNALSERVERERROR,
3114                                                         "osrfMethodException",
3115                                                         ctx->request,
3116                                                         "Ill-formed SELECT item in JSON query"
3117                                                 );
3118                                         jsonIteratorFree( select_itr );
3119                                         jsonIteratorFree( selclass_itr );
3120                                         buffer_free( sql_buf );
3121                                         buffer_free( select_buf );
3122                                         buffer_free( order_buf );
3123                                         buffer_free( group_buf );
3124                                         buffer_free( having_buf );
3125                                         if( defaultselhash ) jsonObjectFree( defaultselhash );
3126                                         free( core_class );
3127                                         return NULL;
3128                                 }
3129
3130                                 const jsonObject* agg_obj = jsonObjectGetKey( selfield, "aggregate" );
3131                                 if( obj_is_true( agg_obj ) )
3132                                         aggregate_found = 1;
3133                                 else {
3134                                         // Append a comma (except for the first one)
3135                                         // and add the column to a GROUP BY clause
3136                                         if (gfirst)
3137                                                 gfirst = 0;
3138                                         else
3139                                                 OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3140
3141                                         buffer_fadd(group_buf, " %d", sel_pos);
3142                                 }
3143
3144 #if 0
3145                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
3146
3147                                         const jsonObject* aggregate_obj = jsonObjectGetKey( selfield, "aggregate" );
3148                                     if ( ! obj_is_true( aggregate_obj ) ) {
3149                                             if (gfirst) {
3150                                                     gfirst = 0;
3151                                             } else {
3152                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3153                                             }
3154
3155                                             buffer_fadd(group_buf, " %d", sel_pos);
3156
3157                                         /*
3158                                     } else if (is_agg = jsonObjectGetKey( selfield, "having" )) {
3159                                             if (gfirst) {
3160                                                     gfirst = 0;
3161                                             } else {
3162                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
3163                                             }
3164
3165                                             _column = searchFieldTransform(cname, field, selfield);
3166                                                 OSRF_BUFFER_ADD_CHAR(group_buf, ' ');
3167                                                 OSRF_BUFFER_ADD(group_buf, _column);
3168                                             _column = searchFieldTransform(cname, field, selfield);
3169                                         */
3170                                     }
3171                             }
3172 #endif
3173
3174                             sel_pos++;
3175                     } // end while -- iterating across SELECT columns
3176
3177             jsonIteratorFree(select_itr);
3178             } // end while -- iterating across classes
3179
3180         jsonIteratorFree(selclass_itr);
3181     }
3182
3183
3184         char* col_list = buffer_release(select_buf);
3185         char* table = NULL;
3186         if (from_function) table = searchValueTransform(join_hash);
3187         else table = getSourceDefinition(core_meta);
3188
3189         if( !table ) {
3190                 if (ctx)
3191                         osrfAppSessionStatus(
3192                                 ctx->session,
3193                                 OSRF_STATUS_INTERNALSERVERERROR,
3194                                 "osrfMethodException",
3195                                 ctx->request,
3196                                 "Unable to identify table for core class"
3197                         );
3198                 free( col_list );
3199                 buffer_free( sql_buf );
3200                 buffer_free( order_buf );
3201                 buffer_free( group_buf );
3202                 buffer_free( having_buf );
3203                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3204                 free( core_class );
3205                 return NULL;
3206         }
3207
3208         // Put it all together
3209         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
3210         free(col_list);
3211         free(table);
3212
3213     if (!from_function) {
3214             // Now, walk the join tree and add that clause
3215             if ( join_hash ) {
3216                     char* join_clause = searchJOIN( join_hash, core_meta );
3217                         if( join_clause ) {
3218                                 buffer_add(sql_buf, join_clause);
3219                         free(join_clause);
3220                         } else {
3221                                 if (ctx)
3222                                         osrfAppSessionStatus(
3223                                                 ctx->session,
3224                                                 OSRF_STATUS_INTERNALSERVERERROR,
3225                                                 "osrfMethodException",
3226                                                 ctx->request,
3227                                                 "Unable to construct JOIN clause(s)"
3228                                         );
3229                                 buffer_free( sql_buf );
3230                                 buffer_free( order_buf );
3231                                 buffer_free( group_buf );
3232                                 buffer_free( having_buf );
3233                                 if( defaultselhash ) jsonObjectFree( defaultselhash );
3234                                 free( core_class );
3235                                 return NULL;
3236                         }
3237             }
3238
3239                 // Build a WHERE clause, if there is one
3240             if ( search_hash ) {
3241                     buffer_add(sql_buf, " WHERE ");
3242
3243                     // and it's on the WHERE clause
3244                     char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN, ctx );
3245
3246                     if (pred) {
3247                                 buffer_add(sql_buf, pred);
3248                                 free(pred);
3249                         } else {
3250                                 if (ctx) {
3251                                 osrfAppSessionStatus(
3252                                         ctx->session,
3253                                         OSRF_STATUS_INTERNALSERVERERROR,
3254                                         "osrfMethodException",
3255                                         ctx->request,
3256                                         "Severe query error in WHERE predicate -- see error log for more details"
3257                                 );
3258                             }
3259                             free(core_class);
3260                             buffer_free(having_buf);
3261                             buffer_free(group_buf);
3262                             buffer_free(order_buf);
3263                             buffer_free(sql_buf);
3264                             if (defaultselhash) jsonObjectFree(defaultselhash);
3265                             return NULL;
3266                     }
3267         }
3268
3269                 // Build a HAVING clause, if there is one
3270             if ( having_hash ) {
3271                     buffer_add(sql_buf, " HAVING ");
3272
3273                     // and it's on the the WHERE clause
3274                     char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN, ctx );
3275
3276                     if (pred) {
3277                                 buffer_add(sql_buf, pred);
3278                                 free(pred);
3279                         } else {
3280                                 if (ctx) {
3281                                 osrfAppSessionStatus(
3282                                         ctx->session,
3283                                         OSRF_STATUS_INTERNALSERVERERROR,
3284                                         "osrfMethodException",
3285                                         ctx->request,
3286                                         "Severe query error in HAVING predicate -- see error log for more details"
3287                                 );
3288                             }
3289                             free(core_class);
3290                             buffer_free(having_buf);
3291                             buffer_free(group_buf);
3292                             buffer_free(order_buf);
3293                             buffer_free(sql_buf);
3294                             if (defaultselhash) jsonObjectFree(defaultselhash);
3295                             return NULL;
3296                     }
3297             }
3298
3299                 // Build an ORDER BY clause, if there is one
3300             first = 1;
3301             jsonIterator* class_itr = jsonNewIterator( order_hash );
3302             while ( (snode = jsonIteratorNext( class_itr )) ) {
3303
3304                     if (!jsonObjectGetKeyConst(selhash,class_itr->key))
3305                             continue;
3306
3307                     if ( snode->type == JSON_HASH ) {
3308
3309                         jsonIterator* order_itr = jsonNewIterator( snode );
3310                             while ( (onode = jsonIteratorNext( order_itr )) ) {
3311
3312                                         if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
3313                                                 continue;
3314
3315                                         const char* direction = NULL;
3316                                         if ( onode->type == JSON_HASH ) {
3317                                                 if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
3318                                                     string = searchFieldTransform(
3319                                                             class_itr->key,
3320                                                             oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
3321                                                             onode
3322                                                     );
3323                                             } else {
3324                                                     growing_buffer* field_buf = buffer_init(16);
3325                                                     buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
3326                                                     string = buffer_release(field_buf);
3327                                             }
3328
3329                                                 if ( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
3330                                                         const char* dir = jsonObjectGetString(tmp_const);
3331                                                         if (!strncasecmp(dir, "d", 1)) {
3332                                                                 direction = " DESC";
3333                                                         } else {
3334                                                                 direction = " ASC";
3335                                                         }
3336                                                 }
3337
3338                                         } else {
3339                                                 string = strdup(order_itr->key);
3340                                                 const char* dir = jsonObjectGetString(onode);
3341                                                 if (!strncasecmp(dir, "d", 1)) {
3342                                                         direction = " DESC";
3343                                                 } else {
3344                                                         direction = " ASC";
3345                                                 }
3346                                         }
3347
3348                                     if (first) {
3349                                             first = 0;
3350                                     } else {
3351                                             buffer_add(order_buf, ", ");
3352                                     }
3353
3354                                     buffer_add(order_buf, string);
3355                                     free(string);
3356
3357                                     if (direction) {
3358                                             buffer_add(order_buf, direction);
3359                                     }
3360
3361                             } // end while
3362                 // jsonIteratorFree(order_itr);
3363
3364                     } else if ( snode->type == JSON_ARRAY ) {
3365
3366                         jsonIterator* order_itr = jsonNewIterator( snode );
3367                             while ( (onode = jsonIteratorNext( order_itr )) ) {
3368
3369                                         const char* _f = jsonObjectGetString( onode );
3370
3371                                         if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, _f))
3372                                                 continue;
3373
3374                                         if (first) {
3375                                                 first = 0;
3376                                         } else {
3377                                                 buffer_add(order_buf, ", ");
3378                                         }
3379
3380                                         buffer_add(order_buf, _f);
3381
3382                                 } // end while
3383                 // jsonIteratorFree(order_itr);
3384
3385
3386                     // IT'S THE OOOOOOOOOOOLD STYLE!
3387                     } else {
3388                             osrfLogError(OSRF_LOG_MARK, "%s: Possible SQL injection attempt; direct order by is not allowed", MODULENAME);
3389                             if (ctx) {
3390                                 osrfAppSessionStatus(
3391                                         ctx->session,
3392                                         OSRF_STATUS_INTERNALSERVERERROR,
3393                                         "osrfMethodException",
3394                                         ctx->request,
3395                                         "Severe query error -- see error log for more details"
3396                                 );
3397                             }
3398
3399                             free(core_class);
3400                             buffer_free(having_buf);
3401                             buffer_free(group_buf);
3402                             buffer_free(order_buf);
3403                             buffer_free(sql_buf);
3404                             if (defaultselhash) jsonObjectFree(defaultselhash);
3405                             jsonIteratorFree(class_itr);
3406                             return NULL;
3407                     }
3408
3409             } // end while
3410                 // jsonIteratorFree(class_itr);
3411         }
3412
3413
3414         string = buffer_release(group_buf);
3415
3416         if ( *string && ( aggregate_found || (flags & SELECT_DISTINCT) ) ) {
3417                 OSRF_BUFFER_ADD( sql_buf, " GROUP BY " );
3418                 OSRF_BUFFER_ADD( sql_buf, string );
3419         }
3420
3421         free(string);
3422
3423         string = buffer_release(having_buf);
3424
3425         if ( *string ) {
3426                 OSRF_BUFFER_ADD( sql_buf, " HAVING " );
3427                 OSRF_BUFFER_ADD( sql_buf, string );
3428         }
3429
3430         free(string);
3431
3432         string = buffer_release(order_buf);
3433
3434         if ( *string ) {
3435                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
3436                 OSRF_BUFFER_ADD( sql_buf, string );
3437         }
3438
3439         free(string);
3440
3441         if ( limit ){
3442                 const char* str = jsonObjectGetString(limit);
3443                 buffer_fadd( sql_buf, " LIMIT %d", atoi(str) );
3444         }
3445
3446         if (offset) {
3447                 const char* str = jsonObjectGetString(offset);
3448                 buffer_fadd( sql_buf, " OFFSET %d", atoi(str) );
3449         }
3450
3451         if (!(flags & SUBSELECT)) OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
3452
3453         free(core_class);
3454         if (defaultselhash) jsonObjectFree(defaultselhash);
3455
3456         return buffer_release(sql_buf);
3457
3458 }
3459
3460 static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrfHash* meta, osrfMethodContext* ctx ) {
3461
3462         const char* locale = osrf_message_get_last_locale();
3463
3464         osrfHash* fields = osrfHashGet(meta, "fields");
3465         char* core_class = osrfHashGet(meta, "classname");
3466
3467         const jsonObject* join_hash = jsonObjectGetKeyConst( order_hash, "join" );
3468
3469         jsonObject* node = NULL;
3470         jsonObject* snode = NULL;
3471         jsonObject* onode = NULL;
3472         const jsonObject* _tmp = NULL;
3473         jsonObject* selhash = NULL;
3474         jsonObject* defaultselhash = NULL;
3475
3476         growing_buffer* sql_buf = buffer_init(128);
3477         growing_buffer* select_buf = buffer_init(128);
3478
3479         if ( !(selhash = jsonObjectGetKey( order_hash, "select" )) ) {
3480                 defaultselhash = jsonNewObjectType(JSON_HASH);
3481                 selhash = defaultselhash;
3482         }
3483
3484         if ( !jsonObjectGetKeyConst(selhash,core_class) ) {
3485                 jsonObjectSetKey( selhash, core_class, jsonNewObjectType(JSON_ARRAY) );
3486                 jsonObject* flist = jsonObjectGetKey( selhash, core_class );
3487
3488                 int i = 0;
3489                 char* field;
3490
3491                 osrfStringArray* keys = osrfHashKeys( fields );
3492                 while ( (field = osrfStringArrayGetString(keys, i++)) ) {
3493                         if( ! str_is_true( osrfHashGet( osrfHashGet( fields, field ), "virtual" ) ) )
3494                                 jsonObjectPush( flist, jsonNewObject( field ) );
3495                 }
3496                 osrfStringArrayFree(keys);
3497         }
3498
3499         int first = 1;
3500         jsonIterator* class_itr = jsonNewIterator( selhash );
3501         while ( (snode = jsonIteratorNext( class_itr )) ) {
3502
3503                 char* cname = class_itr->key;
3504                 osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
3505                 if (!idlClass) continue;
3506
3507                 if (strcmp(core_class,class_itr->key)) {
3508                         if (!join_hash) continue;
3509
3510                         jsonObject* found =  jsonObjectFindPath(join_hash, "//%s", class_itr->key);
3511                         if (!found->size) {
3512                                 jsonObjectFree(found);
3513                                 continue;
3514                         }
3515
3516                         jsonObjectFree(found);
3517                 }
3518
3519                 jsonIterator* select_itr = jsonNewIterator( snode );
3520                 while ( (node = jsonIteratorNext( select_itr )) ) {
3521                         const char* item_str = jsonObjectGetString( node );
3522                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
3523                         char* fname = osrfHashGet(field, "name");
3524
3525                         if (!field) continue;
3526
3527                         if (first) {
3528                                 first = 0;
3529                         } else {
3530                                 OSRF_BUFFER_ADD_CHAR(select_buf, ',');
3531                         }
3532
3533             if (locale) {
3534                         const char* i18n;
3535                                 const jsonObject* no_i18n_obj = jsonObjectGetKey( order_hash, "no_i18n" );
3536                                 if ( obj_is_true( no_i18n_obj ) )    // Suppress internationalization?
3537                                         i18n = NULL;
3538                                 else
3539                                         i18n = osrfHashGet(field, "i18n");
3540
3541                                 if( str_is_true( i18n ) ) {
3542                         char* pkey = osrfHashGet(idlClass, "primarykey");
3543                         char* tname = osrfHashGet(idlClass, "tablename");
3544
3545                     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);
3546                 } else {
3547                                 buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
3548                 }
3549             } else {
3550                             buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
3551             }
3552                 }
3553
3554         jsonIteratorFree(select_itr);
3555         }
3556
3557     jsonIteratorFree(class_itr);
3558
3559         char* col_list = buffer_release(select_buf);
3560         char* table = getSourceDefinition(meta);
3561         if( !table )
3562                 table = strdup( "(null)" );
3563
3564         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
3565         free(col_list);
3566         free(table);
3567
3568         if ( join_hash ) {
3569                 char* join_clause = searchJOIN( join_hash, meta );
3570                 OSRF_BUFFER_ADD_CHAR(sql_buf, ' ');
3571                 OSRF_BUFFER_ADD(sql_buf, join_clause);
3572                 free(join_clause);
3573         }
3574
3575         osrfLogDebug(OSRF_LOG_MARK, "%s pre-predicate SQL =  %s",
3576                                  MODULENAME, OSRF_BUFFER_C_STR(sql_buf));
3577
3578         buffer_add(sql_buf, " WHERE ");
3579
3580         char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN, ctx );
3581         if (!pred) {
3582                 osrfAppSessionStatus(
3583                         ctx->session,
3584                         OSRF_STATUS_INTERNALSERVERERROR,
3585                                 "osrfMethodException",
3586                                 ctx->request,
3587                                 "Severe query error -- see error log for more details"
3588                         );
3589                 buffer_free(sql_buf);
3590                 if(defaultselhash) jsonObjectFree(defaultselhash);
3591                 return NULL;
3592         } else {
3593                 buffer_add(sql_buf, pred);
3594                 free(pred);
3595         }
3596
3597         if (order_hash) {
3598                 char* string = NULL;
3599                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "order_by" )) ){
3600
3601                         growing_buffer* order_buf = buffer_init(128);
3602
3603                         first = 1;
3604                         jsonIterator* class_itr = jsonNewIterator( _tmp );
3605                         while ( (snode = jsonIteratorNext( class_itr )) ) {
3606
3607                                 if (!jsonObjectGetKeyConst(selhash,class_itr->key))
3608                                         continue;
3609
3610                                 if ( snode->type == JSON_HASH ) {
3611
3612                                         jsonIterator* order_itr = jsonNewIterator( snode );
3613                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
3614
3615                                                 if (!oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ))
3616                                                         continue;
3617
3618                                                 char* direction = NULL;
3619                                                 if ( onode->type == JSON_HASH ) {
3620                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
3621                                                                 string = searchFieldTransform(
3622                                                                         class_itr->key,
3623                                                                         oilsIDLFindPath( "/%s/fields/%s", class_itr->key, order_itr->key ),
3624                                                                         onode
3625                                                                 );
3626                                                         } else {
3627                                                                 growing_buffer* field_buf = buffer_init(16);
3628                                                                 buffer_fadd(field_buf, "\"%s\".%s", class_itr->key, order_itr->key);
3629                                                                 string = buffer_release(field_buf);
3630                                                         }
3631
3632                                                         if ( (_tmp = jsonObjectGetKeyConst( onode, "direction" )) ) {
3633                                                                 const char* dir = jsonObjectGetString(_tmp);
3634                                                                 if (!strncasecmp(dir, "d", 1)) {
3635                                                                         direction = " DESC";
3636                                                                 } else {
3637                                                                         free(direction);
3638                                                                 }
3639                                                         }
3640
3641                                                 } else {
3642                                                         string = strdup(order_itr->key);
3643                                                         const char* dir = jsonObjectGetString(onode);
3644                                                         if (!strncasecmp(dir, "d", 1)) {
3645                                                                 direction = " DESC";
3646                                                         } else {
3647                                                                 direction = " ASC";
3648                                                         }
3649                                                 }
3650
3651                                                 if (first) {
3652                                                         first = 0;
3653                                                 } else {
3654                                                         buffer_add(order_buf, ", ");
3655                                                 }
3656
3657                                                 buffer_add(order_buf, string);
3658                                                 free(string);
3659
3660                                                 if (direction) {
3661                                                         buffer_add(order_buf, direction);
3662                                                 }
3663
3664                                         }
3665
3666                     jsonIteratorFree(order_itr);
3667
3668                                 } else {
3669                                         const char* str = jsonObjectGetString(snode);
3670                                         buffer_add(order_buf, str);
3671                                         break;
3672                                 }
3673
3674                         }
3675
3676                         jsonIteratorFree(class_itr);
3677
3678                         string = buffer_release(order_buf);
3679
3680                         if ( *string ) {
3681                                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
3682                                 OSRF_BUFFER_ADD( sql_buf, string );
3683                         }
3684
3685                         free(string);
3686                 }
3687
3688                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "limit" )) ){
3689                         const char* str = jsonObjectGetString(_tmp);
3690                         buffer_fadd(
3691                                 sql_buf,
3692                                 " LIMIT %d",
3693                                 atoi(str)
3694                         );
3695                 }
3696
3697                 _tmp = jsonObjectGetKeyConst( order_hash, "offset" );
3698                 if (_tmp) {
3699                         const char* str = jsonObjectGetString(_tmp);
3700                         buffer_fadd(
3701                                 sql_buf,
3702                                 " OFFSET %d",
3703                                 atoi(str)
3704                         );
3705                 }
3706         }
3707
3708         if (defaultselhash) jsonObjectFree(defaultselhash);
3709
3710         OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
3711         return buffer_release(sql_buf);
3712 }
3713
3714 int doJSONSearch ( osrfMethodContext* ctx ) {
3715         if(osrfMethodVerifyContext( ctx )) {
3716                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
3717                 return -1;
3718         }
3719
3720         osrfLogDebug(OSRF_LOG_MARK, "Recieved query request");
3721
3722         int err = 0;
3723
3724         // XXX for now...
3725         dbhandle = writehandle;
3726
3727         jsonObject* hash = jsonObjectGetIndex(ctx->params, 0);
3728
3729         int flags = 0;
3730
3731         if ( obj_is_true( jsonObjectGetKey( hash, "distinct" ) ) )
3732                 flags |= SELECT_DISTINCT;
3733
3734         if ( obj_is_true( jsonObjectGetKey( hash, "no_i18n" ) ) )
3735                 flags |= DISABLE_I18N;
3736
3737         osrfLogDebug(OSRF_LOG_MARK, "Building SQL ...");
3738         char* sql = SELECT(
3739                         ctx,
3740                         jsonObjectGetKey( hash, "select" ),
3741                         jsonObjectGetKey( hash, "from" ),
3742                         jsonObjectGetKey( hash, "where" ),
3743                         jsonObjectGetKey( hash, "having" ),
3744                         jsonObjectGetKey( hash, "order_by" ),
3745                         jsonObjectGetKey( hash, "limit" ),
3746                         jsonObjectGetKey( hash, "offset" ),
3747                         flags
3748         );
3749
3750         if (!sql) {
3751                 err = -1;
3752                 return err;
3753         }
3754
3755         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
3756         dbi_result result = dbi_conn_query(dbhandle, sql);
3757
3758         if(result) {
3759                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
3760
3761                 if (dbi_result_first_row(result)) {
3762                         /* JSONify the result */
3763                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
3764
3765                         do {
3766                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
3767                                 osrfAppRespond( ctx, return_val );
3768                 jsonObjectFree( return_val );
3769                         } while (dbi_result_next_row(result));
3770
3771                 } else {
3772                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
3773                 }
3774
3775                 osrfAppRespondComplete( ctx, NULL );
3776
3777                 /* clean up the query */
3778                 dbi_result_free(result);
3779
3780         } else {
3781                 err = -1;
3782                 osrfLogError(OSRF_LOG_MARK, "%s: Error with query [%s]", MODULENAME, sql);
3783                 osrfAppSessionStatus(
3784                         ctx->session,
3785                         OSRF_STATUS_INTERNALSERVERERROR,
3786                         "osrfMethodException",
3787                         ctx->request,
3788                         "Severe query error -- see error log for more details"
3789                 );
3790         }
3791
3792         free(sql);
3793         return err;
3794 }
3795
3796 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* meta,
3797                 const jsonObject* params, int* err ) {
3798
3799         // XXX for now...
3800         dbhandle = writehandle;
3801
3802         osrfHash* links = osrfHashGet(meta, "links");
3803         osrfHash* fields = osrfHashGet(meta, "fields");
3804         char* core_class = osrfHashGet(meta, "classname");
3805         char* pkey = osrfHashGet(meta, "primarykey");
3806
3807         const jsonObject* _tmp;
3808         jsonObject* obj;
3809         jsonObject* search_hash = jsonObjectGetIndex(params, 0);
3810         jsonObject* order_hash = jsonObjectGetIndex(params, 1);
3811
3812         char* sql = buildSELECT( search_hash, order_hash, meta, ctx );
3813         if (!sql) {
3814                 osrfLogDebug(OSRF_LOG_MARK, "Problem building query, returning NULL");
3815                 *err = -1;
3816                 return NULL;
3817         }
3818
3819         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
3820
3821         dbi_result result = dbi_conn_query(dbhandle, sql);
3822         if( NULL == result ) {
3823                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]",
3824                         MODULENAME, osrfHashGet(meta, "fieldmapper"), sql);
3825                 osrfAppSessionStatus(
3826                         ctx->session,
3827                         OSRF_STATUS_INTERNALSERVERERROR,
3828                         "osrfMethodException",
3829                         ctx->request,
3830                         "Severe query error -- see error log for more details"
3831                 );
3832                 *err = -1;
3833                 free(sql);
3834                 return jsonNULL;
3835
3836         } else {
3837                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
3838         }
3839
3840         jsonObject* res_list = jsonNewObjectType(JSON_ARRAY);
3841         osrfHash* dedup = osrfNewHash();
3842
3843         if (dbi_result_first_row(result)) {
3844                 /* JSONify the result */
3845                 osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
3846                 do {
3847                         obj = oilsMakeFieldmapperFromResult( result, meta );
3848                         char* pkey_val = oilsFMGetString( obj, pkey );
3849                         if ( osrfHashGet( dedup, pkey_val ) ) {
3850                                 jsonObjectFree(obj);
3851                                 free(pkey_val);
3852                         } else {
3853                                 osrfHashSet( dedup, pkey_val, pkey_val );
3854                                 jsonObjectPush(res_list, obj);
3855                         }
3856                 } while (dbi_result_next_row(result));
3857         } else {
3858                 osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s",
3859                         MODULENAME, sql );
3860         }
3861
3862         osrfHashFree(dedup);
3863         /* clean up the query */
3864         dbi_result_free(result);
3865         free(sql);
3866
3867         if (res_list->size && order_hash) {
3868                 _tmp = jsonObjectGetKeyConst( order_hash, "flesh" );
3869                 if (_tmp) {
3870                         int x = (int)jsonObjectGetNumber(_tmp);
3871                         if (x == -1 || x > max_flesh_depth) x = max_flesh_depth;
3872
3873                         const jsonObject* temp_blob;
3874                         if ((temp_blob = jsonObjectGetKeyConst( order_hash, "flesh_fields" )) && x > 0) {
3875
3876                                 jsonObject* flesh_blob = jsonObjectClone( temp_blob );
3877                                 const jsonObject* flesh_fields = jsonObjectGetKeyConst( flesh_blob, core_class );
3878
3879                                 osrfStringArray* link_fields = NULL;
3880
3881                                 if (flesh_fields) {
3882                                         if (flesh_fields->size == 1) {
3883                                                 const char* _t = jsonObjectGetString( jsonObjectGetIndex( flesh_fields, 0 ) );
3884                                                 if (!strcmp(_t,"*")) link_fields = osrfHashKeys( links );
3885                                         }
3886
3887                                         if (!link_fields) {
3888                                                 jsonObject* _f;
3889                                                 link_fields = osrfNewStringArray(1);
3890                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
3891                                                 while ((_f = jsonIteratorNext( _i ))) {
3892                                                         osrfStringArrayAdd( link_fields, jsonObjectGetString( _f ) );
3893                                                 }
3894                         jsonIteratorFree(_i);
3895                                         }
3896                                 }
3897
3898                                 jsonObject* cur;
3899                                 jsonIterator* itr = jsonNewIterator( res_list );
3900                                 while ((cur = jsonIteratorNext( itr ))) {
3901
3902                                         int i = 0;
3903                                         char* link_field;
3904
3905                                         while ( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
3906
3907                                                 osrfLogDebug(OSRF_LOG_MARK, "Starting to flesh %s", link_field);
3908
3909                                                 osrfHash* kid_link = osrfHashGet(links, link_field);
3910                                                 if (!kid_link) continue;
3911
3912                                                 osrfHash* field = osrfHashGet(fields, link_field);
3913                                                 if (!field) continue;
3914
3915                                                 osrfHash* value_field = field;
3916
3917                                                 osrfHash* kid_idl = osrfHashGet(oilsIDL(), osrfHashGet(kid_link, "class"));
3918                                                 if (!kid_idl) continue;
3919
3920                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
3921                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3922                                                 }
3923
3924                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) { // might_have
3925                                                         value_field = osrfHashGet( fields, osrfHashGet(meta, "primarykey") );
3926                                                 }
3927
3928                                                 osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
3929
3930                                                 if (link_map->size > 0) {
3931                                                         jsonObject* _kid_key = jsonNewObjectType(JSON_ARRAY);
3932                                                         jsonObjectPush(
3933                                                                 _kid_key,
3934                                                                 jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
3935                                                         );
3936
3937                                                         jsonObjectSetKey(
3938                                                                 flesh_blob,
3939                                                                 osrfHashGet(kid_link, "class"),
3940                                                                 _kid_key
3941                                                         );
3942                                                 };
3943
3944                                                 osrfLogDebug(
3945                                                         OSRF_LOG_MARK,
3946                                                         "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
3947                                                         osrfHashGet(kid_link, "field"),
3948                                                         osrfHashGet(kid_link, "class"),
3949                                                         osrfHashGet(kid_link, "key"),
3950                                                         osrfHashGet(kid_link, "reltype")
3951                                                 );
3952
3953                                                 jsonObject* fake_params = jsonNewObjectType(JSON_ARRAY);
3954                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // search hash
3955                                                 jsonObjectPush(fake_params, jsonNewObjectType(JSON_HASH)); // order/flesh hash
3956
3957                                                 osrfLogDebug(OSRF_LOG_MARK, "Creating dummy params object...");
3958
3959                                                 const char* search_key = jsonObjectGetString(
3960                                                         jsonObjectGetIndex(
3961                                                                 cur,
3962                                                                 atoi( osrfHashGet(value_field, "array_position") )
3963                                                         )
3964                                                 );
3965
3966                                                 if (!search_key) {
3967                                                         osrfLogDebug(OSRF_LOG_MARK, "Nothing to search for!");
3968                                                         continue;
3969                                                 }
3970
3971                                                 jsonObjectSetKey(
3972                                                         jsonObjectGetIndex(fake_params, 0),
3973                                                         osrfHashGet(kid_link, "key"),
3974                                                         jsonNewObject( search_key )
3975                                                 );
3976
3977                                                 jsonObjectSetKey(
3978                                                         jsonObjectGetIndex(fake_params, 1),
3979                                                         "flesh",
3980                                                         jsonNewNumberObject( (double)(x - 1 + link_map->size) )
3981                                                 );
3982
3983                                                 if (flesh_blob)
3984                                                         jsonObjectSetKey( jsonObjectGetIndex(fake_params, 1), "flesh_fields", jsonObjectClone(flesh_blob) );
3985
3986                                                 if (jsonObjectGetKeyConst(order_hash, "order_by")) {
3987                                                         jsonObjectSetKey(
3988                                                                 jsonObjectGetIndex(fake_params, 1),
3989                                                                 "order_by",
3990                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "order_by"))
3991                                                         );
3992                                                 }
3993
3994                                                 if (jsonObjectGetKeyConst(order_hash, "select")) {
3995                                                         jsonObjectSetKey(
3996                                                                 jsonObjectGetIndex(fake_params, 1),
3997                                                                 "select",
3998                                                                 jsonObjectClone(jsonObjectGetKeyConst(order_hash, "select"))
3999                                                         );
4000                                                 }
4001
4002                                                 jsonObject* kids = doFieldmapperSearch(ctx, kid_idl, fake_params, err);
4003
4004                                                 if(*err) {
4005                                                         jsonObjectFree( fake_params );
4006                                                         osrfStringArrayFree(link_fields);
4007                                                         jsonIteratorFree(itr);
4008                                                         jsonObjectFree(res_list);
4009                                                         jsonObjectFree(flesh_blob);
4010                                                         return jsonNULL;
4011                                                 }
4012
4013                                                 osrfLogDebug(OSRF_LOG_MARK, "Search for %s return %d linked objects", osrfHashGet(kid_link, "class"), kids->size);
4014
4015                                                 jsonObject* X = NULL;
4016                                                 if ( link_map->size > 0 && kids->size > 0 ) {
4017                                                         X = kids;
4018                                                         kids = jsonNewObjectType(JSON_ARRAY);
4019
4020                                                         jsonObject* _k_node;
4021                                                         jsonIterator* _k = jsonNewIterator( X );
4022                                                         while ((_k_node = jsonIteratorNext( _k ))) {
4023                                                                 jsonObjectPush(
4024                                                                         kids,
4025                                                                         jsonObjectClone(
4026                                                                                 jsonObjectGetIndex(
4027                                                                                         _k_node,
4028                                                                                         (unsigned long)atoi(
4029                                                                                                 osrfHashGet(
4030                                                                                                         osrfHashGet(
4031                                                                                                                 osrfHashGet(
4032                                                                                                                         osrfHashGet(
4033                                                                                                                                 oilsIDL(),
4034                                                                                                                                 osrfHashGet(kid_link, "class")
4035                                                                                                                         ),
4036                                                                                                                         "fields"
4037                                                                                                                 ),
4038                                                                                                                 osrfStringArrayGetString( link_map, 0 )
4039                                                                                                         ),
4040                                                                                                         "array_position"
4041                                                                                                 )
4042                                                                                         )
4043                                                                                 )
4044                                                                         )
4045                                                                 );
4046                                                         }
4047                                                         jsonIteratorFree(_k);
4048                                                 }
4049
4050                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_a" )) || !(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) {
4051                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
4052                                                         jsonObjectSetIndex(
4053                                                                 cur,
4054                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
4055                                                                 jsonObjectClone( jsonObjectGetIndex(kids, 0) )
4056                                                         );
4057                                                 }
4058
4059                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) { // has_many
4060                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s", osrfHashGet(kid_link, "field"));
4061                                                         jsonObjectSetIndex(
4062                                                                 cur,
4063                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
4064                                                                 jsonObjectClone( kids )
4065                                                         );
4066                                                 }
4067
4068                                                 if (X) {
4069                                                         jsonObjectFree(kids);
4070                                                         kids = X;
4071                                                 }
4072
4073                                                 jsonObjectFree( kids );
4074                                                 jsonObjectFree( fake_params );
4075
4076                                                 osrfLogDebug(OSRF_LOG_MARK, "Fleshing of %s complete", osrfHashGet(kid_link, "field"));
4077                                                 osrfLogDebug(OSRF_LOG_MARK, "%s", jsonObjectToJSON(cur));
4078
4079                                         }
4080                                 }
4081                                 jsonObjectFree( flesh_blob );
4082                                 osrfStringArrayFree(link_fields);
4083                                 jsonIteratorFree(itr);
4084                         }
4085                 }
4086         }
4087
4088         return res_list;
4089 }
4090
4091
4092 static jsonObject* doUpdate(osrfMethodContext* ctx, int* err ) {
4093
4094         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
4095 #ifdef PCRUD
4096         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
4097 #else
4098         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
4099 #endif
4100
4101         if (!verifyObjectClass(ctx, target)) {
4102                 *err = -1;
4103                 return jsonNULL;
4104         }
4105
4106         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
4107                 osrfAppSessionStatus(
4108                         ctx->session,
4109                         OSRF_STATUS_BADREQUEST,
4110                         "osrfMethodException",
4111                         ctx->request,
4112                         "No active transaction -- required for UPDATE"
4113                 );
4114                 *err = -1;
4115                 return jsonNULL;
4116         }
4117
4118         // The following test is harmless but redundant.  If a class is
4119         // readonly, we don't register an update method for it.
4120         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
4121                 osrfAppSessionStatus(
4122                         ctx->session,
4123                         OSRF_STATUS_BADREQUEST,
4124                         "osrfMethodException",
4125                         ctx->request,
4126                         "Cannot UPDATE readonly class"
4127                 );
4128                 *err = -1;
4129                 return jsonNULL;
4130         }
4131
4132         dbhandle = writehandle;
4133
4134         char* trans_id = osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" );
4135
4136         // Set the last_xact_id
4137         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
4138         if (index > -1) {
4139                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d", trans_id, target->classname, index);
4140                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
4141         }
4142
4143         char* pkey = osrfHashGet(meta, "primarykey");
4144         osrfHash* fields = osrfHashGet(meta, "fields");
4145
4146         char* id = oilsFMGetString( target, pkey );
4147
4148         osrfLogDebug(
4149                 OSRF_LOG_MARK,
4150                 "%s updating %s object with %s = %s",
4151                 MODULENAME,
4152                 osrfHashGet(meta, "fieldmapper"),
4153                 pkey,
4154                 id
4155         );
4156
4157         growing_buffer* sql = buffer_init(128);
4158         buffer_fadd(sql,"UPDATE %s SET", osrfHashGet(meta, "tablename"));
4159
4160         int i = 0;
4161         int first = 1;
4162         char* field_name;
4163         osrfStringArray* field_list = osrfHashKeys( fields );
4164         while ( (field_name = osrfStringArrayGetString(field_list, i++)) ) {
4165
4166                 osrfHash* field = osrfHashGet( fields, field_name );
4167
4168                 if(!( strcmp( field_name, pkey ) )) continue;
4169                 if( str_is_true( osrfHashGet(osrfHashGet(fields,field_name), "virtual") ) )
4170                         continue;
4171
4172                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
4173
4174                 int value_is_numeric = 0;    // boolean
4175                 char* value;
4176                 if (field_object && field_object->classname) {
4177                         value = oilsFMGetString(
4178                                 field_object,
4179                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
4180             );
4181                 } else {
4182                         value = jsonObjectToSimpleString( field_object );
4183                         if( field_object && JSON_NUMBER == field_object->type )
4184                                 value_is_numeric = 1;
4185                 }
4186
4187                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s", osrfHashGet(meta, "fieldmapper"), field_name, value);
4188
4189                 if (!field_object || field_object->type == JSON_NULL) {
4190                         if ( !(!( strcmp( osrfHashGet(meta, "classname"), "au" ) ) && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
4191                                 if (first) first = 0;
4192                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
4193                                 buffer_fadd( sql, " %s = NULL", field_name );
4194                         }
4195
4196                 } else if ( value_is_numeric || !strcmp( get_primitive( field ), "number") ) {
4197                         if (first) first = 0;
4198                         else OSRF_BUFFER_ADD_CHAR(sql, ',');
4199
4200                         const char* numtype = get_datatype( field );
4201                         if ( !strncmp( numtype, "INT", 3 ) ) {
4202                                 buffer_fadd( sql, " %s = %ld", field_name, atol(value) );
4203                         } else if ( !strcmp( numtype, "NUMERIC" ) ) {
4204                                 buffer_fadd( sql, " %s = %f", field_name, atof(value) );
4205                         } else {
4206                                 // Must really be intended as a string, so quote it
4207                                 if ( dbi_conn_quote_string(dbhandle, &value) ) {
4208                                         buffer_fadd( sql, " %s = %s", field_name, value );
4209                                 } else {
4210                                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
4211                                         osrfAppSessionStatus(
4212                                                 ctx->session,
4213                                                 OSRF_STATUS_INTERNALSERVERERROR,
4214                                                 "osrfMethodException",
4215                                                 ctx->request,
4216                                                 "Error quoting string -- please see the error log for more details"
4217                                         );
4218                                         free(value);
4219                                         free(id);
4220                                         buffer_free(sql);
4221                                         *err = -1;
4222                                         return jsonNULL;
4223                                 }
4224                         }
4225
4226                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, numtype );
4227
4228                 } else {
4229                         if ( dbi_conn_quote_string(dbhandle, &value) ) {
4230                                 if (first) first = 0;
4231                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
4232                                 buffer_fadd( sql, " %s = %s", field_name, value );
4233
4234                         } else {
4235                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
4236                                 osrfAppSessionStatus(
4237                                         ctx->session,
4238                                         OSRF_STATUS_INTERNALSERVERERROR,
4239                                         "osrfMethodException",
4240                                         ctx->request,
4241                                         "Error quoting string -- please see the error log for more details"
4242                                 );
4243                                 free(value);
4244                                 free(id);
4245                                 buffer_free(sql);
4246                                 *err = -1;
4247                                 return jsonNULL;
4248                         }
4249                 }
4250
4251                 free(value);
4252
4253         }
4254
4255         jsonObject* obj = jsonNewObject(id);
4256
4257         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
4258                 dbi_conn_quote_string(dbhandle, &id);
4259
4260         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
4261
4262         char* query = buffer_release(sql);
4263         osrfLogDebug(OSRF_LOG_MARK, "%s: Update SQL [%s]", MODULENAME, query);
4264
4265         dbi_result result = dbi_conn_query(dbhandle, query);
4266         free(query);
4267
4268         if (!result) {
4269                 jsonObjectFree(obj);
4270                 obj = jsonNewObject(NULL);
4271                 osrfLogError(
4272                         OSRF_LOG_MARK,
4273                         "%s ERROR updating %s object with %s = %s",
4274                         MODULENAME,
4275                         osrfHashGet(meta, "fieldmapper"),
4276                         pkey,
4277                         id
4278                 );
4279         }
4280
4281         free(id);
4282
4283         return obj;
4284 }
4285
4286 static jsonObject* doDelete(osrfMethodContext* ctx, int* err ) {
4287
4288         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
4289
4290         if (!osrfHashGet( (osrfHash*)ctx->session->userData, "xact_id" )) {
4291                 osrfAppSessionStatus(
4292                         ctx->session,
4293                         OSRF_STATUS_BADREQUEST,
4294                         "osrfMethodException",
4295                         ctx->request,
4296                         "No active transaction -- required for DELETE"
4297                 );
4298                 *err = -1;
4299                 return jsonNULL;
4300         }
4301
4302         // The following test is harmless but redundant.  If a class is
4303         // readonly, we don't register a delete method for it.
4304         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
4305                 osrfAppSessionStatus(
4306                         ctx->session,
4307                         OSRF_STATUS_BADREQUEST,
4308                         "osrfMethodException",
4309                         ctx->request,
4310                         "Cannot DELETE readonly class"
4311                 );
4312                 *err = -1;
4313                 return jsonNULL;
4314         }
4315
4316         dbhandle = writehandle;
4317
4318         jsonObject* obj;
4319
4320         char* pkey = osrfHashGet(meta, "primarykey");
4321
4322         int _obj_pos = 0;
4323 #ifdef PCRUD
4324                 _obj_pos = 1;
4325 #endif
4326
4327         char* id;
4328         if (jsonObjectGetIndex(ctx->params, _obj_pos)->classname) {
4329                 if (!verifyObjectClass(ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
4330                         *err = -1;
4331                         return jsonNULL;
4332                 }
4333
4334                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
4335         } else {
4336 #ifdef PCRUD
4337         if (!verifyObjectPCRUD( ctx, NULL )) {
4338                         *err = -1;
4339                         return jsonNULL;
4340         }
4341 #endif
4342                 id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, _obj_pos));
4343         }
4344
4345         osrfLogDebug(
4346                 OSRF_LOG_MARK,
4347                 "%s deleting %s object with %s = %s",
4348                 MODULENAME,
4349                 osrfHashGet(meta, "fieldmapper"),
4350                 pkey,
4351                 id
4352         );
4353
4354         obj = jsonNewObject(id);
4355
4356         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
4357                 dbi_conn_quote_string(writehandle, &id);
4358
4359         dbi_result result = dbi_conn_queryf(writehandle, "DELETE FROM %s WHERE %s = %s;", osrfHashGet(meta, "tablename"), pkey, id);
4360
4361         if (!result) {
4362                 jsonObjectFree(obj);
4363                 obj = jsonNewObject(NULL);
4364                 osrfLogError(
4365                         OSRF_LOG_MARK,
4366                         "%s ERROR deleting %s object with %s = %s",
4367                         MODULENAME,
4368                         osrfHashGet(meta, "fieldmapper"),
4369                         pkey,
4370                         id
4371                 );
4372         }
4373
4374         free(id);
4375
4376         return obj;
4377
4378 }
4379
4380
4381 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
4382         if(!(result && meta)) return jsonNULL;
4383
4384         jsonObject* object = jsonNewObject(NULL);
4385         jsonObjectSetClass(object, osrfHashGet(meta, "classname"));
4386
4387         osrfHash* fields = osrfHashGet(meta, "fields");
4388
4389         osrfLogInternal(OSRF_LOG_MARK, "Setting object class to %s ", object->classname);
4390
4391         osrfHash* _f;
4392         time_t _tmp_dt;
4393         char dt_string[256];
4394         struct tm gmdt;
4395
4396         int fmIndex;
4397         int columnIndex = 1;
4398         int attr;
4399         unsigned short type;
4400         const char* columnName;
4401
4402         /* cycle through the column list */
4403         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
4404
4405                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
4406
4407                 fmIndex = -1; // reset the position
4408
4409                 /* determine the field type and storage attributes */
4410                 type = dbi_result_get_field_type(result, columnName);
4411                 attr = dbi_result_get_field_attribs(result, columnName);
4412
4413                 /* fetch the fieldmapper index */
4414                 if( (_f = osrfHashGet(fields, (char*)columnName)) ) {
4415
4416                         if ( str_is_true( osrfHashGet(_f, "virtual") ) )
4417                                 continue;
4418
4419                         const char* pos = (char*)osrfHashGet(_f, "array_position");
4420                         if ( !pos ) continue;
4421
4422                         fmIndex = atoi( pos );
4423                         osrfLogInternal(OSRF_LOG_MARK, "... Found column at position [%s]...", pos);
4424                 } else {
4425                         continue;
4426                 }
4427
4428                 if (dbi_result_field_is_null(result, columnName)) {
4429                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(NULL) );
4430                 } else {
4431
4432                         switch( type ) {
4433
4434                                 case DBI_TYPE_INTEGER :
4435
4436                                         if( attr & DBI_INTEGER_SIZE8 )
4437                                                 jsonObjectSetIndex( object, fmIndex,
4438                                                         jsonNewNumberObject(dbi_result_get_longlong(result, columnName)));
4439                                         else
4440                                                 jsonObjectSetIndex( object, fmIndex,
4441                                                         jsonNewNumberObject(dbi_result_get_int(result, columnName)));
4442
4443                                         break;
4444
4445                                 case DBI_TYPE_DECIMAL :
4446                                         jsonObjectSetIndex( object, fmIndex,
4447                                                         jsonNewNumberObject(dbi_result_get_double(result, columnName)));
4448                                         break;
4449
4450                                 case DBI_TYPE_STRING :
4451
4452
4453                                         jsonObjectSetIndex(
4454                                                 object,
4455                                                 fmIndex,
4456                                                 jsonNewObject( dbi_result_get_string(result, columnName) )
4457                                         );
4458
4459                                         break;
4460
4461                                 case DBI_TYPE_DATETIME :
4462
4463                                         memset(dt_string, '\0', sizeof(dt_string));
4464                                         memset(&gmdt, '\0', sizeof(gmdt));
4465
4466                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
4467
4468
4469                                         if (!(attr & DBI_DATETIME_DATE)) {
4470                                                 gmtime_r( &_tmp_dt, &gmdt );
4471                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
4472                                         } else if (!(attr & DBI_DATETIME_TIME)) {
4473                                                 localtime_r( &_tmp_dt, &gmdt );
4474                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
4475                                         } else {
4476                                                 localtime_r( &_tmp_dt, &gmdt );
4477                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
4478                                         }
4479
4480                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(dt_string) );
4481
4482                                         break;
4483
4484                                 case DBI_TYPE_BINARY :
4485                                         osrfLogError( OSRF_LOG_MARK,
4486                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
4487                         }
4488                 }
4489         }
4490
4491         return object;
4492 }
4493
4494 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
4495         if(!result) return jsonNULL;
4496
4497         jsonObject* object = jsonNewObject(NULL);
4498
4499         time_t _tmp_dt;
4500         char dt_string[256];
4501         struct tm gmdt;
4502
4503         int fmIndex;
4504         int columnIndex = 1;
4505         int attr;
4506         unsigned short type;
4507         const char* columnName;
4508
4509         /* cycle through the column list */
4510         while( (columnName = dbi_result_get_field_name(result, columnIndex++)) ) {
4511
4512                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
4513
4514                 fmIndex = -1; // reset the position
4515
4516                 /* determine the field type and storage attributes */
4517                 type = dbi_result_get_field_type(result, columnName);
4518                 attr = dbi_result_get_field_attribs(result, columnName);
4519
4520                 if (dbi_result_field_is_null(result, columnName)) {
4521                         jsonObjectSetKey( object, columnName, jsonNewObject(NULL) );
4522                 } else {
4523
4524                         switch( type ) {
4525
4526                                 case DBI_TYPE_INTEGER :
4527
4528                                         if( attr & DBI_INTEGER_SIZE8 )
4529                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_longlong(result, columnName)) );
4530                                         else
4531                                                 jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_int(result, columnName)) );
4532                                         break;
4533
4534                                 case DBI_TYPE_DECIMAL :
4535                                         jsonObjectSetKey( object, columnName, jsonNewNumberObject(dbi_result_get_double(result, columnName)) );
4536                                         break;
4537
4538                                 case DBI_TYPE_STRING :
4539                                         jsonObjectSetKey( object, columnName, jsonNewObject(dbi_result_get_string(result, columnName)) );
4540                                         break;
4541
4542                                 case DBI_TYPE_DATETIME :
4543
4544                                         memset(dt_string, '\0', sizeof(dt_string));
4545                                         memset(&gmdt, '\0', sizeof(gmdt));
4546
4547                                         _tmp_dt = dbi_result_get_datetime(result, columnName);
4548
4549
4550                                         if (!(attr & DBI_DATETIME_DATE)) {
4551                                                 gmtime_r( &_tmp_dt, &gmdt );
4552                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
4553                                         } else if (!(attr & DBI_DATETIME_TIME)) {
4554                                                 localtime_r( &_tmp_dt, &gmdt );
4555                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
4556                                         } else {
4557                                                 localtime_r( &_tmp_dt, &gmdt );
4558                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
4559                                         }
4560
4561                                         jsonObjectSetKey( object, columnName, jsonNewObject(dt_string) );
4562                                         break;
4563
4564                                 case DBI_TYPE_BINARY :
4565                                         osrfLogError( OSRF_LOG_MARK,
4566                                                 "Can't do binary at column %s : index %d", columnName, columnIndex - 1);
4567                         }
4568                 }
4569         }
4570
4571         return object;
4572 }
4573
4574 // Interpret a string as true or false
4575 static int str_is_true( const char* str ) {
4576         if( NULL == str || strcasecmp( str, "true" ) )
4577                 return 0;
4578         else
4579                 return 1;
4580 }
4581
4582 // Interpret a jsonObject as true or false
4583 static int obj_is_true( const jsonObject* obj ) {
4584         if( !obj )
4585                 return 0;
4586         else switch( obj->type )
4587         {
4588                 case JSON_BOOL :
4589                         if( obj->value.b )
4590                                 return 1;
4591                         else
4592                                 return 0;
4593                 case JSON_STRING :
4594                         if( strcasecmp( obj->value.s, "true" ) )
4595                                 return 0;
4596                         else
4597                                 return 1;
4598                 case JSON_NUMBER :          // Support 1/0 for perl's sake
4599                         if( jsonObjectGetNumber( obj ) == 1.0 )
4600                                 return 1;
4601                         else
4602                                 return 0;
4603                 default :
4604                         return 0;
4605         }
4606 }
4607
4608 // Translate a numeric code into a text string identifying a type of
4609 // jsonObject.  To be used for building error messages.
4610 static const char* json_type( int code ) {
4611         switch ( code )
4612         {
4613                 case 0 :
4614                         return "JSON_HASH";
4615                 case 1 :
4616                         return "JSON_ARRAY";
4617                 case 2 :
4618                         return "JSON_STRING";
4619                 case 3 :
4620                         return "JSON_NUMBER";
4621                 case 4 :
4622                         return "JSON_NULL";
4623                 case 5 :
4624                         return "JSON_BOOL";
4625                 default :
4626                         return "(unrecognized)";
4627         }
4628 }
4629
4630 // Extract the "primitive" attribute from an IDL field definition.
4631 // If we haven't initialized the app, then we must be running in
4632 // some kind of testbed.  In that case, default to "string".
4633 static const char* get_primitive( osrfHash* field ) {
4634         const char* s = osrfHashGet( field, "primitive" );
4635         if( !s ) {
4636                 if( child_initialized )
4637                         osrfLogError(
4638                                 OSRF_LOG_MARK,
4639                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
4640                                 MODULENAME,
4641                                 osrfHashGet( field, "name" )
4642                         );
4643                 else
4644                         s = "string";
4645         }
4646         return s;
4647 }
4648
4649 // Extract the "datatype" attribute from an IDL field definition.
4650 // If we haven't initialized the app, then we must be running in
4651 // some kind of testbed.  In that case, default to to NUMERIC,
4652 // since we look at the datatype only for numbers.
4653 static const char* get_datatype( osrfHash* field ) {
4654         const char* s = osrfHashGet( field, "datatype" );
4655         if( !s ) {
4656                 if( child_initialized )
4657                         osrfLogError(
4658                                 OSRF_LOG_MARK,
4659                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
4660                                 MODULENAME,
4661                                 osrfHashGet( field, "name" )
4662                         );
4663                 else
4664                         s = "NUMERIC";
4665         }
4666         return s;
4667 }
4668
4669 /*
4670 If the input string is potentially a valid SQL identifier, return 1.
4671 Otherwise return 0.
4672
4673 Purpose: to prevent certain kinds of SQL injection.  To that end we
4674 don't necessarily need to follow all the rules exactly, such as requiring
4675 that the first character not be a digit.
4676
4677 We allow leading and trailing white space.  In between, we do not allow
4678 punctuation (except for underscores and dollar signs), control
4679 characters, or embedded white space.
4680
4681 More pedantically we should allow quoted identifiers containing arbitrary
4682 characters, but for the foreseeable future such quoted identifiers are not
4683 likely to be an issue.
4684 */
4685 static int is_identifier( const char* s) {
4686         if( !s )
4687                 return 0;
4688
4689         // Skip leading white space
4690         while( isspace( (unsigned char) *s ) )
4691                 ++s;
4692
4693         if( !s )
4694                 return 0;   // Nothing but white space?  Not okay.
4695
4696         // Check each character until we reach white space or
4697         // end-of-string.  Letters, digits, underscores, and
4698         // dollar signs are okay.  Control characters and other
4699         // punctuation characters are not okay.  Anything else
4700         // is okay -- it could for example be part of a multibyte
4701         // UTF8 character such as a letter with diacritical marks,
4702         // and those are allowed.
4703         do {
4704                 if( isalnum( (unsigned char) *s )
4705                         || '_' == *s
4706                         || '$' == *s )
4707                         ;  // Fine; keep going
4708                 else if(   ispunct( (unsigned char) *s )
4709                                 || iscntrl( (unsigned char) *s ) )
4710                         return 0;
4711                         ++s;
4712         } while( *s && ! isspace( (unsigned char) *s ) );
4713
4714         // If we found any white space in the above loop,
4715         // the rest had better be all white space.
4716
4717         while( isspace( (unsigned char) *s ) )
4718                 ++s;
4719
4720         if( *s )
4721                 return 0;   // White space was embedded within non-white space
4722
4723         return 1;
4724 }
4725
4726 /*
4727 Determine whether to accept a character string as a comparison operator.
4728 Return 1 if it's good, or 0 if it's bad.
4729
4730 We don't validate it for real.  We just make sure that it doesn't contain
4731 any semicolons or white space (with a special exception for the
4732 "SIMILAR TO" operator).  The idea is to block certain kinds of SQL
4733 injection.  If it has no semicolons or white space but it's still not a
4734 valid operator, then the database will complain.
4735
4736 Another approach would be to compare the string against a short list of
4737 approved operators.  We don't do that because we want to allow custom
4738 operators like ">100*", which would be difficult or impossible to
4739 express otherwise in a JSON query.
4740 */
4741 static int is_good_operator( const char* op ) {
4742         if( !op ) return 0;   // Sanity check
4743
4744         const char* s = op;
4745         while( *s ) {
4746                 if( isspace( (unsigned char) *s ) ) {
4747                         // Special exception for SIMILAR TO.  Someday we might make
4748                         // exceptions for IS DISTINCT FROM and IS NOT DISTINCT FROM.
4749                         if( !strcasecmp( op, "similar to" ) )
4750                                 return 1;
4751                         else
4752                                 return 0;
4753                 }
4754                 else if( ';' == *s )
4755                         return 0;
4756                 ++s;
4757         }
4758         return 1;
4759 }