4 use DBIx::Class::Optional::Dependencies;
5 use DBIx::Class::Schema::Loader 'make_schema_at';
6 use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
10 use File::Path 'rmtree';
14 use dbixcsl_common_tests ();
15 use dbixcsl_test_dir '$tdir';
17 use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";
19 my $dsn = $ENV{DBICTEST_PG_DSN} || '';
20 my $user = $ENV{DBICTEST_PG_USER} || '';
21 my $password = $ENV{DBICTEST_PG_PASS} || '';
23 my $tester = dbixcsl_common_tests->new(
25 auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
28 password => $password,
29 loader_options => { preserve_case => 1 },
30 connect_info_opts => {
32 on_connect_do => [ 'SET client_min_messages=WARNING' ],
35 default_is_deferrable => 0,
36 default_on_clause => 'NO ACTION',
38 # http://www.postgresql.org/docs/7.4/interactive/datatype.html
41 boolean => { data_type => 'boolean' },
42 bool => { data_type => 'boolean' },
44 => { data_type => 'boolean', default_value => \'false' },
46 => { data_type => 'boolean', default_value => \'true' },
47 'bool default 0::bool'
48 => { data_type => 'boolean', default_value => \'false' },
49 'bool default 1::bool'
50 => { data_type => 'boolean', default_value => \'true' },
52 bigint => { data_type => 'bigint' },
53 int8 => { data_type => 'bigint' },
54 bigserial => { data_type => 'bigint', is_auto_increment => 1 },
55 serial8 => { data_type => 'bigint', is_auto_increment => 1 },
56 integer => { data_type => 'integer' },
57 int => { data_type => 'integer' },
58 int4 => { data_type => 'integer' },
59 serial => { data_type => 'integer', is_auto_increment => 1 },
60 serial4 => { data_type => 'integer', is_auto_increment => 1 },
61 smallint => { data_type => 'smallint' },
62 int2 => { data_type => 'smallint' },
64 money => { data_type => 'money' },
66 'double precision' => { data_type => 'double precision' },
67 float8 => { data_type => 'double precision' },
68 real => { data_type => 'real' },
69 float4 => { data_type => 'real' },
70 'float(24)' => { data_type => 'real' },
71 'float(25)' => { data_type => 'double precision' },
72 'float(53)' => { data_type => 'double precision' },
73 float => { data_type => 'double precision' },
75 numeric => { data_type => 'numeric' },
76 decimal => { data_type => 'numeric' },
77 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
78 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
81 'bit varying(2)' => { data_type => 'varbit', size => 2 },
82 'varbit(2)' => { data_type => 'varbit', size => 2 },
83 'varbit' => { data_type => 'varbit' },
84 bit => { data_type => 'bit', size => 1 },
85 'bit(3)' => { data_type => 'bit', size => 3 },
88 inet => { data_type => 'inet' },
89 cidr => { data_type => 'cidr' },
90 macaddr => { data_type => 'macaddr' },
93 point => { data_type => 'point' },
94 line => { data_type => 'line' },
95 lseg => { data_type => 'lseg' },
96 box => { data_type => 'box' },
97 path => { data_type => 'path' },
98 polygon => { data_type => 'polygon' },
99 circle => { data_type => 'circle' },
102 'character varying(2)' => { data_type => 'varchar', size => 2 },
103 'varchar(2)' => { data_type => 'varchar', size => 2 },
104 'character(2)' => { data_type => 'char', size => 2 },
105 'char(2)' => { data_type => 'char', size => 2 },
106 # check that default null is correctly rewritten
107 'char(3) default null' => { data_type => 'char', size => 3,
108 default_value => \'null' },
109 'character' => { data_type => 'char', size => 1 },
110 'char' => { data_type => 'char', size => 1 },
111 text => { data_type => 'text' },
112 # varchar with no size has unlimited size, we rewrite to 'text'
113 varchar => { data_type => 'text',
114 original => { data_type => 'varchar' } },
115 # check default null again (to make sure ref is safe)
116 'varchar(3) default null' => { data_type => 'varchar', size => 3,
117 default_value => \'null' },
120 date => { data_type => 'date' },
121 interval => { data_type => 'interval' },
122 'interval(2)' => { data_type => 'interval', size => 2 },
123 time => { data_type => 'time' },
124 'time(2)' => { data_type => 'time', size => 2 },
125 'time without time zone' => { data_type => 'time' },
126 'time(2) without time zone' => { data_type => 'time', size => 2 },
127 'time with time zone' => { data_type => 'time with time zone' },
128 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
129 timestamp => { data_type => 'timestamp' },
130 'timestamp default now()'
131 => { data_type => 'timestamp', default_value => \'current_timestamp',
132 original => { default_value => \'now()' } },
133 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
134 'timestamp without time zone' => { data_type => 'timestamp' },
135 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
137 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
138 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
141 bytea => { data_type => 'bytea' },
144 pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
145 list => [ qw/foo bar baz/] } },
149 CREATE TYPE pg_loader_test_enum AS ENUM (
157 CREATE SCHEMA dbicsl_test
160 CREATE SEQUENCE dbicsl_test.myseq
163 CREATE TABLE pg_loader_test1 (
164 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
169 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
172 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
175 CREATE TABLE pg_loader_test2 (
176 id SERIAL PRIMARY KEY,
181 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'
184 CREATE SCHEMA "dbicsl-test"
187 CREATE TABLE "dbicsl-test".pg_loader_test4 (
188 id SERIAL PRIMARY KEY,
193 CREATE TABLE "dbicsl-test".pg_loader_test5 (
194 id SERIAL PRIMARY KEY,
196 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
197 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
201 CREATE SCHEMA "dbicsl.test"
204 CREATE TABLE "dbicsl.test".pg_loader_test5 (
205 pk SERIAL PRIMARY KEY,
207 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
208 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
212 CREATE TABLE "dbicsl.test".pg_loader_test6 (
213 id SERIAL PRIMARY KEY,
215 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
219 CREATE TABLE "dbicsl.test".pg_loader_test7 (
220 id SERIAL PRIMARY KEY,
222 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
226 CREATE TABLE "dbicsl-test".pg_loader_test8 (
227 id SERIAL PRIMARY KEY,
229 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
232 # 4 through 8 are used for the multi-schema tests
234 create table pg_loader_test9 (
235 id bigserial primary key
239 create table pg_loader_test10 (
240 id bigserial primary key,
242 foreign key (nine_id) references pg_loader_test9(id)
243 on delete restrict on update set null deferrable
247 create view pg_loader_test11 as
248 select * from pg_loader_test1
251 create table pg_loader_test12 (
259 create unique index uniq_id_lc_name on pg_loader_test12 (
264 create unique index uniq_uc_name_id on pg_loader_test12 (
269 create unique index pg_loader_test12_value on pg_loader_test12 (
274 create unique index pg_loader_test12_name_active on pg_loader_test12 (
280 'DROP SCHEMA dbicsl_test CASCADE',
281 'DROP SCHEMA "dbicsl-test" CASCADE',
282 'DROP SCHEMA "dbicsl.test" CASCADE',
283 'DROP TYPE pg_loader_test_enum',
284 'DROP VIEW pg_loader_test11',
286 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
287 count => 10 + 30 * 2,
289 my ($schema, $monikers, $classes) = @_;
291 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
293 'qualified sequence detected';
295 my $class = $classes->{pg_loader_test1};
296 my $filename = $schema->loader->get_dump_filename($class);
298 my $code = slurp_file $filename;
300 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
303 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
304 'column comment and attrs';
306 $class = $classes->{pg_loader_test2};
307 $filename = $schema->loader->get_dump_filename($class);
309 $code = slurp_file $filename;
311 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,
312 'long table comment is in DESCRIPTION';
314 # test on delete/update fk clause introspection
315 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
318 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
319 'ON DELETE clause introspected correctly';
321 is $rel_info->{attrs}{on_update}, 'SET NULL',
322 'ON UPDATE clause introspected correctly';
324 is $rel_info->{attrs}{is_deferrable}, 1,
325 'DEFERRABLE clause introspected correctly';
327 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
329 rmtree EXTRA_DUMP_DIR;
332 local $SIG{__WARN__} = sub {
333 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
340 db_schema => $db_schema,
342 dump_directory => EXTRA_DUMP_DIR,
345 [ $dsn, $user, $password, {
346 on_connect_do => [ 'SET client_min_messages=WARNING' ],
350 diag join "\n", @warns if @warns;
353 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
355 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
358 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
359 on_connect_do => [ 'SET client_min_messages=WARNING' ],
361 } 'connected test schema';
364 ok $rsrc = $test_schema->source('PgLoaderTest4');
365 } 'got source for table in schema name with dash';
367 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
368 'column in schema name with dash';
370 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
371 'column in schema name with dash';
373 is try { $rsrc->column_info('value')->{size} }, 100,
374 'column in schema name with dash';
377 ok $rs = $test_schema->resultset('PgLoaderTest4');
378 } 'got resultset for table in schema name with dash';
381 ok $row = $rs->create({ value => 'foo' });
382 } 'executed SQL on table in schema name with dash';
384 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
386 is_deeply $rel_info->{cond}, {
387 'foreign.four_id' => 'self.id'
388 }, 'relationship in schema name with dash';
390 is $rel_info->{attrs}{accessor}, 'single',
391 'relationship in schema name with dash';
393 is $rel_info->{attrs}{join_type}, 'LEFT',
394 'relationship in schema name with dash';
397 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
398 } 'got source for table in schema name with dash';
400 %uniqs = try { $rsrc->unique_constraints };
403 'got unique and primary constraint in schema name with dash';
405 delete $uniqs{primary};
407 is_deeply ((values %uniqs)[0], ['four_id'],
408 'unique constraint is correct in schema name with dash');
411 ok $rsrc = $test_schema->source('PgLoaderTest6');
412 } 'got source for table in schema name with dot';
414 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
415 'column in schema name with dot introspected correctly';
417 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
418 'column in schema name with dot introspected correctly';
420 is try { $rsrc->column_info('value')->{size} }, 100,
421 'column in schema name with dot introspected correctly';
424 ok $rs = $test_schema->resultset('PgLoaderTest6');
425 } 'got resultset for table in schema name with dot';
428 ok $row = $rs->create({ value => 'foo' });
429 } 'executed SQL on table in schema name with dot';
431 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
433 is_deeply $rel_info->{cond}, {
434 'foreign.six_id' => 'self.id'
435 }, 'relationship in schema name with dot';
437 is $rel_info->{attrs}{accessor}, 'single',
438 'relationship in schema name with dot';
440 is $rel_info->{attrs}{join_type}, 'LEFT',
441 'relationship in schema name with dot';
444 ok $rsrc = $test_schema->source('PgLoaderTest7');
445 } 'got source for table in schema name with dot';
447 %uniqs = try { $rsrc->unique_constraints };
450 'got unique and primary constraint in schema name with dot';
452 delete $uniqs{primary};
454 is_deeply ((values %uniqs)[0], ['six_id'],
455 'unique constraint is correct in schema name with dot');
458 ok $test_schema->source('PgLoaderTest6')
459 ->has_relationship('pg_loader_test4');
460 } 'cross-schema relationship in multi-db_schema';
463 ok $test_schema->source('PgLoaderTest4')
464 ->has_relationship('pg_loader_test6s');
465 } 'cross-schema relationship in multi-db_schema';
468 ok $test_schema->source('PgLoaderTest8')
469 ->has_relationship('pg_loader_test7');
470 } 'cross-schema relationship in multi-db_schema';
473 ok $test_schema->source('PgLoaderTest7')
474 ->has_relationship('pg_loader_test8s');
475 } 'cross-schema relationship in multi-db_schema';
478 # test that views are marked as such
479 isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
480 'views have table_class set correctly';
483 { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
484 { pg_loader_test12_value => ['value'] },
485 'unique indexes are dumped correctly';
490 if( !$dsn || !$user ) {
491 $tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
493 elsif (!DBIx::Class::Optional::Dependencies->req_ok_for ('rdbms_pg')) {
494 $tester->skip_tests('You need to install ' . DBIx::Class::Optional::Dependencies->req_missing_for ('rdbms_pg'));
497 $tester->run_tests();
501 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
503 # vim:et sw=4 sts=4 tw=0: