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