Open-ILS/src/c-apps/oils_execsql.c

   1 /**
   2         @file oils_execsql.c
   3         @brief Excecute a specified SQL query and return the results.
   4 */
   5
   6 #include <stdlib.h>
   7 #include <stdio.h>
   8 #include <dbi/dbi.h>
   9 #include "opensrf/utils.h"
  10 #include "opensrf/log.h"
  11 #include "opensrf/string_array.h"
  12 #include "opensrf/osrf_json.h"
  13 #include "opensrf/osrf_application.h"
  14 #include "openils/oils_sql.h"
  15 #include "openils/oils_buildq.h"
  16
  17 static jsonObject* get_row( BuildSQLState* state );
  18 static jsonObject* get_date_column( dbi_result result, int col_idx );
  19 static int values_missing( BuildSQLState* state );
  20
  21 /**
  22         @brief Execute the current SQL statement and return the first row.
  23         @param state Pointer to the query-building context.
  24         @return Pointer to a newly-allocated jsonObject representing the row, if there is one; or
  25                 NULL if there isn't.
  26
  27         The returned row is a JSON_ARRAY of column values, of which each is a JSON_STRING,
  28         JSON_NUMBER, or JSON_NULL.
  29 */
  30 jsonObject* oilsFirstRow( BuildSQLState* state ) {
  31
  32         if( !state )
  33                 return NULL;
  34
  35         // Make sure all the bind variables have values for them
  36         if( !state->values_required && values_missing( state )) {
  37                 state->error = 1;
  38                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
  39                         "Unable to execute query: values not available for all bind variables\n" ));
  40                 return NULL;
  41         }
  42
  43         if( state->result )
  44                 dbi_result_free( state->result );
  45
  46         // Execute the query
  47         state->result = dbi_conn_query( state->dbhandle, OSRF_BUFFER_C_STR( state->sql ));
  48         if( !state->result ) {
  49                 state->error = 1;
  50                 const char* msg;
  51                 (void) dbi_conn_error( state->dbhandle, &msg );
  52                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
  53                         "Unable to execute query: %s",msg ? msg : "No description available" ));
  54                 if( ! oilsIsDBConnected( state->dbhandle ))
  55                         state->panic = 1;
  56                 return NULL;
  57         }
  58
  59         // Get the first row
  60         if( dbi_result_first_row( state->result ))
  61                 return get_row( state );
  62         else {
  63                 dbi_result_free( state->result );
  64                 state->result = NULL;
  65                 return NULL;         // No rows returned
  66         }
  67 }
  68
  69 /**
  70         @brief Return the next row from a previously executed SQL statement.
  71         @param state Pointer to the query-building context.
  72         @return Pointer to a newly-allocated jsonObject representing the row, if there is one; or
  73                 NULL if there isn't.
  74
  75         The returned row is a JSON_ARRAY of column values, of which each is a JSON_STRING,
  76         JSON_NUMBER, or JSON_NULL.
  77 */
  78 jsonObject* oilsNextRow( BuildSQLState* state ) {
  79
  80         if( !state || !state->result )
  81                 return NULL;
  82
  83         // Get the next row
  84         if( dbi_result_next_row( state->result ))
  85                 return get_row( state );
  86         else {
  87                 dbi_result_free( state->result );
  88                 state->result = NULL;
  89                 return NULL;         // No next row returned
  90         }
  91 }
  92
  93 /**
  94         @brief Construct a JSON representation of a returned row.
  95         @param state Pointer to the query-building context.
  96         @return Pointer to a newly-allocated jsonObject representing the row.
  97 */
  98 static jsonObject* get_row( BuildSQLState* state  ) {
  99         unsigned int col_count = dbi_result_get_numfields( state->result );
 100         jsonObject* row = jsonNewObjectType( JSON_ARRAY );
 101
 102         unsigned int i = 1;
 103         for( i = 1; i <= col_count; ++i ) {
 104
 105                 if( dbi_result_field_is_null_idx( state->result, i )) {
 106                         jsonObjectPush( row, jsonNewObjectType( JSON_NULL ));
 107                         continue;       // Column is null
 108                 }
 109
 110                 jsonObject* col_value = NULL;
 111                 int type = dbi_result_get_field_type_idx( state->result, i );
 112                 switch( type ) {
 113                         case DBI_TYPE_INTEGER : {
 114                                 long long value = dbi_result_get_longlong_idx( state->result, i );
 115                                 col_value = jsonNewNumberObject( (double) value );
 116                                 break;
 117                         }
 118                         case DBI_TYPE_DECIMAL : {
 119                                 double value = dbi_result_get_double_idx( state->result, i );
 120                                 col_value = jsonNewNumberObject( value );
 121                                 break;
 122                         }
 123                         case DBI_TYPE_STRING : {
 124                                 const char* value = dbi_result_get_string_idx( state->result, i );
 125                                 col_value = jsonNewObject( value );
 126                                 break;
 127                         }
 128                         case DBI_TYPE_BINARY : {
 129                                 osrfLogError( OSRF_LOG_MARK, "Binary types not supported; column set to null" );
 130                                 col_value = jsonNewObjectType( JSON_NULL );
 131                                 break;
 132                         }
 133                         case DBI_TYPE_DATETIME : {
 134                                 col_value = get_date_column( state->result, i );
 135                                 break;
 136                         }
 137                         default :
 138                                 osrfLogError( OSRF_LOG_MARK,
 139                                         "Unrecognized column type %d; column set to null", type );
 140                                 col_value = jsonNewObjectType( JSON_NULL );
 141                                 break;
 142                 }
 143                 jsonObjectPush( row, col_value );
 144         }
 145
 146         return row;
 147 }
 148
 149 /**
 150         @brief Translate a date column into a string.
 151         @param result Reference to the current returned row.
 152         @param col_idx Column number (starting with 1) within the row.
 153         @return Pointer to a newly-allocated JSON_STRING containing a formatted date string.
 154
 155         The calling code is responsible for freeing the returned jsonObject by calling
 156         jsonObjectFree().
 157 */
 158 static jsonObject* get_date_column( dbi_result result, int col_idx ) {
 159
 160         time_t timestamp = dbi_result_get_datetime_idx( result, col_idx );
 161         char timestring[ 256 ] = "";
 162         int attr = dbi_result_get_field_attribs_idx( result, col_idx );
 163         struct tm gmdt;
 164
 165         if( !( attr & DBI_DATETIME_DATE )) {
 166                 gmtime_r( &timestamp, &gmdt );
 167                 strftime( timestring, sizeof( timestring ), "%T", &gmdt );
 168         } else if( !( attr & DBI_DATETIME_TIME )) {
 169                 localtime_r( &timestamp, &gmdt );
 170                 strftime( timestring, sizeof( timestring ), "%F", &gmdt );
 171         } else {
 172                 localtime_r( &timestamp, &gmdt );
 173                 strftime( timestring, sizeof( timestring ), "%FT%T%z", &gmdt );
 174         }
 175
 176         return jsonNewObject( timestring );
 177 }
 178
 179 /**
 180         @brief Determine whether all bind variables have values supplied for them.
 181         @param state Pointer to the query-building context.
 182         @return The number of bind variables with no available value.
 183 */
 184 static int values_missing( BuildSQLState* state ) {
 185         if( !state->bindvar_list || osrfHashGetCount( state->bindvar_list ) == 0 )
 186                 return 0;   // Nothing to count
 187
 188         int count = 0;
 189         osrfHashIterator* iter = osrfNewHashIterator( state->bindvar_list );
 190
 191         BindVar* bind = NULL;
 192         while(( bind = osrfHashIteratorNext( iter ))) {
 193                 if( !bind->actual_value && !bind->default_value ) {
 194                         sqlAddMsg( state, "No value for bind value \"%s\", with label \"%s\"",
 195                                 bind->name, bind->label );
 196                         ++count;
 197                 }
 198         }
 199
 200         osrfHashIteratorFree( iter );
 201         return count;
 202 }