Applied patch sent in by Daniel Westermann-Clark on Oct 11 2006.

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / DB2.pm

diff --git a/lib/SQL/Translator/Producer/DB2.pm b/lib/SQL/Translator/Producer/DB2.pm
index 2f0fb32..6605f6b 100644 (file)
--- 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.4 2006-08-26 11:30:31 schiffbruechige Exp $
+# $Id: DB2.pm,v 1.5 2006-11-10 21:21:51 mwz444 Exp $
 # -------------------------------------------------------------------
 # 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.4 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.5 $ =~ /(\d+)\.(\d+)/;
 $DEBUG   = 0 unless defined $DEBUG;
 
 use SQL::Translator::Schema::Constants;
@@ -205,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 . ";" if $add_drop_table;
-        push @table_defs, create_table($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)
         {
@@ -229,8 +231,8 @@ sub produce
         push @trigger_defs, create_trigger($trigger);
     }
 
-    return wantarray ? (@table_defs, @index_defs, @view_defs, @trigger_defs) :
-        $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;
@@ -275,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
@@ -307,14 +308,16 @@ sub create_field
     my $field_def = "$field_name $data_type";
     $field_def .= $field->is_auto_increment ? 
         ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)' : '';
-    $field_def .= $data_type =~ /CHAR/i ? "(${size})" : '';
+    $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;
 }
@@ -336,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;
                       
 }