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

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