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
#
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;
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 (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;
}
{
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;
}
package SQL::Translator::Producer::PostgreSQL;
# -------------------------------------------------------------------
-# $Id: PostgreSQL.pm,v 1.26 2006-11-09 18:16:24 schiffbruechige Exp $
+# $Id: PostgreSQL.pm,v 1.27 2006-11-10 21:21:51 mwz444 Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 SQLFairy Authors
#
use strict;
use warnings;
use vars qw[ $DEBUG $WARN $VERSION ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.26 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.27 $ =~ /(\d+)\.(\d+)/;
$DEBUG = 1 unless defined $DEBUG;
use SQL::Translator::Schema::Constants;
$output = join("\n\n", @table_defs);
if ( @fks ) {
$output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
- $output .= join( "\n\n", @fks );
+ $output .= join( "\n\n", @fks ) . "\n";
}
if ( $WARN ) {
#=============================================================================
BEGIN {
- maybe_plan(162, 'SQL::Translator::Parser::XML::SQLFairy');
+ maybe_plan(204, 'SQL::Translator::Parser::XML::SQLFairy');
}
my $testschema = "$Bin/data/xml/schema.xml";
comments => "Hello emptytagdef",
},
{
+ name => "another_id",
+ data_type => "int",
+ size => "10",
+ default_value => 2,
+ is_nullable => 1,
+ is_foreign_key => 1,
+ },
+ {
name => "timest",
data_type => "timestamp",
size => "0",
name => 'emailuniqueindex',
type => UNIQUE,
fields => ["email"],
- }
+ },
+ {
+ type => FOREIGN_KEY,
+ fields => ["another_id"],
+ reference_table => "Another",
+ reference_fields => ["id"],
+ },
],
indices => [
{
},
},
],
- } # end table Basic
+ }, # end table Basic
+ {
+ name => "Another",
+ extra => {
+ foo => "bar",
+ hello => "world",
+ bar => "baz",
+ mysql_table_type => "InnoDB",
+ },
+ fields => [
+ {
+ name => "id",
+ data_type => "int",
+ default_value => undef,
+ is_nullable => 0,
+ size => 10,
+ is_primary_key => 1,
+ is_auto_increment => 1,
+ },
+ ],
+ }, # end table Another
], # end tables
views => [
ok $out ne "" ,"Produced something!";
local $/ = undef; # slurp
eq_or_diff $out, q{
- Table: Basic}
+ Table: Basic
+ Table: Another}
,"Output looks right";
}
order: 7
table: Basic
+ another_id
+ data_type: int
+ size: 10
+ is_nullable: 1
+ default_value: 2
+ is_primary_key: 0
+ is_unique: 0
+ is_auto_increment: 0
+ is_foreign_key: 1
+ foreign_key_reference: Another
+ is_valid: 1
+ order: 8
+ table: Basic
+
timest
data_type: timestamp
size: 0
is_foreign_key: 0
foreign_key_reference:
is_valid: 1
- order: 8
+ order: 9
table: Basic
options:
is_valid: 1
+ ?
+ type: FOREIGN KEY
+ fields: another_id
+ expression:
+ match_type:
+ reference_fields: id
+ reference_table: Another
+ deferrable: 1
+ on_delete:
+ on_update:
+ options:
+ is_valid: 1
+
+Table: Another
+==========================================================================
+
+Fields
+ id
+ data_type: int
+ size: 10
+ is_nullable: 0
+ default_value:
+ is_primary_key: 1
+ is_unique: 0
+ is_auto_increment: 1
+ is_foreign_key: 0
+ foreign_key_reference:
+ is_valid: 1
+ order: 10
+ table: Another
+
+
+Indices
+
+Constraints
+ ?
+ type: PRIMARY KEY
+ fields: id
+ expression:
+ match_type:
+ reference_fields:
+ reference_table:
+ deferrable: 1
+ on_delete:
+ on_update:
+ options:
+ is_valid: 1
+
__DATA__
Hello World
-Tables: Basic
+Tables: Basic, Another
Basic
------
-Fields: id title description email explicitnulldef explicitemptystring emptytagdef timest
+Fields: id title description email explicitnulldef explicitemptystring emptytagdef another_id timest
+
+Another
+------
+Fields: id
+
DROP TABLE Basic;
CREATE TABLE Basic (
-id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
-title VARCHAR(100) NOT NULL DEFAULT 'hello',
-description VARCHAR(0) DEFAULT '',
-email VARCHAR(255),
-explicitnulldef VARCHAR(0),
-explicitemptystring VARCHAR(0) DEFAULT '',
-emptytagdef VARCHAR(0) DEFAULT '',
-timest TIMESTAMP,
-CONSTRAINT emailuniqueindex UNIQUE (email) ,
- PRIMARY KEY(id)
+ id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
+ title VARCHAR(100) NOT NULL DEFAULT 'hello',
+ description VARCHAR(0) DEFAULT '',
+ email VARCHAR(255),
+ explicitnulldef VARCHAR(0),
+ explicitemptystring VARCHAR(0) DEFAULT '',
+ emptytagdef VARCHAR(0) DEFAULT '',
+ another_id INTEGER DEFAULT 2,
+ timest TIMESTAMP,
+ PRIMARY KEY (id),
+ CONSTRAINT emailuniqueindex UNIQUE (email)
);
+DROP TABLE Another;
+
+CREATE TABLE Another (
+ id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
+ PRIMARY KEY (id)
+);
+
+ALTER TABLE Basic ADD FOREIGN KEY (another_id) REFERENCES Another(id);
+
CREATE INDEX titleindex ON Basic ( title );
CREATE VIEW email_list AS
my $want = [ 'DROP TABLE Basic;',
q|CREATE TABLE Basic (
-id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
-title VARCHAR(100) NOT NULL DEFAULT 'hello',
-description VARCHAR(0) DEFAULT '',
-email VARCHAR(255),
-explicitnulldef VARCHAR(0),
-explicitemptystring VARCHAR(0) DEFAULT '',
-emptytagdef VARCHAR(0) DEFAULT '',
-timest TIMESTAMP,
-CONSTRAINT emailuniqueindex UNIQUE (email) ,
- PRIMARY KEY(id)
+ id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
+ title VARCHAR(100) NOT NULL DEFAULT 'hello',
+ description VARCHAR(0) DEFAULT '',
+ email VARCHAR(255),
+ explicitnulldef VARCHAR(0),
+ explicitemptystring VARCHAR(0) DEFAULT '',
+ emptytagdef VARCHAR(0) DEFAULT '',
+ another_id INTEGER DEFAULT 2,
+ timest TIMESTAMP,
+ PRIMARY KEY (id),
+ CONSTRAINT emailuniqueindex UNIQUE (email)
);|,
+'DROP TABLE Another;',
+
+q|CREATE TABLE Another (
+ id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
+ PRIMARY KEY (id)
+);|,
+
+'ALTER TABLE Basic ADD FOREIGN KEY (another_id) REFERENCES Another(id);',
+
'CREATE INDEX titleindex ON Basic ( title );',
'CREATE VIEW email_list AS
"explicitemptystring" character varying DEFAULT '',
-- Hello emptytagdef
"emptytagdef" character varying DEFAULT '',
+ "another_id" integer DEFAULT '2',
"timest" timestamp(0),
PRIMARY KEY ("id"),
Constraint "emailuniqueindex" UNIQUE ("email")
);
CREATE INDEX "titleindex" on "Basic" ("title");
+
+
+DROP TABLE "Another";
+CREATE TABLE "Another" (
+ "id" serial NOT NULL,
+ PRIMARY KEY ("id")
+);
+
+ALTER TABLE "Basic" ADD FOREIGN KEY ("another_id")
+ REFERENCES "Another" ("id");
SQL
explicitemptystring varchar DEFAULT '',
-- Hello emptytagdef
emptytagdef varchar DEFAULT '',
+ another_id int(10) DEFAULT '2',
timest timestamp
);
CREATE INDEX titleindex_Basic on Basic (title);
CREATE UNIQUE INDEX emailuniqueindex_Basic on Basic (email);
+DROP TABLE Another;
+CREATE TABLE Another (
+ id INTEGER PRIMARY KEY NOT NULL
+);
+
+
COMMIT;
SQL
is_unique: [% field.is_unique %]
is_auto_increment: [% field.is_auto_increment %]
is_foreign_key: [% field.is_foreign_key %]
- foreign_key_reference: [% field.foreign_key_reference %]
+ foreign_key_reference: [% field.foreign_key_reference.reference_table %]
is_valid: [% field.is_valid %]
order: [% field.order %]
table: [% field.table %]
data_type="varchar" order="7" default_value="" >
<comments>Hello emptytagdef</comments>
</field>
+ <field name="another_id" size="10"
+ data_type="int" default_value="2" />
<field name="timest" size="0"
data_type="timestamp" order="7" >
</field>
<extra foo="bar" hello="world" bar="baz" />
</constraint>
<constraint name="emailuniqueindex" type="UNIQUE" fields="email" />
+ <constraint name="" type="FOREIGN KEY" fields="another_id"
+ reference_table="Another" options="" deferrable="1" match_type=""
+ expression="" on_update="" on_delete="">
+ </constraint>
</constraints>
<extra foo="bar" hello="world" bar="baz" mysql_table_type="InnoDB" />
</table>
+
+ <table order="1" name="Another">
+ <fields>
+ <field
+ name="id"
+ is_primary_key="1" is_foreign_key="0"
+ size="10" data_type="int" is_auto_increment="1" order="1"
+ is_nullable="0">
+ </field>
+ </fields>
+
+ <constraints>
+ <constraint name="" type="PRIMARY KEY" fields="id"
+ reference_table="" options="" deferrable="1" match_type=""
+ expression="" on_update="" on_delete="">
+ </constraint>
+ </constraints>
+
+ <extra foo="bar" hello="world" bar="baz" mysql_table_type="InnoDB" />
+ </table>
</tables>
<views>
__DATA__
Hello World
-Tables: [% schema.get_tables %]
-[% table = schema.get_tables.first -%]
+Tables: [% schema.get_tables.join(', ') %]
+[% FOREACH table IN schema.get_tables -%]
$table
------
Fields: $table.field_names.join
+[% END %]
projects / dbsrgits/SQL-Translator.git / commitdiff