[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::Optional::Dependencies;
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 %env2optdep = map { $_ => lc "test_rdbms_$_" } qw(SQLANYWHERE SQLANYWHERE_ODBC);

plan skip_all => 'requirements not satisfied:  ' . (join '  OR  ', map
    { "[ @{[ DBIx::Class::Schema::Loader::Optional::Dependencies->req_missing_for( $_ ) ]} ]" }
    values %env2optdep
) unless scalar grep
    { DBIx::Class::Schema::Loader::Optional::Dependencies->req_ok_for( $_ ) }
    values %env2optdep
;

my %dsns;
for my $type (keys %env2optdep) {
    my %conninfo;
    @conninfo{qw(dsn user password)} = map { $ENV{"DBICTEST_${type}_$_"} } qw(DSN USER PASS);
    next unless $conninfo{dsn};

    my $dep_group = $env2optdep{$type};
    if (!DBIx::Class::Schema::Loader::Optional::Dependencies->req_ok_for($dep_group)) {
        diag "Testing with DBICTEST_${type}_DSN needs " . DBIx::Class::Schema::Loader::Optional::Dependencies->req_missing_for($dep_group);
        next;
    }

    $dsns{$type} = \%conninfo;
}

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 => [ map { $dsns{$_} } sort keys %dsns ],
    loader_options => { preserve_case => 1 },
    default_is_deferrable => 1,
    default_on_clause => 'RESTRICT',
    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 => {
        create => [
            # 4 through 8 are used for the multi-schema tests
            q{
                create table sqlanywhere_loader_test9 (
                    id int identity not null primary key
                )
            },
            q{
                create table sqlanywhere_loader_test10 (
                    id int identity not null primary key,
                    nine_id int,
                    foreign key (nine_id) references sqlanywhere_loader_test9(id)
                        on delete cascade on update set null
                )
            },
        ],
        drop  => [ qw/sqlanywhere_loader_test9 sqlanywhere_loader_test10/ ],
        count => 4 + 30 * 2,
        run => sub {
            SKIP: {
                $schema  = $_[0];
                my $self = $_[3];

                # test on delete/update fk clause introspection
                ok ((my $rel_info = $schema->source('SqlanywhereLoaderTest10')->relationship_info('nine')),
                    'got rel info');

                is $rel_info->{attrs}{on_delete}, 'CASCADE',
                    'ON DELETE clause introspected correctly';

                is $rel_info->{attrs}{on_update}, 'SET NULL',
                    'ON UPDATE clause introspected correctly';

                is $rel_info->{attrs}{is_deferrable}, 1,
                    'is_deferrable defaults to 1';

                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", 30 * 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,
                        CONSTRAINT loader_test5_uniq UNIQUE (four_id),
                        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_test5 (
                        pk INT IDENTITY NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        four_id INTEGER NOT NULL,
                        CONSTRAINT loader_test5_uniq UNIQUE (four_id),
                        FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
                    )
EOF
                $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('dbicsl_test1_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('DbicslTest1SqlanywhereLoaderTest5');
                    } 'got source for table in schema one';

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

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

                    delete $uniqs{primary};

                    is_deeply ((values %uniqs)[0], ['four_id'],
                        'correct unique 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';

                    delete $uniqs{primary};

                    is_deeply ((values %uniqs)[0], ['six_id'],
                        'correct unique 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';
                }
            }
        },
    },
);

$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_test2.sqlanywhere_loader_test5',
                               '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';
4fb2971c	7	use DBIx::Class::Optional::Dependencies;
c4a69b87	8	use DBIx::Class::Schema::Loader 'make_schema_at';
	9	use Scope::Guard ();
	10
8793567f	11	use lib qw(t/lib);
c4a69b87	12
8793567f	13	use dbixcsl_common_tests;
c4a69b87	14	use dbixcsl_test_dir '$tdir';
	15
	16	use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump";
8793567f	17
	18	# The default max_cursor_count and max_statement_count settings of 50 are too
	19	# low to run this test.
8a64178e	20	#
8a64178e	21	# Setting them to zero is preferred.
8793567f	22
ffd4ce36	23	my %env2optdep = map { $_ => lc "test_rdbms_$_" } qw(SQLANYWHERE SQLANYWHERE_ODBC);
	24
	25	plan skip_all => 'requirements not satisfied: ' . (join ' OR ', map
	26	{ "[ @{[ DBIx::Class::Schema::Loader::Optional::Dependencies->req_missing_for( $_ ) ]} ]" }
	27	values %env2optdep
	28	) unless scalar grep
	29	{ DBIx::Class::Schema::Loader::Optional::Dependencies->req_ok_for( $_ ) }
	30	values %env2optdep
	31	;
5347f501	32
ffd4ce36	33	my %dsns;
	34	for my $type (keys %env2optdep) {
	35	my %conninfo;
	36	@conninfo{qw(dsn user password)} = map { $ENV{"DBICTEST_${type}_$_"} } qw(DSN USER PASS);
	37	next unless $conninfo{dsn};
	38
	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);
4fb2971c	42	next;
	43	}
	44
