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()' => { data_type => 'timestamp',
133 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',
147 extra => { custom_type_name => 'pg_loader_test_enum',
148 list => [ qw/foo bar baz/] } },
152 CREATE TYPE pg_loader_test_enum AS ENUM (
160 CREATE SCHEMA dbicsl_test
163 CREATE SEQUENCE dbicsl_test.myseq
166 CREATE TABLE pg_loader_test1 (
167 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
172 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
175 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
178 CREATE TABLE pg_loader_test2 (
179 id SERIAL PRIMARY KEY,
184 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'
187 CREATE SCHEMA "dbicsl-test"
190 CREATE TABLE "dbicsl-test".pg_loader_test4 (
191 id SERIAL PRIMARY KEY,
196 CREATE TABLE "dbicsl-test".pg_loader_test5 (
197 id SERIAL PRIMARY KEY,
199 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
200 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
204 CREATE SCHEMA "dbicsl.test"
207 CREATE TABLE "dbicsl.test".pg_loader_test5 (
208 pk SERIAL PRIMARY KEY,
210 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
211 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
215 CREATE TABLE "dbicsl.test".pg_loader_test6 (
216 id SERIAL PRIMARY KEY,
218 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
222 CREATE TABLE "dbicsl.test".pg_loader_test7 (
223 id SERIAL PRIMARY KEY,
225 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
229 CREATE TABLE "dbicsl-test".pg_loader_test8 (
230 id SERIAL PRIMARY KEY,
232 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
235 # 4 through 8 are used for the multi-schema tests
237 create table pg_loader_test9 (
238 id bigserial primary key
242 create table pg_loader_test10 (
243 id bigserial primary key,
245 foreign key (nine_id) references pg_loader_test9(id)
246 on delete restrict on update set null deferrable
250 create view pg_loader_test11 as
251 select * from pg_loader_test1
254 create table pg_loader_test12 (
262 create unique index uniq_id_lc_name on pg_loader_test12 (
267 create unique index uniq_uc_name_id on pg_loader_test12 (
272 create unique index pg_loader_test12_value on pg_loader_test12 (
277 create unique index pg_loader_test12_name_active on pg_loader_test12 (
283 'DROP SCHEMA dbicsl_test CASCADE',
284 'DROP SCHEMA "dbicsl-test" CASCADE',
285 'DROP SCHEMA "dbicsl.test" CASCADE',
286 'DROP TYPE pg_loader_test_enum',
287 'DROP VIEW pg_loader_test11',
289 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
290 count => 10 + 30 * 2,
292 my ($schema, $monikers, $classes) = @_;
294 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
296 'qualified sequence detected';
298 my $class = $classes->{pg_loader_test1};
299 my $filename = $schema->loader->get_dump_filename($class);
301 my $code = slurp_file $filename;
303 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
306 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
307 'column comment and attrs';
309 $class = $classes->{pg_loader_test2};
310 $filename = $schema->loader->get_dump_filename($class);
312 $code = slurp_file $filename;
314 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,
315 'long table comment is in DESCRIPTION';
317 # test on delete/update fk clause introspection
318 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
321 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
322 'ON DELETE clause introspected correctly';
324 is $rel_info->{attrs}{on_update}, 'SET NULL',
325 'ON UPDATE clause introspected correctly';
327 is $rel_info->{attrs}{is_deferrable}, 1,
328 'DEFERRABLE clause introspected correctly';
330 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
332 rmtree EXTRA_DUMP_DIR;
335 local $SIG{__WARN__} = sub {
336 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
343 db_schema => $db_schema,
345 dump_directory => EXTRA_DUMP_DIR,
348 [ $dsn, $user, $password, {
349 on_connect_do => [ 'SET client_min_messages=WARNING' ],
353 diag join "\n", @warns if @warns;
356 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
358 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
361 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
362 on_connect_do => [ 'SET client_min_messages=WARNING' ],
364 } 'connected test schema';
367 ok $rsrc = $test_schema->source('PgLoaderTest4');
368 } 'got source for table in schema name with dash';
370 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
371 'column in schema name with dash';
373 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
374 'column in schema name with dash';
376 is try { $rsrc->column_info('value')->{size} }, 100,
377 'column in schema name with dash';
380 ok $rs = $test_schema->resultset('PgLoaderTest4');
381 } 'got resultset for table in schema name with dash';
384 ok $row = $rs->create({ value => 'foo' });
385 } 'executed SQL on table in schema name with dash';
387 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
389 is_deeply $rel_info->{cond}, {
390 'foreign.four_id' => 'self.id'
391 }, 'relationship in schema name with dash';
393 is $rel_info->{attrs}{accessor}, 'single',
394 'relationship in schema name with dash';
396 is $rel_info->{attrs}{join_type}, 'LEFT',
397 'relationship in schema name with dash';
400 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
401 } 'got source for table in schema name with dash';
403 %uniqs = try { $rsrc->unique_constraints };
406 'got unique and primary constraint in schema name with dash';
408 delete $uniqs{primary};
411 (values %uniqs)[0], ['four_id'],
412 'unique constraint is correct in schema name with dash'
416 ok $rsrc = $test_schema->source('PgLoaderTest6');
417 } 'got source for table in schema name with dot';
419 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
420 'column in schema name with dot introspected correctly';
422 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
423 'column in schema name with dot introspected correctly';
425 is try { $rsrc->column_info('value')->{size} }, 100,
426 'column in schema name with dot introspected correctly';
429 ok $rs = $test_schema->resultset('PgLoaderTest6');
430 } 'got resultset for table in schema name with dot';
433 ok $row = $rs->create({ value => 'foo' });
434 } 'executed SQL on table in schema name with dot';
436 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
438 is_deeply $rel_info->{cond}, {
439 'foreign.six_id' => 'self.id'
440 }, 'relationship in schema name with dot';
442 is $rel_info->{attrs}{accessor}, 'single',
443 'relationship in schema name with dot';
445 is $rel_info->{attrs}{join_type}, 'LEFT',
446 'relationship in schema name with dot';
449 ok $rsrc = $test_schema->source('PgLoaderTest7');
450 } 'got source for table in schema name with dot';
452 %uniqs = try { $rsrc->unique_constraints };
455 'got unique and primary constraint in schema name with dot';
457 delete $uniqs{primary};
460 (values %uniqs)[0], ['six_id'],
461 'unique constraint is correct in schema name with dot'
465 ok $test_schema->source('PgLoaderTest6')
466 ->has_relationship('pg_loader_test4');
467 } 'cross-schema relationship in multi-db_schema';
470 ok $test_schema->source('PgLoaderTest4')
471 ->has_relationship('pg_loader_test6s');
472 } 'cross-schema relationship in multi-db_schema';
475 ok $test_schema->source('PgLoaderTest8')
476 ->has_relationship('pg_loader_test7');
477 } 'cross-schema relationship in multi-db_schema';
480 ok $test_schema->source('PgLoaderTest7')
481 ->has_relationship('pg_loader_test8s');
482 } 'cross-schema relationship in multi-db_schema';
485 # test that views are marked as such
486 isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
487 'views have table_class set correctly';
490 { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
491 { pg_loader_test12_value => ['value'] },
492 'unique indexes are dumped correctly';
498 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
500 # vim:et sw=4 sts=4 tw=0: