[dbsrgits/SQL-Translator-2.0-ish.git] / lib / SQL / Translator / Grammar / MySQL.pm

role SQL::Translator::Grammar::MySQL {
# -------------------------------------------------------------------
# Copyright (C) 2002-2009 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
# -------------------------------------------------------------------
        
    method _build_grammar {
        return q!
        { 
            my ( $database_name, %tables, $table_order, @table_comments, %views,
                $view_order, %procedures, $proc_order );
            my $delimiter = ';';
        }
        
        #
        # 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 { 
            { 
                database_name => $database_name, 
                tables        => \%tables, 
                views         => \%views, 
                procedures    => \%procedures,
            } 
        }
        
        eofile : /^\Z/
        
        statement : comment
            | use
            | set
            | drop
            | create
            | alter
            | insert
            | delimiter
            | empty_statement
            | <error>
        
        use : /use/i WORD "$delimiter"
            {
                $database_name = $item[2];
                @table_comments = ();
            }
        
        set : /set/i /[^;]+/ "$delimiter"
            { @table_comments = () }
        
        drop : /drop/i TABLE /[^;]+/ "$delimiter"
        
        drop : /drop/i WORD(s) "$delimiter"
            { @table_comments = () }
        
        string :
          # MySQL strings, unlike common SQL strings, can be double-quoted or 
          # single-quoted, and you can escape the delmiters by doubling (but only the 
          # delimiter) or by backslashing.
        
           /'(\\.|''|[^\\\'])*'/ |
           /"(\\.|""|[^\\\"])*"/
          # For reference, std sql str: /(?:(?:\')(?:[^\']*(?:(?:\'\')[^\']*)*)(?:\'))//
        
        nonstring : /[^;\'"]+/
        
        statement_body : string | nonstring
        
        insert : /insert/i  statement_body(s?) "$delimiter"
        
        delimiter : /delimiter/i /[\S]+/
            { $delimiter = $item[2] }
        
        empty_statement : "$delimiter"
        
        alter : ALTER TABLE table_name alter_specification(s /,/) "$delimiter"
            {
                my $table_name                       = $item{'table_name'};
            die "Cannot ALTER table '$table_name'; it does not exist"
                unless $tables{ $table_name };
                for my $definition ( @{ $item[4] } ) { 
                $definition->{'extra'}->{'alter'} = 1;
                push @{ $tables{ $table_name }{'constraints'} }, $definition;
            }
            }
        
        alter_specification : ADD foreign_key_def
            { $return = $item[2] }
        
        create : CREATE /database/i WORD "$delimiter"
            { @table_comments = () }
        
        create : CREATE TEMPORARY(?) TABLE opt_if_not_exists(?) table_name '(' create_definition(s /,/) /(,\s*)?\)/ table_option(s?) "$delimiter"
            { 
                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;
                for my $definition ( @{ $item[7] } ) {
                    if ( $definition->{'supertype'} eq 'field' ) {
                        my $field_name = $definition->{'name'};
                        $tables{ $table_name }{'fields'}{ $field_name } = 
                            { %$definition, order => $i };
                        $i++;
                
                        if ( $definition->{'is_primary_key'} ) {
                            push @{ $tables{ $table_name }{'constraints'} },
                                {
                                    type   => 'primary_key',
                                    fields => [ $field_name ],
                                }
                            ;
                        }
                    }
                    elsif ( $definition->{'supertype'} eq 'constraint' ) {
                        push @{ $tables{ $table_name }{'constraints'} }, $definition;
                    }
                    elsif ( $definition->{'supertype'} eq 'index' ) {
                        push @{ $tables{ $table_name }{'indices'} }, $definition;
                    }
                }
        
                if ( my @options = @{ $item{'table_option(s?)'} } ) {
                    for my $option ( @options ) {
                        my ( $key, $value ) = each %$option;
                        if ( $key eq 'comment' ) {
                            push @{ $tables{ $table_name }{'comments'} }, $value;
                        }
                        else {
                            push @{ $tables{ $table_name }{'table_options'} }, $option;
                        }
                    }
                }
        
                1;
            }
        
        opt_if_not_exists : /if not exists/i
        
        create : CREATE UNIQUE(?) /(index|key)/i index_name /on/i table_name '(' field_name(s /,/) ')' "$delimiter"
            {
                @table_comments = ();
                push @{ $tables{ $item{'table_name'} }{'indices'} },
                    {
                        name   => $item[4],
                        type   => $item[2][0] ? 'unique' : 'normal',
                        fields => $item[8],
                    }
                ;
            }
        
        create : CREATE /trigger/i NAME not_delimiter "$delimiter"
            {
                @table_comments = ();
            }
        
        create : CREATE PROCEDURE NAME not_delimiter "$delimiter"
            {
                @table_comments = ();
                my $func_name = $item[3];
                my $owner = '';
                my $sql = "$item[1] $item[2] $item[3] $item[4]";
                
                $procedures{ $func_name }{'order'}  = ++$proc_order;
                $procedures{ $func_name }{'name'}   = $func_name;
                $procedures{ $func_name }{'owner'}  = $owner;
                $procedures{ $func_name }{'sql'}    = $sql;
            }
        
        PROCEDURE : /procedure/i
            | /function/i
        
        create : CREATE replace(?) algorithm(?) /view/i NAME not_delimiter "$delimiter"
            {
                @table_comments = ();
                my $view_name = $item[5];
                my $sql = join(q{ }, grep { defined and length } $item[1], $item[2]->[0], $item[3]->[0])
                    . " $item[4] $item[5] $item[6]";
                
                # Hack to strip database from function calls in SQL
                $sql =~ s#`\w+`\.(`\w+`\()##g;
                
                $views{ $view_name }{'order'}  = ++$view_order;
                $views{ $view_name }{'name'}   = $view_name;
                $views{ $view_name }{'sql'}    = $sql;
            }
        
        replace : /or replace/i
        
        algorithm : /algorithm/i /=/ WORD
            {
                $return = "$item[1]=$item[3]";
            }
        
        not_delimiter : /.*?(?=$delimiter)/is
        
        create_definition : constraint 
            | index
            | field
            | comment
            | <error>
        
        comment : /^\s*(?:#|-{2}).*\n/ 
            { 
                my $comment =  $item[1];
                $comment    =~ s/^\s*(#|--)\s*//;
                $comment    =~ s/\s*$//;
                $return     = $comment;
            }
        
        comment : /\/\*/ /.*?\*\//s
            {
                my $comment = $item[2];
                $comment = substr($comment, 0, -2);
                $comment    =~ s/^\s*|\s*$//g;
                $return = $comment;
            }
            
        field_comment : /^\s*(?:#|-{2}).*\n/ 
            { 
                my $comment =  $item[1];
                $comment    =~ s/^\s*(#|--)\s*//;
                $comment    =~ s/\s*$//;
                $return     = $comment;
            }
        
        
        field_comment2 : /comment/i /'.*?'/
            {
                my $comment = $item[2];
                $comment    =~ s/^'//;
                $comment    =~ s/'$//;
                $return     = $comment;
            }
        
        blank : /\s*/
        
        field : field_comment(s?) field_name data_type field_qualifier(s?) field_comment2(?) reference_definition(?) on_update(?) field_comment(s?)
            { 
                my %qualifiers  = map { %$_ } @{ $item{'field_qualifier(s?)'} || [] };
                if ( my @type_quals = @{ $item{'data_type'}{'qualifiers'} || [] } ) {
                    $qualifiers{ $_ } = 1 for @type_quals;
                }
        
                my $null = defined $qualifiers{'not_null'} 
                           ? $qualifiers{'not_null'} : 1;
                delete $qualifiers{'not_null'};
        
                my @comments = ( @{ $item[1] }, @{ $item[5] }, @{ $item[8] } );
        
                $return = { 
                    supertype   => 'field',
                    name        => $item{'field_name'}, 
                    data_type   => $item{'data_type'}{'type'},
                    size        => $item{'data_type'}{'size'},
                    list        => $item{'data_type'}{'list'},
                    null        => $null,
                    constraints => $item{'reference_definition(?)'},
                    comments    => [ @comments ],
                    %qualifiers,
                } 
            }
            | <error>
        
        field_qualifier : not_null
            { 
                $return = { 
                     null => $item{'not_null'},
                } 
            }
        
        field_qualifier : default_val
            { 
                $return = { 
                     default => $item{'default_val'},
                } 
            }
        
        field_qualifier : auto_inc
            { 
                $return = { 
                     is_auto_inc => $item{'auto_inc'},
                } 
            }
        
        field_qualifier : primary_key
            { 
                $return = { 
                     is_primary_key => $item{'primary_key'},
                } 
            }
        
        field_qualifier : unsigned
            { 
                $return = { 
                     is_unsigned => $item{'unsigned'},
                } 
            }
        
        field_qualifier : /character set/i WORD 
            {
                $return = {
                    'CHARACTER SET' => $item[2],
                }
            }
        
        field_qualifier : /collate/i WORD
            {
                $return = {
                    COLLATE => $item[2],
                }
            }
        
        field_qualifier : /on update/i CURRENT_TIMESTAMP
            {
                $return = {
                    'ON UPDATE' => $item[2],
                }
            }
        
        field_qualifier : /unique/i KEY(?)
            {
                $return = {
                    is_unique => 1,
                }
            }
        
        field_qualifier : KEY
            {
                $return = {
                    has_index => 1,
                }
            }
        
        reference_definition : /references/i table_name parens_field_list(?) match_type(?) on_delete(?) on_update(?)
            {
                $return = {
                    type             => 'foreign_key',
                    reference_table  => $item[2],
                    reference_fields => $item[3][0],
                    match_type       => $item[4][0],
                    on_delete        => $item[5][0],
                    on_update        => $item[6][0],
                }
            }
        
        match_type : /match full/i { 'full' }
            |
            /match partial/i { 'partial' }
        
        on_delete : /on delete/i reference_option
            { $item[2] }
        
        on_update : 
            /on update/i 'CURRENT_TIMESTAMP'
            { $item[2] }
            |
            /on update/i reference_option
            { $item[2] }
        
        reference_option: /restrict/i | 
            /cascade/i   | 
            /set null/i  | 
            /no action/i | 
            /set default/i
            { $item[1] }  
        
        index : normal_index
            | fulltext_index
            | spatial_index
            | <error>
        
        table_name   : NAME
        
        field_name   : NAME
        
        index_name   : NAME
        
        data_type    : WORD parens_value_list(s?) type_qualifier(s?)
            { 
                my $type = $item[1];
                my $size; # field size, applicable only to non-set fields
                my $list; # set list, applicable only to sets (duh)
        
                if ( uc($type) =~ /^(SET|ENUM)$/ ) {
                    $size = undef;
                    $list = $item[2][0];
                }
                else {
                    $size = $item[2][0];
                    $list = [];
                }
        
        
                $return        = { 
                    type       => $type,
                    size       => $size,
                    list       => $list,
                    qualifiers => $item[3],
                } 
            }
        
        parens_field_list : '(' field_name(s /,/) ')'
            { $item[2] }
        
        parens_value_list : '(' VALUE(s /,/) ')'
            { $item[2] }
        
        type_qualifier : /(BINARY|UNSIGNED|ZEROFILL)/i
            { lc $item[1] }
        
        field_type   : WORD
        
        create_index : /create/i /index/i
        
        not_null     : /not/i /null/i 
            { $return = 0 }
            |
            /null/i
            { $return = 1 }
        
        unsigned     : /unsigned/i { $return = 0 }
        
        #default_val  : /default/i /(?:')?[\s\w\d:.-]*(?:')?/ 
        #    { 
        #        $item[2] =~ s/'//g; 
        #        $return  =  $item[2];
        #    }
        
        default_val : 
            /default/i 'CURRENT_TIMESTAMP'
            {
                $return =  \$item[2];
            }
            |
            /default/i /'(?:.*?(?:\\'|''))*.*?'|(?:')?[\w\d:.-]*(?:')?/
            {
                $item[2] =~ s/^\s*'|'\s*$//g;
                $return  =  $item[2];
            }
        
        auto_inc : /auto_increment/i { 1 }
        
        primary_key : /primary/i /key/i { 1 }
        
        constraint : primary_key_def
            | unique_key_def
            | foreign_key_def
            | <error>
        
        foreign_key_def : foreign_key_def_begin parens_field_list reference_definition
            {
                $return              =  {
                    supertype        => 'constraint',
                    type             => 'foreign_key',
                    name             => $item[1],
                    fields           => $item[2],
                    %{ $item{'reference_definition'} },
                }
            }
        
        foreign_key_def_begin : /constraint/i /foreign key/i WORD
            { $return = $item[3] }
            |
            /constraint/i NAME /foreign key/i
            { $return = $item[2] }
            |
            /constraint/i /foreign key/i
            { $return = '' }
            |
            /foreign key/i WORD
            { $return = $item[2] }
            |
            /foreign key/i
            { $return = '' }
        
        primary_key_def : primary_key index_name(?) '(' name_with_opt_paren(s /,/) ')'
            { 
                $return       = { 
                    supertype => 'constraint',
                    name      => $item{'index_name(?)'}[0],
                    type      => 'primary_key',
                    fields    => $item[4],
                };
            }
        
        unique_key_def : UNIQUE KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
            { 
                $return       = { 
                    supertype => 'constraint',
                    name      => $item{'index_name(?)'}[0],
                    type      => 'unique',
                    fields    => $item[5],
                } 
            }
        
        normal_index : KEY index_name(?) '(' name_with_opt_paren(s /,/) ')'
            { 
                $return       = { 
                    supertype => 'index',
                    type      => 'normal',
                    name      => $item{'index_name(?)'}[0],
                    fields    => $item[4],
                } 
            }
        
        fulltext_index : /fulltext/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
            { 
                $return       = { 
                    supertype => 'index',
                    type      => 'fulltext',
                    name      => $item{'index_name(?)'}[0],
                    fields    => $item[5],
                } 
            }
        
        spatial_index : /spatial/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
            { 
                $return       = { 
                    supertype => 'index',
                    type      => 'spatial',
                    name      => $item{'index_name(?)'}[0],
                    fields    => $item[5],
                } 
            }
        
        name_with_opt_paren : NAME parens_value_list(s?)
            { $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
        
        UNIQUE : /unique/i
        
        KEY : /key/i | /index/i
        
        table_option : /comment/i /=/ /'.*?'/
            {
                my $comment = $item[3];
                $comment    =~ s/^'//;
                $comment    =~ s/'$//;
                $return     = { comment => $comment };
            }
            | /(default )?(charset|character set)/i /\s*=?\s*/ WORD
            { 
                $return = { 'CHARACTER SET' => $item[3] };
            }
            | /collate/i WORD
            {
                $return = { 'COLLATE' => $item[2] }
            }
            | /union/i /\s*=\s*/ '(' table_name(s /,/) ')'
            { 
                $return = { $item[1] => $item[4] };
            }
            | WORD /\s*=\s*/ MAYBE_QUOTED_WORD
            {
                $return = { $item[1] => $item[3] };
            }
        
        MAYBE_QUOTED_WORD: /\w+/
                         | /'(\w+)'/
                         { $return = $1 }
                         | /"(\w+)"/
                         { $return = $1 }
        
        default : /default/i
        
        ADD : /add/i
        
        ALTER : /alter/i
        
        CREATE : /create/i
        
        TEMPORARY : /temporary/i
        
        TABLE : /table/i
        
        WORD : /\w+/
        
        DIGITS : /\d+/
        
        COMMA : ','
        
        BACKTICK : '`'
        
        DOUBLE_QUOTE: '"'
        
        NAME    : BACKTICK /[^`]+/ BACKTICK
            { $item[2] }
            | DOUBLE_QUOTE /[^"]+/ DOUBLE_QUOTE
            { $item[2] }
            | /\w+/
            { $item[1] }
        
        VALUE   : /[-+]?\.?\d+(?:[eE]\d+)?/
            { $item[1] }
            | /'.*?'/   
            { 
                # remove leading/trailing quotes 
                my $val = $item[1];
                $val    =~ s/^['"]|['"]$//g;
                $return = $val;
            }
            | /NULL/
            { 'NULL' }
        
        CURRENT_TIMESTAMP : /current_timestamp(\(\))?/i
            | /now\(\)/i
            { 'CURRENT_TIMESTAMP' }
        !;
    }
}
Commit	Line	Data
247f09cd	1	role SQL::Translator::Grammar::MySQL {
247f09cd	2	# -------------------------------------------------------------------
	3	# Copyright (C) 2002-2009 SQLFairy Authors
	4	#
	5	# This program is free software; you can redistribute it and/or
	6	# modify it under the terms of the GNU General Public License as
	7	# published by the Free Software Foundation; version 2.
	8	#
	9	# This program is distributed in the hope that it will be useful, but
	10	# WITHOUT ANY WARRANTY; without even the implied warranty of
	11	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
	12	# General Public License for more details.
	13	#
	14	# You should have received a copy of the GNU General Public License
	15	# along with this program; if not, write to the Free Software
	16	# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
	17	# 02111-1307 USA
	18	# -------------------------------------------------------------------
c1ba0bf9	19
	20	method _build_grammar {
	21	return q!
	22	{
	23	my ( $database_name, %tables, $table_order, @table_comments, %views,
	24	$view_order, %procedures, $proc_order );
	25	my $delimiter = ';';
247f09cd	26	}
c1ba0bf9	27
	28	#
	29	# The "eofile" rule makes the parser fail if any "statement" rule
	30	# fails. Otherwise, the first successful match by a "statement"
	31	# won't cause the failure needed to know that the parse, as a whole,
	32	# failed. -ky
	33	#
	34	startrule : statement(s) eofile {
	35	{
	36	database_name => $database_name,
	37	tables => \%tables,
	38	views => \%views,
	39	procedures => \%procedures,
	40	}
	41	}
	42
	43	eofile : /^\Z/
	44
	45	statement : comment
	46	\| use
	47	\| set
	48	\| drop
	49	\| create
	50	\| alter
	51	\| insert
	52	\| delimiter
	53	\| empty_statement
	54	\| <error>
	55
	56	use : /use/i WORD "$delimiter"
	57	{
	58	$database_name = $item[2];
	59	@table_comments = ();
	60	}
	61
	62	set : /set/i /[^;]+/ "$delimiter"
	63	{ @table_comments = () }
	64
	65	drop : /drop/i TABLE /[^;]+/ "$delimiter"
	66
	67	drop : /drop/i WORD(s) "$delimiter"
	68	{ @table_comments = () }
	69
	70	string :
	71	# MySQL strings, unlike common SQL strings, can be double-quoted or
	72	# single-quoted, and you can escape the delmiters by doubling (but only the
	73	# delimiter) or by backslashing.
	74
	75	/'(\\.\|''\|[^\\\'])*'/ \|
	76	/"(\\.\|""\|[^\\\"])*"/
	77	# For reference, std sql str: /(?:(?:\')(?:[^\'](?:(?:\'\')[^\'])*)(?:\'))//
	78
	79	nonstring : /[^;\'"]+/
	80
	81	statement_body : string \| nonstring
	82
	83	insert : /insert/i statement_body(s?) "$delimiter"
	84
	85	delimiter : /delimiter/i /[\S]+/
	86	{ $delimiter = $item[2] }
	87
	88	empty_statement : "$delimiter"
	89
	90	alter : ALTER TABLE table_name alter_specification(s /,/) "$delimiter"
91	{
92	my $table_name = $item{'table_name'};
93	die "Cannot ALTER table '$table_name'; it does not exist"
94	unless $tables{ $table_name };
95	for my $definition ( @{ $item[4] } ) {
96	$definition->{'extra'}->{'alter'} = 1;
97	push @{ $tables{ $table_name }{'constraints'} }, $definition;
98	}
99	}
100
101	alter_specification : ADD foreign_key_def
102	{ $return = $item[2] }
103
104	create : CREATE /database/i WORD "$delimiter"
105	{ @table_comments = () }
106
107	create : CREATE TEMPORARY(?) TABLE opt_if_not_exists(?) table_name '(' create_definition(s /,/) /(,\s*)?\)/ table_option(s?) "$delimiter"
108	{
109	my $table_name = $item{'table_name'};
110	$tables{ $table_name }{'order'} = ++$table_order;
111	$tables{ $table_name }{'table_name'} = $table_name;
112
113	if ( @table_comments ) {
114	$tables{ $table_name }{'comments'} = [ @table_comments ];
115	@table_comments = ();
116	}
117
118	my $i = 1;
119	for my $definition ( @{ $item[7] } ) {
120	if ( $definition->{'supertype'} eq 'field' ) {
121	my $field_name = $definition->{'name'};
122	$tables{ $table_name }{'fields'}{ $field_name } =
123	{ %$definition, order => $i };
124	$i++;
125
126	if ( $definition->{'is_primary_key'} ) {
127	push @{ $tables{ $table_name }{'constraints'} },
128	{
129	type => 'primary_key',
130	fields => [ $field_name ],
131	}
132	;
247f09cd	133	}
c1ba0bf9	134	}
	135	elsif ( $definition->{'supertype'} eq 'constraint' ) {
	136	push @{ $tables{ $table_name }{'constraints'} }, $definition;
	137	}
	138	elsif ( $definition->{'supertype'} eq 'index' ) {
	139	push @{ $tables{ $table_name }{'indices'} }, $definition;
	140	}
247f09cd	141	}
c1ba0bf9	142
	143	if ( my @options = @{ $item{'table_option(s?)'} } ) {
	144	for my $option ( @options ) {
	145	my ( $key, $value ) = each %$option;
	146	if ( $key eq 'comment' ) {
	147	push @{ $tables{ $table_name }{'comments'} }, $value;
	148	}
	149	else {
	150	push @{ $tables{ $table_name }{'table_options'} }, $option;
	151	}
	152	}
	153	}
	154
	155	1;
247f09cd	156	}
c1ba0bf9	157
	158	opt_if_not_exists : /if not exists/i
	159
	160	create : CREATE UNIQUE(?) /(index\|key)/i index_name /on/i table_name '(' field_name(s /,/) ')' "$delimiter"
	161	{
	162	@table_comments = ();
	163	push @{ $tables{ $item{'table_name'} }{'indices'} },
	164	{
	165	name => $item[4],
	166	type => $item[2][0] ? 'unique' : 'normal',
	167	fields => $item[8],
	168	}
	169	;
247f09cd	170	}
c1ba0bf9	171
	172	create : CREATE /trigger/i NAME not_delimiter "$delimiter"
	173	{
	174	@table_comments = ();
247f09cd	175	}
c1ba0bf9	176
	177	create : CREATE PROCEDURE NAME not_delimiter "$delimiter"
	178	{
	179	@table_comments = ();
	180	my $func_name = $item[3];
	181	my $owner = '';
	182	my $sql = "$item[1] $item[2] $item[3] $item[4]";
	183
	184	$procedures{ $func_name }{'order'} = ++$proc_order;
	185	$procedures{ $func_name }{'name'} = $func_name;
	186	$procedures{ $func_name }{'owner'} = $owner;
	187	$procedures{ $func_name }{'sql'} = $sql;
	188	}
	189
	190	PROCEDURE : /procedure/i
	191	\| /function/i
	192
	193	create : CREATE replace(?) algorithm(?) /view/i NAME not_delimiter "$delimiter"
	194	{
	195	@table_comments = ();
	196	my $view_name = $item[5];
	197	my $sql = join(q{ }, grep { defined and length } $item[1], $item[2]->[0], $item[3]->[0])
	198	. " $item[4] $item[5] $item[6]";
	199
	200	# Hack to strip database from function calls in SQL
	201	$sql =~ s#`\w+`\.(`\w+`\()##g;
	202
	203	$views{ $view_name }{'order'} = ++$view_order;
	204	$views{ $view_name }{'name'} = $view_name;
	205	$views{ $view_name }{'sql'} = $sql;
	206	}
	207
	208	replace : /or replace/i
	209
	210	algorithm : /algorithm/i /=/ WORD
	211	{
	212	$return = "$item[1]=$item[3]";
	213	}
	214
	215	not_delimiter : /.*?(?=$delimiter)/is
	216
	217	create_definition : constraint
	218	\| index
	219	\| field
	220	\| comment
	221	\| <error>
	222
	223	comment : /^\s(?:#\|-{2}).\n/
	224	{
	225	my $comment = $item[1];
	226	$comment =~ s/^\s(#\|--)\s//;
	227	$comment =~ s/\s*$//;
	228	$return = $comment;
	229	}
	230
	231	comment : /\/\/ /.?\*\//s
	232	{
	233	my $comment = $item[2];
	234	$comment = substr($comment, 0, -2);
	235	$comment =~ s/^\s\|\s$//g;
	236	$return = $comment;
	237	}
	238
	239	field_comment : /^\s(?:#\|-{2}).\n/
240	{
241	my $comment = $item[1];
242	$comment =~ s/^\s(#\|--)\s//;
243	$comment =~ s/\s*$//;
244	$return = $comment;
245	}
246
247
248	field_comment2 : /comment/i /'.*?'/
249	{
250	my $comment = $item[2];
251	$comment =~ s/^'//;
252	$comment =~ s/'$//;
253	$return = $comment;
254	}
255
256	blank : /\s*/
257
258	field : field_comment(s?) field_name data_type field_qualifier(s?) field_comment2(?) reference_definition(?) on_update(?) field_comment(s?)
259	{
260	my %qualifiers = map { %$_ } @{ $item{'field_qualifier(s?)'} \|\| [] };
261	if ( my @type_quals = @{ $item{'data_type'}{'qualifiers'} \|\| [] } ) {
262	$qualifiers{ $_ } = 1 for @type_quals;
263	}
264
265	my $null = defined $qualifiers{'not_null'}
266	? $qualifiers{'not_null'} : 1;
267	delete $qualifiers{'not_null'};
268
269	my @comments = ( @{ $item[1] }, @{ $item[5] }, @{ $item[8] } );
270
271	$return = {
272	supertype => 'field',
273	name => $item{'field_name'},
274	data_type => $item{'data_type'}{'type'},
275	size => $item{'data_type'}{'size'},
276	list => $item{'data_type'}{'list'},
277	null => $null,
278	constraints => $item{'reference_definition(?)'},
279	comments => [ @comments ],
280	%qualifiers,
281	}
282	}
283	\| <error>
284
285	field_qualifier : not_null
286	{
287	$return = {
288	null => $item{'not_null'},
289	}
290	}
291
292	field_qualifier : default_val
293	{
294	$return = {
295	default => $item{'default_val'},
296	}
297	}
298
299	field_qualifier : auto_inc
300	{
301	$return = {
302	is_auto_inc => $item{'auto_inc'},
303	}
304	}
305
306	field_qualifier : primary_key
307	{
308	$return = {
309	is_primary_key => $item{'primary_key'},
310	}
311	}
312
313	field_qualifier : unsigned
314	{
315	$return = {
316	is_unsigned => $item{'unsigned'},
317	}
318	}
319
320	field_qualifier : /character set/i WORD
321	{
322	$return = {
323	'CHARACTER SET' => $item[2],
324	}
325	}
326
327	field_qualifier : /collate/i WORD
328	{
329	$return = {
330	COLLATE => $item[2],
331	}
332	}
333
334	field_qualifier : /on update/i CURRENT_TIMESTAMP
335	{
336	$return = {
337	'ON UPDATE' => $item[2],
338	}
339	}
340
341	field_qualifier : /unique/i KEY(?)
342	{
343	$return = {
344	is_unique => 1,
345	}
346	}
347
348	field_qualifier : KEY
349	{
350	$return = {
351	has_index => 1,
352	}
353	}
354
355	reference_definition : /references/i table_name parens_field_list(?) match_type(?) on_delete(?) on_update(?)
356	{
357	$return = {
358	type => 'foreign_key',
359	reference_table => $item[2],
360	reference_fields => $item[3][0],
361	match_type => $item[4][0],
362	on_delete => $item[5][0],
363	on_update => $item[6][0],
364	}
365	}
366
367	match_type : /match full/i { 'full' }
368	\|
369	/match partial/i { 'partial' }
370
371	on_delete : /on delete/i reference_option
372	{ $item[2] }
373
374	on_update :
375	/on update/i 'CURRENT_TIMESTAMP'
376	{ $item[2] }
377	\|
378	/on update/i reference_option
379	{ $item[2] }
380
381	reference_option: /restrict/i \|
382	/cascade/i \|
383	/set null/i \|
384	/no action/i \|
385	/set default/i
386	{ $item[1] }
387
388	index : normal_index
389	\| fulltext_index
390	\| spatial_index
391	\| <error>
392
393	table_name : NAME
394
395	field_name : NAME
396
397	index_name : NAME
398
399	data_type : WORD parens_value_list(s?) type_qualifier(s?)
400	{
401	my $type = $item[1];
402	my $size; # field size, applicable only to non-set fields
403	my $list; # set list, applicable only to sets (duh)
404
405	if ( uc($type) =~ /^(SET\|ENUM)$/ ) {
406	$size = undef;
407	$list = $item[2][0];
247f09cd	408	}
247f09cd	409	else {
c1ba0bf9	410	$size = $item[2][0];
c1ba0bf9	411	$list = [];
247f09cd	412	}
c1ba0bf9	413
	414
	415	$return = {
	416	type => $type,
	417	size => $size,
	418	list => $list,
	419	qualifiers => $item[3],
	420	}
247f09cd	421	}
c1ba0bf9	422
	423	parens_field_list : '(' field_name(s /,/) ')'
	424	{ $item[2] }
	425
	426	parens_value_list : '(' VALUE(s /,/) ')'
	427	{ $item[2] }
	428
	429	type_qualifier : /(BINARY\|UNSIGNED\|ZEROFILL)/i
	430	{ lc $item[1] }
	431
	432	field_type : WORD
	433
	434	create_index : /create/i /index/i
	435
	436	not_null : /not/i /null/i
	437	{ $return = 0 }
	438	\|
	439	/null/i
	440	{ $return = 1 }
	441
	442	unsigned : /unsigned/i { $return = 0 }
	443
	444	#default_val : /default/i /(?:')?[\s\w\d:.-]*(?:')?/
	445	# {
	446	# $item[2] =~ s/'//g;
	447	# $return = $item[2];
	448	# }
	449
	450	default_val :
	451	/default/i 'CURRENT_TIMESTAMP'
247f09cd	452	{
c1ba0bf9	453	$return = \$item[2];
247f09cd	454	}
c1ba0bf9	455	\|
	456	/default/i /'(?:.?(?:\\'\|'')).?'\|(?:')?[\w\d:.-](?:')?/
	457	{
	458	$item[2] =~ s/^\s'\|'\s$//g;
	459	$return = $item[2];
	460	}
	461
	462	auto_inc : /auto_increment/i { 1 }
	463
	464	primary_key : /primary/i /key/i { 1 }
	465
	466	constraint : primary_key_def
	467	\| unique_key_def
	468	\| foreign_key_def
	469	\| <error>
	470
	471	foreign_key_def : foreign_key_def_begin parens_field_list reference_definition
	472	{
	473	$return = {
	474	supertype => 'constraint',
	475	type => 'foreign_key',
	476	name => $item[1],
	477	fields => $item[2],
	478	%{ $item{'reference_definition'} },
	479	}
	480	}
	481
	482	foreign_key_def_begin : /constraint/i /foreign key/i WORD
	483	{ $return = $item[3] }
	484	\|
	485	/constraint/i NAME /foreign key/i
	486	{ $return = $item[2] }
	487	\|
	488	/constraint/i /foreign key/i
	489	{ $return = '' }
	490	\|
	491	/foreign key/i WORD
	492	{ $return = $item[2] }
	493	\|
	494	/foreign key/i
	495	{ $return = '' }
	496
	497	primary_key_def : primary_key index_name(?) '(' name_with_opt_paren(s /,/) ')'
	498	{
	499	$return = {
	500	supertype => 'constraint',
	501	name => $item{'index_name(?)'}[0],
	502	type => 'primary_key',
	503	fields => $item[4],
	504	};
	505	}
	506
	507	unique_key_def : UNIQUE KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
	508	{
	509	$return = {
	510	supertype => 'constraint',
	511	name => $item{'index_name(?)'}[0],
	512	type => 'unique',
	513	fields => $item[5],
	514	}
	515	}
	516
	517	normal_index : KEY index_name(?) '(' name_with_opt_paren(s /,/) ')'
	518	{
519	$return = {
520	supertype => 'index',
521	type => 'normal',
522	name => $item{'index_name(?)'}[0],
523	fields => $item[4],
524	}
525	}
526
527	fulltext_index : /fulltext/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
528	{
529	$return = {
530	supertype => 'index',
531	type => 'fulltext',
532	name => $item{'index_name(?)'}[0],
533	fields => $item[5],
534	}
535	}
536
537	spatial_index : /spatial/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
538	{
539	$return = {
540	supertype => 'index',
541	type => 'spatial',
542	name => $item{'index_name(?)'}[0],
543	fields => $item[5],
544	}
545	}
546
547	name_with_opt_paren : NAME parens_value_list(s?)
548	{ $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
549
550	UNIQUE : /unique/i
551
552	KEY : /key/i \| /index/i
553
554	table_option : /comment/i /=/ /'.*?'/
555	{
556	my $comment = $item[3];
557	$comment =~ s/^'//;
558	$comment =~ s/'$//;
559	$return = { comment => $comment };
560	}
561	\| /(default )?(charset\|character set)/i /\s=?\s/ WORD
562	{
563	$return = { 'CHARACTER SET' => $item[3] };
564	}
565	\| /collate/i WORD
566	{
567	$return = { 'COLLATE' => $item[2] }
568	}
569	\| /union/i /\s=\s/ '(' table_name(s /,/) ')'
570	{
571	$return = { $item[1] => $item[4] };
572	}
573	\| WORD /\s=\s/ MAYBE_QUOTED_WORD
574	{
575	$return = { $item[1] => $item[3] };
576	}
577
578	MAYBE_QUOTED_WORD: /\w+/
579	\| /'(\w+)'/
580	{ $return = $1 }
581	\| /"(\w+)"/
582	{ $return = $1 }
583
584	default : /default/i
585
586	ADD : /add/i
587
588	ALTER : /alter/i
589
590	CREATE : /create/i
591
592	TEMPORARY : /temporary/i
593
594	TABLE : /table/i
595
596	WORD : /\w+/
597
598	DIGITS : /\d+/
599
600	COMMA : ','
601
602	BACKTICK : '`'
603
604	DOUBLE_QUOTE: '"'
605
606	NAME : BACKTICK /[^`]+/ BACKTICK
607	{ $item[2] }
608	\| DOUBLE_QUOTE /[^"]+/ DOUBLE_QUOTE
609	{ $item[2] }
610	\| /\w+/
611	{ $item[1] }
612
613	VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
614	{ $item[1] }
615	\| /'.*?'/
616	{
617	# remove leading/trailing quotes
618	my $val = $item[1];
619	$val =~ s/^['"]\|['"]$//g;
620	$return = $val;
621	}
622	\| /NULL/
623	{ 'NULL' }
624
625	CURRENT_TIMESTAMP : /current_timestamp(\(\))?/i
626	\| /now\(\)/i
627	{ 'CURRENT_TIMESTAMP' }
628	!;
247f09cd	629	}
247f09cd	630	}