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

use strict;
use warnings;
use Test::More;
use Test::Exception;
use DBIx::Class::Schema::Loader 'make_schema_at';
use DBIx::Class::Schema::Loader::Utils qw/slurp_file split_name/;
use Try::Tiny;
use File::Path 'rmtree';
use String::ToIdentifier::EN::Unicode 'to_identifier';
use namespace::clean;

use lib qw(t/lib);
use dbixcsl_common_tests ();
use dbixcsl_test_dir '$tdir';

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

my $dsn      = $ENV{DBICTEST_ORA_DSN} || '';
my $user     = $ENV{DBICTEST_ORA_USER} || '';
my $password = $ENV{DBICTEST_ORA_PASS} || '';

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

my $auto_inc_cb = sub {
    my ($table, $col) = @_;
    return (
        qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1},
        qq{ 
            CREATE OR REPLACE TRIGGER ${table}_${col}_trigger
            BEFORE INSERT ON ${table}
            FOR EACH ROW
            BEGIN
                SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual;
            END;
        }
    );
};

my $auto_inc_drop_cb = sub {
    my ($table, $col) = @_;
    return qq{ DROP SEQUENCE ${table}_${col}_seq };
};

my $tester = dbixcsl_common_tests->new(
    vendor      => 'Oracle',
    auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
    auto_inc_cb => $auto_inc_cb,
    auto_inc_drop_cb => $auto_inc_drop_cb, 
    preserve_case_mode_is_exclusive => 1,
    quote_char                      => '"',
    dsn         => $dsn,
    user        => $user,
    password    => $password,
    data_types  => {
        # From:
        # http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
        #
        # These tests require at least Oracle 9.2, because of the VARCHAR to
        # VARCHAR2 casting.
        #
        # Character Types
        'char'         => { data_type => 'char',      size => 1  },
        'char(11)'     => { data_type => 'char',      size => 11 },
        'nchar'        => { data_type => 'nchar',     size => 1  },
        'national character'
                       => { data_type => 'nchar',     size => 1  },
        'nchar(11)'    => { data_type => 'nchar',     size => 11 },
        'national character(11)'
                       => { data_type => 'nchar',     size => 11 },
        'varchar(20)'  => { data_type => 'varchar2',  size => 20 },
        'varchar2(20)' => { data_type => 'varchar2',  size => 20 },
        'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
        'national character varying(20)'
                       => { data_type => 'nvarchar2', size => 20 },

        # Numeric Types
        #
        # integer/decimal/numeric is alised to NUMBER
        #
        'integer'      => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
        'int'          => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
        'smallint'     => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },

        'decimal'      => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
        'dec'          => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
        'numeric'      => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },

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

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

        'binary_float'  => { data_type => 'real',             original => { data_type => 'binary_float'  } },
        'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } },

        # these are not mentioned in the summary chart, must be aliased
        real            => { data_type => 'real',             original => { data_type => 'float', size => 63  } },
        'float(63)'     => { data_type => 'real',             original => { data_type => 'float', size => 63  } },
        'float(64)'     => { data_type => 'double precision', original => { data_type => 'float', size => 64  } },
        'float(126)'    => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
        float           => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },

        # Blob Types
        'raw(50)'      => { data_type => 'raw', size => 50 },
        'clob'         => { data_type => 'clob' },
        'nclob'        => { data_type => 'nclob' },
        'blob'         => { data_type => 'blob' },
        'bfile'        => { data_type => 'bfile' },
        'long'         => { data_type => 'long' },
        'long raw'     => { data_type => 'long raw' },

        # Datetime Types
        'date'         => { data_type => 'datetime', original => { data_type => 'date' } },
        'date default sysdate'
                       => { data_type => 'datetime', default_value => \'current_timestamp',
                            original  => { data_type => 'date', default_value => \'sysdate' } },
        'timestamp'    => { data_type => 'timestamp' },
        'timestamp default current_timestamp'
                       => { data_type => 'timestamp', default_value => \'current_timestamp' },
        'timestamp(3)' => { data_type => 'timestamp', size => 3 },
        'timestamp with time zone'
                       => { data_type => 'timestamp with time zone' },
        'timestamp(3) with time zone'
                       => { data_type => 'timestamp with time zone', size => 3 },
        'timestamp with local time zone'
                       => { data_type => 'timestamp with local time zone' },
        'timestamp(3) with local time zone'
                       => { data_type => 'timestamp with local time zone', size => 3 },
        'interval year to month'
                       => { data_type => 'interval year to month' },
        'interval year(3) to month'
                       => { data_type => 'interval year to month', size => 3 },
        'interval day to second'
                       => { data_type => 'interval day to second' },
        'interval day(3) to second'
                       => { data_type => 'interval day to second', size => [3,6] },
        'interval day to second(3)'
                       => { data_type => 'interval day to second', size => [2,3] },
        'interval day(3) to second(3)'
                       => { data_type => 'interval day to second', size => [3,3] },

        # Other Types
        'rowid'        => { data_type => 'rowid' },
        'urowid'       => { data_type => 'urowid' },
        'urowid(3333)' => { data_type => 'urowid', size => 3333 },
    },
    extra => {
        create => [
            q{ 
                CREATE TABLE oracle_loader_test1 (
                    id NUMBER(11),
                    value VARCHAR2(100)
                )
            },
            q{ COMMENT ON TABLE oracle_loader_test1 IS 'oracle_loader_test1 table comment' },
            q{ COMMENT ON COLUMN oracle_loader_test1.value IS 'oracle_loader_test1.value column comment' },
        ],
        drop  => [qw/oracle_loader_test1/],
        count => 3 + 30 * 2,
        run   => sub {
            my ($monikers, $classes);
            ($schema, $monikers, $classes) = @_;

            SKIP: {
                if (my $source = $monikers->{loader_test1s}) {
                    is $schema->source($source)->column_info('id')->{sequence},
                        'loader_test1s_id_seq',
                        'Oracle sequence detection';
                }
                else {
                    skip 'not running common tests', 1;
                }
            }

            my $class = $classes->{oracle_loader_test1};

            my $filename = $schema->loader->get_dump_filename($class);
            my $code = slurp_file $filename;

            like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m,
                'table comment';

            like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m,
                'column comment and attrs';

            SKIP: {
                skip 'Set the DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS environment variables to run the cross-schema relationship tests', 6 * 2
                    unless $ENV{DBICTEST_ORA_EXTRAUSER_DSN};

                $extra_schema = $schema->clone;
                $extra_schema->connection(@ENV{map "DBICTEST_ORA_EXTRAUSER_$_",
                    qw/DSN USER PASS/
                });

                my $dbh1 = $schema->storage->dbh;
                my $dbh2 = $extra_schema->storage->dbh;

                my ($schema1) = $dbh1->selectrow_array('SELECT USER FROM DUAL');
                my ($schema2) = $dbh2->selectrow_array('SELECT USER FROM DUAL');

                $dbh1->do(<<'EOF');
                    CREATE TABLE oracle_loader_test4 (
                        id INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100)
                    )
EOF

                $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test4', 'id');

                $dbh1->do("GRANT ALL ON oracle_loader_test4 TO $schema2");
                $dbh1->do("GRANT ALL ON oracle_loader_test4_id_seq TO $schema2");

                $dbh1->do(<<"EOF");
                    CREATE TABLE oracle_loader_test5 (
                        id INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
                        CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
                    )
EOF
                $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test5', 'id');
                $dbh1->do("GRANT ALL ON oracle_loader_test5 TO $schema2");
                $dbh1->do("GRANT ALL ON oracle_loader_test5_id_seq TO $schema2");

                $dbh2->do(<<"EOF");
                    CREATE TABLE oracle_loader_test5 (
                        pk INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
                        CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
                    )
EOF
                $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test5', 'pk');
                $dbh2->do("GRANT ALL ON oracle_loader_test5 TO $schema1");
                $dbh2->do("GRANT ALL ON oracle_loader_test5_pk_seq TO $schema1");

                $dbh2->do(<<"EOF");
                    CREATE TABLE oracle_loader_test6 (
                        id INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        oracle_loader_test4_id INT REFERENCES ${schema1}.oracle_loader_test4 (id)
                    )
EOF
                $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test6', 'id');
                $dbh2->do("GRANT ALL ON oracle_loader_test6 to $schema1");
                $dbh2->do("GRANT ALL ON oracle_loader_test6_id_seq TO $schema1");

                $dbh2->do(<<"EOF");
                    CREATE TABLE oracle_loader_test7 (
                        id INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        six_id INT UNIQUE REFERENCES ${schema2}.oracle_loader_test6 (id)
                    )
EOF
                $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test7', 'id');
                $dbh2->do("GRANT ALL ON oracle_loader_test7 to $schema1");
                $dbh2->do("GRANT ALL ON oracle_loader_test7_id_seq TO $schema1");

                $dbh1->do(<<"EOF");
                    CREATE TABLE oracle_loader_test8 (
                        id INT NOT NULL PRIMARY KEY,
                        value VARCHAR(100),
                        oracle_loader_test7_id INT REFERENCES ${schema2}.oracle_loader_test7 (id)
                    )
EOF
                $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test8', 'id');
                $dbh1->do("GRANT ALL ON oracle_loader_test8 to $schema2");
                $dbh1->do("GRANT ALL ON oracle_loader_test8_id_seq TO $schema2");

                # We add schema to moniker_parts, so make a monikers hash for
                # the tests, of the form schemanum.tablenum
                my $schema1_moniker = join '', map ucfirst lc, split_name to_identifier $schema1;
                my $schema2_moniker = join '', map ucfirst lc, split_name to_identifier $schema2;

                my %monikers;
                $monikers{'1.4'} = $schema1_moniker . 'OracleLoaderTest4';
                $monikers{'1.5'} = $schema1_moniker . 'OracleLoaderTest5';
                $monikers{'2.5'} = $schema2_moniker . 'OracleLoaderTest5';
                $monikers{'2.6'} = $schema2_moniker . 'OracleLoaderTest6';
                $monikers{'2.7'} = $schema2_moniker . 'OracleLoaderTest7';
                $monikers{'1.8'} = $schema1_moniker . 'OracleLoaderTest8';

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

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

                        make_schema_at(
                            'OracleMultiSchema',
                            {
                                naming => 'current',
                                db_schema => $db_schema,
                                moniker_parts => [qw/schema name/],
                                dump_directory => EXTRA_DUMP_DIR,
                                quiet => 1,
                            },
                            [ $dsn, $user, $password ],
                        );

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

                        is @warns, 0;
                    } qq{dumped schema for "$schema1" and "$schema2" schemas with no warnings};

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

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

                    lives_and {
                        ok $rsrc = $test_schema->source($monikers{'1.4'});
                    } 'got source for table in schema1';

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

                    is try { $rsrc->column_info('value')->{data_type} }, 'varchar2',
                        'column in schema1';

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

                    lives_and {
                        ok $rs = $test_schema->resultset($monikers{'1.4'});
                    } 'got resultset for table in schema1';

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

                    my $schema1_identifier = join '_', map lc, split_name to_identifier $schema1;

                    $rel_info = try { $rsrc->relationship_info(
                        $schema1_identifier . '_oracle_loader_test5'
                    ) };

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

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

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

                    lives_and {
                        ok $rsrc = $test_schema->source($monikers{'1.5'});
                    } 'got source for table in schema1';

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

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

                    delete $uniqs{primary};

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

                    lives_and {
                        ok $rsrc = $test_schema->source($monikers{'2.6'});
                    } 'got source for table in schema2';

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

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

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

                    lives_and {
                        ok $rs = $test_schema->resultset($monikers{'2.6'});
                    } 'got resultset for table in schema2';

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

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

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

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

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

                    lives_and {
                        ok $rsrc = $test_schema->source($monikers{'2.7'});
                    } 'got source for table in schema2';

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

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

                    delete $uniqs{primary};

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

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

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

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

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

if( !$dsn || !$user ) {
    $tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
}
else {
    $tester->run_tests();
}

END {
    if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
        if (my $dbh2 = try { $extra_schema->storage->dbh }) {
            my $dbh1 = $schema->storage->dbh;

            try {
                $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test8', 'id');
                $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test7', 'id');
                $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test6', 'id');
                $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'pk');
                $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'id');
                $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test4', 'id');
            }
            catch {
                die "Error dropping sequences for cross-schema test tables: $_";
            };

            try {
                $dbh1->do('DROP TABLE oracle_loader_test8');
                $dbh2->do('DROP TABLE oracle_loader_test7');
                $dbh2->do('DROP TABLE oracle_loader_test6');
                $dbh2->do('DROP TABLE oracle_loader_test5');
                $dbh1->do('DROP TABLE oracle_loader_test5');
                $dbh1->do('DROP TABLE oracle_loader_test4');
            }
            catch {
                die "Error dropping cross-schema test tables: $_";
            };
        }

        rmtree EXTRA_DUMP_DIR;
    }
}
# vim:et sw=4 sts=4 tw=0:
Commit	Line	Data
e7262300	1	use strict;
fcf328c7	2	use warnings;
6b0e47fc	3	use Test::More;
6b0e47fc	4	use Test::Exception;
c4a69b87	5	use DBIx::Class::Schema::Loader 'make_schema_at';
5975bbe6	6	use DBIx::Class::Schema::Loader::Utils qw/slurp_file split_name/;
c4a69b87	7	use Try::Tiny;
c4a69b87	8	use File::Path 'rmtree';
5975bbe6	9	use String::ToIdentifier::EN::Unicode 'to_identifier';
fcf328c7	10	use namespace::clean;
c4a69b87	11
fcf328c7	12	use lib qw(t/lib);
c4a69b87	13	use dbixcsl_common_tests ();
	14	use dbixcsl_test_dir '$tdir';
	15
	16	use constant EXTRA_DUMP_DIR => "$tdir/ora_extra_dump";
