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