1 package SQL::Translator::Parser::DB2;
7 our @EXPORT_OK = qw(parse);
12 use SQL::Translator::Utils qw/ddl_parser_instance/;
14 our $GRAMMAR = <<'END_OF_GRAMMAR';
17 my ( %tables, $table_order, @table_comments, @views, @triggers );
21 # The "eofile" rule makes the parser fail if any "statement" rule
22 # fails. Otherwise, the first successful match by a "statement"
23 # won't cause the failure needed to know that the parse, as a whole,
26 startrule : statement(s) eofile {
30 triggers => \@triggers,
41 comment : /^\s*-{2}.*\n/
43 my $comment = $item[1];
44 $comment =~ s/^\s*(-{2})\s*//;
50 create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
52 my $table_name = $item{'table_name'}{'name'};
55 schema => $item{'trigger_name'}{'schema'},
56 name => $item{'trigger_name'}{'name'},
58 db_event => $item{'type'}->{'event'},
59 fields => $item{'type'}{'fields'},
60 condition => $item{'triggered_action'}{'condition'},
61 reference => $item{'reference_b'},
62 granularity => $item[9],
63 action => $item{'triggered_action'}{'statement'}
66 push @triggers, $return;
69 create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
71 my $table_name = $item{'table_name'}{'name'};
74 schema => $item{'trigger_name'}{'schema'},
75 name => $item{'trigger_name'}{'name'},
77 db_event => $item{'type'}{'event'},
78 fields => $item{'type'}{'fields'},
79 condition => $item{'triggered_action'}{'condition'},
80 reference => $item{'reference_a'},
81 granularity => $item[9],
82 action => $item{'triggered_action'}{'statement'}
85 push @triggers, $return;
88 create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
91 name => $item{view_name}{name},
92 sql => $item{SQL_procedure_statement},
93 with => $item{'with_expression(?)'},
94 fields => $item{'column_list(?)'}
99 # create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
101 # col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )
103 with_expression: /WITH/i common_table_expression(s /,/)
105 $return = $item{'common_table_expression'};
108 SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }
110 column_list: '(' column_name(s /,/) ')'
112 $return = join(' ', '(', @{$item[2]}, ')');
133 trigger_name: SCHEMA '.' NAME
134 { $return = { schema => $item[1], name => $item[3] } }
136 { $return = { name => $item[1] } }
138 table_name: SCHEMA '.' NAME
139 { $return = { schema => $item[1], name => $item[3] } }
141 { $return = { name => $item[1] } }
143 view_name: SCHEMA '.' NAME
144 { $return = { schema => $item[1], name => $item[3] } }
146 { $return = { name => $item[1] } }
152 correlation_name: NAME
154 numeric_constant: /\d+/
164 options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i
166 # root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
168 # subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
170 # oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
172 # with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)
174 # under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
176 common_table_expression: table_name column_list /AS/i get_bracketed
178 $return = { name => $item{table_name}{name},
185 extract_bracketed($text, '(');
188 common_table_expression: table_name column_list /AS/i '(' fullselect ')'
190 # 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)
192 # values_clause: /VALUES/i values_row(s /,/)
194 # values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'
196 # subselect: select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
198 # select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )
200 # from_clause: /FROM/i table_name(s /,/)
202 # from_clause: /FROM/i table_reference(s /,/)
216 # ) correlation_clause(?)
217 # | TABLE '(' function_name '(' expression(s? /,/) ')' ')' correlation_clause
218 # | TABLE(?) '(' fullselect ')' correlation_clause
222 # correlation_clause: /AS/i(?) correlation_name column_list(?)
225 # table_reference ( INNER
227 # )(?) JOIN table_reference ON join_condition
228 # | '(' joined_table ')'
230 # outer: ( LEFT | RIGHT | FULL ) OUTER(?)
232 where_clause: WHERE search_condition
234 # group_by_clause: /GROUP\s+BY/i ( grouping_expression
239 # grouping_expression: expression
241 # orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)
243 # sort_key: simple_column_name | simple_integer | sort_key_expression
245 # # Name of one of the selected columns!
246 # simple_column_name: NAME
248 # simple_integer: /\d+/
249 # { $item[1] <= $numberofcolumns && $item[1] > 1 }
251 # sort_key_expression: expression
252 # { expression from select columns list, grouping_expression, column function.. }
254 # grouping_sets: /GROUPING\s+SETS/i '(' (
255 # ( grouping_expression
258 # | '(' ( grouping_expression
263 # super_groups: /ROLLUP/i '(' grouping_expression_list ')'
264 # | /CUBE/i '(' grouping_expression_list ')'
267 # grouping_expression_list: ( grouping_expression
268 # | '(' grouping_expression(s /,/) ')'
271 # grand_total: '(' ')'
273 # having_clause: /HAVING/i search_condition
275 when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
277 triggered_action: when_clause(?) SQL_procedure_statement
278 { $return = { 'condition' => $item[1][0],
279 'statement' => $item{'SQL_procedure_statement'} };
282 before: /NO CASCADE BEFORE/i
286 type: /UPDATE/i /OF/i column_name(s /,/)
287 { $return = { event => 'update_on',
291 type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
292 { $return = { event => $item[1] } }
294 reference_b: /REFERENCING/i old_new_corr(0..2)
295 { $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
297 reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
298 { $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]}) ) }
300 old_new_corr: /OLD/i /(AS)?/i correlation_name
301 { $return = join(' ', @item[1..3] ) }
302 | /NEW/i /(AS)?/i correlation_name
303 { $return = join(' ', @item[1..3] ) }
305 old_new_table: /OLD_TABLE/i /(AS)?/i identifier
306 { $return = join(' ', @item[1..3] ) }
307 | /NEW_TABLE/i /(AS)?/i identifier
308 { $return = join(' ', @item[1..3] ) }
310 # Just parsing simple search conditions for now.
311 search_condition: /[^)]+/
323 | '(' scalar_fullselect ')'
327 # | dereference_operation
335 operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'
337 function: ( /SYSIBM\.|/i sysibm_function
338 | /SYSFUN\.|/i sysfun_function
339 | userdefined_function
340 ) '(' func_args(s /,/) ')'
342 constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const
344 func_args: expression
346 sysibm_function: ( /ABS/i | /ABSVAL/i )
353 | ( /CONCAT/ | '||' )
354 | ( /CORRELATION/i | /CORR/ )
357 | (/COVARIANCE/i | /COVAR/i )
362 | ( /DECIMAL/i | /DEC/i )
375 | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
385 | /IDENTITY_VAL_LOCAL/i
386 | ( /INTEGER/i | /INT/ )
387 | ( /LCASE/i | /LOWER/ )
408 | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
428 | ( /UCASE/i | /UPPER/i )
432 | ( /VARIANCE/i | /VAR/i )
435 sysfun: ( /ABS/i | /ABSVAL/i )
441 | ( /CEIL/i | /CEILING/i )
465 | /MIDNIGHT_SECONDS/i
482 | /SQLCACHE_SNAPSHOT/i
487 | ( /TRUNCATE/i | /TRUNC/i )
492 scalar_fullselect: '(' fullselect ')'
494 labeled_duration: ld_type ld_duration
502 ld_duration: /YEARS?/i
510 case_expression: /CASE/i ( searched_when_clause
514 | /ELSE/i result_expression
517 searched_when_clause: ( /WHEN/i search_condition /THEN/i
523 simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
529 result_expression: expression
531 cast_specification: /CAST/i '(' ( expression
535 ( /SCOPE/ ( typed_table_name
540 dereference_operation: scoped_reference_expression '->' name1
541 ( '(' expression(s) ')' )(?)
542 # ( '(' expression(s /,/) ')' )(?)
546 scoped_reference_expression: expression
547 { # scoped, reference
552 OLAP_function: ranking_function
554 | aggregation_function
556 ranking_function: ( /RANK/ '()'
557 | /DENSE_RANK|DENSERANK/i '()'
558 ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
560 numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
561 ( window_order_clause window_aggregation_group_clause(?)
563 ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
564 | window_aggregation_group_clause
567 window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
569 window_order_clause: /ORDER\s+BY/i
570 ( sort_key_expression
576 asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
578 desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
580 window_aggregation_group_clause: ( /ROWS/i
588 group_start: /UNBOUNDED\s+PRECEDING/i
589 | unsigned_constant /PRECEDING/i
592 group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
594 group_bound1: /UNBOUNDED\s+PRECEDING/i
595 | unsigned_constant /PRECEDING/i
596 | unsigned_constant /FOLLOWING/i
599 group_bound2: /UNBOUNDED\s+PRECEDING/i
600 | unsigned_constant /PRECEDING/i
601 | unsigned_constant /FOLLOWING/i
604 group_end: /UNBOUNDED\s+PRECEDING/i
605 | unsigned_constant /FOLLOWING/i
607 method_invocation: subject_expression '..' method_name
608 ( '(' expression(s) ')'
609 # ( '(' expression(s /,/) ')'
612 subject_expression: expression
613 { # with static result type that is a used-defined struct type
617 { # must be a method of subject_expression
620 subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
622 sequence_reference: nextval_expression
625 nextval_expression: /NEXTVAL\s+FOR/i sequence_name
627 prevval_expression: /PREVVAL\s+FOR/i sequence_name
632 search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)
634 cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )
636 predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p
638 basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression
640 quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'
645 my ( $translator, $data ) = @_;
647 # Enable warnings within the Parse::RecDescent module.
648 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
649 local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
650 local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
652 local $::RD_TRACE = $translator->trace ? 1 : undef;
653 local $DEBUG = $translator->debug;
655 my $parser = ddl_parser_instance('DB2');
657 my $result = $parser->startrule($data);
658 return $translator->error( "Parse failed." ) unless defined $result;
659 warn Dumper( $result ) if $DEBUG;
661 my $schema = $translator->schema;
664 sort { $a->[0] <=> $b->[0] }
665 map { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
666 keys %{ $result->{'tables'} };
668 for my $table_name ( @tables ) {
669 my $tdata = $result->{'tables'}{ $table_name };
670 my $table = $schema->add_table(
671 name => $tdata->{'name'},
672 ) or die $schema->error;
674 $table->comments( $tdata->{'comments'} );
676 for my $fdata ( @{ $tdata->{'fields'} } ) {
677 my $field = $table->add_field(
678 name => $fdata->{'name'},
679 data_type => $fdata->{'data_type'},
680 size => $fdata->{'size'},
681 default_value => $fdata->{'default'},
682 is_auto_increment => $fdata->{'is_auto_inc'},
683 is_nullable => $fdata->{'is_nullable'},
684 comments => $fdata->{'comments'},
685 ) or die $table->error;
687 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
689 for my $cdata ( @{ $fdata->{'constraints'} } ) {
690 next unless $cdata->{'type'} eq 'foreign_key';
691 $cdata->{'fields'} ||= [ $field->name ];
692 push @{ $tdata->{'constraints'} }, $cdata;
696 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
697 my $index = $table->add_index(
698 name => $idata->{'name'},
699 type => uc $idata->{'type'},
700 fields => $idata->{'fields'},
701 ) or die $table->error;
704 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
705 my $constraint = $table->add_constraint(
706 name => $cdata->{'name'},
707 type => $cdata->{'type'},
708 fields => $cdata->{'fields'},
709 reference_table => $cdata->{'reference_table'},
710 reference_fields => $cdata->{'reference_fields'},
711 match_type => $cdata->{'match_type'} || '',
712 on_delete => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
713 on_update => $cdata->{'on_update'} || $cdata->{'on_update_do'},
714 ) or die $table->error;
718 for my $def ( @{ $result->{'views'} || [] } ) {
719 my $view = $schema->add_view(
720 name => $def->{'name'},
721 sql => $def->{'sql'},
725 for my $def ( @{ $result->{'triggers'} || [] } ) {
726 my $trig = $schema->add_trigger(
727 name => $def->{'name'},
728 perform_action_when => $def->{'when'},
729 database_event => $def->{'db_event'},
730 action => $def->{'action'},
731 fields => $def->{'fields'},
732 on_table => $def->{'table'}
734 $trig->extra( reference => $def->{'reference'},
735 condition => $def->{'condition'},
736 granularity => $def->{'granularity'} );