3 my ( %tables, $table_order, @table_comments, @views, @triggers );
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,
12 startrule : statement(s) eofile {
16 triggers => \@triggers,
27 comment : /^\s*-{2}.*\n/
29 my $comment = $item[1];
30 $comment =~ s/^\s*(-{2})\s*//;
36 create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
38 my $table_name = $item{'table_name'}{'name'};
41 schema => $item{'trigger_name'}{'schema'},
42 name => $item{'trigger_name'}{'name'},
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'}
52 push @triggers, $return;
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
57 my $table_name = $item{'table_name'}{'name'};
60 schema => $item{'trigger_name'}{'schema'},
61 name => $item{'trigger_name'}{'name'},
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'}
71 push @triggers, $return;
74 create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
77 name => $item{view_name}{name},
78 sql => $item{SQL_procedure_statement},
79 with => $item{'with_expression(?)'},
80 fields => $item{'column_list(?)'}
85 # create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
87 # col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )
89 with_expression: /WITH/i common_table_expression(s /,/)
91 $return = $item{'common_table_expression'};
94 SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }
96 column_list: '(' column_name(s /,/) ')'
98 $return = join(' ', '(', @{$item[2]}, ')');
119 trigger_name: SCHEMA '.' NAME
120 { $return = { schema => $item[1], name => $item[3] } }
122 { $return = { name => $item[1] } }
124 table_name: SCHEMA '.' NAME
125 { $return = { schema => $item[1], name => $item[3] } }
127 { $return = { name => $item[1] } }
129 view_name: SCHEMA '.' NAME
130 { $return = { schema => $item[1], name => $item[3] } }
132 { $return = { name => $item[1] } }
138 correlation_name: NAME
140 numeric_constant: /\d+/
150 options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i
152 # root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
154 # subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
156 # oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
158 # with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)
160 # under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
162 common_table_expression: table_name column_list /AS/i get_bracketed
164 $return = { name => $item{table_name}{name},
171 extract_bracketed($text, '(');
174 common_table_expression: table_name column_list /AS/i '(' fullselect ')'
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)
178 # values_clause: /VALUES/i values_row(s /,/)
180 # values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'
182 # subselect: select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
184 # select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )
186 # from_clause: /FROM/i table_name(s /,/)
188 # from_clause: /FROM/i table_reference(s /,/)
202 # ) correlation_clause(?)
203 # | TABLE '(' function_name '(' expression(s? /,/) ')' ')' correlation_clause
204 # | TABLE(?) '(' fullselect ')' correlation_clause
208 # correlation_clause: /AS/i(?) correlation_name column_list(?)
211 # table_reference ( INNER
213 # )(?) JOIN table_reference ON join_condition
214 # | '(' joined_table ')'
216 # outer: ( LEFT | RIGHT | FULL ) OUTER(?)
218 where_clause: WHERE search_condition
220 # group_by_clause: /GROUP\s+BY/i ( grouping_expression
225 # grouping_expression: expression
227 # orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)
229 # sort_key: simple_column_name | simple_integer | sort_key_expression
231 # # Name of one of the selected columns!
232 # simple_column_name: NAME
234 # simple_integer: /\d+/
235 # { $item[1] <= $numberofcolumns && $item[1] > 1 }
237 # sort_key_expression: expression
238 # { expression from select columns list, grouping_expression, column function.. }
240 # grouping_sets: /GROUPING\s+SETS/i '(' (
241 # ( grouping_expression
244 # | '(' ( grouping_expression
249 # super_groups: /ROLLUP/i '(' grouping_expression_list ')'
250 # | /CUBE/i '(' grouping_expression_list ')'
253 # grouping_expression_list: ( grouping_expression
254 # | '(' grouping_expression(s /,/) ')'
257 # grand_total: '(' ')'
259 # having_clause: /HAVING/i search_condition
261 when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
263 triggered_action: when_clause(?) SQL_procedure_statement
264 { $return = { 'condition' => $item[1][0],
265 'statement' => $item{'SQL_procedure_statement'} };
268 before: /NO CASCADE BEFORE/i
272 type: /UPDATE/i /OF/i column_name(s /,/)
273 { $return = { event => 'update_on',
277 type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
278 { $return = { event => $item[1] } }
280 reference_b: /REFERENCING/i old_new_corr(0..2)
281 { $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
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]}) ) }
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] ) }
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] ) }
296 # Just parsing simple search conditions for now.
297 search_condition: /[^)]+/
309 | '(' scalar_fullselect ')'
313 # | dereference_operation
321 operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'
323 function: ( /SYSIBM\.|/i sysibm_function
324 | /SYSFUN\.|/i sysfun_function
325 | userdefined_function
326 ) '(' func_args(s /,/) ')'
328 constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const
330 func_args: expression
332 sysibm_function: ( /ABS/i | /ABSVAL/i )
339 | ( /CONCAT/ | '||' )
340 | ( /CORRELATION/i | /CORR/ )
343 | (/COVARIANCE/i | /COVAR/i )
348 | ( /DECIMAL/i | /DEC/i )
361 | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
371 | /IDENTITY_VAL_LOCAL/i
372 | ( /INTEGER/i | /INT/ )
373 | ( /LCASE/i | /LOWER/ )
394 | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
414 | ( /UCASE/i | /UPPER/i )
418 | ( /VARIANCE/i | /VAR/i )
421 sysfun: ( /ABS/i | /ABSVAL/i )
427 | ( /CEIL/i | /CEILING/i )
451 | /MIDNIGHT_SECONDS/i
468 | /SQLCACHE_SNAPSHOT/i
473 | ( /TRUNCATE/i | /TRUNC/i )
478 scalar_fullselect: '(' fullselect ')'
480 labeled_duration: ld_type ld_duration
488 ld_duration: /YEARS?/i
496 case_expression: /CASE/i ( searched_when_clause
500 | /ELSE/i result_expression
503 searched_when_clause: ( /WHEN/i search_condition /THEN/i
509 simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
515 result_expression: expression
517 cast_specification: /CAST/i '(' ( expression
521 ( /SCOPE/ ( typed_table_name
526 dereference_operation: scoped_reference_expression '->' name1
527 ( '(' expression(s) ')' )(?)
528 # ( '(' expression(s /,/) ')' )(?)
532 scoped_reference_expression: expression
533 { # scoped, reference
538 OLAP_function: ranking_function
540 | aggregation_function
542 ranking_function: ( /RANK/ '()'
543 | /DENSE_RANK|DENSERANK/i '()'
544 ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
546 numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
547 ( window_order_clause window_aggregation_group_clause(?)
549 ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
550 | window_aggregation_group_clause
553 window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
555 window_order_clause: /ORDER\s+BY/i
556 ( sort_key_expression
562 asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
564 desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
566 window_aggregation_group_clause: ( /ROWS/i
574 group_start: /UNBOUNDED\s+PRECEDING/i
575 | unsigned_constant /PRECEDING/i
578 group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
580 group_bound1: /UNBOUNDED\s+PRECEDING/i
581 | unsigned_constant /PRECEDING/i
582 | unsigned_constant /FOLLOWING/i
585 group_bound2: /UNBOUNDED\s+PRECEDING/i
586 | unsigned_constant /PRECEDING/i
587 | unsigned_constant /FOLLOWING/i
590 group_end: /UNBOUNDED\s+PRECEDING/i
591 | unsigned_constant /FOLLOWING/i
593 method_invocation: subject_expression '..' method_name
594 ( '(' expression(s) ')'
595 # ( '(' expression(s /,/) ')'
598 subject_expression: expression
599 { # with static result type that is a used-defined struct type
603 { # must be a method of subject_expression
606 subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
608 sequence_reference: nextval_expression
611 nextval_expression: /NEXTVAL\s+FOR/i sequence_name
613 prevval_expression: /PREVVAL\s+FOR/i sequence_name
618 search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)
620 cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )
622 predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p
624 basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression
626 quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'