Hi list,
I figured out that their is no unit test of the
package SQL::Translator::Parser::DBI::PostgreSQL. I saw lots of patches
proposals for this package and some [1] where refused due to the lack of
unit test. I'm okay with that. It's good practice.
I have create a small unit test file for this package. You will find it
attached. Tests will be run only if you can connect to the 'postgres'
db. If you can't, only the 'use_ok' test will be run and all others
will be skipped nicely.
This unit test shows me that some fields are not parsed as expected
(type 'int4' instead of 'integer', size of varchar(255) is 259 instead
of 255, auto-increment not set for primary key, ...) See the various
FIXME in the code. I guess some of proposed patches [1,2,3] on the
Mailing List should corrects these bugs.
FYI i run:
- perl v5.10.1,
- Ubuntu 10.04.3 LTS
- postgreSQL 8.4
- SQL::Translator from the git repo
Cedric
[1] http://sourceforge.net/mailarchive/message.php?msg_id=25400491
[2] http://sourceforge.net/mailarchive/message.php?msg_id=25272966
[3] http://sourceforge.net/mailarchive/message.php?msg_id=22808163
#!/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(50, 'SQL::Translator::Parser::DBI::PostgreSQL');
SQL::Translator::Parser::DBI::PostgreSQL->import('parse');
}
use_ok('SQL::Translator::Parser::DBI::PostgreSQL');
my $dbh;
eval {
$dbh = DBI->connect("dbi:Pg:dbname=postgres", '', '', {AutoCommit => 0, RaiseError=>1,PrintError => 1});
};
SKIP: {
skip "No connection to test db. DBI says '$DBI::errstr'", 49 if($DBI::err);
ok($dbh, "dbh setup correctly");
my $t = SQL::Translator->new( trace => 0 );
my $sql = q[
create table t_test1 (
f_serial serial NOT NULL primary key,
f_varchar character varying (255),
f_text text default 'FOO'
);
create table t_test2 (
f_id integer NOT NULL,
f_int smallint,
primary key (f_id),
f_fk1 integer NOT NULL references t_test1 (f_serial)
);
CREATE TABLE 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;
is( scalar @tables, 3, 'Three tables' );
my $t1 = $schema->get_table("t_test1");
is( $t1->name, 't_test1', 'Table t_test1 exists' );
my @t1_fields = $t1->get_fields;
is( scalar @t1_fields, 3, '3 fields in t_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('t_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("t_test2");
is( $t2->name, 't_test2', 'Table t_test2 exists' );
my @t2_fields = $t2->get_fields;
is( scalar @t2_fields, 3, '3 fields in t_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, 't_test1', 'FK is to "t_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
------------------------------------------------------------------------------
RSA(R) Conference 2012
Save $700 by Nov 18
Register now
http://p.sf.net/sfu/rsa-sfdev2dev1
--
sqlfairy-developers mailing list
sqlfairy-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
