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(2)' => { data_type => 'interval', size => 2 },
125 time => { data_type => 'time' },
126 'time(2)' => { data_type => 'time', size => 2 },
127 'time without time zone' => { data_type => 'time' },
128 'time(2) without time zone' => { data_type => 'time', size => 2 },
129 'time with time zone' => { data_type => 'time with time zone' },
130 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
131 timestamp => { data_type => 'timestamp' },
132 'timestamp default now()'
133 => { data_type => 'timestamp', default_value => \'current_timestamp',
134 original => { default_value => \'now()' } },
135 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
136 'timestamp without time zone' => { data_type => 'timestamp' },
137 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
139 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
140 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
143 bytea => { data_type => 'bytea' },
146 pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
147 list => [ qw/foo bar baz/] } },
151 CREATE TYPE pg_loader_test_enum AS ENUM (
159 CREATE SCHEMA dbicsl_test
162 CREATE SEQUENCE dbicsl_test.myseq
165 CREATE TABLE pg_loader_test1 (
166 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
171 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
174 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
177 CREATE TABLE pg_loader_test2 (
178 id SERIAL PRIMARY KEY,
183 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'
186 CREATE SCHEMA "dbicsl-test"
189 CREATE TABLE "dbicsl-test".pg_loader_test4 (
190 id SERIAL PRIMARY KEY,
195 CREATE TABLE "dbicsl-test".pg_loader_test5 (
196 id SERIAL PRIMARY KEY,
198 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
199 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
203 CREATE SCHEMA "dbicsl.test"
206 CREATE TABLE "dbicsl.test".pg_loader_test5 (
207 pk SERIAL PRIMARY KEY,
209 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
210 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
214 CREATE TABLE "dbicsl.test".pg_loader_test6 (
215 id SERIAL PRIMARY KEY,
217 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
221 CREATE TABLE "dbicsl.test".pg_loader_test7 (
222 id SERIAL PRIMARY KEY,
224 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
228 CREATE TABLE "dbicsl-test".pg_loader_test8 (
229 id SERIAL PRIMARY KEY,
231 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
234 # 4 through 8 are used for the multi-schema tests
236 create table pg_loader_test9 (
237 id bigserial primary key
241 create table pg_loader_test10 (
242 id bigserial primary key,
244 foreign key (nine_id) references pg_loader_test9(id)
245 on delete restrict on update set null deferrable
249 create view pg_loader_test11 as
250 select * from pg_loader_test1
253 create table pg_loader_test12 (
261 create unique index uniq_id_lc_name on pg_loader_test12 (
266 create unique index uniq_uc_name_id on pg_loader_test12 (
271 create unique index pg_loader_test12_value on pg_loader_test12 (
276 create unique index pg_loader_test12_name_active on pg_loader_test12 (
282 'DROP SCHEMA dbicsl_test CASCADE',
283 'DROP SCHEMA "dbicsl-test" CASCADE',
284 'DROP SCHEMA "dbicsl.test" CASCADE',
285 'DROP TYPE pg_loader_test_enum',
286 'DROP VIEW pg_loader_test11',
288 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
289 count => 10 + 30 * 2,
291 my ($schema, $monikers, $classes) = @_;
293 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
295 'qualified sequence detected';
297 my $class = $classes->{pg_loader_test1};
298 my $filename = $schema->loader->get_dump_filename($class);
300 my $code = slurp_file $filename;
302 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
305 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
306 'column comment and attrs';
308 $class = $classes->{pg_loader_test2};
309 $filename = $schema->loader->get_dump_filename($class);
311 $code = slurp_file $filename;
313 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,
314 'long table comment is in DESCRIPTION';
316 # test on delete/update fk clause introspection
317 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
320 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
321 'ON DELETE clause introspected correctly';
323 is $rel_info->{attrs}{on_update}, 'SET NULL',
324 'ON UPDATE clause introspected correctly';
326 is $rel_info->{attrs}{is_deferrable}, 1,
327 'DEFERRABLE clause introspected correctly';
329 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
331 rmtree EXTRA_DUMP_DIR;
334 local $SIG{__WARN__} = sub {
335 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
342 db_schema => $db_schema,
344 dump_directory => EXTRA_DUMP_DIR,
347 [ $dsn, $user, $password, {
348 on_connect_do => [ 'SET client_min_messages=WARNING' ],
352 diag join "\n", @warns if @warns;
355 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
357 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
360 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
361 on_connect_do => [ 'SET client_min_messages=WARNING' ],
363 } 'connected test schema';
366 ok $rsrc = $test_schema->source('PgLoaderTest4');
367 } 'got source for table in schema name with dash';
369 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
370 'column in schema name with dash';
372 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
373 'column in schema name with dash';
375 is try { $rsrc->column_info('value')->{size} }, 100,
376 'column in schema name with dash';
379 ok $rs = $test_schema->resultset('PgLoaderTest4');
380 } 'got resultset for table in schema name with dash';
383 ok $row = $rs->create({ value => 'foo' });
384 } 'executed SQL on table in schema name with dash';
386 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
388 is_deeply $rel_info->{cond}, {
389 'foreign.four_id' => 'self.id'
390 }, 'relationship in schema name with dash';
392 is $rel_info->{attrs}{accessor}, 'single',
393 'relationship in schema name with dash';
395 is $rel_info->{attrs}{join_type}, 'LEFT',
396 'relationship in schema name with dash';
399 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
400 } 'got source for table in schema name with dash';
402 %uniqs = try { $rsrc->unique_constraints };
405 'got unique and primary constraint in schema name with dash';
407 delete $uniqs{primary};
409 is_deeply ((values %uniqs)[0], ['four_id'],
410 'unique constraint is correct in schema name with dash');
413 ok $rsrc = $test_schema->source('PgLoaderTest6');
414 } 'got source for table in schema name with dot';
416 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
417 'column in schema name with dot introspected correctly';
419 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
420 'column in schema name with dot introspected correctly';
422 is try { $rsrc->column_info('value')->{size} }, 100,
423 'column in schema name with dot introspected correctly';
426 ok $rs = $test_schema->resultset('PgLoaderTest6');
427 } 'got resultset for table in schema name with dot';
430 ok $row = $rs->create({ value => 'foo' });
431 } 'executed SQL on table in schema name with dot';
433 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
435 is_deeply $rel_info->{cond}, {
436 'foreign.six_id' => 'self.id'
437 }, 'relationship in schema name with dot';
439 is $rel_info->{attrs}{accessor}, 'single',
440 'relationship in schema name with dot';
442 is $rel_info->{attrs}{join_type}, 'LEFT',
443 'relationship in schema name with dot';
446 ok $rsrc = $test_schema->source('PgLoaderTest7');
447 } 'got source for table in schema name with dot';
449 %uniqs = try { $rsrc->unique_constraints };
452 'got unique and primary constraint in schema name with dot';
454 delete $uniqs{primary};
456 is_deeply ((values %uniqs)[0], ['six_id'],
457 'unique constraint is correct in schema name with dot');
460 ok $test_schema->source('PgLoaderTest6')
461 ->has_relationship('pg_loader_test4');
462 } 'cross-schema relationship in multi-db_schema';
465 ok $test_schema->source('PgLoaderTest4')
466 ->has_relationship('pg_loader_test6s');
467 } 'cross-schema relationship in multi-db_schema';
470 ok $test_schema->source('PgLoaderTest8')
471 ->has_relationship('pg_loader_test7');
472 } 'cross-schema relationship in multi-db_schema';
475 ok $test_schema->source('PgLoaderTest7')
476 ->has_relationship('pg_loader_test8s');
477 } 'cross-schema relationship in multi-db_schema';
480 # test that views are marked as such
481 isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
482 'views have table_class set correctly';
485 { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
486 { pg_loader_test12_value => ['value'] },
487 'unique indexes are dumped correctly';
493 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
495 # vim:et sw=4 sts=4 tw=0: