4 use DBIx::Class::Schema::Loader 'make_schema_at';
5 use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
9 use File::Path 'rmtree';
13 use dbixcsl_common_tests ();
14 use dbixcsl_test_dir '$tdir';
16 use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";
18 my $dsn = $ENV{DBICTEST_PG_DSN} || '';
19 my $user = $ENV{DBICTEST_PG_USER} || '';
20 my $password = $ENV{DBICTEST_PG_PASS} || '';
22 my $tester = dbixcsl_common_tests->new(
24 auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
27 password => $password,
28 loader_options => { preserve_case => 1 },
29 connect_info_opts => {
31 on_connect_do => [ 'SET client_min_messages=WARNING' ],
34 default_is_deferrable => 0,
35 default_on_clause => 'NO ACTION',
37 # http://www.postgresql.org/docs/7.4/interactive/datatype.html
40 boolean => { data_type => 'boolean' },
41 bool => { data_type => 'boolean' },
43 => { data_type => 'boolean', default_value => \'false' },
45 => { data_type => 'boolean', default_value => \'true' },
46 'bool default 0::bool'
47 => { data_type => 'boolean', default_value => \'false' },
48 'bool default 1::bool'
49 => { data_type => 'boolean', default_value => \'true' },
51 bigint => { data_type => 'bigint' },
52 int8 => { data_type => 'bigint' },
53 bigserial => { data_type => 'bigint', is_auto_increment => 1 },
54 serial8 => { data_type => 'bigint', is_auto_increment => 1 },
55 integer => { data_type => 'integer' },
56 int => { data_type => 'integer' },
57 int4 => { data_type => 'integer' },
58 serial => { data_type => 'integer', is_auto_increment => 1 },
59 serial4 => { data_type => 'integer', is_auto_increment => 1 },
60 smallint => { data_type => 'smallint' },
61 int2 => { data_type => 'smallint' },
63 money => { data_type => 'money' },
65 'double precision' => { data_type => 'double precision' },
66 float8 => { data_type => 'double precision' },
67 real => { data_type => 'real' },
68 float4 => { data_type => 'real' },
69 'float(24)' => { data_type => 'real' },
70 'float(25)' => { data_type => 'double precision' },
71 'float(53)' => { data_type => 'double precision' },
72 float => { data_type => 'double precision' },
74 numeric => { data_type => 'numeric' },
75 decimal => { data_type => 'numeric' },
76 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
77 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
80 'bit varying(2)' => { data_type => 'varbit', size => 2 },
81 'varbit(2)' => { data_type => 'varbit', size => 2 },
82 'varbit' => { data_type => 'varbit' },
83 bit => { data_type => 'bit', size => 1 },
84 'bit(3)' => { data_type => 'bit', size => 3 },
87 inet => { data_type => 'inet' },
88 cidr => { data_type => 'cidr' },
89 macaddr => { data_type => 'macaddr' },
92 point => { data_type => 'point' },
93 line => { data_type => 'line' },
94 lseg => { data_type => 'lseg' },
95 box => { data_type => 'box' },
96 path => { data_type => 'path' },
97 polygon => { data_type => 'polygon' },
98 circle => { data_type => 'circle' },
101 'character varying(2)' => { data_type => 'varchar', size => 2 },
102 'varchar(2)' => { data_type => 'varchar', size => 2 },
103 'character(2)' => { data_type => 'char', size => 2 },
104 'char(2)' => { data_type => 'char', size => 2 },
105 # check that default null is correctly rewritten
106 'char(3) default null' => { data_type => 'char', size => 3,
107 default_value => \'null' },
108 'character' => { data_type => 'char', size => 1 },
109 'char' => { data_type => 'char', size => 1 },
110 text => { data_type => 'text' },
111 # varchar with no size has unlimited size, we rewrite to 'text'
112 varchar => { data_type => 'text',
113 original => { data_type => 'varchar' } },
114 # check default null again (to make sure ref is safe)
115 'varchar(3) default null' => { data_type => 'varchar', size => 3,
116 default_value => \'null' },
119 date => { data_type => 'date' },
120 interval => { data_type => 'interval' },
121 'interval(2)' => { data_type => 'interval', size => 2 },
122 time => { data_type => 'time' },
123 'time(2)' => { data_type => 'time', size => 2 },
124 'time without time zone' => { data_type => 'time' },
125 'time(2) without time zone' => { data_type => 'time', size => 2 },
126 'time with time zone' => { data_type => 'time with time zone' },
127 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
128 timestamp => { data_type => 'timestamp' },
129 'timestamp default now()'
130 => { data_type => 'timestamp', default_value => \'current_timestamp',
131 original => { default_value => \'now()' } },
132 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
133 'timestamp without time zone' => { data_type => 'timestamp' },
134 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
136 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
137 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
140 bytea => { data_type => 'bytea' },
143 pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
144 list => [ qw/foo bar baz/] } },
148 CREATE TYPE pg_loader_test_enum AS ENUM (
156 CREATE SCHEMA dbicsl_test
159 CREATE SEQUENCE dbicsl_test.myseq
162 CREATE TABLE pg_loader_test1 (
163 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
168 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
171 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
174 CREATE TABLE pg_loader_test2 (
175 id SERIAL PRIMARY KEY,
180 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'
183 CREATE SCHEMA "dbicsl-test"
186 CREATE TABLE "dbicsl-test".pg_loader_test4 (
187 id SERIAL PRIMARY KEY,
192 CREATE TABLE "dbicsl-test".pg_loader_test5 (
193 id SERIAL PRIMARY KEY,
195 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
196 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
200 CREATE SCHEMA "dbicsl.test"
203 CREATE TABLE "dbicsl.test".pg_loader_test5 (
204 pk SERIAL PRIMARY KEY,
206 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
207 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
211 CREATE TABLE "dbicsl.test".pg_loader_test6 (
212 id SERIAL PRIMARY KEY,
214 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
218 CREATE TABLE "dbicsl.test".pg_loader_test7 (
219 id SERIAL PRIMARY KEY,
221 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
225 CREATE TABLE "dbicsl-test".pg_loader_test8 (
226 id SERIAL PRIMARY KEY,
228 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
231 # 4 through 8 are used for the multi-schema tests
233 create table pg_loader_test9 (
234 id bigserial primary key
238 create table pg_loader_test10 (
239 id bigserial primary key,
241 foreign key (nine_id) references pg_loader_test9(id)
242 on delete restrict on update set null deferrable
246 create view pg_loader_test11 as
247 select * from pg_loader_test1
250 create table pg_loader_test12 (
256 create unique index uniq_id_lc_name on pg_loader_test12 (
261 create unique index uniq_uc_name_id on pg_loader_test12 (
267 'DROP SCHEMA dbicsl_test CASCADE',
268 'DROP SCHEMA "dbicsl-test" CASCADE',
269 'DROP SCHEMA "dbicsl.test" CASCADE',
270 'DROP TYPE pg_loader_test_enum',
271 'DROP VIEW pg_loader_test11',
273 drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
274 count => 10 + 30 * 2,
276 my ($schema, $monikers, $classes) = @_;
278 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
280 'qualified sequence detected';
282 my $class = $classes->{pg_loader_test1};
283 my $filename = $schema->loader->get_dump_filename($class);
285 my $code = slurp_file $filename;
287 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
290 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
291 'column comment and attrs';
293 $class = $classes->{pg_loader_test2};
294 $filename = $schema->loader->get_dump_filename($class);
296 $code = slurp_file $filename;
298 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,
299 'long table comment is in DESCRIPTION';
301 # test on delete/update fk clause introspection
302 ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
305 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
306 'ON DELETE clause introspected correctly';
308 is $rel_info->{attrs}{on_update}, 'SET NULL',
309 'ON UPDATE clause introspected correctly';
311 is $rel_info->{attrs}{is_deferrable}, 1,
312 'DEFERRABLE clause introspected correctly';
314 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
316 rmtree EXTRA_DUMP_DIR;
319 local $SIG{__WARN__} = sub {
320 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
327 db_schema => $db_schema,
329 dump_directory => EXTRA_DUMP_DIR,
332 [ $dsn, $user, $password, {
333 on_connect_do => [ 'SET client_min_messages=WARNING' ],
337 diag join "\n", @warns if @warns;
340 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
342 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
345 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
346 on_connect_do => [ 'SET client_min_messages=WARNING' ],
348 } 'connected test schema';
351 ok $rsrc = $test_schema->source('PgLoaderTest4');
352 } 'got source for table in schema name with dash';
354 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
355 'column in schema name with dash';
357 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
358 'column in schema name with dash';
360 is try { $rsrc->column_info('value')->{size} }, 100,
361 'column in schema name with dash';
364 ok $rs = $test_schema->resultset('PgLoaderTest4');
365 } 'got resultset for table in schema name with dash';
368 ok $row = $rs->create({ value => 'foo' });
369 } 'executed SQL on table in schema name with dash';
371 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
373 is_deeply $rel_info->{cond}, {
374 'foreign.four_id' => 'self.id'
375 }, 'relationship in schema name with dash';
377 is $rel_info->{attrs}{accessor}, 'single',
378 'relationship in schema name with dash';
380 is $rel_info->{attrs}{join_type}, 'LEFT',
381 'relationship in schema name with dash';
384 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
385 } 'got source for table in schema name with dash';
387 %uniqs = try { $rsrc->unique_constraints };
390 'got unique and primary constraint in schema name with dash';
392 delete $uniqs{primary};
394 is_deeply ((values %uniqs)[0], ['four_id'],
395 'unique constraint is correct in schema name with dash');
398 ok $rsrc = $test_schema->source('PgLoaderTest6');
399 } 'got source for table in schema name with dot';
401 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
402 'column in schema name with dot introspected correctly';
404 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
405 'column in schema name with dot introspected correctly';
407 is try { $rsrc->column_info('value')->{size} }, 100,
408 'column in schema name with dot introspected correctly';
411 ok $rs = $test_schema->resultset('PgLoaderTest6');
412 } 'got resultset for table in schema name with dot';
415 ok $row = $rs->create({ value => 'foo' });
416 } 'executed SQL on table in schema name with dot';
418 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
420 is_deeply $rel_info->{cond}, {
421 'foreign.six_id' => 'self.id'
422 }, 'relationship in schema name with dot';
424 is $rel_info->{attrs}{accessor}, 'single',
425 'relationship in schema name with dot';
427 is $rel_info->{attrs}{join_type}, 'LEFT',
428 'relationship in schema name with dot';
431 ok $rsrc = $test_schema->source('PgLoaderTest7');
432 } 'got source for table in schema name with dot';
434 %uniqs = try { $rsrc->unique_constraints };
437 'got unique and primary constraint in schema name with dot';
439 delete $uniqs{primary};
441 is_deeply ((values %uniqs)[0], ['six_id'],
442 'unique constraint is correct in schema name with dot');
445 ok $test_schema->source('PgLoaderTest6')
446 ->has_relationship('pg_loader_test4');
447 } 'cross-schema relationship in multi-db_schema';
450 ok $test_schema->source('PgLoaderTest4')
451 ->has_relationship('pg_loader_test6s');
452 } 'cross-schema relationship in multi-db_schema';
455 ok $test_schema->source('PgLoaderTest8')
456 ->has_relationship('pg_loader_test7');
457 } 'cross-schema relationship in multi-db_schema';
460 ok $test_schema->source('PgLoaderTest7')
461 ->has_relationship('pg_loader_test8s');
462 } 'cross-schema relationship in multi-db_schema';
465 # test that views are marked as such
466 isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
467 'views have table_class set correctly';
469 is_deeply { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
470 {}, 'unique indexes with expressions are not dumped';
475 if( !$dsn || !$user ) {
476 $tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
479 $tester->run_tests();
483 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
485 # vim:et sw=4 sts=4 tw=0: