Commit | Line | Data |
c1cac633 |
1 | use strict; |
b9a2c3a5 |
2 | use warnings; |
c1cac633 |
3 | |
4 | use Test::More; |
893403c8 |
5 | use Test::Exception; |
199fbc45 |
6 | use Try::Tiny; |
5e724964 |
7 | |
199fbc45 |
8 | use DBIx::Class::Optional::Dependencies (); |
5e724964 |
9 | plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('test_rdbms_mssql_odbc') |
10 | unless DBIx::Class::Optional::Dependencies->req_ok_for ('test_rdbms_mssql_odbc'); |
11 | |
c1cac633 |
12 | use lib qw(t/lib); |
13 | use DBICTest; |
b2d16f1f |
14 | use DBIC::SqlMakerTest; |
5e724964 |
15 | use DBIx::Class::SQLMaker::LimitDialects; |
199fbc45 |
16 | |
fcb7fcbb |
17 | my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype; |
18 | my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype; |
19 | |
c1cac633 |
20 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
21 | |
22 | plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ODBC_DSN}, _USER and _PASS to run this test' |
23 | unless ($dsn && $user); |
24 | |
77c7628c |
25 | { |
26 | my $srv_ver = DBICTest::Schema->connect($dsn, $user, $pass)->storage->_server_info->{dbms_version}; |
27 | ok ($srv_ver, 'Got a test server version on fresh schema: ' . ($srv_ver||'???') ); |
28 | } |
29 | |
ca791b95 |
30 | DBICTest::Schema->load_classes('ArtistGUID'); |
42e5b103 |
31 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
c1cac633 |
32 | |
8c0104fe |
33 | { |
34 | no warnings 'redefine'; |
35 | my $connect_count = 0; |
36 | my $orig_connect = \&DBI::connect; |
37 | local *DBI::connect = sub { $connect_count++; goto &$orig_connect }; |
38 | |
39 | $schema->storage->ensure_connected; |
40 | |
41 | is( $connect_count, 1, 'only one connection made'); |
42 | } |
9b3e916d |
43 | |
c1cac633 |
44 | isa_ok( $schema->storage, 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' ); |
45 | |
cf89555e |
46 | { |
a9ee4be9 |
47 | my $schema2 = $schema->connect (@{$schema->storage->connect_info}); |
cf89555e |
48 | ok (! $schema2->storage->connected, 'a re-connected cloned schema starts unconnected'); |
49 | } |
ecdf1ac8 |
50 | $schema->storage->_dbh->disconnect; |
51 | |
52 | lives_ok { |
53 | $schema->storage->dbh_do(sub { $_[1]->do('select 1') }) |
54 | } '_ping works'; |
55 | |
384b8bce |
56 | my %opts = ( |
57 | use_mars => |
9ffaf8a3 |
58 | { opts => { on_connect_call => 'use_mars' } }, |
384b8bce |
59 | use_dynamic_cursors => |
25d3127d |
60 | { opts => { on_connect_call => 'use_dynamic_cursors' }, |
aca3b4c3 |
61 | required => $schema->storage->_using_freetds ? 0 : 1, |
25d3127d |
62 | }, |
384b8bce |
63 | use_server_cursors => |
9ffaf8a3 |
64 | { opts => { on_connect_call => 'use_server_cursors' } }, |
94f9fbef |
65 | plain => |
9ffaf8a3 |
66 | { opts => {}, required => 1 }, |
384b8bce |
67 | ); |
68 | |
69 | for my $opts_name (keys %opts) { |
70 | SKIP: { |
9ffaf8a3 |
71 | my $opts = $opts{$opts_name}{opts}; |
384b8bce |
72 | $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts); |
73 | |
74 | try { |
75 | $schema->storage->ensure_connected |
76 | } |
77 | catch { |
9ffaf8a3 |
78 | if ($opts{$opts_name}{required}) { |
e81b50f4 |
79 | die "on_connect_call option '$opts_name' is not functional: $_"; |
9ffaf8a3 |
80 | } |
81 | else { |
82 | skip |
94f9fbef |
83 | "on_connect_call option '$opts_name' not functional in this configuration: $_", |
84 | 1 |
85 | ; |
9ffaf8a3 |
86 | } |
384b8bce |
87 | }; |
88 | |
e56b1c2d |
89 | $schema->storage->dbh_do (sub { |
90 | my ($storage, $dbh) = @_; |
91 | eval { $dbh->do("DROP TABLE artist") }; |
92 | $dbh->do(<<'SQL'); |
c1cac633 |
93 | CREATE TABLE artist ( |
94 | artistid INT IDENTITY NOT NULL, |
a0dd8679 |
95 | name VARCHAR(100), |
39da2a2b |
96 | rank INT NOT NULL DEFAULT '13', |
2eebd801 |
97 | charfield CHAR(10) NULL, |
c1cac633 |
98 | primary key(artistid) |
99 | ) |
c5f77f6c |
100 | SQL |
e56b1c2d |
101 | }); |
c5f77f6c |
102 | |
384b8bce |
103 | # test Auto-PK |
104 | $schema->resultset('Artist')->search({ name => 'foo' })->delete; |
c1cac633 |
105 | |
384b8bce |
106 | my $new = $schema->resultset('Artist')->create({ name => 'foo' }); |
7b1b2582 |
107 | |
384b8bce |
108 | ok(($new->artistid||0) > 0, "Auto-PK worked for $opts_name"); |
7b1b2582 |
109 | |
384b8bce |
110 | # Test multiple active statements |
111 | SKIP: { |
112 | skip 'not a multiple active statements configuration', 1 |
113 | if $opts_name eq 'plain'; |
41dd5d30 |
114 | |
94f9fbef |
115 | $schema->storage->ensure_connected; |
41dd5d30 |
116 | |
94f9fbef |
117 | lives_ok { |
c1cac633 |
118 | |
94f9fbef |
119 | no warnings 'redefine'; |
120 | local *DBI::connect = sub { die "NO RECONNECTS!!!" }; |
ca791b95 |
121 | |
94f9fbef |
122 | my $artist_rs = $schema->resultset('Artist'); |
384b8bce |
123 | |
94f9fbef |
124 | $artist_rs->delete; |
384b8bce |
125 | |
94f9fbef |
126 | $artist_rs->create({ name => "Artist$_" }) for (1..3); |
127 | |
128 | my $forward = $artist_rs->search({}, |
129 | { order_by => { -asc => 'artistid' } }); |
130 | my $backward = $artist_rs->search({}, |
131 | { order_by => { -desc => 'artistid' } }); |
132 | |
133 | my @map = ( |
134 | [qw/Artist1 Artist3/], [qw/Artist2 Artist2/], [qw/Artist3 Artist1/] |
135 | ); |
136 | my @result; |
137 | |
138 | while (my $forward_row = $forward->next) { |
139 | my $backward_row = $backward->next; |
140 | push @result, [$forward_row->name, $backward_row->name]; |
141 | } |
384b8bce |
142 | |
94f9fbef |
143 | is_deeply \@result, \@map, "multiple active statements in $opts_name"; |
384b8bce |
144 | |
94f9fbef |
145 | $artist_rs->delete; |
25d3127d |
146 | |
94f9fbef |
147 | is($artist_rs->count, 0, '$dbh still viable'); |
148 | } "Multiple active statements survive $opts_name"; |
384b8bce |
149 | } |
ca791b95 |
150 | |
a54bd479 |
151 | # Test populate |
f6b185e1 |
152 | |
e56b1c2d |
153 | { |
154 | $schema->storage->dbh_do (sub { |
155 | my ($storage, $dbh) = @_; |
156 | eval { $dbh->do("DROP TABLE owners") }; |
157 | eval { $dbh->do("DROP TABLE books") }; |
158 | $dbh->do(<<'SQL'); |
02495deb |
159 | CREATE TABLE books ( |
b9a2c3a5 |
160 | id INT IDENTITY (1, 1) NOT NULL, |
161 | source VARCHAR(100), |
162 | owner INT, |
163 | title VARCHAR(10), |
164 | price INT NULL |
165 | ) |
166 | |
02495deb |
167 | CREATE TABLE owners ( |
b9a2c3a5 |
168 | id INT IDENTITY (1, 1) NOT NULL, |
42e5b103 |
169 | name VARCHAR(100), |
b9a2c3a5 |
170 | ) |
b9a2c3a5 |
171 | SQL |
a54bd479 |
172 | }); |
173 | |
e56b1c2d |
174 | lives_ok ( sub { |
175 | # start a new connection, make sure rebless works |
176 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts); |
177 | $schema->populate ('Owners', [ |
178 | [qw/id name /], |
179 | [qw/1 wiggle/], |
180 | [qw/2 woggle/], |
181 | [qw/3 boggle/], |
182 | [qw/4 fRIOUX/], |
183 | [qw/5 fRUE/], |
184 | [qw/6 fREW/], |
185 | [qw/7 fROOH/], |
186 | [qw/8 fISMBoC/], |
187 | [qw/9 station/], |
188 | [qw/10 mirror/], |
189 | [qw/11 dimly/], |
190 | [qw/12 face_to_face/], |
191 | [qw/13 icarus/], |
192 | [qw/14 dream/], |
193 | [qw/15 dyrstyggyr/], |
194 | ]); |
195 | }, 'populate with PKs supplied ok' ); |
196 | |
197 | |
198 | lives_ok (sub { |
199 | # start a new connection, make sure rebless works |
200 | # test an insert with a supplied identity, followed by one without |
201 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts); |
202 | for (2, 1) { |
203 | my $id = $_ * 20 ; |
204 | $schema->resultset ('Owners')->create ({ id => $id, name => "troglodoogle $id" }); |
205 | $schema->resultset ('Owners')->create ({ name => "troglodoogle " . ($id + 1) }); |
206 | } |
207 | }, 'create with/without PKs ok' ); |
208 | |
209 | is ($schema->resultset ('Owners')->count, 19, 'owner rows really in db' ); |
210 | |
211 | lives_ok ( sub { |
212 | # start a new connection, make sure rebless works |
213 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts); |
214 | $schema->populate ('BooksInLibrary', [ |
215 | [qw/source owner title /], |
216 | [qw/Library 1 secrets0/], |
217 | [qw/Library 1 secrets1/], |
218 | [qw/Eatery 1 secrets2/], |
219 | [qw/Library 2 secrets3/], |
220 | [qw/Library 3 secrets4/], |
221 | [qw/Eatery 3 secrets5/], |
222 | [qw/Library 4 secrets6/], |
223 | [qw/Library 5 secrets7/], |
224 | [qw/Eatery 5 secrets8/], |
225 | [qw/Library 6 secrets9/], |
226 | [qw/Library 7 secrets10/], |
227 | [qw/Eatery 7 secrets11/], |
228 | [qw/Library 8 secrets12/], |
229 | ]); |
230 | }, 'populate without PKs supplied ok' ); |
a54bd479 |
231 | } |
fb7cd45f |
232 | |
e56b1c2d |
233 | # test simple, complex LIMIT and limited prefetch support, with both dialects and quote combinations (if possible) |
234 | for my $dialect ( |
235 | 'Top', |
236 | ($schema->storage->_server_info->{normalized_dbms_version} || 0 ) >= 9 |
237 | ? ('RowNumberOver') |
238 | : () |
239 | , |
240 | ) { |
241 | for my $quoted (0, 1) { |
242 | |
243 | $schema = DBICTest::Schema->connect($dsn, $user, $pass, { |
244 | limit_dialect => $dialect, |
245 | %$opts, |
246 | $quoted |
247 | ? ( quote_char => [ qw/ [ ] / ], name_sep => '.' ) |
248 | : () |
249 | , |
250 | }); |
251 | |
252 | my $test_type = "Dialect:$dialect Quoted:$quoted"; |
253 | |
254 | # basic limit support |
4ca1fd6f |
255 | { |
e56b1c2d |
256 | my $art_rs = $schema->resultset ('Artist'); |
257 | $art_rs->delete; |
258 | $art_rs->create({ name => 'Artist ' . $_ }) for (1..6); |
259 | |
260 | my $it = $schema->resultset('Artist')->search( {}, { |
261 | rows => 4, |
262 | offset => 3, |
263 | order_by => 'artistid', |
264 | }); |
265 | |
266 | is( $it->count, 3, "$test_type: LIMIT count ok" ); |
267 | |
268 | local $TODO = "Top-limit does not work when your limit ends up past the resultset" |
269 | if $dialect eq 'Top'; |
270 | |
271 | is( $it->next->name, 'Artist 4', "$test_type: iterator->next ok" ); |
272 | $it->next; |
273 | is( $it->next->name, 'Artist 6', "$test_type: iterator->next ok" ); |
274 | is( $it->next, undef, "$test_type: next past end of resultset ok" ); |
275 | } |
f0bd60fc |
276 | |
e56b1c2d |
277 | # plain ordered subqueries throw |
278 | throws_ok (sub { |
279 | $schema->resultset('Owners')->search ({}, { order_by => 'name' })->as_query |
280 | }, qr/ordered subselect encountered/, "$test_type: Ordered Subselect detection throws ok"); |
a54bd479 |
281 | |
e56b1c2d |
282 | # make sure ordered subselects *somewhat* work |
283 | { |
284 | my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 }); |
285 | my $sealed_owners = $owners->as_subselect_rs; |
286 | |
287 | is_deeply ( |
288 | [ map { $_->name } ($sealed_owners->all) ], |
289 | [ map { $_->name } ($owners->all) ], |
290 | "$test_type: Sort preserved from within a subquery", |
291 | ); |
292 | } |
a54bd479 |
293 | |
e56b1c2d |
294 | # still even with lost order of IN, we should be getting correct |
295 | # sets |
296 | { |
297 | my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 }); |
298 | my $corelated_owners = $owners->result_source->resultset->search ( |
299 | { |
300 | id => { -in => $owners->get_column('id')->as_query }, |
301 | }, |
302 | { |
303 | order_by => 'name' #reorder because of what is shown above |
304 | }, |
305 | ); |
306 | |
307 | is ( |
308 | join ("\x00", map { $_->name } ($corelated_owners->all) ), |
309 | join ("\x00", map { $_->name } ($owners->all) ), |
310 | "$test_type: With an outer order_by, everything still matches", |
311 | ); |
312 | } |
a54bd479 |
313 | |
e56b1c2d |
314 | # make sure right-join-side single-prefetch ordering limit works |
315 | { |
316 | my $rs = $schema->resultset ('BooksInLibrary')->search ( |
317 | { |
318 | 'owner.name' => { '!=', 'woggle' }, |
319 | }, |
320 | { |
321 | prefetch => 'owner', |
322 | order_by => 'owner.name', |
323 | } |
324 | ); |
325 | # this is the order in which they should come from the above query |
326 | my @owner_names = qw/boggle fISMBoC fREW fRIOUX fROOH fRUE wiggle wiggle/; |
327 | |
328 | is ($rs->all, 8, "$test_type: Correct amount of objects from right-sorted joined resultset"); |
329 | is_deeply ( |
330 | [map { $_->owner->name } ($rs->all) ], |
331 | \@owner_names, |
332 | "$test_type: Prefetched rows were properly ordered" |
333 | ); |
334 | |
335 | my $limited_rs = $rs->search ({}, {rows => 6, offset => 2, unsafe_subselect_ok => 1}); |
336 | is ($limited_rs->count, 6, "$test_type: Correct count of limited right-sorted joined resultset"); |
337 | is ($limited_rs->count_rs->next, 6, "$test_type: Correct count_rs of limited right-sorted joined resultset"); |
338 | |
339 | my $queries; |
340 | my $orig_debug = $schema->storage->debug; |
341 | $schema->storage->debugcb(sub { $queries++; }); |
342 | $schema->storage->debug(1); |
343 | |
344 | is_deeply ( |
345 | [map { $_->owner->name } ($limited_rs->all) ], |
346 | [@owner_names[2 .. 7]], |
347 | "$test_type: Prefetch-limited rows were properly ordered" |
348 | ); |
349 | is ($queries, 1, "$test_type: Only one query with prefetch"); |
350 | |
351 | $schema->storage->debugcb(undef); |
352 | $schema->storage->debug($orig_debug); |
353 | |
354 | is_deeply ( |
355 | [map { $_->name } ($limited_rs->search_related ('owner')->all) ], |
356 | [@owner_names[2 .. 7]], |
357 | "$test_type: Rows are still properly ordered after search_related", |
358 | ); |
359 | } |
a54bd479 |
360 | |
e56b1c2d |
361 | # try a ->has_many direction with duplicates |
362 | my $owners = $schema->resultset ('Owners')->search ( |
363 | { |
364 | 'books.id' => { '!=', undef }, |
365 | 'me.name' => { '!=', 'somebogusstring' }, |
366 | }, |
367 | { |
368 | prefetch => 'books', |
86bb5a27 |
369 | order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation |
e56b1c2d |
370 | group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by |
371 | rows => 3, # 8 results total |
372 | unsafe_subselect_ok => 1, |
373 | }, |
374 | ); |
a54bd479 |
375 | |
fd1d12a9 |
376 | is_same_sql_bind ( |
377 | $owners->page(3)->as_query, |
378 | $dialect eq 'Top' |
379 | ? '( |
380 | SELECT TOP 2147483647 [me].[id], [me].[name], |
381 | [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] |
382 | FROM ( |
383 | SELECT TOP 2147483647 [me].[id], [me].[name] |
384 | FROM ( |
385 | SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001] |
386 | FROM ( |
387 | SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001] |
388 | FROM [owners] [me] |
389 | LEFT JOIN [books] [books] |
390 | ON [books].[owner] = [me].[id] |
391 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
392 | GROUP BY [me].[id], [me].[name] |
393 | ORDER BY name + ? ASC, [me].[id] |
394 | ) [me] |
395 | ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC |
396 | ) [me] |
397 | ORDER BY [ORDER__BY__001] ASC, [me].[id] |
398 | ) [me] |
399 | LEFT JOIN [books] [books] |
400 | ON [books].[owner] = [me].[id] |
401 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
402 | ORDER BY name + ? ASC, [me].[id] |
403 | )' |
404 | : '( |
405 | SELECT TOP 2147483647 [me].[id], [me].[name], |
406 | [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] |
407 | FROM ( |
408 | SELECT TOP 2147483647 [me].[id], [me].[name] |
409 | FROM ( |
410 | SELECT [me].[id], [me].[name], |
411 | ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index] |
412 | FROM ( |
413 | SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001] |
414 | FROM [owners] [me] |
415 | LEFT JOIN [books] [books] |
416 | ON [books].[owner] = [me].[id] |
417 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
418 | GROUP BY [me].[id], [me].[name] |
419 | ) [me] |
420 | ) [me] |
421 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
422 | ) [me] |
423 | LEFT JOIN [books] [books] |
424 | ON [books].[owner] = [me].[id] |
425 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
426 | ORDER BY name + ? ASC, [me].[id] |
427 | )' |
428 | , |
e56b1c2d |
429 | [ |
fcb7fcbb |
430 | [ { dbic_colname => 'test' } |
431 | => 'xxx' ], |
ebc5c60a |
432 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
433 | => 'somebogusstring' ], |
434 | |
435 | ($dialect eq 'Top' |
436 | ? [ { dbic_colname => 'test' } => 'xxx' ] # the extra re-order bind |
437 | : ([ $OFFSET => 7 ], [ $TOTAL => 9 ]) # parameterised RNO |
438 | ), |
439 | |
fcb7fcbb |
440 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
441 | => 'somebogusstring' ], |
442 | [ { dbic_colname => 'test' } |
443 | => 'xxx' ], |
e56b1c2d |
444 | ], |
fd1d12a9 |
445 | ) if $quoted; |
fb7cd45f |
446 | |
e56b1c2d |
447 | is ($owners->page(1)->all, 3, "$test_type: has_many prefetch returns correct number of rows"); |
448 | is ($owners->page(1)->count, 3, "$test_type: has-many prefetch returns correct count"); |
a54bd479 |
449 | |
e56b1c2d |
450 | is ($owners->page(3)->count, 2, "$test_type: has-many prefetch returns correct count"); |
4ca1fd6f |
451 | { |
e56b1c2d |
452 | local $TODO = "Top-limit does not work when your limit ends up past the resultset" |
453 | if $dialect eq 'Top'; |
454 | is ($owners->page(3)->all, 2, "$test_type: has_many prefetch returns correct number of rows"); |
455 | is ($owners->page(3)->count_rs->next, 2, "$test_type: has-many prefetch returns correct count_rs"); |
a54bd479 |
456 | } |
a54bd479 |
457 | |
a54bd479 |
458 | |
e56b1c2d |
459 | # try a ->belongs_to direction (no select collapse, group_by should work) |
460 | my $books = $schema->resultset ('BooksInLibrary')->search ( |
461 | { |
462 | 'owner.name' => [qw/wiggle woggle/], |
463 | }, |
464 | { |
465 | distinct => 1, |
466 | having => \['1 = ?', [ test => 1 ] ], #test having propagation |
467 | prefetch => 'owner', |
468 | rows => 2, # 3 results total |
86bb5a27 |
469 | order_by => [{ -desc => 'me.owner' }, 'me.id'], |
e56b1c2d |
470 | unsafe_subselect_ok => 1, |
471 | }, |
472 | ); |
6de07ea3 |
473 | |
fd1d12a9 |
474 | is_same_sql_bind ( |
475 | $books->page(3)->as_query, |
476 | $dialect eq 'Top' |
477 | ? '( |
478 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
479 | [owner].[id], [owner].[name] |
480 | FROM ( |
481 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
482 | FROM ( |
483 | SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
484 | FROM ( |
485 | SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
486 | FROM [books] [me] |
487 | JOIN [owners] [owner] |
488 | ON [owner].[id] = [me].[owner] |
489 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
490 | GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
491 | HAVING 1 = ? |
492 | ORDER BY [me].[owner] DESC, [me].[id] |
493 | ) [me] |
494 | ORDER BY [me].[owner] ASC, [me].[id] DESC |
495 | ) [me] |
496 | ORDER BY [me].[owner] DESC, [me].[id] |
497 | ) [me] |
498 | JOIN [owners] [owner] |
499 | ON [owner].[id] = [me].[owner] |
500 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
501 | ORDER BY [me].[owner] DESC, [me].[id] |
502 | )' |
503 | : '( |
504 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
505 | [owner].[id], [owner].[name] |
506 | FROM ( |
507 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
508 | FROM ( |
509 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
510 | ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index] |
511 | FROM ( |
512 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
513 | FROM [books] [me] |
514 | JOIN [owners] [owner] |
515 | ON [owner].[id] = [me].[owner] |
516 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
517 | GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
518 | HAVING 1 = ? |
519 | ) [me] |
520 | ) [me] |
521 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
522 | ) [me] |
523 | JOIN [owners] [owner] |
524 | ON [owner].[id] = [me].[owner] |
525 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
526 | ORDER BY [me].[owner] DESC, [me].[id] |
527 | )' |
528 | , |
e56b1c2d |
529 | [ |
530 | # inner |
0e773352 |
531 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
532 | => 'wiggle' ], |
533 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
534 | => 'woggle' ], |
535 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
536 | => 'Library' ], |
537 | [ { dbic_colname => 'test' } |
538 | => '1' ], |
539 | |
fd1d12a9 |
540 | # top(?) |
541 | $dialect eq 'Top' |
542 | ? () |
543 | : ( [ $OFFSET => 5 ], [ $TOTAL => 6 ] ) |
544 | , |
545 | |
e56b1c2d |
546 | # outer |
0e773352 |
547 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
548 | => 'wiggle' ], |
549 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
550 | => 'woggle' ], |
551 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
552 | => 'Library' ], |
e56b1c2d |
553 | ], |
fd1d12a9 |
554 | ) if $quoted; |
4bb438ca |
555 | |
e56b1c2d |
556 | is ($books->page(1)->all, 2, "$test_type: Prefetched grouped search returns correct number of rows"); |
557 | is ($books->page(1)->count, 2, "$test_type: Prefetched grouped search returns correct count"); |
a54bd479 |
558 | |
e56b1c2d |
559 | is ($books->page(2)->count, 1, "$test_type: Prefetched grouped search returns correct count"); |
4ca1fd6f |
560 | { |
e56b1c2d |
561 | local $TODO = "Top-limit does not work when your limit ends up past the resultset" |
562 | if $dialect eq 'Top'; |
563 | is ($books->page(2)->all, 1, "$test_type: Prefetched grouped search returns correct number of rows"); |
564 | is ($books->page(2)->count_rs->next, 1, "$test_type: Prefetched grouped search returns correct count_rs"); |
565 | } |
566 | } |
a54bd479 |
567 | } |
6de07ea3 |
568 | |
569 | |
a54bd479 |
570 | # test GUID columns |
e56b1c2d |
571 | { |
572 | $schema->storage->dbh_do (sub { |
573 | my ($storage, $dbh) = @_; |
574 | eval { $dbh->do("DROP TABLE artist_guid") }; |
575 | $dbh->do(<<'SQL'); |
b1bdb76d |
576 | CREATE TABLE artist_guid ( |
a54bd479 |
577 | artistid UNIQUEIDENTIFIER NOT NULL, |
578 | name VARCHAR(100), |
579 | rank INT NOT NULL DEFAULT '13', |
580 | charfield CHAR(10) NULL, |
581 | a_guid UNIQUEIDENTIFIER, |
582 | primary key(artistid) |
583 | ) |
584 | SQL |
e56b1c2d |
585 | }); |
8ff60918 |
586 | |
e56b1c2d |
587 | # start disconnected to make sure insert works on an un-reblessed storage |
588 | $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts); |
8ff60918 |
589 | |
e56b1c2d |
590 | my $row; |
591 | lives_ok { |
592 | $row = $schema->resultset('ArtistGUID')->create({ name => 'mtfnpy' }) |
593 | } 'created a row with a GUID'; |
8ff60918 |
594 | |
e56b1c2d |
595 | ok( |
596 | eval { $row->artistid }, |
597 | 'row has GUID PK col populated', |
598 | ); |
599 | diag $@ if $@; |
8ff60918 |
600 | |
e56b1c2d |
601 | ok( |
602 | eval { $row->a_guid }, |
603 | 'row has a GUID col with auto_nextval populated', |
604 | ); |
605 | diag $@ if $@; |
8ff60918 |
606 | |
e56b1c2d |
607 | my $row_from_db = $schema->resultset('ArtistGUID') |
608 | ->search({ name => 'mtfnpy' })->first; |
f0bd60fc |
609 | |
e56b1c2d |
610 | is $row_from_db->artistid, $row->artistid, |
611 | 'PK GUID round trip'; |
b9a2c3a5 |
612 | |
e56b1c2d |
613 | is $row_from_db->a_guid, $row->a_guid, |
614 | 'NON-PK GUID round trip'; |
615 | } |
02d133f0 |
616 | |
a54bd479 |
617 | # test MONEY type |
e56b1c2d |
618 | { |
619 | $schema->storage->dbh_do (sub { |
620 | my ($storage, $dbh) = @_; |
621 | eval { $dbh->do("DROP TABLE money_test") }; |
622 | $dbh->do(<<'SQL'); |
a54bd479 |
623 | CREATE TABLE money_test ( |
624 | id INT IDENTITY PRIMARY KEY, |
625 | amount MONEY NULL |
626 | ) |
627 | SQL |
e56b1c2d |
628 | }); |
9010bab8 |
629 | |
4ca1fd6f |
630 | { |
35af31a1 |
631 | my $freetds_and_dynamic_cursors = 1 |
632 | if $opts_name eq 'use_dynamic_cursors' && |
aca3b4c3 |
633 | $schema->storage->_using_freetds; |
35af31a1 |
634 | |
8273e845 |
635 | local $TODO = |
9ffaf8a3 |
636 | 'these tests fail on freetds with dynamic cursors for some reason' |
35af31a1 |
637 | if $freetds_and_dynamic_cursors; |
638 | local $ENV{DBIC_NULLABLE_KEY_NOWARN} = 1 |
639 | if $freetds_and_dynamic_cursors; |
56d2561e |
640 | |
9ffaf8a3 |
641 | my $rs = $schema->resultset('Money'); |
642 | my $row; |
6bc666a5 |
643 | |
9ffaf8a3 |
644 | lives_ok { |
645 | $row = $rs->create({ amount => 100 }); |
646 | } 'inserted a money value'; |
b9a2c3a5 |
647 | |
9ffaf8a3 |
648 | cmp_ok ((try { $rs->find($row->id)->amount })||0, '==', 100, |
649 | 'money value round-trip'); |
fc85215b |
650 | |
9ffaf8a3 |
651 | lives_ok { |
652 | $row->update({ amount => 200 }); |
653 | } 'updated a money value'; |
b1e1d073 |
654 | |
9ffaf8a3 |
655 | cmp_ok ((try { $rs->find($row->id)->amount })||0, '==', 200, |
656 | 'updated money value round-trip'); |
b1e1d073 |
657 | |
9ffaf8a3 |
658 | lives_ok { |
659 | $row->update({ amount => undef }); |
660 | } 'updated a money value to NULL'; |
661 | |
662 | is try { $rs->find($row->id)->amount }, undef, |
663 | 'updated money value to NULL round-trip'; |
664 | } |
e56b1c2d |
665 | } |
666 | } |
384b8bce |
667 | } |
c1cac633 |
668 | |
afcfff01 |
669 | done_testing; |
670 | |
c1cac633 |
671 | # clean up our mess |
672 | END { |
ca791b95 |
673 | if (my $dbh = eval { $schema->storage->_dbh }) { |
674 | eval { $dbh->do("DROP TABLE $_") } |
b1bdb76d |
675 | for qw/artist artist_guid money_test books owners/; |
ca791b95 |
676 | } |
65d35121 |
677 | undef $schema; |
c1cac633 |
678 | } |
fc85215b |
679 | # vim:sw=2 sts=2 |