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/;
33 # THIS GRAMMAR IS INCOMPLETE!!!
34 # Khisanth is slowly working on a replacement
36 our $GRAMMAR = <<'END_OF_GRAMMAR';
39 my ( %tables, $table_order, @table_comments, @views, @triggers );
43 # The "eofile" rule makes the parser fail if any "statement" rule
44 # fails. Otherwise, the first successful match by a "statement"
45 # won't cause the failure needed to know that the parse, as a whole,
48 startrule : statement(s) eofile {
52 triggers => \@triggers,
63 comment : /^\s*-{2}.*\n/
65 my $comment = $item[1];
66 $comment =~ s/^\s*(-{2})\s*//;
72 create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
74 my $table_name = $item{'table_name'}{'name'};
77 schema => $item{'trigger_name'}{'schema'},
78 name => $item{'trigger_name'}{'name'},
80 db_event => $item{'type'}->{'event'},
81 fields => $item{'type'}{'fields'},
82 condition => $item{'triggered_action'}{'condition'},
83 reference => $item{'reference_b'},
84 granularity => $item[9],
85 action => $item{'triggered_action'}{'statement'}
88 push @triggers, $return;
91 create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
93 my $table_name = $item{'table_name'}{'name'};
96 schema => $item{'trigger_name'}{'schema'},
97 name => $item{'trigger_name'}{'name'},
99 db_event => $item{'type'}{'event'},
100 fields => $item{'type'}{'fields'},
101 condition => $item{'triggered_action'}{'condition'},
102 reference => $item{'reference_a'},
103 granularity => $item[9],
104 action => $item{'triggered_action'}{'statement'}
107 push @triggers, $return;
110 create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
113 name => $item{view_name}{name},
114 sql => $item{SQL_procedure_statement},
115 with => $item{'with_expression(?)'},
116 fields => $item{'column_list(?)'}
118 push @views, $return;
121 # create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
123 # col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )
125 with_expression: /WITH/i common_table_expression(s /,/)
127 $return = $item{'common_table_expression'};
130 SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }
132 column_list: '(' column_name(s /,/) ')'
134 $return = join(' ', '(', @{$item[2]}, ')');
155 trigger_name: SCHEMA '.' NAME
156 { $return = { schema => $item[1], name => $item[3] } }
158 { $return = { name => $item[1] } }
160 table_name: SCHEMA '.' NAME
161 { $return = { schema => $item[1], name => $item[3] } }
163 { $return = { name => $item[1] } }
165 view_name: SCHEMA '.' NAME
166 { $return = { schema => $item[1], name => $item[3] } }
168 { $return = { name => $item[1] } }
174 correlation_name: NAME
176 numeric_constant: /\d+/
186 options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i
188 # root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
190 # subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
192 # oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
194 # with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)
196 # under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
198 common_table_expression: table_name column_list /AS/i get_bracketed
200 $return = { name => $item{table_name}{name},
207 extract_bracketed($text, '(');
210 common_table_expression: table_name column_list /AS/i '(' fullselect ')'
212 # 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)
214 # values_clause: /VALUES/i values_row(s /,/)
216 # values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'
218 # subselect: select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
220 # select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )
222 # from_clause: /FROM/i table_name(s /,/)
224 # from_clause: /FROM/i table_reference(s /,/)
238 # ) correlation_clause(?)
239 # | TABLE '(' function_name '(' expression(s? /,/) ')' ')' correlation_clause
240 # | TABLE(?) '(' fullselect ')' correlation_clause
244 # correlation_clause: /AS/i(?) correlation_name column_list(?)
247 # table_reference ( INNER
249 # )(?) JOIN table_reference ON join_condition
250 # | '(' joined_table ')'
252 # outer: ( LEFT | RIGHT | FULL ) OUTER(?)
254 where_clause: WHERE search_condition
256 # group_by_clause: /GROUP\s+BY/i ( grouping_expression
261 # grouping_expression: expression
263 # orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)
265 # sort_key: simple_column_name | simple_integer | sort_key_expression
267 # # Name of one of the selected columns!
268 # simple_column_name: NAME
270 # simple_integer: /\d+/
271 # { $item[1] <= $numberofcolumns && $item[1] > 1 }
273 # sort_key_expression: expression
274 # { expression from select columns list, grouping_expression, column function.. }
276 # grouping_sets: /GROUPING\s+SETS/i '(' (
277 # ( grouping_expression
280 # | '(' ( grouping_expression
285 # super_groups: /ROLLUP/i '(' grouping_expression_list ')'
286 # | /CUBE/i '(' grouping_expression_list ')'
289 # grouping_expression_list: ( grouping_expression
290 # | '(' grouping_expression(s /,/) ')'
293 # grand_total: '(' ')'
295 # having_clause: /HAVING/i search_condition
297 when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
299 triggered_action: when_clause(?) SQL_procedure_statement
300 { $return = { 'condition' => $item[1][0],
301 'statement' => $item{'SQL_procedure_statement'} };
304 before: /NO CASCADE BEFORE/i
308 type: /UPDATE/i /OF/i column_name(s /,/)
309 { $return = { event => 'update_on',
313 type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
314 { $return = { event => $item[1] } }
316 reference_b: /REFERENCING/i old_new_corr(0..2)
317 { $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
319 reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
320 { $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]}) ) }
322 old_new_corr: /OLD/i /(AS)?/i correlation_name
323 { $return = join(' ', @item[1..3] ) }
324 | /NEW/i /(AS)?/i correlation_name
325 { $return = join(' ', @item[1..3] ) }
327 old_new_table: /OLD_TABLE/i /(AS)?/i identifier
328 { $return = join(' ', @item[1..3] ) }
329 | /NEW_TABLE/i /(AS)?/i identifier
330 { $return = join(' ', @item[1..3] ) }
332 # Just parsing simple search conditions for now.
333 search_condition: /[^)]+/
345 | '(' scalar_fullselect ')'
349 # | dereference_operation
357 operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'
359 function: ( /SYSIBM\.|/i sysibm_function
360 | /SYSFUN\.|/i sysfun_function
361 | userdefined_function
362 ) '(' func_args(s /,/) ')'
364 constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const
366 func_args: expression
368 sysibm_function: ( /ABS/i | /ABSVAL/i )
375 | ( /CONCAT/ | '||' )
376 | ( /CORRELATION/i | /CORR/ )
379 | (/COVARIANCE/i | /COVAR/i )
384 | ( /DECIMAL/i | /DEC/i )
397 | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
407 | /IDENTITY_VAL_LOCAL/i
408 | ( /INTEGER/i | /INT/ )
409 | ( /LCASE/i | /LOWER/ )
430 | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
450 | ( /UCASE/i | /UPPER/i )
454 | ( /VARIANCE/i | /VAR/i )
457 sysfun: ( /ABS/i | /ABSVAL/i )
463 | ( /CEIL/i | /CEILING/i )
487 | /MIDNIGHT_SECONDS/i
504 | /SQLCACHE_SNAPSHOT/i
509 | ( /TRUNCATE/i | /TRUNC/i )
514 scalar_fullselect: '(' fullselect ')'
516 labeled_duration: ld_type ld_duration
524 ld_duration: /YEARS?/i
532 case_expression: /CASE/i ( searched_when_clause
536 | /ELSE/i result_expression
539 searched_when_clause: ( /WHEN/i search_condition /THEN/i
545 simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
551 result_expression: expression
553 cast_specification: /CAST/i '(' ( expression
557 ( /SCOPE/ ( typed_table_name
562 dereference_operation: scoped_reference_expression '->' name1
563 ( '(' expression(s) ')' )(?)
564 # ( '(' expression(s /,/) ')' )(?)
568 scoped_reference_expression: expression
569 { # scoped, reference
574 OLAP_function: ranking_function
576 | aggregation_function
578 ranking_function: ( /RANK/ '()'
579 | /DENSE_RANK|DENSERANK/i '()'
580 ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
582 numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
583 ( window_order_clause window_aggregation_group_clause(?)
585 ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
586 | window_aggregation_group_clause
589 window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
591 window_order_clause: /ORDER\s+BY/i
592 ( sort_key_expression
598 asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
600 desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)
602 window_aggregation_group_clause: ( /ROWS/i
610 group_start: /UNBOUNDED\s+PRECEDING/i
611 | unsigned_constant /PRECEDING/i
614 group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
616 group_bound1: /UNBOUNDED\s+PRECEDING/i
617 | unsigned_constant /PRECEDING/i
618 | unsigned_constant /FOLLOWING/i
621 group_bound2: /UNBOUNDED\s+PRECEDING/i
622 | unsigned_constant /PRECEDING/i
623 | unsigned_constant /FOLLOWING/i
626 group_end: /UNBOUNDED\s+PRECEDING/i
627 | unsigned_constant /FOLLOWING/i
629 method_invocation: subject_expression '..' method_name
630 ( '(' expression(s) ')'
631 # ( '(' expression(s /,/) ')'
634 subject_expression: expression
635 { # with static result type that is a used-defined struct type
639 { # must be a method of subject_expression
642 subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
644 sequence_reference: nextval_expression
647 nextval_expression: /NEXTVAL\s+FOR/i sequence_name
649 prevval_expression: /PREVVAL\s+FOR/i sequence_name
654 search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)
656 cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )
658 predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p
660 basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression
662 quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'
667 my ( $translator, $data ) = @_;
669 # Enable warnings within the Parse::RecDescent module.
670 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
671 local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
672 local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
674 local $::RD_TRACE = $translator->trace ? 1 : undef;
675 local $DEBUG = $translator->debug;
677 my $parser = ddl_parser_instance('DB2');
679 my $result = $parser->startrule($data);
680 return $translator->error( "Parse failed." ) unless defined $result;
681 warn Dumper( $result ) if $DEBUG;
683 my $schema = $translator->schema;
686 sort { $a->[0] <=> $b->[0] }
687 map { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
688 keys %{ $result->{'tables'} };
690 for my $table_name ( @tables ) {
691 my $tdata = $result->{'tables'}{ $table_name };
692 my $table = $schema->add_table(
693 name => $tdata->{'name'},
694 ) or die $schema->error;
696 $table->comments( $tdata->{'comments'} );
698 for my $fdata ( @{ $tdata->{'fields'} } ) {
699 my $field = $table->add_field(
700 name => $fdata->{'name'},
701 data_type => $fdata->{'data_type'},
702 size => $fdata->{'size'},
703 default_value => $fdata->{'default'},
704 is_auto_increment => $fdata->{'is_auto_inc'},
705 is_nullable => $fdata->{'is_nullable'},
706 comments => $fdata->{'comments'},
707 ) or die $table->error;
709 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
711 for my $cdata ( @{ $fdata->{'constraints'} } ) {
712 next unless $cdata->{'type'} eq 'foreign_key';
713 $cdata->{'fields'} ||= [ $field->name ];
714 push @{ $tdata->{'constraints'} }, $cdata;
718 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
719 my $index = $table->add_index(
720 name => $idata->{'name'},
721 type => uc $idata->{'type'},
722 fields => $idata->{'fields'},
723 ) or die $table->error;
726 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
727 my $constraint = $table->add_constraint(
728 name => $cdata->{'name'},
729 type => $cdata->{'type'},
730 fields => $cdata->{'fields'},
731 reference_table => $cdata->{'reference_table'},
732 reference_fields => $cdata->{'reference_fields'},
733 match_type => $cdata->{'match_type'} || '',
734 on_delete => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
735 on_update => $cdata->{'on_update'} || $cdata->{'on_update_do'},
736 ) or die $table->error;
740 for my $def ( @{ $result->{'views'} || [] } ) {
741 my $view = $schema->add_view(
742 name => $def->{'name'},
743 sql => $def->{'sql'},
747 for my $def ( @{ $result->{'triggers'} || [] } ) {
748 my $trig = $schema->add_trigger(
749 name => $def->{'name'},
750 perform_action_when => $def->{'when'},
751 database_event => $def->{'db_event'},
752 action => $def->{'action'},
753 fields => $def->{'fields'},
754 on_table => $def->{'table'}
756 $trig->extra( reference => $def->{'reference'},
757 condition => $def->{'condition'},
758 granularity => $def->{'granularity'} );
770 Jess Robinson <cpan@desert-island.me.uk>
774 perl(1), Parse::RecDescent, SQL::Translator::Schema.