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

package SQL::Translator::Parser::Oracle;

# -------------------------------------------------------------------
# $Id: Oracle.pm,v 1.20 2005-06-28 16:39:41 mwz444 Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 SQLFairy Authors
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; version 2.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
# 02111-1307  USA
# -------------------------------------------------------------------

=head1 NAME

SQL::Translator::Parser::Oracle - parser for Oracle

=head1 SYNOPSIS

  use SQL::Translator;
  use SQL::Translator::Parser::Oracle;

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

=head1 DESCRIPTION

From http://www.ss64.com/ora/table_c.html:

 CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...)
     [ON COMMIT {DELETE|PRESERVE} ROWS]
         [storage_options | CLUSTER cluster_name (col1, col2,... )
            | ORGANIZATION {HEAP [storage_options] 
            | INDEX idx_organized_tbl_clause}]
               [LOB_storage_clause][varray_clause][nested_storage_clause]
                   partitioning_options
                      [[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause]
                         [ENABLE enable_clause | DISABLE disable_clause]
                             [AS subquery]

tbl_defs:
   column datatype [DEFAULT expr] [column_constraint(s)]
   table_ref_constraint

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]

idx_organized_tbl_clause:
   storage_option(s) [PCTTHRESHOLD int]
     [COMPRESS int|NOCOMPRESS]
         [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

nested_storage_clause:
   NESTED TABLE nested_item STORE AS storage_table
      [RETURN AS {LOCATOR|VALUE} ]

partitioning_options:
   Partition_clause {ENABLE|DISABLE} ROW MOVEMENT

Column Constraints
(http://www.ss64.com/ora/clause_constraint_col.html)

   CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY} constrnt_state

   CONSTRAINT constrnt_name CHECK(condition) constrnt_state

   CONSTRAINT constrnt_name [NOT] NULL constrnt_state

   CONSTRAINT constrnt_name REFERENCES [schema.]table[(column)]
      [ON DELETE {CASCADE|SET NULL}] constrnt_state

constrnt_state   
    [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
       [RELY | NORELY] [USING INDEX using_index_clause]
          [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
              [EXCEPTIONS INTO [schema.]table]

Note that probably not all of the above syntax is supported, but the grammar 
was altered to better handle the syntax created by DDL::Oracle.

=cut

use strict;
use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
$VERSION = sprintf "%d.%02d", q$Revision: 1.20 $ =~ /(\d+)\.(\d+)/;
$DEBUG   = 0 unless defined $DEBUG;

use Data::Dumper;
use Parse::RecDescent;
use Exporter;
use base qw(Exporter);

@EXPORT_OK = qw(parse);

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

my $parser; 

$GRAMMAR = q!

{ my ( %tables, %indices, %constraints, $table_order, @table_comments ) }

#
# 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,
            indices     => \%indices,
            constraints => \%constraints,
        };
    }

eofile : /^\Z/

statement : remark
    | prompt
    | create
    | table_comment
    | comment_on_table
    | comment_on_column
    | alter
    | drop
    | <error>

alter : /alter/i WORD /[^;]+/ ';'
    { @table_comments = () }

drop : /drop/i TABLE ';'

drop : /drop/i WORD(s) ';'
    { @table_comments = () }

create : create_table table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
    {
        my $table_name                       = $item{'table_name'};
        $tables{ $table_name }{'order'}      = ++$table_order;
        $tables{ $table_name }{'table_name'} = $table_name;

        if ( @table_comments ) {
            $tables{ $table_name }{'comments'} = [ @table_comments ];
            @table_comments = ();
        }

        my $i = 1;
        my @constraints;
        for my $definition ( @{ $item[4] } ) {
            if ( $definition->{'type'} eq 'field' ) {
                my $field_name = $definition->{'name'};
                $tables{ $table_name }{'fields'}{ $field_name } = 
                    { %$definition, order => $i };
                $i++;
				
                for my $constraint ( @{ $definition->{'constraints'} || [] } ) {
                    $constraint->{'fields'} = [ $field_name ];
                    push @{ $tables{ $table_name }{'constraints'} }, 
                        $constraint;
                }
            }
            elsif ( $definition->{'type'} eq 'constraint' ) {
                $definition->{'type'} = $definition->{'constraint_type'};
                push @{ $tables{ $table_name }{'constraints'} }, $definition;
            }
            else {
                push @{ $tables{ $table_name }{'indices'} }, $definition;
            }
        }

        for my $option ( @{ $item[6] } ) {
            push @{ $tables{ $table_name }{'table_options'} }, $option;
        }

        1;
    }

create : /create/i UNIQUE(?) /index/i WORD /on/i table_name parens_word_list table_option(?) ';'
    {
        my $table_name = $item[6];
        if ( $item[2] ) {
            push @{ $constraints{ $table_name } }, {
                name   => $item[4],
                type   => 'unique',
                fields => $item[7][0],
            };
        }
        else {
            push @{ $indices{ $table_name } }, {
                name   => $item[4],
                type   => 'normal',
                fields => $item[7][0],
            };
        }
    }

# Create anything else (e.g., domain, function, etc.)
create : /create/i WORD /[^;]+/ ';'
    { @table_comments = () }

global_temporary: /global/i /temporary/i

table_name : NAME '.' NAME
    { $item[3] }
    | NAME 
    { $item[1] }

create_definition : field
    | table_constraint
    | <error>

table_comment : comment
    {
        my $comment = $item[1];
        $return     = $comment;
        push @table_comments, $comment;
    }

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

comment : /\/\*/ /[^\*]+/ /\*\// 
    {
        my $comment = $item[2];
        $comment    =~ s/^\s*|\s*$//g;
        $return = $comment;
    }

remark : /^REM\s+.*\n/

prompt : /prompt/i /(table|index|sequence|trigger)/i ';'

prompt : /prompt\s+create\s+.*\n/i

comment_on_table : /comment/i /on/i /table/i table_name /is/i comment_phrase ';'
    {
        push @{ $tables{ $item{'table_name'} }{'comments'} }, $item{'comment_phrase'};
    }

comment_on_column : /comment/i /on/i /column/i column_name /is/i comment_phrase ';'
    {
        my $table_name = $item[4]->{'table'};
        my $field_name = $item[4]->{'field'};
        push @{ $tables{ $table_name }{'fields'}{ $field_name }{'comments'} }, 
            $item{'comment_phrase'};
    }

column_name : NAME '.' NAME
    { $return = { table => $item[1], field => $item[3] } }

comment_phrase : /'.*?'/ 
    { 
        my $val = $item[1];
        $val =~ s/^'|'$//g;
        $return = $val;
    }

field : comment(s?) field_name data_type field_meta(s?) comment(s?)
    {
        my ( $is_pk, $default, @constraints );
        my $null = 1;
        for my $meta ( @{ $item[4] } ) {
            if ( $meta->{'type'} eq 'default' ) {
                $default = $meta;
                next;
            }
            elsif ( $meta->{'type'} eq 'not_null' ) {
                $null = 0;
                next;
            }
            elsif ( $meta->{'type'} eq 'primary_key' ) {
                $is_pk = 1;
            }

            push @constraints, $meta if $meta->{'supertype'} eq 'constraint';
        }

        my @comments = ( @{ $item[1] }, @{ $item[5] } );

        $return = { 
            type           => 'field',
            name           => $item{'field_name'}, 
            data_type      => $item{'data_type'}{'type'},
            size           => $item{'data_type'}{'size'},
            null           => $null,
            default        => $default->{'value'},
            is_primary_key => $is_pk,
            constraints    => [ @constraints ],
            comments       => [ @comments ],
        } 
    }
    | <error>

field_name : NAME

data_type : ora_data_type parens_value_list(?)
    { 
        $return  = { 
            type => $item[1],
            size => $item[2][0] || '',
        } 
    }

column_constraint : constraint_name(?) column_constraint_type 
    {
        my $desc       = $item{'column_constraint_type'};
        my $type       = $desc->{'type'};
        my $fields     = $desc->{'fields'}     || [];
        my $expression = $desc->{'expression'} || '';

        $return              =  {
            supertype        => 'constraint',
            name             => $item{'constraint_name(?)'}[0] || '',
            type             => $type,
            expression       => $type eq 'check' ? $expression : '',
            deferrable       => $item{'deferrable'},
            deferred         => $item{'deferred'},
            reference_table  => $desc->{'reference_table'},
            reference_fields => $desc->{'reference_fields'},
#            match_type       => $desc->{'match_type'},
#            on_update        => $desc->{'on_update'},
        } 
    }

constraint_name : /constraint/i NAME { $item[2] }

column_constraint_type : /not\s+null/i { $return = { type => 'not_null' } }
    | /null/ 
        { $return = { type => 'null' } }
    | /unique/ 
        { $return = { type => 'unique' } }
    | /primary\s+key/i 
        { $return = { type => 'primary_key' } }
    | /check/i '(' /[^)]+/ ')' 
        { $return = { type => 'check', expression => $item[3] } }
    | /references/i table_name parens_word_list(?) on_delete(?) 
    {
        $return              =  {
            type             => 'foreign_key',
            reference_table  => $item[2],
            reference_fields => $item[3][0],
#            match_type       => $item[4][0],
            on_delete     => $item[5][0],
        }
    }

