Added support for proper enums under pg (as of 8.3), with pg version check, and defer...

Patch from Debolaz

diff --git a/META.yml b/META.yml
index 30ffda1..72c1613 100644 (file)
--- a/META.yml
+++ b/META.yml
@@ -1,6 +1,6 @@
 ---
 name: SQL-Translator
-version: 0.0900
+version: 0.09000
 author:
   - 'Ken Y. Clark <kclark@cpan.org>'
 abstract: SQL DDL transformations and more
@@ -12,6 +12,7 @@ requires:
   Class::Base: 0
   Class::Data::Inheritable: 0.02
   Class::MakeMethods: 0
+  DBI: 0
   IO::Dir: 0
   Log::Log4perl: 0
   Parse::RecDescent: 1.94
@@ -40,7 +41,7 @@ provides:
     file: lib/SQL/Translator/Parser/DB2/Grammar.pm
   SQL::Translator:
     file: lib/SQL/Translator.pm
-    version: 0.0900
+    version: 0.09000
   SQL::Translator::Diff:
     file: lib/SQL/Translator/Diff.pm
   SQL::Translator::Filter::DefaultExtra:
@@ -222,6 +223,7 @@ provides:
     version: 1.08
   SQL::Translator::Schema::Table:
     file: lib/SQL/Translator/Schema/Table.pm
+    version: 1.27
   SQL::Translator::Schema::Trigger:
     file: lib/SQL/Translator/Schema/Trigger.pm
     version: 1.09

diff --git a/bin/sqlt b/bin/sqlt
index 3df1db4..2608167 100755 (executable)
--- a/bin/sqlt
+++ b/bin/sqlt
@@ -83,6 +83,10 @@ To translate a schema:
     --quote-field-names  Qjuote all field names in statements
     --no-comments      Don't include comments in SQL output
 
+  PostgreSQL Producer Options:
+
+    --postgres-version   PostgreSQL server version
+
   Diagram Producer Options:
 
     --imap-file        Filename to put image map data
@@ -184,6 +188,7 @@ my $producer_db_password; # db_pass "
 my $producer_dsn;     # db_user "
 my $add_truncate;
 my $mysql_parser_version;  # MySQL parser arg for /*! comments
+my $postgres_version; # PostgreSQL version
 
 GetOptions(
     'add-drop-table'   => \$add_drop_table,
@@ -226,6 +231,7 @@ GetOptions(
     'use-same-auth'    => \$use_same_auth,
     'version'          => \$show_version,
     'mysql-parser-version=i' => \$mysql_parser_version,
+    'postgres-version=f' => \$postgres_version,
 ) or pod2usage(2);
 
 if ($use_same_auth) {
@@ -289,6 +295,7 @@ my $translator           =  SQL::Translator->new(
         prefix           => $prefix,
         indent           => $indent,
         newlines         => $newlines,
+        postgres_version => $postgres_version,
            package_name     => $package_name,
     },
 );

diff --git a/lib/SQL/Translator.pm b/lib/SQL/Translator.pm
index 916cd59..645102c 100644 (file)
--- a/lib/SQL/Translator.pm
+++ b/lib/SQL/Translator.pm
@@ -1273,6 +1273,8 @@ The following people have contributed to the SQLFairy project:
 
 =item * Sam Angiuoli <angiuoli@users.sourceforge.net>
 
+=item * Anders Nor Berle <berle@cpan.org>
+
 =item * Dave Cash <dave@gnofn.org>
 
 =item * Darren Chamberlain <dlc@users.sourceforge.net>

diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index 9647357..afea2b8 100644 (file)
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -181,6 +181,9 @@ sub produce {
     my $no_comments    = $translator->no_comments;
     my $add_drop_table = $translator->add_drop_table;
     my $schema         = $translator->schema;
+    my $pargs          = $translator->producer_args;
+
+    my $postgres_version = $pargs->{postgres_version} || 0;
 
     my $qt = '';
     $qt = '"' if ($translator->quote_table_names);
@@ -198,6 +201,7 @@ sub produce {
                                              { quote_table_names => $qt,
                                                quote_field_names => $qf,
                                                no_comments => $no_comments,
+                                               postgres_version => $postgres_version,
                                                add_drop_table => $add_drop_table,});
         push @table_defs, $table_def;
         push @fks, @$fks;
@@ -315,6 +319,7 @@ sub create_table
     my $qf = $options->{quote_field_names} || '';
     my $no_comments = $options->{no_comments} || 0;
     my $add_drop_table = $options->{add_drop_table} || 0;
+    my $postgres_version = $options->{postgres_version} || 0;
 
     my $table_name    = $table->name or next;
     $table_name       = mk_name( $table_name, '', undef, 1 );
@@ -322,7 +327,7 @@ sub create_table
     $table->name($table_name_ur);
 
 # print STDERR "$table_name table_name\n";
-    my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
+    my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks );
 
     push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
 
@@ -341,6 +346,9 @@ sub create_table
         push @field_defs, create_field($field, { quote_table_names => $qt,
                                                  quote_field_names => $qf,
                                                  table_name => $table_name_ur,
+                                                 postgres_version => $postgres_version,
+                                                 type_defs => \@type_defs,
+                                                 type_drops => \@type_drops,
                                                  constraint_defs => \@constraint_defs,});
     }
 
