X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FDB2.pm;h=051877f3863aa40e5e6aa23c2c1aa964c68b49a3;hb=b4414534f4fb5bfa82073f0768d36914340175e9;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..051877f 100644 --- a/lib/SQL/Translator/Producer/DB2.pm +++ b/lib/SQL/Translator/Producer/DB2.pm @@ -1,24 +1,5 @@ package SQL::Translator::Producer::DB2; -# ------------------------------------------------------------------- -# $Id: DB2.pm,v 1.2 2006-05-24 22:06:56 schiffbruechige Exp $ -# ------------------------------------------------------------------- -# Copyright (C) 2002-4 SQLFairy Authors -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU General Public License as -# published by the Free Software Foundation; version 2. -# -# This program is distributed in the hope that it will be useful, but -# WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -# General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA -# 02111-1307 USA -# ------------------------------------------------------------------- =head1 NAME SQL::Translator::Producer::DB2 - DB2 SQL producer @@ -38,8 +19,9 @@ 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+)/; +use warnings; +our ( $DEBUG, $WARN ); +our $VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; use SQL::Translator::Schema::Constants; @@ -52,7 +34,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 +90,7 @@ my %dt_translate = ( varchar2 => 'varchar', long => 'clob', ); +} my %db2_reserved = map { $_ => 1} qw/ ADD DETERMINISTIC LEAVE RESTART @@ -185,11 +170,9 @@ DEFAULT JOIN RELEASE WRITE DEFAULTS KEY RENAME YEAR DEFINITION LABEL REPEAT YEARS DELETE LANGUAGE RESET -DESCRIPTOR LC_CTYPE RESIGNAL +DESCRIPTOR LC_CTYPE RESIGNAL /; -#------------------------------------------------------------------------------ - sub produce { my ($translator) = @_; @@ -202,19 +185,21 @@ 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) { push @index_defs, create_index($index); } - } + } my (@view_defs); foreach my $view ( $schema->get_views ) { @@ -226,7 +211,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; @@ -239,7 +225,7 @@ sub produce if(length($name) > $length) ## Maximum table name length is 18 { warn "Table name $name is longer than $length characters, truncated" if $WARN; -# if(grep {$_ eq substr($name, 0, $length) } +# if(grep {$_ eq substr($name, 0, $length) } # values(%{$objnames{$type}})) # { # die "Got multiple matching table names when truncated"; @@ -253,7 +239,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; } } @@ -261,7 +248,7 @@ sub create_table { my ($table, $options) = @_; - my $table_name = check_name($table->name, 'tables', 128); + my $table_name = check_name($table->name, 'tables', 128); # this limit is 18 in older DB2s ! (<= 8) my (@field_defs, @comments); @@ -270,28 +257,27 @@ 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 { my ($field) = @_; - + my $field_name = check_name($field->name, 'fields', 30); # use Data::Dumper; # print Dumper(\%dt_translate); @@ -300,17 +286,20 @@ sub create_field my $size = $field->size(); 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})" : ''; + $field_def .= $field->is_auto_increment ? + ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)' : ''; + $field_def .= $data_type =~ /(CHAR|CLOB|NUMERIC|DECIMAL)/i ? "(${size})" : ''; $field_def .= !$field->is_nullable ? ' NOT NULL':''; # $field_def .= $field->is_primary_key ? ' PRIMARY KEY':''; - $field_def .= !defined $field->default_value ? '' : + $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 . "'") : ''; + $field->default_value =~ /\Qnow()\E/i ? + ' 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,30 +319,37 @@ 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 \@con_defs, \@fks; - return $out; - } sub create_view @@ -372,33 +368,59 @@ sub create_trigger my ($trigger) = @_; # create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action + my $db_events = join ', ', $trigger->database_events; my $out = sprintf('CREATE TRIGGER %s %s %s ON %s %s %s MODE DB2SQL %s', $trigger->name, $trigger->perform_action_when || 'AFTER', - $trigger->database_event =~ /update_on/i ? + $db_events =~ /update_on/i ? ('UPDATE OF '. join(', ', $trigger->fields)) : - $trigger->database_event || 'UPDATE', - $trigger->on_table->name, + $db_events || 'UPDATE', + $trigger->table->name, $trigger->extra->{reference} || 'REFERENCING OLD AS oldrow NEW AS newrow', $trigger->extra->{granularity} || 'FOR EACH ROW', $trigger->action ); return $out; - + } 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;