add constraints after all tables are added
[dbsrgits/SQL-Translator-2.0-ish.git] / lib / SQL / Translator / Grammar / MySQL.pm
CommitLineData
247f09cd 1role 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 }
409 else {
c1ba0bf9 410 $size = $item[2][0];
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}