X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FDB2.pm;h=c728ae5e0853e1ad8960c4e90191c722f5f6ac3a;hb=821a0fde221f5accf93e3f65efa77b5a6733cb5e;hp=e59704ea0e8d0aef5214764c951664028b3ea5a9;hpb=a0ea6c87dfbb871007b6f455dbd74ff739276755;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/DB2.pm b/lib/SQL/Translator/Producer/DB2.pm index e59704e..c728ae5 100644 --- a/lib/SQL/Translator/Producer/DB2.pm +++ b/lib/SQL/Translator/Producer/DB2.pm @@ -1,7 +1,7 @@ package SQL::Translator::Producer::DB2; # ------------------------------------------------------------------- -# $Id: DB2.pm,v 1.2 2006-05-24 22:06:56 schiffbruechige Exp $ +# $Id$ # ------------------------------------------------------------------- # Copyright (C) 2002-4 SQLFairy Authors # @@ -39,7 +39,7 @@ Creates an SQL DDL suitable for DB2. use warnings; use strict; use vars qw[ $VERSION $DEBUG $WARN ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.2 $ =~ /(\d+)\.(\d+)/; +$VERSION = sprintf "%d.%02d", q$Revision$ =~ /(\d+)\.(\d+)/; $DEBUG = 0 unless defined $DEBUG; use SQL::Translator::Schema::Constants; @@ -52,7 +52,9 @@ use SQL::Translator::Utils qw(header_comment); # of SQL data types, with field->extra entries being used to convert back to # weird types like "polygon" if needed (IMO anyway) -my %dt_translate = ( +my %dt_translate; +BEGIN { + %dt_translate = ( # # MySQL types # @@ -106,6 +108,7 @@ my %dt_translate = ( varchar2 => 'varchar', long => 'clob', ); +} my %db2_reserved = map { $_ => 1} qw/ ADD DETERMINISTIC LEAVE RESTART @@ -202,12 +205,14 @@ sub produce my $indent = ' '; $output .= header_comment unless($no_comments); - my (@table_defs, @index_defs); + my (@table_defs, @fks, @index_defs); foreach my $table ($schema->get_tables) { - push @table_defs, 'DROP TABLE ' . $table->name . ";\n" if $add_drop_table; - push @table_defs, create_table($table, { + push @table_defs, 'DROP TABLE ' . $table->name . ";" if $add_drop_table; + my ($table_def, $fks) = create_table($table, { no_comments => $no_comments}); + push @table_defs, $table_def; + push @fks, @$fks; foreach my $index ($table->get_indices) { @@ -226,7 +231,8 @@ sub produce push @trigger_defs, create_trigger($trigger); } - $output .= join("\n\n", @table_defs, @index_defs, @view_defs, @trigger_defs) . "\n"; + return wantarray ? (@table_defs, @fks, @index_defs, @view_defs, @trigger_defs) : + $output . join("\n\n", @table_defs, @fks, @index_defs, @view_defs, @trigger_defs) . "\n"; } { my %objnames; @@ -253,7 +259,8 @@ sub produce warn "$newname is a reserved word in DB2!" if $WARN; } - return sprintf("%-*s", $length-5, $newname); +# return sprintf("%-*s", $length-5, $newname); + return $newname; } } @@ -270,22 +277,21 @@ sub create_table { push @field_defs, create_field($field); } - my @con_defs; + my (@con_defs, @fks); foreach my $con ($table->get_constraints) { - push @con_defs, create_constraint($con); + my ($cdefs, $fks) = create_constraint($con); + push @con_defs, @$cdefs; + push @fks, @$fks; } - my $pkey = join(", ", $table->primary_key()->fields); my $tablespace = $table->extra()->{'TABLESPACE'} || ''; my $table_def = "CREATE TABLE $table_name (\n"; - $table_def .= join (",\n", @field_defs); - $table_def .= join (",\n", @con_defs); - $table_def .= ",\n PRIMARY KEY($pkey)"; + $table_def .= join (",\n", map { " $_" } @field_defs, @con_defs); $table_def .= "\n)"; $table_def .= $tablespace ? "IN $tablespace;" : ';'; - return $table_def; + return $table_def, \@fks; } sub create_field @@ -301,16 +307,19 @@ sub create_field my $field_def = "$field_name $data_type"; $field_def .= $field->is_auto_increment ? - ' GENERATED BY DEFAULT AS IDENTITY' : ''; - $field_def .= $data_type =~ /CHAR/i ? "(${size})" : ''; + ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)' : ''; + $field_def .= $data_type =~ /(CHAR|CLOB)/i ? "(${size})" : ''; $field_def .= !$field->is_nullable ? ' NOT NULL':''; # $field_def .= $field->is_primary_key ? ' PRIMARY KEY':''; $field_def .= !defined $field->default_value ? '' : $field->default_value =~ /current( |_)timestamp/i || $field->default_value =~ /\Qnow()\E/i ? - 'DEFAULT CURRENT TIMESTAMP' : defined $field->default_value ? - (" DEFAULT '" . $field->default_value . "'") : ''; + ' DEFAULT CURRENT TIMESTAMP' : defined $field->default_value ? + (" DEFAULT " . ($data_type =~ /(INT|DOUBLE)/i ? + $field->default_value : "'" . $field->default_value . "'") + ) : ''; + return $field_def; } sub create_index @@ -330,29 +339,36 @@ sub create_constraint { my ($constraint) = @_; - return '' if($constraint->type =~ /^PRIMARY(_|\s)KEY$/i); + my (@con_defs, @fks); my $ctype = $constraint->type =~ /^PRIMARY(_|\s)KEY$/i ? 'PRIMARY KEY' : $constraint->type =~ /^UNIQUE$/i ? 'UNIQUE' : $constraint->type =~ /^CHECK_C$/i ? 'CHECK' : - $constraint->type =~ /^FOREIGN_KEY$/i ? 'FOREIGN KEY' : ''; + $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? 'FOREIGN KEY' : ''; my $expr = $constraint->type =~ /^CHECK_C$/i ? $constraint->expression : ''; - my $ref = $constraint->type =~ /^FOREIGN_KEY$/i ? ('REFERENCES ' . $constraint->reference_table . '(' . join(', ', $constraint->reference_fields) . ')') : ''; + my $ref = $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? ('REFERENCES ' . $constraint->reference_table . '(' . join(', ', $constraint->reference_fields) . ')') : ''; my $update = $constraint->on_update ? $constraint->on_update : ''; my $delete = $constraint->on_delete ? $constraint->on_delete : ''; - my $out = sprintf('%s %s %s %s %s %s', + my $out = join(' ', grep { $_ } $constraint->name ? ('CONSTRAINT ' . $constraint->name) : '', $ctype, '(' . join (', ', $constraint->fields) . ')', $expr ? $expr : $ref, $update, $delete); + if ($constraint->type eq FOREIGN_KEY) { + my $table_name = $constraint->table->name; + $out = "ALTER TABLE $table_name ADD $out;"; + push @fks, $out; + } + else { + push @con_defs, $out; + } - - return $out; + return \@con_defs, \@fks; } @@ -378,7 +394,7 @@ sub create_trigger $trigger->database_event =~ /update_on/i ? ('UPDATE OF '. join(', ', $trigger->fields)) : $trigger->database_event || 'UPDATE', - $trigger->on_table->name, + $trigger->table->name, $trigger->extra->{reference} || 'REFERENCING OLD AS oldrow NEW AS newrow', $trigger->extra->{granularity} || 'FOR EACH ROW', $trigger->action ); @@ -390,15 +406,40 @@ sub create_trigger sub alter_field { my ($from_field, $to_field) = @_; + + my $data_type = uc($dt_translate{lc($to_field->data_type)} || $to_field->data_type); + + my $size = $to_field->size(); + $data_type .= $data_type =~ /CHAR/i ? "(${size})" : ''; + + # DB2 will only allow changing of varchar/vargraphic datatypes + # to extend their lengths. Or changing of text types to other + # texttypes, and numeric types to larger numeric types. (v8) + # We can also drop/add keys, checks and constraints, but not + # columns !? + + my $out = sprintf('ALTER TABLE %s ALTER %s SET DATATYPE %s', + $to_field->table->name, + $to_field->name, + $data_type); + } sub add_field { - my ($field) = @_; + my ($new_field) = @_; + + my $out = sprintf('ALTER TABLE %s ADD COLUMN %s', + $new_field->table->name, + create_field($new_field)); + + return $out; } sub drop_field { my ($field) = @_; + + return ''; } 1;