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

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 Scope::Guard ();

use lib qw(t/lib);

use dbixcsl_common_tests;
use dbixcsl_test_dir '$tdir';

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

# The default max_cursor_count and max_statement_count settings of 50 are too
# low to run this test.
#
# Setting them to zero is preferred.

my $dbd_sqlanywhere_dsn      = $ENV{DBICTEST_SQLANYWHERE_DSN} || '';
my $dbd_sqlanywhere_user     = $ENV{DBICTEST_SQLANYWHERE_USER} || '';
my $dbd_sqlanywhere_password = $ENV{DBICTEST_SQLANYWHERE_PASS} || '';

my $odbc_dsn      = $ENV{DBICTEST_SQLANYWHERE_ODBC_DSN} || '';
my $odbc_user     = $ENV{DBICTEST_SQLANYWHERE_ODBC_USER} || '';
my $odbc_password = $ENV{DBICTEST_SQLANYWHERE_ODBC_PASS} || '';

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

my $tester = dbixcsl_common_tests->new(
    vendor      => 'SQLAnywhere',
    auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
    connect_info => [ ($dbd_sqlanywhere_dsn ? {
            dsn         => $dbd_sqlanywhere_dsn,
            user        => $dbd_sqlanywhere_user,
            password    => $dbd_sqlanywhere_password,
        } : ()),
        ($odbc_dsn ? {
            dsn         => $odbc_dsn,
            user        => $odbc_user,
            password    => $odbc_password,
        } : ()),
    ],
    loader_options => { preserve_case => 1 },
    data_types  => {
        # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
        #
        # Numeric types
        'bit'         => { data_type => 'bit' },
        'tinyint'     => { data_type => 'tinyint' },
        'smallint'    => { data_type => 'smallint' },
        'int'         => { data_type => 'integer' },
        'integer'     => { data_type => 'integer' },
        'bigint'      => { data_type => 'bigint' },
        'float'       => { data_type => 'real' },
        'real'        => { data_type => 'real' },
        'double'      => { data_type => 'double precision' },
        'double precision' =>
                         { data_type => 'double precision' },

        'float(2)'    => { data_type => 'real' },
        'float(24)'   => { data_type => 'real' },
        'float(25)'   => { data_type => 'double precision' },
        'float(53)'   => { data_type => 'double precision' },

        # This test only works with the default precision and scale options.
        #
        # They are preserved even for the default values, because the defaults
        # can be changed.
        'decimal'     => { data_type => 'decimal', size => [30,6] },
        'dec'         => { data_type => 'decimal', size => [30,6] },
        'numeric'     => { data_type => 'numeric', size => [30,6] },

        'decimal(3)'   => { data_type => 'decimal', size => [3,0] },
        'dec(3)'       => { data_type => 'decimal', size => [3,0] },
        'numeric(3)'   => { data_type => 'numeric', size => [3,0] },

        'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
        'dec(3,3)'     => { data_type => 'decimal', size => [3,3] },
        'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },

        'decimal(18,18)' => { data_type => 'decimal', size => [18,18] },
        'dec(18,18)'     => { data_type => 'decimal', size => [18,18] },
        'numeric(18,18)' => { data_type => 'numeric', size => [18,18] },

        # money types
        'money'        => { data_type => 'money' },
        'smallmoney'   => { data_type => 'smallmoney' },

        # bit arrays
        'long varbit'  => { data_type => 'long varbit' },
        'long bit varying'
                       => { data_type => 'long varbit' },
        'varbit'       => { data_type => 'varbit', size => 1 },
        'varbit(20)'   => { data_type => 'varbit', size => 20 },
        'bit varying'  => { data_type => 'varbit', size => 1 },
        'bit varying(20)'
                       => { data_type => 'varbit', size => 20 },

        # Date and Time Types
        'date'        => { data_type => 'date' },
        'datetime'    => { data_type => 'datetime' },
        'smalldatetime'
                      => { data_type => 'smalldatetime' },
        'timestamp'   => { data_type => 'timestamp' },
        # rewrite 'current timestamp' as 'current_timestamp'
        'timestamp default current timestamp'
                      => { data_type => 'timestamp', default_value => \'current_timestamp',
                           original => { default_value => \'current timestamp' } },
        'time'        => { data_type => 'time' },

        # String Types
        'char'         => { data_type => 'char',      size => 1  },
        'char(11)'     => { data_type => 'char',      size => 11 },
        'nchar'        => { data_type => 'nchar',     size => 1  },
        'nchar(11)'    => { data_type => 'nchar',     size => 11 },
        'varchar'      => { data_type => 'varchar',   size => 1  },
        'varchar(20)'  => { data_type => 'varchar',   size => 20 },
        'char varying(20)'
                       => { data_type => 'varchar',   size => 20 },
        'character varying(20)'
                       => { data_type => 'varchar',   size => 20 },
        'nvarchar(20)' => { data_type => 'nvarchar',  size => 20 },
        'xml'          => { data_type => 'xml' },
        'uniqueidentifierstr'
                       => { data_type => 'uniqueidentifierstr' },

        # Binary types
        'binary'       => { data_type => 'binary', size => 1 },
        'binary(20)'   => { data_type => 'binary', size => 20 },
        'varbinary'    => { data_type => 'varbinary', size => 1 },
        'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
        'uniqueidentifier'
                       => { data_type => 'uniqueidentifier' },

        # Blob types
        'long binary'  => { data_type => 'long binary' },
        'image'        => { data_type => 'image' },
        'long varchar' => { data_type => 'long varchar' },
        'text'         => { data_type => 'text' },
        'long nvarchar'=> { data_type => 'long nvarchar' },
        'ntext'        => { data_type => 'ntext' },
    },
    extra => {
        count => 28 * 2,
        run => sub {
            SKIP: {
                $schema  = $_[0];
                my $self = $_[3];

                my $connect_info = [@$self{qw/dsn user password/}];

                my $dbh = $schema->storage->dbh;

                try {
                    $dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'");
                }
                catch {
                    $schemas_created = 0;
                    skip "no CREATE USER privileges", 28 * 2;
                };

                $dbh->do(<<"EOF");
                    CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 (
                        id INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100)
                    )
EOF
                $dbh->do(<<"EOF");
                    CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 (
                        id INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        four_id INTEGER NOT NULL UNIQUE,
                        FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
                    )
EOF
                $dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'");
                $dbh->do(<<"EOF");
                    CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 (
                        id INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        sqlanywhere_loader_test4_id INTEGER,
                        FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
                    )
EOF
                $dbh->do(<<"EOF");
                    CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 (
                        id INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        six_id INTEGER NOT NULL UNIQUE,
                        FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id)
                    )
EOF
                $dbh->do(<<"EOF");
                    CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 (
                        id INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        sqlanywhere_loader_test7_id INTEGER,
                        FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id)
                    )
EOF

                $schemas_created = 1;

                my $guard = Scope::Guard->new(\&extra_cleanup);

                foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') {
                    lives_and {
                        rmtree EXTRA_DUMP_DIR;

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

                        make_schema_at(
                            'SQLAnywhereMultiSchema',
                            {
                                naming => 'current',
                                db_schema => $db_schema,
                                dump_directory => EXTRA_DUMP_DIR,
                                quiet => 1,
                            },
                            $connect_info,
                        );

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

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

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

                    lives_and {
                        ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info);
                    } 'connected test schema';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                    lives_and {
                        ok $test_schema->source('SqlanywhereLoaderTest6')
                            ->has_relationship('sqlanywhere_loader_test4');
                    } 'cross-schema relationship in multi-db_schema';

                    lives_and {
                        ok $test_schema->source('SqlanywhereLoaderTest4')
                            ->has_relationship('sqlanywhere_loader_test6s');
                    } 'cross-schema relationship in multi-db_schema';

                    lives_and {
                        ok $test_schema->source('SqlanywhereLoaderTest8')
                            ->has_relationship('sqlanywhere_loader_test7');
                    } 'cross-schema relationship in multi-db_schema';

                    lives_and {
                        ok $test_schema->source('SqlanywhereLoaderTest7')
                            ->has_relationship('sqlanywhere_loader_test8s');
                    } 'cross-schema relationship in multi-db_schema';
                }
            }
        },
    },
);

