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/sybase_extra_dump";
17 my $dsn = $ENV{DBICTEST_SYBASE_DSN} || '';
18 my $user = $ENV{DBICTEST_SYBASE_USER} || '';
19 my $password = $ENV{DBICTEST_SYBASE_PASS} || '';
21 my ($schema, $databases_created); # for cleanup in END for extra tests
23 my $tester = dbixcsl_common_tests->new(
25 auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
26 default_function => 'getdate()',
27 default_function_def => 'AS getdate()',
30 password => $password,
32 # http://ispirer.com/wiki/sqlways/sybase/data-types
35 'integer identity' => { data_type => 'integer', is_auto_increment => 1 },
36 int => { data_type => 'integer' },
37 integer => { data_type => 'integer' },
38 bigint => { data_type => 'bigint' },
39 smallint => { data_type => 'smallint' },
40 tinyint => { data_type => 'tinyint' },
41 'double precision' => { data_type => 'double precision' },
42 real => { data_type => 'real' },
43 float => { data_type => 'double precision' },
44 'float(14)' => { data_type => 'real' },
45 'float(15)' => { data_type => 'real' },
46 'float(16)' => { data_type => 'double precision' },
47 'float(48)' => { data_type => 'double precision' },
48 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
49 'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
50 numeric => { data_type => 'numeric' },
51 decimal => { data_type => 'numeric' },
52 bit => { data_type => 'bit' },
55 money => { data_type => 'money' },
56 smallmoney => { data_type => 'smallmoney' },
59 'AS getdate()' => { data_type => undef, inflate_datetime => 1, default_value => \'getdate()' },
62 text => { data_type => 'text' },
63 unitext => { data_type => 'unitext' },
64 image => { data_type => 'image' },
67 date => { data_type => 'date' },
68 time => { data_type => 'time' },
69 datetime => { data_type => 'datetime' },
70 smalldatetime => { data_type => 'smalldatetime' },
73 timestamp => { data_type => 'timestamp', inflate_datetime => 0 },
76 'char' => { data_type => 'char', size => 1 },
77 'char(2)' => { data_type => 'char', size => 2 },
78 'nchar' => { data_type => 'nchar', size => 1 },
79 'nchar(2)' => { data_type => 'nchar', size => 2 },
80 'unichar(2)' => { data_type => 'unichar', size => 2 },
81 'varchar(2)' => { data_type => 'varchar', size => 2 },
82 'nvarchar(2)' => { data_type => 'nvarchar', size => 2 },
83 'univarchar(2)' => { data_type => 'univarchar', size => 2 },
86 'binary' => { data_type => 'binary', size => 1 },
87 'binary(2)' => { data_type => 'binary', size => 2 },
88 'varbinary(2)' => { data_type => 'varbinary', size => 2 },
90 # test that named constraints aren't picked up as tables (I can't reproduce this on my machine)
91 failtrigger_warnings => [ qr/^Bad table or view 'sybase_loader_test2_ref_slt1'/ ],
95 CREATE TABLE sybase_loader_test1 (
96 id int identity primary key
100 CREATE TABLE sybase_loader_test2 (
101 id int identity primary key,
102 sybase_loader_test1_id int,
103 CONSTRAINT sybase_loader_test2_ref_slt1 FOREIGN KEY (sybase_loader_test1_id) REFERENCES sybase_loader_test1 (id)
107 drop => [ qw/sybase_loader_test1 sybase_loader_test2/ ],
113 my $dbh = $schema->storage->dbh;
116 $dbh->do('USE master');
119 skip "these tests require the sysadmin role", 28 * 4;
123 $dbh->do('CREATE DATABASE [dbicsl_test1]');
124 $dbh->do('CREATE DATABASE [dbicsl_test2]');
127 skip "cannot create databases: $_", 28 * 4;
131 my $warn_handler = $SIG{__WARN__} || sub { warn @_ };
132 local $SIG{__WARN__} = sub {
134 unless $_[0] =~ /^Password correctly set\.$|^Account unlocked\.$|^New login created\.$|^New user added\.$/;
137 $dbh->do("sp_addlogin dbicsl_user1, dbicsl, [dbicsl_test1]");
138 $dbh->do("sp_addlogin dbicsl_user2, dbicsl, [dbicsl_test2]");
140 $dbh->do("USE [dbicsl_test1]");
141 $dbh->do("sp_adduser dbicsl_user1");
142 $dbh->do("sp_adduser dbicsl_user2");
143 $dbh->do("GRANT ALL TO dbicsl_user1");
144 $dbh->do("GRANT ALL TO dbicsl_user2");
146 $dbh->do("USE [dbicsl_test2]");
147 $dbh->do("sp_adduser dbicsl_user2");
148 $dbh->do("sp_adduser dbicsl_user1");
149 $dbh->do("GRANT ALL TO dbicsl_user2");
150 $dbh->do("GRANT ALL TO dbicsl_user1");
153 skip "cannot add logins: $_", 28 * 4;
158 my $warn_handler = $SIG{__WARN__} || sub { warn @_ };
159 local $SIG{__WARN__} = sub {
160 $warn_handler->(@_) unless $_[0] =~ /can't change context/;
163 $dbh1 = DBI->connect($dsn, 'dbicsl_user1', 'dbicsl', {
167 $dbh1->do('USE [dbicsl_test1]');
169 $dbh2 = DBI->connect($dsn, 'dbicsl_user2', 'dbicsl', {
173 $dbh2->do('USE [dbicsl_test2]');
177 CREATE TABLE sybase_loader_test4 (
178 id INT IDENTITY PRIMARY KEY,
179 value VARCHAR(100) NULL
182 $dbh1->do('GRANT ALL ON sybase_loader_test4 TO dbicsl_user2');
184 CREATE TABLE sybase_loader_test5 (
185 id INT IDENTITY PRIMARY KEY,
186 value VARCHAR(100) NULL,
187 four_id INTEGER UNIQUE,
188 FOREIGN KEY (four_id) REFERENCES sybase_loader_test4 (id)
192 CREATE TABLE sybase_loader_test6 (
193 id INT IDENTITY PRIMARY KEY,
194 value VARCHAR(100) NULL,
195 sybase_loader_test4_id INTEGER NULL,
196 FOREIGN KEY (sybase_loader_test4_id) REFERENCES [dbicsl_test1].dbicsl_user1.sybase_loader_test4 (id)
200 CREATE TABLE sybase_loader_test7 (
201 id INT IDENTITY PRIMARY KEY,
202 value VARCHAR(100) NULL,
203 six_id INTEGER UNIQUE,
204 FOREIGN KEY (six_id) REFERENCES sybase_loader_test6 (id)
207 $dbh2->do('GRANT ALL ON sybase_loader_test7 TO dbicsl_user1');
209 CREATE TABLE sybase_loader_test8 (
210 id INT IDENTITY PRIMARY KEY,
211 value VARCHAR(100) NULL,
212 sybase_loader_test7_id INTEGER,
213 FOREIGN KEY (sybase_loader_test7_id) REFERENCES [dbicsl_test2].dbicsl_user2.sybase_loader_test7 (id)
217 $databases_created = 1;
219 foreach my $databases (['dbicsl_test1', 'dbicsl_test2'], '%') {
220 foreach my $owners ([qw/dbicsl_user1 dbicsl_user2/], '%') {
222 rmtree EXTRA_DUMP_DIR;
225 local $SIG{__WARN__} = sub {
226 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/
227 || $_[0] =~ /can't change context/;
230 my $database = $databases;
232 $database = [ $database ] unless ref $database;
236 foreach my $db (@$database) {
237 $db_schema->{$db} = $owners;
244 db_schema => $db_schema,
245 dump_directory => EXTRA_DUMP_DIR,
248 [ $dsn, $user, $password ],
251 diag join "\n", @warns if @warns;
254 } 'dumped schema for "dbicsl_test1" and "dbicsl_test2" databases with no warnings';
256 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
259 ok $test_schema = SybaseMultiSchema->connect($dsn, $user, $password);
260 } 'connected test schema';
263 ok $rsrc = $test_schema->source('SybaseLoaderTest4');
264 } 'got source for table in database one';
266 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
267 'column in database one';
269 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
270 'column in database one';
272 is try { $rsrc->column_info('value')->{size} }, 100,
273 'column in database one';
276 ok $rs = $test_schema->resultset('SybaseLoaderTest4');
277 } 'got resultset for table in database one';
280 ok $row = $rs->create({ value => 'foo' });
281 } 'executed SQL on table in database one';
283 $rel_info = try { $rsrc->relationship_info('sybase_loader_test5') };
285 is_deeply $rel_info->{cond}, {
286 'foreign.four_id' => 'self.id'
287 }, 'relationship in database one';
289 is $rel_info->{attrs}{accessor}, 'single',
290 'relationship in database one';
292 is $rel_info->{attrs}{join_type}, 'LEFT',
293 'relationship in database one';
296 ok $rsrc = $test_schema->source('SybaseLoaderTest5');
297 } 'got source for table in database one';
299 %uniqs = try { $rsrc->unique_constraints };
302 'got unique and primary constraint in database one';
305 ok $rsrc = $test_schema->source('SybaseLoaderTest6');
306 } 'got source for table in database two';
308 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
309 'column in database two introspected correctly';
311 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
312 'column in database two introspected correctly';
314 is try { $rsrc->column_info('value')->{size} }, 100,
315 'column in database two introspected correctly';
318 ok $rs = $test_schema->resultset('SybaseLoaderTest6');
319 } 'got resultset for table in database two';
322 ok $row = $rs->create({ value => 'foo' });
323 } 'executed SQL on table in database two';
325 $rel_info = try { $rsrc->relationship_info('sybase_loader_test7') };
327 is_deeply $rel_info->{cond}, {
328 'foreign.six_id' => 'self.id'
329 }, 'relationship in database two';
331 is $rel_info->{attrs}{accessor}, 'single',
332 'relationship in database two';
334 is $rel_info->{attrs}{join_type}, 'LEFT',
335 'relationship in database two';
338 ok $rsrc = $test_schema->source('SybaseLoaderTest7');
339 } 'got source for table in database two';
341 %uniqs = try { $rsrc->unique_constraints };
344 'got unique and primary constraint in database two';
347 ok $test_schema->source('SybaseLoaderTest6')
348 ->has_relationship('sybase_loader_test4');
349 } 'cross-database relationship in multi database schema';
352 ok $test_schema->source('SybaseLoaderTest4')
353 ->has_relationship('sybase_loader_test6s');
354 } 'cross-database relationship in multi database schema';
357 ok $test_schema->source('SybaseLoaderTest8')
358 ->has_relationship('sybase_loader_test7');
359 } 'cross-database relationship in multi database schema';
362 ok $test_schema->source('SybaseLoaderTest7')
363 ->has_relationship('sybase_loader_test8s');
364 } 'cross-database relationship in multi database schema';
372 if( !$dsn || !$user ) {
373 $tester->skip_tests('You need to set the DBICTEST_SYBASE_DSN, _USER, and _PASS environment variables');
376 $tester->run_tests();
380 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
381 rmtree EXTRA_DUMP_DIR;
383 if ($databases_created) {
384 my $dbh = $schema->storage->dbh;
386 $dbh->do('USE master');
388 local $dbh->{FetchHashKeyName} = 'NAME_lc';
390 my $sth = $dbh->prepare('sp_who');
393 while (my $row = $sth->fetchrow_hashref) {
394 if ($row->{dbname} =~ /^dbicsl_test[12]\z/) {
395 $dbh->do("kill $row->{spid}");
399 foreach my $table ('[dbicsl_test1].dbicsl_user1.sybase_loader_test8',
400 '[dbicsl_test2].dbicsl_user2.sybase_loader_test7',
401 '[dbicsl_test2].dbicsl_user2.sybase_loader_test6',
402 '[dbicsl_test1].dbicsl_user1.sybase_loader_test5',
403 '[dbicsl_test1].dbicsl_user1.sybase_loader_test4') {
405 $dbh->do("DROP TABLE $table");
408 diag "Error dropping table $table: $_";
412 foreach my $db (qw/dbicsl_test1 dbicsl_test2/) {
414 $dbh->do("DROP DATABASE [$db]");
417 diag "Error dropping test database $db: $_";
421 foreach my $login (qw/dbicsl_user1 dbicsl_user2/) {
423 my $warn_handler = $SIG{__WARN__} || sub { warn @_ };
424 local $SIG{__WARN__} = sub {
426 unless $_[0] =~ /^Account locked\.$|^Login dropped\.$/;
429 $dbh->do("sp_droplogin $login");
432 diag "Error dropping login $login: $_"
433 unless /Incorrect syntax/;
439 # vim:et sts=4 sw=4 tw=0: