From 411b36bf3441fdba8991ed5b7526c0b49af728e7 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 28 Mar 2014 13:04:06 -0400 Subject: [PATCH] Add array support directly to cstore and friends, including pcrud context org support Signed-off-by: Mike Rylander --- Open-ILS/src/c-apps/oils_sql.c | 406 +++++++++++++++++++++++++++++++++-------- 1 file changed, 333 insertions(+), 73 deletions(-) diff --git a/Open-ILS/src/c-apps/oils_sql.c b/Open-ILS/src/c-apps/oils_sql.c index e72bb49530..d39e7865d4 100644 --- a/Open-ILS/src/c-apps/oils_sql.c +++ b/Open-ILS/src/c-apps/oils_sql.c @@ -76,6 +76,7 @@ static inline void clearXactId( osrfMethodContext* ctx ); static jsonObject* doFieldmapperSearch ( osrfMethodContext* ctx, osrfHash* class_meta, jsonObject* where_hash, jsonObject* query_hash, int* err ); +static jsonObject* oilsUnnestArray( char* array_string ); static jsonObject* oilsMakeFieldmapperFromResult( dbi_result, osrfHash* ); static jsonObject* oilsMakeJSONFromResult( dbi_result ); @@ -374,57 +375,67 @@ int oilsExtendIDL( dbi_conn handle ) { osrfLogDebug(OSRF_LOG_MARK, "Found [%s] in IDL hash...", columnName); - /* determine the field type and storage attributes */ - - switch( dbi_result_get_field_type_idx( result, columnIndex )) { - - case DBI_TYPE_INTEGER : { - - if( !osrfHashGet(_f, "primitive") ) - osrfHashSet(_f, "number", "primitive"); - - int attr = dbi_result_get_field_attribs_idx( result, columnIndex ); - if( attr & DBI_INTEGER_SIZE8 ) - osrfHashSet( _f, "INT8", "datatype" ); - else - osrfHashSet( _f, "INT", "datatype" ); - break; - } - case DBI_TYPE_DECIMAL : - if( !osrfHashGet( _f, "primitive" )) - osrfHashSet( _f, "number", "primitive" ); + const char* dtype = osrfHashGet(_f, "datatype"); + const char* ptype = osrfHashGet(_f, "primitive"); + if (ptype && !strcmp(ptype, "array")) { /* for arrays, trust the IDL */ + if ( !strcmp(dtype,"text") || !strcmp(dtype,"timesamp") || !strcmp(dtype,"interval") || !strcmp(dtype,"bool") ) { + osrfHashSet( _f, "TEXT", "datatype" ); + } else { osrfHashSet( _f, "NUMERIC", "datatype" ); - break; - - case DBI_TYPE_STRING : - if( !osrfHashGet( _f, "primitive" )) - osrfHashSet( _f, "string", "primitive" ); - - osrfHashSet( _f,"TEXT", "datatype" ); - break; - - case DBI_TYPE_DATETIME : - if( !osrfHashGet( _f, "primitive" )) - osrfHashSet( _f, "string", "primitive" ); - - osrfHashSet( _f, "TIMESTAMP", "datatype" ); - break; - - case DBI_TYPE_BINARY : - if( !osrfHashGet( _f, "primitive" )) - osrfHashSet( _f, "string", "primitive" ); - - osrfHashSet( _f, "BYTEA", "datatype" ); + } + } else { /* determine the field type and storage attributes */ + + switch( dbi_result_get_field_type_idx( result, columnIndex )) { + + case DBI_TYPE_INTEGER : { + + if( !ptype ) + osrfHashSet(_f, "number", "primitive"); + + int attr = dbi_result_get_field_attribs_idx( result, columnIndex ); + if( attr & DBI_INTEGER_SIZE8 ) + osrfHashSet( _f, "INT8", "datatype" ); + else + osrfHashSet( _f, "INT", "datatype" ); + break; + } + case DBI_TYPE_DECIMAL : + if( !ptype ) + osrfHashSet( _f, "number", "primitive" ); + + osrfHashSet( _f, "NUMERIC", "datatype" ); + break; + + case DBI_TYPE_STRING : + if( !ptype ) + osrfHashSet( _f, "string", "primitive" ); + + osrfHashSet( _f,"TEXT", "datatype" ); + break; + + case DBI_TYPE_DATETIME : + if( !ptype ) + osrfHashSet( _f, "string", "primitive" ); + + osrfHashSet( _f, "TIMESTAMP", "datatype" ); + break; + + case DBI_TYPE_BINARY : + if( !ptype ) + osrfHashSet( _f, "string", "primitive" ); + + osrfHashSet( _f, "BYTEA", "datatype" ); + } + + osrfLogDebug( + OSRF_LOG_MARK, + "Setting [%s] to primitive [%s] and datatype [%s]...", + columnName, + osrfHashGet( _f, "primitive" ), + osrfHashGet( _f, "datatype" ) + ); } - - osrfLogDebug( - OSRF_LOG_MARK, - "Setting [%s] to primitive [%s] and datatype [%s]...", - columnName, - osrfHashGet( _f, "primitive" ), - osrfHashGet( _f, "datatype" ) - ); } ++columnIndex; } // end while loop for traversing columns of result @@ -1666,15 +1677,25 @@ static int verifyObjectPCRUD ( osrfMethodContext* ctx, osrfHash *class, const js int i = 0; const char* lcontext = NULL; while ( (lcontext = osrfStringArrayGetString(local_context, i++)) ) { - const char* fkey_value = oilsFMGetStringConst( param, lcontext ); - if( fkey_value ) { // if not null - osrfStringArrayAdd( context_org_array, fkey_value ); - osrfLogDebug( - OSRF_LOG_MARK, - "adding class-local field %s (value: %s) to the context org list", - lcontext, - osrfStringArrayGetString( context_org_array, context_org_array->size - 1 ) - ); + jsonObject* ctx_obj = oilsFMGetObject( param, lcontext ); + if ( ctx_obj->type == JSON_ARRAY ) { // it's a primitive-fleshed array + for (int cind = 0; cind < ctx_obj->size; cind++) { + osrfStringArrayAdd( + context_org_array, + jsonObjectToSimpleString( jsonObjectGetIndex(ctx_obj, cind) ) + ); + } + } else { + const char* fkey_value = jsonObjectToSimpleString( ctx_obj ); + if( fkey_value ) { // if not null + osrfStringArrayAdd( context_org_array, fkey_value ); + osrfLogDebug( + OSRF_LOG_MARK, + "adding class-local field %s (value: %s) to the context org list", + lcontext, + osrfStringArrayGetString( context_org_array, context_org_array->size - 1 ) + ); + } } } } @@ -1822,17 +1843,30 @@ static int verifyObjectPCRUD ( osrfMethodContext* ctx, osrfHash *class, const js int j = 0; const char* foreign_field = NULL; osrfStringArray* ctx_array = osrfHashGet( fcontext, "context" ); - while ( (foreign_field = osrfStringArrayGetString( ctx_array, j++ )) ) { - osrfStringArrayAdd( context_org_array, - oilsFMGetStringConst( _fparam, foreign_field )); - osrfLogDebug( OSRF_LOG_MARK, - "adding foreign class %s field %s (value: %s) " - "to the context org list", - class_name, - foreign_field, - osrfStringArrayGetString( - context_org_array, context_org_array->size - 1 ) - ); + + while ( (foreign_field = osrfStringArrayGetString(ctx_array, j++)) ) { + jsonObject* ctx_obj = oilsFMGetObject( _fparam, foreign_field ); + if ( ctx_obj->type == JSON_ARRAY ) { // it's a primitive-fleshed array + for (int cind = 0; cind < ctx_obj->size; cind++) { + osrfStringArrayAdd( + context_org_array, + jsonObjectToSimpleString( jsonObjectGetIndex(ctx_obj, cind) ) + ); + } + } else { + const char* fkey_value = jsonObjectToSimpleString( ctx_obj ); + if( fkey_value ) { // if not null + osrfStringArrayAdd( context_org_array, fkey_value ); + osrfLogDebug( + OSRF_LOG_MARK, + "adding foreign class %s field %s (value: %s) to the context org list", + class_name, + foreign_field, + osrfStringArrayGetString( + context_org_array, context_org_array->size - 1 ) + ); + } + } } jsonObjectFree( _fparam ); @@ -2198,7 +2232,7 @@ int doCreate( osrfMethodContext* ctx ) { const jsonObject* field_object = oilsFMGetObject( target, field_name ); - char* value; + char* value = NULL; if( field_object && field_object->classname ) { value = oilsFMGetString( field_object, @@ -2209,7 +2243,7 @@ int doCreate( osrfMethodContext* ctx ) { value = strdup( "t" ); else value = strdup( "f" ); - } else { + } else if( field_object && JSON_ARRAY != field_object->type ) { value = jsonObjectToSimpleString( field_object ); } @@ -2225,6 +2259,58 @@ int doCreate( osrfMethodContext* ctx ) { if( !field_object || field_object->type == JSON_NULL ) { buffer_add( val_buf, "DEFAULT" ); + } else if( !strcmp( get_primitive( field ), "array" )) { // we should try to handle a literal array + if (field_object->type == JSON_ARRAY) { // we should stringify it + buffer_add( val_buf, "ARRAY[" ); + const char* ftype = get_datatype( field ); + + for (int vind = 0; vind < field_object->size; vind++) { + char* vstr = jsonObjectToSimpleString( jsonObjectGetIndex(field_object, vind) ); + if ( !( !strncmp( ftype, "I", 1) || !strncmp( ftype, "N", 1) ) ) { // it's text-y, quote it + if ( !dbi_conn_quote_string( writehandle, &vstr ) ) { + osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, vstr ); + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Error quoting string -- please see the error log for more details" + ); + free( vstr ); + buffer_free( table_buf ); + buffer_free( col_buf ); + buffer_free( val_buf ); + osrfAppRespondComplete( ctx, NULL ); + return -1; + } + } + if (cind > 0) OSRF_BUFFER_ADD_CHAR( val_buf, ',' ); + buffer_add( val_buf, vstr ); + free(vstr); + } + + } else { + if( dbi_conn_quote_string( writehandle, &value )) { + OSRF_BUFFER_ADD( val_buf, value ); + + } else { + osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value ); + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Error quoting string -- please see the error log for more details" + ); + free( value ); + buffer_free( table_buf ); + buffer_free( col_buf ); + buffer_free( val_buf ); + osrfAppRespondComplete( ctx, NULL ); + return -1; + } + } + } else if( !strcmp( get_primitive( field ), "number" )) { const char* numtype = get_datatype( field ); if( !strcmp( numtype, "INT8" )) { @@ -2258,7 +2344,7 @@ int doCreate( osrfMethodContext* ctx ) { } } - free( value ); + if (value) free( value ); } osrfHashIteratorFree( field_itr ); @@ -6110,7 +6196,7 @@ int doUpdate( osrfMethodContext* ctx ) { value = strdup( "t" ); else value = strdup( "f" ); - } else { + } else if( field_object && JSON_ARRAY != field_object->type ) { value = jsonObjectToSimpleString( field_object ); if( field_object && JSON_NUMBER == field_object->type ) value_is_numeric = 1; @@ -6129,6 +6215,64 @@ int doUpdate( osrfMethodContext* ctx ) { buffer_fadd( sql, " %s = NULL", field_name ); } + } else if( !strcmp( get_primitive( field_def ), "array" )) { // we should try to handle a literal array + if( first ) + first = 0; + else + OSRF_BUFFER_ADD_CHAR( sql, ',' ); + + if (field_object->type == JSON_ARRAY) { // we should stringify it + buffer_fadd( sql, " %s = ARRAY[", field_name ); + const char* ftype = get_datatype( field_def ); + + for (int vind = 0; vind < field_object->size; vind++) { + char* vstr = jsonObjectToSimpleString( jsonObjectGetIndex(field_object, vind) ); + if ( !( !strncmp( ftype, "I", 1) || !strncmp( ftype, "N", 1) ) ) { // it's text-y, quote it + if ( !dbi_conn_quote_string( writehandle, &vstr ) ) { + osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, vstr ); + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Error quoting string -- please see the error log for more details" + ); + free( vstr ); + free( id ); + osrfHashIteratorFree( field_itr ); + buffer_free( sql ); + osrfAppRespondComplete( ctx, NULL ); + return -1; + } + } + if (cind > 0) OSRF_BUFFER_ADD_CHAR( sql, ',' ); + buffer_add( sql, vstr ); + free(vstr); + } + OSRF_BUFFER_ADD_CHAR( sql, ']' ); + + } else { // already stringy + if( dbi_conn_quote_string( writehandle, &value )) { + buffer_fadd( sql, " %s = %s", field_name, value ); + + } else { + osrfLogError( OSRF_LOG_MARK, "%s: Error quoting string [%s]", modulename, value ); + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Error quoting string -- please see the error log for more details" + ); + free( value ); + free( id ); + osrfHashIteratorFree( field_itr ); + buffer_free( sql ); + osrfAppRespondComplete( ctx, NULL ); + return -1; + } + } + } else if( value_is_numeric || !strcmp( get_primitive( field_def ), "number") ) { if( first ) first = 0; @@ -6358,6 +6502,116 @@ int doDelete( osrfMethodContext* ctx ) { return rc; } +static jsonObject* oilsUnnestArray( char* array_string ) { + + // Turn it into a JSON text array + osrfLogDebug( OSRF_LOG_MARK, "Building SQL to pull apart an array" ); + + if( dbi_conn_quote_string( dbhandle, &array_string )) { + jsonObject* unnest_field = jsonNewObjectFmt( "{'from':['unnest',%s]}", array_string ); + + push_query_frame(); + char* unnest_sql = buildQuery( NULL, unnest_field, 0 ); + pop_query_frame(); + + dbi_result unnest_result = dbi_conn_query( dbhandle, unnest_sql ); + + if( unnest_result ) { + unsigned short unnest_type = dbi_result_get_field_type_idx( unnest_result, 1 ); + int unnest_attr = dbi_result_get_field_attribs_idx( unnest_result, 1 ); + jsonObject* unnest_list = jsonNewObjectType( JSON_ARRAY ); + + if( dbi_result_first_row( unnest_result ) ) { + + do { + switch( unnest_type ) { + + case DBI_TYPE_INTEGER : + + if( unnest_attr & DBI_INTEGER_SIZE8 ) + jsonObjectPush( + unnest_list, + jsonNewNumberObject( + dbi_result_get_longlong_idx( unnest_result, 1 ) + ) + ); + else + jsonObjectPush( + unnest_list, + jsonNewNumberObject( + dbi_result_get_int_idx( unnest_result, 1 ) + ) + ); + break; + + case DBI_TYPE_DECIMAL : + jsonObjectPush( + unnest_list, + jsonNewNumberObject( + dbi_result_get_double_idx( unnest_result, 1 ) + ) + ); + break; + + case DBI_TYPE_STRING : + jsonObjectPush( + unnest_list, + jsonNewObject( + dbi_result_get_string_idx( unnest_result, 1 ) + ) + ); + break; + + case DBI_TYPE_DATETIME : { + + char dt_string[ 256 ] = ""; + struct tm gmdt; + + // Fetch the date column as a time_t + time_t _tmp_dt = dbi_result_get_datetime_idx( unnest_result, 1 ); + + // Translate the time_t to a human-readable string + if( !( unnest_attr & DBI_DATETIME_DATE )) { + gmtime_r( &_tmp_dt, &gmdt ); + strftime( dt_string, sizeof( dt_string ), "%T", &gmdt ); + } else if( !( unnest_attr & DBI_DATETIME_TIME )) { + localtime_r( &_tmp_dt, &gmdt ); + strftime( dt_string, sizeof( dt_string ), "%04Y-%m-%d", &gmdt ); + } else { + localtime_r( &_tmp_dt, &gmdt ); + strftime( dt_string, sizeof( dt_string ), "%04Y-%m-%dT%T%z", &gmdt ); + } + + jsonObjectPush( unnest_list, jsonNewObject( dt_string ) ); + + break; + } + case DBI_TYPE_BINARY : + osrfLogError( OSRF_LOG_MARK, "Can't do binary in UNNEST" ); + } // End switch + } while( dbi_result_next_row( unnest_result )); + + } else { + osrfLogDebug( OSRF_LOG_MARK, "%s returned no results for query %s", + modulename, unnest_sql ); + } + + /* clean up the query */ + dbi_result_free( unnest_result ); + } + + free( unnest_sql ); + jsonObjectFree(unnest_field); + + } else { + osrfLogError( OSRF_LOG_MARK, "%s: Error quoting key string [%s]", modulename, array_string ); + return jsonNewObject( array_string ); + } + + return unnest_list; +} + + /** @brief Translate a row returned from the database into a jsonObject of type JSON_ARRAY. @param result An iterator for a result set; we only look at the current row. @@ -6427,6 +6681,12 @@ static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* m // of columns in the SELECT clause). if( dbi_result_field_is_null_idx( result, columnIndex )) { jsonObjectSetIndex( object, fmIndex, jsonNewObject( NULL )); + + } else if( !strcmp( get_primitive( field ), "array" )) { + char* array_string = dbi_result_get_string_copy_idx( result, columnIndex ); + jsonObjectSetIndex( object, fmIndex, oilsUnnestArray(array_string) ); + free( array_string ); + } else { switch( type ) { @@ -6486,7 +6746,7 @@ static jsonObject* oilsMakeFieldmapperFromResult( dbi_result result, osrfHash* m osrfLogError( OSRF_LOG_MARK, "Can't do binary at column %s : index %d", columnName, columnIndex ); } // End switch - } + } // End interned-array if ++columnIndex; } // End while -- 2.11.0