From: Jess Robinson <castaway@desert-island.me.uk>
Date: Fri, 4 Aug 2006 21:38:21 +0000 (+0000)
Subject: Add timestamp tests, make postgres produce timestamp(0) if asked
X-Git-Tag: v0.11008~413
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=08d91aada45b94a16f3660b5cc10384df5616ff9

Add timestamp tests, make postgres produce timestamp(0) if asked
---

diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index e34d31f..9ea1d69 100644
--- 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.24 2006-07-23 14:03:52 schiffbruechige Exp $
+# $Id: PostgreSQL.pm,v 1.25 2006-08-04 21:38:20 schiffbruechige 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.24 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.25 $ =~ /(\d+)\.(\d+)/;
 $DEBUG = 1 unless defined $DEBUG;
 
 use SQL::Translator::Schema::Constants;
@@ -191,24 +191,24 @@ sub produce {
     $output .= header_comment unless ($no_comments);
 #    my %used_index_names;
 
-    my @table_defs;
+    my (@table_defs, @fks);
     for my $table ( $schema->get_tables ) {
-        
-        push @table_defs, create_table($table, { quote_table_names => $qt,
-                                                 quote_field_names => $qf,
-                                                 no_comments => $no_comments,
-                                                 add_drop_table => $add_drop_table,});
-
-#        $output .= join( "\n\n", 
-#            @comments,
-#            @sequence_defs, 
-#            $create_statement, 
-#            @index_defs, 
-#            '' 
- #       );
+
+        my ($table_def, $fks) = create_table($table, 
+                                             { quote_table_names => $qt,
+                                               quote_field_names => $qf,
+                                               no_comments => $no_comments,
+                                               add_drop_table => $add_drop_table,});
+        push @table_defs, $table_def;
+        push @fks, @$fks;
+
     }
 
     $output = join("\n\n", @table_defs);
+    if ( @fks ) {
+        $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
+        $output .= join( "\n\n", @fks );
+    }
 
     if ( $WARN ) {
         if ( %truncated ) {
@@ -318,7 +318,7 @@ sub create_table
 
     my $table_name    = $table->name or next;
     $table_name       = mk_name( $table_name, '', undef, 1 );
-    my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
+    my $table_name_ur = $qt ? $table_name : unreserve($table_name);
     $table->name($table_name_ur);
 
 # print STDERR "$table_name table_name\n";
@@ -383,14 +383,10 @@ sub create_table
                             join( ",\n", map { "  $_" } @field_defs, @constraint_defs ).
                             "\n);"
                             ;
-    if ( @fks ) {
-        $create_statement .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
-        $create_statement .= join( "\n\n", @fks );
-    }
 
     $create_statement .= "\n" . join(";\n", @index_defs) . "\n";
     
-    return $create_statement;    
+    return $create_statement, \@fks;
 }
 
 { 
@@ -410,7 +406,7 @@ sub create_table
         my $field_name    = mk_name(
                                     $field->name, '', $field_name_scope{$table_name}, 1 
                                     );
-        my $field_name_ur = $qf ? unreserve($field_name, $table_name ) : $field_name;
+        my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name );
         $field->name($field_name_ur);
         my $field_comments = $field->comments 
             ? "-- " . $field->comments . "\n  " 
@@ -463,7 +459,7 @@ sub create_table
         my $qt = $options->{quote_table_names} ||'';
         my $qf = $options->{quote_field_names} ||'';
         my $table_name = $index->table->name;
-        my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
+#        my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
 
         my ($index_def, @constraint_defs);
 
@@ -477,7 +473,7 @@ sub create_table
         my $type = $index->type || NORMAL;
         my @fields     = 
             map { $_ =~ s/\(.+\)//; $_ }
-        map { $qt ? unreserve($_, $table_name ) : $_ }
+        map { $qt ? $_ : unreserve($_, $table_name ) }
         $index->fields;
         next unless @fields;
 
@@ -492,7 +488,7 @@ sub create_table
         }
         elsif ( $type eq NORMAL ) {
             $index_def = 
-                "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name_ur}${qt} (".
+                "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (".
                 join( ', ', map { qq[$qf$_$qf] } @fields ).  
                 ');'
                 ; 
@@ -522,12 +518,12 @@ sub create_table
 
         my @fields     = 
             map { $_ =~ s/\(.+\)//; $_ }
-        map { $qt ? unreserve( $_, $table_name ) : $_}
+        map { $qt ? $_ : unreserve( $_, $table_name )}
         $c->fields;
 
         my @rfields     = 
             map { $_ =~ s/\(.+\)//; $_ }
-        map { $qt ? unreserve( $_, $table_name ) : $_}
+        map { $qt ? $_ : unreserve( $_, $table_name )}
         $c->reference_fields;
 
         next if !@fields && $c->type ne CHECK_C;
@@ -640,6 +636,9 @@ sub convert_datatype
     if ( defined $size[0] && $size[0] > 0 ) {
         $data_type .= '(' . join( ',', @size ) . ')';
     }
+    elsif (defined $size[0] && $data_type eq 'timestamp' ) {
+        $data_type .= '(' . join( ',', @size ) . ')';
+    }
 
 
     return $data_type;
diff --git a/t/14postgres-parser.t b/t/14postgres-parser.t
index 0c32dd0..e655614 100644
--- a/t/14postgres-parser.t
+++ b/t/14postgres-parser.t
@@ -8,7 +8,7 @@ use SQL::Translator::Schema::Constants;
 use Test::SQL::Translator qw(maybe_plan);
 
 BEGIN {
-    maybe_plan(119, 'SQL::Translator::Parser::PostgreSQL');
+    maybe_plan(117, 'SQL::Translator::Parser::PostgreSQL');
     SQL::Translator::Parser::PostgreSQL->import('parse');
 }
 
@@ -27,7 +27,9 @@ my $sql = q[
         f_tz timestamp,
         f_text text,
         f_fk1 integer not null references t_test2 (f_id),
-        f_dropped text
+        f_dropped text,
+        f_timestamp timestamp(0) with time zone,
+        f_timestamp2 timestamp without time zone
     );
 
     create table t_test2 (
@@ -52,7 +54,7 @@ my $sql = q[
     alter table t_test1 alter column f_char drop default;
 
     -- The following are allowed by the grammar 
-    -- but won't do anything... - ky
+    -- but won\'t do anything... - ky
 
     alter table t_text1 alter column f_char set not null;
 
@@ -86,7 +88,7 @@ is( $t1->name, 't_test1', 'Table t_test1 exists' );
 is( $t1->comments, 'comment on t_test1', 'Table comment exists' );
 
 my @t1_fields = $t1->get_fields;
-is( scalar @t1_fields, 11, '11 fields in t_test1' );
+is( scalar @t1_fields, 13, '13 fields in t_test1' );
 
 my $f1 = shift @t1_fields;
 is( $f1->name, 'f_serial', 'First field is "f_serial"' );
@@ -176,16 +178,16 @@ isa_ok( $fk_ref1, 'SQL::Translator::Schema::Constraint', 'FK' );
 is( $fk_ref1->reference_table, 't_test2', 'FK is to "t_test2" table' );
 
 my $f11 = shift @t1_fields;
-is( $f11->name, 'f_fk2', 'Eleventh field is "f_fk2"' );
-is( $f11->data_type, 'integer', 'Field is an integer' );
+is( $f11->name, 'f_timestamp', 'Eleventh field is "f_timestamp"' );
+is( $f11->data_type, 'timestamp', 'Field is a timestamp' );
 is( $f11->is_nullable, 1, 'Field can be null' );
-is( $f11->size, 10, 'Size is "10"' );
-is( $f11->default_value, 'FOO', 'Default value is "FOO"' );
+is( $f11->size, 0, 'Size is "0"' );
+is( $f11->default_value, undef, 'Default value is "undef"' );
 is( $f11->is_primary_key, 0, 'Field is not PK' );
-is( $f11->is_foreign_key, 1, 'Field is a FK' );
-my $fk_ref2 = $f11->foreign_key_reference;
-isa_ok( $fk_ref2, 'SQL::Translator::Schema::Constraint', 'FK' );
-is( $fk_ref2->reference_table, 't_test2', 'FK is to "t_test2" table' );
+is( $f11->is_foreign_key, 0, 'Field is not FK' );
+# my $fk_ref2 = $f11->foreign_key_reference;
+# isa_ok( $fk_ref2, 'SQL::Translator::Schema::Constraint', 'FK' );
+# is( $fk_ref2->reference_table, 't_test2', 'FK is to "t_test2" table' );
 
 my @t1_constraints = $t1->get_constraints;
 is( scalar @t1_constraints, 8, '8 constraints on t_test1' );
diff --git a/t/16xml-parser.t b/t/16xml-parser.t
index 3d1c568..433ec08 100644
--- a/t/16xml-parser.t
+++ b/t/16xml-parser.t
@@ -27,7 +27,7 @@ use constant DEBUG => (exists $opt{d} ? 1 : 0);
 #=============================================================================
 
 BEGIN {
-    maybe_plan(150, 'SQL::Translator::Parser::XML::SQLFairy');
+    maybe_plan(162, 'SQL::Translator::Parser::XML::SQLFairy');
 }
 
 my $testschema = "$Bin/data/xml/schema.xml";
@@ -119,6 +119,12 @@ schema_ok( $scma, {
                     is_nullable => 1,
                     comments => "Hello emptytagdef",
                 },
+                {
+                    name => "timest",
+                    data_type => "timestamp",
+                    size => "0",
+                    is_nullable => 1,
+                },
             ],
             constraints => [
                 {
diff --git a/t/18ttschema-producer.t b/t/18ttschema-producer.t
index 997065a..5002d1e 100644
--- a/t/18ttschema-producer.t
+++ b/t/18ttschema-producer.t
@@ -193,6 +193,20 @@ Fields
         order:                 7
         table:                 Basic
     
+    timest
+        data_type:             timestamp
+        size:                  0
+        is_nullable:           1
+        default_value:         
+        is_primary_key:        0
+        is_unique:             0
+        is_auto_increment:     0
+        is_foreign_key:        0
+        foreign_key_reference: 
+        is_valid:              1
+        order:                 8
+        table:                 Basic
+    
 
 Indices
     titleindex
diff --git a/t/34tt-base.t b/t/34tt-base.t
index d104dbe..8808b1d 100644
--- a/t/34tt-base.t
+++ b/t/34tt-base.t
@@ -46,4 +46,4 @@ Tables: Basic
 
 Basic
 ------
-Fields: id title description email explicitnulldef explicitemptystring emptytagdef
+Fields: id title description email explicitnulldef explicitemptystring emptytagdef timest
diff --git a/t/43xml-to-db2.t b/t/43xml-to-db2.t
index f48c609..8e4b7ca 100644
--- a/t/43xml-to-db2.t
+++ b/t/43xml-to-db2.t
@@ -43,6 +43,7 @@ email VARCHAR(255),
 explicitnulldef VARCHAR(0),
 explicitemptystring VARCHAR(0) DEFAULT '',
 emptytagdef VARCHAR(0) DEFAULT '',
+timest TIMESTAMP,
 CONSTRAINT emailuniqueindex UNIQUE (email)   ,
  PRIMARY KEY(id)
 );
diff --git a/t/44-xml-to-db2-array.t b/t/44-xml-to-db2-array.t
index d20ba89..d254af9 100644
--- a/t/44-xml-to-db2-array.t
+++ b/t/44-xml-to-db2-array.t
@@ -41,6 +41,7 @@ email VARCHAR(255),
 explicitnulldef VARCHAR(0),
 explicitemptystring VARCHAR(0) DEFAULT '',
 emptytagdef VARCHAR(0) DEFAULT '',
+timest TIMESTAMP,
 CONSTRAINT emailuniqueindex UNIQUE (email)   ,
  PRIMARY KEY(id)
 );|,
@@ -53,4 +54,4 @@ SELECT email FROM Basic WHERE email IS NOT NULL;',
 'CREATE TRIGGER foo_trigger after insert ON Basic REFERENCING OLD AS oldrow NEW AS newrow FOR EACH ROW MODE DB2SQL update modified=timestamp();'
 ];
 
-is_deeply(\@sql, $want, 'Got correct DB2 statements in list context');
\ No newline at end of file
+is_deeply(\@sql, $want, 'Got correct DB2 statements in list context');
diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t
index 0104bfe..7bb025f 100644
--- a/t/46xml-to-pg.t
+++ b/t/46xml-to-pg.t
@@ -43,6 +43,7 @@ CREATE TABLE "Basic" (
   "explicitemptystring" character varying DEFAULT '',
   -- Hello emptytagdef
   "emptytagdef" character varying DEFAULT '',
+  "timest" timestamp(0),
   PRIMARY KEY ("id"),
   Constraint "emailuniqueindex" UNIQUE ("email")
 );
diff --git a/t/48xml-to-sqlite.t b/t/48xml-to-sqlite.t
index bac7be5..fd05ad4 100644
--- a/t/48xml-to-sqlite.t
+++ b/t/48xml-to-sqlite.t
@@ -50,7 +50,8 @@ CREATE TABLE Basic (
   explicitnulldef varchar,
   explicitemptystring varchar DEFAULT '',
   -- Hello emptytagdef
-  emptytagdef varchar DEFAULT ''
+  emptytagdef varchar DEFAULT '',
+  timest timestamp
 );
 
 CREATE INDEX titleindex_Basic on Basic (title);
diff --git a/t/data/xml/schema.xml b/t/data/xml/schema.xml
index 459291c..3a1f2de 100644
--- a/t/data/xml/schema.xml
+++ b/t/data/xml/schema.xml
@@ -34,6 +34,9 @@ Created on Fri Aug 15 15:08:18 2003
               data_type="varchar" order="7" default_value="" >
               <comments>Hello emptytagdef</comments>
           </field>
+          <field name="timest" size="0"
+              data_type="timestamp" order="7" >
+          </field>
         </fields>
 
         <indices>