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 }