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