ffd4ce36	45	$dsns{$type} = \%conninfo;
ffd4ce36	46	}
8793567f	47
c4a69b87	48	my ($schema, $schemas_created); # for cleanup in END for extra tests
c4a69b87	49
8793567f	50	my $tester = dbixcsl_common_tests->new(
	51	vendor => 'SQLAnywhere',
	52	auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY',
7336f5dc	53	connect_info => [ map { $dsns{$_} } sort keys %dsns ],
dd87d4c4	54	loader_options => { preserve_case => 1 },
4b5eca90	55	default_is_deferrable => 1,
4b5eca90	56	default_on_clause => 'RESTRICT',
9dc968df	57	data_types => {
	58	# http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
	59	#
	60	# Numeric types
	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' },
dd87d4c4	67	'float' => { data_type => 'real' },
dd87d4c4	68	'real' => { data_type => 'real' },
9dc968df	69	'double' => { data_type => 'double precision' },
	70	'double precision' =>
	71	{ data_type => 'double precision' },
	72
dd87d4c4	73	'float(2)' => { data_type => 'real' },
dd87d4c4	74	'float(24)' => { data_type => 'real' },
9dc968df	75	'float(25)' => { data_type => 'double precision' },
	76	'float(53)' => { data_type => 'double precision' },
	77
	78	# This test only works with the default precision and scale options.
	79	#
	80	# They are preserved even for the default values, because the defaults
	81	# can be changed.
	82	'decimal' => { data_type => 'decimal', size => [30,6] },
	83	'dec' => { data_type => 'decimal', size => [30,6] },
	84	'numeric' => { data_type => 'numeric', size => [30,6] },
	85
	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] },
	89
	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] },
	93
	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] },
	97
	98	# money types
	99	'money' => { data_type => 'money' },
	100	'smallmoney' => { data_type => 'smallmoney' },
	101
	102	# bit arrays
	103	'long varbit' => { data_type => 'long varbit' },
	104	'long bit varying'
	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 },
	109	'bit varying(20)'
	110	=> { data_type => 'varbit', size => 20 },
	111
	112	# Date and Time Types
	113	'date' => { data_type => 'date' },
	114	'datetime' => { data_type => 'datetime' },
	115	'smalldatetime'
	116	=> { data_type => 'smalldatetime' },
	117	'timestamp' => { data_type => 'timestamp' },
6e566cc4	118	# rewrite 'current timestamp' as 'current_timestamp'
6e566cc4	119	'timestamp default current timestamp'
701cd3e3	120	=> { data_type => 'timestamp', default_value => \'current_timestamp',
701cd3e3	121	original => { default_value => \'current timestamp' } },
9dc968df	122	'time' => { data_type => 'time' },
	123
	124	# String Types
	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 },
	131	'char varying(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'
825c42a3	138	=> { data_type => 'uniqueidentifierstr' },
9dc968df	139
	140	# Binary types
	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 },
	145	'uniqueidentifier'
825c42a3	146	=> { data_type => 'uniqueidentifier' },
9dc968df	147
	148	# Blob types
	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' },
	155	},
c4a69b87	156	extra => {
4b5eca90	157	create => [
	158	# 4 through 8 are used for the multi-schema tests
	159	q{
	160	create table sqlanywhere_loader_test9 (
	161	id int identity not null primary key
	162	)
	163	},
	164	q{
	165	create table sqlanywhere_loader_test10 (
	166	id int identity not null primary key,
	167	nine_id int,
	168	foreign key (nine_id) references sqlanywhere_loader_test9(id)
	169	on delete cascade on update set null
	170	)
	171	},
	172	],
	173	drop => [ qw/sqlanywhere_loader_test9 sqlanywhere_loader_test10/ ],
	174	count => 4 + 30 * 2,
c4a69b87	175	run => sub {
	176	SKIP: {
	177	$schema = $_[0];
	178	my $self = $_[3];
	179
4b5eca90	180	# test on delete/update fk clause introspection
	181	ok ((my $rel_info = $schema->source('SqlanywhereLoaderTest10')->relationship_info('nine')),
	182	'got rel info');
	183
	184	is $rel_info->{attrs}{on_delete}, 'CASCADE',
	185	'ON DELETE clause introspected correctly';
	186
	187	is $rel_info->{attrs}{on_update}, 'SET NULL',
	188	'ON UPDATE clause introspected correctly';
	189
	190	is $rel_info->{attrs}{is_deferrable}, 1,
	191	'is_deferrable defaults to 1';
	192
c4a69b87	193	my $connect_info = [@$self{qw/dsn user password/}];
	194
	195	my $dbh = $schema->storage->dbh;
	196
	197	try {
	198	$dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'");
	199	}
	200	catch {
	201	$schemas_created = 0;
5975bbe6	202	skip "no CREATE USER privileges", 30 * 2;
c4a69b87	203	};
	204
	205	$dbh->do(<<"EOF");
	206	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 (
	207	id INT IDENTITY NOT NULL PRIMARY KEY,
	208	value VARCHAR(100)
	209	)
	210	EOF
	211	$dbh->do(<<"EOF");
	212	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 (
	213	id INT IDENTITY NOT NULL PRIMARY KEY,
	214	value VARCHAR(100),
5975bbe6	215	four_id INTEGER NOT NULL,
5975bbe6	216	CONSTRAINT loader_test5_uniq UNIQUE (four_id),
c4a69b87	217	FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
	218	)
	219	EOF
	220	$dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'");
	221	$dbh->do(<<"EOF");
5975bbe6	222	CREATE TABLE dbicsl_test2.sqlanywhere_loader_test5 (
	223	pk INT IDENTITY NOT NULL PRIMARY KEY,
	224	value VARCHAR(100),
	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)
	228	)
	229	EOF
	230	$dbh->do(<<"EOF");
c4a69b87	231	CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 (
	232	id INT IDENTITY NOT NULL PRIMARY KEY,
	233	value VARCHAR(100),
	234	sqlanywhere_loader_test4_id INTEGER,
	235	FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id)
	236	)
	237	EOF
	238	$dbh->do(<<"EOF");
	239	CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 (
	240	id INT IDENTITY NOT NULL PRIMARY KEY,
	241	value VARCHAR(100),
	242	six_id INTEGER NOT NULL UNIQUE,
	243	FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id)
	244	)
	245	EOF
	246	$dbh->do(<<"EOF");
	247	CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 (
	248	id INT IDENTITY NOT NULL PRIMARY KEY,
	249	value VARCHAR(100),
	250	sqlanywhere_loader_test7_id INTEGER,
	251	FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id)
	252	)
	253	EOF
	254
	255	$schemas_created = 1;
	256
	257	my $guard = Scope::Guard->new(\&extra_cleanup);
	258
	259	foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') {
	260	lives_and {
	261	rmtree EXTRA_DUMP_DIR;
	262
	263	my @warns;
	264	local $SIG{__WARN__} = sub {
	265	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
	266	};
	267
	268	make_schema_at(
	269	'SQLAnywhereMultiSchema',
	270	{
	271	naming => 'current',
	272	db_schema => $db_schema,
	273	dump_directory => EXTRA_DUMP_DIR,
	274	quiet => 1,
	275	},
	276	$connect_info,
	277	);
	278
	279	diag join "\n", @warns if @warns;
	280
	281	is @warns, 0;
	282	} 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings';
	283
	284	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
	285
	286	lives_and {
	287	ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info);
	288	} 'connected test schema';
	289
	290	lives_and {
4c2e2ce9	291	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4');
c4a69b87	292	} 'got source for table in schema one';
	293
	294	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	295	'column in schema one';
	296
	297	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
	298	'column in schema one';
	299
	300	is try { $rsrc->column_info('value')->{size} }, 100,
	301	'column in schema one';
	302
	303	lives_and {
4c2e2ce9	304	ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4');
c4a69b87	305	} 'got resultset for table in schema one';
	306
	307	lives_and {
	308	ok $row = $rs->create({ value => 'foo' });
	309	} 'executed SQL on table in schema one';
	310
5975bbe6	311	$rel_info = try { $rsrc->relationship_info('dbicsl_test1_sqlanywhere_loader_test5') };
c4a69b87	312
	313	is_deeply $rel_info->{cond}, {
	314	'foreign.four_id' => 'self.id'
	315	}, 'relationship in schema one';
