3 use DBIx::Class::Schema::Loader 'make_schema_at';
4 use DBIx::Class::Schema::Loader::Utils 'no_warnings';
5 use dbixcsl_common_tests;
8 use File::Slurp 'slurp';
13 my $dsn = $ENV{DBICTEST_PG_DSN} || '';
14 my $user = $ENV{DBICTEST_PG_USER} || '';
15 my $password = $ENV{DBICTEST_PG_PASS} || '';
17 my $tester = dbixcsl_common_tests->new(
19 auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
22 password => $password,
23 loader_options => { preserve_case => 1 },
24 connect_info_opts => {
26 on_connect_do => [ 'SET client_min_messages=WARNING' ],
30 # http://www.postgresql.org/docs/7.4/interactive/datatype.html
33 boolean => { data_type => 'boolean' },
34 bool => { data_type => 'boolean' },
36 => { data_type => 'boolean', default_value => \'false' },
38 bigint => { data_type => 'bigint' },
39 int8 => { data_type => 'bigint' },
40 bigserial => { data_type => 'bigint', is_auto_increment => 1 },
41 serial8 => { data_type => 'bigint', is_auto_increment => 1 },
42 integer => { data_type => 'integer' },
43 int => { data_type => 'integer' },
44 int4 => { data_type => 'integer' },
45 serial => { data_type => 'integer', is_auto_increment => 1 },
46 serial4 => { data_type => 'integer', is_auto_increment => 1 },
47 smallint => { data_type => 'smallint' },
48 int2 => { data_type => 'smallint' },
50 money => { data_type => 'money' },
52 'double precision' => { data_type => 'double precision' },
53 float8 => { data_type => 'double precision' },
54 real => { data_type => 'real' },
55 float4 => { data_type => 'real' },
56 'float(24)' => { data_type => 'real' },
57 'float(25)' => { data_type => 'double precision' },
58 'float(53)' => { data_type => 'double precision' },
59 float => { data_type => 'double precision' },
61 numeric => { data_type => 'numeric' },
62 decimal => { data_type => 'numeric' },
63 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
64 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
67 'bit varying(2)' => { data_type => 'varbit', size => 2 },
68 'varbit(2)' => { data_type => 'varbit', size => 2 },
69 'varbit' => { data_type => 'varbit' },
70 bit => { data_type => 'bit', size => 1 },
71 'bit(3)' => { data_type => 'bit', size => 3 },
74 inet => { data_type => 'inet' },
75 cidr => { data_type => 'cidr' },
76 macaddr => { data_type => 'macaddr' },
79 point => { data_type => 'point' },
80 line => { data_type => 'line' },
81 lseg => { data_type => 'lseg' },
82 box => { data_type => 'box' },
83 path => { data_type => 'path' },
84 polygon => { data_type => 'polygon' },
85 circle => { data_type => 'circle' },
88 'character varying(2)' => { data_type => 'varchar', size => 2 },
89 'varchar(2)' => { data_type => 'varchar', size => 2 },
90 'character(2)' => { data_type => 'char', size => 2 },
91 'char(2)' => { data_type => 'char', size => 2 },
92 'character' => { data_type => 'char', size => 1 },
93 'char' => { data_type => 'char', size => 1 },
94 text => { data_type => 'text' },
95 # varchar with no size has unlimited size, we rewrite to 'text'
96 varchar => { data_type => 'text',
97 original => { data_type => 'varchar' } },
100 date => { data_type => 'date' },
101 interval => { data_type => 'interval' },
102 'interval(2)' => { data_type => 'interval', size => 2 },
103 time => { data_type => 'time' },
104 'time(2)' => { data_type => 'time', size => 2 },
105 'time without time zone' => { data_type => 'time' },
106 'time(2) without time zone' => { data_type => 'time', size => 2 },
107 'time with time zone' => { data_type => 'time with time zone' },
108 'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
109 timestamp => { data_type => 'timestamp' },
110 'timestamp default now()'
111 => { data_type => 'timestamp', default_value => \'current_timestamp',
112 original => { default_value => \'now()' } },
113 'timestamp(2)' => { data_type => 'timestamp', size => 2 },
114 'timestamp without time zone' => { data_type => 'timestamp' },
115 'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
117 'timestamp with time zone' => { data_type => 'timestamp with time zone' },
118 'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
121 bytea => { data_type => 'bytea' },
124 pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
125 list => [ qw/foo bar baz/] } },
129 CREATE TYPE pg_loader_test_enum AS ENUM (
137 CREATE SCHEMA dbicsl_test
140 CREATE SEQUENCE dbicsl_test.myseq
143 CREATE TABLE pg_loader_test1 (
144 id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
149 COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
152 COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
155 CREATE TABLE pg_loader_test2 (
156 id SERIAL PRIMARY KEY,
161 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'
164 CREATE SCHEMA "dbicsl-test"
167 CREATE TABLE "dbicsl-test".pg_loader_test3 (
168 id SERIAL PRIMARY KEY,
173 CREATE TABLE "dbicsl-test".pg_loader_test4 (
174 id SERIAL PRIMARY KEY,
176 three_id INTEGER UNIQUE REFERENCES "dbicsl-test".pg_loader_test3 (id)
180 CREATE SCHEMA "dbicsl.test"
183 CREATE TABLE "dbicsl.test".pg_loader_test5 (
184 id SERIAL PRIMARY KEY,
186 pg_loader_test3_id INTEGER NOT NULL REFERENCES "dbicsl-test".pg_loader_test3 (id)
190 CREATE TABLE "dbicsl.test".pg_loader_test6 (
191 id SERIAL PRIMARY KEY,
193 five_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test5 (id)
197 CREATE TABLE "dbicsl-test".pg_loader_test7 (
198 id SERIAL PRIMARY KEY,
200 pg_loader_test6_id INTEGER NOT NULL REFERENCES "dbicsl.test".pg_loader_test6 (id)
205 'DROP SCHEMA dbicsl_test CASCADE',
206 'DROP SCHEMA "dbicsl-test" CASCADE',
207 'DROP SCHEMA "dbicsl.test" CASCADE',
208 'DROP TYPE pg_loader_test_enum',
210 drop => [ qw/ pg_loader_test1 pg_loader_test2 / ],
213 my ($schema, $monikers, $classes) = @_;
215 is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
217 'qualified sequence detected';
219 my $class = $classes->{pg_loader_test1};
220 my $filename = $schema->_loader->get_dump_filename($class);
222 my $code = decode('UTF-8', scalar slurp $filename);
224 like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
227 like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
228 'column comment and attrs';
230 $class = $classes->{pg_loader_test2};
231 $filename = $schema->_loader->get_dump_filename($class);
233 $code = decode('UTF-8', scalar slurp $filename);
235 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,
236 'long table comment is in DESCRIPTION';
238 foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
246 db_schema => $db_schema,
248 [ $dsn, $user, $password, {
249 on_connect_do => [ 'SET client_min_messages=WARNING' ],
254 'created dynamic schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
256 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
259 ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
260 on_connect_do => [ 'SET client_min_messages=WARNING' ],
262 } 'connected test schema';
265 ok $rsrc = $test_schema->source('PgLoaderTest3');
266 } 'got source for table in schema name with dash';
268 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
269 'column in schema name with dash';
271 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
272 'column in schema name with dash';
274 is try { $rsrc->column_info('value')->{size} }, 100,
275 'column in schema name with dash';
278 ok $rs = $test_schema->resultset('PgLoaderTest3');
279 } 'got resultset for table in schema name with dash';
282 ok $row = $rs->create({ value => 'foo' });
283 } 'executed SQL on table in schema name with dash';
285 $rel_info = try { $rsrc->relationship_info('pg_loader_test4') };
287 is_deeply $rel_info->{cond}, {
288 'foreign.three_id' => 'self.id'
289 }, 'relationship in schema name with dash';
291 is $rel_info->{attrs}{accessor}, 'single',
292 'relationship in schema name with dash';
294 is $rel_info->{attrs}{join_type}, 'LEFT',
295 'relationship in schema name with dash';
298 ok $rsrc = $test_schema->source('PgLoaderTest4');
299 } 'got source for table in schema name with dash';
301 %uniqs = try { $rsrc->unique_constraints };
304 'got unique and primary constraint in schema name with dash';
307 ok $rsrc = PGMultiSchema->source('PgLoaderTest5');
308 } 'got source for table in schema name with dot';
310 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
311 'column in schema name with dot introspected correctly';
313 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
314 'column in schema name with dash introspected correctly';
316 is try { $rsrc->column_info('value')->{size} }, 100,
317 'column in schema name with dash introspected correctly';
320 ok $rs = $test_schema->resultset('PgLoaderTest5');
321 } 'got resultset for table in schema name with dot';
324 ok $row = $rs->create({ value => 'foo' });
325 } 'executed SQL on table in schema name with dot';
327 $rel_info = try { $rsrc->relationship_info('pg_loader_test6') };
329 is_deeply $rel_info->{cond}, {
330 'foreign.five_id' => 'self.id'
331 }, 'relationship in schema name with dot';
333 is $rel_info->{attrs}{accessor}, 'single',
334 'relationship in schema name with dot';
336 is $rel_info->{attrs}{join_type}, 'LEFT',
337 'relationship in schema name with dot';
340 ok $rsrc = $test_schema->source('PgLoaderTest6');
341 } 'got source for table in schema name with dot';
343 %uniqs = try { $rsrc->unique_constraints };
346 'got unique and primary constraint in schema name with dot';
349 ok $test_schema->source('PgLoaderTest5')
350 ->has_relationship('pg_loader_test3');
351 } 'cross-schema relationship in multi-db_schema';
354 ok $test_schema->source('PgLoaderTest7')
355 ->has_relationship('pg_loader_test6');
356 } 'cross-schema relationship in multi-db_schema';
362 if( !$dsn || !$user ) {
363 $tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
366 $tester->run_tests();
368 # vim:et sw=4 sts=4 tw=0: