From 837827e285df1bb0bf5b036396d09bac32fe0b55 Mon Sep 17 00:00:00 2001 From: scottmk Date: Tue, 1 Jun 2010 15:06:20 +0000 Subject: [PATCH 1/1] Add support for GROUP BY. 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@16541 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/include/openils/oils_buildq.h | 3 +- Open-ILS/src/c-apps/buildSQL.c | 38 +++++++++++++++++++++++++- Open-ILS/src/c-apps/oils_storedq.c | 33 ++++++++++++---------- 3 files changed, 56 insertions(+), 18 deletions(-) diff --git a/Open-ILS/include/openils/oils_buildq.h b/Open-ILS/include/openils/oils_buildq.h index c3f07f09ea..78ead41e28 100644 --- a/Open-ILS/include/openils/oils_buildq.h +++ b/Open-ILS/include/openils/oils_buildq.h @@ -182,10 +182,9 @@ struct Expression_ { int negate; // Boolean BindVar* bind; Expression* subexp_list; // Linked list of subexpressions - // The next two columns come, not from query.expression, + // The next column comes, not from query.expression, // but from query.function_sig: char* function_name; - int is_aggregate; // Boolean }; struct QSeq_ { diff --git a/Open-ILS/src/c-apps/buildSQL.c b/Open-ILS/src/c-apps/buildSQL.c index 7749cdabd0..596e0ab8f0 100644 --- a/Open-ILS/src/c-apps/buildSQL.c +++ b/Open-ILS/src/c-apps/buildSQL.c @@ -21,6 +21,7 @@ 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 buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ); static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ); static void buildExpression( BuildSQLState* state, const Expression* expr ); static void buildFunction( BuildSQLState* state, const Expression* exp ); @@ -293,7 +294,8 @@ static void buildSelect( BuildSQLState* state, const StoredQ* query ) { decr_indent( state ); } - // To do: build GROUP BY clause, if there is one + // Build GROUP BY clause, if there is one + buildGroupBy( state, query->select_list ); // Build HAVING clause, if there is one if( query->having_clause ) { @@ -403,6 +405,11 @@ static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) { decr_indent( state ); } +/** + @brief Add a JOIN clause. + @param state Pointer to the query-building context. + @param join Pointer to the FromRelation representing the JOIN to be added. +*/ static void buildJoin( BuildSQLState* state, const FromRelation* join ) { add_newline( state ); switch( join->join_type ) { @@ -526,6 +533,35 @@ static void buildSelectList( BuildSQLState* state, const SelectItem* item ) { buffer_add_char( state->sql, ' ' ); } +/** + @brief Add a GROUP BY clause, if there is one, to the current query. + @param state Pointer to the query-building context. + @param sel_list Pointer to the first node in a linked list of SelectItems + + We reference the GROUP BY items by number, not by repeating the expressions. +*/ +static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) { + int seq = 0; // Sequence number of current SelectItem + int first = 1; // Boolean: true for the first GROUPed BY item + while( sel_list ) { + ++seq; + + if( sel_list->grouped_by ) { + if( first ) { + add_newline( state ); + buffer_add( state->sql, "GROUP BY " ); + first = 0; + } + else + buffer_add( state->sql, ", " ); + + buffer_fadd( state->sql, "%d", seq ); + } + + sel_list = sel_list->next; + } +} + /** @brief Add an ORDER BY clause to the current query. @param state Pointer to the query-building context. diff --git a/Open-ILS/src/c-apps/oils_storedq.c b/Open-ILS/src/c-apps/oils_storedq.c index 914a1e13e5..a4bcc98a5e 100644 --- a/Open-ILS/src/c-apps/oils_storedq.c +++ b/Open-ILS/src/c-apps/oils_storedq.c @@ -118,7 +118,7 @@ StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) { } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); - osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, + osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.stored_query table: #%d %s", errnum, msg ? msg : "No description available" )); state->error = 1; @@ -302,7 +302,7 @@ static StoredQ* constructStoredQ( BuildSQLState* state, dbi_result result ) { */ static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) { QSeq* child_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, @@ -687,7 +687,7 @@ static FromRelation* constructFromRelation( BuildSQLState* state, dbi_result res */ static FromRelation* getJoinList( BuildSQLState* state, int id ) { FromRelation* join_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, @@ -771,6 +771,11 @@ static void fromRelationFree( FromRelation* fr ) { } } +/** + @brief Build a SELECT list for a given query ID. + @param state Pointer to the query-building context. + @param query_id ID of the query to which the SELECT list belongs. +*/ static SelectItem* getSelectList( BuildSQLState* state, int query_id ) { SelectItem* select_list = NULL; @@ -834,7 +839,7 @@ static SelectItem* constructSelectItem( BuildSQLState* state, dbi_result result int expression_id = dbi_result_get_int_idx( result, 4 ); const char* column_alias = dbi_result_get_string_idx( result, 5 ); int grouped_by = oils_result_get_bool_idx( result, 6 ); - + // Construct an Expression Expression* expression = getExpression( state, expression_id ); if( !expression ) { @@ -919,7 +924,7 @@ static BindVar* getBindVar( BuildSQLState* state, const char* name ) { bind = constructBindVar( state, result ); if( bind ) { PRINT( "Got a bind variable for %s\n", name ); - } else + } else osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to load bind variable \"%s\"", name )); } else { @@ -1047,7 +1052,7 @@ static void bindVarFree( char* key, void* p ) { @return Pointer to a newly-created Expression if successful, or NULL if not. */ static Expression* getExpression( BuildSQLState* state, int id ) { - + // Check the stack to see if the current expression is nested inside itself. If it is, // then abort in order to avoid infinite recursion. If it isn't, then add it to the // stack. (Make sure to pop it off the stack before returning.) @@ -1064,7 +1069,7 @@ static Expression* getExpression( BuildSQLState* state, int id ) { "SELECT exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, " "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, " "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, " - "func.function_name, COALESCE(func.is_aggregate, false) " + "func.function_name " "FROM query.expression AS exp LEFT JOIN query.function_sig AS func " "ON (exp.function_id = func.id) " "WHERE exp.id = %d;", id ); @@ -1077,7 +1082,7 @@ static Expression* getExpression( BuildSQLState* state, int id ) { PRINT( "\ttype = %d\n", exp->type ); PRINT( "\tparenthesize = %d\n", exp->parenthesize ); PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" ); - } else + } else osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to construct an Expression for id = %d", id )); } @@ -1106,7 +1111,7 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result int id = dbi_result_get_int_idx( result, 1 ); const char* type_str = dbi_result_get_string_idx( result, 2 ); - + ExprType type; if( !strcmp( type_str, "xbet" )) type = EXP_BETWEEN; @@ -1152,7 +1157,7 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result parent_expr_id = -1; else parent_expr_id = dbi_result_get_int_idx( result, 4 ); - + int seq_no = dbi_result_get_int_idx( result, 5 ); const char* literal = dbi_result_get_string_idx( result, 6 ); const char* table_alias = dbi_result_get_string_idx( result, 7 ); @@ -1187,7 +1192,6 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result int negate = oils_result_get_bool_idx( result, 14 ); const char* bind_variable = dbi_result_get_string_idx( result, 15 ); const char* function_name = dbi_result_get_string_idx( result, 16 ); - int is_aggregate = oils_result_get_bool_idx( result, 17 ); Expression* left_operand = NULL; Expression* right_operand = NULL; @@ -1472,7 +1476,6 @@ static Expression* constructExpression( BuildSQLState* state, dbi_result result exp->bind = bind; exp->subexp_list = subexp_list; exp->function_name = function_name ? strdup( function_name ) : NULL; - exp->is_aggregate = is_aggregate; return exp; } @@ -1548,14 +1551,14 @@ static void expressionFree( Expression* exp ) { */ 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 exp.id, exp.type, exp.parenthesize, exp.parent_expr, exp.seq_no, " "exp.literal, exp.table_alias, exp.column_name, exp.left_operand, exp.operator, " "exp.right_operand, exp.subquery, exp.cast_type, exp.negate, exp.bind_variable, " - "func.function_name, COALESCE(func.is_aggregate, false) " + "func.function_name " "FROM query.expression AS exp LEFT JOIN query.function_sig AS func " "ON (exp.function_id = func.id) " "WHERE exp.parent_expr = %d " @@ -1796,7 +1799,7 @@ static void push_id( IdNode** stack, int id, const char* alias ) { node->alias = strdup( alias ); else node->alias = NULL; - + // Reseat the stack *stack = node; } -- 2.43.2