[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / DB2.pm

package SQL::Translator::Parser::DB2;

=head1 NAME

SQL::Translator::Parser::DB2 - parser for DB2

=head1 SYNOPSIS

  use SQL::Translator;
  use SQL::Translator::Parser::DB2;

  my $translator = SQL::Translator->new;
  $translator->parser("SQL::Translator::Parser::DB2");

=head1 DESCRIPTION

This is a grammar for parsing CREATE statements for DB2

=cut

use warnings;
use strict;

use base qw(Exporter);
our @EXPORT_OK = qw(parse);

our $DEBUG;

use Data::Dumper;
use SQL::Translator::Utils qw/ddl_parser_instance/;

# !!!!!!
# THIS GRAMMAR IS INCOMPLETE!!!
# Khisanth is slowly working on a replacement
# !!!!!!
our $GRAMMAR = <<'END_OF_GRAMMAR';

{
    my ( %tables, $table_order, @table_comments, @views, @triggers );
}

#
# The "eofile" rule makes the parser fail if any "statement" rule
# fails.  Otherwise, the first successful match by a "statement"
# won't cause the failure needed to know that the parse, as a whole,
# failed. -ky
#
startrule : statement(s) eofile {
    $return      = {
        tables   => \%tables,
        views    => \@views,
        triggers => \@triggers,
    }
}

eofile : /^\Z/

statement :
    comment
    | create
    | <error>

comment : /^\s*-{2}.*\n/
    {
        my $comment =  $item[1];
        $comment    =~ s/^\s*(-{2})\s*//;
        $comment    =~ s/\s*$//;
        $return     = $comment;
    }


create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
{
    my $table_name = $item{'table_name'}{'name'};
    $return =  {
        table      => $table_name,
        schema     => $item{'trigger_name'}{'schema'},
        name       => $item{'trigger_name'}{'name'},
        when       => 'before',
        db_event   => $item{'type'}->{'event'},
        fields     => $item{'type'}{'fields'},
        condition  => $item{'triggered_action'}{'condition'},
        reference  => $item{'reference_b'},
        granularity => $item[9],
        action     => $item{'triggered_action'}{'statement'}
    };

    push @triggers, $return;
}

create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
{
    my $table_name = $item{'table_name'}{'name'};
    $return = {
        table      => $table_name,
        schema     => $item{'trigger_name'}{'schema'},
        name       => $item{'trigger_name'}{'name'},
        when       => 'after',
        db_event   => $item{'type'}{'event'},
        fields     => $item{'type'}{'fields'},
        condition  => $item{'triggered_action'}{'condition'},
        reference  => $item{'reference_a'},
        granularity => $item[9],
        action     => $item{'triggered_action'}{'statement'}
    };

    push @triggers, $return;
}

create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
{
    $return = {
        name   => $item{view_name}{name},
        sql    => $item{SQL_procedure_statement},
        with   => $item{'with_expression(?)'},
        fields => $item{'column_list(?)'}
    };
    push @views, $return;
}

# create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)

# col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )

with_expression: /WITH/i common_table_expression(s /,/)
{
    $return = $item{'common_table_expression'};
}

SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }

column_list: '(' column_name(s /,/) ')'
{
    $return = join(' ', '(', @{$item[2]}, ')');
}

CREATE: /create/i

TRIGGER: /trigger/i

VIEW: /view/i

INNER: /inner/i

LEFT: /left/i

RIGHT: /right/i

FULL: /full/i

OUTER: /outer/i

WHERE: /where/i

trigger_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } }

table_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } }

view_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } }

column_name: NAME

identifier: NAME

correlation_name: NAME

numeric_constant: /\d+/

SCHEMA: /\w+/

SCHEMA: /\w{1,128}/

NAME: /\w+/

NAME: /\w{1,18}/

options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i

# root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'

# subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)

# oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)

# with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)

# under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i

