1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.47 2006-06-07 16:32:11 schiffbruechige Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
29 Use via SQL::Translator:
33 my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
38 This module will produce text output of the schema suitable for MySQL.
39 There are still some issues to be worked out with syntax differences
40 between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
45 Normally the tables will be created without any explicit table type given and
46 so will use the MySQL default.
48 Any tables involved in foreign key constraints automatically get a table type
49 of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
50 attribute explicitly on the table.
52 =head2 Extra attributes.
54 The producer recognises the following extra attributes on the Schema objects.
60 Set the list of allowed values for Enum fields.
62 =item field.binary field.unsigned field.zerofill
64 Set the MySQL field options of the same name.
66 =item table.mysql_table_type
68 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
69 automatically set for tables involved in foreign key constraints if it is
70 not already set explicitly. See L<"Table Types">.
72 =item mysql_character_set
74 MySql-4.1+. Set the tables character set.
75 Run SHOW CHARACTER SET to see list.
79 MySql-4.1+. Set the tables colation order.
81 =item table.mysql_charset table.mysql_collate
83 Set the tables default charater set and collation order.
85 =item field.mysql_charset field.mysql_collate
87 Set the fields charater set and collation order.
94 use vars qw[ $VERSION $DEBUG ];
95 $VERSION = sprintf "%d.%02d", q$Revision: 1.47 $ =~ /(\d+)\.(\d+)/;
96 $DEBUG = 0 unless defined $DEBUG;
99 use SQL::Translator::Schema::Constants;
100 use SQL::Translator::Utils qw(debug header_comment);
103 # Use only lowercase for the keys (e.g. "long" and not "LONG")
109 varchar2 => 'varchar',
125 'long integer' => 'integer',
127 'datetime' => 'datetime',
131 my $translator = shift;
132 local $DEBUG = $translator->debug;
133 my $no_comments = $translator->no_comments;
134 my $add_drop_table = $translator->add_drop_table;
135 my $schema = $translator->schema;
136 my $show_warnings = $translator->show_warnings || 0;
138 debug("PKG: Beginning production\n");
141 $create .= header_comment unless ($no_comments);
142 # \todo Don't set if MySQL 3.x is set on command line
143 $create .= "SET foreign_key_checks=0;\n\n";
146 # Work out which tables need to be InnoDB to support foreign key
147 # constraints. We do this first as we need InnoDB at both ends.
149 foreach ( map { $_->get_constraints } $schema->get_tables ) {
150 foreach my $meth (qw/table reference_table/) {
151 my $table = $schema->get_table($_->$meth) || next;
152 next if $table->extra('mysql_table_type');
153 $table->extra( 'mysql_table_type' => 'InnoDB');
161 for my $table ( $schema->get_tables ) {
162 push @table_defs, create_table($table,
163 { add_drop_table => $add_drop_table,
164 show_warnings => $show_warnings,
165 no_comments => $no_comments
169 return wantarray ? @table_defs : $create . join ('', @table_defs);
174 my ($table, $options) = @_;
176 my $table_name = $table->name;
177 debug("PKG: Looking at table '$table_name'\n");
180 # Header. Should this look like what mysqldump produces?
182 my $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
183 $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $options->{add_drop_table};
184 $create .= "CREATE TABLE $table_name (\n";
190 for my $field ( $table->get_fields ) {
191 push @field_defs, create_field($field);
199 for my $index ( $table->get_indices ) {
200 push @index_defs, create_index($index);
201 $indexed_fields{ $_ } = 1 for $index->fields;
205 # Constraints -- need to handle more than just FK. -ky
208 my @constraints = $table->get_constraints;
209 for my $c ( @constraints ) {
210 push @constraint_defs, create_constraint($c, $options);
212 unless ( $indexed_fields{ ($c->fields())[0] } ) {
213 push @index_defs, "INDEX (" . ($c->fields())[0] . ")";
214 $indexed_fields{ ($c->fields())[0] } = 1;
218 $create .= join(",\n", map { " $_" }
219 @field_defs, @index_defs, @constraint_defs
226 my $table_type_defined = 0;
227 for my $t1_option_ref ( $table->options ) {
228 my($key, $value) = %{$t1_option_ref};
229 $table_type_defined = 1
230 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
231 $create .= " $key=$value";
233 my $mysql_table_type = $table->extra('mysql_table_type');
234 #my $charset = $table->extra('mysql_character_set');
235 #my $collate = $table->extra('mysql_collate');
236 #$create .= " Type=$mysql_table_type" if $mysql_table_type;
237 #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
238 #$create .= " COLLATE $collate" if $collate;
239 $create .= " Type=$mysql_table_type"
240 if $mysql_table_type && !$table_type_defined;
241 my $charset = $table->extra('mysql_charset');
242 my $collate = $table->extra('mysql_collate');
243 my $comments = $table->comments;
245 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
246 $create .= " COLLATE $collate" if $collate;
247 $create .= qq[ comment='$comments'] if $comments;
257 my $field_name = $field->name;
258 debug("PKG: Looking at field '$field_name'\n");
259 my $field_def = $field_name;
262 my $data_type = $field->data_type;
263 my @size = $field->size;
264 my %extra = $field->extra;
265 my $list = $extra{'list'} || [];
266 # \todo deal with embedded quotes
267 my $commalist = join( ', ', map { qq['$_'] } @$list );
268 my $charset = $extra{'mysql_charset'};
269 my $collate = $extra{'mysql_collate'};
272 # Oracle "number" type -- figure best MySQL type
274 if ( lc $data_type eq 'number' ) {
276 if ( scalar @size > 1 ) {
277 $data_type = 'double';
279 elsif ( $size[0] && $size[0] >= 12 ) {
280 $data_type = 'bigint';
282 elsif ( $size[0] && $size[0] <= 1 ) {
283 $data_type = 'tinyint';
290 # Convert a large Oracle varchar to "text"
292 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
296 elsif ( $data_type =~ /char/i && ! $size[0] ) {
299 elsif ( $data_type =~ /boolean/i ) {
301 $commalist = "'0','1'";
303 elsif ( exists $translate{ lc $data_type } ) {
304 $data_type = $translate{ lc $data_type };
307 @size = () if $data_type =~ /(text|blob)/i;
309 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
313 $field_def .= " $data_type";
315 if ( lc $data_type eq 'enum' ) {
316 $field_def .= '(' . $commalist . ')';
318 elsif ( defined $size[0] && $size[0] > 0 ) {
319 $field_def .= '(' . join( ', ', @size ) . ')';
323 $field_def .= " CHARACTER SET $charset" if $charset;
324 $field_def .= " COLLATE $collate" if $collate;
327 for my $qual ( qw[ binary unsigned zerofill ] ) {
328 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
329 $field_def .= " $qual";
331 for my $qual ( 'character set', 'collate', 'on update' ) {
332 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
333 $field_def .= " $qual $val";
337 $field_def .= ' NOT NULL' unless $field->is_nullable;
339 # Default? XXX Need better quoting!
340 my $default = $field->default_value;
341 if ( defined $default ) {
342 if ( uc $default eq 'NULL') {
343 $field_def .= ' DEFAULT NULL';
345 $field_def .= " DEFAULT '$default'";
349 if ( my $comments = $field->comments ) {
350 $field_def .= qq[ comment '$comments'];
354 $field_def .= " auto_increment" if $field->is_auto_increment;
364 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
366 '(' . join( ', ', $index->fields ) . ')'
371 sub create_constraint
373 my ($c, $options) = @_;
375 my @fields = $c->fields or next;
377 if ( $c->type eq PRIMARY_KEY ) {
378 return 'PRIMARY KEY (' . join(', ', @fields). ')';
380 elsif ( $c->type eq UNIQUE ) {
383 (defined $c->name ? $c->name.' ' : '').
384 '(' . join(', ', @fields). ')';
386 elsif ( $c->type eq FOREIGN_KEY ) {
388 # Make sure FK field is indexed or MySQL complains.
392 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
395 $def .= ' (' . join( ', ', @fields ) . ')';
397 $def .= ' REFERENCES ' . $c->reference_table;
399 my @rfields = map { $_ || () } $c->reference_fields;
400 unless ( @rfields ) {
401 my $rtable_name = $c->reference_table;
402 if ( my $ref_table = $c->table->schema->get_table( $rtable_name ) ) {
403 push @rfields, $ref_table->primary_key;
406 warn "Can't find reference table '$rtable_name' " .
407 "in schema\n" if $options->{show_warnings};
412 $def .= ' (' . join( ', ', @rfields ) . ')';
415 warn "FK constraint on " . $c->table->name . '.' .
416 join('', @fields) . " has no reference fields\n"
417 if $options->{show_warnings};
420 if ( $c->match_type ) {
422 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
425 if ( $c->on_delete ) {
426 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
429 if ( $c->on_update ) {
430 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
438 my ($from_field, $to_field) = @_;
440 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
441 $to_field->table->name,
443 create_field($to_field));
450 my ($new_field) = @_;
452 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
453 $new_field->table->name,
454 create_field($new_field));
462 my ($old_field) = @_;
464 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
465 $old_field->table->name,
474 # -------------------------------------------------------------------
480 SQL::Translator, http://www.mysql.com/.
484 darren chamberlain E<lt>darren@cpan.orgE<gt>,
485 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.