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' ],
35 # http://www.postgresql.org/docs/7.4/interactive/datatype.html
38 boolean => { data_type => 'boolean' },
39 bool => { data_type => 'boolean' },
41 => { data_type => 'boolean', default_value => \'false' },
43 => { data_type => 'boolean', default_value => \'true' },
44 'bool default 0::bool'
45 => { data_type => 'boolean', default_value => \'false' },
46 'bool default 1::bool'
47 => { data_type => 'boolean', default_value => \'true' },
49 bigint => { data_type => 'bigint' },
50 int8 => { data_type => 'bigint' },
51 bigserial => { data_type => 'bigint', is_auto_increment => 1 },
52 serial8 => { data_type => 'bigint', is_auto_increment => 1 },
53 integer => { data_type => 'integer' },
54 int => { data_type => 'integer' },
55 int4 => { data_type => 'integer' },
56 serial => { data_type => 'integer', is_auto_increment => 1 },
57 serial4 => { data_type => 'integer', is_auto_increment => 1 },
58 smallint => { data_type => 'smallint' },
59 int2 => { data_type => 'smallint' },
61 money => { data_type => 'money' },
63 'double precision' => { data_type => 'double precision' },
64 float8 => { data_type => 'double precision' },
65 real => { data_type => 'real' },
66 float4 => { data_type => 'real' },
67 'float(24)' => { data_type => 'real' },
68 'float(25)' => { data_type => 'double precision' },
69 'float(53)' => { data_type => 'double precision' },
70 float => { data_type => 'double precision' },
72 numeric => { data_type => 'numeric' },
73 decimal => { data_type => 'numeric' },
74 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
75 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
78 'bit varying(2)' => { data_type => 'varbit', size => 2 },
79 'varbit(2)' => { data_type => 'varbit', size => 2 },
80 'varbit' => { data_type => 'varbit' },
81 bit => { data_type => 'bit', size => 1 },
82 'bit(3)' => { data_type => 'bit', size => 3 },
85 inet => { data_type => 'inet' },
86 cidr => { data_type => 'cidr' },
87 macaddr => { data_type => 'macaddr' },
90 point => { data_type => 'point' },
91 line => { data_type => 'line' },
92 lseg => { data_type => 'lseg' },
93 box => { data_type => 'box' },
94 path => { data_type => 'path' },
95 polygon => { data_type => 'polygon' },
96 circle => { data_type => 'circle' },
99 'character varying(2)' => { data_type => 'varchar', size => 2 },
100 'varchar(2)' => { data_type => 'varchar', size => 2 },
101 'character(2)' => { data_type => 'char', size => 2 },
102 'char(2)' => { data_type => 'char', size => 2 },
103 # check that default null is correctly rewritten
104 'char(3) default null' => { data_type => 'char', size => 3,
105 default_value => \'null' },
106 'character' => { data_type => 'char', size => 1 },
107 'char' => { data_type => 'char', size => 1 },
108 text => { data_type => 'text' },
109 # varchar with no size has unlimited size, we rewrite to 'text'
110 varchar => { data_type => 'text',
111 original => { data_type => 'varchar' } },
112 # check default null again (to make sure ref is safe)
113 'varchar(3) default null' => { data_type => 'varchar', size => 3,
114 default_value => \'null' },
117 date => { data_type => 'date' },
118 interval => { data_type => 'interval' },
119 'interval(2)' => { data_type => 'interval', size => 2 },
120 time => { data_type => 'time' },
121 'time(2)' => { data_type => 'time', size => 2 },
122 'time without time zone' => { data_type => 'time' },
123 'time(2) without time zone' => { data_type => 'time', size => 2 },
124 'time with time zone' => { data_type => 'time with time zone' },
125 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
126 timestamp => { data_type => 'timestamp' },
127 'timestamp default now()'
128 => { data_type => 'timestamp', default_value => \'current_timestamp',
129 original => { default_value => \'now()' } },
130 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
131 'timestamp without time zone' => { data_type => 'timestamp' },
132 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
134 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
135 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
138 bytea => { data_type => 'bytea' },
141 pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
142 list => [ qw/foo bar baz/] } },
146 CREATE TYPE pg_loader_test_enum AS ENUM (
154 CREATE SCHEMA dbicsl_test
157 CREATE SEQUENCE dbicsl_test.myseq
160 CREATE TABLE pg_loader_test1 (
161 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
166 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
169 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
172 CREATE TABLE pg_loader_test2 (
173 id SERIAL PRIMARY KEY,
178 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'
181 CREATE SCHEMA "dbicsl-test"
184 CREATE TABLE "dbicsl-test".pg_loader_test4 (
185 id SERIAL PRIMARY KEY,
190 CREATE TABLE "dbicsl-test".pg_loader_test5 (
191 id SERIAL PRIMARY KEY,
193 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
194 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
198 CREATE SCHEMA "dbicsl.test"
201 CREATE TABLE "dbicsl.test".pg_loader_test5 (
202 pk SERIAL PRIMARY KEY,
204 four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
205 CONSTRAINT loader_test5_uniq UNIQUE (four_id)
209 CREATE TABLE "dbicsl.test".pg_loader_test6 (
210 id SERIAL PRIMARY KEY,
212 pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
216 CREATE TABLE "dbicsl.test".pg_loader_test7 (
217 id SERIAL PRIMARY KEY,
219 six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
223 CREATE TABLE "dbicsl-test".pg_loader_test8 (
224 id SERIAL PRIMARY KEY,
226 pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
231 'DROP SCHEMA dbicsl_test CASCADE',
232 'DROP SCHEMA "dbicsl-test" CASCADE',
233 'DROP SCHEMA "dbicsl.test" CASCADE',
234 'DROP TYPE pg_loader_test_enum',
236 drop => [ qw/ pg_loader_test1 pg_loader_test2 / ],
239 my ($schema, $monikers, $classes) = @_;
241 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
243 'qualified sequence detected';
245 my $class = $classes->{pg_loader_test1};
246 my $filename = $schema->loader->get_dump_filename($class);
248 my $code = slurp_file $filename;
250 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
253 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
254 'column comment and attrs';
256 $class = $classes->{pg_loader_test2};
257 $filename = $schema->loader->get_dump_filename($class);
259 $code = slurp_file $filename;
261 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,
262 'long table comment is in DESCRIPTION';
264 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
266 rmtree EXTRA_DUMP_DIR;
269 local $SIG{__WARN__} = sub {
270 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
277 db_schema => $db_schema,
279 dump_directory => EXTRA_DUMP_DIR,
282 [ $dsn, $user, $password, {
283 on_connect_do => [ 'SET client_min_messages=WARNING' ],
287 diag join "\n", @warns if @warns;
290 } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
292 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
295 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
296 on_connect_do => [ 'SET client_min_messages=WARNING' ],
298 } 'connected test schema';
301 ok $rsrc = $test_schema->source('PgLoaderTest4');
302 } 'got source for table in schema name with dash';
304 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
305 'column in schema name with dash';
307 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
308 'column in schema name with dash';
310 is try { $rsrc->column_info('value')->{size} }, 100,
311 'column in schema name with dash';
314 ok $rs = $test_schema->resultset('PgLoaderTest4');
315 } 'got resultset for table in schema name with dash';
318 ok $row = $rs->create({ value => 'foo' });
319 } 'executed SQL on table in schema name with dash';
321 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
323 is_deeply $rel_info->{cond}, {
324 'foreign.four_id' => 'self.id'
325 }, 'relationship in schema name with dash';
327 is $rel_info->{attrs}{accessor}, 'single',
328 'relationship in schema name with dash';
330 is $rel_info->{attrs}{join_type}, 'LEFT',
331 'relationship in schema name with dash';
334 ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
335 } 'got source for table in schema name with dash';
337 %uniqs = try { $rsrc->unique_constraints };
340 'got unique and primary constraint in schema name with dash';
342 delete $uniqs{primary};
344 is_deeply ((values %uniqs)[0], ['four_id'],
345 'unique constraint is correct in schema name with dash');
348 ok $rsrc = $test_schema->source('PgLoaderTest6');
349 } 'got source for table in schema name with dot';
351 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
352 'column in schema name with dot introspected correctly';
354 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
355 'column in schema name with dot introspected correctly';
357 is try { $rsrc->column_info('value')->{size} }, 100,
358 'column in schema name with dot introspected correctly';
361 ok $rs = $test_schema->resultset('PgLoaderTest6');
362 } 'got resultset for table in schema name with dot';
365 ok $row = $rs->create({ value => 'foo' });
366 } 'executed SQL on table in schema name with dot';
368 $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
370 is_deeply $rel_info->{cond}, {
371 'foreign.six_id' => 'self.id'
372 }, 'relationship in schema name with dot';
374 is $rel_info->{attrs}{accessor}, 'single',
375 'relationship in schema name with dot';
377 is $rel_info->{attrs}{join_type}, 'LEFT',
378 'relationship in schema name with dot';
381 ok $rsrc = $test_schema->source('PgLoaderTest7');
382 } 'got source for table in schema name with dot';
384 %uniqs = try { $rsrc->unique_constraints };
387 'got unique and primary constraint in schema name with dot';
389 delete $uniqs{primary};
391 is_deeply ((values %uniqs)[0], ['six_id'],
392 'unique constraint is correct in schema name with dot');
395 ok $test_schema->source('PgLoaderTest6')
396 ->has_relationship('pg_loader_test4');
397 } 'cross-schema relationship in multi-db_schema';
400 ok $test_schema->source('PgLoaderTest4')
401 ->has_relationship('pg_loader_test6s');
402 } 'cross-schema relationship in multi-db_schema';
405 ok $test_schema->source('PgLoaderTest8')
406 ->has_relationship('pg_loader_test7');
407 } 'cross-schema relationship in multi-db_schema';
410 ok $test_schema->source('PgLoaderTest7')
411 ->has_relationship('pg_loader_test8s');
412 } 'cross-schema relationship in multi-db_schema';
418 if( !$dsn || !$user ) {
419 $tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
422 $tester->run_tests();
426 rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
428 # vim:et sw=4 sts=4 tw=0: