[dbsrgits/DBIx-Class-Schema-Loader.git] / t / 10_06sybase_common.t

use DBIx::Class::Schema::Loader::Optional::Dependencies
    -skip_all_without => 'test_rdbms_ase';

use strict;
use warnings;
use Test::More;
use Test::Exception;
use Try::Tiny;
use File::Path 'rmtree';
use DBIx::Class::Schema::Loader 'make_schema_at';
use DBIx::Class::Schema::Loader::Utils qw/sigwarn_silencer/;
use DBI ();

use lib qw(t/lib);

use dbixcsl_common_tests ();
use dbixcsl_test_dir '$tdir';

use constant EXTRA_DUMP_DIR => "$tdir/sybase_extra_dump";

my $dsn      = $ENV{DBICTEST_SYBASE_DSN} || '';
my $user     = $ENV{DBICTEST_SYBASE_USER} || '';
my $password = $ENV{DBICTEST_SYBASE_PASS} || '';

BEGIN { $ENV{DBIC_SYBASE_FREETDS_NOWARN} = 1 }

my ($schema, $databases_created); # for cleanup in END for extra tests

dbixcsl_common_tests->new(
    vendor      => 'sybase',
    auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
    default_function     => 'getdate()',
    default_function_def => 'AS getdate()',
    dsn         => $dsn,
    user        => $user,
    password    => $password,
    data_types  => {
        # http://ispirer.com/wiki/sqlways/sybase/data-types
        #
        # Numeric Types
        'integer identity' => { data_type => 'integer', is_auto_increment => 1 },
        int      => { data_type => 'integer' },
        integer  => { data_type => 'integer' },
        bigint   => { data_type => 'bigint' },
        smallint => { data_type => 'smallint' },
        tinyint  => { data_type => 'tinyint' },
        'double precision' => { data_type => 'double precision' },
        real           => { data_type => 'real' },
        float          => { data_type => 'double precision' },
        'float(14)'    => { data_type => 'real' },
        'float(15)'    => { data_type => 'real' },
        'float(16)'    => { data_type => 'double precision' },
        'float(48)'    => { data_type => 'double precision' },
        'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
        'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
        numeric        => { data_type => 'numeric' },
        decimal        => { data_type => 'numeric' },
        bit            => { data_type => 'bit' },

        # Money Types
        money          => { data_type => 'money' },
        smallmoney     => { data_type => 'smallmoney' },

        # Computed Column
        'AS getdate()'     => { data_type => undef, inflate_datetime => 1, default_value => \'getdate()' },

        # Blob Types
        text     => { data_type => 'text' },
        unitext  => { data_type => 'unitext' },
        image    => { data_type => 'image' },

        # DateTime Types
        date     => { data_type => 'date' },
        time     => { data_type => 'time' },
        datetime => { data_type => 'datetime' },
        smalldatetime  => { data_type => 'smalldatetime' },

        # Timestamp column
        timestamp      => { data_type => 'timestamp', inflate_datetime => 0 },

        # String Types
        'char'         => { data_type => 'char', size => 1 },
        'char(2)'      => { data_type => 'char', size => 2 },
        'nchar'        => { data_type => 'nchar', size => 1 },
        'nchar(2)'     => { data_type => 'nchar', size => 2 },
        'unichar(2)'   => { data_type => 'unichar', size => 2 },
        'varchar(2)'   => { data_type => 'varchar', size => 2 },
        'nvarchar(2)'  => { data_type => 'nvarchar', size => 2 },
        'univarchar(2)' => { data_type => 'univarchar', size => 2 },

        # Binary Types
        'binary'       => { data_type => 'binary', size => 1 },
        'binary(2)'    => { data_type => 'binary', size => 2 },
        'varbinary(2)' => { data_type => 'varbinary', size => 2 },
    },
    # test that named constraints aren't picked up as tables (I can't reproduce this on my machine)
    failtrigger_warnings => [ qr/^Bad table or view 'sybase_loader_test2_ref_slt1'/ ],
    extra => {
        create => [
            q{
                CREATE TABLE sybase_loader_test1 (
                    id int identity primary key
                )
            },
            q{
                CREATE TABLE sybase_loader_test2 (
                    id int identity primary key,
                    sybase_loader_test1_id int,
                    CONSTRAINT sybase_loader_test2_ref_slt1 FOREIGN KEY (sybase_loader_test1_id) REFERENCES sybase_loader_test1 (id)
                )
            },
        ],
        drop => [ qw/sybase_loader_test1 sybase_loader_test2/ ],
        count => 30 * 4,
        run => sub {
            $schema = shift;

            SKIP: {
                my $dbh = $schema->storage->dbh;

                try {
                    $dbh->do('USE master');
                }
                catch {
                    skip "these tests require the sysadmin role", 30 * 4;
                };

                try {
                    $dbh->do('CREATE DATABASE [dbicsl_test1]');
                    $dbh->do('CREATE DATABASE [dbicsl_test2]');
                }
                catch {
                    skip "cannot create databases: $_", 30 * 4;
                };

                try {
                    local $SIG{__WARN__} = sigwarn_silencer(
                        qr/^Password correctly set\.$|^Account unlocked\.$|^New login created\.$|^New user added\.$/
                    );

                    $dbh->do("sp_addlogin dbicsl_user1, dbicsl, [dbicsl_test1]");
                    $dbh->do("sp_addlogin dbicsl_user2, dbicsl, [dbicsl_test2]");

                    $dbh->do("USE [dbicsl_test1]");
                    $dbh->do("sp_adduser dbicsl_user1");
                    $dbh->do("sp_adduser dbicsl_user2");
                    $dbh->do("GRANT ALL TO dbicsl_user1");
                    $dbh->do("GRANT ALL TO dbicsl_user2");

                    $dbh->do("USE [dbicsl_test2]");
                    $dbh->do("sp_adduser dbicsl_user2");
                    $dbh->do("sp_adduser dbicsl_user1");
                    $dbh->do("GRANT ALL TO dbicsl_user2");
                    $dbh->do("GRANT ALL TO dbicsl_user1");
                }
                catch {
                    skip "cannot add logins: $_", 30 * 4;
                };

                my ($dbh1, $dbh2);
                {
                    local $SIG{__WARN__} = sigwarn_silencer(
                        qr/can't change context/
                    );
                    $dbh1 = DBI->connect($dsn, 'dbicsl_user1', 'dbicsl', {
                        RaiseError => 1,
                        PrintError => 0,
                    });
                    $dbh1->do('USE [dbicsl_test1]');

                    $dbh2 = DBI->connect($dsn, 'dbicsl_user2', 'dbicsl', {
                        RaiseError => 1,
                        PrintError => 0,
                    });
                    $dbh2->do('USE [dbicsl_test2]');
                }

                $dbh1->do(<<"EOF");
                    CREATE TABLE sybase_loader_test4 (
                        id INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL
                    )
EOF
                $dbh1->do('GRANT ALL ON sybase_loader_test4 TO dbicsl_user2');
                $dbh1->do(<<"EOF");
                    CREATE TABLE sybase_loader_test5 (
                        id INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL,
                        four_id INTEGER,
                        CONSTRAINT loader_test5_uniq UNIQUE (four_id),
                        FOREIGN KEY (four_id) REFERENCES sybase_loader_test4 (id)
                    )
EOF
                $dbh2->do(<<"EOF");
                    CREATE TABLE sybase_loader_test5 (
                        pk INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL,
                        four_id INTEGER,
                        CONSTRAINT loader_test5_uniq UNIQUE (four_id),
                        FOREIGN KEY (four_id) REFERENCES [dbicsl_test1].dbicsl_user1.sybase_loader_test4 (id)
                    )
EOF
                $dbh2->do(<<"EOF");
                    CREATE TABLE sybase_loader_test6 (
                        id INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL,
                        sybase_loader_test4_id INTEGER NULL,
                        FOREIGN KEY (sybase_loader_test4_id) REFERENCES [dbicsl_test1].dbicsl_user1.sybase_loader_test4 (id)
                    )
EOF
                $dbh2->do(<<"EOF");
                    CREATE TABLE sybase_loader_test7 (
                        id INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL,
                        six_id INTEGER UNIQUE,
                        FOREIGN KEY (six_id) REFERENCES sybase_loader_test6 (id)
                    )
EOF
                $dbh2->do('GRANT ALL ON sybase_loader_test7 TO dbicsl_user1');
                $dbh1->do(<<"EOF");
                    CREATE TABLE sybase_loader_test8 (
                        id INT IDENTITY PRIMARY KEY,
                        value VARCHAR(100) NULL,
                        sybase_loader_test7_id INTEGER,
                        FOREIGN KEY (sybase_loader_test7_id) REFERENCES [dbicsl_test2].dbicsl_user2.sybase_loader_test7 (id)
                    )
EOF

                $databases_created = 1;

                foreach my $databases (['dbicsl_test1', 'dbicsl_test2'], '%') {
                    foreach my $owners ([qw/dbicsl_user1 dbicsl_user2/], '%') {
                        lives_and {
                            rmtree EXTRA_DUMP_DIR;

                            my @warns;
                            local $SIG{__WARN__} = sub {
                                push @warns, $_[0] unless $_[0] =~ /\bcollides\b/
                                    || $_[0] =~ /can't change context/;
                            };

                            my $database = $databases;

                            $database = [ $database ] unless ref $database;

                            my $db_schema = {};

                            foreach my $db (@$database) {
                                $db_schema->{$db} = $owners;
                            }

                            make_schema_at(
                                'SybaseMultiSchema',
                                {
                                    naming => 'current',
                                    db_schema => $db_schema,
                                    dump_directory => EXTRA_DUMP_DIR,
                                    quiet => 1,
                                },
                                [ $dsn, $user, $password ],
                            );

                            SybaseMultiSchema->storage->disconnect;

                            diag join "\n", @warns if @warns;

                            is @warns, 0;
                        } 'dumped schema for "dbicsl_test1" and "dbicsl_test2" databases with no warnings';

                        my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);

                        lives_and {
                            ok $test_schema = SybaseMultiSchema->connect($dsn, $user, $password);
                        } 'connected test schema';

                        lives_and {
                            ok $rsrc = $test_schema->source('SybaseLoaderTest4');
                        } 'got source for table in database one';

                        is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
                            'column in database one';

                        is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
                            'column in database one';

                        is try { $rsrc->column_info('value')->{size} }, 100,
                            'column in database one';

                        lives_and {
                            ok $rs = $test_schema->resultset('SybaseLoaderTest4');
                        } 'got resultset for table in database one';

                        lives_and {
                            ok $row = $rs->create({ value => 'foo' });
                        } 'executed SQL on table in database one';

                        $rel_info = try { $rsrc->relationship_info('dbicsl_test1_sybase_loader_test5') };

                        is_deeply $rel_info->{cond}, {
                            'foreign.four_id' => 'self.id'
                        }, 'relationship in database one';

                        is $rel_info->{attrs}{accessor}, 'single',
                            'relationship in database one';

                        is $rel_info->{attrs}{join_type}, 'LEFT',
                            'relationship in database one';

                        lives_and {
                            ok $rsrc = $test_schema->source('DbicslTest1SybaseLoaderTest5');
                        } 'got source for table in database one';

                        %uniqs = try { $rsrc->unique_constraints };

                        is keys %uniqs, 2,
                            'got unique and primary constraint in database one';

                        delete $uniqs{primary};

                        is_deeply ((values %uniqs)[0], ['four_id'],
                            'correct unique constraint in database one');

                        lives_and {
                            ok $rsrc = $test_schema->source('SybaseLoaderTest6');
                        } 'got source for table in database two';

                        is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
                            'column in database two introspected correctly';

                        is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
                            'column in database two introspected correctly';

                        is try { $rsrc->column_info('value')->{size} }, 100,
                            'column in database two introspected correctly';

                        lives_and {
                            ok $rs = $test_schema->resultset('SybaseLoaderTest6');
                        } 'got resultset for table in database two';

                        lives_and {
                            ok $row = $rs->create({ value => 'foo' });
                        } 'executed SQL on table in database two';

                        $rel_info = try { $rsrc->relationship_info('sybase_loader_test7') };

                        is_deeply $rel_info->{cond}, {
                            'foreign.six_id' => 'self.id'
                        }, 'relationship in database two';

                        is $rel_info->{attrs}{accessor}, 'single',
                            'relationship in database two';

                        is $rel_info->{attrs}{join_type}, 'LEFT',
                            'relationship in database two';

                        lives_and {
                            ok $rsrc = $test_schema->source('SybaseLoaderTest7');
                        } 'got source for table in database two';

                        %uniqs = try { $rsrc->unique_constraints };

                        is keys %uniqs, 2,
                            'got unique and primary constraint in database two';

                        delete $uniqs{primary};

                        is_deeply ((values %uniqs)[0], ['six_id'],
                            'correct unique constraint in database two');

                        lives_and {
                            ok $test_schema->source('SybaseLoaderTest6')
                                ->has_relationship('sybase_loader_test4');
                        } 'cross-database relationship in multi database schema';

                        lives_and {
                            ok $test_schema->source('SybaseLoaderTest4')
                                ->has_relationship('sybase_loader_test6s');
                        } 'cross-database relationship in multi database schema';

                        lives_and {
                            ok $test_schema->source('SybaseLoaderTest8')
                                ->has_relationship('sybase_loader_test7');
                        } 'cross-database relationship in multi database schema';

                        lives_and {
                            ok $test_schema->source('SybaseLoaderTest7')
                                ->has_relationship('sybase_loader_test8s');
                        } 'cross-database relationship in multi database schema';
                    }
                }
            }
        },
    },
)->run_tests();

END {
    if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
        rmtree EXTRA_DUMP_DIR;

        if ($databases_created) {
            my $dbh = $schema->storage->dbh;

            $dbh->do('USE master');

            local $dbh->{FetchHashKeyName} = 'NAME_lc';

            my $sth = $dbh->prepare('sp_who');
            $sth->execute;

            while (my $row = $sth->fetchrow_hashref) {
                if ($row->{dbname} =~ /^dbicsl_test[12]\z/) {
                    $dbh->do("kill $row->{spid}");
                }
            }

            foreach my $table ('[dbicsl_test1].dbicsl_user1.sybase_loader_test8',
                               '[dbicsl_test2].dbicsl_user2.sybase_loader_test7',
                               '[dbicsl_test2].dbicsl_user2.sybase_loader_test6',
                               '[dbicsl_test2].dbicsl_user2.sybase_loader_test5',
                               '[dbicsl_test1].dbicsl_user1.sybase_loader_test5',
                               '[dbicsl_test1].dbicsl_user1.sybase_loader_test4') {
                try {
                    $dbh->do("DROP TABLE $table");
                }
                catch {
                    diag "Error dropping table $table: $_";
                };
            }

            foreach my $db (qw/dbicsl_test1 dbicsl_test2/) {
                try {
                    $dbh->do("DROP DATABASE [$db]");
                }
                catch {
                    diag "Error dropping test database $db: $_";
                };
            }

            foreach my $login (qw/dbicsl_user1 dbicsl_user2/) {
                try {
                    local $SIG{__WARN__} = sigwarn_silencer(
                        qr/^Account locked\.$|^Login dropped\.$/
                    );

                    $dbh->do("sp_droplogin $login");
                }
                catch {
                    diag "Error dropping login $login: $_"
                        unless /Incorrect syntax/;
                };
            }
        }
    }
}
# vim:et sts=4 sw=4 tw=0:
Commit	Line	Data
406a97c2	1	use DBIx::Class::Schema::Loader::Optional::Dependencies
	2	-skip_all_without => 'test_rdbms_ase';
	3
