Create unit test for ::Parser::DBI::PostgreSQL, fix parser namespace lookup

diff --git a/AUTHORS b/AUTHORS
index 0ee6d21..3e069da 100644 (file)
--- a/AUTHORS
+++ b/AUTHORS
@@ -6,6 +6,7 @@ The following people have contributed to the SQLFairy project:
 -   Andrew Moore <amoore@cpan.org>
 -   Arthur Axel "fREW" Schmidt <frioux@gmail.com>
 -   Ben Faga <faga@cshl.edu>
+-   Cedric Carree <beg0@free.fr>
 -   Chris Hilton <chilton@alterpoint.com>
 -   Chris Mungall <cjm@fruitfly.org>
 -   Chris To <christot@users.sourceforge.net>

diff --git a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
index 86c66d7..525c72c 100644 (file)
--- a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
+++ b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
@@ -68,7 +68,7 @@ sub parse {
       "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
               i.indisprimary, pg_get_indexdef(oid) AS create_string
        FROM pg_class c,pg_index i
-       WHERE c.relnamespace=2200 AND c.relkind='i'
+       WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname='public') AND c.relkind='i'
          AND c.oid=i.indexrelid AND i.indrelid=?"
     );
 

diff --git a/t/66-postgres-dbi-parser.t b/t/66-postgres-dbi-parser.t
new file mode 100644 (file)
index 0000000..024c6a2
--- /dev/null
+++ b/t/66-postgres-dbi-parser.t
@@ -0,0 +1,162 @@
+#!/usr/bin/perl
+# vim: set ft=perl:
+
+use strict;
+use Test::More;
+use SQL::Translator;
+use SQL::Translator::Schema::Constants;
+use Test::SQL::Translator qw(maybe_plan table_ok);
+
+BEGIN {
+    maybe_plan(49, 'SQL::Translator::Parser::DBI::PostgreSQL');
+    SQL::Translator::Parser::DBI::PostgreSQL->import('parse');
+}
+
+use_ok('SQL::Translator::Parser::DBI::PostgreSQL');
+
+my @dsn =
+  $ENV{DBICTEST_PG_DSN} ? @ENV{ map { "DBICTEST_PG_$_" } qw/DSN USER PASS/ }
+: $ENV{DBI_DSN} ? @ENV{ map { "DBI_$_" } qw/DSN USER PASS/ }
+: ( "dbi:Pg:dbname=postgres", '', '' );
+
+my $dbh = eval {
+  DBI->connect(@dsn, {AutoCommit => 1, RaiseError=>1,PrintError => 1} );
+};
+
+SKIP: {
+    if (my $err = ($@ || $DBI::err )) {
+      chomp $err;
+      skip "No connection to test db. DBI says '$err'", 48;
+    }
+
+    ok($dbh, "dbh setup correctly");
+    $dbh->do('SET client_min_messages=WARNING');
+
+my $t   = SQL::Translator->new( trace => 0 );
+my $sql = q[
+    drop table if exists sqlt_test2;
+    drop table if exists sqlt_test1;
+    drop table if exists sqlt_products_1;
+
+    create table sqlt_test1 (
+        f_serial serial NOT NULL primary key,
+        f_varchar character varying (255),
+        f_text text default 'FOO'
+    );
+
+
+    create table sqlt_test2 (
+        f_id integer NOT NULL,
+        f_int smallint,
+        primary key (f_id),
+        f_fk1 integer NOT NULL references sqlt_test1 (f_serial)
+    );
+
+    CREATE TABLE sqlt_products_1 (
+        product_no integer,
+        name text,
+        price numeric
+    );
+];
+
+$| = 1;
+
+$dbh->do($sql);
+
+my $data   = SQL::Translator::Parser::DBI::PostgreSQL::parse( $t, $dbh );
+my $schema = $t->schema;
+
+isa_ok( $schema, 'SQL::Translator::Schema', 'Schema object' );
+my @tables = $schema->get_tables;
+
+my $t1 = $schema->get_table("sqlt_test1");
+is( $t1->name, 'sqlt_test1', 'Table sqlt_test1 exists' );
+
+my @t1_fields = $t1->get_fields;
+is( scalar @t1_fields, 3, '3 fields in sqlt_test1' );
+
+my $f1 = shift @t1_fields;
+is( $f1->name, 'f_serial', 'First field is "f_serial"' );
+#FIXME: it should better be 'INTEGER' instead of 'int4'
+is( $f1->data_type, 'int4', 'Field is an integer' );
+is( $f1->is_nullable, 0, 'Field cannot be null' );
+is( $f1->default_value, "nextval('sqlt_test1_f_serial_seq'::regclass)", 'Default value is nextval()' );
+is( $f1->is_primary_key, 1, 'Field is PK' );
+#FIXME: not set to auto-increment? maybe we can guess auto-increment behavior by looking at the default_value (i.e. it call function nextval() )
+#is( $f1->is_auto_increment, 1, 'Field is auto increment' );
+
+my $f2 = shift @t1_fields;
+is( $f2->name, 'f_varchar', 'Second field is "f_varchar"' );
+is( $f2->data_type, 'varchar', 'Field is a varchar' );
+is( $f2->is_nullable, 1, 'Field can be null' );
+#FIXME: should not be 255?
+is( $f2->size, 259, 'Size is "259"' );
+is( $f2->default_value, undef, 'Default value is undefined' );
+is( $f2->is_primary_key, 0, 'Field is not PK' );
+is( $f2->is_auto_increment, 0, 'Field is not auto increment' );
+
+my $f3 = shift @t1_fields;
+is( $f3->name, 'f_text', 'Third field is "f_text"' );
+is( $f3->data_type, 'text', 'Field is a text' );
+is( $f3->is_nullable, 1, 'Field can be null' );
+is( $f3->size, 0, 'Size is 0' );
+is( $f3->default_value, "'FOO'::text", 'Default value is "FOO"' );
+is( $f3->is_primary_key, 0, 'Field is not PK' );
+is( $f3->is_auto_increment, 0, 'Field is not auto increment' );
+
+#TODO: no 'NOT NULL' constraint not set
+
+my $t2 = $schema->get_table("sqlt_test2");
+is( $t2->name, 'sqlt_test2', 'Table sqlt_test2 exists' );
+
+my @t2_fields = $t2->get_fields;
+is( scalar @t2_fields, 3, '3 fields in sqlt_test2' );
+
+my $t2_f1 = shift @t2_fields;
+is( $t2_f1->name, 'f_id', 'First field is "f_id"' );
+is( $t2_f1->data_type, 'int4', 'Field is an integer' );
+is( $t2_f1->is_nullable, 0, 'Field cannot be null' );
+is( $t2_f1->size, 0, 'Size is "0"' );
+is( $t2_f1->default_value, undef, 'Default value is undefined' );
+is( $t2_f1->is_primary_key, 1, 'Field is PK' );
+
+my $t2_f2= shift @t2_fields;
+is( $t2_f2->name, 'f_int', 'Third field is "f_int"' );
+is( $t2_f2->data_type, 'int2', 'Field is an integer' );
+is( $t2_f2->is_nullable, 1, 'Field can be null' );
+is( $t2_f2->size, 0, 'Size is "0"' );
+is( $t2_f2->default_value, undef, 'Default value is undefined' );
+is( $t2_f2->is_primary_key, 0, 'Field is not PK' );
+
+my $t2_f3 = shift @t2_fields;
+is( $t2_f3->name, 'f_fk1', 'Third field is "f_fk1"' );
+is( $t2_f3->data_type, 'int4', 'Field is an integer' );
+is( $t2_f3->is_nullable, 0, 'Field cannot be null' );
+is( $t2_f3->size, 0, 'Size is "0"' );
+is( $t2_f3->default_value, undef, 'Default value is undefined' );
+is( $t2_f3->is_primary_key, 0, 'Field is not PK' );
+is( $t2_f3->is_foreign_key, 1, 'Field is a FK' );
+my $fk_ref1 = $t2_f3->foreign_key_reference;
+isa_ok( $fk_ref1, 'SQL::Translator::Schema::Constraint', 'FK' );
+is( $fk_ref1->reference_table, 'sqlt_test1', 'FK is to "sqlt_test1" table' );
+
+my @t2_constraints = $t2->get_constraints;
+is( scalar @t2_constraints, 1, "One constraint on table" );
+
+my $t2_c1 = shift @t2_constraints;
+is( $t2_c1->type, FOREIGN_KEY, "Constraint is a FK" );
+
+$dbh->disconnect;
+} # end of SKIP block
+
+END {
+  if ($dbh) {
+    for (
+      'drop table if exists sqlt_test2',
+      'drop table if exists sqlt_test1',
+      'drop table if exists sqlt_products_1',
+    ) {
+      eval { $dbh->do($_) };
+    }
+  }
+}