common_table_expression: table_name column_list /AS/i get_bracketed
{
    $return = { name  => $item{table_name}{name},
                query => $item[4]
                };
}

get_bracketed:
{
    extract_bracketed($text, '(');
}

common_table_expression: table_name column_list /AS/i '(' fullselect ')'

# 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)

# values_clause: /VALUES/i values_row(s /,/)

# values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'

# subselect:  select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)

# select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )

# from_clause: /FROM/i table_name(s /,/)

# from_clause: /FROM/i table_reference(s /,/)

# table_reference:
#     (
#       ( nickname
#       | table_name
#       | view_name
#       )
#     | ( /ONLY/i
#       | /OUTER/i
#       ) '('
#       ( table_name
#       | view_name
#       ) ')'
#     ) correlation_clause(?)
#   | TABLE '(' function_name '(' expression(s? /,/) ')' ')'  correlation_clause
#   | TABLE(?) '(' fullselect ')' correlation_clause
#   | joined_table


# correlation_clause: /AS/i(?) correlation_name column_list(?)

# joined_table:
#    table_reference ( INNER
#                     | outer
#                     )(?) JOIN table_reference ON join_condition
#   | '(' joined_table ')'

# outer: ( LEFT | RIGHT | FULL ) OUTER(?)

where_clause: WHERE search_condition

# group_by_clause: /GROUP\s+BY/i ( grouping_expression
#                                | grouping_sets
#                                | super_groups
#                                )(s /,/)

# grouping_expression: expression

# orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)

# sort_key: simple_column_name | simple_integer | sort_key_expression

# # Name of one of the selected columns!
# simple_column_name: NAME

# simple_integer: /\d+/
#   { $item[1] <= $numberofcolumns && $item[1] > 1 }

# sort_key_expression: expression
#   { expression from select columns list, grouping_expression, column function.. }

# grouping_sets: /GROUPING\s+SETS/i '(' (
#                                         ( grouping_expression
#                                         | super_groups
#                                         )
#                                       | '(' ( grouping_expression
#                                             | super_groups
#                                             )(s /,/) ')'
#                                       )(s /,/) ')'

# super_groups: /ROLLUP/i '(' grouping_expression_list ')'
#            | /CUBE/i '(' grouping_expression_list ')'
#            | grand_total

# grouping_expression_list:  ( grouping_expression
#                            | '(' grouping_expression(s /,/) ')'
#                            )(s /,/)

# grand_total: '(' ')'

# having_clause: /HAVING/i search_condition

when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}

triggered_action: when_clause(?) SQL_procedure_statement
{ $return = { 'condition' => $item[1][0],
              'statement' => $item{'SQL_procedure_statement'} };
}

before: /NO CASCADE BEFORE/i

after: /AFTER/i

type: /UPDATE/i /OF/i column_name(s /,/)
{ $return = { event  => 'update_on',
              fields => $item[3] }
}

type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
{ $return = { event => $item[1] } }

reference_b: /REFERENCING/i old_new_corr(0..2)
{ $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }

reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
{ $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]})  ) }

old_new_corr: /OLD/i /(AS)?/i correlation_name
{ $return = join(' ', @item[1..3] ) }
| /NEW/i /(AS)?/i correlation_name
{ $return = join(' ', @item[1..3] ) }

old_new_table: /OLD_TABLE/i /(AS)?/i identifier
{ $return = join(' ', @item[1..3] ) }
| /NEW_TABLE/i /(AS)?/i identifier
{ $return = join(' ', @item[1..3] ) }

# Just parsing simple search conditions for now.
search_condition: /[^)]+/

expression: (
              ( '+'
              | '-'
              )(?)
              ( function
              | '(' expression ')'
              | constant
              | column_name
              | host_variable
              | special_register
              | '(' scalar_fullselect ')'
              | labeled_duration
              | case_expression
              | cast_specification
#              | dereference_operation
              | OLAP_function
              | method_invocation
              | subtype_treatment
              | sequence_reference
              )
            )(s /operator/)

operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-'

function: ( /SYSIBM\.|/i sysibm_function
          | /SYSFUN\.|/i sysfun_function
          | userdefined_function
          ) '(' func_args(s /,/)  ')'

constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const

func_args: expression

sysibm_function: ( /ABS/i | /ABSVAL/i )
                | /AVG/i
                | /BIGINT/i
                | /BLOB/i
                | /CHAR/i
                | /CLOB/i
                | /COALESCE/i
                | ( /CONCAT/ | '||' )
                | ( /CORRELATION/i | /CORR/ )
                | /COUNT/i
                | /COUNT_BIG/i
                | (/COVARIANCE/i | /COVAR/i )
                | /DATE/i
                | /DAY/i
                | /DAYS/i
                | /DBCLOB/i
                | ( /DECIMAL/i | /DEC/i )
                | /DECRYPT_BIN/i
                | /DECRYPT_CHAR/i
                | /DEREF/i
                | /DIGITS/i
                | /DLCOMMENT/i
                | /DLLINKTYPE/i
                | /DLURLCOMPLETE/i
                | /DLURLPATH/i
                | /DLURLPATHONLY/i
                | /DLURLSCHEME/i
                | /DLURLSERVER/i
                | /DLVALUE/i
                | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
                | /ENCRYPT/i
                | /EVENT_MON_STATE/i
                | /FLOAT/i
                | /GETHINT/i
                | /GENERATE_UNIQUE/i
                | /GRAPHIC/i
                | /GROUPING/i
                | /HEX/i
                | /HOUR/i
                | /IDENTITY_VAL_LOCAL/i
                | ( /INTEGER/i | /INT/ )
                | ( /LCASE/i | /LOWER/ )
                | /LENGTH/i
                | /LONG_VARCHAR/i
                | /LONG_VARGRAPHIC/i
                | /LTRIM/i
                | /MAX/i
                | /MICROSECOND/i
                | /MIN/i
                | /MINUTE/i
                | /MONTH/i
                | /MULTIPLY_ACT/i
                | /NODENUMBER/i
                | /NULLIF/i
                | /PARTITON/i
                | /POSSTR/i
                | /RAISE_ERROR/i
                | /REAL/i
                | /REC2XML/i
                | /REGR_AVGX/i
                | /REGR_AVGY/i
                | /REGR_COUNT/i
                | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
                | /REGR_R2/i
                | /REGR_SLOPE/i
                | /REGR_SXX/i
                | /REGR_SXY/i
                | /REGR_SYY/i
                | /RTRIM/i
                | /SECOND/i
                | /SMALLINT/i
                | /STDDEV/i
                | /SUBSTR/i
                | /SUM/i
                | /TABLE_NAME/i
                | /TABLE_SCHEMA/i
                | /TIME/i
                | /TIMESTAMP/i
                | /TRANSLATE/i
                | /TYPE_ID/i
                | /TYPE_NAME/i
                | /TYPE_SCHEMA/i
                | ( /UCASE/i | /UPPER/i )
                | /VALUE/i
                | /VARCHAR/i
                | /VARGRAPHIC/i
                | ( /VARIANCE/i | /VAR/i )
                | /YEAR/i

