package SQL::Translator::Producer::DB2;
# -------------------------------------------------------------------
-# $Id: DB2.pm,v 1.1 2005-09-18 20:06:31 schiffbruechige Exp $
+# $Id: DB2.pm,v 1.2 2006-05-24 22:06:56 schiffbruechige Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 SQLFairy Authors
#
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
# 02111-1307 USA
# -------------------------------------------------------------------
-
=head1 NAME
SQL::Translator::Producer::DB2 - DB2 SQL producer
=head1 DESCRIPTION
-Creates an SQL DDL suitable for DB.
+Creates an SQL DDL suitable for DB2.
=cut
+use warnings;
use strict;
use vars qw[ $VERSION $DEBUG $WARN ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.2 $ =~ /(\d+)\.(\d+)/;
$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 %translate = (
+my %dt_translate = (
#
# MySQL types
#
mediumblob => 'blob',
longblob => 'long varchar for bit data',
tinytext => 'varchar',
- text => 'clob',
- longtext => 'clob',
- mediumtext => 'clob',
+ text => 'varchar',
+ longtext => 'varchar',
+ mediumtext => 'varchar',
enum => 'varchar',
set => 'varchar',
date => 'date',
my $indent = ' ';
$output .= header_comment unless($no_comments);
- my (@table_defs);
+ my (@table_defs, @index_defs);
foreach my $table ($schema->get_tables)
{
- my $table_name = check_name($table->name, 'tables', 18);
+ push @table_defs, 'DROP TABLE ' . $table->name . ";\n" if $add_drop_table;
+ push @table_defs, create_table($table, {
+ no_comments => $no_comments});
- my (@field_defs, @comments);
- push @comments, "--\n-- Table: $table_name\n--" unless $no_comments;
- foreach my $field ($table->get_fields)
+ foreach my $index ($table->get_indices)
{
- my $field_name = check_name($field->name, 'fields', 30);
- my $data_type = uc($translate{lc($field->data_type)} || $field->data_type);
- 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_nullable ? ' NOT NULL':'';
- $field_def .= $field->is_primary_key ? ' PRIMARY KEY':'';
- $field_def .= $field->default_value ? ' DEFAULT ' . $field->default_value : '';
-
- push @field_defs, "${indent}${field_def}";
+ push @index_defs, create_index($index);
}
-
-
- my $tablespace = $table->extra()->{'TABLESPACE'} || '';
- my $table_def = "CREATE TABLE $table_name (\n";
- $table_def .= join (",\n", @field_defs);
- $table_def .= "\n)";
- $table_def .= $tablespace ? "IN $tablespace;" : ';';
-
- push @table_defs, "DROP TABLE $table_name;\n" if $add_drop_table;
- push @table_defs, $table_def;
+
}
+ my (@view_defs);
+ foreach my $view ( $schema->get_views )
+ {
+ push @view_defs, create_view($view);
+ }
+ my (@trigger_defs);
+ foreach my $trigger ( $schema->get_triggers )
+ {
+ push @trigger_defs, create_trigger($trigger);
+ }
- $output .= join("\n\n", @table_defs);
+ $output .= join("\n\n", @table_defs, @index_defs, @view_defs, @trigger_defs) . "\n";
}
{ my %objnames;
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) }
- values(%{$objnames{$type}}))
- {
- die "Got multiple matching table names when truncated";
- }
- $objnames{$type}{$name} = substr($name, 0,$length);
- $newname = $objnames{$type}{$name};
+# if(grep {$_ eq substr($name, 0, $length) }
+# values(%{$objnames{$type}}))
+# {
+# die "Got multiple matching table names when truncated";
+# }
+# $objnames{$type}{$name} = substr($name, 0,$length);
+# $newname = $objnames{$type}{$name};
}
if($db2_reserved{uc($newname)})
}
}
+sub create_table
+{
+ my ($table, $options) = @_;
+
+ my $table_name = check_name($table->name, 'tables', 128);
+ # this limit is 18 in older DB2s ! (<= 8)
+
+ my (@field_defs, @comments);
+ push @comments, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
+ foreach my $field ($table->get_fields)
+ {
+ push @field_defs, create_field($field);
+ }
+ my @con_defs;
+ foreach my $con ($table->get_constraints)
+ {
+ push @con_defs, create_constraint($con);
+ }
+ 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 .= "\n)";
+ $table_def .= $tablespace ? "IN $tablespace;" : ';';
+
+ return $table_def;
+}
+
+sub create_field
+{
+ my ($field) = @_;
+
+ my $field_name = check_name($field->name, 'fields', 30);
+# use Data::Dumper;
+# print Dumper(\%dt_translate);
+# print $field->data_type, " ", $dt_translate{lc($field->data_type)}, "\n";
+ my $data_type = uc($dt_translate{lc($field->data_type)} || $field->data_type);
+ 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_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 . "'") : '';
+
+}
+
+sub create_index
+{
+ my ($index) = @_;
+
+ my $out = sprintf('CREATE %sINDEX %s ON %s ( %s );',
+ $index->type() =~ /^UNIQUE$/i ? 'UNIQUE' : '',
+ $index->name,
+ $index->table->name,
+ join(', ', $index->fields) );
+
+ return $out;
+}
+
+sub create_constraint
+{
+ my ($constraint) = @_;
+
+ return '' if($constraint->type =~ /^PRIMARY(_|\s)KEY$/i);
+
+ 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' : '';
+
+ 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 $update = $constraint->on_update ? $constraint->on_update : '';
+ my $delete = $constraint->on_delete ? $constraint->on_delete : '';
+
+ my $out = sprintf('%s %s %s %s %s %s',
+ $constraint->name ? ('CONSTRAINT ' . $constraint->name) : '',
+ $ctype,
+ '(' . join (', ', $constraint->fields) . ')',
+ $expr ? $expr : $ref,
+ $update,
+ $delete);
+
+
+ return $out;
+
+}
+
+sub create_view
+{
+ my ($view) = @_;
+
+ my $out = sprintf("CREATE VIEW %s AS\n%s;",
+ $view->name,
+ $view->sql);
+
+ return $out;
+}
+
+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 $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 ?
+ ('UPDATE OF '. join(', ', $trigger->fields)) :
+ $trigger->database_event || 'UPDATE',
+ $trigger->on_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) = @_;
+}
+
+sub add_field
+{
+ my ($field) = @_;
+}
+
+sub drop_field
+{
+ my ($field) = @_;
+}
1;
projects / dbsrgits/SQL-Translator.git / blobdiff