[dbsrgits/DBIx-Class-Schema-Loader.git] / t / 14ora_common.t

use strict;
use lib qw(t/lib);
use dbixcsl_common_tests;
use Test::More;
use Test::Exception;
use File::Slurp ();

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

my $tester = dbixcsl_common_tests->new(
    vendor      => 'Oracle',
    auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
    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;
            }
        );
    },
    auto_inc_drop_cb => sub {
        my ($table, $col) = @_;
        return qq{ DROP SEQUENCE ${table}_${col}_seq };
    },
    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  },
        'nchar(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 },

        # 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,
        run   => sub {
            my ($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;
                }
            }

            SKIP: {
                skip 'not running comment tests', 1 unless (my $class = $classes->{oracle_loader_test1});
                my $filename = $schema->_loader->_get_dump_filename($class);
                my $code = File::Slurp::slurp $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';
            }
        },
    },
);

if( !$dsn || !$user ) {
    $tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
}
else {
    $tester->run_tests();
}
# vim:et sw=4 sts=4 tw=0:
Commit	Line	Data
e7262300	1	use strict;
	2	use lib qw(t/lib);
	3	use dbixcsl_common_tests;
6b0e47fc	4	use Test::More;
6b0e47fc	5	use Test::Exception;
4cd5155b	6	use File::Slurp ();
e7262300	7
	8	my $dsn = $ENV{DBICTEST_ORA_DSN} \|\| '';
	9	my $user = $ENV{DBICTEST_ORA_USER} \|\| '';
	10	my $password = $ENV{DBICTEST_ORA_PASS} \|\| '';
	11
	12	my $tester = dbixcsl_common_tests->new(
	13	vendor => 'Oracle',
c1ac681d	14	auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
	15	auto_inc_cb => sub {
	16	my ($table, $col) = @_;
	17	return (
	18	qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1},
	19	qq{
	20	CREATE OR REPLACE TRIGGER ${table}_${col}_trigger
	21	BEFORE INSERT ON ${table}
	22	FOR EACH ROW
	23	BEGIN
	24	SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual;
	25	END;
	26	}
	27	);
	28	},
	29	auto_inc_drop_cb => sub {
	30	my ($table, $col) = @_;
	31	return qq{ DROP SEQUENCE ${table}_${col}_seq };
	32	},
b511f36e	33	preserve_case_mode_is_exclusive => 1,
b511f36e	34	quote_char => '"',
e7262300	35	dsn => $dsn,
	36	user => $user,
	37	password => $password,
760fd65c	38	data_types => {
	39	# From:
	40	# http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
	41	#
	42	# These tests require at least Oracle 9.2, because of the VARCHAR to
	43	# VARCHAR2 casting.
	44	#
	45	# Character Types
	46	'char' => { data_type => 'char', size => 1 },
	47	'char(11)' => { data_type => 'char', size => 11 },
	48	'nchar' => { data_type => 'nchar', size => 1 },
	49	'nchar(11)' => { data_type => 'nchar', size => 11 },
	50	'varchar(20)' => { data_type => 'varchar2', size => 20 },
	51	'varchar2(20)' => { data_type => 'varchar2', size => 20 },
	52	'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
	53
	54	# Numeric Types
	55	#
1c22571b	56	# integer/decimal/numeric is alised to NUMBER
760fd65c	57	#
4ea15dfe	58	'integer' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	59	'int' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	60	'smallint' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
760fd65c	61
4ea15dfe	62	'decimal' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	63	'dec' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
	64	'numeric' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
760fd65c	65
4ea15dfe	66	'decimal(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
	67	'dec(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
	68	'numeric(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
760fd65c	69
4ea15dfe	70	'decimal(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
	71	'dec(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
	72	'numeric(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
760fd65c	73
1c22571b	74	'binary_float' => { data_type => 'real', original => { data_type => 'binary_float' } },
	75	'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } },
	76
	77	# these are not mentioned in the summary chart, must be aliased
ceb009d3	78	real => { data_type => 'real', original => { data_type => 'float', size => 63 } },
1c22571b	79	'float(63)' => { data_type => 'real', original => { data_type => 'float', size => 63 } },
	80	'float(64)' => { data_type => 'double precision', original => { data_type => 'float', size => 64 } },
	81	'float(126)' => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
	82	float => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
760fd65c	83
	84	# Blob Types
	85	'raw(50)' => { data_type => 'raw', size => 50 },
	86	'clob' => { data_type => 'clob' },
	87	'nclob' => { data_type => 'nclob' },
	88	'blob' => { data_type => 'blob' },
	89	'bfile' => { data_type => 'bfile' },
3b29d0ac	90	'long' => { data_type => 'long' },
760fd65c	91	'long raw' => { data_type => 'long raw' },
	92
	93	# Datetime Types
3785bc2e	94	'date' => { data_type => 'datetime', original => { data_type => 'date' } },
760fd65c	95	'date default sysdate'
3785bc2e	96	=> { data_type => 'datetime', default_value => \'current_timestamp',
3785bc2e	97	original => { data_type => 'date', default_value => \'sysdate' } },
760fd65c	98	'timestamp' => { data_type => 'timestamp' },
	99	'timestamp default current_timestamp'
	100	=> { data_type => 'timestamp', default_value => \'current_timestamp' },
	101	'timestamp(3)' => { data_type => 'timestamp', size => 3 },
	102	'timestamp with time zone'
	103	=> { data_type => 'timestamp with time zone' },
	104	'timestamp(3) with time zone'
	105	=> { data_type => 'timestamp with time zone', size => 3 },
	106	'timestamp with local time zone'
	107	=> { data_type => 'timestamp with local time zone' },
	108	'timestamp(3) with local time zone'
	109	=> { data_type => 'timestamp with local time zone', size => 3 },
	110	'interval year to month'
	111	=> { data_type => 'interval year to month' },
	112	'interval year(3) to month'
	113	=> { data_type => 'interval year to month', size => 3 },
	114	'interval day to second'
	115	=> { data_type => 'interval day to second' },
	116	'interval day(3) to second'
	117	=> { data_type => 'interval day to second', size => [3,6] },
	118	'interval day to second(3)'
	119	=> { data_type => 'interval day to second', size => [2,3] },
	120	'interval day(3) to second(3)'
	121	=> { data_type => 'interval day to second', size => [3,3] },
	122
	123	# Other Types
	124	'rowid' => { data_type => 'rowid' },
	125	'urowid' => { data_type => 'urowid' },
	126	'urowid(3333)' => { data_type => 'urowid', size => 3333 },
	127	},
5cd600fa	128	extra => {
4cd5155b	129	create => [
	130	q{
	131	CREATE TABLE oracle_loader_test1 (
	132	id NUMBER(11),
	133	value VARCHAR2(100)
	134	)
	135	},
	136	q{ COMMENT ON TABLE oracle_loader_test1 IS 'oracle_loader_test1 table comment' },
	137	q{ COMMENT ON COLUMN oracle_loader_test1.value IS 'oracle_loader_test1.value column comment' },
	138	],
	139	drop => [qw/oracle_loader_test1/],
	140	count => 3,
5cd600fa	141	run => sub {
	142	my ($schema, $monikers, $classes) = @_;
	143
	144	SKIP: {
	145	if (my $source = $monikers->{loader_test1s}) {
	146	is $schema->source($source)->column_info('id')->{sequence},
	147	'loader_test1s_id_seq',
	148	'Oracle sequence detection';
	149	}
	150	else {
7640ef4b	151	skip 'not running common tests', 1;
5cd600fa	152	}
5cd600fa	153	}
4cd5155b	154
	155	SKIP: {
	156	skip 'not running comment tests', 1 unless (my $class = $classes->{oracle_loader_test1});
	157	my $filename = $schema->_loader->_get_dump_filename($class);
	158	my $code = File::Slurp::slurp $filename;
	159
	160	like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m,
	161	'table comment';
	162
	163	like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m,
	164	'column comment and attrs';
	165	}
5cd600fa	166	},
5cd600fa	167	},
e7262300	168	);
	169
	170	if( !$dsn \|\| !$user ) {
	171	$tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
	172	}
	173	else {
	174	$tester->run_tests();
	175	}
760fd65c	176	# vim:et sw=4 sts=4 tw=0: