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