From 4c1b01b3954982515e6b73acb15b502cd82db00b Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 6 May 2010 18:56:46 +0000 Subject: [PATCH] 1. Support negation of an expression (except in a few cases where it doesn't make sense, such as negation of a number or string). 2. Support HAVING clauses. This isn't useful yet because we don't support GROUP BY yet. 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@16399 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/include/openils/oils_buildq.h | 5 +- Open-ILS/src/c-apps/buildSQL.c | 80 +++++++++++++++++++++----- Open-ILS/src/c-apps/oils_storedq.c | 34 ++++++++--- 3 files changed, 92 insertions(+), 27 deletions(-) diff --git a/Open-ILS/include/openils/oils_buildq.h b/Open-ILS/include/openils/oils_buildq.h index c031c1f6a6..756b3f8b8c 100644 --- a/Open-ILS/include/openils/oils_buildq.h +++ b/Open-ILS/include/openils/oils_buildq.h @@ -73,6 +73,7 @@ struct StoredQ_ { Expression* where_clause; SelectItem* select_list; QSeq* child_list; + Expression* having_clause; OrderItem* order_by_list; }; @@ -127,9 +128,6 @@ typedef enum { EXP_FIELD, EXP_FUNCTION, EXP_IN, - EXP_NOT_BETWEEN, - EXP_NOT_EXIST, - EXP_NOT_IN, EXP_NULL, EXP_NUMBER, EXP_OPERATOR, @@ -154,6 +152,7 @@ struct Expression_ { int subquery_id; StoredQ* subquery; int cast_type_id; + int negate; // Boolean }; struct QSeq_ { diff --git a/Open-ILS/src/c-apps/buildSQL.c b/Open-ILS/src/c-apps/buildSQL.c index e3031d2df9..5f5052106d 100644 --- a/Open-ILS/src/c-apps/buildSQL.c +++ b/Open-ILS/src/c-apps/buildSQL.c @@ -168,12 +168,27 @@ static void buildSelect( BuildSQLState* state, StoredQ* query ) { state->error = 1; return; } - //else - //buffer_add_char( state->sql, ' ' ); decr_indent( state ); } - // Build WHERE clause, if there is one + // To do: build GROUP BY clause, if there is one + + // Build HAVING clause, if there is one + if( query->having_clause ) { + add_newline( state ); + buffer_add( state->sql, "HAVING" ); + incr_indent( state ); + add_newline( state ); + buildExpression( state, query->having_clause ); + if( state->error ) { + sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id ); + state->error = 1; + return; + } + decr_indent( state ); + } + + // Build ORDER BY clause, if there is one if( query->order_by_list ) { buildOrderBy( state, query->order_by_list ); if( state->error ) { @@ -182,7 +197,11 @@ static void buildSelect( BuildSQLState* state, StoredQ* query ) { return; } } - + + // To do: Build LIMIT clause, if there is one + + // To do: Build OFFSET clause, if there is one + state->error = 0; } @@ -335,7 +354,7 @@ static void buildJoin( BuildSQLState* state, FromRelation* join ) { buffer_add( state->sql, effective_alias ); buffer_add_char( state->sql, '\"' ); } - + if( join->on_clause ) { incr_indent( state ); add_newline( state ); @@ -356,7 +375,7 @@ static void buildJoin( BuildSQLState* state, FromRelation* join ) { } static void buildSelectList( BuildSQLState* state, SelectItem* item ) { - + int first = 1; while( item ) { if( !first ) @@ -428,10 +447,16 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { switch( expr->type ) { case EXP_BETWEEN : + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + sqlAddMsg( state, "BETWEEN expressions not yet supported" ); state->error = 1; break; case EXP_BOOL : + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + if( expr->literal ) { buffer_add( state->sql, expr->literal ); buffer_add_char( state->sql, ' ' ); @@ -439,14 +464,20 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { buffer_add( state->sql, "FALSE " ); break; case EXP_CASE : + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + sqlAddMsg( state, "CASE expressions not yet supported" ); state->error = 1; break; - case EXP_CAST : // Type cast + case EXP_CAST : // Type cast sqlAddMsg( state, "Cast expressions not yet supported" ); state->error = 1; break; case EXP_COLUMN : // Table column + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + if( expr->table_alias ) { buffer_add_char( state->sql, '\"' ); buffer_add( state->sql, expr->table_alias ); @@ -466,6 +497,9 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { "No subquery found for EXIST expression # %d", expr->id )); state->error = 1; } else { + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + buffer_add( state->sql, "EXISTS (" ); incr_indent( state ); build_Query( state, expr->subquery ); @@ -475,14 +509,23 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } break; case EXP_FIELD : + sqlAddMsg( state, "Field expressions not yet supported" ); + state->error = 1; + break; case EXP_FUNCTION : - sqlAddMsg( state, "Expression type not yet supported" ); + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + + sqlAddMsg( state, "Function expressions not yet supported" ); state->error = 1; break; case EXP_IN : if( expr->left_operand ) { buildExpression( state, expr->left_operand ); if( !state->error ) { + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + if( expr->subquery ) { buffer_add( state->sql, " IN (" ); incr_indent( state ); @@ -497,13 +540,10 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } } break; - case EXP_NOT_BETWEEN : - case EXP_NOT_EXIST : - case EXP_NOT_IN : - sqlAddMsg( state, "Expression type not yet supported" ); - state->error = 1; - break; case EXP_NULL : + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + buffer_add( state->sql, "NULL" ); break; case EXP_NUMBER : // Numeric literal @@ -516,6 +556,9 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } break; case EXP_OPERATOR : + if( expr->negate ) + buffer_add( state->sql, "NOT (" ); + if( expr->left_operand ) { buildExpression( state, expr->left_operand ); if( state->error ) { @@ -535,6 +578,10 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { break; } } + + if( expr->negate ) + buffer_add_char( state->sql, ')' ); + break; case EXP_STRING : // String literal if( !expr->literal ) { @@ -548,6 +595,9 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } break; case EXP_SUBQUERY : + if( expr->negate ) + buffer_add( state->sql, "NOT " ); + if( expr->subquery ) { buffer_add_char( state->sql, '(' ); incr_indent( state ); @@ -562,7 +612,7 @@ static void buildExpression( BuildSQLState* state, Expression* expr ) { } break; } - + if( expr->parenthesize ) buffer_add_char( state->sql, ')' ); } diff --git a/Open-ILS/src/c-apps/oils_storedq.c b/Open-ILS/src/c-apps/oils_storedq.c index 44fd37f890..8a6a516b2a 100644 --- a/Open-ILS/src/c-apps/oils_storedq.c +++ b/Open-ILS/src/c-apps/oils_storedq.c @@ -225,18 +225,34 @@ static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) { } } + Expression* having_clause = NULL; + if( having_clause_id != -1 ) { + having_clause = getExpression( state, having_clause_id ); + if( ! having_clause ) { + // shouldn't happen due to foreign key constraint + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + "Unable to fetch HAVING expression for query id = %d", id )); + expressionFree( where_clause ); + freeQSeqList( child_list ); + fromRelationFree( from_clause ); + selectListFree( select_list ); + return NULL; + } + } + // Get the ORDER BY clause, if there is one OrderItem* order_by_list = getOrderByList( state, id ); if( state->error ) { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to load ORDER BY clause for query %d", id )); + expressionFree( having_clause ); expressionFree( where_clause ); freeQSeqList( child_list ); fromRelationFree( from_clause ); selectListFree( select_list ); return NULL; } - + // Allocate a StoredQ: from the free list if possible, from the heap if necessary StoredQ* sq; @@ -257,6 +273,7 @@ static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) { sq->where_clause = where_clause; sq->select_list = select_list; sq->child_list = child_list; + sq->having_clause = having_clause; sq->order_by_list = order_by_list; return sq; @@ -407,6 +424,8 @@ void storedQFree( StoredQ* sq ) { orderItemListFree( sq->order_by_list ); sq->order_by_list = NULL; } + if( sq->having_clause ) + expressionFree( sq->having_clause ); // Stick the empty husk on the free list for potential reuse sq->next = free_storedq_list; @@ -851,8 +870,8 @@ static Expression* getExpression( BuildSQLState* state, int id ) { Expression* exp = NULL; 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 " + "SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, column_name, " + "left_operand, operator, right_operand, function_id, subquery, cast_type, negate " "FROM query.expression WHERE id = %d;", id ); if( result ) { if( dbi_result_first_row( result ) ) { @@ -911,12 +930,6 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result type = EXP_FUNCTION; else if( !strcmp( type_str, "xin" )) type = EXP_IN; - else if( !strcmp( type_str, "xnbet" )) - type = EXP_NOT_BETWEEN; - else if( !strcmp( type_str, "xnex" )) - type = EXP_NOT_EXIST; - else if( !strcmp( type_str, "xnin" )) - type = EXP_NOT_IN; else if( !strcmp( type_str, "xnull" )) type = EXP_NULL; else if( !strcmp( type_str, "xnum" )) @@ -975,6 +988,8 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result else cast_type_id = dbi_result_get_int_idx( result, 14 ); + int negate = oils_result_get_bool_idx( result, 15 ); + Expression* left_operand = NULL; Expression* right_operand = NULL; StoredQ* subquery = NULL; @@ -1104,6 +1119,7 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result exp->subquery_id = subquery_id; exp->subquery = subquery; exp->cast_type_id = subquery_id; + exp->negate = negate; return exp; } -- 2.43.2