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 "openils/oils_buildq.h"
  13
  14 static void buildQuery( BuildSQLState* state, StoredQ* query );
  15 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str );
  16 static void buildSelect( BuildSQLState* state, StoredQ* query );
  17 static void buildFrom( BuildSQLState* state, FromRelation* core_from );
  18 static void buildJoin( BuildSQLState* state, FromRelation* join );
  19 static void buildSelectList( BuildSQLState* state, SelectItem* item );
  20 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list );
  21 static void buildExpression( BuildSQLState* state, Expression* expr );
  22
  23 static void add_newline( BuildSQLState* state );
  24 static inline void incr_indent( BuildSQLState* state );
  25 static inline void decr_indent( BuildSQLState* state );
  26
  27 /**
  28         @brief Build an SQL query.
  29         @param state Pointer to the query-building context.
  30         @param query Pointer to the query to be built.
  31         @return Zero if successful, or 1 if not.
  32
  33         Clear the output buffer, call buildQuery() to do the work, and add a closing semicolon.
  34 */
  35 int buildSQL( BuildSQLState* state, StoredQ* query ) {
  36         state->error  = 0;
  37         buffer_reset( state->sql );
  38         state->indent = 0;
  39         buildQuery( state, query );
  40         if( ! state->error ) {
  41                 // Remove the trailing space, if there is one, and add a semicolon.
  42                 char c = buffer_chomp( state->sql );
  43                 if( c != ' ' )
  44                         buffer_add_char( state->sql, c );  // oops, not a space; put it back
  45                 buffer_add( state->sql, ";\n" );
  46         }
  47         return state->error;
  48 }
  49
  50 /**
  51         @brief Build an SQL query, appending it to what has been built so far.
  52         @param state Pointer to the query-building context.
  53         @param query Pointer to the query to be built.
  54
  55         Look at the query type and branch to the corresponding routine.
  56 */
  57 static void buildQuery( BuildSQLState* state, StoredQ* query ) {
  58         if( buffer_length( state->sql ))
  59                 add_newline( state );
  60
  61         switch( query->type ) {
  62                 case QT_SELECT :
  63                         buildSelect( state, query );
  64                         break;
  65                 case QT_UNION :
  66                         buildCombo( state, query, "UNION" );
  67                         break;
  68                 case QT_INTERSECT :
  69                         buildCombo( state, query, "INTERSECT" );
  70                         break;
  71                 case QT_EXCEPT :
  72                         buildCombo( state, query, "EXCEPT" );
  73                         break;
  74                 default :
  75                         osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
  76                                 "Internal error: invalid query type %d in query # %d",
  77                                 query->type, query->id ));
  78                         state->error = 1;
  79                         break;
  80         }
  81 }
  82
  83 /**
  84         @brief Build a UNION, INTERSECT, or EXCEPT query.
  85         @param state Pointer to the query-building context.
  86         @param query Pointer to the query to be built.
  87         @param type_str The query type, as a string.
  88 */
  89 static void buildCombo( BuildSQLState* state, StoredQ* query, const char* type_str ) {
  90
  91         QSeq* seq = query->child_list;
  92         if( !seq ) {
  93                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
  94                         "Internal error: No child queries within %s query # %d",
  95                         type_str, query->id ));
  96                 state->error = 1;
  97                 return;
  98         }
  99
 100         // Traverse the list of child queries
 101         while( seq ) {
 102                 buildQuery( state, seq->child_query );
 103                 if( state->error ) {
 104                         sqlAddMsg( state, "Unable to build child query # %d within %s query %d",
 105                                 seq->child_query->id, type_str, query->id );
 106                         return;
 107                 }
 108                 seq = seq->next;
 109                 if( seq ) {
 110                         add_newline( state );
 111                         buffer_add( state->sql, type_str );
 112                         buffer_add_char( state->sql, ' ' );
 113                         if( query->use_all )
 114                                 buffer_add( state->sql, "ALL " );
 115                 }
 116         }
 117
 118         return;
 119 }
 120
 121 /**
 122         @brief Build a SELECT statement.
 123         @param state Pointer to the query-building context.
 124         @param query Pointer to the StoredQ structure that represents the query.
 125 */
 126 static void buildSelect( BuildSQLState* state, StoredQ* query ) {
 127
 128         FromRelation* from_clause = query->from_clause;
 129         if( !from_clause ) {
 130                 sqlAddMsg( state, "SELECT has no FROM clause in query # %d", query->id );
 131                 state->error = 1;
 132                 return;
 133         }
 134
 135         // To do: get SELECT list; just a stub here
 136         buffer_add( state->sql, "SELECT" );
 137         incr_indent( state );
 138         buildSelectList( state, query->select_list );
 139         if( state->error ) {
 140                 sqlAddMsg( state, "Unable to build SELECT list for query # %d", query->id );
 141                 state->error = 1;
 142                 return;
 143         }
 144         decr_indent( state );
 145
 146         // Build FROM clause, if there is one
 147         if( query->from_clause ) {
 148                 buildFrom( state, query->from_clause );
 149                 if( state->error ) {
 150                         sqlAddMsg( state, "Unable to build FROM clause for query # %d", query->id );
 151                         state->error = 1;
 152                         return;
 153                 }
 154         }
 155
 156         // Build WHERE clause, if there is one
 157         if( query->where_clause ) {
 158                 add_newline( state );
 159                 buffer_add( state->sql, "WHERE" );
 160                 incr_indent( state );
 161                 add_newline( state );
 162                 buildExpression( state, query->where_clause );
 163                 if( state->error ) {
 164                         sqlAddMsg( state, "Unable to build WHERE clause for query # %d", query->id );
 165                         state->error = 1;
 166                         return;
 167                 }
 168                 //else
 169                         //buffer_add_char( state->sql, ' ' );
 170                 decr_indent( state );
 171         }
 172
 173         // Build WHERE clause, if there is one
 174         if( query->order_by_list ) {
 175                 buildOrderBy( state, query->order_by_list );
 176                 if( state->error ) {
 177                         sqlAddMsg( state, "Unable to build ORDER BY clause for query # %d", query->id );
 178                         state->error = 1;
 179                         return;
 180                 }
 181         }
 182
 183         state->error = 0;
 184 }
 185
 186 /**
 187         @brief Build a FROM clause.
 188         @param Pointer to the query-building context.
 189         @param Pointer to the StoredQ query to which the FROM clause belongs.
 190 */
 191 static void buildFrom( BuildSQLState* state, FromRelation* core_from ) {
 192
 193         add_newline( state );
 194         buffer_add( state->sql, "FROM" );
 195         incr_indent( state );
 196         add_newline( state );
 197
 198         switch( core_from->type ) {
 199                 case FRT_RELATION :
 200                         if( ! core_from->table_name ) {
 201                                 // To do: if class is available, look up table name
 202                                 // or source_definition in the IDL
 203                                 sqlAddMsg( state, "No table or view name available for core relation # %d",
 204                                         core_from->id );
 205                                 state->error = 1;
 206                                 return;
 207                         }
 208
 209                         // Add table or view
 210                         buffer_add( state->sql, core_from->table_name );
 211                         break;
 212                 case FRT_SUBQUERY :
 213                         buffer_add_char( state->sql, '(' );
 214                         incr_indent( state );
 215                         buildQuery( state, core_from->subquery );
 216                         decr_indent( state );
 217                         add_newline( state );
 218                         buffer_add_char( state->sql, ')' );
 219                         break;
 220                 case FRT_FUNCTION :
 221                         sqlAddMsg( state, "Functions in FROM clause not yet supported" );
 222                         state->error = 1;
 223                         return;
 224         }
 225
 226         // Add a table alias, if possible
 227         if( core_from->table_alias ) {
 228                 buffer_add( state->sql, " AS \"" );
 229                 buffer_add( state->sql, core_from->table_alias );
 230                 buffer_add( state->sql, "\" " );
 231         }
 232         else if( core_from->class_name ) {
 233                 buffer_add( state->sql, " AS \"" );
 234                 buffer_add( state->sql, core_from->class_name );
 235                 buffer_add( state->sql, "\" " );
 236         } else
 237                 buffer_add_char( state->sql, ' ' );
 238
 239         incr_indent( state );
 240         FromRelation* join = core_from->join_list;
 241         while( join ) {
 242                 buildJoin( state, join );
 243                 if( state->error ) {
 244                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d",
 245                                 core_from->id );
 246                         break;
 247                 } else
 248                         join = join->next;
 249         }
 250         decr_indent( state );
 251         decr_indent( state );
 252 }
 253
 254 static void buildJoin( BuildSQLState* state, FromRelation* join ) {
 255         add_newline( state );
 256         switch( join->join_type ) {
 257                 case JT_NONE :
 258                         sqlAddMsg( state, "Non-join relation # %d in JOIN clause", join->id );
 259                         state->error = 1;
 260                         return;
 261                 case JT_INNER :
 262                         buffer_add( state->sql, "INNER JOIN " );
 263                         break;
 264                 case JT_LEFT:
 265                         buffer_add( state->sql, "LEFT JOIN " );
 266                         break;
 267                 case JT_RIGHT:
 268                         buffer_add( state->sql, "RIGHT JOIN " );
 269                         break;
 270                 case JT_FULL:
 271                         buffer_add( state->sql, "FULL JOIN " );
 272                         break;
 273                 default :
 274                         sqlAddMsg( state, "Unrecognized join type in relation # %d", join->id );
 275                         state->error = 1;
 276                         return;
 277         }
 278
 279         switch( join->type ) {
 280                 case FRT_RELATION :
 281                         // Sanity check
 282                         if( !join->table_name || ! *join->table_name ) {
 283                                 sqlAddMsg( state, "No relation designated for relation # %d", join->id );
 284                                 state->error = 1;
 285                                 return;
 286                         }
 287                         buffer_add( state->sql, join->table_name );
 288                         break;
 289                 case FRT_SUBQUERY :
 290                         // Sanity check
 291                         if( !join->subquery ) {
 292                                 sqlAddMsg( state, "Subquery expected, not found for relation # %d", join->id );
 293                                 state->error = 1;
 294                                 return;
 295                         } else if( !join->table_alias ) {
 296                                 sqlAddMsg( state, "No table alias for subquery in FROM relation # %d",
 297                                         join->id );
 298                                 state->error = 1;
 299                                 return;
 300                         }
 301                         buffer_add_char( state->sql, '(' );
 302                         incr_indent( state );
 303                         buildQuery( state, join->subquery );
 304                         decr_indent( state );
 305                         add_newline( state );
 306                         buffer_add_char( state->sql, ')' );
 307                         break;
 308                 case FRT_FUNCTION :
 309                         if( !join->table_name || ! *join->table_name ) {
 310                                 sqlAddMsg( state, "Joins to functions not yet supported in relation # %d",
 311                                         join->id );
 312                                 state->error = 1;
 313                                 return;
 314                         }
 315                         break;
 316         }
 317
 318         const char* effective_alias = join->table_alias;
 319         if( !effective_alias )
 320                 effective_alias = join->class_name;
 321
 322         if( effective_alias ) {
 323                 buffer_add( state->sql, " AS \"" );
 324                 buffer_add( state->sql, effective_alias );
 325                 buffer_add_char( state->sql, '\"' );
 326         }
 327
 328         if( join->on_clause ) {
 329                 incr_indent( state );
 330                 add_newline( state );
 331                 buffer_add( state->sql, "ON " );
 332                 buildExpression( state, join->on_clause );
 333                 decr_indent( state );
 334         }
 335
 336         FromRelation* subjoin = join->join_list;
 337         while( subjoin ) {
 338                 buildJoin( state, subjoin );
 339                 if( state->error ) {
 340                         sqlAddMsg( state, "Unable to build JOIN clause(s) for relation # %d", join->id );
 341                         break;
 342                 } else
 343                         subjoin = subjoin->next;
 344         }
 345 }
 346
 347 static void buildSelectList( BuildSQLState* state, SelectItem* item ) {
 348
 349         int first = 1;
 350         while( item ) {
 351                 if( !first )
 352                         buffer_add_char( state->sql, ',' );
 353                 add_newline( state );
 354                 buildExpression( state, item->expression );
 355                 if( state->error ) {
 356                         sqlAddMsg( state, "Unable to build an expression for SELECT item # %d", item->id );
 357                         state->error = 1;
 358                         break;
 359                 }
 360
 361                 if( item->column_alias ) {
 362                         buffer_add( state->sql, " AS \"" );
 363                         buffer_add( state->sql, item->column_alias );
 364                         buffer_add_char( state->sql, '\"' );
 365                 }
 366                 first = 0;
 367                 item = item->next;
 368         };
 369         buffer_add_char( state->sql, ' ' );
 370 }
 371
 372 /**
 373         @brief Add an ORDER BY clause to the current query.
 374         @param state Pointer to the query-building context.
 375         @param ord_list Pointer to the first node in a linked list of OrderItems.
 376 */
 377 static void buildOrderBy( BuildSQLState* state, OrderItem* ord_list ) {
 378         add_newline( state );
 379         buffer_add( state->sql, "ORDER BY" );
 380         incr_indent( state );
 381
 382         int first = 1;    // boolean
 383         while( ord_list ) {
 384                 if( first )
 385                         first = 0;
 386                 else
 387                         buffer_add_char( state->sql, ',' );
 388                 add_newline( state );
 389                 buildExpression( state, ord_list->expression );
 390                 if( state->error ) {
 391                         sqlAddMsg( state, "Unable to add ORDER BY expression # %d", ord_list->id );
 392                         return;
 393                 }
 394
 395                 ord_list = ord_list->next;
 396         }
 397
 398         decr_indent( state );
 399         return;
 400 }
 401
 402 /**
 403         @brief Build an arbitrary expression.
 404         @param state Pointer to the query-building context.
 405         @param expr Pointer to the Expression representing the expression to be built.
 406 */
 407 static void buildExpression( BuildSQLState* state, Expression* expr ) {
 408         if( !expr ) {
 409                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 410                         "Internal error: NULL pointer to Expression" ));
 411                 state->error = 1;
 412                 return;
 413         }
 414
 415         if( expr->parenthesize )
 416                 buffer_add_char( state->sql, '(' );
 417
 418         switch( expr->type ) {
 419                 case EXP_BETWEEN :
 420                         sqlAddMsg( state, "BETWEEN expressions not yet supported" );
 421                         state->error = 1;
 422                         break;
 423                 case EXP_BOOL :
 424                         if( expr->literal ) {
 425                                 buffer_add( state->sql, expr->literal );
 426                                 buffer_add_char( state->sql, ' ' );
 427                         } else
 428                                 buffer_add( state->sql, "FALSE " );
 429                         break;
 430                 case EXP_CASE :
 431                         sqlAddMsg( state, "CASE expressions not yet supported" );
 432                         state->error = 1;
 433                         break;
 434                         case EXP_CAST :                   // Type cast
 435                         sqlAddMsg( state, "Cast expressions not yet supported" );
 436                         state->error = 1;
 437                         break;
 438                 case EXP_COLUMN :                 // Table column
 439                         if( expr->table_alias ) {
 440                                 buffer_add_char( state->sql, '\"' );
 441                                 buffer_add( state->sql, expr->table_alias );
 442                                 buffer_add( state->sql, "\"." );
 443                         }
 444                         if( expr->column_name ) {
 445                                 buffer_add( state->sql, expr->column_name );
 446                         } else {
 447                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 448                                         "Column name not present in expression # %d", expr->id ));
 449                                 state->error = 1;
 450                         }
 451                         break;
 452                 case EXP_EXIST :
 453                         if( !expr->subquery ) {
 454                                 osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
 455                                         "No subquery found for EXIST expression # %d", expr->id ));
 456                                 state->error = 1;
 457                         } else {
 458                                 buffer_add( state->sql, "EXISTS (" );
 459                                 incr_indent( state );
 460                                 buildQuery( state, expr->subquery );
 461                                 decr_indent( state );
 462                                 add_newline( state );
 463                                 buffer_add_char( state->sql, ')' );
 464                         }
 465                         break;
 466                 case EXP_FIELD :
 467                 case EXP_FUNCTION :
 468                         sqlAddMsg( state, "Expression type not yet supported" );
 469                         state->error = 1;
 470                         break;
 471                 case EXP_IN :
 472                         if( expr->left_operand ) {
 473                                 buildExpression( state, expr->left_operand );
 474                                 if( !state->error ) {
 475                                         if( expr->subquery ) {
 476                                                 buffer_add( state->sql, " IN (" );
 477                                                 incr_indent( state );
 478                                                 buildQuery( state, expr->subquery );
 479                                                 decr_indent( state );
 480                                                 add_newline( state );
 481                                                 buffer_add_char( state->sql, ')' );
 482                                         } else {
 483                                                 sqlAddMsg( state, "IN lists not yet supported" );
 484                                                 state->error = 1;
 485                                         }
 486                                 }
 487                         }
 488                         break;
 489                 case EXP_NOT_BETWEEN :
 490                 case EXP_NOT_EXIST :
 491                 case EXP_NOT_IN :
 492                         sqlAddMsg( state, "Expression type not yet supported" );
 493                         state->error = 1;
 494                         break;
 495                 case EXP_NULL :
 496                         buffer_add( state->sql, "NULL" );
 497                         break;
 498                 case EXP_NUMBER :                    // Numeric literal
 499                         if( !expr->literal ) {
 500                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 501                                         "Internal error: No numeric value in string expression # %d", expr->id ));
 502                                 state->error = 1;
 503                         } else {
 504                                 buffer_add( state->sql, expr->literal );
 505                         }
 506                         break;
 507                 case EXP_OPERATOR :
 508                         if( expr->left_operand ) {
 509                                 buildExpression( state, expr->left_operand );
 510                                 if( state->error ) {
 511                                         sqlAddMsg( state, "Unable to emit left operand in expression # %d",
 512                                                 expr->id );
 513                                         break;
 514                                 }
 515                         }
 516                         buffer_add_char( state->sql, ' ' );
 517                         buffer_add( state->sql, expr->op );
 518                         buffer_add_char( state->sql, ' ' );
 519                         if( expr->right_operand ) {
 520                                 buildExpression( state, expr->right_operand );
 521                                 if( state->error ) {
 522                                         sqlAddMsg( state, "Unable to emit right operand in expression # %d",
 523                                                            expr->id );
 524                                         break;
 525                                 }
 526                         }
 527                         break;
 528                 case EXP_STRING :                     // String literal
 529                         if( !expr->literal ) {
 530                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 531                                         "Internal error: No string value in string expression # %d", expr->id ));
 532                                         state->error = 1;
 533                         } else {
 534                                 buffer_add_char( state->sql, '\'' );
 535                                 buffer_add( state->sql, expr->literal );
 536                                 buffer_add_char( state->sql, '\'' );
 537                         }
 538                         break;
 539                 case EXP_SUBQUERY :
 540                         if( expr->subquery ) {
 541                                 buffer_add_char( state->sql, '(' );
 542                                 incr_indent( state );
 543                                 buildQuery( state, expr->subquery );
 544                                 decr_indent( state );
 545                                 add_newline( state );
 546                                 buffer_add_char( state->sql, ')' );
 547                         } else {
 548                                 osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
 549                                         "Internal error: No subquery in subquery expression # %d", expr->id ));
 550                                 state->error = 1;
 551                         }
 552                         break;
 553         }
 554
 555         if( expr->parenthesize )
 556                 buffer_add_char( state->sql, ')' );
 557 }
 558
 559 static void add_newline( BuildSQLState* state ) {
 560         buffer_add_char( state->sql, '\n' );
 561
 562         // Add indentation
 563         static const char blanks[] = "                                ";   // 32 blanks
 564         static const size_t maxlen = sizeof( blanks ) - 1;
 565         const int blanks_per_level = 3;
 566         int n = state->indent * blanks_per_level;
 567         while( n > 0 ) {
 568                 size_t len = n >= maxlen ? maxlen : n;
 569                 buffer_add_n( state->sql, blanks, len );
 570                 n -= len;
 571         }
 572 }
 573
 574 static inline void incr_indent( BuildSQLState* state ) {
 575         ++state->indent;
 576 }
 577
 578 static inline void decr_indent( BuildSQLState* state ) {
 579         if( state->indent )
 580                 --state->indent;
 581 }