6 use File::Path 'rmtree';
7 use DBIx::Class::Optional::Dependencies;
8 use DBIx::Class::Schema::Loader 'make_schema_at';
13 use dbixcsl_common_tests;
14 use dbixcsl_test_dir '$tdir';
16 use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump";
18 # The default max_cursor_count and max_statement_count settings of 50 are too
19 # low to run this test.
21 # Setting them to zero is preferred.
23 my %env2optdep = map { $_ => lc "test_rdbms_$_" } qw(SQLANYWHERE SQLANYWHERE_ODBC);
25 plan skip_all => 'requirements not satisfied: ' . (join ' OR ', map
26 { "[ @{[ DBIx::Class::Schema::Loader::Optional::Dependencies->req_missing_for( $_ ) ]} ]" }
29 { DBIx::Class::Schema::Loader::Optional::Dependencies->req_ok_for( $_ ) }
34 for my $type (keys %env2optdep) {
36 @conninfo{qw(dsn user password)} = map { $ENV{"DBICTEST_${type}_$_"} } qw(DSN USER PASS);
37 next unless $conninfo{dsn};
39 my $dep_group = $env2optdep{$type};
40 if (!DBIx::Class::Schema::Loader::Optional::Dependencies->req_ok_for($dep_group)) {
41 diag "Testing with DBICTEST_${type}_DSN needs " . DBIx::Class::Schema::Loader::Optional::Dependencies->req_missing_for($dep_group);
45 $dsns{$type} = \%conninfo;
48 my ($schema, $schemas_created); # for cleanup in END for extra tests
50 my $tester = dbixcsl_common_tests->new(
51 vendor => 'SQLAnywhere',
52 auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
53 connect_info => [ map { $dsns{$_} } sort keys %dsns ],
54 loader_options => { preserve_case => 1 },
55 default_is_deferrable => 1,
56 default_on_clause => 'RESTRICT',
58 # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
61 'bit' => { data_type => 'bit' },
62 'tinyint' => { data_type => 'tinyint' },
63 'smallint' => { data_type => 'smallint' },
64 'int' => { data_type => 'integer' },
65 'integer' => { data_type => 'integer' },
66 'bigint' => { data_type => 'bigint' },
67 'float' => { data_type => 'real' },
68 'real' => { data_type => 'real' },
69 'double' => { data_type => 'double precision' },
71 { data_type => 'double precision' },
73 'float(2)' => { data_type => 'real' },
74 'float(24)' => { data_type => 'real' },
75 'float(25)' => { data_type => 'double precision' },
76 'float(53)' => { data_type => 'double precision' },
78 # This test only works with the default precision and scale options.
80 # They are preserved even for the default values, because the defaults
82 'decimal' => { data_type => 'decimal', size => [30,6] },
83 'dec' => { data_type => 'decimal', size => [30,6] },
84 'numeric' => { data_type => 'numeric', size => [30,6] },
86 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
87 'dec(3)' => { data_type => 'decimal', size => [3,0] },
88 'numeric(3)' => { data_type => 'numeric', size => [3,0] },
90 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
91 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
92 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
94 'decimal(18,18)' => { data_type => 'decimal', size => [18,18] },
95 'dec(18,18)' => { data_type => 'decimal', size => [18,18] },
96 'numeric(18,18)' => { data_type => 'numeric', size => [18,18] },
99 'money' => { data_type => 'money' },
100 'smallmoney' => { data_type => 'smallmoney' },
103 'long varbit' => { data_type => 'long varbit' },
105 => { data_type => 'long varbit' },
106 'varbit' => { data_type => 'varbit', size => 1 },
107 'varbit(20)' => { data_type => 'varbit', size => 20 },
108 'bit varying' => { data_type => 'varbit', size => 1 },
110 => { data_type => 'varbit', size => 20 },
112 # Date and Time Types
113 'date' => { data_type => 'date' },
114 'datetime' => { data_type => 'datetime' },
116 => { data_type => 'smalldatetime' },
117 'timestamp' => { data_type => 'timestamp' },
118 # rewrite 'current timestamp' as 'current_timestamp'
119 'timestamp default current timestamp'
120 => { data_type => 'timestamp', default_value => \'current_timestamp',
121 original => { default_value => \'current timestamp' } },
122 'time' => { data_type => 'time' },
125 'char' => { data_type => 'char', size => 1 },
126 'char(11)' => { data_type => 'char', size => 11 },
127 'nchar' => { data_type => 'nchar', size => 1 },
128 'nchar(11)' => { data_type => 'nchar', size => 11 },
129 'varchar' => { data_type => 'varchar', size => 1 },
130 'varchar(20)' => { data_type => 'varchar', size => 20 },
132 => { data_type => 'varchar', size => 20 },
133 'character varying(20)'
134 => { data_type => 'varchar', size => 20 },
135 'nvarchar(20)' => { data_type => 'nvarchar', size => 20 },
136 'xml' => { data_type => 'xml' },
137 'uniqueidentifierstr'
138 => { data_type => 'uniqueidentifierstr' },
141 'binary' => { data_type => 'binary', size => 1 },
142 'binary(20)' => { data_type => 'binary', size => 20 },
143 'varbinary' => { data_type => 'varbinary', size => 1 },
144 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
146 => { data_type => 'uniqueidentifier' },
149 'long binary' => { data_type => 'long binary' },
150 'image' => { data_type => 'image' },
151 'long varchar' => { data_type => 'long varchar' },
152 'text' => { data_type => 'text' },
153 'long nvarchar'=> { data_type => 'long nvarchar' },
154 'ntext' => { data_type => 'ntext' },
158 # 4 through 8 are used for the multi-schema tests
160 create table sqlanywhere_loader_test9 (
161 id int identity not null primary key
165 create table sqlanywhere_loader_test10 (
166 id int identity not null primary key,
168 foreign key (nine_id) references sqlanywhere_loader_test9(id)
169 on delete cascade on update set null
173 drop => [ qw/sqlanywhere_loader_test9 sqlanywhere_loader_test10/ ],
180 # test on delete/update fk clause introspection
181 ok ((my $rel_info = $schema->source('SqlanywhereLoaderTest10')->relationship_info('nine')),
184 is $rel_info->{attrs}{on_delete}, 'CASCADE',
185 'ON DELETE clause introspected correctly';
187 is $rel_info->{attrs}{on_update}, 'SET NULL',
188 'ON UPDATE clause introspected correctly';
190 is $rel_info->{attrs}{is_deferrable}, 1,
191 'is_deferrable defaults to 1';
193 my $connect_info = [@$self{qw/dsn user password/}];
195 my $dbh = $schema->storage->dbh;
198 $dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'");
201 $schemas_created = 0;
202 skip "no CREATE USER privileges", 30 * 2;
206 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 (
207 id INT IDENTITY NOT NULL PRIMARY KEY,
212 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 (
213 id INT IDENTITY NOT NULL PRIMARY KEY,
215 four_id INTEGER NOT NULL,
216 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
217 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
220 $dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'");
222 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test5 (
223 pk INT IDENTITY NOT NULL PRIMARY KEY,
225 four_id INTEGER NOT NULL,
226 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
227 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
231 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 (
232 id INT IDENTITY NOT NULL PRIMARY KEY,
234 sqlanywhere_loader_test4_id INTEGER,
235 FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
239 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 (
240 id INT IDENTITY NOT NULL PRIMARY KEY,
242 six_id INTEGER NOT NULL UNIQUE,
243 FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id)
247 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 (
248 id INT IDENTITY NOT NULL PRIMARY KEY,
250 sqlanywhere_loader_test7_id INTEGER,
251 FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id)
255 $schemas_created = 1;
257 my $guard = Scope::Guard->new(\&extra_cleanup);
259 foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') {
261 rmtree EXTRA_DUMP_DIR;
264 local $SIG{__WARN__} = sub {
265 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
269 'SQLAnywhereMultiSchema',
272 db_schema => $db_schema,
273 dump_directory => EXTRA_DUMP_DIR,
279 diag join "\n", @warns if @warns;
282 } 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings';
284 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
287 ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info);
288 } 'connected test schema';
291 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4');
292 } 'got source for table in schema one';
294 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
295 'column in schema one';
297 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
298 'column in schema one';
300 is try { $rsrc->column_info('value')->{size} }, 100,
301 'column in schema one';
304 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4');
305 } 'got resultset for table in schema one';
308 ok $row = $rs->create({ value => 'foo' });
309 } 'executed SQL on table in schema one';
311 $rel_info = try { $rsrc->relationship_info('dbicsl_test1_sqlanywhere_loader_test5') };
313 is_deeply $rel_info->{cond}, {
314 'foreign.four_id' => 'self.id'
315 }, 'relationship in schema one';
317 is $rel_info->{attrs}{accessor}, 'single',
318 'relationship in schema one';
320 is $rel_info->{attrs}{join_type}, 'LEFT',
321 'relationship in schema one';
324 ok $rsrc = $test_schema->source('DbicslTest1SqlanywhereLoaderTest5');
325 } 'got source for table in schema one';
327 %uniqs = try { $rsrc->unique_constraints };
330 'got unique and primary constraint in schema one';
332 delete $uniqs{primary};
334 is_deeply ((values %uniqs)[0], ['four_id'],
335 'correct unique constraint in schema one');
338 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6');
339 } 'got source for table in schema two';
341 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
342 'column in schema two introspected correctly';
344 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
345 'column in schema two introspected correctly';
347 is try { $rsrc->column_info('value')->{size} }, 100,
348 'column in schema two introspected correctly';
351 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6');
352 } 'got resultset for table in schema two';
355 ok $row = $rs->create({ value => 'foo' });
356 } 'executed SQL on table in schema two';
358 $rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') };
360 is_deeply $rel_info->{cond}, {
361 'foreign.six_id' => 'self.id'
362 }, 'relationship in schema two';
364 is $rel_info->{attrs}{accessor}, 'single',
365 'relationship in schema two';
367 is $rel_info->{attrs}{join_type}, 'LEFT',
368 'relationship in schema two';
371 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7');
372 } 'got source for table in schema two';
374 %uniqs = try { $rsrc->unique_constraints };
377 'got unique and primary constraint in schema two';
379 delete $uniqs{primary};
381 is_deeply ((values %uniqs)[0], ['six_id'],
382 'correct unique constraint in schema two');
385 ok $test_schema->source('SqlanywhereLoaderTest6')
386 ->has_relationship('sqlanywhere_loader_test4');
387 } 'cross-schema relationship in multi-db_schema';
390 ok $test_schema->source('SqlanywhereLoaderTest4')
391 ->has_relationship('sqlanywhere_loader_test6s');
392 } 'cross-schema relationship in multi-db_schema';
395 ok $test_schema->source('SqlanywhereLoaderTest8')
396 ->has_relationship('sqlanywhere_loader_test7');
397 } 'cross-schema relationship in multi-db_schema';
400 ok $test_schema->source('SqlanywhereLoaderTest7')
401 ->has_relationship('sqlanywhere_loader_test8s');
402 } 'cross-schema relationship in multi-db_schema';
409 $tester->run_tests();
412 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
413 if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) {
414 foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8',
415 'dbicsl_test2.sqlanywhere_loader_test7',
416 'dbicsl_test2.sqlanywhere_loader_test6',
417 'dbicsl_test2.sqlanywhere_loader_test5',
418 'dbicsl_test1.sqlanywhere_loader_test5',
419 'dbicsl_test1.sqlanywhere_loader_test4') {
421 $dbh->do("DROP TABLE $table");
424 diag "Error dropping table: $_";
428 foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) {
430 $dbh->do("DROP USER $db_schema");
433 diag "Error dropping test user $db_schema: $_";
437 rmtree EXTRA_DUMP_DIR;
440 # vim:et sts=4 sw=4 tw=0: