1. Degrade (relatively) gracefully when the database connection dies.
[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
1383         // Build a list of org units that own the row.  This is fairly convoluted because there
1384         // are several different ways that an org unit may own the row, as defined by the
1385         // permacrud entry.
1386
1387         // Local context means that the row includes a foreign key pointing to actor.org_unit,
1388         // identifying an owning org_unit..
1389         osrfStringArray* local_context = osrfHashGet( pcrud, "local_context" );
1390
1391         // Foreign context adds a layer of indirection.  The row points to some other row that
1392         // an org unit may own.  The "jump" attribute, if present, adds another layer of
1393         // indirection.
1394         osrfHash* foreign_context = osrfHashGet( pcrud, "foreign_context" );
1395
1396         // The following string array stores the list of org units.  (We don't have a thingie
1397         // for storing lists of integers, so we fake it with a list of strings.)
1398         osrfStringArray* context_org_array = osrfNewStringArray( 1 );
1399
1400         int err = 0;
1401         const char* pkey_value = NULL;
1402         if( str_is_true( osrfHashGet(pcrud, "global_required") ) ) {
1403                 // If the global_required attribute is present and true, then the only owning
1404                 // org unit is the root org unit, i.e. the one with no parent.
1405                 osrfLogDebug( OSRF_LOG_MARK,
1406                                 "global-level permissions required, fetching top of the org tree" );
1407
1408                 // check for perm at top of org tree
1409                 const char* org_tree_root_id = org_tree_root( ctx );
1410                 if( org_tree_root_id ) {
1411                         osrfStringArrayAdd( context_org_array, org_tree_root_id );
1412                         osrfLogDebug( OSRF_LOG_MARK, "top of the org tree is %s", org_tree_root_id );
1413                 } else  {
1414                         osrfStringArrayFree( context_org_array );
1415                         return 0;
1416                 }
1417
1418         } else {
1419                 // If the global_required attribute is absent or false, then we look for
1420                 // local and/or foreign context.  In order to find the relevant foreign
1421                 // keys, we must either read the relevant row from the database, or look at
1422                 // the image of the row that we already have in memory.
1423
1424                 // (Herein lies a bug.  Even if we have an image of the row in memory, that
1425                 // image may not include the foreign key column(s) that we need.)
1426
1427             osrfLogDebug( OSRF_LOG_MARK, "global-level permissions not required, "
1428                                 "fetching context org ids" );
1429             const char* pkey = osrfHashGet( class, "primarykey" );
1430                 jsonObject *param = NULL;
1431
1432                 if( obj->classname ) {
1433                         pkey_value = oilsFMGetStringConst( obj, pkey );
1434                         if( !fetch )
1435                                 param = jsonObjectClone( obj );
1436                         osrfLogDebug( OSRF_LOG_MARK, "Object supplied, using primary key value of %s",
1437                                         pkey_value );
1438                 } else {
1439                         pkey_value = jsonObjectGetString( obj );
1440                         fetch = 1;
1441                         osrfLogDebug( OSRF_LOG_MARK, "Object not supplied, using primary key value "
1442                                         "of %s and retrieving from the database", pkey_value );
1443                 }
1444
1445                 if( fetch ) {
1446                         // Fetch the row so that we can look at the foreign key(s)
1447                         jsonObject* _tmp_params = single_hash( pkey, pkey_value );
1448                         jsonObject* _list = doFieldmapperSearch( ctx, class, _tmp_params, NULL, &err );
1449                         jsonObjectFree( _tmp_params );
1450
1451                         param = jsonObjectExtractIndex( _list, 0 );
1452                         jsonObjectFree( _list );
1453                 }
1454
1455                 if( !param ) {
1456                         // The row doesn't exist.  Complain, and deny access.
1457                         osrfLogDebug( OSRF_LOG_MARK,
1458                                         "Object not found in the database with primary key %s of %s",
1459                                         pkey, pkey_value );
1460
1461                         growing_buffer* msg = buffer_init( 128 );
1462                         buffer_fadd(
1463                                 msg,
1464                                 "%s: no object found with primary key %s of %s",
1465                                 modulename,
1466                                 pkey,
1467                                 pkey_value
1468                         );
1469
1470                         char* m = buffer_release( msg );
1471                         osrfAppSessionStatus(
1472                                 ctx->session,
1473                                 OSRF_STATUS_INTERNALSERVERERROR,
1474                                 "osrfMethodException",
1475                                 ctx->request,
1476                                 m
1477                         );
1478
1479                         free( m );
1480                         return 0;
1481                 }
1482
1483                 if( local_context && local_context->size > 0 ) {
1484                         // The IDL provides a list of column names for the foreign keys denoting
1485                         // local context.  Look up the value of each one, and if it isn't null,
1486                         // add it to the list of org units.
1487                         osrfLogDebug( OSRF_LOG_MARK, "%d class-local context field(s) specified",
1488                                         local_context->size );
1489                         int i = 0;
1490                         const char* lcontext = NULL;
1491                         while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) {
1492                                 const char* fkey_value = oilsFMGetStringConst( param, lcontext );
1493                                 if( fkey_value ) {    // if not null
1494                                         osrfStringArrayAdd( context_org_array, fkey_value );
1495                                         osrfLogDebug(
1496                                                 OSRF_LOG_MARK,
1497                                                 "adding class-local field %s (value: %s) to the context org list",
1498                                                 lcontext,
1499                                                 osrfStringArrayGetString( context_org_array, context_org_array->size - 1 )
1500                                         );
1501                                 }
1502                         }
1503                 }
1504
1505                 if( foreign_context ) {
1506                         unsigned long class_count = osrfHashGetCount( foreign_context );
1507                         osrfLogDebug( OSRF_LOG_MARK, "%d foreign context classes(s) specified", class_count );
1508
1509                         if( class_count > 0 ) {
1510
1511                                 // The IDL provides a list of foreign key columns pointing to rows that
1512                                 // an org unit may own.  Follow each link, identify the owning org unit,
1513                                 // and add it to the list.
1514                                 osrfHash* fcontext = NULL;
1515                                 osrfHashIterator* class_itr = osrfNewHashIterator( foreign_context );
1516                                 while( (fcontext = osrfHashIteratorNext( class_itr )) ) {
1517                                         // For each class to which a foreign key points:
1518                                         const char* class_name = osrfHashIteratorKey( class_itr );
1519                                         osrfHash* fcontext = osrfHashGet( foreign_context, class_name );
1520
1521                                         osrfLogDebug(
1522                                                 OSRF_LOG_MARK,
1523                                                 "%d foreign context fields(s) specified for class %s",
1524                                                 ((osrfStringArray*)osrfHashGet(fcontext,"context"))->size,
1525                                                 class_name
1526                                         );
1527
1528                                         // Get the name of the key field in the foreign table
1529                                         const char* foreign_pkey = osrfHashGet( fcontext, "field" );
1530
1531                                         // Get the value of the foreign key pointing to the foreign table
1532                                         char* foreign_pkey_value =
1533                                                         oilsFMGetString( param, osrfHashGet( fcontext, "fkey" ));
1534                                         if( !foreign_pkey_value )
1535                                                 continue;    // Foreign key value is null; skip it
1536
1537                                         // Look up the row to which the foreign key points
1538                                         jsonObject* _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1539                                         jsonObject* _list = doFieldmapperSearch(
1540                                                 ctx, osrfHashGet( oilsIDL(), class_name ), _tmp_params, NULL, &err );
1541
1542                                         jsonObject* _fparam = NULL;
1543                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1544                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1545
1546                                         jsonObjectFree( _tmp_params );
1547                                         jsonObjectFree( _list );
1548
1549                                         // At this point _fparam either points to the row identified by the
1550                                         // foreign key, or it's NULL (no such row found).
1551
1552                                         osrfStringArray* jump_list = osrfHashGet( fcontext, "jump" );
1553
1554                                         const char* bad_class = NULL;  // For noting failed lookups
1555                                         if( ! _fparam )
1556                                                 bad_class = class_name;    // Referenced row not found
1557                                         else if( jump_list ) {
1558                                                 // Follow a chain of rows, linked by foreign keys, to find an owner
1559                                                 const char* flink = NULL;
1560                                                 int k = 0;
1561                                                 while ( (flink = osrfStringArrayGetString(jump_list, k++)) && _fparam ) {
1562                                                         // For each entry in the jump list.  Each entry (i.e. flink) is
1563                                                         // the name of a foreign key column in the current row.
1564
1565                                                         // From the IDL, get the linkage information for the next jump
1566                                                         osrfHash* foreign_link_hash =
1567                                                                         oilsIDLFindPath( "/%s/links/%s", _fparam->classname, flink );
1568
1569                                                         // Get the class metadata for the class
1570                                                         // to which the foreign key points
1571                                                         osrfHash* foreign_class_meta = osrfHashGet( oilsIDL(),
1572                                                                         osrfHashGet( foreign_link_hash, "class" ));
1573
1574                                                         // Get the name of the referenced key of that class
1575                                                         foreign_pkey = osrfHashGet( foreign_link_hash, "key" );
1576
1577                                                         // Get the value of the foreign key pointing to that class
1578                                                         free( foreign_pkey_value );
1579                                                         foreign_pkey_value = oilsFMGetString( _fparam, flink );
1580                                                         if( !foreign_pkey_value )
1581                                                                 break;    // Foreign key is null; quit looking
1582
1583                                                         // Build a WHERE clause for the lookup
1584                                                         _tmp_params = single_hash( foreign_pkey, foreign_pkey_value );
1585
1586                                                         // Do the lookup
1587                                                         _list = doFieldmapperSearch( ctx, foreign_class_meta,
1588                                                                         _tmp_params, NULL, &err );
1589
1590                                                         // Get the resulting row
1591                                                         jsonObjectFree( _fparam );
1592                                                         if( _list && JSON_ARRAY == _list->type && _list->size > 0 )
1593                                                                 _fparam = jsonObjectExtractIndex( _list, 0 );
1594                                                         else {
1595                                                                 // Referenced row not found
1596                                                                 _fparam = NULL;
1597                                                                 bad_class = osrfHashGet( foreign_link_hash, "class" );
1598                                                         }
1599
1600                                                         jsonObjectFree( _tmp_params );
1601                                                         jsonObjectFree( _list );
1602                                                 }
1603                                         }
1604
1605                                         if( bad_class ) {
1606
1607                                                 // We had a foreign key pointing to such-and-such a row, but then
1608                                                 // we couldn't fetch that row.  The data in the database are in an
1609                                                 // inconsistent state; the database itself may even be corrupted.
1610                                                 growing_buffer* msg = buffer_init( 128 );
1611                                                 buffer_fadd(
1612                                                         msg,
1613                                                         "%s: no object of class %s found with primary key %s of %s",
1614                                                         modulename,
1615                                                         bad_class,
1616                                                         foreign_pkey,
1617                                                         foreign_pkey_value ? foreign_pkey_value : "(null)"
1618                                                 );
1619
1620                                                 char* m = buffer_release( msg );
1621                                                 osrfAppSessionStatus(
1622                                                         ctx->session,
1623                                                         OSRF_STATUS_INTERNALSERVERERROR,
1624                                                         "osrfMethodException",
1625                                                         ctx->request,
1626                                                         m
1627                                                 );
1628
1629                                                 free( m );
1630                                                 osrfHashIteratorFree( class_itr );
1631                                                 free( foreign_pkey_value );
1632                                                 jsonObjectFree( param );
1633
1634                                                 return 0;
1635                                         }
1636
1637                                         free( foreign_pkey_value );
1638
1639                                         if( _fparam ) {
1640                                                 // Examine each context column of the foreign row,
1641                                                 // and add its value to the list of org units.
1642                                                 int j = 0;
1643                                                 const char* foreign_field = NULL;
1644                                                 osrfStringArray* ctx_array = osrfHashGet( fcontext, "context" );
1645                                                 while ( (foreign_field = osrfStringArrayGetString( ctx_array, j++ )) ) {
1646                                                         osrfStringArrayAdd( context_org_array,
1647                                                                 oilsFMGetStringConst( _fparam, foreign_field ));
1648                                                         osrfLogDebug( OSRF_LOG_MARK,
1649                                                                 "adding foreign class %s field %s (value: %s) "
1650                                                                         "to the context org list",
1651                                                                 class_name,
1652                                                                 foreign_field,
1653                                                                 osrfStringArrayGetString(
1654                                                                         context_org_array, context_org_array->size - 1 )
1655                                                         );
1656                                                 }
1657
1658                                                 jsonObjectFree( _fparam );
1659                                         }
1660                                 }
1661
1662                                 osrfHashIteratorFree( class_itr );
1663                         }
1664                 }
1665
1666                 jsonObjectFree( param );
1667         }
1668
1669         const char* context_org = NULL;
1670         const char* perm = NULL;
1671         int OK = 0;
1672
1673         if( permission->size == 0 ) {
1674             osrfLogDebug( OSRF_LOG_MARK, "No permission specified for this action, passing through" );
1675                 OK = 1;
1676         }
1677
1678         // For every combination of permission and context org unit: call a stored procedure
1679         // to determine if the user has this permission in the context of this org unit.
1680         // If the answer is yes at any point, then we're done, and the user has permission.
1681         // In other words permissions are additive.
1682         int i = 0;
1683         while( (perm = osrfStringArrayGetString(permission, i++)) ) {
1684                 int j = 0;
1685                 while( (context_org = osrfStringArrayGetString( context_org_array, j++ )) ) {
1686                         dbi_result result;
1687
1688                         if( pkey_value ) {
1689                                 osrfLogDebug(
1690                                         OSRF_LOG_MARK,
1691                                         "Checking object permission [%s] for user %d "
1692                                                         "on object %s (class %s) at org %d",
1693                                         perm,
1694                                         userid,
1695                                         pkey_value,
1696                                         osrfHashGet( class, "classname" ),
1697                                         atoi( context_org )
1698                                 );
1699
1700                                 result = dbi_conn_queryf(
1701                                         writehandle,
1702                                         "SELECT permission.usr_has_object_perm(%d, '%s', '%s', '%s', %d) AS has_perm;",
1703                                         userid,
1704                                         perm,
1705                                         osrfHashGet( class, "classname" ),
1706                                         pkey_value,
1707                                         atoi( context_org )
1708                                 );
1709
1710                                 if( result ) {
1711                                         osrfLogDebug(
1712                                                 OSRF_LOG_MARK,
1713                                                 "Received a result for object permission [%s] "
1714                                                                 "for user %d on object %s (class %s) at org %d",
1715                                                 perm,
1716                                                 userid,
1717                                                 pkey_value,
1718                                                 osrfHashGet( class, "classname" ),
1719                                                 atoi( context_org )
1720                                         );
1721
1722                                         if( dbi_result_first_row( result )) {
1723                                                 jsonObject* return_val = oilsMakeJSONFromResult( result );
1724                                                 const char* has_perm = jsonObjectGetString(
1725                                                                 jsonObjectGetKeyConst( return_val, "has_perm" ));
1726
1727                                                 osrfLogDebug(
1728                                                         OSRF_LOG_MARK,
1729                                                         "Status of object permission [%s] for user %d "
1730                                                                         "on object %s (class %s) at org %d is %s",
1731                                                         perm,
1732                                                         userid,
1733                                                         pkey_value,
1734                                                         osrfHashGet(class, "classname"),
1735                                                         atoi(context_org),
1736                                                         has_perm
1737                                                 );
1738
1739                                                 if( *has_perm == 't' )
1740                                                         OK = 1;
1741                                                 jsonObjectFree( return_val );
1742                                         }
1743
1744                                         dbi_result_free( result );
1745                                         if( OK )
1746                                                 break;
1747                                 } else {
1748                                         const char* msg;
1749                                         int errnum = dbi_conn_error( writehandle, &msg );
1750                                         osrfLogWarning( OSRF_LOG_MARK,
1751                                                 "Unable to call check object permissions: %d, %s",
1752                                                 errnum, msg ? msg : "(No description available)" );
1753                                         if( !oilsIsDBConnected( writehandle ))
1754                                                 osrfAppSessionPanic( ctx->session );
1755                                 }
1756                         }
1757
1758                         osrfLogDebug( OSRF_LOG_MARK,
1759                                         "Checking non-object permission [%s] for user %d at org %d",
1760                                         perm, userid, atoi(context_org) );
1761                         result = dbi_conn_queryf(
1762                                 writehandle,
1763                                 "SELECT permission.usr_has_perm(%d, '%s', %d) AS has_perm;",
1764                                 userid,
1765                                 perm,
1766                                 atoi( context_org )
1767                         );
1768
1769                         if( result ) {
1770                                 osrfLogDebug( OSRF_LOG_MARK,
1771                                         "Received a result for permission [%s] for user %d at org %d",
1772                                         perm, userid, atoi( context_org ));
1773                                 if( dbi_result_first_row( result )) {
1774                                         jsonObject* return_val = oilsMakeJSONFromResult( result );
1775                                         const char* has_perm = jsonObjectGetString(
1776                                                 jsonObjectGetKeyConst( return_val, "has_perm" ));
1777                                         osrfLogDebug( OSRF_LOG_MARK,
1778                                                 "Status of permission [%s] for user %d at org %d is [%s]",
1779                                                 perm, userid, atoi( context_org ), has_perm );
1780                                         if( *has_perm == 't' )
1781                                                 OK = 1;
1782                                         jsonObjectFree( return_val );
1783                                 }
1784
1785                                 dbi_result_free( result );
1786                                 if( OK )
1787                                         break;
1788                         } else {
1789                                 const char* msg;
1790                                 int errnum = dbi_conn_error( writehandle, &msg );
1791                                 osrfLogWarning( OSRF_LOG_MARK, "Unable to call user object permissions: %d, %s",
1792                                         errnum, msg ? msg : "(No description available)" );
1793                                 if( !oilsIsDBConnected( writehandle ))
1794                                         osrfAppSessionPanic( ctx->session );
1795                         }
1796
1797                 }
1798                 if( OK )
1799                         break;
1800         }
1801
1802         osrfStringArrayFree( context_org_array );
1803
1804         return OK;
1805 }
1806
1807 /**
1808         @brief Look up the root of the org_unit tree.
1809         @param ctx Pointer to the method context.
1810         @return The id of the root org unit, as a character string.
1811
1812         Query actor.org_unit where parent_ou is null, and return the id as a string.
1813
1814         This function assumes that there is only one root org unit, i.e. that we
1815         have a single tree, not a forest.
1816
1817         The calling code is responsible for freeing the returned string.
1818 */
1819 static const char* org_tree_root( osrfMethodContext* ctx ) {
1820
1821         static char cached_root_id[ 32 ] = "";  // extravagantly large buffer
1822         static time_t last_lookup_time = 0;
1823         time_t current_time = time( NULL );
1824
1825         if( cached_root_id[ 0 ] && ( current_time - last_lookup_time < 3600 ) ) {
1826                 // We successfully looked this up less than an hour ago.
1827                 // It's not likely to have changed since then.
1828                 return strdup( cached_root_id );
1829         }
1830         last_lookup_time = current_time;
1831
1832         int err = 0;
1833         jsonObject* where_clause = single_hash( "parent_ou", NULL );
1834         jsonObject* result = doFieldmapperSearch(
1835                 ctx, osrfHashGet( oilsIDL(), "aou" ), where_clause, NULL, &err );
1836         jsonObjectFree( where_clause );
1837
1838         jsonObject* tree_top = jsonObjectGetIndex( result, 0 );
1839
1840         if( !tree_top ) {
1841                 jsonObjectFree( result );
1842
1843                 growing_buffer* msg = buffer_init( 128 );
1844                 OSRF_BUFFER_ADD( msg, modulename );
1845                 OSRF_BUFFER_ADD( msg,
1846                                 ": Internal error, could not find the top of the org tree (parent_ou = NULL)" );
1847
1848                 char* m = buffer_release( msg );
1849                 osrfAppSessionStatus( ctx->session,
1850                                 OSRF_STATUS_INTERNALSERVERERROR, "osrfMethodException", ctx->request, m );
1851                 free( m );
1852
1853                 cached_root_id[ 0 ] = '\0';
1854                 return NULL;
1855         }
1856
1857         const char* root_org_unit_id = oilsFMGetStringConst( tree_top, "id" );
1858         osrfLogDebug( OSRF_LOG_MARK, "Top of the org tree is %s", root_org_unit_id );
1859
1860         strcpy( cached_root_id, root_org_unit_id );
1861         jsonObjectFree( result );
1862         return cached_root_id;
1863 }
1864
1865 /**
1866         @brief Create a JSON_HASH with a single key/value pair.
1867         @param key The key of the key/value pair.
1868         @param value the value of the key/value pair.
1869         @return Pointer to a newly created jsonObject of type JSON_HASH.
1870
1871         The value of the key/value is either a string or (if @a value is NULL) a null.
1872 */
1873 static jsonObject* single_hash( const char* key, const char* value ) {
1874         // Sanity check
1875         if( ! key ) key = "";
1876
1877         jsonObject* hash = jsonNewObjectType( JSON_HASH );
1878         jsonObjectSetKey( hash, key, jsonNewObject( value ) );
1879         return hash;
1880 }
1881
1882
1883 int doCreate( osrfMethodContext* ctx ) {
1884         if(osrfMethodVerifyContext( ctx )) {
1885                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
1886                 return -1;
1887         }
1888
1889         if( enforce_pcrud )
1890                 timeout_needs_resetting = 1;
1891
1892         osrfHash* meta = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
1893         jsonObject* target = NULL;
1894         jsonObject* options = NULL;
1895
1896         if( enforce_pcrud ) {
1897                 target = jsonObjectGetIndex( ctx->params, 1 );
1898                 options = jsonObjectGetIndex( ctx->params, 2 );
1899         } else {
1900                 target = jsonObjectGetIndex( ctx->params, 0 );
1901                 options = jsonObjectGetIndex( ctx->params, 1 );
1902         }
1903
1904         if( !verifyObjectClass( ctx, target )) {
1905                 osrfAppRespondComplete( ctx, NULL );
1906                 return -1;
1907         }
1908
1909         osrfLogDebug( OSRF_LOG_MARK, "Object seems to be of the correct type" );
1910
1911         const char* trans_id = getXactId( ctx );
1912         if( !trans_id ) {
1913                 osrfLogError( OSRF_LOG_MARK, "No active transaction -- required for CREATE" );
1914
1915                 osrfAppSessionStatus(
1916                         ctx->session,
1917                         OSRF_STATUS_BADREQUEST,
1918                         "osrfMethodException",
1919                         ctx->request,
1920                         "No active transaction -- required for CREATE"
1921                 );
1922                 osrfAppRespondComplete( ctx, NULL );
1923                 return -1;
1924         }
1925
1926         // The following test is harmless but redundant.  If a class is
1927         // readonly, we don't register a create method for it.
1928         if( str_is_true( osrfHashGet( meta, "readonly" ) ) ) {
1929                 osrfAppSessionStatus(
1930                         ctx->session,
1931                         OSRF_STATUS_BADREQUEST,
1932                         "osrfMethodException",
1933                         ctx->request,
1934                         "Cannot INSERT readonly class"
1935                 );
1936                 osrfAppRespondComplete( ctx, NULL );
1937                 return -1;
1938         }
1939
1940         // Set the last_xact_id
1941         int index = oilsIDL_ntop( target->classname, "last_xact_id" );
1942         if( index > -1 ) {
1943                 osrfLogDebug(OSRF_LOG_MARK, "Setting last_xact_id to %s on %s at position %d",
1944                         trans_id, target->classname, index);
1945                 jsonObjectSetIndex( target, index, jsonNewObject( trans_id ));
1946         }
1947
1948         osrfLogDebug( OSRF_LOG_MARK, "There is a transaction running..." );
1949
1950         dbhandle = writehandle;
1951
1952         osrfHash* fields = osrfHashGet( meta, "fields" );
1953         char* pkey       = osrfHashGet( meta, "primarykey" );
1954         char* seq        = osrfHashGet( meta, "sequence" );
1955
1956         growing_buffer* table_buf = buffer_init( 128 );
1957         growing_buffer* col_buf   = buffer_init( 128 );
1958         growing_buffer* val_buf   = buffer_init( 128 );
1959
1960         OSRF_BUFFER_ADD( table_buf, "INSERT INTO " );
1961         OSRF_BUFFER_ADD( table_buf, osrfHashGet( meta, "tablename" ));
1962         OSRF_BUFFER_ADD_CHAR( col_buf, '(' );
1963         buffer_add( val_buf,"VALUES (" );
1964
1965
1966         int first = 1;
1967         osrfHash* field = NULL;
1968         osrfHashIterator* field_itr = osrfNewHashIterator( fields );
1969         while( (field = osrfHashIteratorNext( field_itr ) ) ) {
1970
1971                 const char* field_name = osrfHashIteratorKey( field_itr );
1972
1973                 if( str_is_true( osrfHashGet( field, "virtual" ) ) )
1974                         continue;
1975
1976                 const jsonObject* field_object = oilsFMGetObject( target, field_name );
1977
1978                 char* value;
1979                 if( field_object && field_object->classname ) {
1980                         value = oilsFMGetString(
1981                                 field_object,
1982                                 (char*)oilsIDLFindPath( "/%s/primarykey", field_object->classname )
1983                         );
1984                 } else if( field_object && JSON_BOOL == field_object->type ) {
1985                         if( jsonBoolIsTrue( field_object ) )
1986                                 value = strdup( "t" );
1987                         else
1988                                 value = strdup( "f" );
1989                 } else {
1990                         value = jsonObjectToSimpleString( field_object );
1991                 }
1992
1993                 if( first ) {
1994                         first = 0;
1995                 } else {
1996                         OSRF_BUFFER_ADD_CHAR( col_buf, ',' );
1997                         OSRF_BUFFER_ADD_CHAR( val_buf, ',' );
1998                 }
1999
2000                 buffer_add( col_buf, field_name );
2001
2002                 if( !field_object || field_object->type == JSON_NULL ) {
2003                         buffer_add( val_buf, "DEFAULT" );
2004
2005                 } else if( !strcmp( get_primitive( field ), "number" )) {
2006                         const char* numtype = get_datatype( field );
2007                         if( !strcmp( numtype, "INT8" )) {
2008                                 buffer_fadd( val_buf, "%lld", atoll( value ));
2009
2010                         } else if( !strcmp( numtype, "INT" )) {
2011                                 buffer_fadd( val_buf, "%d", atoi( value ));
2012
2013                         } else if( !strcmp( numtype, "NUMERIC" )) {
2014                                 buffer_fadd( val_buf, "%f", atof( value ));
2015                         }
2016                 } else {
2017                         if( dbi_conn_quote_string( writehandle, &value )) {
2018                                 OSRF_BUFFER_ADD( val_buf, value );
2019
2020                         } else {
2021                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value );
2022                                 osrfAppSessionStatus(
2023                                         ctx->session,
2024                                         OSRF_STATUS_INTERNALSERVERERROR,
2025                                         "osrfMethodException",
2026                                         ctx->request,
2027                                         "Error quoting string -- please see the error log for more details"
2028                                 );
2029                                 free( value );
2030                                 buffer_free( table_buf );
2031                                 buffer_free( col_buf );
2032                                 buffer_free( val_buf );
2033                                 osrfAppRespondComplete( ctx, NULL );
2034                                 return -1;
2035                         }
2036                 }
2037
2038                 free( value );
2039         }
2040
2041         osrfHashIteratorFree( field_itr );
2042
2043         OSRF_BUFFER_ADD_CHAR( col_buf, ')' );
2044         OSRF_BUFFER_ADD_CHAR( val_buf, ')' );
2045
2046         char* table_str = buffer_release( table_buf );
2047         char* col_str   = buffer_release( col_buf );
2048         char* val_str   = buffer_release( val_buf );
2049         growing_buffer* sql = buffer_init( 128 );
2050         buffer_fadd( sql, "%s %s %s;", table_str, col_str, val_str );
2051         free( table_str );
2052         free( col_str );
2053         free( val_str );
2054
2055         char* query = buffer_release( sql );
2056
2057         osrfLogDebug( OSRF_LOG_MARK, "%s: Insert SQL [%s]", modulename, query );
2058
2059         jsonObject* obj = NULL;
2060         int rc = 0;
2061
2062         dbi_result result = dbi_conn_query( writehandle, query );
2063         if( !result ) {
2064                 obj = jsonNewObject( NULL );
2065                 const char* msg;
2066                 int errnum = dbi_conn_error( writehandle, &msg );
2067                 osrfLogError(
2068                         OSRF_LOG_MARK,
2069                         "%s ERROR inserting %s object using query [%s]: %d %s",
2070                         modulename,
2071                         osrfHashGet(meta, "fieldmapper"),
2072                         query,
2073                         errnum,
2074                         msg ? msg : "(No description available)"
2075                 );
2076                 osrfAppSessionStatus(
2077                         ctx->session,
2078                         OSRF_STATUS_INTERNALSERVERERROR,
2079                         "osrfMethodException",
2080                         ctx->request,
2081                         "INSERT error -- please see the error log for more details"
2082                 );
2083                 if( !oilsIsDBConnected( writehandle ))
2084                         osrfAppSessionPanic( ctx->session );
2085                 rc = -1;
2086         } else {
2087
2088                 char* id = oilsFMGetString( target, pkey );
2089                 if( !id ) {
2090                         unsigned long long new_id = dbi_conn_sequence_last( writehandle, seq );
2091                         growing_buffer* _id = buffer_init( 10 );
2092                         buffer_fadd( _id, "%lld", new_id );
2093                         id = buffer_release( _id );
2094                 }
2095
2096                 // Find quietness specification, if present
2097                 const char* quiet_str = NULL;
2098                 if( options ) {
2099                         const jsonObject* quiet_obj = jsonObjectGetKeyConst( options, "quiet" );
2100                         if( quiet_obj )
2101                                 quiet_str = jsonObjectGetString( quiet_obj );
2102                 }
2103
2104                 if( str_is_true( quiet_str )) {  // if quietness is specified
2105                         obj = jsonNewObject( id );
2106                 }
2107                 else {
2108
2109                         // Fetch the row that we just inserted, so that we can return it to the client
2110                         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2111                         jsonObjectSetKey( where_clause, pkey, jsonNewObject( id ));
2112
2113                         int err = 0;
2114                         jsonObject* list = doFieldmapperSearch( ctx, meta, where_clause, NULL, &err );
2115                         if( err )
2116                                 rc = -1;
2117                         else
2118                                 obj = jsonObjectClone( jsonObjectGetIndex( list, 0 ));
2119
2120                         jsonObjectFree( list );
2121                         jsonObjectFree( where_clause );
2122                 }
2123
2124                 free( id );
2125         }
2126
2127         free( query );
2128         osrfAppRespondComplete( ctx, obj );
2129         jsonObjectFree( obj );
2130         return rc;
2131 }
2132
2133 /**
2134         @brief Implement the retrieve method.
2135         @param ctx Pointer to the method context.
2136         @param err Pointer through which to return an error code.
2137         @return If successful, a pointer to the result to be returned to the client;
2138         otherwise NULL.
2139
2140         From the method's class, fetch a row with a specified value in the primary key.  This
2141         method relies on the database design convention that a primary key consists of a single
2142         column.
2143
2144         Method parameters:
2145         - authkey (PCRUD only)
2146         - value of the primary key for the desired row, for building the WHERE clause
2147         - a JSON_HASH containing any other SQL clauses: select, join, etc.
2148
2149         Return to client: One row from the query.
2150 */
2151 int doRetrieve( osrfMethodContext* ctx ) {
2152         if(osrfMethodVerifyContext( ctx )) {
2153                 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
2154                 return -1;
2155         }
2156
2157         if( enforce_pcrud )
2158                 timeout_needs_resetting = 1;
2159
2160         int id_pos = 0;
2161         int order_pos = 1;
2162
2163         if( enforce_pcrud ) {
2164                 id_pos = 1;
2165                 order_pos = 2;
2166         }
2167
2168         // Get the class metadata
2169         osrfHash* class_def = osrfHashGet( (osrfHash*) ctx->method->userData, "class" );
2170
2171         // Get the value of the primary key, from a method parameter
2172         const jsonObject* id_obj = jsonObjectGetIndex( ctx->params, id_pos );
2173
2174         osrfLogDebug(
2175                 OSRF_LOG_MARK,
2176                 "%s retrieving %s object with primary key value of %s",
2177                 modulename,
2178                 osrfHashGet( class_def, "fieldmapper" ),
2179                 jsonObjectGetString( id_obj )
2180         );
2181
2182         // Build a WHERE clause based on the key value
2183         jsonObject* where_clause = jsonNewObjectType( JSON_HASH );
2184         jsonObjectSetKey(
2185                 where_clause,
2186                 osrfHashGet( class_def, "primarykey" ),  // name of key column
2187                 jsonObjectClone( id_obj )                // value of key column
2188         );
2189
2190         jsonObject* rest_of_query = jsonObjectGetIndex( ctx->params, order_pos );
2191
2192         // Do the query
2193         int err = 0;
2194         jsonObject* list = doFieldmapperSearch( ctx, class_def, where_clause, rest_of_query, &err );
2195
2196         jsonObjectFree( where_clause );
2197         if( err ) {
2198                 osrfAppRespondComplete( ctx, NULL );
2199                 return -1;
2200         }
2201
2202         jsonObject* obj = jsonObjectExtractIndex( list, 0 );
2203         jsonObjectFree( list );
2204
2205         if( enforce_pcrud ) {
2206                 if(!verifyObjectPCRUD( ctx, obj )) {
2207                         jsonObjectFree( obj );
2208
2209                         growing_buffer* msg = buffer_init( 128 );
2210                         OSRF_BUFFER_ADD( msg, modulename );
2211                         OSRF_BUFFER_ADD( msg, ": Insufficient permissions to retrieve object" );
2212
2213                         char* m = buffer_release( msg );
2214                         osrfAppSessionStatus( ctx->session, OSRF_STATUS_NOTALLOWED, "osrfMethodException",
2215                                         ctx->request, m );
2216                         free( m );
2217
2218                         osrfAppRespondComplete( ctx, NULL );
2219                         return -1;
2220                 }
2221         }
2222
2223         osrfAppRespondComplete( ctx, obj );
2224         jsonObjectFree( obj );
2225         return 0;
2226 }
2227
2228 /**
2229         @brief Translate a numeric value to a string representation for the database.
2230         @param field Pointer to the IDL field definition.
2231         @param value Pointer to a jsonObject holding the value of a field.
2232         @return Pointer to a newly allocated string.
2233
2234         The input object is typically a JSON_NUMBER, but it may be a JSON_STRING as long as
2235         its contents are numeric.  A non-numeric string is likely to result in invalid SQL,
2236         or (what is worse) valid SQL that is wrong.
2237
2238         If the datatype of the receiving field is not numeric, wrap the value in quotes.
2239
2240         The calling code is responsible for freeing the resulting string by calling free().
2241 */
2242 static char* jsonNumberToDBString( osrfHash* field, const jsonObject* value ) {
2243         growing_buffer* val_buf = buffer_init( 32 );
2244         const char* numtype = get_datatype( field );
2245
2246         // For historical reasons the following contains cruft that could be cleaned up.
2247         if( !strncmp( numtype, "INT", 3 ) ) {
2248                 if( value->type == JSON_NUMBER )
2249                         //buffer_fadd( val_buf, "%ld", (long)jsonObjectGetNumber(value) );
2250                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2251                 else {
2252                         buffer_fadd( val_buf, jsonObjectGetString( value ) );
2253                 }
2254
2255         } else if( !strcmp( numtype, "NUMERIC" )) {
2256                 if( value->type == JSON_NUMBER )
2257                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2258                 else {
2259                         buffer_fadd( val_buf, jsonObjectGetString( value ));
2260                 }
2261
2262         } else {
2263                 // Presumably this was really intended to be a string, so quote it
2264                 char* str = jsonObjectToSimpleString( value );
2265                 if( dbi_conn_quote_string( dbhandle, &str )) {
2266                         OSRF_BUFFER_ADD( val_buf, str );
2267                         free( str );
2268                 } else {
2269                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]", modulename, str );
2270                         free( str );
2271                         buffer_free( val_buf );
2272                         return NULL;
2273                 }
2274         }
2275
2276         return buffer_release( val_buf );
2277 }
2278
2279 static char* searchINPredicate( const char* class_alias, osrfHash* field,
2280                 jsonObject* node, const char* op, osrfMethodContext* ctx ) {
2281         growing_buffer* sql_buf = buffer_init( 32 );
2282
2283         buffer_fadd(
2284                 sql_buf,
2285                 "\"%s\".%s ",
2286                 class_alias,
2287                 osrfHashGet( field, "name" )
2288         );
2289
2290         if( !op ) {
2291                 buffer_add( sql_buf, "IN (" );
2292         } else if( !strcasecmp( op,"not in" )) {
2293                 buffer_add( sql_buf, "NOT IN (" );
2294         } else {
2295                 buffer_add( sql_buf, "IN (" );
2296         }
2297
2298         if( node->type == JSON_HASH ) {
2299                 // subquery predicate
2300                 char* subpred = buildQuery( ctx, node, SUBSELECT );
2301                 if( ! subpred ) {
2302                         buffer_free( sql_buf );
2303                         return NULL;
2304                 }
2305
2306                 buffer_add( sql_buf, subpred );
2307                 free( subpred );
2308
2309         } else if( node->type == JSON_ARRAY ) {
2310                 // literal value list
2311                 int in_item_index = 0;
2312                 int in_item_first = 1;
2313                 const jsonObject* in_item;
2314                 while( (in_item = jsonObjectGetIndex( node, in_item_index++ )) ) {
2315
2316                         if( in_item_first )
2317                                 in_item_first = 0;
2318                         else
2319                                 buffer_add( sql_buf, ", " );
2320
2321                         // Sanity check
2322                         if( in_item->type != JSON_STRING && in_item->type != JSON_NUMBER ) {
2323                                 osrfLogError( OSRF_LOG_MARK,
2324                                                 "%s: Expected string or number within IN list; found %s",
2325                                                 modulename, json_type( in_item->type ) );
2326                                 buffer_free( sql_buf );
2327                                 return NULL;
2328                         }
2329
2330                         // Append the literal value -- quoted if not a number
2331                         if( JSON_NUMBER == in_item->type ) {
2332                                 char* val = jsonNumberToDBString( field, in_item );
2333                                 OSRF_BUFFER_ADD( sql_buf, val );
2334                                 free( val );
2335
2336                         } else if( !strcmp( get_primitive( field ), "number" )) {
2337                                 char* val = jsonNumberToDBString( field, in_item );
2338                                 OSRF_BUFFER_ADD( sql_buf, val );
2339                                 free( val );
2340
2341                         } else {
2342                                 char* key_string = jsonObjectToSimpleString( in_item );
2343                                 if( dbi_conn_quote_string( dbhandle, &key_string )) {
2344                                         OSRF_BUFFER_ADD( sql_buf, key_string );
2345                                         free( key_string );
2346                                 } else {
2347                                         osrfLogError( OSRF_LOG_MARK,
2348                                                         "%s: Error quoting key string [%s]", modulename, key_string );
2349                                         free( key_string );
2350                                         buffer_free( sql_buf );
2351                                         return NULL;
2352                                 }
2353                         }
2354                 }
2355
2356                 if( in_item_first ) {
2357                         osrfLogError(OSRF_LOG_MARK, "%s: Empty IN list", modulename );
2358                         buffer_free( sql_buf );
2359                         return NULL;
2360                 }
2361         } else {
2362                 osrfLogError( OSRF_LOG_MARK, "%s: Expected object or array for IN clause; found %s",
2363                         modulename, json_type( node->type ));
2364                 buffer_free( sql_buf );
2365                 return NULL;
2366         }
2367
2368         OSRF_BUFFER_ADD_CHAR( sql_buf, ')' );
2369
2370         return buffer_release( sql_buf );
2371 }
2372
2373 // Receive a JSON_ARRAY representing a function call.  The first
2374 // entry in the array is the function name.  The rest are parameters.
2375 static char* searchValueTransform( const jsonObject* array ) {
2376
2377         if( array->size < 1 ) {
2378                 osrfLogError( OSRF_LOG_MARK, "%s: Empty array for value transform", modulename );
2379                 return NULL;
2380         }
2381
2382         // Get the function name
2383         jsonObject* func_item = jsonObjectGetIndex( array, 0 );
2384         if( func_item->type != JSON_STRING ) {
2385                 osrfLogError( OSRF_LOG_MARK, "%s: Error: expected function name, found %s",
2386                         modulename, json_type( func_item->type ));
2387                 return NULL;
2388         }
2389
2390         growing_buffer* sql_buf = buffer_init( 32 );
2391
2392         OSRF_BUFFER_ADD( sql_buf, jsonObjectGetString( func_item ) );
2393         OSRF_BUFFER_ADD( sql_buf, "( " );
2394
2395         // Get the parameters
2396         int func_item_index = 1;   // We already grabbed the zeroth entry
2397         while( (func_item = jsonObjectGetIndex( array, func_item_index++ )) ) {
2398
2399                 // Add a separator comma, if we need one
2400                 if( func_item_index > 2 )
2401                         buffer_add( sql_buf, ", " );
2402
2403                 // Add the current parameter
2404                 if( func_item->type == JSON_NULL ) {
2405                         buffer_add( sql_buf, "NULL" );
2406                 } else {
2407                         char* val = jsonObjectToSimpleString( func_item );
2408                         if( dbi_conn_quote_string( dbhandle, &val )) {
2409                                 OSRF_BUFFER_ADD( sql_buf, val );
2410                                 free( val );
2411                         } else {
2412                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2413                                         modulename, val );
2414                                 buffer_free( sql_buf );
2415                                 free( val );
2416                                 return NULL;
2417                         }
2418                 }
2419         }
2420
2421         buffer_add( sql_buf, " )" );
2422
2423         return buffer_release( sql_buf );
2424 }
2425
2426 static char* searchFunctionPredicate( const char* class_alias, osrfHash* field,
2427                 const jsonObject* node, const char* op ) {
2428
2429         if( ! is_good_operator( op ) ) {
2430                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2431                 return NULL;
2432         }
2433
2434         char* val = searchValueTransform( node );
2435         if( !val )
2436                 return NULL;
2437
2438         growing_buffer* sql_buf = buffer_init( 32 );
2439         buffer_fadd(
2440                 sql_buf,
2441                 "\"%s\".%s %s %s",
2442                 class_alias,
2443                 osrfHashGet( field, "name" ),
2444                 op,
2445                 val
2446         );
2447
2448         free( val );
2449
2450         return buffer_release( sql_buf );
2451 }
2452
2453 // class_alias is a class name or other table alias
2454 // field is a field definition as stored in the IDL
2455 // node comes from the method parameter, and may represent an entry in the SELECT list
2456 static char* searchFieldTransform( const char* class_alias, osrfHash* field,
2457                 const jsonObject* node ) {
2458         growing_buffer* sql_buf = buffer_init( 32 );
2459
2460         const char* field_transform = jsonObjectGetString(
2461                 jsonObjectGetKeyConst( node, "transform" ) );
2462         const char* transform_subcolumn = jsonObjectGetString(
2463                 jsonObjectGetKeyConst( node, "result_field" ) );
2464
2465         if( transform_subcolumn ) {
2466                 if( ! is_identifier( transform_subcolumn ) ) {
2467                         osrfLogError( OSRF_LOG_MARK, "%s: Invalid subfield name: \"%s\"\n",
2468                                         modulename, transform_subcolumn );
2469                         buffer_free( sql_buf );
2470                         return NULL;
2471                 }
2472                 OSRF_BUFFER_ADD_CHAR( sql_buf, '(' );    // enclose transform in parentheses
2473         }
2474
2475         if( field_transform ) {
2476
2477                 if( ! is_identifier( field_transform ) ) {
2478                         osrfLogError( OSRF_LOG_MARK, "%s: Expected function name, found \"%s\"\n",
2479                                         modulename, field_transform );
2480                         buffer_free( sql_buf );
2481                         return NULL;
2482                 }
2483
2484                 if( obj_is_true( jsonObjectGetKeyConst( node, "distinct" ) ) ) {
2485                         buffer_fadd( sql_buf, "%s(DISTINCT \"%s\".%s",
2486                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2487                 } else {
2488                         buffer_fadd( sql_buf, "%s(\"%s\".%s",
2489                                 field_transform, class_alias, osrfHashGet( field, "name" ));
2490                 }
2491
2492                 const jsonObject* array = jsonObjectGetKeyConst( node, "params" );
2493
2494                 if( array ) {
2495                         if( array->type != JSON_ARRAY ) {
2496                                 osrfLogError( OSRF_LOG_MARK,
2497                                         "%s: Expected JSON_ARRAY for function params; found %s",
2498                                         modulename, json_type( array->type ) );
2499                                 buffer_free( sql_buf );
2500                                 return NULL;
2501                         }
2502                         int func_item_index = 0;
2503                         jsonObject* func_item;
2504                         while( (func_item = jsonObjectGetIndex( array, func_item_index++ ))) {
2505
2506                                 char* val = jsonObjectToSimpleString( func_item );
2507
2508                                 if( !val ) {
2509                                         buffer_add( sql_buf, ",NULL" );
2510                                 } else if( dbi_conn_quote_string( dbhandle, &val )) {
2511                                         OSRF_BUFFER_ADD_CHAR( sql_buf, ',' );
2512                                         OSRF_BUFFER_ADD( sql_buf, val );
2513                                 } else {
2514                                         osrfLogError( OSRF_LOG_MARK,
2515                                                         "%s: Error quoting key string [%s]", modulename, val );
2516                                         free( val );
2517                                         buffer_free( sql_buf );
2518                                         return NULL;
2519                                 }
2520                                 free( val );
2521                         }
2522                 }
2523
2524                 buffer_add( sql_buf, " )" );
2525
2526         } else {
2527                 buffer_fadd( sql_buf, "\"%s\".%s", class_alias, osrfHashGet( field, "name" ));
2528         }
2529
2530         if( transform_subcolumn )
2531                 buffer_fadd( sql_buf, ").\"%s\"", transform_subcolumn );
2532
2533         return buffer_release( sql_buf );
2534 }
2535
2536 static char* searchFieldTransformPredicate( const ClassInfo* class_info, osrfHash* field,
2537                 const jsonObject* node, const char* op ) {
2538
2539         if( ! is_good_operator( op ) ) {
2540                 osrfLogError( OSRF_LOG_MARK, "%s: Error: Invalid operator %s", modulename, op );
2541                 return NULL;
2542         }
2543
2544         char* field_transform = searchFieldTransform( class_info->alias, field, node );
2545         if( ! field_transform )
2546                 return NULL;
2547         char* value = NULL;
2548         int extra_parens = 0;   // boolean
2549
2550         const jsonObject* value_obj = jsonObjectGetKeyConst( node, "value" );
2551         if( ! value_obj ) {
2552                 value = searchWHERE( node, class_info, AND_OP_JOIN, NULL );
2553                 if( !value ) {
2554                         osrfLogError( OSRF_LOG_MARK, "%s: Error building condition for field transform",
2555                                 modulename );
2556                         free( field_transform );
2557                         return NULL;
2558                 }
2559                 extra_parens = 1;
2560         } else if( value_obj->type == JSON_ARRAY ) {
2561                 value = searchValueTransform( value_obj );
2562                 if( !value ) {
2563                         osrfLogError( OSRF_LOG_MARK,
2564                                 "%s: Error building value transform for field transform", modulename );
2565                         free( field_transform );
2566                         return NULL;
2567                 }
2568         } else if( value_obj->type == JSON_HASH ) {
2569                 value = searchWHERE( value_obj, class_info, AND_OP_JOIN, NULL );
2570                 if( !value ) {
2571                         osrfLogError( OSRF_LOG_MARK, "%s: Error building predicate for field transform",
2572                                 modulename );
2573                         free( field_transform );
2574                         return NULL;
2575                 }
2576                 extra_parens = 1;
2577         } else if( value_obj->type == JSON_NUMBER ) {
2578                 value = jsonNumberToDBString( field, value_obj );
2579         } else if( value_obj->type == JSON_NULL ) {
2580                 osrfLogError( OSRF_LOG_MARK,
2581                         "%s: Error building predicate for field transform: null value", modulename );
2582                 free( field_transform );
2583                 return NULL;
2584         } else if( value_obj->type == JSON_BOOL ) {
2585                 osrfLogError( OSRF_LOG_MARK,
2586                         "%s: Error building predicate for field transform: boolean value", modulename );
2587                 free( field_transform );
2588                 return NULL;
2589         } else {
2590                 if( !strcmp( get_primitive( field ), "number") ) {
2591                         value = jsonNumberToDBString( field, value_obj );
2592                 } else {
2593                         value = jsonObjectToSimpleString( value_obj );
2594                         if( !dbi_conn_quote_string( dbhandle, &value )) {
2595                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2596                                         modulename, value );
2597                                 free( value );
2598                                 free( field_transform );
2599                                 return NULL;
2600                         }
2601                 }
2602         }
2603
2604         const char* left_parens  = "";
2605         const char* right_parens = "";
2606
2607         if( extra_parens ) {
2608                 left_parens  = "(";
2609                 right_parens = ")";
2610         }
2611
2612         growing_buffer* sql_buf = buffer_init( 32 );
2613
2614         buffer_fadd(
2615                 sql_buf,
2616                 "%s%s %s %s %s %s%s",
2617                 left_parens,
2618                 field_transform,
2619                 op,
2620                 left_parens,
2621                 value,
2622                 right_parens,
2623                 right_parens
2624         );
2625
2626         free( value );
2627         free( field_transform );
2628
2629         return buffer_release( sql_buf );
2630 }
2631
2632 static char* searchSimplePredicate( const char* op, const char* class_alias,
2633                 osrfHash* field, const jsonObject* node ) {
2634
2635         if( ! is_good_operator( op ) ) {
2636                 osrfLogError( OSRF_LOG_MARK, "%s: Invalid operator [%s]", modulename, op );
2637                 return NULL;
2638         }
2639
2640         char* val = NULL;
2641
2642         // Get the value to which we are comparing the specified column
2643         if( node->type != JSON_NULL ) {
2644                 if( node->type == JSON_NUMBER ) {
2645                         val = jsonNumberToDBString( field, node );
2646                 } else if( !strcmp( get_primitive( field ), "number" ) ) {
2647                         val = jsonNumberToDBString( field, node );
2648                 } else {
2649                         val = jsonObjectToSimpleString( node );
2650                 }
2651         }
2652
2653         if( val ) {
2654                 if( JSON_NUMBER != node->type && strcmp( get_primitive( field ), "number") ) {
2655                         // Value is not numeric; enclose it in quotes
2656                         if( !dbi_conn_quote_string( dbhandle, &val ) ) {
2657                                 osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]",
2658                                         modulename, val );
2659                                 free( val );
2660                                 return NULL;
2661                         }
2662                 }
2663         } else {
2664                 // Compare to a null value
2665                 val = strdup( "NULL" );
2666                 if( strcmp( op, "=" ))
2667                         op = "IS NOT";
2668                 else
2669                         op = "IS";
2670         }
2671
2672         growing_buffer* sql_buf = buffer_init( 32 );
2673         buffer_fadd( sql_buf, "\"%s\".%s %s %s", class_alias, osrfHashGet(field, "name"), op, val );
2674         char* pred = buffer_release( sql_buf );
2675
2676         free( val );
2677
2678         return pred;
2679 }
2680
2681 static char* searchBETWEENPredicate( const char* class_alias,
2682                 osrfHash* field, const jsonObject* node ) {
2683
2684         const jsonObject* x_node = jsonObjectGetIndex( node, 0 );
2685         const jsonObject* y_node = jsonObjectGetIndex( node, 1 );
2686
2687         if( NULL == y_node ) {
2688                 osrfLogError( OSRF_LOG_MARK, "%s: Not enough operands for BETWEEN operator", modulename );
2689                 return NULL;
2690         }
2691         else if( NULL != jsonObjectGetIndex( node, 2 ) ) {
2692                 osrfLogError( OSRF_LOG_MARK, "%s: Too many operands for BETWEEN operator", modulename );
2693                 return NULL;
2694         }
2695
2696         char* x_string;
2697         char* y_string;
2698
2699         if( !strcmp( get_primitive( field ), "number") ) {
2700                 x_string = jsonNumberToDBString( field, x_node );
2701                 y_string = jsonNumberToDBString( field, y_node );
2702
2703         } else {
2704                 x_string = jsonObjectToSimpleString( x_node );
2705                 y_string = jsonObjectToSimpleString( y_node );
2706                 if( !(dbi_conn_quote_string( dbhandle, &x_string )
2707                         && dbi_conn_quote_string( dbhandle, &y_string )) ) {
2708                         osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key strings [%s] and [%s]",
2709                                         modulename, x_string, y_string );
2710                         free( x_string );
2711                         free( y_string );
2712                         return NULL;
2713                 }
2714         }
2715
2716         growing_buffer* sql_buf = buffer_init( 32 );
2717         buffer_fadd( sql_buf, "\"%s\".%s BETWEEN %s AND %s",
2718                         class_alias, osrfHashGet( field, "name" ), x_string, y_string );
2719         free( x_string );
2720         free( y_string );
2721
2722         return buffer_release( sql_buf );
2723 }
2724
2725 static char* searchPredicate( const ClassInfo* class_info, osrfHash* field,
2726                                                           jsonObject* node, osrfMethodContext* ctx ) {
2727
2728         char* pred = NULL;
2729         if( node->type == JSON_ARRAY ) { // equality IN search
2730                 pred = searchINPredicate( class_info->alias, field, node, NULL, ctx );
2731         } else if( node->type == JSON_HASH ) { // other search
2732                 jsonIterator* pred_itr = jsonNewIterator( node );
2733                 if( !jsonIteratorHasNext( pred_itr ) ) {
2734                         osrfLogError( OSRF_LOG_MARK, "%s: Empty predicate for field \"%s\"",
2735                                         modulename, osrfHashGet(field, "name" ));
2736                 } else {
2737                         jsonObject* pred_node = jsonIteratorNext( pred_itr );
2738
2739                         // Verify that there are no additional predicates
2740                         if( jsonIteratorHasNext( pred_itr ) ) {
2741                                 osrfLogError( OSRF_LOG_MARK, "%s: Multiple predicates for field \"%s\"",
2742                                                 modulename, osrfHashGet(field, "name" ));
2743                         } else if( !(strcasecmp( pred_itr->key,"between" )) )
2744                                 pred = searchBETWEENPredicate( class_info->alias, field, pred_node );
2745                         else if( !(strcasecmp( pred_itr->key,"in" ))
2746                                         || !(strcasecmp( pred_itr->key,"not in" )) )
2747                                 pred = searchINPredicate(
2748                                         class_info->alias, field, pred_node, pred_itr->key, ctx );
2749                         else if( pred_node->type == JSON_ARRAY )
2750                                 pred = searchFunctionPredicate(
2751                                         class_info->alias, field, pred_node, pred_itr->key );
2752                         else if( pred_node->type == JSON_HASH )
2753                                 pred = searchFieldTransformPredicate(
2754                                         class_info, field, pred_node, pred_itr->key );
2755                         else
2756                                 pred = searchSimplePredicate( pred_itr->key, class_info->alias, field, pred_node );
2757                 }
2758                 jsonIteratorFree( pred_itr );
2759
2760         } else if( node->type == JSON_NULL ) { // IS NULL search
2761                 growing_buffer* _p = buffer_init( 64 );
2762                 buffer_fadd(
2763                         _p,
2764                         "\"%s\".%s IS NULL",
2765                         class_info->class_name,
2766                         osrfHashGet( field, "name" )
2767                 );
2768                 pred = buffer_release( _p );
2769         } else { // equality search
2770                 pred = searchSimplePredicate( "=", class_info->alias, field, node );
2771         }
2772
2773         return pred;
2774
2775 }
2776
2777
2778 /*
2779
2780 join : {
2781         acn : {
2782                 field : record,
2783                 fkey : id
2784                 type : left
2785                 filter_op : or
2786                 filter : { ... },
2787                 join : {
2788                         acp : {
2789                                 field : call_number,
2790                                 fkey : id,
2791                                 filter : { ... },
2792                         },
2793                 },
2794         },
2795         mrd : {
2796                 field : record,
2797                 type : inner
2798                 fkey : id,
2799                 filter : { ... },
2800         }
2801 }
2802
2803 */
2804
2805 static char* searchJOIN( const jsonObject* join_hash, const ClassInfo* left_info ) {
2806
2807         const jsonObject* working_hash;
2808         jsonObject* freeable_hash = NULL;
2809
2810         if( join_hash->type == JSON_HASH ) {
2811                 working_hash = join_hash;
2812         } else if( join_hash->type == JSON_STRING ) {
2813                 // turn it into a JSON_HASH by creating a wrapper
2814                 // around a copy of the original
2815                 const char* _tmp = jsonObjectGetString( join_hash );
2816                 freeable_hash = jsonNewObjectType( JSON_HASH );
2817                 jsonObjectSetKey( freeable_hash, _tmp, NULL );
2818                 working_hash = freeable_hash;
2819         } else {
2820                 osrfLogError(
2821                         OSRF_LOG_MARK,
2822                         "%s: JOIN failed; expected JSON object type not found",
2823                         modulename
2824                 );
2825                 return NULL;
2826         }
2827
2828         growing_buffer* join_buf = buffer_init( 128 );
2829         const char* leftclass = left_info->class_name;
2830
2831         jsonObject* snode = NULL;
2832         jsonIterator* search_itr = jsonNewIterator( working_hash );
2833
2834         while ( (snode = jsonIteratorNext( search_itr )) ) {
2835                 const char* right_alias = search_itr->key;
2836                 const char* class =
2837                                 jsonObjectGetString( jsonObjectGetKeyConst( snode, "class" ) );
2838                 if( ! class )
2839                         class = right_alias;
2840
2841                 const ClassInfo* right_info = add_joined_class( right_alias, class );
2842                 if( !right_info ) {
2843                         osrfLogError(
2844                                 OSRF_LOG_MARK,
2845                                 "%s: JOIN failed.  Class \"%s\" not resolved in IDL",
2846                                 modulename,
2847                                 search_itr->key
2848                         );
2849                         jsonIteratorFree( search_itr );
2850                         buffer_free( join_buf );
2851                         if( freeable_hash )
2852                                 jsonObjectFree( freeable_hash );
2853                         return NULL;
2854                 }
2855                 osrfHash* links    = right_info->links;
2856                 const char* table  = right_info->source_def;
2857
2858                 const char* fkey  = jsonObjectGetString( jsonObjectGetKeyConst( snode, "fkey" ) );
2859                 const char* field = jsonObjectGetString( jsonObjectGetKeyConst( snode, "field" ) );
2860
2861                 if( field && !fkey ) {
2862                         // Look up the corresponding join column in the IDL.
2863                         // The link must be defined in the child table,
2864                         // and point to the right parent table.
2865                         osrfHash* idl_link = (osrfHash*) osrfHashGet( links, field );
2866                         const char* reltype = NULL;
2867                         const char* other_class = NULL;
2868                         reltype = osrfHashGet( idl_link, "reltype" );
2869                         if( reltype && strcmp( reltype, "has_many" ) )
2870                                 other_class = osrfHashGet( idl_link, "class" );
2871                         if( other_class && !strcmp( other_class, leftclass ) )
2872                                 fkey = osrfHashGet( idl_link, "key" );
2873                         if( !fkey ) {
2874                                 osrfLogError(