#constraint_state : deferrable { $return = { type => $item[1] } }
#    | deferred { $return = { type => $item[1] } }
#    | /(no)?rely/ { $return = { type => $item[1] } }
#    | /using/i /index/i using_index_clause 
#        { $return = { type => 'using_index', index => $item[3] }
#    | (dis)?enable { $return = { type => $item[1] } }
#    | (no)?validate { $return = { type => $item[1] } }
#    | /exceptions/i /into/i table_name 
#        { $return = { type => 'exceptions_into', table => $item[3] } }

deferrable : /not/i /deferrable/i 
    { $return = 'not_deferrable' }
    | /deferrable/i 
    { $return = 'deferrable' }

deferred : /initially/i /(deferred|immediate)/i { $item[2] }

ora_data_type :
    /(n?varchar2|varchar)/i { $return = 'varchar2' }
    |
    /n?char/i { $return = 'character' }
    |
	/n?dec/i { $return = 'decimal' }
	|
    /number/i { $return = 'number' }
    |
    /integer/i { $return = 'integer' }
    |
    /(pls_integer|binary_integer)/i { $return = 'integer' }
    |
    /interval\s+day/i { $return = 'interval_day' }
    |
    /interval\s+year/i { $return = 'interval_year' }
    |
    /long\s+raw/i { $return = 'long_raw' }
    |
    /(long|date|timestamp|raw|rowid|urowid|mlslabel|clob|nclob|blob|bfile)/i { $item[1] }

