Commit | Line | Data |
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 |
28 | use strict; |
29 | use warnings; |
30 | |
5db2758d |
31 | use Test::Exception; |
70350518 |
32 | use Test::More; |
33 | use lib qw(t/lib); |
34 | use DBICTest; |
d2a3958e |
35 | use DateTime; |
0567538f |
36 | |
37 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; |
38 | |
70350518 |
39 | plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' . |
39b8d119 |
40 | 'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''. |
41 | ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\'' |
0567538f |
42 | unless ($dsn && $user && $pass); |
43 | |
9900b569 |
44 | plan tests => 35; |
0567538f |
45 | |
cb464582 |
46 | DBICTest::Schema->load_classes('ArtistFQN'); |
9900b569 |
47 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
0567538f |
48 | |
3ff5b740 |
49 | my $dbh = $schema->storage->dbh; |
0567538f |
50 | |
51 | eval { |
52 | $dbh->do("DROP SEQUENCE artist_seq"); |
39b8d119 |
53 | $dbh->do("DROP SEQUENCE pkid1_seq"); |
54 | $dbh->do("DROP SEQUENCE pkid2_seq"); |
55 | $dbh->do("DROP SEQUENCE nonpkid_seq"); |
0567538f |
56 | $dbh->do("DROP TABLE artist"); |
39b8d119 |
57 | $dbh->do("DROP TABLE sequence_test"); |
2660b14e |
58 | $dbh->do("DROP TABLE cd"); |
59 | $dbh->do("DROP TABLE track"); |
0567538f |
60 | }; |
61 | $dbh->do("CREATE SEQUENCE artist_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); |
39b8d119 |
62 | $dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0"); |
63 | $dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0"); |
64 | $dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0"); |
7c4ead2d |
65 | $dbh->do("CREATE TABLE artist (artistid NUMBER(12), name VARCHAR(255), rank NUMBER(38), charfield VARCHAR2(10))"); |
39b8d119 |
66 | $dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))"); |
2660b14e |
67 | $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4))"); |
5cde8dbf |
68 | $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12), position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE)"); |
2660b14e |
69 | |
0567538f |
70 | $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))"); |
39b8d119 |
71 | $dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))"); |
0567538f |
72 | $dbh->do(qq{ |
73 | CREATE OR REPLACE TRIGGER artist_insert_trg |
74 | BEFORE INSERT ON artist |
75 | FOR EACH ROW |
76 | BEGIN |
77 | IF :new.artistid IS NULL THEN |
78 | SELECT artist_seq.nextval |
79 | INTO :new.artistid |
80 | FROM DUAL; |
81 | END IF; |
82 | END; |
83 | }); |
84 | |
5db2758d |
85 | { |
86 | # Swiped from t/bindtype_columns.t to avoid creating my own Resultset. |
87 | |
88 | local $SIG{__WARN__} = sub {}; |
89 | eval { $dbh->do('DROP TABLE bindtype_test') }; |
90 | |
91 | $dbh->do(qq[ |
92 | CREATE TABLE bindtype_test |
93 | ( |
94 | id integer NOT NULL PRIMARY KEY, |
95 | bytea integer NULL, |
96 | blob blob NULL, |
97 | clob clob NULL |
98 | ) |
99 | ],{ RaiseError => 1, PrintError => 1 }); |
100 | } |
101 | |
3ff5b740 |
102 | # This is in Core now, but it's here just to test that it doesn't break |
103 | $schema->class('Artist')->load_components('PK::Auto'); |
104 | # These are compat shims for PK::Auto... |
105 | $schema->class('CD')->load_components('PK::Auto::Oracle'); |
106 | $schema->class('Track')->load_components('PK::Auto::Oracle'); |
0567538f |
107 | |
108 | # test primary key handling |
3ff5b740 |
109 | my $new = $schema->resultset('Artist')->create({ name => 'foo' }); |
c8f4b52b |
110 | is($new->artistid, 1, "Oracle Auto-PK worked"); |
0567538f |
111 | |
cb464582 |
112 | # test again with fully-qualified table name |
113 | $new = $schema->resultset('ArtistFQN')->create( { name => 'bar' } ); |
114 | is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" ); |
115 | |
9900b569 |
116 | # test join with row count ambiguity |
d2a3958e |
117 | |
3ff5b740 |
118 | my $cd = $schema->resultset('CD')->create({ cdid => 1, artist => 1, title => 'EP C', year => '2003' }); |
d2a3958e |
119 | my $track = $schema->resultset('Track')->create({ trackid => 1, cd => 1, |
9900b569 |
120 | position => 1, title => 'Track1' }); |
3ff5b740 |
121 | my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'}, |
2660b14e |
122 | { join => 'cd', |
123 | rows => 2 } |
124 | ); |
125 | |
d2a3958e |
126 | ok(my $row = $tjoin->next); |
127 | |
128 | is($row->title, 'Track1', "ambiguous column ok"); |
2660b14e |
129 | |
286f32b3 |
130 | # check count distinct with multiple columns |
3ff5b740 |
131 | my $other_track = $schema->resultset('Track')->create({ trackid => 2, cd => 1, position => 1, title => 'Track2' }); |
11d68671 |
132 | |
3ff5b740 |
133 | my $tcount = $schema->resultset('Track')->search( |
11d68671 |
134 | {}, |
135 | { |
136 | select => [ qw/position title/ ], |
137 | distinct => 1, |
138 | } |
139 | ); |
f12f0d97 |
140 | is($tcount->count, 2, 'multiple column COUNT DISTINCT ok'); |
11d68671 |
141 | |
142 | $tcount = $schema->resultset('Track')->search( |
143 | {}, |
144 | { |
145 | columns => [ qw/position title/ ], |
146 | distinct => 1, |
147 | } |
148 | ); |
f12f0d97 |
149 | is($tcount->count, 2, 'multiple column COUNT DISTINCT ok'); |
286f32b3 |
150 | |
11d68671 |
151 | $tcount = $schema->resultset('Track')->search( |
152 | {}, |
153 | { |
154 | group_by => [ qw/position title/ ] |
155 | } |
156 | ); |
f12f0d97 |
157 | is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok'); |
2660b14e |
158 | |
0567538f |
159 | # test LIMIT support |
160 | for (1..6) { |
3ff5b740 |
161 | $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); |
0567538f |
162 | } |
3ff5b740 |
163 | my $it = $schema->resultset('Artist')->search( {}, |
0567538f |
164 | { rows => 3, |
cb464582 |
165 | offset => 3, |
0567538f |
166 | order_by => 'artistid' } |
167 | ); |
168 | is( $it->count, 3, "LIMIT count ok" ); |
169 | is( $it->next->name, "Artist 2", "iterator->next ok" ); |
170 | $it->next; |
171 | $it->next; |
172 | is( $it->next, undef, "next past end of resultset ok" ); |
173 | |
e8e971f2 |
174 | { |
175 | my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 }); |
176 | my @results = $rs->all; |
177 | is( scalar @results, 1, "Group by with limit OK" ); |
178 | } |
179 | |
ccd6f984 |
180 | # test auto increment using sequences WITHOUT triggers |
39b8d119 |
181 | for (1..5) { |
182 | my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' }); |
183 | is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key"); |
184 | is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key"); |
185 | is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key"); |
186 | } |
187 | my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 }); |
188 | is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually"); |
ccd6f984 |
189 | |
5db2758d |
190 | { |
191 | my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) ); |
192 | $binstr{'large'} = $binstr{'small'} x 1024; |
193 | |
194 | my $maxloblen = length $binstr{'large'}; |
195 | note "Localizing LongReadLen to $maxloblen to avoid truncation of test data"; |
196 | local $dbh->{'LongReadLen'} = $maxloblen; |
197 | |
198 | my $rs = $schema->resultset('BindType'); |
199 | my $id = 0; |
200 | |
201 | foreach my $type (qw( blob clob )) { |
202 | foreach my $size (qw( small large )) { |
203 | $id++; |
204 | |
0821ae59 |
205 | lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) } |
5db2758d |
206 | "inserted $size $type without dying"; |
0821ae59 |
207 | ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" ); |
5db2758d |
208 | } |
209 | } |
210 | } |
211 | |
0567538f |
212 | # clean up our mess |
3ff5b740 |
213 | END { |
fe0d48d3 |
214 | if($schema && ($dbh = $schema->storage->dbh)) { |
3ff5b740 |
215 | $dbh->do("DROP SEQUENCE artist_seq"); |
39b8d119 |
216 | $dbh->do("DROP SEQUENCE pkid1_seq"); |
217 | $dbh->do("DROP SEQUENCE pkid2_seq"); |
218 | $dbh->do("DROP SEQUENCE nonpkid_seq"); |
3ff5b740 |
219 | $dbh->do("DROP TABLE artist"); |
39b8d119 |
220 | $dbh->do("DROP TABLE sequence_test"); |
3ff5b740 |
221 | $dbh->do("DROP TABLE cd"); |
222 | $dbh->do("DROP TABLE track"); |
5db2758d |
223 | $dbh->do("DROP TABLE bindtype_test"); |
3ff5b740 |
224 | } |
225 | } |
0567538f |
226 | |