6 use File::Path 'rmtree';
7 use DBIx::Class::Schema::Loader 'make_schema_at';
12 use dbixcsl_common_tests;
13 use dbixcsl_test_dir '$tdir';
15 use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump";
17 # The default max_cursor_count and max_statement_count settings of 50 are too
18 # low to run this test.
20 # Setting them to zero is preferred.
22 my $dbd_sqlanywhere_dsn = $ENV{DBICTEST_SQLANYWHERE_DSN} || '';
23 my $dbd_sqlanywhere_user = $ENV{DBICTEST_SQLANYWHERE_USER} || '';
24 my $dbd_sqlanywhere_password = $ENV{DBICTEST_SQLANYWHERE_PASS} || '';
26 my $odbc_dsn = $ENV{DBICTEST_SQLANYWHERE_ODBC_DSN} || '';
27 my $odbc_user = $ENV{DBICTEST_SQLANYWHERE_ODBC_USER} || '';
28 my $odbc_password = $ENV{DBICTEST_SQLANYWHERE_ODBC_PASS} || '';
30 my ($schema, $schemas_created); # for cleanup in END for extra tests
32 my $tester = dbixcsl_common_tests->new(
33 vendor => 'SQLAnywhere',
34 auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
35 connect_info => [ ($dbd_sqlanywhere_dsn ? {
36 dsn => $dbd_sqlanywhere_dsn,
37 user => $dbd_sqlanywhere_user,
38 password => $dbd_sqlanywhere_password,
43 password => $odbc_password,
46 loader_options => { preserve_case => 1 },
47 default_is_deferrable => 1,
48 default_on_clause => 'RESTRICT',
50 # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
53 'bit' => { data_type => 'bit' },
54 'tinyint' => { data_type => 'tinyint' },
55 'smallint' => { data_type => 'smallint' },
56 'int' => { data_type => 'integer' },
57 'integer' => { data_type => 'integer' },
58 'bigint' => { data_type => 'bigint' },
59 'float' => { data_type => 'real' },
60 'real' => { data_type => 'real' },
61 'double' => { data_type => 'double precision' },
63 { data_type => 'double precision' },
65 'float(2)' => { data_type => 'real' },
66 'float(24)' => { data_type => 'real' },
67 'float(25)' => { data_type => 'double precision' },
68 'float(53)' => { data_type => 'double precision' },
70 # This test only works with the default precision and scale options.
72 # They are preserved even for the default values, because the defaults
74 'decimal' => { data_type => 'decimal', size => [30,6] },
75 'dec' => { data_type => 'decimal', size => [30,6] },
76 'numeric' => { data_type => 'numeric', size => [30,6] },
78 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
79 'dec(3)' => { data_type => 'decimal', size => [3,0] },
80 'numeric(3)' => { data_type => 'numeric', size => [3,0] },
82 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
83 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
84 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
86 'decimal(18,18)' => { data_type => 'decimal', size => [18,18] },
87 'dec(18,18)' => { data_type => 'decimal', size => [18,18] },
88 'numeric(18,18)' => { data_type => 'numeric', size => [18,18] },
91 'money' => { data_type => 'money' },
92 'smallmoney' => { data_type => 'smallmoney' },
95 'long varbit' => { data_type => 'long varbit' },
97 => { data_type => 'long varbit' },
98 'varbit' => { data_type => 'varbit', size => 1 },
99 'varbit(20)' => { data_type => 'varbit', size => 20 },
100 'bit varying' => { data_type => 'varbit', size => 1 },
102 => { data_type => 'varbit', size => 20 },
104 # Date and Time Types
105 'date' => { data_type => 'date' },
106 'datetime' => { data_type => 'datetime' },
108 => { data_type => 'smalldatetime' },
109 'timestamp' => { data_type => 'timestamp' },
110 # rewrite 'current timestamp' as 'current_timestamp'
111 'timestamp default current timestamp'
112 => { data_type => 'timestamp', default_value => \'current_timestamp',
113 original => { default_value => \'current timestamp' } },
114 'time' => { data_type => 'time' },
117 'char' => { data_type => 'char', size => 1 },
118 'char(11)' => { data_type => 'char', size => 11 },
119 'nchar' => { data_type => 'nchar', size => 1 },
120 'nchar(11)' => { data_type => 'nchar', size => 11 },
121 'varchar' => { data_type => 'varchar', size => 1 },
122 'varchar(20)' => { data_type => 'varchar', size => 20 },
124 => { data_type => 'varchar', size => 20 },
125 'character varying(20)'
126 => { data_type => 'varchar', size => 20 },
127 'nvarchar(20)' => { data_type => 'nvarchar', size => 20 },
128 'xml' => { data_type => 'xml' },
129 'uniqueidentifierstr'
130 => { data_type => 'uniqueidentifierstr' },
133 'binary' => { data_type => 'binary', size => 1 },
134 'binary(20)' => { data_type => 'binary', size => 20 },
135 'varbinary' => { data_type => 'varbinary', size => 1 },
136 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
138 => { data_type => 'uniqueidentifier' },
141 'long binary' => { data_type => 'long binary' },
142 'image' => { data_type => 'image' },
143 'long varchar' => { data_type => 'long varchar' },
144 'text' => { data_type => 'text' },
145 'long nvarchar'=> { data_type => 'long nvarchar' },
146 'ntext' => { data_type => 'ntext' },
150 # 4 through 8 are used for the multi-schema tests
152 create table sqlanywhere_loader_test9 (
153 id int identity not null primary key
157 create table sqlanywhere_loader_test10 (
158 id int identity not null primary key,
160 foreign key (nine_id) references sqlanywhere_loader_test9(id)
161 on delete cascade on update set null
165 drop => [ qw/sqlanywhere_loader_test9 sqlanywhere_loader_test10/ ],
172 # test on delete/update fk clause introspection
173 ok ((my $rel_info = $schema->source('SqlanywhereLoaderTest10')->relationship_info('nine')),
176 is $rel_info->{attrs}{on_delete}, 'CASCADE',
177 'ON DELETE clause introspected correctly';
179 is $rel_info->{attrs}{on_update}, 'SET NULL',
180 'ON UPDATE clause introspected correctly';
182 is $rel_info->{attrs}{is_deferrable}, 1,
183 'is_deferrable defaults to 1';
185 my $connect_info = [@$self{qw/dsn user password/}];
187 my $dbh = $schema->storage->dbh;
190 $dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'");
193 $schemas_created = 0;
194 skip "no CREATE USER privileges", 30 * 2;
198 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 (
199 id INT IDENTITY NOT NULL PRIMARY KEY,
204 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 (
205 id INT IDENTITY NOT NULL PRIMARY KEY,
207 four_id INTEGER NOT NULL,
208 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
209 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
212 $dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'");
214 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test5 (
215 pk INT IDENTITY NOT NULL PRIMARY KEY,
217 four_id INTEGER NOT NULL,
218 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
219 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
223 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 (
224 id INT IDENTITY NOT NULL PRIMARY KEY,
226 sqlanywhere_loader_test4_id INTEGER,
227 FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
231 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 (
232 id INT IDENTITY NOT NULL PRIMARY KEY,
234 six_id INTEGER NOT NULL UNIQUE,
235 FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id)
239 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 (
240 id INT IDENTITY NOT NULL PRIMARY KEY,
242 sqlanywhere_loader_test7_id INTEGER,
243 FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id)
247 $schemas_created = 1;
249 my $guard = Scope::Guard->new(\&extra_cleanup);
251 foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') {
253 rmtree EXTRA_DUMP_DIR;
256 local $SIG{__WARN__} = sub {
257 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
261 'SQLAnywhereMultiSchema',
264 db_schema => $db_schema,
265 dump_directory => EXTRA_DUMP_DIR,
271 diag join "\n", @warns if @warns;
274 } 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings';
276 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
279 ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info);
280 } 'connected test schema';
283 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4');
284 } 'got source for table in schema one';
286 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
287 'column in schema one';
289 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
290 'column in schema one';
292 is try { $rsrc->column_info('value')->{size} }, 100,
293 'column in schema one';
296 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4');
297 } 'got resultset for table in schema one';
300 ok $row = $rs->create({ value => 'foo' });
301 } 'executed SQL on table in schema one';
303 $rel_info = try { $rsrc->relationship_info('dbicsl_test1_sqlanywhere_loader_test5') };
305 is_deeply $rel_info->{cond}, {
306 'foreign.four_id' => 'self.id'
307 }, 'relationship in schema one';
309 is $rel_info->{attrs}{accessor}, 'single',
310 'relationship in schema one';
312 is $rel_info->{attrs}{join_type}, 'LEFT',
313 'relationship in schema one';
316 ok $rsrc = $test_schema->source('DbicslTest1SqlanywhereLoaderTest5');
317 } 'got source for table in schema one';
319 %uniqs = try { $rsrc->unique_constraints };
322 'got unique and primary constraint in schema one';
324 delete $uniqs{primary};
326 is_deeply ((values %uniqs)[0], ['four_id'],
327 'correct unique constraint in schema one');
330 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6');
331 } 'got source for table in schema two';
333 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
334 'column in schema two introspected correctly';
336 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
337 'column in schema two introspected correctly';
339 is try { $rsrc->column_info('value')->{size} }, 100,
340 'column in schema two introspected correctly';
343 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6');
344 } 'got resultset for table in schema two';
347 ok $row = $rs->create({ value => 'foo' });
348 } 'executed SQL on table in schema two';
350 $rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') };
352 is_deeply $rel_info->{cond}, {
353 'foreign.six_id' => 'self.id'
354 }, 'relationship in schema two';
356 is $rel_info->{attrs}{accessor}, 'single',
357 'relationship in schema two';
359 is $rel_info->{attrs}{join_type}, 'LEFT',
360 'relationship in schema two';
363 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7');
364 } 'got source for table in schema two';
366 %uniqs = try { $rsrc->unique_constraints };
369 'got unique and primary constraint in schema two';
371 delete $uniqs{primary};
373 is_deeply ((values %uniqs)[0], ['six_id'],
374 'correct unique constraint in schema two');
377 ok $test_schema->source('SqlanywhereLoaderTest6')
378 ->has_relationship('sqlanywhere_loader_test4');
379 } 'cross-schema relationship in multi-db_schema';
382 ok $test_schema->source('SqlanywhereLoaderTest4')
383 ->has_relationship('sqlanywhere_loader_test6s');
384 } 'cross-schema relationship in multi-db_schema';
387 ok $test_schema->source('SqlanywhereLoaderTest8')
388 ->has_relationship('sqlanywhere_loader_test7');
389 } 'cross-schema relationship in multi-db_schema';
392 ok $test_schema->source('SqlanywhereLoaderTest7')
393 ->has_relationship('sqlanywhere_loader_test8s');
394 } 'cross-schema relationship in multi-db_schema';
401 if (not ($dbd_sqlanywhere_dsn || $odbc_dsn)) {
402 $tester->skip_tests('You need to set the DBICTEST_SQLANYWHERE_DSN, _USER and _PASS and/or the DBICTEST_SQLANYWHERE_ODBC_DSN, _USER and _PASS environment variables');
405 $tester->run_tests();
409 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
410 if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) {
411 foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8',
412 'dbicsl_test2.sqlanywhere_loader_test7',
413 'dbicsl_test2.sqlanywhere_loader_test6',
414 'dbicsl_test2.sqlanywhere_loader_test5',
415 'dbicsl_test1.sqlanywhere_loader_test5',
416 'dbicsl_test1.sqlanywhere_loader_test4') {
418 $dbh->do("DROP TABLE $table");
421 diag "Error dropping table: $_";
425 foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) {
427 $dbh->do("DROP USER $db_schema");
430 diag "Error dropping test user $db_schema: $_";
434 rmtree EXTRA_DUMP_DIR;
437 # vim:et sts=4 sw=4 tw=0: