package SQL::Translator::Producer::DB2;
-# -------------------------------------------------------------------
-# $Id: DB2.pm,v 1.3 2006-06-07 16:02:54 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
use warnings;
use strict;
use vars qw[ $VERSION $DEBUG $WARN ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.3 $ =~ /(\d+)\.(\d+)/;
+$VERSION = '1.59';
$DEBUG = 0 unless defined $DEBUG;
use SQL::Translator::Schema::Constants;
# 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 = (
#
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)
{
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;
{
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_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|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->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;
}
{
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;
}
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',
+ $db_events || 'UPDATE',
$trigger->table->name,
$trigger->extra->{reference} || 'REFERENCING OLD AS oldrow NEW AS newrow',
$trigger->extra->{granularity} || 'FOR EACH ROW',