From 372deb96dc7d9770dafdb7e401bf53072febe1e2 Mon Sep 17 00:00:00 2001 From: scottmk Date: Wed, 28 Apr 2010 03:41:56 +0000 Subject: [PATCH] Implement .execute method of qstore server. M Open-ILS/include/openils/oils_buildq.h M Open-ILS/src/c-apps/oils_qstore.c M Open-ILS/src/c-apps/Makefile.am A Open-ILS/src/c-apps/oils_execsql.c git-svn-id: svn://svn.open-ils.org/ILS/trunk@16328 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/include/openils/oils_buildq.h | 19 ++- Open-ILS/src/c-apps/Makefile.am | 2 +- Open-ILS/src/c-apps/oils_execsql.c | 172 +++++++++++++++++++++++++ Open-ILS/src/c-apps/oils_qstore.c | 47 ++++++- 4 files changed, 229 insertions(+), 11 deletions(-) create mode 100644 Open-ILS/src/c-apps/oils_execsql.c diff --git a/Open-ILS/include/openils/oils_buildq.h b/Open-ILS/include/openils/oils_buildq.h index 994eb715dc..718f38ab48 100644 --- a/Open-ILS/include/openils/oils_buildq.h +++ b/Open-ILS/include/openils/oils_buildq.h @@ -6,6 +6,8 @@ #ifndef OILS_BUILDQ_H #define OILS_BUILDQ_H +#include "opensrf/osrf_json.h" + #ifdef __cplusplus extern "C" { #endif @@ -37,20 +39,21 @@ typedef struct IdNode_ IdNode; /** @brief Stores various things related to the construction of an SQL query. - This struct carries around various bits and scraps of context for constructing an SQL - query. It also provides a way for buildSQLQuery() to return more than one kind of thing - to its caller. In particular it can return a status code, a list of error messages, and - (if there is no error) an SQL string. + This struct carries around various bits and scraps of context for constructing and + executing an SQL query. It also provides a way for buildSQLQuery() to return more than + one kind of thing to its caller. In particular it can return a status code, a list of + error messages, and (if there is no error) an SQL string. */ struct BuildSQLState_ { dbi_conn dbhandle; /**< Handle for the database connection */ + dbi_result result; /**< Reference to current row or result set */ int error; /**< Boolean; true if an error has occurred */ osrfStringArray* error_msgs; /**< Descriptions of errors, if any */ growing_buffer* sql; /**< To hold the constructed query */ IdNode* query_stack; /**< For avoiding infinite recursion of nested queries */ IdNode* expr_stack; /**< For avoiding infinite recursion of nested expressions */ IdNode* from_stack; /**< For avoiding infinite recursion of from clauses */ - int indent; /**< For prettifying output: level of indentation */ + int indent; /**< For prettifying SQL output: level of indentation */ }; typedef enum { @@ -189,6 +192,12 @@ int buildSQL( BuildSQLState* state, StoredQ* query ); void oilsStoredQSetVerbose( void ); +jsonObject* oilsExecSql( BuildSQLState* state ); + +jsonObject* oilsFirstRow( BuildSQLState* state ); + +jsonObject* oilsNextRow( BuildSQLState* state ); + #ifdef __cplusplus } #endif diff --git a/Open-ILS/src/c-apps/Makefile.am b/Open-ILS/src/c-apps/Makefile.am index 9a0e55648c..b725746d3c 100644 --- a/Open-ILS/src/c-apps/Makefile.am +++ b/Open-ILS/src/c-apps/Makefile.am @@ -31,7 +31,7 @@ oils_cstore_la_SOURCES = oils_cstore.c oils_sql.c oils_cstore_la_LDFLAGS = $(AM_LDFLAGS) -loils_idl -ldbi -ldbdpgsql -loils_utils -module oils_cstore_la_DEPENDENCIES = liboils_idl.la liboils_idl.la -oils_qstore_la_SOURCES = oils_qstore.c oils_sql.c oils_storedq.c oils_buildq.c buildSQL.c +oils_qstore_la_SOURCES = oils_qstore.c oils_sql.c oils_storedq.c oils_buildq.c buildSQL.c oils_execsql.c oils_qstore_la_LDFLAGS = $(AM_LDFLAGS) -loils_idl -ldbi -ldbdpgsql -loils_utils -module oils_qstore_la_DEPENDENCIES = liboils_idl.la liboils_idl.la diff --git a/Open-ILS/src/c-apps/oils_execsql.c b/Open-ILS/src/c-apps/oils_execsql.c new file mode 100644 index 0000000000..fb23b5895f --- /dev/null +++ b/Open-ILS/src/c-apps/oils_execsql.c @@ -0,0 +1,172 @@ +/** + @file oils_execsql.c + @brief Excecute a specified SQL query and return the results. +*/ + +#include +#include +#include +#include "opensrf/utils.h" +#include "opensrf/log.h" +#include "opensrf/string_array.h" +#include "opensrf/osrf_json.h" +#include "openils/oils_buildq.h" + +static jsonObject* get_row( BuildSQLState* state ); +static jsonObject* get_date_column( dbi_result result, int col_idx ); + +jsonObject* oilsExecSql( BuildSQLState* state ) { + + if( !state ) + return NULL; + + // Execute the query + dbi_result result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql )); + if( !result ) { + state->error = 1; + const char* msg; + (void) dbi_conn_error( state->dbhandle, &msg ); + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to execute query: %s",msg ? msg : "No description available" )); + return NULL; + } + + if( !dbi_result_first_row( result ) ) + return NULL; // No rows returned + + jsonObject* result_set = jsonNewObjectType( JSON_ARRAY ); + + do { + jsonObject* row = get_row( state ); + if( row ) + jsonObjectPush( result_set, row ); + } while( dbi_result_next_row( result )); + + dbi_result_free( result ); + return result_set; +} + +jsonObject* oilsFirstRow( BuildSQLState* state ) { + + if( !state ) + return NULL; + + if( state->result ) + dbi_result_free( state->result ); + + // Execute the query + state->result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql )); + if( !state->result ) { + state->error = 1; + const char* msg; + (void) dbi_conn_error( state->dbhandle, &msg ); + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to execute query: %s",msg ? msg : "No description available" )); + return NULL; + } + + // Get the first row + if( dbi_result_first_row( state->result )) + return get_row( state ); + else { + dbi_result_free( state->result ); + state->result = NULL; + return NULL; // No rows returned + } +} + +jsonObject* oilsNextRow( BuildSQLState* state ) { + + if( !state || !state->result ) + return NULL; + + // Get the next row + if( dbi_result_next_row( state->result )) + return get_row( state ); + else { + dbi_result_free( state->result ); + state->result = NULL; + return NULL; // No next row returned + } +} + +static jsonObject* get_row( BuildSQLState* state ) { + unsigned int col_count = dbi_result_get_numfields( state->result ); + jsonObject* row = jsonNewObjectType( JSON_ARRAY ); + + unsigned int i = 1; + for( i = 1; i <= col_count; ++i ) { + + if( dbi_result_field_is_null_idx( state->result, i )) { + jsonObjectPush( row, jsonNewObjectType( JSON_NULL )); + continue; // Column is null + } + + jsonObject* col_value = NULL; + int type = dbi_result_get_field_type_idx( state->result, i ); + switch( type ) { + case DBI_TYPE_INTEGER : { + long long value = dbi_result_get_longlong_idx( state->result, i ); + col_value = jsonNewNumberObject( (double) value ); + break; + } + case DBI_TYPE_DECIMAL : { + double value = dbi_result_get_double_idx( state->result, i ); + col_value = jsonNewNumberObject( value ); + break; + } + case DBI_TYPE_STRING : { + const char* value = dbi_result_get_string_idx( state->result, i ); + col_value = jsonNewObject( value ); + break; + } + case DBI_TYPE_BINARY : { + osrfLogError( OSRF_LOG_MARK, "Binary types not supported; column set to null" ); + col_value = jsonNewObjectType( JSON_NULL ); + break; + } + case DBI_TYPE_DATETIME : { + col_value = get_date_column( state->result, i ); + break; + } + default : + osrfLogError( OSRF_LOG_MARK, + "Unrecognized column type %d; column set to null", type ); + col_value = jsonNewObjectType( JSON_NULL ); + break; + } + jsonObjectPush( row, col_value ); + } + + return row; +} + +/** + @brief Translate a date column into a string. + @param result Reference to the current returned row. + @param col_idx Column number (starting with 1) within the row. + @return Pointer to a newly-allocated JSON_STRING containing a formatted date string. + + The calling code is responsible for freeing the returned jsonObject by calling + jsonObjectFree(). +*/ +static jsonObject* get_date_column( dbi_result result, int col_idx ) { + + time_t timestamp = dbi_result_get_datetime_idx( result, col_idx ); + char timestring[ 256 ] = ""; + int attr = dbi_result_get_field_attribs_idx( result, col_idx ); + struct tm gmdt; + + if( !( attr & DBI_DATETIME_DATE )) { + gmtime_r( ×tamp, &gmdt ); + strftime( timestring, sizeof( timestring ), "%T", &gmdt ); + } else if( !( attr & DBI_DATETIME_TIME )) { + localtime_r( ×tamp, &gmdt ); + strftime( timestring, sizeof( timestring ), "%F", &gmdt ); + } else { + localtime_r( ×tamp, &gmdt ); + strftime( timestring, sizeof( timestring ), "%FT%T%z", &gmdt ); + } + + return jsonNewObject( timestring ); +} diff --git a/Open-ILS/src/c-apps/oils_qstore.c b/Open-ILS/src/c-apps/oils_qstore.c index a141741c9d..dbf74f6629 100644 --- a/Open-ILS/src/c-apps/oils_qstore.c +++ b/Open-ILS/src/c-apps/oils_qstore.c @@ -285,8 +285,35 @@ int doExecute( osrfMethodContext* ctx ) { } osrfLogInfo( OSRF_LOG_MARK, "Executing query for token \"%s\"", token ); + if( query->state->error ) { + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state, + "No valid prepared query available for query id # %d", query->query->id )); + osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", + ctx->request, "No valid prepared query available" ); + return -1; + } else if( buildSQL( query->state, query->query )) { + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state, + "Unable to build SQL statement for query id # %d", query->query->id )); + osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", + ctx->request, "Unable to build SQL statement" ); + return -1; + } + + jsonObject* row = oilsFirstRow( query->state ); + while( row ) { + osrfAppRespond( ctx, row ); + row = oilsNextRow( query->state ); + } - osrfAppRespondComplete( ctx, jsonNewObject( "execute method not yet implemented" )); + if( query->state->error ) { + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state, + "Unable to execute SQL statement for query id # %d", query->query->id )); + osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", + ctx->request, "Unable to execute SQL statement" ); + return -1; + } + + osrfAppRespondComplete( ctx, NULL ); return 0; } @@ -315,14 +342,14 @@ int doSql( osrfMethodContext* ctx ) { osrfLogInfo( OSRF_LOG_MARK, "Returning SQL for token \"%s\"", token ); if( query->state->error ) { - osrfLogWarning( OSRF_LOG_MARK, "No valid prepared query available for query id # %d", - query->query->id ); + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state, + "No valid prepared query available for query id # %d", query->query->id )); osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", ctx->request, "No valid prepared query available" ); return -1; } else if( buildSQL( query->state, query->query )) { - osrfLogWarning( OSRF_LOG_MARK, "Unable to build SQL statement for query id # %d", - query->query->id ); + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( query->state, + "Unable to build SQL statement for query id # %d", query->query->id )); osrfAppSessionStatus( ctx->session, OSRF_STATUS_BADREQUEST, "osrfMethodException", ctx->request, "Unable to build SQL statement" ); return -1; @@ -332,6 +359,16 @@ int doSql( osrfMethodContext* ctx ) { return 0; } +/** + @brief Discard a previously stored query, as identified by a token. + @param ctx Pointer to the current method context. + @return Zero if successful, or -1 if not. + + Method parameters: + - query token, as previously returned by the .prepare method. + + Returns: Nothing. +*/ int doFinish( osrfMethodContext* ctx ) { if(osrfMethodVerifyContext( ctx )) { osrfLogError( OSRF_LOG_MARK, "Invalid method context" ); -- 2.43.2