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 void pop_query_frame( void );
 106 static void push_query_frame( void );
 107 static int add_query_core( const char* alias, const char* class_name );
 108 static inline ClassInfo* search_alias( const char* target );
 109 static ClassInfo* search_all_alias( const char* target );
 110 static ClassInfo* add_joined_class( const char* alias, const char* classname );
 111 static void clear_query_stack( void );
 112
 113 static const jsonObject* verifyUserPCRUD( osrfMethodContext* );
 114 static int verifyObjectPCRUD( osrfMethodContext*, const jsonObject* );
 115 static const char* org_tree_root( osrfMethodContext* ctx );
 116 static jsonObject* single_hash( const char* key, const char* value );
 117
 118 static int child_initialized = 0;   /* boolean */
 119
 120 static dbi_conn writehandle; /* our MASTER db connection */
 121 static dbi_conn dbhandle; /* our CURRENT db connection */
 122 //static osrfHash * readHandles;
 123
 124 // The following points to the top of a stack of QueryFrames.  It's a little
 125 // confusing because the top level of the query is at the bottom of the stack.
 126 static QueryFrame* curr_query = NULL;
 127
 128 static dbi_conn writehandle; /* our MASTER db connection */
 129 static dbi_conn dbhandle; /* our CURRENT db connection */
 130 //static osrfHash * readHandles;
 131
 132 static int max_flesh_depth = 100;
 133
 134 static int enforce_pcrud = 0;     // Boolean
 135 static char* modulename = NULL;
 136
 137 /**
 138         @brief Connect to the database.
 139         @return A database connection if successful, or NULL if not.
 140 */
 141 dbi_conn oilsConnectDB( const char* mod_name ) {
 142
 143         osrfLogDebug( OSRF_LOG_MARK, "Attempting to initialize libdbi..." );
 144         if( dbi_initialize( NULL ) == -1 ) {
 145                 osrfLogError( OSRF_LOG_MARK, "Unable to initialize libdbi" );
 146                 return NULL;
 147         } else
 148                 osrfLogDebug( OSRF_LOG_MARK, "... libdbi initialized." );
 149
 150         char* driver = osrf_settings_host_value( "/apps/%s/app_settings/driver", mod_name );
 151         char* user   = osrf_settings_host_value( "/apps/%s/app_settings/database/user", mod_name );
 152         char* host   = osrf_settings_host_value( "/apps/%s/app_settings/database/host", mod_name );
 153         char* port   = osrf_settings_host_value( "/apps/%s/app_settings/database/port", mod_name );
 154         char* db     = osrf_settings_host_value( "/apps/%s/app_settings/database/db", mod_name );
 155         char* pw     = osrf_settings_host_value( "/apps/%s/app_settings/database/pw", mod_name );
 156
 157         osrfLogDebug( OSRF_LOG_MARK, "Attempting to load the database driver [%s]...", driver );
 158         dbi_conn handle = dbi_conn_new( driver );
 159
 160         if( !handle ) {
 161                 osrfLogError( OSRF_LOG_MARK, "Error loading database driver [%s]", driver );
 162                 return NULL;
 163         }
 164         osrfLogDebug( OSRF_LOG_MARK, "Database driver [%s] seems OK", driver );
 165
 166         osrfLogInfo(OSRF_LOG_MARK, "%s connecting to database.  host=%s, "
 167                 "port=%s, user=%s, db=%s", mod_name, host, port, user, db );
 168
 169         if( host ) dbi_conn_set_option( handle, "host", host );
 170         if( port ) dbi_conn_set_option_numeric( handle, "port", atoi( port ));
 171         if( user ) dbi_conn_set_option( handle, "username", user );
 172         if( pw )   dbi_conn_set_option( handle, "password", pw );
 173         if( db )   dbi_conn_set_option( handle, "dbname", db );
 174
 175         free( user );
 176         free( host );
 177         free( port );
 178         free( db );
 179         free( pw );
 180
 181         if( dbi_conn_connect( handle ) < 0 ) {
 182                 sleep( 1 );
 183                 if( dbi_conn_connect( handle ) < 0 ) {
 184                         const char* errmsg;
 185                         dbi_conn_error( handle, &errmsg );
 186                         osrfLogError( OSRF_LOG_MARK, "Error connecting to database: %s", errmsg );
 187                         return NULL;
 188                 }
 189         }
 190
 191         osrfLogInfo( OSRF_LOG_MARK, "%s successfully connected to the database", mod_name );
 192
 193         return handle;
 194 }
 195
 196 /**
 197         @brief Select some options.
 198         @param module_name: Name of the server.
 199         @param do_pcrud: Boolean.  True if we are to enforce PCRUD permissions.
 200
 201         This source file is used (at this writing) to implement three different servers:
 202         - open-ils.reporter-store
 203         - open-ils.pcrud
 204         - open-ils.cstore
 205
 206         These servers behave mostly the same, but they implement different combinations of
 207         methods, and open-ils.pcrud enforces a permissions scheme that the other two don't.
 208
 209         Here we use the server name in messages to identify which kind of server issued them.
 210         We use do_crud as a boolean to control whether or not to enforce the permissions scheme.
 211 */
 212 void oilsSetSQLOptions( const char* module_name, int do_pcrud, int flesh_depth ) {
 213         if( !module_name )
 214                 module_name = "open-ils.cstore";   // bulletproofing with a default
 215
 216         if( modulename )
 217                 free( modulename );
 218
 219         modulename = strdup( module_name );
 220         enforce_pcrud = do_pcrud;
 221         max_flesh_depth = flesh_depth;
 222 }
 223
 224 /**
 225         @brief Install a database connection.
 226         @param conn Pointer to a database connection.
 227
 228         In some contexts, @a conn may merely provide a driver so that we can process strings
 229         properly, without providing an open database connection.
 230 */
 231 void oilsSetDBConnection( dbi_conn conn ) {
 232         dbhandle = writehandle = conn;
 233 }
 234
 235 /**
 236         @brief Get a table name, view name, or subquery for use in a FROM clause.
 237         @param class Pointer to the IDL class entry.
 238         @return A table name, a view name, or a subquery in parentheses.
 239
 240         In some cases the IDL defines a class, not with a table name or a view name, but with
 241         a SELECT statement, which may be used as a subquery.
 242 */
 243 char* oilsGetRelation( osrfHash* classdef ) {
 244
 245         char* source_def = NULL;
 246         const char* tabledef = osrfHashGet( classdef, "tablename" );
 247
 248         if( tabledef ) {
 249                 source_def = strdup( tabledef );   // Return the name of a table or view
 250         } else {
 251                 tabledef = osrfHashGet( classdef, "source_definition" );
 252                 if( tabledef ) {
 253                         // Return a subquery, enclosed in parentheses
 254                         source_def = safe_malloc( strlen( tabledef ) + 3 );
 255                         source_def[ 0 ] = '(';
 256                         strcpy( source_def + 1, tabledef );
 257                         strcat( source_def, ")" );
 258                 } else {
 259                         // Not found: return an error
 260                         const char* classname = osrfHashGet( classdef, "classname" );
 261                         if( !classname )
 262                                 classname = "???";
 263                         osrfLogError(
 264                                 OSRF_LOG_MARK,
 265                                 "%s ERROR No tablename or source_definition for class \"%s\"",
 266                                 modulename,
 267                                 classname
 268                         );
 269                 }
 270         }
 271
 272         return source_def;
 273 }
 274
 275 /**
 276         @brief Add datatypes from the database to the fields in the IDL.
 277         @param handle Handle for a database connection
 278         @return Zero if successful, or 1 upon error.
 279
 280         For each relevant class in the IDL: ask the database for the datatype of every field.
 281         In particular, determine which fields are text fields and which fields are numeric
 282         fields, so that we know whether to enclose their values in quotes.
 283 */
 284 int oilsExtendIDL( dbi_conn handle ) {
 285         osrfHashIterator* class_itr = osrfNewHashIterator( oilsIDL() );
 286         osrfHash* class = NULL;
 287         growing_buffer* query_buf = buffer_init( 64 );
 288         int results_found = 0;   // boolean
 289
 290         // For each class in the IDL...
 291         while( (class = osrfHashIteratorNext( class_itr ) ) ) {
 292                 const char* classname = osrfHashIteratorKey( class_itr );
 293                 osrfHash* fields = osrfHashGet( class, "fields" );
 294
 295                 // If the class is virtual, ignore it
 296                 if( str_is_true( osrfHashGet(class, "virtual") ) ) {
 297                         osrfLogDebug(OSRF_LOG_MARK, "Class %s is virtual, skipping", classname );
 298                         continue;
 299                 }
 300
 301                 char* tabledef = oilsGetRelation( class );
 302                 if( !tabledef )
 303                         continue;   // No such relation -- a query of it would be doomed to failure
 304
 305                 buffer_reset( query_buf );
 306                 buffer_fadd( query_buf, "SELECT * FROM %s AS x WHERE 1=0;", tabledef );
 307
 308                 free(tabledef );
 309
 310                 osrfLogDebug( OSRF_LOG_MARK, "%s Investigatory SQL = %s",
 311                                 modulename, OSRF_BUFFER_C_STR( query_buf ) );
 312
 313                 dbi_result result = dbi_conn_query( handle, OSRF_BUFFER_C_STR( query_buf ) );
 314                 if( result ) {
 315
 316                         results_found = 1;
 317                         int columnIndex = 1;
 318                         const char* columnName;
 319                         while( (columnName = dbi_result_get_field_name(result, columnIndex)) ) {
 320
 321                                 osrfLogInternal( OSRF_LOG_MARK, "Looking for column named [%s]...",
 322                                                 columnName );
 323
 324                                 /* fetch the fieldmapper index */
 325                                 osrfHash* _f = osrfHashGet(fields, columnName);
 326                                 if( _f ) {
 327
 328                                         osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", columnName);
 329
 330                                         /* determine the field type and storage attributes */
 331
 332                                         switch( dbi_result_get_field_type_idx( result, columnIndex )) {
 333
 334                                                 case DBI_TYPE_INTEGER : {
 335
 336                                                         if( !osrfHashGet(_f, "primitive") )
 337                                                                 osrfHashSet(_f, "number", "primitive");
 338
 339                                                         int attr = dbi_result_get_field_attribs_idx( result, columnIndex );
 340                                                         if( attr & DBI_INTEGER_SIZE8 )
 341                                                                 osrfHashSet( _f, "INT8", "datatype" );
 342                                                         else
 343                                                                 osrfHashSet( _f, "INT", "datatype" );
 344                                                         break;
 345                                                 }
 346                                                 case DBI_TYPE_DECIMAL :
 347                                                         if( !osrfHashGet( _f, "primitive" ))
 348                                                                 osrfHashSet( _f, "number", "primitive" );
 349
 350                                                         osrfHashSet( _f, "NUMERIC", "datatype" );
 351                                                         break;
 352
 353                                                 case DBI_TYPE_STRING :
 354                                                         if( !osrfHashGet( _f, "primitive" ))
 355                                                                 osrfHashSet( _f, "string", "primitive" );
 356
 357                                                         osrfHashSet( _f,"TEXT", "datatype" );
 358                                                         break;
 359
 360                                                 case DBI_TYPE_DATETIME :
 361                                                         if( !osrfHashGet( _f, "primitive" ))
 362                                                                 osrfHashSet( _f, "string", "primitive" );
 363
 364                                                         osrfHashSet( _f, "TIMESTAMP", "datatype" );
 365                                                         break;
 366
 367                                                 case DBI_TYPE_BINARY :
 368                                                         if( !osrfHashGet( _f, "primitive" ))
 369                                                                 osrfHashSet( _f, "string", "primitive" );
 370
 371                                                         osrfHashSet( _f, "BYTEA", "datatype" );
 372                                         }
 373
 374                                         osrfLogDebug(
 375                                                 OSRF_LOG_MARK,
 376                                                 "Setting [%s] to primitive [%s] and datatype [%s]...",
 377                                                 columnName,
 378                                                 osrfHashGet( _f, "primitive" ),
 379                                                 osrfHashGet( _f, "datatype" )
 380                                         );
 381                                 }
 382                                 ++columnIndex;
 383                         } // end while loop for traversing columns of result
 384                         dbi_result_free( result  );
 385                 } else {
 386                         osrfLogDebug( OSRF_LOG_MARK, "No data found for class [%s]...", classname );
 387                 }
 388         } // end for each class in IDL
 389
 390         buffer_free( query_buf );
 391         osrfHashIteratorFree( class_itr );
 392         child_initialized = 1;
 393
 394         if( !results_found ) {
 395                 osrfLogError( OSRF_LOG_MARK,
 396                         "No results found for any class -- bad database connection?" );
 397                 return 1;
 398         }
 399         else
 400                 return 0;
 401 }
 402
 403 /**
 404         @brief Free an osrfHash that stores a transaction ID.
 405         @param blob A pointer to the osrfHash to be freed, cast to a void pointer.
 406
 407         This function is a callback, to be called by the application session when it ends.
 408         The application session stores the osrfHash via an opaque pointer.
 409
 410         If the osrfHash contains an entry for the key "xact_id", it means that an
 411         uncommitted transaction is pending.  Roll it back.
 412 */
 413 void userDataFree( void* blob ) {
 414         osrfHash* hash = (osrfHash*) blob;
 415         if( osrfHashGet( hash, "xact_id" ) && writehandle )
 416                 dbi_conn_query( writehandle, "ROLLBACK;" );
 417
 418         osrfHashFree( hash );
 419 }
 420
 421 /**
 422         @name Managing session data
 423         @brief Maintain data stored via the userData pointer of the application session.
 424
 425         Currently, session-level data is stored in an osrfHash.  Other arrangements are
 426         possible, and some would be more efficient.  The application session calls a
 427         callback function to free userData before terminating.
 428
 429         Currently, the only data we store at the session level is the transaction id.  By this
 430         means we can ensure that any pending transactions are rolled back before the application
 431         session terminates.
 432 */
 433 /*@{*/
 434
 435 /**
 436         @brief Free an item in the application session's userData.
 437         @param key The name of a key for an osrfHash.
 438         @param item An opaque pointer to the item associated with the key.
 439
 440         We store an osrfHash as userData with the application session, and arrange (by
 441         installing userDataFree() as a different callback) for the session to free that
 442         osrfHash before terminating.
 443
 444         This function is a callback for freeing items in the osrfHash.  Currently we store
 445         two things:
 446         - Transaction id of a pending transaction; a character string.  Key: "xact_id".
 447         - Authkey; a character string.  Key: "authkey".
 448         - User object from the authentication server; a jsonObject.  Key: "user_login".
 449
 450         If we ever store anything else in userData, we will need to revisit this function so
 451         that it will free whatever else needs freeing.
 452 */
 453 static void sessionDataFree( char* key, void* item ) {
 454         if( !strcmp( key, "xact_id" )
 455              || !strcmp( key, "authkey" ) ) {
 456                 free( item );
 457         } else if( !strcmp( key, "user_login" ) )
 458                 jsonObjectFree( (jsonObject*) item );
 459 }
 460
 461 /**
 462         @brief Save a transaction id.
 463         @param ctx Pointer to the method context.
 464
 465         Save the session_id of the current application session as a transaction id.
 466 */
 467 static void setXactId( osrfMethodContext* ctx ) {
 468         if( ctx && ctx->session ) {
 469                 osrfAppSession* session = ctx->session;
 470
 471                 osrfHash* cache = session->userData;
 472
 473                 // If the session doesn't already have a hash, create one.  Make sure
 474                 // that the application session frees the hash when it terminates.
 475                 if( NULL == cache ) {
 476                         session->userData = cache = osrfNewHash();
 477                         osrfHashSetCallback( cache, &sessionDataFree );
 478                         ctx->session->userDataFree = &userDataFree;
 479                 }
 480
 481                 // Save the transaction id in the hash, with the key "xact_id"
 482                 osrfHashSet( cache, strdup( session->session_id ), "xact_id" );
 483         }
 484 }
 485
 486 /**
 487         @brief Get the transaction ID for the current transaction, if any.
 488         @param ctx Pointer to the method context.
 489         @return Pointer to the transaction ID.
 490
 491         The return value points to an internal buffer, and will become invalid upon issuing
 492         a commit or rollback.
 493 */
 494 static inline const char* getXactId( osrfMethodContext* ctx ) {
 495         if( ctx && ctx->session && ctx->session->userData )
 496                 return osrfHashGet( (osrfHash*) ctx->session->userData, "xact_id" );
 497         else
 498                 return NULL;
 499 }
 500
 501 /**
 502         @brief Clear the current transaction id.
 503         @param ctx Pointer to the method context.
 504 */
 505 static inline void clearXactId( osrfMethodContext* ctx ) {
 506         if( ctx && ctx->session && ctx->session->userData )
 507                 osrfHashRemove( ctx->session->userData, "xact_id" );
 508 }
 509 /*@}*/
 510
 511 /**
 512         @brief Save the user's login in the userData for the current application session.
 513         @param ctx Pointer to the method context.
 514         @param user_login Pointer to the user login object to be cached (we cache the original,
 515         not a copy of it).
 516
 517         If @a user_login is NULL, remove the user login if one is already cached.
 518 */
 519 static void setUserLogin( osrfMethodContext* ctx, jsonObject* user_login ) {
 520         if( ctx && ctx->session ) {
 521                 osrfAppSession* session = ctx->session;
 522
 523                 osrfHash* cache = session->userData;
 524
 525                 // If the session doesn't already have a hash, create one.  Make sure
 526                 // that the application session frees the hash when it terminates.
 527                 if( NULL == cache ) {
 528                         session->userData = cache = osrfNewHash();
 529                         osrfHashSetCallback( cache, &sessionDataFree );
 530                         ctx->session->userDataFree = &userDataFree;
 531                 }
 532
 533                 if( user_login )
 534                         osrfHashSet( cache, user_login, "user_login" );
 535                 else
 536                         osrfHashRemove( cache, "user_login" );
 537         }
 538 }
 539
 540 /**
 541         @brief Get the user login object for the current application session, if any.
 542         @param ctx Pointer to the method context.
 543         @return Pointer to the user login object if found; otherwise NULL.
 544
 545         The user login object was returned from the authentication server, and then cached so
 546         we don't have to call the authentication server again for the same user.
 547 */
 548 static const jsonObject* getUserLogin( osrfMethodContext* ctx ) {
 549         if( ctx && ctx->session && ctx->session->userData )
 550                 return osrfHashGet( (osrfHash*) ctx->session->userData, "user_login" );
 551         else
 552                 return NULL;
 553 }
 554
 555 /**
 556         @brief Save a copy of an authkey in the userData of the current application session.
 557         @param ctx Pointer to the method context.
 558         @param authkey The authkey to be saved.
 559
 560         If @a authkey is NULL, remove the authkey if one is already cached.
 561 */
 562 static void setAuthkey( osrfMethodContext* ctx, const char* authkey ) {
 563         if( ctx && ctx->session && authkey ) {
 564                 osrfAppSession* session = ctx->session;
 565                 osrfHash* cache = session->userData;
 566
 567                 // If the session doesn't already have a hash, create one.  Make sure
 568                 // that the application session frees the hash when it terminates.
 569                 if( NULL == cache ) {
 570                         session->userData = cache = osrfNewHash();
 571                         osrfHashSetCallback( cache, &sessionDataFree );
 572                         ctx->session->userDataFree = &userDataFree;
 573                 }
 574
 575                 // Save the transaction id in the hash, with the key "xact_id"
 576                 if( authkey && *authkey )
 577                         osrfHashSet( cache, strdup( authkey ), "authkey" );
 578                 else
 579                         osrfHashRemove( cache, "authkey" );
 580         }
 581 }
 582
 583 /**
 584         @brief Reset the login timeout.
 585         @param authkey The authentication key for the current login session.
 586         @param now The current time.
 587         @return Zero if successful, or 1 if not.
 588
 589         Tell the authentication server to reset the timeout so that the login session won't
 590         expire for a while longer.
 591
 592         We could dispense with the @a now parameter by calling time().  But we just called
 593         time() in order to decide whether to reset the timeout, so we might as well reuse
 594         the result instead of calling time() again.
 595 */
 596 static int reset_timeout( const char* authkey, time_t now ) {
 597         jsonObject* auth_object = jsonNewObject( authkey );
 598
 599         // Ask the authentication server to reset the timeout.  It returns an event
 600         // indicating success or failure.
 601         jsonObject* result = oilsUtilsQuickReq( "open-ils.auth",
 602                 "open-ils.auth.session.reset_timeout", auth_object );
 603         jsonObjectFree( auth_object );
 604
 605         if( !result || result->type != JSON_HASH ) {
 606                 osrfLogError( OSRF_LOG_MARK,
 607                          "Unexpected object type receieved from open-ils.auth.session.reset_timeout" );
 608                 jsonObjectFree( result );
 609                 return 1;       // Not the right sort of object returned
 610         }
 611
 612         const jsonObject* ilsevent = jsonObjectGetKey( result, "ilsevent" );
 613         if( !ilsevent || ilsevent->type != JSON_NUMBER ) {
 614                 osrfLogError( OSRF_LOG_MARK, "ilsevent is absent or malformed" );
 615                 jsonObjectFree( result );
 616                 return 1;    // Return code from method not available
 617         }
 618
 619         if( jsonObjectGetNumber( ilsevent ) != 0.0 ) {
 620                 const char* desc = jsonObjectGetString( jsonObjectGetKey( result, "desc" ) );
 621                 if( !desc )
 622                         desc = "(No reason available)";    // failsafe; shouldn't happen
 623                 osrfLogInfo( OSRF_LOG_MARK, "Failure to reset timeout: %s", desc );
 624                 jsonObjectFree( result );
 625                 return 1;
 626         }
 627
 628         // Revise our local proxy for the timeout deadline
 629         // by a smallish fraction of the timeout interval
 630         const char* timeout = jsonObjectGetString( jsonObjectGetKey( result, "payload" ) );
 631         if( !timeout )
 632                 timeout = "1";   // failsafe; shouldn't happen
 633         time_next_reset = now + atoi( timeout ) / 15;
 634
 635         jsonObjectFree( result );
 636         return 0;     // Successfully reset timeout
 637 }
 638
 639 /**
 640         @brief Get the authkey string for the current application session, if any.
 641         @param ctx Pointer to the method context.
 642         @return Pointer to the cached authkey if found; otherwise NULL.
 643
 644         If present, the authkey string was cached from a previous method call.
 645 */
 646 static const char* getAuthkey( osrfMethodContext* ctx ) {
 647         if( ctx && ctx->session && ctx->session->userData ) {
 648                 const char* authkey = osrfHashGet( (osrfHash*) ctx->session->userData, "authkey" );
 649
 650                 // Possibly reset the authentication timeout to keep the login alive.  We do so
 651                 // no more than once per method call, and not at all if it has been only a short
 652                 // time since the last reset.
 653
 654                 // Here we reset explicitly, if at all.  We also implicitly reset the timeout
 655                 // whenever we call the "open-ils.auth.session.retrieve" method.
 656                 if( timeout_needs_resetting ) {
 657                         time_t now = time( NULL );
 658                         if( now >= time_next_reset && reset_timeout( authkey, now ) )
 659                                 authkey = NULL;    // timeout has apparently expired already
 660                 }
 661
 662                 timeout_needs_resetting = 0;
 663                 return authkey;
 664         }
 665         else
 666                 return NULL;
 667 }
 668
 669 /**
 670         @brief Implement the transaction.begin method.
 671         @param ctx Pointer to the method context.
 672         @return Zero if successful, or -1 upon error.
 673
 674         Start a transaction.  Save a transaction ID for future reference.
 675
 676         Method parameters:
 677         - authkey (PCRUD only)
 678
 679         Return to client: Transaction ID
 680 */
 681 int beginTransaction( osrfMethodContext* ctx ) {
 682         if(osrfMethodVerifyContext( ctx )) {
 683                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 684                 return -1;
 685         }
 686
 687         if( enforce_pcrud ) {
 688                 timeout_needs_resetting = 1;
 689                 const jsonObject* user = verifyUserPCRUD( ctx );
 690                 if( !user )
 691                         return -1;
 692         }
 693
 694         dbi_result result = dbi_conn_query( writehandle, "START TRANSACTION;" );
 695         if( !result ) {
 696                 osrfLogError( OSRF_LOG_MARK, "%s: Error starting transaction", modulename );
 697                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 698                                 "osrfMethodException", ctx->request, "Error starting transaction" );
 699                 return -1;
 700         } else {
 701                 setXactId( ctx );
 702                 jsonObject* ret = jsonNewObject( getXactId( ctx ) );
 703                 osrfAppRespondComplete( ctx, ret );
 704                 jsonObjectFree( ret );
 705         }
 706         return 0;
 707 }
 708
 709 /**
 710         @brief Implement the savepoint.set method.
 711         @param ctx Pointer to the method context.
 712         @return Zero if successful, or -1 if not.
 713
 714         Issue a SAVEPOINT to the database server.
 715
 716         Method parameters:
 717         - authkey (PCRUD only)
 718         - savepoint name
 719
 720         Return to client: Savepoint name
 721 */
 722 int setSavepoint( osrfMethodContext* ctx ) {
 723         if(osrfMethodVerifyContext( ctx )) {
 724                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 725                 return -1;
 726         }
 727
 728         int spNamePos = 0;
 729         if( enforce_pcrud ) {
 730                 spNamePos = 1;
 731                 timeout_needs_resetting = 1;
 732                 const jsonObject* user = verifyUserPCRUD( ctx );
 733                 if( !user )
 734                         return -1;
 735         }
 736
 737         // Verify that a transaction is pending
 738         const char* trans_id = getXactId( ctx );
 739         if( NULL == trans_id ) {
 740                 osrfAppSessionStatus(
 741                         ctx->session,
 742                         OSRF_STATUS_INTERNALSERVERERROR,
 743                         "osrfMethodException",
 744                         ctx->request,
 745                         "No active transaction -- required for savepoints"
 746                 );
 747                 return -1;
 748         }
 749
 750         // Get the savepoint name from the method params
 751         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 752
 753         dbi_result result = dbi_conn_queryf( writehandle, "SAVEPOINT \"%s\";", spName );
 754         if( !result ) {
 755                 osrfLogError(
 756                         OSRF_LOG_MARK,
 757                         "%s: Error creating savepoint %s in transaction %s",
 758                         modulename,
 759                         spName,
 760                         trans_id
 761                 );
 762                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 763                                 "osrfMethodException", ctx->request, "Error creating savepoint" );
 764                 return -1;
 765         } else {
 766                 jsonObject* ret = jsonNewObject( spName );
 767                 osrfAppRespondComplete( ctx, ret );
 768                 jsonObjectFree( ret  );
 769         }
 770         return 0;
 771 }
 772
 773 /**
 774         @brief Implement the savepoint.release method.
 775         @param ctx Pointer to the method context.
 776         @return Zero if successful, or -1 if not.
 777
 778         Issue a RELEASE SAVEPOINT to the database server.
 779
 780         Method parameters:
 781         - authkey (PCRUD only)
 782         - savepoint name
 783
 784         Return to client: Savepoint name
 785 */
 786 int releaseSavepoint( osrfMethodContext* ctx ) {
 787         if(osrfMethodVerifyContext( ctx )) {
 788                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 789                 return -1;
 790         }
 791
 792         int spNamePos = 0;
 793         if( enforce_pcrud ) {
 794                 spNamePos = 1;
 795                 timeout_needs_resetting = 1;
 796                 const jsonObject* user = verifyUserPCRUD( ctx );
 797                 if(  !user )
 798                         return -1;
 799         }
 800
 801         // Verify that a transaction is pending
 802         const char* trans_id = getXactId( ctx );
 803         if( NULL == trans_id ) {
 804                 osrfAppSessionStatus(
 805                         ctx->session,
 806                         OSRF_STATUS_INTERNALSERVERERROR,
 807                         "osrfMethodException",
 808                         ctx->request,
 809                         "No active transaction -- required for savepoints"
 810                 );
 811                 return -1;
 812         }
 813
 814         // Get the savepoint name from the method params
 815         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 816
 817         dbi_result result = dbi_conn_queryf( writehandle, "RELEASE SAVEPOINT \"%s\";", spName );
 818         if( !result ) {
 819                 osrfLogError(
 820                         OSRF_LOG_MARK,
 821                         "%s: Error releasing savepoint %s in transaction %s",
 822                         modulename,
 823                         spName,
 824                         trans_id
 825                 );
 826                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 827                                 "osrfMethodException", ctx->request, "Error releasing savepoint" );
 828                 return -1;
 829         } else {
 830                 jsonObject* ret = jsonNewObject( spName );
 831                 osrfAppRespondComplete( ctx, ret );
 832                 jsonObjectFree( ret );
 833         }
 834         return 0;
 835 }
 836
 837 /**
 838         @brief Implement the savepoint.rollback method.
 839         @param ctx Pointer to the method context.
 840         @return Zero if successful, or -1 if not.
 841
 842         Issue a ROLLBACK TO SAVEPOINT to the database server.
 843
 844         Method parameters:
 845         - authkey (PCRUD only)
 846         - savepoint name
 847
 848         Return to client: Savepoint name
 849 */
 850 int rollbackSavepoint( osrfMethodContext* ctx ) {
 851         if(osrfMethodVerifyContext( ctx )) {
 852                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 853                 return -1;
 854         }
 855
 856         int spNamePos = 0;
 857         if( enforce_pcrud ) {
 858                 spNamePos = 1;
 859                 timeout_needs_resetting = 1;
 860                 const jsonObject* user = verifyUserPCRUD( ctx );
 861                 if( !user )
 862                         return -1;
 863         }
 864
 865         // Verify that a transaction is pending
 866         const char* trans_id = getXactId( ctx );
 867         if( NULL == trans_id ) {
 868                 osrfAppSessionStatus(
 869                         ctx->session,
 870                         OSRF_STATUS_INTERNALSERVERERROR,
 871                         "osrfMethodException",
 872                         ctx->request,
 873                         "No active transaction -- required for savepoints"
 874                 );
 875                 return -1;
 876         }
 877
 878         // Get the savepoint name from the method params
 879         const char* spName = jsonObjectGetString( jsonObjectGetIndex(ctx->params, spNamePos) );
 880
 881         dbi_result result = dbi_conn_queryf( writehandle, "ROLLBACK TO SAVEPOINT \"%s\";", spName );
 882         if( !result ) {
 883                 osrfLogError(
 884                         OSRF_LOG_MARK,
 885                         "%s: Error rolling back savepoint %s in transaction %s",
 886                         modulename,
 887                         spName,
 888                         trans_id
 889                 );
 890                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 891                                 "osrfMethodException", ctx->request, "Error rolling back savepoint" );
 892                 return -1;
 893         } else {
 894                 jsonObject* ret = jsonNewObject( spName );
 895                 osrfAppRespondComplete( ctx, ret );
 896                 jsonObjectFree( ret );
 897         }
 898         return 0;
 899 }
 900
 901 /**
 902         @brief Implement the transaction.commit method.
 903         @param ctx Pointer to the method context.
 904         @return Zero if successful, or -1 if not.
 905
 906         Issue a COMMIT to the database server.
 907
 908         Method parameters:
 909         - authkey (PCRUD only)
 910
 911         Return to client: Transaction ID.
 912 */
 913 int commitTransaction( osrfMethodContext* ctx ) {
 914         if(osrfMethodVerifyContext( ctx )) {
 915                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
 916                 return -1;
 917         }
 918
 919         if( enforce_pcrud ) {
 920                 timeout_needs_resetting = 1;
 921                 const jsonObject* user = verifyUserPCRUD( ctx );
 922                 if( !user )
 923                         return -1;
 924         }
 925
 926         // Verify that a transaction is pending
 927         const char* trans_id = getXactId( ctx );
 928         if( NULL == trans_id ) {
 929                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 930                                 "osrfMethodException", ctx->request, "No active transaction to commit" );
 931                 return -1;
 932         }
 933
 934         dbi_result result = dbi_conn_query( writehandle, "COMMIT;" );
 935         if( !result ) {
 936                 osrfLogError( OSRF_LOG_MARK, "%s: Error committing transaction", modulename );
 937                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 938                                 "osrfMethodException", ctx->request, "Error committing transaction" );
 939                 return -1;
 940         } else {
 941                 jsonObject* ret = jsonNewObject( trans_id );
 942                 osrfAppRespondComplete( ctx, ret );
 943                 jsonObjectFree( ret );
 944                 clearXactId( ctx );
 945         }
 946         return 0;
 947 }
 948
 949 /**
 950         @brief Implement the transaction.rollback method.
 951         @param ctx Pointer to the method context.
 952         @return Zero if successful, or -1 if not.
 953
 954         Issue a ROLLBACK to the database server.
 955
 956         Method parameters:
 957         - authkey (PCRUD only)
 958
 959         Return to client: Transaction ID
 960 */
 961 int rollbackTransaction( osrfMethodContext* ctx ) {
 962         if( osrfMethodVerifyContext( ctx )) {
 963                 osrfLogError( OSRF_LOG_MARK,  "Invalid method context" );
 964                 return -1;
 965         }
 966
 967         if( enforce_pcrud ) {
 968                 timeout_needs_resetting = 1;
 969                 const jsonObject* user = verifyUserPCRUD( ctx );
 970                 if( !user )
 971                         return -1;
 972         }
 973
 974         // Verify that a transaction is pending
 975         const char* trans_id = getXactId( ctx );
 976         if( NULL == trans_id ) {
 977                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 978                                 "osrfMethodException", ctx->request, "No active transaction to roll back" );
 979                 return -1;
 980         }
 981
 982         dbi_result result = dbi_conn_query( writehandle, "ROLLBACK;" );
 983         if( !result ) {
 984                 osrfLogError( OSRF_LOG_MARK, "%s: Error rolling back transaction", modulename );
 985                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_INTERNALSERVERERROR,
 986                                 "osrfMethodException", ctx->request, "Error rolling back transaction" );
 987                 return -1;
 988         } else {
 989                 jsonObject* ret = jsonNewObject( trans_id );
 990                 osrfAppRespondComplete( ctx, ret );
 991                 jsonObjectFree( ret );
 992                 clearXactId( ctx );
 993         }
 994         return 0;
 995 }
 996
 997 /**
 998         @brief Implement the "search" method.
 999         @param ctx Pointer to the method context.
1000         @return Zero if successful, or -1 if not.
1001
1002         Method parameters:
1003         - authkey (PCRUD only)
1004         - WHERE clause, as jsonObject
1005         - Other SQL clause(s), as a JSON_HASH: joins, SELECT list, LIMIT, etc.
1006
1007         Return to client: rows of the specified class that satisfy a specified WHERE clause.
1008         Optionally flesh linked fields.
1009 */
1010 int doSearch( osrfMethodContext* ctx ) {
1011         if( osrfMethodVerifyContext( ctx )) {
1012                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1013                 return -1;
1014         }
1015
1016         if( enforce_pcrud )
1017                 timeout_needs_resetting = 1;
1018
1019         jsonObject* where_clause;
1020         jsonObject* rest_of_query;
1021
1022         if( enforce_pcrud ) {
1023                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1024                 rest_of_query = jsonObjectGetIndex( ctx->params, 2 );
1025         } else {
1026                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1027                 rest_of_query = jsonObjectGetIndex( ctx->params, 1 );
1028         }
1029
1030         // Get the class metadata
1031         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1032         osrfHash* class_meta = osrfHashGet( method_meta, "class" );
1033
1034         // Do the query
1035         int err = 0;
1036         jsonObject* obj = doFieldmapperSearch( ctx, class_meta, where_clause, rest_of_query, &err );
1037         if( err ) {
1038                 osrfAppRespondComplete( ctx, NULL );
1039                 return -1;
1040         }
1041
1042         // Return each row to the client (except that some may be suppressed by PCRUD)
1043         jsonObject* cur = 0;
1044         unsigned long res_idx = 0;
1045         while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1046                 if( enforce_pcrud && !verifyObjectPCRUD( ctx, cur ))
1047                         continue;
1048                 osrfAppRespond( ctx, cur );
1049         }
1050         jsonObjectFree( obj );
1051
1052         osrfAppRespondComplete( ctx, NULL );
1053         return 0;
1054 }
1055
1056 /**
1057         @brief Implement the "id_list" method.
1058         @param ctx Pointer to the method context.
1059         @param err Pointer through which to return an error code.
1060         @return Zero if successful, or -1 if not.
1061
1062         Method parameters:
1063         - authkey (PCRUD only)
1064         - WHERE clause, as jsonObject
1065         - Other SQL clause(s), as a JSON_HASH: joins, LIMIT, etc.
1066
1067         Return to client: The primary key values for all rows of the relevant class that
1068         satisfy a specified WHERE clause.
1069
1070         This method relies on the assumption that every class has a primary key consisting of
1071         a single column.
1072 */
1073 int doIdList( osrfMethodContext* ctx ) {
1074         if( osrfMethodVerifyContext( ctx )) {
1075                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1076                 return -1;
1077         }
1078
1079         if( enforce_pcrud )
1080                 timeout_needs_resetting = 1;
1081
1082         jsonObject* where_clause;
1083         jsonObject* rest_of_query;
1084
1085         // We use the where clause without change.  But we need to massage the rest of the
1086         // query, so we work with a copy of it instead of modifying the original.
1087
1088         if( enforce_pcrud ) {
1089                 where_clause  = jsonObjectGetIndex( ctx->params, 1 );
1090                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 2 ) );
1091         } else {
1092                 where_clause  = jsonObjectGetIndex( ctx->params, 0 );
1093                 rest_of_query = jsonObjectClone( jsonObjectGetIndex( ctx->params, 1 ) );
1094         }
1095
1096         // Eliminate certain SQL clauses, if present.
1097         if( rest_of_query ) {
1098                 jsonObjectRemoveKey( rest_of_query, "select" );
1099                 jsonObjectRemoveKey( rest_of_query, "no_i18n" );
1100                 jsonObjectRemoveKey( rest_of_query, "flesh" );
1101                 jsonObjectRemoveKey( rest_of_query, "flesh_fields" );
1102         } else {
1103                 rest_of_query = jsonNewObjectType( JSON_HASH );
1104         }
1105
1106         jsonObjectSetKey( rest_of_query, "no_i18n", jsonNewBoolObject( 1 ) );
1107
1108         // Get the class metadata
1109         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1110         osrfHash* class_meta = osrfHashGet( method_meta, "class" );
1111
1112         // Build a SELECT list containing just the primary key,
1113         // i.e. like { "classname":["keyname"] }
1114         jsonObject* col_list_obj = jsonNewObjectType( JSON_ARRAY );
1115
1116         // Load array with name of primary key
1117         jsonObjectPush( col_list_obj, jsonNewObject( osrfHashGet( class_meta, "primarykey" ) ) );
1118         jsonObject* select_clause = jsonNewObjectType( JSON_HASH );
1119         jsonObjectSetKey( select_clause, osrfHashGet( class_meta, "classname" ), col_list_obj );
1120
1121         jsonObjectSetKey( rest_of_query, "select", select_clause );
1122
1123         // Do the query
1124         int err = 0;
1125         jsonObject* obj =
1126                 doFieldmapperSearch( ctx, class_meta, where_clause, rest_of_query, &err );
1127
1128         jsonObjectFree( rest_of_query );
1129         if( err ) {
1130                 osrfAppRespondComplete( ctx, NULL );
1131                 return -1;
1132         }
1133
1134         // Return each primary key value to the client
1135         jsonObject* cur;
1136         unsigned long res_idx = 0;
1137         while((cur = jsonObjectGetIndex( obj, res_idx++ ) )) {
1138                 if( enforce_pcrud && !verifyObjectPCRUD( ctx, cur ))
1139                         continue;        // Suppress due to lack of permission
1140                 else
1141                         osrfAppRespond( ctx,
1142                                 oilsFMGetObject( cur, osrfHashGet( class_meta, "primarykey" ) ) );
1143         }
1144
1145         jsonObjectFree( obj );
1146         osrfAppRespondComplete( ctx, NULL );
1147         return 0;
1148 }
1149
1150 /**
1151         @brief Verify that we have a valid class reference.
1152         @param ctx Pointer to the method context.
1153         @param param Pointer to the method parameters.
1154         @return 1 if the class reference is valid, or zero if it isn't.
1155
1156         The class of the method params must match the class to which the method id devoted.
1157         For PCRUD there are additional restrictions.
1158 */
1159 static int verifyObjectClass ( osrfMethodContext* ctx, const jsonObject* param ) {
1160
1161         osrfHash* method_meta = (osrfHash*) ctx->method->userData;
1162         osrfHash* class = osrfHashGet( method_meta, "class" );
1163
1164         // Compare the method's class to the parameters' class
1165         if( !param->classname || (strcmp( osrfHashGet(class, "classname"), param->classname ))) {
1166
1167                 // Oops -- they don't match.  Complain.
1168                 growing_buffer* msg = buffer_init( 128 );
1169                 buffer_fadd(
1170                         msg,
1171                         "%s: %s method for type %s was passed a %s",
1172                         modulename,
1173                         osrfHashGet( method_meta, "methodtype" ),
1174                         osrfHashGet( class, "classname" ),
1175                         param->classname ? param->classname : "(null)"
1176                 );
1177
1178                 char* m = buffer_release( msg );
1179                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
1180                                 ctx->request, m );
1181                 free( m );
1182
1183                 return 0;
1184         }
1185
1186         if( enforce_pcrud )
1187                 return verifyObjectPCRUD( ctx, param );
1188         else
1189                 return 1;
1190 }
1191
1192 /**
1193         @brief (PCRUD only) Verify that the user is properly logged in.
1194         @param ctx Pointer to the method context.
1195         @return If the user is logged in, a pointer to the user object from the authentication
1196         server; otherwise NULL.
1197 */
1198 static const jsonObject* verifyUserPCRUD( osrfMethodContext* ctx ) {
1199
1200         // Get the authkey (the first method parameter)
1201         const char* auth = jsonObjectGetString( jsonObjectGetIndex( ctx->params, 0 ) );
1202
1203         // See if we have the same authkey, and a user object,
1204         // locally cached from a previous call
1205         const char* cached_authkey = getAuthkey( ctx );
1206         if( cached_authkey && !strcmp( cached_authkey, auth ) ) {
1207                 const jsonObject* cached_user = getUserLogin( ctx );
1208                 if( cached_user )
1209                         return cached_user;
1210         }
1211
1212         // We have no matching authentication data in the cache.  Authenticate from scratch.
1213         jsonObject* auth_object = jsonNewObject( auth );
1214
1215         // Fetch the user object from the authentication server
1216         jsonObject* user = oilsUtilsQuickReq( "open-ils.auth", "open-ils.auth.session.retrieve",
1217                         auth_object );
1218         jsonObjectFree( auth_object );
1219
1220         if( !user->classname || strcmp(user->classname, "au" )) {
1221
1222                 growing_buffer* msg = buffer_init( 128 );
1223                 buffer_fadd(
1224                         msg,
1225                         "%s: permacrud received a bad auth token: %s",
1226                         modulename,
1227                         auth
1228                 );
1229
1230                 char* m = buffer_release( msg );
1231                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_UNAUTHORIZED, "osrfMethodException",
1232                                 ctx->request, m );
1233
1234                 free( m );
1235                 jsonObjectFree( user );
1236                 user = NULL;
1237         }
1238
1239         setUserLogin( ctx, user );
1240         setAuthkey( ctx, auth );
1241
1242         // Allow ourselves up to a second before we have to reset the login timeout.
1243         // It would be nice to use some fraction of the timeout interval enforced by the
1244         // authentication server, but that value is not readily available at this point.
1245         // Instead, we use a conservative default interval.
1246         time_next_reset = time( NULL ) + 1;
1247
1248         return user;
1249 }
1250
1251 /**
1252         @brief For PCRUD: Determine whether the current user may access the current row.
1253         @param ctx Pointer to the method context.
1254         @param obj Pointer to the row being potentially accessed.
1255         @return 1 if access is permitted, or 0 if it isn't.
1256
1257         The @a obj parameter points to a JSON_HASH of column values, keyed on column name.
1258 */
1259 static int verifyObjectPCRUD (  osrfMethodContext* ctx, const jsonObject* obj ) {
1260
1261         dbhandle = writehandle;
1262
1263         // Figure out what class and method are involved
1264         osrfHash* method_metadata = (osrfHash*) ctx->method->userData;
1265         osrfHash* class = osrfHashGet( method_metadata, "class" );
1266         const char* method_type = osrfHashGet( method_metadata, "methodtype" );
1267
1268         // Set fetch to 1 in all cases except for inserts, meaning that for local or foreign
1269         // contexts we will do another lookup of the current row, even if we already have a
1270         // previously fetched row image, because the row image in hand may not include the
1271         // foreign key(s) that we need.
1272
1273         // This is a quick fix with a bludgeon.  There are ways to avoid the extra lookup,
1274         // but they aren't implemented yet.
1275
1276         int fetch = 0;
1277         if( *method_type == 's' || *method_type == 'i' ) {
1278                 method_type = "retrieve"; // search and id_list are equivalent to retrieve for this
1279                 fetch = 1;
1280         } else if( *method_type == 'u' || *method_type == 'd' ) {
1281                 fetch = 1; // MUST go to the db for the object for update and delete
1282         }
1283
1284         // Get the appropriate permacrud entry from the IDL, depending on method type
1285         osrfHash* pcrud = osrfHashGet( osrfHashGet( class, "permacrud" ), method_type );
1286         if( !pcrud ) {
1287                 // No permacrud for this method type on this class
1288
1289                 growing_buffer* msg = buffer_init( 128 );
1290                 buffer_fadd(
1291                         msg,
1292                         "%s: %s on class %s has no permacrud IDL entry",
1293                         modulename,
1294                         osrfHashGet( method_metadata, "methodtype" ),
1295                         osrfHashGet( class, "classname" )
1296                 );
1297
1298                 char* m = buffer_release( msg );
1299                 osrfAppSessionStatus( ctx->session, OSRF_STATUS_FORBIDDEN,
1300                                 "osrfMethodException", ctx->request, m );
1301
1302                 free( m );
1303
1304                 return 0;
1305         }
1306
1307         // Get the user id, and make sure the user is logged in
1308         const jsonObject* user = verifyUserPCRUD( ctx );
1309         if( !user )
1310                 return 0;    // Not logged in?  No access.
1311
1312         int userid = atoi( oilsFMGetStringConst( user, "id" ) );
1313
1314         // Get a list of permissions from the permacrud entry.
1315         osrfStringArray* permission = osrfHashGet( pcrud, "permission" );
1316
1317         // Build a list of org units that own the row.  This is fairly convoluted because there
1318         // are several different ways that an org unit may own the row, as defined by the
1319         // permacrud entry.
1320
1321         // Local context means that the row includes a foreign key pointing to actor.org_unit,
1322         // identifying an owning org_unit..
1323         osrfStringArray* local_context = osrfHashGet( pcrud, "local_context" );
1324
1325         // Foreign context adds a layer of indirection.  The row points to some other row that
1326         // an org unit may own.  The "jump" attribute, if present, adds another layer of
1327         // indirection.
1328         osrfHash* foreign_context = osrfHashGet( pcrud, "foreign_context" );
1329
1330         // The following string array stores the list of org units.  (We don't have a thingie
1331         // for storing lists of integers, so we fake it with a list of strings.)
1332         osrfStringArray* context_org_array = osrfNewStringArray( 1 );
1333
1334         int err = 0;
1335         const char* pkey_value = NULL;
1336         if( str_is_true( osrfHashGet(pcrud, "global_required") ) ) {
1337                 // If the global_required attribute is present and true, then the only owning
1338                 // org unit is the root org unit, i.e. the one with no parent.
1339                 osrfLogDebug( OSRF_LOG_MARK,
1340                                 "global-level permissions required, fetching top of the org tree" );
1341
1342                 // check for perm at top of org tree
1343                 const char* org_tree_root_id = org_tree_root( ctx );
1344                 if( org_tree_root_id ) {
1345                         osrfStringArrayAdd( context_org_array, org_tree_root_id );
1346                         osrfLogDebug( OSRF_LOG_MARK, "top of the org tree is %s", org_tree_root_id );
1347                 } else  {
1348                         osrfStringArrayFree( context_org_array );
1349                         return 0;
1350                 }
1351
1352         } else {
1353                 // If the global_required attribute is absent or false, then we look for
1354                 // local and/or foreign context.  In order to find the relevant foreign
1355                 // keys, we must either read the relevant row from the database, or look at
1356                 // the image of the row that we already have in memory.
1357
1358                 // (Herein lies a bug.  Even if we have an image of the row in memory, that
1359                 // image may not include the foreign key column(s) that we need.)
1360
1361             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions not required, "
1362                                 "fetching context org ids" );
1363             const char* pkey = osrfHashGet( class, "primarykey" );
1364                 jsonObject *param = NULL;
1365
1366                 if( obj->classname ) {
1367                         pkey_value = oilsFMGetStringConst( obj, pkey );
1368                         if( !fetch )
1369                                 param = jsonObjectClone( obj );
1370                         osrfLogDebug( OSRF_LOG_MARK, "Object supplied, using primary key value of %s",
1371                                         pkey_value );
1372                 } else {
1373                         pkey_value = jsonObjectGetString( obj );
1374                         fetch = 1;
1375                         osrfLogDebug( OSRF_LOG_MARK, "Object not supplied, using primary key value "
1376                                         "of %s and retrieving from the database", pkey_value );
1377                 }
1378
1379                 if( fetch ) {
1380                         // Fetch the row so that we can look at the foreign key(s)
1381                         jsonObject* _tmp_params = single_hash( pkey, pkey_value );
1382                         jsonObject* _list = doFieldmapperSearch( ctx, class, _tmp_params, NULL, &err );
1383                         jsonObjectFree( _tmp_params );
1384
1385                         param = jsonObjectExtractIndex( _list, 0 );
1386                         jsonObjectFree( _list );
1387                 }
1388
1389                 if( !param ) {
1390                         // The row doesn't exist.  Complain, and deny access.
1391                         osrfLogDebug( OSRF_LOG_MARK,
1392                                         "Object not found in the database with primary key %s of %s",
1393                                         pkey, pkey_value );
1394
1395                         growing_buffer* msg = buffer_init( 128 );
1396                         buffer_fadd(
1397                                 msg,
1398                                 "%s: no object found with primary key %s of %s",
1399                                 modulename,
1400                                 pkey,
1401                                 pkey_value
1402                         );
1403
1404                         char* m = buffer_release( msg );
1405                         osrfAppSessionStatus(
1406                                 ctx->session,
1407                                 OSRF_STATUS_INTERNALSERVERERROR,
1408                                 "osrfMethodException",
1409                                 ctx->request,
1410                                 m
1411                         );
1412
1413                         free( m );
1414                         return 0;
1415                 }
1416
1417                 if( local_context && local_context->size > 0 ) {
1418                         // The IDL provides a list of column names for the foreign keys denoting
1419                         // local context.  Look up the value of each one, and if it isn't null,
1420                         // add it to the list of org units.
1421                         osrfLogDebug( OSRF_LOG_MARK, "%d class-local context field(s) specified",
1422                                         local_context->size );
1423                         int i = 0;
1424                         const char* lcontext = NULL;
1425                         while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
1426                                 const char* fkey_value = oilsFMGetStringConst( param, lcontext );
1427                                 if( fkey_value ) {    // if not null
1428                                         osrfStringArrayAdd( context_org_array, fkey_value );
1429                                         osrfLogDebug(
1430                                                 OSRF_LOG_MARK,
1431                                                 "adding class-local field %s (value: %s) to the context org list",
1432                                                 lcontext,
1433                                                 osrfStringArrayGetString( context_org_array, context_org_array->size - 1 )
1434                                         );
1435                                 }
1436                         }
1437                 }
1438
1439                 if( foreign_context ) {
1440                         unsigned long class_count = osrfHashGetCount( foreign_context );
1441                         osrfLogDebug( OSRF_LOG_MARK, "%d foreign context classes(s) specified", class_count );
1442
1443                         if( class_count > 0 ) {
1444
1445                                 // The IDL provides a list of foreign key columns pointing to rows that
1446                                 // an org unit may own.  Follow each link, identify the owning org unit,
1447                                 // and add it to the list.
1448                                 osrfHash* fcontext = NULL;
1449                                 osrfHashIterator* class_itr = osrfNewHashIterator( foreign_context );
1450                                 while( (fcontext = osrfHashIteratorNext( class_itr )) ) {
1451                                         // For each class to which a foreign key points:
1452                                         const char* class_name = osrfHashIteratorKey( class_itr );
1453                                         osrfHash* fcontext = osrfHashGet( foreign_context, class_name );
1454
1455                                         osrfLogDebug(
1456                                                 OSRF_LOG_MARK,
1457                                                 "%d foreign context fields(s) specified for class %s",
1458                                                 ((osrfStringArray*)osrfHashGet(fcontext,"context"))->size,
1459                                                 class_name
1460                                         );
1461
1462                                         // Get the name of the key field in the foreign table
1463                                         const char* foreign_pkey = osrfHashGet( fcontext, "field" );
1464
1465                                         // Get the value of the foreign key pointing to the foreign table
1466                                         char* foreign_pkey_value =
1467                                                         oilsFMGetString( param, osrfHashGet( fcontext, "fkey" ));
1468                                         if( !foreign_pkey_value )
1469                                                 continue;    // Foreign key value is null; skip it
1470
1471                                         // Look up the row to which the foreign key points
1472                                         jsonObject* _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1473                                         jsonObject* _list = doFieldmapperSearch(
1474                                                 ctx, osrfHashGet( oilsIDL(), class_name ), _tmp_params, NULL, &err );
1475
1476                                         jsonObject* _fparam = NULL;
1477                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1478                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1479
1480                                         jsonObjectFree( _tmp_params );
1481                                         jsonObjectFree( _list );
1482
1483                                         // At this point _fparam either points to the row identified by the
1484                                         // foreign key, or it's NULL (no such row found).
1485
1486                                         osrfStringArray* jump_list = osrfHashGet( fcontext, "jump" );
1487
1488                                         const char* bad_class = NULL;  // For noting failed lookups
1489                                         if( ! _fparam )
1490                                                 bad_class = class_name;    // Referenced row not found
1491                                         else if( jump_list ) {
1492                                                 // Follow a chain of rows, linked by foreign keys, to find an owner
1493                                                 const char* flink = NULL;
1494                                                 int k = 0;
1495                                                 while ( (flink = osrfStringArrayGetString(jump_list, k++)) && _fparam ) {
1496                                                         // For each entry in the jump list.  Each entry (i.e. flink) is
1497                                                         // the name of a foreign key column in the current row.
1498
1499                                                         // From the IDL, get the linkage information for the next jump
1500                                                         osrfHash* foreign_link_hash =
1501                                                                         oilsIDLFindPath( "/%s/links/%s", _fparam->classname, flink );
1502
1503                                                         // Get the class metadata for the class
1504                                                         // to which the foreign key points
1505                                                         osrfHash* foreign_class_meta = osrfHashGet( oilsIDL(),
1506                                                                         osrfHashGet( foreign_link_hash, "class" ));
1507
1508                                                         // Get the name of the referenced key of that class
1509                                                         foreign_pkey = osrfHashGet( foreign_link_hash, "key" );
1510
1511                                                         // Get the value of the foreign key pointing to that class
1512                                                         free( foreign_pkey_value );
1513                                                         foreign_pkey_value = oilsFMGetString( _fparam, flink );
1514                                                         if( !foreign_pkey_value )
1515                                                                 break;    // Foreign key is null; quit looking
1516
1517                                                         // Build a WHERE clause for the lookup
1518                                                         _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1519
1520                                                         // Do the lookup
1521                                                         _list = doFieldmapperSearch( ctx, foreign_class_meta,
1522                                                                         _tmp_params, NULL, &err );
1523
1524                                                         // Get the resulting row
1525                                                         jsonObjectFree( _fparam );
1526                                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1527                                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1528                                                         else {
1529                                                                 // Referenced row not found
1530                                                                 _fparam = NULL;
1531                                                                 bad_class = osrfHashGet( foreign_link_hash, "class" );
1532                                                         }
1533
1534                                                         jsonObjectFree( _tmp_params );
1535                                                         jsonObjectFree( _list );
1536                                                 }
1537                                         }
1538
1539                                         if( bad_class ) {
1540
1541                                                 // We had a foreign key pointing to such-and-such a row, but then
1542                                                 // we couldn't fetch that row.  The data in the database are in an
1543                                                 // inconsistent state; the database itself may even be corrupted.
1544                                                 growing_buffer* msg = buffer_init( 128 );
1545                                                 buffer_fadd(
1546                                                         msg,
1547                                                         "%s: no object of class %s found with primary key %s of %s",
1548                                                         modulename,
1549                                                         bad_class,
1550                                                         foreign_pkey,
1551                                                         foreign_pkey_value ? foreign_pkey_value : "(null)"
1552                                                 );
1553
1554                                                 char* m = buffer_release( msg );
1555                                                 osrfAppSessionStatus(
1556                                                         ctx->session,
1557                                                         OSRF_STATUS_INTERNALSERVERERROR,
1558                                                         "osrfMethodException",
1559                                                         ctx->request,
1560                                                         m
1561                                                 );
1562
1563                                                 free( m );
1564                                                 osrfHashIteratorFree( class_itr );
1565                                                 free( foreign_pkey_value );
1566                                                 jsonObjectFree( param );
1567
1568                                                 return 0;
1569                                         }
1570
1571                                         free( foreign_pkey_value );
1572
1573                                         if( _fparam ) {
1574                                                 // Examine each context column of the foreign row,
1575                                                 // and add its value to the list of org units.
1576                                                 int j = 0;
1577                                                 const char* foreign_field = NULL;
1578                                                 osrfStringArray* ctx_array = osrfHashGet( fcontext, "context" );
1579                                                 while ( (foreign_field = osrfStringArrayGetString( ctx_array, j++ )) ) {
1580                                                         osrfStringArrayAdd( context_org_array,
1581                                                                 oilsFMGetStringConst( _fparam, foreign_field ));
1582                                                         osrfLogDebug( OSRF_LOG_MARK,
1583                                                                 "adding foreign class %s field %s (value: %s) "
1584                                                                         "to the context org      list",
1585                                                                 class_name,
1586                                                                 foreign_field,
1587                                                                 osrfStringArrayGetString(
1588                                                                         context_org_array, context_org_array->size - 1 )
1589                                                         );
1590                                                 }
1591
1592                                                 jsonObjectFree( _fparam );
1593                                         }
1594                                 }
1595
1596                                 osrfHashIteratorFree( class_itr );
1597                         }
1598                 }
1599
1600                 jsonObjectFree( param );
1601         }
1602
1603         const char* context_org = NULL;
1604         const char* perm = NULL;
1605         int OK = 0;
1606
1607         if( permission->size == 0 ) {
1608             osrfLogDebug( OSRF_LOG_MARK, "No permission specified for this action, passing through" );
1609                 OK = 1;
1610         }
1611
1612         // For every combination of permission and context org unit: call a stored procedure
1613         // to determine if the user has this permission in the context of this org unit.
1614         // If the answer is yes at any point, then we're done, and the user has permission.
1615         // In other words permissions are additive.
1616         int i = 0;
1617         while( (perm = osrfStringArrayGetString(permission, i++)) ) {
1618                 int j = 0;
1619                 while( (context_org = osrfStringArrayGetString( context_org_array, j++ )) ) {
1620                         dbi_result result;
1621
1622                         if( pkey_value ) {
1623                                 osrfLogDebug(
1624                                         OSRF_LOG_MARK,
1625                                         "Checking object permission [%s] for user %d "
1626                                                         "on object %s (class %s) at org %d",
1627                                         perm,
1628                                         userid,
1629                                         pkey_value,
1630                                         osrfHashGet( class, "classname" ),
1631                                         atoi( context_org )
1632                                 );
1633
1634                                 result = dbi_conn_queryf(
1635                                         writehandle,
1636                                         "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
1637                                         userid,
1638                                         perm,
1639                                         osrfHashGet( class, "classname" ),
1640                                         pkey_value,
1641                                         atoi( context_org )
1642                                 );
1643
1644                                 if( result ) {
1645                                         osrfLogDebug(
1646                                                 OSRF_LOG_MARK,
1647                                                 "Received a result for object permission [%s] "
1648                                                                 "for user %d on object %s (class %s) at org %d",
1649                                                 perm,
1650                                                 userid,
1651                                                 pkey_value,
1652                                                 osrfHashGet( class, "classname" ),
1653                                                 atoi( context_org )
1654                                         );
1655
1656                                         if( dbi_result_first_row( result )) {
1657                                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
1658                                                 const char* has_perm = jsonObjectGetString(
1659                                                                 jsonObjectGetKeyConst( return_val, "has_perm" ));
1660
1661                                                 osrfLogDebug(
1662                                                         OSRF_LOG_MARK,
1663                                                         "Status of object permission [%s] for user %d "
1664                                                                         "on object %s (class %s) at org %d is %s",
1665                                                         perm,
1666                                                         userid,
1667                                                         pkey_value,
1668                                                         osrfHashGet(class, "classname"),
1669                                                         atoi(context_org),
1670                                                         has_perm
1671                                                 );
1672
1673                                                 if( *has_perm == 't' )
1674                                                         OK = 1;
1675                                                 jsonObjectFree( return_val );
1676                                         }
1677
1678                                         dbi_result_free( result );
1679                                         if( OK )
1680                                                 break;
1681                                 }
1682                         }
1683
1684                         osrfLogDebug( OSRF_LOG_MARK,
1685                                         "Checking non-object permission [%s] for user %d at org %d",
1686                                         perm, userid, atoi(context_org) );
1687                         result = dbi_conn_queryf(
1688                                 writehandle,
1689                                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
1690                                 userid,
1691                                 perm,
1692                                 atoi( context_org )
1693                         );
1694
1695                         if( result ) {
1696                                 osrfLogDebug( OSRF_LOG_MARK,
1697                                                 "Received a result for permission [%s] for user %d at org %d",
1698                                                 perm, userid, atoi( context_org ));
1699                                 if( dbi_result_first_row( result )) {
1700                                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1701                                         const char* has_perm = jsonObjectGetString(
1702                                                         jsonObjectGetKeyConst( return_val, "has_perm" ));
1703                                         osrfLogDebug( OSRF_LOG_MARK,
1704                                                         "Status of permission [%s] for user %d at org %d is [%s]",
1705                                                         perm, userid, atoi( context_org ), has_perm );
1706                                         if( *has_perm == 't' )
1707                                                 OK = 1;
1708                                         jsonObjectFree( return_val );
1709                                 }
1710
1711                                 dbi_result_free( result );
1712                                 if( OK )
1713                                         break;
1714                         }
1715
1716                 }
1717                 if( OK )
1718                         break;
1719         }
1720
1721         osrfStringArrayFree( context_org_array );
1722
1723         return OK;
1724 }
1725
1726 /**
1727         @brief Look up the root of the org_unit tree.
1728         @param ctx Pointer to the method context.
1729         @return The id of the root org unit, as a character string.
1730
1731         Query actor.org_unit where parent_ou is null, and return the id as a string.
1732
1733         This function assumes that there is only one root org unit, i.e. that we
1734         have a single tree, not a forest.
1735
1736         The calling code is responsible for freeing the returned string.
1737 */
1738 static const char* org_tree_root( osrfMethodContext* ctx ) {
1739
1740         static char cached_root_id[ 32 ] = "";  // extravagantly large buffer
1741         static time_t last_lookup_time = 0;
1742         time_t current_time = time( NULL );
1743
1744         if( cached_root_id[ 0 ] && ( current_time - last_lookup_time < 3600 ) ) {
1745                 // We successfully looked this up less than an hour ago.
1746                 // It's not likely to have changed since then.
1747                 return strdup( cached_root_id );
1748         }
1749         last_lookup_time = current_time;
1750
1751         int err = 0;
1752         jsonObject* where_clause = single_hash( "parent_ou", NULL );
1753         jsonObject* result = doFieldmapperSearch(
1754                 ctx, osrfHashGet( oilsIDL(), "aou" ), where_clause, NULL, &err );
1755         jsonObjectFree( where_clause );
1756
1757         jsonObject* tree_top = jsonObjectGetIndex( result, 0 );
1758
1759         if( !tree_top ) {
1760                 jsonObjectFree( result );
1761
1762                 growing_buffer* msg = buffer_init( 128 );
1763                 OSRF_BUFFER_ADD( msg, modulename );
1764                 OSRF_BUFFER_ADD( msg,
1765                                 ": Internal error, could not find the top of the org tree (parent_ou = NULL)" );
1766
1767                 char* m = buffer_release( msg );
1768                 osrfAppSessionStatus( ctx->session,
1769                                 OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, m );
1770                 free( m );
1771
1772                 cached_root_id[ 0 ] = '\0';
1773                 return NULL;
1774         }
1775
1776         const char* root_org_unit_id = oilsFMGetStringConst( tree_top, "id" );
1777         osrfLogDebug( OSRF_LOG_MARK, "Top of the org tree is %s", root_org_unit_id );
1778
1779         strcpy( cached_root_id, root_org_unit_id );
1780         jsonObjectFree( result );
1781         return cached_root_id;
1782 }
1783
1784 /**
1785         @brief Create a JSON_HASH with a single key/value pair.
1786         @param key The key of the key/value pair.
1787         @param value the value of the key/value pair.
1788         @return Pointer to a newly created jsonObject of type JSON_HASH.
1789
1790         The value of the key/value is either a string or (if @a value is NULL) a null.
1791 */
1792 static jsonObject* single_hash( const char* key, const char* value ) {
1793         // Sanity check
1794         if( ! key ) key = "";
1795
1796         jsonObject* hash = jsonNewObjectType( JSON_HASH );
1797         jsonObjectSetKey( hash, key, jsonNewObject( value ) );
1798         return hash;
1799 }
1800
1801
1802 int doCreate( osrfMethodContext* ctx ) {
1803         if(osrfMethodVerifyContext( ctx )) {
1804                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1805                 return -1;
1806         }
1807
1808         if( enforce_pcrud )
1809                 timeout_needs_resetting = 1;
1810
1811         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1812         jsonObject* target = NULL;
1813         jsonObject* options = NULL;
1814
1815         if( enforce_pcrud ) {
1816                 target = jsonObjectGetIndex( ctx->params, 1 );
1817                 options = jsonObjectGetIndex( ctx->params, 2 );
1818         } else {
1819                 target = jsonObjectGetIndex( ctx->params, 0 );
1820                 options = jsonObjectGetIndex( ctx->params, 1 );
1821         }
1822
1823         if( !verifyObjectClass( ctx, target )) {
1824                 osrfAppRespondComplete( ctx, NULL );
1825                 return -1;
1826         }
1827
1828         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1829
1830         const char* trans_id = getXactId( ctx );
1831         if( !trans_id ) {
1832                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1833
1834                 osrfAppSessionStatus(
1835                         ctx->session,
1836                         OSRF_STATUS_BADREQUEST,
1837                         "osrfMethodException",
1838                         ctx->request,
1839                         "No active transaction -- required for CREATE"
1840                 );
1841                 osrfAppRespondComplete( ctx, NULL );
1842                 return -1;
1843         }
1844
1845         // The following test is harmless but redundant.  If a class is
1846         // readonly, we don't register a create method for it.
1847         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
1848                 osrfAppSessionStatus(
1849                         ctx->session,
1850                         OSRF_STATUS_BADREQUEST,
1851                         "osrfMethodException",
1852                         ctx->request,
1853                         "Cannot INSERT readonly class"
1854                 );
1855                 osrfAppRespondComplete( ctx, NULL );
1856                 return -1;
1857         }
1858
1859         // Set the last_xact_id
1860         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
1861         if( index > -1 ) {
1862                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
1863                         trans_id, target->classname, index);
1864                 jsonObjectSetIndex( target, index, jsonNewObject( trans_id ));
1865         }
1866
1867         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
1868
1869         dbhandle = writehandle;
1870
1871         osrfHash* fields = osrfHashGet( meta, "fields" );
1872         char* pkey       = osrfHashGet( meta, "primarykey" );
1873         char* seq        = osrfHashGet( meta, "sequence" );
1874
1875         growing_buffer* table_buf = buffer_init( 128 );
1876         growing_buffer* col_buf   = buffer_init( 128 );
1877         growing_buffer* val_buf   = buffer_init( 128 );
1878
1879         OSRF_BUFFER_ADD( table_buf, "INSERT INTO " );
1880         OSRF_BUFFER_ADD( table_buf, osrfHashGet( meta, "tablename" ));
1881         OSRF_BUFFER_ADD_CHAR( col_buf, '(' );
1882         buffer_add( val_buf,"VALUES (" );
1883
1884
1885         int first = 1;
1886         osrfHash* field = NULL;
1887         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
1888         while( (field = osrfHashIteratorNext( field_itr ) ) ) {
1889
1890                 const char* field_name = osrfHashIteratorKey( field_itr );
1891
1892                 if( str_is_true( osrfHashGet( field, "virtual" ) ) )
1893                         continue;
1894
1895                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
1896
1897                 char* value;
1898                 if( field_object && field_object->classname ) {
1899                         value = oilsFMGetString(
1900                                 field_object,
1901                                 (char*)oilsIDLFindPath( "/%s/primarykey", field_object->classname )
1902                         );
1903                 } else if( field_object && JSON_BOOL == field_object->type ) {
1904                         if( jsonBoolIsTrue( field_object ) )
1905                                 value = strdup( "t" );
1906                         else
1907                                 value = strdup( "f" );
1908                 } else {
1909                         value = jsonObjectToSimpleString( field_object );
1910                 }
1911
1912                 if( first ) {
1913                         first = 0;
1914                 } else {
1915                         OSRF_BUFFER_ADD_CHAR( col_buf, ',' );
1916                         OSRF_BUFFER_ADD_CHAR( val_buf, ',' );
1917                 }
1918
1919                 buffer_add( col_buf, field_name );
1920
1921                 if( !field_object || field_object->type == JSON_NULL ) {
1922                         buffer_add( val_buf, "DEFAULT" );
1923
1924                 } else if( !strcmp( get_primitive( field ), "number" )) {
1925                         const char* numtype = get_datatype( field );
1926                         if( !strcmp( numtype, "INT8" )) {
1927                                 buffer_fadd( val_buf, "%lld", atoll( value ));
1928
1929                         } else if( !strcmp( numtype, "INT" )) {
1930                                 buffer_fadd( val_buf, "%d", atoi( value ));
1931
1932                         } else if( !strcmp( numtype, "NUMERIC" )) {
1933                                 buffer_fadd( val_buf, "%f", atof( value ));
1934                         }
1935                 } else {
1936                         if( dbi_conn_quote_string( writehandle, &value )) {
1937                                 OSRF_BUFFER_ADD( val_buf, value );
1938
1939                         } else {
1940                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value );
1941                                 osrfAppSessionStatus(
1942                                         ctx->session,
1943                                         OSRF_STATUS_INTERNALSERVERERROR,
1944                                         "osrfMethodException",
1945                                         ctx->request,
1946                                         "Error quoting string -- please see the error log for more details"
1947                                 );
1948                                 free( value );
1949                                 buffer_free( table_buf );
1950                                 buffer_free( col_buf );
1951                                 buffer_free( val_buf );
1952                                 osrfAppRespondComplete( ctx, NULL );
1953                                 return -1;
1954                         }
1955                 }
1956
1957                 free( value );
1958         }
1959
1960         osrfHashIteratorFree( field_itr );
1961
1962         OSRF_BUFFER_ADD_CHAR( col_buf, ')' );
1963         OSRF_BUFFER_ADD_CHAR( val_buf, ')' );
1964
1965         char* table_str = buffer_release( table_buf );
1966         char* col_str   = buffer_release( col_buf );
1967         char* val_str   = buffer_release( val_buf );
1968         growing_buffer* sql = buffer_init( 128 );
1969         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
1970         free( table_str );
1971         free( col_str );
1972         free( val_str );
1973
1974         char* query = buffer_release( sql );
1975
1976         osrfLogDebug( OSRF_LOG_MARK, "%s: Insert SQL [%s]", modulename, query );
1977
1978         jsonObject* obj = NULL;
1979         int rc = 0;
1980
1981         dbi_result result = dbi_conn_query( writehandle, query );
1982         if( !result ) {
1983                 obj = jsonNewObject( NULL );
1984                 osrfLogError(
1985                         OSRF_LOG_MARK,
1986                         "%s ERROR inserting %s object using query [%s]",
1987                         modulename,
1988                         osrfHashGet(meta, "fieldmapper"),
1989                         query
1990                 );
1991                 osrfAppSessionStatus(
1992                         ctx->session,
1993                         OSRF_STATUS_INTERNALSERVERERROR,
1994                         "osrfMethodException",
1995                         ctx->request,
1996                         "INSERT error -- please see the error log for more details"
1997                 );
1998                 rc = -1;
1999         } else {
2000
2001                 char* id = oilsFMGetString( target, pkey );
2002                 if( !id ) {
2003                         unsigned long long new_id = dbi_conn_sequence_last( writehandle, seq );
2004                         growing_buffer* _id = buffer_init( 10 );
2005                         buffer_fadd( _id, "%lld", new_id );
2006                         id = buffer_release( _id );
2007                 }
2008
2009                 // Find quietness specification, if present
2010                 const char* quiet_str = NULL;
2011                 if( options ) {
2012                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
2013                         if( quiet_obj )
2014                                 quiet_str = jsonObjectGetString( quiet_obj );
2015                 }
2016
2017                 if( str_is_true( quiet_str )) {  // if quietness is specified
2018                         obj = jsonNewObject( id );
2019                 }
2020                 else {
2021
2022                         // Fetch the row that we just inserted, so that we can return it to the client
2023                         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2024                         jsonObjectSetKey( where_clause, pkey, jsonNewObject( id ));
2025
2026                         int err = 0;
2027                         jsonObject* list = doFieldmapperSearch( ctx, meta, where_clause, NULL, &err );
2028                         if( err )
2029                                 rc = -1;
2030                         else
2031                                 obj = jsonObjectClone( jsonObjectGetIndex( list, 0 ));
2032
2033                         jsonObjectFree( list );
2034                         jsonObjectFree( where_clause );
2035                 }
2036
2037                 free( id );
2038         }
2039
2040         free( query );
2041         osrfAppRespondComplete( ctx, obj );
2042         jsonObjectFree( obj );
2043         return rc;
2044 }
2045
2046 /**
2047         @brief Implement the retrieve method.
2048         @param ctx Pointer to the method context.
2049         @param err Pointer through which to return an error code.
2050         @return If successful, a pointer to the result to be returned to the client;
2051         otherwise NULL.
2052
2053         From the method's class, fetch a row with a specified value in the primary key.  This
2054         method relies on the database design convention that a primary key consists of a single
2055         column.
2056
2057         Method parameters:
2058         - authkey (PCRUD only)
2059         - value of the primary key for the desired row, for building the WHERE clause
2060         - a JSON_HASH containing any other SQL clauses: select, join, etc.
2061
2062         Return to client: One row from the query.
2063 */
2064 int doRetrieve( osrfMethodContext* ctx ) {
2065         if(osrfMethodVerifyContext( ctx )) {
2066                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
2067                 return -1;
2068         }
2069
2070         if( enforce_pcrud )
2071                 timeout_needs_resetting = 1;
2072
2073         int id_pos = 0;
2074         int order_pos = 1;
2075
2076         if( enforce_pcrud ) {
2077                 id_pos = 1;
2078                 order_pos = 2;
2079         }
2080
2081         // Get the class metadata
2082         osrfHash* class_def = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
2083
2084         // Get the value of the primary key, from a method parameter
2085         const jsonObject* id_obj = jsonObjectGetIndex( ctx->params, id_pos );
2086
2087         osrfLogDebug(
2088                 OSRF_LOG_MARK,
2089                 "%s retrieving %s object with primary key value of %s",
2090                 modulename,
2091                 osrfHashGet( class_def, "fieldmapper" ),
2092                 jsonObjectGetString( id_obj )
2093         );
2094
2095         // Build a WHERE clause based on the key value
2096         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2097         jsonObjectSetKey(
2098                 where_clause,
2099                 osrfHashGet( class_def, "primarykey" ),  // name of key column
2100                 jsonObjectClone( id_obj )                // value of key column
2101         );
2102
2103         jsonObject* rest_of_query = jsonObjectGetIndex( ctx->params, order_pos );
2104
2105         // Do the query
2106         int err = 0;
2107         jsonObject* list = doFieldmapperSearch( ctx, class_def, where_clause, rest_of_query, &err );
2108
2109         jsonObjectFree( where_clause );
2110         if( err ) {
2111                 osrfAppRespondComplete( ctx, NULL );
2112                 return -1;
2113         }
2114
2115         jsonObject* obj = jsonObjectExtractIndex( list, 0 );
2116         jsonObjectFree( list );
2117
2118         if( enforce_pcrud ) {
2119                 if(!verifyObjectPCRUD( ctx, obj )) {
2120                         jsonObjectFree( obj );
2121
2122                         growing_buffer* msg = buffer_init( 128 );
2123                         OSRF_BUFFER_ADD( msg, modulename );
2124                         OSRF_BUFFER_ADD( msg, ": Insufficient permissions to retrieve object" );
2125
2126                         char* m = buffer_release( msg );
2127                         osrfAppSessionStatus( ctx->session, OSRF_STATUS_NOTALLOWED, "osrfMethodException",
2128                                         ctx->request, m );
2129                         free( m );
2130
2131                         osrfAppRespondComplete( ctx, NULL );
2132                         return -1;
2133                 }
2134         }
2135
2136         osrfAppRespondComplete( ctx, obj );
2137         jsonObjectFree( obj );
2138         return 0;
2139 }
2140
2141 /**
2142         @brief Translate a numeric value to a string representation for the database.
2143         @param field Pointer to the IDL field definition.
2144         @param value Pointer to a jsonObject holding the value of a field.
2145         @return Pointer to a newly allocated string.
2146
2147         The input object is typically a JSON_NUMBER, but it may be a JSON_STRING as long as
2148         its contents are numeric.  A non-numeric string is likely to result in invalid SQL,
2149         or (what is worse) valid SQL that is wrong.
2150
2151         If the datatype of the receiving field is not numeric, wrap the value in quotes.
2152
2153         The calling code is responsible for freeing the resulting string by calling free().
2154 */
2155 static char* jsonNumberToDBString( osrfHash* field, const jsonObject* value ) {
2156         growing_buffer* val_buf = buffer_init( 32 );
2157         const char* numtype = get_datatype( field );
2158
2159         // For historical reasons the following contains cruft that could be cleaned up.
2160         if( !strncmp( numtype, "INT", 3 ) ) {
2161                 if( value->type == JSON_NUMBER )
2162                         //buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
2163                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2164                 else {
2165                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2166                 }
2167
2168         } else if( !strcmp( numtype, "NUMERIC" )) {
2169                 if( value->type == JSON_NUMBER )
2170                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2171                 else {
2172                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2173                 }
2174
2175         } else {
2176                 // Presumably this was really intended to be a string, so quote it
2177                 char* str = jsonObjectToSimpleString( value );
2178                 if( dbi_conn_quote_string( dbhandle, &str )) {
2179                         OSRF_BUFFER_ADD( val_buf, str );
2180                         free( str );
2181                 } else {
2182                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]", modulename, str );
2183                         free( str );
2184                         buffer_free( val_buf );
2185                         return NULL;
2186                 }
2187         }
2188
2189         return buffer_release( val_buf );
2190 }
2191
2192 static char* searchINPredicate( const char* class_alias, osrfHash* field,
2193                 jsonObject* node, const char* op, osrfMethodContext* ctx ) {
2194         growing_buffer* sql_buf = buffer_init( 32 );
2195
2196         buffer_fadd(
2197                 sql_buf,
2198                 "\"%s\".%s ",
2199                 class_alias,
2200                 osrfHashGet( field, "name" )
2201         );
2202
2203         if( !op ) {
2204                 buffer_add( sql_buf, "IN (" );
2205         } else if( !strcasecmp( op,"not in" )) {
2206                 buffer_add( sql_buf, "NOT IN (" );
2207         } else {
2208                 buffer_add( sql_buf, "IN (" );
2209         }
2210
2211         if( node->type == JSON_HASH ) {
2212                 // subquery predicate
2213                 char* subpred = buildQuery( ctx, node, SUBSELECT );
2214                 if( ! subpred ) {
2215                         buffer_free( sql_buf );
2216                         return NULL;
2217                 }
2218
2219                 buffer_add( sql_buf, subpred );
2220                 free( subpred );
2221
2222         } else if( node->type == JSON_ARRAY ) {
2223                 // literal value list
2224                 int in_item_index = 0;
2225                 int in_item_first = 1;
2226                 const jsonObject* in_item;
2227                 while( (in_item = jsonObjectGetIndex( node, in_item_index++ )) ) {
2228
2229                         if( in_item_first )
2230                                 in_item_first = 0;
2231                         else
2232                                 buffer_add( sql_buf, ", " );
2233
2234                         // Sanity check
2235                         if( in_item->type != JSON_STRING && in_item->type != JSON_NUMBER ) {
2236                                 osrfLogError( OSRF_LOG_MARK,
2237                                                 "%s: Expected string or number within IN list; found %s",
2238                                                 modulename, json_type( in_item->type ) );
2239                                 buffer_free( sql_buf );
2240                                 return NULL;
2241                         }
2242
2243                         // Append the literal value -- quoted if not a number
2244                         if( JSON_NUMBER == in_item->type ) {
2245                                 char* val = jsonNumberToDBString( field, in_item );
2246                                 OSRF_BUFFER_ADD( sql_buf, val );
2247                                 free( val );
2248
2249                         } else if( !strcmp( get_primitive( field ), "number" )) {
2250                                 char* val = jsonNumberToDBString( field, in_item );
2251                                 OSRF_BUFFER_ADD( sql_buf, val );
2252                                 free( val );
2253
2254                         } else {
2255                                 char* key_string = jsonObjectToSimpleString( in_item );
2256                                 if( dbi_conn_quote_string( dbhandle, &key_string )) {
2257                                         OSRF_BUFFER_ADD( sql_buf, key_string );
2258                                         free( key_string );
2259                                 } else {
2260                                         osrfLogError( OSRF_LOG_MARK,
2261                                                         "%s: Error quoting key string [%s]", modulename, key_string );
2262                                         free( key_string );
2263                                         buffer_free( sql_buf );
2264                                         return NULL;
2265                                 }
2266                         }
2267                 }
2268
2269                 if( in_item_first ) {
2270                         osrfLogError(OSRF_LOG_MARK, "%s: Empty IN list", modulename );
2271                         buffer_free( sql_buf );
2272                         return NULL;
2273                 }
2274         } else {
2275                 osrfLogError( OSRF_LOG_MARK, "%s: Expected object or array for IN clause; found %s",
2276                         modulename, json_type( node->type ));
2277                 buffer_free( sql_buf );
2278                 return NULL;
2279         }
2280
2281         OSRF_BUFFER_ADD_CHAR( sql_buf, ')' );
2282
2283         return buffer_release( sql_buf );
2284 }
2285
2286 // Receive a JSON_ARRAY representing a function call.  The first
2287 // entry in the array is the function name.  The rest are parameters.
2288 static char* searchValueTransform( const jsonObject* array ) {
2289
2290         if( array->size < 1 ) {
2291                 osrfLogError( OSRF_LOG_MARK, "%s: Empty array for value transform", modulename );
2292                 return NULL;
2293         }
2294
2295         // Get the function name
2296         jsonObject* func_item = jsonObjectGetIndex( array, 0 );
2297         if( func_item->type != JSON_STRING ) {
2298                 osrfLogError( OSRF_LOG_MARK, "%s: Error: expected function name, found %s",
2299                         modulename, json_type( func_item->type ));
2300                 return NULL;
2301         }
2302
2303         growing_buffer* sql_buf = buffer_init( 32 );
2304
2305         OSRF_BUFFER_ADD( sql_buf, jsonObjectGetString( func_item ) );
2306         OSRF_BUFFER_ADD( sql_buf, "( " );
2307
2308         // Get the parameters
2309         int func_item_index = 1;   // We already grabbed the zeroth entry
2310         while( (func_item = jsonObjectGetIndex( array, func_item_index++ )) ) {
2311
2312                 // Add a separator comma, if we need one
2313                 if( func_item_index > 2 )
2314                         buffer_add( sql_buf, ", " );
2315
2316                 // Add the current parameter
2317                 if( func_item->type == JSON_NULL ) {
2318                         buffer_add( sql_buf, "NULL" );
2319                 } else {
2320                         char* val = jsonObjectToSimpleString( func_item );
2321                         if( dbi_conn_quote_string( dbhandle, &val )) {
2322                                 OSRF_BUFFER_ADD( sql_buf, val );
2323                                 free( val );
2324                         } else {
2325                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2326                                         modulename, val );
2327                                 buffer_free( sql_buf );
2328                                 free( val );
2329                                 return NULL;
2330                         }
2331                 }
2332         }
2333
2334         buffer_add( sql_buf, " )" );
2335
2336         return buffer_release( sql_buf );
2337 }
2338
2339 static char* searchFunctionPredicate( const char* class_alias, osrfHash* field,
2340                 const jsonObject* node, const char* op ) {
2341
2342         if( ! is_good_operator( op ) ) {
2343                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2344                 return NULL;
2345         }
2346
2347         char* val = searchValueTransform( node );
2348         if( !val )
2349                 return NULL;
2350
2351         growing_buffer* sql_buf = buffer_init( 32 );
2352         buffer_fadd(
2353                 sql_buf,
2354                 "\"%s\".%s %s %s",
2355                 class_alias,
2356                 osrfHashGet( field, "name" ),
2357                 op,
2358                 val
2359         );
2360
2361         free( val );
2362
2363         return buffer_release( sql_buf );
2364 }
2365
2366 // class_alias is a class name or other table alias
2367 // field is a field definition as stored in the IDL
2368 // node comes from the method parameter, and may represent an entry in the SELECT list
2369 static char* searchFieldTransform( const char* class_alias, osrfHash* field,
2370                 const jsonObject* node ) {
2371         growing_buffer* sql_buf = buffer_init( 32 );
2372
2373         const char* field_transform = jsonObjectGetString(
2374                 jsonObjectGetKeyConst( node, "transform" ) );
2375         const char* transform_subcolumn = jsonObjectGetString(
2376                 jsonObjectGetKeyConst( node, "result_field" ) );
2377
2378         if( transform_subcolumn ) {
2379                 if( ! is_identifier( transform_subcolumn ) ) {
2380                         osrfLogError( OSRF_LOG_MARK, "%s: Invalid subfield name: \"%s\"\n",
2381                                         modulename, transform_subcolumn );
2382                         buffer_free( sql_buf );
2383                         return NULL;
2384                 }
2385                 OSRF_BUFFER_ADD_CHAR( sql_buf, '(' );    // enclose transform in parentheses
2386         }
2387
2388         if( field_transform ) {
2389
2390                 if( ! is_identifier( field_transform ) ) {
2391                         osrfLogError( OSRF_LOG_MARK, "%s: Expected function name, found \"%s\"\n",
2392                                         modulename, field_transform );
2393                         buffer_free( sql_buf );
2394                         return NULL;
2395                 }
2396
2397                 if( obj_is_true( jsonObjectGetKeyConst( node, "distinct" ) ) ) {
2398                         buffer_fadd( sql_buf, "%s(DISTINCT \"%s\".%s",
2399                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2400                 } else {
2401                         buffer_fadd( sql_buf, "%s(\"%s\".%s",
2402                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2403                 }
2404
2405                 const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
2406
2407                 if( array ) {
2408                         if( array->type != JSON_ARRAY ) {
2409                                 osrfLogError( OSRF_LOG_MARK,
2410                                         "%s: Expected JSON_ARRAY for function params; found %s",
2411                                         modulename, json_type( array->type ) );
2412                                 buffer_free( sql_buf );
2413                                 return NULL;
2414                         }
2415                         int func_item_index = 0;
2416                         jsonObject* func_item;
2417                         while( (func_item = jsonObjectGetIndex( array, func_item_index++ ))) {
2418
2419                                 char* val = jsonObjectToSimpleString( func_item );
2420
2421                                 if( !val ) {
2422                                         buffer_add( sql_buf, ",NULL" );
2423                                 } else if( dbi_conn_quote_string( dbhandle, &val )) {
2424                                         OSRF_BUFFER_ADD_CHAR( sql_buf, ',' );
2425                                         OSRF_BUFFER_ADD( sql_buf, val );
2426                                 } else {
2427                                         osrfLogError( OSRF_LOG_MARK,
2428                                                         "%s: Error quoting key string [%s]", modulename, val );
2429                                         free( val );
2430                                         buffer_free( sql_buf );
2431                                         return NULL;
2432                                 }
2433                                 free( val );
2434                         }
2435                 }
2436
2437                 buffer_add( sql_buf, " )" );
2438
2439         } else {
2440                 buffer_fadd( sql_buf, "\"%s\".%s", class_alias, osrfHashGet( field, "name" ));
2441         }
2442
2443         if( transform_subcolumn )
2444                 buffer_fadd( sql_buf, ").\"%s\"", transform_subcolumn );
2445
2446         return buffer_release( sql_buf );
2447 }
2448
2449 static char* searchFieldTransformPredicate( const ClassInfo* class_info, osrfHash* field,
2450                 const jsonObject* node, const char* op ) {
2451
2452         if( ! is_good_operator( op ) ) {
2453                 osrfLogError( OSRF_LOG_MARK, "%s: Error: Invalid operator %s", modulename, op );
2454                 return NULL;
2455         }
2456
2457         char* field_transform = searchFieldTransform( class_info->alias, field, node );
2458         if( ! field_transform )
2459                 return NULL;
2460         char* value = NULL;
2461         int extra_parens = 0;   // boolean
2462
2463         const jsonObject* value_obj = jsonObjectGetKeyConst( node, "value" );
2464         if( ! value_obj ) {
2465                 value = searchWHERE( node, class_info, AND_OP_JOIN, NULL );
2466                 if( !value ) {
2467                         osrfLogError( OSRF_LOG_MARK, "%s: Error building condition for field transform",
2468                                 modulename );
2469                         free( field_transform );
2470                         return NULL;
2471                 }
2472                 extra_parens = 1;
2473         } else if( value_obj->type == JSON_ARRAY ) {
2474                 value = searchValueTransform( value_obj );
2475                 if( !value ) {
2476                         osrfLogError( OSRF_LOG_MARK,
2477                                 "%s: Error building value transform for field transform", modulename );
2478                         free( field_transform );
2479                         return NULL;
2480                 }
2481         } else if( value_obj->type == JSON_HASH ) {
2482                 value = searchWHERE( value_obj, class_info, AND_OP_JOIN, NULL );
2483                 if( !value ) {
2484                         osrfLogError( OSRF_LOG_MARK, "%s: Error building predicate for field transform",
2485                                 modulename );
2486                         free( field_transform );
2487                         return NULL;
2488                 }
2489                 extra_parens = 1;
2490         } else if( value_obj->type == JSON_NUMBER ) {
2491                 value = jsonNumberToDBString( field, value_obj );
2492         } else if( value_obj->type == JSON_NULL ) {
2493                 osrfLogError( OSRF_LOG_MARK,
2494                         "%s: Error building predicate for field transform: null value", modulename );
2495                 free( field_transform );
2496                 return NULL;
2497         } else if( value_obj->type == JSON_BOOL ) {
2498                 osrfLogError( OSRF_LOG_MARK,
2499                         "%s: Error building predicate for field transform: boolean value", modulename );
2500                 free( field_transform );
2501                 return NULL;
2502         } else {
2503                 if( !strcmp( get_primitive( field ), "number") ) {
2504                         value = jsonNumberToDBString( field, value_obj );
2505                 } else {
2506                         value = jsonObjectToSimpleString( value_obj );
2507                         if( !dbi_conn_quote_string( dbhandle, &value )) {
2508                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2509                                         modulename, value );
2510                                 free( value );
2511                                 free( field_transform );
2512                                 return NULL;
2513                         }
2514                 }
2515         }
2516
2517         const char* left_parens  = "";
2518         const char* right_parens = "";
2519
2520         if( extra_parens ) {
2521                 left_parens  = "(";
2522                 right_parens = ")";
2523         }
2524
2525         growing_buffer* sql_buf = buffer_init( 32 );
2526
2527         buffer_fadd(
2528                 sql_buf,
2529                 "%s%s %s %s %s %s%s",
2530                 left_parens,
2531                 field_transform,
2532                 op,
2533                 left_parens,
2534                 value,
2535                 right_parens,
2536                 right_parens
2537         );
2538
2539         free( value );
2540         free( field_transform );
2541
2542         return buffer_release( sql_buf );
2543 }
2544
2545 static char* searchSimplePredicate( const char* op, const char* class_alias,
2546                 osrfHash* field, const jsonObject* node ) {
2547
2548         if( ! is_good_operator( op ) ) {
2549                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2550                 return NULL;
2551         }
2552
2553         char* val = NULL;
2554
2555         // Get the value to which we are comparing the specified column
2556         if( node->type != JSON_NULL ) {
2557                 if( node->type == JSON_NUMBER ) {
2558                         val = jsonNumberToDBString( field, node );
2559                 } else if( !strcmp( get_primitive( field ), "number" ) ) {
2560                         val = jsonNumberToDBString( field, node );
2561                 } else {
2562                         val = jsonObjectToSimpleString( node );
2563                 }
2564         }
2565
2566         if( val ) {
2567                 if( JSON_NUMBER != node->type && strcmp( get_primitive( field ), "number") ) {
2568                         // Value is not numeric; enclose it in quotes
2569                         if( !dbi_conn_quote_string( dbhandle, &val ) ) {
2570                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2571                                         modulename, val );
2572                                 free( val );
2573                                 return NULL;
2574                         }
2575                 }
2576         } else {
2577                 // Compare to a null value
2578                 val = strdup( "NULL" );
2579                 if( strcmp( op, "=" ))
2580                         op = "IS NOT";
2581                 else
2582                         op = "IS";
2583         }
2584
2585         growing_buffer* sql_buf = buffer_init( 32 );
2586         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class_alias, osrfHashGet(field, "name"), op, val );
2587         char* pred = buffer_release( sql_buf );
2588
2589         free( val );
2590
2591         return pred;
2592 }
2593
2594 static char* searchBETWEENPredicate( const char* class_alias,
2595                 osrfHash* field, const jsonObject* node ) {
2596
2597         const jsonObject* x_node = jsonObjectGetIndex( node, 0 );
2598         const jsonObject* y_node = jsonObjectGetIndex( node, 1 );
2599
2600         if( NULL == y_node ) {
2601                 osrfLogError( OSRF_LOG_MARK, "%s: Not enough operands for BETWEEN operator", modulename );
2602                 return NULL;
2603         }
2604         else if( NULL != jsonObjectGetIndex( node, 2 ) ) {
2605                 osrfLogError( OSRF_LOG_MARK, "%s: Too many operands for BETWEEN operator", modulename );
2606                 return NULL;
2607         }
2608
2609         char* x_string;
2610         char* y_string;
2611
2612         if( !strcmp( get_primitive( field ), "number") ) {
2613                 x_string = jsonNumberToDBString( field, x_node );
2614                 y_string = jsonNumberToDBString( field, y_node );
2615
2616         } else {
2617                 x_string = jsonObjectToSimpleString( x_node );
2618                 y_string = jsonObjectToSimpleString( y_node );
2619                 if( !(dbi_conn_quote_string( dbhandle, &x_string )
2620                         && dbi_conn_quote_string( dbhandle, &y_string )) ) {
2621                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]",
2622                                         modulename, x_string, y_string );
2623                         free( x_string );
2624                         free( y_string );
2625                         return NULL;
2626                 }
2627         }
2628
2629         growing_buffer* sql_buf = buffer_init( 32 );
2630         buffer_fadd( sql_buf, "\"%s\".%s BETWEEN %s AND %s",
2631                         class_alias, osrfHashGet( field, "name" ), x_string, y_string );
2632         free( x_string );
2633         free( y_string );
2634
2635         return buffer_release( sql_buf );
2636 }
2637
2638 static char* searchPredicate( const ClassInfo* class_info, osrfHash* field,
2639                                                           jsonObject* node, osrfMethodContext* ctx ) {
2640
2641         char* pred = NULL;
2642         if( node->type == JSON_ARRAY ) { // equality IN search
2643                 pred = searchINPredicate( class_info->alias, field, node, NULL, ctx );
2644         } else if( node->type == JSON_HASH ) { // other search
2645                 jsonIterator* pred_itr = jsonNewIterator( node );
2646                 if( !jsonIteratorHasNext( pred_itr ) ) {
2647                         osrfLogError( OSRF_LOG_MARK, "%s: Empty predicate for field \"%s\"",
2648                                         modulename, osrfHashGet(field, "name" ));
2649                 } else {
2650                         jsonObject* pred_node = jsonIteratorNext( pred_itr );
2651
2652                         // Verify that there are no additional predicates
2653                         if( jsonIteratorHasNext( pred_itr ) ) {
2654                                 osrfLogError( OSRF_LOG_MARK, "%s: Multiple predicates for field \"%s\"",
2655                                                 modulename, osrfHashGet(field, "name" ));
2656                         } else if( !(strcasecmp( pred_itr->key,"between" )) )
2657                                 pred = searchBETWEENPredicate( class_info->alias, field, pred_node );
2658                         else if( !(strcasecmp( pred_itr->key,"in" ))
2659                                         || !(strcasecmp( pred_itr->key,"not in" )) )
2660                                 pred = searchINPredicate(
2661                                         class_info->alias, field, pred_node, pred_itr->key, ctx );
2662                         else if( pred_node->type == JSON_ARRAY )
2663                                 pred = searchFunctionPredicate(
2664                                         class_info->alias, field, pred_node, pred_itr->key );
2665                         else if( pred_node->type == JSON_HASH )
2666                                 pred = searchFieldTransformPredicate(
2667                                         class_info, field, pred_node, pred_itr->key );
2668                         else
2669                                 pred = searchSimplePredicate( pred_itr->key, class_info->alias, field, pred_node );
2670                 }
2671                 jsonIteratorFree( pred_itr );
2672
2673         } else if( node->type == JSON_NULL ) { // IS NULL search
2674                 growing_buffer* _p = buffer_init( 64 );
2675                 buffer_fadd(
2676                         _p,
2677                         "\"%s\".%s IS NULL",
2678                         class_info->class_name,
2679                         osrfHashGet( field, "name" )
2680                 );
2681                 pred = buffer_release( _p );
2682         } else { // equality search
2683                 pred = searchSimplePredicate( "=", class_info->alias, field, node );
2684         }
2685
2686         return pred;
2687
2688 }
2689
2690
2691 /*
2692
2693 join : {
2694         acn : {
2695                 field : record,
2696                 fkey : id
2697                 type : left
2698                 filter_op : or
2699                 filter : { ... },
2700                 join : {
2701                         acp : {
2702                                 field : call_number,
2703                                 fkey : id,
2704                                 filter : { ... },
2705                         },
2706                 },
2707         },
2708         mrd : {
2709                 field : record,
2710                 type : inner
2711                 fkey : id,
2712                 filter : { ... },
2713         }
2714 }
2715
2716 */
2717
2718 static char* searchJOIN( const jsonObject* join_hash, const ClassInfo* left_info ) {
2719
2720         const jsonObject* working_hash;
2721         jsonObject* freeable_hash = NULL;
2722
2723         if( join_hash->type == JSON_HASH ) {
2724                 working_hash = join_hash;
2725         } else if( join_hash->type == JSON_STRING ) {
2726                 // turn it into a JSON_HASH by creating a wrapper
2727                 // around a copy of the original
2728                 const char* _tmp = jsonObjectGetString( join_hash );
2729                 freeable_hash = jsonNewObjectType( JSON_HASH );
2730                 jsonObjectSetKey( freeable_hash, _tmp, NULL );
2731                 working_hash = freeable_hash;
2732         } else {
2733                 osrfLogError(
2734                         OSRF_LOG_MARK,
2735                         "%s: JOIN failed; expected JSON object type not found",
2736                         modulename
2737                 );
2738                 return NULL;
2739         }
2740
2741         growing_buffer* join_buf = buffer_init( 128 );
2742         const char* leftclass = left_info->class_name;
2743
2744         jsonObject* snode = NULL;
2745         jsonIterator* search_itr = jsonNewIterator( working_hash );
2746
2747         while ( (snode = jsonIteratorNext( search_itr )) ) {
2748                 const char* right_alias = search_itr->key;
2749                 const char* class =
2750                                 jsonObjectGetString( jsonObjectGetKeyConst( snode, "class" ) );
2751                 if( ! class )
2752                         class = right_alias;
2753
2754                 const ClassInfo* right_info = add_joined_class( right_alias, class );
2755                 if( !right_info ) {
2756                         osrfLogError(
2757                                 OSRF_LOG_MARK,
2758                                 "%s: JOIN failed.  Class \"%s\" not resolved in IDL",
2759                                 modulename,
2760                                 search_itr->key
2761                         );
2762                         jsonIteratorFree( search_itr );
2763                         buffer_free( join_buf );
2764                         if( freeable_hash )
2765                                 jsonObjectFree( freeable_hash );
2766                         return NULL;
2767                 }
2768                 osrfHash* links    = right_info->links;
2769                 const char* table  = right_info->source_def;
2770
2771                 const char* fkey  = jsonObjectGetString( jsonObjectGetKeyConst( snode, "fkey" ) );
2772                 const char* field = jsonObjectGetString( jsonObjectGetKeyConst( snode, "field" ) );
2773
2774                 if( field && !fkey ) {
2775                         // Look up the corresponding join column in the IDL.
2776                         // The link must be defined in the child table,
2777                         // and point to the right parent table.
2778                         osrfHash* idl_link = (osrfHash*) osrfHashGet( links, field );
2779                         const char* reltype = NULL;
2780                         const char* other_class = NULL;
2781                         reltype = osrfHashGet( idl_link, "reltype" );
2782                         if( reltype && strcmp( reltype, "has_many" ) )
2783                                 other_class = osrfHashGet( idl_link, "class" );
2784                         if( other_class && !strcmp( other_class, leftclass ) )
2785                                 fkey = osrfHashGet( idl_link, "key" );
2786                         if( !fkey ) {
2787                                 osrfLogError(
2788                                         OSRF_LOG_MARK,
2789                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2790                                         modulename,
2791                                         class,
2792                                         field,
2793                                         leftclass
2794                                 );
2795                                 buffer_free( join_buf );
2796                                 if( freeable_hash )
2797                                         jsonObjectFree( freeable_hash );
2798                                 jsonIteratorFree( search_itr );
2799                                 return NULL;
2800                         }
2801
2802                 } else if( !field && fkey ) {
2803                         // Look up the corresponding join column in the IDL.
2804                         // The link must be defined in the child table,
2805                         // and point to the right parent table.
2806                         osrfHash* left_links = left_info->links;
2807                         osrfHash* idl_link = (osrfHash*) osrfHashGet( left_links, fkey );
2808                         const char* reltype = NULL;
2809                         const char* other_class = NULL;
2810                         reltype = osrfHashGet( idl_link, "reltype" );
2811                         if( reltype && strcmp( reltype, "has_many" ) )
2812                                 other_class = osrfHashGet( idl_link, "class" );
2813                         if( other_class && !strcmp( other_class, class ) )
2814                                 field = osrfHashGet( idl_link, "key" );
2815                         if( !field ) {
2816                                 osrfLogError(
2817                                         OSRF_LOG_MARK,
2818                                         "%s: JOIN failed.  No link defined from %s.%s to %s",
2819                                         modulename,
2820                                         leftclass,
2821                                         fkey,
2822                                         class
2823                                 );
2824                                 buffer_free( join_buf );
2825                                 if( freeable_hash )
2826                                         jsonObjectFree( freeable_hash );
2827                                 jsonIteratorFree( search_itr );
2828                                 return NULL;
2829                         }
2830
2831                 } else if( !field && !fkey ) {
2832                         osrfHash* left_links = left_info->links;
2833
2834                         // For each link defined for the left class:
2835                         // see if the link references the joined class
2836                         osrfHashIterator* itr = osrfNewHashIterator( left_links );
2837                         osrfHash* curr_link = NULL;
2838                         while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2839                                 const char* other_class = osrfHashGet( curr_link, "class" );
2840                                 if( other_class && !strcmp( other_class, class ) ) {
2841
2842                                         // In the IDL, the parent class doesn't always know then names of the child
2843                                         // columns that are pointing to it, so don't use that end of the link
2844                                         const char* reltype = osrfHashGet( curr_link, "reltype" );
2845                                         if( reltype && strcmp( reltype, "has_many" ) ) {
2846                                                 // Found a link between the classes
2847                                                 fkey = osrfHashIteratorKey( itr );
2848                                                 field = osrfHashGet( curr_link, "key" );
2849                                                 break;
2850                                         }
2851                                 }
2852                         }
2853                         osrfHashIteratorFree( itr );
2854
2855                         if( !field || !fkey ) {
2856                                 // Do another such search, with the classes reversed
2857
2858                                 // For each link defined for the joined class:
2859                                 // see if the link references the left class
2860                                 osrfHashIterator* itr = osrfNewHashIterator( links );
2861                                 osrfHash* curr_link = NULL;
2862                                 while( (curr_link = osrfHashIteratorNext( itr ) ) ) {
2863                                         const char* other_class = osrfHashGet( curr_link, "class" );
2864                                         if( other_class && !strcmp( other_class, leftclass ) ) {
2865
2866          &nb