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