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 character string serving as a token for future references to the query.
174 NB: the method return type is temporary. Eventually this method will return both a token
175 and a list of bind variables.
177 int doPrepare( osrfMethodContext* ctx ) {
178 if(osrfMethodVerifyContext( ctx )) {
179 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
183 // Get the query id from a method parameter
184 const jsonObject* query_id_obj = jsonObjectGetIndex( ctx->params, 0 );
185 if( query_id_obj->type != JSON_NUMBER ) {
186 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
187 ctx->request, "Invalid parameter; query id must be a number" );
191 int query_id = atoi( jsonObjectGetString( query_id_obj ));
192 if( query_id <= 0 ) {
193 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
194 ctx->request, "Invalid parameter: query id must be greater than zero" );
198 osrfLogInfo( OSRF_LOG_MARK, "Loading query for id # %d", query_id );
200 BuildSQLState* state = buildSQLStateNew( dbhandle );
201 state->defaults_usable = 1;
202 state->values_required = 0;
203 StoredQ* query = getStoredQuery( state, query_id );
205 osrfLogWarning( OSRF_LOG_MARK, "Unable to load stored query # %d", query_id );
206 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
207 ctx->request, "Unable to load stored query" );
211 const char* token = save_query( ctx, state, query );
213 osrfLogInfo( OSRF_LOG_MARK, "Token for query id # %d is \"%s\"", query_id, token );
215 osrfAppRespondComplete( ctx, jsonNewObject( token ));
220 @brief Return a list of column names for the SELECT list.
221 @param ctx Pointer to the current method context.
222 @return Zero if successful, or -1 if not.
225 - query token, as previously returned by the .prepare method.
227 Returns: An array of column names; unavailable names are represented as nulls.
229 int doColumns( osrfMethodContext* ctx ) {
230 if(osrfMethodVerifyContext( ctx )) {
231 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
235 // Get the query token from a method parameter
236 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
237 if( token_obj->type != JSON_STRING ) {
238 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
239 ctx->request, "Invalid parameter; query token must be a string" );
242 const char* token = jsonObjectGetString( token_obj );
244 // Look up the query token in the session-level userData
245 CachedQuery* query = search_token( ctx, token );
247 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
248 ctx->request, "Invalid query token" );
252 osrfLogInfo( OSRF_LOG_MARK, "Listing column names for token %s", token );
254 jsonObject* col_list = oilsGetColNames( query->state, query->query );
255 if( query->state->error ) {
256 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
257 ctx->request, "Unable to get column names" );
260 osrfAppRespondComplete( ctx, col_list );
265 int doParamList( osrfMethodContext* ctx ) {
266 if(osrfMethodVerifyContext( ctx )) {
267 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
271 // Get the query token from a method parameter
272 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
273 if( token_obj->type != JSON_STRING ) {
274 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
275 ctx->request, "Invalid parameter; query token must be a string" );
278 const char* token = jsonObjectGetString( token_obj );
280 // Look up the query token in the session-level userData
281 CachedQuery* query = search_token( ctx, token );
283 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
284 ctx->request, "Invalid query token" );
288 osrfLogInfo( OSRF_LOG_MARK, "Returning list of bind variables for token %s", token );
290 osrfAppRespondComplete( ctx, oilsBindVarList( query->state->bindvar_list ) );
295 @brief Implement the bind_param method.
296 @param ctx Pointer to the current method context.
297 @return Zero if successful, or -1 if not.
300 - query token, as previously returned by the .prepare method.
301 - hash of bind variable values, keyed on bind variable names.
305 int doBindParam( osrfMethodContext* ctx ) {
306 if(osrfMethodVerifyContext( ctx )) {
307 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
311 // Get the query token from a method parameter
312 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
313 if( token_obj->type != JSON_STRING ) {
314 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
315 ctx->request, "Invalid parameter; query token must be a string" );
318 const char* token = jsonObjectGetString( token_obj );
320 // Look up the query token in the session-level userData
321 CachedQuery* query = search_token( ctx, token );
323 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
324 ctx->request, "Invalid query token" );
328 osrfLogInfo( OSRF_LOG_MARK, "Binding parameter(s) for token %s", token );
330 jsonObject* bindings = jsonObjectGetIndex( ctx->params, 1 );
332 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
333 ctx->request, "No parameter provided for bind variable values" );
335 } else if( bindings->type != JSON_HASH ) {
336 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
337 ctx->request, "Invalid parameter for bind variable values: not a hash" );
341 if( 0 == bindings->size ) {
342 // No values to assign; we're done.
343 osrfAppRespondComplete( ctx, NULL );
347 osrfHash* bindvar_list = query->state->bindvar_list;
348 if( !bindvar_list || osrfHashGetCount( bindvar_list ) == 0 ) {
349 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
350 ctx->request, "There are no bind variables to which to assign values" );
354 if( oilsApplyBindValues( query->state, bindings )) {
355 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
356 ctx->request, "Unable to apply values to bind variables" );
359 osrfAppRespondComplete( ctx, NULL );
365 @brief Execute an SQL query and return a result set.
366 @param ctx Pointer to the current method context.
367 @return Zero if successful, or -1 if not.
370 - query token, as previously returned by the .prepare method.
372 Returns: A series of responses, each of them a row represented as an array of column values.
374 int doExecute( osrfMethodContext* ctx ) {
375 if(osrfMethodVerifyContext( ctx )) {
376 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
380 // Get the query token
381 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
382 if( token_obj->type != JSON_STRING ) {
383 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
384 ctx->request, "Invalid parameter; query token must be a string" );
387 const char* token = jsonObjectGetString( token_obj );
389 // Look up the query token in the session-level userData
390 CachedQuery* query = search_token( ctx, token );
392 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
393 ctx->request, "Invalid query token" );
397 osrfLogInfo( OSRF_LOG_MARK, "Executing query for token \"%s\"", token );
398 if( query->state->error ) {
399 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
400 "No valid prepared query available for query id # %d", query->query->id ));
401 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
402 ctx->request, "No valid prepared query available" );
404 } else if( buildSQL( query->state, query->query )) {
405 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
406 "Unable to build SQL statement for query id # %d", query->query->id ));
407 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
408 ctx->request, "Unable to build SQL statement" );
412 jsonObject* row = oilsFirstRow( query->state );
414 osrfAppRespond( ctx, row );
415 row = oilsNextRow( query->state );
418 if( query->state->error ) {
419 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
420 "Unable to execute SQL statement for query id # %d", query->query->id ));
421 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
422 ctx->request, "Unable to execute SQL statement" );
426 osrfAppRespondComplete( ctx, NULL );
431 @brief Construct an SQL query, but without executing it.
432 @param ctx Pointer to the current method context.
433 @return Zero if successful, or -1 if not.
436 - query token, as previously returned by the .prepare method.
438 Returns: A string containing an SQL query..
440 int doSql( osrfMethodContext* ctx ) {
441 if(osrfMethodVerifyContext( ctx )) {
442 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
446 // Get the query token
447 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
448 if( token_obj->type != JSON_STRING ) {
449 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
450 ctx->request, "Invalid parameter; query token must be a string" );
453 const char* token = jsonObjectGetString( token_obj );
455 // Look up the query token in the session-level userData
456 CachedQuery* query = search_token( ctx, token );
458 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
459 ctx->request, "Invalid query token" );
463 osrfLogInfo( OSRF_LOG_MARK, "Returning SQL for token \"%s\"", token );
464 if( query->state->error ) {
465 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
466 "No valid prepared query available for query id # %d", query->query->id ));
467 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
468 ctx->request, "No valid prepared query available" );
470 } else if( buildSQL( query->state, query->query )) {
471 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state,
472 "Unable to build SQL statement for query id # %d", query->query->id ));
473 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
474 ctx->request, "Unable to build SQL statement" );
478 osrfAppRespondComplete( ctx, jsonNewObject( OSRF_BUFFER_C_STR( query->state->sql )));
483 @brief Return a list of previously generated error messages for a specified query.
484 @param ctx Pointer to the current method context.
485 @return Zero if successful, or -1 if not.
488 - query token, as previously returned by the .prepare method.
490 Returns: A (possibly empty) array of strings, each one an error message generated during
491 previous operations in connection with the specified query.
493 int doMessages( osrfMethodContext* ctx ) {
494 if(osrfMethodVerifyContext( ctx )) {
495 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
499 // Get the query token from a method parameter
500 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
501 if( token_obj->type != JSON_STRING ) {
502 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
503 ctx->request, "Invalid parameter; query token must be a string" );
506 const char* token = jsonObjectGetString( token_obj );
508 // Look up the query token in the session-level userData
509 CachedQuery* query = search_token( ctx, token );
511 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
512 ctx->request, "Invalid query token" );
516 osrfLogInfo( OSRF_LOG_MARK, "Returning messages for token %s", token );
518 jsonObject* msgs = jsonNewObjectType( JSON_ARRAY );
519 const osrfStringArray* error_msgs = query->state->error_msgs;
521 for( i = 0; i < error_msgs->size; ++i ) {
522 jsonObject* msg = jsonNewObject( osrfStringArrayGetString( error_msgs, i ));
523 jsonObjectPush( msgs, msg );
526 osrfAppRespondComplete( ctx, msgs );
531 @brief Discard a previously stored query, as identified by a token.
532 @param ctx Pointer to the current method context.
533 @return Zero if successful, or -1 if not.
536 - query token, as previously returned by the .prepare method.
540 int doFinish( osrfMethodContext* ctx ) {
541 if(osrfMethodVerifyContext( ctx )) {
542 osrfLogError( OSRF_LOG_MARK, "Invalid method context" );
546 // Get the query token.
547 const jsonObject* token_obj = jsonObjectGetIndex( ctx->params, 0 );
548 if( token_obj->type != JSON_STRING ) {
549 osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException",
550 ctx->request, "Invalid parameter; query token must be a string" );
553 const char* token = jsonObjectGetString( token_obj );
555 // Delete the corresponding entry from the cache. If there is no cache, or no such entry,
556 // just ignore the problem and report success.
557 osrfHash* cache = ctx->session->userData;
559 osrfHashRemove( cache, token );
561 osrfAppRespondComplete( ctx, NULL );
566 @brief Save a query in session-level userData for reference in future method calls.
567 @param ctx Pointer to the current method context.
568 @param state Pointer to the state of the query.
569 @param query Pointer to the abstract representation of the query.
570 @return Pointer to an identifying token to be returned to the client.
572 static const char* save_query(
573 osrfMethodContext* ctx, BuildSQLState* state, StoredQ* query ) {
575 CachedQuery* cached_query = safe_malloc( sizeof( CachedQuery ));
576 cached_query->state = state;
577 cached_query->query = query;
579 // Get the cache. If we don't have one yet, make one.
580 osrfHash* cache = ctx->session->userData;
582 cache = osrfNewHash();
583 osrfHashSetCallback( cache, free_cached_query );
584 ctx->session->userData = cache;
585 ctx->session->userDataFree = userDataFree; // arrange to free it at end of session
588 // Create a token string to be used as a key
589 static unsigned int token_count = 0;
590 char* token = va_list_to_string(
591 "%u_%ld_%ld", ++token_count, (long) time( NULL ), (long) getpid() );
593 osrfHashSet( cache, cached_query, token );
598 @brief Free a CachedQuery
599 @param Pointer to the CachedQuery to be freed.
601 static void free_cached_query( char* key, void* data ) {
603 CachedQuery* cached_query = data;
604 buildSQLStateFree( cached_query->state );
605 storedQFree( cached_query->query );
610 @brief Callback for freeing session-level userData.
611 @param blob Opaque pointer t userData.
613 static void userDataFree( void* blob ) {
614 osrfHashFree( (osrfHash*) blob );
618 @brief Search for the cached query corresponding to a given token.
619 @param ctx Pointer to the current method context.
620 @param token Token string from a previous call to the prepare method.
621 @return A pointer to the cached query, if found, or NULL if not.
623 static CachedQuery* search_token( osrfMethodContext* ctx, const char* token ) {
624 if( ctx && ctx->session->userData && token ) {
625 osrfHash* cache = ctx->session->userData;
626 return osrfHashGet( cache, token );