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 "openils/oils_buildq.h"
14 static void buildQuery( BuildSQLState* state, StoredQ* query );
15 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str );
16 static void buildSelect( BuildSQLState* state, StoredQ* query );
17 static void buildFrom( BuildSQLState* state, FromRelation* core_from );
18 static void buildJoin( BuildSQLState* state, FromRelation* join );
19 static void buildSelectList( BuildSQLState* state, SelectItem* item );
20 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list );
21 static void buildExpression( BuildSQLState* state, Expression* expr );
23 static void add_newline( BuildSQLState* state );
24 static inline void incr_indent( BuildSQLState* state );
25 static inline void decr_indent( BuildSQLState* state );
28 @brief Build an SQL query.
29 @param state Pointer to the query-building context.
30 @param query Pointer to the query to be built.
31 @return Zero if successful, or 1 if not.
33 Clear the output buffer, call buildQuery() to do the work, and add a closing semicolon.
35 int buildSQL( BuildSQLState* state, StoredQ* query ) {
37 buffer_reset( state->sql );
39 buildQuery( state, query );
40 if( ! state->error ) {
41 // Remove the trailing space, if there is one, and add a semicolon.
42 char c = buffer_chomp( state->sql );
44 buffer_add_char( state->sql, c ); // oops, not a space; put it back
45 buffer_add( state->sql, ";\n" );
51 @brief Build an SQL query, appending it to what has been built so far.
52 @param state Pointer to the query-building context.
53 @param query Pointer to the query to be built.
55 Look at the query type and branch to the corresponding routine.
57 static void buildQuery( BuildSQLState* state, StoredQ* query ) {
58 if( buffer_length( state->sql ))
61 switch( query->type ) {
63 buildSelect( state, query );
66 buildCombo( state, query, "UNION" );
69 buildCombo( state, query, "INTERSECT" );
72 buildCombo( state, query, "EXCEPT" );
75 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
76 "Internal error: invalid query type %d in query # %d",
77 query->type, query->id ));
84 @brief Build a UNION, INTERSECT, or EXCEPT query.
85 @param state Pointer to the query-building context.
86 @param query Pointer to the query to be built.
87 @param type_str The query type, as a string.
89 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
91 QSeq* seq = query->child_list;
93 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
94 "Internal error: No child queries within %s query # %d",
95 type_str, query->id ));
100 // Traverse the list of child queries
102 buildQuery( state, seq->child_query );
104 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
105 seq->child_query->id, type_str, query->id );
110 add_newline( state );
111 buffer_add( state->sql, type_str );
112 buffer_add_char( state->sql, ' ' );
114 buffer_add( state->sql, "ALL " );
122 @brief Build a SELECT statement.
123 @param state Pointer to the query-building context.
124 @param query Pointer to the StoredQ structure that represents the query.
126 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
128 FromRelation* from_clause = query->from_clause;
130 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
135 // To do: get SELECT list; just a stub here
136 buffer_add( state->sql, "SELECT" );
137 incr_indent( state );
138 buildSelectList( state, query->select_list );
140 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
144 decr_indent( state );
146 // Build FROM clause, if there is one
147 if( query->from_clause ) {
148 buildFrom( state, query->from_clause );
150 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
156 // Build WHERE clause, if there is one
157 if( query->where_clause ) {
158 add_newline( state );
159 buffer_add( state->sql, "WHERE" );
160 incr_indent( state );
161 add_newline( state );
162 buildExpression( state, query->where_clause );
164 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
169 //buffer_add_char( state->sql, ' ' );
170 decr_indent( state );
173 // Build WHERE clause, if there is one
174 if( query->order_by_list ) {
175 buildOrderBy( state, query->order_by_list );
177 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
187 @brief Build a FROM clause.
188 @param Pointer to the query-building context.
189 @param Pointer to the StoredQ query to which the FROM clause belongs.
191 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
193 add_newline( state );
194 buffer_add( state->sql, "FROM" );
195 incr_indent( state );
196 add_newline( state );
198 switch( core_from->type ) {
200 if( ! core_from->table_name ) {
201 // To do: if class is available, look up table name
202 // or source_definition in the IDL
203 sqlAddMsg( state, "No table or view name available for core relation # %d",
210 buffer_add( state->sql, core_from->table_name );
213 buffer_add_char( state->sql, '(' );
214 incr_indent( state );
215 buildQuery( state, core_from->subquery );
216 decr_indent( state );
217 add_newline( state );
218 buffer_add_char( state->sql, ')' );
221 sqlAddMsg( state, "Functions in FROM clause not yet supported" );
226 // Add a table alias, if possible
227 if( core_from->table_alias ) {
228 buffer_add( state->sql, " AS \"" );
229 buffer_add( state->sql, core_from->table_alias );
230 buffer_add( state->sql, "\" " );
232 else if( core_from->class_name ) {
233 buffer_add( state->sql, " AS \"" );
234 buffer_add( state->sql, core_from->class_name );
235 buffer_add( state->sql, "\" " );
237 buffer_add_char( state->sql, ' ' );
239 incr_indent( state );
240 FromRelation* join = core_from->join_list;
242 buildJoin( state, join );
244 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
250 decr_indent( state );
251 decr_indent( state );
254 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
255 add_newline( state );
256 switch( join->join_type ) {
258 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
262 buffer_add( state->sql, "INNER JOIN " );
265 buffer_add( state->sql, "LEFT JOIN " );
268 buffer_add( state->sql, "RIGHT JOIN " );
271 buffer_add( state->sql, "FULL JOIN " );
274 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
279 switch( join->type ) {
282 if( !join->table_name || ! *join->table_name ) {
283 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
287 buffer_add( state->sql, join->table_name );
291 if( !join->subquery ) {
292 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
295 } else if( !join->table_alias ) {
296 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
301 buffer_add_char( state->sql, '(' );
302 incr_indent( state );
303 buildQuery( state, join->subquery );
304 decr_indent( state );
305 add_newline( state );
306 buffer_add_char( state->sql, ')' );
309 if( !join->table_name || ! *join->table_name ) {
310 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
318 const char* effective_alias = join->table_alias;
319 if( !effective_alias )
320 effective_alias = join->class_name;
322 if( effective_alias ) {
323 buffer_add( state->sql, " AS \"" );
324 buffer_add( state->sql, effective_alias );
325 buffer_add_char( state->sql, '\"' );
328 if( join->on_clause ) {
329 incr_indent( state );
330 add_newline( state );
331 buffer_add( state->sql, "ON " );
332 buildExpression( state, join->on_clause );
333 decr_indent( state );
336 FromRelation* subjoin = join->join_list;
338 buildJoin( state, subjoin );
340 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
343 subjoin = subjoin->next;
347 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
352 buffer_add_char( state->sql, ',' );
353 add_newline( state );
354 buildExpression( state, item->expression );
356 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
361 if( item->column_alias ) {
362 buffer_add( state->sql, " AS \"" );
363 buffer_add( state->sql, item->column_alias );
364 buffer_add_char( state->sql, '\"' );
369 buffer_add_char( state->sql, ' ' );
373 @brief Add an ORDER BY clause to the current query.
374 @param state Pointer to the query-building context.
375 @param ord_list Pointer to the first node in a linked list of OrderItems.
377 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
378 add_newline( state );
379 buffer_add( state->sql, "ORDER BY" );
380 incr_indent( state );
382 int first = 1; // boolean
387 buffer_add_char( state->sql, ',' );
388 add_newline( state );
389 buildExpression( state, ord_list->expression );
391 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
395 ord_list = ord_list->next;
398 decr_indent( state );
403 @brief Build an arbitrary expression.
404 @param state Pointer to the query-building context.
405 @param expr Pointer to the Expression representing the expression to be built.
407 static void buildExpression( BuildSQLState* state, Expression* expr ) {
409 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
410 "Internal error: NULL pointer to Expression" ));
415 if( expr->parenthesize )
416 buffer_add_char( state->sql, '(' );
418 switch( expr->type ) {
420 sqlAddMsg( state, "BETWEEN expressions not yet supported" );
424 if( expr->literal ) {
425 buffer_add( state->sql, expr->literal );
426 buffer_add_char( state->sql, ' ' );
428 buffer_add( state->sql, "FALSE " );
431 sqlAddMsg( state, "CASE expressions not yet supported" );
434 case EXP_CAST : // Type cast
435 sqlAddMsg( state, "Cast expressions not yet supported" );
438 case EXP_COLUMN : // Table column
439 if( expr->table_alias ) {
440 buffer_add_char( state->sql, '\"' );
441 buffer_add( state->sql, expr->table_alias );
442 buffer_add( state->sql, "\"." );
444 if( expr->column_name ) {
445 buffer_add( state->sql, expr->column_name );
447 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
448 "Column name not present in expression # %d", expr->id ));
453 if( !expr->subquery ) {
454 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
455 "No subquery found for EXIST expression # %d", expr->id ));
458 buffer_add( state->sql, "EXISTS (" );
459 incr_indent( state );
460 buildQuery( state, expr->subquery );
461 decr_indent( state );
462 add_newline( state );
463 buffer_add_char( state->sql, ')' );
468 sqlAddMsg( state, "Expression type not yet supported" );
472 if( expr->left_operand ) {
473 buildExpression( state, expr->left_operand );
474 if( !state->error ) {
475 if( expr->subquery ) {
476 buffer_add( state->sql, " IN (" );
477 incr_indent( state );
478 buildQuery( state, expr->subquery );
479 decr_indent( state );
480 add_newline( state );
481 buffer_add_char( state->sql, ')' );
483 sqlAddMsg( state, "IN lists not yet supported" );
489 case EXP_NOT_BETWEEN :
492 sqlAddMsg( state, "Expression type not yet supported" );
496 buffer_add( state->sql, "NULL" );
498 case EXP_NUMBER : // Numeric literal
499 if( !expr->literal ) {
500 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
501 "Internal error: No numeric value in string expression # %d", expr->id ));
504 buffer_add( state->sql, expr->literal );
508 if( expr->left_operand ) {
509 buildExpression( state, expr->left_operand );
511 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
516 buffer_add_char( state->sql, ' ' );
517 buffer_add( state->sql, expr->op );
518 buffer_add_char( state->sql, ' ' );
519 if( expr->right_operand ) {
520 buildExpression( state, expr->right_operand );
522 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
528 case EXP_STRING : // String literal
529 if( !expr->literal ) {
530 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
531 "Internal error: No string value in string expression # %d", expr->id ));
534 buffer_add_char( state->sql, '\'' );
535 buffer_add( state->sql, expr->literal );
536 buffer_add_char( state->sql, '\'' );
540 if( expr->subquery ) {
541 buffer_add_char( state->sql, '(' );
542 incr_indent( state );
543 buildQuery( state, expr->subquery );
544 decr_indent( state );
545 add_newline( state );
546 buffer_add_char( state->sql, ')' );
548 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
549 "Internal error: No subquery in subquery expression # %d", expr->id ));
555 if( expr->parenthesize )
556 buffer_add_char( state->sql, ')' );
559 static void add_newline( BuildSQLState* state ) {
560 buffer_add_char( state->sql, '\n' );
563 static const char blanks[] = " "; // 32 blanks
564 static const size_t maxlen = sizeof( blanks ) - 1;
565 const int blanks_per_level = 3;
566 int n = state->indent * blanks_per_level;
568 size_t len = n >= maxlen ? maxlen : n;
569 buffer_add_n( state->sql, blanks, len );
574 static inline void incr_indent( BuildSQLState* state ) {
578 static inline void decr_indent( BuildSQLState* state ) {