From a3db1107dd1d259d779f59c666ac0f4ca2c2c41c Mon Sep 17 00:00:00 2001 From: scottmk Date: Fri, 28 May 2010 01:46:29 +0000 Subject: [PATCH] Support series expressions, i.e. a series of expressions separated by operators or commas. This construct will be especially useful for chains of ANDs or ORs. M Open-ILS/include/openils/oils_buildq.h M Open-ILS/src/c-apps/oils_storedq.c M Open-ILS/src/c-apps/buildSQL.c git-svn-id: svn://svn.open-ils.org/ILS/trunk@16526 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/include/openils/oils_buildq.h | 6 +- Open-ILS/src/c-apps/buildSQL.c | 117 ++++++++++++++++++++----- Open-ILS/src/c-apps/oils_storedq.c | 97 +++++++++++++++++++- 3 files changed, 195 insertions(+), 25 deletions(-) diff --git a/Open-ILS/include/openils/oils_buildq.h b/Open-ILS/include/openils/oils_buildq.h index 42ea5f1533..6f25490328 100644 --- a/Open-ILS/include/openils/oils_buildq.h +++ b/Open-ILS/include/openils/oils_buildq.h @@ -158,6 +158,7 @@ typedef enum { EXP_NULL, EXP_NUMBER, EXP_OPERATOR, + EXP_SERIES, EXP_STRING, EXP_SUBQUERY } ExprType; @@ -181,6 +182,7 @@ struct Expression_ { int cast_type_id; int negate; // Boolean BindVar* bind; + Expression* subexp_list; // Linked list of subexpressions }; struct QSeq_ { @@ -217,7 +219,7 @@ void storedQFree( StoredQ* sq ); void storedQCleanup( void ); -int buildSQL( BuildSQLState* state, StoredQ* query ); +int buildSQL( BuildSQLState* state, const StoredQ* query ); void oilsStoredQSetVerbose( void ); @@ -227,7 +229,7 @@ jsonObject* oilsNextRow( BuildSQLState* state ); jsonObject* oilsBindVarList( osrfHash* bindvar_list ); -int oilsApplyBindValues( BuildSQLState* state, jsonObject* bindings ); +int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ); #ifdef __cplusplus } diff --git a/Open-ILS/src/c-apps/buildSQL.c b/Open-ILS/src/c-apps/buildSQL.c index 0951074cf9..3fcf85e800 100644 --- a/Open-ILS/src/c-apps/buildSQL.c +++ b/Open-ILS/src/c-apps/buildSQL.c @@ -15,15 +15,16 @@ #include "openils/oils_sql.h" #include "openils/oils_buildq.h" -static void build_Query( BuildSQLState* state, StoredQ* query ); -static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ); -static void buildSelect( BuildSQLState* state, StoredQ* query ); -static void buildFrom( BuildSQLState* state, FromRelation* core_from ); -static void buildJoin( BuildSQLState* state, FromRelation* join ); -static void buildSelectList( BuildSQLState* state, SelectItem* item ); -static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ); -static void buildExpression( BuildSQLState* state, Expression* expr ); -static void buildBindVar( BuildSQLState* state, BindVar* bind ); +static void build_Query( BuildSQLState* state, const StoredQ* query ); +static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ); +static void buildSelect( BuildSQLState* state, const StoredQ* query ); +static void buildFrom( BuildSQLState* state, const FromRelation* core_from ); +static void buildJoin( BuildSQLState* state, const FromRelation* join ); +static void buildSelectList( BuildSQLState* state, const SelectItem* item ); +static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ); +static void buildExpression( BuildSQLState* state, const Expression* expr ); +static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ); +static void buildBindVar( BuildSQLState* state, const BindVar* bind ); static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ); static void add_newline( BuildSQLState* state ); @@ -111,7 +112,7 @@ jsonObject* oilsBindVarList( osrfHash* bindvar_list ) { The @a bindings parameter must be a JSON_HASH. The keys are the names of bind variables. The values are the corresponding values for the variables. */ -int oilsApplyBindValues( BuildSQLState* state, jsonObject* bindings ) { +int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) { if( !state ) { osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" ); return 1; @@ -155,7 +156,7 @@ int oilsApplyBindValues( BuildSQLState* state, jsonObject* bindings ) { Clear the output buffer, call build_Query() to do the work, and add a closing semicolon. */ -int buildSQL( BuildSQLState* state, StoredQ* query ) { +int buildSQL( BuildSQLState* state, const StoredQ* query ) { state->error = 0; buffer_reset( state->sql ); state->indent = 0; @@ -177,7 +178,7 @@ int buildSQL( BuildSQLState* state, StoredQ* query ) { Look at the query type and branch to the corresponding routine. */ -static void build_Query( BuildSQLState* state, StoredQ* query ) { +static void build_Query( BuildSQLState* state, const StoredQ* query ) { if( buffer_length( state->sql )) add_newline( state ); @@ -209,7 +210,7 @@ static void build_Query( BuildSQLState* state, StoredQ* query ) { @param query Pointer to the query to be built. @param type_str The query type, as a string. */ -static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) { +static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) { QSeq* seq = query->child_list; if( !seq ) { @@ -246,7 +247,7 @@ static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_s @param state Pointer to the query-building context. @param query Pointer to the StoredQ structure that represents the query. */ -static void buildSelect( BuildSQLState* state, StoredQ* query ) { +static void buildSelect( BuildSQLState* state, const StoredQ* query ) { FromRelation* from_clause = query->from_clause; if( !from_clause ) { @@ -330,7 +331,7 @@ static void buildSelect( BuildSQLState* state, StoredQ* query ) { @param Pointer to the query-building context. @param Pointer to the StoredQ query to which the FROM clause belongs. */ -static void buildFrom( BuildSQLState* state, FromRelation* core_from ) { +static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) { add_newline( state ); buffer_add( state->sql, "FROM" ); @@ -401,7 +402,7 @@ static void buildFrom( BuildSQLState* state, FromRelation* core_from ) { decr_indent( state ); } -static void buildJoin( BuildSQLState* state, FromRelation* join ) { +static void buildJoin( BuildSQLState* state, const FromRelation* join ) { add_newline( state ); switch( join->join_type ) { case JT_NONE : @@ -494,7 +495,12 @@ static void buildJoin( BuildSQLState* state, FromRelation* join ) { } } -static void buildSelectList( BuildSQLState* state, SelectItem* item ) { +/** + @brief Build a SELECT list. + @param state Pointer to the query-building context. + @param item Pointer to the first in a linked list of SELECT items. +*/ +static void buildSelectList( BuildSQLState* state, const SelectItem* item ) { int first = 1; while( item ) { @@ -524,7 +530,7 @@ static void buildSelectList( BuildSQLState* state, SelectItem* item ) { @param state Pointer to the query-building context. @param ord_list Pointer to the first node in a linked list of OrderItems. */ -static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) { +static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) { add_newline( state ); buffer_add( state->sql, "ORDER BY" ); incr_indent( state ); @@ -554,7 +560,7 @@ static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) { @param state Pointer to the query-building context. @param expr Pointer to the Expression representing the expression to be built. */ -static void buildExpression( BuildSQLState* state, Expression* expr ) { +static void buildExpression( BuildSQLState* state, const Expression* expr ) { if( !expr ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Internal error: NULL pointer to Expression" )); @@ -728,6 +734,19 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } } + if( expr->negate ) + buffer_add_char( state->sql, ')' ); + + break; + case EXP_SERIES : + if( expr->negate ) + buffer_add( state->sql, "NOT (" ); + + buildSeries( state, expr->subexp_list, expr->op ); + if( state->error ) { + sqlAddMsg( state, "Unable to build series expression using operator \"%s\"", + expr->op ? expr->op : "," ); + } if( expr->negate ) buffer_add_char( state->sql, ')' ); @@ -767,6 +786,52 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { buffer_add_char( state->sql, ')' ); } +/** + @brief Build a series of expressions separated by a specified operator, or by commas. + @param state Pointer to the query-building context. + @param subexp_list Pointer to the first Expression in a linked list. + @param op Pointer to the operator, or NULL for commas. + + If the operator is AND or OR (in upper, lower, or mixed case), the second and all + subsequent operators will begin on a new line. +*/ +static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) { + + int comma = 0; // Boolean; true if separator is a comma + int newline_needed = 0; // Boolean; true if operator is AND or OR + + if( !op ) { + op = ","; + comma = 1; + } else if( !strcmp( op, "," )) + comma = 1; + else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" )) + newline_needed = 1; + + int first = 1; // Boolean; true for first item in list + while( subexp_list ) { + if( first ) + first = 0; // No separator needed yet + else { + // Insert a separator + if( comma ) + buffer_add( state->sql, ", " ); + else { + if( newline_needed ) + add_newline( state ); + else + buffer_add_char( state->sql, ' ' ); + + buffer_add( state->sql, op ); + buffer_add_char( state->sql, ' ' ); + } + } + + buildExpression( state, subexp_list ); + subexp_list = subexp_list->next; + } +} + /** @brief Add the value of a bind variable to an SQL statement. @param state Pointer to the query-building context. @@ -775,7 +840,7 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { The value may be a null, a scalar, or an array of nulls and/or scalars, depending on the type of the bind variable. */ -static void buildBindVar( BuildSQLState* state, BindVar* bind ) { +static void buildBindVar( BuildSQLState* state, const BindVar* bind ) { // Decide where to get the value, if any const jsonObject* value = NULL; @@ -902,6 +967,10 @@ static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* ob } } +/** + @brief Start a new line in the output, with the current level of indentation. + @param state Pointer to the query-building context. +*/ static void add_newline( BuildSQLState* state ) { buffer_add_char( state->sql, '\n' ); @@ -917,10 +986,18 @@ static void add_newline( BuildSQLState* state ) { } } +/** + @brief Increase the degree of indentation. + @param state Pointer to the query-building context. +*/ static inline void incr_indent( BuildSQLState* state ) { ++state->indent; } +/** + @brief Reduce the degree of indentation. + @param state Pointer to the query-building context. +*/ static inline void decr_indent( BuildSQLState* state ) { if( state->indent ) --state->indent; diff --git a/Open-ILS/src/c-apps/oils_storedq.c b/Open-ILS/src/c-apps/oils_storedq.c index 6fb97694fc..3b01840f94 100644 --- a/Open-ILS/src/c-apps/oils_storedq.c +++ b/Open-ILS/src/c-apps/oils_storedq.c @@ -43,7 +43,9 @@ static void bindVarFree( char* name, void* p ); static Expression* getExpression( BuildSQLState* state, int id ); static Expression* constructExpression( BuildSQLState* state, dbi_result result ); +static void expressionListFree( Expression* exp ); static void expressionFree( Expression* exp ); +static Expression* getExpressionList( BuildSQLState* state, int id ); static OrderItem* getOrderByList( BuildSQLState* state, int query_id ); static OrderItem* constructOrderItem( BuildSQLState* state, dbi_result result ); @@ -119,6 +121,7 @@ StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.stored_query table: #%d %s", errnum, msg ? msg : "No description available" )); + state->error = 1; } pop_id( &state->query_stack ); @@ -503,6 +506,7 @@ static FromRelation* getFromRelation( BuildSQLState* state, int id ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.from_relation table: #%d %s", errnum, msg ? msg : "No description available" )); + state->error = 1; } if( fr ) @@ -709,6 +713,7 @@ static FromRelation* getJoinList( BuildSQLState* state, int id ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.from_relation table for join list: #%d %s", errnum, msg ? msg : "No description available" )); + state->error = 1; } return join_list; @@ -795,8 +800,9 @@ static SelectItem* getSelectList( BuildSQLState* state, int query_id ) { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, - "Unable to query query.select_list table: #%d %s", - errnum, msg ? msg : "No description available" )); + "Unable to query query.select_list table: #%d %s", + errnum, msg ? msg : "No description available" )); + state->error = 1; } return select_list; @@ -917,6 +923,7 @@ static BindVar* getBindVar( BuildSQLState* state, const char* name ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.bind_variable table for \"%s\": #%d %s", name, errnum, msg ? msg : "No description available" )); + state->error = 1; } if( bind ) { @@ -1068,6 +1075,7 @@ static Expression* getExpression( BuildSQLState* state, int id ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.expression table: #%d %s", errnum, msg ? msg : "No description available" )); + state->error = 1; } pop_id( &state->expr_stack ); @@ -1116,6 +1124,8 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result type = EXP_NUMBER; else if( !strcmp( type_str, "xop" )) type = EXP_OPERATOR; + else if( !strcmp( type_str, "xser" )) + type = EXP_SERIES; else if( !strcmp( type_str, "xstr" )) type = EXP_STRING; else if( !strcmp( type_str, "xsubq" )) @@ -1175,6 +1185,7 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result Expression* right_operand = NULL; StoredQ* subquery = NULL; BindVar* bind = NULL; + Expression* subexp_list = NULL; if( EXP_OPERATOR == type ) { // Load left and/or right operands @@ -1268,6 +1279,14 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result return NULL; } } + } else if( EXP_SERIES == type ) { + subexp_list = getExpressionList( state, id ); + if( state->error ) { + osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to get subexpressions for expression series using operator \"%s\"", + operator ? operator : "," )); + return NULL; + } } else if( EXP_SUBQUERY == type ) { if( -1 == subquery_id ) { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, @@ -1339,15 +1358,29 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result exp->cast_type_id = subquery_id; exp->negate = negate; exp->bind = bind; + exp->subexp_list = subexp_list; return exp; } +/** + @brief Free all the Expressions in a linked list of Expressions. + @param exp Pointer to the first Expression in the list. +*/ +static void expressionListFree( Expression* exp ) { + while( exp ) { + Expression* next = exp->next; + expressionFree( exp ); + exp = next; + } +} + /** @brief Deallocate an Expression. @param exp Pointer to the Expression to be deallocated. - Free the strings owned by the Expression. Put the Expressions itself into a free list. + Free the strings owned by the Expression. Put the Expression itself, and any + subexpressions that it owns, into a free list. */ static void expressionFree( Expression* exp ) { if( exp ) { @@ -1371,15 +1404,72 @@ static void expressionFree( Expression* exp ) { storedQFree( exp->subquery ); exp->subquery = NULL; } + // We don't free the bind member here because the Expression doesn't own it; // the bindvar_list hash owns it, so that multiple Expressions can reference it. + if( exp->subexp_list ) { + // Free the linked list of subexpressions + expressionListFree( exp->subexp_list ); + exp->subexp_list = NULL; + } + // Prepend to the free list exp->next = free_expression_list; free_expression_list = exp; } } +/** + @brief Build a list of subexpressions. + @param state Pointer to the query-building context. + @param id ID of the parent Expression. + @return A pointer to the first in a linked list of Expressions, if there are any; or + NULL if there aren't any, or in case of an error. +*/ +static Expression* getExpressionList( BuildSQLState* state, int id ) { + Expression* exp_list = NULL; + + // The ORDER BY is in descending order so that we can build the list by adding to + // the head, and it will wind up in the right order. + dbi_result result = dbi_conn_queryf( state->dbhandle, + "SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, column_name, " + "left_operand, operator, right_operand, function_id, subquery, cast_type, negate, " + "bind_variable " + "FROM query.expression WHERE parent_expr = %d " + "ORDER BY seq_no desc;", id ); + + if( result ) { + if( dbi_result_first_row( result ) ) { + while( 1 ) { + Expression* exp = constructExpression( state, result ); + if( exp ) { + PRINT( "Found a subexpression\n" ); + exp->next = exp_list; + exp_list = exp; + } else { + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to build subexpression list for expression id #%d", id )); + expressionListFree( exp_list ); + exp_list = NULL; + break; + } + if( !dbi_result_next_row( result ) ) + break; + }; + } + } else { + const char* msg; + int errnum = dbi_conn_error( state->dbhandle, &msg ); + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to query query.expression table for expression list: #%d %s", + errnum, msg ? msg : "No description available" )); + state->error = 1; + } + + return exp_list; +} + /** @brief Build a list of ORDER BY items as a linked list of OrderItems. @param state Pointer to the query-building context. @@ -1422,6 +1512,7 @@ static OrderItem* getOrderByList( BuildSQLState* state, int query_id ) { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.order_by_list table: #%d %s", errnum, msg ? msg : "No description available" )); + state->error = 1; } return ord_list; -- 2.43.2