Commit | Line | Data |
c1cac633 |
1 | use strict; |
b9a2c3a5 |
2 | use warnings; |
c1cac633 |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
b2d16f1f |
7 | use DBIC::SqlMakerTest; |
c1cac633 |
8 | |
9 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
10 | |
11 | plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ODBC_DSN}, _USER and _PASS to run this test' |
12 | unless ($dsn && $user); |
13 | |
b2d16f1f |
14 | plan tests => 23; |
c1cac633 |
15 | |
42e5b103 |
16 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
c1cac633 |
17 | |
8c0104fe |
18 | { |
19 | no warnings 'redefine'; |
20 | my $connect_count = 0; |
21 | my $orig_connect = \&DBI::connect; |
22 | local *DBI::connect = sub { $connect_count++; goto &$orig_connect }; |
23 | |
24 | $schema->storage->ensure_connected; |
25 | |
26 | is( $connect_count, 1, 'only one connection made'); |
27 | } |
9b3e916d |
28 | |
c1cac633 |
29 | isa_ok( $schema->storage, 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' ); |
30 | |
c5f77f6c |
31 | $schema->storage->dbh_do (sub { |
32 | my ($storage, $dbh) = @_; |
33 | eval { $dbh->do("DROP TABLE artist") }; |
34 | $dbh->do(<<'SQL'); |
c1cac633 |
35 | |
c1cac633 |
36 | CREATE TABLE artist ( |
37 | artistid INT IDENTITY NOT NULL, |
a0dd8679 |
38 | name VARCHAR(100), |
39da2a2b |
39 | rank INT NOT NULL DEFAULT '13', |
2eebd801 |
40 | charfield CHAR(10) NULL, |
c1cac633 |
41 | primary key(artistid) |
42 | ) |
43 | |
c5f77f6c |
44 | SQL |
45 | |
46 | }); |
47 | |
c1cac633 |
48 | my %seen_id; |
49 | |
2eebd801 |
50 | # fresh $schema so we start unconnected |
c6fc5645 |
51 | $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
2eebd801 |
52 | |
c1cac633 |
53 | # test primary key handling |
54 | my $new = $schema->resultset('Artist')->create({ name => 'foo' }); |
55 | ok($new->artistid > 0, "Auto-PK worked"); |
56 | |
57 | $seen_id{$new->artistid}++; |
58 | |
59 | # test LIMIT support |
60 | for (1..6) { |
61 | $new = $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); |
62 | is ( $seen_id{$new->artistid}, undef, "id for Artist $_ is unique" ); |
63 | $seen_id{$new->artistid}++; |
64 | } |
65 | |
66 | my $it = $schema->resultset('Artist')->search( {}, { |
67 | rows => 3, |
68 | order_by => 'artistid', |
69 | }); |
70 | |
71 | is( $it->count, 3, "LIMIT count ok" ); |
72 | is( $it->next->name, "foo", "iterator->next ok" ); |
73 | $it->next; |
74 | is( $it->next->name, "Artist 2", "iterator->next ok" ); |
75 | is( $it->next, undef, "next past end of resultset ok" ); |
76 | |
b9a2c3a5 |
77 | $schema->storage->dbh_do (sub { |
78 | my ($storage, $dbh) = @_; |
79 | eval { $dbh->do("DROP TABLE Owners") }; |
80 | eval { $dbh->do("DROP TABLE Books") }; |
81 | $dbh->do(<<'SQL'); |
82 | |
83 | |
84 | CREATE TABLE Books ( |
85 | id INT IDENTITY (1, 1) NOT NULL, |
86 | source VARCHAR(100), |
87 | owner INT, |
88 | title VARCHAR(10), |
89 | price INT NULL |
90 | ) |
91 | |
92 | CREATE TABLE Owners ( |
93 | id INT IDENTITY (1, 1) NOT NULL, |
42e5b103 |
94 | name VARCHAR(100), |
b9a2c3a5 |
95 | ) |
96 | |
b9a2c3a5 |
97 | SQL |
98 | |
99 | }); |
100 | $schema->populate ('Owners', [ |
134a3bb9 |
101 | [qw/id name /], |
b9a2c3a5 |
102 | [qw/1 wiggle/], |
103 | [qw/2 woggle/], |
104 | [qw/3 boggle/], |
fc85215b |
105 | [qw/4 fREW/], |
106 | [qw/5 fRIOUX/], |
107 | [qw/6 fROOH/], |
108 | [qw/7 fRUE/], |
109 | [qw/8 fISMBoC/], |
110 | [qw/9 station/], |
111 | [qw/10 mirror/], |
112 | [qw/11 dimly/], |
113 | [qw/12 face_to_face/], |
114 | [qw/13 icarus/], |
115 | [qw/14 dream/], |
116 | [qw/15 dyrstyggyr/], |
b9a2c3a5 |
117 | ]); |
118 | |
119 | $schema->populate ('BooksInLibrary', [ |
120 | [qw/source owner title /], |
56d2561e |
121 | [qw/Library 1 secrets0/], |
b9a2c3a5 |
122 | [qw/Library 1 secrets1/], |
123 | [qw/Eatery 1 secrets2/], |
124 | [qw/Library 2 secrets3/], |
fc85215b |
125 | [qw/Library 3 secrets4/], |
126 | [qw/Eatery 3 secrets5/], |
127 | [qw/Library 4 secrets6/], |
128 | [qw/Library 5 secrets7/], |
129 | [qw/Eatery 5 secrets8/], |
130 | [qw/Library 6 secrets9/], |
131 | [qw/Library 7 secrets10/], |
132 | [qw/Eatery 7 secrets11/], |
133 | [qw/Library 8 secrets12/], |
b9a2c3a5 |
134 | ]); |
135 | |
136 | # |
137 | # try a distinct + prefetch on tables with identically named columns |
138 | # |
139 | |
140 | { |
42e5b103 |
141 | # try a ->has_many direction (group_by is not possible on has_many with limit) |
fc85215b |
142 | my $owners = $schema->resultset ('Owners')->search ({ |
143 | 'books.id' => { '!=', undef } |
144 | }, { |
145 | prefetch => 'books', |
ff1ef65b |
146 | order_by => 'name', |
56d2561e |
147 | rows => 3, # 8 results total |
fc85215b |
148 | }); |
149 | |
56d2561e |
150 | is ($owners->page(1)->all, 3, 'has_many prefetch returns correct number of rows'); |
151 | is ($owners->page(1)->count, 3, 'has-many prefetch returns correct count'); |
152 | |
618a0fe3 |
153 | TODO: { |
154 | local $TODO = 'limit past end of resultset problem'; |
56d2561e |
155 | is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows'); |
156 | is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count'); |
618a0fe3 |
157 | } |
b9a2c3a5 |
158 | |
b2d16f1f |
159 | # make sure count does not become overly complex FIXME |
160 | is_same_sql_bind ( |
161 | $owners->page(2)->count_rs->as_query, |
162 | '( |
163 | SELECT COUNT( * ) |
164 | FROM ( |
165 | SELECT TOP 3 id |
166 | FROM ( |
167 | SELECT TOP 6 me.id |
168 | FROM owners me |
169 | LEFT JOIN books books ON books.owner = me.id |
170 | WHERE ( books.id IS NOT NULL ) |
171 | GROUP BY me.id |
172 | ORDER BY me.id ASC |
173 | ) AS inner_sel |
174 | ORDER BY id DESC |
175 | ) count_subq |
176 | )', |
177 | [], |
178 | ); |
179 | |
42e5b103 |
180 | # try a ->belongs_to direction (no select collapse, group_by should work) |
fc85215b |
181 | my $books = $schema->resultset ('BooksInLibrary')->search ({ |
56d2561e |
182 | 'owner.name' => [qw/wiggle woggle/], |
fc85215b |
183 | }, { |
fc85215b |
184 | distinct => 1, |
42e5b103 |
185 | prefetch => 'owner', |
ff1ef65b |
186 | order_by => 'name', |
56d2561e |
187 | rows => 2, # 3 results total |
fc85215b |
188 | }); |
189 | |
b1e1d073 |
190 | |
56d2561e |
191 | is ($books->page(1)->all, 2, 'Prefetched grouped search returns correct number of rows'); |
192 | is ($books->page(1)->count, 2, 'Prefetched grouped search returns correct count'); |
b1e1d073 |
193 | |
618a0fe3 |
194 | TODO: { |
195 | local $TODO = 'limit past end of resultset problem'; |
56d2561e |
196 | is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows'); |
197 | is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count'); |
618a0fe3 |
198 | } |
b2d16f1f |
199 | |
200 | # make sure count does not become overly complex FIXME |
201 | is_same_sql_bind ( |
202 | $books->page(2)->count_rs->as_query, |
203 | '( |
204 | SELECT COUNT( * ) |
205 | FROM ( |
206 | SELECT TOP 2 id |
207 | FROM ( |
208 | SELECT TOP 4 me.id |
209 | FROM books me |
210 | JOIN owners owner ON owner.id = me.owner |
211 | WHERE ( ( ( owner.name = ? OR owner.name = ? ) AND source = ? ) ) |
212 | GROUP BY me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name |
213 | ORDER BY me.id ASC |
214 | ) AS inner_sel |
215 | ORDER BY id DESC |
216 | ) count_subq |
217 | )', |
218 | [ |
219 | [ 'owner.name' => 'wiggle' ], |
220 | [ 'owner.name' => 'woggle' ], |
221 | [ 'source' => 'Library' ], |
222 | ], |
223 | ); |
224 | |
b9a2c3a5 |
225 | } |
c1cac633 |
226 | |
227 | # clean up our mess |
228 | END { |
c5f77f6c |
229 | my $dbh = eval { $schema->storage->_dbh }; |
c1cac633 |
230 | $dbh->do('DROP TABLE artist') if $dbh; |
231 | } |
fc85215b |
232 | # vim:sw=2 sts=2 |