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_application.h"
13 #include "openils/oils_idl.h"
14 #include "openils/oils_sql.h"
15 #include "openils/oils_buildq.h"
17 static void build_Query( BuildSQLState* state, StoredQ* query );
18 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str );
19 static void buildSelect( BuildSQLState* state, StoredQ* query );
20 static void buildFrom( BuildSQLState* state, FromRelation* core_from );
21 static void buildJoin( BuildSQLState* state, FromRelation* join );
22 static void buildSelectList( BuildSQLState* state, SelectItem* item );
23 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list );
24 static void buildExpression( BuildSQLState* state, Expression* expr );
26 static void add_newline( BuildSQLState* state );
27 static inline void incr_indent( BuildSQLState* state );
28 static inline void decr_indent( BuildSQLState* state );
31 @brief Build an SQL query.
32 @param state Pointer to the query-building context.
33 @param query Pointer to the query to be built.
34 @return Zero if successful, or 1 if not.
36 Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
38 int buildSQL( BuildSQLState* state, StoredQ* query ) {
40 buffer_reset( state->sql );
42 build_Query( state, query );
43 if( ! state->error ) {
44 // Remove the trailing space, if there is one, and add a semicolon.
45 char c = buffer_chomp( state->sql );
47 buffer_add_char( state->sql, c ); // oops, not a space; put it back
48 buffer_add( state->sql, ";\n" );
54 @brief Build an SQL query, appending it to what has been built so far.
55 @param state Pointer to the query-building context.
56 @param query Pointer to the query to be built.
58 Look at the query type and branch to the corresponding routine.
60 static void build_Query( BuildSQLState* state, StoredQ* query ) {
61 if( buffer_length( state->sql ))
64 switch( query->type ) {
66 buildSelect( state, query );
69 buildCombo( state, query, "UNION" );
72 buildCombo( state, query, "INTERSECT" );
75 buildCombo( state, query, "EXCEPT" );
78 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
79 "Internal error: invalid query type %d in query # %d",
80 query->type, query->id ));
87 @brief Build a UNION, INTERSECT, or EXCEPT query.
88 @param state Pointer to the query-building context.
89 @param query Pointer to the query to be built.
90 @param type_str The query type, as a string.
92 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
94 QSeq* seq = query->child_list;
96 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
97 "Internal error: No child queries within %s query # %d",
98 type_str, query->id ));
103 // Traverse the list of child queries
105 build_Query( state, seq->child_query );
107 sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
108 seq->child_query->id, type_str, query->id );
113 add_newline( state );
114 buffer_add( state->sql, type_str );
115 buffer_add_char( state->sql, ' ' );
117 buffer_add( state->sql, "ALL " );
125 @brief Build a SELECT statement.
126 @param state Pointer to the query-building context.
127 @param query Pointer to the StoredQ structure that represents the query.
129 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
131 FromRelation* from_clause = query->from_clause;
133 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
138 // To do: get SELECT list; just a stub here
139 buffer_add( state->sql, "SELECT" );
140 incr_indent( state );
141 buildSelectList( state, query->select_list );
143 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
147 decr_indent( state );
149 // Build FROM clause, if there is one
150 if( query->from_clause ) {
151 buildFrom( state, query->from_clause );
153 sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
159 // Build WHERE clause, if there is one
160 if( query->where_clause ) {
161 add_newline( state );
162 buffer_add( state->sql, "WHERE" );
163 incr_indent( state );
164 add_newline( state );
165 buildExpression( state, query->where_clause );
167 sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
172 //buffer_add_char( state->sql, ' ' );
173 decr_indent( state );
176 // Build WHERE clause, if there is one
177 if( query->order_by_list ) {
178 buildOrderBy( state, query->order_by_list );
180 sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
190 @brief Build a FROM clause.
191 @param Pointer to the query-building context.
192 @param Pointer to the StoredQ query to which the FROM clause belongs.
194 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
196 add_newline( state );
197 buffer_add( state->sql, "FROM" );
198 incr_indent( state );
199 add_newline( state );
201 switch( core_from->type ) {
202 case FRT_RELATION : {
203 char* relation = core_from->table_name;
205 if( !core_from->class_name ) {
206 sqlAddMsg( state, "No relation specified for core relation # %d",
212 // Look up table name, view name, or source_definition in the IDL
213 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
214 relation = oilsGetRelation( class_hash );
218 buffer_add( state->sql, relation );
219 if( !core_from->table_name )
220 free( relation ); // In this case we strdup'd it, must free it
224 buffer_add_char( state->sql, '(' );
225 incr_indent( state );
226 build_Query( state, core_from->subquery );
227 decr_indent( state );
228 add_newline( state );
229 buffer_add_char( state->sql, ')' );
232 sqlAddMsg( state, "Functions in FROM clause not yet supported" );
237 // Add a table alias, if possible
238 if( core_from->table_alias ) {
239 buffer_add( state->sql, " AS \"" );
240 buffer_add( state->sql, core_from->table_alias );
241 buffer_add( state->sql, "\" " );
243 else if( core_from->class_name ) {
244 buffer_add( state->sql, " AS \"" );
245 buffer_add( state->sql, core_from->class_name );
246 buffer_add( state->sql, "\" " );
248 buffer_add_char( state->sql, ' ' );
250 incr_indent( state );
251 FromRelation* join = core_from->join_list;
253 buildJoin( state, join );
255 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
261 decr_indent( state );
262 decr_indent( state );
265 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
266 add_newline( state );
267 switch( join->join_type ) {
269 sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
273 buffer_add( state->sql, "INNER JOIN " );
276 buffer_add( state->sql, "LEFT JOIN " );
279 buffer_add( state->sql, "RIGHT JOIN " );
282 buffer_add( state->sql, "FULL JOIN " );
285 sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
290 switch( join->type ) {
293 if( !join->table_name || ! *join->table_name ) {
294 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
298 buffer_add( state->sql, join->table_name );
302 if( !join->subquery ) {
303 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
306 } else if( !join->table_alias ) {
307 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
312 buffer_add_char( state->sql, '(' );
313 incr_indent( state );
314 build_Query( state, join->subquery );
315 decr_indent( state );
316 add_newline( state );
317 buffer_add_char( state->sql, ')' );
320 if( !join->table_name || ! *join->table_name ) {
321 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
329 const char* effective_alias = join->table_alias;
330 if( !effective_alias )
331 effective_alias = join->class_name;
333 if( effective_alias ) {
334 buffer_add( state->sql, " AS \"" );
335 buffer_add( state->sql, effective_alias );
336 buffer_add_char( state->sql, '\"' );
339 if( join->on_clause ) {
340 incr_indent( state );
341 add_newline( state );
342 buffer_add( state->sql, "ON " );
343 buildExpression( state, join->on_clause );
344 decr_indent( state );
347 FromRelation* subjoin = join->join_list;
349 buildJoin( state, subjoin );
351 sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
354 subjoin = subjoin->next;
358 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
363 buffer_add_char( state->sql, ',' );
364 add_newline( state );
365 buildExpression( state, item->expression );
367 sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
372 if( item->column_alias ) {
373 buffer_add( state->sql, " AS \"" );
374 buffer_add( state->sql, item->column_alias );
375 buffer_add_char( state->sql, '\"' );
380 buffer_add_char( state->sql, ' ' );
384 @brief Add an ORDER BY clause to the current query.
385 @param state Pointer to the query-building context.
386 @param ord_list Pointer to the first node in a linked list of OrderItems.
388 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
389 add_newline( state );
390 buffer_add( state->sql, "ORDER BY" );
391 incr_indent( state );
393 int first = 1; // boolean
398 buffer_add_char( state->sql, ',' );
399 add_newline( state );
400 buildExpression( state, ord_list->expression );
402 sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
406 ord_list = ord_list->next;
409 decr_indent( state );
414 @brief Build an arbitrary expression.
415 @param state Pointer to the query-building context.
416 @param expr Pointer to the Expression representing the expression to be built.
418 static void buildExpression( BuildSQLState* state, Expression* expr ) {
420 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
421 "Internal error: NULL pointer to Expression" ));
426 if( expr->parenthesize )
427 buffer_add_char( state->sql, '(' );
429 switch( expr->type ) {
431 sqlAddMsg( state, "BETWEEN expressions not yet supported" );
435 if( expr->literal ) {
436 buffer_add( state->sql, expr->literal );
437 buffer_add_char( state->sql, ' ' );
439 buffer_add( state->sql, "FALSE " );
442 sqlAddMsg( state, "CASE expressions not yet supported" );
445 case EXP_CAST : // Type cast
446 sqlAddMsg( state, "Cast expressions not yet supported" );
449 case EXP_COLUMN : // Table column
450 if( expr->table_alias ) {
451 buffer_add_char( state->sql, '\"' );
452 buffer_add( state->sql, expr->table_alias );
453 buffer_add( state->sql, "\"." );
455 if( expr->column_name ) {
456 buffer_add( state->sql, expr->column_name );
458 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
459 "Column name not present in expression # %d", expr->id ));
464 if( !expr->subquery ) {
465 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
466 "No subquery found for EXIST expression # %d", expr->id ));
469 buffer_add( state->sql, "EXISTS (" );
470 incr_indent( state );
471 build_Query( state, expr->subquery );
472 decr_indent( state );
473 add_newline( state );
474 buffer_add_char( state->sql, ')' );
479 sqlAddMsg( state, "Expression type not yet supported" );
483 if( expr->left_operand ) {
484 buildExpression( state, expr->left_operand );
485 if( !state->error ) {
486 if( expr->subquery ) {
487 buffer_add( state->sql, " IN (" );
488 incr_indent( state );
489 build_Query( state, expr->subquery );
490 decr_indent( state );
491 add_newline( state );
492 buffer_add_char( state->sql, ')' );
494 sqlAddMsg( state, "IN lists not yet supported" );
500 case EXP_NOT_BETWEEN :
503 sqlAddMsg( state, "Expression type not yet supported" );
507 buffer_add( state->sql, "NULL" );
509 case EXP_NUMBER : // Numeric literal
510 if( !expr->literal ) {
511 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
512 "Internal error: No numeric value in string expression # %d", expr->id ));
515 buffer_add( state->sql, expr->literal );
519 if( expr->left_operand ) {
520 buildExpression( state, expr->left_operand );
522 sqlAddMsg( state, "Unable to emit left operand in expression # %d",
527 buffer_add_char( state->sql, ' ' );
528 buffer_add( state->sql, expr->op );
529 buffer_add_char( state->sql, ' ' );
530 if( expr->right_operand ) {
531 buildExpression( state, expr->right_operand );
533 sqlAddMsg( state, "Unable to emit right operand in expression # %d",
539 case EXP_STRING : // String literal
540 if( !expr->literal ) {
541 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
542 "Internal error: No string value in string expression # %d", expr->id ));
545 buffer_add_char( state->sql, '\'' );
546 buffer_add( state->sql, expr->literal );
547 buffer_add_char( state->sql, '\'' );
551 if( expr->subquery ) {
552 buffer_add_char( state->sql, '(' );
553 incr_indent( state );
554 build_Query( state, expr->subquery );
555 decr_indent( state );
556 add_newline( state );
557 buffer_add_char( state->sql, ')' );
559 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
560 "Internal error: No subquery in subquery expression # %d", expr->id ));
566 if( expr->parenthesize )
567 buffer_add_char( state->sql, ')' );
570 static void add_newline( BuildSQLState* state ) {
571 buffer_add_char( state->sql, '\n' );
574 static const char blanks[] = " "; // 32 blanks
575 static const size_t maxlen = sizeof( blanks ) - 1;
576 const int blanks_per_level = 3;
577 int n = state->indent * blanks_per_level;
579 size_t len = n >= maxlen ? maxlen : n;
580 buffer_add_n( state->sql, blanks, len );
585 static inline void incr_indent( BuildSQLState* state ) {
589 static inline void decr_indent( BuildSQLState* state ) {