parens_value_list : '(' VALUE(s /,/) ')'
    { $item[2] }

parens_word_list : '(' WORD(s /,/) ')'
    { $item[2] }

field_meta : default_val
    | column_constraint

default_val  : /default/i /(?:')?[\w\d.-]*(?:')?/ 
    { 
        my $val =  $item[2];
        $val    =~ s/'//g if defined $val; 
        $return =  {
            supertype => 'constraint',
            type      => 'default',
            value     => $val,
        }
    }

create_table : /create/i global_temporary(?) /table/i

table_option : /organization/i WORD
    {
        $return = { 'ORGANIZATION' => $item[2] }
    }

table_option : /nomonitoring/i
    {
        $return = { 'NOMONITORING' => undef }
    }

table_option : /parallel/i '(' key_value(s) ')'
    {
        $return = { 'PARALLEL' => $item[3] }
    }

key_value : WORD VALUE
    {
        $return = { $item[1], $item[2] }
    }

table_option : /[^;]+/

table_constraint : comment(s?) constraint_name(?) table_constraint_type deferrable(?) deferred(?) comment(s?)
    {
        my $desc       = $item{'table_constraint_type'};
        my $type       = $desc->{'type'};
        my $fields     = $desc->{'fields'};
        my $expression = $desc->{'expression'};
        my @comments   = ( @{ $item[1] }, @{ $item[-1] } );

        $return              =  {
            name             => $item{'constraint_name(?)'}[0] || '',
            type             => 'constraint',
            constraint_type  => $type,
            fields           => $type ne 'check' ? $fields : [],
            expression       => $type eq 'check' ? $expression : '',
            deferrable       => $item{'deferrable(?)'},
            deferred         => $item{'deferred(?)'},
            reference_table  => $desc->{'reference_table'},
            reference_fields => $desc->{'reference_fields'},
#            match_type       => $desc->{'match_type'}[0],
            on_delete        => $desc->{'on_delete'} || $desc->{'on_delete_do'},
            on_update        => $desc->{'on_update'} || $desc->{'on_update_do'},
            comments         => [ @comments ],
        } 
    }

table_constraint_type : /primary key/i '(' NAME(s /,/) ')' 
    { 
        $return = {
            type   => 'primary_key',
            fields => $item[3],
        }
    }
    |
    /unique/i '(' NAME(s /,/) ')' 
    { 
        $return    =  {
            type   => 'unique',
            fields => $item[3],
        }
    }
    |
    /check/ '(' /(.+)/ ')'
    {
        $return        =  {
            type       => 'check',
            expression => $item[3],
        }
    }
    |
    /foreign key/i '(' NAME(s /,/) ')' /references/i table_name parens_word_list(?) on_delete(?)
    {
        $return              =  {
            type             => 'foreign_key',
            fields           => $item[3],
            reference_table  => $item[6],
            reference_fields => $item[7][0],
            match_type       => $item[8][0],
            on_delete     => $item[9][0],
            on_update     => $item[10][0],
        }
    }

on_delete : /on delete/i WORD(s)
    { $item[2] }

UNIQUE : /unique/i { $return = 1 }

WORD : /\w+/

NAME : /\w+/ { $item[1] }

TABLE : /table/i

VALUE   : /[-+]?\.?\d+(?:[eE]\d+)?/
    { $item[1] }
    | /'.*?'/   # XXX doesn't handle embedded quotes
    { $item[1] }
    | /NULL/
    { 'NULL' }

!;

# -------------------------------------------------------------------
sub parse {
    my ( $translator, $data ) = @_;
    $parser ||= Parse::RecDescent->new($GRAMMAR);

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

    unless (defined $parser) {
        return $translator->error("Error instantiating Parse::RecDescent ".
            "instance: Bad grammer");
    }

    my $result = $parser->startrule( $data );
    die "Parse failed.\n" unless defined $result;
    if ( $DEBUG ) {
        warn "Parser results =\n", Dumper($result), "\n";
    }

    my $schema      = $translator->schema;
    my $indices     = $result->{'indices'};
    my $constraints = $result->{'constraints'};
    my @tables      = sort { 
        $result->{'tables'}{ $a }{'order'} 
        <=> 
        $result->{'tables'}{ $b }{'order'}
    } keys %{ $result->{'tables'} };

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

        $table->options( $tdata->{'table_options'} );

        my @fields = sort { 
            $tdata->{'fields'}->{$a}->{'order'} 
            <=>
            $tdata->{'fields'}->{$b}->{'order'}
        } keys %{ $tdata->{'fields'} };

        for my $fname ( @fields ) {
            my $fdata = $tdata->{'fields'}{ $fname };
            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->{'null'},
                comments          => $fdata->{'comments'},
            ) or die $table->error;
        }

        push @{ $tdata->{'indices'} }, @{ $indices->{ $table_name } || [] };
        push @{ $tdata->{'constraints'} }, 
             @{ $constraints->{ $table_name } || [] };

        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;
        }
    }

    return 1;
}

