datetime millisecond precision for MSSQL
[dbsrgits/DBIx-Class-Historic.git] / t / 73oracle.t
CommitLineData
cb464582 1{
2 package # hide from PAUSE
3 DBICTest::Schema::ArtistFQN;
4
5 use base 'DBIx::Class::Core';
6
7 __PACKAGE__->table(
8 defined $ENV{DBICTEST_ORA_USER}
9 ? $ENV{DBICTEST_ORA_USER} . '.artist'
10 : 'artist'
11 );
12 __PACKAGE__->add_columns(
13 'artistid' => {
14 data_type => 'integer',
15 is_auto_increment => 1,
16 },
17 'name' => {
18 data_type => 'varchar',
19 size => 100,
20 is_nullable => 1,
21 },
22 );
23 __PACKAGE__->set_primary_key('artistid');
24
25 1;
26}
27
70350518 28use strict;
e6dd7b42 29use warnings;
70350518 30
5db2758d 31use Test::Exception;
70350518 32use Test::More;
33use lib qw(t/lib);
34use DBICTest;
0567538f 35
36my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
37
70350518 38plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
39b8d119 39 'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
40 ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
0567538f 41 unless ($dsn && $user && $pass);
42
cb464582 43DBICTest::Schema->load_classes('ArtistFQN');
9900b569 44my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
0567538f 45
3ff5b740 46my $dbh = $schema->storage->dbh;
0567538f 47
48eval {
49 $dbh->do("DROP SEQUENCE artist_seq");
e6dd7b42 50 $dbh->do("DROP SEQUENCE cd_seq");
39b8d119 51 $dbh->do("DROP SEQUENCE pkid1_seq");
52 $dbh->do("DROP SEQUENCE pkid2_seq");
53 $dbh->do("DROP SEQUENCE nonpkid_seq");
0567538f 54 $dbh->do("DROP TABLE artist");
39b8d119 55 $dbh->do("DROP TABLE sequence_test");
2660b14e 56 $dbh->do("DROP TABLE track");
b7b18f32 57 $dbh->do("DROP TABLE cd");
0567538f 58};
59$dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
e6dd7b42 60$dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
39b8d119 61$dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
62$dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
63$dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
b7b18f32 64
7c4ead2d 65$dbh->do("CREATE TABLE artist (artistid NUMBER(12), name VARCHAR(255), rank NUMBER(38), charfield VARCHAR2(10))");
b7b18f32 66$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
67
39b8d119 68$dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))");
b7b18f32 69$dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
2660b14e 70
b7b18f32 71$dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
e6dd7b42 72$dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
b7b18f32 73
74$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)");
75
0567538f 76$dbh->do(qq{
77 CREATE OR REPLACE TRIGGER artist_insert_trg
78 BEFORE INSERT ON artist
79 FOR EACH ROW
80 BEGIN
81 IF :new.artistid IS NULL THEN
82 SELECT artist_seq.nextval
83 INTO :new.artistid
84 FROM DUAL;
85 END IF;
86 END;
87});
e6dd7b42 88$dbh->do(qq{
89 CREATE OR REPLACE TRIGGER cd_insert_trg
90 BEFORE INSERT ON cd
91 FOR EACH ROW
92 BEGIN
93 IF :new.cdid IS NULL THEN
94 SELECT cd_seq.nextval
95 INTO :new.cdid
96 FROM DUAL;
97 END IF;
98 END;
99});
0567538f 100
5db2758d 101{
102 # Swiped from t/bindtype_columns.t to avoid creating my own Resultset.
103
104 local $SIG{__WARN__} = sub {};
105 eval { $dbh->do('DROP TABLE bindtype_test') };
106
107 $dbh->do(qq[
e6dd7b42 108 CREATE TABLE bindtype_test
5db2758d 109 (
110 id integer NOT NULL PRIMARY KEY,
111 bytea integer NULL,
112 blob blob NULL,
113 clob clob NULL
114 )
115 ],{ RaiseError => 1, PrintError => 1 });
116}
117
3ff5b740 118# This is in Core now, but it's here just to test that it doesn't break
119$schema->class('Artist')->load_components('PK::Auto');
120# These are compat shims for PK::Auto...
121$schema->class('CD')->load_components('PK::Auto::Oracle');
122$schema->class('Track')->load_components('PK::Auto::Oracle');
0567538f 123
124# test primary key handling
3ff5b740 125my $new = $schema->resultset('Artist')->create({ name => 'foo' });
c8f4b52b 126is($new->artistid, 1, "Oracle Auto-PK worked");
0567538f 127
e6dd7b42 128my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
62d4dbae 129is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name");
e6dd7b42 130
cb464582 131# test again with fully-qualified table name
132$new = $schema->resultset('ArtistFQN')->create( { name => 'bar' } );
133is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" );
134
62d4dbae 135# test rel names over the 30 char limit
136my $query = $schema->resultset('Artist')->search({
6c0230de 137 artistid => 1
62d4dbae 138}, {
139 prefetch => 'cds_very_very_very_long_relationship_name'
140});
141
142lives_and {
6c0230de 143 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
62d4dbae 144} 'query with rel name over 30 chars survived and worked';
145
6c0230de 146# rel name over 30 char limit with user condition
147# This requires walking the SQLA data structure.
148{
149 local $TODO = 'user condition on rel longer than 30 chars';
150
151 $query = $schema->resultset('Artist')->search({
152 'cds_very_very_very_long_relationship_name.title' => 'EP C'
153 }, {
154 prefetch => 'cds_very_very_very_long_relationship_name'
155 });
156
157 lives_and {
158 is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
159 } 'query with rel name over 30 chars and user condition survived and worked';
160}
161
9900b569 162# test join with row count ambiguity
d2a3958e 163
d2a3958e 164my $track = $schema->resultset('Track')->create({ trackid => 1, cd => 1,
9900b569 165 position => 1, title => 'Track1' });
3ff5b740 166my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
2660b14e 167 { join => 'cd',
168 rows => 2 }
169);
170
d2a3958e 171ok(my $row = $tjoin->next);
172
173is($row->title, 'Track1', "ambiguous column ok");
2660b14e 174
286f32b3 175# check count distinct with multiple columns
3ff5b740 176my $other_track = $schema->resultset('Track')->create({ trackid => 2, cd => 1, position => 1, title => 'Track2' });
11d68671 177
3ff5b740 178my $tcount = $schema->resultset('Track')->search(
11d68671 179 {},
180 {
181 select => [ qw/position title/ ],
182 distinct => 1,
183 }
184);
f12f0d97 185is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
11d68671 186
187$tcount = $schema->resultset('Track')->search(
188 {},
189 {
190 columns => [ qw/position title/ ],
191 distinct => 1,
192 }
193);
f12f0d97 194is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
286f32b3 195
11d68671 196$tcount = $schema->resultset('Track')->search(
197 {},
e6dd7b42 198 {
11d68671 199 group_by => [ qw/position title/ ]
200 }
201);
f12f0d97 202is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok');
2660b14e 203
0567538f 204# test LIMIT support
205for (1..6) {
3ff5b740 206 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
0567538f 207}
3ff5b740 208my $it = $schema->resultset('Artist')->search( {},
0567538f 209 { rows => 3,
cb464582 210 offset => 3,
0567538f 211 order_by => 'artistid' }
212);
213is( $it->count, 3, "LIMIT count ok" );
214is( $it->next->name, "Artist 2", "iterator->next ok" );
215$it->next;
216$it->next;
217is( $it->next, undef, "next past end of resultset ok" );
218
e8e971f2 219{
220 my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 });
221 my @results = $rs->all;
222 is( scalar @results, 1, "Group by with limit OK" );
223}
224
b7b18f32 225# test with_deferred_fk_checks
226lives_ok {
227 $schema->storage->with_deferred_fk_checks(sub {
228 $schema->resultset('Track')->create({
229 trackid => 999, cd => 999, position => 1, title => 'deferred FK track'
230 });
231 $schema->resultset('CD')->create({
232 artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd'
233 });
234 });
235} 'with_deferred_fk_checks code survived';
236
237is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track',
238 'code in with_deferred_fk_checks worked';
239
240throws_ok {
241 $schema->resultset('Track')->create({
242 trackid => 1, cd => 9999, position => 1, title => 'Track1'
243 });
244} qr/constraint/i, 'with_deferred_fk_checks is off';
245
ccd6f984 246# test auto increment using sequences WITHOUT triggers
39b8d119 247for (1..5) {
248 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
249 is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
250 is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
251 is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
252}
253my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
254is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
ccd6f984 255
8068691e 256SKIP: {
d7f20fdf 257 my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
258 $binstr{'large'} = $binstr{'small'} x 1024;
5db2758d 259
d7f20fdf 260 my $maxloblen = length $binstr{'large'};
261 note "Localizing LongReadLen to $maxloblen to avoid truncation of test data";
262 local $dbh->{'LongReadLen'} = $maxloblen;
5db2758d 263
d7f20fdf 264 my $rs = $schema->resultset('BindType');
265 my $id = 0;
5db2758d 266
931e5d43 267 if ($DBD::Oracle::VERSION eq '1.23') {
268 throws_ok { $rs->create({ id => 1, blob => $binstr{large} }) }
269 qr/broken/,
270 'throws on blob insert with DBD::Oracle == 1.23';
271
272 skip 'buggy BLOB support in DBD::Oracle 1.23', 7;
273 }
274
d7f20fdf 275 foreach my $type (qw( blob clob )) {
276 foreach my $size (qw( small large )) {
277 $id++;
5db2758d 278
d7f20fdf 279 lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
280 "inserted $size $type without dying";
281
282 ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
283 }
284 }
5db2758d 285}
286
86cc4156 287done_testing;
288
0567538f 289# clean up our mess
3ff5b740 290END {
fe0d48d3 291 if($schema && ($dbh = $schema->storage->dbh)) {
3ff5b740 292 $dbh->do("DROP SEQUENCE artist_seq");
e6dd7b42 293 $dbh->do("DROP SEQUENCE cd_seq");
39b8d119 294 $dbh->do("DROP SEQUENCE pkid1_seq");
295 $dbh->do("DROP SEQUENCE pkid2_seq");
296 $dbh->do("DROP SEQUENCE nonpkid_seq");
3ff5b740 297 $dbh->do("DROP TABLE artist");
39b8d119 298 $dbh->do("DROP TABLE sequence_test");
3ff5b740 299 $dbh->do("DROP TABLE track");
b7b18f32 300 $dbh->do("DROP TABLE cd");
5db2758d 301 $dbh->do("DROP TABLE bindtype_test");
3ff5b740 302 }
303}
0567538f 304