@@ -377,8 +385,17 @@ sub create_table
 
     my $create_statement;
     $create_statement = join("\n", @comments);
-    $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n] 
-        if $add_drop_table;
+    if ($add_drop_table) {
+        if ($postgres_version >= 8.2) {
+            $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n];
+            $create_statement .= join ("\n", @type_drops) . "\n"
+                if $postgres_version >= 8.3;
+        } else {
+            $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n];
+        }
+    }
+    $create_statement .= join("\n", @type_defs) . "\n"
+        if $postgres_version >= 8.3;
     $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n].
                             join( ",\n", map { "  $_" } @field_defs, @constraint_defs ).
                             "\n);"
@@ -401,6 +418,9 @@ sub create_table
         my $qf = $options->{quote_field_names} || '';
         my $table_name = $field->table->name;
         my $constraint_defs = $options->{constraint_defs} || [];
+        my $postgres_version = $options->{postgres_version} || 0;
+        my $type_defs = $options->{type_defs} || [];
+        my $type_drops = $options->{type_drops} || [];
 
         $field_name_scope{$table_name} ||= {};
         my $field_name    = mk_name(
@@ -425,7 +445,14 @@ sub create_table
         my $commalist = join( ', ', map { qq['$_'] } @$list );
         my $seq_name;
 
-        $field_def .= ' '. convert_datatype($field);
+        if ($postgres_version >= 8.3 && $field->data_type eq 'enum') {
+            my $type_name = $field->table->name . '_' . $field->name . '_type';
+            $field_def .= ' '. $type_name;
+            push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist);";
+            push @$type_drops, "DROP TYPE IF EXISTS $type_name;";
+        } else {
+            $field_def .= ' '. convert_datatype($field);
+        }
 
         #
         # Default value -- disallow for timestamps
@@ -568,6 +595,10 @@ sub create_table
                 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
             }
 
+            if ( $c->deferrable ) {
+                $def .= ' DEFERRABLE';
+            }
+
             push @fks, "$def;";
         }
 

diff --git a/lib/SQL/Translator/Schema/Table.pm b/lib/SQL/Translator/Schema/Table.pm
index 3aa2c4c..feb0014 100644 (file)
--- a/lib/SQL/Translator/Schema/Table.pm
+++ b/lib/SQL/Translator/Schema/Table.pm
@@ -51,6 +51,8 @@ use base 'SQL::Translator::Schema::Object';
 
 use vars qw( $VERSION $FIELD_ORDER );
 
+$VERSION = sprintf "%d.%02d", q$Revision: 1.27 $ =~ /(\d+)\.(\d+)/;
+
 # Stringify to our name, being careful not to pass any args through so we don't
 # accidentally set it to undef. We also have to tweak bool so the object is
 # still true when it doesn't have a name (which shouldn't happen!).

diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t
index 0fd269c..ced163f 100644 (file)
--- a/t/46xml-to-pg.t
+++ b/t/46xml-to-pg.t
@@ -58,5 +58,5 @@ CREATE TABLE "Another" (
 );
 
 ALTER TABLE "Basic" ADD FOREIGN KEY ("another_id")
-  REFERENCES "Another" ("id");
+  REFERENCES "Another" ("id") DEFERRABLE;
 SQL

diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t
index 9c0590d..d52136f 100644 (file)
--- a/t/47postgres-producer.t
+++ b/t/47postgres-producer.t
@@ -14,7 +14,7 @@ use FindBin qw/$Bin/;
 #=============================================================================
 
 BEGIN {
-    maybe_plan(6,
+    maybe_plan(7,
         'SQL::Translator::Producer::PostgreSQL',
         'Test::Differences',
     )
@@ -90,3 +90,17 @@ my $field4 = SQL::Translator::Schema::Field->new( name      => 'bytea_field',
 my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4);
 
 is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
+
+my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field',
+                                                   table => $table,
+                                                   data_type => 'enum',
+                                                   extra => { list => [ 'Foo', 'Bar' ] },
+                                                   is_auto_increment => 0,
+                                                   is_nullable => 0,
+                                                   is_foreign_key => 0,
+                                                   is_unique => 0 );
+
+my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
+
+is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
+