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