1 package SQL::Translator::Parser::DB2;
5 SQL::Translator::Parser::DB2 - parser for DB2
10 use SQL::Translator::Parser::DB2;
12 my $translator = SQL::Translator->new;
13 $translator->parser("SQL::Translator::Parser::DB2");
17 This is a grammar for parsing CREATE statements for DB2
24 use base qw(Exporter);
25 our @EXPORT_OK = qw(parse);
30 use SQL::Translator::Utils qw/ddl_parser_instance/;
32 our $GRAMMAR = <<'END_OF_GRAMMAR';
35 my ( %tables, $table_order, @table_comments, @views, @triggers );
39 # The "eofile" rule makes the parser fail if any "statement" rule
40 # fails. Otherwise, the first successful match by a "statement"
41 # won't cause the failure needed to know that the parse, as a whole,
44 startrule : statement(s) eofile {
48 triggers => \@triggers,
59 comment : /^\s*-{2}.*\n/
61 my $comment = $item[1];
62 $comment =~ s/^\s*(-{2})\s*//;
68 create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
70 my $table_name = $item{'table_name'}{'name'};
73 schema => $item{'trigger_name'}{'schema'},
74 name => $item{'trigger_name'}{'name'},
76 db_event => $item{'type'}->{'event'},
77 fields => $item{'type'}{'fields'},
78 condition => $item{'triggered_action'}{'condition'},
79 reference => $item{'reference_b'},
80 granularity => $item[9],
81 action => $item{'triggered_action'}{'statement'}
84 push @triggers, $return;
87 create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
89 my $table_name = $item{'table_name'}{'name'};
92 schema => $item{'trigger_name'}{'schema'},
93 name => $item{'trigger_name'}{'name'},
95 db_event => $item{'type'}{'event'},
96 fields => $item{'type'}{'fields'},
97 condition => $item{'triggered_action'}{'condition'},
98 reference => $item{'reference_a'},
99 granularity => $item[9],
100 action => $item{'triggered_action'}{'statement'}
103 push @triggers, $return;
106 create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
109 name => $item{view_name}{name},
110 sql => $item{SQL_procedure_statement},
111 with => $item{'with_expression(?)'},
112 fields => $item{'column_list(?)'}
114 push @views, $return;
117 # create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
119 # col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )
121 with_expression: /WITH/i common_table_expression(s /,/)
123 $return = $item{'common_table_expression'};
126 SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }
128 column_list: '(' column_name(s /,/) ')'
130 $return = join(' ', '(', @{$item[2]}, ')');
151 trigger_name: SCHEMA '.' NAME
152 { $return = { schema => $item[1], name => $item[3] } }
154 { $return = { name => $item[1] } }
156 table_name: SCHEMA '.' NAME
157 { $return = { schema => $item[1], name => $item[3] } }
159 { $return = { name => $item[1] } }
161 view_name: SCHEMA '.' NAME
162 { $return = { schema => $item[1], name => $item[3] } }
164 { $return = { name => $item[1] } }
170 correlation_name: NAME
172 numeric_constant: /\d+/
182 options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i
184 # root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
186 # subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
188 # oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
190 # with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)
192 # under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
194 common_table_expression: table_name column_list /AS/i get_bracketed
196 $return = { name => $item{table_name}{name},
203 extract_bracketed($text, '(');
206 common_table_expression: table_name column_list /AS/i '(' fullselect ')'
208 # 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)
210 # values_clause: /VALUES/i values_row(s /,/)
212 # values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'
214 # subselect: select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
216 # select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )
218 # from_clause: /FROM/i table_name(s /,/)
220 # from_clause: /FROM/i table_reference(s /,/)
234 # ) correlation_clause(?)
235 # | TABLE '(' function_name '(' expression(s? /,/) ')' ')' correlation_clause
236 # | TABLE(?) '(' fullselect ')' correlation_clause
240 # correlation_clause: /AS/i(?) correlation_name column_list(?)
243 # table_reference ( INNER
245 # )(?) JOIN table_reference ON join_condition
246 # | '(' joined_table ')'
248 # outer: ( LEFT | RIGHT | FULL ) OUTER(?)
250 where_clause: WHERE search_condition
252 # group_by_clause: /GROUP\s+BY/i ( grouping_expression
257 # grouping_expression: expression
259 # orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)
261 # sort_key: simple_column_name | simple_integer | sort_key_expression
263 # # Name of one of the selected columns!
264 # simple_column_name: NAME
266 # simple_integer: /\d+/
267 # { $item[1] <= $numberofcolumns && $item[1] > 1 }
269 # sort_key_expression: expression
270 # { expression from select columns list, grouping_expression, column function.. }
272 # grouping_sets: /GROUPING\s+SETS/i '(' (
273 # ( grouping_expression
276 # | '(' ( grouping_expression
281 # super_groups: /ROLLUP/i '(' grouping_expression_list ')'
282 # | /CUBE/i '(' grouping_expression_list ')'
285 # grouping_expression_list: ( grouping_expression
286 # | '(' grouping_expression(s /,/) ')'
289 # grand_total: '(' ')'
291 # having_clause: /HAVING/i search_condition
293 when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
295 triggered_action: when_clause(?) SQL_procedure_statement
296 { $return = { 'condition' => $item[1][0],
297 'statement' => $item{'SQL_procedure_statement'} };
300 before: /NO CASCADE BEFORE/i
304 type: /UPDATE/i /OF/i column_name(s /,/)
305 { $return = { event => 'update_on',
309 type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
310 { $return = { event => $item[1] } }
312 reference_b: /REFERENCING/i old_new_corr(0..2)
313 { $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
315 reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
316 { $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]}) ) }
318 old_new_corr: /OLD/i /(AS)?/i correlation_name
319 { $return = join(' ', @item[1..3] ) }
320 | /NEW/i /(AS)?/i correlation_name
321 { $return = join(' ', @item[1..3] ) }
323 old_new_table: /OLD_TABLE/i /(AS)?/i identifier
324 { $return = join(' ', @item[1..3] ) }
325 | /NEW_TABLE/i /(AS)?/i identifier
326 { $return = join(' ', @item[1..3] ) }
328 # Just parsing simple search conditions for now.
329 search_condition: /[^)]+/
341 | '(' scalar_fullselect ')'
345 # | dereference_operation
353 operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'
355 function: ( /SYSIBM\.|/i sysibm_function
356 | /SYSFUN\.|/i sysfun_function
357 | userdefined_function
358 ) '(' func_args(s /,/) ')'
360 constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const
362 func_args: expression
364 sysibm_function: ( /ABS/i | /ABSVAL/i )
371 | ( /CONCAT/ | '||' )
372 | ( /CORRELATION/i | /CORR/ )
375 | (/COVARIANCE/i | /COVAR/i )
380 | ( /DECIMAL/i | /DEC/i )
393 | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
403 | /IDENTITY_VAL_LOCAL/i
404 | ( /INTEGER/i | /INT/ )
405 | ( /LCASE/i | /LOWER/ )
426 | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
446 | ( /UCASE/i | /UPPER/i )
450 | ( /VARIANCE/i | /VAR/i )
453 sysfun: ( /ABS/i | /ABSVAL/i )
459 | ( /CEIL/i | /CEILING/i )
483 | /MIDNIGHT_SECONDS/i
500 | /SQLCACHE_SNAPSHOT/i
505 | ( /TRUNCATE/i | /TRUNC/i )
510 scalar_fullselect: '(' fullselect ')'
512 labeled_duration: ld_type ld_duration
520 ld_duration: /YEARS?/i
528 case_expression: /CASE/i ( searched_when_clause
532 | /ELSE/i result_expression
535 searched_when_clause: ( /WHEN/i search_condition /THEN/i
541 simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
547 result_expression: expression
549 cast_specification: /CAST/i '(' ( expression
553 ( /SCOPE/ ( typed_table_name
558 dereference_operation: scoped_reference_expression '->' name1
559 ( '(' expression(s) ')' )(?)
560 # ( '(' expression(s /,/) ')' )(?)
564 scoped_reference_expression: expression
565 { # scoped, reference
570 OLAP_function: ranking_function
572 | aggregation_function
574 ranking_function: ( /RANK/ '()'
575 | /DENSE_RANK|DENSERANK/i '()'
576 ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
578 numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
579 ( window_order_clause window_aggregation_group_clause(?)
581 ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
582 | window_aggregation_group_clause
585 window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
587 window_order_clause: /ORDER\s+BY/i
588 ( sort_key_expression
594 asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
596 desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
598 window_aggregation_group_clause: ( /ROWS/i
606 group_start: /UNBOUNDED\s+PRECEDING/i
607 | unsigned_constant /PRECEDING/i
610 group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
612 group_bound1: /UNBOUNDED\s+PRECEDING/i
613 | unsigned_constant /PRECEDING/i
614 | unsigned_constant /FOLLOWING/i
617 group_bound2: /UNBOUNDED\s+PRECEDING/i
618 | unsigned_constant /PRECEDING/i
619 | unsigned_constant /FOLLOWING/i
622 group_end: /UNBOUNDED\s+PRECEDING/i
623 | unsigned_constant /FOLLOWING/i
625 method_invocation: subject_expression '..' method_name
626 ( '(' expression(s) ')'
627 # ( '(' expression(s /,/) ')'
630 subject_expression: expression
631 { # with static result type that is a used-defined struct type
635 { # must be a method of subject_expression
638 subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
640 sequence_reference: nextval_expression
643 nextval_expression: /NEXTVAL\s+FOR/i sequence_name
645 prevval_expression: /PREVVAL\s+FOR/i sequence_name
650 search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)
652 cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )
654 predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p
656 basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression
658 quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'
663 my ( $translator, $data ) = @_;
665 # Enable warnings within the Parse::RecDescent module.
666 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
667 local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
668 local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
670 local $::RD_TRACE = $translator->trace ? 1 : undef;
671 local $DEBUG = $translator->debug;
673 my $parser = ddl_parser_instance('DB2');
675 my $result = $parser->startrule($data);
676 return $translator->error( "Parse failed." ) unless defined $result;
677 warn Dumper( $result ) if $DEBUG;
679 my $schema = $translator->schema;
682 sort { $a->[0] <=> $b->[0] }
683 map { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
684 keys %{ $result->{'tables'} };
686 for my $table_name ( @tables ) {
687 my $tdata = $result->{'tables'}{ $table_name };
688 my $table = $schema->add_table(
689 name => $tdata->{'name'},
690 ) or die $schema->error;
692 $table->comments( $tdata->{'comments'} );
694 for my $fdata ( @{ $tdata->{'fields'} } ) {
695 my $field = $table->add_field(
696 name => $fdata->{'name'},
697 data_type => $fdata->{'data_type'},
698 size => $fdata->{'size'},
699 default_value => $fdata->{'default'},
700 is_auto_increment => $fdata->{'is_auto_inc'},
701 is_nullable => $fdata->{'is_nullable'},
702 comments => $fdata->{'comments'},
703 ) or die $table->error;
705 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
707 for my $cdata ( @{ $fdata->{'constraints'} } ) {
708 next unless $cdata->{'type'} eq 'foreign_key';
709 $cdata->{'fields'} ||= [ $field->name ];
710 push @{ $tdata->{'constraints'} }, $cdata;
714 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
715 my $index = $table->add_index(
716 name => $idata->{'name'},
717 type => uc $idata->{'type'},
718 fields => $idata->{'fields'},
719 ) or die $table->error;
722 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
723 my $constraint = $table->add_constraint(
724 name => $cdata->{'name'},
725 type => $cdata->{'type'},
726 fields => $cdata->{'fields'},
727 reference_table => $cdata->{'reference_table'},
728 reference_fields => $cdata->{'reference_fields'},
729 match_type => $cdata->{'match_type'} || '',
730 on_delete => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
731 on_update => $cdata->{'on_update'} || $cdata->{'on_update_do'},
732 ) or die $table->error;
736 for my $def ( @{ $result->{'views'} || [] } ) {
737 my $view = $schema->add_view(
738 name => $def->{'name'},
739 sql => $def->{'sql'},
743 for my $def ( @{ $result->{'triggers'} || [] } ) {
744 my $trig = $schema->add_trigger(
745 name => $def->{'name'},
746 perform_action_when => $def->{'when'},
747 database_event => $def->{'db_event'},
748 action => $def->{'action'},
749 fields => $def->{'fields'},
750 on_table => $def->{'table'}
752 $trig->extra( reference => $def->{'reference'},
753 condition => $def->{'condition'},
754 granularity => $def->{'granularity'} );
766 Jess Robinson <cpan@desert-island.me.uk>
770 perl(1), Parse::RecDescent, SQL::Translator::Schema.