rename option to undef_on_null_fk and make it default for belongs_to
[dbsrgits/DBIx-Class-Historic.git] / t / 73oracle.t
CommitLineData
70350518 1use strict;
2use warnings;
3
4use Test::More;
5use lib qw(t/lib);
6use DBICTest;
0567538f 7
8my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
9
70350518 10plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
39b8d119 11 'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
12 ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
0567538f 13 unless ($dsn && $user && $pass);
14
39b8d119 15plan tests => 23;
0567538f 16
3ff5b740 17my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
0567538f 18
3ff5b740 19my $dbh = $schema->storage->dbh;
0567538f 20
21eval {
22 $dbh->do("DROP SEQUENCE artist_seq");
39b8d119 23 $dbh->do("DROP SEQUENCE pkid1_seq");
24 $dbh->do("DROP SEQUENCE pkid2_seq");
25 $dbh->do("DROP SEQUENCE nonpkid_seq");
0567538f 26 $dbh->do("DROP TABLE artist");
39b8d119 27 $dbh->do("DROP TABLE sequence_test");
2660b14e 28 $dbh->do("DROP TABLE cd");
29 $dbh->do("DROP TABLE track");
0567538f 30};
31$dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
39b8d119 32$dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
33$dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
34$dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
7c4ead2d 35$dbh->do("CREATE TABLE artist (artistid NUMBER(12), name VARCHAR(255), rank NUMBER(38), charfield VARCHAR2(10))");
39b8d119 36$dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))");
2660b14e 37$dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4))");
e8e971f2 38$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE)");
2660b14e 39
0567538f 40$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
39b8d119 41$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
0567538f 42$dbh->do(qq{
43 CREATE OR REPLACE TRIGGER artist_insert_trg
44 BEFORE INSERT ON artist
45 FOR EACH ROW
46 BEGIN
47 IF :new.artistid IS NULL THEN
48 SELECT artist_seq.nextval
49 INTO :new.artistid
50 FROM DUAL;
51 END IF;
52 END;
53});
54
3ff5b740 55# This is in Core now, but it's here just to test that it doesn't break
56$schema->class('Artist')->load_components('PK::Auto');
57# These are compat shims for PK::Auto...
58$schema->class('CD')->load_components('PK::Auto::Oracle');
59$schema->class('Track')->load_components('PK::Auto::Oracle');
0567538f 60
61# test primary key handling
3ff5b740 62my $new = $schema->resultset('Artist')->create({ name => 'foo' });
c8f4b52b 63is($new->artistid, 1, "Oracle Auto-PK worked");
0567538f 64
2660b14e 65# test join with row count ambiguity
3ff5b740 66my $cd = $schema->resultset('CD')->create({ cdid => 1, artist => 1, title => 'EP C', year => '2003' });
67my $track = $schema->resultset('Track')->create({ trackid => 1, cd => 1, position => 1, title => 'Track1' });
68my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
2660b14e 69 { join => 'cd',
70 rows => 2 }
71);
72
73is($tjoin->next->title, 'Track1', "ambiguous column ok");
74
286f32b3 75# check count distinct with multiple columns
3ff5b740 76my $other_track = $schema->resultset('Track')->create({ trackid => 2, cd => 1, position => 1, title => 'Track2' });
77my $tcount = $schema->resultset('Track')->search(
286f32b3 78 {},
79 {
70350518 80 select => [{count => {distinct => ['position', 'title']}}],
81 as => ['count']
286f32b3 82 }
83 );
84
85is($tcount->next->get_column('count'), 2, "multiple column select distinct ok");
2660b14e 86
0567538f 87# test LIMIT support
88for (1..6) {
3ff5b740 89 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
0567538f 90}
3ff5b740 91my $it = $schema->resultset('Artist')->search( {},
0567538f 92 { rows => 3,
93 offset => 2,
94 order_by => 'artistid' }
95);
96is( $it->count, 3, "LIMIT count ok" );
97is( $it->next->name, "Artist 2", "iterator->next ok" );
98$it->next;
99$it->next;
100is( $it->next, undef, "next past end of resultset ok" );
101
e8e971f2 102{
103 my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 });
104 my @results = $rs->all;
105 is( scalar @results, 1, "Group by with limit OK" );
106}
107
ccd6f984 108# test auto increment using sequences WITHOUT triggers
39b8d119 109for (1..5) {
110 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
111 is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
112 is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
113 is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
114}
115my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
116is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
ccd6f984 117
0567538f 118# clean up our mess
3ff5b740 119END {
120 if($dbh) {
121 $dbh->do("DROP SEQUENCE artist_seq");
39b8d119 122 $dbh->do("DROP SEQUENCE pkid1_seq");
123 $dbh->do("DROP SEQUENCE pkid2_seq");
124 $dbh->do("DROP SEQUENCE nonpkid_seq");
3ff5b740 125 $dbh->do("DROP TABLE artist");
39b8d119 126 $dbh->do("DROP TABLE sequence_test");
3ff5b740 127 $dbh->do("DROP TABLE cd");
128 $dbh->do("DROP TABLE track");
129 }
130}
0567538f 131