1;

# -------------------------------------------------------------------
# Something there is that doesn't love a wall.
# Robert Frost
# -------------------------------------------------------------------

=pod

=head1 AUTHOR

Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.

=head1 SEE ALSO

SQL::Translator, Parse::RecDescent, DDL::Oracle.

=cut
Commit	Line	Data
17cae8ab	1	package SQL::Translator::Parser::Oracle;
	2
	3	# -------------------------------------------------------------------
100684f3	4	# $Id: Oracle.pm,v 1.20 2005-06-28 16:39:41 mwz444 Exp $
17cae8ab	5	# -------------------------------------------------------------------
90075866	6	# Copyright (C) 2002-4 SQLFairy Authors
17cae8ab	7	#
	8	# This program is free software; you can redistribute it and/or
	9	# modify it under the terms of the GNU General Public License as
	10	# published by the Free Software Foundation; version 2.
	11	#
	12	# This program is distributed in the hope that it will be useful, but
	13	# WITHOUT ANY WARRANTY; without even the implied warranty of
	14	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
	15	# General Public License for more details.
	16	#
	17	# You should have received a copy of the GNU General Public License
	18	# along with this program; if not, write to the Free Software
	19	# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
	20	# 02111-1307 USA
	21	# -------------------------------------------------------------------
	22
	23	=head1 NAME
	24
	25	SQL::Translator::Parser::Oracle - parser for Oracle
	26
	27	=head1 SYNOPSIS
	28
	29	use SQL::Translator;
	30	use SQL::Translator::Parser::Oracle;
	31
	32	my $translator = SQL::Translator->new;
	33	$translator->parser("SQL::Translator::Parser::Oracle");
	34
	35	=head1 DESCRIPTION
	36
	37	From http://www.ss64.com/ora/table_c.html:
	38
	39	CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...)
	40	[ON COMMIT {DELETE\|PRESERVE} ROWS]
	41	[storage_options \| CLUSTER cluster_name (col1, col2,... )
	42	\| ORGANIZATION {HEAP [storage_options]
	43	\| INDEX idx_organized_tbl_clause}]
	44	[LOB_storage_clause][varray_clause][nested_storage_clause]
	45	partitioning_options
	46	[[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause]
	47	[ENABLE enable_clause \| DISABLE disable_clause]
	48	[AS subquery]
	49
	50	tbl_defs:
	51	column datatype [DEFAULT expr] [column_constraint(s)]
17cae8ab	52	table_ref_constraint
	53
	54	storage_options:
	55	PCTFREE int
	56	PCTUSED int
	57	INITTRANS int
	58	MAXTRANS int
	59	STORAGE storage_clause
	60	TABLESPACE tablespace
	61	[LOGGING\|NOLOGGING]
	62
	63	idx_organized_tbl_clause:
	64	storage_option(s) [PCTTHRESHOLD int]
	65	[COMPRESS int\|NOCOMPRESS]
	66	[ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]
	67
	68	nested_storage_clause:
	69	NESTED TABLE nested_item STORE AS storage_table
	70	[RETURN AS {LOCATOR\|VALUE} ]
	71
	72	partitioning_options:
	73	Partition_clause {ENABLE\|DISABLE} ROW MOVEMENT
	74
	75	Column Constraints
	76	(http://www.ss64.com/ora/clause_constraint_col.html)
	77
	78	CONSTRAINT constrnt_name {UNIQUE\|PRIMARY KEY} constrnt_state
	79
	80	CONSTRAINT constrnt_name CHECK(condition) constrnt_state
	81
	82	CONSTRAINT constrnt_name [NOT] NULL constrnt_state
	83
	84	CONSTRAINT constrnt_name REFERENCES [schema.]table[(column)]
	85	[ON DELETE {CASCADE\|SET NULL}] constrnt_state
	86
	87	constrnt_state
	88	[[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE\|DEFERRED}]
	89	[RELY \| NORELY] [USING INDEX using_index_clause]
	90	[ENABLE\|DISABLE] [VALIDATE\|NOVALIDATE]
	91	[EXCEPTIONS INTO [schema.]table]
	92
3b2be65a	93	Note that probably not all of the above syntax is supported, but the grammar
	94	was altered to better handle the syntax created by DDL::Oracle.
	95
17cae8ab	96	=cut
	97
	98	use strict;
	99	use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
100684f3	100	$VERSION = sprintf "%d.%02d", q$Revision: 1.20 $ =~ /(\d+)\.(\d+)/;
17cae8ab	101	$DEBUG = 0 unless defined $DEBUG;
	102
	103	use Data::Dumper;
	104	use Parse::RecDescent;
	105	use Exporter;
	106	use base qw(Exporter);
	107
	108	@EXPORT_OK = qw(parse);
	109
	110	# Enable warnings within the Parse::RecDescent module.
	111	$::RD_ERRORS = 1; # Make sure the parser dies when it encounters an error
	112	$::RD_WARN = 1; # Enable warnings. This will warn on unused rules &c.
	113	$::RD_HINT = 1; # Give out hints to help fix problems.
	114
	115	my $parser;
	116
	117	$GRAMMAR = q!
	118
6a36a3d8	119	{ my ( %tables, %indices, %constraints, $table_order, @table_comments ) }
17cae8ab	120
	121	#
	122	# The "eofile" rule makes the parser fail if any "statement" rule
	123	# fails. Otherwise, the first successful match by a "statement"
	124	# won't cause the failure needed to know that the parse, as a whole,
	125	# failed. -ky
	126	#
87053733	127	startrule : statement(s) eofile
	128	{
	129	$return = {
6a36a3d8	130	tables => \%tables,
	131	indices => \%indices,
	132	constraints => \%constraints,
87053733	133	};
87053733	134	}
17cae8ab	135
	136	eofile : /^\Z/
	137
87053733	138	statement : remark
	139	\| prompt
	140	\| create
f77a985b	141	\| table_comment
067e3cf4	142	\| comment_on_table
	143	\| comment_on_column
	144	\| alter
	145	\| drop
	146	\| <error>
17cae8ab	147
44567b47	148	alter : /alter/i WORD /[^;]+/ ';'
067e3cf4	149	{ @table_comments = () }
	150
	151	drop : /drop/i TABLE ';'
	152
	153	drop : /drop/i WORD(s) ';'
	154	{ @table_comments = () }
44567b47	155
87053733	156	create : create_table table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
17cae8ab	157	{
	158	my $table_name = $item{'table_name'};
	159	$tables{ $table_name }{'order'} = ++$table_order;
	160	$tables{ $table_name }{'table_name'} = $table_name;
	161
067e3cf4	162	if ( @table_comments ) {
	163	$tables{ $table_name }{'comments'} = [ @table_comments ];
	164	@table_comments = ();
	165	}
	166
17cae8ab	167	my $i = 1;
17cae8ab	168	my @constraints;
87053733	169	for my $definition ( @{ $item[4] } ) {
17cae8ab	170	if ( $definition->{'type'} eq 'field' ) {
	171	my $field_name = $definition->{'name'};
	172	$tables{ $table_name }{'fields'}{ $field_name } =
	173	{ %$definition, order => $i };
	174	$i++;
	175
17cae8ab	176	for my $constraint ( @{ $definition->{'constraints'} \|\| [] } ) {
	177	$constraint->{'fields'} = [ $field_name ];
	178	push @{ $tables{ $table_name }{'constraints'} },
	179	$constraint;
	180	}
	181	}
	182	elsif ( $definition->{'type'} eq 'constraint' ) {
	183	$definition->{'type'} = $definition->{'constraint_type'};
f77a985b	184	push @{ $tables{ $table_name }{'constraints'} }, $definition;
17cae8ab	185	}
	186	else {
	187	push @{ $tables{ $table_name }{'indices'} }, $definition;
	188	}
	189	}
	190
87053733	191	for my $option ( @{ $item[6] } ) {
7c4cf567	192	push @{ $tables{ $table_name }{'table_options'} }, $option;
17cae8ab	193	}
	194
	195	1;
	196	}
	197
6a36a3d8	198	create : /create/i UNIQUE(?) /index/i WORD /on/i table_name parens_word_list table_option(?) ';'
b85a95ec	199	{
6a36a3d8	200	my $table_name = $item[6];
	201	if ( $item[2] ) {
	202	push @{ $constraints{ $table_name } }, {
	203	name => $item[4],
	204	type => 'unique',
	205	fields => $item[7][0],
	206	};
	207	}
	208	else {
	209	push @{ $indices{ $table_name } }, {
	210	name => $item[4],
	211	type => 'normal',
	212	fields => $item[7][0],
	213	};
	214	}
b85a95ec	215	}
b85a95ec	216
17cae8ab	217	# Create anything else (e.g., domain, function, etc.)
17cae8ab	218	create : /create/i WORD /[^;]+/ ';'
067e3cf4	219	{ @table_comments = () }
17cae8ab	220
	221	global_temporary: /global/i /temporary/i
	222
	223	table_name : NAME '.' NAME
	224	{ $item[3] }
	225	\| NAME
	226	{ $item[1] }
	227
	228	create_definition : field
	229	\| table_constraint
	230	\| <error>
	231
f77a985b	232	table_comment : comment
	233	{
	234	my $comment = $item[1];
	235	$return = $comment;
	236	push @table_comments, $comment;
	237	}
	238
17cae8ab	239	comment : /^\s(?:#\|-{2}).\n/
067e3cf4	240	{
39129b47	241	my $comment = $item[1];
	242	$comment =~ s/^\s(#\|-{2})\s//;
	243	$comment =~ s/\s*$//;
	244	$return = $comment;
f77a985b	245	}
	246
	247	comment : /\/\/ /[^\]+/ /\*\//
	248	{
	249	my $comment = $item[2];
	250	$comment =~ s/^\s\|\s$//g;
	251	$return = $comment;
067e3cf4	252	}
17cae8ab	253
87053733	254	remark : /^REM\s+.*\n/
	255
	256	prompt : /prompt/i /(table\|index\|sequence\|trigger)/i ';'
	257
	258	prompt : /prompt\s+create\s+.*\n/i
	259
947dfd86	260	comment_on_table : /comment/i /on/i /table/i table_name /is/i comment_phrase ';'
17cae8ab	261	{
947dfd86	262	push @{ $tables{ $item{'table_name'} }{'comments'} }, $item{'comment_phrase'};
17cae8ab	263	}
17cae8ab	264
947dfd86	265	comment_on_column : /comment/i /on/i /column/i column_name /is/i comment_phrase ';'
17cae8ab	266	{
	267	my $table_name = $item[4]->{'table'};
	268	my $field_name = $item[4]->{'field'};
	269	push @{ $tables{ $table_name }{'fields'}{ $field_name }{'comments'} },
947dfd86	270	$item{'comment_phrase'};
17cae8ab	271	}
	272
	273	column_name : NAME '.' NAME
	274	{ $return = { table => $item[1], field => $item[3] } }
	275
947dfd86	276	comment_phrase : /'.*?'/
	277	{
	278	my $val = $item[1];
	279	$val =~ s/^'\|'$//g;
	280	$return = $val;
	281	}
17cae8ab	282
	283	field : comment(s?) field_name data_type field_meta(s?) comment(s?)
	284	{
947dfd86	285	my ( $is_pk, $default, @constraints );
947dfd86	286	my $null = 1;
17cae8ab	287	for my $meta ( @{ $item[4] } ) {
947dfd86	288	if ( $meta->{'type'} eq 'default' ) {
	289	$default = $meta;
	290	next;
	291	}
	292	elsif ( $meta->{'type'} eq 'not_null' ) {
	293	$null = 0;
	294	next;
	295	}
	296	elsif ( $meta->{'type'} eq 'primary_key' ) {
	297	$is_pk = 1;
	298	}
	299
	300	push @constraints, $meta if $meta->{'supertype'} eq 'constraint';
17cae8ab	301	}
17cae8ab	302
947dfd86	303	my @comments = ( @{ $item[1] }, @{ $item[5] } );
17cae8ab	304
	305	$return = {
	306	type => 'field',
	307	name => $item{'field_name'},
	308	data_type => $item{'data_type'}{'type'},
	309	size => $item{'data_type'}{'size'},
17cae8ab	310	null => $null,
17cae8ab	311	default => $default->{'value'},
947dfd86	312	is_primary_key => $is_pk,
17cae8ab	313	constraints => [ @constraints ],
947dfd86	314	comments => [ @comments ],
17cae8ab	315	}
	316	}
	317	\| <error>
	318
	319	field_name : NAME
	320
	321	data_type : ora_data_type parens_value_list(?)
	322	{
	323	$return = {
	324	type => $item[1],
	325	size => $item[2][0] \|\| '',
	326	}
	327	}
	328
	329	column_constraint : constraint_name(?) column_constraint_type
17cae8ab	330	{
	331	my $desc = $item{'column_constraint_type'};
	332	my $type = $desc->{'type'};
	333	my $fields = $desc->{'fields'} \|\| [];
	334	my $expression = $desc->{'expression'} \|\| '';
	335
	336	$return = {
947dfd86	337	supertype => 'constraint',
947dfd86	338	name => $item{'constraint_name(?)'}[0] \|\| '',
17cae8ab	339	type => $type,
17cae8ab	340	expression => $type eq 'check' ? $expression : '',
40c5e2f4	341	deferrable => $item{'deferrable'},
17cae8ab	342	deferred => $item{'deferred'},
	343	reference_table => $desc->{'reference_table'},
	344	reference_fields => $desc->{'reference_fields'},
	345	# match_type => $desc->{'match_type'},
100684f3	346	# on_update => $desc->{'on_update'},
17cae8ab	347	}
	348	}
	349
	350	constraint_name : /constraint/i NAME { $item[2] }
	351
c72b8f32	352	column_constraint_type : /not\s+null/i { $return = { type => 'not_null' } }
947dfd86	353	\| /null/
17cae8ab	354	{ $return = { type => 'null' } }
947dfd86	355	\| /unique/
17cae8ab	356	{ $return = { type => 'unique' } }
c72b8f32	357	\| /primary\s+key/i
17cae8ab	358	{ $return = { type => 'primary_key' } }
947dfd86	359	\| /check/i '(' /[^)]+/ ')'
c72b8f32	360	{ $return = { type => 'check', expression => $item[3] } }
100684f3	361	\| /references/i table_name parens_word_list(?) on_delete(?)
17cae8ab	362	{
	363	$return = {
	364	type => 'foreign_key',
	365	reference_table => $item[2],
	366	reference_fields => $item[3][0],
	367	# match_type => $item[4][0],
100684f3	368	on_delete => $item[5][0],
17cae8ab	369	}
	370	}
	371
	372	#constraint_state : deferrable { $return = { type => $item[1] } }
	373	# \| deferred { $return = { type => $item[1] } }
	374	# \| /(no)?rely/ { $return = { type => $item[1] } }
	375	# \| /using/i /index/i using_index_clause
	376	# { $return = { type => 'using_index', index => $item[3] }
	377	# \| (dis)?enable { $return = { type => $item[1] } }
	378	# \| (no)?validate { $return = { type => $item[1] } }
	379	# \| /exceptions/i /into/i table_name
	380	# { $return = { type => 'exceptions_into', table => $item[3] } }
	381
	382	deferrable : /not/i /deferrable/i
	383	{ $return = 'not_deferrable' }
	384	\| /deferrable/i
	385	{ $return = 'deferrable' }
	386
	387	deferred : /initially/i /(deferred\|immediate)/i { $item[2] }
	388
	389	ora_data_type :
947dfd86	390	/(n?varchar2\|varchar)/i { $return = 'varchar2' }
17cae8ab	391	\|
	392	/n?char/i { $return = 'character' }
	393	\|
00e41431	394	/n?dec/i { $return = 'decimal' }
00e41431	395	\|
17cae8ab	396	/number/i { $return = 'number' }
17cae8ab	397	\|
87053733	398	/integer/i { $return = 'integer' }
87053733	399	\|
17cae8ab	400	/(pls_integer\|binary_integer)/i { $return = 'integer' }
	401	\|
	402	/interval\s+day/i { $return = 'interval_day' }
	403	\|
	404	/interval\s+year/i { $return = 'interval_year' }
	405	\|
	406	/long\s+raw/i { $return = 'long_raw' }
	407	\|
	408	/(long\|date\|timestamp\|raw\|rowid\|urowid\|mlslabel\|clob\|nclob\|blob\|bfile)/i { $item[1] }
	409
	410	parens_value_list : '(' VALUE(s /,/) ')'
	411	{ $item[2] }
	412
	413	parens_word_list : '(' WORD(s /,/) ')'
	414	{ $item[2] }
	415
	416	field_meta : default_val
947dfd86	417	\| column_constraint
17cae8ab	418
	419	default_val : /default/i /(?:')?[\w\d.-]*(?:')?/
	420	{
b85a95ec	421	my $val = $item[2];
b85a95ec	422	$val =~ s/'//g if defined $val;
17cae8ab	423	$return = {
947dfd86	424	supertype => 'constraint',
947dfd86	425	type => 'default',
17cae8ab	426	value => $val,
	427	}
	428	}
	429
	430	create_table : /create/i global_temporary(?) /table/i
	431
7c4cf567	432	table_option : /organization/i WORD
	433	{
	434	$return = { 'ORGANIZATION' => $item[2] }
	435	}
	436
	437	table_option : /nomonitoring/i
	438	{
	439	$return = { 'NOMONITORING' => undef }
	440	}
	441
	442	table_option : /parallel/i '(' key_value(s) ')'
	443	{
	444	$return = { 'PARALLEL' => $item[3] }
	445	}
	446
	447	key_value : WORD VALUE
	448	{
	449	$return = { $item[1], $item[2] }
	450	}
	451
17cae8ab	452	table_option : /[^;]+/
	453
	454	table_constraint : comment(s?) constraint_name(?) table_constraint_type deferrable(?) deferred(?) comment(s?)
	455	{
	456	my $desc = $item{'table_constraint_type'};
	457	my $type = $desc->{'type'};
	458	my $fields = $desc->{'fields'};
	459	my $expression = $desc->{'expression'};
	460	my @comments = ( @{ $item[1] }, @{ $item[-1] } );
	461
	462	$return = {
947dfd86	463	name => $item{'constraint_name(?)'}[0] \|\| '',
17cae8ab	464	type => 'constraint',
	465	constraint_type => $type,
	466	fields => $type ne 'check' ? $fields : [],
	467	expression => $type eq 'check' ? $expression : '',
40c5e2f4	468	deferrable => $item{'deferrable(?)'},
947dfd86	469	deferred => $item{'deferred(?)'},
17cae8ab	470	reference_table => $desc->{'reference_table'},
	471	reference_fields => $desc->{'reference_fields'},
	472	# match_type => $desc->{'match_type'}[0],
100684f3	473	on_delete => $desc->{'on_delete'} \|\| $desc->{'on_delete_do'},
100684f3	474	on_update => $desc->{'on_update'} \|\| $desc->{'on_update_do'},
17cae8ab	475	comments => [ @comments ],
	476	}
	477	}
	478
	479	table_constraint_type : /primary key/i '(' NAME(s /,/) ')'
	480	{
	481	$return = {
	482	type => 'primary_key',
	483	fields => $item[3],
	484	}
	485	}
	486	\|
	487	/unique/i '(' NAME(s /,/) ')'
	488	{
	489	$return = {
	490	type => 'unique',
	491	fields => $item[3],
	492	}
	493	}
	494	\|
	495	/check/ '(' /(.+)/ ')'
	496	{
	497	$return = {
	498	type => 'check',
	499	expression => $item[3],
	500	}
	501	}
	502	\|
100684f3	503	/foreign key/i '(' NAME(s /,/) ')' /references/i table_name parens_word_list(?) on_delete(?)
17cae8ab	504	{
	505	$return = {
	506	type => 'foreign_key',
	507	fields => $item[3],
	508	reference_table => $item[6],
	509	reference_fields => $item[7][0],
	510	match_type => $item[8][0],
100684f3	511	on_delete => $item[9][0],
100684f3	512	on_update => $item[10][0],
17cae8ab	513	}
	514	}
	515
100684f3	516	on_delete : /on delete/i WORD(s)
17cae8ab	517	{ $item[2] }
17cae8ab	518
6a36a3d8	519	UNIQUE : /unique/i { $return = 1 }
6a36a3d8	520
17cae8ab	521	WORD : /\w+/
	522
	523	NAME : /\w+/ { $item[1] }
	524
067e3cf4	525	TABLE : /table/i
067e3cf4	526
17cae8ab	527	VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
	528	{ $item[1] }
	529	\| /'.*?'/ # XXX doesn't handle embedded quotes
	530	{ $item[1] }
	531	\| /NULL/
	532	{ 'NULL' }
	533
	534	!;
	535
	536	# -------------------------------------------------------------------
	537	sub parse {
	538	my ( $translator, $data ) = @_;
	539	$parser \|\|= Parse::RecDescent->new($GRAMMAR);
	540
	541	local $::RD_TRACE = $translator->trace ? 1 : undef;
	542	local $DEBUG = $translator->debug;
	543
	544	unless (defined $parser) {
	545	return $translator->error("Error instantiating Parse::RecDescent ".
	546	"instance: Bad grammer");
	547	}
	548
87053733	549	my $result = $parser->startrule( $data );
17cae8ab	550	die "Parse failed.\n" unless defined $result;
c72b8f32	551	if ( $DEBUG ) {
	552	warn "Parser results =\n", Dumper($result), "\n";
	553	}
947dfd86	554
6a36a3d8	555	my $schema = $translator->schema;
	556	my $indices = $result->{'indices'};
	557	my $constraints = $result->{'constraints'};
	558	my @tables = sort {
87053733	559	$result->{'tables'}{ $a }{'order'}
	560	<=>
	561	$result->{'tables'}{ $b }{'order'}
	562	} keys %{ $result->{'tables'} };
947dfd86	563
947dfd86	564	for my $table_name ( @tables ) {
87053733	565	my $tdata = $result->{'tables'}{ $table_name };
87053733	566	next unless $tdata->{'table_name'};
947dfd86	567	my $table = $schema->add_table(
947dfd86	568	name => $tdata->{'table_name'},
44567b47	569	comments => $tdata->{'comments'},
947dfd86	570	) or die $schema->error;
947dfd86	571
7c4cf567	572	$table->options( $tdata->{'table_options'} );
7c4cf567	573
947dfd86	574	my @fields = sort {
	575	$tdata->{'fields'}->{$a}->{'order'}
	576	<=>
	577	$tdata->{'fields'}->{$b}->{'order'}
	578	} keys %{ $tdata->{'fields'} };
	579
	580	for my $fname ( @fields ) {
	581	my $fdata = $tdata->{'fields'}{ $fname };
	582	my $field = $table->add_field(
	583	name => $fdata->{'name'},
	584	data_type => $fdata->{'data_type'},
	585	size => $fdata->{'size'},
	586	default_value => $fdata->{'default'},
	587	is_auto_increment => $fdata->{'is_auto_inc'},
	588	is_nullable => $fdata->{'null'},
44567b47	589	comments => $fdata->{'comments'},
947dfd86	590	) or die $table->error;
947dfd86	591	}
947dfd86	592
87053733	593	push @{ $tdata->{'indices'} }, @{ $indices->{ $table_name } \|\| [] };
6a36a3d8	594	push @{ $tdata->{'constraints'} },
6a36a3d8	595	@{ $constraints->{ $table_name } \|\| [] };
87053733	596
947dfd86	597	for my $idata ( @{ $tdata->{'indices'} \|\| [] } ) {
	598	my $index = $table->add_index(
	599	name => $idata->{'name'},
	600	type => uc $idata->{'type'},
	601	fields => $idata->{'fields'},
	602	) or die $table->error;
	603	}
	604
	605	for my $cdata ( @{ $tdata->{'constraints'} \|\| [] } ) {
	606	my $constraint = $table->add_constraint(
	607	name => $cdata->{'name'},
	608	type => $cdata->{'type'},
	609	fields => $cdata->{'fields'},
	610	reference_table => $cdata->{'reference_table'},
	611	reference_fields => $cdata->{'reference_fields'},
	612	match_type => $cdata->{'match_type'} \|\| '',
100684f3	613	on_delete => $cdata->{'on_delete'} \|\| $cdata->{'on_delete_do'},
100684f3	614	on_update => $cdata->{'on_update'} \|\| $cdata->{'on_update_do'},
947dfd86	615	) or die $table->error;
	616	}
	617	}
	618
f62bd16c	619	return 1;
17cae8ab	620	}
	621
	622	1;
	623
947dfd86	624	# -------------------------------------------------------------------
	625	# Something there is that doesn't love a wall.
	626	# Robert Frost
	627	# -------------------------------------------------------------------
	628
17cae8ab	629	=pod
	630
	631	=head1 AUTHOR
	632
	633	Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
	634
	635	=head1 SEE ALSO
	636
3b2be65a	637	SQL::Translator, Parse::RecDescent, DDL::Oracle.
17cae8ab	638
17cae8ab	639	=cut