if (not ($dbd_sqlanywhere_dsn || $odbc_dsn)) {
    $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');
}
else {
    $tester->run_tests();
}

sub extra_cleanup {
    if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
        if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) {
            foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8',
                               'dbicsl_test2.sqlanywhere_loader_test7',
                               'dbicsl_test2.sqlanywhere_loader_test6',
                               'dbicsl_test1.sqlanywhere_loader_test5',
                               'dbicsl_test1.sqlanywhere_loader_test4') {
                try {
                    $dbh->do("DROP TABLE $table");
                }
                catch {
                    diag "Error dropping table: $_";
                };
            }

            foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) {
                try {
                    $dbh->do("DROP USER $db_schema");
                }
                catch {
                    diag "Error dropping test user $db_schema: $_";
                };
            }
        }
        rmtree EXTRA_DUMP_DIR;
    }
}
# vim:et sts=4 sw=4 tw=0:
Commit	Line	Data
8793567f	1	use strict;
5347f501	2	use warnings;
c4a69b87	3	use Test::More;
	4	use Test::Exception;
	5	use Try::Tiny;
	6	use File::Path 'rmtree';
	7	use DBIx::Class::Schema::Loader 'make_schema_at';
	8	use Scope::Guard ();
	9
8793567f	10	use lib qw(t/lib);
c4a69b87	11
8793567f	12	use dbixcsl_common_tests;
c4a69b87	13	use dbixcsl_test_dir '$tdir';
	14
	15	use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump";
