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