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                 decr_indent( state );
 172         }
 173
 174         // To do: build GROUP BY clause, if there is one
 175
 176         // Build HAVING clause, if there is one
 177         if( query->having_clause ) {
 178                 add_newline( state );
 179                 buffer_add( state->sql, "HAVING" );
 180                 incr_indent( state );
 181                 add_newline( state );
 182                 buildExpression( state, query->having_clause );
 183                 if( state->error ) {
 184                         sqlAddMsg( state, "Unable to build HAVING clause for query # %d", query->id );
 185                         state->error = 1;
 186                         return;
 187                 }
 188                 decr_indent( state );
 189         }
 190
 191         // Build ORDER BY clause, if there is one
 192         if( query->order_by_list ) {
 193                 buildOrderBy( state, query->order_by_list );
 194                 if( state->error ) {
 195                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
 196                         state->error = 1;
 197                         return;
 198                 }
 199         }
 200
 201         // To do: Build LIMIT clause, if there is one
 202
 203         // To do: Build OFFSET clause, if there is one
 204
 205         state->error = 0;
 206 }
 207
 208 /**
 209         @brief Build a FROM clause.
 210         @param Pointer to the query-building context.
 211         @param Pointer to the StoredQ query to which the FROM clause belongs.
 212 */
 213 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
 214
 215         add_newline( state );
 216         buffer_add( state->sql, "FROM" );
 217         incr_indent( state );
 218         add_newline( state );
 219
 220         switch( core_from->type ) {
 221                 case FRT_RELATION : {
 222                         char* relation = core_from->table_name;
 223                         if( !relation ) {
 224                                 if( !core_from->class_name ) {
 225                                         sqlAddMsg( state, "No relation specified for core relation # %d",
 226                                                 core_from->id );
 227                                         state->error = 1;
 228                                         return;
 229                                 }
 230
 231                                 // Look up table name, view name, or source_definition in the IDL
 232                                 osrfHash* class_hash = osrfHashGet( oilsIDL(), core_from->class_name );
 233                                 relation = oilsGetRelation( class_hash );
 234                         }
 235
 236                         // Add table or view
 237                         buffer_add( state->sql, relation );
 238                         if( !core_from->table_name )
 239                                 free( relation );   // In this case we strdup'd it, must free it
 240                         break;
 241                 }
 242                 case FRT_SUBQUERY :
 243                         buffer_add_char( state->sql, '(' );
 244                         incr_indent( state );
 245                         build_Query( state, core_from->subquery );
 246                         decr_indent( state );
 247                         add_newline( state );
 248                         buffer_add_char( state->sql, ')' );
 249                         break;
 250                 case FRT_FUNCTION :
 251                         sqlAddMsg( state, "Functions in FROM clause not yet supported" );
 252                         state->error = 1;
 253                         return;
 254         }
 255
 256         // Add a table alias, if possible
 257         if( core_from->table_alias ) {
 258                 buffer_add( state->sql, " AS \"" );
 259                 buffer_add( state->sql, core_from->table_alias );
 260                 buffer_add( state->sql, "\" " );
 261         }
 262         else if( core_from->class_name ) {
 263                 buffer_add( state->sql, " AS \"" );
 264                 buffer_add( state->sql, core_from->class_name );
 265                 buffer_add( state->sql, "\" " );
 266         } else
 267                 buffer_add_char( state->sql, ' ' );
 268
 269         incr_indent( state );
 270         FromRelation* join = core_from->join_list;
 271         while( join ) {
 272                 buildJoin( state, join );
 273                 if( state->error ) {
 274                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
 275                                 core_from->id );
 276                         break;
 277                 } else
 278                         join = join->next;
 279         }
 280         decr_indent( state );
 281         decr_indent( state );
 282 }
 283
 284 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
 285         add_newline( state );
 286         switch( join->join_type ) {
 287                 case JT_NONE :
 288                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
 289                         state->error = 1;
 290                         return;
 291                 case JT_INNER :
 292                         buffer_add( state->sql, "INNER JOIN " );
 293                         break;
 294                 case JT_LEFT:
 295                         buffer_add( state->sql, "LEFT JOIN " );
 296                         break;
 297                 case JT_RIGHT:
 298                         buffer_add( state->sql, "RIGHT JOIN " );
 299                         break;
 300                 case JT_FULL:
 301                         buffer_add( state->sql, "FULL JOIN " );
 302                         break;
 303                 default :
 304                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
 305                         state->error = 1;
 306                         return;
 307         }
 308
 309         switch( join->type ) {
 310                 case FRT_RELATION :
 311                         // Sanity check
 312                         if( !join->table_name || ! *join->table_name ) {
 313                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
 314                                 state->error = 1;
 315                                 return;
 316                         }
 317                         buffer_add( state->sql, join->table_name );
 318                         break;
 319                 case FRT_SUBQUERY :
 320                         // Sanity check
 321                         if( !join->subquery ) {
 322                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
 323                                 state->error = 1;
 324                                 return;
 325                         } else if( !join->table_alias ) {
 326                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
 327                                         join->id );
 328                                 state->error = 1;
 329                                 return;
 330                         }
 331                         buffer_add_char( state->sql, '(' );
 332                         incr_indent( state );
 333                         build_Query( state, join->subquery );
 334                         decr_indent( state );
 335                         add_newline( state );
 336                         buffer_add_char( state->sql, ')' );
 337                         break;
 338                 case FRT_FUNCTION :
 339                         if( !join->table_name || ! *join->table_name ) {
 340                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
 341                                         join->id );
 342                                 state->error = 1;
 343                                 return;
 344                         }
 345                         break;
 346         }
 347
 348         const char* effective_alias = join->table_alias;
 349         if( !effective_alias )
 350                 effective_alias = join->class_name;
 351
 352         if( effective_alias ) {
 353                 buffer_add( state->sql, " AS \"" );
 354                 buffer_add( state->sql, effective_alias );
 355                 buffer_add_char( state->sql, '\"' );
 356         }
 357
 358         if( join->on_clause ) {
 359                 incr_indent( state );
 360                 add_newline( state );
 361                 buffer_add( state->sql, "ON " );
 362                 buildExpression( state, join->on_clause );
 363                 decr_indent( state );
 364         }
 365
 366         FromRelation* subjoin = join->join_list;
 367         while( subjoin ) {
 368                 buildJoin( state, subjoin );
 369                 if( state->error ) {
 370                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
 371                         break;
 372                 } else
 373                         subjoin = subjoin->next;
 374         }
 375 }
 376
 377 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
 378
 379         int first = 1;
 380         while( item ) {
 381                 if( !first )
 382                         buffer_add_char( state->sql, ',' );
 383                 add_newline( state );
 384                 buildExpression( state, item->expression );
 385                 if( state->error ) {
 386                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
 387                         state->error = 1;
 388                         break;
 389                 }
 390
 391                 if( item->column_alias ) {
 392                         buffer_add( state->sql, " AS \"" );
 393                         buffer_add( state->sql, item->column_alias );
 394                         buffer_add_char( state->sql, '\"' );
 395                 }
 396                 first = 0;
 397                 item = item->next;
 398         };
 399         buffer_add_char( state->sql, ' ' );
 400 }
 401
 402 /**
 403         @brief Add an ORDER BY clause to the current query.
 404         @param state Pointer to the query-building context.
 405         @param ord_list Pointer to the first node in a linked list of OrderItems.
 406 */
 407 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
 408         add_newline( state );
 409         buffer_add( state->sql, "ORDER BY" );
 410         incr_indent( state );
 411
 412         int first = 1;    // boolean
 413         while( ord_list ) {
 414                 if( first )
 415                         first = 0;
 416                 else
 417                         buffer_add_char( state->sql, ',' );
 418                 add_newline( state );
 419                 buildExpression( state, ord_list->expression );
 420                 if( state->error ) {
 421                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
 422                         return;
 423                 }
 424
 425                 ord_list = ord_list->next;
 426         }
 427
 428         decr_indent( state );
 429         return;
 430 }
 431
 432 /**
 433         @brief Build an arbitrary expression.
 434         @param state Pointer to the query-building context.
 435         @param expr Pointer to the Expression representing the expression to be built.
 436 */
 437 static void buildExpression( BuildSQLState* state, Expression* expr ) {
 438         if( !expr ) {
 439                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 440                         "Internal error: NULL pointer to Expression" ));
 441                 state->error = 1;
 442                 return;
 443         }
 444
 445         if( expr->parenthesize )
 446                 buffer_add_char( state->sql, '(' );
 447
 448         switch( expr->type ) {
 449                 case EXP_BETWEEN :
 450                         if( expr->negate )
 451                                 buffer_add( state->sql, "NOT " );
 452
 453                         sqlAddMsg( state, "BETWEEN expressions not yet supported" );
 454                         state->error = 1;
 455                         break;
 456                 case EXP_BOOL :
 457                         if( expr->negate )
 458                                 buffer_add( state->sql, "NOT " );
 459
 460                         if( expr->literal ) {
 461                                 buffer_add( state->sql, expr->literal );
 462                                 buffer_add_char( state->sql, ' ' );
 463                         } else
 464                                 buffer_add( state->sql, "FALSE " );
 465                         break;
 466                 case EXP_CASE :
 467                         if( expr->negate )
 468                                 buffer_add( state->sql, "NOT " );
 469
 470                         sqlAddMsg( state, "CASE expressions not yet supported" );
 471                         state->error = 1;
 472                         break;
 473                 case EXP_CAST :                   // Type cast
 474                         sqlAddMsg( state, "Cast expressions not yet supported" );
 475                         state->error = 1;
 476                         break;
 477                 case EXP_COLUMN :                 // Table column
 478                         if( expr->negate )
 479                                 buffer_add( state->sql, "NOT " );
 480
 481                         if( expr->table_alias ) {
 482                                 buffer_add_char( state->sql, '\"' );
 483                                 buffer_add( state->sql, expr->table_alias );
 484                                 buffer_add( state->sql, "\"." );
 485                         }
 486                         if( expr->column_name ) {
 487                                 buffer_add( state->sql, expr->column_name );
 488                         } else {
 489                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 490                                         "Column name not present in expression # %d", expr->id ));
 491                                 state->error = 1;
 492                         }
 493                         break;
 494                 case EXP_EXIST :
 495                         if( !expr->subquery ) {
 496                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 497                                         "No subquery found for EXIST expression # %d", expr->id ));
 498                                 state->error = 1;
 499                         } else {
 500                                 if( expr->negate )
 501                                         buffer_add( state->sql, "NOT " );
 502
 503                                 buffer_add( state->sql, "EXISTS (" );
 504                                 incr_indent( state );
 505                                 build_Query( state, expr->subquery );
 506                                 decr_indent( state );
 507                                 add_newline( state );
 508                                 buffer_add_char( state->sql, ')' );
 509                         }
 510                         break;
 511                 case EXP_FIELD :
 512                         sqlAddMsg( state, "Field expressions not yet supported" );
 513                         state->error = 1;
 514                         break;
 515                 case EXP_FUNCTION :
 516                         if( expr->negate )
 517                                 buffer_add( state->sql, "NOT " );
 518
 519                         sqlAddMsg( state, "Function expressions not yet supported" );
 520                         state->error = 1;
 521                         break;
 522                 case EXP_IN :
 523                         if( expr->left_operand ) {
 524                                 buildExpression( state, expr->left_operand );
 525                                 if( !state->error ) {
 526                                         if( expr->negate )
 527                                                 buffer_add( state->sql, "NOT " );
 528
 529                                         if( expr->subquery ) {
 530                                                 buffer_add( state->sql, " IN (" );
 531                                                 incr_indent( state );
 532                                                 build_Query( state, expr->subquery );
 533                                                 decr_indent( state );
 534                                                 add_newline( state );
 535                                                 buffer_add_char( state->sql, ')' );
 536                                         } else {
 537                                                 sqlAddMsg( state, "IN lists not yet supported" );
 538                                                 state->error = 1;
 539                                         }
 540                                 }
 541                         }
 542                         break;
 543                 case EXP_NULL :
 544                         if( expr->negate )
 545                                 buffer_add( state->sql, "NOT " );
 546
 547                         buffer_add( state->sql, "NULL" );
 548                         break;
 549                 case EXP_NUMBER :                    // Numeric literal
 550                         if( !expr->literal ) {
 551                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 552                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 553                                 state->error = 1;
 554                         } else {
 555                                 buffer_add( state->sql, expr->literal );
 556                         }
 557                         break;
 558                 case EXP_OPERATOR :
 559                         if( expr->negate )
 560                                 buffer_add( state->sql, "NOT (" );
 561
 562                         if( expr->left_operand ) {
 563                                 buildExpression( state, expr->left_operand );
 564                                 if( state->error ) {
 565                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 566                                                 expr->id );
 567                                         break;
 568                                 }
 569                         }
 570                         buffer_add_char( state->sql, ' ' );
 571                         buffer_add( state->sql, expr->op );
 572                         buffer_add_char( state->sql, ' ' );
 573                         if( expr->right_operand ) {
 574                                 buildExpression( state, expr->right_operand );
 575                                 if( state->error ) {
 576                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 577                                                            expr->id );
 578                                         break;
 579                                 }
 580                         }
 581
 582                         if( expr->negate )
 583                                 buffer_add_char( state->sql, ')' );
 584
 585                         break;
 586                 case EXP_STRING :                     // String literal
 587                         if( !expr->literal ) {
 588                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 589                                         "Internal error: No string value in string expression # %d", expr->id ));
 590                                         state->error = 1;
 591                         } else {
 592                                 buffer_add_char( state->sql, '\'' );
 593                                 buffer_add( state->sql, expr->literal );
 594                                 buffer_add_char( state->sql, '\'' );
 595                         }
 596                         break;
 597                 case EXP_SUBQUERY :
 598                         if( expr->negate )
 599                                 buffer_add( state->sql, "NOT " );
 600
 601                         if( expr->subquery ) {
 602                                 buffer_add_char( state->sql, '(' );
 603                                 incr_indent( state );
 604                                 build_Query( state, expr->subquery );
 605                                 decr_indent( state );
 606                                 add_newline( state );
 607                                 buffer_add_char( state->sql, ')' );
 608                         } else {
 609                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 610                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 611                                 state->error = 1;
 612                         }
 613                         break;
 614         }
 615
 616         if( expr->parenthesize )
 617                 buffer_add_char( state->sql, ')' );
 618 }
 619
 620 static void add_newline( BuildSQLState* state ) {
 621         buffer_add_char( state->sql, '\n' );
 622
 623         // Add indentation
 624         static const char blanks[] = "                                ";   // 32 blanks
 625         static const size_t maxlen = sizeof( blanks ) - 1;
 626         const int blanks_per_level = 3;
 627         int n = state->indent * blanks_per_level;
 628         while( n > 0 ) {
 629                 size_t len = n >= maxlen ? maxlen : n;
 630                 buffer_add_n( state->sql, blanks, len );
 631                 n -= len;
 632         }
 633 }
 634
 635 static inline void incr_indent( BuildSQLState* state ) {
 636         ++state->indent;
 637 }
 638
 639 static inline void decr_indent( BuildSQLState* state ) {
 640         if( state->indent )
 641                 --state->indent;
 642 }