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 );
29 static void buildFunction( BuildSQLState* state, const Expression* exp );
30 static int subexp_count( const Expression* expr );
31 static void buildTypicalFunction( BuildSQLState* state, const Expression* expr );
32 static void buildExtract( BuildSQLState* state, const Expression* expr );
34 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op );
35 static void buildBindVar( BuildSQLState* state, const BindVar* bind );
36 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj );
38 static void add_newline( BuildSQLState* state );
39 static inline void incr_indent( BuildSQLState* state );
40 static inline void decr_indent( BuildSQLState* state );
43 @brief Create a jsonObject representing the current list of bind variables.
44 @param bindvar_list Pointer to the bindvar_list member of a BuildSQLState.
45 @return Pointer to the newly created jsonObject.
47 The returned jsonObject is a (possibly empty) JSON_HASH, keyed on the names of the bind
48 variables. The data for each is another level of JSON_HASH with a fixed set of tags:
52 - "default_value" (as a jsonObject)
53 - "actual_value" (as a jsonObject)
55 Any non-existent values are represented as JSON_NULLs.
57 The calling code is responsible for freeing the returned jsonOjbect by calling
60 jsonObject* oilsBindVarList( osrfHash* bindvar_list ) {
61 jsonObject* list = jsonNewObjectType( JSON_HASH );
63 if( bindvar_list && osrfHashGetCount( bindvar_list )) {
64 // Traverse our internal list of bind variables
66 osrfHashIterator* iter = osrfNewHashIterator( bindvar_list );
67 while(( bind = osrfHashIteratorNext( iter ))) {
68 // Create an hash to represent the bind variable
69 jsonObject* bind_obj = jsonNewObjectType( JSON_HASH );
71 // Add an entry for each attribute
72 jsonObject* attr = jsonNewObject( bind->label );
73 jsonObjectSetKey( bind_obj, "label", attr );
75 const char* type = NULL;
76 switch( bind->type ) {
93 attr = jsonNewObject( type );
94 jsonObjectSetKey( bind_obj, "type", attr );
96 attr = jsonNewObject( bind->description );
97 jsonObjectSetKey( bind_obj, "description", attr );
99 attr = jsonObjectClone( bind->default_value );
100 jsonObjectSetKey( bind_obj, "default_value", attr );
102 attr = jsonObjectClone( bind->actual_value );
103 jsonObjectSetKey( bind_obj, "actual_value", attr );
105 // Add the bind variable to the list
106 jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
108 osrfHashIteratorFree( iter );
115 @brief Apply values to bind variables, overriding the defaults, if any.
116 @param state Pointer to the query-building context.
117 @param bindings A JSON_HASH of values.
118 @return 0 if successful, or 1 if not.
120 The @a bindings parameter must be a JSON_HASH. The keys are the names of bind variables.
121 The values are the corresponding values for the variables.
123 int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) {
125 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
127 } else if( !bindings ) {
128 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
129 "Internal error: No pointer to bindings" ));
131 } else if( bindings->type != JSON_HASH ) {
132 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
133 "Internal error: bindings parameter is not a JSON_HASH" ));
138 jsonObject* value = NULL;
139 jsonIterator* iter = jsonNewIterator( bindings );
140 while(( value = jsonIteratorNext( iter ))) {
141 const char* var_name = iter->key;
142 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
144 // Apply or replace the value for the specified variable
145 if( bind->actual_value )
146 jsonObjectFree( bind->actual_value );
147 bind->actual_value = jsonObjectClone( value );
149 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
150 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
154 jsonIteratorFree( iter );
160 @brief Build an SQL query.
161 @param state Pointer to the query-building context.
162 @param query Pointer to the query to be built.
163 @return Zero if successful, or 1 if not.
165 Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
167 int buildSQL( BuildSQLState* state, const StoredQ* query ) {
169 buffer_reset( state->sql );
171 build_Query( state, query );
172 if( ! state->error ) {
173 // Remove the trailing space, if there is one, and add a semicolon.
174 char c = buffer_chomp( state->sql );
176 buffer_add_char( state->sql, c ); // oops, not a space; put it back
177 buffer_add( state->sql, ";\n" );
183 @brief Build an SQL query, appending it to what has been built so far.
184 @param state Pointer to the query-building context.
185 @param query Pointer to the query to be built.
187 Look at the query type and branch to the corresponding routine.
189 static void build_Query( BuildSQLState* state, const StoredQ* query ) {
190 if( buffer_length( state->sql ))
191 add_newline( state );
193 switch( query->type ) {
195 buildSelect( state, query );
198 buildCombo( state, query, "UNION" );
201 buildCombo( state, query, "INTERSECT" );
204 buildCombo( state, query, "EXCEPT" );
207 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
208 "Internal error: invalid query type %d in query # %d",
209 query->type, query->id ));
216 @brief Build a UNION, INTERSECT, or EXCEPT query.
217 @param state Pointer to the query-building context.
218 @param query Pointer to the query to be built.
219 @param type_str The query type, as a string.
221 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) {
223 QSeq* seq = query->child_list;
225 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
226 "Internal error: No child queries within %s query # %d",
227 type_str, query->id ));
232 // Traverse the list of child queries
234 build_Query( state, seq->child_query );
236 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
237 seq->child_query->id, type_str, query->id );
242 add_newline( state );
243 buffer_add( state->sql, type_str );
244 buffer_add_char( state->sql, ' ' );
246 buffer_add( state->sql, "ALL " );
254 @brief Build a SELECT statement.
255 @param state Pointer to the query-building context.
256 @param query Pointer to the StoredQ structure that represents the query.
258 static void buildSelect( BuildSQLState* state, const StoredQ* query ) {
260 FromRelation* from_clause = query->from_clause;
262 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
268 buffer_add( state->sql, "SELECT" );
269 incr_indent( state );
270 buildSelectList( state, query->select_list );
272 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
276 decr_indent( state );
278 // Build FROM clause, if there is one
279 if( query->from_clause ) {
280 buildFrom( state, query->from_clause );
282 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
288 // Build WHERE clause, if there is one
289 if( query->where_clause ) {
290 add_newline( state );
291 buffer_add( state->sql, "WHERE" );
292 incr_indent( state );
293 add_newline( state );
294 buildExpression( state, query->where_clause );
296 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
300 decr_indent( state );
303 // Build GROUP BY clause, if there is one
304 buildGroupBy( state, query->select_list );
306 // Build HAVING clause, if there is one
307 if( query->having_clause ) {
308 add_newline( state );
309 buffer_add( state->sql, "HAVING" );
310 incr_indent( state );
311 add_newline( state );
312 buildExpression( state, query->having_clause );
314 sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
318 decr_indent( state );
321 // Build ORDER BY clause, if there is one
322 if( query->order_by_list ) {
323 buildOrderBy( state, query->order_by_list );
325 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
331 // Build LIMIT clause, if there is one
332 if( query->limit_count ) {
333 add_newline( state );
334 buffer_add( state->sql, "LIMIT " );
335 buildExpression( state, query->limit_count );
338 // Build OFFSET clause, if there is one
339 if( query->offset_count ) {
340 add_newline( state );
341 buffer_add( state->sql, "OFFSET " );
342 buildExpression( state, query->offset_count );
349 @brief Build a FROM clause.
350 @param Pointer to the query-building context.
351 @param Pointer to the StoredQ query to which the FROM clause belongs.
353 static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) {
355 add_newline( state );
356 buffer_add( state->sql, "FROM" );
357 incr_indent( state );
358 add_newline( state );
360 switch( core_from->type ) {
361 case FRT_RELATION : {
362 char* relation = core_from->table_name;
364 if( !core_from->class_name ) {
365 sqlAddMsg( state, "No relation specified for core relation # %d",
371 // Look up table name, view name, or source_definition in the IDL
372 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
373 relation = oilsGetRelation( class_hash );
377 buffer_add( state->sql, relation );
378 if( !core_from->table_name )
379 free( relation ); // In this case we strdup'd it, must free it
383 buffer_add_char( state->sql, '(' );
384 incr_indent( state );
385 build_Query( state, core_from->subquery );
386 decr_indent( state );
387 add_newline( state );
388 buffer_add_char( state->sql, ')' );
391 buildFunction( state, core_from->function_call );
392 if ( state->error ) {
394 "Unable to include function call # %d in FROM relation # %d",
395 core_from->function_call->id, core_from->id );
400 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
401 "Internal error: Invalid type # %d in FROM relation # %d",
402 core_from->type, core_from->id ));
407 // Add a table alias, if possible
408 if( core_from->table_alias ) {
409 buffer_add( state->sql, " AS \"" );
410 buffer_add( state->sql, core_from->table_alias );
411 buffer_add( state->sql, "\" " );
413 else if( core_from->class_name ) {
414 buffer_add( state->sql, " AS \"" );
415 buffer_add( state->sql, core_from->class_name );
416 buffer_add( state->sql, "\" " );
418 buffer_add_char( state->sql, ' ' );
420 incr_indent( state );
421 FromRelation* join = core_from->join_list;
423 buildJoin( state, join );
425 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
431 decr_indent( state );
432 decr_indent( state );
436 @brief Add a JOIN clause.
437 @param state Pointer to the query-building context.
438 @param join Pointer to the FromRelation representing the JOIN to be added.
440 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
441 add_newline( state );
442 switch( join->join_type ) {
444 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
448 buffer_add( state->sql, "INNER JOIN " );
451 buffer_add( state->sql, "LEFT JOIN " );
454 buffer_add( state->sql, "RIGHT JOIN " );
457 buffer_add( state->sql, "FULL JOIN " );
460 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
465 switch( join->type ) {
468 if( !join->table_name || ! *join->table_name ) {
469 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
473 buffer_add( state->sql, join->table_name );
477 if( !join->subquery ) {
478 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
481 } else if( !join->table_alias ) {
482 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
487 buffer_add_char( state->sql, '(' );
488 incr_indent( state );
489 build_Query( state, join->subquery );
490 decr_indent( state );
491 add_newline( state );
492 buffer_add_char( state->sql, ')' );
495 if( !join->table_name || ! *join->table_name ) {
496 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
504 const char* effective_alias = join->table_alias;
505 if( !effective_alias )
506 effective_alias = join->class_name;
508 if( effective_alias ) {
509 buffer_add( state->sql, " AS \"" );
510 buffer_add( state->sql, effective_alias );
511 buffer_add_char( state->sql, '\"' );
514 if( join->on_clause ) {
515 incr_indent( state );
516 add_newline( state );
517 buffer_add( state->sql, "ON " );
518 buildExpression( state, join->on_clause );
519 decr_indent( state );
522 FromRelation* subjoin = join->join_list;
524 buildJoin( state, subjoin );
526 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
529 subjoin = subjoin->next;
534 @brief Build a SELECT list.
535 @param state Pointer to the query-building context.
536 @param item Pointer to the first in a linked list of SELECT items.
538 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
543 buffer_add_char( state->sql, ',' );
544 add_newline( state );
545 buildExpression( state, item->expression );
547 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
552 if( item->column_alias ) {
553 buffer_add( state->sql, " AS \"" );
554 buffer_add( state->sql, item->column_alias );
555 buffer_add_char( state->sql, '\"' );
560 buffer_add_char( state->sql, ' ' );
564 @brief Add a GROUP BY clause, if there is one, to the current query.
565 @param state Pointer to the query-building context.
566 @param sel_list Pointer to the first node in a linked list of SelectItems
568 We reference the GROUP BY items by number, not by repeating the expressions.
570 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
571 int seq = 0; // Sequence number of current SelectItem
572 int first = 1; // Boolean: true for the first GROUPed BY item
576 if( sel_list->grouped_by ) {
578 add_newline( state );
579 buffer_add( state->sql, "GROUP BY " );
583 buffer_add( state->sql, ", " );
585 buffer_fadd( state->sql, "%d", seq );
588 sel_list = sel_list->next;
593 @brief Add an ORDER BY clause to the current query.
594 @param state Pointer to the query-building context.
595 @param ord_list Pointer to the first node in a linked list of OrderItems.
597 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
598 add_newline( state );
599 buffer_add( state->sql, "ORDER BY" );
600 incr_indent( state );
602 int first = 1; // boolean
607 buffer_add_char( state->sql, ',' );
608 add_newline( state );
609 buildExpression( state, ord_list->expression );
611 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
615 ord_list = ord_list->next;
618 decr_indent( state );
623 @brief Build an arbitrary expression.
624 @param state Pointer to the query-building context.
625 @param expr Pointer to the Expression representing the expression to be built.
627 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
629 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
630 "Internal error: NULL pointer to Expression" ));
635 if( expr->parenthesize )
636 buffer_add_char( state->sql, '(' );
638 switch( expr->type ) {
641 buffer_add( state->sql, "NOT " );
643 buildExpression( state, expr->left_operand );
645 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
650 buffer_add( state->sql, " BETWEEN " );
652 buildExpression( state, expr->subexp_list );
654 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
659 buffer_add( state->sql, " AND " );
661 buildExpression( state, expr->subexp_list->next );
663 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
670 if( !expr->bind ) { // Sanity check
671 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
672 "Internal error: no variable for bind variable expression" ));
675 buildBindVar( state, expr->bind );
679 buffer_add( state->sql, "NOT " );
681 if( expr->literal ) {
682 buffer_add( state->sql, expr->literal );
683 buffer_add_char( state->sql, ' ' );
685 buffer_add( state->sql, "FALSE " );
688 buildCase( state, expr );
690 sqlAddMsg( state, "Unable to build CASE expression # %d", expr->id );
693 case EXP_CAST : // Type cast
695 buffer_add( state->sql, "NOT " );
697 buffer_add( state->sql, "CAST (" );
698 buildExpression( state, expr->left_operand );
700 sqlAddMsg( state, "Unable to build left operand for CAST expression # %d",
703 buffer_add( state->sql, " AS " );
704 if( expr->cast_type && expr->cast_type->datatype_name ) {
705 buffer_add( state->sql, expr->cast_type->datatype_name );
706 buffer_add_char( state->sql, ')' );
708 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
709 "No datatype available for CAST expression # %d", expr->id ));
714 case EXP_COLUMN : // Table column
716 buffer_add( state->sql, "NOT " );
718 if( expr->table_alias ) {
719 buffer_add_char( state->sql, '\"' );
720 buffer_add( state->sql, expr->table_alias );
721 buffer_add( state->sql, "\"." );
723 if( expr->column_name ) {
724 buffer_add( state->sql, expr->column_name );
726 buffer_add_char( state->sql, '*' );
730 if( !expr->subquery ) {
731 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
732 "No subquery found for EXIST expression # %d", expr->id ));
736 buffer_add( state->sql, "NOT " );
738 buffer_add( state->sql, "EXISTS (" );
739 incr_indent( state );
740 build_Query( state, expr->subquery );
741 decr_indent( state );
742 add_newline( state );
743 buffer_add_char( state->sql, ')' );
747 buildFunction( state, expr );
750 if( expr->left_operand ) {
751 buildExpression( state, expr->left_operand );
752 if( !state->error ) {
754 buffer_add( state->sql, "NOT " );
755 buffer_add( state->sql, " IN (" );
757 if( expr->subquery ) {
758 incr_indent( state );
759 build_Query( state, expr->subquery );
761 sqlAddMsg( state, "Unable to build subquery for IN condition" );
763 decr_indent( state );
764 add_newline( state );
765 buffer_add_char( state->sql, ')' );
768 buildSeries( state, expr->subexp_list, NULL );
770 sqlAddMsg( state, "Unable to build IN list" );
772 buffer_add_char( state->sql, ')' );
778 if( expr->left_operand ) {
779 buildExpression( state, expr->left_operand );
781 sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
788 buffer_add( state->sql, " IS NOT NULL" );
790 buffer_add( state->sql, " IS NULL" );
794 buffer_add( state->sql, "NOT " );
796 buffer_add( state->sql, "NULL" );
798 case EXP_NUMBER : // Numeric literal
799 if( !expr->literal ) {
800 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
801 "Internal error: No numeric value in string expression # %d", expr->id ));
804 buffer_add( state->sql, expr->literal );
809 buffer_add( state->sql, "NOT (" );
811 if( expr->left_operand ) {
812 buildExpression( state, expr->left_operand );
814 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
819 buffer_add_char( state->sql, ' ' );
820 buffer_add( state->sql, expr->op );
821 buffer_add_char( state->sql, ' ' );
822 if( expr->right_operand ) {
823 buildExpression( state, expr->right_operand );
825 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
832 buffer_add_char( state->sql, ')' );
837 buffer_add( state->sql, "NOT (" );
839 buildSeries( state, expr->subexp_list, expr->op );
841 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
842 expr->op ? expr->op : "," );
845 buffer_add_char( state->sql, ')' );
848 case EXP_STRING : // String literal
849 if( !expr->literal ) {
850 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
851 "Internal error: No string value in string expression # %d", expr->id ));
854 char* str = strdup( expr->literal );
855 dbi_conn_quote_string( state->dbhandle, &str );
857 buffer_add( state->sql, str );
860 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
861 "Unable to format string literal \"%s\" for expression # %d",
862 expr->literal, expr->id ));
869 buffer_add( state->sql, "NOT " );
871 if( expr->subquery ) {
872 buffer_add_char( state->sql, '(' );
873 incr_indent( state );
874 build_Query( state, expr->subquery );
875 decr_indent( state );
876 add_newline( state );
877 buffer_add_char( state->sql, ')' );
879 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
880 "Internal error: No subquery in subquery expression # %d", expr->id ));
886 if( expr->parenthesize )
887 buffer_add_char( state->sql, ')' );
891 @brief Build a CASE expression.
892 @param state Pointer to the query-building context.
893 @param exp Pointer to an Expression representing a CASE expression.
895 static void buildCase( BuildSQLState* state, const Expression* expr ) {
897 if( ! expr->left_operand ) {
898 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
901 } else if( ! expr->branch_list ) {
902 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
908 buffer_add( state->sql, "NOT (" );
910 // left_operand is the expression on which we shall branch
911 buffer_add( state->sql, "CASE " );
912 buildExpression( state, expr->left_operand );
914 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
918 incr_indent( state );
920 // Emit each branch in turn
921 CaseBranch* branch = expr->branch_list;
923 add_newline( state );
925 if( branch->condition ) {
926 // Emit a WHEN condition
927 buffer_add( state->sql, "WHEN " );
928 buildExpression( state, branch->condition );
929 incr_indent( state );
930 add_newline( state );
931 buffer_add( state->sql, "THEN " );
934 buffer_add( state->sql, "ELSE " );
935 incr_indent( state );
936 add_newline( state );
939 // Emit the THEN expression
940 buildExpression( state, branch->result );
941 decr_indent( state );
943 branch = branch->next;
946 decr_indent( state );
947 add_newline( state );
948 buffer_add( state->sql, "END" );
951 buffer_add( state->sql, ")" );
955 @brief Build a function call, with a subfield if specified.
956 @param state Pointer to the query-building context.
957 @param exp Pointer to an Expression representing a function call.
959 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
961 buffer_add( state->sql, "NOT " );
963 // If a subfield is specified, the function call
964 // needs an extra layer of parentheses
965 if( expr->column_name )
966 buffer_add_char( state->sql, '(' );
968 // First, check for some specific functions with peculiar syntax, and treat them
969 // as special exceptions. We rely on the input side to ensure that the function
970 // name is available.
971 if( !strcasecmp( expr->function_name, "EXTRACT" ))
972 buildExtract( state, expr );
973 else if( !strcasecmp( expr->function_name, "CURRENT_DATE" ) && ! expr->subexp_list )
974 buffer_add( state->sql, "CURRENT_DATE " );
975 else if( !strcasecmp( expr->function_name, "CURRENT_TIME" ) && ! expr->subexp_list )
976 buffer_add( state->sql, "CURRENT_TIME " );
977 else if( !strcasecmp( expr->function_name, "CURRENT_TIMESTAMP" ) && ! expr->subexp_list )
978 buffer_add( state->sql, "CURRENT_TIMESTAMP " );
979 else if( !strcasecmp( expr->function_name, "LOCALTIME" ) && ! expr->subexp_list )
980 buffer_add( state->sql, "LOCALTIME " );
981 else if( !strcasecmp( expr->function_name, "LOCALTIMESTAMP" ) && ! expr->subexp_list )
982 buffer_add( state->sql, "LOCALTIMESTAMP " );
983 else if( !strcasecmp( expr->function_name, "TRIM" )) {
984 int arg_count = subexp_count( expr );
986 if( (arg_count != 2 && arg_count != 3 ) || expr->subexp_list->type != EXP_STRING )
987 buildTypicalFunction( state, expr );
990 "TRIM function not supported in expr # %d; use ltrim() and/or rtrim()",
996 buildTypicalFunction( state, expr ); // Not a special exception.
998 if( expr->column_name ) {
999 // Add the name of the subfield
1000 buffer_add( state->sql, ").\"" );
1001 buffer_add( state->sql, expr->column_name );
1002 buffer_add_char( state->sql, '\"' );
1007 @brief Count the number of subexpressions attached to a given Expression.
1008 @param expr Pointer to the Expression whose subexpressions are to be counted.
1009 @return The number of subexpressions.
1011 static int subexp_count( const Expression* expr ) {
1016 const Expression* sub = expr->subexp_list;
1025 @brief Build an ordinary function call, i.e. one with no special syntax,
1026 @param state Pointer to the query-building context.
1027 @param exp Pointer to an Expression representing a function call.
1029 Emit the parameters as a comma-separated list of expressions.
1031 static void buildTypicalFunction( BuildSQLState* state, const Expression* expr ) {
1032 buffer_add( state->sql, expr->function_name );
1033 buffer_add_char( state->sql, '(' );
1035 // Add the parameters, if any
1036 buildSeries( state, expr->subexp_list, NULL );
1038 buffer_add_char( state->sql, ')' );
1042 @brief Build a call to the EXTRACT function, with its peculiar syntax.
1043 @param state Pointer to the query-building context.
1044 @param exp Pointer to an Expression representing an EXTRACT call.
1046 If there are not exactly two parameters, or if the first parameter is not a string,
1047 then assume it is an ordinary function overloading on the same name. We don't try to
1048 check the type of the second parameter. Hence it is possible for a legitimately
1049 overloaded function to be uncallable.
1051 The first parameter of EXTRACT() must be one of a short list of names for some fragment
1052 of a date or time. Here we accept that parameter in the form of a string. We don't
1053 surround it with quotes in the output, although PostgreSQL wouldn't mind if we did.
1055 static void buildExtract( BuildSQLState* state, const Expression* expr ) {
1057 const Expression* arg = expr->subexp_list;
1059 // See if this is the special form of EXTRACT(), so far as we can tell
1060 if( subexp_count( expr ) != 2 || arg->type != EXP_STRING ) {
1061 buildTypicalFunction( state, expr );
1064 // check the first argument against a list of valid values
1065 if( strcasecmp( arg->literal, "century" )
1066 && strcasecmp( arg->literal, "day" )
1067 && strcasecmp( arg->literal, "decade" )
1068 && strcasecmp( arg->literal, "dow" )
1069 && strcasecmp( arg->literal, "doy" )
1070 && strcasecmp( arg->literal, "epoch" )
1071 && strcasecmp( arg->literal, "hour" )
1072 && strcasecmp( arg->literal, "isodow" )
1073 && strcasecmp( arg->literal, "isoyear" )
1074 && strcasecmp( arg->literal, "microseconds" )
1075 && strcasecmp( arg->literal, "millennium" )
1076 && strcasecmp( arg->literal, "milliseconds" )
1077 && strcasecmp( arg->literal, "minute" )
1078 && strcasecmp( arg->literal, "month" )
1079 && strcasecmp( arg->literal, "quarter" )
1080 && strcasecmp( arg->literal, "second" )
1081 && strcasecmp( arg->literal, "timezone" )
1082 && strcasecmp( arg->literal, "timezone_hour" )
1083 && strcasecmp( arg->literal, "timezone_minute" )
1084 && strcasecmp( arg->literal, "week" )
1085 && strcasecmp( arg->literal, "year" )) {
1086 // This *could* be an ordinary function, overloading on the name. However it's
1087 // more likely that the user misspelled one of the names expected by EXTRACT().
1089 "Invalid name \"%s\" as EXTRACT argument in expression # %d",
1090 expr->literal, expr->id );
1095 buffer_add( state->sql, "EXTRACT(" );
1096 buffer_add( state->sql, arg->literal );
1097 buffer_add( state->sql, " FROM " );
1102 "Only one argument supplied to EXTRACT function in expression # %d", expr->id );
1107 // The second parameter must be of type timestamp, time, or interval. We don't have
1108 // a good way of checking it here, so we rely on PostgreSQL to complain if necessary.
1109 buildExpression( state, arg );
1110 buffer_add_char( state->sql, ')' );
1114 @brief Build a series of expressions separated by a specified operator, or by commas.
1115 @param state Pointer to the query-building context.
1116 @param subexp_list Pointer to the first Expression in a linked list.
1117 @param op Pointer to the operator, or NULL for commas.
1119 If the operator is AND or OR (in upper, lower, or mixed case), the second and all
1120 subsequent operators will begin on a new line.
1122 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
1125 return; // List is empty
1127 int comma = 0; // Boolean; true if separator is a comma
1128 int newline_needed = 0; // Boolean; true if operator is AND or OR
1133 } else if( !strcmp( op, "," ))
1135 else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
1138 int first = 1; // Boolean; true for first item in list
1139 while( subexp_list ) {
1141 first = 0; // No separator needed yet
1143 // Insert a separator
1145 buffer_add( state->sql, ", " );
1147 if( newline_needed )
1148 add_newline( state );
1150 buffer_add_char( state->sql, ' ' );
1152 buffer_add( state->sql, op );
1153 buffer_add_char( state->sql, ' ' );
1157 buildExpression( state, subexp_list );
1158 subexp_list = subexp_list->next;
1163 @brief Add the value of a bind variable to an SQL statement.
1164 @param state Pointer to the query-building context.
1165 @param bind Pointer to the bind variable whose value is to be added to the SQL.
1167 The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1168 the type of the bind variable.
1170 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1172 // Decide where to get the value, if any
1173 const jsonObject* value = NULL;
1174 if( bind->actual_value )
1175 value = bind->actual_value;
1176 else if( bind->default_value ) {
1177 if( state->defaults_usable )
1178 value = bind->default_value;
1180 sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1185 } else if( state->values_required ) {
1186 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1190 // No value available, and that's okay. Emit the name of the bind variable.
1191 buffer_add_char( state->sql, ':' );
1192 buffer_add( state->sql, bind->name );
1196 // If we get to this point, we know that a value is available. Carry on.
1198 int numeric = 0; // Boolean
1199 if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1203 switch( bind->type ) {
1206 buildScalar( state, numeric, value );
1208 case BIND_STR_LIST :
1209 case BIND_NUM_LIST :
1210 if( JSON_ARRAY == value->type ) {
1211 // Iterate over array, emit each value
1212 int first = 1; // Boolean
1213 unsigned long max = value->size;
1214 unsigned long i = 0;
1219 buffer_add( state->sql, ", " );
1221 buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1225 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1226 "Invalid value for bind variable; expected a list of values" ));
1231 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1232 "Internal error: invalid type for bind variable" ));
1238 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1239 "Unable to emit value of bind variable \"%s\"", bind->name ));
1243 @brief Add a number or quoted string to an SQL statement.
1244 @param state Pointer to the query-building context.
1245 @param numeric Boolean; true if the value is expected to be a number
1246 @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1248 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1249 switch( obj->type ) {
1251 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1252 "Internal error: hash value for bind variable" ));
1256 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1257 "Internal error: array value for bind variable" ));
1263 "Invalid value for bind variable: expected a string, found a number" );
1266 char* str = jsonObjectToSimpleString( obj );
1267 dbi_conn_quote_string( state->dbhandle, &str );
1269 buffer_add( state->sql, str );
1272 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1273 "Unable to format string literal \"%s\" for bind variable",
1274 jsonObjectGetString( obj )));
1281 buffer_add( state->sql, jsonObjectGetString( obj ));
1284 "Invalid value for bind variable: expected a number, found a string" );
1289 buffer_add( state->sql, "NULL" );
1292 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1293 "Internal error: boolean value for bind variable" ));
1297 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1298 "Internal error: corrupted value for bind variable" ));
1305 @brief Start a new line in the output, with the current level of indentation.
1306 @param state Pointer to the query-building context.
1308 static void add_newline( BuildSQLState* state ) {
1309 buffer_add_char( state->sql, '\n' );
1312 static const char blanks[] = " "; // 32 blanks
1313 static const size_t maxlen = sizeof( blanks ) - 1;
1314 const int blanks_per_level = 3;
1315 int n = state->indent * blanks_per_level;
1317 size_t len = n >= maxlen ? maxlen : n;
1318 buffer_add_n( state->sql, blanks, len );
1324 @brief Increase the degree of indentation.
1325 @param state Pointer to the query-building context.
1327 static inline void incr_indent( BuildSQLState* state ) {
1332 @brief Reduce the degree of indentation.
1333 @param state Pointer to the query-building context.
1335 static inline void decr_indent( BuildSQLState* state ) {