8793567f	16
	17	# The default max_cursor_count and max_statement_count settings of 50 are too
	18	# low to run this test.
8a64178e	19	#
8a64178e	20	# Setting them to zero is preferred.
8793567f	21
637a5785	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} \|\| '';
5347f501	25
637a5785	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} \|\| '';
8793567f	29
c4a69b87	30	my ($schema, $schemas_created); # for cleanup in END for extra tests
c4a69b87	31
8793567f	32	my $tester = dbixcsl_common_tests->new(
	33	vendor => 'SQLAnywhere',
	34	auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
5347f501	35	connect_info => [ ($dbd_sqlanywhere_dsn ? {
	36	dsn => $dbd_sqlanywhere_dsn,
	37	user => $dbd_sqlanywhere_user,
	38	password => $dbd_sqlanywhere_password,
	39	} : ()),
	40	($odbc_dsn ? {
	41	dsn => $odbc_dsn,
	42	user => $odbc_user,
	43	password => $odbc_password,
	44	} : ()),
	45	],
dd87d4c4	46	loader_options => { preserve_case => 1 },
9dc968df	47	data_types => {
	48	# http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
	49	#
	50	# Numeric types
	51	'bit' => { data_type => 'bit' },
	52	'tinyint' => { data_type => 'tinyint' },
	53	'smallint' => { data_type => 'smallint' },
	54	'int' => { data_type => 'integer' },
	55	'integer' => { data_type => 'integer' },
	56	'bigint' => { data_type => 'bigint' },
dd87d4c4	57	'float' => { data_type => 'real' },
dd87d4c4	58	'real' => { data_type => 'real' },
9dc968df	59	'double' => { data_type => 'double precision' },
	60	'double precision' =>
	61	{ data_type => 'double precision' },
	62
dd87d4c4	63	'float(2)' => { data_type => 'real' },
dd87d4c4	64	'float(24)' => { data_type => 'real' },
9dc968df	65	'float(25)' => { data_type => 'double precision' },
	66	'float(53)' => { data_type => 'double precision' },
	67
	68	# This test only works with the default precision and scale options.
	69	#
	70	# They are preserved even for the default values, because the defaults
	71	# can be changed.
	72	'decimal' => { data_type => 'decimal', size => [30,6] },
	73	'dec' => { data_type => 'decimal', size => [30,6] },
	74	'numeric' => { data_type => 'numeric', size => [30,6] },
	75
	76	'decimal(3)' => { data_type => 'decimal', size => [3,0] },
	77	'dec(3)' => { data_type => 'decimal', size => [3,0] },
	78	'numeric(3)' => { data_type => 'numeric', size => [3,0] },
	79
	80	'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
	81	'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
	82	'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
	83
	84	'decimal(18,18)' => { data_type => 'decimal', size => [18,18] },
	85	'dec(18,18)' => { data_type => 'decimal', size => [18,18] },
	86	'numeric(18,18)' => { data_type => 'numeric', size => [18,18] },
	87
	88	# money types
	89	'money' => { data_type => 'money' },
	90	'smallmoney' => { data_type => 'smallmoney' },
	91
	92	# bit arrays
	93	'long varbit' => { data_type => 'long varbit' },
	94	'long bit varying'
	95	=> { data_type => 'long varbit' },
	96	'varbit' => { data_type => 'varbit', size => 1 },
	97	'varbit(20)' => { data_type => 'varbit', size => 20 },
	98	'bit varying' => { data_type => 'varbit', size => 1 },
	99	'bit varying(20)'
	100	=> { data_type => 'varbit', size => 20 },
	101
	102	# Date and Time Types
	103	'date' => { data_type => 'date' },
	104	'datetime' => { data_type => 'datetime' },
	105	'smalldatetime'
	106	=> { data_type => 'smalldatetime' },
	107	'timestamp' => { data_type => 'timestamp' },
6e566cc4	108	# rewrite 'current timestamp' as 'current_timestamp'
6e566cc4	109	'timestamp default current timestamp'
701cd3e3	110	=> { data_type => 'timestamp', default_value => \'current_timestamp',
701cd3e3	111	original => { default_value => \'current timestamp' } },
9dc968df	112	'time' => { data_type => 'time' },
	113
	114	# String Types
	115	'char' => { data_type => 'char', size => 1 },
	116	'char(11)' => { data_type => 'char', size => 11 },
	117	'nchar' => { data_type => 'nchar', size => 1 },
	118	'nchar(11)' => { data_type => 'nchar', size => 11 },
	119	'varchar' => { data_type => 'varchar', size => 1 },
	120	'varchar(20)' => { data_type => 'varchar', size => 20 },
	121	'char varying(20)'
	122	=> { data_type => 'varchar', size => 20 },
	123	'character varying(20)'
	124	=> { data_type => 'varchar', size => 20 },
	125	'nvarchar(20)' => { data_type => 'nvarchar', size => 20 },
	126	'xml' => { data_type => 'xml' },
	127	'uniqueidentifierstr'
825c42a3	128	=> { data_type => 'uniqueidentifierstr' },
9dc968df	129
	130	# Binary types
	131	'binary' => { data_type => 'binary', size => 1 },
	132	'binary(20)' => { data_type => 'binary', size => 20 },
	133	'varbinary' => { data_type => 'varbinary', size => 1 },
	134	'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
	135	'uniqueidentifier'
825c42a3	136	=> { data_type => 'uniqueidentifier' },
9dc968df	137
	138	# Blob types
	139	'long binary' => { data_type => 'long binary' },
	140	'image' => { data_type => 'image' },
	141	'long varchar' => { data_type => 'long varchar' },
	142	'text' => { data_type => 'text' },
	143	'long nvarchar'=> { data_type => 'long nvarchar' },
	144	'ntext' => { data_type => 'ntext' },
	145	},
c4a69b87	146	extra => {
	147	count => 28 * 2,
	148	run => sub {
	149	SKIP: {
	150	$schema = $_[0];
	151	my $self = $_[3];
	152
	153	my $connect_info = [@$self{qw/dsn user password/}];
	154
	155	my $dbh = $schema->storage->dbh;
	156
	157	try {
	158	$dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'");
	159	}
	160	catch {
	161	$schemas_created = 0;
	162	skip "no CREATE USER privileges", 28 * 2;
	163	};
	164
	165	$dbh->do(<<"EOF");
	166	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 (
	167	id INT IDENTITY NOT NULL PRIMARY KEY,
	168	value VARCHAR(100)
	169	)
	170	EOF
	171	$dbh->do(<<"EOF");
	172	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 (
	173	id INT IDENTITY NOT NULL PRIMARY KEY,
	174	value VARCHAR(100),
	175	four_id INTEGER NOT NULL UNIQUE,
	176	FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
	177	)
	178	EOF
	179	$dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'");
	180	$dbh->do(<<"EOF");
	181	CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 (
	182	id INT IDENTITY NOT NULL PRIMARY KEY,
	183	value VARCHAR(100),
	184	sqlanywhere_loader_test4_id INTEGER,
	185	FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
	186	)
	187	EOF
	188	$dbh->do(<<"EOF");
	189	CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 (
	190	id INT IDENTITY NOT NULL PRIMARY KEY,
	191	value VARCHAR(100),
	192	six_id INTEGER NOT NULL UNIQUE,
	193	FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id)
	194	)
	195	EOF
	196	$dbh->do(<<"EOF");
	197	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 (
	198	id INT IDENTITY NOT NULL PRIMARY KEY,
	199	value VARCHAR(100),
	200	sqlanywhere_loader_test7_id INTEGER,
	201	FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id)
	202	)
	203	EOF
	204
	205	$schemas_created = 1;
	206
	207	my $guard = Scope::Guard->new(\&extra_cleanup);
	208
	209	foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') {
210	lives_and {
211	rmtree EXTRA_DUMP_DIR;
212
213	my @warns;
214	local $SIG{__WARN__} = sub {
215	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
216	};
217
218	make_schema_at(
219	'SQLAnywhereMultiSchema',
220	{
221	naming => 'current',
222	db_schema => $db_schema,
223	dump_directory => EXTRA_DUMP_DIR,
224	quiet => 1,
225	},
226	$connect_info,
227	);
228
229	diag join "\n", @warns if @warns;
230
231	is @warns, 0;
232	} 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings';
233
234	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
235
236	lives_and {
237	ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info);
238	} 'connected test schema';
239
240	lives_and {
241	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4');
242	} 'got source for table in schema one';
243
244	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
245	'column in schema one';
246
247	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
248	'column in schema one';
249
250	is try { $rsrc->column_info('value')->{size} }, 100,
251	'column in schema one';
252
253	lives_and {
254	ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4');
255	} 'got resultset for table in schema one';
256
257	lives_and {
258	ok $row = $rs->create({ value => 'foo' });
259	} 'executed SQL on table in schema one';
260
261	$rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test5') };
262
263	is_deeply $rel_info->{cond}, {
264	'foreign.four_id' => 'self.id'
265	}, 'relationship in schema one';
9dc968df	266
c4a69b87	267	is $rel_info->{attrs}{accessor}, 'single',
	268	'relationship in schema one';
	269
	270	is $rel_info->{attrs}{join_type}, 'LEFT',
	271	'relationship in schema one';
	272
	273	lives_and {
	274	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest5');
	275	} 'got source for table in schema one';
	276
	277	%uniqs = try { $rsrc->unique_constraints };
	278
	279	is keys %uniqs, 2,
	280	'got unique and primary constraint in schema one';
	281
	282	lives_and {
	283	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6');
	284	} 'got source for table in schema two';
	285
	286	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	287	'column in schema two introspected correctly';
	288
	289	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
	290	'column in schema two introspected correctly';
	291
	292	is try { $rsrc->column_info('value')->{size} }, 100,
	293	'column in schema two introspected correctly';
	294
	295	lives_and {
	296	ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6');
	297	} 'got resultset for table in schema two';
	298
	299	lives_and {
	300	ok $row = $rs->create({ value => 'foo' });
	301	} 'executed SQL on table in schema two';
	302
	303	$rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') };
	304
	305	is_deeply $rel_info->{cond}, {
	306	'foreign.six_id' => 'self.id'
	307	}, 'relationship in schema two';
	308
	309	is $rel_info->{attrs}{accessor}, 'single',
	310	'relationship in schema two';
	311
	312	is $rel_info->{attrs}{join_type}, 'LEFT',
	313	'relationship in schema two';
	314
	315	lives_and {
	316	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7');
	317	} 'got source for table in schema two';
	318
	319	%uniqs = try { $rsrc->unique_constraints };
	320
	321	is keys %uniqs, 2,
	322	'got unique and primary constraint in schema two';
	323
	324	lives_and {
	325	ok $test_schema->source('SqlanywhereLoaderTest6')
	326	->has_relationship('sqlanywhere_loader_test4');
	327	} 'cross-schema relationship in multi-db_schema';
	328
	329	lives_and {
	330	ok $test_schema->source('SqlanywhereLoaderTest4')
331	->has_relationship('sqlanywhere_loader_test6s');
332	} 'cross-schema relationship in multi-db_schema';
333
334	lives_and {
335	ok $test_schema->source('SqlanywhereLoaderTest8')
336	->has_relationship('sqlanywhere_loader_test7');
337	} 'cross-schema relationship in multi-db_schema';
338
339	lives_and {
340	ok $test_schema->source('SqlanywhereLoaderTest7')
341	->has_relationship('sqlanywhere_loader_test8s');
342	} 'cross-schema relationship in multi-db_schema';
343	}
344	}
345	},
346	},
8793567f	347	);
8793567f	348
5347f501	349	if (not ($dbd_sqlanywhere_dsn \|\| $odbc_dsn)) {
637a5785	350	$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');
8793567f	351	}
	352	else {
	353	$tester->run_tests();
	354	}
c4a69b87	355
	356	sub extra_cleanup {
	357	if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
	358	if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) {
	359	foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8',
	360	'dbicsl_test2.sqlanywhere_loader_test7',
	361	'dbicsl_test2.sqlanywhere_loader_test6',
	362	'dbicsl_test1.sqlanywhere_loader_test5',
	363	'dbicsl_test1.sqlanywhere_loader_test4') {
	364	try {
	365	$dbh->do("DROP TABLE $table");
	366	}
	367	catch {
	368	diag "Error dropping table: $_";
	369	};
	370	}
	371
	372	foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) {
	373	try {
	374	$dbh->do("DROP USER $db_schema");
	375	}
	376	catch {
	377	diag "Error dropping test user $db_schema: $_";
	378	};
	379	}
	380	}
	381	rmtree EXTRA_DUMP_DIR;
	382	}
	383	}
701cd3e3	384	# vim:et sts=4 sw=4 tw=0: