Allow passing an arrayref to SQLT->filename
[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / MySQL.pm
CommitLineData
16dc9970 1package SQL::Translator::Parser::MySQL;
2
d529894e 3=head1 NAME
4
5SQL::Translator::Parser::MySQL - parser for MySQL
6
7=head1 SYNOPSIS
8
9 use SQL::Translator;
10 use SQL::Translator::Parser::MySQL;
11
12 my $translator = SQL::Translator->new;
13 $translator->parser("SQL::Translator::Parser::MySQL");
14
15=head1 DESCRIPTION
16
17The grammar is influenced heavily by Tim Bunce's "mysql2ora" grammar.
18
629b76f9 19Here's the word from the MySQL site
20(http://www.mysql.com/doc/en/CREATE_TABLE.html):
21
22 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
23 [table_options] [select_statement]
ea93df61 24
629b76f9 25 or
ea93df61 26
629b76f9 27 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;
ea93df61 28
629b76f9 29 create_definition:
30 col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
31 [PRIMARY KEY] [reference_definition]
32 or PRIMARY KEY (index_col_name,...)
33 or KEY [index_name] (index_col_name,...)
34 or INDEX [index_name] (index_col_name,...)
35 or UNIQUE [INDEX] [index_name] (index_col_name,...)
36 or FULLTEXT [INDEX] [index_name] (index_col_name,...)
37 or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
38 [reference_definition]
39 or CHECK (expr)
ea93df61 40
629b76f9 41 type:
42 TINYINT[(length)] [UNSIGNED] [ZEROFILL]
43 or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
44 or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
45 or INT[(length)] [UNSIGNED] [ZEROFILL]
46 or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
47 or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
48 or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
49 or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
50 or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
51 or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
52 or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
53 or CHAR(length) [BINARY]
54 or VARCHAR(length) [BINARY]
55 or DATE
56 or TIME
57 or TIMESTAMP
58 or DATETIME
59 or TINYBLOB
60 or BLOB
61 or MEDIUMBLOB
62 or LONGBLOB
63 or TINYTEXT
64 or TEXT
65 or MEDIUMTEXT
66 or LONGTEXT
67 or ENUM(value1,value2,value3,...)
68 or SET(value1,value2,value3,...)
ea93df61 69
629b76f9 70 index_col_name:
71 col_name [(length)]
ea93df61 72
629b76f9 73 reference_definition:
74 REFERENCES tbl_name [(index_col_name,...)]
75 [MATCH FULL | MATCH PARTIAL]
76 [ON DELETE reference_option]
77 [ON UPDATE reference_option]
ea93df61 78
629b76f9 79 reference_option:
80 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
ea93df61 81
629b76f9 82 table_options:
83 TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
a7f49dfb 84 or ENGINE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
629b76f9 85 or AUTO_INCREMENT = #
86 or AVG_ROW_LENGTH = #
a7f49dfb 87 or [ DEFAULT ] CHARACTER SET charset_name
629b76f9 88 or CHECKSUM = {0 | 1}
a7f49dfb 89 or COLLATE collation_name
629b76f9 90 or COMMENT = "string"
91 or MAX_ROWS = #
92 or MIN_ROWS = #
93 or PACK_KEYS = {0 | 1 | DEFAULT}
94 or PASSWORD = "string"
95 or DELAY_KEY_WRITE = {0 | 1}
96 or ROW_FORMAT= { default | dynamic | fixed | compressed }
97 or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
98 or UNION = (table_name,[table_name...])
99 or INSERT_METHOD= {NO | FIRST | LAST }
100 or DATA DIRECTORY="absolute path to directory"
101 or INDEX DIRECTORY="absolute path to directory"
102
a7f49dfb 103
13aec984 104A subset of the ALTER TABLE syntax that allows addition of foreign keys:
105
106 ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
107
108 alter_specification:
109 ADD [CONSTRAINT [symbol]]
110 FOREIGN KEY [index_name] (index_col_name,...)
111 [reference_definition]
112
113A subset of INSERT that we ignore:
114
115 INSERT anything
116
5d666b31 117=head1 ARGUMENTS
118
119This parser takes a single optional parser_arg C<mysql_parser_version>, which
120provides the desired version for the target database. Any statement in the processed
121dump file, that is commented with a version higher than the one supplied, will be stripped.
122
aa4301a7 123The default C<mysql_parser_version> is set to the conservative value of 40000 (MySQL 4.0)
124
5d666b31 125Valid version specifiers for C<mysql_parser_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
126
127More information about the MySQL comment-syntax: L<http://dev.mysql.com/doc/refman/5.0/en/comments.html>
128
129
d529894e 130=cut
131
16dc9970 132use strict;
f27f9229 133use warnings;
bdf60588 134
0c04c5a2 135our $VERSION = '1.59';
bdf60588 136
137our $DEBUG;
8d0f3086 138$DEBUG = 0 unless defined $DEBUG;
077ebf34 139
d529894e 140use Data::Dumper;
07d6e5f7 141use Storable qw(dclone);
6b2dbb1a 142use DBI qw(:sql_types);
bdf60588 143use SQL::Translator::Utils qw/parse_mysql_version ddl_parser_instance/;
077ebf34 144
bdf60588 145use base qw(Exporter);
146our @EXPORT_OK = qw(parse);
5d666b31 147
f1fe509d 148our %type_mapping = ();
6b2dbb1a 149
aa4301a7 150use constant DEFAULT_PARSER_VERSION => 40000;
d31c185b 151
bdf60588 152our $GRAMMAR = << 'END_OF_GRAMMAR';
d529894e 153
ea93df61 154{
f1fe509d 155 my ( $database_name, %tables, $table_order, @table_comments, %views,
156 $view_order, %procedures, $proc_order );
86318717 157 my $delimiter = ';';
8ccdeb42 158}
d529894e 159
629b76f9 160#
161# The "eofile" rule makes the parser fail if any "statement" rule
ea93df61 162# fails. Otherwise, the first successful match by a "statement"
629b76f9 163# won't cause the failure needed to know that the parse, as a whole,
164# failed. -ky
165#
ea93df61 166startrule : statement(s) eofile {
167 {
168 database_name => $database_name,
169 tables => \%tables,
170 views => \%views,
f1fe509d 171 procedures => \%procedures,
ea93df61 172 }
13aec984 173}
629b76f9 174
175eofile : /^\Z/
d529894e 176
177statement : comment
dcb4fa06 178 | use
33d0d6d4 179 | set
61745327 180 | drop
d529894e 181 | create
13aec984 182 | alter
183 | insert
86318717 184 | delimiter
185 | empty_statement
d529894e 186 | <error>
187
86318717 188use : /use/i WORD "$delimiter"
13aec984 189 {
190 $database_name = $item[2];
191 @table_comments = ();
192 }
dcb4fa06 193
aa4301a7 194set : /set/i not_delimiter "$delimiter"
c5dabd71 195 { @table_comments = () }
734dfc91 196
aa4301a7 197drop : /drop/i TABLE not_delimiter "$delimiter"
33d0d6d4 198
86318717 199drop : /drop/i WORD(s) "$delimiter"
c5dabd71 200 { @table_comments = () }
61745327 201
bc9932bf 202bit:
b98bd48d 203 /(b'[01]{1,64}')/ |
204 /(b"[01]{1,64}")/
bc9932bf 205
9bf756df 206string :
ea93df61 207 # MySQL strings, unlike common SQL strings, can be double-quoted or
208 # single-quoted, and you can escape the delmiters by doubling (but only the
9bf756df 209 # delimiter) or by backslashing.
210
211 /'(\\.|''|[^\\\'])*'/ |
212 /"(\\.|""|[^\\\"])*"/
213 # For reference, std sql str: /(?:(?:\')(?:[^\']*(?:(?:\'\')[^\']*)*)(?:\'))//
214
215nonstring : /[^;\'"]+/
216
9644fab3 217statement_body : string | nonstring
9bf756df 218
9644fab3 219insert : /insert/i statement_body(s?) "$delimiter"
13aec984 220
86318717 221delimiter : /delimiter/i /[\S]+/
07d6e5f7 222 { $delimiter = $item[2] }
86318717 223
224empty_statement : "$delimiter"
225
226alter : ALTER TABLE table_name alter_specification(s /,/) "$delimiter"
13aec984 227 {
228 my $table_name = $item{'table_name'};
229 die "Cannot ALTER table '$table_name'; it does not exist"
230 unless $tables{ $table_name };
ea93df61 231 for my $definition ( @{ $item[4] } ) {
13aec984 232 $definition->{'extra'}->{'alter'} = 1;
233 push @{ $tables{ $table_name }{'constraints'} }, $definition;
234 }
235 }
236
237alter_specification : ADD foreign_key_def
238 { $return = $item[2] }
239
86318717 240create : CREATE /database/i WORD "$delimiter"
c5dabd71 241 { @table_comments = () }
dcb4fa06 242
86318717 243create : CREATE TEMPORARY(?) TABLE opt_if_not_exists(?) table_name '(' create_definition(s /,/) /(,\s*)?\)/ table_option(s?) "$delimiter"
ea93df61 244 {
d529894e 245 my $table_name = $item{'table_name'};
22529e36 246 die "There is more than one definition for $table_name"
247 if ($tables{$table_name});
248
d529894e 249 $tables{ $table_name }{'order'} = ++$table_order;
250 $tables{ $table_name }{'table_name'} = $table_name;
251
734dfc91 252 if ( @table_comments ) {
253 $tables{ $table_name }{'comments'} = [ @table_comments ];
254 @table_comments = ();
255 }
256
61745327 257 my $i = 1;
40c1ade1 258 for my $definition ( @{ $item[7] } ) {
f2cf1734 259 if ( $definition->{'supertype'} eq 'field' ) {
d529894e 260 my $field_name = $definition->{'name'};
ea93df61 261 $tables{ $table_name }{'fields'}{ $field_name } =
d529894e 262 { %$definition, order => $i };
263 $i++;
ea93df61 264
d529894e 265 if ( $definition->{'is_primary_key'} ) {
f2cf1734 266 push @{ $tables{ $table_name }{'constraints'} },
d529894e 267 {
268 type => 'primary_key',
269 fields => [ $field_name ],
16dc9970 270 }
d529894e 271 ;
272 }
dd2ef5ae 273 }
f2cf1734 274 elsif ( $definition->{'supertype'} eq 'constraint' ) {
f2cf1734 275 push @{ $tables{ $table_name }{'constraints'} }, $definition;
40c1ade1 276 }
f2cf1734 277 elsif ( $definition->{'supertype'} eq 'index' ) {
734dfc91 278 push @{ $tables{ $table_name }{'indices'} }, $definition;
dd2ef5ae 279 }
d529894e 280 }
dd2ef5ae 281
02a21f1a 282 if ( my @options = @{ $item{'table_option(s?)'} } ) {
35843e6b 283 for my $option ( @options ) {
284 my ( $key, $value ) = each %$option;
285 if ( $key eq 'comment' ) {
286 push @{ $tables{ $table_name }{'comments'} }, $value;
287 }
288 else {
289 push @{ $tables{ $table_name }{'table_options'} }, $option;
290 }
291 }
d529894e 292 }
58a88238 293
294 1;
d529894e 295 }
dd2ef5ae 296
40c1ade1 297opt_if_not_exists : /if not exists/i
298
86318717 299create : CREATE UNIQUE(?) /(index|key)/i index_name /on/i table_name '(' field_name(s /,/) ')' "$delimiter"
d529894e 300 {
734dfc91 301 @table_comments = ();
d529894e 302 push @{ $tables{ $item{'table_name'} }{'indices'} },
303 {
304 name => $item[4],
041e659f 305 type => $item[2][0] ? 'unique' : 'normal',
d529894e 306 fields => $item[8],
dd2ef5ae 307 }
d529894e 308 ;
309 }
dd2ef5ae 310
d31c185b 311create : CREATE /trigger/i NAME not_delimiter "$delimiter"
07d6e5f7 312 {
313 @table_comments = ();
314 }
d31c185b 315
316create : CREATE PROCEDURE NAME not_delimiter "$delimiter"
07d6e5f7 317 {
318 @table_comments = ();
d31c185b 319 my $func_name = $item[3];
320 my $owner = '';
321 my $sql = "$item[1] $item[2] $item[3] $item[4]";
ea93df61 322
d31c185b 323 $procedures{ $func_name }{'order'} = ++$proc_order;
324 $procedures{ $func_name }{'name'} = $func_name;
325 $procedures{ $func_name }{'owner'} = $owner;
326 $procedures{ $func_name }{'sql'} = $sql;
07d6e5f7 327 }
d31c185b 328
329PROCEDURE : /procedure/i
07d6e5f7 330 | /function/i
d31c185b 331
aa4301a7 332create : CREATE or_replace(?) create_view_option(s?) /view/i NAME /as/i view_select_statement "$delimiter"
07d6e5f7 333 {
334 @table_comments = ();
aa4301a7 335 my $view_name = $item{'NAME'};
336 my $select_sql = $item{'view_select_statement'};
337 my $options = $item{'create_view_option(s?)'};
338
339 my $sql = join(q{ },
340 grep { defined and length }
341 map { ref $_ eq 'ARRAY' ? @$_ : $_ }
342 $item{'CREATE'},
343 $item{'or_replace(?)'},
344 $options,
345 $view_name,
346 'as select',
347 join(', ',
348 map {
349 sprintf('%s%s',
350 $_->{'name'},
351 $_->{'alias'} ? ' as ' . $_->{'alias'} : ''
352 )
353 }
354 @{ $select_sql->{'columns'} || [] }
355 ),
356 ' from ',
357 join(', ',
358 map {
359 sprintf('%s%s',
360 $_->{'name'},
361 $_->{'alias'} ? ' as ' . $_->{'alias'} : ''
362 )
363 }
364 @{ $select_sql->{'from'}{'tables'} || [] }
365 ),
366 $select_sql->{'from'}{'where'}
367 ? 'where ' . $select_sql->{'from'}{'where'}
368 : ''
369 ,
370 );
ea93df61 371
d31c185b 372 # Hack to strip database from function calls in SQL
373 $sql =~ s#`\w+`\.(`\w+`\()##g;
ea93df61 374
aa4301a7 375 $views{ $view_name }{'order'} = ++$view_order;
376 $views{ $view_name }{'name'} = $view_name;
377 $views{ $view_name }{'sql'} = $sql;
378 $views{ $view_name }{'options'} = $options;
379 $views{ $view_name }{'select'} = $item{'view_select_statement'};
07d6e5f7 380 }
d31c185b 381
aa4301a7 382create_view_option : view_algorithm | view_sql_security | view_definer
22b9814a 383
aa4301a7 384or_replace : /or replace/i
385
386view_algorithm : /algorithm/i /=/ WORD
07d6e5f7 387 {
388 $return = "$item[1]=$item[3]";
389 }
d31c185b 390
aa4301a7 391view_definer : /definer=\S+/i
392
393view_sql_security : /sql \s+ security \s+ (definer|invoker)/ixs
394
395not_delimiter : /.*?(?=$delimiter)/is
396
397view_select_statement : /[(]?/ /select/i view_column_def /from/i view_table_def /[)]?/
398 {
399 $return = {
400 columns => $item{'view_column_def'},
401 from => $item{'view_table_def'},
402 };
403 }
404
405view_column_def : /(.*?)(?=\bfrom\b)/ixs
406 {
407 # split on commas not in parens,
408 # e.g., "concat_ws(\' \', first, last) as first_last"
409 my @tmp = $1 =~ /((?:[^(,]+|\(.*?\))+)/g;
410 my @cols;
411 for my $col ( @tmp ) {
412 my ( $name, $alias ) = map {
413 s/^\s+|\s+$//g;
414 s/[`]//g;
415 $_
416 } split /\s+as\s+/i, $col;
417
418 push @cols, { name => $name, alias => $alias || '' };
419 }
420
421 $return = \@cols;
422 }
423
d31c185b 424not_delimiter : /.*?(?=$delimiter)/is
425
aa4301a7 426view_table_def : not_delimiter
427 {
428 my $clause = $item[1];
429 my $where = $1 if $clause =~ s/\bwhere \s+ (.*)//ixs;
430 $clause =~ s/[)]\s*$//;
431
432 my @tables;
433 for my $tbl ( split( /\s*,\s*/, $clause ) ) {
434 my ( $name, $alias ) = split /\s+as\s+/i, $tbl;
435 push @tables, { name => $name, alias => $alias || '' };
436 }
437
438 $return = {
439 tables => \@tables,
440 where => $where || '',
441 };
442 }
443
444view_column_alias : /as/i WORD
445 { $return = $item[2] }
446
ea93df61 447create_definition : constraint
f2cf1734 448 | index
d529894e 449 | field
02a21f1a 450 | comment
d529894e 451 | <error>
452
ea93df61 453comment : /^\s*(?:#|-{2}).*\n/
454 {
734dfc91 455 my $comment = $item[1];
a82fa2cb 456 $comment =~ s/^\s*(#|--)\s*//;
734dfc91 457 $comment =~ s/\s*$//;
458 $return = $comment;
734dfc91 459 }
460
aa4301a7 461comment : m{ / \* (?! \!) .*? \* / }xs
e78d62f2 462 {
463 my $comment = $item[2];
73212389 464 $comment = substr($comment, 0, -2);
e78d62f2 465 $comment =~ s/^\s*|\s*$//g;
466 $return = $comment;
467 }
ea93df61 468
aa4301a7 469comment_like_command : m{/\*!(\d+)?}s
470
471comment_end : m{ \* / }xs
472
ea93df61 473field_comment : /^\s*(?:#|-{2}).*\n/
474 {
734dfc91 475 my $comment = $item[1];
a82fa2cb 476 $comment =~ s/^\s*(#|--)\s*//;
734dfc91 477 $comment =~ s/\s*$//;
478 $return = $comment;
479 }
d529894e 480
35843e6b 481
482field_comment2 : /comment/i /'.*?'/
483 {
484 my $comment = $item[2];
485 $comment =~ s/^'//;
486 $comment =~ s/'$//;
487 $return = $comment;
488 }
489
d529894e 490blank : /\s*/
491
100684f3 492field : field_comment(s?) field_name data_type field_qualifier(s?) field_comment2(?) reference_definition(?) on_update(?) field_comment(s?)
ea93df61 493 {
734dfc91 494 my %qualifiers = map { %$_ } @{ $item{'field_qualifier(s?)'} || [] };
d529894e 495 if ( my @type_quals = @{ $item{'data_type'}{'qualifiers'} || [] } ) {
496 $qualifiers{ $_ } = 1 for @type_quals;
497 }
498
ea93df61 499 my $null = defined $qualifiers{'not_null'}
c5dabd71 500 ? $qualifiers{'not_null'} : 1;
501 delete $qualifiers{'not_null'};
502
35843e6b 503 my @comments = ( @{ $item[1] }, @{ $item[5] }, @{ $item[8] } );
88b89793 504
ea93df61 505 $return = {
f2cf1734 506 supertype => 'field',
ea93df61 507 name => $item{'field_name'},
f2cf1734 508 data_type => $item{'data_type'}{'type'},
509 size => $item{'data_type'}{'size'},
510 list => $item{'data_type'}{'list'},
511 null => $null,
512 constraints => $item{'reference_definition(?)'},
88b89793 513 comments => [ @comments ],
d529894e 514 %qualifiers,
ea93df61 515 }
d529894e 516 }
517 | <error>
dd2ef5ae 518
d529894e 519field_qualifier : not_null
ea93df61 520 {
521 $return = {
d529894e 522 null => $item{'not_null'},
ea93df61 523 }
d529894e 524 }
16dc9970 525
d529894e 526field_qualifier : default_val
ea93df61 527 {
528 $return = {
d529894e 529 default => $item{'default_val'},
ea93df61 530 }
d529894e 531 }
16dc9970 532
d529894e 533field_qualifier : auto_inc
ea93df61 534 {
535 $return = {
d529894e 536 is_auto_inc => $item{'auto_inc'},
ea93df61 537 }
d529894e 538 }
16dc9970 539
d529894e 540field_qualifier : primary_key
ea93df61 541 {
542 $return = {
d529894e 543 is_primary_key => $item{'primary_key'},
ea93df61 544 }
d529894e 545 }
16dc9970 546
d529894e 547field_qualifier : unsigned
ea93df61 548 {
549 $return = {
d529894e 550 is_unsigned => $item{'unsigned'},
ea93df61 551 }
d529894e 552 }
16dc9970 553
ea93df61 554field_qualifier : /character set/i WORD
095b4549 555 {
556 $return = {
bd30a9a2 557 'CHARACTER SET' => $item[2],
558 }
559 }
560
561field_qualifier : /collate/i WORD
562 {
563 $return = {
564 COLLATE => $item[2],
565 }
566 }
567
568field_qualifier : /on update/i CURRENT_TIMESTAMP
569 {
570 $return = {
571 'ON UPDATE' => $item[2],
095b4549 572 }
573 }
574
bd356af8 575field_qualifier : /unique/i KEY(?)
576 {
577 $return = {
578 is_unique => 1,
579 }
580 }
581
582field_qualifier : KEY
583 {
584 $return = {
585 has_index => 1,
586 }
587 }
588
100684f3 589reference_definition : /references/i table_name parens_field_list(?) match_type(?) on_delete(?) on_update(?)
658637cd 590 {
40c1ade1 591 $return = {
658637cd 592 type => 'foreign_key',
593 reference_table => $item[2],
594 reference_fields => $item[3][0],
595 match_type => $item[4][0],
100684f3 596 on_delete => $item[5][0],
597 on_update => $item[6][0],
658637cd 598 }
599 }
600
02a21f1a 601match_type : /match full/i { 'full' }
658637cd 602 |
02a21f1a 603 /match partial/i { 'partial' }
658637cd 604
100684f3 605on_delete : /on delete/i reference_option
658637cd 606 { $item[2] }
607
ea93df61 608on_update :
0a2833d8 609 /on update/i CURRENT_TIMESTAMP
6fa97af6 610 { $item[2] }
611 |
612 /on update/i reference_option
658637cd 613 { $item[2] }
614
ea93df61 615reference_option: /restrict/i |
616 /cascade/i |
617 /set null/i |
618 /no action/i |
658637cd 619 /set default/i
ea93df61 620 { $item[1] }
658637cd 621
f2cf1734 622index : normal_index
371f5f88 623 | fulltext_index
531652d6 624 | spatial_index
58a88238 625 | <error>
d529894e 626
0d41bc9b 627table_name : NAME
d529894e 628
0d41bc9b 629field_name : NAME
d529894e 630
02a21f1a 631index_name : NAME
d529894e 632
633data_type : WORD parens_value_list(s?) type_qualifier(s?)
ea93df61 634 {
d529894e 635 my $type = $item[1];
636 my $size; # field size, applicable only to non-set fields
637 my $list; # set list, applicable only to sets (duh)
638
44fcd0b5 639 if ( uc($type) =~ /^(SET|ENUM)$/ ) {
d529894e 640 $size = undef;
641 $list = $item[2][0];
642 }
643 else {
644 $size = $item[2][0];
645 $list = [];
646 }
647
256d534a 648
ea93df61 649 $return = {
d529894e 650 type => $type,
651 size => $size,
652 list => $list,
653 qualifiers => $item[3],
ea93df61 654 }
d529894e 655 }
16dc9970 656
658637cd 657parens_field_list : '(' field_name(s /,/) ')'
658 { $item[2] }
659
d529894e 660parens_value_list : '(' VALUE(s /,/) ')'
661 { $item[2] }
16dc9970 662
d529894e 663type_qualifier : /(BINARY|UNSIGNED|ZEROFILL)/i
664 { lc $item[1] }
16dc9970 665
d529894e 666field_type : WORD
16dc9970 667
d529894e 668create_index : /create/i /index/i
dd2ef5ae 669
ea93df61 670not_null : /not/i /null/i
e78d62f2 671 { $return = 0 }
672 |
673 /null/i
674 { $return = 1 }
16dc9970 675
d529894e 676unsigned : /unsigned/i { $return = 0 }
16dc9970 677
ea93df61 678default_val :
0a2833d8 679 /default/i CURRENT_TIMESTAMP
6fa97af6 680 {
0a2833d8 681 $return = $item[2];
6fa97af6 682 }
683 |
bc9932bf 684 /default/i string
685 {
686 $item[2] =~ s/^\s*'|'\s*$//g or $item[2] =~ s/^\s*"|"\s*$//g;
687 $return = $item[2];
688 }
689 |
690 /default/i bit
691 {
692 $item[2] =~ s/b['"]([01]+)['"]/$1/g;
693 $return = $item[2];
694 }
695 |
34248db8 696 /default/i /[\w\d:.-]+/
09fa21a6 697 {
d529894e 698 $return = $item[2];
699 }
16dc9970 700
d529894e 701auto_inc : /auto_increment/i { 1 }
16dc9970 702
d529894e 703primary_key : /primary/i /key/i { 1 }
16dc9970 704
f2cf1734 705constraint : primary_key_def
706 | unique_key_def
707 | foreign_key_def
708 | <error>
709
02a21f1a 710foreign_key_def : foreign_key_def_begin parens_field_list reference_definition
40c1ade1 711 {
712 $return = {
f2cf1734 713 supertype => 'constraint',
40c1ade1 714 type => 'foreign_key',
02a21f1a 715 name => $item[1],
09fa21a6 716 fields => $item[2],
40c1ade1 717 %{ $item{'reference_definition'} },
718 }
719 }
720
e78d62f2 721foreign_key_def_begin : /constraint/i /foreign key/i WORD
722 { $return = $item[3] }
723 |
724 /constraint/i NAME /foreign key/i
725 { $return = $item[2] }
726 |
727 /constraint/i /foreign key/i
02a21f1a 728 { $return = '' }
729 |
e78d62f2 730 /foreign key/i WORD
02a21f1a 731 { $return = $item[2] }
732 |
733 /foreign key/i
734 { $return = '' }
40c1ade1 735
1687dad4 736primary_key_def : primary_key index_type(?) '(' name_with_opt_paren(s /,/) ')' index_type(?)
ea93df61 737 {
738 $return = {
f2cf1734 739 supertype => 'constraint',
f2cf1734 740 type => 'primary_key',
1687dad4 741 fields => $item[4],
742 options => $item[2][0] || $item[6][0],
743 };
744 }
745 # In theory, and according to the doc, names should not be allowed here, but
746 # MySQL accept (and ignores) them, so we are not going to be less :)
747 | primary_key index_name_not_using(?) '(' name_with_opt_paren(s /,/) ')' index_type(?)
748 {
749 $return = {
750 supertype => 'constraint',
751 type => 'primary_key',
752 fields => $item[4],
753 options => $item[6][0],
58a88238 754 };
d529894e 755 }
16dc9970 756
62a64f6c 757unique_key_def : UNIQUE KEY(?) index_name_not_using(?) index_type(?) '(' name_with_opt_paren(s /,/) ')' index_type(?)
ea93df61 758 {
759 $return = {
f2cf1734 760 supertype => 'constraint',
62a64f6c 761 name => $item[3][0],
f2cf1734 762 type => 'unique',
62a64f6c 763 fields => $item[6],
764 options => $item[4][0] || $item[8][0],
ea93df61 765 }
d529894e 766 }
16dc9970 767
62a64f6c 768normal_index : KEY index_name_not_using(?) index_type(?) '(' name_with_opt_paren(s /,/) ')' index_type(?)
ea93df61 769 {
770 $return = {
f2cf1734 771 supertype => 'index',
772 type => 'normal',
fd11d3c5 773 name => $item[2][0],
774 fields => $item[5],
62a64f6c 775 options => $item[3][0] || $item[7][0],
776 }
d529894e 777 }
16dc9970 778
fd11d3c5 779index_name_not_using : QUOTED_NAME
62a64f6c 780 | /(\b(?!using)\w+\b)/ { $return = ($1 =~ /^using/i) ? undef : $1 }
fd11d3c5 781
782index_type : /using (btree|hash|rtree)/i { $return = uc $1 }
783
f2cf1734 784fulltext_index : /fulltext/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
ea93df61 785 {
786 $return = {
f2cf1734 787 supertype => 'index',
788 type => 'fulltext',
789 name => $item{'index_name(?)'}[0],
790 fields => $item[5],
ea93df61 791 }
371f5f88 792 }
793
531652d6 794spatial_index : /spatial/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
ea93df61 795 {
796 $return = {
531652d6 797 supertype => 'index',
798 type => 'spatial',
799 name => $item{'index_name(?)'}[0],
800 fields => $item[5],
ea93df61 801 }
531652d6 802 }
803
d529894e 804name_with_opt_paren : NAME parens_value_list(s?)
805 { $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
16dc9970 806
041e659f 807UNIQUE : /unique/i
16dc9970 808
f2cf1734 809KEY : /key/i | /index/i
16dc9970 810
19c5bc53 811table_option : /comment/i /=/ /'.*?'/
35843e6b 812 {
813 my $comment = $item[3];
814 $comment =~ s/^'//;
815 $comment =~ s/'$//;
816 $return = { comment => $comment };
817 }
bb4c66d1 818 | /(default )?(charset|character set)/i /\s*=?\s*/ WORD
ea93df61 819 {
bd30a9a2 820 $return = { 'CHARACTER SET' => $item[3] };
d529894e 821 }
a7f49dfb 822 | /collate/i WORD
823 {
824 $return = { 'COLLATE' => $item[2] }
825 }
9a96648f 826 | /union/i /\s*=\s*/ '(' table_name(s /,/) ')'
ea93df61 827 {
9a96648f 828 $return = { $item[1] => $item[4] };
829 }
47666977 830 | WORD /\s*=\s*/ MAYBE_QUOTED_WORD
831 {
19c5bc53 832 $return = { $item[1] => $item[3] };
833 }
47666977 834
835MAYBE_QUOTED_WORD: /\w+/
836 | /'(\w+)'/
837 { $return = $1 }
838 | /"(\w+)"/
839 { $return = $1 }
840
bd30a9a2 841default : /default/i
16dc9970 842
13aec984 843ADD : /add/i
844
845ALTER : /alter/i
846
40c1ade1 847CREATE : /create/i
848
849TEMPORARY : /temporary/i
850
851TABLE : /table/i
852
d529894e 853WORD : /\w+/
16dc9970 854
d529894e 855DIGITS : /\d+/
16dc9970 856
d529894e 857COMMA : ','
16dc9970 858
a7f49dfb 859BACKTICK : '`'
860
f1fe509d 861DOUBLE_QUOTE: '"'
862
fd11d3c5 863QUOTED_NAME : BACKTICK /[^`]+/ BACKTICK
d529894e 864 { $item[2] }
f1fe509d 865 | DOUBLE_QUOTE /[^"]+/ DOUBLE_QUOTE
866 { $item[2] }
16dc9970 867
fd11d3c5 868NAME: QUOTED_NAME
ea93df61 869 | /\w+/
fd11d3c5 870
871VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
d529894e 872 { $item[1] }
ea93df61 873 | /'.*?'/
874 {
875 # remove leading/trailing quotes
f2cf1734 876 my $val = $item[1];
877 $val =~ s/^['"]|['"]$//g;
878 $return = $val;
879 }
d529894e 880 | /NULL/
881 { 'NULL' }
16dc9970 882
0a2833d8 883# always a scalar-ref, so that it is treated as a function and not quoted by consumers
884CURRENT_TIMESTAMP :
885 /current_timestamp(\(\))?/i { \'CURRENT_TIMESTAMP' }
886 | /now\(\)/i { \'CURRENT_TIMESTAMP' }
ea93df61 887
9bf756df 888END_OF_GRAMMAR
16dc9970 889
d529894e 890sub parse {
70944bc5 891 my ( $translator, $data ) = @_;
bdf60588 892
893 # Enable warnings within the Parse::RecDescent module.
f9a2a1d9 894 # Make sure the parser dies when it encounters an error
895 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS;
896 # Enable warnings. This will warn on unused rules &c.
897 local $::RD_WARN = 1 unless defined $::RD_WARN;
898 # Give out hints to help fix problems.
899 local $::RD_HINT = 1 unless defined $::RD_HINT;
e099bee9 900 local $::RD_TRACE = $translator->trace ? 1 : undef;
901 local $DEBUG = $translator->debug;
d529894e 902
bdf60588 903 my $parser = ddl_parser_instance('MySQL');
ea93df61 904
f1fe509d 905 # Preprocess for MySQL-specific and not-before-version comments
906 # from mysqldump
907 my $parser_version = parse_mysql_version(
908 $translator->parser_args->{mysql_parser_version}, 'mysql'
909 ) || DEFAULT_PARSER_VERSION;
910
ea93df61 911 while ( $data =~
912 s#/\*!(\d{5})?(.*?)\*/#($1 && $1 > $parser_version ? '' : $2)#es
f1fe509d 913 ) {
914 # do nothing; is there a better way to write this? -- ky
915 }
d529894e 916
917 my $result = $parser->startrule($data);
40c1ade1 918 return $translator->error( "Parse failed." ) unless defined $result;
13aec984 919 warn "Parse result:".Dumper( $result ) if $DEBUG;
8ccdeb42 920
70944bc5 921 my $schema = $translator->schema;
13aec984 922 $schema->name($result->{'database_name'}) if $result->{'database_name'};
923
ea93df61 924 my @tables = sort {
925 $result->{'tables'}{ $a }{'order'}
926 <=>
13aec984 927 $result->{'tables'}{ $b }{'order'}
928 } keys %{ $result->{'tables'} };
034ecdec 929
930 for my $table_name ( @tables ) {
13aec984 931 my $tdata = $result->{tables}{ $table_name };
ea93df61 932 my $table = $schema->add_table(
8ccdeb42 933 name => $tdata->{'table_name'},
40c1ade1 934 ) or die $schema->error;
8ccdeb42 935
734dfc91 936 $table->comments( $tdata->{'comments'} );
f2cf1734 937
ea93df61 938 my @fields = sort {
939 $tdata->{'fields'}->{$a}->{'order'}
8ccdeb42 940 <=>
941 $tdata->{'fields'}->{$b}->{'order'}
942 } keys %{ $tdata->{'fields'} };
943
944 for my $fname ( @fields ) {
945 my $fdata = $tdata->{'fields'}{ $fname };
946 my $field = $table->add_field(
947 name => $fdata->{'name'},
948 data_type => $fdata->{'data_type'},
949 size => $fdata->{'size'},
950 default_value => $fdata->{'default'},
951 is_auto_increment => $fdata->{'is_auto_inc'},
952 is_nullable => $fdata->{'null'},
88b89793 953 comments => $fdata->{'comments'},
40c1ade1 954 ) or die $table->error;
f2cf1734 955
956 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
957
bd30a9a2 958 for my $qual ( qw[ binary unsigned zerofill list collate ],
07d6e5f7 959 'character set', 'on update' ) {
f2cf1734 960 if ( my $val = $fdata->{ $qual } || $fdata->{ uc $qual } ) {
961 next if ref $val eq 'ARRAY' && !@$val;
962 $field->extra( $qual, $val );
963 }
964 }
965
bd356af8 966 if ( $fdata->{'has_index'} ) {
967 $table->add_index(
968 name => '',
969 type => 'NORMAL',
970 fields => $fdata->{'name'},
971 ) or die $table->error;
972 }
973
974 if ( $fdata->{'is_unique'} ) {
975 $table->add_constraint(
976 name => '',
977 type => 'UNIQUE',
978 fields => $fdata->{'name'},
979 ) or die $table->error;
980 }
981
f2cf1734 982 for my $cdata ( @{ $fdata->{'constraints'} } ) {
983 next unless $cdata->{'type'} eq 'foreign_key';
984 $cdata->{'fields'} ||= [ $field->name ];
985 push @{ $tdata->{'constraints'} }, $cdata;
986 }
07d6e5f7 987
f2cf1734 988 }
989
990 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
991 my $index = $table->add_index(
992 name => $idata->{'name'},
993 type => uc $idata->{'type'},
994 fields => $idata->{'fields'},
995 ) or die $table->error;
996 }
997
02a21f1a 998 if ( my @options = @{ $tdata->{'table_options'} || [] } ) {
f7f81963 999 my @cleaned_options;
f1fe509d 1000 my @ignore_opts = $translator->parser_args->{'ignore_opts'}
1001 ? split( /,/, $translator->parser_args->{'ignore_opts'} )
1002 : ();
f7f81963 1003 if (@ignore_opts) {
1004 my $ignores = { map { $_ => 1 } @ignore_opts };
1005 foreach my $option (@options) {
1006 # make sure the option isn't in ignore list
1007 my ($option_key) = keys %$option;
f1fe509d 1008 if ( !exists $ignores->{$option_key} ) {
1009 push @cleaned_options, $option;
1010 }
f7f81963 1011 }
1012 } else {
1013 @cleaned_options = @options;
1014 }
1015 $table->options( \@cleaned_options ) or die $table->error;
02a21f1a 1016 }
1017
f2cf1734 1018 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
1019 my $constraint = $table->add_constraint(
1020 name => $cdata->{'name'},
1021 type => $cdata->{'type'},
1022 fields => $cdata->{'fields'},
1023 reference_table => $cdata->{'reference_table'},
1024 reference_fields => $cdata->{'reference_fields'},
1025 match_type => $cdata->{'match_type'} || '',
ea93df61 1026 on_delete => $cdata->{'on_delete'}
f1fe509d 1027 || $cdata->{'on_delete_do'},
ea93df61 1028 on_update => $cdata->{'on_update'}
f1fe509d 1029 || $cdata->{'on_update_do'},
f2cf1734 1030 ) or die $table->error;
8ccdeb42 1031 }
07d6e5f7 1032
ea93df61 1033 # After the constrains and PK/idxs have been created,
f1fe509d 1034 # we normalize fields
07d6e5f7 1035 normalize_field($_) for $table->get_fields;
8ccdeb42 1036 }
ea93df61 1037
1038 my @procedures = sort {
1039 $result->{procedures}->{ $a }->{'order'}
1040 <=>
f1fe509d 1041 $result->{procedures}->{ $b }->{'order'}
d31c185b 1042 } keys %{ $result->{procedures} };
f1fe509d 1043
1044 for my $proc_name ( @procedures ) {
07d6e5f7 1045 $schema->add_procedure(
1046 name => $proc_name,
1047 owner => $result->{procedures}->{$proc_name}->{owner},
1048 sql => $result->{procedures}->{$proc_name}->{sql},
1049 );
d31c185b 1050 }
aa4301a7 1051
ea93df61 1052 my @views = sort {
1053 $result->{views}->{ $a }->{'order'}
1054 <=>
f1fe509d 1055 $result->{views}->{ $b }->{'order'}
d31c185b 1056 } keys %{ $result->{views} };
f1fe509d 1057
3ebe2ce5 1058 for my $view_name ( @views ) {
aa4301a7 1059 my $view = $result->{'views'}{ $view_name };
1060 my @flds = map { $_->{'alias'} || $_->{'name'} }
1061 @{ $view->{'select'}{'columns'} || [] };
e6c5fb6e 1062 my @from = map { $_->{'alias'} || $_->{'name'} }
1063 @{ $view->{'from'}{'tables'} || [] };
aa4301a7 1064
07d6e5f7 1065 $schema->add_view(
aa4301a7 1066 name => $view_name,
1067 sql => $view->{'sql'},
1068 order => $view->{'order'},
1069 fields => \@flds,
e6c5fb6e 1070 tables => \@from,
1071 options => $view->{'options'}
07d6e5f7 1072 );
d31c185b 1073 }
8ccdeb42 1074
f62bd16c 1075 return 1;
d529894e 1076}
1077
ea93df61 1078# Takes a field, and returns
07d6e5f7 1079sub normalize_field {
1080 my ($field) = @_;
936e626b 1081 my ($size, $type, $list, $unsigned, $changed);
ea93df61 1082
07d6e5f7 1083 $size = $field->size;
1084 $type = $field->data_type;
1085 $list = $field->extra->{list} || [];
936e626b 1086 $unsigned = defined($field->extra->{unsigned});
07d6e5f7 1087
1088 if ( !ref $size && $size eq 0 ) {
1089 if ( lc $type eq 'tinyint' ) {
936e626b 1090 $changed = $size != 4 - $unsigned;
1091 $size = 4 - $unsigned;
07d6e5f7 1092 }
1093 elsif ( lc $type eq 'smallint' ) {
936e626b 1094 $changed = $size != 6 - $unsigned;
1095 $size = 6 - $unsigned;
07d6e5f7 1096 }
1097 elsif ( lc $type eq 'mediumint' ) {
936e626b 1098 $changed = $size != 9 - $unsigned;
1099 $size = 9 - $unsigned;
07d6e5f7 1100 }
1101 elsif ( $type =~ /^int(eger)?$/i ) {
936e626b 1102 $changed = $size != 11 - $unsigned || $type ne 'int';
07d6e5f7 1103 $type = 'int';
936e626b 1104 $size = 11 - $unsigned;
07d6e5f7 1105 }
1106 elsif ( lc $type eq 'bigint' ) {
1107 $changed = $size != 20;
1108 $size = 20;
1109 }
1110 elsif ( lc $type =~ /(float|double|decimal|numeric|real|fixed|dec)/ ) {
1111 my $old_size = (ref $size || '') eq 'ARRAY' ? $size : [];
ea93df61 1112 $changed = @$old_size != 2
1113 || $old_size->[0] != 8
f1fe509d 1114 || $old_size->[1] != 2;
1115 $size = [8,2];
07d6e5f7 1116 }
1117 }
1118
1119 if ( $type =~ /^tiny(text|blob)$/i ) {
1120 $changed = $size != 255;
1121 $size = 255;
1122 }
1123 elsif ( $type =~ /^(blob|text)$/i ) {
1124 $changed = $size != 65_535;
1125 $size = 65_535;
1126 }
1127 elsif ( $type =~ /^medium(blob|text)$/i ) {
1128 $changed = $size != 16_777_215;
1129 $size = 16_777_215;
1130 }
1131 elsif ( $type =~ /^long(blob|text)$/i ) {
1132 $changed = $size != 4_294_967_295;
1133 $size = 4_294_967_295;
1134 }
f1fe509d 1135
9ab59f87 1136 if ( $field->data_type =~ /(set|enum)/i && !$field->size ) {
1137 my %extra = $field->extra;
1138 my $longest = 0;
1139 for my $len ( map { length } @{ $extra{'list'} || [] } ) {
1140 $longest = $len if $len > $longest;
1141 }
1142 $changed = 1;
1143 $size = $longest if $longest;
1144 }
1145
1146
f1fe509d 1147 if ( $changed ) {
1148 # We only want to clone the field, not *everything*
1149 {
1150 local $field->{table} = undef;
1151 $field->parsed_field( dclone( $field ) );
1152 $field->parsed_field->{table} = $field->table;
1153 }
1154 $field->size( $size );
1155 $field->data_type( $type );
1156 $field->sql_data_type( $type_mapping{ lc $type } )
1157 if exists $type_mapping{ lc $type };
1158 $field->extra->{list} = $list if @$list;
07d6e5f7 1159 }
1160}
1161
d529894e 11621;
1163
034ecdec 1164# -------------------------------------------------------------------
d529894e 1165# Where man is not nature is barren.
1166# William Blake
034ecdec 1167# -------------------------------------------------------------------
16dc9970 1168
d529894e 1169=pod
16dc9970 1170
1171=head1 AUTHOR
1172
19c5bc53 1173Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
8ccdeb42 1174Chris Mungall E<lt>cjm@fruitfly.orgE<gt>.
16dc9970 1175
1176=head1 SEE ALSO
1177
19c5bc53 1178Parse::RecDescent, SQL::Translator::Schema.
16dc9970 1179
1180=cut