Merge 'trunk' into 'sybase'
[dbsrgits/DBIx-Class-Historic.git] / t / 746mssql.t
CommitLineData
c1cac633 1use strict;
b9a2c3a5 2use warnings;
c1cac633 3
4use Test::More;
893403c8 5use Test::Exception;
c1cac633 6use lib qw(t/lib);
7use DBICTest;
b2d16f1f 8use DBIC::SqlMakerTest;
c1cac633 9
10my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
11
12plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ODBC_DSN}, _USER and _PASS to run this test'
13 unless ($dsn && $user);
14
ca791b95 15plan tests => 39;
c1cac633 16
ca791b95 17DBICTest::Schema->load_classes('ArtistGUID');
42e5b103 18my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
c1cac633 19
8c0104fe 20{
21 no warnings 'redefine';
22 my $connect_count = 0;
23 my $orig_connect = \&DBI::connect;
24 local *DBI::connect = sub { $connect_count++; goto &$orig_connect };
25
26 $schema->storage->ensure_connected;
27
28 is( $connect_count, 1, 'only one connection made');
29}
9b3e916d 30
c1cac633 31isa_ok( $schema->storage, 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' );
32
c5f77f6c 33$schema->storage->dbh_do (sub {
34 my ($storage, $dbh) = @_;
35 eval { $dbh->do("DROP TABLE artist") };
36 $dbh->do(<<'SQL');
c1cac633 37CREATE 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)
c5f77f6c 44SQL
c5f77f6c 45});
46
c1cac633 47my %seen_id;
48
7b1b2582 49my @opts = (
50 { on_connect_call => 'use_dynamic_cursors' },
51 {},
52);
53my $new;
2eebd801 54
7b1b2582 55# test Auto-PK with different options
56for my $opts (@opts) {
41dd5d30 57 SKIP: {
58 $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opts);
7b1b2582 59
41dd5d30 60 eval {
61 $schema->storage->ensure_connected
62 };
63 if ($@ =~ /dynamic cursors/) {
64 skip
65'Dynamic Cursors not functional, tds_version 8.0 or greater required if using'.
66' FreeTDS', 1;
67 }
2eebd801 68
41dd5d30 69 $schema->resultset('Artist')->search({ name => 'foo' })->delete;
70
71 $new = $schema->resultset('Artist')->create({ name => 'foo' });
72
73 ok($new->artistid > 0, "Auto-PK worked");
74 }
7b1b2582 75}
c1cac633 76
77$seen_id{$new->artistid}++;
78
79# test LIMIT support
80for (1..6) {
81 $new = $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
82 is ( $seen_id{$new->artistid}, undef, "id for Artist $_ is unique" );
83 $seen_id{$new->artistid}++;
84}
85
86my $it = $schema->resultset('Artist')->search( {}, {
87 rows => 3,
88 order_by => 'artistid',
89});
90
91is( $it->count, 3, "LIMIT count ok" );
92is( $it->next->name, "foo", "iterator->next ok" );
93$it->next;
94is( $it->next->name, "Artist 2", "iterator->next ok" );
95is( $it->next, undef, "next past end of resultset ok" );
96
ca791b95 97# test GUID columns
98
99$schema->storage->dbh_do (sub {
100 my ($storage, $dbh) = @_;
101 eval { $dbh->do("DROP TABLE artist") };
102 $dbh->do(<<'SQL');
103CREATE TABLE artist (
104 artistid UNIQUEIDENTIFIER NOT NULL,
105 name VARCHAR(100),
106 rank INT NOT NULL DEFAULT '13',
107 charfield CHAR(10) NULL,
108 a_guid UNIQUEIDENTIFIER,
109 primary key(artistid)
110)
111SQL
112});
113
e34bae3a 114# start disconnected to make sure insert works on an un-reblessed storage
115$schema = DBICTest::Schema->connect($dsn, $user, $pass);
116
ca791b95 117my $row;
118lives_ok {
119 $row = $schema->resultset('ArtistGUID')->create({ name => 'mtfnpy' })
120} 'created a row with a GUID';
121
122ok(
123 eval { $row->artistid },
124 'row has GUID PK col populated',
125);
126diag $@ if $@;
127
128ok(
129 eval { $row->a_guid },
130 'row has a GUID col with auto_nextval populated',
131);
132diag $@ if $@;
133
134my $row_from_db = $schema->resultset('ArtistGUID')
135 ->search({ name => 'mtfnpy' })->first;
136
137is $row_from_db->artistid, $row->artistid,
138 'PK GUID round trip';
139
140is $row_from_db->a_guid, $row->a_guid,
141 'NON-PK GUID round trip';
142
818ec409 143# test MONEY type
144$schema->storage->dbh_do (sub {
145 my ($storage, $dbh) = @_;
146 eval { $dbh->do("DROP TABLE money_test") };
147 $dbh->do(<<'SQL');
818ec409 148CREATE TABLE money_test (
149 id INT IDENTITY PRIMARY KEY,
5064f5c3 150 amount MONEY NULL
818ec409 151)
818ec409 152SQL
818ec409 153});
154
155my $rs = $schema->resultset('Money');
156
818ec409 157lives_ok {
d68f21ee 158 $row = $rs->create({ amount => 100 });
818ec409 159} 'inserted a money value';
160
a33d2444 161cmp_ok $rs->find($row->id)->amount, '==', 100, 'money value round-trip';
818ec409 162
d68f21ee 163lives_ok {
164 $row->update({ amount => 200 });
165} 'updated a money value';
166
a33d2444 167cmp_ok $rs->find($row->id)->amount, '==', 200,
168 'updated money value round-trip';
d68f21ee 169
f6b185e1 170lives_ok {
171 $row->update({ amount => undef });
172} 'updated a money value to NULL';
173
174is $rs->find($row->id)->amount, undef,'updated money value to NULL round-trip';
175
b9a2c3a5 176$schema->storage->dbh_do (sub {
177 my ($storage, $dbh) = @_;
178 eval { $dbh->do("DROP TABLE Owners") };
179 eval { $dbh->do("DROP TABLE Books") };
180 $dbh->do(<<'SQL');
b9a2c3a5 181CREATE TABLE Books (
182 id INT IDENTITY (1, 1) NOT NULL,
183 source VARCHAR(100),
184 owner INT,
185 title VARCHAR(10),
186 price INT NULL
187)
188
189CREATE TABLE Owners (
190 id INT IDENTITY (1, 1) NOT NULL,
42e5b103 191 name VARCHAR(100),
b9a2c3a5 192)
b9a2c3a5 193SQL
194
195});
893403c8 196
197lives_ok ( sub {
198 $schema->populate ('Owners', [
199 [qw/id name /],
200 [qw/1 wiggle/],
201 [qw/2 woggle/],
202 [qw/3 boggle/],
203 [qw/4 fREW/],
204 [qw/5 fRIOUX/],
205 [qw/6 fROOH/],
206 [qw/7 fRUE/],
207 [qw/8 fISMBoC/],
208 [qw/9 station/],
209 [qw/10 mirror/],
210 [qw/11 dimly/],
211 [qw/12 face_to_face/],
212 [qw/13 icarus/],
213 [qw/14 dream/],
214 [qw/15 dyrstyggyr/],
215 ]);
216}, 'populate with PKs supplied ok' );
217
218lives_ok ( sub {
219 $schema->populate ('BooksInLibrary', [
220 [qw/source owner title /],
221 [qw/Library 1 secrets0/],
222 [qw/Library 1 secrets1/],
223 [qw/Eatery 1 secrets2/],
224 [qw/Library 2 secrets3/],
225 [qw/Library 3 secrets4/],
226 [qw/Eatery 3 secrets5/],
227 [qw/Library 4 secrets6/],
228 [qw/Library 5 secrets7/],
229 [qw/Eatery 5 secrets8/],
230 [qw/Library 6 secrets9/],
231 [qw/Library 7 secrets10/],
232 [qw/Eatery 7 secrets11/],
233 [qw/Library 8 secrets12/],
234 ]);
235}, 'populate without PKs supplied ok' );
b9a2c3a5 236
237#
02d133f0 238# try a prefetch on tables with identically named columns
b9a2c3a5 239#
240
02d133f0 241# set quote char - make sure things work while quoted
242$schema->storage->_sql_maker->{quote_char} = [qw/[ ]/];
243$schema->storage->_sql_maker->{name_sep} = '.';
244
b9a2c3a5 245{
893403c8 246 # try a ->has_many direction
fc85215b 247 my $owners = $schema->resultset ('Owners')->search ({
248 'books.id' => { '!=', undef }
249 }, {
250 prefetch => 'books',
ff1ef65b 251 order_by => 'name',
56d2561e 252 rows => 3, # 8 results total
fc85215b 253 });
254
56d2561e 255 is ($owners->page(1)->all, 3, 'has_many prefetch returns correct number of rows');
256 is ($owners->page(1)->count, 3, 'has-many prefetch returns correct count');
257
618a0fe3 258 TODO: {
259 local $TODO = 'limit past end of resultset problem';
56d2561e 260 is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows');
261 is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count');
64c7c000 262 is ($owners->page(3)->count_rs->next, 2, 'has-many prefetch returns correct count_rs');
263
e2751d0b 264 # make sure count does not become overly complex
64c7c000 265 is_same_sql_bind (
266 $owners->page(3)->count_rs->as_query,
267 '(
268 SELECT COUNT( * )
269 FROM (
02d133f0 270 SELECT TOP 3 [me].[id]
271 FROM [owners] [me]
272 LEFT JOIN [books] [books] ON [books].[owner] = [me].[id]
273 WHERE ( [books].[id] IS NOT NULL )
274 GROUP BY [me].[id]
275 ORDER BY [me].[id] DESC
276 ) [count_subq]
64c7c000 277 )',
278 [],
279 );
618a0fe3 280 }
b9a2c3a5 281
42e5b103 282 # try a ->belongs_to direction (no select collapse, group_by should work)
fc85215b 283 my $books = $schema->resultset ('BooksInLibrary')->search ({
56d2561e 284 'owner.name' => [qw/wiggle woggle/],
fc85215b 285 }, {
fc85215b 286 distinct => 1,
42e5b103 287 prefetch => 'owner',
56d2561e 288 rows => 2, # 3 results total
ac93965c 289 order_by => { -desc => 'owner' },
290 # there is no sane way to order by the right side of a grouped prefetch currently :(
291 #order_by => { -desc => 'owner.name' },
fc85215b 292 });
293
b1e1d073 294
56d2561e 295 is ($books->page(1)->all, 2, 'Prefetched grouped search returns correct number of rows');
296 is ($books->page(1)->count, 2, 'Prefetched grouped search returns correct count');
b1e1d073 297
618a0fe3 298 TODO: {
299 local $TODO = 'limit past end of resultset problem';
56d2561e 300 is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows');
301 is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count');
64c7c000 302 is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
303
e2751d0b 304 # make sure count does not become overly complex (FIXME - the distinct-induced group_by is incorrect)
64c7c000 305 is_same_sql_bind (
306 $books->page(2)->count_rs->as_query,
307 '(
308 SELECT COUNT( * )
309 FROM (
02d133f0 310 SELECT TOP 2 [me].[id]
311 FROM [books] [me]
312 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
313 WHERE ( ( ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? ) )
e2751d0b 314 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
02d133f0 315 ORDER BY [me].[id] DESC
316 ) [count_subq]
64c7c000 317 )',
318 [
319 [ 'owner.name' => 'wiggle' ],
320 [ 'owner.name' => 'woggle' ],
321 [ 'source' => 'Library' ],
322 ],
323 );
618a0fe3 324 }
b2d16f1f 325
b9a2c3a5 326}
c1cac633 327
328# clean up our mess
329END {
e06ad5d5 330 if (my $dbh = eval { $schema->storage->_dbh }) {
331 eval { $dbh->do("DROP TABLE $_") }
332 for qw/artist money_test Books Owners/;
333 }
c1cac633 334}
fc85215b 335# vim:sw=2 sts=2