1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.49 2006-07-23 14:03:52 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.
95 use vars qw[ $VERSION $DEBUG ];
96 $VERSION = sprintf "%d.%02d", q$Revision: 1.49 $ =~ /(\d+)\.(\d+)/;
97 $DEBUG = 0 unless defined $DEBUG;
100 use SQL::Translator::Schema::Constants;
101 use SQL::Translator::Utils qw(debug header_comment);
104 # Use only lowercase for the keys (e.g. "long" and not "LONG")
110 varchar2 => 'varchar',
126 'long integer' => 'integer',
128 'datetime' => 'datetime',
132 my $translator = shift;
133 local $DEBUG = $translator->debug;
134 my $no_comments = $translator->no_comments;
135 my $add_drop_table = $translator->add_drop_table;
136 my $schema = $translator->schema;
137 my $show_warnings = $translator->show_warnings || 0;
139 debug("PKG: Beginning production\n");
142 $create .= header_comment unless ($no_comments);
143 # \todo Don't set if MySQL 3.x is set on command line
144 $create .= "SET foreign_key_checks=0;\n\n";
147 # Work out which tables need to be InnoDB to support foreign key
148 # constraints. We do this first as we need InnoDB at both ends.
150 foreach ( map { $_->get_constraints } $schema->get_tables ) {
151 next unless $_->type eq FOREIGN_KEY;
152 foreach my $meth (qw/table reference_table/) {
153 my $table = $schema->get_table($_->$meth) || next;
154 next if $table->extra('mysql_table_type');
155 $table->extra( 'mysql_table_type' => 'InnoDB');
163 for my $table ( $schema->get_tables ) {
164 # print $table->name, "\n";
165 push @table_defs, create_table($table,
166 { add_drop_table => $add_drop_table,
167 show_warnings => $show_warnings,
168 no_comments => $no_comments
172 # print "@table_defs\n";
174 return wantarray ? ($create, @table_defs, 'SET foreign_key_checks=1;') : $create . join ('', @table_defs, "SET foreign_key_checks=1;\n\n");
179 my ($table, $options) = @_;
181 my $table_name = $table->name;
182 debug("PKG: Looking at table '$table_name'\n");
185 # Header. Should this look like what mysqldump produces?
188 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
189 $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $options->{add_drop_table};
190 $create .= "CREATE TABLE $table_name (\n";
196 for my $field ( $table->get_fields ) {
197 push @field_defs, create_field($field);
205 for my $index ( $table->get_indices ) {
206 push @index_defs, create_index($index);
207 $indexed_fields{ $_ } = 1 for $index->fields;
211 # Constraints -- need to handle more than just FK. -ky
214 my @constraints = $table->get_constraints;
215 for my $c ( @constraints ) {
216 my $constr = create_constraint($c, $options);
217 push @constraint_defs, $constr if($constr);
219 unless ( $indexed_fields{ ($c->fields())[0] } ) {
220 push @index_defs, "INDEX (" . ($c->fields())[0] . ")";
221 $indexed_fields{ ($c->fields())[0] } = 1;
225 $create .= join(",\n", map { " $_" }
226 @field_defs, @index_defs, @constraint_defs
233 my $table_type_defined = 0;
234 for my $t1_option_ref ( $table->options ) {
235 my($key, $value) = %{$t1_option_ref};
236 $table_type_defined = 1
237 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
238 $create .= " $key=$value";
240 my $mysql_table_type = $table->extra('mysql_table_type');
241 #my $charset = $table->extra('mysql_character_set');
242 #my $collate = $table->extra('mysql_collate');
243 #$create .= " Type=$mysql_table_type" if $mysql_table_type;
244 #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
245 #$create .= " COLLATE $collate" if $collate;
246 $create .= " Type=$mysql_table_type"
247 if $mysql_table_type && !$table_type_defined;
248 my $charset = $table->extra('mysql_charset');
249 my $collate = $table->extra('mysql_collate');
250 my $comments = $table->comments;
252 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
253 $create .= " COLLATE $collate" if $collate;
254 $create .= qq[ comment='$comments'] if $comments;
264 my $field_name = $field->name;
265 debug("PKG: Looking at field '$field_name'\n");
266 my $field_def = $field_name;
269 my $data_type = $field->data_type;
270 my @size = $field->size;
271 my %extra = $field->extra;
272 my $list = $extra{'list'} || [];
273 # \todo deal with embedded quotes
274 my $commalist = join( ', ', map { qq['$_'] } @$list );
275 my $charset = $extra{'mysql_charset'};
276 my $collate = $extra{'mysql_collate'};
279 # Oracle "number" type -- figure best MySQL type
281 if ( lc $data_type eq 'number' ) {
283 if ( scalar @size > 1 ) {
284 $data_type = 'double';
286 elsif ( $size[0] && $size[0] >= 12 ) {
287 $data_type = 'bigint';
289 elsif ( $size[0] && $size[0] <= 1 ) {
290 $data_type = 'tinyint';
297 # Convert a large Oracle varchar to "text"
299 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
303 elsif ( $data_type =~ /char/i && ! $size[0] ) {
306 elsif ( $data_type =~ /boolean/i ) {
308 $commalist = "'0','1'";
310 elsif ( exists $translate{ lc $data_type } ) {
311 $data_type = $translate{ lc $data_type };
314 @size = () if $data_type =~ /(text|blob)/i;
316 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
320 $field_def .= " $data_type";
322 if ( lc $data_type eq 'enum' ) {
323 $field_def .= '(' . $commalist . ')';
325 elsif ( defined $size[0] && $size[0] > 0 ) {
326 $field_def .= '(' . join( ', ', @size ) . ')';
330 $field_def .= " CHARACTER SET $charset" if $charset;
331 $field_def .= " COLLATE $collate" if $collate;
334 for my $qual ( qw[ binary unsigned zerofill ] ) {
335 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
336 $field_def .= " $qual";
338 for my $qual ( 'character set', 'collate', 'on update' ) {
339 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
340 $field_def .= " $qual $val";
344 $field_def .= ' NOT NULL' unless $field->is_nullable;
346 # Default? XXX Need better quoting!
347 my $default = $field->default_value;
348 if ( defined $default ) {
349 if ( uc $default eq 'NULL') {
350 $field_def .= ' DEFAULT NULL';
352 $field_def .= " DEFAULT '$default'";
356 if ( my $comments = $field->comments ) {
357 $field_def .= qq[ comment '$comments'];
361 $field_def .= " auto_increment" if $field->is_auto_increment;
371 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
373 '(' . join( ', ', $index->fields ) . ')'
378 sub create_constraint
380 my ($c, $options) = @_;
382 my @fields = $c->fields or next;
384 if ( $c->type eq PRIMARY_KEY ) {
385 return 'PRIMARY KEY (' . join(', ', @fields). ')';
387 elsif ( $c->type eq UNIQUE ) {
390 (defined $c->name ? $c->name.' ' : '').
391 '(' . join(', ', @fields). ')';
393 elsif ( $c->type eq FOREIGN_KEY ) {
395 # Make sure FK field is indexed or MySQL complains.
399 map { $_ || () } 'CONSTRAINT', $c->table . '_' . $c->name, 'FOREIGN KEY'
402 $def .= ' (' . join( ', ', @fields ) . ')';
404 $def .= ' REFERENCES ' . $c->reference_table;
406 my @rfields = map { $_ || () } $c->reference_fields;
407 unless ( @rfields ) {
408 my $rtable_name = $c->reference_table;
409 if ( my $ref_table = $c->table->schema->get_table( $rtable_name ) ) {
410 push @rfields, $ref_table->primary_key;
413 warn "Can't find reference table '$rtable_name' " .
414 "in schema\n" if $options->{show_warnings};
419 $def .= ' (' . join( ', ', @rfields ) . ')';
422 warn "FK constraint on " . $c->table->name . '.' .
423 join('', @fields) . " has no reference fields\n"
424 if $options->{show_warnings};
427 if ( $c->match_type ) {
429 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
432 if ( $c->on_delete ) {
433 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
436 if ( $c->on_update ) {
437 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
447 my ($from_field, $to_field) = @_;
449 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
450 $to_field->table->name,
452 create_field($to_field));
459 my ($new_field) = @_;
461 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
462 $new_field->table->name,
463 create_field($new_field));
471 my ($old_field) = @_;
473 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
474 $old_field->table->name,
483 # -------------------------------------------------------------------
489 SQL::Translator, http://www.mysql.com/.
493 darren chamberlain E<lt>darren@cpan.orgE<gt>,
494 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.