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" );
210 const char* token = save_query( ctx, state, query );
212 osrfLogInfo( OSRF_LOG_MARK, "Token for query id # %d is \"%s\"", query_id, token );
214 // Build an object to return: a hash containing the query token
215 // and a list of bind variables.
216 jsonObject* returned_obj = jsonNewObjectType( JSON_HASH );
217 jsonObjectSetKey( returned_obj, "token", jsonNewObject( token ));
218 jsonObjectSetKey( returned_obj, "bind_variables",
219 oilsBindVarList( state->bindvar_list ));
221 osrfAppRespondComplete( ctx, returned_obj );
226 @brief Return a list of column names for the SELECT list.
227 @param ctx Pointer to the current method context.
228 @return Zero if successful, or -1 if not.
231 - query token, as previously returned by the .prepare method.
233 Returns: An array of column names; unavailable names are represented as nulls.
235 int doColumns( osrfMethodContext* ctx ) {
236 if(osrfMethodVerifyContext( ctx )) {
237 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
241 // Get the query token from a method parameter
242 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
243 if( token_obj->type != JSON_STRING ) {
244 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
245 ctx->request, "Invalid parameter; query token must be a string" );
248 const char* token = jsonObjectGetString( token_obj );
250 // Look up the query token in the session-level userData
251 CachedQuery* query = search_token( ctx, token );
253 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
254 ctx->request, "Invalid query token" );
258 osrfLogInfo( OSRF_LOG_MARK, "Listing column names for token %s", token );
260 jsonObject* col_list = oilsGetColNames( query->state, query->query );
261 if( query->state->error ) {
262 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
263 ctx->request, "Unable to get column names" );
266 osrfAppRespondComplete( ctx, col_list );
272 @brief Implement the param_list method.
273 @param ctx Pointer to the current method context.
274 @return Zero if successful, or -1 if not.
276 Provide a list of bind variables for a specified query, along with their various
280 - query token, as previously returned by the .prepare method.
282 Returns: A (possibly empty) JSON_HASH, keyed on the names of the bind variables.
283 The data for each is another level of JSON_HASH with a fixed set of tags:
287 - "default_value" (as a jsonObject)
288 - "actual_value" (as a jsonObject)
290 Any non-existent values are represented as JSON_NULLs.
292 int doParamList( osrfMethodContext* ctx ) {
293 if(osrfMethodVerifyContext( ctx )) {
294 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
298 // Get the query token from a method parameter
299 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
300 if( token_obj->type != JSON_STRING ) {
301 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
302 ctx->request, "Invalid parameter; query token must be a string" );
305 const char* token = jsonObjectGetString( token_obj );
307 // Look up the query token in the session-level userData
308 CachedQuery* query = search_token( ctx, token );
310 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
311 ctx->request, "Invalid query token" );
315 osrfLogInfo( OSRF_LOG_MARK, "Returning list of bind variables for token %s", token );
317 osrfAppRespondComplete( ctx, oilsBindVarList( query->state->bindvar_list ) );
322 @brief Implement the bind_param method.
323 @param ctx Pointer to the current method context.
324 @return Zero if successful, or -1 if not.
326 Apply values to bind variables, overriding the defaults, if any.
329 - query token, as previously returned by the .prepare method.
330 - hash of bind variable values, keyed on bind variable names.
334 int doBindParam( osrfMethodContext* ctx ) {
335 if(osrfMethodVerifyContext( ctx )) {
336 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
340 // Get the query token from a method parameter
341 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
342 if( token_obj->type != JSON_STRING ) {
343 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
344 ctx->request, "Invalid parameter; query token must be a string" );
347 const char* token = jsonObjectGetString( token_obj );
349 // Look up the query token in the session-level userData
350 CachedQuery* query = search_token( ctx, token );
352 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
353 ctx->request, "Invalid query token" );
357 osrfLogInfo( OSRF_LOG_MARK, "Binding parameter(s) for token %s", token );
359 jsonObject* bindings = jsonObjectGetIndex( ctx->params, 1 );
361 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
362 ctx->request, "No parameter provided for bind variable values" );
364 } else if( bindings->type != JSON_HASH ) {
365 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
366 ctx->request, "Invalid parameter for bind variable values: not a hash" );
370 if( 0 == bindings->size ) {
371 // No values to assign; we're done.
372 osrfAppRespondComplete( ctx, NULL );
376 osrfHash* bindvar_list = query->state->bindvar_list;
377 if( !bindvar_list || osrfHashGetCount( bindvar_list ) == 0 ) {
378 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
379 ctx->request, "There are no bind variables to which to assign values" );
383 if( oilsApplyBindValues( query->state, bindings )) {
384 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
385 ctx->request, "Unable to apply values to bind variables" );
388 osrfAppRespondComplete( ctx, NULL );
394 @brief Execute an SQL query and return a result set.
395 @param ctx Pointer to the current method context.
396 @return Zero if successful, or -1 if not.
399 - query token, as previously returned by the .prepare method.
401 Returns: A series of responses, each of them a row represented as an array of column values.
403 int doExecute( osrfMethodContext* ctx ) {
404 if(osrfMethodVerifyContext( ctx )) {
405 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
409 // Get the query token
410 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
411 if( token_obj->type != JSON_STRING ) {
412 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
413 ctx->request, "Invalid parameter; query token must be a string" );
416 const char* token = jsonObjectGetString( token_obj );
418 // Look up the query token in the session-level userData
419 CachedQuery* query = search_token( ctx, token );
421 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
422 ctx->request, "Invalid query token" );
426 osrfLogInfo( OSRF_LOG_MARK, "Executing query for token \"%s\"", token );
427 if( query->state->error ) {
428 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
429 "No valid prepared query available for query id # %d", query->query->id ));
430 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
431 ctx->request, "No valid prepared query available" );
433 } else if( buildSQL( query->state, query->query )) {
434 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
435 "Unable to build SQL statement for query id # %d", query->query->id ));
436 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
437 ctx->request, "Unable to build SQL statement" );
441 jsonObject* row = oilsFirstRow( query->state );
443 osrfAppRespond( ctx, row );
444 row = oilsNextRow( query->state );
447 if( query->state->error ) {
448 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
449 "Unable to execute SQL statement for query id # %d", query->query->id ));
450 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
451 ctx->request, "Unable to execute SQL statement" );
455 osrfAppRespondComplete( ctx, NULL );
460 @brief Construct an SQL query, but without executing it.
461 @param ctx Pointer to the current method context.
462 @return Zero if successful, or -1 if not.
465 - query token, as previously returned by the .prepare method.
467 Returns: A string containing an SQL query..
469 int doSql( osrfMethodContext* ctx ) {
470 if(osrfMethodVerifyContext( ctx )) {
471 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
475 // Get the query token
476 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
477 if( token_obj->type != JSON_STRING ) {
478 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
479 ctx->request, "Invalid parameter; query token must be a string" );
482 const char* token = jsonObjectGetString( token_obj );
484 // Look up the query token in the session-level userData
485 CachedQuery* query = search_token( ctx, token );
487 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
488 ctx->request, "Invalid query token" );
492 osrfLogInfo( OSRF_LOG_MARK, "Returning SQL for token \"%s\"", token );
493 if( query->state->error ) {
494 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
495 "No valid prepared query available for query id # %d", query->query->id ));
496 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
497 ctx->request, "No valid prepared query available" );
499 } else if( buildSQL( query->state, query->query )) {
500 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
501 "Unable to build SQL statement for query id # %d", query->query->id ));
502 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
503 ctx->request, "Unable to build SQL statement" );
507 osrfAppRespondComplete( ctx, jsonNewObject( OSRF_BUFFER_C_STR( query->state->sql )));
512 @brief Return a list of previously generated error messages for a specified query.
513 @param ctx Pointer to the current method context.
514 @return Zero if successful, or -1 if not.
517 - query token, as previously returned by the .prepare method.
519 Returns: A (possibly empty) array of strings, each one an error message generated during
520 previous operations in connection with the specified query.
522 int doMessages( osrfMethodContext* ctx ) {
523 if(osrfMethodVerifyContext( ctx )) {
524 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
528 // Get the query token from a method parameter
529 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
530 if( token_obj->type != JSON_STRING ) {
531 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
532 ctx->request, "Invalid parameter; query token must be a string" );
535 const char* token = jsonObjectGetString( token_obj );
537 // Look up the query token in the session-level userData
538 CachedQuery* query = search_token( ctx, token );
540 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
541 ctx->request, "Invalid query token" );
545 osrfLogInfo( OSRF_LOG_MARK, "Returning messages for token %s", token );
547 jsonObject* msgs = jsonNewObjectType( JSON_ARRAY );
548 const osrfStringArray* error_msgs = query->state->error_msgs;
550 for( i = 0; i < error_msgs->size; ++i ) {
551 jsonObject* msg = jsonNewObject( osrfStringArrayGetString( error_msgs, i ));
552 jsonObjectPush( msgs, msg );
555 osrfAppRespondComplete( ctx, msgs );
560 @brief Discard a previously stored query, as identified by a token.
561 @param ctx Pointer to the current method context.
562 @return Zero if successful, or -1 if not.
565 - query token, as previously returned by the .prepare method.
569 int doFinish( osrfMethodContext* ctx ) {
570 if(osrfMethodVerifyContext( ctx )) {
571 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
575 // Get the query token.
576 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
577 if( token_obj->type != JSON_STRING ) {
578 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
579 ctx->request, "Invalid parameter; query token must be a string" );
582 const char* token = jsonObjectGetString( token_obj );
584 // Delete the corresponding entry from the cache. If there is no cache, or no such entry,
585 // just ignore the problem and report success.
586 osrfHash* cache = ctx->session->userData;
588 osrfHashRemove( cache, token );
590 osrfAppRespondComplete( ctx, NULL );
595 @brief Save a query in session-level userData for reference in future method calls.
596 @param ctx Pointer to the current method context.
597 @param state Pointer to the state of the query.
598 @param query Pointer to the abstract representation of the query.
599 @return Pointer to an identifying token to be returned to the client.
601 static const char* save_query(
602 osrfMethodContext* ctx, BuildSQLState* state, StoredQ* query ) {
604 CachedQuery* cached_query = safe_malloc( sizeof( CachedQuery ));
605 cached_query->state = state;
606 cached_query->query = query;
608 // Get the cache. If we don't have one yet, make one.
609 osrfHash* cache = ctx->session->userData;
611 cache = osrfNewHash();
612 osrfHashSetCallback( cache, free_cached_query );
613 ctx->session->userData = cache;
614 ctx->session->userDataFree = userDataFree; // arrange to free it at end of session
617 // Create a token string to be used as a key
618 static unsigned int token_count = 0;
619 char* token = va_list_to_string(
620 "%u_%ld_%ld", ++token_count, (long) time( NULL ), (long) getpid() );
622 osrfHashSet( cache, cached_query, token );
627 @brief Free a CachedQuery
628 @param Pointer to the CachedQuery to be freed.
630 static void free_cached_query( char* key, void* data ) {
632 CachedQuery* cached_query = data;
633 buildSQLStateFree( cached_query->state );
634 storedQFree( cached_query->query );
639 @brief Callback for freeing session-level userData.
640 @param blob Opaque pointer t userData.
642 static void userDataFree( void* blob ) {
643 osrfHashFree( (osrfHash*) blob );
647 @brief Search for the cached query corresponding to a given token.
648 @param ctx Pointer to the current method context.
649 @param token Token string from a previous call to the prepare method.
650 @return A pointer to the cached query, if found, or NULL if not.
652 static CachedQuery* search_token( osrfMethodContext* ctx, const char* token ) {
653 if( ctx && ctx->session->userData && token ) {
654 osrfHash* cache = ctx->session->userData;
655 return osrfHashGet( cache, token );