e7262300	17
	18	my $dsn = $ENV{DBICTEST_ORA_DSN} \|\| '';
	19	my $user = $ENV{DBICTEST_ORA_USER} \|\| '';
	20	my $password = $ENV{DBICTEST_ORA_PASS} \|\| '';
	21
c4a69b87	22	my ($schema, $extra_schema); # for cleanup in END for extra tests
c4a69b87	23
5975bbe6	24	my $auto_inc_cb = sub {
	25	my ($table, $col) = @_;
	26	return (
	27	qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1},
	28	qq{
	29	CREATE OR REPLACE TRIGGER ${table}_${col}_trigger
	30	BEFORE INSERT ON ${table}
	31	FOR EACH ROW
	32	BEGIN
	33	SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual;
	34	END;
	35	}
	36	);
	37	};
	38
	39	my $auto_inc_drop_cb = sub {
	40	my ($table, $col) = @_;
	41	return qq{ DROP SEQUENCE ${table}_${col}_seq };
	42	};
	43
e7262300	44	my $tester = dbixcsl_common_tests->new(
e7262300	45	vendor => 'Oracle',
c1ac681d	46	auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
5975bbe6	47	auto_inc_cb => $auto_inc_cb,
5975bbe6	48	auto_inc_drop_cb => $auto_inc_drop_cb,
b511f36e	49	preserve_case_mode_is_exclusive => 1,
b511f36e	50	quote_char => '"',
e7262300	51	dsn => $dsn,
	52	user => $user,
	53	password => $password,
760fd65c	54	data_types => {
	55	# From:
	56	# http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
	57	#
	58	# These tests require at least Oracle 9.2, because of the VARCHAR to
	59	# VARCHAR2 casting.
	60	#
	61	# Character Types
	62	'char' => { data_type => 'char', size => 1 },
	63	'char(11)' => { data_type => 'char', size => 11 },
	64	'nchar' => { data_type => 'nchar', size => 1 },
bc319c63	65	'national character'
bc319c63	66	=> { data_type => 'nchar', size => 1 },
760fd65c	67	'nchar(11)' => { data_type => 'nchar', size => 11 },
bc319c63	68	'national character(11)'
bc319c63	69	=> { data_type => 'nchar', size => 11 },
760fd65c	70	'varchar(20)' => { data_type => 'varchar2', size => 20 },
	71	'varchar2(20)' => { data_type => 'varchar2', size => 20 },
	72	'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
bc319c63	73	'national character varying(20)'
bc319c63	74	=> { data_type => 'nvarchar2', size => 20 },
760fd65c	75
	76	# Numeric Types
	77	#
1c22571b	78	# integer/decimal/numeric is alised to NUMBER
760fd65c	79	#
4ea15dfe	80	'integer' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	81	'int' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	82	'smallint' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
760fd65c	83
4ea15dfe	84	'decimal' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	85	'dec' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	86	'numeric' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
760fd65c	87
4ea15dfe	88	'decimal(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
	89	'dec(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
	90	'numeric(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
760fd65c	91
4ea15dfe	92	'decimal(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
	93	'dec(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
	94	'numeric(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
760fd65c	95
1c22571b	96	'binary_float' => { data_type => 'real', original => { data_type => 'binary_float' } },
	97	'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } },
	98
	99	# these are not mentioned in the summary chart, must be aliased
ceb009d3	100	real => { data_type => 'real', original => { data_type => 'float', size => 63 } },
1c22571b	101	'float(63)' => { data_type => 'real', original => { data_type => 'float', size => 63 } },
	102	'float(64)' => { data_type => 'double precision', original => { data_type => 'float', size => 64 } },
	103	'float(126)' => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
	104	float => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
760fd65c	105
	106	# Blob Types
	107	'raw(50)' => { data_type => 'raw', size => 50 },
	108	'clob' => { data_type => 'clob' },
	109	'nclob' => { data_type => 'nclob' },
	110	'blob' => { data_type => 'blob' },
	111	'bfile' => { data_type => 'bfile' },
3b29d0ac	112	'long' => { data_type => 'long' },
760fd65c	113	'long raw' => { data_type => 'long raw' },
	114
	115	# Datetime Types
3785bc2e	116	'date' => { data_type => 'datetime', original => { data_type => 'date' } },
760fd65c	117	'date default sysdate'
3785bc2e	118	=> { data_type => 'datetime', default_value => \'current_timestamp',
3785bc2e	119	original => { data_type => 'date', default_value => \'sysdate' } },
760fd65c	120	'timestamp' => { data_type => 'timestamp' },
	121	'timestamp default current_timestamp'
	122	=> { data_type => 'timestamp', default_value => \'current_timestamp' },
	123	'timestamp(3)' => { data_type => 'timestamp', size => 3 },
	124	'timestamp with time zone'
	125	=> { data_type => 'timestamp with time zone' },
	126	'timestamp(3) with time zone'
	127	=> { data_type => 'timestamp with time zone', size => 3 },
	128	'timestamp with local time zone'
	129	=> { data_type => 'timestamp with local time zone' },
	130	'timestamp(3) with local time zone'
	131	=> { data_type => 'timestamp with local time zone', size => 3 },
	132	'interval year to month'
	133	=> { data_type => 'interval year to month' },
	134	'interval year(3) to month'
	135	=> { data_type => 'interval year to month', size => 3 },
	136	'interval day to second'
	137	=> { data_type => 'interval day to second' },
	138	'interval day(3) to second'
	139	=> { data_type => 'interval day to second', size => [3,6] },
	140	'interval day to second(3)'
	141	=> { data_type => 'interval day to second', size => [2,3] },
	142	'interval day(3) to second(3)'
	143	=> { data_type => 'interval day to second', size => [3,3] },
	144
	145	# Other Types
	146	'rowid' => { data_type => 'rowid' },
	147	'urowid' => { data_type => 'urowid' },
	148	'urowid(3333)' => { data_type => 'urowid', size => 3333 },
	149	},
5cd600fa	150	extra => {
4cd5155b	151	create => [
	152	q{
	153	CREATE TABLE oracle_loader_test1 (
	154	id NUMBER(11),
	155	value VARCHAR2(100)
	156	)
	157	},
	158	q{ COMMENT ON TABLE oracle_loader_test1 IS 'oracle_loader_test1 table comment' },
	159	q{ COMMENT ON COLUMN oracle_loader_test1.value IS 'oracle_loader_test1.value column comment' },
	160	],
	161	drop => [qw/oracle_loader_test1/],
5975bbe6	162	count => 3 + 30 * 2,
5cd600fa	163	run => sub {
c4a69b87	164	my ($monikers, $classes);
c4a69b87	165	($schema, $monikers, $classes) = @_;
5cd600fa	166
	167	SKIP: {
	168	if (my $source = $monikers->{loader_test1s}) {
	169	is $schema->source($source)->column_info('id')->{sequence},
	170	'loader_test1s_id_seq',
	171	'Oracle sequence detection';
	172	}
	173	else {
7640ef4b	174	skip 'not running common tests', 1;
5cd600fa	175	}
5cd600fa	176	}
4cd5155b	177
61d1cca1	178	my $class = $classes->{oracle_loader_test1};
c4a69b87	179
c4a69b87	180	my $filename = $schema->loader->get_dump_filename($class);
fcf328c7	181	my $code = slurp_file $filename;
4cd5155b	182
61d1cca1	183	like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m,
61d1cca1	184	'table comment';
4cd5155b	185
61d1cca1	186	like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m,
61d1cca1	187	'column comment and attrs';
c4a69b87	188
	189	SKIP: {
	190	skip 'Set the DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS environment variables to run the cross-schema relationship tests', 6 * 2
	191	unless $ENV{DBICTEST_ORA_EXTRAUSER_DSN};
	192
	193	$extra_schema = $schema->clone;
	194	$extra_schema->connection(@ENV{map "DBICTEST_ORA_EXTRAUSER_$_",
	195	qw/DSN USER PASS/
	196	});
	197
	198	my $dbh1 = $schema->storage->dbh;
	199	my $dbh2 = $extra_schema->storage->dbh;
	200
	201	my ($schema1) = $dbh1->selectrow_array('SELECT USER FROM DUAL');
	202	my ($schema2) = $dbh2->selectrow_array('SELECT USER FROM DUAL');
	203
	204	$dbh1->do(<<'EOF');
	205	CREATE TABLE oracle_loader_test4 (
	206	id INT NOT NULL PRIMARY KEY,
	207	value VARCHAR(100)
	208	)
	209	EOF
5975bbe6	210
	211	$dbh1->do($_) for $auto_inc_cb->('oracle_loader_test4', 'id');
	212
c4a69b87	213	$dbh1->do("GRANT ALL ON oracle_loader_test4 TO $schema2");
5975bbe6	214	$dbh1->do("GRANT ALL ON oracle_loader_test4_id_seq TO $schema2");
	215
	216	$dbh1->do(<<"EOF");
	217	CREATE TABLE oracle_loader_test5 (
	218	id INT NOT NULL PRIMARY KEY,
	219	value VARCHAR(100),
	220	four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
	221	CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
	222	)
	223	EOF
	224	$dbh1->do($_) for $auto_inc_cb->('oracle_loader_test5', 'id');
	225	$dbh1->do("GRANT ALL ON oracle_loader_test5 TO $schema2");
	226	$dbh1->do("GRANT ALL ON oracle_loader_test5_id_seq TO $schema2");
	227
	228	$dbh2->do(<<"EOF");
	229	CREATE TABLE oracle_loader_test5 (
	230	pk INT NOT NULL PRIMARY KEY,
	231	value VARCHAR(100),
	232	four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
	233	CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
	234	)
	235	EOF
	236	$dbh2->do($_) for $auto_inc_cb->('oracle_loader_test5', 'pk');
	237	$dbh2->do("GRANT ALL ON oracle_loader_test5 TO $schema1");
	238	$dbh2->do("GRANT ALL ON oracle_loader_test5_pk_seq TO $schema1");
	239
c4a69b87	240	$dbh2->do(<<"EOF");
	241	CREATE TABLE oracle_loader_test6 (
	242	id INT NOT NULL PRIMARY KEY,
	243	value VARCHAR(100),
	244	oracle_loader_test4_id INT REFERENCES ${schema1}.oracle_loader_test4 (id)
	245	)
	246	EOF
5975bbe6	247	$dbh2->do($_) for $auto_inc_cb->('oracle_loader_test6', 'id');
c4a69b87	248	$dbh2->do("GRANT ALL ON oracle_loader_test6 to $schema1");
5975bbe6	249	$dbh2->do("GRANT ALL ON oracle_loader_test6_id_seq TO $schema1");
5975bbe6	250
c4a69b87	251	$dbh2->do(<<"EOF");
	252	CREATE TABLE oracle_loader_test7 (
	253	id INT NOT NULL PRIMARY KEY,
5975bbe6	254	value VARCHAR(100),
5975bbe6	255	six_id INT UNIQUE REFERENCES ${schema2}.oracle_loader_test6 (id)
c4a69b87	256	)
c4a69b87	257	EOF
5975bbe6	258	$dbh2->do($_) for $auto_inc_cb->('oracle_loader_test7', 'id');
c4a69b87	259	$dbh2->do("GRANT ALL ON oracle_loader_test7 to $schema1");
5975bbe6	260	$dbh2->do("GRANT ALL ON oracle_loader_test7_id_seq TO $schema1");
5975bbe6	261
c4a69b87	262	$dbh1->do(<<"EOF");
	263	CREATE TABLE oracle_loader_test8 (
	264	id INT NOT NULL PRIMARY KEY,
	265	value VARCHAR(100),
	266	oracle_loader_test7_id INT REFERENCES ${schema2}.oracle_loader_test7 (id)
	267	)
	268	EOF
5975bbe6	269	$dbh1->do($_) for $auto_inc_cb->('oracle_loader_test8', 'id');
	270	$dbh1->do("GRANT ALL ON oracle_loader_test8 to $schema2");
	271	$dbh1->do("GRANT ALL ON oracle_loader_test8_id_seq TO $schema2");
	272
	273	# We add schema to moniker_parts, so make a monikers hash for
	274	# the tests, of the form schemanum.tablenum
	275	my $schema1_moniker = join '', map ucfirst lc, split_name to_identifier $schema1;
	276	my $schema2_moniker = join '', map ucfirst lc, split_name to_identifier $schema2;
	277
	278	my %monikers;
	279	$monikers{'1.4'} = $schema1_moniker . 'OracleLoaderTest4';
	280	$monikers{'1.5'} = $schema1_moniker . 'OracleLoaderTest5';
	281	$monikers{'2.5'} = $schema2_moniker . 'OracleLoaderTest5';
	282	$monikers{'2.6'} = $schema2_moniker . 'OracleLoaderTest6';
	283	$monikers{'2.7'} = $schema2_moniker . 'OracleLoaderTest7';
	284	$monikers{'1.8'} = $schema1_moniker . 'OracleLoaderTest8';
c4a69b87	285
	286	foreach my $db_schema ([$schema1, $schema2], '%') {
	287	lives_and {
	288	rmtree EXTRA_DUMP_DIR;
	289
	290	my @warns;
	291	local $SIG{__WARN__} = sub {
	292	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
	293	};
	294
	295	make_schema_at(
	296	'OracleMultiSchema',
	297	{
	298	naming => 'current',
	299	db_schema => $db_schema,
5975bbe6	300	moniker_parts => [qw/schema name/],
c4a69b87	301	dump_directory => EXTRA_DUMP_DIR,
	302	quiet => 1,
	303	},
	304	[ $dsn, $user, $password ],
	305	);
	306
	307	diag join "\n", @warns if @warns;
	308
	309	is @warns, 0;
	310	} qq{dumped schema for "$schema1" and "$schema2" schemas with no warnings};
	311
5975bbe6	312	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
c4a69b87	313
	314	lives_and {
	315	ok $test_schema = OracleMultiSchema->connect($dsn, $user, $password);
	316	} 'connected test schema';
	317
	318	lives_and {
5975bbe6	319	ok $rsrc = $test_schema->source($monikers{'1.4'});
	320	} 'got source for table in schema1';
	321
	322	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	323	'column in schema1';
	324
	325	is try { $rsrc->column_info('value')->{data_type} }, 'varchar2',
	326	'column in schema1';
	327
	328	is try { $rsrc->column_info('value')->{size} }, 100,
	329	'column in schema1';
	330
	331	lives_and {
	332	ok $rs = $test_schema->resultset($monikers{'1.4'});
	333	} 'got resultset for table in schema1';
	334
	335	lives_and {
	336	ok $row = $rs->create({ value => 'foo' });
	337	} 'executed SQL on table in schema1';
	338
	339	my $schema1_identifier = join '_', map lc, split_name to_identifier $schema1;
	340
	341	$rel_info = try { $rsrc->relationship_info(
	342	$schema1_identifier . '_oracle_loader_test5'
	343	) };
	344
	345	is_deeply $rel_info->{cond}, {
	346	'foreign.four_id' => 'self.id'
	347	}, 'relationship in schema1';
	348
	349	is $rel_info->{attrs}{accessor}, 'single',
	350	'relationship in schema1';
	351
	352	is $rel_info->{attrs}{join_type}, 'LEFT',
	353	'relationship in schema1';
	354
	355	lives_and {
	356	ok $rsrc = $test_schema->source($monikers{'1.5'});
	357	} 'got source for table in schema1';
	358
	359	%uniqs = try { $rsrc->unique_constraints };
	360
	361	is keys %uniqs, 2,
	362	'got unique and primary constraint in schema1';
	363
	364	delete $uniqs{primary};
	365
	366	is_deeply ((values %uniqs)[0], ['four_id'],
	367	'correct unique constraint in schema1');
	368
	369	lives_and {
	370	ok $rsrc = $test_schema->source($monikers{'2.6'});
	371	} 'got source for table in schema2';
	372
	373	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
	374	'column in schema2 introspected correctly';
	375
	376	is try { $rsrc->column_info('value')->{data_type} }, 'varchar2',
	377	'column in schema2 introspected correctly';
	378
	379	is try { $rsrc->column_info('value')->{size} }, 100,
	380	'column in schema2 introspected correctly';
	381
	382	lives_and {
383	ok $rs = $test_schema->resultset($monikers{'2.6'});
384	} 'got resultset for table in schema2';
385
386	lives_and {
387	ok $row = $rs->create({ value => 'foo' });
388	} 'executed SQL on table in schema2';
389
390	$rel_info = try { $rsrc->relationship_info('oracle_loader_test7') };
391
392	is_deeply $rel_info->{cond}, {
393	'foreign.six_id' => 'self.id'
394	}, 'relationship in schema2';
395
396	is $rel_info->{attrs}{accessor}, 'single',
397	'relationship in schema2';
398
399	is $rel_info->{attrs}{join_type}, 'LEFT',
400	'relationship in schema2';
401
402	lives_and {
403	ok $rsrc = $test_schema->source($monikers{'2.7'});
404	} 'got source for table in schema2';
405
406	%uniqs = try { $rsrc->unique_constraints };
407
408	is keys %uniqs, 2,
409	'got unique and primary constraint in schema2';
410
411	delete $uniqs{primary};
412
413	is_deeply ((values %uniqs)[0], ['six_id'],
414	'correct unique constraint in schema2');
415
416	lives_and {
417	ok $test_schema->source($monikers{'2.6'})
c4a69b87	418	->has_relationship('oracle_loader_test4');
	419	} 'cross-schema relationship in multi-db_schema';
	420
	421	lives_and {
5975bbe6	422	ok $test_schema->source($monikers{'1.4'})
c4a69b87	423	->has_relationship('oracle_loader_test6s');
	424	} 'cross-schema relationship in multi-db_schema';
	425
	426	lives_and {
5975bbe6	427	ok $test_schema->source($monikers{'1.8'})
c4a69b87	428	->has_relationship('oracle_loader_test7');
	429	} 'cross-schema relationship in multi-db_schema';
	430
	431	lives_and {
5975bbe6	432	ok $test_schema->source($monikers{'2.7'})
c4a69b87	433	->has_relationship('oracle_loader_test8s');
	434	} 'cross-schema relationship in multi-db_schema';
	435	}
	436	}
5cd600fa	437	},
5cd600fa	438	},
e7262300	439	);
	440
	441	if( !$dsn \|\| !$user ) {
	442	$tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
	443	}
	444	else {
	445	$tester->run_tests();
	446	}
