2 role SQL::Translator::Grammar::MySQL {
4 # -------------------------------------------------------------------
5 # Copyright (C) 2002-2009 SQLFairy Authors
7 # This program is free software; you can redistribute it and/or
8 # modify it under the terms of the GNU General Public License as
9 # published by the Free Software Foundation; version 2.
11 # This program is distributed in the hope that it will be useful, but
12 # WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 # General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program; if not, write to the Free Software
18 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
20 # -------------------------------------------------------------------
22 method _build_grammar {
25 my ( $database_name, %tables, $table_order, @table_comments, %views,
26 $view_order, %procedures, $proc_order );
31 # The "eofile" rule makes the parser fail if any "statement" rule
32 # fails. Otherwise, the first successful match by a "statement"
33 # won't cause the failure needed to know that the parse, as a whole,
36 startrule : statement(s) eofile {
38 database_name => $database_name,
41 procedures => \%procedures,
58 use : /use/i WORD "$delimiter"
60 $database_name = $item[2];
64 set : /set/i /[^;]+/ "$delimiter"
65 { @table_comments = () }
67 drop : /drop/i TABLE /[^;]+/ "$delimiter"
69 drop : /drop/i WORD(s) "$delimiter"
70 { @table_comments = () }
73 # MySQL strings, unlike common SQL strings, can be double-quoted or
74 # single-quoted, and you can escape the delmiters by doubling (but only the
75 # delimiter) or by backslashing.
77 /'(\\.|''|[^\\\'])*'/ |
79 # For reference, std sql str: /(?:(?:\')(?:[^\']*(?:(?:\'\')[^\']*)*)(?:\'))//
81 nonstring : /[^;\'"]+/
83 statement_body : string | nonstring
85 insert : /insert/i statement_body(s?) "$delimiter"
87 delimiter : /delimiter/i /[\S]+/
88 { $delimiter = $item[2] }
90 empty_statement : "$delimiter"
92 alter : ALTER TABLE table_name alter_specification(s /,/) "$delimiter"
94 my $table_name = $item{'table_name'};
95 die "Cannot ALTER table '$table_name'; it does not exist"
96 unless $tables{ $table_name };
97 for my $definition ( @{ $item[4] } ) {
98 $definition->{'extra'}->{'alter'} = 1;
99 push @{ $tables{ $table_name }{'constraints'} }, $definition;
103 alter_specification : ADD foreign_key_def
104 { $return = $item[2] }
106 create : CREATE /database/i WORD "$delimiter"
107 { @table_comments = () }
109 create : CREATE TEMPORARY(?) TABLE opt_if_not_exists(?) table_name '(' create_definition(s /,/) /(,\s*)?\)/ table_option(s?) "$delimiter"
111 my $table_name = $item{'table_name'};
112 $tables{ $table_name }{'order'} = ++$table_order;
113 $tables{ $table_name }{'name'} = $table_name;
115 if ( @table_comments ) {
116 $tables{ $table_name }{'comments'} = [ @table_comments ];
117 @table_comments = ();
121 for my $definition ( @{ $item[7] } ) {
122 if ( $definition->{'supertype'} eq 'field' ) {
123 my $field_name = $definition->{'name'};
124 # $tables{ $table_name }{'columns'}{ $field_name } = { %$definition, order => $i };
125 push @{$tables{$table_name}{'columns'}}, $definition;
128 if ( $definition->{'is_primary_key'} ) {
129 push @{ $tables{ $table_name }{'constraints'} },
131 type => 'primary_key',
132 fields => [ $field_name ],
137 elsif ( $definition->{'supertype'} eq 'constraint' ) {
138 push @{ $tables{ $table_name }{'constraints'} }, $definition;
140 elsif ( $definition->{'supertype'} eq 'index' ) {
141 push @{ $tables{ $table_name }{'indices'} }, $definition;
145 if ( my @options = @{ $item{'table_option(s?)'} } ) {
146 for my $option ( @options ) {
147 my ( $key, $value ) = each %$option;
148 if ( $key eq 'comment' ) {
149 push @{ $tables{ $table_name }{'comments'} }, $value;
152 push @{ $tables{ $table_name }{'table_options'} }, $option;
160 opt_if_not_exists : /if not exists/i
162 create : CREATE UNIQUE(?) /(index|key)/i index_name /on/i table_name '(' field_name(s /,/) ')' "$delimiter"
164 @table_comments = ();
165 push @{ $tables{ $item{'table_name'} }{'indices'} },
168 type => $item[2][0] ? 'unique' : 'normal',
174 create : CREATE /trigger/i NAME not_delimiter "$delimiter"
176 @table_comments = ();
179 create : CREATE PROCEDURE NAME not_delimiter "$delimiter"
181 @table_comments = ();
182 my $func_name = $item[3];
184 my $sql = "$item[1] $item[2] $item[3] $item[4]";
186 $procedures{ $func_name }{'order'} = ++$proc_order;
187 $procedures{ $func_name }{'name'} = $func_name;
188 $procedures{ $func_name }{'owner'} = $owner;
189 $procedures{ $func_name }{'sql'} = $sql;
192 PROCEDURE : /procedure/i
195 create : CREATE replace(?) algorithm(?) /view/i NAME not_delimiter "$delimiter"
197 @table_comments = ();
198 my $view_name = $item[5];
199 my $sql = join(q{ }, grep { defined and length } $item[1], $item[2]->[0], $item[3]->[0])
200 . " $item[4] $item[5] $item[6]";
202 # Hack to strip database from function calls in SQL
203 $sql =~ s#`\w+`\.(`\w+`\()##g;
205 $views{ $view_name }{'order'} = ++$view_order;
206 $views{ $view_name }{'name'} = $view_name;
207 $views{ $view_name }{'sql'} = $sql;
210 replace : /or replace/i
212 algorithm : /algorithm/i /=/ WORD
214 $return = "$item[1]=$item[3]";
217 not_delimiter : /.*?(?=$delimiter)/is
219 create_definition : constraint
225 comment : /^\s*(?:#|-{2}).*\n/
227 my $comment = $item[1];
228 $comment =~ s/^\s*(#|--)\s*//;
229 $comment =~ s/\s*$//;
233 comment : /\/\*/ /.*?\*\//s
235 my $comment = $item[2];
236 $comment = substr($comment, 0, -2);
237 $comment =~ s/^\s*|\s*$//g;
241 field_comment : /^\s*(?:#|-{2}).*\n/
243 my $comment = $item[1];
244 $comment =~ s/^\s*(#|--)\s*//;
245 $comment =~ s/\s*$//;
250 field_comment2 : /comment/i /'.*?'/
252 my $comment = $item[2];
260 field : field_comment(s?) field_name data_type field_qualifier(s?) field_comment2(?) reference_definition(?) on_update(?) field_comment(s?)
262 my %qualifiers = map { %$_ } @{ $item{'field_qualifier(s?)'} || [] };
263 if ( my @type_quals = @{ $item{'data_type'}{'qualifiers'} || [] } ) {
264 $qualifiers{ $_ } = 1 for @type_quals;
267 my $null = defined $qualifiers{'not_null'}
268 ? $qualifiers{'not_null'} : 1;
269 delete $qualifiers{'not_null'};
271 my @comments = ( @{ $item[1] }, @{ $item[5] }, @{ $item[8] } );
274 supertype => 'field',
275 name => $item{'field_name'},
276 data_type => $item{'data_type'}{'type'},
277 size => $item{'data_type'}{'size'},
278 list => $item{'data_type'}{'list'},
279 constraints => $item{'reference_definition(?)'},
280 comments => [ @comments ],
286 field_qualifier : not_null
289 is_nullable => $item{'not_null'},
293 field_qualifier : default_val
296 default => $item{'default_val'},
300 field_qualifier : auto_inc
303 is_auto_inc => $item{'auto_inc'},
307 field_qualifier : primary_key
310 is_primary_key => $item{'primary_key'},
314 field_qualifier : unsigned
317 is_unsigned => $item{'unsigned'},
321 field_qualifier : /character set/i WORD
324 'CHARACTER SET' => $item[2],
328 field_qualifier : /collate/i WORD
335 field_qualifier : /on update/i CURRENT_TIMESTAMP
338 'ON UPDATE' => $item[2],
342 field_qualifier : /unique/i KEY(?)
349 field_qualifier : KEY
356 reference_definition : /references/i table_name parens_field_list(?) match_type(?) on_delete(?) on_update(?)
359 type => 'foreign_key',
360 reference_table => $item[2],
361 reference_fields => $item[3][0],
362 match_type => $item[4][0],
363 on_delete => $item[5][0],
364 on_update => $item[6][0],
368 match_type : /match full/i { 'full' }
370 /match partial/i { 'partial' }
372 on_delete : /on delete/i reference_option
376 /on update/i 'CURRENT_TIMESTAMP'
379 /on update/i reference_option
382 reference_option: /restrict/i |
400 data_type : WORD parens_value_list(s?) type_qualifier(s?)
403 my $size; # field size, applicable only to non-set fields
404 my $list; # set list, applicable only to sets (duh)
406 if ( uc($type) =~ /^(SET|ENUM)$/ ) {
411 $size = $item[2][0][0];
420 qualifiers => $item[3],
424 parens_field_list : '(' field_name(s /,/) ')'
427 parens_value_list : '(' VALUE(s /,/) ')'
430 type_qualifier : /(BINARY|UNSIGNED|ZEROFILL)/i
435 create_index : /create/i /index/i
437 not_null : /not null/i
443 unsigned : /unsigned/i { $return = 0 }
445 #default_val : /default/i /(?:')?[\s\w\d:.-]*(?:')?/
447 # $item[2] =~ s/'//g;
448 # $return = $item[2];
452 /default/i 'CURRENT_TIMESTAMP'
457 /default/i /'(?:.*?(?:\\'|''))*.*?'|(?:')?[\w\d:.-]*(?:')?/
459 $item[2] =~ s/^\s*'|'\s*$//g;
463 auto_inc : /auto_increment/i { 1 }
465 primary_key : /primary/i /key/i { 1 }
467 constraint : primary_key_def
472 foreign_key_def : foreign_key_def_begin parens_field_list reference_definition
475 supertype => 'constraint',
476 type => 'foreign_key',
479 %{ $item{'reference_definition'} },
483 foreign_key_def_begin : /constraint/i /foreign key/i WORD
484 { $return = $item[3] }
486 /constraint/i NAME /foreign key/i
487 { $return = $item[2] }
489 /constraint/i /foreign key/i
493 { $return = $item[2] }
498 primary_key_def : primary_key index_name(?) '(' name_with_opt_paren(s /,/) ')'
501 supertype => 'constraint',
502 name => $item{'index_name(?)'}[0],
503 type => 'primary_key',
508 unique_key_def : UNIQUE KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
511 supertype => 'constraint',
512 name => $item{'index_name(?)'}[0],
518 normal_index : KEY index_name(?) '(' name_with_opt_paren(s /,/) ')'
521 supertype => 'index',
523 name => $item{'index_name(?)'}[0],
528 fulltext_index : /fulltext/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
531 supertype => 'index',
533 name => $item{'index_name(?)'}[0],
538 spatial_index : /spatial/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
541 supertype => 'index',
543 name => $item{'index_name(?)'}[0],
548 name_with_opt_paren : NAME parens_value_list(s?)
549 { $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
553 KEY : /key/i | /index/i
555 table_option : /comment/i /=/ /'.*?'/
557 my $comment = $item[3];
560 $return = { comment => $comment };
562 | /(default )?(charset|character set)/i /\s*=?\s*/ WORD
564 $return = { 'CHARACTER SET' => $item[3] };
568 $return = { 'COLLATE' => $item[2] }
570 | /union/i /\s*=\s*/ '(' table_name(s /,/) ')'
572 $return = { $item[1] => $item[4] };
574 | WORD /\s*=\s*/ MAYBE_QUOTED_WORD
576 $return = { $item[1] => $item[3] };
579 MAYBE_QUOTED_WORD: /\w+/
593 TEMPORARY : /temporary/i
607 NAME : BACKTICK /[^`]+/ BACKTICK
609 | DOUBLE_QUOTE /[^"]+/ DOUBLE_QUOTE
614 VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
618 # remove leading/trailing quotes
620 $val =~ s/^['"]|['"]$//g;
626 CURRENT_TIMESTAMP : /current_timestamp(\(\))?/i
628 { 'CURRENT_TIMESTAMP' }