9dc968df	316
c4a69b87	317	is $rel_info->{attrs}{accessor}, 'single',
	318	'relationship in schema one';
	319
	320	is $rel_info->{attrs}{join_type}, 'LEFT',
	321	'relationship in schema one';
	322
	323	lives_and {
5975bbe6	324	ok $rsrc = $test_schema->source('DbicslTest1SqlanywhereLoaderTest5');
c4a69b87	325	} 'got source for table in schema one';
	326
	327	%uniqs = try { $rsrc->unique_constraints };
	328
	329	is keys %uniqs, 2,
	330	'got unique and primary constraint in schema one';
	331
5975bbe6	332	delete $uniqs{primary};
	333
	334	is_deeply ((values %uniqs)[0], ['four_id'],
	335	'correct unique constraint in schema one');
	336
c4a69b87	337	lives_and {
4c2e2ce9	338	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6');
c4a69b87	339	} 'got source for table in schema two';
	340
	341	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	342	'column in schema two introspected correctly';
	343
	344	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
	345	'column in schema two introspected correctly';
	346
	347	is try { $rsrc->column_info('value')->{size} }, 100,
	348	'column in schema two introspected correctly';
	349
	350	lives_and {
4c2e2ce9	351	ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6');
c4a69b87	352	} 'got resultset for table in schema two';
	353
	354	lives_and {
	355	ok $row = $rs->create({ value => 'foo' });
	356	} 'executed SQL on table in schema two';
	357
	358	$rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') };
	359
	360	is_deeply $rel_info->{cond}, {
	361	'foreign.six_id' => 'self.id'
	362	}, 'relationship in schema two';
	363
	364	is $rel_info->{attrs}{accessor}, 'single',
	365	'relationship in schema two';
	366
	367	is $rel_info->{attrs}{join_type}, 'LEFT',
	368	'relationship in schema two';
	369
	370	lives_and {
4c2e2ce9	371	ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7');
c4a69b87	372	} 'got source for table in schema two';
	373
	374	%uniqs = try { $rsrc->unique_constraints };
	375
	376	is keys %uniqs, 2,
	377	'got unique and primary constraint in schema two';
	378
5975bbe6	379	delete $uniqs{primary};
	380
	381	is_deeply ((values %uniqs)[0], ['six_id'],
	382	'correct unique constraint in schema two');
	383
c4a69b87	384	lives_and {
4c2e2ce9	385	ok $test_schema->source('SqlanywhereLoaderTest6')
c4a69b87	386	->has_relationship('sqlanywhere_loader_test4');
	387	} 'cross-schema relationship in multi-db_schema';
	388
	389	lives_and {
4c2e2ce9	390	ok $test_schema->source('SqlanywhereLoaderTest4')
c4a69b87	391	->has_relationship('sqlanywhere_loader_test6s');
	392	} 'cross-schema relationship in multi-db_schema';
	393
	394	lives_and {
4c2e2ce9	395	ok $test_schema->source('SqlanywhereLoaderTest8')
c4a69b87	396	->has_relationship('sqlanywhere_loader_test7');
	397	} 'cross-schema relationship in multi-db_schema';
	398
	399	lives_and {
4c2e2ce9	400	ok $test_schema->source('SqlanywhereLoaderTest7')
c4a69b87	401	->has_relationship('sqlanywhere_loader_test8s');
	402	} 'cross-schema relationship in multi-db_schema';
	403	}
	404	}
	405	},
	406	},
8793567f	407	);
8793567f	408
4fb2971c	409	$tester->run_tests();
c4a69b87	410
	411	sub extra_cleanup {
	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',
5975bbe6	417	'dbicsl_test2.sqlanywhere_loader_test5',
c4a69b87	418	'dbicsl_test1.sqlanywhere_loader_test5',
	419	'dbicsl_test1.sqlanywhere_loader_test4') {
	420	try {
	421	$dbh->do("DROP TABLE $table");
	422	}
	423	catch {
	424	diag "Error dropping table: $_";
	425	};
	426	}
	427
	428	foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) {
	429	try {
	430	$dbh->do("DROP USER $db_schema");
	431	}
	432	catch {
	433	diag "Error dropping test user $db_schema: $_";
	434	};
	435	}
	436	}
	437	rmtree EXTRA_DUMP_DIR;
	438	}
	439	}
701cd3e3	440	# vim:et sts=4 sw=4 tw=0: