3 @brief As a server, perform database queries as defined in the database itself.
10 #include "opensrf/utils.h"
11 #include "opensrf/log.h"
12 #include "opensrf/osrf_json.h"
13 #include "opensrf/osrf_application.h"
14 #include "openils/oils_utils.h"
15 #include "openils/oils_sql.h"
16 #include "openils/oils_buildq.h"
19 @brief Information about a previously prepared query.
21 We store an osrfHash of CachedQueries in the userData area of the application session,
22 keyed on query token. That way we can fetch what a previous call to the prepare method
30 static dbi_conn dbhandle; /* our db connection */
32 static const char modulename[] = "open-ils.qstore";
34 int doPrepare( osrfMethodContext* ctx );
35 int doExecute( osrfMethodContext* ctx );
36 int doSql( osrfMethodContext* ctx );
38 static const char* save_query(
39 osrfMethodContext* ctx, BuildSQLState* state, StoredQ* query );
40 static void free_cached_query( char* key, void* data );
41 static void userDataFree( void* blob );
42 static CachedQuery* search_token( osrfMethodContext* ctx, const char* token );
45 @brief Disconnect from the database.
47 This function is called when the server drone is about to terminate.
49 void osrfAppChildExit() {
50 osrfLogDebug( OSRF_LOG_MARK, "Child is exiting, disconnecting from database..." );
53 dbi_conn_query( dbhandle, "ROLLBACK;" );
54 dbi_conn_close( dbhandle );
60 @brief Initialize the application.
61 @return Zero if successful, or non-zero if not.
63 Load the IDL file into an internal data structure for future reference. Each non-virtual
64 class in the IDL corresponds to a table or view in the database, or to a subquery defined
65 in the IDL. Ignore all virtual tables and virtual fields.
67 Register the functions for remote procedure calls.
69 This function is called when the registering the application, and is executed by the
70 listener before spawning the drones.
72 int osrfAppInitialize() {
74 osrfLogInfo( OSRF_LOG_MARK, "Initializing the QStore Server..." );
75 osrfLogInfo( OSRF_LOG_MARK, "Finding XML file..." );
77 if ( !oilsIDLInit( osrf_settings_host_value( "/IDL" )))
78 return 1; /* return non-zero to indicate error */
80 // Set the SQL options. Here the second and third parameters are irrelevant, but we need
81 // to set the module name for use in error messages.
82 oilsSetSQLOptions( modulename, 0, 100 );
84 growing_buffer* method_name = buffer_init( 64 );
86 OSRF_BUFFER_ADD( method_name, modulename );
87 OSRF_BUFFER_ADD( method_name, ".prepare" );
88 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
89 "doPrepare", "", 1, 0 );
91 buffer_reset( method_name );
92 OSRF_BUFFER_ADD( method_name, modulename );
93 OSRF_BUFFER_ADD( method_name, ".columns" );
94 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
95 "doColumns", "", 1, 0 );
97 buffer_reset( method_name );
98 OSRF_BUFFER_ADD( method_name, modulename );
99 OSRF_BUFFER_ADD( method_name, ".param_list" );
100 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
101 "doParamList", "", 1, 0 );
103 buffer_reset( method_name );
104 OSRF_BUFFER_ADD( method_name, modulename );
105 OSRF_BUFFER_ADD( method_name, ".bind_param" );
106 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
107 "doBindParam", "", 2, 0 );
109 buffer_reset( method_name );
110 OSRF_BUFFER_ADD( method_name, modulename );
111 OSRF_BUFFER_ADD( method_name, ".execute" );
112 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
113 "doExecute", "", 1, OSRF_METHOD_STREAMING );
115 buffer_reset( method_name );
116 OSRF_BUFFER_ADD( method_name, modulename );
117 OSRF_BUFFER_ADD( method_name, ".sql" );
118 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
119 "doSql", "", 1, OSRF_METHOD_STREAMING );
121 buffer_reset( method_name );
122 OSRF_BUFFER_ADD( method_name, modulename );
123 OSRF_BUFFER_ADD( method_name, ".finish" );
124 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
125 "doFinish", "", 1, 0 );
127 buffer_reset( method_name );
128 OSRF_BUFFER_ADD( method_name, modulename );
129 OSRF_BUFFER_ADD( method_name, ".messages" );
130 osrfAppRegisterMethod( modulename, OSRF_BUFFER_C_STR( method_name ),
131 "doMessages", "", 1, 0 );
137 @brief Initialize a server drone.
138 @return Zero if successful, -1 if not.
140 Connect to the database. For each non-virtual class in the IDL, execute a dummy "SELECT * "
141 query to get the datatype of each column. Record the datatypes in the loaded IDL.
143 This function is called by a server drone shortly after it is spawned by the listener.
145 int osrfAppChildInit( void ) {
147 dbhandle = oilsConnectDB( modulename );
151 oilsSetDBConnection( dbhandle );
152 osrfLogInfo( OSRF_LOG_MARK, "%s successfully connected to the database", modulename );
154 // Apply datatypes from database to the fields in the IDL
155 //if( oilsExtendIDL() ) {
156 // osrfLogError( OSRF_LOG_MARK, "Error extending the IDL" );
165 @brief Load a specified query from the database query tables.
166 @param ctx Pointer to the current method context.
167 @return Zero if successful, or -1 if not.
170 - query id (key of query.stored_query table)
172 Returns: a hash with two entries:
173 - "token": A character string serving as a token for future references to the query.
174 - "bind_variables" A hash of bind variables; see notes for doParamList().
176 int doPrepare( osrfMethodContext* ctx ) {
177 if(osrfMethodVerifyContext( ctx )) {
178 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
182 // Get the query id from a method parameter
183 const jsonObject* query_id_obj = jsonObjectGetIndex( ctx->params, 0 );
184 if( query_id_obj->type != JSON_NUMBER ) {
185 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
186 ctx->request, "Invalid parameter; query id must be a number" );
190 int query_id = atoi( jsonObjectGetString( query_id_obj ));
191 if( query_id <= 0 ) {
192 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
193 ctx->request, "Invalid parameter: query id must be greater than zero" );
197 osrfLogInfo( OSRF_LOG_MARK, "Loading query for id # %d", query_id );
199 BuildSQLState* state = buildSQLStateNew( dbhandle );
200 state->defaults_usable = 1;
201 state->values_required = 0;
202 StoredQ* query = getStoredQuery( state, query_id );
204 osrfLogWarning( OSRF_LOG_MARK, "Unable to load stored query # %d", query_id );
205 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
206 ctx->request, "Unable to load stored query" );
208 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
209 "Database connection isn't working" ));
210 osrfAppSessionPanic( ctx->session );
215 const char* token = save_query( ctx, state, query );
217 osrfLogInfo( OSRF_LOG_MARK, "Token for query id # %d is \"%s\"", query_id, token );
219 // Build an object to return. It will be a hash containing the query token and a
220 // list of bind variables.
221 jsonObject* returned_obj = jsonNewObjectType( JSON_HASH );
222 jsonObjectSetKey( returned_obj, "token", jsonNewObject( token ));
223 jsonObjectSetKey( returned_obj, "bind_variables",
224 oilsBindVarList( state->bindvar_list ));
226 osrfAppRespondComplete( ctx, returned_obj );
231 @brief Return a list of column names for the SELECT list.
232 @param ctx Pointer to the current method context.
233 @return Zero if successful, or -1 if not.
236 - query token, as previously returned by the .prepare method.
238 Returns: An array of column names; unavailable names are represented as nulls.
240 int doColumns( osrfMethodContext* ctx ) {
241 if(osrfMethodVerifyContext( ctx )) {
242 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
246 // Get the query token from a method parameter
247 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
248 if( token_obj->type != JSON_STRING ) {
249 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
250 ctx->request, "Invalid parameter; query token must be a string" );
253 const char* token = jsonObjectGetString( token_obj );
255 // Look up the query token in the session-level userData
256 CachedQuery* query = search_token( ctx, token );
258 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
259 ctx->request, "Invalid query token" );
263 osrfLogInfo( OSRF_LOG_MARK, "Listing column names for token %s", token );
265 jsonObject* col_list = oilsGetColNames( query->state, query->query );
266 if( query->state->error ) {
267 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
268 ctx->request, "Unable to get column names" );
269 if( query->state->panic ) {
270 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( query->state,
271 "Database connection isn't working" ));
272 osrfAppSessionPanic( ctx->session );
276 osrfAppRespondComplete( ctx, col_list );
282 @brief Implement the param_list method.
283 @param ctx Pointer to the current method context.
284 @return Zero if successful, or -1 if not.
286 Provide a list of bind variables for a specified query, along with their various
290 - query token, as previously returned by the .prepare method.
292 Returns: A (possibly empty) JSON_HASH, keyed on the names of the bind variables.
293 The data for each is another level of JSON_HASH with a fixed set of tags:
297 - "default_value" (as a jsonObject)
298 - "actual_value" (as a jsonObject)
300 Any non-existent values are represented as JSON_NULLs.
302 int doParamList( osrfMethodContext* ctx ) {
303 if(osrfMethodVerifyContext( ctx )) {
304 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
308 // Get the query token from a method parameter
309 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
310 if( token_obj->type != JSON_STRING ) {
311 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
312 ctx->request, "Invalid parameter; query token must be a string" );
315 const char* token = jsonObjectGetString( token_obj );
317 // Look up the query token in the session-level userData
318 CachedQuery* query = search_token( ctx, token );
320 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
321 ctx->request, "Invalid query token" );
325 osrfLogInfo( OSRF_LOG_MARK, "Returning list of bind variables for token %s", token );
327 osrfAppRespondComplete( ctx, oilsBindVarList( query->state->bindvar_list ) );
332 @brief Implement the bind_param method.
333 @param ctx Pointer to the current method context.
334 @return Zero if successful, or -1 if not.
336 Apply values to bind variables, overriding the defaults, if any.
339 - query token, as previously returned by the .prepare method.
340 - hash of bind variable values, keyed on bind variable names.
344 int doBindParam( osrfMethodContext* ctx ) {
345 if(osrfMethodVerifyContext( ctx )) {
346 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
350 // Get the query token from a method parameter
351 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
352 if( token_obj->type != JSON_STRING ) {
353 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
354 ctx->request, "Invalid parameter; query token must be a string" );
357 const char* token = jsonObjectGetString( token_obj );
359 // Look up the query token in the session-level userData
360 CachedQuery* query = search_token( ctx, token );
362 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
363 ctx->request, "Invalid query token" );
367 osrfLogInfo( OSRF_LOG_MARK, "Binding parameter(s) for token %s", token );
369 jsonObject* bindings = jsonObjectGetIndex( ctx->params, 1 );
371 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
372 ctx->request, "No parameter provided for bind variable values" );
374 } else if( bindings->type != JSON_HASH ) {
375 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
376 ctx->request, "Invalid parameter for bind variable values: not a hash" );
380 if( 0 == bindings->size ) {
381 // No values to assign; we're done.
382 osrfAppRespondComplete( ctx, NULL );
386 osrfHash* bindvar_list = query->state->bindvar_list;
387 if( !bindvar_list || osrfHashGetCount( bindvar_list ) == 0 ) {
388 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
389 ctx->request, "There are no bind variables to which to assign values" );
393 if( oilsApplyBindValues( query->state, bindings )) {
394 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
395 ctx->request, "Unable to apply values to bind variables" );
398 osrfAppRespondComplete( ctx, NULL );
404 @brief Execute an SQL query and return a result set.
405 @param ctx Pointer to the current method context.
406 @return Zero if successful, or -1 if not.
409 - query token, as previously returned by the .prepare method.
411 Returns: A series of responses, each of them a row represented as an array of column values.
413 int doExecute( osrfMethodContext* ctx ) {
414 if(osrfMethodVerifyContext( ctx )) {
415 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
419 // Get the query token
420 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
421 if( token_obj->type != JSON_STRING ) {
422 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
423 ctx->request, "Invalid parameter; query token must be a string" );
426 const char* token = jsonObjectGetString( token_obj );
428 // Look up the query token in the session-level userData
429 CachedQuery* query = search_token( ctx, token );
431 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
432 ctx->request, "Invalid query token" );
436 osrfLogInfo( OSRF_LOG_MARK, "Executing query for token \"%s\"", token );
437 if( query->state->error ) {
438 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
439 "No valid prepared query available for query id # %d", query->query->id ));
440 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
441 ctx->request, "No valid prepared query available" );
443 } else if( buildSQL( query->state, query->query )) {
444 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
445 "Unable to build SQL statement for query id # %d", query->query->id ));
446 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
447 ctx->request, "Unable to build SQL statement" );
451 jsonObject* row = oilsFirstRow( query->state );
453 osrfAppRespond( ctx, row );
454 row = oilsNextRow( query->state );
457 if( query->state->error ) {
458 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
459 "Unable to execute SQL statement for query id # %d", query->query->id ));
460 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
461 ctx->request, "Unable to execute SQL statement" );
462 if( query->state->panic ) {
463 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( query->state,
464 "Database connection isn't working" ));
465 osrfAppSessionPanic( ctx->session );
470 osrfAppRespondComplete( ctx, NULL );
475 @brief Construct an SQL query, but without executing it.
476 @param ctx Pointer to the current method context.
477 @return Zero if successful, or -1 if not.
480 - query token, as previously returned by the .prepare method.
482 Returns: A string containing an SQL query..
484 int doSql( osrfMethodContext* ctx ) {
485 if(osrfMethodVerifyContext( ctx )) {
486 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
490 // Get the query token
491 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
492 if( token_obj->type != JSON_STRING ) {
493 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
494 ctx->request, "Invalid parameter; query token must be a string" );
497 const char* token = jsonObjectGetString( token_obj );
499 // Look up the query token in the session-level userData
500 CachedQuery* query = search_token( ctx, token );
502 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
503 ctx->request, "Invalid query token" );
507 osrfLogInfo( OSRF_LOG_MARK, "Returning SQL for token \"%s\"", token );
508 if( query->state->error ) {
509 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
510 "No valid prepared query available for query id # %d", query->query->id ));
511 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
512 ctx->request, "No valid prepared query available" );
514 } else if( buildSQL( query->state, query->query )) {
515 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
516 "Unable to build SQL statement for query id # %d", query->query->id ));
517 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
518 ctx->request, "Unable to build SQL statement" );
522 osrfAppRespondComplete( ctx, jsonNewObject( OSRF_BUFFER_C_STR( query->state->sql )));
527 @brief Return a list of previously generated error messages for a specified query.
528 @param ctx Pointer to the current method context.
529 @return Zero if successful, or -1 if not.
532 - query token, as previously returned by the .prepare method.
534 Returns: A (possibly empty) array of strings, each one an error message generated during
535 previous operations in connection with the specified query.
537 int doMessages( osrfMethodContext* ctx ) {
538 if(osrfMethodVerifyContext( ctx )) {
539 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
543 // Get the query token from a method parameter
544 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
545 if( token_obj->type != JSON_STRING ) {
546 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
547 ctx->request, "Invalid parameter; query token must be a string" );
550 const char* token = jsonObjectGetString( token_obj );
552 // Look up the query token in the session-level userData
553 CachedQuery* query = search_token( ctx, token );
555 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
556 ctx->request, "Invalid query token" );
560 osrfLogInfo( OSRF_LOG_MARK, "Returning messages for token %s", token );
562 jsonObject* msgs = jsonNewObjectType( JSON_ARRAY );
563 const osrfStringArray* error_msgs = query->state->error_msgs;
565 for( i = 0; i < error_msgs->size; ++i ) {
566 jsonObject* msg = jsonNewObject( osrfStringArrayGetString( error_msgs, i ));
567 jsonObjectPush( msgs, msg );
570 osrfAppRespondComplete( ctx, msgs );
575 @brief Discard a previously stored query, as identified by a token.
576 @param ctx Pointer to the current method context.
577 @return Zero if successful, or -1 if not.
580 - query token, as previously returned by the .prepare method.
584 int doFinish( osrfMethodContext* ctx ) {
585 if(osrfMethodVerifyContext( ctx )) {
586 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
590 // Get the query token.
591 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
592 if( token_obj->type != JSON_STRING ) {
593 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
594 ctx->request, "Invalid parameter; query token must be a string" );
597 const char* token = jsonObjectGetString( token_obj );
599 // Delete the corresponding entry from the cache. If there is no cache, or no such entry,
600 // just ignore the problem and report success.
601 osrfHash* cache = ctx->session->userData;
603 osrfHashRemove( cache, token );
605 osrfAppRespondComplete( ctx, NULL );
610 @brief Save a query in session-level userData for reference in future method calls.
611 @param ctx Pointer to the current method context.
612 @param state Pointer to the state of the query.
613 @param query Pointer to the abstract representation of the query.
614 @return Pointer to an identifying token to be returned to the client.
616 static const char* save_query(
617 osrfMethodContext* ctx, BuildSQLState* state, StoredQ* query ) {
619 CachedQuery* cached_query = safe_malloc( sizeof( CachedQuery ));
620 cached_query->state = state;
621 cached_query->query = query;
623 // Get the cache. If we don't have one yet, make one.
624 osrfHash* cache = ctx->session->userData;
626 cache = osrfNewHash();
627 osrfHashSetCallback( cache, free_cached_query );
628 ctx->session->userData = cache;
629 ctx->session->userDataFree = userDataFree; // arrange to free it at end of session
632 // Create a token string to be used as a key
633 static unsigned int token_count = 0;
634 char* token = va_list_to_string(
635 "%u_%ld_%ld", ++token_count, (long) time( NULL ), (long) getpid() );
637 osrfHashSet( cache, cached_query, token );
642 @brief Free a CachedQuery
643 @param Pointer to the CachedQuery to be freed.
645 static void free_cached_query( char* key, void* data ) {
647 CachedQuery* cached_query = data;
648 buildSQLStateFree( cached_query->state );
649 storedQFree( cached_query->query );
654 @brief Callback for freeing session-level userData.
655 @param blob Opaque pointer t userData.
657 static void userDataFree( void* blob ) {
658 osrfHashFree( (osrfHash*) blob );
662 @brief Search for the cached query corresponding to a given token.
663 @param ctx Pointer to the current method context.
664 @param token Token string from a previous call to the prepare method.
665 @return A pointer to the cached query, if found, or NULL if not.
667 static CachedQuery* search_token( osrfMethodContext* ctx, const char* token ) {
668 if( ctx && ctx->session->userData && token ) {
669 osrfHash* cache = ctx->session->userData;
670 return osrfHashGet( cache, token );