1 use DBIx::Class::Schema::Loader::Optional::Dependencies
2 -skip_all_without => 'test_rdbms_pg';
7 use DBIx::Class::Schema::Loader 'make_schema_at';
8 use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
12 use File::Path 'rmtree';
16 use dbixcsl_common_tests ();
17 use dbixcsl_test_dir '$tdir';
19 use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";
21 my $dsn = $ENV{DBICTEST_PG_DSN} || '';
22 my $user = $ENV{DBICTEST_PG_USER} || '';
23 my $password = $ENV{DBICTEST_PG_PASS} || '';
25 dbixcsl_common_tests->new(
27 auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
30 password => $password,
31 loader_options => { preserve_case => 1 },
32 connect_info_opts => {
34 on_connect_do => [ 'SET client_min_messages=WARNING' ],
37 default_is_deferrable => 0,
38 default_on_clause => 'NO ACTION',
40 # http://www.postgresql.org/docs/7.4/interactive/datatype.html
43 boolean => { data_type => 'boolean' },
44 bool => { data_type => 'boolean' },
46 => { data_type => 'boolean', default_value => \'false' },
48 => { data_type => 'boolean', default_value => \'true' },
49 'bool default 0::bool'
50 => { data_type => 'boolean', default_value => \'false' },
51 'bool default 1::bool'
52 => { data_type => 'boolean', default_value => \'true' },
54 bigint => { data_type => 'bigint' },
55 int8 => { data_type => 'bigint' },
56 bigserial => { data_type => 'bigint', is_auto_increment => 1 },
57 serial8 => { data_type => 'bigint', is_auto_increment => 1 },
58 integer => { data_type => 'integer' },
59 int => { data_type => 'integer' },
60 int4 => { data_type => 'integer' },
61 serial => { data_type => 'integer', is_auto_increment => 1 },
62 serial4 => { data_type => 'integer', is_auto_increment => 1 },
63 smallint => { data_type => 'smallint' },
64 int2 => { data_type => 'smallint' },
66 money => { data_type => 'money' },
68 'double precision' => { data_type => 'double precision' },
69 float8 => { data_type => 'double precision' },
70 real => { data_type => 'real' },
71 float4 => { data_type => 'real' },
72 'float(24)' => { data_type => 'real' },
73 'float(25)' => { data_type => 'double precision' },
74 'float(53)' => { data_type => 'double precision' },
75 float => { data_type => 'double precision' },
77 numeric => { data_type => 'numeric' },
78 decimal => { data_type => 'numeric' },
79 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
80 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
83 'bit varying(2)' => { data_type => 'varbit', size => 2 },
84 'varbit(2)' => { data_type => 'varbit', size => 2 },
85 'varbit' => { data_type => 'varbit' },
86 bit => { data_type => 'bit', size => 1 },
87 'bit(3)' => { data_type => 'bit', size => 3 },
90 inet => { data_type => 'inet' },
91 cidr => { data_type => 'cidr' },
92 macaddr => { data_type => 'macaddr' },
95 point => { data_type => 'point' },
96 line => { data_type => 'line' },
97 lseg => { data_type => 'lseg' },
98 box => { data_type => 'box' },
99 path => { data_type => 'path' },
100 polygon => { data_type => 'polygon' },
101 circle => { data_type => 'circle' },
104 'character varying(2)' => { data_type => 'varchar', size => 2 },
105 'varchar(2)' => { data_type => 'varchar', size => 2 },
106 'character(2)' => { data_type => 'char', size => 2 },
107 'char(2)' => { data_type => 'char', size => 2 },
108 # check that default null is correctly rewritten
109 'char(3) default null' => { data_type => 'char', size => 3,
110 default_value => \'null' },
111 'character' => { data_type => 'char', size => 1 },
112 'char' => { data_type => 'char', size => 1 },
113 text => { data_type => 'text' },
114 # varchar with no size has unlimited size, we rewrite to 'text'
115 varchar => { data_type => 'text',
116 original => { data_type => 'varchar' } },
117 # check default null again (to make sure ref is safe)
118 'varchar(3) default null' => { data_type => 'varchar', size => 3,
119 default_value => \'null' },
122 date => { data_type => 'date' },
123 interval => { data_type => 'interval' },
124 'interval(0)' => { data_type => 'interval', size => 0 },
125 'interval(2)' => { data_type => 'interval', size => 2 },
126 time => { data_type => 'time' },
127 'time(0)' => { data_type => 'time', size => 0 },
128 'time(2)' => { data_type => 'time', size => 2 },
129 'time without time zone' => { data_type => 'time' },
130 'time(0) without time zone' => { data_type => 'time', size => 0 },
131 'time with time zone' => { data_type => 'time with time zone' },
132 'time(0) with time zone' => { data_type => 'time with time zone', size => 0 },
133 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
134 timestamp => { data_type => 'timestamp' },
135 'timestamp default now()' => { data_type => 'timestamp',
136 default_value => \'current_timestamp',
137 original => { default_value => \'now()' } },
138 'timestamp(0)' => { data_type => 'timestamp', size => 0 },
139 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
140 'timestamp without time zone' => { data_type => 'timestamp' },
141 'timestamp(0) without time zone' => { data_type => 'timestamp', size => 0 },
142 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
144 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
145 'timestamp(0) with time zone' => { data_type => 'timestamp with time zone', size => 0 },
146 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
149 bytea => { data_type => 'bytea' },
152 pg_loader_test_enum => { data_type => 'enum',
153 extra => { custom_type_name => 'pg_loader_test_enum',
154 list => [ qw/foo bar baz/] } },
158 CREATE TYPE pg_loader_test_enum AS ENUM (
166 CREATE SCHEMA dbicsl_test
169 CREATE SEQUENCE dbicsl_test.myseq
172 CREATE TABLE pg_loader_test1 (
173 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
178 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
181 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
184 CREATE TABLE pg_loader_test2 (
185 id SERIAL PRIMARY KEY,
190 COMMENT ON TABLE pg_loader_test2 IS 'very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment'
193 CREATE SCHEMA "dbicsl-test"
196 CREATE TABLE "dbicsl-test".pg_loader_test4 (
197 id SERIAL PRIMARY KEY,
202 CREATE TABLE "dbicsl-test".pg_loader_test5 (
203 id SERIAL PRIMARY KEY,
205 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
206 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
210 CREATE SCHEMA "dbicsl.test"
213 CREATE TABLE "dbicsl.test".pg_loader_test5 (
214 pk SERIAL PRIMARY KEY,
216 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
217 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
221 CREATE TABLE "dbicsl.test".pg_loader_test6 (
222 id SERIAL PRIMARY KEY,
224 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
228 CREATE TABLE "dbicsl.test".pg_loader_test7 (
229 id SERIAL PRIMARY KEY,
231 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
235 CREATE TABLE "dbicsl-test".pg_loader_test8 (
236 id SERIAL PRIMARY KEY,
238 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
241 # 4 through 8 are used for the multi-schema tests
243 create table pg_loader_test9 (
244 id bigserial primary key
248 create table pg_loader_test10 (
249 id bigserial primary key,
251 foreign key (nine_id) references pg_loader_test9(id)
252 on delete restrict on update set null deferrable
256 create view pg_loader_test11 as
257 select * from pg_loader_test1
260 create table pg_loader_test12 (
268 create unique index uniq_id_lc_name on pg_loader_test12 (
273 create unique index uniq_uc_name_id on pg_loader_test12 (
278 create unique index pg_loader_test12_value on pg_loader_test12 (
283 create unique index pg_loader_test12_name_active on pg_loader_test12 (
289 'DROP SCHEMA dbicsl_test CASCADE',
290 'DROP SCHEMA "dbicsl-test" CASCADE',
291 'DROP SCHEMA "dbicsl.test" CASCADE',
292 'DROP TYPE pg_loader_test_enum',
293 'DROP VIEW pg_loader_test11',
295 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
296 count => 10 + 30 * 2,
298 my ($schema, $monikers, $classes) = @_;
300 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
302 'qualified sequence detected';
304 my $class = $classes->{pg_loader_test1};
305 my $filename = $schema->loader->get_dump_filename($class);
307 my $code = slurp_file $filename;
309 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
312 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
313 'column comment and attrs';
315 $class = $classes->{pg_loader_test2};
316 $filename = $schema->loader->get_dump_filename($class);
318 $code = slurp_file $filename;
320 like $code, qr/^=head1 NAME\n\n^$class\n\n=head1 DESCRIPTION\n\n^very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment\n\n^=cut\n/m,
321 'long table comment is in DESCRIPTION';
323 # test on delete/update fk clause introspection
324 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
327 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
328 'ON DELETE clause introspected correctly';
330 is $rel_info->{attrs}{on_update}, 'SET NULL',
331 'ON UPDATE clause introspected correctly';
333 is $rel_info->{attrs}{is_deferrable}, 1,
334 'DEFERRABLE clause introspected correctly';
336 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
338 rmtree EXTRA_DUMP_DIR;
341 local $SIG{__WARN__} = sub {
342 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
349 db_schema => $db_schema,
351 dump_directory => EXTRA_DUMP_DIR,
354 [ $dsn, $user, $password, {
355 on_connect_do => [ 'SET client_min_messages=WARNING' ],
359 diag join "\n", @warns if @warns;
362 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
364 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
367 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
368 on_connect_do => [ 'SET client_min_messages=WARNING' ],
370 } 'connected test schema';
373 ok $rsrc = $test_schema->source('PgLoaderTest4');
374 } 'got source for table in schema name with dash';
376 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
377 'column in schema name with dash';
379 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
380 'column in schema name with dash';
382 is try { $rsrc->column_info('value')->{size} }, 100,
383 'column in schema name with dash';
386 ok $rs = $test_schema->resultset('PgLoaderTest4');
387 } 'got resultset for table in schema name with dash';
390 ok $row = $rs->create({ value => 'foo' });
391 } 'executed SQL on table in schema name with dash';
393 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
395 is_deeply $rel_info->{cond}, {
396 'foreign.four_id' => 'self.id'
397 }, 'relationship in schema name with dash';
399 is $rel_info->{attrs}{accessor}, 'single',
400 'relationship in schema name with dash';
402 is $rel_info->{attrs}{join_type}, 'LEFT',
403 'relationship in schema name with dash';
406 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
407 } 'got source for table in schema name with dash';
409 %uniqs = try { $rsrc->unique_constraints };
412 'got unique and primary constraint in schema name with dash';
414 delete $uniqs{primary};
417 (values %uniqs)[0], ['four_id'],
418 'unique constraint is correct in schema name with dash'
422 ok $rsrc = $test_schema->source('PgLoaderTest6');
423 } 'got source for table in schema name with dot';
425 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
426 'column in schema name with dot introspected correctly';
428 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
429 'column in schema name with dot introspected correctly';
431 is try { $rsrc->column_info('value')->{size} }, 100,
432 'column in schema name with dot introspected correctly';
435 ok $rs = $test_schema->resultset('PgLoaderTest6');
436 } 'got resultset for table in schema name with dot';
439 ok $row = $rs->create({ value => 'foo' });
440 } 'executed SQL on table in schema name with dot';
442 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
444 is_deeply $rel_info->{cond}, {
445 'foreign.six_id' => 'self.id'
446 }, 'relationship in schema name with dot';
448 is $rel_info->{attrs}{accessor}, 'single',
449 'relationship in schema name with dot';
451 is $rel_info->{attrs}{join_type}, 'LEFT',
452 'relationship in schema name with dot';
455 ok $rsrc = $test_schema->source('PgLoaderTest7');
456 } 'got source for table in schema name with dot';
458 %uniqs = try { $rsrc->unique_constraints };
461 'got unique and primary constraint in schema name with dot';
463 delete $uniqs{primary};
466 (values %uniqs)[0], ['six_id'],
467 'unique constraint is correct in schema name with dot'
471 ok $test_schema->source('PgLoaderTest6')
472 ->has_relationship('pg_loader_test4');
473 } 'cross-schema relationship in multi-db_schema';
476 ok $test_schema->source('PgLoaderTest4')
477 ->has_relationship('pg_loader_test6s');
478 } 'cross-schema relationship in multi-db_schema';
481 ok $test_schema->source('PgLoaderTest8')
482 ->has_relationship('pg_loader_test7');
483 } 'cross-schema relationship in multi-db_schema';
486 ok $test_schema->source('PgLoaderTest7')
487 ->has_relationship('pg_loader_test8s');
488 } 'cross-schema relationship in multi-db_schema';
491 # test that views are marked as such
492 isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
493 'views have table_class set correctly';
496 { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
497 { pg_loader_test12_value => ['value'] },
498 'unique indexes are dumped correctly';
504 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
506 # vim:et sw=4 sts=4 tw=0: