Stop the DBI parser from disconnecting externally supplied DBI handles
[dbsrgits/SQL-Translator.git] / t / 66-postgres-dbi-parser.t
CommitLineData
122353c5 1#!/usr/bin/perl
2# vim: set ft=perl:
3
4use strict;
5use Test::More;
6use SQL::Translator;
7use SQL::Translator::Schema::Constants;
8use Test::SQL::Translator qw(maybe_plan table_ok);
9
10BEGIN {
c3919fdd 11 maybe_plan(61, 'SQL::Translator::Parser::DBI::PostgreSQL');
122353c5 12 SQL::Translator::Parser::DBI::PostgreSQL->import('parse');
13}
14
15use_ok('SQL::Translator::Parser::DBI::PostgreSQL');
16
17my @dsn =
18 $ENV{DBICTEST_PG_DSN} ? @ENV{ map { "DBICTEST_PG_$_" } qw/DSN USER PASS/ }
19: $ENV{DBI_DSN} ? @ENV{ map { "DBI_$_" } qw/DSN USER PASS/ }
20: ( "dbi:Pg:dbname=postgres", '', '' );
21
22my $dbh = eval {
23 DBI->connect(@dsn, {AutoCommit => 1, RaiseError=>1,PrintError => 1} );
24};
25
26SKIP: {
27 if (my $err = ($@ || $DBI::err )) {
28 chomp $err;
fd52d7dd 29 skip "No connection to test db. DBI says '$err'", 59;
122353c5 30 }
31
32 ok($dbh, "dbh setup correctly");
33 $dbh->do('SET client_min_messages=WARNING');
34
122353c5 35my $sql = q[
36 drop table if exists sqlt_test2;
37 drop table if exists sqlt_test1;
38 drop table if exists sqlt_products_1;
39
40 create table sqlt_test1 (
41 f_serial serial NOT NULL primary key,
a23f9a9d 42 f_varchar character varying(255),
c601ca5d 43 f_text text default 'FOO',
44 f_to_drop integer,
45 f_last text
122353c5 46 );
47
fd52d7dd 48 comment on table sqlt_test1 is 'this is a comment on the first table';
49 comment on column sqlt_test1.f_text is 'this is a comment on a field of the first table';
50
c601ca5d 51 create index sqlt_test1_f_last_idx on sqlt_test1 (f_last);
122353c5 52
53 create table sqlt_test2 (
54 f_id integer NOT NULL,
55 f_int smallint,
56 primary key (f_id),
57 f_fk1 integer NOT NULL references sqlt_test1 (f_serial)
58 );
59
60 CREATE TABLE sqlt_products_1 (
61 product_no integer,
62 name text,
63 price numeric
64 );
c601ca5d 65
aee4b66e 66 -- drop a column, to not have a linear id
c601ca5d 67 -- When the table t_test1 is created, f_last get id 5 but
68 -- after this drop, there is only 4 columns.
69 alter table sqlt_test1 drop column f_to_drop;
122353c5 70];
71
72$| = 1;
73
74$dbh->do($sql);
75
c3919fdd 76my $t = SQL::Translator->new(
77 trace => 0,
78 parser => 'DBI',
79 parser_args => { dbh => $dbh },
80);
81$t->translate;
122353c5 82my $schema = $t->schema;
83
84isa_ok( $schema, 'SQL::Translator::Schema', 'Schema object' );
c3919fdd 85
86ok ($dbh->ping, 'External handle still connected');
87
122353c5 88my @tables = $schema->get_tables;
89
90my $t1 = $schema->get_table("sqlt_test1");
91is( $t1->name, 'sqlt_test1', 'Table sqlt_test1 exists' );
fd52d7dd 92is( $t1->comments, 'this is a comment on the first table', 'First table has a comment');
122353c5 93
94my @t1_fields = $t1->get_fields;
c601ca5d 95is( scalar @t1_fields, 4, '4 fields in sqlt_test1' );
122353c5 96
97my $f1 = shift @t1_fields;
98is( $f1->name, 'f_serial', 'First field is "f_serial"' );
a23f9a9d 99is( $f1->data_type, 'integer', 'Field is an integer' );
122353c5 100is( $f1->is_nullable, 0, 'Field cannot be null' );
101is( $f1->default_value, "nextval('sqlt_test1_f_serial_seq'::regclass)", 'Default value is nextval()' );
102is( $f1->is_primary_key, 1, 'Field is PK' );
103#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() )
104#is( $f1->is_auto_increment, 1, 'Field is auto increment' );
105
106my $f2 = shift @t1_fields;
107is( $f2->name, 'f_varchar', 'Second field is "f_varchar"' );
a23f9a9d 108is( $f2->data_type, 'character varying(255)', 'Field is a character varying(255)' );
122353c5 109is( $f2->is_nullable, 1, 'Field can be null' );
110#FIXME: should not be 255?
111is( $f2->size, 259, 'Size is "259"' );
112is( $f2->default_value, undef, 'Default value is undefined' );
113is( $f2->is_primary_key, 0, 'Field is not PK' );
114is( $f2->is_auto_increment, 0, 'Field is not auto increment' );
fd52d7dd 115is( $f2->comments, '', 'There is no comment on the second field');
122353c5 116
117my $f3 = shift @t1_fields;
118is( $f3->name, 'f_text', 'Third field is "f_text"' );
119is( $f3->data_type, 'text', 'Field is a text' );
120is( $f3->is_nullable, 1, 'Field can be null' );
121is( $f3->size, 0, 'Size is 0' );
122is( $f3->default_value, "'FOO'::text", 'Default value is "FOO"' );
123is( $f3->is_primary_key, 0, 'Field is not PK' );
124is( $f3->is_auto_increment, 0, 'Field is not auto increment' );
fd52d7dd 125is( $f3->comments, 'this is a comment on a field of the first table', 'There is a comment on the third field');
122353c5 126
c601ca5d 127my $f4 = shift @t1_fields;
128is( $f4->name, 'f_last', 'Fouth field is "f_last"' );
129is( $f4->data_type, 'text', 'Field is a text' );
130is( $f4->is_nullable, 1, 'Field can be null' );
131is( $f4->size, 0, 'Size is 0' );
132is( $f4->default_value, undef, 'No default value' );
133is( $f4->is_primary_key, 0, 'Field is not PK' );
134is( $f4->is_auto_increment, 0, 'Field is not auto increment' );
135
122353c5 136#TODO: no 'NOT NULL' constraint not set
137
138my $t2 = $schema->get_table("sqlt_test2");
139is( $t2->name, 'sqlt_test2', 'Table sqlt_test2 exists' );
fd52d7dd 140is( $t2->comments, undef, 'No comment on table sqlt_test2');
122353c5 141
142my @t2_fields = $t2->get_fields;
143is( scalar @t2_fields, 3, '3 fields in sqlt_test2' );
144
145my $t2_f1 = shift @t2_fields;
146is( $t2_f1->name, 'f_id', 'First field is "f_id"' );
a23f9a9d 147is( $t2_f1->data_type, 'integer', 'Field is an integer' );
122353c5 148is( $t2_f1->is_nullable, 0, 'Field cannot be null' );
149is( $t2_f1->size, 0, 'Size is "0"' );
150is( $t2_f1->default_value, undef, 'Default value is undefined' );
151is( $t2_f1->is_primary_key, 1, 'Field is PK' );
152
153my $t2_f2= shift @t2_fields;
154is( $t2_f2->name, 'f_int', 'Third field is "f_int"' );
a23f9a9d 155is( $t2_f2->data_type, 'smallint', 'Field is an smallint' );
122353c5 156is( $t2_f2->is_nullable, 1, 'Field can be null' );
157is( $t2_f2->size, 0, 'Size is "0"' );
158is( $t2_f2->default_value, undef, 'Default value is undefined' );
159is( $t2_f2->is_primary_key, 0, 'Field is not PK' );
160
161my $t2_f3 = shift @t2_fields;
162is( $t2_f3->name, 'f_fk1', 'Third field is "f_fk1"' );
a23f9a9d 163is( $t2_f3->data_type, 'integer', 'Field is an integer' );
122353c5 164is( $t2_f3->is_nullable, 0, 'Field cannot be null' );
165is( $t2_f3->size, 0, 'Size is "0"' );
166is( $t2_f3->default_value, undef, 'Default value is undefined' );
167is( $t2_f3->is_primary_key, 0, 'Field is not PK' );
168is( $t2_f3->is_foreign_key, 1, 'Field is a FK' );
169my $fk_ref1 = $t2_f3->foreign_key_reference;
170isa_ok( $fk_ref1, 'SQL::Translator::Schema::Constraint', 'FK' );
171is( $fk_ref1->reference_table, 'sqlt_test1', 'FK is to "sqlt_test1" table' );
172
173my @t2_constraints = $t2->get_constraints;
174is( scalar @t2_constraints, 1, "One constraint on table" );
175
176my $t2_c1 = shift @t2_constraints;
177is( $t2_c1->type, FOREIGN_KEY, "Constraint is a FK" );
178
179$dbh->disconnect;
180} # end of SKIP block
181
182END {
183 if ($dbh) {
184 for (
185 'drop table if exists sqlt_test2',
186 'drop table if exists sqlt_test1',
187 'drop table if exists sqlt_products_1',
188 ) {
71fee1bc 189 local $SIG{__WARN__} = sub {};
122353c5 190 eval { $dbh->do($_) };
191 }
192 }
193}