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

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

diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index 10976e8..afea2b8 100644 (file)
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -1,7 +1,7 @@
 package SQL::Translator::Producer::PostgreSQL;
 
 # -------------------------------------------------------------------
-# $Id: PostgreSQL.pm,v 1.28 2006-11-20 23:56:14 schiffbruechige Exp $
+# $Id: PostgreSQL.pm,v 1.29 2007-06-04 04:01:14 mwz444 Exp $
 # -------------------------------------------------------------------
 # Copyright (C) 2002-4 SQLFairy Authors
 #
@@ -39,7 +39,7 @@ producer.
 use strict;
 use warnings;
 use vars qw[ $DEBUG $WARN $VERSION ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.28 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.29 $ =~ /(\d+)\.(\d+)/;
 $DEBUG = 1 unless defined $DEBUG;
 
 use SQL::Translator::Schema::Constants;
@@ -77,7 +77,7 @@ BEGIN {
     set        => 'character varying',
     date       => 'date',
     datetime   => 'timestamp',
-    time       => 'date',
+    time       => 'time',
     timestamp  => 'timestamp',
     year       => 'date',
 
@@ -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,14 +327,14 @@ 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--" unless $no_comments;
+    push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
 
     if ( $table->comments and !$no_comments ){
         my $c = "-- Comments: \n-- ";
         $c .= join "\n-- ",  $table->comments;
-        $c .= "\n--";
+        $c .= "\n--\n";
         push @comments, $c;
     }
 
@@ -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,});
     }
 
@@ -356,7 +364,7 @@ sub create_table
                                                   quote_table_names => $qt,
                                                   table_name => $table_name,
                                               });
-        push @index_defs, $idef;
+        $idef and push @index_defs, $idef;
         push @constraint_defs, @$constraints;
     }
 
@@ -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;";
         }
 
@@ -631,12 +662,15 @@ sub convert_datatype
             $data_type = 'integer';
         }
     }
+    my @type_without_size = qw/bigint boolean box bytea cidr circle date inet
+                               integer smallint text line lseg macaddr money
+                               path point polygon real/;
+    foreach (@type_without_size) {
+        if ( $data_type =~ qr/$_/ ) {
+            undef @size; last;
+        }
+    }
 
-    #
-    # PG doesn't need a size for integers or text
-    #
-    undef @size if $data_type =~ m/(integer|smallint|bigint|text)/;
-    
     if ( defined $size[0] && $size[0] > 0 ) {
         $data_type .= '(' . join( ',', @size ) . ')';
     }