c4a69b87	447
	448	END {
	449	if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
	450	if (my $dbh2 = try { $extra_schema->storage->dbh }) {
	451	my $dbh1 = $schema->storage->dbh;
	452
	453	try {
5975bbe6	454	$dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test8', 'id');
	455	$dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test7', 'id');
	456	$dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test6', 'id');
	457	$dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'pk');
	458	$dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'id');
	459	$dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test4', 'id');
	460	}
	461	catch {
	462	die "Error dropping sequences for cross-schema test tables: $_";
	463	};
	464
	465	try {
c4a69b87	466	$dbh1->do('DROP TABLE oracle_loader_test8');
c4a69b87	467	$dbh2->do('DROP TABLE oracle_loader_test7');
5975bbe6	468	$dbh2->do('DROP TABLE oracle_loader_test6');
	469	$dbh2->do('DROP TABLE oracle_loader_test5');
	470	$dbh1->do('DROP TABLE oracle_loader_test5');
	471	$dbh1->do('DROP TABLE oracle_loader_test4');
c4a69b87	472	}
	473	catch {
	474	die "Error dropping cross-schema test tables: $_";
	475	};
	476	}
	477
	478	rmtree EXTRA_DUMP_DIR;
	479	}
	480	}
760fd65c	481	# vim:et sw=4 sts=4 tw=0: