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