aliasing ugly tablename_fieldname ref methods where possible (ie create them
[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / MySQL.pm
CommitLineData
16dc9970 1package SQL::Translator::Parser::MySQL;
2
49e1eb70 3# -------------------------------------------------------------------
f62bd16c 4# $Id: MySQL.pm,v 1.25 2003-06-11 03:59:49 kycl4rk Exp $
49e1eb70 5# -------------------------------------------------------------------
abfa405a 6# Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>,
7# darren chamberlain <darren@cpan.org>,
8# Chris Mungall <cjm@fruitfly.org>
077ebf34 9#
10# This program is free software; you can redistribute it and/or
11# modify it under the terms of the GNU General Public License as
12# published by the Free Software Foundation; version 2.
13#
14# This program is distributed in the hope that it will be useful, but
15# WITHOUT ANY WARRANTY; without even the implied warranty of
16# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17# General Public License for more details.
18#
19# You should have received a copy of the GNU General Public License
20# along with this program; if not, write to the Free Software
21# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
22# 02111-1307 USA
23# -------------------------------------------------------------------
16dc9970 24
d529894e 25=head1 NAME
26
27SQL::Translator::Parser::MySQL - parser for MySQL
28
29=head1 SYNOPSIS
30
31 use SQL::Translator;
32 use SQL::Translator::Parser::MySQL;
33
34 my $translator = SQL::Translator->new;
35 $translator->parser("SQL::Translator::Parser::MySQL");
36
37=head1 DESCRIPTION
38
39The grammar is influenced heavily by Tim Bunce's "mysql2ora" grammar.
40
629b76f9 41Here's the word from the MySQL site
42(http://www.mysql.com/doc/en/CREATE_TABLE.html):
43
44 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
45 [table_options] [select_statement]
46
47 or
48
49 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;
50
51 create_definition:
52 col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
53 [PRIMARY KEY] [reference_definition]
54 or PRIMARY KEY (index_col_name,...)
55 or KEY [index_name] (index_col_name,...)
56 or INDEX [index_name] (index_col_name,...)
57 or UNIQUE [INDEX] [index_name] (index_col_name,...)
58 or FULLTEXT [INDEX] [index_name] (index_col_name,...)
59 or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
60 [reference_definition]
61 or CHECK (expr)
62
63 type:
64 TINYINT[(length)] [UNSIGNED] [ZEROFILL]
65 or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
66 or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
67 or INT[(length)] [UNSIGNED] [ZEROFILL]
68 or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
69 or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
70 or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
71 or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
72 or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
73 or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
74 or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
75 or CHAR(length) [BINARY]
76 or VARCHAR(length) [BINARY]
77 or DATE
78 or TIME
79 or TIMESTAMP
80 or DATETIME
81 or TINYBLOB
82 or BLOB
83 or MEDIUMBLOB
84 or LONGBLOB
85 or TINYTEXT
86 or TEXT
87 or MEDIUMTEXT
88 or LONGTEXT
89 or ENUM(value1,value2,value3,...)
90 or SET(value1,value2,value3,...)
91
92 index_col_name:
93 col_name [(length)]
94
95 reference_definition:
96 REFERENCES tbl_name [(index_col_name,...)]
97 [MATCH FULL | MATCH PARTIAL]
98 [ON DELETE reference_option]
99 [ON UPDATE reference_option]
100
101 reference_option:
102 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
103
104 table_options:
105 TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
106 or AUTO_INCREMENT = #
107 or AVG_ROW_LENGTH = #
108 or CHECKSUM = {0 | 1}
109 or COMMENT = "string"
110 or MAX_ROWS = #
111 or MIN_ROWS = #
112 or PACK_KEYS = {0 | 1 | DEFAULT}
113 or PASSWORD = "string"
114 or DELAY_KEY_WRITE = {0 | 1}
115 or ROW_FORMAT= { default | dynamic | fixed | compressed }
116 or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
117 or UNION = (table_name,[table_name...])
118 or INSERT_METHOD= {NO | FIRST | LAST }
119 or DATA DIRECTORY="absolute path to directory"
120 or INDEX DIRECTORY="absolute path to directory"
121
d529894e 122=cut
123
16dc9970 124use strict;
d529894e 125use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
f62bd16c 126$VERSION = sprintf "%d.%02d", q$Revision: 1.25 $ =~ /(\d+)\.(\d+)/;
8d0f3086 127$DEBUG = 0 unless defined $DEBUG;
077ebf34 128
d529894e 129use Data::Dumper;
077ebf34 130use Parse::RecDescent;
131use Exporter;
132use base qw(Exporter);
133
134@EXPORT_OK = qw(parse);
135
d529894e 136# Enable warnings within the Parse::RecDescent module.
137$::RD_ERRORS = 1; # Make sure the parser dies when it encounters an error
138$::RD_WARN = 1; # Enable warnings. This will warn on unused rules &c.
139$::RD_HINT = 1; # Give out hints to help fix problems.
140
d529894e 141$GRAMMAR = q!
142
8ccdeb42 143{
144 our ( %tables, $table_order );
145}
d529894e 146
629b76f9 147#
148# The "eofile" rule makes the parser fail if any "statement" rule
149# fails. Otherwise, the first successful match by a "statement"
150# won't cause the failure needed to know that the parse, as a whole,
151# failed. -ky
152#
153startrule : statement(s) eofile { \%tables }
154
155eofile : /^\Z/
d529894e 156
157statement : comment
dcb4fa06 158 | use
61745327 159 | drop
d529894e 160 | create
161 | <error>
162
dcb4fa06 163use : /use/i WORD ';'
164
61745327 165drop : /drop/i WORD(s) ';'
166
dcb4fa06 167create : CREATE /database/i WORD ';'
168
40c1ade1 169create : CREATE TEMPORARY(?) TABLE opt_if_not_exists(?) table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
d529894e 170 {
171 my $table_name = $item{'table_name'};
172 $tables{ $table_name }{'order'} = ++$table_order;
173 $tables{ $table_name }{'table_name'} = $table_name;
174
61745327 175 my $i = 1;
40c1ade1 176 for my $definition ( @{ $item[7] } ) {
f2cf1734 177 if ( $definition->{'supertype'} eq 'field' ) {
178
d529894e 179 my $field_name = $definition->{'name'};
180 $tables{ $table_name }{'fields'}{ $field_name } =
181 { %$definition, order => $i };
182 $i++;
183
184 if ( $definition->{'is_primary_key'} ) {
f2cf1734 185 push @{ $tables{ $table_name }{'constraints'} },
d529894e 186 {
187 type => 'primary_key',
188 fields => [ $field_name ],
16dc9970 189 }
d529894e 190 ;
191 }
dd2ef5ae 192 }
f2cf1734 193 elsif ( $definition->{'supertype'} eq 'constraint' ) {
194 # prob get rid of this?
195# for my $field ( @{ $definition->{'fields'} } ) {
196# push @{
197# $tables{$table_name}{'fields'}{$field}{'constraints'}
198# },
199# $definition;
200# }
201
202 # this should be the only one needed
203 push @{ $tables{ $table_name }{'constraints'} }, $definition;
40c1ade1 204 }
f2cf1734 205 elsif ( $definition->{'supertype'} eq 'index' ) {
d529894e 206 push @{ $tables{ $table_name }{'indices'} },
207 $definition;
dd2ef5ae 208 }
d529894e 209 }
dd2ef5ae 210
58a88238 211 for my $opt ( @{ $item{'table_option(s?)'} } ) {
d529894e 212 if ( my ( $key, $val ) = each %$opt ) {
213 $tables{ $table_name }{'table_options'}{ $key } = $val;
dd2ef5ae 214 }
d529894e 215 }
58a88238 216
217 1;
d529894e 218 }
dd2ef5ae 219
40c1ade1 220opt_if_not_exists : /if not exists/i
221
f2cf1734 222create : CREATE UNIQUE(?) /(index|key)/i index_name /on/i table_name '(' field_name(s /,/) ')' ';'
d529894e 223 {
224 push @{ $tables{ $item{'table_name'} }{'indices'} },
225 {
226 name => $item[4],
227 type => $item[2] ? 'unique' : 'normal',
228 fields => $item[8],
dd2ef5ae 229 }
d529894e 230 ;
231 }
dd2ef5ae 232
f2cf1734 233create_definition : constraint
234 | index
d529894e 235 | field
236 | <error>
237
238comment : /^\s*(?:#|-{2}).*\n/
239
240blank : /\s*/
241
658637cd 242field : field_name data_type field_qualifier(s?) reference_definition(?)
d529894e 243 {
58a88238 244 my %qualifiers = map { %$_ } @{ $item{'field_qualifier(s?)'} || [] };
d529894e 245 my $null = defined $item{'not_null'} ? $item{'not_null'} : 1;
246 delete $qualifiers{'not_null'};
247 if ( my @type_quals = @{ $item{'data_type'}{'qualifiers'} || [] } ) {
248 $qualifiers{ $_ } = 1 for @type_quals;
249 }
250
251 $return = {
f2cf1734 252 supertype => 'field',
253 name => $item{'field_name'},
254 data_type => $item{'data_type'}{'type'},
255 size => $item{'data_type'}{'size'},
256 list => $item{'data_type'}{'list'},
257 null => $null,
258 constraints => $item{'reference_definition(?)'},
d529894e 259 %qualifiers,
260 }
261 }
262 | <error>
dd2ef5ae 263
d529894e 264field_qualifier : not_null
265 {
266 $return = {
267 null => $item{'not_null'},
268 }
269 }
16dc9970 270
d529894e 271field_qualifier : default_val
272 {
273 $return = {
274 default => $item{'default_val'},
275 }
276 }
16dc9970 277
d529894e 278field_qualifier : auto_inc
279 {
280 $return = {
281 is_auto_inc => $item{'auto_inc'},
282 }
283 }
16dc9970 284
d529894e 285field_qualifier : primary_key
286 {
287 $return = {
288 is_primary_key => $item{'primary_key'},
289 }
290 }
16dc9970 291
d529894e 292field_qualifier : unsigned
293 {
294 $return = {
295 is_unsigned => $item{'unsigned'},
296 }
297 }
16dc9970 298
658637cd 299reference_definition : /references/i table_name parens_field_list(?) match_type(?) on_delete_do(?) on_update_do(?)
300 {
40c1ade1 301 $return = {
658637cd 302 type => 'foreign_key',
303 reference_table => $item[2],
304 reference_fields => $item[3][0],
305 match_type => $item[4][0],
306 on_delete_do => $item[5][0],
307 on_update_do => $item[6][0],
308 }
309 }
310
311match_type : /match full/i { 'match_full' }
312 |
313 /match partial/i { 'match_partial' }
314
315on_delete_do : /on delete/i reference_option
316 { $item[2] }
317
318on_update_do : /on update/i reference_option
319 { $item[2] }
320
321reference_option: /restrict/i |
322 /cascade/i |
323 /set null/i |
324 /no action/i |
325 /set default/i
326 { $item[1] }
327
f2cf1734 328index : normal_index
371f5f88 329 | fulltext_index
58a88238 330 | <error>
d529894e 331
332table_name : WORD
333
334field_name : WORD
335
336index_name : WORD
337
338data_type : WORD parens_value_list(s?) type_qualifier(s?)
339 {
340 my $type = $item[1];
341 my $size; # field size, applicable only to non-set fields
342 my $list; # set list, applicable only to sets (duh)
343
44fcd0b5 344 if ( uc($type) =~ /^(SET|ENUM)$/ ) {
d529894e 345 $size = undef;
346 $list = $item[2][0];
347 }
348 else {
349 $size = $item[2][0];
350 $list = [];
351 }
352
6333c482 353 unless ( @{ $size || [] } ) {
354 if ( lc $type eq 'tinyint' ) {
355 $size = [4];
356 }
357 elsif ( lc $type eq 'smallint' ) {
358 $size = [6];
359 }
360 elsif ( lc $type eq 'mediumint' ) {
361 $size = [9];
362 }
c736c39c 363 elsif ( $type =~ /^int(eger)?$/ ) {
f2cf1734 364 $type = 'int';
6333c482 365 $size = [11];
366 }
367 elsif ( lc $type eq 'bigint' ) {
368 $size = [20];
369 }
c736c39c 370 elsif ( lc $type =~ /(float|double|decimal|numeric|real)/ ) {
6333c482 371 $size = [8,2];
372 }
373 }
374
d529894e 375 $return = {
376 type => $type,
377 size => $size,
378 list => $list,
379 qualifiers => $item[3],
380 }
381 }
16dc9970 382
658637cd 383parens_field_list : '(' field_name(s /,/) ')'
384 { $item[2] }
385
d529894e 386parens_value_list : '(' VALUE(s /,/) ')'
387 { $item[2] }
16dc9970 388
d529894e 389type_qualifier : /(BINARY|UNSIGNED|ZEROFILL)/i
390 { lc $item[1] }
16dc9970 391
d529894e 392field_type : WORD
16dc9970 393
d529894e 394create_index : /create/i /index/i
dd2ef5ae 395
d529894e 396not_null : /not/i /null/i { $return = 0 }
16dc9970 397
d529894e 398unsigned : /unsigned/i { $return = 0 }
16dc9970 399
be019aae 400default_val : /default/i /(?:')?[\w\d:.-]*(?:')?/
d529894e 401 {
402 $item[2] =~ s/'//g;
403 $return = $item[2];
404 }
16dc9970 405
d529894e 406auto_inc : /auto_increment/i { 1 }
16dc9970 407
d529894e 408primary_key : /primary/i /key/i { 1 }
16dc9970 409
f2cf1734 410constraint : primary_key_def
411 | unique_key_def
412 | foreign_key_def
413 | <error>
414
415foreign_key_def : opt_constraint(?) /foreign key/i WORD(?) parens_field_list reference_definition
40c1ade1 416 {
417 $return = {
f2cf1734 418 supertype => 'constraint',
40c1ade1 419 type => 'foreign_key',
420 name => $item[3][0],
421 fields => $item[4],
422 %{ $item{'reference_definition'} },
423 }
424 }
425
426opt_constraint : /constraint/i WORD
427
f2cf1734 428primary_key_def : primary_key index_name(?) '(' field_name(s /,/) ')'
d529894e 429 {
f2cf1734 430 $return = {
431 supertype => 'constraint',
432 name => $item{'index_name(?)'}[0],
433 type => 'primary_key',
434 fields => $item[4],
58a88238 435 };
d529894e 436 }
16dc9970 437
f2cf1734 438unique_key_def : UNIQUE KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
d529894e 439 {
f2cf1734 440 $return = {
441 supertype => 'constraint',
442 name => $item{'index_name(?)'}[0],
443 type => 'unique',
444 fields => $item[5],
d529894e 445 }
446 }
16dc9970 447
f2cf1734 448normal_index : KEY index_name(?) '(' name_with_opt_paren(s /,/) ')'
d529894e 449 {
f2cf1734 450 $return = {
451 supertype => 'index',
452 type => 'normal',
453 name => $item{'index_name(?)'}[0],
454 fields => $item[4],
d529894e 455 }
456 }
16dc9970 457
f2cf1734 458fulltext_index : /fulltext/i KEY(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
371f5f88 459 {
f2cf1734 460 $return = {
461 supertype => 'index',
462 type => 'fulltext',
463 name => $item{'index_name(?)'}[0],
464 fields => $item[5],
371f5f88 465 }
466 }
467
d529894e 468name_with_opt_paren : NAME parens_value_list(s?)
469 { $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
16dc9970 470
f2cf1734 471UNIQUE : /unique/i { 1 }
16dc9970 472
f2cf1734 473KEY : /key/i | /index/i
16dc9970 474
44fcd0b5 475table_option : /[^\s;]*/
d529894e 476 {
477 $return = { split /=/, $item[1] }
478 }
16dc9970 479
40c1ade1 480CREATE : /create/i
481
482TEMPORARY : /temporary/i
483
484TABLE : /table/i
485
d529894e 486WORD : /\w+/
16dc9970 487
d529894e 488DIGITS : /\d+/
16dc9970 489
d529894e 490COMMA : ','
16dc9970 491
d529894e 492NAME : "`" /\w+/ "`"
493 { $item[2] }
494 | /\w+/
495 { $item[1] }
16dc9970 496
d529894e 497VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
498 { $item[1] }
f2cf1734 499 | /'.*?'/
500 {
501 # remove leading/trailing quotes
502 my $val = $item[1];
503 $val =~ s/^['"]|['"]$//g;
504 $return = $val;
505 }
d529894e 506 | /NULL/
507 { 'NULL' }
16dc9970 508
d529894e 509!;
16dc9970 510
d529894e 511# -------------------------------------------------------------------
512sub parse {
70944bc5 513 my ( $translator, $data ) = @_;
40c1ade1 514 my $parser = Parse::RecDescent->new($GRAMMAR);
077ebf34 515
e099bee9 516 local $::RD_TRACE = $translator->trace ? 1 : undef;
517 local $DEBUG = $translator->debug;
d529894e 518
519 unless (defined $parser) {
520 return $translator->error("Error instantiating Parse::RecDescent ".
521 "instance: Bad grammer");
522 }
523
524 my $result = $parser->startrule($data);
40c1ade1 525 return $translator->error( "Parse failed." ) unless defined $result;
8ccdeb42 526 warn Dumper( $result ) if $DEBUG;
527
70944bc5 528 my $schema = $translator->schema;
034ecdec 529 my @tables = sort {
530 $result->{ $a }->{'order'} <=> $result->{ $b }->{'order'}
531 } keys %{ $result };
532
533 for my $table_name ( @tables ) {
8ccdeb42 534 my $tdata = $result->{ $table_name };
535 my $table = $schema->add_table(
536 name => $tdata->{'table_name'},
40c1ade1 537 ) or die $schema->error;
8ccdeb42 538
f2cf1734 539# for my $opt ( @{ $tdata->{'table_options'} } ) {
540# if ( my ( $key, $val ) = each %$opt ) {
541# $tables->options(
542# }
543# }
544
8ccdeb42 545 my @fields = sort {
546 $tdata->{'fields'}->{$a}->{'order'}
547 <=>
548 $tdata->{'fields'}->{$b}->{'order'}
549 } keys %{ $tdata->{'fields'} };
550
551 for my $fname ( @fields ) {
552 my $fdata = $tdata->{'fields'}{ $fname };
553 my $field = $table->add_field(
554 name => $fdata->{'name'},
555 data_type => $fdata->{'data_type'},
556 size => $fdata->{'size'},
557 default_value => $fdata->{'default'},
558 is_auto_increment => $fdata->{'is_auto_inc'},
559 is_nullable => $fdata->{'null'},
40c1ade1 560 ) or die $table->error;
f2cf1734 561
562 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
563
564 for my $qual ( qw[ binary unsigned zerofill list ] ) {
565 if ( my $val = $fdata->{ $qual } || $fdata->{ uc $qual } ) {
566 next if ref $val eq 'ARRAY' && !@$val;
567 $field->extra( $qual, $val );
568 }
569 }
570
571 if ( $field->data_type =~ /(set|enum)/i && !$field->size ) {
572 my %extra = $field->extra;
573 my $longest;
574 for my $len ( map { length } @{ $extra{'list'} || [] } ) {
575 $longest = $len if $len > $longest;
576 }
577 $field->size( $longest ) if $longest;
578 }
579
580 for my $cdata ( @{ $fdata->{'constraints'} } ) {
581 next unless $cdata->{'type'} eq 'foreign_key';
582 $cdata->{'fields'} ||= [ $field->name ];
583 push @{ $tdata->{'constraints'} }, $cdata;
584 }
585 }
586
587 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
588 my $index = $table->add_index(
589 name => $idata->{'name'},
590 type => uc $idata->{'type'},
591 fields => $idata->{'fields'},
592 ) or die $table->error;
593 }
594
595 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
596 my $constraint = $table->add_constraint(
597 name => $cdata->{'name'},
598 type => $cdata->{'type'},
599 fields => $cdata->{'fields'},
600 reference_table => $cdata->{'reference_table'},
601 reference_fields => $cdata->{'reference_fields'},
602 match_type => $cdata->{'match_type'} || '',
603 on_delete => $cdata->{'on_delete_do'},
604 on_update => $cdata->{'on_update_do'},
605 ) or die $table->error;
8ccdeb42 606 }
607 }
608
f62bd16c 609 return 1;
d529894e 610}
611
6121;
613
034ecdec 614# -------------------------------------------------------------------
d529894e 615# Where man is not nature is barren.
616# William Blake
034ecdec 617# -------------------------------------------------------------------
16dc9970 618
d529894e 619=pod
16dc9970 620
621=head1 AUTHOR
622
d529894e 623Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
8ccdeb42 624Chris Mungall E<lt>cjm@fruitfly.orgE<gt>.
16dc9970 625
626=head1 SEE ALSO
627
8ccdeb42 628perl(1), Parse::RecDescent, SQL::Translator::Schema.
16dc9970 629
630=cut