sysfun: ( /ABS/i | /ABSVAL/i )
                | /ACOS/i
                | /ASCII/i
                | /ASIN/i
                | /ATAN/i
                | /ATAN2/i
                | ( /CEIL/i | /CEILING/i )
                | /CHAR/i
                | /CHR/i
                | /COS/i
                | /COT/i
                | /DAYNAME/i
                | /DAYOFWEEK/i
                | /DAYOFWEEK_ISO/i
                | /DAYOFYEAR/i
                | /DEGREES/i
                | /DIFFERENCE/i
                | /DOUBLE/i
                | /EXP/i
                | /FLOOR/i
                | /GET_ROUTINE_SAR/i
                | /INSERT/i
                | /JULIAN_DAY/i
                | /LCASE/i
                | /LEFT/i
                | /LN/i
                | /LOCATE/i
                | /LOG/i
                | /LOG10/i
                | /LTRIM/i
                | /MIDNIGHT_SECONDS/i
                | /MOD/i
                | /MONTHNAME/i
                | /POWER/i
                | /PUT_ROUTINE_SAR/i
                | /QUARTER/i
                | /RADIANS/i
                | /RAND/i
                | /REPEAT/i
                | /REPLACE/i
                | /RIGHT/i
                | /ROUND/i
                | /RTRIM/I
                | /SIGN/i
                | /SIN/i
                | /SOUNDEX/i
                | /SPACE/i
                | /SQLCACHE_SNAPSHOT/i
                | /SQRT/i
                | /TAN/i
                | /TIMESTAMP_ISO/i
                | /TIMESTAMPDIFF/i
                | ( /TRUNCATE/i | /TRUNC/i )
                | /UCASE/i
                | /WEEK/i
                | /WEEK_ISO/i

scalar_fullselect: '(' fullselect ')'

labeled_duration: ld_type ld_duration

ld_type: function
       | '(' expression ')'
       | constant
       | column_name
       | host_variable

ld_duration: /YEARS?/i
           | /MONTHS?/i
           | /DAYS?/i
           | /HOURS?/i
           | /MINUTES?/i
           | /SECONDS?/i
           | /MICROSECONDS?/i

case_expression: /CASE/i ( searched_when_clause
                         | simple_when_clause
                         )
                         ( /ELSE\s+NULL/i
                         | /ELSE/i result_expression
                         )(?) /END/i

searched_when_clause: ( /WHEN/i search_condition /THEN/i
                        ( result_expression
                        | /NULL/i
                        )
                      )(s)

simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
                                 ( result_expression
                                 | /NULL/i
                                 )
                               )(s)

result_expression: expression

cast_specification: /CAST/i '(' ( expression
                                | /NULL/i
                                | parameter_marker
                                ) /AS/i data_type
                                  ( /SCOPE/ ( typed_table_name
                                            | typed_view_name
                                            )
                                  )(?) ')'

dereference_operation: scoped_reference_expression '->' name1
                      (  '(' expression(s) ')' )(?)
#                         ( '(' expression(s /,/) ')' )(?)


scoped_reference_expression: expression
{ # scoped, reference
}

name1: NAME

OLAP_function: ranking_function
             | numbering_function
             | aggregation_function

ranking_function: ( /RANK/ '()'
                  | /DENSE_RANK|DENSERANK/i '()'
                  ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'

numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
                      ( window_order_clause window_aggregation_group_clause(?)
                      )(?)
                      ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
                      | window_aggregation_group_clause
                      )(?) ')'

window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)

window_order_clause: /ORDER\s+BY/i
                      ( sort_key_expression
                        ( asc_option
                        | desc_option
                        )(?)
                      )(s /,/)

asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)

desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)

window_aggregation_group_clause: ( /ROWS/i
                                 | /RANGE/i
                                 )
                                 ( group_start
                                 | group_between
                                 | group_end
                                 )

group_start: /UNBOUNDED\s+PRECEDING/i
           | unsigned_constant /PRECEDING/i
           | /CURRENT\s+ROW/i

group_between: /BETWEEN/i group_bound1 /AND/i group_bound2

group_bound1: /UNBOUNDED\s+PRECEDING/i
           | unsigned_constant /PRECEDING/i
           | unsigned_constant /FOLLOWING/i
           | /CURRENT\s+ROW/i

group_bound2: /UNBOUNDED\s+PRECEDING/i
           | unsigned_constant /PRECEDING/i
           | unsigned_constant /FOLLOWING/i
           | /CURRENT\s+ROW/i

group_end: /UNBOUNDED\s+PRECEDING/i
           | unsigned_constant /FOLLOWING/i

method_invocation: subject_expression '..' method_name
                    ( '(' expression(s) ')'
#                    ( '(' expression(s /,/) ')'
                    )(?)

