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 if( bind->default_value ) {
100 attr = jsonObjectClone( bind->default_value );
101 jsonObjectSetKey( bind_obj, "default_value", attr );
104 if( bind->actual_value ) {
105 attr = jsonObjectClone( bind->actual_value );
106 jsonObjectSetKey( bind_obj, "actual_value", attr );
109 // Add the bind variable to the list
110 jsonObjectSetKey( list, osrfHashIteratorKey( iter ), bind_obj );
112 osrfHashIteratorFree( iter );
119 @brief Apply values to bind variables, overriding the defaults, if any.
120 @param state Pointer to the query-building context.
121 @param bindings A JSON_HASH of values.
122 @return 0 if successful, or 1 if not.
124 The @a bindings parameter must be a JSON_HASH. The keys are the names of bind variables.
125 The values are the corresponding values for the variables.
127 int oilsApplyBindValues( BuildSQLState* state, const jsonObject* bindings ) {
129 osrfLogError( OSRF_LOG_MARK, "NULL pointer to state" );
131 } else if( !bindings ) {
132 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
133 "Internal error: No pointer to bindings" ));
135 } else if( bindings->type != JSON_HASH ) {
136 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
137 "Internal error: bindings parameter is not a JSON_HASH" ));
142 jsonObject* value = NULL;
143 jsonIterator* iter = jsonNewIterator( bindings );
144 while(( value = jsonIteratorNext( iter ))) {
145 const char* var_name = iter->key;
146 BindVar* bind = osrfHashGet( state->bindvar_list, var_name );
148 // Apply or replace the value for the specified variable
149 if( bind->actual_value )
150 jsonObjectFree( bind->actual_value );
151 bind->actual_value = jsonObjectClone( value );
153 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
154 "Can't assign value to bind variable \"%s\": no such variable", var_name ));
158 jsonIteratorFree( iter );
164 @brief Build an SQL query.
165 @param state Pointer to the query-building context.
166 @param query Pointer to the query to be built.
167 @return Zero if successful, or 1 if not.
169 Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
171 int buildSQL( BuildSQLState* state, const StoredQ* query ) {
173 buffer_reset( state->sql );
175 build_Query( state, query );
176 if( ! state->error ) {
177 // Remove the trailing space, if there is one, and add a semicolon.
178 char c = buffer_chomp( state->sql );
180 buffer_add_char( state->sql, c ); // oops, not a space; put it back
181 buffer_add( state->sql, ";\n" );
187 @brief Build an SQL query, appending it to what has been built so far.
188 @param state Pointer to the query-building context.
189 @param query Pointer to the query to be built.
191 Look at the query type and branch to the corresponding routine.
193 static void build_Query( BuildSQLState* state, const StoredQ* query ) {
194 if( buffer_length( state->sql ))
195 add_newline( state );
197 switch( query->type ) {
199 buildSelect( state, query );
202 buildCombo( state, query, "UNION" );
205 buildCombo( state, query, "INTERSECT" );
208 buildCombo( state, query, "EXCEPT" );
211 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
212 "Internal error: invalid query type %d in query # %d",
213 query->type, query->id ));
220 @brief Build a UNION, INTERSECT, or EXCEPT query.
221 @param state Pointer to the query-building context.
222 @param query Pointer to the query to be built.
223 @param type_str The query type, as a string.
225 static void buildCombo( BuildSQLState* state, const StoredQ* query, const char* type_str ) {
227 QSeq* seq = query->child_list;
229 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
230 "Internal error: No child queries within %s query # %d",
231 type_str, query->id ));
236 // Traverse the list of child queries
238 build_Query( state, seq->child_query );
240 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
241 seq->child_query->id, type_str, query->id );
246 add_newline( state );
247 buffer_add( state->sql, type_str );
248 buffer_add_char( state->sql, ' ' );
250 buffer_add( state->sql, "ALL " );
258 @brief Build a SELECT statement.
259 @param state Pointer to the query-building context.
260 @param query Pointer to the StoredQ structure that represents the query.
262 static void buildSelect( BuildSQLState* state, const StoredQ* query ) {
264 FromRelation* from_clause = query->from_clause;
266 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
272 buffer_add( state->sql, "SELECT" );
273 incr_indent( state );
274 buildSelectList( state, query->select_list );
276 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
280 decr_indent( state );
282 // Build FROM clause, if there is one
283 if( query->from_clause ) {
284 buildFrom( state, query->from_clause );
286 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
292 // Build WHERE clause, if there is one
293 if( query->where_clause ) {
294 add_newline( state );
295 buffer_add( state->sql, "WHERE" );
296 incr_indent( state );
297 add_newline( state );
298 buildExpression( state, query->where_clause );
300 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
304 decr_indent( state );
307 // Build GROUP BY clause, if there is one
308 buildGroupBy( state, query->select_list );
310 // Build HAVING clause, if there is one
311 if( query->having_clause ) {
312 add_newline( state );
313 buffer_add( state->sql, "HAVING" );
314 incr_indent( state );
315 add_newline( state );
316 buildExpression( state, query->having_clause );
318 sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
322 decr_indent( state );
325 // Build ORDER BY clause, if there is one
326 if( query->order_by_list ) {
327 buildOrderBy( state, query->order_by_list );
329 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
335 // Build LIMIT clause, if there is one
336 if( query->limit_count ) {
337 add_newline( state );
338 buffer_add( state->sql, "LIMIT " );
339 buildExpression( state, query->limit_count );
342 // Build OFFSET clause, if there is one
343 if( query->offset_count ) {
344 add_newline( state );
345 buffer_add( state->sql, "OFFSET " );
346 buildExpression( state, query->offset_count );
353 @brief Build a FROM clause.
354 @param Pointer to the query-building context.
355 @param Pointer to the StoredQ query to which the FROM clause belongs.
357 static void buildFrom( BuildSQLState* state, const FromRelation* core_from ) {
359 add_newline( state );
360 buffer_add( state->sql, "FROM" );
361 incr_indent( state );
362 add_newline( state );
364 switch( core_from->type ) {
365 case FRT_RELATION : {
366 char* relation = core_from->table_name;
368 if( !core_from->class_name ) {
369 sqlAddMsg( state, "No relation specified for core relation # %d",
375 // Look up table name, view name, or source_definition in the IDL
376 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
377 relation = oilsGetRelation( class_hash );
381 buffer_add( state->sql, relation );
382 if( !core_from->table_name )
383 free( relation ); // In this case we strdup'd it, must free it
387 buffer_add_char( state->sql, '(' );
388 incr_indent( state );
389 build_Query( state, core_from->subquery );
390 decr_indent( state );
391 add_newline( state );
392 buffer_add_char( state->sql, ')' );
395 buildFunction( state, core_from->function_call );
396 if ( state->error ) {
398 "Unable to include function call # %d in FROM relation # %d",
399 core_from->function_call->id, core_from->id );
404 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
405 "Internal error: Invalid type # %d in FROM relation # %d",
406 core_from->type, core_from->id ));
411 // Add a table alias, if possible
412 if( core_from->table_alias ) {
413 buffer_add( state->sql, " AS \"" );
414 buffer_add( state->sql, core_from->table_alias );
415 buffer_add( state->sql, "\" " );
417 else if( core_from->class_name ) {
418 buffer_add( state->sql, " AS \"" );
419 buffer_add( state->sql, core_from->class_name );
420 buffer_add( state->sql, "\" " );
422 buffer_add_char( state->sql, ' ' );
424 incr_indent( state );
425 FromRelation* join = core_from->join_list;
427 buildJoin( state, join );
429 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
435 decr_indent( state );
436 decr_indent( state );
440 @brief Add a JOIN clause.
441 @param state Pointer to the query-building context.
442 @param join Pointer to the FromRelation representing the JOIN to be added.
444 static void buildJoin( BuildSQLState* state, const FromRelation* join ) {
445 add_newline( state );
446 switch( join->join_type ) {
448 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
452 buffer_add( state->sql, "INNER JOIN " );
455 buffer_add( state->sql, "LEFT JOIN " );
458 buffer_add( state->sql, "RIGHT JOIN " );
461 buffer_add( state->sql, "FULL JOIN " );
464 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
469 switch( join->type ) {
472 if( !join->table_name || ! *join->table_name ) {
473 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
477 buffer_add( state->sql, join->table_name );
481 if( !join->subquery ) {
482 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
485 } else if( !join->table_alias ) {
486 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
491 buffer_add_char( state->sql, '(' );
492 incr_indent( state );
493 build_Query( state, join->subquery );
494 decr_indent( state );
495 add_newline( state );
496 buffer_add_char( state->sql, ')' );
499 if( !join->table_name || ! *join->table_name ) {
500 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
508 const char* effective_alias = join->table_alias;
509 if( !effective_alias )
510 effective_alias = join->class_name;
512 if( effective_alias ) {
513 buffer_add( state->sql, " AS \"" );
514 buffer_add( state->sql, effective_alias );
515 buffer_add_char( state->sql, '\"' );
518 if( join->on_clause ) {
519 incr_indent( state );
520 add_newline( state );
521 buffer_add( state->sql, "ON " );
522 buildExpression( state, join->on_clause );
523 decr_indent( state );
526 FromRelation* subjoin = join->join_list;
528 buildJoin( state, subjoin );
530 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
533 subjoin = subjoin->next;
538 @brief Build a SELECT list.
539 @param state Pointer to the query-building context.
540 @param item Pointer to the first in a linked list of SELECT items.
542 static void buildSelectList( BuildSQLState* state, const SelectItem* item ) {
547 buffer_add_char( state->sql, ',' );
548 add_newline( state );
549 buildExpression( state, item->expression );
551 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
556 if( item->column_alias ) {
557 buffer_add( state->sql, " AS \"" );
558 buffer_add( state->sql, item->column_alias );
559 buffer_add_char( state->sql, '\"' );
564 buffer_add_char( state->sql, ' ' );
568 @brief Add a GROUP BY clause, if there is one, to the current query.
569 @param state Pointer to the query-building context.
570 @param sel_list Pointer to the first node in a linked list of SelectItems
572 We reference the GROUP BY items by number, not by repeating the expressions.
574 static void buildGroupBy( BuildSQLState* state, const SelectItem* sel_list ) {
575 int seq = 0; // Sequence number of current SelectItem
576 int first = 1; // Boolean: true for the first GROUPed BY item
580 if( sel_list->grouped_by ) {
582 add_newline( state );
583 buffer_add( state->sql, "GROUP BY " );
587 buffer_add( state->sql, ", " );
589 buffer_fadd( state->sql, "%d", seq );
592 sel_list = sel_list->next;
597 @brief Add an ORDER BY clause to the current query.
598 @param state Pointer to the query-building context.
599 @param ord_list Pointer to the first node in a linked list of OrderItems.
601 static void buildOrderBy( BuildSQLState* state, const OrderItem* ord_list ) {
602 add_newline( state );
603 buffer_add( state->sql, "ORDER BY" );
604 incr_indent( state );
606 int first = 1; // boolean
611 buffer_add_char( state->sql, ',' );
612 add_newline( state );
613 buildExpression( state, ord_list->expression );
615 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
619 ord_list = ord_list->next;
622 decr_indent( state );
627 @brief Build an arbitrary expression.
628 @param state Pointer to the query-building context.
629 @param expr Pointer to the Expression representing the expression to be built.
631 static void buildExpression( BuildSQLState* state, const Expression* expr ) {
633 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
634 "Internal error: NULL pointer to Expression" ));
639 if( expr->parenthesize )
640 buffer_add_char( state->sql, '(' );
642 switch( expr->type ) {
645 buffer_add( state->sql, "NOT " );
647 buildExpression( state, expr->left_operand );
649 sqlAddMsg( state, "Unable to emit left operand in BETWEEN expression # %d",
654 buffer_add( state->sql, " BETWEEN " );
656 buildExpression( state, expr->subexp_list );
658 sqlAddMsg( state, "Unable to emit lower limit in BETWEEN expression # %d",
663 buffer_add( state->sql, " AND " );
665 buildExpression( state, expr->subexp_list->next );
667 sqlAddMsg( state, "Unable to emit upper limit in BETWEEN expression # %d",
674 if( !expr->bind ) { // Sanity check
675 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
676 "Internal error: no variable for bind variable expression" ));
679 buildBindVar( state, expr->bind );
683 buffer_add( state->sql, "NOT " );
685 if( expr->literal ) {
686 buffer_add( state->sql, expr->literal );
687 buffer_add_char( state->sql, ' ' );
689 buffer_add( state->sql, "FALSE " );
692 buildCase( state, expr );
694 sqlAddMsg( state, "Unable to build CASE expression # %d", expr->id );
697 case EXP_CAST : // Type cast
699 buffer_add( state->sql, "NOT " );
701 buffer_add( state->sql, "CAST (" );
702 buildExpression( state, expr->left_operand );
704 sqlAddMsg( state, "Unable to build left operand for CAST expression # %d",
707 buffer_add( state->sql, " AS " );
708 if( expr->cast_type && expr->cast_type->datatype_name ) {
709 buffer_add( state->sql, expr->cast_type->datatype_name );
710 buffer_add_char( state->sql, ')' );
712 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
713 "No datatype available for CAST expression # %d", expr->id ));
718 case EXP_COLUMN : // Table column
720 buffer_add( state->sql, "NOT " );
722 if( expr->table_alias ) {
723 buffer_add_char( state->sql, '\"' );
724 buffer_add( state->sql, expr->table_alias );
725 buffer_add( state->sql, "\"." );
727 if( expr->column_name ) {
728 buffer_add( state->sql, expr->column_name );
730 buffer_add_char( state->sql, '*' );
734 if( !expr->subquery ) {
735 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
736 "No subquery found for EXIST expression # %d", expr->id ));
740 buffer_add( state->sql, "NOT " );
742 buffer_add( state->sql, "EXISTS (" );
743 incr_indent( state );
744 build_Query( state, expr->subquery );
745 decr_indent( state );
746 add_newline( state );
747 buffer_add_char( state->sql, ')' );
751 buildFunction( state, expr );
754 if( expr->left_operand ) {
755 buildExpression( state, expr->left_operand );
756 if( !state->error ) {
758 buffer_add( state->sql, "NOT " );
759 buffer_add( state->sql, " IN (" );
761 if( expr->subquery ) {
762 incr_indent( state );
763 build_Query( state, expr->subquery );
765 sqlAddMsg( state, "Unable to build subquery for IN condition" );
767 decr_indent( state );
768 add_newline( state );
769 buffer_add_char( state->sql, ')' );
772 buildSeries( state, expr->subexp_list, NULL );
774 sqlAddMsg( state, "Unable to build IN list" );
776 buffer_add_char( state->sql, ')' );
782 if( expr->left_operand ) {
783 buildExpression( state, expr->left_operand );
785 sqlAddMsg( state, "Unable to emit left operand in IS NULL expression # %d",
792 buffer_add( state->sql, " IS NOT NULL" );
794 buffer_add( state->sql, " IS NULL" );
798 buffer_add( state->sql, "NOT " );
800 buffer_add( state->sql, "NULL" );
802 case EXP_NUMBER : // Numeric literal
803 if( !expr->literal ) {
804 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
805 "Internal error: No numeric value in string expression # %d", expr->id ));
808 buffer_add( state->sql, expr->literal );
813 buffer_add( state->sql, "NOT (" );
815 if( expr->left_operand ) {
816 buildExpression( state, expr->left_operand );
818 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
823 buffer_add_char( state->sql, ' ' );
824 buffer_add( state->sql, expr->op );
825 buffer_add_char( state->sql, ' ' );
826 if( expr->right_operand ) {
827 buildExpression( state, expr->right_operand );
829 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
836 buffer_add_char( state->sql, ')' );
841 buffer_add( state->sql, "NOT (" );
843 buildSeries( state, expr->subexp_list, expr->op );
845 sqlAddMsg( state, "Unable to build series expression using operator \"%s\"",
846 expr->op ? expr->op : "," );
849 buffer_add_char( state->sql, ')' );
852 case EXP_STRING : // String literal
853 if( !expr->literal ) {
854 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
855 "Internal error: No string value in string expression # %d", expr->id ));
858 char* str = strdup( expr->literal );
859 dbi_conn_quote_string( state->dbhandle, &str );
861 buffer_add( state->sql, str );
864 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
865 "Unable to format string literal \"%s\" for expression # %d",
866 expr->literal, expr->id ));
873 buffer_add( state->sql, "NOT " );
875 if( expr->subquery ) {
876 buffer_add_char( state->sql, '(' );
877 incr_indent( state );
878 build_Query( state, expr->subquery );
879 decr_indent( state );
880 add_newline( state );
881 buffer_add_char( state->sql, ')' );
883 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
884 "Internal error: No subquery in subquery expression # %d", expr->id ));
890 if( expr->parenthesize )
891 buffer_add_char( state->sql, ')' );
895 @brief Build a CASE expression.
896 @param state Pointer to the query-building context.
897 @param exp Pointer to an Expression representing a CASE expression.
899 static void buildCase( BuildSQLState* state, const Expression* expr ) {
901 if( ! expr->left_operand ) {
902 sqlAddMsg( state, "CASE expression # %d has no left operand", expr->id );
905 } else if( ! expr->branch_list ) {
906 sqlAddMsg( state, "CASE expression # %d has no branches", expr->id );
912 buffer_add( state->sql, "NOT (" );
914 // left_operand is the expression on which we shall branch
915 buffer_add( state->sql, "CASE " );
916 buildExpression( state, expr->left_operand );
918 sqlAddMsg( state, "Unable to build operand of CASE expression # %d", expr->id );
922 incr_indent( state );
924 // Emit each branch in turn
925 CaseBranch* branch = expr->branch_list;
927 add_newline( state );
929 if( branch->condition ) {
930 // Emit a WHEN condition
931 buffer_add( state->sql, "WHEN " );
932 buildExpression( state, branch->condition );
933 incr_indent( state );
934 add_newline( state );
935 buffer_add( state->sql, "THEN " );
938 buffer_add( state->sql, "ELSE " );
939 incr_indent( state );
940 add_newline( state );
943 // Emit the THEN expression
944 buildExpression( state, branch->result );
945 decr_indent( state );
947 branch = branch->next;
950 decr_indent( state );
951 add_newline( state );
952 buffer_add( state->sql, "END" );
955 buffer_add( state->sql, ")" );
959 @brief Build a function call, with a subfield if specified.
960 @param state Pointer to the query-building context.
961 @param exp Pointer to an Expression representing a function call.
963 static void buildFunction( BuildSQLState* state, const Expression* expr ) {
965 buffer_add( state->sql, "NOT " );
967 // If a subfield is specified, the function call
968 // needs an extra layer of parentheses
969 if( expr->column_name )
970 buffer_add_char( state->sql, '(' );
972 // First, check for some specific functions with peculiar syntax, and treat them
973 // as special exceptions. We rely on the input side to ensure that the function
974 // name is available.
975 if( !strcasecmp( expr->function_name, "EXTRACT" ))
976 buildExtract( state, expr );
977 else if( !strcasecmp( expr->function_name, "CURRENT_DATE" ) && ! expr->subexp_list )
978 buffer_add( state->sql, "CURRENT_DATE " );
979 else if( !strcasecmp( expr->function_name, "CURRENT_TIME" ) && ! expr->subexp_list )
980 buffer_add( state->sql, "CURRENT_TIME " );
981 else if( !strcasecmp( expr->function_name, "CURRENT_TIMESTAMP" ) && ! expr->subexp_list )
982 buffer_add( state->sql, "CURRENT_TIMESTAMP " );
983 else if( !strcasecmp( expr->function_name, "LOCALTIME" ) && ! expr->subexp_list )
984 buffer_add( state->sql, "LOCALTIME " );
985 else if( !strcasecmp( expr->function_name, "LOCALTIMESTAMP" ) && ! expr->subexp_list )
986 buffer_add( state->sql, "LOCALTIMESTAMP " );
987 else if( !strcasecmp( expr->function_name, "TRIM" )) {
988 int arg_count = subexp_count( expr );
990 if( (arg_count != 2 && arg_count != 3 ) || expr->subexp_list->type != EXP_STRING )
991 buildTypicalFunction( state, expr );
994 "TRIM function not supported in expr # %d; use ltrim() and/or rtrim()",
1000 buildTypicalFunction( state, expr ); // Not a special exception.
1002 if( expr->column_name ) {
1003 // Add the name of the subfield
1004 buffer_add( state->sql, ").\"" );
1005 buffer_add( state->sql, expr->column_name );
1006 buffer_add_char( state->sql, '\"' );
1011 @brief Count the number of subexpressions attached to a given Expression.
1012 @param expr Pointer to the Expression whose subexpressions are to be counted.
1013 @return The number of subexpressions.
1015 static int subexp_count( const Expression* expr ) {
1020 const Expression* sub = expr->subexp_list;
1029 @brief Build an ordinary function call, i.e. one with no special syntax,
1030 @param state Pointer to the query-building context.
1031 @param exp Pointer to an Expression representing a function call.
1033 Emit the parameters as a comma-separated list of expressions.
1035 static void buildTypicalFunction( BuildSQLState* state, const Expression* expr ) {
1036 buffer_add( state->sql, expr->function_name );
1037 buffer_add_char( state->sql, '(' );
1039 // Add the parameters, if any
1040 buildSeries( state, expr->subexp_list, NULL );
1042 buffer_add_char( state->sql, ')' );
1046 @brief Build a call to the EXTRACT function, with its peculiar syntax.
1047 @param state Pointer to the query-building context.
1048 @param exp Pointer to an Expression representing an EXTRACT call.
1050 If there are not exactly two parameters, or if the first parameter is not a string,
1051 then assume it is an ordinary function overloading on the same name. We don't try to
1052 check the type of the second parameter. Hence it is possible for a legitimately
1053 overloaded function to be uncallable.
1055 The first parameter of EXTRACT() must be one of a short list of names for some fragment
1056 of a date or time. Here we accept that parameter in the form of a string. We don't
1057 surround it with quotes in the output, although PostgreSQL wouldn't mind if we did.
1059 static void buildExtract( BuildSQLState* state, const Expression* expr ) {
1061 const Expression* arg = expr->subexp_list;
1063 // See if this is the special form of EXTRACT(), so far as we can tell
1064 if( subexp_count( expr ) != 2 || arg->type != EXP_STRING ) {
1065 buildTypicalFunction( state, expr );
1068 // check the first argument against a list of valid values
1069 if( strcasecmp( arg->literal, "century" )
1070 && strcasecmp( arg->literal, "day" )
1071 && strcasecmp( arg->literal, "decade" )
1072 && strcasecmp( arg->literal, "dow" )
1073 && strcasecmp( arg->literal, "doy" )
1074 && strcasecmp( arg->literal, "epoch" )
1075 && strcasecmp( arg->literal, "hour" )
1076 && strcasecmp( arg->literal, "isodow" )
1077 && strcasecmp( arg->literal, "isoyear" )
1078 && strcasecmp( arg->literal, "microseconds" )
1079 && strcasecmp( arg->literal, "millennium" )
1080 && strcasecmp( arg->literal, "milliseconds" )
1081 && strcasecmp( arg->literal, "minute" )
1082 && strcasecmp( arg->literal, "month" )
1083 && strcasecmp( arg->literal, "quarter" )
1084 && strcasecmp( arg->literal, "second" )
1085 && strcasecmp( arg->literal, "timezone" )
1086 && strcasecmp( arg->literal, "timezone_hour" )
1087 && strcasecmp( arg->literal, "timezone_minute" )
1088 && strcasecmp( arg->literal, "week" )
1089 && strcasecmp( arg->literal, "year" )) {
1090 // This *could* be an ordinary function, overloading on the name. However it's
1091 // more likely that the user misspelled one of the names expected by EXTRACT().
1093 "Invalid name \"%s\" as EXTRACT argument in expression # %d",
1094 expr->literal, expr->id );
1099 buffer_add( state->sql, "EXTRACT(" );
1100 buffer_add( state->sql, arg->literal );
1101 buffer_add( state->sql, " FROM " );
1106 "Only one argument supplied to EXTRACT function in expression # %d", expr->id );
1111 // The second parameter must be of type timestamp, time, or interval. We don't have
1112 // a good way of checking it here, so we rely on PostgreSQL to complain if necessary.
1113 buildExpression( state, arg );
1114 buffer_add_char( state->sql, ')' );
1118 @brief Build a series of expressions separated by a specified operator, or by commas.
1119 @param state Pointer to the query-building context.
1120 @param subexp_list Pointer to the first Expression in a linked list.
1121 @param op Pointer to the operator, or NULL for commas.
1123 If the operator is AND or OR (in upper, lower, or mixed case), the second and all
1124 subsequent operators will begin on a new line.
1126 static void buildSeries( BuildSQLState* state, const Expression* subexp_list, const char* op ) {
1129 return; // List is empty
1131 int comma = 0; // Boolean; true if separator is a comma
1132 int newline_needed = 0; // Boolean; true if operator is AND or OR
1137 } else if( !strcmp( op, "," ))
1139 else if( !strcasecmp( op, "AND" ) || !strcasecmp( op, "OR" ))
1142 int first = 1; // Boolean; true for first item in list
1143 while( subexp_list ) {
1145 first = 0; // No separator needed yet
1147 // Insert a separator
1149 buffer_add( state->sql, ", " );
1151 if( newline_needed )
1152 add_newline( state );
1154 buffer_add_char( state->sql, ' ' );
1156 buffer_add( state->sql, op );
1157 buffer_add_char( state->sql, ' ' );
1161 buildExpression( state, subexp_list );
1162 subexp_list = subexp_list->next;
1167 @brief Add the value of a bind variable to an SQL statement.
1168 @param state Pointer to the query-building context.
1169 @param bind Pointer to the bind variable whose value is to be added to the SQL.
1171 The value may be a null, a scalar, or an array of nulls and/or scalars, depending on
1172 the type of the bind variable.
1174 static void buildBindVar( BuildSQLState* state, const BindVar* bind ) {
1176 // Decide where to get the value, if any
1177 const jsonObject* value = NULL;
1178 if( bind->actual_value )
1179 value = bind->actual_value;
1180 else if( bind->default_value ) {
1181 if( state->defaults_usable )
1182 value = bind->default_value;
1184 sqlAddMsg( state, "No confirmed value available for bind variable \"%s\"",
1189 } else if( state->values_required ) {
1190 sqlAddMsg( state, "No value available for bind variable \"%s\"", bind->name );
1194 // No value available, and that's okay. Emit the name of the bind variable.
1195 buffer_add_char( state->sql, ':' );
1196 buffer_add( state->sql, bind->name );
1200 // If we get to this point, we know that a value is available. Carry on.
1202 int numeric = 0; // Boolean
1203 if( BIND_NUM == bind->type || BIND_NUM_LIST == bind->type )
1207 switch( bind->type ) {
1210 buildScalar( state, numeric, value );
1212 case BIND_STR_LIST :
1213 case BIND_NUM_LIST :
1214 if( JSON_ARRAY == value->type ) {
1215 // Iterate over array, emit each value
1216 int first = 1; // Boolean
1217 unsigned long max = value->size;
1218 unsigned long i = 0;
1223 buffer_add( state->sql, ", " );
1225 buildScalar( state, numeric, jsonObjectGetIndex( value, i ));
1229 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1230 "Invalid value for bind variable; expected a list of values" ));
1235 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1236 "Internal error: invalid type for bind variable" ));
1242 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1243 "Unable to emit value of bind variable \"%s\"", bind->name ));
1247 @brief Add a number or quoted string to an SQL statement.
1248 @param state Pointer to the query-building context.
1249 @param numeric Boolean; true if the value is expected to be a number
1250 @param obj Pointer to the jsonObject whose value is to be added to the SQL.
1252 static void buildScalar( BuildSQLState* state, int numeric, const jsonObject* obj ) {
1253 switch( obj->type ) {
1255 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1256 "Internal error: hash value for bind variable" ));
1260 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1261 "Internal error: array value for bind variable" ));
1267 "Invalid value for bind variable: expected a string, found a number" );
1270 char* str = jsonObjectToSimpleString( obj );
1271 dbi_conn_quote_string( state->dbhandle, &str );
1273 buffer_add( state->sql, str );
1276 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
1277 "Unable to format string literal \"%s\" for bind variable",
1278 jsonObjectGetString( obj )));
1285 buffer_add( state->sql, jsonObjectGetString( obj ));
1288 "Invalid value for bind variable: expected a number, found a string" );
1293 buffer_add( state->sql, "NULL" );
1296 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1297 "Internal error: boolean value for bind variable" ));
1301 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
1302 "Internal error: corrupted value for bind variable" ));
1309 @brief Start a new line in the output, with the current level of indentation.
1310 @param state Pointer to the query-building context.
1312 static void add_newline( BuildSQLState* state ) {
1313 buffer_add_char( state->sql, '\n' );
1316 static const char blanks[] = " "; // 32 blanks
1317 static const size_t maxlen = sizeof( blanks ) - 1;
1318 const int blanks_per_level = 3;
1319 int n = state->indent * blanks_per_level;
1321 size_t len = n >= maxlen ? maxlen : n;
1322 buffer_add_n( state->sql, blanks, len );
1328 @brief Increase the degree of indentation.
1329 @param state Pointer to the query-building context.
1331 static inline void incr_indent( BuildSQLState* state ) {
1336 @brief Reduce the degree of indentation.
1337 @param state Pointer to the query-building context.
1339 static inline void decr_indent( BuildSQLState* state ) {