3 @brief Translate an abstract representation of a query into an SQL statement.
10 #include "opensrf/utils.h"
11 #include "opensrf/string_array.h"
12 #include "opensrf/osrf_hash.h"
13 #include "opensrf/osrf_application.h"
14 #include "openils/oils_idl.h"
15 #include "openils/oils_sql.h"
16 #include "openils/oils_buildq.h"
18 static void build_Query( BuildSQLState* state, StoredQ* query );
19 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str );
20 static void buildSelect( BuildSQLState* state, StoredQ* query );
21 static void buildFrom( BuildSQLState* state, FromRelation* core_from );
22 static void buildJoin( BuildSQLState* state, FromRelation* join );
23 static void buildSelectList( BuildSQLState* state, SelectItem* item );
24 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list );
25 static void buildExpression( BuildSQLState* state, Expression* expr );
26 static void buildBindVar( BuildSQLState* state, BindVar* bind );
27 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
29 static void add_newline( BuildSQLState* state );
30 static inline void incr_indent( BuildSQLState* state );
31 static inline void decr_indent( BuildSQLState* state );
34 @brief Create a jsonObject representing the current list of bind variables.
35 @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
36 @return Pointer to the newly created jsonObject.
38 The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
39 variables. The data for each is another level of JSON_HASH with a fixed set of tags:
43 - "default_value" (as a jsonObject)
44 - "actual_value" (as a jsonObject)
46 Any non-existent values are represented as JSON_NULLs.
48 The calling code is responsible for freeing the returned jsonOjbect by calling
51 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
52 jsonObject* list = jsonNewObjectType( JSON_HASH );
54 if( bindvar_list && osrfHashGetCount( bindvar_list )) {
55 // Traverse our internal list of bind variables
57 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
58 while(( bind = osrfHashIteratorNext( iter ))) {
59 // Create an hash to represent the bind variable
60 jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
62 // Add an entry for each attribute
63 jsonObject* attr = jsonNewObject( bind->label );
64 jsonObjectSetKey( bind_obj, "label", attr );
66 const char* type = NULL;
67 switch( bind->type ) {
84 attr = jsonNewObject( type );
85 jsonObjectSetKey( bind_obj, "type", attr );
87 attr = jsonNewObject( bind->description );
88 jsonObjectSetKey( bind_obj, "description", attr );
90 attr = jsonObjectClone( bind->default_value );
91 jsonObjectSetKey( bind_obj, "default_value", attr );
93 attr = jsonObjectClone( bind->actual_value );
94 jsonObjectSetKey( bind_obj, "actual_value", attr );
96 // Add the bind variable to the list
97 jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
99 osrfHashIteratorFree( iter );
106 @brief Apply values to bind variables, overriding the defaults, if any.
107 @param state Pointer to the query-building context.
108 @param bindings A JSON_HASH of values.
109 @return 0 if successful, or 1 if not.
111 The @a bindings parameter must be a JSON_HASH. The keys are the names of bind variables.
112 The values are the corresponding values for the variables.
114 int oilsApplyBindValues( BuildSQLState* state, jsonObject* bindings ) {
116 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
118 } else if( !bindings ) {
119 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
120 "Internal error: No pointer to bindings" ));
122 } else if( bindings->type != JSON_HASH ) {
123 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
124 "Internal error: bindings parameter is not a JSON_HASH" ));
129 jsonObject* value = NULL;
130 jsonIterator* iter = jsonNewIterator( bindings );
131 while(( value = jsonIteratorNext( iter ))) {
132 const char* var_name = iter->key;
133 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
135 // Apply or replace the value for the specified variable
136 if( bind->actual_value )
137 jsonObjectFree( bind->actual_value );
138 bind->actual_value = jsonObjectClone( value );
140 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
141 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
145 jsonIteratorFree( iter );
151 @brief Build an SQL query.
152 @param state Pointer to the query-building context.
153 @param query Pointer to the query to be built.
154 @return Zero if successful, or 1 if not.
156 Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
158 int buildSQL( BuildSQLState* state, StoredQ* query ) {
160 buffer_reset( state->sql );
162 build_Query( state, query );
163 if( ! state->error ) {
164 // Remove the trailing space, if there is one, and add a semicolon.
165 char c = buffer_chomp( state->sql );
167 buffer_add_char( state->sql, c ); // oops, not a space; put it back
168 buffer_add( state->sql, ";\n" );
174 @brief Build an SQL query, appending it to what has been built so far.
175 @param state Pointer to the query-building context.
176 @param query Pointer to the query to be built.
178 Look at the query type and branch to the corresponding routine.
180 static void build_Query( BuildSQLState* state, StoredQ* query ) {
181 if( buffer_length( state->sql ))
182 add_newline( state );
184 switch( query->type ) {
186 buildSelect( state, query );
189 buildCombo( state, query, "UNION" );
192 buildCombo( state, query, "INTERSECT" );
195 buildCombo( state, query, "EXCEPT" );
198 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
199 "Internal error: invalid query type %d in query # %d",
200 query->type, query->id ));
207 @brief Build a UNION, INTERSECT, or EXCEPT query.
208 @param state Pointer to the query-building context.
209 @param query Pointer to the query to be built.
210 @param type_str The query type, as a string.
212 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
214 QSeq* seq = query->child_list;
216 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
217 "Internal error: No child queries within %s query # %d",
218 type_str, query->id ));
223 // Traverse the list of child queries
225 build_Query( state, seq->child_query );
227 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
228 seq->child_query->id, type_str, query->id );
233 add_newline( state );
234 buffer_add( state->sql, type_str );
235 buffer_add_char( state->sql, ' ' );
237 buffer_add( state->sql, "ALL " );
245 @brief Build a SELECT statement.
246 @param state Pointer to the query-building context.
247 @param query Pointer to the StoredQ structure that represents the query.
249 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
251 FromRelation* from_clause = query->from_clause;
253 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
258 // To do: get SELECT list; just a stub here
259 buffer_add( state->sql, "SELECT" );
260 incr_indent( state );
261 buildSelectList( state, query->select_list );
263 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
267 decr_indent( state );
269 // Build FROM clause, if there is one
270 if( query->from_clause ) {
271 buildFrom( state, query->from_clause );
273 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
279 // Build WHERE clause, if there is one
280 if( query->where_clause ) {
281 add_newline( state );
282 buffer_add( state->sql, "WHERE" );
283 incr_indent( state );
284 add_newline( state );
285 buildExpression( state, query->where_clause );
287 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
291 decr_indent( state );
294 // To do: build GROUP BY clause, if there is one
296 // Build HAVING clause, if there is one
297 if( query->having_clause ) {
298 add_newline( state );
299 buffer_add( state->sql, "HAVING" );
300 incr_indent( state );
301 add_newline( state );
302 buildExpression( state, query->having_clause );
304 sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
308 decr_indent( state );
311 // Build ORDER BY clause, if there is one
312 if( query->order_by_list ) {
313 buildOrderBy( state, query->order_by_list );
315 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
321 // To do: Build LIMIT clause, if there is one
323 // To do: Build OFFSET clause, if there is one
329 @brief Build a FROM clause.
330 @param Pointer to the query-building context.
331 @param Pointer to the StoredQ query to which the FROM clause belongs.
333 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
335 add_newline( state );
336 buffer_add( state->sql, "FROM" );
337 incr_indent( state );
338 add_newline( state );
340 switch( core_from->type ) {
341 case FRT_RELATION : {
342 char* relation = core_from->table_name;
344 if( !core_from->class_name ) {
345 sqlAddMsg( state, "No relation specified for core relation # %d",
351 // Look up table name, view name, or source_definition in the IDL
352 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
353 relation = oilsGetRelation( class_hash );
357 buffer_add( state->sql, relation );
358 if( !core_from->table_name )
359 free( relation ); // In this case we strdup'd it, must free it
363 buffer_add_char( state->sql, '(' );
364 incr_indent( state );
365 build_Query( state, core_from->subquery );
366 decr_indent( state );
367 add_newline( state );
368 buffer_add_char( state->sql, ')' );
371 sqlAddMsg( state, "Functions in FROM clause not yet supported" );
376 // Add a table alias, if possible
377 if( core_from->table_alias ) {
378 buffer_add( state->sql, " AS \"" );
379 buffer_add( state->sql, core_from->table_alias );
380 buffer_add( state->sql, "\" " );
382 else if( core_from->class_name ) {
383 buffer_add( state->sql, " AS \"" );
384 buffer_add( state->sql, core_from->class_name );
385 buffer_add( state->sql, "\" " );
387 buffer_add_char( state->sql, ' ' );
389 incr_indent( state );
390 FromRelation* join = core_from->join_list;
392 buildJoin( state, join );
394 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
400 decr_indent( state );
401 decr_indent( state );
404 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
405 add_newline( state );
406 switch( join->join_type ) {
408 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
412 buffer_add( state->sql, "INNER JOIN " );
415 buffer_add( state->sql, "LEFT JOIN " );
418 buffer_add( state->sql, "RIGHT JOIN " );
421 buffer_add( state->sql, "FULL JOIN " );
424 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
429 switch( join->type ) {
432 if( !join->table_name || ! *join->table_name ) {
433 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
437 buffer_add( state->sql, join->table_name );
441 if( !join->subquery ) {
442 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
445 } else if( !join->table_alias ) {
446 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
451 buffer_add_char( state->sql, '(' );
452 incr_indent( state );
453 build_Query( state, join->subquery );
454 decr_indent( state );
455 add_newline( state );
456 buffer_add_char( state->sql, ')' );
459 if( !join->table_name || ! *join->table_name ) {
460 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
468 const char* effective_alias = join->table_alias;
469 if( !effective_alias )
470 effective_alias = join->class_name;
472 if( effective_alias ) {
473 buffer_add( state->sql, " AS \"" );
474 buffer_add( state->sql, effective_alias );
475 buffer_add_char( state->sql, '\"' );
478 if( join->on_clause ) {
479 incr_indent( state );
480 add_newline( state );
481 buffer_add( state->sql, "ON " );
482 buildExpression( state, join->on_clause );
483 decr_indent( state );
486 FromRelation* subjoin = join->join_list;
488 buildJoin( state, subjoin );
490 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
493 subjoin = subjoin->next;
497 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
502 buffer_add_char( state->sql, ',' );
503 add_newline( state );
504 buildExpression( state, item->expression );
506 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
511 if( item->column_alias ) {
512 buffer_add( state->sql, " AS \"" );
513 buffer_add( state->sql, item->column_alias );
514 buffer_add_char( state->sql, '\"' );
519 buffer_add_char( state->sql, ' ' );
523 @brief Add an ORDER BY clause to the current query.
524 @param state Pointer to the query-building context.
525 @param ord_list Pointer to the first node in a linked list of OrderItems.
527 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
528 add_newline( state );
529 buffer_add( state->sql, "ORDER BY" );
530 incr_indent( state );
532 int first = 1; // boolean
537 buffer_add_char( state->sql, ',' );
538 add_newline( state );
539 buildExpression( state, ord_list->expression );
541 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
545 ord_list = ord_list->next;
548 decr_indent( state );
553 @brief Build an arbitrary expression.
554 @param state Pointer to the query-building context.
555 @param expr Pointer to the Expression representing the expression to be built.
557 static void buildExpression( BuildSQLState* state, Expression* expr ) {
559 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
560 "Internal error: NULL pointer to Expression" ));
565 if( expr->parenthesize )
566 buffer_add_char( state->sql, '(' );
568 switch( expr->type ) {
571 buffer_add( state->sql, "NOT " );
573 sqlAddMsg( state, "BETWEEN expressions not yet supported" );
577 if( !expr->bind ) { // Sanity check
578 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
579 "Internal error: no variable for bind variable expression" ));
582 buildBindVar( state, expr->bind );
586 buffer_add( state->sql, "NOT " );
588 if( expr->literal ) {
589 buffer_add( state->sql, expr->literal );
590 buffer_add_char( state->sql, ' ' );
592 buffer_add( state->sql, "FALSE " );
596 buffer_add( state->sql, "NOT " );
598 sqlAddMsg( state, "CASE expressions not yet supported" );
601 case EXP_CAST : // Type cast
603 buffer_add( state->sql, "NOT " );
605 sqlAddMsg( state, "Cast expressions not yet supported" );
608 case EXP_COLUMN : // Table column
610 buffer_add( state->sql, "NOT " );
612 if( expr->table_alias ) {
613 buffer_add_char( state->sql, '\"' );
614 buffer_add( state->sql, expr->table_alias );
615 buffer_add( state->sql, "\"." );
617 if( expr->column_name ) {
618 buffer_add( state->sql, expr->column_name );
620 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
621 "Column name not present in expression # %d", expr->id ));
626 if( !expr->subquery ) {
627 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
628 "No subquery found for EXIST expression # %d", expr->id ));
632 buffer_add( state->sql, "NOT " );
634 buffer_add( state->sql, "EXISTS (" );
635 incr_indent( state );
636 build_Query( state, expr->subquery );
637 decr_indent( state );
638 add_newline( state );
639 buffer_add_char( state->sql, ')' );
644 buffer_add( state->sql, "NOT " );
646 sqlAddMsg( state, "Field expressions not yet supported" );
651 buffer_add( state->sql, "NOT " );
653 sqlAddMsg( state, "Function expressions not yet supported" );
657 if( expr->left_operand ) {
658 buildExpression( state, expr->left_operand );
659 if( !state->error ) {
661 buffer_add( state->sql, "NOT " );
663 if( expr->subquery ) {
664 buffer_add( state->sql, " IN (" );
665 incr_indent( state );
666 build_Query( state, expr->subquery );
667 decr_indent( state );
668 add_newline( state );
669 buffer_add_char( state->sql, ')' );
671 sqlAddMsg( state, "IN lists not yet supported" );
678 if( expr->left_operand ) {
679 buildExpression( state, expr->left_operand );
681 sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
688 buffer_add( state->sql, " IS NOT NULL" );
690 buffer_add( state->sql, " IS NULL" );
694 buffer_add( state->sql, "NOT " );
696 buffer_add( state->sql, "NULL" );
698 case EXP_NUMBER : // Numeric literal
699 if( !expr->literal ) {
700 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
701 "Internal error: No numeric value in string expression # %d", expr->id ));
704 buffer_add( state->sql, expr->literal );
709 buffer_add( state->sql, "NOT (" );
711 if( expr->left_operand ) {
712 buildExpression( state, expr->left_operand );
714 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
719 buffer_add_char( state->sql, ' ' );
720 buffer_add( state->sql, expr->op );
721 buffer_add_char( state->sql, ' ' );
722 if( expr->right_operand ) {
723 buildExpression( state, expr->right_operand );
725 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
732 buffer_add_char( state->sql, ')' );
735 case EXP_STRING : // String literal
736 if( !expr->literal ) {
737 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
738 "Internal error: No string value in string expression # %d", expr->id ));
741 // To do: escape special characters in the string
742 buffer_add_char( state->sql, '\'' );
743 buffer_add( state->sql, expr->literal );
744 buffer_add_char( state->sql, '\'' );
749 buffer_add( state->sql, "NOT " );
751 if( expr->subquery ) {
752 buffer_add_char( state->sql, '(' );
753 incr_indent( state );
754 build_Query( state, expr->subquery );
755 decr_indent( state );
756 add_newline( state );
757 buffer_add_char( state->sql, ')' );
759 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
760 "Internal error: No subquery in subquery expression # %d", expr->id ));
766 if( expr->parenthesize )
767 buffer_add_char( state->sql, ')' );
771 @brief Add the value of a bind variable to an SQL statement.
772 @param state Pointer to the query-building context.
773 @param bind Pointer to the bind variable whose value is to be added to the SQL.
775 The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
776 the type of the bind variable.
778 static void buildBindVar( BuildSQLState* state, BindVar* bind ) {
780 // Decide where to get the value, if any
781 const jsonObject* value = NULL;
782 if( bind->actual_value )
783 value = bind->actual_value;
784 else if( bind->default_value ) {
785 if( state->defaults_usable )
786 value = bind->default_value;
788 sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
793 } else if( state->values_required ) {
794 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
798 // No value available, and that's okay. Emit the name of the bind variable.
799 buffer_add_char( state->sql, ':' );
800 buffer_add( state->sql, bind->name );
804 // If we get to this point, we know that a value is available. Carry on.
806 int numeric = 0; // Boolean
807 if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
811 switch( bind->type ) {
814 buildScalar( state, numeric, value );
818 if( JSON_ARRAY == value->type ) {
819 // Iterate over array, emit each value
820 int first = 1; // Boolean
821 unsigned long max = value->size;
827 buffer_add( state->sql, ", " );
829 buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
833 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
834 "Invalid value for bind variable; expected a list of values" ));
839 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
840 "Internal error: invalid type for bind variable" ));
846 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
847 "Unable to emit value of bind variable \"%s\"", bind->name ));
851 @brief Add a number or quoted string to an SQL statement.
852 @param state Pointer to the query-building context.
853 @param numeric Boolean; true if the value is expected to be a number
854 @param obj Pointer to the jsonObject whose value is to be added to the SQL.
856 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
857 switch( obj->type ) {
859 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
860 "Internal error: hash value for bind variable" ));
864 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
865 "Internal error: array value for bind variable" ));
871 "Invalid value for bind variable: expected a string, found a number" );
874 // To do: escape special characters in the string
875 buffer_add_char( state->sql, '\'' );
876 buffer_add( state->sql, jsonObjectGetString( obj ));
877 buffer_add_char( state->sql, '\'' );
882 buffer_add( state->sql, jsonObjectGetString( obj ));
885 "Invalid value for bind variable: expected a number, found a string" );
890 buffer_add( state->sql, "NULL" );
893 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
894 "Internal error: boolean value for bind variable" ));
898 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
899 "Internal error: corrupted value for bind variable" ));
905 static void add_newline( BuildSQLState* state ) {
906 buffer_add_char( state->sql, '\n' );
909 static const char blanks[] = " "; // 32 blanks
910 static const size_t maxlen = sizeof( blanks ) - 1;
911 const int blanks_per_level = 3;
912 int n = state->indent * blanks_per_level;
914 size_t len = n >= maxlen ? maxlen : n;
915 buffer_add_n( state->sql, blanks, len );
920 static inline void incr_indent( BuildSQLState* state ) {
924 static inline void decr_indent( BuildSQLState* state ) {