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 'bit varying' => { data_type => 'varbit' },
86 'varbit' => { data_type => 'varbit' },
87 bit => { data_type => 'bit', size => 1 },
88 'bit(3)' => { data_type => 'bit', size => 3 },
91 inet => { data_type => 'inet' },
92 cidr => { data_type => 'cidr' },
93 macaddr => { data_type => 'macaddr' },
96 point => { data_type => 'point' },
97 line => { data_type => 'line' },
98 lseg => { data_type => 'lseg' },
99 box => { data_type => 'box' },
100 path => { data_type => 'path' },
101 polygon => { data_type => 'polygon' },
102 circle => { data_type => 'circle' },
105 'character varying(2)' => { data_type => 'varchar', size => 2 },
106 'varchar(2)' => { data_type => 'varchar', size => 2 },
107 'character(2)' => { data_type => 'char', size => 2 },
108 'char(2)' => { data_type => 'char', size => 2 },
109 # check that default null is correctly rewritten
110 'char(3) default null' => { data_type => 'char', size => 3,
111 default_value => \'null' },
112 'character' => { data_type => 'char', size => 1 },
113 'char' => { data_type => 'char', size => 1 },
114 text => { data_type => 'text' },
115 # varchar with no size has unlimited size, we rewrite to 'text'
116 varchar => { data_type => 'text',
117 original => { data_type => 'varchar' } },
118 # check default null again (to make sure ref is safe)
119 'varchar(3) default null' => { data_type => 'varchar', size => 3,
120 default_value => \'null' },
123 date => { data_type => 'date' },
124 interval => { data_type => 'interval' },
125 'interval(0)' => { data_type => 'interval', size => 0 },
126 'interval(2)' => { data_type => 'interval', size => 2 },
127 time => { data_type => 'time' },
128 'time(0)' => { data_type => 'time', size => 0 },
129 'time(2)' => { data_type => 'time', size => 2 },
130 'time without time zone' => { data_type => 'time' },
131 'time(0) without time zone' => { data_type => 'time', size => 0 },
132 'time with time zone' => { data_type => 'time with time zone' },
133 'time(0) with time zone' => { data_type => 'time with time zone', size => 0 },
134 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
135 timestamp => { data_type => 'timestamp' },
136 'timestamp default now()' => { data_type => 'timestamp',
137 default_value => \'current_timestamp',
138 original => { default_value => \'now()' } },
139 'timestamp(0)' => { data_type => 'timestamp', size => 0 },
140 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
141 'timestamp without time zone' => { data_type => 'timestamp' },
142 'timestamp(0) without time zone' => { data_type => 'timestamp', size => 0 },
143 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
145 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
146 'timestamp(0) with time zone' => { data_type => 'timestamp with time zone', size => 0 },
147 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
150 bytea => { data_type => 'bytea' },
153 pg_loader_test_enum => { data_type => 'enum',
154 extra => { custom_type_name => 'pg_loader_test_enum',
155 list => [ qw/foo bar baz/] } },
159 CREATE TYPE pg_loader_test_enum AS ENUM (
167 CREATE SCHEMA dbicsl_test
170 CREATE SEQUENCE dbicsl_test.myseq
173 CREATE TABLE pg_loader_test1 (
174 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
179 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
182 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
185 CREATE TABLE pg_loader_test2 (
186 id SERIAL PRIMARY KEY,
191 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'
194 CREATE SCHEMA "dbicsl-test"
197 CREATE TABLE "dbicsl-test".pg_loader_test4 (
198 id SERIAL PRIMARY KEY,
203 CREATE TABLE "dbicsl-test".pg_loader_test5 (
204 id SERIAL PRIMARY KEY,
206 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
207 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
211 CREATE SCHEMA "dbicsl.test"
214 CREATE TABLE "dbicsl.test".pg_loader_test5 (
215 pk SERIAL PRIMARY KEY,
217 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
218 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
222 CREATE TABLE "dbicsl.test".pg_loader_test6 (
223 id SERIAL PRIMARY KEY,
225 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
229 CREATE TYPE "dbicsl.test".pg_loader_test_enum2 AS ENUM ('wibble','wobble')
232 CREATE TABLE "dbicsl.test".pg_loader_test7 (
233 id SERIAL PRIMARY KEY,
234 value "dbicsl.test".pg_loader_test_enum2,
235 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
239 CREATE TABLE "dbicsl-test".pg_loader_test8 (
240 id SERIAL PRIMARY KEY,
242 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
245 # 4 through 8 are used for the multi-schema tests
247 create table pg_loader_test9 (
248 id bigserial primary key
252 create table pg_loader_test10 (
253 id bigserial primary key,
255 foreign key (nine_id) references pg_loader_test9(id)
256 on delete restrict on update set null deferrable
260 create view pg_loader_test11 as
261 select * from pg_loader_test1
264 create table pg_loader_test12 (
272 create unique index uniq_id_lc_name on pg_loader_test12 (
277 create unique index uniq_uc_name_id on pg_loader_test12 (
282 create unique index pg_loader_test12_value on pg_loader_test12 (
287 create unique index pg_loader_test12_name_active on pg_loader_test12 (
293 'DROP SCHEMA dbicsl_test CASCADE',
294 'DROP SCHEMA "dbicsl-test" CASCADE',
295 'DROP SCHEMA "dbicsl.test" CASCADE',
296 'DROP TYPE pg_loader_test_enum',
297 'DROP VIEW pg_loader_test11',
299 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
300 count => 11 + 33 * 2, # regular + multi-schema * 2
302 my ($schema, $monikers, $classes) = @_;
304 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
306 'qualified sequence detected';
308 my $class = $classes->{pg_loader_test1};
309 my $filename = $schema->loader->get_dump_filename($class);
311 my $code = slurp_file $filename;
313 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
316 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
317 'column comment and attrs';
319 $class = $classes->{pg_loader_test2};
320 $filename = $schema->loader->get_dump_filename($class);
322 $code = slurp_file $filename;
324 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,
325 'long table comment is in DESCRIPTION';
327 # test on delete/update fk clause introspection
328 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
331 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
332 'ON DELETE clause introspected correctly';
334 is $rel_info->{attrs}{on_update}, 'SET NULL',
335 'ON UPDATE clause introspected correctly';
337 is $rel_info->{attrs}{is_deferrable}, 1,
338 'DEFERRABLE clause introspected correctly';
340 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
342 rmtree EXTRA_DUMP_DIR;
345 local $SIG{__WARN__} = sub {
346 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
353 db_schema => $db_schema,
355 dump_directory => EXTRA_DUMP_DIR,
358 [ $dsn, $user, $password, {
359 on_connect_do => [ 'SET client_min_messages=WARNING' ],
363 diag join "\n", @warns if @warns;
366 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
368 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
371 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
372 on_connect_do => [ 'SET client_min_messages=WARNING' ],
374 } 'connected test schema';
377 ok $rsrc = $test_schema->source('PgLoaderTest4');
378 } 'got source for table in schema name with dash';
380 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
381 'column in schema name with dash';
383 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
384 'column in schema name with dash';
386 is try { $rsrc->column_info('value')->{size} }, 100,
387 'column in schema name with dash';
390 ok $rs = $test_schema->resultset('PgLoaderTest4');
391 } 'got resultset for table in schema name with dash';
394 ok $row = $rs->create({ value => 'foo' });
395 } 'executed SQL on table in schema name with dash';
397 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
399 is_deeply $rel_info->{cond}, {
400 'foreign.four_id' => 'self.id'
401 }, 'relationship in schema name with dash';
403 is $rel_info->{attrs}{accessor}, 'single',
404 'relationship in schema name with dash';
406 is $rel_info->{attrs}{join_type}, 'LEFT',
407 'relationship in schema name with dash';
410 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
411 } 'got source for table in schema name with dash';
413 %uniqs = try { $rsrc->unique_constraints };
416 'got unique and primary constraint in schema name with dash';
418 delete $uniqs{primary};
421 (values %uniqs)[0], ['four_id'],
422 'unique constraint is correct in schema name with dash'
426 ok $rsrc = $test_schema->source('PgLoaderTest6');
427 } 'got source for table in schema name with dot';
429 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
430 'column in schema name with dot introspected correctly';
432 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
433 'column in schema name with dot introspected correctly';
435 is try { $rsrc->column_info('value')->{size} }, 100,
436 'column in schema name with dot introspected correctly';
439 ok $rs = $test_schema->resultset('PgLoaderTest6');
440 } 'got resultset for table in schema name with dot';
443 ok $row = $rs->create({ value => 'foo' });
444 } 'executed SQL on table in schema name with dot';
446 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
448 is_deeply $rel_info->{cond}, {
449 'foreign.six_id' => 'self.id'
450 }, 'relationship in schema name with dot';
452 is $rel_info->{attrs}{accessor}, 'single',
453 'relationship in schema name with dot';
455 is $rel_info->{attrs}{join_type}, 'LEFT',
456 'relationship in schema name with dot';
459 ok $rsrc = $test_schema->source('PgLoaderTest7');
460 my $col_info = $rsrc->column_info('value');
461 is $col_info->{data_type}, 'enum',
462 'enum column in schema name with dot';
463 is $col_info->{extra}{custom_type_name}, '"dbicsl.test".pg_loader_test_enum2',
464 'original data type for enum in schema name with dot';
465 is_deeply $col_info->{extra}{list}, [qw(wibble wobble)],
466 'value list for for enum in schema name with dot';
467 } 'got source for table in schema name with dot';
469 %uniqs = try { $rsrc->unique_constraints };
472 'got unique and primary constraint in schema name with dot';
474 delete $uniqs{primary};
477 (values %uniqs)[0], ['six_id'],
478 'unique constraint is correct in schema name with dot'
482 ok $test_schema->source('PgLoaderTest6')
483 ->has_relationship('pg_loader_test4');
484 } 'cross-schema relationship in multi-db_schema';
487 ok $test_schema->source('PgLoaderTest4')
488 ->has_relationship('pg_loader_test6s');
489 } 'cross-schema relationship in multi-db_schema';
492 ok $test_schema->source('PgLoaderTest8')
493 ->has_relationship('pg_loader_test7');
494 } 'cross-schema relationship in multi-db_schema';
497 ok $test_schema->source('PgLoaderTest7')
498 ->has_relationship('pg_loader_test8s');
499 } 'cross-schema relationship in multi-db_schema';
502 # test that views are marked as such
503 my $view_source = $schema->resultset($monikers->{pg_loader_test11})->result_source;
504 isa_ok $view_source, 'DBIx::Class::ResultSource::View',
505 'view result source';
507 like $view_source->view_definition,
508 qr/\A \s* select\b .* \bfrom \s+ pg_loader_test1 \s* \z/imsx,
512 { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
513 { pg_loader_test12_value => ['value'] },
514 'unique indexes are dumped correctly';
520 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
522 # vim:et sw=4 sts=4 tw=0: