share/Grammar/DB2.gra

   1
   2 {
   3     my ( %tables, $table_order, @table_comments, @views, @triggers );
   4 }
   5
   6 #
   7 # The "eofile" rule makes the parser fail if any "statement" rule
   8 # fails.  Otherwise, the first successful match by a "statement"
   9 # won't cause the failure needed to know that the parse, as a whole,
  10 # failed. -ky
  11 #
  12 startrule : statement(s) eofile {
  13     $return      = {
  14         tables   => \%tables,
  15         views    => \@views,
  16         triggers => \@triggers,
  17     }
  18 }
  19
  20 eofile : /^\Z/
  21
  22 statement :
  23     comment
  24     | create
  25     | <error>
  26
  27 comment : /^\s*-{2}.*\n/
  28     {
  29         my $comment =  $item[1];
  30         $comment    =~ s/^\s*(-{2})\s*//;
  31         $comment    =~ s/\s*$//;
  32         $return     = $comment;
  33     }
  34
  35
  36 create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
  37 {
  38     my $table_name = $item{'table_name'}{'name'};
  39     $return =  {
  40         table      => $table_name,
  41         schema     => $item{'trigger_name'}{'schema'},
  42         name       => $item{'trigger_name'}{'name'},
  43         when       => 'before',
  44         db_event   => $item{'type'}->{'event'},
  45         fields     => $item{'type'}{'fields'},
  46         condition  => $item{'triggered_action'}{'condition'},
  47         reference  => $item{'reference_b'},
  48         granularity => $item[9],
  49         action     => $item{'triggered_action'}{'statement'}
  50     };
  51
  52     push @triggers, $return;
  53 }
  54
  55 create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
  56 {
  57     my $table_name = $item{'table_name'}{'name'};
  58     $return = {
  59         table      => $table_name,
  60         schema     => $item{'trigger_name'}{'schema'},
  61         name       => $item{'trigger_name'}{'name'},
  62         when       => 'after',
  63         db_event   => $item{'type'}{'event'},
  64         fields     => $item{'type'}{'fields'},
  65         condition  => $item{'triggered_action'}{'condition'},
  66         reference  => $item{'reference_a'},
  67         granularity => $item[9],
  68         action     => $item{'triggered_action'}{'statement'}
  69     };
  70
  71     push @triggers, $return;
  72 }
  73
  74 create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
  75 {
  76     $return = {
  77         name   => $item{view_name}{name},
  78         sql    => $item{SQL_procedure_statement},
  79         with   => $item{'with_expression(?)'},
  80         fields => $item{'column_list(?)'}
  81     };
  82     push @views, $return;
  83 }
  84
  85 # create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
  86
  87 # col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )
  88
  89 with_expression: /WITH/i common_table_expression(s /,/)
  90 {
  91     $return = $item{'common_table_expression'};
  92 }
  93
  94 SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }
  95
  96 column_list: '(' column_name(s /,/) ')'
  97 {
  98     $return = join(' ', '(', @{$item[2]}, ')');
  99 }
 100
 101 CREATE: /create/i
 102
 103 TRIGGER: /trigger/i
 104
 105 VIEW: /view/i
 106
 107 INNER: /inner/i
 108
 109 LEFT: /left/i
 110
 111 RIGHT: /right/i
 112
 113 FULL: /full/i
 114
 115 OUTER: /outer/i
 116
 117 WHERE: /where/i
 118
 119 trigger_name: SCHEMA '.' NAME
 120     { $return = { schema => $item[1], name => $item[3] } }
 121     | NAME
 122     { $return = { name => $item[1] } }
 123
 124 table_name: SCHEMA '.' NAME
 125     { $return = { schema => $item[1], name => $item[3] } }
 126     | NAME
 127     { $return = { name => $item[1] } }
 128
 129 view_name: SCHEMA '.' NAME
 130     { $return = { schema => $item[1], name => $item[3] } }
 131     | NAME
 132     { $return = { name => $item[1] } }
 133
 134 column_name: NAME
 135
 136 identifier: NAME
 137
 138 correlation_name: NAME
 139
 140 numeric_constant: /\d+/
 141
 142 SCHEMA: /\w+/
 143
 144 SCHEMA: /\w{1,128}/
 145
 146 NAME: /\w+/
 147
 148 NAME: /\w{1,18}/
 149
 150 options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i
 151
 152 # root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
 153
 154 # subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
 155
 156 # oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
 157
 158 # with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)
 159
 160 # under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
 161
 162 common_table_expression: table_name column_list /AS/i get_bracketed
 163 {
 164     $return = { name  => $item{table_name}{name},
 165                 query => $item[4]
 166                 };
 167 }
 168
 169 get_bracketed:
 170 {
 171     extract_bracketed($text, '(');
 172 }
 173
 174 common_table_expression: table_name column_list /AS/i '(' fullselect ')'
 175
 176 # fullselect: ( subselect | '(' fullselect ')' | values_clause ) ( ( /UNION/i | /UNION/i /ALL/i | /EXCEPT/i | /EXCEPT/i /ALL/i | /INTERSECT/i | /INTERSECT/i /ALL/i ) ( subselect | '(' fullselect ')' | values_clause ) )(s)
 177
 178 # values_clause: /VALUES/i values_row(s /,/)
 179
 180 # values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'
 181
 182 # subselect:  select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
 183
 184 # select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )
 185
 186 # from_clause: /FROM/i table_name(s /,/)
 187
 188 # from_clause: /FROM/i table_reference(s /,/)
 189
 190 # table_reference:
 191 #     (
 192 #       ( nickname
 193 #       | table_name
 194 #       | view_name
 195 #       )
 196 #     | ( /ONLY/i
 197 #       | /OUTER/i
 198 #       ) '('
 199 #       ( table_name
 200 #       | view_name
 201 #       ) ')'
 202 #     ) correlation_clause(?)
 203 #   | TABLE '(' function_name '(' expression(s? /,/) ')' ')'  correlation_clause
 204 #   | TABLE(?) '(' fullselect ')' correlation_clause
 205 #   | joined_table
 206
 207
 208 # correlation_clause: /AS/i(?) correlation_name column_list(?)
 209
 210 # joined_table:
 211 #    table_reference ( INNER
 212 #                     | outer
 213 #                     )(?) JOIN table_reference ON join_condition
 214 #   | '(' joined_table ')'
 215
 216 # outer: ( LEFT | RIGHT | FULL ) OUTER(?)
 217
 218 where_clause: WHERE search_condition
 219
 220 # group_by_clause: /GROUP\s+BY/i ( grouping_expression
 221 #                                | grouping_sets
 222 #                                | super_groups
 223 #                                )(s /,/)
 224
 225 # grouping_expression: expression
 226
 227 # orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)
 228
 229 # sort_key: simple_column_name | simple_integer | sort_key_expression
 230
 231 # # Name of one of the selected columns!
 232 # simple_column_name: NAME
 233
 234 # simple_integer: /\d+/
 235 #   { $item[1] <= $numberofcolumns && $item[1] > 1 }
 236
 237 # sort_key_expression: expression
 238 #   { expression from select columns list, grouping_expression, column function.. }
 239
 240 # grouping_sets: /GROUPING\s+SETS/i '(' (
 241 #                                         ( grouping_expression
 242 #                                         | super_groups
 243 #                                         )
 244 #                                       | '(' ( grouping_expression
 245 #                                             | super_groups
 246 #                                             )(s /,/) ')'
 247 #                                       )(s /,/) ')'
 248
 249 # super_groups: /ROLLUP/i '(' grouping_expression_list ')'
 250 #            | /CUBE/i '(' grouping_expression_list ')'
 251 #            | grand_total
 252
 253 # grouping_expression_list:  ( grouping_expression
 254 #                            | '(' grouping_expression(s /,/) ')'
 255 #                            )(s /,/)
 256
 257 # grand_total: '(' ')'
 258
 259 # having_clause: /HAVING/i search_condition
 260
 261 when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
 262
 263 triggered_action: when_clause(?) SQL_procedure_statement
 264 { $return = { 'condition' => $item[1][0],
 265               'statement' => $item{'SQL_procedure_statement'} };
 266 }
 267
 268 before: /NO CASCADE BEFORE/i
 269
 270 after: /AFTER/i
 271
 272 type: /UPDATE/i /OF/i column_name(s /,/)
 273 { $return = { event  => 'update_on',
 274               fields => $item[3] }
 275 }
 276
 277 type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
 278 { $return = { event => $item[1] } }
 279
 280 reference_b: /REFERENCING/i old_new_corr(0..2)
 281 { $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
 282
 283 reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
 284 { $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]})  ) }
 285
 286 old_new_corr: /OLD/i /(AS)?/i correlation_name
 287 { $return = join(' ', @item[1..3] ) }
 288 | /NEW/i /(AS)?/i correlation_name
 289 { $return = join(' ', @item[1..3] ) }
 290
 291 old_new_table: /OLD_TABLE/i /(AS)?/i identifier
 292 { $return = join(' ', @item[1..3] ) }
 293 | /NEW_TABLE/i /(AS)?/i identifier
 294 { $return = join(' ', @item[1..3] ) }
 295
 296 # Just parsing simple search conditions for now.
 297 search_condition: /[^)]+/
 298
 299 expression: (
 300               ( '+'
 301               | '-'
 302               )(?)
 303               ( function
 304               | '(' expression ')'
 305               | constant
 306               | column_name
 307               | host_variable
 308               | special_register
 309               | '(' scalar_fullselect ')'
 310               | labeled_duration
 311               | case_expression
 312               | cast_specification
 313 #              | dereference_operation
 314               | OLAP_function
 315               | method_invocation
 316               | subtype_treatment
 317               | sequence_reference
 318               )
 319             )(s /operator/)
 320
 321 operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'
 322
 323 function: ( /SYSIBM\.|/i sysibm_function
 324           | /SYSFUN\.|/i sysfun_function
 325           | userdefined_function
 326           ) '(' func_args(s /,/)  ')'
 327
 328 constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const
 329
 330 func_args: expression
 331
 332 sysibm_function: ( /ABS/i | /ABSVAL/i )
 333                 | /AVG/i
 334                 | /BIGINT/i
 335                 | /BLOB/i
 336                 | /CHAR/i
 337                 | /CLOB/i
 338                 | /COALESCE/i
 339                 | ( /CONCAT/ | '||' )
 340                 | ( /CORRELATION/i | /CORR/ )
 341                 | /COUNT/i
 342                 | /COUNT_BIG/i
 343                 | (/COVARIANCE/i | /COVAR/i )
 344                 | /DATE/i
 345                 | /DAY/i
 346                 | /DAYS/i
 347                 | /DBCLOB/i
 348                 | ( /DECIMAL/i | /DEC/i )
 349                 | /DECRYPT_BIN/i
 350                 | /DECRYPT_CHAR/i
 351                 | /DEREF/i
 352                 | /DIGITS/i
 353                 | /DLCOMMENT/i
 354                 | /DLLINKTYPE/i
 355                 | /DLURLCOMPLETE/i
 356                 | /DLURLPATH/i
 357                 | /DLURLPATHONLY/i
 358                 | /DLURLSCHEME/i
 359                 | /DLURLSERVER/i
 360                 | /DLVALUE/i
 361                 | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
 362                 | /ENCRYPT/i
 363                 | /EVENT_MON_STATE/i
 364                 | /FLOAT/i
 365                 | /GETHINT/i
 366                 | /GENERATE_UNIQUE/i
 367                 | /GRAPHIC/i
 368                 | /GROUPING/i
 369                 | /HEX/i
 370                 | /HOUR/i
 371                 | /IDENTITY_VAL_LOCAL/i
 372                 | ( /INTEGER/i | /INT/ )
 373                 | ( /LCASE/i | /LOWER/ )
 374                 | /LENGTH/i
 375                 | /LONG_VARCHAR/i
 376                 | /LONG_VARGRAPHIC/i
 377                 | /LTRIM/i
 378                 | /MAX/i
 379                 | /MICROSECOND/i
 380                 | /MIN/i
 381                 | /MINUTE/i
 382                 | /MONTH/i
 383                 | /MULTIPLY_ACT/i
 384                 | /NODENUMBER/i
 385                 | /NULLIF/i
 386                 | /PARTITON/i
 387                 | /POSSTR/i
 388                 | /RAISE_ERROR/i
 389                 | /REAL/i
 390                 | /REC2XML/i
 391                 | /REGR_AVGX/i
 392                 | /REGR_AVGY/i
 393                 | /REGR_COUNT/i
 394                 | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
 395                 | /REGR_R2/i
 396                 | /REGR_SLOPE/i
 397                 | /REGR_SXX/i
 398                 | /REGR_SXY/i
 399                 | /REGR_SYY/i
 400                 | /RTRIM/i
 401                 | /SECOND/i
 402                 | /SMALLINT/i
 403                 | /STDDEV/i
 404                 | /SUBSTR/i
 405                 | /SUM/i
 406                 | /TABLE_NAME/i
 407                 | /TABLE_SCHEMA/i
 408                 | /TIME/i
 409                 | /TIMESTAMP/i
 410                 | /TRANSLATE/i
 411                 | /TYPE_ID/i
 412                 | /TYPE_NAME/i
 413                 | /TYPE_SCHEMA/i
 414                 | ( /UCASE/i | /UPPER/i )
 415                 | /VALUE/i
 416                 | /VARCHAR/i
 417                 | /VARGRAPHIC/i
 418                 | ( /VARIANCE/i | /VAR/i )
 419                 | /YEAR/i
 420
 421 sysfun: ( /ABS/i | /ABSVAL/i )
 422                 | /ACOS/i
 423                 | /ASCII/i
 424                 | /ASIN/i
 425                 | /ATAN/i
 426                 | /ATAN2/i
 427                 | ( /CEIL/i | /CEILING/i )
 428                 | /CHAR/i
 429                 | /CHR/i
 430                 | /COS/i
 431                 | /COT/i
 432                 | /DAYNAME/i
 433                 | /DAYOFWEEK/i
 434                 | /DAYOFWEEK_ISO/i
 435                 | /DAYOFYEAR/i
 436                 | /DEGREES/i
 437                 | /DIFFERENCE/i
 438                 | /DOUBLE/i
 439                 | /EXP/i
 440                 | /FLOOR/i
 441                 | /GET_ROUTINE_SAR/i
 442                 | /INSERT/i
 443                 | /JULIAN_DAY/i
 444                 | /LCASE/i
 445                 | /LEFT/i
 446                 | /LN/i
 447                 | /LOCATE/i
 448                 | /LOG/i
 449                 | /LOG10/i
 450                 | /LTRIM/i
 451                 | /MIDNIGHT_SECONDS/i
 452                 | /MOD/i
 453                 | /MONTHNAME/i
 454                 | /POWER/i
 455                 | /PUT_ROUTINE_SAR/i
 456                 | /QUARTER/i
 457                 | /RADIANS/i
 458                 | /RAND/i
 459                 | /REPEAT/i
 460                 | /REPLACE/i
 461                 | /RIGHT/i
 462                 | /ROUND/i
 463                 | /RTRIM/I
 464                 | /SIGN/i
 465                 | /SIN/i
 466                 | /SOUNDEX/i
 467                 | /SPACE/i
 468                 | /SQLCACHE_SNAPSHOT/i
 469                 | /SQRT/i
 470                 | /TAN/i
 471                 | /TIMESTAMP_ISO/i
 472                 | /TIMESTAMPDIFF/i
 473                 | ( /TRUNCATE/i | /TRUNC/i )
 474                 | /UCASE/i
 475                 | /WEEK/i
 476                 | /WEEK_ISO/i
 477
 478 scalar_fullselect: '(' fullselect ')'
 479
 480 labeled_duration: ld_type ld_duration
 481
 482 ld_type: function
 483        | '(' expression ')'
 484        | constant
 485        | column_name
 486        | host_variable
 487
 488 ld_duration: /YEARS?/i
 489            | /MONTHS?/i
 490            | /DAYS?/i
 491            | /HOURS?/i
 492            | /MINUTES?/i
 493            | /SECONDS?/i
 494            | /MICROSECONDS?/i
 495
 496 case_expression: /CASE/i ( searched_when_clause
 497                          | simple_when_clause
 498                          )
 499                          ( /ELSE\s+NULL/i
 500                          | /ELSE/i result_expression
 501                          )(?) /END/i
 502
 503 searched_when_clause: ( /WHEN/i search_condition /THEN/i
 504                         ( result_expression
 505                         | /NULL/i
 506                         )
 507                       )(s)
 508
 509 simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
 510                                  ( result_expression
 511                                  | /NULL/i
 512                                  )
 513                                )(s)
 514
 515 result_expression: expression
 516
 517 cast_specification: /CAST/i '(' ( expression
 518                                 | /NULL/i
 519                                 | parameter_marker
 520                                 ) /AS/i data_type
 521                                   ( /SCOPE/ ( typed_table_name
 522                                             | typed_view_name
 523                                             )
 524                                   )(?) ')'
 525
 526 dereference_operation: scoped_reference_expression '->' name1
 527                       (  '(' expression(s) ')' )(?)
 528 #                         ( '(' expression(s /,/) ')' )(?)
 529
 530
 531
 532 scoped_reference_expression: expression
 533 { # scoped, reference
 534 }
 535
 536 name1: NAME
 537
 538 OLAP_function: ranking_function
 539              | numbering_function
 540              | aggregation_function
 541
 542 ranking_function: ( /RANK/ '()'
 543                   | /DENSE_RANK|DENSERANK/i '()'
 544                   ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
 545
 546 numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
 547                       ( window_order_clause window_aggregation_group_clause(?)
 548                       )(?)
 549                       ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
 550                       | window_aggregation_group_clause
 551                       )(?) ')'
 552
 553 window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
 554
 555 window_order_clause: /ORDER\s+BY/i
 556                       ( sort_key_expression
 557                         ( asc_option
 558                         | desc_option
 559                         )(?)
 560                       )(s /,/)
 561
 562 asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
 563
 564 desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
 565
 566 window_aggregation_group_clause: ( /ROWS/i
 567                                  | /RANGE/i
 568                                  )
 569                                  ( group_start
 570                                  | group_between
 571                                  | group_end
 572                                  )
 573
 574 group_start: /UNBOUNDED\s+PRECEDING/i
 575            | unsigned_constant /PRECEDING/i
 576            | /CURRENT\s+ROW/i
 577
 578 group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
 579
 580 group_bound1: /UNBOUNDED\s+PRECEDING/i
 581            | unsigned_constant /PRECEDING/i
 582            | unsigned_constant /FOLLOWING/i
 583            | /CURRENT\s+ROW/i
 584
 585 group_bound2: /UNBOUNDED\s+PRECEDING/i
 586            | unsigned_constant /PRECEDING/i
 587            | unsigned_constant /FOLLOWING/i
 588            | /CURRENT\s+ROW/i
 589
 590 group_end: /UNBOUNDED\s+PRECEDING/i
 591            | unsigned_constant /FOLLOWING/i
 592
 593 method_invocation: subject_expression '..' method_name
 594                     ( '(' expression(s) ')'
 595 #                    ( '(' expression(s /,/) ')'
 596                     )(?)
 597
 598 subject_expression: expression
 599 { # with static result type that is a used-defined struct type
 600 }
 601
 602 method_name: NAME
 603 { # must be a method of subject_expression
 604 }
 605
 606 subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
 607
 608 sequence_reference: nextval_expression
 609                   | prevval_expression
 610
 611 nextval_expression: /NEXTVAL\s+FOR/i sequence_name
 612
 613 prevval_expression: /PREVVAL\s+FOR/i sequence_name
 614
 615 sequence_name: NAME
 616
 617
 618 search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)
 619
 620 cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )
 621
 622 predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p
 623
 624 basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression
 625
 626 quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'
 627