fe67d343	4	use strict;
c4a69b87	5	use warnings;
804c115d	6	use Test::More;
2d1dc6de	7	use Test::Exception;
c4a69b87	8	use Try::Tiny;
	9	use File::Path 'rmtree';
	10	use DBIx::Class::Schema::Loader 'make_schema_at';
ff4b0152	11	use DBIx::Class::Schema::Loader::Utils qw/sigwarn_silencer/;
c4a69b87	12	use DBI ();
	13
	14	use lib qw(t/lib);
	15
	16	use dbixcsl_common_tests ();
	17	use dbixcsl_test_dir '$tdir';
	18
	19	use constant EXTRA_DUMP_DIR => "$tdir/sybase_extra_dump";
f9f65ded	20
fe67d343	21	my $dsn = $ENV{DBICTEST_SYBASE_DSN} \|\| '';
	22	my $user = $ENV{DBICTEST_SYBASE_USER} \|\| '';
	23	my $password = $ENV{DBICTEST_SYBASE_PASS} \|\| '';
	24
4c2e2ce9	25	BEGIN { $ENV{DBIC_SYBASE_FREETDS_NOWARN} = 1 }
4c2e2ce9	26
c4a69b87	27	my ($schema, $databases_created); # for cleanup in END for extra tests
c4a69b87	28
406a97c2	29	dbixcsl_common_tests->new(
7cb9244f	30	vendor => 'sybase',
fe67d343	31	auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
41968729	32	default_function => 'getdate()',
41968729	33	default_function_def => 'AS getdate()',
fe67d343	34	dsn => $dsn,
	35	user => $user,
	36	password => $password,
5163dc4a	37	data_types => {
760fd65c	38	# http://ispirer.com/wiki/sqlways/sybase/data-types
6bbfc7ed	39	#
6bbfc7ed	40	# Numeric Types
5163dc4a	41	'integer identity' => { data_type => 'integer', is_auto_increment => 1 },
5163dc4a	42	int => { data_type => 'integer' },
5163dc4a	43	integer => { data_type => 'integer' },
6bbfc7ed	44	bigint => { data_type => 'bigint' },
5163dc4a	45	smallint => { data_type => 'smallint' },
5163dc4a	46	tinyint => { data_type => 'tinyint' },
6bbfc7ed	47	'double precision' => { data_type => 'double precision' },
	48	real => { data_type => 'real' },
	49	float => { data_type => 'double precision' },
	50	'float(14)' => { data_type => 'real' },
	51	'float(15)' => { data_type => 'real' },
	52	'float(16)' => { data_type => 'double precision' },
	53	'float(48)' => { data_type => 'double precision' },
	54	'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
	55	'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
	56	numeric => { data_type => 'numeric' },
	57	decimal => { data_type => 'numeric' },
	58	bit => { data_type => 'bit' },
	59
	60	# Money Types
	61	money => { data_type => 'money' },
	62	smallmoney => { data_type => 'smallmoney' },
	63
	64	# Computed Column
	65	'AS getdate()' => { data_type => undef, inflate_datetime => 1, default_value => \'getdate()' },
	66
	67	# Blob Types
	68	text => { data_type => 'text' },
	69	unitext => { data_type => 'unitext' },
	70	image => { data_type => 'image' },
	71
	72	# DateTime Types
5163dc4a	73	date => { data_type => 'date' },
	74	time => { data_type => 'time' },
	75	datetime => { data_type => 'datetime' },
	76	smalldatetime => { data_type => 'smalldatetime' },
6bbfc7ed	77
6bbfc7ed	78	# Timestamp column
5163dc4a	79	timestamp => { data_type => 'timestamp', inflate_datetime => 0 },
6bbfc7ed	80
	81	# String Types
	82	'char' => { data_type => 'char', size => 1 },
5163dc4a	83	'char(2)' => { data_type => 'char', size => 2 },
6bbfc7ed	84	'nchar' => { data_type => 'nchar', size => 1 },
5163dc4a	85	'nchar(2)' => { data_type => 'nchar', size => 2 },
	86	'unichar(2)' => { data_type => 'unichar', size => 2 },
	87	'varchar(2)' => { data_type => 'varchar', size => 2 },
	88	'nvarchar(2)' => { data_type => 'nvarchar', size => 2 },
	89	'univarchar(2)' => { data_type => 'univarchar', size => 2 },
6bbfc7ed	90
	91	# Binary Types
	92	'binary' => { data_type => 'binary', size => 1 },
5163dc4a	93	'binary(2)' => { data_type => 'binary', size => 2 },
5163dc4a	94	'varbinary(2)' => { data_type => 'varbinary', size => 2 },
804c115d	95	},
8f65b7e5	96	# test that named constraints aren't picked up as tables (I can't reproduce this on my machine)
8f65b7e5	97	failtrigger_warnings => [ qr/^Bad table or view 'sybase_loader_test2_ref_slt1'/ ],
5c1b0a23	98	extra => {
	99	create => [
	100	q{
	101	CREATE TABLE sybase_loader_test1 (
	102	id int identity primary key
	103	)
	104	},
	105	q{
	106	CREATE TABLE sybase_loader_test2 (
	107	id int identity primary key,
	108	sybase_loader_test1_id int,
	109	CONSTRAINT sybase_loader_test2_ref_slt1 FOREIGN KEY (sybase_loader_test1_id) REFERENCES sybase_loader_test1 (id)
	110	)
	111	},
	112	],
	113	drop => [ qw/sybase_loader_test1 sybase_loader_test2/ ],
5975bbe6	114	count => 30 * 4,
c4a69b87	115	run => sub {
	116	$schema = shift;
	117
	118	SKIP: {
	119	my $dbh = $schema->storage->dbh;
	120
	121	try {
	122	$dbh->do('USE master');
	123	}
	124	catch {
5975bbe6	125	skip "these tests require the sysadmin role", 30 * 4;
c4a69b87	126	};
	127
	128	try {
	129	$dbh->do('CREATE DATABASE [dbicsl_test1]');
	130	$dbh->do('CREATE DATABASE [dbicsl_test2]');
	131	}
	132	catch {
5975bbe6	133	skip "cannot create databases: $_", 30 * 4;
c4a69b87	134	};
	135
	136	try {
ff4b0152	137	local $SIG{__WARN__} = sigwarn_silencer(
	138	qr/^Password correctly set\.$\|^Account unlocked\.$\|^New login created\.$\|^New user added\.$/
	139	);
c4a69b87	140
	141	$dbh->do("sp_addlogin dbicsl_user1, dbicsl, [dbicsl_test1]");
	142	$dbh->do("sp_addlogin dbicsl_user2, dbicsl, [dbicsl_test2]");
	143
	144	$dbh->do("USE [dbicsl_test1]");
	145	$dbh->do("sp_adduser dbicsl_user1");
	146	$dbh->do("sp_adduser dbicsl_user2");
	147	$dbh->do("GRANT ALL TO dbicsl_user1");
	148	$dbh->do("GRANT ALL TO dbicsl_user2");
	149
	150	$dbh->do("USE [dbicsl_test2]");
	151	$dbh->do("sp_adduser dbicsl_user2");
	152	$dbh->do("sp_adduser dbicsl_user1");
	153	$dbh->do("GRANT ALL TO dbicsl_user2");
	154	$dbh->do("GRANT ALL TO dbicsl_user1");
	155	}
	156	catch {
5975bbe6	157	skip "cannot add logins: $_", 30 * 4;
c4a69b87	158	};
	159
	160	my ($dbh1, $dbh2);
	161	{
ff4b0152	162	local $SIG{__WARN__} = sigwarn_silencer(
	163	qr/can't change context/
	164	);
c4a69b87	165	$dbh1 = DBI->connect($dsn, 'dbicsl_user1', 'dbicsl', {
	166	RaiseError => 1,
	167	PrintError => 0,
	168	});
	169	$dbh1->do('USE [dbicsl_test1]');
	170
	171	$dbh2 = DBI->connect($dsn, 'dbicsl_user2', 'dbicsl', {
	172	RaiseError => 1,
	173	PrintError => 0,
	174	});
	175	$dbh2->do('USE [dbicsl_test2]');
	176	}
	177
	178	$dbh1->do(<<"EOF");
	179	CREATE TABLE sybase_loader_test4 (
	180	id INT IDENTITY PRIMARY KEY,
	181	value VARCHAR(100) NULL
	182	)
	183	EOF
	184	$dbh1->do('GRANT ALL ON sybase_loader_test4 TO dbicsl_user2');
	185	$dbh1->do(<<"EOF");
	186	CREATE TABLE sybase_loader_test5 (
	187	id INT IDENTITY PRIMARY KEY,
	188	value VARCHAR(100) NULL,
5975bbe6	189	four_id INTEGER,
5975bbe6	190	CONSTRAINT loader_test5_uniq UNIQUE (four_id),
c4a69b87	191	FOREIGN KEY (four_id) REFERENCES sybase_loader_test4 (id)
	192	)
	193	EOF
	194	$dbh2->do(<<"EOF");
5975bbe6	195	CREATE TABLE sybase_loader_test5 (
	196	pk INT IDENTITY PRIMARY KEY,
	197	value VARCHAR(100) NULL,
	198	four_id INTEGER,
	199	CONSTRAINT loader_test5_uniq UNIQUE (four_id),
	200	FOREIGN KEY (four_id) REFERENCES [dbicsl_test1].dbicsl_user1.sybase_loader_test4 (id)
	201	)
	202	EOF
	203	$dbh2->do(<<"EOF");
c4a69b87	204	CREATE TABLE sybase_loader_test6 (
	205	id INT IDENTITY PRIMARY KEY,
	206	value VARCHAR(100) NULL,
	207	sybase_loader_test4_id INTEGER NULL,
	208	FOREIGN KEY (sybase_loader_test4_id) REFERENCES [dbicsl_test1].dbicsl_user1.sybase_loader_test4 (id)
	209	)
	210	EOF
	211	$dbh2->do(<<"EOF");
	212	CREATE TABLE sybase_loader_test7 (
	213	id INT IDENTITY PRIMARY KEY,
	214	value VARCHAR(100) NULL,
	215	six_id INTEGER UNIQUE,
	216	FOREIGN KEY (six_id) REFERENCES sybase_loader_test6 (id)
	217	)
	218	EOF
	219	$dbh2->do('GRANT ALL ON sybase_loader_test7 TO dbicsl_user1');
	220	$dbh1->do(<<"EOF");
	221	CREATE TABLE sybase_loader_test8 (
	222	id INT IDENTITY PRIMARY KEY,
	223	value VARCHAR(100) NULL,
	224	sybase_loader_test7_id INTEGER,
	225	FOREIGN KEY (sybase_loader_test7_id) REFERENCES [dbicsl_test2].dbicsl_user2.sybase_loader_test7 (id)
	226	)
	227	EOF
	228
	229	$databases_created = 1;
	230
	231	foreach my $databases (['dbicsl_test1', 'dbicsl_test2'], '%') {
	232	foreach my $owners ([qw/dbicsl_user1 dbicsl_user2/], '%') {
	233	lives_and {
	234	rmtree EXTRA_DUMP_DIR;
	235
	236	my @warns;
	237	local $SIG{__WARN__} = sub {
	238	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/
	239	\|\| $_[0] =~ /can't change context/;
	240	};
	241
	242	my $database = $databases;
	243
	244	$database = [ $database ] unless ref $database;
	245
	246	my $db_schema = {};
	247
	248	foreach my $db (@$database) {
	249	$db_schema->{$db} = $owners;
	250	}
	251
	252	make_schema_at(
	253	'SybaseMultiSchema',
	254	{
	255	naming => 'current',
	256	db_schema => $db_schema,
	257	dump_directory => EXTRA_DUMP_DIR,
	258	quiet => 1,
	259	},
	260	[ $dsn, $user, $password ],
	261	);
	262
e17ad40a	263	SybaseMultiSchema->storage->disconnect;
e17ad40a	264
c4a69b87	265	diag join "\n", @warns if @warns;
	266
	267	is @warns, 0;
	268	} 'dumped schema for "dbicsl_test1" and "dbicsl_test2" databases with no warnings';
	269
	270	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
	271
	272	lives_and {
	273	ok $test_schema = SybaseMultiSchema->connect($dsn, $user, $password);
	274	} 'connected test schema';
	275
	276	lives_and {
4c2e2ce9	277	ok $rsrc = $test_schema->source('SybaseLoaderTest4');
c4a69b87	278	} 'got source for table in database one';
	279
	280	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	281	'column in database one';
	282
	283	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
	284	'column in database one';
	285
	286	is try { $rsrc->column_info('value')->{size} }, 100,
	287	'column in database one';
	288
	289	lives_and {
4c2e2ce9	290	ok $rs = $test_schema->resultset('SybaseLoaderTest4');
c4a69b87	291	} 'got resultset for table in database one';
	292
	293	lives_and {
	294	ok $row = $rs->create({ value => 'foo' });
	295	} 'executed SQL on table in database one';
	296
5975bbe6	297	$rel_info = try { $rsrc->relationship_info('dbicsl_test1_sybase_loader_test5') };
c4a69b87	298
	299	is_deeply $rel_info->{cond}, {
	300	'foreign.four_id' => 'self.id'
	301	}, 'relationship in database one';
	302
	303	is $rel_info->{attrs}{accessor}, 'single',
	304	'relationship in database one';
	305
	306	is $rel_info->{attrs}{join_type}, 'LEFT',
	307	'relationship in database one';
	308
	309	lives_and {
5975bbe6	310	ok $rsrc = $test_schema->source('DbicslTest1SybaseLoaderTest5');
c4a69b87	311	} 'got source for table in database one';
	312
	313	%uniqs = try { $rsrc->unique_constraints };
	314
	315	is keys %uniqs, 2,
	316	'got unique and primary constraint in database one';
	317
5975bbe6	318	delete $uniqs{primary};
	319
	320	is_deeply ((values %uniqs)[0], ['four_id'],
	321	'correct unique constraint in database one');
	322
c4a69b87	323	lives_and {
4c2e2ce9	324	ok $rsrc = $test_schema->source('SybaseLoaderTest6');
c4a69b87	325	} 'got source for table in database two';
	326
	327	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	328	'column in database two introspected correctly';
	329
	330	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
	331	'column in database two introspected correctly';
	332
	333	is try { $rsrc->column_info('value')->{size} }, 100,
	334	'column in database two introspected correctly';
	335
	336	lives_and {
4c2e2ce9	337	ok $rs = $test_schema->resultset('SybaseLoaderTest6');
c4a69b87	338	} 'got resultset for table in database two';
	339
	340	lives_and {
	341	ok $row = $rs->create({ value => 'foo' });
	342	} 'executed SQL on table in database two';
	343
	344	$rel_info = try { $rsrc->relationship_info('sybase_loader_test7') };
	345
	346	is_deeply $rel_info->{cond}, {
	347	'foreign.six_id' => 'self.id'
	348	}, 'relationship in database two';
	349
	350	is $rel_info->{attrs}{accessor}, 'single',
	351	'relationship in database two';
	352
	353	is $rel_info->{attrs}{join_type}, 'LEFT',
	354	'relationship in database two';
	355
	356	lives_and {
4c2e2ce9	357	ok $rsrc = $test_schema->source('SybaseLoaderTest7');
c4a69b87	358	} 'got source for table in database two';
	359
	360	%uniqs = try { $rsrc->unique_constraints };
	361
	362	is keys %uniqs, 2,
	363	'got unique and primary constraint in database two';
	364
5975bbe6	365	delete $uniqs{primary};
	366
	367	is_deeply ((values %uniqs)[0], ['six_id'],
	368	'correct unique constraint in database two');
	369
c4a69b87	370	lives_and {
4c2e2ce9	371	ok $test_schema->source('SybaseLoaderTest6')
c4a69b87	372	->has_relationship('sybase_loader_test4');
	373	} 'cross-database relationship in multi database schema';
	374
	375	lives_and {
4c2e2ce9	376	ok $test_schema->source('SybaseLoaderTest4')
c4a69b87	377	->has_relationship('sybase_loader_test6s');
	378	} 'cross-database relationship in multi database schema';
	379
	380	lives_and {
4c2e2ce9	381	ok $test_schema->source('SybaseLoaderTest8')
c4a69b87	382	->has_relationship('sybase_loader_test7');
	383	} 'cross-database relationship in multi database schema';
	384
	385	lives_and {
4c2e2ce9	386	ok $test_schema->source('SybaseLoaderTest7')
c4a69b87	387	->has_relationship('sybase_loader_test8s');
	388	} 'cross-database relationship in multi database schema';
	389	}
	390	}
	391	}
	392	},
5c1b0a23	393	},
406a97c2	394	)->run_tests();
5c1b0a23	395
c4a69b87	396	END {
	397	if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
	398	rmtree EXTRA_DUMP_DIR;
	399
	400	if ($databases_created) {
	401	my $dbh = $schema->storage->dbh;
	402
	403	$dbh->do('USE master');
	404
	405	local $dbh->{FetchHashKeyName} = 'NAME_lc';
	406
	407	my $sth = $dbh->prepare('sp_who');
	408	$sth->execute;
	409
	410	while (my $row = $sth->fetchrow_hashref) {
	411	if ($row->{dbname} =~ /^dbicsl_test[12]\z/) {
	412	$dbh->do("kill $row->{spid}");
	413	}
	414	}
	415
	416	foreach my $table ('[dbicsl_test1].dbicsl_user1.sybase_loader_test8',
	417	'[dbicsl_test2].dbicsl_user2.sybase_loader_test7',
	418	'[dbicsl_test2].dbicsl_user2.sybase_loader_test6',
5975bbe6	419	'[dbicsl_test2].dbicsl_user2.sybase_loader_test5',
c4a69b87	420	'[dbicsl_test1].dbicsl_user1.sybase_loader_test5',
	421	'[dbicsl_test1].dbicsl_user1.sybase_loader_test4') {
	422	try {
	423	$dbh->do("DROP TABLE $table");
	424	}
	425	catch {
	426	diag "Error dropping table $table: $_";
	427	};
	428	}
	429
	430	foreach my $db (qw/dbicsl_test1 dbicsl_test2/) {
	431	try {
	432	$dbh->do("DROP DATABASE [$db]");
	433	}
	434	catch {
	435	diag "Error dropping test database $db: $_";
	436	};
	437	}
	438
	439	foreach my $login (qw/dbicsl_user1 dbicsl_user2/) {
	440	try {
ff4b0152	441	local $SIG{__WARN__} = sigwarn_silencer(
	442	qr/^Account locked\.$\|^Login dropped\.$/
	443	);
c4a69b87	444
	445	$dbh->do("sp_droplogin $login");
	446	}
	447	catch {
	448	diag "Error dropping login $login: $_"
	449	unless /Incorrect syntax/;
	450	};
	451	}
	452	}
	453	}
	454	}
5c1b0a23	455	# vim:et sts=4 sw=4 tw=0: