1. When a class name is available instead of a table/view name,
[working/Evergreen.git] / Open-ILS / src / c-apps / buildSQL.c
1 /**
2         @file buildSQL.c
3         @brief Translate an abstract representation of a query into an SQL statement.
4 */
5
6 #include <stdlib.h>
7 #include <stdio.h>
8 #include <errno.h>
9 #include <dbi/dbi.h>
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"
16
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 );
25
26 static void add_newline( BuildSQLState* state );
27 static inline void incr_indent( BuildSQLState* state );
28 static inline void decr_indent( BuildSQLState* state );
29
30 /**
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.
35
36         Clear the output buffer, call build_Query() to do the work, and add a closing semicolon.
37 */
38 int buildSQL( BuildSQLState* state, StoredQ* query ) {
39         state->error  = 0;
40         buffer_reset( state->sql );
41         state->indent = 0;
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 );
46                 if( c != ' ' )
47                         buffer_add_char( state->sql, c );  // oops, not a space; put it back
48                 buffer_add( state->sql, ";\n" );
49         }
50         return state->error;
51 }
52
53 /**
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.
57
58         Look at the query type and branch to the corresponding routine.
59 */
60 static void build_Query( BuildSQLState* state, StoredQ* query ) {
61         if( buffer_length( state->sql ))
62                 add_newline( state );
63
64         switch( query->type ) {
65                 case QT_SELECT :
66                         buildSelect( state, query );
67                         break;
68                 case QT_UNION :
69                         buildCombo( state, query, "UNION" );
70                         break;
71                 case QT_INTERSECT :
72                         buildCombo( state, query, "INTERSECT" );
73                         break;
74                 case QT_EXCEPT :
75                         buildCombo( state, query, "EXCEPT" );
76                         break;
77                 default :
78                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
79                                 "Internal error: invalid query type %d in query # %d",
80                                 query->type, query->id ));
81                         state->error = 1;
82                         break;
83         }
84 }
85
86 /**
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.
91 */
92 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
93
94         QSeq* seq = query->child_list;
95         if( !seq ) {
96                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
97                         "Internal error: No child queries within %s query # %d",
98                         type_str, query->id ));
99                 state->error = 1;
100                 return;
101         }
102
103         // Traverse the list of child queries
104         while( seq ) {
105                 build_Query( state, seq->child_query );
106                 if( state->error ) {
107                         sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
108                                 seq->child_query->id, type_str, query->id );
109                         return;
110                 }
111                 seq = seq->next;
112                 if( seq ) {
113                         add_newline( state );
114                         buffer_add( state->sql, type_str );
115                         buffer_add_char( state->sql, ' ' );
116                         if( query->use_all )
117                                 buffer_add( state->sql, "ALL " );
118                 }
119         }
120
121         return;
122 }
123
124 /**
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.
128 */
129 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
130
131         FromRelation* from_clause = query->from_clause;
132         if( !from_clause ) {
133                 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
134                 state->error = 1;
135                 return;
136         }
137
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 );
142         if( state->error ) {
143                 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
144                 state->error = 1;
145                 return;
146         }
147         decr_indent( state );
148
149         // Build FROM clause, if there is one
150         if( query->from_clause ) {
151                 buildFrom( state, query->from_clause );
152                 if( state->error ) {
153                         sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
154                         state->error = 1;
155                         return;
156                 }
157         }
158
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 );
166                 if( state->error ) {
167                         sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
168                         state->error = 1;
169                         return;
170                 }
171                 //else
172                         //buffer_add_char( state->sql, ' ' );
173                 decr_indent( state );
174         }
175
176         // Build WHERE clause, if there is one
177         if( query->order_by_list ) {
178                 buildOrderBy( state, query->order_by_list );
179                 if( state->error ) {
180                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
181                         state->error = 1;
182                         return;
183                 }
184         }
185         
186         state->error = 0;
187 }
188
189 /**
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.
193 */
194 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
195
196         add_newline( state );
197         buffer_add( state->sql, "FROM" );
198         incr_indent( state );
199         add_newline( state );
200
201         switch( core_from->type ) {
202                 case FRT_RELATION : {
203                         char* relation = core_from->table_name;
204                         if( !relation ) {
205                                 if( !core_from->class_name ) {
206                                         sqlAddMsg( state, "No relation specified for core relation # %d",
207                                                 core_from->id );
208                                         state->error = 1;
209                                         return;
210                                 }
211
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 );
215                         }
216
217                         // Add table or view
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
221                         break;
222                 }
223                 case FRT_SUBQUERY :
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, ')' );
230                         break;
231                 case FRT_FUNCTION :
232                         sqlAddMsg( state, "Functions in FROM clause not yet supported" );
233                         state->error = 1;
234                         return;
235         }
236
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, "\" " );
242         }
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, "\" " );
247         } else
248                 buffer_add_char( state->sql, ' ' );
249
250         incr_indent( state );
251         FromRelation* join = core_from->join_list;
252         while( join ) {
253                 buildJoin( state, join );
254                 if( state->error ) {
255                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
256                                 core_from->id );
257                         break;
258                 } else
259                         join = join->next;
260         }
261         decr_indent( state );
262         decr_indent( state );
263 }
264
265 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
266         add_newline( state );
267         switch( join->join_type ) {
268                 case JT_NONE :
269                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
270                         state->error = 1;
271                         return;
272                 case JT_INNER :
273                         buffer_add( state->sql, "INNER JOIN " );
274                         break;
275                 case JT_LEFT:
276                         buffer_add( state->sql, "LEFT JOIN " );
277                         break;
278                 case JT_RIGHT:
279                         buffer_add( state->sql, "RIGHT JOIN " );
280                         break;
281                 case JT_FULL:
282                         buffer_add( state->sql, "FULL JOIN " );
283                         break;
284                 default :
285                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
286                         state->error = 1;
287                         return;
288         }
289
290         switch( join->type ) {
291                 case FRT_RELATION :
292                         // Sanity check
293                         if( !join->table_name || ! *join->table_name ) {
294                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
295                                 state->error = 1;
296                                 return;
297                         }
298                         buffer_add( state->sql, join->table_name );
299                         break;
300                 case FRT_SUBQUERY :
301                         // Sanity check
302                         if( !join->subquery ) {
303                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
304                                 state->error = 1;
305                                 return;
306                         } else if( !join->table_alias ) {
307                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
308                                         join->id );
309                                 state->error = 1;
310                                 return;
311                         }
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, ')' );
318                         break;
319                 case FRT_FUNCTION :
320                         if( !join->table_name || ! *join->table_name ) {
321                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
322                                         join->id );
323                                 state->error = 1;
324                                 return;
325                         }
326                         break;
327         }
328
329         const char* effective_alias = join->table_alias;
330         if( !effective_alias )
331                 effective_alias = join->class_name;
332
333         if( effective_alias ) {
334                 buffer_add( state->sql, " AS \"" );
335                 buffer_add( state->sql, effective_alias );
336                 buffer_add_char( state->sql, '\"' );
337         }
338         
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 );
345         }
346
347         FromRelation* subjoin = join->join_list;
348         while( subjoin ) {
349                 buildJoin( state, subjoin );
350                 if( state->error ) {
351                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
352                         break;
353                 } else
354                         subjoin = subjoin->next;
355         }
356 }
357
358 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
359         
360         int first = 1;
361         while( item ) {
362                 if( !first )
363                         buffer_add_char( state->sql, ',' );
364                 add_newline( state );
365                 buildExpression( state, item->expression );
366                 if( state->error ) {
367                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
368                         state->error = 1;
369                         break;
370                 }
371
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, '\"' );
376                 }
377                 first = 0;
378                 item = item->next;
379         };
380         buffer_add_char( state->sql, ' ' );
381 }
382
383 /**
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.
387 */
388 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
389         add_newline( state );
390         buffer_add( state->sql, "ORDER BY" );
391         incr_indent( state );
392
393         int first = 1;    // boolean
394         while( ord_list ) {
395                 if( first )
396                         first = 0;
397                 else
398                         buffer_add_char( state->sql, ',' );
399                 add_newline( state );
400                 buildExpression( state, ord_list->expression );
401                 if( state->error ) {
402                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
403                         return;
404                 }
405
406                 ord_list = ord_list->next;
407         }
408
409         decr_indent( state );
410         return;
411 }
412
413 /**
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.
417 */
418 static void buildExpression( BuildSQLState* state, Expression* expr ) {
419         if( !expr ) {
420                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
421                         "Internal error: NULL pointer to Expression" ));
422                 state->error = 1;
423                 return;
424         }
425
426         if( expr->parenthesize )
427                 buffer_add_char( state->sql, '(' );
428
429         switch( expr->type ) {
430                 case EXP_BETWEEN :
431                         sqlAddMsg( state, "BETWEEN expressions not yet supported" );
432                         state->error = 1;
433                         break;
434                 case EXP_BOOL :
435                         if( expr->literal ) {
436                                 buffer_add( state->sql, expr->literal );
437                                 buffer_add_char( state->sql, ' ' );
438                         } else
439                                 buffer_add( state->sql, "FALSE " );
440                         break;
441                 case EXP_CASE :
442                         sqlAddMsg( state, "CASE expressions not yet supported" );
443                         state->error = 1;
444                         break;
445                         case EXP_CAST :                   // Type cast
446                         sqlAddMsg( state, "Cast expressions not yet supported" );
447                         state->error = 1;
448                         break;
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, "\"." );
454                         }
455                         if( expr->column_name ) {
456                                 buffer_add( state->sql, expr->column_name );
457                         } else {
458                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
459                                         "Column name not present in expression # %d", expr->id ));
460                                 state->error = 1;
461                         }
462                         break;
463                 case EXP_EXIST :
464                         if( !expr->subquery ) {
465                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
466                                         "No subquery found for EXIST expression # %d", expr->id ));
467                                 state->error = 1;
468                         } else {
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, ')' );
475                         }
476                         break;
477                 case EXP_FIELD :
478                 case EXP_FUNCTION :
479                         sqlAddMsg( state, "Expression type not yet supported" );
480                         state->error = 1;
481                         break;
482                 case EXP_IN :
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, ')' );
493                                         } else {
494                                                 sqlAddMsg( state, "IN lists not yet supported" );
495                                                 state->error = 1;
496                                         }
497                                 }
498                         }
499                         break;
500                 case EXP_NOT_BETWEEN :
501                 case EXP_NOT_EXIST :
502                 case EXP_NOT_IN :
503                         sqlAddMsg( state, "Expression type not yet supported" );
504                         state->error = 1;
505                         break;
506                 case EXP_NULL :
507                         buffer_add( state->sql, "NULL" );
508                         break;
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 ));
513                                 state->error = 1;
514                         } else {
515                                 buffer_add( state->sql, expr->literal );
516                         }
517                         break;
518                 case EXP_OPERATOR :
519                         if( expr->left_operand ) {
520                                 buildExpression( state, expr->left_operand );
521                                 if( state->error ) {
522                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
523                                                 expr->id );
524                                         break;
525                                 }
526                         }
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 );
532                                 if( state->error ) {
533                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
534                                                            expr->id );
535                                         break;
536                                 }
537                         }
538                         break;
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 ));
543                                         state->error = 1;
544                         } else {
545                                 buffer_add_char( state->sql, '\'' );
546                                 buffer_add( state->sql, expr->literal );
547                                 buffer_add_char( state->sql, '\'' );
548                         }
549                         break;
550                 case EXP_SUBQUERY :
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, ')' );
558                         } else {
559                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
560                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
561                                 state->error = 1;
562                         }
563                         break;
564         }
565         
566         if( expr->parenthesize )
567                 buffer_add_char( state->sql, ')' );
568 }
569
570 static void add_newline( BuildSQLState* state ) {
571         buffer_add_char( state->sql, '\n' );
572
573         // Add indentation
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;
578         while( n > 0 ) {
579                 size_t len = n >= maxlen ? maxlen : n;
580                 buffer_add_n( state->sql, blanks, len );
581                 n -= len;
582         }
583 }
584
585 static inline void incr_indent( BuildSQLState* state ) {
586         ++state->indent;
587 }
588
589 static inline void decr_indent( BuildSQLState* state ) {
590         if( state->indent )
591                 --state->indent;
592 }