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, const StoredQ* query );
19 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str );
20 static void buildSelect( BuildSQLState* state, const StoredQ* query );
21 static void buildFrom( BuildSQLState* state, const FromRelation* core_from );
22 static void buildJoin( BuildSQLState* state, const FromRelation* join );
23 static void buildSelectList( BuildSQLState* state, const SelectItem* item );
24 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list );
25 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list );
26 static void buildCase( BuildSQLState* state, const Expression* expr );
27 static void buildExpression( BuildSQLState* state, const Expression* expr );
28 static void buildFunction( BuildSQLState* state, const Expression* exp );
29 static int subexp_count( const Expression* expr );
30 static void buildTypicalFunction( BuildSQLState* state, const Expression* expr );
31 static void buildExtract( BuildSQLState* state, const Expression* expr );
32 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op );
33 static void buildBindVar( BuildSQLState* state, const BindVar* bind );
34 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
36 static void add_newline( BuildSQLState* state );
37 static inline void incr_indent( BuildSQLState* state );
38 static inline void decr_indent( BuildSQLState* state );
41 @brief Create a jsonObject representing the current list of bind variables.
42 @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
43 @return Pointer to the newly created jsonObject.
45 The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
46 variables. The data for each is another level of JSON_HASH with a fixed set of tags:
50 - "default_value" (as a jsonObject)
51 - "actual_value" (as a jsonObject)
53 Any non-existent values are represented as JSON_NULLs.
55 The calling code is responsible for freeing the returned jsonOjbect by calling
58 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
59 jsonObject* list = jsonNewObjectType( JSON_HASH );
61 if( bindvar_list && osrfHashGetCount( bindvar_list )) {
62 // Traverse our internal list of bind variables
64 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
65 while(( bind = osrfHashIteratorNext( iter ))) {
66 // Create an hash to represent the bind variable
67 jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
69 // Add an entry for each attribute
70 jsonObject* attr = jsonNewObject( bind->label );
71 jsonObjectSetKey( bind_obj, "label", attr );
73 const char* type = NULL;
74 switch( bind->type ) {
91 attr = jsonNewObject( type );
92 jsonObjectSetKey( bind_obj, "type", attr );
94 attr = jsonNewObject( bind->description );
95 jsonObjectSetKey( bind_obj, "description", attr );
97 attr = jsonObjectClone( bind->default_value );
98 jsonObjectSetKey( bind_obj, "default_value", attr );
100 attr = jsonObjectClone( bind->actual_value );
101 jsonObjectSetKey( bind_obj, "actual_value", attr );
103 // Add the bind variable to the list
104 jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
106 osrfHashIteratorFree( iter );
113 @brief Apply values to bind variables, overriding the defaults, if any.
114 @param state Pointer to the query-building context.
115 @param bindings A JSON_HASH of values.
116 @return 0 if successful, or 1 if not.
118 The @a bindings parameter must be a JSON_HASH. The keys are the names of bind variables.
119 The values are the corresponding values for the variables.
121 int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) {
123 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
125 } else if( !bindings ) {
126 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
127 "Internal error: No pointer to bindings" ));
129 } else if( bindings->type != JSON_HASH ) {
130 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
131 "Internal error: bindings parameter is not a JSON_HASH" ));
136 jsonObject* value = NULL;
137 jsonIterator* iter = jsonNewIterator( bindings );
138 while(( value = jsonIteratorNext( iter ))) {
139 const char* var_name = iter->key;
140 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
142 // Apply or replace the value for the specified variable
143 if( bind->actual_value )
144 jsonObjectFree( bind->actual_value );
145 bind->actual_value = jsonObjectClone( value );
147 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
148 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
152 jsonIteratorFree( iter );
158 @brief Build an SQL query.
159 @param state Pointer to the query-building context.
160 @param query Pointer to the query to be built.
161 @return Zero if successful, or 1 if not.
163 Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
165 int buildSQL( BuildSQLState* state, const StoredQ* query ) {
167 buffer_reset( state->sql );
169 build_Query( state, query );
170 if( ! state->error ) {
171 // Remove the trailing space, if there is one, and add a semicolon.
172 char c = buffer_chomp( state->sql );
174 buffer_add_char( state->sql, c ); // oops, not a space; put it back
175 buffer_add( state->sql, ";\n" );
181 @brief Build an SQL query, appending it to what has been built so far.
182 @param state Pointer to the query-building context.
183 @param query Pointer to the query to be built.
185 Look at the query type and branch to the corresponding routine.
187 static void build_Query( BuildSQLState* state, const StoredQ* query ) {
188 if( buffer_length( state->sql ))
189 add_newline( state );
191 switch( query->type ) {
193 buildSelect( state, query );
196 buildCombo( state, query, "UNION" );
199 buildCombo( state, query, "INTERSECT" );
202 buildCombo( state, query, "EXCEPT" );
205 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
206 "Internal error: invalid query type %d in query # %d",
207 query->type, query->id ));
214 @brief Build a UNION, INTERSECT, or EXCEPT query.
215 @param state Pointer to the query-building context.
216 @param query Pointer to the query to be built.
217 @param type_str The query type, as a string.
219 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) {
221 QSeq* seq = query->child_list;
223 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
224 "Internal error: No child queries within %s query # %d",
225 type_str, query->id ));
230 // Traverse the list of child queries
232 build_Query( state, seq->child_query );
234 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
235 seq->child_query->id, type_str, query->id );
240 add_newline( state );
241 buffer_add( state->sql, type_str );
242 buffer_add_char( state->sql, ' ' );
244 buffer_add( state->sql, "ALL " );
252 @brief Build a SELECT statement.
253 @param state Pointer to the query-building context.
254 @param query Pointer to the StoredQ structure that represents the query.
256 static void buildSelect( BuildSQLState* state, const StoredQ* query ) {
258 FromRelation* from_clause = query->from_clause;
260 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
265 // To do: get SELECT list; just a stub here
266 buffer_add( state->sql, "SELECT" );
267 incr_indent( state );
268 buildSelectList( state, query->select_list );
270 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
274 decr_indent( state );
276 // Build FROM clause, if there is one
277 if( query->from_clause ) {
278 buildFrom( state, query->from_clause );
280 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
286 // Build WHERE clause, if there is one
287 if( query->where_clause ) {
288 add_newline( state );
289 buffer_add( state->sql, "WHERE" );
290 incr_indent( state );
291 add_newline( state );
292 buildExpression( state, query->where_clause );
294 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
298 decr_indent( state );
301 // Build GROUP BY clause, if there is one
302 buildGroupBy( state, query->select_list );
304 // Build HAVING clause, if there is one
305 if( query->having_clause ) {
306 add_newline( state );
307 buffer_add( state->sql, "HAVING" );
308 incr_indent( state );
309 add_newline( state );
310 buildExpression( state, query->having_clause );
312 sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
316 decr_indent( state );
319 // Build ORDER BY clause, if there is one
320 if( query->order_by_list ) {
321 buildOrderBy( state, query->order_by_list );
323 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
329 // To do: Build LIMIT clause, if there is one
331 // To do: Build OFFSET clause, if there is one
337 @brief Build a FROM clause.
338 @param Pointer to the query-building context.
339 @param Pointer to the StoredQ query to which the FROM clause belongs.
341 static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) {
343 add_newline( state );
344 buffer_add( state->sql, "FROM" );
345 incr_indent( state );
346 add_newline( state );
348 switch( core_from->type ) {
349 case FRT_RELATION : {
350 char* relation = core_from->table_name;
352 if( !core_from->class_name ) {
353 sqlAddMsg( state, "No relation specified for core relation # %d",
359 // Look up table name, view name, or source_definition in the IDL
360 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
361 relation = oilsGetRelation( class_hash );
365 buffer_add( state->sql, relation );
366 if( !core_from->table_name )
367 free( relation ); // In this case we strdup'd it, must free it
371 buffer_add_char( state->sql, '(' );
372 incr_indent( state );
373 build_Query( state, core_from->subquery );
374 decr_indent( state );
375 add_newline( state );
376 buffer_add_char( state->sql, ')' );
379 buildFunction( state, core_from->function_call );
380 if ( state->error ) {
382 "Unable to include function call # %d in FROM relation # %d",
383 core_from->function_call->id, core_from->id );
388 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
389 "Internal error: Invalid type # %d in FROM relation # %d",
390 core_from->type, core_from->id ));
395 // Add a table alias, if possible
396 if( core_from->table_alias ) {
397 buffer_add( state->sql, " AS \"" );
398 buffer_add( state->sql, core_from->table_alias );
399 buffer_add( state->sql, "\" " );
401 else if( core_from->class_name ) {
402 buffer_add( state->sql, " AS \"" );
403 buffer_add( state->sql, core_from->class_name );
404 buffer_add( state->sql, "\" " );
406 buffer_add_char( state->sql, ' ' );
408 incr_indent( state );
409 FromRelation* join = core_from->join_list;
411 buildJoin( state, join );
413 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
419 decr_indent( state );
420 decr_indent( state );
424 @brief Add a JOIN clause.
425 @param state Pointer to the query-building context.
426 @param join Pointer to the FromRelation representing the JOIN to be added.
428 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
429 add_newline( state );
430 switch( join->join_type ) {
432 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
436 buffer_add( state->sql, "INNER JOIN " );
439 buffer_add( state->sql, "LEFT JOIN " );
442 buffer_add( state->sql, "RIGHT JOIN " );
445 buffer_add( state->sql, "FULL JOIN " );
448 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
453 switch( join->type ) {
456 if( !join->table_name || ! *join->table_name ) {
457 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
461 buffer_add( state->sql, join->table_name );
465 if( !join->subquery ) {
466 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
469 } else if( !join->table_alias ) {
470 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
475 buffer_add_char( state->sql, '(' );
476 incr_indent( state );
477 build_Query( state, join->subquery );
478 decr_indent( state );
479 add_newline( state );
480 buffer_add_char( state->sql, ')' );
483 if( !join->table_name || ! *join->table_name ) {
484 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
492 const char* effective_alias = join->table_alias;
493 if( !effective_alias )
494 effective_alias = join->class_name;
496 if( effective_alias ) {
497 buffer_add( state->sql, " AS \"" );
498 buffer_add( state->sql, effective_alias );
499 buffer_add_char( state->sql, '\"' );
502 if( join->on_clause ) {
503 incr_indent( state );
504 add_newline( state );
505 buffer_add( state->sql, "ON " );
506 buildExpression( state, join->on_clause );
507 decr_indent( state );
510 FromRelation* subjoin = join->join_list;
512 buildJoin( state, subjoin );
514 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
517 subjoin = subjoin->next;
522 @brief Build a SELECT list.
523 @param state Pointer to the query-building context.
524 @param item Pointer to the first in a linked list of SELECT items.
526 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
531 buffer_add_char( state->sql, ',' );
532 add_newline( state );
533 buildExpression( state, item->expression );
535 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
540 if( item->column_alias ) {
541 buffer_add( state->sql, " AS \"" );
542 buffer_add( state->sql, item->column_alias );
543 buffer_add_char( state->sql, '\"' );
548 buffer_add_char( state->sql, ' ' );
552 @brief Add a GROUP BY clause, if there is one, to the current query.
553 @param state Pointer to the query-building context.
554 @param sel_list Pointer to the first node in a linked list of SelectItems
556 We reference the GROUP BY items by number, not by repeating the expressions.
558 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
559 int seq = 0; // Sequence number of current SelectItem
560 int first = 1; // Boolean: true for the first GROUPed BY item
564 if( sel_list->grouped_by ) {
566 add_newline( state );
567 buffer_add( state->sql, "GROUP BY " );
571 buffer_add( state->sql, ", " );
573 buffer_fadd( state->sql, "%d", seq );
576 sel_list = sel_list->next;
581 @brief Add an ORDER BY clause to the current query.
582 @param state Pointer to the query-building context.
583 @param ord_list Pointer to the first node in a linked list of OrderItems.
585 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
586 add_newline( state );
587 buffer_add( state->sql, "ORDER BY" );
588 incr_indent( state );
590 int first = 1; // boolean
595 buffer_add_char( state->sql, ',' );
596 add_newline( state );
597 buildExpression( state, ord_list->expression );
599 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
603 ord_list = ord_list->next;
606 decr_indent( state );
611 @brief Build an arbitrary expression.
612 @param state Pointer to the query-building context.
613 @param expr Pointer to the Expression representing the expression to be built.
615 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
617 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
618 "Internal error: NULL pointer to Expression" ));
623 if( expr->parenthesize )
624 buffer_add_char( state->sql, '(' );
626 switch( expr->type ) {
629 buffer_add( state->sql, "NOT " );
631 buildExpression( state, expr->left_operand );
633 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
638 buffer_add( state->sql, " BETWEEN " );
640 buildExpression( state, expr->subexp_list );
642 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
647 buffer_add( state->sql, " AND " );
649 buildExpression( state, expr->subexp_list->next );
651 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
658 if( !expr->bind ) { // Sanity check
659 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
660 "Internal error: no variable for bind variable expression" ));
663 buildBindVar( state, expr->bind );
667 buffer_add( state->sql, "NOT " );
669 if( expr->literal ) {
670 buffer_add( state->sql, expr->literal );
671 buffer_add_char( state->sql, ' ' );
673 buffer_add( state->sql, "FALSE " );
676 buildCase( state, expr );
678 sqlAddMsg( state, "Unable to build CASE expression # %d", expr->id );
681 case EXP_CAST : // Type cast
683 buffer_add( state->sql, "NOT " );
685 buffer_add( state->sql, "CAST (" );
686 buildExpression( state, expr->left_operand );
688 sqlAddMsg( state, "Unable to build left operand for CAST expression # %d",
691 buffer_add( state->sql, " AS " );
692 if( expr->cast_type && expr->cast_type->datatype_name ) {
693 buffer_add( state->sql, expr->cast_type->datatype_name );
694 buffer_add_char( state->sql, ')' );
696 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
697 "No datatype available for CAST expression # %d", expr->id ));
702 case EXP_COLUMN : // Table column
704 buffer_add( state->sql, "NOT " );
706 if( expr->table_alias ) {
707 buffer_add_char( state->sql, '\"' );
708 buffer_add( state->sql, expr->table_alias );
709 buffer_add( state->sql, "\"." );
711 if( expr->column_name ) {
712 buffer_add( state->sql, expr->column_name );
714 buffer_add_char( state->sql, '*' );
718 if( !expr->subquery ) {
719 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
720 "No subquery found for EXIST expression # %d", expr->id ));
724 buffer_add( state->sql, "NOT " );
726 buffer_add( state->sql, "EXISTS (" );
727 incr_indent( state );
728 build_Query( state, expr->subquery );
729 decr_indent( state );
730 add_newline( state );
731 buffer_add_char( state->sql, ')' );
735 buildFunction( state, expr );
738 if( expr->left_operand ) {
739 buildExpression( state, expr->left_operand );
740 if( !state->error ) {
742 buffer_add( state->sql, "NOT " );
743 buffer_add( state->sql, " IN (" );
745 if( expr->subquery ) {
746 incr_indent( state );
747 build_Query( state, expr->subquery );
749 sqlAddMsg( state, "Unable to build subquery for IN condition" );
751 decr_indent( state );
752 add_newline( state );
753 buffer_add_char( state->sql, ')' );
756 buildSeries( state, expr->subexp_list, NULL );
758 sqlAddMsg( state, "Unable to build IN list" );
760 buffer_add_char( state->sql, ')' );
766 if( expr->left_operand ) {
767 buildExpression( state, expr->left_operand );
769 sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
776 buffer_add( state->sql, " IS NOT NULL" );
778 buffer_add( state->sql, " IS NULL" );
782 buffer_add( state->sql, "NOT " );
784 buffer_add( state->sql, "NULL" );
786 case EXP_NUMBER : // Numeric literal
787 if( !expr->literal ) {
788 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
789 "Internal error: No numeric value in string expression # %d", expr->id ));
792 buffer_add( state->sql, expr->literal );
797 buffer_add( state->sql, "NOT (" );
799 if( expr->left_operand ) {
800 buildExpression( state, expr->left_operand );
802 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
807 buffer_add_char( state->sql, ' ' );
808 buffer_add( state->sql, expr->op );
809 buffer_add_char( state->sql, ' ' );
810 if( expr->right_operand ) {
811 buildExpression( state, expr->right_operand );
813 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
820 buffer_add_char( state->sql, ')' );
825 buffer_add( state->sql, "NOT (" );
827 buildSeries( state, expr->subexp_list, expr->op );
829 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
830 expr->op ? expr->op : "," );
833 buffer_add_char( state->sql, ')' );
836 case EXP_STRING : // String literal
837 if( !expr->literal ) {
838 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
839 "Internal error: No string value in string expression # %d", expr->id ));
842 char* str = strdup( expr->literal );
843 dbi_conn_quote_string( state->dbhandle, &str );
845 buffer_add( state->sql, str );
848 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
849 "Unable to format string literal \"%s\" for expression # %d",
850 expr->literal, expr->id ));
857 buffer_add( state->sql, "NOT " );
859 if( expr->subquery ) {
860 buffer_add_char( state->sql, '(' );
861 incr_indent( state );
862 build_Query( state, expr->subquery );
863 decr_indent( state );
864 add_newline( state );
865 buffer_add_char( state->sql, ')' );
867 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
868 "Internal error: No subquery in subquery expression # %d", expr->id ));
874 if( expr->parenthesize )
875 buffer_add_char( state->sql, ')' );
879 @brief Build a CASE expression.
880 @param state Pointer to the query-building context.
881 @param exp Pointer to an Expression representing a CASE expression.
883 static void buildCase( BuildSQLState* state, const Expression* expr ) {
885 if( ! expr->left_operand ) {
886 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
889 } else if( ! expr->branch_list ) {
890 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
896 buffer_add( state->sql, "NOT (" );
898 // left_operand is the expression on which we shall branch
899 buffer_add( state->sql, "CASE " );
900 buildExpression( state, expr->left_operand );
902 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
906 incr_indent( state );
908 // Emit each branch in turn
909 CaseBranch* branch = expr->branch_list;
911 add_newline( state );
913 if( branch->condition ) {
914 // Emit a WHEN condition
915 buffer_add( state->sql, "WHEN " );
916 buildExpression( state, branch->condition );
917 incr_indent( state );
918 add_newline( state );
919 buffer_add( state->sql, "THEN " );
922 buffer_add( state->sql, "ELSE " );
923 incr_indent( state );
924 add_newline( state );
927 // Emit the THEN expression
928 buildExpression( state, branch->result );
929 decr_indent( state );
931 branch = branch->next;
934 decr_indent( state );
935 add_newline( state );
936 buffer_add( state->sql, "END" );
939 buffer_add( state->sql, ")" );
943 @brief Build a function call, with a subfield if specified.
944 @param state Pointer to the query-building context.
945 @param exp Pointer to an Expression representing a function call.
947 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
949 buffer_add( state->sql, "NOT " );
951 // If a subfield is specified, the function call
952 // needs an extra layer of parentheses
953 if( expr->column_name )
954 buffer_add_char( state->sql, '(' );
956 // First, check for some specific functions with peculiar syntax, and treat them
957 // as special exceptions. We rely on the input side to ensure that the function
958 // name is available.
959 if( !strcasecmp( expr->function_name, "EXTRACT" ))
960 buildExtract( state, expr );
961 else if( !strcasecmp( expr->function_name, "CURRENT_DATE" ) && ! expr->subexp_list )
962 buffer_add( state->sql, "CURRENT_DATE " );
963 else if( !strcasecmp( expr->function_name, "CURRENT_TIME" ) && ! expr->subexp_list )
964 buffer_add( state->sql, "CURRENT_TIME " );
965 else if( !strcasecmp( expr->function_name, "CURRENT_TIMESTAMP" ) && ! expr->subexp_list )
966 buffer_add( state->sql, "CURRENT_TIMESTAMP " );
967 else if( !strcasecmp( expr->function_name, "LOCALTIME" ) && ! expr->subexp_list )
968 buffer_add( state->sql, "LOCALTIME " );
969 else if( !strcasecmp( expr->function_name, "LOCALTIMESTAMP" ) && ! expr->subexp_list )
970 buffer_add( state->sql, "LOCALTIMESTAMP " );
972 buildTypicalFunction( state, expr ); // Not a special exception.
974 if( expr->column_name ) {
975 // Add the name of the subfield
976 buffer_add( state->sql, ").\"" );
977 buffer_add( state->sql, expr->column_name );
978 buffer_add_char( state->sql, '\"' );
983 @brief Count the number of subexpressions attached to a given Expression.
984 @param expr Pointer to the Expression whose subexpressions are to be counted.
985 @return The number of subexpressions.
987 static int subexp_count( const Expression* expr ) {
989 const Expression* sub = expr->subexp_list;
998 @brief Build an ordinary function call, i.e. one with no special syntax,
999 @param state Pointer to the query-building context.
1000 @param exp Pointer to an Expression representing a function call.
1002 Emit the parameters as a comma-separated list of expressions.
1004 static void buildTypicalFunction( BuildSQLState* state, const Expression* expr ) {
1005 buffer_add( state->sql, expr->function_name );
1006 buffer_add_char( state->sql, '(' );
1008 // Add the parameters, if any
1009 buildSeries( state, expr->subexp_list, NULL );
1011 buffer_add_char( state->sql, ')' );
1015 @brief Build a call to the EXTRACT function, with its peculiar syntax.
1016 @param state Pointer to the query-building context.
1017 @param exp Pointer to an Expression representing an EXTRACT call.
1019 If there are not exactly two parameters, or if the first parameter is not a string,
1020 then assume it is an ordinary function overloading on the same name. We don't try to
1021 check the type of the second parameter. Hence it is possible for a legitimately
1022 overloaded function to be uncallable.
1024 The first parameter of EXTRACT() must be one of a short list of names for some fragment
1025 of a date or time. Here we accept that parameter in the form of a string. We don't
1026 surround it with quotes in the output, although PostgreSQL wouldn't mind if we did.
1028 static void buildExtract( BuildSQLState* state, const Expression* expr ) {
1030 const Expression* arg = expr->subexp_list;
1032 // See if this is the special form of EXTRACT(), so far as we can tell
1033 if( subexp_count( expr ) != 2 || arg->type != EXP_STRING ) {
1034 buildTypicalFunction( state, expr );
1037 // check the first argument against a list of valid values
1038 if( strcasecmp( arg->literal, "century" )
1039 && strcasecmp( arg->literal, "day" )
1040 && strcasecmp( arg->literal, "decade" )
1041 && strcasecmp( arg->literal, "dow" )
1042 && strcasecmp( arg->literal, "doy" )
1043 && strcasecmp( arg->literal, "epoch" )
1044 && strcasecmp( arg->literal, "hour" )
1045 && strcasecmp( arg->literal, "isodow" )
1046 && strcasecmp( arg->literal, "isoyear" )
1047 && strcasecmp( arg->literal, "microseconds" )
1048 && strcasecmp( arg->literal, "millennium" )
1049 && strcasecmp( arg->literal, "milliseconds" )
1050 && strcasecmp( arg->literal, "minute" )
1051 && strcasecmp( arg->literal, "month" )
1052 && strcasecmp( arg->literal, "quarter" )
1053 && strcasecmp( arg->literal, "second" )
1054 && strcasecmp( arg->literal, "timezone" )
1055 && strcasecmp( arg->literal, "timezone_hour" )
1056 && strcasecmp( arg->literal, "timezone_minute" )
1057 && strcasecmp( arg->literal, "week" )
1058 && strcasecmp( arg->literal, "year" )) {
1059 // This *could* be an ordinary function, overloading on the name. However it's
1060 // more likely that the user misspelled one of the names expected by EXTRACT().
1062 "Invalid name \"%s\" as EXTRACT argument in expression # %d",
1063 expr->literal, expr->id );
1068 buffer_add( state->sql, "EXTRACT(" );
1069 buffer_add( state->sql, arg->literal );
1070 buffer_add( state->sql, " FROM " );
1075 "Only one argument supplied to EXTRACT function in expression # %d", expr->id );
1080 // The second parameter must be of type timestamp, time, or interval. We don't have
1081 // a good way of checking it here, so we rely on PostgreSQL to complain if necessary.
1082 buildExpression( state, arg );
1083 buffer_add_char( state->sql, ')' );
1087 @brief Build a series of expressions separated by a specified operator, or by commas.
1088 @param state Pointer to the query-building context.
1089 @param subexp_list Pointer to the first Expression in a linked list.
1090 @param op Pointer to the operator, or NULL for commas.
1092 If the operator is AND or OR (in upper, lower, or mixed case), the second and all
1093 subsequent operators will begin on a new line.
1095 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
1098 return; // List is empty
1100 int comma = 0; // Boolean; true if separator is a comma
1101 int newline_needed = 0; // Boolean; true if operator is AND or OR
1106 } else if( !strcmp( op, "," ))
1108 else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
1111 int first = 1; // Boolean; true for first item in list
1112 while( subexp_list ) {
1114 first = 0; // No separator needed yet
1116 // Insert a separator
1118 buffer_add( state->sql, ", " );
1120 if( newline_needed )
1121 add_newline( state );
1123 buffer_add_char( state->sql, ' ' );
1125 buffer_add( state->sql, op );
1126 buffer_add_char( state->sql, ' ' );
1130 buildExpression( state, subexp_list );
1131 subexp_list = subexp_list->next;
1136 @brief Add the value of a bind variable to an SQL statement.
1137 @param state Pointer to the query-building context.
1138 @param bind Pointer to the bind variable whose value is to be added to the SQL.
1140 The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1141 the type of the bind variable.
1143 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1145 // Decide where to get the value, if any
1146 const jsonObject* value = NULL;
1147 if( bind->actual_value )
1148 value = bind->actual_value;
1149 else if( bind->default_value ) {
1150 if( state->defaults_usable )
1151 value = bind->default_value;
1153 sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1158 } else if( state->values_required ) {
1159 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1163 // No value available, and that's okay. Emit the name of the bind variable.
1164 buffer_add_char( state->sql, ':' );
1165 buffer_add( state->sql, bind->name );
1169 // If we get to this point, we know that a value is available. Carry on.
1171 int numeric = 0; // Boolean
1172 if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1176 switch( bind->type ) {
1179 buildScalar( state, numeric, value );
1181 case BIND_STR_LIST :
1182 case BIND_NUM_LIST :
1183 if( JSON_ARRAY == value->type ) {
1184 // Iterate over array, emit each value
1185 int first = 1; // Boolean
1186 unsigned long max = value->size;
1187 unsigned long i = 0;
1192 buffer_add( state->sql, ", " );
1194 buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1198 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1199 "Invalid value for bind variable; expected a list of values" ));
1204 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1205 "Internal error: invalid type for bind variable" ));
1211 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1212 "Unable to emit value of bind variable \"%s\"", bind->name ));
1216 @brief Add a number or quoted string to an SQL statement.
1217 @param state Pointer to the query-building context.
1218 @param numeric Boolean; true if the value is expected to be a number
1219 @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1221 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1222 switch( obj->type ) {
1224 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1225 "Internal error: hash value for bind variable" ));
1229 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1230 "Internal error: array value for bind variable" ));
1236 "Invalid value for bind variable: expected a string, found a number" );
1239 char* str = jsonObjectToSimpleString( obj );
1240 dbi_conn_quote_string( state->dbhandle, &str );
1242 buffer_add( state->sql, str );
1245 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1246 "Unable to format string literal \"%s\" for bind variable",
1247 jsonObjectGetString( obj )));
1254 buffer_add( state->sql, jsonObjectGetString( obj ));
1257 "Invalid value for bind variable: expected a number, found a string" );
1262 buffer_add( state->sql, "NULL" );
1265 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1266 "Internal error: boolean value for bind variable" ));
1270 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1271 "Internal error: corrupted value for bind variable" ));
1278 @brief Start a new line in the output, with the current level of indentation.
1279 @param state Pointer to the query-building context.
1281 static void add_newline( BuildSQLState* state ) {
1282 buffer_add_char( state->sql, '\n' );
1285 static const char blanks[] = " "; // 32 blanks
1286 static const size_t maxlen = sizeof( blanks ) - 1;
1287 const int blanks_per_level = 3;
1288 int n = state->indent * blanks_per_level;
1290 size_t len = n >= maxlen ? maxlen : n;
1291 buffer_add_n( state->sql, blanks, len );
1297 @brief Increase the degree of indentation.
1298 @param state Pointer to the query-building context.
1300 static inline void incr_indent( BuildSQLState* state ) {
1305 @brief Reduce the degree of indentation.
1306 @param state Pointer to the query-building context.
1308 static inline void decr_indent( BuildSQLState* state ) {