subject_expression: expression
{ # with static result type that is a used-defined struct type
}

method_name: NAME
{ # must be a method of subject_expression
}

subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'

sequence_reference: nextval_expression
                  | prevval_expression

nextval_expression: /NEXTVAL\s+FOR/i sequence_name

prevval_expression: /PREVVAL\s+FOR/i sequence_name

sequence_name: NAME


search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)

cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )

predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p

basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression

quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'

END_OF_GRAMMAR

sub parse {
    my ( $translator, $data ) = @_;

    # Enable warnings within the Parse::RecDescent module.
    local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
    local $::RD_WARN   = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
    local $::RD_HINT   = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.

    local $::RD_TRACE  = $translator->trace ? 1 : undef;
    local $DEBUG       = $translator->debug;

    my $parser = ddl_parser_instance('DB2');

    my $result = $parser->startrule($data);
    return $translator->error( "Parse failed." ) unless defined $result;
    warn Dumper( $result ) if $DEBUG;

    my $schema = $translator->schema;
    my @tables =
        map   { $_->[1] }
        sort  { $a->[0] <=> $b->[0] }
        map   { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
        keys %{ $result->{'tables'} };

    for my $table_name ( @tables ) {
        my $tdata =  $result->{'tables'}{ $table_name };
        my $table =  $schema->add_table(
            name  => $tdata->{'name'},
        ) or die $schema->error;

        $table->comments( $tdata->{'comments'} );

        for my $fdata ( @{ $tdata->{'fields'} } ) {
            my $field = $table->add_field(
                name              => $fdata->{'name'},
                data_type         => $fdata->{'data_type'},
                size              => $fdata->{'size'},
                default_value     => $fdata->{'default'},
                is_auto_increment => $fdata->{'is_auto_inc'},
                is_nullable       => $fdata->{'is_nullable'},
                comments          => $fdata->{'comments'},
            ) or die $table->error;

            $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};

            for my $cdata ( @{ $fdata->{'constraints'} } ) {
                next unless $cdata->{'type'} eq 'foreign_key';
                $cdata->{'fields'} ||= [ $field->name ];
                push @{ $tdata->{'constraints'} }, $cdata;
            }
        }

        for my $idata ( @{ $tdata->{'indices'} || [] } ) {
            my $index  =  $table->add_index(
                name   => $idata->{'name'},
                type   => uc $idata->{'type'},
                fields => $idata->{'fields'},
            ) or die $table->error;
        }

        for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
            my $constraint       =  $table->add_constraint(
                name             => $cdata->{'name'},
                type             => $cdata->{'type'},
                fields           => $cdata->{'fields'},
                reference_table  => $cdata->{'reference_table'},
                reference_fields => $cdata->{'reference_fields'},
                match_type       => $cdata->{'match_type'} || '',
                on_delete        => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
                on_update        => $cdata->{'on_update'} || $cdata->{'on_update_do'},
            ) or die $table->error;
        }
    }

    for my $def ( @{ $result->{'views'} || [] } ) {
        my $view = $schema->add_view(
            name => $def->{'name'},
            sql  => $def->{'sql'},
        );
    }

    for my $def ( @{ $result->{'triggers'} || [] } ) {
        my $trig                = $schema->add_trigger(
            name                => $def->{'name'},
            perform_action_when => $def->{'when'},
            database_event      => $def->{'db_event'},
            action              => $def->{'action'},
            fields              => $def->{'fields'},
            on_table            => $def->{'table'}
                                                       );
        $trig->extra( reference => $def->{'reference'},
                      condition => $def->{'condition'},
                      granularity => $def->{'granularity'} );
    }

    return 1;
}

1;

=pod

=head1 AUTHOR

Jess Robinson <cpan@desert-island.me.uk>

=head1 SEE ALSO

perl(1), Parse::RecDescent, SQL::Translator::Schema.

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