[dbsrgits/DBIx-Class-Schema-Loader.git] / t / 10_10informix_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 lib qw(t/lib);

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

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

# to support " quoted identifiers
BEGIN { $ENV{DELIMIDENT} = 'y' }

# This test doesn't run over a shared memory connection, because of the single connection limit.

my $dsn      = $ENV{DBICTEST_INFORMIX_DSN} || '';
my $user     = $ENV{DBICTEST_INFORMIX_USER} || '';
my $password = $ENV{DBICTEST_INFORMIX_PASS} || '';

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

my $tester = dbixcsl_common_tests->new(
    vendor         => 'Informix',
    auto_inc_pk    => 'serial primary key',
    null           => '',
    default_function     => 'current year to fraction(5)',
    default_function_def => 'datetime year to fraction(5) default current year to fraction(5)',
    dsn            => $dsn,
    user           => $user,
    password       => $password,
    loader_options => { preserve_case => 1 },
    quote_char     => '"',
    data_types => {
        # http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqlr.doc/ids_sqr_094.htm

        # Numeric Types
        'int'              => { data_type => 'integer' },
        integer            => { data_type => 'integer' },
        int8               => { data_type => 'bigint' },
        bigint             => { data_type => 'bigint' },
        serial             => { data_type => 'integer', is_auto_increment => 1 },
        bigserial          => { data_type => 'bigint',  is_auto_increment => 1 },
        serial8            => { data_type => 'bigint',  is_auto_increment => 1 },
        smallint           => { data_type => 'smallint' },
        real               => { data_type => 'real' },
        smallfloat         => { data_type => 'real' },
        # just 'double' is a syntax error
        'double precision' => { data_type => 'double precision' },
        float              => { data_type => 'double precision' },
        'float(1)'         => { data_type => 'double precision' },
        'float(5)'         => { data_type => 'double precision' },
        'float(10)'        => { data_type => 'double precision' },
        'float(15)'        => { data_type => 'double precision' },
        'float(16)'        => { data_type => 'double precision' },
        numeric            => { data_type => 'numeric' },
        decimal            => { data_type => 'numeric' },
        dec                => { data_type => 'numeric' },
	'numeric(6,3)'     => { data_type => 'numeric', size => [6,3] },
	'decimal(6,3)'     => { data_type => 'numeric', size => [6,3] },
	'dec(6,3)'         => { data_type => 'numeric', size => [6,3] },

        # Boolean Type
        # XXX this should map to 'boolean'
        boolean            => { data_type => 'smallint' },

        # Money Type
        money              => { data_type => 'money' },
        'money(3,3)'       => { data_type => 'numeric', size => [3,3] },

        # Byte Type
        byte               => { data_type => 'bytea', original => { data_type => 'byte' } },

        # Character String Types
        char               => { data_type => 'char', size => 1 },
        'char(3)'          => { data_type => 'char', size => 3 },
        character          => { data_type => 'char', size => 1 },
        'character(3)'     => { data_type => 'char', size => 3 },
        'varchar(3)'       => { data_type => 'varchar', size => 3 },
        'character varying(3)'
                           => { data_type => 'varchar', size => 3 },
        # XXX min size not supported, colmin from syscolumns is NULL
        'varchar(3,2)'     => { data_type => 'varchar', size => 3 },
        'character varying(3,2)'
                           => { data_type => 'varchar', size => 3 },
        nchar              => { data_type => 'nchar', size => 1 },
        'nchar(3)'         => { data_type => 'nchar', size => 3 },
        'nvarchar(3)'      => { data_type => 'nvarchar', size => 3 },
        'nvarchar(3,2)'    => { data_type => 'nvarchar', size => 3 },
        'lvarchar(3)'      => { data_type => 'lvarchar', size => 3 },
        'lvarchar(33)'     => { data_type => 'lvarchar', size => 33 },
        text               => { data_type => 'text' },

        # DateTime Types
        date               => { data_type => 'date' },
        'date default today'
                           => { data_type => 'date', default_value => \'today' },
        # XXX support all precisions
        'datetime year to fraction(5)',
                           => { data_type => 'datetime year to fraction(5)' },
        'datetime year to fraction(5) default current year to fraction(5)',
                           => { data_type => 'datetime year to fraction(5)', default_value => \'current year to fraction(5)' },
        # XXX do interval

        # Blob Types
        # XXX no way to distinguish opaque types boolean, blob and clob
        blob               => { data_type => 'blob' },
        clob               => { data_type => 'blob' },

        # IDSSECURITYLABEL Type
        #
        # This requires the DBSECADM privilege and a security policy on the
        # table, things I know nothing about.
#        idssecuritylabel   => { data_type => 'idssecuritylabel' },

        # List Types
        # XXX need to introspect element type too
        'list(varchar(20) not null)'
                           => { data_type => 'list' },
        'multiset(varchar(20) not null)'
                           => { data_type => 'multiset' },
        'set(varchar(20) not null)'
                           => { data_type => 'set' },
    },
    extra => {
        count => 24,
        run   => sub {
            ($schema) = @_;

            SKIP: {
                skip 'Set the DBICTEST_INFORMIX_EXTRADB_DSN, _USER and _PASS environment variables to run the multi-database tests', 24
                    unless $ENV{DBICTEST_INFORMIX_EXTRADB_DSN};

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

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

                $dbh1->do(<<'EOF');
                    CREATE TABLE informix_loader_test4 (
                        id SERIAL PRIMARY KEY,
                        value VARCHAR(100)
                    )
EOF
                $dbh1->do(<<'EOF');
                    CREATE TABLE informix_loader_test5 (
                        id SERIAL PRIMARY KEY,
                        value VARCHAR(100),
                        four_id INTEGER UNIQUE REFERENCES informix_loader_test4 (id)
                    )
EOF
                $dbh2->do(<<"EOF");
                    CREATE TABLE informix_loader_test6 (
                        id SERIAL PRIMARY KEY,
                        value VARCHAR(100)
                    )
EOF
                $dbh2->do(<<"EOF");
                    CREATE TABLE informix_loader_test7 (
                        id SERIAL PRIMARY KEY,
                        value VARCHAR(100),
                        six_id INTEGER UNIQUE REFERENCES informix_loader_test6 (id)
                    )
EOF
                lives_and {
                    my @warns;
                    local $SIG{__WARN__} = sub {
                        push @warns, $_[0] unless $_[0] =~ /\bcollides\b/
                            || $_[0] =~ /unreferencable/;
                    };
 
                    make_schema_at(
                        'InformixMultiDatabase',
                        {
                            naming => 'current',
                            db_schema => { '%' => '%' },
                            dump_directory => EXTRA_DUMP_DIR,
                            quiet => 1,
                        },
                        [ $dsn, $user, $password ],
                    );

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

                    is @warns, 0;
                } 'dumped schema for all databases with no warnings';

                my $test_schema;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if( !$dsn ) {
    $tester->skip_tests('You need to set the DBICTEST_INFORMIX_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 {
                $dbh2->do('DROP TABLE informix_loader_test7');
                $dbh2->do('DROP TABLE informix_loader_test6');
                $dbh1->do('DROP TABLE informix_loader_test5');
                $dbh1->do('DROP TABLE informix_loader_test4');
            }
            catch {
                die "Error dropping test tables: $_";
            };
        }

        rmtree EXTRA_DUMP_DIR;
    }
}
# vim:et sts=4 sw=4 tw=0:
Commit	Line	Data
bc5afe55	1	use strict;
c4a69b87	2	use warnings;
	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
bc5afe55	9	use lib qw(t/lib);
c4a69b87	10
	11	use dbixcsl_common_tests ();
	12	use dbixcsl_test_dir '$tdir';
	13
	14	use constant EXTRA_DUMP_DIR => "$tdir/informix_extra_dump";
bc5afe55	15
b511f36e	16	# to support " quoted identifiers
	17	BEGIN { $ENV{DELIMIDENT} = 'y' }
	18
bc5afe55	19	# This test doesn't run over a shared memory connection, because of the single connection limit.
	20
	21	my $dsn = $ENV{DBICTEST_INFORMIX_DSN} \|\| '';
	22	my $user = $ENV{DBICTEST_INFORMIX_USER} \|\| '';
	23	my $password = $ENV{DBICTEST_INFORMIX_PASS} \|\| '';
	24
c4a69b87	25	my ($schema, $extra_schema); # for cleanup in END for extra tests
c4a69b87	26
bc5afe55	27	my $tester = dbixcsl_common_tests->new(
bc5afe55	28	vendor => 'Informix',
c7e6dc1f	29	auto_inc_pk => 'serial primary key',
bc5afe55	30	null => '',
c7e6dc1f	31	default_function => 'current year to fraction(5)',
c7e6dc1f	32	default_function_def => 'datetime year to fraction(5) default current year to fraction(5)',
bc5afe55	33	dsn => $dsn,
	34	user => $user,
	35	password => $password,
b511f36e	36	loader_options => { preserve_case => 1 },
b511f36e	37	quote_char => '"',
c7e6dc1f	38	data_types => {
	39	# http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqlr.doc/ids_sqr_094.htm
	40
	41	# Numeric Types
	42	'int' => { data_type => 'integer' },
	43	integer => { data_type => 'integer' },
	44	int8 => { data_type => 'bigint' },
	45	bigint => { data_type => 'bigint' },
	46	serial => { data_type => 'integer', is_auto_increment => 1 },
	47	bigserial => { data_type => 'bigint', is_auto_increment => 1 },
	48	serial8 => { data_type => 'bigint', is_auto_increment => 1 },
	49	smallint => { data_type => 'smallint' },
	50	real => { data_type => 'real' },
	51	smallfloat => { data_type => 'real' },
	52	# just 'double' is a syntax error
	53	'double precision' => { data_type => 'double precision' },
	54	float => { data_type => 'double precision' },
	55	'float(1)' => { data_type => 'double precision' },
	56	'float(5)' => { data_type => 'double precision' },
	57	'float(10)' => { data_type => 'double precision' },
	58	'float(15)' => { data_type => 'double precision' },
	59	'float(16)' => { data_type => 'double precision' },
	60	numeric => { data_type => 'numeric' },
	61	decimal => { data_type => 'numeric' },
	62	dec => { data_type => 'numeric' },
	63	'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
	64	'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
	65	'dec(6,3)' => { data_type => 'numeric', size => [6,3] },
	66
	67	# Boolean Type
	68	# XXX this should map to 'boolean'
	69	boolean => { data_type => 'smallint' },
	70
	71	# Money Type
	72	money => { data_type => 'money' },
	73	'money(3,3)' => { data_type => 'numeric', size => [3,3] },
	74
	75	# Byte Type
	76	byte => { data_type => 'bytea', original => { data_type => 'byte' } },
	77
	78	# Character String Types
	79	char => { data_type => 'char', size => 1 },
	80	'char(3)' => { data_type => 'char', size => 3 },
	81	character => { data_type => 'char', size => 1 },
	82	'character(3)' => { data_type => 'char', size => 3 },
	83	'varchar(3)' => { data_type => 'varchar', size => 3 },
	84	'character varying(3)'
	85	=> { data_type => 'varchar', size => 3 },
	86	# XXX min size not supported, colmin from syscolumns is NULL
	87	'varchar(3,2)' => { data_type => 'varchar', size => 3 },
	88	'character varying(3,2)'
	89	=> { data_type => 'varchar', size => 3 },
	90	nchar => { data_type => 'nchar', size => 1 },
	91	'nchar(3)' => { data_type => 'nchar', size => 3 },
	92	'nvarchar(3)' => { data_type => 'nvarchar', size => 3 },
	93	'nvarchar(3,2)' => { data_type => 'nvarchar', size => 3 },
	94	'lvarchar(3)' => { data_type => 'lvarchar', size => 3 },
	95	'lvarchar(33)' => { data_type => 'lvarchar', size => 33 },
	96	text => { data_type => 'text' },
	97
	98	# DateTime Types
	99	date => { data_type => 'date' },
	100	'date default today'
	101	=> { data_type => 'date', default_value => \'today' },
102	# XXX support all precisions
103	'datetime year to fraction(5)',
104	=> { data_type => 'datetime year to fraction(5)' },
105	'datetime year to fraction(5) default current year to fraction(5)',
106	=> { data_type => 'datetime year to fraction(5)', default_value => \'current year to fraction(5)' },
107	# XXX do interval
108
109	# Blob Types
110	# XXX no way to distinguish opaque types boolean, blob and clob
111	blob => { data_type => 'blob' },
112	clob => { data_type => 'blob' },
113
114	# IDSSECURITYLABEL Type
115	#
116	# This requires the DBSECADM privilege and a security policy on the
117	# table, things I know nothing about.
118	# idssecuritylabel => { data_type => 'idssecuritylabel' },
119
120	# List Types
121	# XXX need to introspect element type too
122	'list(varchar(20) not null)'
123	=> { data_type => 'list' },
124	'multiset(varchar(20) not null)'
125	=> { data_type => 'multiset' },
126	'set(varchar(20) not null)'
127	=> { data_type => 'set' },
128	},
c4a69b87	129	extra => {
	130	count => 24,
	131	run => sub {
	132	($schema) = @_;
	133
	134	SKIP: {
	135	skip 'Set the DBICTEST_INFORMIX_EXTRADB_DSN, _USER and _PASS environment variables to run the multi-database tests', 24
	136	unless $ENV{DBICTEST_INFORMIX_EXTRADB_DSN};
	137
	138	$extra_schema = $schema->clone;
	139	$extra_schema->connection(@ENV{map "DBICTEST_INFORMIX_EXTRADB_$_",
	140	qw/DSN USER PASS/
	141	});
	142
	143	my $dbh1 = $schema->storage->dbh;
	144	my $dbh2 = $extra_schema->storage->dbh;
	145
	146	$dbh1->do(<<'EOF');
	147	CREATE TABLE informix_loader_test4 (
	148	id SERIAL PRIMARY KEY,
	149	value VARCHAR(100)
	150	)
	151	EOF
	152	$dbh1->do(<<'EOF');
	153	CREATE TABLE informix_loader_test5 (
	154	id SERIAL PRIMARY KEY,
	155	value VARCHAR(100),
	156	four_id INTEGER UNIQUE REFERENCES informix_loader_test4 (id)
	157	)
	158	EOF
	159	$dbh2->do(<<"EOF");
	160	CREATE TABLE informix_loader_test6 (
	161	id SERIAL PRIMARY KEY,
	162	value VARCHAR(100)
	163	)
	164	EOF
	165	$dbh2->do(<<"EOF");
	166	CREATE TABLE informix_loader_test7 (
	167	id SERIAL PRIMARY KEY,
	168	value VARCHAR(100),
	169	six_id INTEGER UNIQUE REFERENCES informix_loader_test6 (id)
	170	)
	171	EOF
	172	lives_and {
	173	my @warns;
	174	local $SIG{__WARN__} = sub {
	175	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/
	176	\|\| $_[0] =~ /unreferencable/;
	177	};
	178
	179	make_schema_at(
	180	'InformixMultiDatabase',
	181	{
	182	naming => 'current',
	183	db_schema => { '%' => '%' },
	184	dump_directory => EXTRA_DUMP_DIR,
	185	quiet => 1,
	186	},
	187	[ $dsn, $user, $password ],
	188	);
	189
	190	diag join "\n", @warns if @warns;
	191
	192	is @warns, 0;
193	} 'dumped schema for all databases with no warnings';
194
195	my $test_schema;
196
197	lives_and {
198	ok $test_schema = InformixMultiDatabase->connect($dsn, $user, $password);
199	} 'connected test schema';
200
201	my ($rsrc, $rs, $row, $rel_info, %uniqs);
202
203	lives_and {
204	ok $rsrc = $test_schema->source('InformixLoaderTest4');
205	} 'got source for table in database one';
206
207	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
208	'column in database one';
209
210	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
211	'column in database one';
212
213	is try { $rsrc->column_info('value')->{size} }, 100,
214	'column in database one';
215
216	lives_and {
217	ok $rs = $test_schema->resultset('InformixLoaderTest4');
218	} 'got resultset for table in database one';
219
220	lives_and {
221	ok $row = $rs->create({ value => 'foo' });
222	} 'executed SQL on table in database one';
223
224	$rel_info = try { $rsrc->relationship_info('informix_loader_test5') };
225
226	is_deeply $rel_info->{cond}, {
227	'foreign.four_id' => 'self.id'
228	}, 'relationship in database one';
229
230	is $rel_info->{attrs}{accessor}, 'single',
231	'relationship in database one';
232
233	is $rel_info->{attrs}{join_type}, 'LEFT',
234	'relationship in database one';
235
236	lives_and {
237	ok $rsrc = $test_schema->source('InformixLoaderTest5');
238	} 'got source for table in database one';
239
240	%uniqs = try { $rsrc->unique_constraints };
241
242	is keys %uniqs, 2,
243	'got unique and primary constraint in database one';
244
245	lives_and {
246	ok $rsrc = $test_schema->source('InformixLoaderTest6');
247	} 'got source for table in database two';
248
249	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
250	'column in database two introspected correctly';
251
252	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
253	'column in database two introspected correctly';
254
255	is try { $rsrc->column_info('value')->{size} }, 100,
256	'column in database two introspected correctly';
257
258	lives_and {
259	ok $rs = $test_schema->resultset('InformixLoaderTest6');
260	} 'got resultset for table in database two';
261
262	lives_and {
263	ok $row = $rs->create({ value => 'foo' });
264	} 'executed SQL on table in database two';
265
266	$rel_info = try { $rsrc->relationship_info('informix_loader_test7') };
267
268	is_deeply $rel_info->{cond}, {
269	'foreign.six_id' => 'self.id'
270	}, 'relationship in database two';
271
272	is $rel_info->{attrs}{accessor}, 'single',
273	'relationship in database two';
274
275	is $rel_info->{attrs}{join_type}, 'LEFT',
276	'relationship in database two';
277
278	lives_and {
279	ok $rsrc = $test_schema->source('InformixLoaderTest7');
280	} 'got source for table in database two';
281
282	%uniqs = try { $rsrc->unique_constraints };
283
284	is keys %uniqs, 2,
285	'got unique and primary constraint in database two';
286	}
287	},
288	},
bc5afe55	289	);
	290
	291	if( !$dsn ) {
	292	$tester->skip_tests('You need to set the DBICTEST_INFORMIX_DSN, _USER, and _PASS environment variables');
	293	}
	294	else {
	295	$tester->run_tests();
	296	}
c4a69b87	297
	298	END {
	299	if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
	300	if (my $dbh2 = try { $extra_schema->storage->dbh }) {
	301	my $dbh1 = $schema->storage->dbh;
	302
	303	try {
	304	$dbh2->do('DROP TABLE informix_loader_test7');
	305	$dbh2->do('DROP TABLE informix_loader_test6');
	306	$dbh1->do('DROP TABLE informix_loader_test5');
	307	$dbh1->do('DROP TABLE informix_loader_test4');
	308	}
	309	catch {
	310	die "Error dropping test tables: $_";
	311	};
	312	}
	313
	314	rmtree EXTRA_DUMP_DIR;
	315	}
	316	}
bc5afe55	317	# vim:et sts=4 sw=4 tw=0: