some reorganization and cleanup of pg-specific tests
[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 }
7b1b2582 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');
148
149CREATE TABLE money_test (
150 id INT IDENTITY PRIMARY KEY,
5064f5c3 151 amount MONEY NULL
818ec409 152)
153
154SQL
155
156});
157
158my $rs = $schema->resultset('Money');
159
818ec409 160lives_ok {
d68f21ee 161 $row = $rs->create({ amount => 100 });
818ec409 162} 'inserted a money value';
163
a33d2444 164cmp_ok $rs->find($row->id)->amount, '==', 100, 'money value round-trip';
818ec409 165
d68f21ee 166lives_ok {
167 $row->update({ amount => 200 });
168} 'updated a money value';
169
a33d2444 170cmp_ok $rs->find($row->id)->amount, '==', 200,
171 'updated money value round-trip';
d68f21ee 172
f6b185e1 173lives_ok {
174 $row->update({ amount => undef });
175} 'updated a money value to NULL';
176
177is $rs->find($row->id)->amount, undef,'updated money value to NULL round-trip';
178
b9a2c3a5 179$schema->storage->dbh_do (sub {
180 my ($storage, $dbh) = @_;
181 eval { $dbh->do("DROP TABLE Owners") };
182 eval { $dbh->do("DROP TABLE Books") };
183 $dbh->do(<<'SQL');
b9a2c3a5 184CREATE TABLE Books (
185 id INT IDENTITY (1, 1) NOT NULL,
186 source VARCHAR(100),
187 owner INT,
188 title VARCHAR(10),
189 price INT NULL
190)
191
192CREATE TABLE Owners (
193 id INT IDENTITY (1, 1) NOT NULL,
42e5b103 194 name VARCHAR(100),
b9a2c3a5 195)
b9a2c3a5 196SQL
197
198});
893403c8 199
200lives_ok ( sub {
201 $schema->populate ('Owners', [
202 [qw/id name /],
203 [qw/1 wiggle/],
204 [qw/2 woggle/],
205 [qw/3 boggle/],
206 [qw/4 fREW/],
207 [qw/5 fRIOUX/],
208 [qw/6 fROOH/],
209 [qw/7 fRUE/],
210 [qw/8 fISMBoC/],
211 [qw/9 station/],
212 [qw/10 mirror/],
213 [qw/11 dimly/],
214 [qw/12 face_to_face/],
215 [qw/13 icarus/],
216 [qw/14 dream/],
217 [qw/15 dyrstyggyr/],
218 ]);
219}, 'populate with PKs supplied ok' );
220
221lives_ok ( sub {
222 $schema->populate ('BooksInLibrary', [
223 [qw/source owner title /],
224 [qw/Library 1 secrets0/],
225 [qw/Library 1 secrets1/],
226 [qw/Eatery 1 secrets2/],
227 [qw/Library 2 secrets3/],
228 [qw/Library 3 secrets4/],
229 [qw/Eatery 3 secrets5/],
230 [qw/Library 4 secrets6/],
231 [qw/Library 5 secrets7/],
232 [qw/Eatery 5 secrets8/],
233 [qw/Library 6 secrets9/],
234 [qw/Library 7 secrets10/],
235 [qw/Eatery 7 secrets11/],
236 [qw/Library 8 secrets12/],
237 ]);
238}, 'populate without PKs supplied ok' );
b9a2c3a5 239
240#
02d133f0 241# try a prefetch on tables with identically named columns
b9a2c3a5 242#
243
02d133f0 244# set quote char - make sure things work while quoted
245$schema->storage->_sql_maker->{quote_char} = [qw/[ ]/];
246$schema->storage->_sql_maker->{name_sep} = '.';
247
b9a2c3a5 248{
893403c8 249 # try a ->has_many direction
fc85215b 250 my $owners = $schema->resultset ('Owners')->search ({
251 'books.id' => { '!=', undef }
252 }, {
253 prefetch => 'books',
ff1ef65b 254 order_by => 'name',
56d2561e 255 rows => 3, # 8 results total
fc85215b 256 });
257
56d2561e 258 is ($owners->page(1)->all, 3, 'has_many prefetch returns correct number of rows');
259 is ($owners->page(1)->count, 3, 'has-many prefetch returns correct count');
260
618a0fe3 261 TODO: {
262 local $TODO = 'limit past end of resultset problem';
56d2561e 263 is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows');
264 is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count');
64c7c000 265 is ($owners->page(3)->count_rs->next, 2, 'has-many prefetch returns correct count_rs');
266
e2751d0b 267 # make sure count does not become overly complex
64c7c000 268 is_same_sql_bind (
269 $owners->page(3)->count_rs->as_query,
270 '(
271 SELECT COUNT( * )
272 FROM (
02d133f0 273 SELECT TOP 3 [me].[id]
274 FROM [owners] [me]
275 LEFT JOIN [books] [books] ON [books].[owner] = [me].[id]
276 WHERE ( [books].[id] IS NOT NULL )
277 GROUP BY [me].[id]
278 ORDER BY [me].[id] DESC
279 ) [count_subq]
64c7c000 280 )',
281 [],
282 );
618a0fe3 283 }
b9a2c3a5 284
42e5b103 285 # try a ->belongs_to direction (no select collapse, group_by should work)
fc85215b 286 my $books = $schema->resultset ('BooksInLibrary')->search ({
56d2561e 287 'owner.name' => [qw/wiggle woggle/],
fc85215b 288 }, {
fc85215b 289 distinct => 1,
42e5b103 290 prefetch => 'owner',
56d2561e 291 rows => 2, # 3 results total
ac93965c 292 order_by => { -desc => 'owner' },
293 # there is no sane way to order by the right side of a grouped prefetch currently :(
294 #order_by => { -desc => 'owner.name' },
fc85215b 295 });
296
b1e1d073 297
56d2561e 298 is ($books->page(1)->all, 2, 'Prefetched grouped search returns correct number of rows');
299 is ($books->page(1)->count, 2, 'Prefetched grouped search returns correct count');
b1e1d073 300
618a0fe3 301 TODO: {
302 local $TODO = 'limit past end of resultset problem';
56d2561e 303 is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows');
304 is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count');
64c7c000 305 is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
306
e2751d0b 307 # make sure count does not become overly complex (FIXME - the distinct-induced group_by is incorrect)
64c7c000 308 is_same_sql_bind (
309 $books->page(2)->count_rs->as_query,
310 '(
311 SELECT COUNT( * )
312 FROM (
02d133f0 313 SELECT TOP 2 [me].[id]
314 FROM [books] [me]
315 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
316 WHERE ( ( ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? ) )
e2751d0b 317 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
02d133f0 318 ORDER BY [me].[id] DESC
319 ) [count_subq]
64c7c000 320 )',
321 [
322 [ 'owner.name' => 'wiggle' ],
323 [ 'owner.name' => 'woggle' ],
324 [ 'source' => 'Library' ],
325 ],
326 );
618a0fe3 327 }
b2d16f1f 328
b9a2c3a5 329}
c1cac633 330
331# clean up our mess
332END {
ca791b95 333 if (my $dbh = eval { $schema->storage->_dbh }) {
334 eval { $dbh->do("DROP TABLE $_") }
335 for qw/artist money_test Books Owners/;
336 }
c1cac633 337}
fc85215b 338# vim:sw=2 sts=2