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

   1 /**
   2         @file oils_cstore.c
   3         @brief As a server, perform database operations at the request of clients.
   4 */
   5
   6 #include <ctype.h>
   7 #include "opensrf/osrf_application.h"
   8 #include "opensrf/osrf_settings.h"
   9 #include "opensrf/osrf_message.h"
  10 #include "opensrf/utils.h"
  11 #include "opensrf/osrf_json.h"
  12 #include "opensrf/log.h"
  13 #include "openils/oils_utils.h"
  14 #include "openils/oils_constants.h"
  15 #include <dbi/dbi.h>
  16
  17 #include <time.h>
  18 #include <stdlib.h>
  19 #include <string.h>
  20 #include <unistd.h>
  21
  22 #ifdef RSTORE
  23 #  define MODULENAME "open-ils.reporter-store"
  24 #else
  25 #  ifdef PCRUD
  26 #    define MODULENAME "open-ils.pcrud"
  27 #  else
  28 #    define MODULENAME "open-ils.cstore"
  29 #  endif
  30 #endif
  31
  32 // The next four macros are OR'd together as needed to form a set
  33 // of bitflags.  SUBCOMBO enables an extra pair of parentheses when
  34 // nesting one UNION, INTERSECT or EXCEPT inside another.
  35 // SUBSELECT tells us we're in a subquery, so don't add the
  36 // terminal semicolon yet.
  37 #define SUBCOMBO    8
  38 #define SUBSELECT   4
  39 #define DISABLE_I18N    2
  40 #define SELECT_DISTINCT 1
  41
  42 #define AND_OP_JOIN     0
  43 #define OR_OP_JOIN      1
  44
  45 struct ClassInfoStruct;
  46 typedef struct ClassInfoStruct ClassInfo;
  47
  48 #define ALIAS_STORE_SIZE 16
  49 #define CLASS_NAME_STORE_SIZE 16
  50
  51 struct ClassInfoStruct {
  52         char* alias;
  53         char* class_name;
  54         char* source_def;
  55         osrfHash* class_def;      // Points into IDL
  56         osrfHash* fields;         // Points into IDL
  57         osrfHash* links;          // Points into IDL
  58
  59         // The remaining members are private and internal.  Client code should not
  60         // access them directly.
  61
  62         ClassInfo* next;          // Supports linked list of joined classes
  63         int in_use;               // boolean
  64
  65         // We usually store the alias and class name in the following arrays, and
  66         // point the corresponding pointers at them.  When the string is too big
  67         // for the array (which will probably never happen in practice), we strdup it.
  68
  69         char alias_store[ ALIAS_STORE_SIZE + 1 ];
  70         char class_name_store[ CLASS_NAME_STORE_SIZE + 1 ];
  71 };
  72
  73 struct QueryFrameStruct;
  74 typedef struct QueryFrameStruct QueryFrame;
  75
  76 struct QueryFrameStruct {
  77         ClassInfo core;
  78         ClassInfo* join_list;  // linked list of classes joined to the core class
  79         QueryFrame* next;      // implements stack as linked list
  80         int in_use;            // boolean
  81 };
  82
  83 #ifdef PCRUD
  84 static int timeout_needs_resetting;
  85 static time_t time_next_reset;
  86 #endif
  87
  88 int osrfAppChildInit();
  89 int osrfAppInitialize();
  90 void osrfAppChildExit();
  91
  92 static int verifyObjectClass ( osrfMethodContext*, const jsonObject* );
  93
  94 static void setXactId( osrfMethodContext* ctx );
  95 static inline const char* getXactId( osrfMethodContext* ctx );
  96 static inline void clearXactId( osrfMethodContext* ctx );
  97
  98 int beginTransaction ( osrfMethodContext* );
  99 int commitTransaction ( osrfMethodContext* );
 100 int rollbackTransaction ( osrfMethodContext* );
 101
 102 int setSavepoint ( osrfMethodContext* );
 103 int releaseSavepoint ( osrfMethodContext* );
 104 int rollbackSavepoint ( osrfMethodContext* );
 105
 106 int doJSONSearch ( osrfMethodContext* );
 107
 108 int dispatchCRUDMethod ( osrfMethodContext* );
 109 static jsonObject* doCreate ( osrfMethodContext*, int* );
 110 static jsonObject* doRetrieve ( osrfMethodContext*, int* );
 111 static jsonObject* doUpdate ( osrfMethodContext*, int* );
 112 static jsonObject* doDelete ( osrfMethodContext*, int* );
 113 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* class_meta,
 114                 jsonObject* where_hash, jsonObject* query_hash, int* err );
 115 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result, osrfHash* );
 116 static jsonObject* oilsMakeJSONFromResult( dbi_result );
 117
 118 static char* searchSimplePredicate ( const char* op, const char* class_alias,
 119                                 osrfHash* field, const jsonObject* node );
 120 static char* searchFunctionPredicate ( const char*, osrfHash*, const jsonObject*, const char* );
 121 static char* searchFieldTransform ( const char*, osrfHash*, const jsonObject*);
 122 static char* searchFieldTransformPredicate ( const ClassInfo*, osrfHash*, const jsonObject*, const char* );
 123 static char* searchBETWEENPredicate ( const char*, osrfHash*, const jsonObject* );
 124 static char* searchINPredicate ( const char*, osrfHash*,
 125                                                                  jsonObject*, const char*, osrfMethodContext* );
 126 static char* searchPredicate ( const ClassInfo*, osrfHash*, jsonObject*, osrfMethodContext* );
 127 static char* searchJOIN ( const jsonObject*, const ClassInfo* left_info );
 128 static char* searchWHERE ( const jsonObject*, const ClassInfo*, int, osrfMethodContext* );
 129 static char* buildSELECT ( jsonObject*, jsonObject*, osrfHash*, osrfMethodContext* );
 130 char* buildQuery( osrfMethodContext* ctx, jsonObject* query, int flags );
 131
 132 char* SELECT ( osrfMethodContext*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, int );
 133
 134 void userDataFree( void* );
 135 static void sessionDataFree( char*, void* );
 136 static char* getRelation( osrfHash* );
 137 static int str_is_true( const char* str );
 138 static int obj_is_true( const jsonObject* obj );
 139 static const char* json_type( int code );
 140 static const char* get_primitive( osrfHash* field );
 141 static const char* get_datatype( osrfHash* field );
 142 static int is_identifier( const char* s);
 143 static int is_good_operator( const char* op );
 144 static void pop_query_frame( void );
 145 static void push_query_frame( void );
 146 static int add_query_core( const char* alias, const char* class_name );
 147 static inline ClassInfo* search_alias( const char* target );
 148 static ClassInfo* search_all_alias( const char* target );
 149 static ClassInfo* add_joined_class( const char* alias, const char* classname );
 150 static void clear_query_stack( void );
 151
 152 #ifdef PCRUD
 153 static const jsonObject* verifyUserPCRUD( osrfMethodContext* );
 154 static int verifyObjectPCRUD( osrfMethodContext*, const jsonObject* );
 155 static char* org_tree_root( osrfMethodContext* ctx );
 156 static jsonObject* single_hash( const char* key, const char* value );
 157 #endif
 158
 159 static int child_initialized = 0;   /* boolean */
 160
 161 static dbi_conn writehandle; /* our MASTER db connection */
 162 static dbi_conn dbhandle; /* our CURRENT db connection */
 163 //static osrfHash * readHandles;
 164 static jsonObject* const jsonNULL = NULL; //
 165 static int max_flesh_depth = 100;
 166
 167 // The following points to the top of a stack of QueryFrames.  It's a little
 168 // confusing because the top level of the query is at the bottom of the stack.
 169 static QueryFrame* curr_query = NULL;
 170
 171 /**
 172         @brief Disconnect from the database.
 173
 174         This function is called when the server drone is about to terminate.
 175 */
 176 void osrfAppChildExit() {
 177         osrfLogDebug(OSRF_LOG_MARK, "Child is exiting, disconnecting from database...");
 178
 179         int same = 0;
 180         if (writehandle == dbhandle)
 181                 same = 1;
 182
 183         if (writehandle) {
 184                 dbi_conn_query(writehandle, "ROLLBACK;");
 185                 dbi_conn_close(writehandle);
 186                 writehandle = NULL;
 187         }
 188         if (dbhandle && !same)
 189                 dbi_conn_close(dbhandle);
 190
 191         // XXX add cleanup of readHandles whenever that gets used
 192
 193         return;
 194 }
 195
 196 /**
 197         @brief Initialize the application.
 198         @return Zero if successful, or non-zero if not.
 199
 200         Load the IDL file into an internal data structure for future reference.  Each non-virtual
 201         class in the IDL corresponds to a table or view in the database, or to a subquery defined
 202         in the IDL.  Ignore all virtual tables and virtual fields.
 203
 204         Register a number of methods, some of them general-purpose and others specific for
 205         particular classes.
 206
 207         The name of the application is given by the MODULENAME macro, whose value depends on
 208         conditional compilation.  The method names also incorporate MODULENAME, followed by a
 209         dot, as a prefix.  Some methods are registered or not registered depending on whether
 210         the PCRUD macro is defined, and on whether the IDL includes permacrud entries for the
 211         class and method.
 212
 213         The general-purpose methods are as follows (minus their MODULENAME prefixes):
 214
 215         - json_query (not registered for PCRUD)
 216         - transaction.begin
 217         - transaction.commit
 218         - transaction.rollback
 219         - savepoint.set
 220         - savepoint.release
 221         - savepoint.rollback
 222
 223         For each non-virtual class, create up to eight class-specific methods:
 224
 225         - create    (not for readonly classes)
 226         - retrieve
 227         - update    (not for readonly classes)
 228         - delete    (not for readonly classes
 229         - search    (atomic and non-atomic versions)
 230         - id_list   (atomic and non-atomic versions)
 231
 232         For PCRUD, the full method names follow the pattern "MODULENAME.method_type.classname".
 233         Otherwise they follow the pattern "MODULENAME.direct.XXX.method_type", where XXX is the
 234         fieldmapper name from the IDL, with every run of one or more consecutive colons replaced
 235         by a period.  In addition, the names of atomic methods have a suffix of ".atomic".
 236
 237         This function is called when the registering the application, and is executed by the
 238         listener before spawning the drones.
 239 */
 240 int osrfAppInitialize() {
 241
 242         osrfLogInfo(OSRF_LOG_MARK, "Initializing the CStore Server...");
 243         osrfLogInfo(OSRF_LOG_MARK, "Finding XML file...");
 244
 245         if (!oilsIDLInit( osrf_settings_host_value("/IDL") ))
 246                 return 1; /* return non-zero to indicate error */
 247
 248         growing_buffer* method_name = buffer_init(64);
 249 #ifndef PCRUD
 250         // Generic search thingy
 251         buffer_add(method_name, MODULENAME);
 252         buffer_add(method_name, ".json_query");
 253         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 254                         "doJSONSearch", "", 1, OSRF_METHOD_STREAMING );
 255 #endif
 256
 257         // first we register all the transaction and savepoint methods
 258         buffer_reset(method_name);
 259         OSRF_BUFFER_ADD(method_name, MODULENAME);
 260         OSRF_BUFFER_ADD(method_name, ".transaction.begin");
 261         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 262                         "beginTransaction", "", 0, 0 );
 263
 264         buffer_reset(method_name);
 265         OSRF_BUFFER_ADD(method_name, MODULENAME);
 266         OSRF_BUFFER_ADD(method_name, ".transaction.commit");
 267         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 268                         "commitTransaction", "", 0, 0 );
 269
 270         buffer_reset(method_name);
 271         OSRF_BUFFER_ADD(method_name, MODULENAME);
 272         OSRF_BUFFER_ADD(method_name, ".transaction.rollback");
 273         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 274                         "rollbackTransaction", "", 0, 0 );
 275
 276         buffer_reset(method_name);
 277         OSRF_BUFFER_ADD(method_name, MODULENAME);
 278         OSRF_BUFFER_ADD(method_name, ".savepoint.set");
 279         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 280                         "setSavepoint", "", 1, 0 );
 281
 282         buffer_reset(method_name);
 283         OSRF_BUFFER_ADD(method_name, MODULENAME);
 284         OSRF_BUFFER_ADD(method_name, ".savepoint.release");
 285         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 286                         "releaseSavepoint", "", 1, 0 );
 287
 288         buffer_reset(method_name);
 289         OSRF_BUFFER_ADD(method_name, MODULENAME);
 290         OSRF_BUFFER_ADD(method_name, ".savepoint.rollback");
 291         osrfAppRegisterMethod( MODULENAME, OSRF_BUFFER_C_STR(method_name),
 292                         "rollbackSavepoint", "", 1, 0 );
 293
 294         static const char* global_method[] = {
 295                 "create",
 296                 "retrieve",
 297                 "update",
 298                 "delete",
 299                 "search",
 300                 "id_list"
 301         };
 302         const int global_method_count
 303                 = sizeof( global_method ) / sizeof ( global_method[0] );
 304
 305         unsigned long class_count = osrfHashGetCount( oilsIDL() );
 306         osrfLogDebug(OSRF_LOG_MARK, "%lu classes loaded", class_count );
 307         osrfLogDebug(OSRF_LOG_MARK,
 308                 "At most %lu methods will be generated",
 309                 (unsigned long) (class_count * global_method_count) );
 310
 311         osrfHashIterator* class_itr = osrfNewHashIterator( oilsIDL() );
 312         osrfHash* idlClass = NULL;
 313
 314         // For each class in the IDL...
 315         while( (idlClass = osrfHashIteratorNext( class_itr ) ) ) {
 316
 317                 const char* classname = osrfHashIteratorKey( class_itr );
 318                 osrfLogInfo(OSRF_LOG_MARK, "Generating class methods for %s", classname);
 319
 320                 if (!osrfStringArrayContains( osrfHashGet(idlClass, "controller"), MODULENAME )) {
 321                         osrfLogInfo(OSRF_LOG_MARK, "%s is not listed as a controller for %s, moving on",
 322                                         MODULENAME, classname);
 323                         continue;
 324                 }
 325
 326                 if ( str_is_true( osrfHashGet(idlClass, "virtual") ) ) {
 327                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 328                         continue;
 329                 }
 330
 331                 // Look up some other attributes of the current class
 332                 const char* idlClass_fieldmapper = osrfHashGet(idlClass, "fieldmapper");
 333                 if( !idlClass_fieldmapper ) {
 334                         osrfLogDebug( OSRF_LOG_MARK, "Skipping class \"%s\"; no fieldmapper in IDL",
 335                                         classname );
 336                         continue;
 337                 }
 338
 339 #ifdef PCRUD
 340                 // For PCRUD, ignore classes with no permacrud attribute
 341                 osrfHash* idlClass_permacrud = osrfHashGet(idlClass, "permacrud");
 342                 if (!idlClass_permacrud) {
 343                         osrfLogDebug( OSRF_LOG_MARK, "Skipping class \"%s\"; no permacrud in IDL", classname );
 344                         continue;
 345                 }
 346 #endif
 347                 const char* readonly = osrfHashGet(idlClass, "readonly");
 348
 349                 int i;
 350                 for( i = 0; i < global_method_count; ++i ) {  // for each global method
 351                         const char* method_type = global_method[ i ];
 352                         osrfLogDebug(OSRF_LOG_MARK,
 353                                 "Using files to build %s class methods for %s", method_type, classname);
 354
 355 #ifdef PCRUD
 356                         // Treat "id_list" or "search" as forms of "retrieve"
 357                         const char* tmp_method = method_type;
 358                         if ( *tmp_method == 'i' || *tmp_method == 's') {  // "id_list" or "search"
 359                                 tmp_method = "retrieve";
 360                         }
 361                         // Skip this method if there is no permacrud entry for it
 362                         if (!osrfHashGet( idlClass_permacrud, tmp_method ))
 363                                 continue;
 364 #endif
 365
 366                         // No create, update, or delete methods for a readonly class
 367                         if ( str_is_true( readonly )
 368                                 && ( *method_type == 'c' || *method_type == 'u' || *method_type == 'd') )
 369                                 continue;
 370
 371                         buffer_reset( method_name );
 372
 373                         // Build the method name
 374 #ifdef PCRUD
 375                         // For PCRUD: MODULENAME.method_type.classname
 376                         buffer_fadd(method_name, "%s.%s.%s", MODULENAME, method_type, classname);
 377 #else
 378                         // For non-PCRUD: MODULENAME.MODULENAME.direct.XXX.method_type
 379                         // where XXX is the fieldmapper name from the IDL, with every run of
 380                         // one or more consecutive colons replaced by a period.
 381                         char* st_tmp = NULL;
 382                         char* part = NULL;
 383                         char* _fm = strdup( idlClass_fieldmapper );
 384                         part = strtok_r(_fm, ":", &st_tmp);
 385
 386                         buffer_fadd(method_name, "%s.direct.%s", MODULENAME, part);
 387
 388                         while ((part = strtok_r(NULL, ":", &st_tmp))) {
 389                                 OSRF_BUFFER_ADD_CHAR(method_name, '.');
 390                                 OSRF_BUFFER_ADD(method_name, part);
 391                         }
 392                         OSRF_BUFFER_ADD_CHAR(method_name, '.');
 393                         OSRF_BUFFER_ADD(method_name, method_type);
 394                         free(_fm);
 395 #endif
 396
 397                         // For an id_list or search method we specify the OSRF_METHOD_STREAMING option.
 398                         // The consequence is that we implicitly create an atomic method in addition to
 399                         // the usual non-atomic method.
 400                         int flags = 0;
 401                         if (*method_type == 'i' || *method_type == 's') {  // id_list or search
 402                                 flags = flags | OSRF_METHOD_STREAMING;
 403                         }
 404
 405                         osrfHash* method_meta = osrfNewHash();
 406                         osrfHashSet( method_meta, idlClass, "class");
 407                         osrfHashSet( method_meta, buffer_data( method_name ), "methodname" );
 408                         osrfHashSet( method_meta, strdup(method_type), "methodtype" );
 409
 410                         // Register the method, with a pointer to an osrfHash to tell the method
 411                         // its name, type, and class.
 412                         osrfAppRegisterExtendedMethod(
 413                                 MODULENAME,
 414                                 OSRF_BUFFER_C_STR( method_name ),
 415                                 "dispatchCRUDMethod",
 416                                 "",
 417                                 1,
 418                                 flags,
 419                                 (void*)method_meta
 420                         );
 421
 422                 } // end for each global method
 423         } // end for each class in IDL
 424
 425         buffer_free( method_name );
 426         osrfHashIteratorFree( class_itr );
 427
 428         return 0;
 429 }
 430
 431 /**
 432         @brief Get a table name, view name, or subquery for use in a FROM clause.
 433         @param class Pointer to the IDL class entry.
 434         @return A table name, a view name, or a subquery in parentheses.
 435
 436         In some cases the IDL defines a class, not with a table name or a view name, but with
 437         a SELECT statement, which may be used as a subquery.
 438 */
 439 static char* getRelation( osrfHash* class ) {
 440
 441         char* source_def = NULL;
 442         const char* tabledef = osrfHashGet(class, "tablename");
 443
 444         if ( tabledef ) {
 445                 source_def = strdup( tabledef );   // Return the name of a table or view
 446         } else {
 447                 tabledef = osrfHashGet( class, "source_definition" );
 448                 if( tabledef ) {
 449                         // Return a subquery, enclosed in parentheses
 450                         source_def = safe_malloc( strlen( tabledef ) + 3 );
 451                         source_def[ 0 ] = '(';
 452                         strcpy( source_def + 1, tabledef );
 453                         strcat( source_def, ")" );
 454                 } else {
 455                         // Not found: return an error
 456                         const char* classname = osrfHashGet( class, "classname" );
 457                         if( !classname )
 458                                 classname = "???";
 459                         osrfLogError(
 460                                 OSRF_LOG_MARK,
 461                                 "%s ERROR No tablename or source_definition for class \"%s\"",
 462                                 MODULENAME,
 463                                 classname
 464                         );
 465                 }
 466         }
 467
 468         return source_def;
 469 }
 470
 471 /**
 472         @brief Initialize a server drone.
 473         @return Zero if successful, -1 if not.
 474
 475         Connect to the database.  For each non-virtual class in the IDL, execute a dummy "SELECT * "
 476         query to get the datatype of each column.  Record the datatypes in the loaded IDL.
 477
 478         This function is called by a server drone shortly after it is spawned by the listener.
 479 */
 480 int osrfAppChildInit() {
 481
 482         osrfLogDebug(OSRF_LOG_MARK, "Attempting to initialize libdbi...");
 483         dbi_initialize(NULL);
 484         osrfLogDebug(OSRF_LOG_MARK, "... libdbi initialized.");
 485
 486         char* driver = osrf_settings_host_value("/apps/%s/app_settings/driver", MODULENAME);
 487         char* user   = osrf_settings_host_value("/apps/%s/app_settings/database/user", MODULENAME);
 488         char* host   = osrf_settings_host_value("/apps/%s/app_settings/database/host", MODULENAME);
 489         char* port   = osrf_settings_host_value("/apps/%s/app_settings/database/port", MODULENAME);
 490         char* db     = osrf_settings_host_value("/apps/%s/app_settings/database/db", MODULENAME);
 491         char* pw     = osrf_settings_host_value("/apps/%s/app_settings/database/pw", MODULENAME);
 492         char* md     = osrf_settings_host_value("/apps/%s/app_settings/max_query_recursion",
 493                         MODULENAME);
 494
 495         osrfLogDebug(OSRF_LOG_MARK, "Attempting to load the database driver [%s]...", driver);
 496         writehandle = dbi_conn_new(driver);
 497
 498         if(!writehandle) {
 499                 osrfLogError(OSRF_LOG_MARK, "Error loading database driver [%s]", driver);
 500                 return -1;
 501         }
 502         osrfLogDebug(OSRF_LOG_MARK, "Database driver [%s] seems OK", driver);
 503
 504         osrfLogInfo(OSRF_LOG_MARK, "%s connecting to database.  host=%s, "
 505                         "port=%s, user=%s, db=%s", MODULENAME, host, port, user, db );
 506
 507         if(host) dbi_conn_set_option(writehandle, "host", host );
 508         if(port) dbi_conn_set_option_numeric( writehandle, "port", atoi(port) );
 509         if(user) dbi_conn_set_option(writehandle, "username", user);
 510         if(pw)   dbi_conn_set_option(writehandle, "password", pw );
 511         if(db)   dbi_conn_set_option(writehandle, "dbname", db );
 512
 513         if(md)                     max_flesh_depth = atoi(md);
 514         if(max_flesh_depth < 0)    max_flesh_depth = 1;
 515         if(max_flesh_depth > 1000) max_flesh_depth = 1000;
 516
 517         free(user);
 518         free(host);
 519         free(port);
 520         free(db);
 521         free(pw);
 522
 523         const char* err;
 524         if (dbi_conn_connect(writehandle) < 0) {
 525                 sleep(1);
 526                 if (dbi_conn_connect(writehandle) < 0) {
 527                         dbi_conn_error(writehandle, &err);
 528                         osrfLogError( OSRF_LOG_MARK, "Error connecting to database: %s", err);
 529                         return -1;
 530                 }
 531         }
 532
 533         osrfLogInfo(OSRF_LOG_MARK, "%s successfully connected to the database", MODULENAME);
 534
 535         osrfHashIterator* class_itr = osrfNewHashIterator( oilsIDL() );
 536         osrfHash* class = NULL;
 537         growing_buffer* query_buf = buffer_init( 64 );
 538
 539         // For each class in the IDL...
 540         while( (class = osrfHashIteratorNext( class_itr ) ) ) {
 541                 const char* classname = osrfHashIteratorKey( class_itr );
 542                 osrfHash* fields = osrfHashGet( class, "fields" );
 543
 544                 // If the class is virtual, ignore it
 545                 if( str_is_true( osrfHashGet(class, "virtual") ) ) {
 546                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 547                         continue;
 548                 }
 549
 550                 char* tabledef = getRelation(class);
 551                 if( !tabledef )
 552                         continue;   // No such relation -- a query of it would be doomed to failure
 553
 554                 buffer_reset( query_buf );
 555                 buffer_fadd( query_buf, "SELECT * FROM %s AS x WHERE 1=0;", tabledef );
 556
 557                 free(tabledef);
 558
 559                 osrfLogDebug( OSRF_LOG_MARK, "%s Investigatory SQL = %s",
 560                                 MODULENAME, OSRF_BUFFER_C_STR( query_buf ) );
 561
 562                 dbi_result result = dbi_conn_query( writehandle, OSRF_BUFFER_C_STR( query_buf ) );
 563                 if (result) {
 564
 565                         int columnIndex = 1;
 566                         const char* columnName;
 567                         osrfHash* _f;
 568                         while( (columnName = dbi_result_get_field_name(result, columnIndex)) ) {
 569
 570                                 osrfLogInternal( OSRF_LOG_MARK, "Looking for column named [%s]...",
 571                                                 columnName );
 572
 573                                 /* fetch the fieldmapper index */
 574                                 if( (_f = osrfHashGet(fields, columnName)) ) {
 575
 576                                         osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", columnName);
 577
 578                                         /* determine the field type and storage attributes */
 579
 580                                         switch( dbi_result_get_field_type_idx(result, columnIndex) ) {
 581
 582                                                 case DBI_TYPE_INTEGER : {
 583
 584                                                         if ( !osrfHashGet(_f, "primitive") )
 585                                                                 osrfHashSet(_f, "number", "primitive");
 586
 587                                                         int attr = dbi_result_get_field_attribs_idx(result, columnIndex);
 588                                                         if( attr & DBI_INTEGER_SIZE8 )
 589                                                                 osrfHashSet(_f, "INT8", "datatype");
 590                                                         else
 591                                                                 osrfHashSet(_f, "INT", "datatype");
 592                                                         break;
 593                                                 }
 594                                                 case DBI_TYPE_DECIMAL :
 595                                                         if ( !osrfHashGet(_f, "primitive") )
 596                                                                 osrfHashSet(_f, "number", "primitive");
 597
 598                                                         osrfHashSet(_f,"NUMERIC", "datatype");
 599                                                         break;
 600
 601                                                 case DBI_TYPE_STRING :
 602                                                         if ( !osrfHashGet(_f, "primitive") )
 603                                                                 osrfHashSet(_f,"string", "primitive");
 604
 605                                                         osrfHashSet(_f,"TEXT", "datatype");
 606                                                         break;
 607
 608                                                 case DBI_TYPE_DATETIME :
 609                                                         if ( !osrfHashGet(_f, "primitive") )
 610                                                                 osrfHashSet(_f,"string", "primitive");
 611
 612                                                         osrfHashSet(_f,"TIMESTAMP", "datatype");
 613                                                         break;
 614
 615                                                 case DBI_TYPE_BINARY :
 616                                                         if ( !osrfHashGet(_f, "primitive") )
 617                                                                 osrfHashSet(_f,"string", "primitive");
 618
 619                                                         osrfHashSet(_f,"BYTEA", "datatype");
 620                                         }
 621
 622                                         osrfLogDebug(
 623                                                 OSRF_LOG_MARK,
 624                                                 "Setting [%s] to primitive [%s] and datatype [%s]...",
 625                                                 columnName,
 626                                                 osrfHashGet(_f, "primitive"),
 627                                                 osrfHashGet(_f, "datatype")
 628                                         );
 629                                 }
 630                                 ++columnIndex;
 631                         } // end while loop for traversing columns of result
 632                         dbi_result_free(result);
 633                 } else {
 634                         osrfLogDebug(OSRF_LOG_MARK, "No data found for class [%s]...", classname);
 635                 }
 636         } // end for each class in IDL
 637
 638         buffer_free( query_buf );
 639         osrfHashIteratorFree( class_itr );
 640         child_initialized = 1;
 641         return 0;
 642 }
 643
 644 /**
 645         @brief Install a database driver.
 646         @param conn Pointer to a database driver.
 647
 648         The driver is used to process quoted strings correctly.
 649
 650         This function is a sleazy hack, intended @em only for testing and debugging without
 651         actually connecting to a database. Any real server process should initialize the
 652         database connection by calling osrfAppChildInit().
 653 */
 654 void set_cstore_dbi_conn( dbi_conn conn ) {
 655         dbhandle = writehandle = conn;
 656 }
 657
 658 /**
 659         @brief Free an osrfHash that stores a transaction ID.
 660         @param blob A pointer to the osrfHash to be freed, cast to a void pointer.
 661
 662         This function is a callback, to be called by the application session when it ends.
 663         The application session stores the osrfHash via an opaque pointer.
 664
 665         If the osrfHash contains an entry for the key "xact_id", it means that an
 666         uncommitted transaction is pending.  Roll it back.
 667 */
 668 void userDataFree( void* blob ) {
 669         osrfHash* hash = (osrfHash*) blob;
 670         if( osrfHashGet( hash, "xact_id" ) && writehandle )
 671                 dbi_conn_query( writehandle, "ROLLBACK;" );
 672
 673         osrfHashFree( hash );
 674 }
 675
 676 /**
 677         @name Managing session data
 678         @brief Maintain data stored via the userData pointer of the application session.
 679
 680         Currently, session-level data is stored in an osrfHash.  Other arrangements are
 681         possible, and some would be more efficient.  The application session calls a
 682         callback function to free userData before terminating.
 683
 684         Currently, the only data we store at the session level is the transaction id.  By this
 685         means we can ensure that any pending transactions are rolled back before the application
 686         session terminates.
 687 */
 688 /*@{*/
 689
 690 /**
 691         @brief Free an item in the application session's userData.
 692         @param key The name of a key for an osrfHash.
 693         @param item An opaque pointer to the item associated with the key.
 694
 695         We store an osrfHash as userData with the application session, and arrange (by
 696         installing userDataFree() as a different callback) for the session to free that
 697         osrfHash before terminating.
 698
 699         This function is a callback for freeing items in the osrfHash.  Currently we store
 700         two things:
 701         - Transaction id of a pending transaction; a character string.  Key: "xact_id".
 702         - Authkey; a character string.  Key: "authkey".
 703         - User object from the authentication server; a jsonObject.  Key: "user_login".
 704
 705         If we ever store anything else in userData, we will need to revisit this function so
 706         that it will free whatever else needs freeing.
 707 */
 708 static void sessionDataFree( char* key, void* item ) {
 709         if ( !strcmp( key, "xact_id" )
 710              || !strcmp( key, "authkey" ) ) {
 711                 free( item );
 712         } else if( !strcmp( key, "user_login" ) )
 713                 jsonObjectFree( (jsonObject*) item );
 714 }
 715
 716 /**
 717         @brief Save a transaction id.
 718         @param ctx Pointer to the method context.
 719
 720         Save the session_id of the current application session as a transaction id.
 721 */
 722 static void setXactId( osrfMethodContext* ctx ) {
 723         if( ctx && ctx->session ) {
 724                 osrfAppSession* session = ctx->session;
 725
 726                 osrfHash* cache = session->userData;
 727
 728                 // If the session doesn't already have a hash, create one.  Make sure
 729                 // that the application session frees the hash when it terminates.
 730                 if( NULL == cache ) {
 731                         session->userData = cache = osrfNewHash();
 732                         osrfHashSetCallback( cache, &sessionDataFree );
 733                         ctx->session->userDataFree = &userDataFree;
 734                 }
 735
 736                 // Save the transaction id in the hash, with the key "xact_id"
 737                 osrfHashSet( cache, strdup( session->session_id ), "xact_id" );
 738         }
 739 }
 740
 741 /**
 742         @brief Get the transaction ID for the current transaction, if any.
 743         @param ctx Pointer to the method context.
 744         @return Pointer to the transaction ID.
 745
 746         The return value points to an internal buffer, and will become invalid upon issuing
 747         a commit or rollback.
 748 */
 749 static inline const char* getXactId( osrfMethodContext* ctx ) {
 750         if( ctx && ctx->session && ctx->session->userData )
 751                 return osrfHashGet( (osrfHash*) ctx->session->userData, "xact_id" );
 752         else
 753                 return NULL;
 754 }
 755
 756 /**
 757         @brief Clear the current transaction id.
 758         @param ctx Pointer to the method context.
 759 */
 760 static inline void clearXactId( osrfMethodContext* ctx ) {
 761         if( ctx && ctx->session && ctx->session->userData )
 762                 osrfHashRemove( ctx->session->userData, "xact_id" );
 763 }
 764 /*@}*/
 765
 766 #ifdef PCRUD
 767 /**
 768         @brief Save the user's login in the userData for the current application session.
 769         @param ctx Pointer to the method context.
 770         @param user_login Pointer to the user login object to be cached (we cache the original,
 771         not a copy of it).
 772
 773         If @a user_login is NULL, remove the user login if one is already cached.
 774 */
 775 static void setUserLogin( osrfMethodContext* ctx, jsonObject* user_login ) {
 776         if( ctx && ctx->session ) {
 777                 osrfAppSession* session = ctx->session;
 778
 779                 osrfHash* cache = session->userData;
 780
 781                 // If the session doesn't already have a hash, create one.  Make sure
 782                 // that the application session frees the hash when it terminates.
 783                 if( NULL == cache ) {
 784                         session->userData = cache = osrfNewHash();
 785                         osrfHashSetCallback( cache, &sessionDataFree );
 786                         ctx->session->userDataFree = &userDataFree;
 787                 }
 788
 789                 if( user_login )
 790                         osrfHashSet( cache, user_login, "user_login" );
 791                 else
 792                         osrfHashRemove( cache, "user_login" );
 793         }
 794 }
 795
 796 /**
 797         @brief Get the user login object for the current application session, if any.
 798         @param ctx Pointer to the method context.
 799         @return Pointer to the user login object if found; otherwise NULL.
 800
 801         The user login object was returned from the authentication server, and then cached so
 802         we don't have to call the authentication server again for the same user.
 803 */
 804 static const jsonObject* getUserLogin( osrfMethodContext* ctx ) {
 805         if( ctx && ctx->session && ctx->session->userData )
 806                 return osrfHashGet( (osrfHash*) ctx->session->userData, "user_login" );
 807         else
 808                 return NULL;
 809 }
 810
 811 /**
 812         @brief Save a copy of an authkey in the userData of the current application session.
 813         @param ctx Pointer to the method context.
 814         @param authkey The authkey to be saved.
 815
 816         If @a authkey is NULL, remove the authkey if one is already cached.
 817 */
 818 static void setAuthkey( osrfMethodContext* ctx, const char* authkey ) {
 819         if( ctx && ctx->session && authkey ) {
 820                 osrfAppSession* session = ctx->session;
 821                 osrfHash* cache = session->userData;
 822
 823                 // If the session doesn't already have a hash, create one.  Make sure
 824                 // that the application session frees the hash when it terminates.
 825                 if( NULL == cache ) {
 826                         session->userData = cache = osrfNewHash();
 827                         osrfHashSetCallback( cache, &sessionDataFree );
 828                         ctx->session->userDataFree = &userDataFree;
 829                 }
 830
 831                 // Save the transaction id in the hash, with the key "xact_id"
 832                 if( authkey && *authkey )
 833                         osrfHashSet( cache, strdup( authkey ), "authkey" );
 834                 else
 835                         osrfHashRemove( cache, "authkey" );
 836         }
 837 }
 838
 839 /**
 840         @brief Reset the login timeout.
 841         @param authkey The authentication key for the current login session.
 842         @param now The current time.
 843         @return Zero if successful, or 1 if not.
 844
 845         Tell the authentication server to reset the timeout so that the login session won't
 846         expire for a while longer.
 847
 848         We could dispense with the @a now parameter by calling time().  But we just called
 849         time() in order to decide whether to reset the timeout, so we might as well reuse
 850         the result instead of calling time() again.
 851 */
 852 static int reset_timeout( const char* authkey, time_t now ) {
 853         jsonObject* auth_object = jsonNewObject( authkey );
 854
 855         // Ask the authentication server to reset the timeout.  It returns an event
 856         // indicating success or failure.
 857         jsonObject* result = oilsUtilsQuickReq( "open-ils.auth",
 858                 "open-ils.auth.session.reset_timeout", auth_object );
 859         jsonObjectFree(auth_object);
 860
 861         if( !result || result->type != JSON_HASH ) {
 862                 osrfLogError( OSRF_LOG_MARK,
 863                          "Unexpected object type receieved from open-ils.auth.session.reset_timeout" );
 864                 jsonObjectFree( result );
 865                 return 1;       // Not the right sort of object returned
 866         }
 867
 868         const jsonObject* ilsevent = jsonObjectGetKey( result, "ilsevent" );
 869         if( !ilsevent || ilsevent->type != JSON_NUMBER ) {
 870                 osrfLogError( OSRF_LOG_MARK, "ilsevent is absent or malformed" );
 871                 jsonObjectFree( result );
 872                 return 1;    // Return code from method not available
 873         }
 874
 875         if( jsonObjectGetNumber( ilsevent ) != 0.0 ){
 876                 const char* desc = jsonObjectGetString( jsonObjectGetKey( result, "desc" ) );
 877                 if( !desc )
 878                         desc = "(No reason available)";    // failsafe; shouldn't happen
 879                 osrfLogInfo( OSRF_LOG_MARK, "Failure to reset timeout: %s", desc );
 880                 jsonObjectFree( result );
 881                 return 1;
 882         }
 883
 884         // Revise our local proxy for the timeout deadline
 885         // by a smallish fraction of the timeout interval
 886         const char* timeout = jsonObjectGetString( jsonObjectGetKey( result, "payload" ) );
 887         if( !timeout )
 888                 timeout = "1";   // failsafe; shouldn't happen
 889         time_next_reset = now + atoi( timeout ) / 15;
 890
 891         jsonObjectFree( result );
 892         return 0;     // Successfully reset timeout
 893 }
 894
 895 /**
 896         @brief Get the authkey string for the current application session, if any.
 897         @param ctx Pointer to the method context.
 898         @return Pointer to the cached authkey if found; otherwise NULL.
 899
 900         If present, the authkey string was cached from a previous method call.
 901 */
 902 static const char* getAuthkey( osrfMethodContext* ctx ) {
 903         if( ctx && ctx->session && ctx->session->userData ) {
 904                 const char* authkey = osrfHashGet( (osrfHash*) ctx->session->userData, "authkey" );
 905
 906                 // Possibly reset the authentication timeout to keep the login alive.  We do so
 907                 // no more than once per method call, and not at all if it has been only a short
 908                 // time since the last reset.
 909
 910                 // Here we reset explicitly, if at all.  We also implicitly reset the timeout
 911                 // whenever we call the "open-ils.auth.session.retrieve" method.
 912                 if( timeout_needs_resetting ) {
 913                         time_t now = time( NULL );
 914                         if( now >= time_next_reset && reset_timeout( authkey, now ) )
 915                                 authkey = NULL;    // timeout has apparently expired already
 916                 }
 917
 918                 timeout_needs_resetting = 0;
 919                 return authkey;
 920         }
 921         else
 922                 return NULL;
 923 }
 924 #endif
 925
 926 /**
 927         @brief Implement the transaction.begin method.
 928         @param ctx Pointer to the method context.
 929         @return Zero if successful, or -1 upon error.
 930
 931         Start a transaction.  Save a transaction ID for future reference.
 932
 933         Method parameters:
 934         - authkey (PCRUD only)
 935
 936         Return to client: Transaction ID
 937 */
 938 int beginTransaction ( osrfMethodContext* ctx ) {
 939         if(osrfMethodVerifyContext( ctx )) {
 940                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 941                 return -1;
 942         }
 943
 944 #ifdef PCRUD
 945         timeout_needs_resetting = 1;
 946         const jsonObject* user = verifyUserPCRUD( ctx );
 947         if (!user)
 948                 return -1;
 949 #endif
 950
 951         dbi_result result = dbi_conn_query(writehandle, "START TRANSACTION;");
 952         if (!result) {
 953                 osrfLogError(OSRF_LOG_MARK, "%s: Error starting transaction", MODULENAME );
 954                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 955                                 "osrfMethodException", ctx->request, "Error starting transaction" );
 956                 return -1;
 957         } else {
 958                 setXactId( ctx );
 959                 jsonObject* ret = jsonNewObject( getXactId( ctx ) );
 960                 osrfAppRespondComplete( ctx, ret );
 961                 jsonObjectFree(ret);
 962         }
 963         return 0;
 964 }
 965
 966 /**
 967         @brief Implement the savepoint.set method.
 968         @param ctx Pointer to the method context.
 969         @return Zero if successful, or -1 if not.
 970
 971         Issue a SAVEPOINT to the database server.
 972
 973         Method parameters:
 974         - authkey (PCRUD only)
 975         - savepoint name
 976
 977         Return to client: Savepoint name
 978 */
 979 int setSavepoint ( osrfMethodContext* ctx ) {
 980         if(osrfMethodVerifyContext( ctx )) {
 981                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 982                 return -1;
 983         }
 984
 985         int spNamePos = 0;
 986 #ifdef PCRUD
 987         timeout_needs_resetting = 1;
 988         spNamePos = 1;
 989         const jsonObject* user = verifyUserPCRUD( ctx );
 990         if (!user)
 991                 return -1;
 992 #endif
 993
 994         // Verify that a transaction is pending
 995         const char* trans_id = getXactId( ctx );
 996         if( NULL == trans_id ) {
 997                 osrfAppSessionStatus(
 998                         ctx->session,
 999                         OSRF_STATUS_INTERNALSERVERERROR,
1000                         "osrfMethodException",
1001                         ctx->request,
1002                         "No active transaction -- required for savepoints"
1003                 );
1004                 return -1;
1005         }
1006
1007         // Get the savepoint name from the method params
1008         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
1009
1010         dbi_result result = dbi_conn_queryf(writehandle, "SAVEPOINT \"%s\";", spName);
1011         if (!result) {
1012                 osrfLogError(
1013                         OSRF_LOG_MARK,
1014                         "%s: Error creating savepoint %s in transaction %s",
1015                         MODULENAME,
1016                         spName,
1017                         trans_id
1018                 );
1019                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1020                                 "osrfMethodException", ctx->request, "Error creating savepoint" );
1021                 return -1;
1022         } else {
1023                 jsonObject* ret = jsonNewObject(spName);
1024                 osrfAppRespondComplete( ctx, ret );
1025                 jsonObjectFree(ret);
1026         }
1027         return 0;
1028 }
1029
1030 /**
1031         @brief Implement the savepoint.release method.
1032         @param ctx Pointer to the method context.
1033         @return Zero if successful, or -1 if not.
1034
1035         Issue a RELEASE SAVEPOINT to the database server.
1036
1037         Method parameters:
1038         - authkey (PCRUD only)
1039         - savepoint name
1040
1041         Return to client: Savepoint name
1042 */
1043 int releaseSavepoint ( osrfMethodContext* ctx ) {
1044         if(osrfMethodVerifyContext( ctx )) {
1045                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
1046                 return -1;
1047         }
1048
1049         int spNamePos = 0;
1050 #ifdef PCRUD
1051         timeout_needs_resetting = 1;
1052         spNamePos = 1;
1053         const jsonObject* user = verifyUserPCRUD( ctx );
1054         if (!user)
1055                 return -1;
1056 #endif
1057
1058         // Verify that a transaction is pending
1059         const char* trans_id = getXactId( ctx );
1060         if( NULL == trans_id ) {
1061                 osrfAppSessionStatus(
1062                         ctx->session,
1063                         OSRF_STATUS_INTERNALSERVERERROR,
1064                         "osrfMethodException",
1065                         ctx->request,
1066                         "No active transaction -- required for savepoints"
1067                 );
1068                 return -1;
1069         }
1070
1071         // Get the savepoint name from the method params
1072         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
1073
1074         dbi_result result = dbi_conn_queryf(writehandle, "RELEASE SAVEPOINT \"%s\";", spName);
1075         if (!result) {
1076                 osrfLogError(
1077                         OSRF_LOG_MARK,
1078                         "%s: Error releasing savepoint %s in transaction %s",
1079                         MODULENAME,
1080                         spName,
1081                         trans_id
1082                 );
1083                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1084                                 "osrfMethodException", ctx->request, "Error releasing savepoint" );
1085                 return -1;
1086         } else {
1087                 jsonObject* ret = jsonNewObject(spName);
1088                 osrfAppRespondComplete( ctx, ret );
1089                 jsonObjectFree(ret);
1090         }
1091         return 0;
1092 }
1093
1094 /**
1095         @brief Implement the savepoint.rollback method.
1096         @param ctx Pointer to the method context.
1097         @return Zero if successful, or -1 if not.
1098
1099         Issue a ROLLBACK TO SAVEPOINT to the database server.
1100
1101         Method parameters:
1102         - authkey (PCRUD only)
1103         - savepoint name
1104
1105         Return to client: Savepoint name
1106 */
1107 int rollbackSavepoint ( osrfMethodContext* ctx ) {
1108         if(osrfMethodVerifyContext( ctx )) {
1109                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
1110                 return -1;
1111         }
1112
1113         int spNamePos = 0;
1114 #ifdef PCRUD
1115         timeout_needs_resetting = 1;
1116         spNamePos = 1;
1117         const jsonObject* user = verifyUserPCRUD( ctx );
1118         if (!user)
1119                 return -1;
1120 #endif
1121
1122         // Verify that a transaction is pending
1123         const char* trans_id = getXactId( ctx );
1124         if( NULL == trans_id ) {
1125                 osrfAppSessionStatus(
1126                         ctx->session,
1127                         OSRF_STATUS_INTERNALSERVERERROR,
1128                         "osrfMethodException",
1129                         ctx->request,
1130                         "No active transaction -- required for savepoints"
1131                 );
1132                 return -1;
1133         }
1134
1135         // Get the savepoint name from the method params
1136         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
1137
1138         dbi_result result = dbi_conn_queryf(writehandle, "ROLLBACK TO SAVEPOINT \"%s\";", spName);
1139         if (!result) {
1140                 osrfLogError(
1141                         OSRF_LOG_MARK,
1142                         "%s: Error rolling back savepoint %s in transaction %s",
1143                         MODULENAME,
1144                         spName,
1145                         trans_id
1146                 );
1147                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1148                                 "osrfMethodException", ctx->request, "Error rolling back savepoint" );
1149                 return -1;
1150         } else {
1151                 jsonObject* ret = jsonNewObject(spName);
1152                 osrfAppRespondComplete( ctx, ret );
1153                 jsonObjectFree(ret);
1154         }
1155         return 0;
1156 }
1157
1158 /**
1159         @brief Implement the transaction.commit method.
1160         @param ctx Pointer to the method context.
1161         @return Zero if successful, or -1 if not.
1162
1163         Issue a COMMIT to the database server.
1164
1165         Method parameters:
1166         - authkey (PCRUD only)
1167
1168         Return to client: Transaction ID.
1169 */
1170 int commitTransaction ( osrfMethodContext* ctx ) {
1171         if(osrfMethodVerifyContext( ctx )) {
1172                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1173                 return -1;
1174         }
1175
1176 #ifdef PCRUD
1177         timeout_needs_resetting = 1;
1178         const jsonObject* user = verifyUserPCRUD( ctx );
1179         if (!user)
1180                 return -1;
1181 #endif
1182
1183         // Verify that a transaction is pending
1184         const char* trans_id = getXactId( ctx );
1185         if( NULL == trans_id ) {
1186                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1187                                 "osrfMethodException", ctx->request, "No active transaction to commit" );
1188                 return -1;
1189         }
1190
1191         dbi_result result = dbi_conn_query(writehandle, "COMMIT;");
1192         if (!result) {
1193                 osrfLogError(OSRF_LOG_MARK, "%s: Error committing transaction", MODULENAME );
1194                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1195                                 "osrfMethodException", ctx->request, "Error committing transaction" );
1196                 return -1;
1197         } else {
1198                 jsonObject* ret = jsonNewObject( trans_id );
1199                 osrfAppRespondComplete( ctx, ret );
1200                 jsonObjectFree(ret);
1201                 clearXactId( ctx );
1202         }
1203         return 0;
1204 }
1205
1206 /**
1207         @brief Implement the transaction.rollback method.
1208         @param ctx Pointer to the method context.
1209         @return Zero if successful, or -1 if not.
1210
1211         Issue a ROLLBACK to the database server.
1212
1213         Method parameters:
1214         - authkey (PCRUD only)
1215
1216         Return to client: Transaction ID
1217 */
1218 int rollbackTransaction ( osrfMethodContext* ctx ) {
1219         if(osrfMethodVerifyContext( ctx )) {
1220                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
1221                 return -1;
1222         }
1223
1224 #ifdef PCRUD
1225         timeout_needs_resetting = 1;
1226         const jsonObject* user = verifyUserPCRUD( ctx );
1227         if (!user)
1228                 return -1;
1229 #endif
1230
1231         // Verify that a transaction is pending
1232         const char* trans_id = getXactId( ctx );
1233         if( NULL == trans_id ) {
1234                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1235                                 "osrfMethodException", ctx->request, "No active transaction to roll back" );
1236                 return -1;
1237         }
1238
1239         dbi_result result = dbi_conn_query(writehandle, "ROLLBACK;");
1240         if (!result) {
1241                 osrfLogError(OSRF_LOG_MARK, "%s: Error rolling back transaction", MODULENAME );
1242                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
1243                                 "osrfMethodException", ctx->request, "Error rolling back transaction" );
1244                 return -1;
1245         } else {
1246                 jsonObject* ret = jsonNewObject( trans_id );
1247                 osrfAppRespondComplete( ctx, ret );
1248                 jsonObjectFree(ret);
1249                 clearXactId( ctx );
1250         }
1251         return 0;
1252 }
1253
1254 /**
1255         @brief Implement the class-specific methods.
1256         @param ctx Pointer to the method context.
1257         @return Zero if successful, or -1 if not.
1258
1259         Branch on the method type: create, retrieve, update, delete, search, and id_list.
1260
1261         The method parameters and the type of value returned to the client depend on the method
1262         type.  However, for PCRUD methods, the first method parameter should always be an
1263         authkey.
1264 */
1265 int dispatchCRUDMethod ( osrfMethodContext* ctx ) {
1266         if(osrfMethodVerifyContext( ctx )) {
1267                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
1268                 return -1;
1269         }
1270
1271         int err = 0;                // to be returned to caller
1272         jsonObject * obj = NULL;    // to be returned to client
1273
1274 #ifdef PCRUD
1275         timeout_needs_resetting = 1;
1276 #endif
1277
1278         // Get the method type so that we can branch on it
1279         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1280         const char* methodtype = osrfHashGet( method_meta, "methodtype" );
1281
1282         if (!strcmp(methodtype, "create")) {
1283                 obj = doCreate(ctx, &err);
1284                 osrfAppRespondComplete( ctx, obj );
1285         }
1286         else if (!strcmp(methodtype, "retrieve")) {
1287                 obj = doRetrieve(ctx, &err);
1288                 osrfAppRespondComplete( ctx, obj );
1289         }
1290         else if (!strcmp(methodtype, "update")) {
1291                 obj = doUpdate(ctx, &err);
1292                 osrfAppRespondComplete( ctx, obj );
1293         }
1294         else if (!strcmp(methodtype, "delete")) {
1295                 obj = doDelete(ctx, &err);
1296                 osrfAppRespondComplete( ctx, obj );
1297         }
1298         else if (!strcmp(methodtype, "search")) {
1299
1300                 // Implement search method: return rows of the specified class that satisfy
1301                 // a specified WHERE clause.
1302
1303                 // Method parameters:
1304                 // - authkey (PCRUD only)
1305                 // - WHERE clause, as jsonObject
1306                 // - Other SQL clause(s), as a JSON_HASH: joins, SELECT list, LIMIT, etc.
1307
1308                 jsonObject* where_clause;
1309                 jsonObject* rest_of_query;
1310
1311 #ifdef PCRUD
1312                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1313                 rest_of_query = jsonObjectGetIndex( ctx->params, 2 );
1314 #else
1315                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1316                 rest_of_query = jsonObjectGetIndex( ctx->params, 1 );
1317 #endif
1318
1319                 // Do the query
1320                 osrfHash* class_obj = osrfHashGet( method_meta, "class" );
1321                 obj = doFieldmapperSearch( ctx, class_obj, where_clause, rest_of_query, &err );
1322
1323                 if(err)
1324                         return err;
1325
1326                 // Return each row to the client (except that some may be suppressed by PCRUD)
1327                 jsonObject* cur = 0;
1328                 unsigned long res_idx = 0;
1329                 while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1330 #ifdef PCRUD
1331                         if(!verifyObjectPCRUD(ctx, cur))
1332                                 continue;
1333 #endif
1334                         osrfAppRespond( ctx, cur );
1335                 }
1336                 osrfAppRespondComplete( ctx, NULL );
1337
1338         } else if (!strcmp(methodtype, "id_list")) {
1339
1340                 // Implement the id_list method.  Return the primary key values for all rows of the
1341                 // relevant class that satisfy a specified WHERE clause.  This method relies on the
1342                 // assumption that every class has a primary key consisting of a single column.
1343
1344                 // Method parameters:
1345                 // - authkey (PCRUD only)
1346                 // - WHERE clause, as jsonObject
1347                 // - Other SQL clause(s), as a JSON_HASH: joins, LIMIT, etc.
1348
1349                 jsonObject* where_clause;
1350                 jsonObject* rest_of_query;
1351
1352                 // We use the where clause without change.  But we need to massage the rest of the
1353                 // query, so we work with a copy of it instead of modifying the original.
1354
1355 #ifdef PCRUD
1356                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1357                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 2 ) );
1358 #else
1359                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1360                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 1 ) );
1361 #endif
1362
1363                 // Eliminate certain SQL clauses, if present
1364                 if ( rest_of_query ) {
1365                         jsonObjectRemoveKey( rest_of_query, "select" );
1366                         jsonObjectRemoveKey( rest_of_query, "no_i18n" );
1367                         jsonObjectRemoveKey( rest_of_query, "flesh" );
1368                         jsonObjectRemoveKey( rest_of_query, "flesh_columns" );
1369                 } else {
1370                         rest_of_query = jsonNewObjectType( JSON_HASH );
1371                 }
1372
1373                 jsonObjectSetKey( rest_of_query, "no_i18n", jsonNewBoolObject( 1 ) );
1374
1375                 // Get the class metadata
1376                 osrfHash* class_obj = osrfHashGet( method_meta, "class" );
1377
1378                 // Build a SELECT list containing just the primary key,
1379                 // i.e. like { "classname":["keyname"] }
1380                 jsonObject* col_list_obj = jsonNewObjectType( JSON_ARRAY );
1381                 jsonObjectPush( col_list_obj,     // Load array with name of primary key
1382                         jsonNewObject( osrfHashGet( class_obj, "primarykey" ) ) );
1383                 jsonObject* select_clause = jsonNewObjectType( JSON_HASH );
1384                 jsonObjectSetKey( select_clause, osrfHashGet( class_obj, "classname" ), col_list_obj );
1385
1386                 jsonObjectSetKey( rest_of_query, "select", select_clause );
1387
1388                 // Do the query
1389                 obj = doFieldmapperSearch( ctx, class_obj, where_clause, rest_of_query, &err );
1390
1391                 jsonObjectFree( rest_of_query );
1392                 if(err)
1393                         return err;
1394
1395                 // Return each primary key value to the client
1396                 jsonObject* cur;
1397                 unsigned long res_idx = 0;
1398                 while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1399 #ifdef PCRUD
1400                         if(!verifyObjectPCRUD(ctx, cur))
1401                                 continue;
1402 #endif
1403                         osrfAppRespond( ctx,
1404                                 oilsFMGetObject( cur, osrfHashGet( class_obj, "primarykey" ) )
1405                         );
1406                 }
1407                 osrfAppRespondComplete( ctx, NULL );
1408
1409         } else {
1410                 osrfAppRespondComplete( ctx, obj );      // should be unreachable...
1411         }
1412
1413         jsonObjectFree(obj);
1414
1415         return err;
1416 }
1417
1418 /**
1419         @brief Verify that we have a valid class reference.
1420         @param ctx Pointer to the method context.
1421         @param param Pointer to the method parameters.
1422         @return 1 if the class reference is valid, or zero if it isn't.
1423
1424         The class of the method params must match the class to which the method id devoted.
1425         For PCRUD there are additional restrictions.
1426 */
1427 static int verifyObjectClass ( osrfMethodContext* ctx, const jsonObject* param ) {
1428
1429         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1430         osrfHash* class = osrfHashGet( method_meta, "class" );
1431
1432         // Compare the method's class to the parameters' class
1433         if (!param->classname || (strcmp( osrfHashGet(class, "classname"), param->classname ))) {
1434
1435                 // Oops -- they don't match.  Complain.
1436                 growing_buffer* msg = buffer_init(128);
1437                 buffer_fadd(
1438                         msg,
1439                         "%s: %s method for type %s was passed a %s",
1440                         MODULENAME,
1441                         osrfHashGet(method_meta, "methodtype"),
1442                         osrfHashGet(class, "classname"),
1443                         param->classname ? param->classname : "(null)"
1444                 );
1445
1446                 char* m = buffer_release(msg);
1447                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
1448                                 ctx->request, m );
1449                 free(m);
1450
1451                 return 0;
1452         }
1453
1454         int ret = 1;
1455 #ifdef PCRUD
1456         ret = verifyObjectPCRUD( ctx, param );
1457 #endif
1458
1459         return ret;
1460 }
1461
1462 #ifdef PCRUD
1463
1464 /**
1465         @brief (PCRUD only) Verify that the user is properly logged in.
1466         @param ctx Pointer to the method context.
1467         @return If the user is logged in, a pointer to the user object from the authentication
1468         server; otherwise NULL.
1469 */
1470 static const jsonObject* verifyUserPCRUD( osrfMethodContext* ctx ) {
1471
1472         // Get the authkey (the first method parameter)
1473         const char* auth = jsonObjectGetString( jsonObjectGetIndex( ctx->params, 0 ) );
1474
1475         // See if we have the same authkey, and a user object,
1476         // locally cached from a previous call
1477         const char* cached_authkey = getAuthkey( ctx );
1478         if( cached_authkey && !strcmp( cached_authkey, auth ) ) {
1479                 const jsonObject* cached_user = getUserLogin( ctx );
1480                 if( cached_user )
1481                         return cached_user;
1482         }
1483
1484         // We have no matching authentication data in the cache.  Authenticate from scratch.
1485         jsonObject* auth_object = jsonNewObject(auth);
1486
1487         // Fetch the user object from the authentication server
1488         jsonObject* user = oilsUtilsQuickReq( "open-ils.auth", "open-ils.auth.session.retrieve",
1489                         auth_object );
1490         jsonObjectFree(auth_object);
1491
1492         if (!user->classname || strcmp(user->classname, "au")) {
1493
1494                 growing_buffer* msg = buffer_init(128);
1495                 buffer_fadd(
1496                         msg,
1497                         "%s: permacrud received a bad auth token: %s",
1498                         MODULENAME,
1499                         auth
1500                 );
1501
1502                 char* m = buffer_release(msg);
1503                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_UNAUTHORIZED, "osrfMethodException",
1504                                 ctx->request, m );
1505
1506                 free(m);
1507                 jsonObjectFree(user);
1508                 user = NULL;
1509         }
1510
1511         setUserLogin( ctx, user );
1512         setAuthkey( ctx, auth );
1513
1514         // Allow ourselves up to a second before we have to reset the login timeout.
1515         // It would be nice to use some fraction of the timeout interval enforced by the
1516         // authentication server, but that value is not readily available at this point.
1517         // Instead, we use a conservative default interval.
1518         time_next_reset = time( NULL ) + 1;
1519
1520         return user;
1521 }
1522
1523 static int verifyObjectPCRUD (  osrfMethodContext* ctx, const jsonObject* obj ) {
1524
1525         dbhandle = writehandle;
1526
1527         osrfHash* method_metadata = (osrfHash*) ctx->method->userData;
1528         osrfHash* class = osrfHashGet( method_metadata, "class" );
1529         const char* method_type = osrfHashGet( method_metadata, "methodtype" );
1530         int fetch = 0;
1531
1532         if ( ( *method_type == 's' || *method_type == 'i' ) ) {
1533                 method_type = "retrieve"; // search and id_list are equivalent to retrieve for this
1534         } else if ( *method_type == 'u' || *method_type == 'd' ) {
1535                 fetch = 1; // MUST go to the db for the object for update and delete
1536         }
1537
1538         osrfHash* pcrud = osrfHashGet( osrfHashGet(class, "permacrud"), method_type );
1539
1540         if (!pcrud) {
1541                 // No permacrud for this method type on this class
1542
1543                 growing_buffer* msg = buffer_init(128);
1544                 buffer_fadd(
1545                         msg,
1546                         "%s: %s on class %s has no permacrud IDL entry",
1547                         MODULENAME,
1548                         osrfHashGet(method_metadata, "methodtype"),
1549                         osrfHashGet(class, "classname")
1550                 );
1551
1552                 char* m = buffer_release(msg);
1553                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_FORBIDDEN,
1554                                 "osrfMethodException", ctx->request, m );
1555
1556                 free(m);
1557
1558                 return 0;
1559         }
1560
1561         const jsonObject* user = verifyUserPCRUD( ctx );
1562         if (!user)
1563                 return 0;
1564
1565         int userid = atoi( oilsFMGetString( user, "id" ) );
1566
1567         osrfStringArray* permission = osrfHashGet(pcrud, "permission");
1568         osrfStringArray* local_context = osrfHashGet(pcrud, "local_context");
1569         osrfHash* foreign_context = osrfHashGet(pcrud, "foreign_context");
1570
1571         osrfStringArray* context_org_array = osrfNewStringArray(1);
1572
1573         int err = 0;
1574         char* pkey_value = NULL;
1575         if ( str_is_true( osrfHashGet(pcrud, "global_required") ) ) {
1576                 osrfLogDebug( OSRF_LOG_MARK,
1577                                 "global-level permissions required, fetching top of the org tree" );
1578
1579                 // check for perm at top of org tree
1580                 char* org_tree_root_id = org_tree_root( ctx );
1581                 if( org_tree_root_id ) {
1582                         osrfStringArrayAdd( context_org_array, org_tree_root_id );
1583                         osrfLogDebug( OSRF_LOG_MARK, "top of the org tree is %s", org_tree_root_id );
1584                 } else  {
1585                         osrfStringArrayFree( context_org_array );
1586                         return 0;
1587                 }
1588
1589         } else {
1590             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions not required, "
1591                                 "fetching context org ids" );
1592             const char* pkey = osrfHashGet(class, "primarykey");
1593                 jsonObject *param = NULL;
1594
1595                 if (obj->classname) {
1596                         pkey_value = oilsFMGetString( obj, pkey );
1597                         if (!fetch)
1598                                 param = jsonObjectClone(obj);
1599                         osrfLogDebug( OSRF_LOG_MARK, "Object supplied, using primary key value of %s",
1600                                         pkey_value );
1601                 } else {
1602                         pkey_value = jsonObjectToSimpleString( obj );
1603                         fetch = 1;
1604                         osrfLogDebug( OSRF_LOG_MARK, "Object not supplied, using primary key value "
1605                                         "of %s and retrieving from the database", pkey_value );
1606                 }
1607
1608                 if (fetch) {
1609                         jsonObject* _tmp_params = single_hash( pkey, pkey_value );
1610                         jsonObject* _list = doFieldmapperSearch( ctx, class, _tmp_params, NULL, &err );
1611                         jsonObjectFree(_tmp_params);
1612
1613                         param = jsonObjectExtractIndex(_list, 0);
1614                         jsonObjectFree(_list);
1615                 }
1616
1617                 if (!param) {
1618                         osrfLogDebug( OSRF_LOG_MARK,
1619                                         "Object not found in the database with primary key %s of %s",
1620                                         pkey, pkey_value );
1621
1622                         growing_buffer* msg = buffer_init(128);
1623                         buffer_fadd(
1624                                 msg,
1625                                 "%s: no object found with primary key %s of %s",
1626                                 MODULENAME,
1627                                 pkey,
1628                                 pkey_value
1629                         );
1630
1631                         char* m = buffer_release(msg);
1632                         osrfAppSessionStatus(
1633                                 ctx->session,
1634                                 OSRF_STATUS_INTERNALSERVERERROR,
1635                                 "osrfMethodException",
1636                                 ctx->request,
1637                                 m
1638                         );
1639
1640                         free(m);
1641                         if (pkey_value) free(pkey_value);
1642
1643                         return 0;
1644                 }
1645
1646                 if (local_context->size > 0) {
1647                         osrfLogDebug( OSRF_LOG_MARK, "%d class-local context field(s) specified",
1648                                         local_context->size);
1649                         int i = 0;
1650                         const char* lcontext = NULL;
1651                         while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
1652                                 osrfStringArrayAdd( context_org_array, oilsFMGetString( param, lcontext ) );
1653                                 osrfLogDebug(
1654                                         OSRF_LOG_MARK,
1655                                         "adding class-local field %s (value: %s) to the context org list",
1656                                         lcontext,
1657                                         osrfStringArrayGetString(context_org_array, context_org_array->size - 1)
1658                                 );
1659                         }
1660                 }
1661
1662                 if (foreign_context) {
1663                         unsigned long class_count = osrfHashGetCount( foreign_context );
1664                         osrfLogDebug( OSRF_LOG_MARK, "%d foreign context classes(s) specified", class_count);
1665
1666                         if (class_count > 0) {
1667
1668                                 osrfHash* fcontext = NULL;
1669                                 osrfHashIterator* class_itr = osrfNewHashIterator( foreign_context );
1670                                 while( (fcontext = osrfHashIteratorNext( class_itr ) ) ) {
1671                                         const char* class_name = osrfHashIteratorKey( class_itr );
1672                                         osrfHash* fcontext = osrfHashGet(foreign_context, class_name);
1673
1674                                         osrfLogDebug(
1675                                                 OSRF_LOG_MARK,
1676                                                 "%d foreign context fields(s) specified for class %s",
1677                                                 ((osrfStringArray*)osrfHashGet(fcontext,"context"))->size,
1678                                                 class_name
1679                                         );
1680
1681                                         char* foreign_pkey = osrfHashGet(fcontext, "field");
1682                                         char* foreign_pkey_value =
1683                                                         oilsFMGetString(param, osrfHashGet(fcontext, "fkey"));
1684
1685                                         jsonObject* _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1686
1687                                         jsonObject* _list = doFieldmapperSearch(
1688                                                 ctx, osrfHashGet( oilsIDL(), class_name ), _tmp_params, NULL, &err );
1689
1690                                         jsonObject* _fparam = jsonObjectClone(jsonObjectGetIndex(_list, 0));
1691                                         jsonObjectFree(_tmp_params);
1692                                         jsonObjectFree(_list);
1693
1694                                         osrfStringArray* jump_list = osrfHashGet(fcontext, "jump");
1695
1696                                         if (_fparam && jump_list) {
1697                                                 const char* flink = NULL;
1698                                                 int k = 0;
1699                                                 while ( (flink = osrfStringArrayGetString(jump_list, k++)) && _fparam ) {
1700                                                         free(foreign_pkey_value);
1701
1702                                                         osrfHash* foreign_link_hash =
1703                                                                         oilsIDLFindPath( "/%s/links/%s", _fparam->classname, flink );
1704
1705                                                         foreign_pkey_value = oilsFMGetString(_fparam, flink);
1706                                                         foreign_pkey = osrfHashGet( foreign_link_hash, "key" );
1707
1708                                                         _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1709
1710                                                         _list = doFieldmapperSearch(
1711                                                                 ctx,
1712                                                                 osrfHashGet( oilsIDL(),
1713                                                                                 osrfHashGet( foreign_link_hash, "class" ) ),
1714                                                                 _tmp_params,
1715                                                                 NULL,
1716                                                                 &err
1717                                                         );
1718
1719                                                         _fparam = jsonObjectClone(jsonObjectGetIndex(_list, 0));
1720                                                         jsonObjectFree(_tmp_params);
1721                                                         jsonObjectFree(_list);
1722                                                 }
1723                                         }
1724
1725                                         if (!_fparam) {
1726
1727                                                 growing_buffer* msg = buffer_init(128);
1728                                                 buffer_fadd(
1729                                                         msg,
1730                                                         "%s: no object found with primary key %s of %s",
1731                                                         MODULENAME,
1732                                                         foreign_pkey,
1733                                                         foreign_pkey_value
1734                                                 );
1735
1736                                                 char* m = buffer_release(msg);
1737                                                 osrfAppSessionStatus(
1738                                                         ctx->session,
1739                                                         OSRF_STATUS_INTERNALSERVERERROR,
1740                                                         "osrfMethodException",
1741                                                         ctx->request,
1742                                                         m
1743                                                 );
1744
1745                                                 free(m);
1746                                                 osrfHashIteratorFree(class_itr);
1747                                                 free(foreign_pkey_value);
1748                                                 jsonObjectFree(param);
1749
1750                                                 return 0;
1751                                         }
1752
1753                                         free(foreign_pkey_value);
1754
1755                                         int j = 0;
1756                                         const char* foreign_field = NULL;
1757                                         while ( (foreign_field = osrfStringArrayGetString(
1758                                                          osrfHashGet(fcontext,"context" ), j++)) ) {
1759                                                 osrfStringArrayAdd( context_org_array,
1760                                                                 oilsFMGetString( _fparam, foreign_field ) );
1761                                                 osrfLogDebug(
1762                                                         OSRF_LOG_MARK,
1763                                                         "adding foreign class %s field %s (value: %s) to the context org list",
1764                                                         class_name,
1765                                                         foreign_field,
1766                                                         osrfStringArrayGetString(
1767                                                                         context_org_array, context_org_array->size - 1)
1768                                                 );
1769                                         }
1770
1771                                         jsonObjectFree(_fparam);
1772                                 }
1773
1774                                 osrfHashIteratorFree( class_itr );
1775                         }
1776                 }
1777
1778                 jsonObjectFree(param);
1779         }
1780
1781         const char* context_org = NULL;
1782         const char* perm = NULL;
1783         int OK = 0;
1784
1785         if (permission->size == 0) {
1786             osrfLogDebug( OSRF_LOG_MARK, "No permission specified for this action, passing through" );
1787                 OK = 1;
1788         }
1789
1790         int i = 0;
1791         while ( (perm = osrfStringArrayGetString(permission, i++)) ) {
1792                 int j = 0;
1793                 while ( (context_org = osrfStringArrayGetString(context_org_array, j++)) ) {
1794                         dbi_result result;
1795
1796                         if (pkey_value) {
1797                                 osrfLogDebug(
1798                                         OSRF_LOG_MARK,
1799                                         "Checking object permission [%s] for user %d "
1800                                                         "on object %s (class %s) at org %d",
1801                                         perm,
1802                                         userid,
1803                                         pkey_value,
1804                                         osrfHashGet(class, "classname"),
1805                                         atoi(context_org)
1806                                 );
1807
1808                                 result = dbi_conn_queryf(
1809                                         writehandle,
1810                                         "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
1811                                         userid,
1812                                         perm,
1813                                         osrfHashGet(class, "classname"),
1814                                         pkey_value,
1815                                         atoi(context_org)
1816                                 );
1817
1818                                 if (result) {
1819                                         osrfLogDebug(
1820                                                 OSRF_LOG_MARK,
1821                                                 "Received a result for object permission [%s] "
1822                                                                 "for user %d on object %s (class %s) at org %d",
1823                                                 perm,
1824                                                 userid,
1825                                                 pkey_value,
1826                                                 osrfHashGet(class, "classname"),
1827                                                 atoi(context_org)
1828                                         );
1829
1830                                         if (dbi_result_first_row(result)) {
1831                                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
1832                                                 const char* has_perm = jsonObjectGetString(
1833                                                                 jsonObjectGetKeyConst(return_val, "has_perm") );
1834
1835                                                 osrfLogDebug(
1836                                                         OSRF_LOG_MARK,
1837                                                         "Status of object permission [%s] for user %d "
1838                                                                         "on object %s (class %s) at org %d is %s",
1839                                                         perm,
1840                                                         userid,
1841                                                         pkey_value,
1842                                                         osrfHashGet(class, "classname"),
1843                                                         atoi(context_org),
1844                                                         has_perm
1845                                                 );
1846
1847                                                 if ( *has_perm == 't' ) OK = 1;
1848                                                 jsonObjectFree(return_val);
1849                                         }
1850
1851                                         dbi_result_free(result);
1852                                         if (OK)
1853                                                 break;
1854                                 }
1855                         }
1856
1857                         osrfLogDebug( OSRF_LOG_MARK,
1858                                         "Checking non-object permission [%s] for user %d at org %d",
1859                                         perm, userid, atoi(context_org) );
1860                         result = dbi_conn_queryf(
1861                                 writehandle,
1862                                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
1863                                 userid,
1864                                 perm,
1865                                 atoi(context_org)
1866                         );
1867
1868                         if (result) {
1869                                 osrfLogDebug( OSRF_LOG_MARK,
1870                                                 "Received a result for permission [%s] for user %d at org %d",
1871                                                 perm, userid, atoi(context_org) );
1872                                 if ( dbi_result_first_row(result) ) {
1873                                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1874                                         const char* has_perm = jsonObjectGetString(
1875                                                         jsonObjectGetKeyConst(return_val, "has_perm") );
1876                                         osrfLogDebug( OSRF_LOG_MARK,
1877                                                         "Status of permission [%s] for user %d at org %d is [%s]",
1878                                                         perm, userid, atoi(context_org), has_perm );
1879                                         if ( *has_perm == 't' )
1880                                                 OK = 1;
1881                                         jsonObjectFree(return_val);
1882                                 }
1883
1884                                 dbi_result_free(result);
1885                                 if (OK) break;
1886                         }
1887
1888                 }
1889                 if (OK)
1890                         break;
1891         }
1892
1893         if (pkey_value) free(pkey_value);
1894         osrfStringArrayFree(context_org_array);
1895
1896         return OK;
1897 }
1898
1899 /**
1900         @brief Look up the root of the org_unit tree.
1901         @param ctx Pointer to the method context.
1902         @return The id of the root org unit, as a character string.
1903
1904         Query actor.org_unit where parent_ou is null, and return the id as a string.
1905
1906         This function assumes that there is only one root org unit, i.e. that we
1907         have a single tree, not a forest.
1908
1909         The calling code is responsible for freeing the returned string.
1910 */
1911 static char* org_tree_root( osrfMethodContext* ctx ) {
1912
1913         static char cached_root_id[ 32 ] = "";  // extravagantly large buffer
1914         static time_t last_lookup_time = 0;
1915         time_t current_time = time( NULL );
1916
1917         if( cached_root_id[ 0 ] && ( current_time - last_lookup_time < 3600 ) ) {
1918                 // We successfully looked this up less than an hour ago.
1919                 // It's not likely to have changed since then.
1920                 return strdup( cached_root_id );
1921         }
1922         last_lookup_time = current_time;
1923
1924         int err = 0;
1925         jsonObject* where_clause = single_hash( "parent_ou", NULL );
1926         jsonObject* result = doFieldmapperSearch(
1927                 ctx, osrfHashGet( oilsIDL(), "aou" ), where_clause, NULL, &err );
1928         jsonObjectFree( where_clause );
1929
1930         jsonObject* tree_top = jsonObjectGetIndex( result, 0 );
1931
1932         if (! tree_top) {
1933                 jsonObjectFree( result );
1934
1935                 growing_buffer* msg = buffer_init(128);
1936                 OSRF_BUFFER_ADD( msg, MODULENAME );
1937                 OSRF_BUFFER_ADD( msg,
1938                                 ": Internal error, could not find the top of the org tree (parent_ou = NULL)" );
1939
1940                 char* m = buffer_release(msg);
1941                 osrfAppSessionStatus( ctx->session,
1942                                 OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, m );
1943                 free(m);
1944
1945                 cached_root_id[ 0 ] = '\0';
1946                 return NULL;
1947         }
1948
1949         char* root_org_unit_id = oilsFMGetString( tree_top, "id" );
1950         osrfLogDebug( OSRF_LOG_MARK, "Top of the org tree is %s", root_org_unit_id );
1951
1952         jsonObjectFree( result );
1953
1954         strcpy( cached_root_id, root_org_unit_id );
1955         return root_org_unit_id;
1956 }
1957
1958 /**
1959         @brief Create a JSON_HASH with a single key/value pair.
1960         @param key The key of the key/value pair.
1961         @param value the value of the key/value pair.
1962         @return Pointer to a newly created jsonObject of type JSON_HASH.
1963
1964         The value of the key/value is either a string or (if @a value is NULL) a null.
1965 */
1966 static jsonObject* single_hash( const char* key, const char* value ) {
1967         // Sanity check
1968         if( ! key ) key = "";
1969
1970         jsonObject* hash = jsonNewObjectType( JSON_HASH );
1971         jsonObjectSetKey( hash, key, jsonNewObject( value ) );
1972         return hash;
1973 }
1974 #endif
1975
1976
1977 static jsonObject* doCreate(osrfMethodContext* ctx, int* err ) {
1978
1979         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1980 #ifdef PCRUD
1981         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
1982         jsonObject* options = jsonObjectGetIndex( ctx->params, 2 );
1983 #else
1984         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
1985         jsonObject* options = jsonObjectGetIndex( ctx->params, 1 );
1986 #endif
1987
1988         if (!verifyObjectClass(ctx, target)) {
1989                 *err = -1;
1990                 return jsonNULL;
1991         }
1992
1993         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1994
1995         const char* trans_id = getXactId( ctx );
1996         if ( !trans_id ) {
1997                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1998
1999                 osrfAppSessionStatus(
2000                         ctx->session,
2001                         OSRF_STATUS_BADREQUEST,
2002                         "osrfMethodException",
2003                         ctx->request,
2004                         "No active transaction -- required for CREATE"
2005                 );
2006                 *err = -1;
2007                 return jsonNULL;
2008         }
2009
2010         // The following test is harmless but redundant.  If a class is
2011         // readonly, we don't register a create method for it.
2012         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
2013                 osrfAppSessionStatus(
2014                         ctx->session,
2015                         OSRF_STATUS_BADREQUEST,
2016                         "osrfMethodException",
2017                         ctx->request,
2018                         "Cannot INSERT readonly class"
2019                 );
2020                 *err = -1;
2021                 return jsonNULL;
2022         }
2023
2024         // Set the last_xact_id
2025         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
2026         if (index > -1) {
2027                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
2028                         trans_id, target->classname, index);
2029                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
2030         }
2031
2032         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
2033
2034         dbhandle = writehandle;
2035
2036         osrfHash* fields = osrfHashGet(meta, "fields");
2037         char* pkey = osrfHashGet(meta, "primarykey");
2038         char* seq = osrfHashGet(meta, "sequence");
2039
2040         growing_buffer* table_buf = buffer_init(128);
2041         growing_buffer* col_buf = buffer_init(128);
2042         growing_buffer* val_buf = buffer_init(128);
2043
2044         OSRF_BUFFER_ADD(table_buf, "INSERT INTO ");
2045         OSRF_BUFFER_ADD(table_buf, osrfHashGet(meta, "tablename"));
2046         OSRF_BUFFER_ADD_CHAR( col_buf, '(' );
2047         buffer_add(val_buf,"VALUES (");
2048
2049
2050         int first = 1;
2051         osrfHash* field = NULL;
2052         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
2053         while( (field = osrfHashIteratorNext( field_itr ) ) ) {
2054
2055                 const char* field_name = osrfHashIteratorKey( field_itr );
2056
2057                 if( str_is_true( osrfHashGet( field, "virtual" ) ) )
2058                         continue;
2059
2060                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
2061
2062                 char* value;
2063                 if (field_object && field_object->classname) {
2064                         value = oilsFMGetString(
2065                                 field_object,
2066                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
2067                         );
2068                 } else if( field_object && JSON_BOOL == field_object->type ) {
2069                         if( jsonBoolIsTrue( field_object ) )
2070                                 value = strdup( "t" );
2071                         else
2072                                 value = strdup( "f" );
2073                 } else {
2074                         value = jsonObjectToSimpleString( field_object );
2075                 }
2076
2077                 if (first) {
2078                         first = 0;
2079                 } else {
2080                         OSRF_BUFFER_ADD_CHAR( col_buf, ',' );
2081                         OSRF_BUFFER_ADD_CHAR( val_buf, ',' );
2082                 }
2083
2084                 buffer_add(col_buf, field_name);
2085
2086                 if (!field_object || field_object->type == JSON_NULL) {
2087                         buffer_add( val_buf, "DEFAULT" );
2088
2089                 } else if ( !strcmp(get_primitive( field ), "number") ) {
2090                         const char* numtype = get_datatype( field );
2091                         if ( !strcmp( numtype, "INT8") ) {
2092                                 buffer_fadd( val_buf, "%lld", atoll(value) );
2093
2094                         } else if ( !strcmp( numtype, "INT") ) {
2095                                 buffer_fadd( val_buf, "%d", atoi(value) );
2096
2097                         } else if ( !strcmp( numtype, "NUMERIC") ) {
2098                                 buffer_fadd( val_buf, "%f", atof(value) );
2099                         }
2100                 } else {
2101                         if ( dbi_conn_quote_string(writehandle, &value) ) {
2102                                 OSRF_BUFFER_ADD( val_buf, value );
2103
2104                         } else {
2105                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
2106                                 osrfAppSessionStatus(
2107                                         ctx->session,
2108                                         OSRF_STATUS_INTERNALSERVERERROR,
2109                                         "osrfMethodException",
2110                                         ctx->request,
2111                                         "Error quoting string -- please see the error log for more details"
2112                                 );
2113                                 free(value);
2114                                 buffer_free(table_buf);
2115                                 buffer_free(col_buf);
2116                                 buffer_free(val_buf);
2117                                 *err = -1;
2118                                 return jsonNULL;
2119                         }
2120                 }
2121
2122                 free(value);
2123
2124         }
2125
2126         osrfHashIteratorFree( field_itr );
2127
2128         OSRF_BUFFER_ADD_CHAR( col_buf, ')' );
2129         OSRF_BUFFER_ADD_CHAR( val_buf, ')' );
2130
2131         char* table_str = buffer_release(table_buf);
2132         char* col_str   = buffer_release(col_buf);
2133         char* val_str   = buffer_release(val_buf);
2134         growing_buffer* sql = buffer_init(128);
2135         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
2136         free(table_str);
2137         free(col_str);
2138         free(val_str);
2139
2140         char* query = buffer_release(sql);
2141
2142         osrfLogDebug(OSRF_LOG_MARK, "%s: Insert SQL [%s]", MODULENAME, query);
2143
2144
2145         dbi_result result = dbi_conn_query(writehandle, query);
2146
2147         jsonObject* obj = NULL;
2148
2149         if (!result) {
2150                 obj = jsonNewObject(NULL);
2151                 osrfLogError(
2152                         OSRF_LOG_MARK,
2153                         "%s ERROR inserting %s object using query [%s]",
2154                         MODULENAME,
2155                         osrfHashGet(meta, "fieldmapper"),
2156                         query
2157                 );
2158                 osrfAppSessionStatus(
2159                         ctx->session,
2160                         OSRF_STATUS_INTERNALSERVERERROR,
2161                         "osrfMethodException",
2162                         ctx->request,
2163                         "INSERT error -- please see the error log for more details"
2164                 );
2165                 *err = -1;
2166         } else {
2167
2168                 char* id = oilsFMGetString(target, pkey);
2169                 if (!id) {
2170                         unsigned long long new_id = dbi_conn_sequence_last(writehandle, seq);
2171                         growing_buffer* _id = buffer_init(10);
2172                         buffer_fadd(_id, "%lld", new_id);
2173                         id = buffer_release(_id);
2174                 }
2175
2176                 // Find quietness specification, if present
2177                 const char* quiet_str = NULL;
2178                 if ( options ) {
2179                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
2180                         if( quiet_obj )
2181                                 quiet_str = jsonObjectGetString( quiet_obj );
2182                 }
2183
2184                 if( str_is_true( quiet_str ) ) {  // if quietness is specified
2185                         obj = jsonNewObject(id);
2186                 }
2187                 else {
2188
2189                         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2190                         jsonObjectSetKey( where_clause, pkey, jsonNewObject(id) );
2191
2192                         jsonObject* list = doFieldmapperSearch( ctx, meta, where_clause, NULL, err );
2193
2194                         jsonObjectFree( where_clause );
2195
2196                         if(*err) {
2197                                 obj = jsonNULL;
2198                         } else {
2199                                 obj = jsonObjectClone( jsonObjectGetIndex(list, 0) );
2200                         }
2201
2202                         jsonObjectFree( list );
2203                 }
2204
2205                 free(id);
2206         }
2207
2208         free(query);
2209
2210         return obj;
2211
2212 }
2213
2214 /**
2215         @brief Implement the retrieve method.
2216         @param ctx Pointer to the method context.
2217         @param err Pointer through which to return an error code.
2218         @return If successful, a pointer to the result to be returned to the client;
2219         otherwise NULL.
2220
2221         From the method's class, fetch a row with a specified value in the primary key.  This
2222         method relies on the database design convention that a primary key consists of a single
2223         column.
2224
2225         Method parameters:
2226         - authkey (PCRUD only)
2227         - value of the primary key for the desired row, for building the WHERE clause
2228         - a JSON_HASH containing any other SQL clauses: select, join, etc.
2229
2230         Return to client: One row from the query.
2231 */
2232 static jsonObject* doRetrieve(osrfMethodContext* ctx, int* err ) {
2233
2234         int id_pos = 0;
2235         int order_pos = 1;
2236
2237 #ifdef PCRUD
2238         id_pos = 1;
2239         order_pos = 2;
2240 #endif
2241
2242         // Get the class metadata
2243         osrfHash* class_def = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
2244
2245         // Get the value of the primary key, from a method parameter
2246         const jsonObject* id_obj = jsonObjectGetIndex(ctx->params, id_pos);
2247
2248         osrfLogDebug(
2249                 OSRF_LOG_MARK,
2250                 "%s retrieving %s object with primary key value of %s",
2251                 MODULENAME,
2252                 osrfHashGet( class_def, "fieldmapper" ),
2253                 jsonObjectGetString( id_obj )
2254         );
2255
2256         // Build a WHERE clause based on the key value
2257         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2258         jsonObjectSetKey(
2259                 where_clause,
2260                 osrfHashGet( class_def, "primarykey" ),  // name of key column
2261                 jsonObjectClone( id_obj )                // value of key column
2262         );
2263
2264         jsonObject* rest_of_query = jsonObjectGetIndex(ctx->params, order_pos);
2265
2266         // Do the query
2267         jsonObject* list = doFieldmapperSearch( ctx, class_def, where_clause, rest_of_query, err );
2268
2269         jsonObjectFree( where_clause );
2270         if(*err)
2271                 return NULL;
2272
2273         jsonObject* obj = jsonObjectExtractIndex( list, 0 );
2274         jsonObjectFree( list );
2275
2276 #ifdef PCRUD
2277         if(!verifyObjectPCRUD(ctx, obj)) {
2278                 jsonObjectFree(obj);
2279                 *err = -1;
2280
2281                 growing_buffer* msg = buffer_init(128);
2282                 OSRF_BUFFER_ADD( msg, MODULENAME );
2283                 OSRF_BUFFER_ADD( msg, ": Insufficient permissions to retrieve object" );
2284
2285                 char* m = buffer_release(msg);
2286                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_NOTALLOWED, "osrfMethodException",
2287                                 ctx->request, m );
2288
2289                 free(m);
2290
2291                 return jsonNULL;
2292         }
2293 #endif
2294
2295         return obj;
2296 }
2297
2298 static char* jsonNumberToDBString ( osrfHash* field, const jsonObject* value ) {
2299         growing_buffer* val_buf = buffer_init(32);
2300         const char* numtype = get_datatype( field );
2301
2302         if ( !strncmp( numtype, "INT", 3 ) ) {
2303                 if (value->type == JSON_NUMBER)
2304                         //buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
2305                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2306                 else {
2307                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2308                 }
2309
2310         } else if ( !strcmp( numtype, "NUMERIC" ) ) {
2311                 if (value->type == JSON_NUMBER)
2312                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2313                 else {
2314                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2315                 }
2316
2317         } else {
2318                 // Presumably this was really intended ot be a string, so quote it
2319                 char* str = jsonObjectToSimpleString( value );
2320                 if ( dbi_conn_quote_string(dbhandle, &str) ) {
2321                         OSRF_BUFFER_ADD( val_buf, str );
2322                         free(str);
2323                 } else {
2324                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, str);
2325                         free(str);
2326                         buffer_free(val_buf);
2327                         return NULL;
2328                 }
2329         }
2330
2331         return buffer_release(val_buf);
2332 }
2333
2334 static char* searchINPredicate (const char* class_alias, osrfHash* field,
2335                 jsonObject* node, const char* op, osrfMethodContext* ctx ) {
2336         growing_buffer* sql_buf = buffer_init(32);
2337
2338         buffer_fadd(
2339                 sql_buf,
2340                 "\"%s\".%s ",
2341                 class_alias,
2342                 osrfHashGet(field, "name")
2343         );
2344
2345         if (!op) {
2346                 buffer_add(sql_buf, "IN (");
2347         } else if (!(strcasecmp(op,"not in"))) {
2348                 buffer_add(sql_buf, "NOT IN (");
2349         } else {
2350                 buffer_add(sql_buf, "IN (");
2351         }
2352
2353         if (node->type == JSON_HASH) {
2354                 // subquery predicate
2355                 char* subpred = buildQuery( ctx, node, SUBSELECT );
2356                 if( ! subpred ) {
2357                         buffer_free( sql_buf );
2358                         return NULL;
2359                 }
2360
2361                 buffer_add(sql_buf, subpred);
2362                 free(subpred);
2363
2364         } else if (node->type == JSON_ARRAY) {
2365                 // literal value list
2366                 int in_item_index = 0;
2367                 int in_item_first = 1;
2368                 const jsonObject* in_item;
2369                 while ( (in_item = jsonObjectGetIndex(node, in_item_index++)) ) {
2370
2371                         if (in_item_first)
2372                                 in_item_first = 0;
2373                         else
2374                                 buffer_add(sql_buf, ", ");
2375
2376                         // Sanity check
2377                         if ( in_item->type != JSON_STRING && in_item->type != JSON_NUMBER ) {
2378                                 osrfLogError( OSRF_LOG_MARK,
2379                                                 "%s: Expected string or number within IN list; found %s",
2380                                                 MODULENAME, json_type( in_item->type ) );
2381                                 buffer_free(sql_buf);
2382                                 return NULL;
2383                         }
2384
2385                         // Append the literal value -- quoted if not a number
2386                         if ( JSON_NUMBER == in_item->type ) {
2387                                 char* val = jsonNumberToDBString( field, in_item );
2388                                 OSRF_BUFFER_ADD( sql_buf, val );
2389                                 free(val);
2390
2391                         } else if ( !strcmp( get_primitive( field ), "number") ) {
2392                                 char* val = jsonNumberToDBString( field, in_item );
2393                                 OSRF_BUFFER_ADD( sql_buf, val );
2394                                 free(val);
2395
2396                         } else {
2397                                 char* key_string = jsonObjectToSimpleString(in_item);
2398                                 if ( dbi_conn_quote_string(dbhandle, &key_string) ) {
2399                                         OSRF_BUFFER_ADD( sql_buf, key_string );
2400                                         free(key_string);
2401                                 } else {
2402                                         osrfLogError(OSRF_LOG_MARK,
2403                                                         "%s: Error quoting key string [%s]", MODULENAME, key_string);
2404                                         free(key_string);
2405                                         buffer_free(sql_buf);
2406                                         return NULL;
2407                                 }
2408                         }
2409                 }
2410
2411                 if( in_item_first ) {
2412                         osrfLogError(OSRF_LOG_MARK, "%s: Empty IN list", MODULENAME );
2413                         buffer_free( sql_buf );
2414                         return NULL;
2415                 }
2416         } else {
2417                 osrfLogError(OSRF_LOG_MARK, "%s: Expected object or array for IN clause; found %s",
2418                         MODULENAME, json_type( node->type ) );
2419                 buffer_free(sql_buf);
2420                 return NULL;
2421         }
2422
2423         OSRF_BUFFER_ADD_CHAR( sql_buf, ')' );
2424
2425         return buffer_release(sql_buf);
2426 }
2427
2428 // Receive a JSON_ARRAY representing a function call.  The first
2429 // entry in the array is the function name.  The rest are parameters.
2430 static char* searchValueTransform( const jsonObject* array ) {
2431
2432         if( array->size < 1 ) {
2433                 osrfLogError(OSRF_LOG_MARK, "%s: Empty array for value transform", MODULENAME);
2434                 return NULL;
2435         }
2436
2437         // Get the function name
2438         jsonObject* func_item = jsonObjectGetIndex( array, 0 );
2439         if( func_item->type != JSON_STRING ) {
2440                 osrfLogError(OSRF_LOG_MARK, "%s: Error: expected function name, found %s",
2441                         MODULENAME, json_type( func_item->type ) );
2442                 return NULL;
2443         }
2444
2445         growing_buffer* sql_buf = buffer_init(32);
2446
2447         OSRF_BUFFER_ADD( sql_buf, jsonObjectGetString( func_item ) );
2448         OSRF_BUFFER_ADD( sql_buf, "( " );
2449
2450         // Get the parameters
2451         int func_item_index = 1;   // We already grabbed the zeroth entry
2452         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
2453
2454                 // Add a separator comma, if we need one
2455                 if( func_item_index > 2 )
2456                         buffer_add( sql_buf, ", " );
2457
2458                 // Add the current parameter
2459                 if (func_item->type == JSON_NULL) {
2460                         buffer_add( sql_buf, "NULL" );
2461                 } else {
2462                         char* val = jsonObjectToSimpleString(func_item);
2463                         if ( dbi_conn_quote_string(dbhandle, &val) ) {
2464                                 OSRF_BUFFER_ADD( sql_buf, val );
2465                                 free(val);
2466                         } else {
2467                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key string [%s]", MODULENAME, val);
2468                                 buffer_free(sql_buf);
2469                                 free(val);
2470                                 return NULL;
2471                         }
2472                 }
2473         }
2474
2475         buffer_add( sql_buf, " )" );
2476
2477         return buffer_release(sql_buf);
2478 }
2479
2480 static char* searchFunctionPredicate (const char* class_alias, osrfHash* field,
2481                 const jsonObject* node, const char* op) {
2482
2483         if( ! is_good_operator( op ) ) {
2484                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", MODULENAME, op );
2485                 return NULL;
2486         }
2487
2488         char* val = searchValueTransform(node);
2489         if( !val )
2490                 return NULL;
2491
2492         growing_buffer* sql_buf = buffer_init(32);
2493         buffer_fadd(
2494                 sql_buf,
2495                 "\"%s\".%s %s %s",
2496                 class_alias,
2497                 osrfHashGet(field, "name"),
2498                 op,
2499                 val
2500         );
2501
2502         free(val);
2503
2504         return buffer_release(sql_buf);
2505 }
2506
2507 // class_alias is a class name or other table alias
2508 // field is a field definition as stored in the IDL
2509 // node comes from the method parameter, and may represent an entry in the SELECT list
2510 static char* searchFieldTransform (const char* class_alias, osrfHash* field, const jsonObject* node) {
2511         growing_buffer* sql_buf = buffer_init(32);
2512
2513         const char* field_transform = jsonObjectGetString(
2514                 jsonObjectGetKeyConst( node, "transform" ) );
2515         const char* transform_subcolumn = jsonObjectGetString(
2516                 jsonObjectGetKeyConst( node, "result_field" ) );
2517
2518         if(transform_subcolumn) {
2519                 if( ! is_identifier( transform_subcolumn ) ) {
2520                         osrfLogError( OSRF_LOG_MARK, "%s: Invalid subfield name: \"%s\"\n",
2521                                         MODULENAME, transform_subcolumn );
2522                         buffer_free( sql_buf );
2523                         return NULL;
2524                 }
2525                 OSRF_BUFFER_ADD_CHAR( sql_buf, '(' );    // enclose transform in parentheses
2526         }
2527
2528         if (field_transform) {
2529
2530                 if( ! is_identifier( field_transform ) ) {
2531                         osrfLogError( OSRF_LOG_MARK, "%s: Expected function name, found \"%s\"\n",
2532                                         MODULENAME, field_transform );
2533                         buffer_free( sql_buf );
2534                         return NULL;
2535                 }
2536
2537                 buffer_fadd( sql_buf, "%s(\"%s\".%s",
2538                         field_transform, class_alias, osrfHashGet(field, "name"));
2539                 const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
2540
2541                 if (array) {
2542                         if( array->type != JSON_ARRAY ) {
2543                                 osrfLogError( OSRF_LOG_MARK,
2544                                         "%s: Expected JSON_ARRAY for function params; found %s",
2545                                         MODULENAME, json_type( array->type ) );
2546                                 buffer_free( sql_buf );
2547                                 return NULL;
2548                         }
2549                         int func_item_index = 0;
2550                         jsonObject* func_item;
2551                         while ( (func_item = jsonObjectGetIndex(array, func_item_index++)) ) {
2552
2553                                 char* val = jsonObjectToSimpleString(func_item);
2554
2555                                 if ( !val ) {
2556                                         buffer_add( sql_buf, ",NULL" );
2557                                 } else if ( dbi_conn_quote_string(dbhandle, &val) ) {
2558                                         OSRF_BUFFER_ADD_CHAR( sql_buf, ',' );
2559                                         OSRF_BUFFER_ADD( sql_buf, val );
2560                                 } else {
2561                                         osrfLogError( OSRF_LOG_MARK,
2562                                                         "%s: Error quoting key string [%s]", MODULENAME, val);
2563                                         free(val);
2564                                         buffer_free(sql_buf);
2565                                         return NULL;
2566                                 }
2567                                 free(val);
2568                         }
2569                 }
2570
2571                 buffer_add( sql_buf, " )" );
2572
2573         } else {
2574                 buffer_fadd( sql_buf, "\"%s\".%s", class_alias, osrfHashGet(field, "name"));
2575         }
2576
2577         if (transform_subcolumn)
2578                 buffer_fadd( sql_buf, ").\"%s\"", transform_subcolumn );
2579
2580         return buffer_release(sql_buf);
2581 }
2582
2583 static char* searchFieldTransformPredicate( const ClassInfo* class_info, osrfHash* field,
2584                 const jsonObject* node, const char* op ) {
2585
2586         if( ! is_good_operator( op ) ) {
2587                 osrfLogError(OSRF_LOG_MARK, "%s: Error: Invalid operator %s", MODULENAME, op);
2588                 return NULL;
2589         }
2590
2591         char* field_transform = searchFieldTransform( class_info->alias, field, node );
2592         if( ! field_transform )
2593                 return NULL;
2594         char* value = NULL;
2595         int extra_parens = 0;   // boolean
2596
2597         const jsonObject* value_obj = jsonObjectGetKeyConst( node, "value" );
2598         if ( ! value_obj ) {
2599                 value = searchWHERE( node, class_info, AND_OP_JOIN, NULL );
2600                 if( !value ) {
2601                         osrfLogError( OSRF_LOG_MARK, "%s: Error building condition for field transform",
2602                                 MODULENAME );
2603                         free(field_transform);
2604                         return NULL;
2605                 }
2606                 extra_parens = 1;
2607         } else if ( value_obj->type == JSON_ARRAY ) {
2608                 value = searchValueTransform( value_obj );
2609                 if( !value ) {
2610                         osrfLogError(OSRF_LOG_MARK,
2611                                 "%s: Error building value transform for field transform", MODULENAME);
2612                         free( field_transform );
2613                         return NULL;
2614                 }
2615         } else if ( value_obj->type == JSON_HASH ) {
2616                 value = searchWHERE( value_obj, class_info, AND_OP_JOIN, NULL );
2617                 if( !value ) {
2618                         osrfLogError(OSRF_LOG_MARK, "%s: Error building predicate for field transform",
2619                                 MODULENAME);
2620                         free(field_transform);
2621                         return NULL;
2622                 }
2623                 extra_parens = 1;
2624         } else if ( value_obj->type == JSON_NUMBER ) {
2625                 value = jsonNumberToDBString( field, value_obj );
2626         } else if ( value_obj->type == JSON_NULL ) {
2627                 osrfLogError( OSRF_LOG_MARK,
2628                         "%s: Error building predicate for field transform: null value", MODULENAME );
2629                 free(field_transform);
2630                 return NULL;
2631         } else if ( value_obj->type == JSON_BOOL ) {
2632                 osrfLogError( OSRF_LOG_MARK,
2633                         "%s: Error building predicate for field transform: boolean value", MODULENAME );
2634                 free(field_transform);
2635                 return NULL;
2636         } else {
2637                 if ( !strcmp( get_primitive( field ), "number") ) {
2638                         value = jsonNumberToDBString( field, value_obj );
2639                 } else {
2640                         value = jsonObjectToSimpleString( value_obj );
2641                         if ( !dbi_conn_quote_string(dbhandle, &value) ) {
2642                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2643                                         MODULENAME, value);
2644                                 free(value);
2645                                 free(field_transform);
2646                                 return NULL;
2647                         }
2648                 }
2649         }
2650
2651         const char* left_parens  = "";
2652         const char* right_parens = "";
2653
2654         if( extra_parens ) {
2655                 left_parens  = "(";
2656                 right_parens = ")";
2657         }
2658
2659         growing_buffer* sql_buf = buffer_init(32);
2660
2661         buffer_fadd(
2662                 sql_buf,
2663                 "%s%s %s %s %s %s%s",
2664                 left_parens,
2665                 field_transform,
2666                 op,
2667                 left_parens,
2668                 value,
2669                 right_parens,
2670                 right_parens
2671         );
2672
2673         free(value);
2674         free(field_transform);
2675
2676         return buffer_release(sql_buf);
2677 }
2678
2679 static char* searchSimplePredicate (const char* op, const char* class_alias,
2680                 osrfHash* field, const jsonObject* node) {
2681
2682         if( ! is_good_operator( op ) ) {
2683                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", MODULENAME, op );
2684                 return NULL;
2685         }
2686
2687         char* val = NULL;
2688
2689         // Get the value to which we are comparing the specified column
2690         if (node->type != JSON_NULL) {
2691                 if ( node->type == JSON_NUMBER ) {
2692                         val = jsonNumberToDBString( field, node );
2693                 } else if ( !strcmp( get_primitive( field ), "number" ) ) {
2694                         val = jsonNumberToDBString( field, node );
2695                 } else {
2696                         val = jsonObjectToSimpleString(node);
2697                 }
2698         }
2699
2700         if( val ) {
2701                 if( JSON_NUMBER != node->type && strcmp( get_primitive( field ), "number") ) {
2702                         // Value is not numeric; enclose it in quotes
2703                         if ( !dbi_conn_quote_string( dbhandle, &val ) ) {
2704                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2705                                         MODULENAME, val );
2706                                 free( val );
2707                                 return NULL;
2708                         }
2709                 }
2710         } else {
2711                 // Compare to a null value
2712                 val = strdup( "NULL" );
2713                 if (strcmp( op, "=" ))
2714                         op = "IS NOT";
2715                 else
2716                         op = "IS";
2717         }
2718
2719         growing_buffer* sql_buf = buffer_init(32);
2720         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class_alias, osrfHashGet(field, "name"), op, val );
2721         char* pred = buffer_release( sql_buf );
2722
2723         free(val);
2724
2725         return pred;
2726 }
2727
2728 static char* searchBETWEENPredicate (const char* class_alias,
2729                 osrfHash* field, const jsonObject* node) {
2730
2731         const jsonObject* x_node = jsonObjectGetIndex( node, 0 );
2732         const jsonObject* y_node = jsonObjectGetIndex( node, 1 );
2733
2734         if( NULL == y_node ) {
2735                 osrfLogError( OSRF_LOG_MARK, "%s: Not enough operands for BETWEEN operator", MODULENAME );
2736                 return NULL;
2737         }
2738         else if( NULL != jsonObjectGetIndex( node, 2 ) ) {
2739                 osrfLogError( OSRF_LOG_MARK, "%s: Too many operands for BETWEEN operator", MODULENAME );
2740                 return NULL;
2741         }
2742
2743         char* x_string;
2744         char* y_string;
2745
2746         if ( !strcmp( get_primitive( field ), "number") ) {
2747                 x_string = jsonNumberToDBString(field, x_node);
2748                 y_string = jsonNumberToDBString(field, y_node);
2749
2750         } else {
2751                 x_string = jsonObjectToSimpleString(x_node);
2752                 y_string = jsonObjectToSimpleString(y_node);
2753                 if ( !(dbi_conn_quote_string(dbhandle, &x_string) && dbi_conn_quote_string(dbhandle, &y_string)) ) {
2754                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]",
2755                                         MODULENAME, x_string, y_string);
2756                         free(x_string);
2757                         free(y_string);
2758                         return NULL;
2759                 }
2760         }
2761
2762         growing_buffer* sql_buf = buffer_init(32);
2763         buffer_fadd( sql_buf, "\"%s\".%s BETWEEN %s AND %s",
2764                         class_alias, osrfHashGet(field, "name"), x_string, y_string );
2765         free(x_string);
2766         free(y_string);
2767
2768         return buffer_release(sql_buf);
2769 }
2770
2771 static char* searchPredicate ( const ClassInfo* class_info, osrfHash* field,
2772                                                            jsonObject* node, osrfMethodContext* ctx ) {
2773
2774         char* pred = NULL;
2775         if (node->type == JSON_ARRAY) { // equality IN search
2776                 pred = searchINPredicate( class_info->alias, field, node, NULL, ctx );
2777         } else if (node->type == JSON_HASH) { // other search
2778                 jsonIterator* pred_itr = jsonNewIterator( node );
2779                 if( !jsonIteratorHasNext( pred_itr ) ) {
2780                         osrfLogError( OSRF_LOG_MARK, "%s: Empty predicate for field \"%s\"",
2781                                         MODULENAME, osrfHashGet(field, "name") );
2782                 } else {
2783                         jsonObject* pred_node = jsonIteratorNext( pred_itr );
2784
2785                         // Verify that there are no additional predicates
2786                         if( jsonIteratorHasNext( pred_itr ) ) {
2787                                 osrfLogError( OSRF_LOG_MARK, "%s: Multiple predicates for field \"%s\"",
2788                                                 MODULENAME, osrfHashGet(field, "name") );
2789                         } else if ( !(strcasecmp( pred_itr->key,"between" )) )
2790                                 pred = searchBETWEENPredicate( class_info->alias, field, pred_node );
2791                         else if ( !(strcasecmp( pred_itr->key,"in" ))
2792                                         || !(strcasecmp( pred_itr->key,"not in" )) )
2793                                 pred = searchINPredicate(
2794                                         class_info->alias, field, pred_node, pred_itr->key, ctx );
2795                         else if ( pred_node->type == JSON_ARRAY )
2796                                 pred = searchFunctionPredicate(
2797                                         class_info->alias, field, pred_node, pred_itr->key );
2798                         else if ( pred_node->type == JSON_HASH )
2799                                 pred = searchFieldTransformPredicate(
2800                                         class_info, field, pred_node, pred_itr->key );
2801                         else
2802                                 pred = searchSimplePredicate( pred_itr->key, class_info->alias, field, pred_node );
2803                 }
2804                 jsonIteratorFree(pred_itr);
2805
2806         } else if (node->type == JSON_NULL) { // IS NULL search
2807                 growing_buffer* _p = buffer_init(64);
2808                 buffer_fadd(
2809                         _p,
2810                         "\"%s\".%s IS NULL",
2811                         class_info->class_name,
2812                         osrfHashGet(field, "name")
2813                 );
2814                 pred = buffer_release(_p);
2815         } else { // equality search
2816                 pred = searchSimplePredicate( "=", class_info->alias, field, node );
2817         }
2818
2819         return pred;
2820
2821 }
2822
2823
2824 /*
2825
2826 join : {
2827         acn : {
2828                 field : record,
2829                 fkey : id
2830                 type : left
2831                 filter_op : or
2832                 filter : { ... },
2833                 join : {
2834                         acp : {
2835                                 field : call_number,
2836                                 fkey : id,
2837                                 filter : { ... },
2838                         },
2839                 },
2840         },
2841         mrd : {
2842                 field : record,
2843                 type : inner
2844                 fkey : id,
2845                 filter : { ... },
2846         }
2847 }
2848
2849 */
2850
2851 static char* searchJOIN ( const jsonObject* join_hash, const ClassInfo* left_info ) {
2852
2853         const jsonObject* working_hash;
2854         jsonObject* freeable_hash = NULL;
2855
2856         if (join_hash->type == JSON_HASH) {
2857                 working_hash = join_hash;
2858         } else if (join_hash->type == JSON_STRING) {
2859                 // turn it into a JSON_HASH by creating a wrapper
2860                 // around a copy of the original
2861                 const char* _tmp = jsonObjectGetString( join_hash );
2862                 freeable_hash = jsonNewObjectType(JSON_HASH);
2863                 jsonObjectSetKey(freeable_hash, _tmp, NULL);
2864                 working_hash = freeable_hash;
2865         } else {
2866                 osrfLogError(
2867                         OSRF_LOG_MARK,
2868                         "%s: JOIN failed; expected JSON object type not found",
2869                         MODULENAME
2870                 );
2871                 return NULL;
2872         }
2873
2874         growing_buffer* join_buf = buffer_init(128);
2875         const char* leftclass = left_info->class_name;
2876
2877         jsonObject* snode = NULL;
2878         jsonIterator* search_itr = jsonNewIterator( working_hash );
2879
2880         while ( (snode = jsonIteratorNext( search_itr )) ) {
2881                 const char* right_alias = search_itr->key;
2882                 const char* class =
2883                                 jsonObjectGetString( jsonObjectGetKeyConst( snode, "class" ) );
2884                 if( ! class )
2885                         class = right_alias;
2886
2887                 const ClassInfo* right_info = add_joined_class( right_alias, class );
2888                 if( !right_info ) {
2889                         osrfLogError(
2890                                 OSRF_LOG_MARK,
2891                                 "%s: JOIN failed.  Class \"%s\" not resolved in IDL",
2892                                 MODULENAME,
2893                                 search_itr->key
2894                         );
2895                         jsonIteratorFree( search_itr );
2896                         buffer_free( join_buf );
2897                         if( freeable_hash )
2898                                 jsonObjectFree( freeable_hash );
2899                         return NULL;
2900                 }
2901                 osrfHash* links    = right_info->links;
2902                 const char* table  = right_info->source_def;
2903
2904                 const char* fkey  = jsonObjectGetString( jsonObjectGetKeyConst( snode, "fkey" ) );
2905                 const char* field = jsonObjectGetString( jsonObjectGetKeyConst( snode, "field" ) );
2906
2907                 if (field && !fkey) {
2908                         // Look up the corresponding join column in the IDL.
2909                         // The link must be defined in the child table,
2910                         // and point to the right parent table.
2911                         osrfHash* idl_link = (osrfHash*) osrfHashGet( links, field );
2912                         const char* reltype = NULL;
2913                         const char* other_class = NULL;
2914                         reltype = osrfHashGet( idl_link, "reltype" );
2915                         if( reltype && strcmp( reltype, "has_many" ) )
2916                                 other_class = osrfHashGet( idl_link, "class" );
2917                         if( other_class && !strcmp( other_class, leftclass ) )
2918                                 fkey = osrfHashGet( idl_link, "key" );
2919                         if (!fkey) {
2920                                 osrfLogError(
2921                                         OSRF_LOG_MARK,
2922                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2923                                         MODULENAME,
2924                                         class,
2925                                         field,
2926                                         leftclass
2927                                 );
2928                                 buffer_free(join_buf);
2929                                 if(freeable_hash)
2930                                         jsonObjectFree(freeable_hash);
2931                                 jsonIteratorFree(search_itr);
2932                                 return NULL;
2933                         }
2934
2935                 } else if (!field && fkey) {
2936                         // Look up the corresponding join column in the IDL.
2937                         // The link must be defined in the child table,
2938                         // and point to the right parent table.
2939                         osrfHash* left_links = left_info->links;
2940                         osrfHash* idl_link = (osrfHash*) osrfHashGet( left_links, fkey );
2941                         const char* reltype = NULL;
2942                         const char* other_class = NULL;
2943                         reltype = osrfHashGet( idl_link, "reltype" );
2944                         if( reltype && strcmp( reltype, "has_many" ) )
2945                                 other_class = osrfHashGet( idl_link, "class" );
2946                         if( other_class && !strcmp( other_class, class ) )
2947                                 field = osrfHashGet( idl_link, "key" );
2948                         if (!field) {
2949                                 osrfLogError(
2950                                         OSRF_LOG_MARK,
2951                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2952                                         MODULENAME,
2953                                         leftclass,
2954                                         fkey,
2955                                         class
2956                                 );
2957                                 buffer_free(join_buf);
2958                                 if(freeable_hash)
2959                                         jsonObjectFree(freeable_hash);
2960                                 jsonIteratorFree(search_itr);
2961                                 return NULL;
2962                         }
2963
2964                 } else if (!field && !fkey) {
2965                         osrfHash* left_links = left_info->links;
2966
2967                         // For each link defined for the left class:
2968                         // see if the link references the joined class
2969                         osrfHashIterator* itr = osrfNewHashIterator( left_links );
2970                         osrfHash* curr_link = NULL;
2971                         while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2972                                 const char* other_class = osrfHashGet( curr_link, "class" );
2973                                 if( other_class && !strcmp( other_class, class ) ) {
2974
2975                                         // In the IDL, the parent class doesn't always know then names of the child
2976                                         // columns that are pointing to it, so don't use that end of the link
2977                                         const char* reltype = osrfHashGet( curr_link, "reltype" );
2978                                         if( reltype && strcmp( reltype, "has_many" ) ) {
2979                                                 // Found a link between the classes
2980                                                 fkey = osrfHashIteratorKey( itr );
2981                                                 field = osrfHashGet( curr_link, "key" );
2982                                                 break;
2983                                         }
2984                                 }
2985                         }
2986                         osrfHashIteratorFree( itr );
2987
2988                         if (!field || !fkey) {
2989                                 // Do another such search, with the classes reversed
2990
2991                                 // For each link defined for the joined class:
2992                                 // see if the link references the left class
2993                                 osrfHashIterator* itr = osrfNewHashIterator( links );
2994                                 osrfHash* curr_link = NULL;
2995                                 while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2996                                         const char* other_class = osrfHashGet( curr_link, "class" );
2997                                         if( other_class && !strcmp( other_class, leftclass ) ) {
2998
2999                                                 // In the IDL, the parent class doesn't know then names of the child
3000                                                 // columns that are pointing to it, so don't use that end of the link
3001                                                 const char* reltype = osrfHashGet( curr_link, "reltype" );
3002                                                 if( reltype && strcmp( reltype, "has_many" ) ) {
3003                                                         // Found a link between the classes
3004                                                         field = osrfHashIteratorKey( itr );
3005                                                         fkey = osrfHashGet( curr_link, "key" );
3006                                                         break;
3007                                                 }
3008                                         }
3009                                 }
3010                                 osrfHashIteratorFree( itr );
3011                         }
3012
3013                         if (!field || !fkey) {
3014                                 osrfLogError(
3015                                         OSRF_LOG_MARK,
3016                                         "%s: JOIN failed.  No link defined between %s and %s",
3017                                         MODULENAME,
3018                                         leftclass,
3019                                         class
3020                                 );
3021                                 buffer_free(join_buf);
3022                                 if(freeable_hash)
3023                                         jsonObjectFree(freeable_hash);
3024                                 jsonIteratorFree(search_itr);
3025                                 return NULL;
3026                         }
3027                 }
3028
3029                 const char* type = jsonObjectGetString( jsonObjectGetKeyConst( snode, "type" ) );
3030                 if (type) {
3031                         if ( !strcasecmp(type,"left") ) {
3032                                 buffer_add(join_buf, " LEFT JOIN");
3033                         } else if ( !strcasecmp(type,"right") ) {
3034                                 buffer_add(join_buf, " RIGHT JOIN");
3035                         } else if ( !strcasecmp(type,"full") ) {
3036                                 buffer_add(join_buf, " FULL JOIN");
3037                         } else {
3038                                 buffer_add(join_buf, " INNER JOIN");
3039                         }
3040                 } else {
3041                         buffer_add(join_buf, " INNER JOIN");
3042                 }
3043
3044                 buffer_fadd(join_buf, " %s AS \"%s\" ON ( \"%s\".%s = \"%s\".%s",
3045                                         table, right_alias, right_alias, field, left_info->alias, fkey);
3046
3047                 // Add any other join conditions as specified by "filter"
3048                 const jsonObject* filter = jsonObjectGetKeyConst( snode, "filter" );
3049                 if (filter) {
3050                         const char* filter_op = jsonObjectGetString(
3051                                 jsonObjectGetKeyConst( snode, "filter_op" ) );
3052                         if ( filter_op && !strcasecmp("or",filter_op) ) {
3053                                 buffer_add( join_buf, " OR " );
3054                         } else {
3055                                 buffer_add( join_buf, " AND " );
3056                         }
3057
3058                         char* jpred = searchWHERE( filter, right_info, AND_OP_JOIN, NULL );
3059                         if( jpred ) {
3060                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
3061                                 OSRF_BUFFER_ADD( join_buf, jpred );
3062                                 free(jpred);
3063                         } else {
3064                                 osrfLogError(
3065                                         OSRF_LOG_MARK,
3066                                         "%s: JOIN failed.  Invalid conditional expression.",
3067                                         MODULENAME
3068                                 );
3069                                 jsonIteratorFree( search_itr );
3070                                 buffer_free( join_buf );
3071                                 if( freeable_hash )
3072                                         jsonObjectFree( freeable_hash );
3073                                 return NULL;
3074                         }
3075                 }
3076
3077                 buffer_add(join_buf, " ) ");
3078
3079                 // Recursively add a nested join, if one is present
3080                 const jsonObject* join_filter = jsonObjectGetKeyConst( snode, "join" );
3081                 if (join_filter) {
3082                         char* jpred = searchJOIN( join_filter, right_info );
3083                         if( jpred ) {
3084                                 OSRF_BUFFER_ADD_CHAR( join_buf, ' ' );
3085                                 OSRF_BUFFER_ADD( join_buf, jpred );
3086                                 free(jpred);
3087                         } else {
3088                                 osrfLogError(  OSRF_LOG_MARK, "%s: Invalid nested join.", MODULENAME );
3089                                 jsonIteratorFree( search_itr );
3090                                 buffer_free( join_buf );
3091                                 if( freeable_hash )
3092                                         jsonObjectFree( freeable_hash );
3093                                 return NULL;
3094                         }
3095                 }
3096         }
3097
3098         if(freeable_hash)
3099                 jsonObjectFree(freeable_hash);
3100         jsonIteratorFree(search_itr);
3101
3102         return buffer_release(join_buf);
3103 }
3104
3105 /*
3106
3107 { +class : { -or|-and : { field : { op : value }, ... } ... }, ... }
3108 { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }
3109 [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ]
3110
3111 Generate code to express a set of conditions, as for a WHERE clause.  Parameters:
3112
3113 search_hash is the JSON expression of the conditions.
3114 meta is the class definition from the IDL, for the relevant table.
3115 opjoin_type indicates whether multiple conditions, if present, should be
3116         connected by AND or OR.
3117 osrfMethodContext is loaded with all sorts of stuff, but all we do with it here is
3118         to pass it to other functions -- and all they do with it is to use the session
3119         and request members to send error messages back to the client.
3120
3121 */
3122
3123 static char* searchWHERE ( const jsonObject* search_hash, const ClassInfo* class_info,
3124                 int opjoin_type, osrfMethodContext* ctx ) {
3125
3126         osrfLogDebug(
3127                 OSRF_LOG_MARK,
3128                 "%s: Entering searchWHERE; search_hash addr = %p, meta addr = %p, "
3129                 "opjoin_type = %d, ctx addr = %p",
3130                 MODULENAME,
3131                 search_hash,
3132                 class_info->class_def,
3133                 opjoin_type,
3134                 ctx
3135         );
3136
3137         growing_buffer* sql_buf = buffer_init(128);
3138
3139         jsonObject* node = NULL;
3140
3141         int first = 1;
3142         if ( search_hash->type == JSON_ARRAY ) {
3143                 osrfLogDebug( OSRF_LOG_MARK,
3144                         "%s: In WHERE clause, condition type is JSON_ARRAY", MODULENAME );
3145                 if( 0 == search_hash->size ) {
3146                         osrfLogError(
3147                                 OSRF_LOG_MARK,
3148                                 "%s: Invalid predicate structure: empty JSON array",
3149                                 MODULENAME
3150                         );
3151                         buffer_free( sql_buf );
3152                         return NULL;
3153                 }
3154
3155                 unsigned long i = 0;
3156                 while((node = jsonObjectGetIndex( search_hash, i++ ) )) {
3157                         if (first) {
3158                                 first = 0;
3159                         } else {
3160                                 if (opjoin_type == OR_OP_JOIN)
3161                                         buffer_add(sql_buf, " OR ");
3162                                 else
3163                                         buffer_add(sql_buf, " AND ");
3164                         }
3165
3166                         char* subpred = searchWHERE( node, class_info, opjoin_type, ctx );
3167                         if( ! subpred ) {
3168                                 buffer_free( sql_buf );
3169                                 return NULL;
3170                         }
3171
3172                         buffer_fadd(sql_buf, "( %s )", subpred);
3173                         free(subpred);
3174                 }
3175
3176         } else if ( search_hash->type == JSON_HASH ) {
3177                 osrfLogDebug( OSRF_LOG_MARK,
3178                         "%s: In WHERE clause, condition type is JSON_HASH", MODULENAME );
3179                 jsonIterator* search_itr = jsonNewIterator( search_hash );
3180                 if( !jsonIteratorHasNext( search_itr ) ) {
3181                         osrfLogError(
3182                                 OSRF_LOG_MARK,
3183                                 "%s: Invalid predicate structure: empty JSON object",
3184                                 MODULENAME
3185                         );
3186                         jsonIteratorFree( search_itr );
3187                         buffer_free( sql_buf );
3188                         return NULL;
3189                 }
3190
3191                 while ( (node = jsonIteratorNext( search_itr )) ) {
3192
3193                         if (first) {
3194                                 first = 0;
3195                         } else {
3196                                 if (opjoin_type == OR_OP_JOIN)
3197                                         buffer_add(sql_buf, " OR ");
3198                                 else
3199                                         buffer_add(sql_buf, " AND ");
3200                         }
3201
3202                         if ( '+' == search_itr->key[ 0 ] ) {
3203
3204                                 // This plus sign prefixes a class name or other table alias;
3205                                 // make sure the table alias is in scope
3206                                 ClassInfo* alias_info = search_all_alias( search_itr->key + 1 );
3207                                 if( ! alias_info ) {
3208                                         osrfLogError(
3209                                                          OSRF_LOG_MARK,
3210                                                         "%s: Invalid table alias \"%s\" in WHERE clause",
3211                                                         MODULENAME,
3212                                                         search_itr->key + 1
3213                                         );
3214                                         jsonIteratorFree( search_itr );
3215                                         buffer_free( sql_buf );
3216                                         return NULL;
3217                                 }
3218
3219                                 if ( node->type == JSON_STRING ) {
3220                                         // It's the name of a column; make sure it belongs to the class
3221                                         const char* fieldname = jsonObjectGetString( node );
3222                                         if( ! osrfHashGet( alias_info->fields, fieldname ) ) {
3223                                                 osrfLogError(
3224                                                         OSRF_LOG_MARK,
3225                                                         "%s: Invalid column name \"%s\" in WHERE clause "
3226                                                         "for table alias \"%s\"",
3227                                                         MODULENAME,
3228                                                         fieldname,
3229                                                         alias_info->alias
3230                                                 );
3231                                                 jsonIteratorFree( search_itr );
3232                                                 buffer_free( sql_buf );
3233                                                 return NULL;
3234                                         }
3235
3236                                         buffer_fadd(sql_buf, " \"%s\".%s ", alias_info->alias, fieldname );
3237                                 } else {
3238                                         // It's something more complicated
3239                                         char* subpred = searchWHERE( node, alias_info, AND_OP_JOIN, ctx );
3240                                         if( ! subpred ) {
3241                                                 jsonIteratorFree( search_itr );
3242                                                 buffer_free( sql_buf );
3243                                                 return NULL;
3244                                         }
3245
3246                                         buffer_fadd(sql_buf, "( %s )", subpred);
3247                                         free(subpred);
3248                                 }
3249                         } else if ( '-' == search_itr->key[ 0 ] ) {
3250                                 if ( !strcasecmp("-or",search_itr->key) ) {
3251                                         char* subpred = searchWHERE( node, class_info, OR_OP_JOIN, ctx );
3252                                         if( ! subpred ) {
3253                                                 jsonIteratorFree( search_itr );
3254                                                 buffer_free( sql_buf );
3255                                                 return NULL;
3256                                         }
3257
3258                                         buffer_fadd(sql_buf, "( %s )", subpred);
3259                                         free( subpred );
3260                                 } else if ( !strcasecmp("-and",search_itr->key) ) {
3261                                         char* subpred = searchWHERE( node, class_info, AND_OP_JOIN, ctx );
3262                                         if( ! subpred ) {
3263                                                 jsonIteratorFree( search_itr );
3264                                                 buffer_free( sql_buf );
3265                                                 return NULL;
3266                                         }
3267
3268                                         buffer_fadd(sql_buf, "( %s )", subpred);
3269                                         free( subpred );
3270                                 } else if ( !strcasecmp("-not",search_itr->key) ) {
3271                                         char* subpred = searchWHERE( node, class_info, AND_OP_JOIN, ctx );
3272                                         if( ! subpred ) {
3273                                                 jsonIteratorFree( search_itr );
3274                                                 buffer_free( sql_buf );
3275                                                 return NULL;
3276                                         }
3277
3278                                         buffer_fadd(sql_buf, " NOT ( %s )", subpred);
3279                                         free( subpred );
3280                                 } else if ( !strcasecmp("-exists",search_itr->key) ) {
3281                                         char* subpred = buildQuery( ctx, node, SUBSELECT );
3282                                         if( ! subpred ) {
3283                                                 jsonIteratorFree( search_itr );
3284                                                 buffer_free( sql_buf );
3285                                                 return NULL;
3286                                         }
3287
3288                                         buffer_fadd(sql_buf, "EXISTS ( %s )", subpred);
3289                                         free(subpred);
3290                                 } else if ( !strcasecmp("-not-exists",search_itr->key) ) {
3291                                         char* subpred = buildQuery( ctx, node, SUBSELECT );
3292                                         if( ! subpred ) {
3293                                                 jsonIteratorFree( search_itr );
3294                                                 buffer_free( sql_buf );
3295                                                 return NULL;
3296                                         }
3297
3298                                         buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred);
3299                                         free(subpred);
3300                                 } else {     // Invalid "minus" operator
3301                                         osrfLogError(
3302                                                          OSRF_LOG_MARK,
3303                                                         "%s: Invalid operator \"%s\" in WHERE clause",
3304                                                         MODULENAME,
3305                                                         search_itr->key
3306                                         );
3307                                         jsonIteratorFree( search_itr );
3308                                         buffer_free( sql_buf );
3309                                         return NULL;
3310                                 }
3311
3312                         } else {
3313
3314                                 const char* class = class_info->class_name;
3315                                 osrfHash* fields = class_info->fields;
3316                                 osrfHash* field = osrfHashGet( fields, search_itr->key );
3317
3318                                 if (!field) {
3319                                         const char* table = class_info->source_def;
3320                                         osrfLogError(
3321                                                 OSRF_LOG_MARK,
3322                                                 "%s: Attempt to reference non-existent column \"%s\" on %s (%s)",
3323                                                 MODULENAME,
3324                                                 search_itr->key,
3325                                                 table ? table : "?",
3326                                                 class ? class : "?"
3327                                         );
3328                                         jsonIteratorFree(search_itr);
3329                                         buffer_free(sql_buf);
3330                                         return NULL;
3331                                 }
3332
3333                                 char* subpred = searchPredicate( class_info, field, node, ctx );
3334                                 if( ! subpred ) {
3335                                         buffer_free(sql_buf);
3336                                         jsonIteratorFree(search_itr);
3337                                         return NULL;
3338                                 }
3339
3340                                 buffer_add( sql_buf, subpred );
3341                                 free(subpred);
3342                         }
3343                 }
3344                 jsonIteratorFree(search_itr);
3345
3346         } else {
3347                 // ERROR ... only hash and array allowed at this level
3348                 char* predicate_string = jsonObjectToJSON( search_hash );
3349                 osrfLogError(
3350                         OSRF_LOG_MARK,
3351                         "%s: Invalid predicate structure: %s",
3352                         MODULENAME,
3353                         predicate_string
3354                 );
3355                 buffer_free(sql_buf);
3356                 free(predicate_string);
3357                 return NULL;
3358         }
3359
3360         return buffer_release(sql_buf);
3361 }
3362
3363 /* Build a JSON_ARRAY of field names for a given table alias
3364 */
3365 static jsonObject* defaultSelectList( const char* table_alias ) {
3366
3367         if( ! table_alias )
3368                 table_alias = "";
3369
3370         ClassInfo* class_info = search_all_alias( table_alias );
3371         if( ! class_info ) {
3372                 osrfLogError(
3373                         OSRF_LOG_MARK,
3374                         "%s: Can't build default SELECT clause for \"%s\"; no such table alias",
3375                         MODULENAME,
3376                         table_alias
3377                 );
3378                 return NULL;
3379         }
3380
3381         jsonObject* array = jsonNewObjectType( JSON_ARRAY );
3382         osrfHash* field_def = NULL;
3383         osrfHashIterator* field_itr = osrfNewHashIterator( class_info->fields );
3384         while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
3385                 const char* field_name = osrfHashIteratorKey( field_itr );
3386                 if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
3387                         jsonObjectPush( array, jsonNewObject( field_name ) );
3388                 }
3389         }
3390         osrfHashIteratorFree( field_itr );
3391
3392         return array;
3393 }
3394
3395 // Translate a jsonObject into a UNION, INTERSECT, or EXCEPT query.
3396 // The jsonObject must be a JSON_HASH with an single entry for "union",
3397 // "intersect", or "except".  The data associated with this key must be an
3398 // array of hashes, each hash being a query.
3399 // Also allowed but currently ignored: entries for "order_by" and "alias".
3400 static char* doCombo( osrfMethodContext* ctx, jsonObject* combo, int flags ) {
3401         // Sanity check
3402         if( ! combo || combo->type != JSON_HASH )
3403                 return NULL;      // should be impossible; validated by caller
3404
3405         const jsonObject* query_array = NULL;   // array of subordinate queries
3406         const char* op = NULL;     // name of operator, e.g. UNION
3407         const char* alias = NULL;  // alias for the query (needed for ORDER BY)
3408         int op_count = 0;          // for detecting conflicting operators
3409         int excepting = 0;         // boolean
3410         int all = 0;               // boolean
3411         jsonObject* order_obj = NULL;
3412
3413         // Identify the elements in the hash
3414         jsonIterator* query_itr = jsonNewIterator( combo );
3415         jsonObject* curr_obj = NULL;
3416         while( (curr_obj = jsonIteratorNext( query_itr ) ) ) {
3417                 if( ! strcmp( "union", query_itr->key ) ) {
3418                         ++op_count;
3419                         op = " UNION ";
3420                         query_array = curr_obj;
3421                 } else if( ! strcmp( "intersect", query_itr->key ) ) {
3422                         ++op_count;
3423                         op = " INTERSECT ";
3424                         query_array = curr_obj;
3425                 } else if( ! strcmp( "except", query_itr->key ) ) {
3426                         ++op_count;
3427                         op = " EXCEPT ";
3428                         excepting = 1;
3429                         query_array = curr_obj;
3430                 } else if( ! strcmp( "order_by", query_itr->key ) ) {
3431                         osrfLogWarning(
3432                                 OSRF_LOG_MARK,
3433                                 "%s: ORDER BY not supported for UNION, INTERSECT, or EXCEPT",
3434                                 MODULENAME
3435                         );
3436                         order_obj = curr_obj;
3437                 } else if( ! strcmp( "alias", query_itr->key ) ) {
3438                         if( curr_obj->type != JSON_STRING ) {
3439                                 jsonIteratorFree( query_itr );
3440                                 return NULL;
3441                         }
3442                         alias = jsonObjectGetString( curr_obj );
3443                 } else if( ! strcmp( "all", query_itr->key ) ) {
3444                         if( obj_is_true( curr_obj ) )
3445                                 all = 1;
3446                 } else {
3447                         if( ctx )
3448                                 osrfAppSessionStatus(
3449                                         ctx->session,
3450                                         OSRF_STATUS_INTERNALSERVERERROR,
3451                                         "osrfMethodException",
3452                                         ctx->request,
3453                                         "Malformed query; unexpected entry in query object"
3454                                 );
3455                         osrfLogError(
3456                                 OSRF_LOG_MARK,
3457                                 "%s: Unexpected entry for \"%s\" in%squery",
3458                                 MODULENAME,
3459                                 query_itr->key,
3460                                 op
3461                         );
3462                         jsonIteratorFree( query_itr );
3463                         return NULL;
3464                 }
3465         }
3466         jsonIteratorFree( query_itr );
3467
3468         // More sanity checks
3469         if( ! query_array ) {
3470                 if( ctx )
3471                         osrfAppSessionStatus(
3472                                 ctx->session,
3473                                 OSRF_STATUS_INTERNALSERVERERROR,
3474                                 "osrfMethodException",
3475                                 ctx->request,
3476                                 "Expected UNION, INTERSECT, or EXCEPT operator not found"
3477                         );
3478                 osrfLogError(
3479                         OSRF_LOG_MARK,
3480                         "%s: Expected UNION, INTERSECT, or EXCEPT operator not found",
3481                         MODULENAME
3482                 );
3483                 return NULL;        // should be impossible...
3484         } else if( op_count > 1 ) {
3485                 if( ctx )
3486                                 osrfAppSessionStatus(
3487                                 ctx->session,
3488                                 OSRF_STATUS_INTERNALSERVERERROR,
3489                                 "osrfMethodException",
3490                                 ctx->request,
3491                                 "Found more than one of UNION, INTERSECT, and EXCEPT in same query"
3492                         );
3493                 osrfLogError(
3494                         OSRF_LOG_MARK,
3495                         "%s: Found more than one of UNION, INTERSECT, and EXCEPT in same query",
3496                         MODULENAME
3497                 );
3498                 return NULL;
3499         } if( query_array->type != JSON_ARRAY ) {
3500                 if( ctx )
3501                                 osrfAppSessionStatus(
3502                                 ctx->session,
3503                                 OSRF_STATUS_INTERNALSERVERERROR,
3504                                 "osrfMethodException",
3505                                 ctx->request,
3506                                 "Malformed query: expected array of queries under UNION, INTERSECT or EXCEPT"
3507                         );
3508                 osrfLogError(
3509                         OSRF_LOG_MARK,
3510                         "%s: Expected JSON_ARRAY of queries for%soperator; found %s",
3511                         MODULENAME,
3512                         op,
3513                         json_type( query_array->type )
3514                 );
3515                 return NULL;
3516         } if( query_array->size < 2 ) {
3517                 if( ctx )
3518                         osrfAppSessionStatus(
3519                                 ctx->session,
3520                                 OSRF_STATUS_INTERNALSERVERERROR,
3521                                 "osrfMethodException",
3522                                 ctx->request,
3523                                 "UNION, INTERSECT or EXCEPT requires multiple queries as operands"
3524                         );
3525                 osrfLogError(
3526                         OSRF_LOG_MARK,
3527                         "%s:%srequires multiple queries as operands",
3528                         MODULENAME,
3529                         op
3530                 );
3531                 return NULL;
3532         } else if( excepting && query_array->size > 2 ) {
3533                 if( ctx )
3534                         osrfAppSessionStatus(
3535                                 ctx->session,
3536                                 OSRF_STATUS_INTERNALSERVERERROR,
3537                                 "osrfMethodException",
3538                                 ctx->request,
3539                                 "EXCEPT operator has too many queries as operands"
3540                         );
3541                 osrfLogError(
3542                         OSRF_LOG_MARK,
3543                         "%s:EXCEPT operator has too many queries as operands",
3544                         MODULENAME
3545                 );
3546                 return NULL;
3547         } else if( order_obj && ! alias ) {
3548                 if( ctx )
3549                         osrfAppSessionStatus(
3550                                 ctx->session,
3551                                 OSRF_STATUS_INTERNALSERVERERROR,
3552                                 "osrfMethodException",
3553                                 ctx->request,
3554                                 "ORDER BY requires an alias for a UNION, INTERSECT, or EXCEPT"
3555                         );
3556                 osrfLogError(
3557                         OSRF_LOG_MARK,
3558                         "%s:ORDER BY requires an alias for a UNION, INTERSECT, or EXCEPT",
3559                         MODULENAME
3560                 );
3561                 return NULL;
3562         }
3563
3564         // So far so good.  Now build the SQL.
3565         growing_buffer* sql = buffer_init( 256 );
3566
3567         // If we nested inside another UNION, INTERSECT, or EXCEPT,
3568         // Add a layer of parentheses
3569         if( flags & SUBCOMBO )
3570                 OSRF_BUFFER_ADD( sql, "( " );
3571
3572         // Traverse the query array.  Each entry should be a hash.
3573         int first = 1;   // boolean
3574         int i = 0;
3575         jsonObject* query = NULL;
3576         while((query = jsonObjectGetIndex( query_array, i++ ) )) {
3577                 if( query->type != JSON_HASH ) {
3578                         if( ctx )
3579                                 osrfAppSessionStatus(
3580                                         ctx->session,
3581                                         OSRF_STATUS_INTERNALSERVERERROR,
3582                                         "osrfMethodException",
3583                                         ctx->request,
3584                                         "Malformed query under UNION, INTERSECT or EXCEPT"
3585                                 );
3586                         osrfLogError(
3587                                 OSRF_LOG_MARK,
3588                                 "%s: Malformed query under%s -- expected JSON_HASH, found %s",
3589                                 MODULENAME,
3590                                 op,
3591                                 json_type( query->type )
3592                         );
3593                         buffer_free( sql );
3594                         return NULL;
3595                 }
3596
3597                 if( first )
3598                         first = 0;
3599                 else {
3600                         OSRF_BUFFER_ADD( sql, op );
3601                         if( all )
3602                                 OSRF_BUFFER_ADD( sql, "ALL " );
3603                 }
3604
3605                 char* query_str = buildQuery( ctx, query, SUBSELECT | SUBCOMBO );
3606                 if( ! query_str ) {
3607                         osrfLogError(
3608                                 OSRF_LOG_MARK,
3609                                 "%s: Error building query under%s",
3610                                 MODULENAME,
3611                                 op
3612                         );
3613                         buffer_free( sql );
3614                         return NULL;
3615                 }
3616
3617                 OSRF_BUFFER_ADD( sql, query_str );
3618         }
3619
3620         if( flags & SUBCOMBO )
3621                 OSRF_BUFFER_ADD_CHAR( sql, ')' );
3622
3623         if ( !(flags & SUBSELECT) )
3624                 OSRF_BUFFER_ADD_CHAR( sql, ';' );
3625
3626         return buffer_release( sql );
3627 }
3628
3629 // Translate a jsonObject into a SELECT, UNION, INTERSECT, or EXCEPT query.
3630 // The jsonObject must be a JSON_HASH with an entry for "from", "union", "intersect",
3631 // or "except" to indicate the type of query.
3632 char* buildQuery( osrfMethodContext* ctx, jsonObject* query, int flags ) {
3633         // Sanity checks
3634         if( ! query ) {
3635                 if( ctx )
3636                         osrfAppSessionStatus(
3637                                 ctx->session,
3638                                 OSRF_STATUS_INTERNALSERVERERROR,
3639                                 "osrfMethodException",
3640                                 ctx->request,
3641                                 "Malformed query; no query object"
3642                         );
3643                 osrfLogError( OSRF_LOG_MARK, "%s: Null pointer to query object", MODULENAME );
3644                 return NULL;
3645         } else if( query->type != JSON_HASH ) {
3646                 if( ctx )
3647                         osrfAppSessionStatus(
3648                                 ctx->session,
3649                                 OSRF_STATUS_INTERNALSERVERERROR,
3650                                 "osrfMethodException",
3651                                 ctx->request,
3652                                 "Malformed query object"
3653                         );
3654                 osrfLogError(
3655                         OSRF_LOG_MARK,
3656                         "%s: Query object is %s instead of JSON_HASH",
3657                         MODULENAME,
3658                         json_type( query->type )
3659                 );
3660                 return NULL;
3661         }
3662
3663         // Determine what kind of query it purports to be, and dispatch accordingly.
3664         if( jsonObjectGetKey( query, "union" ) ||
3665                 jsonObjectGetKey( query, "intersect" ) ||
3666                 jsonObjectGetKey( query, "except" ) ) {
3667                 return doCombo( ctx, query, flags );
3668         } else {
3669                 // It is presumably a SELECT query
3670
3671                 // Push a node onto the stack for the current query.  Every level of
3672                 // subquery gets its own QueryFrame on the Stack.
3673                 push_query_frame();
3674
3675                 // Build an SQL SELECT statement
3676                 char* sql = SELECT(
3677                         ctx,
3678                         jsonObjectGetKey( query, "select" ),
3679                         jsonObjectGetKey( query, "from" ),
3680                         jsonObjectGetKey( query, "where" ),
3681                         jsonObjectGetKey( query, "having" ),
3682                         jsonObjectGetKey( query, "order_by" ),
3683                         jsonObjectGetKey( query, "limit" ),
3684                         jsonObjectGetKey( query, "offset" ),
3685                         flags
3686                 );
3687                 pop_query_frame();
3688                 return sql;
3689         }
3690 }
3691
3692 char* SELECT (
3693                 /* method context */ osrfMethodContext* ctx,
3694
3695                 /* SELECT   */ jsonObject* selhash,
3696                 /* FROM     */ jsonObject* join_hash,
3697                 /* WHERE    */ jsonObject* search_hash,
3698                 /* HAVING   */ jsonObject* having_hash,
3699                 /* ORDER BY */ jsonObject* order_hash,
3700                 /* LIMIT    */ jsonObject* limit,
3701                 /* OFFSET   */ jsonObject* offset,
3702                 /* flags    */ int flags
3703 ) {
3704         const char* locale = osrf_message_get_last_locale();
3705
3706         // general tmp objects
3707         const jsonObject* tmp_const;
3708         jsonObject* selclass = NULL;
3709         jsonObject* snode = NULL;
3710         jsonObject* onode = NULL;
3711
3712         char* string = NULL;
3713         int from_function = 0;
3714         int first = 1;
3715         int gfirst = 1;
3716         //int hfirst = 1;
3717
3718         osrfLogDebug(OSRF_LOG_MARK, "cstore SELECT locale: %s", locale ? locale : "(none)" );
3719
3720         // punt if there's no FROM clause
3721         if (!join_hash || ( join_hash->type == JSON_HASH && !join_hash->size )) {
3722                 osrfLogError(
3723                         OSRF_LOG_MARK,
3724                         "%s: FROM clause is missing or empty",
3725                         MODULENAME
3726                 );
3727                 if( ctx )
3728                         osrfAppSessionStatus(
3729                                 ctx->session,
3730                                 OSRF_STATUS_INTERNALSERVERERROR,
3731                                 "osrfMethodException",
3732                                 ctx->request,
3733                                 "FROM clause is missing or empty in JSON query"
3734                         );
3735                 return NULL;
3736         }
3737
3738         // the core search class
3739         const char* core_class = NULL;
3740
3741         // get the core class -- the only key of the top level FROM clause, or a string
3742         if (join_hash->type == JSON_HASH) {
3743                 jsonIterator* tmp_itr = jsonNewIterator( join_hash );
3744                 snode = jsonIteratorNext( tmp_itr );
3745
3746                 // Populate the current QueryFrame with information
3747                 // about the core class
3748                 if( add_query_core( NULL, tmp_itr->key ) ) {
3749                         if( ctx )
3750                                 osrfAppSessionStatus(
3751                                         ctx->session,
3752                                         OSRF_STATUS_INTERNALSERVERERROR,
3753                                         "osrfMethodException",
3754                                         ctx->request,
3755                                         "Unable to look up core class"
3756                                 );
3757                         return NULL;
3758                 }
3759                 core_class = curr_query->core.class_name;
3760                 join_hash = snode;
3761
3762                 jsonObject* extra = jsonIteratorNext( tmp_itr );
3763
3764                 jsonIteratorFree( tmp_itr );
3765                 snode = NULL;
3766
3767                 // There shouldn't be more than one entry in join_hash
3768                 if( extra ) {
3769                         osrfLogError(
3770                                 OSRF_LOG_MARK,
3771                                 "%s: Malformed FROM clause: extra entry in JSON_HASH",
3772                                 MODULENAME
3773                         );
3774                         if( ctx )
3775                                 osrfAppSessionStatus(
3776                                         ctx->session,
3777                                         OSRF_STATUS_INTERNALSERVERERROR,
3778                                         "osrfMethodException",
3779                                         ctx->request,
3780                                         "Malformed FROM clause in JSON query"
3781                                 );
3782                         return NULL;    // Malformed join_hash; extra entry
3783                 }
3784         } else if (join_hash->type == JSON_ARRAY) {
3785                 // We're selecting from a function, not from a table
3786                 from_function = 1;
3787                 core_class = jsonObjectGetString( jsonObjectGetIndex(join_hash, 0) );
3788                 selhash = NULL;
3789
3790         } else if (join_hash->type == JSON_STRING) {
3791                 // Populate the current QueryFrame with information
3792                 // about the core class
3793                 core_class = jsonObjectGetString( join_hash );
3794                 join_hash = NULL;
3795                 if( add_query_core( NULL, core_class ) ) {
3796                         if( ctx )
3797                                 osrfAppSessionStatus(
3798                                         ctx->session,
3799                                         OSRF_STATUS_INTERNALSERVERERROR,
3800                                         "osrfMethodException",
3801                                         ctx->request,
3802                                         "Unable to look up core class"
3803                                 );
3804                         return NULL;
3805                 }
3806         }
3807         else {
3808                 osrfLogError(
3809                         OSRF_LOG_MARK,
3810                         "%s: FROM clause is unexpected JSON type: %s",
3811                         MODULENAME,
3812                         json_type( join_hash->type )
3813                 );
3814                 if( ctx )
3815                         osrfAppSessionStatus(
3816                                 ctx->session,
3817                                 OSRF_STATUS_INTERNALSERVERERROR,
3818                                 "osrfMethodException",
3819                                 ctx->request,
3820                                 "Ill-formed FROM clause in JSON query"
3821                         );
3822                 return NULL;
3823         }
3824
3825         // Build the join clause, if any, while filling out the list
3826         // of joined classes in the current QueryFrame.
3827         char* join_clause = NULL;
3828         if( join_hash && ! from_function ) {
3829
3830                 join_clause = searchJOIN( join_hash, &curr_query->core );
3831                 if( ! join_clause ) {
3832                         if (ctx)
3833                                 osrfAppSessionStatus(
3834                                         ctx->session,
3835                                         OSRF_STATUS_INTERNALSERVERERROR,
3836                                         "osrfMethodException",
3837                                         ctx->request,
3838                                         "Unable to construct JOIN clause(s)"
3839                                 );
3840                         return NULL;
3841                 }
3842         }
3843
3844         // For in case we don't get a select list
3845         jsonObject* defaultselhash = NULL;
3846
3847         // if there is no select list, build a default select list ...
3848         if (!selhash && !from_function) {
3849                 jsonObject* default_list = defaultSelectList( core_class );
3850                 if( ! default_list ) {
3851                         if (ctx) {
3852                                 osrfAppSessionStatus(
3853                                         ctx->session,
3854                                         OSRF_STATUS_INTERNALSERVERERROR,
3855                                         "osrfMethodException",
3856                                         ctx->request,
3857                                         "Unable to build default SELECT clause in JSON query"
3858                                 );
3859                                 free( join_clause );
3860                                 return NULL;
3861                         }
3862                 }
3863
3864                 selhash = defaultselhash = jsonNewObjectType(JSON_HASH);
3865                 jsonObjectSetKey( selhash, core_class, default_list );
3866         }
3867
3868         // The SELECT clause can be encoded only by a hash
3869         if( !from_function && selhash->type != JSON_HASH ) {
3870                 osrfLogError(
3871                         OSRF_LOG_MARK,
3872                         "%s: Expected JSON_HASH for SELECT clause; found %s",
3873                         MODULENAME,
3874                         json_type( selhash->type )
3875                 );
3876
3877                 if (ctx)
3878                         osrfAppSessionStatus(
3879                                 ctx->session,
3880                                 OSRF_STATUS_INTERNALSERVERERROR,
3881                                 "osrfMethodException",
3882                                 ctx->request,
3883                                 "Malformed SELECT clause in JSON query"
3884                         );
3885                 free( join_clause );
3886                 return NULL;
3887         }
3888
3889         // If you see a null or wild card specifier for the core class, or an
3890         // empty array, replace it with a default SELECT list
3891         tmp_const = jsonObjectGetKeyConst( selhash, core_class );
3892         if ( tmp_const ) {
3893                 int default_needed = 0;   // boolean
3894                 if( JSON_STRING == tmp_const->type
3895                         && !strcmp( "*", jsonObjectGetString( tmp_const ) ))
3896                                 default_needed = 1;
3897                 else if( JSON_NULL == tmp_const->type )
3898                         default_needed = 1;
3899
3900                 if( default_needed ) {
3901                         // Build a default SELECT list
3902                         jsonObject* default_list = defaultSelectList( core_class );
3903                         if( ! default_list ) {
3904                                 if (ctx) {
3905                                         osrfAppSessionStatus(
3906                                                 ctx->session,
3907                                                 OSRF_STATUS_INTERNALSERVERERROR,
3908                                                 "osrfMethodException",
3909                                                 ctx->request,
3910                                                 "Can't build default SELECT clause in JSON query"
3911                                         );
3912                                         free( join_clause );
3913                                         return NULL;
3914                                 }
3915                         }
3916
3917                         jsonObjectSetKey( selhash, core_class, default_list );
3918                 }
3919         }
3920
3921         // temp buffers for the SELECT list and GROUP BY clause
3922         growing_buffer* select_buf = buffer_init(128);
3923         growing_buffer* group_buf = buffer_init(128);
3924
3925         int aggregate_found = 0;     // boolean
3926
3927         // Build a select list
3928         if(from_function)   // From a function we select everything
3929                 OSRF_BUFFER_ADD_CHAR( select_buf, '*' );
3930         else {
3931
3932                 // Build the SELECT list as SQL
3933             int sel_pos = 1;
3934             first = 1;
3935             gfirst = 1;
3936             jsonIterator* selclass_itr = jsonNewIterator( selhash );
3937             while ( (selclass = jsonIteratorNext( selclass_itr )) ) {    // For each class
3938
3939                         const char* cname = selclass_itr->key;
3940
3941                         // Make sure the target relation is in the FROM clause.
3942
3943                         // At this point join_hash is a step down from the join_hash we
3944                         // received as a parameter.  If the original was a JSON_STRING,
3945                         // then json_hash is now NULL.  If the original was a JSON_HASH,
3946                         // then json_hash is now the first (and only) entry in it,
3947                         // denoting the core class.  We've already excluded the
3948                         // possibility that the original was a JSON_ARRAY, because in
3949                         // that case from_function would be non-NULL, and we wouldn't
3950                         // be here.
3951
3952                         // If the current table alias isn't in scope, bail out
3953                         ClassInfo* class_info = search_alias( cname );
3954                         if( ! class_info ) {
3955                                 osrfLogError(
3956                                         OSRF_LOG_MARK,
3957                                         "%s: SELECT clause references class not in FROM clause: \"%s\"",
3958                                         MODULENAME,
3959                                         cname
3960                                 );
3961                                 if( ctx )
3962                                         osrfAppSessionStatus(
3963                                                 ctx->session,
3964                                                 OSRF_STATUS_INTERNALSERVERERROR,
3965                                                 "osrfMethodException",
3966                                                 ctx->request,
3967                                                 "Selected class not in FROM clause in JSON query"
3968                                         );
3969                                 jsonIteratorFree( selclass_itr );
3970                                 buffer_free( select_buf );
3971                                 buffer_free( group_buf );
3972                                 if( defaultselhash )
3973                                         jsonObjectFree( defaultselhash );
3974                                 free( join_clause );
3975                                 return NULL;
3976                         }
3977
3978                         if( selclass->type != JSON_ARRAY ) {
3979                                 osrfLogError(
3980                                         OSRF_LOG_MARK,
3981                                         "%s: Malformed SELECT list for class \"%s\"; not an array",
3982                                         MODULENAME,
3983                                         cname
3984                                 );
3985                                 if( ctx )
3986                                         osrfAppSessionStatus(
3987                                                 ctx->session,
3988                                                 OSRF_STATUS_INTERNALSERVERERROR,
3989                                                 "osrfMethodException",
3990                                                 ctx->request,
3991                                                 "Selected class not in FROM clause in JSON query"
3992                                         );
3993
3994                                 jsonIteratorFree( selclass_itr );
3995                                 buffer_free( select_buf );
3996                                 buffer_free( group_buf );
3997                                 if( defaultselhash )
3998                                         jsonObjectFree( defaultselhash );
3999                                 free( join_clause );
4000                                 return NULL;
4001                         }
4002
4003                         // Look up some attributes of the current class
4004                         osrfHash* idlClass = class_info->class_def;
4005                         osrfHash* class_field_set = class_info->fields;
4006                         const char* class_pkey = osrfHashGet( idlClass, "primarykey" );
4007                         const char* class_tname = osrfHashGet( idlClass, "tablename" );
4008
4009                         if( 0 == selclass->size ) {
4010                                 osrfLogWarning(
4011                                         OSRF_LOG_MARK,
4012                                         "%s: No columns selected from \"%s\"",
4013                                         MODULENAME,
4014                                         cname
4015                                 );
4016                         }
4017
4018                         // stitch together the column list for the current table alias...
4019                         unsigned long field_idx = 0;
4020                         jsonObject* selfield = NULL;
4021                         while((selfield = jsonObjectGetIndex( selclass, field_idx++ ) )) {
4022
4023                                 // If we need a separator comma, add one
4024                                 if (first) {
4025                                         first = 0;
4026                                 } else {
4027                                         OSRF_BUFFER_ADD_CHAR( select_buf, ',' );
4028                                 }
4029
4030                                 // if the field specification is a string, add it to the list
4031                                 if (selfield->type == JSON_STRING) {
4032
4033                                         // Look up the field in the IDL
4034                                         const char* col_name = jsonObjectGetString( selfield );
4035                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
4036                                         if ( !field_def ) {
4037                                                 // No such field in current class
4038                                                 osrfLogError(
4039                                                         OSRF_LOG_MARK,
4040                                                         "%s: Selected column \"%s\" not defined in IDL for class \"%s\"",
4041                                                         MODULENAME,
4042                                                         col_name,
4043                                                         cname
4044                                                 );
4045                                                 if( ctx )
4046                                                         osrfAppSessionStatus(
4047                                                                 ctx->session,
4048                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4049                                                                 "osrfMethodException",
4050                                                                 ctx->request,
4051                                                                 "Selected column not defined in JSON query"
4052                                                         );
4053                                                 jsonIteratorFree( selclass_itr );
4054                                                 buffer_free( select_buf );
4055                                                 buffer_free( group_buf );
4056                                                 if( defaultselhash )
4057                                                         jsonObjectFree( defaultselhash );
4058                                                 free( join_clause );
4059                                                 return NULL;
4060                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4061                                                 // Virtual field not allowed
4062                                                 osrfLogError(
4063                                                         OSRF_LOG_MARK,
4064                                                         "%s: Selected column \"%s\" for class \"%s\" is virtual",
4065                                                         MODULENAME,
4066                                                         col_name,
4067                                                         cname
4068                                                 );
4069                                                 if( ctx )
4070                                                         osrfAppSessionStatus(
4071                                                                 ctx->session,
4072                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4073                                                                 "osrfMethodException",
4074                                                                 ctx->request,
4075                                                                 "Selected column may not be virtual in JSON query"
4076                                                         );
4077                                                 jsonIteratorFree( selclass_itr );
4078                                                 buffer_free( select_buf );
4079                                                 buffer_free( group_buf );
4080                                                 if( defaultselhash )
4081                                                         jsonObjectFree( defaultselhash );
4082                                                 free( join_clause );
4083                                                 return NULL;
4084                                         }
4085
4086                                         if (locale) {
4087                                                 const char* i18n;
4088                                                 if (flags & DISABLE_I18N)
4089                                                         i18n = NULL;
4090                                                 else
4091                                                         i18n = osrfHashGet(field_def, "i18n");
4092
4093                                                 if( str_is_true( i18n ) ) {
4094                                                         buffer_fadd( select_buf, " oils_i18n_xlate('%s', '%s', '%s', "
4095                                                                 "'%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
4096                                                                 class_tname, cname, col_name, class_pkey,
4097                                                                 cname, class_pkey, locale, col_name );
4098                                                 } else {
4099                                                         buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"",
4100                                                                 cname, col_name, col_name );
4101                                                 }
4102                                         } else {
4103                                                 buffer_fadd(select_buf, " \"%s\".%s AS \"%s\"",
4104                                                                 cname, col_name, col_name );
4105                                         }
4106
4107                                 // ... but it could be an object, in which case we check for a Field Transform
4108                                 } else if (selfield->type == JSON_HASH) {
4109
4110                                         const char* col_name = jsonObjectGetString(
4111                                                         jsonObjectGetKeyConst( selfield, "column" ) );
4112
4113                                         // Get the field definition from the IDL
4114                                         osrfHash* field_def = osrfHashGet( class_field_set, col_name );
4115                                         if ( !field_def ) {
4116                                                 // No such field in current class
4117                                                 osrfLogError(
4118                                                         OSRF_LOG_MARK,
4119                                                         "%s: Selected column \"%s\" is not defined in IDL for class \"%s\"",
4120                                                         MODULENAME,
4121                                                         col_name,
4122                                                         cname
4123                                                 );
4124                                                 if( ctx )
4125                                                         osrfAppSessionStatus(
4126                                                                 ctx->session,
4127                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4128                                                                 "osrfMethodException",
4129                                                                 ctx->request,
4130                                                                 "Selected column is not defined in JSON query"
4131                                                         );
4132                                                 jsonIteratorFree( selclass_itr );
4133                                                 buffer_free( select_buf );
4134                                                 buffer_free( group_buf );
4135                                                 if( defaultselhash )
4136                                                         jsonObjectFree( defaultselhash );
4137                                                 free( join_clause );
4138                                                 return NULL;
4139                                         } else if ( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4140                                                 // No such field in current class
4141                                                 osrfLogError(
4142                                                         OSRF_LOG_MARK,
4143                                                         "%s: Selected column \"%s\" is virtual for class \"%s\"",
4144                                                         MODULENAME,
4145                                                         col_name,
4146                                                         cname
4147                                                 );
4148                                                 if( ctx )
4149                                                         osrfAppSessionStatus(
4150                                                                 ctx->session,
4151                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4152                                                                 "osrfMethodException",
4153                                                                 ctx->request,
4154                                                                 "Selected column is virtual in JSON query"
4155                                                         );
4156                                                 jsonIteratorFree( selclass_itr );
4157                                                 buffer_free( select_buf );
4158                                                 buffer_free( group_buf );
4159                                                 if( defaultselhash )
4160                                                         jsonObjectFree( defaultselhash );
4161                                                 free( join_clause );
4162                                                 return NULL;
4163                                         }
4164
4165                                         // Decide what to use as a column alias
4166                                         const char* _alias;
4167                                         if ((tmp_const = jsonObjectGetKeyConst( selfield, "alias" ))) {
4168                                                 _alias = jsonObjectGetString( tmp_const );
4169                                         } else {         // Use field name as the alias
4170                                                 _alias = col_name;
4171                                         }
4172
4173                                         if (jsonObjectGetKeyConst( selfield, "transform" )) {
4174                                                 char* transform_str = searchFieldTransform(
4175                                                         class_info->alias, field_def, selfield );
4176                                                 if( transform_str ) {
4177                                                         buffer_fadd(select_buf, " %s AS \"%s\"", transform_str, _alias);
4178                                                         free(transform_str);
4179                                                 } else {
4180                                                         if( ctx )
4181                                                                 osrfAppSessionStatus(
4182                                                                         ctx->session,
4183                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4184                                                                         "osrfMethodException",
4185                                                                         ctx->request,
4186                                                                         "Unable to generate transform function in JSON query"
4187                                                                 );
4188                                                         jsonIteratorFree( selclass_itr );
4189                                                         buffer_free( select_buf );
4190                                                         buffer_free( group_buf );
4191                                                         if( defaultselhash )
4192                                                                 jsonObjectFree( defaultselhash );
4193                                                         free( join_clause );
4194                                                         return NULL;
4195                                                 }
4196                                         } else {
4197
4198                                                 if (locale) {
4199                                                         const char* i18n;
4200                                                         if (flags & DISABLE_I18N)
4201                                                                 i18n = NULL;
4202                                                         else
4203                                                                 i18n = osrfHashGet(field_def, "i18n");
4204
4205                                                         if( str_is_true( i18n ) ) {
4206                                                                 buffer_fadd( select_buf,
4207                                                                         " oils_i18n_xlate('%s', '%s', '%s', '%s', "
4208                                                                         "\"%s\".%s::TEXT, '%s') AS \"%s\"",
4209                                                                         class_tname, cname, col_name, class_pkey, cname,
4210                                                                         class_pkey, locale, _alias);
4211                                                         } else {
4212                                                                 buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4213                                                                         cname, col_name, _alias );
4214                                                         }
4215                                                 } else {
4216                                                         buffer_fadd( select_buf, " \"%s\".%s AS \"%s\"",
4217                                                                 cname, col_name, _alias);
4218                                                 }
4219                                         }
4220                                 }
4221                                 else {
4222                                         osrfLogError(
4223                                                 OSRF_LOG_MARK,
4224                                                 "%s: Selected item is unexpected JSON type: %s",
4225                                                 MODULENAME,
4226                                                 json_type( selfield->type )
4227                                         );
4228                                         if( ctx )
4229                                                 osrfAppSessionStatus(
4230                                                         ctx->session,
4231                                                         OSRF_STATUS_INTERNALSERVERERROR,
4232                                                         "osrfMethodException",
4233                                                         ctx->request,
4234                                                         "Ill-formed SELECT item in JSON query"
4235                                                 );
4236                                         jsonIteratorFree( selclass_itr );
4237                                         buffer_free( select_buf );
4238                                         buffer_free( group_buf );
4239                                         if( defaultselhash )
4240                                                 jsonObjectFree( defaultselhash );
4241                                         free( join_clause );
4242                                         return NULL;
4243                                 }
4244
4245                                 const jsonObject* agg_obj = jsonObjectGetKey( selfield, "aggregate" );
4246                                 if( obj_is_true( agg_obj ) )
4247                                         aggregate_found = 1;
4248                                 else {
4249                                         // Append a comma (except for the first one)
4250                                         // and add the column to a GROUP BY clause
4251                                         if (gfirst)
4252                                                 gfirst = 0;
4253                                         else
4254                                                 OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4255
4256                                         buffer_fadd(group_buf, " %d", sel_pos);
4257                                 }
4258
4259 #if 0
4260                             if (is_agg->size || (flags & SELECT_DISTINCT)) {
4261
4262                                         const jsonObject* aggregate_obj = jsonObjectGetKey( selfield, "aggregate" );
4263                                     if ( ! obj_is_true( aggregate_obj ) ) {
4264                                             if (gfirst) {
4265                                                     gfirst = 0;
4266                                             } else {
4267                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4268                                             }
4269
4270                                             buffer_fadd(group_buf, " %d", sel_pos);
4271
4272                                         /*
4273                                     } else if (is_agg = jsonObjectGetKey( selfield, "having" )) {
4274                                             if (gfirst) {
4275                                                     gfirst = 0;
4276                                             } else {
4277                                                         OSRF_BUFFER_ADD_CHAR( group_buf, ',' );
4278                                             }
4279
4280                                             _column = searchFieldTransform(class_info->alias, field, selfield);
4281                                                 OSRF_BUFFER_ADD_CHAR(group_buf, ' ');
4282                                                 OSRF_BUFFER_ADD(group_buf, _column);
4283                                             _column = searchFieldTransform(class_info->alias, field, selfield);
4284                                         */
4285                                     }
4286                             }
4287 #endif
4288
4289                                 sel_pos++;
4290                         } // end while -- iterating across SELECT columns
4291
4292                 } // end while -- iterating across classes
4293
4294                 jsonIteratorFree(selclass_itr);
4295         }
4296
4297
4298         char* col_list = buffer_release(select_buf);
4299
4300         // Make sure the SELECT list isn't empty.  This can happen, for example,
4301         // if we try to build a default SELECT clause from a non-core table.
4302
4303         if( ! *col_list ) {
4304                 osrfLogError(OSRF_LOG_MARK, "%s: SELECT clause is empty", MODULENAME );
4305                 if (ctx)
4306                         osrfAppSessionStatus(
4307                                 ctx->session,
4308                                 OSRF_STATUS_INTERNALSERVERERROR,
4309                                 "osrfMethodException",
4310                                 ctx->request,
4311                                 "SELECT list is empty"
4312                 );
4313                 free( col_list );
4314                 buffer_free( group_buf );
4315                 if( defaultselhash )
4316                         jsonObjectFree( defaultselhash );
4317                 free( join_clause );
4318                 return NULL;
4319         }
4320
4321         char* table = NULL;
4322         if (from_function) table = searchValueTransform(join_hash);
4323         else table = strdup( curr_query->core.source_def );
4324
4325         if( !table ) {
4326                 if (ctx)
4327                         osrfAppSessionStatus(
4328                                 ctx->session,
4329                                 OSRF_STATUS_INTERNALSERVERERROR,
4330                                 "osrfMethodException",
4331                                 ctx->request,
4332                                 "Unable to identify table for core class"
4333                         );
4334                 free( col_list );
4335                 buffer_free( group_buf );
4336                 if( defaultselhash )
4337                         jsonObjectFree( defaultselhash );
4338                 free( join_clause );
4339                 return NULL;
4340         }
4341
4342         // Put it all together
4343         growing_buffer* sql_buf = buffer_init(128);
4344         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\" ", col_list, table, core_class );
4345         free(col_list);
4346         free(table);
4347
4348         // Append the join clause, if any
4349         if( join_clause ) {
4350                 buffer_add(sql_buf, join_clause);
4351                 free(join_clause);
4352         }
4353
4354         char* order_by_list = NULL;
4355         char* having_buf = NULL;
4356
4357         if (!from_function) {
4358
4359                 // Build a WHERE clause, if there is one
4360                 if ( search_hash ) {
4361                         buffer_add(sql_buf, " WHERE ");
4362
4363                         // and it's on the WHERE clause
4364                         char* pred = searchWHERE( search_hash, &curr_query->core, AND_OP_JOIN, ctx );
4365                         if ( ! pred ) {
4366                                 if (ctx) {
4367                                         osrfAppSessionStatus(
4368                                                 ctx->session,
4369                                                 OSRF_STATUS_INTERNALSERVERERROR,
4370                                                 "osrfMethodException",
4371                                                 ctx->request,
4372                                                 "Severe query error in WHERE predicate -- see error log for more details"
4373                                         );
4374                                 }
4375                                 buffer_free(group_buf);
4376                                 buffer_free(sql_buf);
4377                                 if (defaultselhash)
4378                                         jsonObjectFree(defaultselhash);
4379                                 return NULL;
4380                         }
4381
4382                         buffer_add(sql_buf, pred);
4383                         free(pred);
4384                 }
4385
4386                 // Build a HAVING clause, if there is one
4387                 if ( having_hash ) {
4388
4389                         // and it's on the the WHERE clause
4390                         having_buf = searchWHERE( having_hash, &curr_query->core, AND_OP_JOIN, ctx );
4391
4392                         if( ! having_buf ) {
4393                                 if (ctx) {
4394                                                 osrfAppSessionStatus(
4395                                                 ctx->session,
4396                                                 OSRF_STATUS_INTERNALSERVERERROR,
4397                                                 "osrfMethodException",
4398                                                 ctx->request,
4399                                                 "Severe query error in HAVING predicate -- see error log for more details"
4400                                         );
4401                                 }
4402                                 buffer_free(group_buf);
4403                                 buffer_free(sql_buf);
4404                                 if (defaultselhash)
4405                                         jsonObjectFree(defaultselhash);
4406                                 return NULL;
4407                         }
4408                 }
4409
4410                 growing_buffer* order_buf = NULL;  // to collect ORDER BY list
4411
4412                 // Build an ORDER BY clause, if there is one
4413                 if( NULL == order_hash )
4414                         ;  // No ORDER BY? do nothing
4415                 else if( JSON_ARRAY == order_hash->type ) {
4416                         // Array of field specifications, each specification being a
4417                         // hash to define the class, field, and other details
4418                         int order_idx = 0;
4419                         jsonObject* order_spec;
4420                         while( (order_spec = jsonObjectGetIndex( order_hash, order_idx++ ) ) ) {
4421
4422                                 if( JSON_HASH != order_spec->type ) {
4423                                         osrfLogError(OSRF_LOG_MARK,
4424                                                  "%s: Malformed field specification in ORDER BY clause; expected JSON_HASH, found %s",
4425                                                 MODULENAME, json_type( order_spec->type ) );
4426                                         if( ctx )
4427                                                 osrfAppSessionStatus(
4428                                                          ctx->session,
4429                                                         OSRF_STATUS_INTERNALSERVERERROR,
4430                                                         "osrfMethodException",
4431                                                         ctx->request,
4432                                                         "Malformed ORDER BY clause -- see error log for more details"
4433                                                 );
4434                                         buffer_free( order_buf );
4435                                         free(having_buf);
4436                                         buffer_free(group_buf);
4437                                         buffer_free(sql_buf);
4438                                         if (defaultselhash)
4439                                                 jsonObjectFree(defaultselhash);
4440                                         return NULL;
4441                                 }
4442
4443                                 const char* class_alias =
4444                                                 jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "class" ) );
4445                                 const char* field =
4446                                                 jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "field" ) );
4447
4448                                 if ( order_buf )
4449                                         OSRF_BUFFER_ADD(order_buf, ", ");
4450                                 else
4451                                         order_buf = buffer_init(128);
4452
4453                                 if( !field || !class_alias ) {
4454                                         osrfLogError( OSRF_LOG_MARK,
4455                                                 "%s: Missing class or field name in field specification "
4456                                                 "of ORDER BY clause",
4457                                                 MODULENAME );
4458                                         if( ctx )
4459                                                 osrfAppSessionStatus(
4460                                                         ctx->session,
4461                                                         OSRF_STATUS_INTERNALSERVERERROR,
4462                                                         "osrfMethodException",
4463                                                         ctx->request,
4464                                                         "Malformed ORDER BY clause -- see error log for more details"
4465                                                 );
4466                                         buffer_free( order_buf );
4467                                         free(having_buf);
4468                                         buffer_free(group_buf);
4469                                         buffer_free(sql_buf);
4470                                         if (defaultselhash)
4471                                                 jsonObjectFree(defaultselhash);
4472                                         return NULL;
4473                                 }
4474
4475                                 ClassInfo* order_class_info = search_alias( class_alias );
4476                                 if( ! order_class_info ) {
4477                                         osrfLogError(OSRF_LOG_MARK, "%s: ORDER BY clause references class \"%s\" "
4478                                                         "not in FROM clause", MODULENAME, class_alias );
4479                                         if( ctx )
4480                                                 osrfAppSessionStatus(
4481                                                         ctx->session,
4482                                                         OSRF_STATUS_INTERNALSERVERERROR,
4483                                                         "osrfMethodException",
4484                                                         ctx->request,
4485                                                         "Invalid class referenced in ORDER BY clause -- "
4486                                                         "see error log for more details"
4487                                                 );
4488                                         free(having_buf);
4489                                         buffer_free(group_buf);
4490                                         buffer_free(sql_buf);
4491                                         if (defaultselhash)
4492                                                 jsonObjectFree(defaultselhash);
4493                                         return NULL;
4494                                 }
4495
4496                                 osrfHash* field_def = osrfHashGet( order_class_info->fields, field );
4497                                 if( !field_def ) {
4498                                         osrfLogError( OSRF_LOG_MARK,
4499                                                 "%s: Invalid field \"%s\".%s referenced in ORDER BY clause",
4500                                                 MODULENAME, class_alias, field );
4501                                         if( ctx )
4502                                                 osrfAppSessionStatus(
4503                                                         ctx->session,
4504                                                         OSRF_STATUS_INTERNALSERVERERROR,
4505                                                         "osrfMethodException",
4506                                                         ctx->request,
4507                                                         "Invalid field referenced in ORDER BY clause -- "
4508                                                         "see error log for more details"
4509                                                 );
4510                                         free(having_buf);
4511                                         buffer_free(group_buf);
4512                                         buffer_free(sql_buf);
4513                                         if (defaultselhash)
4514                                                 jsonObjectFree(defaultselhash);
4515                                         return NULL;
4516                                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4517                                         osrfLogError(OSRF_LOG_MARK, "%s: Virtual field \"%s\" in ORDER BY clause",
4518                                                                  MODULENAME, field );
4519                                         if( ctx )
4520                                                 osrfAppSessionStatus(
4521                                                         ctx->session,
4522                                                         OSRF_STATUS_INTERNALSERVERERROR,
4523                                                         "osrfMethodException",
4524                                                         ctx->request,
4525                                                         "Virtual field in ORDER BY clause -- see error log for more details"
4526                                                 );
4527                                         buffer_free( order_buf );
4528                                         free(having_buf);
4529                                         buffer_free(group_buf);
4530                                         buffer_free(sql_buf);
4531                                         if (defaultselhash)
4532                                                 jsonObjectFree(defaultselhash);
4533                                         return NULL;
4534                                 }
4535
4536                                 if( jsonObjectGetKeyConst( order_spec, "transform" ) ) {
4537                                         char* transform_str = searchFieldTransform(
4538                                                         class_alias, field_def, order_spec );
4539                                         if( ! transform_str ) {
4540                                                 if( ctx )
4541                                                         osrfAppSessionStatus(
4542                                                                 ctx->session,
4543                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4544                                                                 "osrfMethodException",
4545                                                                 ctx->request,
4546                                                                 "Severe query error in ORDER BY clause -- "
4547                                                                 "see error log for more details"
4548                                                         );
4549                                                 buffer_free( order_buf );
4550                                                 free(having_buf);
4551                                                 buffer_free(group_buf);
4552                                                 buffer_free(sql_buf);
4553                                                 if (defaultselhash)
4554                                                         jsonObjectFree(defaultselhash);
4555                                                 return NULL;
4556                                         }
4557
4558                                         OSRF_BUFFER_ADD( order_buf, transform_str );
4559                                         free( transform_str );
4560                                 }
4561                                 else
4562                                         buffer_fadd( order_buf, "\"%s\".%s", class_alias, field );
4563
4564                                 const char* direction =
4565                                                 jsonObjectGetString( jsonObjectGetKeyConst( order_spec, "direction" ) );
4566                                 if( direction ) {
4567                                         if( direction[ 0 ] || 'D' == direction[ 0 ] )
4568                                                 OSRF_BUFFER_ADD( order_buf, " DESC" );
4569                                         else
4570                                                 OSRF_BUFFER_ADD( order_buf, " ASC" );
4571                                 }
4572                         }
4573                 } else if( JSON_HASH == order_hash->type ) {
4574                         // This hash is keyed on class alias.  Each class has either
4575                         // an array of field names or a hash keyed on field name.
4576                         jsonIterator* class_itr = jsonNewIterator( order_hash );
4577                         while ( (snode = jsonIteratorNext( class_itr )) ) {
4578
4579                                 ClassInfo* order_class_info = search_alias( class_itr->key );
4580                                 if( ! order_class_info ) {
4581                                         osrfLogError(OSRF_LOG_MARK,
4582                                                 "%s: Invalid class \"%s\" referenced in ORDER BY clause",
4583                                                 MODULENAME, class_itr->key );
4584                                         if( ctx )
4585                                                 osrfAppSessionStatus(
4586                                                         ctx->session,
4587                                                         OSRF_STATUS_INTERNALSERVERERROR,
4588                                                         "osrfMethodException",
4589                                                         ctx->request,
4590                                                         "Invalid class referenced in ORDER BY clause -- "
4591                                                         "see error log for more details"
4592                                                 );
4593                                         jsonIteratorFree( class_itr );
4594                                         buffer_free( order_buf );
4595                                         free(having_buf);
4596                                         buffer_free(group_buf);
4597                                         buffer_free(sql_buf);
4598                                         if (defaultselhash)
4599                                                 jsonObjectFree(defaultselhash);
4600                                         return NULL;
4601                                 }
4602
4603                                 osrfHash* field_list_def = order_class_info->fields;
4604
4605                                 if ( snode->type == JSON_HASH ) {
4606
4607                                         // Hash is keyed on field names from the current class.  For each field
4608                                         // there is another layer of hash to define the sorting details, if any,
4609                                         // or a string to indicate direction of sorting.
4610                                         jsonIterator* order_itr = jsonNewIterator( snode );
4611                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
4612
4613                                                 osrfHash* field_def = osrfHashGet( field_list_def, order_itr->key );
4614                                                 if( !field_def ) {
4615                                                         osrfLogError( OSRF_LOG_MARK,
4616                                                                 "%s: Invalid field \"%s\" in ORDER BY clause",
4617                                                                 MODULENAME, order_itr->key );
4618                                                         if( ctx )
4619                                                                 osrfAppSessionStatus(
4620                                                                         ctx->session,
4621                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4622                                                                         "osrfMethodException",
4623                                                                         ctx->request,
4624                                                                         "Invalid field in ORDER BY clause -- see error log for more details"
4625                                                                 );
4626                                                         jsonIteratorFree( order_itr );
4627                                                         jsonIteratorFree( class_itr );
4628                                                         buffer_free( order_buf );
4629                                                         free(having_buf);
4630                                                         buffer_free(group_buf);
4631                                                         buffer_free(sql_buf);
4632                                                         if (defaultselhash)
4633                                                                 jsonObjectFree(defaultselhash);
4634                                                         return NULL;
4635                                                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4636                                                         osrfLogError( OSRF_LOG_MARK,
4637                                                                 "%s: Virtual field \"%s\" in ORDER BY clause",
4638                                                                 MODULENAME, order_itr->key );
4639                                                         if( ctx )
4640                                                                 osrfAppSessionStatus(
4641                                                                         ctx->session,
4642                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4643                                                                         "osrfMethodException",
4644                                                                         ctx->request,
4645                                                                         "Virtual field in ORDER BY clause -- "
4646                                                                         "see error log for more details"
4647                                                         );
4648                                                         jsonIteratorFree( order_itr );
4649                                                         jsonIteratorFree( class_itr );
4650                                                         buffer_free( order_buf );
4651                                                         free(having_buf);
4652                                                         buffer_free(group_buf);
4653                                                         buffer_free(sql_buf);
4654                                                         if (defaultselhash)
4655                                                                 jsonObjectFree(defaultselhash);
4656                                                         return NULL;
4657                                                 }
4658
4659                                                 const char* direction = NULL;
4660                                                 if ( onode->type == JSON_HASH ) {
4661                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
4662                                                                 string = searchFieldTransform(
4663                                                                         class_itr->key,
4664                                                                         osrfHashGet( field_list_def, order_itr->key ),
4665                                                                         onode
4666                                                                 );
4667                                                                 if( ! string ) {
4668                                                                         if( ctx ) osrfAppSessionStatus(
4669                                                                                 ctx->session,
4670                                                                                 OSRF_STATUS_INTERNALSERVERERROR,
4671                                                                                 "osrfMethodException",
4672                                                                                 ctx->request,
4673                                                                                 "Severe query error in ORDER BY clause -- "
4674                                                                                 "see error log for more details"
4675                                                                         );
4676                                                                         jsonIteratorFree( order_itr );
4677                                                                         jsonIteratorFree( class_itr );
4678                                                                         free(having_buf);
4679                                                                         buffer_free(group_buf);
4680                                                                         buffer_free(order_buf);
4681                                                                         buffer_free(sql_buf);
4682                                                                         if (defaultselhash)
4683                                                                                 jsonObjectFree(defaultselhash);
4684                                                                         return NULL;
4685                                                                 }
4686                                                         } else {
4687                                                                 growing_buffer* field_buf = buffer_init(16);
4688                                                                 buffer_fadd( field_buf, "\"%s\".%s",
4689                                                                         class_itr->key, order_itr->key );
4690                                                                 string = buffer_release(field_buf);
4691                                                         }
4692
4693                                                         if ( (tmp_const = jsonObjectGetKeyConst( onode, "direction" )) ) {
4694                                                                 const char* dir = jsonObjectGetString(tmp_const);
4695                                                                 if (!strncasecmp(dir, "d", 1)) {
4696                                                                         direction = " DESC";
4697                                                                 } else {
4698                                                                         direction = " ASC";
4699                                                                 }
4700                                                         }
4701
4702                                                 } else if ( JSON_NULL == onode->type || JSON_ARRAY == onode->type ) {
4703                                                         osrfLogError( OSRF_LOG_MARK,
4704                                                                 "%s: Expected JSON_STRING in ORDER BY clause; found %s",
4705                                                                 MODULENAME, json_type( onode->type ) );
4706                                                         if( ctx )
4707                                                                 osrfAppSessionStatus(
4708                                                                         ctx->session,
4709                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4710                                                                         "osrfMethodException",
4711                                                                         ctx->request,
4712                                                                         "Malformed ORDER BY clause -- see error log for more details"
4713                                                                 );
4714                                                         jsonIteratorFree( order_itr );
4715                                                         jsonIteratorFree( class_itr );
4716                                                         free(having_buf);
4717                                                         buffer_free(group_buf);
4718                                                         buffer_free(order_buf);
4719                                                         buffer_free(sql_buf);
4720                                                         if (defaultselhash)
4721                                                                 jsonObjectFree(defaultselhash);
4722                                                         return NULL;
4723
4724                                                 } else {
4725                                                         string = strdup(order_itr->key);
4726                                                         const char* dir = jsonObjectGetString(onode);
4727                                                         if (!strncasecmp(dir, "d", 1)) {
4728                                                                 direction = " DESC";
4729                                                         } else {
4730                                                                 direction = " ASC";
4731                                                         }
4732                                                 }
4733
4734                                                 if ( order_buf )
4735                                                         OSRF_BUFFER_ADD(order_buf, ", ");
4736                                                 else
4737                                                         order_buf = buffer_init(128);
4738
4739                                                 OSRF_BUFFER_ADD(order_buf, string);
4740                                                 free(string);
4741
4742                                                 if (direction) {
4743                                                          OSRF_BUFFER_ADD(order_buf, direction);
4744                                                 }
4745
4746                                         } // end while
4747                                         jsonIteratorFree(order_itr);
4748
4749                                 } else if ( snode->type == JSON_ARRAY ) {
4750
4751                                         // Array is a list of fields from the current class
4752                                         unsigned long order_idx = 0;
4753                                         while(( onode = jsonObjectGetIndex( snode, order_idx++ ) )) {
4754
4755                                                 const char* _f = jsonObjectGetString( onode );
4756
4757                                                 osrfHash* field_def = osrfHashGet( field_list_def, _f );
4758                                                 if( !field_def ) {
4759                                                         osrfLogError( OSRF_LOG_MARK,
4760                                                                         "%s: Invalid field \"%s\" in ORDER BY clause",
4761                                                                         MODULENAME, _f );
4762                                                         if( ctx )
4763                                                                 osrfAppSessionStatus(
4764                                                                         ctx->session,
4765                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4766                                                                         "osrfMethodException",
4767                                                                         ctx->request,
4768                                                                         "Invalid field in ORDER BY clause -- "
4769                                                                         "see error log for more details"
4770                                                                 );
4771                                                         jsonIteratorFree( class_itr );
4772                                                         buffer_free( order_buf );
4773                                                         free(having_buf);
4774                                                         buffer_free(group_buf);
4775                                                         buffer_free(sql_buf);
4776                                                         if (defaultselhash)
4777                                                                 jsonObjectFree(defaultselhash);
4778                                                         return NULL;
4779                                                 } else if( str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4780                                                         osrfLogError( OSRF_LOG_MARK,
4781                                                                 "%s: Virtual field \"%s\" in ORDER BY clause",
4782                                                                 MODULENAME, _f );
4783                                                         if( ctx )
4784                                                                 osrfAppSessionStatus(
4785                                                                         ctx->session,
4786                                                                         OSRF_STATUS_INTERNALSERVERERROR,
4787                                                                         "osrfMethodException",
4788                                                                         ctx->request,
4789                                                                         "Virtual field in ORDER BY clause -- "
4790                                                                         "see error log for more details"
4791                                                                 );
4792                                                         jsonIteratorFree( class_itr );
4793                                                         buffer_free( order_buf );
4794                                                         free(having_buf);
4795                                                         buffer_free(group_buf);
4796                                                         buffer_free(sql_buf);
4797                                                         if (defaultselhash)
4798                                                                 jsonObjectFree(defaultselhash);
4799                                                         return NULL;
4800                                                 }
4801
4802                                                 if ( order_buf )
4803                                                         OSRF_BUFFER_ADD(order_buf, ", ");
4804                                                 else
4805                                                         order_buf = buffer_init(128);
4806
4807                                                 buffer_fadd( order_buf, "\"%s\".%s", class_itr->key, _f);
4808
4809                                         } // end while
4810
4811                                 // IT'S THE OOOOOOOOOOOLD STYLE!
4812                                 } else {
4813                                         osrfLogError(OSRF_LOG_MARK,
4814                                                 "%s: Possible SQL injection attempt; direct order by is not allowed",
4815                                                 MODULENAME);
4816                                         if (ctx) {
4817                                                 osrfAppSessionStatus(
4818                                                         ctx->session,
4819                                                         OSRF_STATUS_INTERNALSERVERERROR,
4820                                                         "osrfMethodException",
4821                                                         ctx->request,
4822                                                         "Severe query error -- see error log for more details"
4823                                                 );
4824                                         }
4825
4826                                         free(having_buf);
4827                                         buffer_free(group_buf);
4828                                         buffer_free(order_buf);
4829                                         buffer_free(sql_buf);
4830                                         if (defaultselhash)
4831                                                 jsonObjectFree(defaultselhash);
4832                                         jsonIteratorFree(class_itr);
4833                                         return NULL;
4834                                 }
4835                         } // end while
4836                         jsonIteratorFree( class_itr );
4837                 } else {
4838                         osrfLogError(OSRF_LOG_MARK,
4839                                 "%s: Malformed ORDER BY clause; expected JSON_HASH or JSON_ARRAY, found %s",
4840                                 MODULENAME, json_type( order_hash->type ) );
4841                         if( ctx )
4842                                 osrfAppSessionStatus(
4843                                         ctx->session,
4844                                         OSRF_STATUS_INTERNALSERVERERROR,
4845                                         "osrfMethodException",
4846                                         ctx->request,
4847                                         "Malformed ORDER BY clause -- see error log for more details"
4848                                 );
4849                         buffer_free( order_buf );
4850                         free(having_buf);
4851                         buffer_free(group_buf);
4852                         buffer_free(sql_buf);
4853                         if (defaultselhash)
4854                                 jsonObjectFree(defaultselhash);
4855                         return NULL;
4856                 }
4857
4858                 if( order_buf )
4859                         order_by_list = buffer_release( order_buf );
4860         }
4861
4862
4863         string = buffer_release(group_buf);
4864
4865         if ( *string && ( aggregate_found || (flags & SELECT_DISTINCT) ) ) {
4866                 OSRF_BUFFER_ADD( sql_buf, " GROUP BY " );
4867                 OSRF_BUFFER_ADD( sql_buf, string );
4868         }
4869
4870         free(string);
4871
4872         if( having_buf && *having_buf ) {
4873                 OSRF_BUFFER_ADD( sql_buf, " HAVING " );
4874                 OSRF_BUFFER_ADD( sql_buf, having_buf );
4875                 free( having_buf );
4876         }
4877
4878         if( order_by_list ) {
4879
4880                 if ( *order_by_list ) {
4881                         OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
4882                         OSRF_BUFFER_ADD( sql_buf, order_by_list );
4883                 }
4884
4885                 free( order_by_list );
4886         }
4887
4888         if ( limit ){
4889                 const char* str = jsonObjectGetString(limit);
4890                 buffer_fadd( sql_buf, " LIMIT %d", atoi(str) );
4891         }
4892
4893         if (offset) {
4894                 const char* str = jsonObjectGetString(offset);
4895                 buffer_fadd( sql_buf, " OFFSET %d", atoi(str) );
4896         }
4897
4898         if (!(flags & SUBSELECT)) OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
4899
4900         if (defaultselhash)
4901                  jsonObjectFree(defaultselhash);
4902
4903         return buffer_release(sql_buf);
4904
4905 } // end of SELECT()
4906
4907 static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrfHash* meta, osrfMethodContext* ctx ) {
4908
4909         const char* locale = osrf_message_get_last_locale();
4910
4911         osrfHash* fields = osrfHashGet(meta, "fields");
4912         char* core_class = osrfHashGet(meta, "classname");
4913
4914         const jsonObject* join_hash = jsonObjectGetKeyConst( order_hash, "join" );
4915
4916         jsonObject* node = NULL;
4917         jsonObject* snode = NULL;
4918         jsonObject* onode = NULL;
4919         const jsonObject* _tmp = NULL;
4920         jsonObject* selhash = NULL;
4921         jsonObject* defaultselhash = NULL;
4922
4923         growing_buffer* sql_buf = buffer_init(128);
4924         growing_buffer* select_buf = buffer_init(128);
4925
4926         if ( !(selhash = jsonObjectGetKey( order_hash, "select" )) ) {
4927                 defaultselhash = jsonNewObjectType(JSON_HASH);
4928                 selhash = defaultselhash;
4929         }
4930
4931         // If there's no SELECT list for the core class, build one
4932         if ( !jsonObjectGetKeyConst(selhash,core_class) ) {
4933                 jsonObject* field_list = jsonNewObjectType( JSON_ARRAY );
4934
4935                 // Add every non-virtual field to the field list
4936                 osrfHash* field_def = NULL;
4937                 osrfHashIterator* field_itr = osrfNewHashIterator( fields );
4938                 while( ( field_def = osrfHashIteratorNext( field_itr ) ) ) {
4939                         if( ! str_is_true( osrfHashGet( field_def, "virtual" ) ) ) {
4940                                 const char* field = osrfHashIteratorKey( field_itr );
4941                                 jsonObjectPush( field_list, jsonNewObject( field ) );
4942                         }
4943                 }
4944                 osrfHashIteratorFree( field_itr );
4945                 jsonObjectSetKey( selhash, core_class, field_list );
4946         }
4947
4948         int first = 1;
4949         jsonIterator* class_itr = jsonNewIterator( selhash );
4950         while ( (snode = jsonIteratorNext( class_itr )) ) {
4951
4952                 const char* cname = class_itr->key;
4953                 osrfHash* idlClass = osrfHashGet( oilsIDL(), cname );
4954                 if (!idlClass)
4955                         continue;
4956
4957                 if (strcmp(core_class,class_itr->key)) {
4958                         if (!join_hash)
4959                                 continue;
4960
4961                         jsonObject* found =  jsonObjectFindPath(join_hash, "//%s", class_itr->key);
4962                         if (!found->size) {
4963                                 jsonObjectFree(found);
4964                                 continue;
4965                         }
4966
4967                         jsonObjectFree(found);
4968                 }
4969
4970                 jsonIterator* select_itr = jsonNewIterator( snode );
4971                 while ( (node = jsonIteratorNext( select_itr )) ) {
4972                         const char* item_str = jsonObjectGetString( node );
4973                         osrfHash* field = osrfHashGet( osrfHashGet( idlClass, "fields" ), item_str );
4974                         char* fname = osrfHashGet(field, "name");
4975
4976                         if (!field)
4977                                 continue;
4978
4979                         if (first) {
4980                                 first = 0;
4981                         } else {
4982                                 OSRF_BUFFER_ADD_CHAR(select_buf, ',');
4983                         }
4984
4985                         if (locale) {
4986                                 const char* i18n;
4987                                 const jsonObject* no_i18n_obj = jsonObjectGetKey( order_hash, "no_i18n" );
4988                                 if ( obj_is_true( no_i18n_obj ) )    // Suppress internationalization?
4989                                         i18n = NULL;
4990                                 else
4991                                         i18n = osrfHashGet(field, "i18n");
4992
4993                                 if( str_is_true( i18n ) ) {
4994                                         char* pkey = osrfHashGet(idlClass, "primarykey");
4995                                         char* tname = osrfHashGet(idlClass, "tablename");
4996
4997                                         buffer_fadd(select_buf, " oils_i18n_xlate('%s', '%s', '%s', "
4998                                                         "'%s', \"%s\".%s::TEXT, '%s') AS \"%s\"",
4999                                                         tname, cname, fname, pkey, cname, pkey, locale, fname);
5000                                 } else {
5001                                         buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
5002                                 }
5003                         } else {
5004                                 buffer_fadd(select_buf, " \"%s\".%s", cname, fname);
5005                         }
5006                 }
5007
5008                 jsonIteratorFree(select_itr);
5009         }
5010
5011         jsonIteratorFree(class_itr);
5012
5013         char* col_list = buffer_release(select_buf);
5014         char* table = getRelation(meta);
5015         if( !table )
5016                 table = strdup( "(null)" );
5017
5018         buffer_fadd(sql_buf, "SELECT %s FROM %s AS \"%s\"", col_list, table, core_class );
5019         free(col_list);
5020         free(table);
5021
5022         // Clear the query stack (as a fail-safe precaution against possible
5023         // leftover garbage); then push the first query frame onto the stack.
5024         clear_query_stack();
5025         push_query_frame();
5026         if( add_query_core( NULL, core_class ) ) {
5027                 if( ctx )
5028                         osrfAppSessionStatus(
5029                                 ctx->session,
5030                                 OSRF_STATUS_INTERNALSERVERERROR,
5031                                 "osrfMethodException",
5032                                 ctx->request,
5033                                 "Unable to build query frame for core class"
5034                         );
5035                 return NULL;
5036         }
5037
5038         if ( join_hash ) {
5039                 char* join_clause = searchJOIN( join_hash, &curr_query->core );
5040                 OSRF_BUFFER_ADD_CHAR(sql_buf, ' ');
5041                 OSRF_BUFFER_ADD(sql_buf, join_clause);
5042                 free(join_clause);
5043         }
5044
5045         osrfLogDebug(OSRF_LOG_MARK, "%s pre-predicate SQL =  %s",
5046                                  MODULENAME, OSRF_BUFFER_C_STR(sql_buf));
5047
5048         OSRF_BUFFER_ADD(sql_buf, " WHERE ");
5049
5050         char* pred = searchWHERE( search_hash, &curr_query->core, AND_OP_JOIN, ctx );
5051         if (!pred) {
5052                 osrfAppSessionStatus(
5053                         ctx->session,
5054                         OSRF_STATUS_INTERNALSERVERERROR,
5055                                 "osrfMethodException",
5056                                 ctx->request,
5057                                 "Severe query error -- see error log for more details"
5058                         );
5059                 buffer_free(sql_buf);
5060                 if(defaultselhash)
5061                         jsonObjectFree(defaultselhash);
5062                 clear_query_stack();
5063                 return NULL;
5064         } else {
5065                 buffer_add(sql_buf, pred);
5066                 free(pred);
5067         }
5068
5069         if (order_hash) {
5070                 char* string = NULL;
5071                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "order_by" )) ){
5072
5073                         growing_buffer* order_buf = buffer_init(128);
5074
5075                         first = 1;
5076                         jsonIterator* class_itr = jsonNewIterator( _tmp );
5077                         while ( (snode = jsonIteratorNext( class_itr )) ) {
5078
5079                                 if (!jsonObjectGetKeyConst(selhash,class_itr->key))
5080                                         continue;
5081
5082                                 if ( snode->type == JSON_HASH ) {
5083
5084                                         jsonIterator* order_itr = jsonNewIterator( snode );
5085                                         while ( (onode = jsonIteratorNext( order_itr )) ) {
5086
5087                                                 osrfHash* field_def = oilsIDLFindPath( "/%s/fields/%s",
5088                                                                 class_itr->key, order_itr->key );
5089                                                 if ( !field_def )
5090                                                         continue;
5091
5092                                                 char* direction = NULL;
5093                                                 if ( onode->type == JSON_HASH ) {
5094                                                         if ( jsonObjectGetKeyConst( onode, "transform" ) ) {
5095                                                                 string = searchFieldTransform( class_itr->key, field_def, onode );
5096                                                                 if( ! string ) {
5097                                                                         osrfAppSessionStatus(
5098                                                                                 ctx->session,
5099                                                                                 OSRF_STATUS_INTERNALSERVERERROR,
5100                                                                                 "osrfMethodException",
5101                                                                                 ctx->request,
5102                                                                                 "Severe query error in ORDER BY clause -- "
5103                                                                                 "see error log for more details"
5104                                                                         );
5105                                                                         jsonIteratorFree( order_itr );
5106                                                                         jsonIteratorFree( class_itr );
5107                                                                         buffer_free( order_buf );
5108                                                                         buffer_free( sql_buf );
5109                                                                         if( defaultselhash )
5110                                                                                 jsonObjectFree( defaultselhash );
5111                                                                         clear_query_stack();
5112                                                                         return NULL;
5113                                                                 }
5114                                                         } else {
5115                                                                 growing_buffer* field_buf = buffer_init(16);
5116                                                                 buffer_fadd( field_buf, "\"%s\".%s",
5117                                                                         class_itr->key, order_itr->key );
5118                                                                 string = buffer_release(field_buf);
5119                                                         }
5120
5121                                                         if ( (_tmp = jsonObjectGetKeyConst( onode, "direction" )) ) {
5122                                                                 const char* dir = jsonObjectGetString(_tmp);
5123                                                                 if (!strncasecmp(dir, "d", 1)) {
5124                                                                         direction = " DESC";
5125                                                                 } else {
5126                                                                         free(direction);
5127                                                                 }
5128                                                         }
5129                                                 } else {
5130                                                         string = strdup(order_itr->key);
5131                                                         const char* dir = jsonObjectGetString(onode);
5132                                                         if (!strncasecmp(dir, "d", 1)) {
5133                                                                 direction = " DESC";
5134                                                         } else {
5135                                                                 direction = " ASC";
5136                                                         }
5137                                                 }
5138
5139                                                 if (first) {
5140                                                         first = 0;
5141                                                 } else {
5142                                                         buffer_add(order_buf, ", ");
5143                                                 }
5144
5145                                                 buffer_add(order_buf, string);
5146                                                 free(string);
5147
5148                                                 if (direction) {
5149                                                         buffer_add(order_buf, direction);
5150                                                 }
5151                                         }
5152
5153                                         jsonIteratorFree(order_itr);
5154
5155                                 } else {
5156                                         const char* str = jsonObjectGetString(snode);
5157                                         buffer_add(order_buf, str);
5158                                         break;
5159                                 }
5160
5161                         }
5162
5163                         jsonIteratorFree(class_itr);
5164
5165                         string = buffer_release(order_buf);
5166
5167                         if ( *string ) {
5168                                 OSRF_BUFFER_ADD( sql_buf, " ORDER BY " );
5169                                 OSRF_BUFFER_ADD( sql_buf, string );
5170                         }
5171
5172                         free(string);
5173                 }
5174
5175                 if ( (_tmp = jsonObjectGetKeyConst( order_hash, "limit" )) ){
5176                         const char* str = jsonObjectGetString(_tmp);
5177                         buffer_fadd(
5178                                 sql_buf,
5179                                 " LIMIT %d",
5180                                 atoi(str)
5181                         );
5182                 }
5183
5184                 _tmp = jsonObjectGetKeyConst( order_hash, "offset" );
5185                 if (_tmp) {
5186                         const char* str = jsonObjectGetString(_tmp);
5187                         buffer_fadd(
5188                                 sql_buf,
5189                                 " OFFSET %d",
5190                                 atoi(str)
5191                         );
5192                 }
5193         }
5194
5195         if (defaultselhash)
5196                 jsonObjectFree(defaultselhash);
5197         clear_query_stack();
5198
5199         OSRF_BUFFER_ADD_CHAR(sql_buf, ';');
5200         return buffer_release(sql_buf);
5201 }
5202
5203 int doJSONSearch ( osrfMethodContext* ctx ) {
5204         if(osrfMethodVerifyContext( ctx )) {
5205                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
5206                 return -1;
5207         }
5208
5209         osrfLogDebug(OSRF_LOG_MARK, "Received query request");
5210
5211         int err = 0;
5212
5213         // XXX for now...
5214         dbhandle = writehandle;
5215
5216         jsonObject* hash = jsonObjectGetIndex(ctx->params, 0);
5217
5218         int flags = 0;
5219
5220         if ( obj_is_true( jsonObjectGetKey( hash, "distinct" ) ) )
5221                 flags |= SELECT_DISTINCT;
5222
5223         if ( obj_is_true( jsonObjectGetKey( hash, "no_i18n" ) ) )
5224                 flags |= DISABLE_I18N;
5225
5226         osrfLogDebug(OSRF_LOG_MARK, "Building SQL ...");
5227         clear_query_stack();       // a possibly needless precaution
5228         char* sql = buildQuery( ctx, hash, flags );
5229         clear_query_stack();
5230
5231         if (!sql) {
5232                 err = -1;
5233                 return err;
5234         }
5235
5236         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
5237         dbi_result result = dbi_conn_query(dbhandle, sql);
5238
5239         if(result) {
5240                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
5241
5242                 if (dbi_result_first_row(result)) {
5243                         /* JSONify the result */
5244                         osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
5245
5246                         do {
5247                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
5248                                 osrfAppRespond( ctx, return_val );
5249                                 jsonObjectFree( return_val );
5250                         } while (dbi_result_next_row(result));
5251
5252                 } else {
5253                         osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s", MODULENAME, sql);
5254                 }
5255
5256                 osrfAppRespondComplete( ctx, NULL );
5257
5258                 /* clean up the query */
5259                 dbi_result_free(result);
5260
5261         } else {
5262                 err = -1;
5263                 osrfLogError(OSRF_LOG_MARK, "%s: Error with query [%s]", MODULENAME, sql);
5264                 osrfAppSessionStatus(
5265                         ctx->session,
5266                         OSRF_STATUS_INTERNALSERVERERROR,
5267                         "osrfMethodException",
5268                         ctx->request,
5269                         "Severe query error -- see error log for more details"
5270                 );
5271         }
5272
5273         free(sql);
5274         return err;
5275 }
5276
5277 static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* class_meta,
5278                 jsonObject* where_hash, jsonObject* query_hash, int* err ) {
5279
5280         // XXX for now...
5281         dbhandle = writehandle;
5282
5283         char* core_class = osrfHashGet( class_meta, "classname" );
5284         char* pkey = osrfHashGet( class_meta, "primarykey" );
5285
5286         const jsonObject* _tmp;
5287
5288         char* sql = buildSELECT( where_hash, query_hash, class_meta, ctx );
5289         if (!sql) {
5290                 osrfLogDebug(OSRF_LOG_MARK, "Problem building query, returning NULL");
5291                 *err = -1;
5292                 return NULL;
5293         }
5294
5295         osrfLogDebug(OSRF_LOG_MARK, "%s SQL =  %s", MODULENAME, sql);
5296
5297         dbi_result result = dbi_conn_query(dbhandle, sql);
5298         if( NULL == result ) {
5299                 osrfLogError(OSRF_LOG_MARK, "%s: Error retrieving %s with query [%s]",
5300                         MODULENAME, osrfHashGet( class_meta, "fieldmapper" ), sql);
5301                 osrfAppSessionStatus(
5302                         ctx->session,
5303                         OSRF_STATUS_INTERNALSERVERERROR,
5304                         "osrfMethodException",
5305                         ctx->request,
5306                         "Severe query error -- see error log for more details"
5307                 );
5308                 *err = -1;
5309                 free(sql);
5310                 return jsonNULL;
5311
5312         } else {
5313                 osrfLogDebug(OSRF_LOG_MARK, "Query returned with no errors");
5314         }
5315
5316         jsonObject* res_list = jsonNewObjectType(JSON_ARRAY);
5317         jsonObject* row_obj = NULL;
5318
5319         if (dbi_result_first_row(result)) {
5320
5321                 // Convert each row to a JSON_ARRAY of column values, and enclose those objects
5322                 // in a JSON_ARRAY of rows.  If two or more rows have the same key value, then
5323                 // eliminate the duplicates.
5324                 osrfLogDebug(OSRF_LOG_MARK, "Query returned at least one row");
5325                 osrfHash* dedup = osrfNewHash();
5326                 do {
5327                         row_obj = oilsMakeFieldmapperFromResult( result, class_meta );
5328                         char* pkey_val = oilsFMGetString( row_obj, pkey );
5329                         if ( osrfHashGet( dedup, pkey_val ) ) {
5330                                 jsonObjectFree( row_obj );
5331                                 free( pkey_val );
5332                         } else {
5333                                 osrfHashSet( dedup, pkey_val, pkey_val );
5334                                 jsonObjectPush( res_list, row_obj );
5335                         }
5336                 } while (dbi_result_next_row(result));
5337                 osrfHashFree(dedup);
5338
5339         } else {
5340                 osrfLogDebug(OSRF_LOG_MARK, "%s returned no results for query %s",
5341                         MODULENAME, sql );
5342         }
5343
5344         /* clean up the query */
5345         dbi_result_free(result);
5346         free(sql);
5347
5348         // If we're asked to flesh, and there's anything to flesh, then flesh.
5349         if (res_list->size && query_hash) {
5350                 _tmp = jsonObjectGetKeyConst( query_hash, "flesh" );
5351                 if (_tmp) {
5352                         // Get the flesh depth
5353                         int flesh_depth = (int) jsonObjectGetNumber( _tmp );
5354                         if ( flesh_depth == -1 || flesh_depth > max_flesh_depth )
5355                                 flesh_depth = max_flesh_depth;
5356
5357                         // We need a non-zero flesh depth, and a list of fields to flesh
5358                         const jsonObject* temp_blob = jsonObjectGetKeyConst( query_hash, "flesh_fields" );
5359                         if ( temp_blob && flesh_depth > 0 ) {
5360
5361                                 jsonObject* flesh_blob = jsonObjectClone( temp_blob );
5362                                 const jsonObject* flesh_fields = jsonObjectGetKeyConst( flesh_blob, core_class );
5363
5364                                 osrfStringArray* link_fields = NULL;
5365                                 osrfHash* links = osrfHashGet( class_meta, "links" );
5366
5367                                 // Make an osrfStringArray of the names of fields to be fleshed
5368                                 if (flesh_fields) {
5369                                         if (flesh_fields->size == 1) {
5370                                                 const char* _t = jsonObjectGetString(
5371                                                         jsonObjectGetIndex( flesh_fields, 0 ) );
5372                                                 if (!strcmp(_t,"*"))
5373                                                         link_fields = osrfHashKeys( links );
5374                                         }
5375
5376                                         if (!link_fields) {
5377                                                 jsonObject* _f;
5378                                                 link_fields = osrfNewStringArray(1);
5379                                                 jsonIterator* _i = jsonNewIterator( flesh_fields );
5380                                                 while ((_f = jsonIteratorNext( _i ))) {
5381                                                         osrfStringArrayAdd( link_fields, jsonObjectGetString( _f ) );
5382                                                 }
5383                                                 jsonIteratorFree(_i);
5384                                         }
5385                                 }
5386
5387                                 osrfHash* fields = osrfHashGet( class_meta, "fields" );
5388
5389                                 // Iterate over the JSON_ARRAY of rows
5390                                 jsonObject* cur;
5391                                 unsigned long res_idx = 0;
5392                                 while ((cur = jsonObjectGetIndex( res_list, res_idx++ ) )) {
5393
5394                                         int i = 0;
5395                                         const char* link_field;
5396
5397                                         // Iterate over the list of fleshable fields
5398                                         while ( (link_field = osrfStringArrayGetString(link_fields, i++)) ) {
5399
5400                                                 osrfLogDebug(OSRF_LOG_MARK, "Starting to flesh %s", link_field);
5401
5402                                                 osrfHash* kid_link = osrfHashGet(links, link_field);
5403                                                 if (!kid_link)
5404                                                         continue;     // Not a link field; skip it
5405
5406                                                 osrfHash* field = osrfHashGet(fields, link_field);
5407                                                 if (!field)
5408                                                         continue;     // Not a field at all; skip it (IDL is ill-formed)
5409
5410                                                 osrfHash* kid_idl = osrfHashGet(oilsIDL(), osrfHashGet(kid_link, "class"));
5411                                                 if (!kid_idl)
5412                                                         continue;   // The class it links to doesn't exist; skip it
5413
5414                                                 const char* reltype = osrfHashGet( kid_link, "reltype" );
5415                                                 if( !reltype )
5416                                                         continue;   // No reltype; skip it (IDL is ill-formed)
5417
5418                                                 osrfHash* value_field = field;
5419
5420                                                 if (   !strcmp( reltype, "has_many" )
5421                                                         || !strcmp( reltype, "might_have" ) ) { // has_many or might_have
5422                                                         value_field = osrfHashGet(
5423                                                                 fields, osrfHashGet( class_meta, "primarykey" ) );
5424                                                 }
5425
5426                                                 osrfStringArray* link_map = osrfHashGet( kid_link, "map" );
5427
5428                                                 if (link_map->size > 0) {
5429                                                         jsonObject* _kid_key = jsonNewObjectType(JSON_ARRAY);
5430                                                         jsonObjectPush(
5431                                                                 _kid_key,
5432                                                                 jsonNewObject( osrfStringArrayGetString( link_map, 0 ) )
5433                                                         );
5434
5435                                                         jsonObjectSetKey(
5436                                                                 flesh_blob,
5437                                                                 osrfHashGet(kid_link, "class"),
5438                                                                 _kid_key
5439                                                         );
5440                                                 };
5441
5442                                                 osrfLogDebug(
5443                                                         OSRF_LOG_MARK,
5444                                                         "Link field: %s, remote class: %s, fkey: %s, reltype: %s",
5445                                                         osrfHashGet(kid_link, "field"),
5446                                                         osrfHashGet(kid_link, "class"),
5447                                                         osrfHashGet(kid_link, "key"),
5448                                                         osrfHashGet(kid_link, "reltype")
5449                                                 );
5450
5451                                                 const char* search_key = jsonObjectGetString(
5452                                                         jsonObjectGetIndex( cur,
5453                                                                 atoi( osrfHashGet(value_field, "array_position") )
5454                                                         )
5455                                                 );
5456
5457                                                 if (!search_key) {
5458                                                         osrfLogDebug(OSRF_LOG_MARK, "Nothing to search for!");
5459                                                         continue;
5460                                                 }
5461
5462                                                 osrfLogDebug(OSRF_LOG_MARK, "Creating param objects...");
5463
5464                                                 // construct WHERE clause
5465                                                 jsonObject* where_clause  = jsonNewObjectType(JSON_HASH);
5466                                                 jsonObjectSetKey(
5467                                                         where_clause,
5468                                                         osrfHashGet(kid_link, "key"),
5469                                                         jsonNewObject( search_key )
5470                                                 );
5471
5472                                                 // construct the rest of the query, mostly
5473                                                 // by copying pieces of the previous level of query
5474                                                 jsonObject* rest_of_query = jsonNewObjectType(JSON_HASH);
5475                                                 jsonObjectSetKey( rest_of_query, "flesh",
5476                                                         jsonNewNumberObject( flesh_depth - 1 + link_map->size )
5477                                                 );
5478
5479                                                 if (flesh_blob)
5480                                                         jsonObjectSetKey( rest_of_query, "flesh_fields",
5481                                                                 jsonObjectClone(flesh_blob) );
5482
5483                                                 if (jsonObjectGetKeyConst(query_hash, "order_by")) {
5484                                                         jsonObjectSetKey( rest_of_query, "order_by",
5485                                                                 jsonObjectClone(jsonObjectGetKeyConst(query_hash, "order_by"))
5486                                                         );
5487                                                 }
5488
5489                                                 if (jsonObjectGetKeyConst(query_hash, "select")) {
5490                                                         jsonObjectSetKey( rest_of_query, "select",
5491                                                                 jsonObjectClone(jsonObjectGetKeyConst(query_hash, "select"))
5492                                                         );
5493                                                 }
5494
5495                                                 // do the query, recursively, to expand the fleshable field
5496                                                 jsonObject* kids = doFieldmapperSearch( ctx, kid_idl,
5497                                                         where_clause, rest_of_query, err);
5498
5499                                                 jsonObjectFree( where_clause );
5500                                                 jsonObjectFree( rest_of_query );
5501
5502                                                 if(*err) {
5503                                                         osrfStringArrayFree(link_fields);
5504                                                         jsonObjectFree(res_list);
5505                                                         jsonObjectFree(flesh_blob);
5506                                                         return jsonNULL;
5507                                                 }
5508
5509                                                 osrfLogDebug(OSRF_LOG_MARK, "Search for %s return %d linked objects",
5510                                                         osrfHashGet(kid_link, "class"), kids->size);
5511
5512                                                 // Traverse the result set
5513                                                 jsonObject* X = NULL;
5514                                                 if ( link_map->size > 0 && kids->size > 0 ) {
5515                                                         X = kids;
5516                                                         kids = jsonNewObjectType(JSON_ARRAY);
5517
5518                                                         jsonObject* _k_node;
5519                                                         unsigned long res_idx = 0;
5520                                                         while ((_k_node = jsonObjectGetIndex( X, res_idx++ ) )) {
5521                                                                 jsonObjectPush(
5522                                                                         kids,
5523                                                                         jsonObjectClone(
5524                                                                                 jsonObjectGetIndex(
5525                                                                                         _k_node,
5526                                                                                         (unsigned long)atoi(
5527                                                                                                 osrfHashGet(
5528                                                                                                         osrfHashGet(
5529                                                                                                                 osrfHashGet(
5530                                                                                                                         osrfHashGet(
5531                                                                                                                                 oilsIDL(),
5532                                                                                                                                 osrfHashGet(kid_link, "class")
5533                                                                                                                         ),
5534                                                                                                                         "fields"
5535                                                                                                                 ),
5536                                                                                                                 osrfStringArrayGetString( link_map, 0 )
5537                                                                                                         ),
5538                                                                                                         "array_position"
5539                                                                                                 )
5540                                                                                         )
5541                                                                                 )
5542                                                                         )
5543                                                                 );
5544                                                         } // end while loop traversing X
5545                                                 }
5546
5547                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_a" ))
5548                                                         || !(strcmp( osrfHashGet(kid_link, "reltype"), "might_have" ))) {
5549                                                         osrfLogDebug(OSRF_LOG_MARK, "Storing fleshed objects in %s",
5550                                                                 osrfHashGet(kid_link, "field"));
5551                                                         jsonObjectSetIndex(
5552                                                                 cur,
5553                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
5554                                                                 jsonObjectClone( jsonObjectGetIndex(kids, 0) )
5555                                                         );
5556                                                 }
5557
5558                                                 if (!(strcmp( osrfHashGet(kid_link, "reltype"), "has_many" ))) {
5559                                                         // has_many
5560                                                         osrfLogDebug( OSRF_LOG_MARK, "Storing fleshed objects in %s",
5561                                                                 osrfHashGet( kid_link, "field" ) );
5562                                                         jsonObjectSetIndex(
5563                                                                 cur,
5564                                                                 (unsigned long)atoi( osrfHashGet( field, "array_position" ) ),
5565                                                                 jsonObjectClone( kids )
5566                                                         );
5567                                                 }
5568
5569                                                 if (X) {
5570                                                         jsonObjectFree(kids);
5571                                                         kids = X;
5572                                                 }
5573
5574                                                 jsonObjectFree( kids );
5575
5576                                                 osrfLogDebug( OSRF_LOG_MARK, "Fleshing of %s complete",
5577                                                         osrfHashGet( kid_link, "field" ) );
5578                                                 osrfLogDebug(OSRF_LOG_MARK, "%s", jsonObjectToJSON(cur));
5579
5580                                         } // end while loop traversing list of fleshable fields
5581                                 } // end while loop traversing res_list
5582                                 jsonObjectFree( flesh_blob );
5583                                 osrfStringArrayFree(link_fields);
5584                         }
5585                 }
5586         }
5587
5588         return res_list;
5589 }
5590
5591
5592 static jsonObject* doUpdate(osrfMethodContext* ctx, int* err ) {
5593
5594         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
5595 #ifdef PCRUD
5596         jsonObject* target = jsonObjectGetIndex( ctx->params, 1 );
5597 #else
5598         jsonObject* target = jsonObjectGetIndex( ctx->params, 0 );
5599 #endif
5600
5601         if (!verifyObjectClass(ctx, target)) {
5602                 *err = -1;
5603                 return jsonNULL;
5604         }
5605
5606         if( getXactId( ctx ) == NULL ) {
5607                 osrfAppSessionStatus(
5608                         ctx->session,
5609                         OSRF_STATUS_BADREQUEST,
5610                         "osrfMethodException",
5611                         ctx->request,
5612                         "No active transaction -- required for UPDATE"
5613                 );
5614                 *err = -1;
5615                 return jsonNULL;
5616         }
5617
5618         // The following test is harmless but redundant.  If a class is
5619         // readonly, we don't register an update method for it.
5620         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
5621                 osrfAppSessionStatus(
5622                         ctx->session,
5623                         OSRF_STATUS_BADREQUEST,
5624                         "osrfMethodException",
5625                         ctx->request,
5626                         "Cannot UPDATE readonly class"
5627                 );
5628                 *err = -1;
5629                 return jsonNULL;
5630         }
5631
5632         dbhandle = writehandle;
5633         const char* trans_id = getXactId( ctx );
5634
5635         // Set the last_xact_id
5636         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
5637         if (index > -1) {
5638                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
5639                                 trans_id, target->classname, index);
5640                 jsonObjectSetIndex(target, index, jsonNewObject(trans_id));
5641         }
5642
5643         char* pkey = osrfHashGet(meta, "primarykey");
5644         osrfHash* fields = osrfHashGet(meta, "fields");
5645
5646         char* id = oilsFMGetString( target, pkey );
5647
5648         osrfLogDebug(
5649                 OSRF_LOG_MARK,
5650                 "%s updating %s object with %s = %s",
5651                 MODULENAME,
5652                 osrfHashGet(meta, "fieldmapper"),
5653                 pkey,
5654                 id
5655         );
5656
5657         growing_buffer* sql = buffer_init(128);
5658         buffer_fadd(sql,"UPDATE %s SET", osrfHashGet(meta, "tablename"));
5659
5660         int first = 1;
5661         osrfHash* field_def = NULL;
5662         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
5663         while( (field_def = osrfHashIteratorNext( field_itr ) ) ) {
5664
5665                 // Skip virtual fields, and the primary key
5666                 if( str_is_true( osrfHashGet( field_def, "virtual") ) )
5667                         continue;
5668
5669                 const char* field_name = osrfHashIteratorKey( field_itr );
5670                 if( ! strcmp( field_name, pkey ) )
5671                         continue;
5672
5673                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
5674
5675                 int value_is_numeric = 0;    // boolean
5676                 char* value;
5677                 if (field_object && field_object->classname) {
5678                         value = oilsFMGetString(
5679                                 field_object,
5680                                 (char*)oilsIDLFindPath("/%s/primarykey", field_object->classname)
5681                         );
5682                 } else if( field_object && JSON_BOOL == field_object->type ) {
5683                         if( jsonBoolIsTrue( field_object ) )
5684                                 value = strdup( "t" );
5685                         else
5686                                 value = strdup( "f" );
5687                 } else {
5688                         value = jsonObjectToSimpleString( field_object );
5689                         if( field_object && JSON_NUMBER == field_object->type )
5690                                 value_is_numeric = 1;
5691                 }
5692
5693                 osrfLogDebug( OSRF_LOG_MARK, "Updating %s object with %s = %s",
5694                                 osrfHashGet(meta, "fieldmapper"), field_name, value);
5695
5696                 if (!field_object || field_object->type == JSON_NULL) {
5697                         if ( !(!( strcmp( osrfHashGet(meta, "classname"), "au" ) )
5698                                         && !( strcmp( field_name, "passwd" ) )) ) { // arg at the special case!
5699                                 if (first) first = 0;
5700                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
5701                                 buffer_fadd( sql, " %s = NULL", field_name );
5702                         }
5703
5704                 } else if ( value_is_numeric || !strcmp( get_primitive( field_def ), "number") ) {
5705                         if (first) first = 0;
5706                         else OSRF_BUFFER_ADD_CHAR(sql, ',');
5707
5708                         const char* numtype = get_datatype( field_def );
5709                         if ( !strncmp( numtype, "INT", 3 ) ) {
5710                                 buffer_fadd( sql, " %s = %ld", field_name, atol(value) );
5711                         } else if ( !strcmp( numtype, "NUMERIC" ) ) {
5712                                 buffer_fadd( sql, " %s = %f", field_name, atof(value) );
5713                         } else {
5714                                 // Must really be intended as a string, so quote it
5715                                 if ( dbi_conn_quote_string(dbhandle, &value) ) {
5716                                         buffer_fadd( sql, " %s = %s", field_name, value );
5717                                 } else {
5718                                         osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]",
5719                                                 MODULENAME, value);
5720                                         osrfAppSessionStatus(
5721                                                 ctx->session,
5722                                                 OSRF_STATUS_INTERNALSERVERERROR,
5723                                                 "osrfMethodException",
5724                                                 ctx->request,
5725                                                 "Error quoting string -- please see the error log for more details"
5726                                         );
5727                                         free(value);
5728                                         free(id);
5729                                         osrfHashIteratorFree( field_itr );
5730                                         buffer_free(sql);
5731                                         *err = -1;
5732                                         return jsonNULL;
5733                                 }
5734                         }
5735
5736                         osrfLogDebug( OSRF_LOG_MARK, "%s is of type %s", field_name, numtype );
5737
5738                 } else {
5739                         if ( dbi_conn_quote_string(dbhandle, &value) ) {
5740                                 if (first) first = 0;
5741                                 else OSRF_BUFFER_ADD_CHAR(sql, ',');
5742                                 buffer_fadd( sql, " %s = %s", field_name, value );
5743
5744                         } else {
5745                                 osrfLogError(OSRF_LOG_MARK, "%s: Error quoting string [%s]", MODULENAME, value);
5746                                 osrfAppSessionStatus(
5747                                         ctx->session,
5748                                         OSRF_STATUS_INTERNALSERVERERROR,
5749                                         "osrfMethodException",
5750                                         ctx->request,
5751                                         "Error quoting string -- please see the error log for more details"
5752                                 );
5753                                 free(value);
5754                                 free(id);
5755                                 osrfHashIteratorFree( field_itr );
5756                                 buffer_free(sql);
5757                                 *err = -1;
5758                                 return jsonNULL;
5759                         }
5760                 }
5761
5762                 free(value);
5763
5764         } // end while
5765
5766         osrfHashIteratorFree( field_itr );
5767
5768         jsonObject* obj = jsonNewObject(id);
5769
5770         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
5771                 dbi_conn_quote_string(dbhandle, &id);
5772
5773         buffer_fadd( sql, " WHERE %s = %s;", pkey, id );
5774
5775         char* query = buffer_release(sql);
5776         osrfLogDebug(OSRF_LOG_MARK, "%s: Update SQL [%s]", MODULENAME, query);
5777
5778         dbi_result result = dbi_conn_query(dbhandle, query);
5779         free(query);
5780
5781         if (!result) {
5782                 jsonObjectFree(obj);
5783                 obj = jsonNewObject(NULL);
5784                 osrfLogError(
5785                         OSRF_LOG_MARK,
5786                         "%s ERROR updating %s object with %s = %s",
5787                         MODULENAME,
5788                         osrfHashGet(meta, "fieldmapper"),
5789                         pkey,
5790                         id
5791                 );
5792         }
5793
5794         free(id);
5795
5796         return obj;
5797 }
5798
5799 static jsonObject* doDelete(osrfMethodContext* ctx, int* err ) {
5800
5801         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
5802
5803         if( getXactId( ctx ) == NULL ) {
5804                 osrfAppSessionStatus(
5805                         ctx->session,
5806                         OSRF_STATUS_BADREQUEST,
5807                         "osrfMethodException",
5808                         ctx->request,
5809                         "No active transaction -- required for DELETE"
5810                 );
5811                 *err = -1;
5812                 return jsonNULL;
5813         }
5814
5815         // The following test is harmless but redundant.  If a class is
5816         // readonly, we don't register a delete method for it.
5817         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
5818                 osrfAppSessionStatus(
5819                         ctx->session,
5820                         OSRF_STATUS_BADREQUEST,
5821                         "osrfMethodException",
5822                         ctx->request,
5823                         "Cannot DELETE readonly class"
5824                 );
5825                 *err = -1;
5826                 return jsonNULL;
5827         }
5828
5829         dbhandle = writehandle;
5830
5831         jsonObject* obj;
5832
5833         char* pkey = osrfHashGet(meta, "primarykey");
5834
5835         int _obj_pos = 0;
5836 #ifdef PCRUD
5837                 _obj_pos = 1;
5838 #endif
5839
5840         char* id;
5841         if (jsonObjectGetIndex(ctx->params, _obj_pos)->classname) {
5842                 if (!verifyObjectClass(ctx, jsonObjectGetIndex( ctx->params, _obj_pos ))) {
5843                         *err = -1;
5844                         return jsonNULL;
5845                 }
5846
5847                 id = oilsFMGetString( jsonObjectGetIndex(ctx->params, _obj_pos), pkey );
5848         } else {
5849 #ifdef PCRUD
5850                 if (!verifyObjectPCRUD( ctx, NULL )) {
5851                         *err = -1;
5852                         return jsonNULL;
5853                 }
5854 #endif
5855                 id = jsonObjectToSimpleString(jsonObjectGetIndex(ctx->params, _obj_pos));
5856         }
5857
5858         osrfLogDebug(
5859                 OSRF_LOG_MARK,
5860                 "%s deleting %s object with %s = %s",
5861                 MODULENAME,
5862                 osrfHashGet(meta, "fieldmapper"),
5863                 pkey,
5864                 id
5865         );
5866
5867         obj = jsonNewObject(id);
5868
5869         if ( strcmp( get_primitive( osrfHashGet( osrfHashGet(meta, "fields"), pkey ) ), "number" ) )
5870                 dbi_conn_quote_string(writehandle, &id);
5871
5872         dbi_result result = dbi_conn_queryf(writehandle, "DELETE FROM %s WHERE %s = %s;",
5873                 osrfHashGet(meta, "tablename"), pkey, id);
5874
5875         if (!result) {
5876                 jsonObjectFree(obj);
5877                 obj = jsonNewObject(NULL);
5878                 osrfLogError(
5879                         OSRF_LOG_MARK,
5880                         "%s ERROR deleting %s object with %s = %s",
5881                         MODULENAME,
5882                         osrfHashGet(meta, "fieldmapper"),
5883                         pkey,
5884                         id
5885                 );
5886         }
5887
5888         free(id);
5889
5890         return obj;
5891 }
5892
5893 /**
5894         @brief Translate a row returned from the database into a jsonObject of type JSON_ARRAY.
5895         @param result An iterator for a result set; we only look at the current row.
5896         @param @meta Pointer to the class metadata for the core class.
5897         @return Pointer to the resulting jsonObject if successful; otherwise NULL.
5898
5899         If a column is not defined in the IDL, or if it has no array_position defined for it in
5900         the IDL, or if it is defined as virtual, ignore it.
5901
5902         Otherwise, translate the column value into a jsonObject of type JSON_NULL, JSON_NUMBER,
5903         or JSON_STRING.  Then insert this jsonObject into the JSON_ARRAY according to its
5904         array_position in the IDL.
5905
5906         A field defined in the IDL but not represented in the returned row will leave a hole
5907         in the JSON_ARRAY.  In effect it will be treated as a null value.
5908
5909         In the resulting JSON_ARRAY, the field values appear in the sequence defined by the IDL,
5910         regardless of their sequence in the SELECT statement.  The JSON_ARRAY is assigned the
5911         classname corresponding to the @a meta argument.
5912
5913         The calling code is responsible for freeing the the resulting jsonObject by calling
5914         jsonObjectFree().
5915 */
5916 static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* meta) {
5917         if(!(result && meta)) return jsonNULL;
5918
5919         jsonObject* object = jsonNewObjectType( JSON_ARRAY );
5920         jsonObjectSetClass(object, osrfHashGet(meta, "classname"));
5921         osrfLogInternal(OSRF_LOG_MARK, "Setting object class to %s ", object->classname);
5922
5923         osrfHash* fields = osrfHashGet(meta, "fields");
5924
5925         int columnIndex = 1;
5926         const char* columnName;
5927
5928         /* cycle through the columns in the row returned from the database */
5929         while( (columnName = dbi_result_get_field_name(result, columnIndex)) ) {
5930
5931                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
5932
5933                 int fmIndex = -1;  // Will be set to the IDL's sequence number for this field
5934
5935                 /* determine the field type and storage attributes */
5936                 unsigned short type = dbi_result_get_field_type_idx(result, columnIndex);
5937                 int attr            = dbi_result_get_field_attribs_idx(result, columnIndex);
5938
5939                 // Fetch the IDL's sequence number for the field.  If the field isn't in the IDL,
5940                 // or if it has no sequence number there, or if it's virtual, skip it.
5941                 osrfHash* _f = osrfHashGet( fields, (char*) columnName );
5942                 if( _f ) {
5943
5944                         if ( str_is_true( osrfHashGet(_f, "virtual") ) )
5945                                 continue;   // skip this column: IDL says it's virtual
5946
5947                         const char* pos = (char*)osrfHashGet(_f, "array_position");
5948                         if ( !pos )      // IDL has no sequence number for it.  This shouldn't happen,
5949                                 continue;    // since we assign sequence numbers dynamically as we load the IDL.
5950
5951                         fmIndex = atoi( pos );
5952                         osrfLogInternal(OSRF_LOG_MARK, "... Found column at position [%s]...", pos);
5953                 } else {
5954                         continue;     // This field is not defined in the IDL
5955                 }
5956
5957                 // Stuff the column value into a slot in the JSON_ARRAY, indexed according to the
5958                 // sequence number from the IDL (which is likely to be different from the sequence
5959                 // of columns in the SELECT clause).
5960                 if (dbi_result_field_is_null_idx(result, columnIndex)) {
5961                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(NULL) );
5962                 } else {
5963
5964                         switch( type ) {
5965
5966                                 case DBI_TYPE_INTEGER :
5967
5968                                         if( attr & DBI_INTEGER_SIZE8 )
5969                                                 jsonObjectSetIndex( object, fmIndex,
5970                                                         jsonNewNumberObject(dbi_result_get_longlong_idx(result, columnIndex)));
5971                                         else
5972                                                 jsonObjectSetIndex( object, fmIndex,
5973                                                         jsonNewNumberObject(dbi_result_get_int_idx(result, columnIndex)));
5974
5975                                         break;
5976
5977                                 case DBI_TYPE_DECIMAL :
5978                                         jsonObjectSetIndex( object, fmIndex,
5979                                                         jsonNewNumberObject(dbi_result_get_double_idx(result, columnIndex)));
5980                                         break;
5981
5982                                 case DBI_TYPE_STRING :
5983
5984                                         jsonObjectSetIndex(
5985                                                 object,
5986                                                 fmIndex,
5987                                                 jsonNewObject( dbi_result_get_string_idx(result, columnIndex) )
5988                                         );
5989
5990                                         break;
5991
5992                                 case DBI_TYPE_DATETIME : {
5993
5994                                         char dt_string[256] = "";
5995                                         struct tm gmdt;
5996
5997                                         // Fetch the date column as a time_t
5998                                         time_t _tmp_dt = dbi_result_get_datetime_idx(result, columnIndex);
5999
6000                                         // Translate the time_t to a human-readable string
6001                                         if (!(attr & DBI_DATETIME_DATE)) {
6002                                                 gmtime_r( &_tmp_dt, &gmdt );
6003                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
6004                                         } else if (!(attr & DBI_DATETIME_TIME)) {
6005                                                 localtime_r( &_tmp_dt, &gmdt );
6006                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
6007                                         } else {
6008                                                 localtime_r( &_tmp_dt, &gmdt );
6009                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
6010                                         }
6011
6012                                         jsonObjectSetIndex( object, fmIndex, jsonNewObject(dt_string) );
6013
6014                                         break;
6015                                 }
6016                                 case DBI_TYPE_BINARY :
6017                                         osrfLogError( OSRF_LOG_MARK,
6018                                                 "Can't do binary at column %s : index %d", columnName, columnIndex);
6019                         } // End switch
6020                 }
6021                 ++columnIndex;
6022         } // End while
6023
6024         return object;
6025 }
6026
6027 static jsonObject* oilsMakeJSONFromResult( dbi_result result ) {
6028         if(!result) return jsonNULL;
6029
6030         jsonObject* object = jsonNewObject(NULL);
6031
6032         time_t _tmp_dt;
6033         char dt_string[256];
6034         struct tm gmdt;
6035
6036         int fmIndex;
6037         int columnIndex = 1;
6038         int attr;
6039         unsigned short type;
6040         const char* columnName;
6041
6042         /* cycle through the column list */
6043         while( (columnName = dbi_result_get_field_name(result, columnIndex)) ) {
6044
6045                 osrfLogInternal(OSRF_LOG_MARK, "Looking for column named [%s]...", (char*)columnName);
6046
6047                 fmIndex = -1; // reset the position
6048
6049                 /* determine the field type and storage attributes */
6050                 type = dbi_result_get_field_type_idx(result, columnIndex);
6051                 attr = dbi_result_get_field_attribs_idx(result, columnIndex);
6052
6053                 if (dbi_result_field_is_null_idx(result, columnIndex)) {
6054                         jsonObjectSetKey( object, columnName, jsonNewObject(NULL) );
6055                 } else {
6056
6057                         switch( type ) {
6058
6059                                 case DBI_TYPE_INTEGER :
6060
6061                                         if( attr & DBI_INTEGER_SIZE8 )
6062                                                 jsonObjectSetKey( object, columnName,
6063                                                                 jsonNewNumberObject(dbi_result_get_longlong_idx(
6064                                                                                 result, columnIndex)) );
6065                                         else
6066                                                 jsonObjectSetKey( object, columnName,
6067                                                                 jsonNewNumberObject(dbi_result_get_int_idx(result, columnIndex)) );
6068                                         break;
6069
6070                                 case DBI_TYPE_DECIMAL :
6071                                         jsonObjectSetKey( object, columnName,
6072                                                         jsonNewNumberObject(dbi_result_get_double_idx(result, columnIndex)) );
6073                                         break;
6074
6075                                 case DBI_TYPE_STRING :
6076                                         jsonObjectSetKey( object, columnName,
6077                                                         jsonNewObject(dbi_result_get_string_idx(result, columnIndex)) );
6078                                         break;
6079
6080                                 case DBI_TYPE_DATETIME :
6081
6082                                         memset(dt_string, '\0', sizeof(dt_string));
6083                                         memset(&gmdt, '\0', sizeof(gmdt));
6084
6085                                         _tmp_dt = dbi_result_get_datetime_idx(result, columnIndex);
6086
6087
6088                                         if (!(attr & DBI_DATETIME_DATE)) {
6089                                                 gmtime_r( &_tmp_dt, &gmdt );
6090                                                 strftime(dt_string, sizeof(dt_string), "%T", &gmdt);
6091                                         } else if (!(attr & DBI_DATETIME_TIME)) {
6092                                                 localtime_r( &_tmp_dt, &gmdt );
6093                                                 strftime(dt_string, sizeof(dt_string), "%F", &gmdt);
6094                                         } else {
6095                                                 localtime_r( &_tmp_dt, &gmdt );
6096                                                 strftime(dt_string, sizeof(dt_string), "%FT%T%z", &gmdt);
6097                                         }
6098
6099                                         jsonObjectSetKey( object, columnName, jsonNewObject(dt_string) );
6100                                         break;
6101
6102                                 case DBI_TYPE_BINARY :
6103                                         osrfLogError( OSRF_LOG_MARK,
6104                                                 "Can't do binary at column %s : index %d", columnName, columnIndex );
6105                         }
6106                 }
6107                 ++columnIndex;
6108         } // end while loop traversing result
6109
6110         return object;
6111 }
6112
6113 // Interpret a string as true or false
6114 static int str_is_true( const char* str ) {
6115         if( NULL == str || strcasecmp( str, "true" ) )
6116                 return 0;
6117         else
6118                 return 1;
6119 }
6120
6121 // Interpret a jsonObject as true or false
6122 static int obj_is_true( const jsonObject* obj ) {
6123         if( !obj )
6124                 return 0;
6125         else switch( obj->type )
6126         {
6127                 case JSON_BOOL :
6128                         if( obj->value.b )
6129                                 return 1;
6130                         else
6131                                 return 0;
6132                 case JSON_STRING :
6133                         if( strcasecmp( obj->value.s, "true" ) )
6134                                 return 0;
6135                         else
6136                                 return 1;
6137                 case JSON_NUMBER :          // Support 1/0 for perl's sake
6138                         if( jsonObjectGetNumber( obj ) == 1.0 )
6139                                 return 1;
6140                         else
6141                                 return 0;
6142                 default :
6143                         return 0;
6144         }
6145 }
6146
6147 // Translate a numeric code into a text string identifying a type of
6148 // jsonObject.  To be used for building error messages.
6149 static const char* json_type( int code ) {
6150         switch ( code )
6151         {
6152                 case 0 :
6153                         return "JSON_HASH";
6154                 case 1 :
6155                         return "JSON_ARRAY";
6156                 case 2 :
6157                         return "JSON_STRING";
6158                 case 3 :
6159                         return "JSON_NUMBER";
6160                 case 4 :
6161                         return "JSON_NULL";
6162                 case 5 :
6163                         return "JSON_BOOL";
6164                 default :
6165                         return "(unrecognized)";
6166         }
6167 }
6168
6169 // Extract the "primitive" attribute from an IDL field definition.
6170 // If we haven't initialized the app, then we must be running in
6171 // some kind of testbed.  In that case, default to "string".
6172 static const char* get_primitive( osrfHash* field ) {
6173         const char* s = osrfHashGet( field, "primitive" );
6174         if( !s ) {
6175                 if( child_initialized )
6176                         osrfLogError(
6177                                 OSRF_LOG_MARK,
6178                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
6179                                 MODULENAME,
6180                                 osrfHashGet( field, "name" )
6181                         );
6182
6183                 s = "string";
6184         }
6185         return s;
6186 }
6187
6188 // Extract the "datatype" attribute from an IDL field definition.
6189 // If we haven't initialized the app, then we must be running in
6190 // some kind of testbed.  In that case, default to to NUMERIC,
6191 // since we look at the datatype only for numbers.
6192 static const char* get_datatype( osrfHash* field ) {
6193         const char* s = osrfHashGet( field, "datatype" );
6194         if( !s ) {
6195                 if( child_initialized )
6196                         osrfLogError(
6197                                 OSRF_LOG_MARK,
6198                                 "%s ERROR No \"datatype\" attribute for field \"%s\"",
6199                                 MODULENAME,
6200                                 osrfHashGet( field, "name" )
6201                         );
6202                 else
6203                         s = "NUMERIC";
6204         }
6205         return s;
6206 }
6207
6208 /*
6209 If the input string is potentially a valid SQL identifier, return 1.
6210 Otherwise return 0.
6211
6212 Purpose: to prevent certain kinds of SQL injection.  To that end we
6213 don't necessarily need to follow all the rules exactly, such as requiring
6214 that the first character not be a digit.
6215
6216 We allow leading and trailing white space.  In between, we do not allow
6217 punctuation (except for underscores and dollar signs), control
6218 characters, or embedded white space.
6219
6220 More pedantically we should allow quoted identifiers containing arbitrary
6221 characters, but for the foreseeable future such quoted identifiers are not
6222 likely to be an issue.
6223 */
6224 static int is_identifier( const char* s) {
6225         if( !s )
6226                 return 0;
6227
6228         // Skip leading white space
6229         while( isspace( (unsigned char) *s ) )
6230                 ++s;
6231
6232         if( !s )
6233                 return 0;   // Nothing but white space?  Not okay.
6234
6235         // Check each character until we reach white space or
6236         // end-of-string.  Letters, digits, underscores, and
6237         // dollar signs are okay. With the exception of periods
6238         // (as in schema.identifier), control characters and other
6239         // punctuation characters are not okay.  Anything else
6240         // is okay -- it could for example be part of a multibyte
6241         // UTF8 character such as a letter with diacritical marks,
6242         // and those are allowed.
6243         do {
6244                 if( isalnum( (unsigned char) *s )
6245                         || '.' == *s
6246                         || '_' == *s
6247                         || '$' == *s )
6248                         ;  // Fine; keep going
6249                 else if(   ispunct( (unsigned char) *s )
6250                                 || iscntrl( (unsigned char) *s ) )
6251                         return 0;
6252                         ++s;
6253         } while( *s && ! isspace( (unsigned char) *s ) );
6254
6255         // If we found any white space in the above loop,
6256         // the rest had better be all white space.
6257
6258         while( isspace( (unsigned char) *s ) )
6259                 ++s;
6260
6261         if( *s )
6262                 return 0;   // White space was embedded within non-white space
6263
6264         return 1;
6265 }
6266
6267 /*
6268 Determine whether to accept a character string as a comparison operator.
6269 Return 1 if it's good, or 0 if it's bad.
6270
6271 We don't validate it for real.  We just make sure that it doesn't contain
6272 any semicolons or white space (with special exceptions for a few specific
6273 operators).   The idea is to block certain kinds of SQL injection.  If it
6274 has no semicolons or white space but it's still not a valid operator, then
6275 the database will complain.
6276
6277 Another approach would be to compare the string against a short list of
6278 approved operators.  We don't do that because we want to allow custom
6279 operators like ">100*", which would be difficult or impossible to
6280 express otherwise in a JSON query.
6281 */
6282 static int is_good_operator( const char* op ) {
6283         if( !op ) return 0;   // Sanity check
6284
6285         const char* s = op;
6286         while( *s ) {
6287                 if( isspace( (unsigned char) *s ) ) {
6288                         // Special exceptions for SIMILAR TO, IS DISTINCT FROM,
6289                         // and IS NOT DISTINCT FROM.
6290                         if( !strcasecmp( op, "similar to" ) )
6291                                 return 1;
6292                         else if( !strcasecmp( op, "is distinct from" ) )
6293                                 return 1;
6294                         else if( !strcasecmp( op, "is not distinct from" ) )
6295                                 return 1;
6296                         else
6297                                 return 0;
6298                 }
6299                 else if( ';' == *s )
6300                         return 0;
6301                 ++s;
6302         }
6303         return 1;
6304 }
6305
6306 /**
6307         @name Query Frame Management
6308
6309         The following machinery supports a stack of query frames for use by SELECT().
6310
6311         A query frame caches information about one level of a SELECT query.  When we enter
6312         a subquery, we push another query frame onto the stack, and pop it off when we leave.
6313
6314         The query frame stores information about the core class, and about any joined classes
6315         in the FROM clause.
6316
6317         The main purpose is to map table aliases to classes and tables, so that a query can
6318         join to the same table more than once.  A secondary goal is to reduce the number of
6319         lookups in the IDL by caching the results.
6320 */
6321 /*@{*/
6322
6323 #define STATIC_CLASS_INFO_COUNT 3
6324
6325 static ClassInfo static_class_info[ STATIC_CLASS_INFO_COUNT ];
6326
6327 /**
6328         @brief Allocate a ClassInfo as raw memory.
6329         @return Pointer to the newly allocated ClassInfo.
6330
6331         Except for the in_use flag, which is used only by the allocation and deallocation
6332         logic, we don't initialize the ClassInfo here.
6333 */
6334 static ClassInfo* allocate_class_info( void ) {
6335         // In order to reduce the number of mallocs and frees, we return a static
6336         // instance of ClassInfo, if we can find one that we're not already using.
6337         // We rely on the fact that the compiler will implicitly initialize the
6338         // static instances so that in_use == 0.
6339
6340         int i;
6341         for( i = 0; i < STATIC_CLASS_INFO_COUNT; ++i ) {
6342                 if( ! static_class_info[ i ].in_use ) {
6343                         static_class_info[ i ].in_use = 1;
6344                         return static_class_info + i;
6345                 }
6346         }
6347
6348         // The static ones are all in use.  Malloc one.
6349
6350         return safe_malloc( sizeof( ClassInfo ) );
6351 }
6352
6353 /**
6354         @brief Free any malloc'd memory owned by a ClassInfo, returning it to a pristine state.
6355         @param info Pointer to the ClassInfo to be cleared.
6356 */
6357 static void clear_class_info( ClassInfo* info ) {
6358         // Sanity check
6359         if( ! info )
6360                 return;
6361
6362         // Free any malloc'd strings
6363
6364         if( info->alias != info->alias_store )
6365                 free( info->alias );
6366
6367         if( info->class_name != info->class_name_store )
6368                 free( info->class_name );
6369
6370         free( info->source_def );
6371
6372         info->alias = info->class_name = info->source_def = NULL;
6373         info->next = NULL;
6374 }
6375
6376 /**
6377         @brief Free a ClassInfo and everything it owns.
6378         @param info Pointer to the ClassInfo to be freed.
6379 */
6380 static void free_class_info( ClassInfo* info ) {
6381         // Sanity check
6382         if( ! info )
6383                 return;
6384
6385         clear_class_info( info );
6386
6387         // If it's one of the static instances, just mark it as not in use
6388
6389         int i;
6390         for( i = 0; i < STATIC_CLASS_INFO_COUNT; ++i ) {
6391                 if( info == static_class_info + i ) {
6392                         static_class_info[ i ].in_use = 0;
6393                         return;
6394                 }
6395         }
6396
6397         // Otherwise it must have been malloc'd, so free it
6398
6399         free( info );
6400 }
6401
6402 /**
6403         @brief Populate an already-allocated ClassInfo.
6404         @param info Pointer to the ClassInfo to be populated.
6405         @param alias Alias for the class.  If it is NULL, or an empty string, use the class
6406         name for an alias.
6407         @param class Name of the class.
6408         @return Zero if successful, or 1 if not.
6409
6410         Populate the ClassInfo with copies of the alias and class name, and with pointers to
6411         the relevant portions of the IDL for the specified class.
6412 */
6413 static int build_class_info( ClassInfo* info, const char* alias, const char* class ) {
6414         // Sanity checks
6415         if( ! info ){
6416                 osrfLogError( OSRF_LOG_MARK,
6417                                           "%s ERROR: No ClassInfo available to populate", MODULENAME );
6418                 info->alias = info->class_name = info->source_def = NULL;
6419                 info->class_def = info->fields = info->links = NULL;
6420                 return 1;
6421         }
6422
6423         if( ! class ) {
6424                 osrfLogError( OSRF_LOG_MARK,
6425                                           "%s ERROR: No class name provided for lookup", MODULENAME );
6426                 info->alias = info->class_name = info->source_def = NULL;
6427                 info->class_def = info->fields = info->links = NULL;
6428                 return 1;
6429         }
6430
6431         // Alias defaults to class name if not supplied
6432         if( ! alias || ! alias[ 0 ] )
6433                 alias = class;
6434
6435         // Look up class info in the IDL
6436         osrfHash* class_def = osrfHashGet( oilsIDL(), class );
6437         if( ! class_def ) {
6438                 osrfLogError( OSRF_LOG_MARK,
6439                                           "%s ERROR: Class %s not defined in IDL", MODULENAME, class );
6440                 info->alias = info->class_name = info->source_def = NULL;
6441                 info->class_def = info->fields = info->links = NULL;
6442                 return 1;
6443         } else if( str_is_true( osrfHashGet( class_def, "virtual" ) ) ) {
6444                 osrfLogError( OSRF_LOG_MARK,
6445                                           "%s ERROR: Class %s is defined as virtual", MODULENAME, class );
6446                 info->alias = info->class_name = info->source_def = NULL;
6447                 info->class_def = info->fields = info->links = NULL;
6448                 return 1;
6449         }
6450
6451         osrfHash* links = osrfHashGet( class_def, "links" );
6452         if( ! links ) {
6453                 osrfLogError( OSRF_LOG_MARK,
6454                                           "%s ERROR: No links defined in IDL for class %s", MODULENAME, class );
6455                 info->alias = info->class_name = info->source_def = NULL;
6456                 info->class_def = info->fields = info->links = NULL;
6457                 return 1;
6458         }
6459
6460         osrfHash* fields = osrfHashGet( class_def, "fields" );
6461         if( ! fields ) {
6462                 osrfLogError( OSRF_LOG_MARK,
6463                                           "%s ERROR: No fields defined in IDL for class %s", MODULENAME, class );
6464                 info->alias = info->class_name = info->source_def = NULL;
6465                 info->class_def = info->fields = info->links = NULL;
6466                 return 1;
6467         }
6468
6469         char* source_def = getRelation( class_def );
6470         if( ! source_def )
6471                 return 1;
6472
6473         // We got everything we need, so populate the ClassInfo
6474         if( strlen( alias ) > ALIAS_STORE_SIZE )
6475                 info->alias = strdup( alias );
6476         else {
6477                 strcpy( info->alias_store, alias );
6478                 info->alias = info->alias_store;
6479         }
6480
6481         if( strlen( class ) > CLASS_NAME_STORE_SIZE )
6482                 info->class_name = strdup( class );
6483         else {
6484                 strcpy( info->class_name_store, class );
6485                 info->class_name = info->class_name_store;
6486         }
6487
6488         info->source_def = source_def;
6489
6490         info->class_def = class_def;
6491         info->links     = links;
6492         info->fields    = fields;
6493
6494         return 0;
6495 }
6496
6497 #define STATIC_FRAME_COUNT 3
6498
6499 static QueryFrame static_frame[ STATIC_FRAME_COUNT ];
6500
6501 /**
6502         @brief Allocate a QueryFrame as raw memory.
6503         @return Pointer to the newly allocated QueryFrame.
6504
6505         Except for the in_use flag, which is used only by the allocation and deallocation
6506         logic, we don't initialize the QueryFrame here.
6507 */
6508 static QueryFrame* allocate_frame( void ) {
6509         // In order to reduce the number of mallocs and frees, we return a static
6510         // instance of QueryFrame, if we can find one that we're not already using.
6511         // We rely on the fact that the compiler will implicitly initialize the
6512         // static instances so that in_use == 0.
6513
6514         int i;
6515         for( i = 0; i < STATIC_FRAME_COUNT; ++i ) {
6516                 if( ! static_frame[ i ].in_use ) {
6517                         static_frame[ i ].in_use = 1;
6518                         return static_frame + i;
6519                 }
6520         }
6521
6522         // The static ones are all in use.  Malloc one.
6523
6524         return safe_malloc( sizeof( QueryFrame ) );
6525 }
6526
6527 /**
6528         @brief Free a QueryFrame, and all the memory it owns.
6529         @param frame Pointer to the QueryFrame to be freed.
6530 */
6531 static void free_query_frame( QueryFrame* frame ) {
6532         // Sanity check
6533         if( ! frame )
6534                 return;
6535
6536         clear_class_info( &frame->core );
6537
6538         // Free the join list
6539         ClassInfo* temp;
6540         ClassInfo* info = frame->join_list;
6541         while( info ) {
6542                 temp = info->next;
6543                 free_class_info( info );
6544                 info = temp;
6545         }
6546
6547         frame->join_list = NULL;
6548         frame->next = NULL;
6549
6550         // If the frame is a static instance, just mark it as unused
6551         int i;
6552         for( i = 0; i < STATIC_FRAME_COUNT; ++i ) {
6553                 if( frame == static_frame + i ) {
6554                         static_frame[ i ].in_use = 0;
6555                         return;
6556                 }
6557         }
6558
6559         // Otherwise it must have been malloc'd, so free it
6560
6561         free( frame );
6562 }
6563
6564 /**
6565         @brief Search a given QueryFrame for a specified alias.
6566         @param frame Pointer to the QueryFrame to be searched.
6567         @param target The alias for which to search.
6568         @return Pointer to the ClassInfo for the specified alias, if found; otherwise NULL.
6569 */
6570 static ClassInfo* search_alias_in_frame( QueryFrame* frame, const char* target ) {
6571         if( ! frame || ! target ) {
6572                 return NULL;
6573         }
6574
6575         ClassInfo* found_class = NULL;
6576
6577         if( !strcmp( target, frame->core.alias ) )
6578                 return &(frame->core);
6579         else {
6580                 ClassInfo* curr_class = frame->join_list;
6581                 while( curr_class ) {
6582                         if( strcmp( target, curr_class->alias ) )
6583                                 curr_class = curr_class->next;
6584                         else {
6585                                 found_class = curr_class;
6586                                 break;
6587                         }
6588                 }
6589         }
6590
6591         return found_class;
6592 }
6593
6594 /**
6595         @brief Push a new (blank) QueryFrame onto the stack.
6596 */
6597 static void push_query_frame( void ) {
6598         QueryFrame* frame = allocate_frame();
6599         frame->join_list = NULL;
6600         frame->next = curr_query;
6601
6602         // Initialize the ClassInfo for the core class
6603         ClassInfo* core = &frame->core;
6604         core->alias = core->class_name = core->source_def = NULL;
6605         core->class_def = core->fields = core->links = NULL;
6606
6607         curr_query = frame;
6608 }
6609
6610 /**
6611         @brief Pop a QueryFrame off the stack and destroy it.
6612 */
6613 static void pop_query_frame( void ) {
6614         // Sanity check
6615         if( ! curr_query )
6616                 return;
6617
6618         QueryFrame* popped = curr_query;
6619         curr_query = popped->next;
6620
6621         free_query_frame( popped );
6622 }
6623
6624 /**
6625         @brief Populate the ClassInfo for the core class.
6626         @param alias Alias for the core class.  If it is NULL or an empty string, we use the
6627         class name as an alias.
6628         @param class_name Name of the core class.
6629         @return Zero if successful, or 1 if not.
6630
6631         Populate the ClassInfo of the core class with copies of the alias and class name, and
6632         with pointers to the relevant portions of the IDL for the core class.
6633 */
6634 static int add_query_core( const char* alias, const char* class_name ) {
6635
6636         // Sanity checks
6637         if( ! curr_query ) {
6638                 osrfLogError( OSRF_LOG_MARK,
6639                                           "%s ERROR: No QueryFrame available for class %s", MODULENAME, class_name );
6640                 return 1;
6641         } else if( curr_query->core.alias ) {
6642                 osrfLogError( OSRF_LOG_MARK,
6643                                           "%s ERROR: Core class %s already populated as %s",
6644                                           MODULENAME, curr_query->core.class_name, curr_query->core.alias );
6645                 return 1;
6646         }
6647
6648         build_class_info( &curr_query->core, alias, class_name );
6649         if( curr_query->core.alias )
6650                 return 0;
6651         else {
6652                 osrfLogError( OSRF_LOG_MARK,
6653                                           "%s ERROR: Unable to look up core class %s", MODULENAME, class_name );
6654                 return 1;
6655         }
6656 }
6657
6658 /**
6659         @brief Search the current QueryFrame for a specified alias.
6660         @param target The alias for which to search.
6661         @return A pointer to the corresponding ClassInfo, if found; otherwise NULL.
6662 */
6663 static inline ClassInfo* search_alias( const char* target ) {
6664         return search_alias_in_frame( curr_query, target );
6665 }
6666
6667 /**
6668         @brief Search all levels of query for a specified alias, starting with the current query.
6669         @param target The alias for which to search.
6670         @return A pointer to the corresponding ClassInfo, if found; otherwise NULL.
6671 */
6672 static ClassInfo* search_all_alias( const char* target ) {
6673         ClassInfo* found_class = NULL;
6674         QueryFrame* curr_frame = curr_query;
6675
6676         while( curr_frame ) {
6677                 if(( found_class = search_alias_in_frame( curr_frame, target ) ))
6678                         break;
6679                 else
6680                         curr_frame = curr_frame->next;
6681         }
6682
6683         return found_class;
6684 }
6685
6686 /**
6687         @brief Add a class to the list of classes joined to the current query.
6688         @param alias Alias of the class to be added.  If it is NULL or an empty string, we use
6689         the class name as an alias.
6690         @param classname The name of the class to be added.
6691         @return A pointer to the ClassInfo for the added class, if successful; otherwise NULL.
6692 */
6693 static ClassInfo* add_joined_class( const char* alias, const char* classname ) {
6694
6695         if( ! classname || ! *classname ) {    // sanity check
6696                 osrfLogError( OSRF_LOG_MARK, "Can't join a class with no class name" );
6697                 return NULL;
6698         }
6699
6700         if( ! alias )
6701                 alias = classname;
6702
6703         const ClassInfo* conflict = search_alias( alias );
6704         if( conflict ) {
6705                 osrfLogError( OSRF_LOG_MARK,
6706                                           "%s ERROR: Table alias \"%s\" conflicts with class \"%s\"",
6707                                           MODULENAME, alias, conflict->class_name );
6708                 return NULL;
6709         }
6710
6711         ClassInfo* info = allocate_class_info();
6712
6713         if( build_class_info( info, alias, classname ) ) {
6714                 free_class_info( info );
6715                 return NULL;
6716         }
6717
6718         // Add the new ClassInfo to the join list of the current QueryFrame
6719         info->next = curr_query->join_list;
6720         curr_query->join_list = info;
6721
6722         return info;
6723 }
6724
6725 /**
6726         @brief Destroy all nodes on the query stack.
6727 */
6728 static void clear_query_stack( void ) {
6729         while( curr_query )
6730                 pop_query_frame();
6731 }
6732
6733 /*@}*/