MSSQL GUID support
[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) {
57 $schema = DBICTest::Schema->clone;
58 $schema->connection($dsn, $user, $pass, $opts);
59
60 $schema->resultset('Artist')->search({ name => 'foo' })->delete;
61
62 $new = $schema->resultset('Artist')->create({ name => 'foo' });
63 ok($new->artistid > 0, "Auto-PK worked");
64}
c1cac633 65
66$seen_id{$new->artistid}++;
67
68# test LIMIT support
69for (1..6) {
70 $new = $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
71 is ( $seen_id{$new->artistid}, undef, "id for Artist $_ is unique" );
72 $seen_id{$new->artistid}++;
73}
74
75my $it = $schema->resultset('Artist')->search( {}, {
76 rows => 3,
77 order_by => 'artistid',
78});
79
80is( $it->count, 3, "LIMIT count ok" );
81is( $it->next->name, "foo", "iterator->next ok" );
82$it->next;
83is( $it->next->name, "Artist 2", "iterator->next ok" );
84is( $it->next, undef, "next past end of resultset ok" );
85
ca791b95 86# test GUID columns
87
88$schema->storage->dbh_do (sub {
89 my ($storage, $dbh) = @_;
90 eval { $dbh->do("DROP TABLE artist") };
91 $dbh->do(<<'SQL');
92CREATE TABLE artist (
93 artistid UNIQUEIDENTIFIER NOT NULL,
94 name VARCHAR(100),
95 rank INT NOT NULL DEFAULT '13',
96 charfield CHAR(10) NULL,
97 a_guid UNIQUEIDENTIFIER,
98 primary key(artistid)
99)
100SQL
101});
102
103my $row;
104lives_ok {
105 $row = $schema->resultset('ArtistGUID')->create({ name => 'mtfnpy' })
106} 'created a row with a GUID';
107
108ok(
109 eval { $row->artistid },
110 'row has GUID PK col populated',
111);
112diag $@ if $@;
113
114ok(
115 eval { $row->a_guid },
116 'row has a GUID col with auto_nextval populated',
117);
118diag $@ if $@;
119
120my $row_from_db = $schema->resultset('ArtistGUID')
121 ->search({ name => 'mtfnpy' })->first;
122
123is $row_from_db->artistid, $row->artistid,
124 'PK GUID round trip';
125
126is $row_from_db->a_guid, $row->a_guid,
127 'NON-PK GUID round trip';
128
818ec409 129# test MONEY type
130$schema->storage->dbh_do (sub {
131 my ($storage, $dbh) = @_;
132 eval { $dbh->do("DROP TABLE money_test") };
133 $dbh->do(<<'SQL');
134
135CREATE TABLE money_test (
136 id INT IDENTITY PRIMARY KEY,
5064f5c3 137 amount MONEY NULL
818ec409 138)
139
140SQL
141
142});
143
144my $rs = $schema->resultset('Money');
145
818ec409 146lives_ok {
d68f21ee 147 $row = $rs->create({ amount => 100 });
818ec409 148} 'inserted a money value';
149
150is $rs->find($row->id)->amount, '100.00', 'money value round-trip';
151
d68f21ee 152lives_ok {
153 $row->update({ amount => 200 });
154} 'updated a money value';
155
156is $rs->find($row->id)->amount, '200.00', 'updated money value round-trip';
157
f6b185e1 158lives_ok {
159 $row->update({ amount => undef });
160} 'updated a money value to NULL';
161
162is $rs->find($row->id)->amount, undef,'updated money value to NULL round-trip';
163
b9a2c3a5 164$schema->storage->dbh_do (sub {
165 my ($storage, $dbh) = @_;
166 eval { $dbh->do("DROP TABLE Owners") };
167 eval { $dbh->do("DROP TABLE Books") };
168 $dbh->do(<<'SQL');
b9a2c3a5 169CREATE TABLE Books (
170 id INT IDENTITY (1, 1) NOT NULL,
171 source VARCHAR(100),
172 owner INT,
173 title VARCHAR(10),
174 price INT NULL
175)
176
177CREATE TABLE Owners (
178 id INT IDENTITY (1, 1) NOT NULL,
42e5b103 179 name VARCHAR(100),
b9a2c3a5 180)
b9a2c3a5 181SQL
182
183});
893403c8 184
185lives_ok ( sub {
186 $schema->populate ('Owners', [
187 [qw/id name /],
188 [qw/1 wiggle/],
189 [qw/2 woggle/],
190 [qw/3 boggle/],
191 [qw/4 fREW/],
192 [qw/5 fRIOUX/],
193 [qw/6 fROOH/],
194 [qw/7 fRUE/],
195 [qw/8 fISMBoC/],
196 [qw/9 station/],
197 [qw/10 mirror/],
198 [qw/11 dimly/],
199 [qw/12 face_to_face/],
200 [qw/13 icarus/],
201 [qw/14 dream/],
202 [qw/15 dyrstyggyr/],
203 ]);
204}, 'populate with PKs supplied ok' );
205
206lives_ok ( sub {
207 $schema->populate ('BooksInLibrary', [
208 [qw/source owner title /],
209 [qw/Library 1 secrets0/],
210 [qw/Library 1 secrets1/],
211 [qw/Eatery 1 secrets2/],
212 [qw/Library 2 secrets3/],
213 [qw/Library 3 secrets4/],
214 [qw/Eatery 3 secrets5/],
215 [qw/Library 4 secrets6/],
216 [qw/Library 5 secrets7/],
217 [qw/Eatery 5 secrets8/],
218 [qw/Library 6 secrets9/],
219 [qw/Library 7 secrets10/],
220 [qw/Eatery 7 secrets11/],
221 [qw/Library 8 secrets12/],
222 ]);
223}, 'populate without PKs supplied ok' );
b9a2c3a5 224
225#
02d133f0 226# try a prefetch on tables with identically named columns
b9a2c3a5 227#
228
02d133f0 229# set quote char - make sure things work while quoted
230$schema->storage->_sql_maker->{quote_char} = [qw/[ ]/];
231$schema->storage->_sql_maker->{name_sep} = '.';
232
b9a2c3a5 233{
893403c8 234 # try a ->has_many direction
fc85215b 235 my $owners = $schema->resultset ('Owners')->search ({
236 'books.id' => { '!=', undef }
237 }, {
238 prefetch => 'books',
ff1ef65b 239 order_by => 'name',
56d2561e 240 rows => 3, # 8 results total
fc85215b 241 });
242
56d2561e 243 is ($owners->page(1)->all, 3, 'has_many prefetch returns correct number of rows');
244 is ($owners->page(1)->count, 3, 'has-many prefetch returns correct count');
245
618a0fe3 246 TODO: {
247 local $TODO = 'limit past end of resultset problem';
56d2561e 248 is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows');
249 is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count');
64c7c000 250 is ($owners->page(3)->count_rs->next, 2, 'has-many prefetch returns correct count_rs');
251
252 # make sure count does not become overly complex FIXME
253 is_same_sql_bind (
254 $owners->page(3)->count_rs->as_query,
255 '(
256 SELECT COUNT( * )
257 FROM (
02d133f0 258 SELECT TOP 3 [me].[id]
259 FROM [owners] [me]
260 LEFT JOIN [books] [books] ON [books].[owner] = [me].[id]
261 WHERE ( [books].[id] IS NOT NULL )
262 GROUP BY [me].[id]
263 ORDER BY [me].[id] DESC
264 ) [count_subq]
64c7c000 265 )',
266 [],
267 );
618a0fe3 268 }
b9a2c3a5 269
42e5b103 270 # try a ->belongs_to direction (no select collapse, group_by should work)
fc85215b 271 my $books = $schema->resultset ('BooksInLibrary')->search ({
56d2561e 272 'owner.name' => [qw/wiggle woggle/],
fc85215b 273 }, {
fc85215b 274 distinct => 1,
42e5b103 275 prefetch => 'owner',
56d2561e 276 rows => 2, # 3 results total
ac93965c 277 order_by => { -desc => 'owner' },
278 # there is no sane way to order by the right side of a grouped prefetch currently :(
279 #order_by => { -desc => 'owner.name' },
fc85215b 280 });
281
b1e1d073 282
56d2561e 283 is ($books->page(1)->all, 2, 'Prefetched grouped search returns correct number of rows');
284 is ($books->page(1)->count, 2, 'Prefetched grouped search returns correct count');
b1e1d073 285
618a0fe3 286 TODO: {
287 local $TODO = 'limit past end of resultset problem';
56d2561e 288 is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows');
289 is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count');
64c7c000 290 is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
291
292 # make sure count does not become overly complex FIXME
293 is_same_sql_bind (
294 $books->page(2)->count_rs->as_query,
295 '(
296 SELECT COUNT( * )
297 FROM (
02d133f0 298 SELECT TOP 2 [me].[id]
299 FROM [books] [me]
300 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
301 WHERE ( ( ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? ) )
302 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[id], [owner].[name]
303 ORDER BY [me].[id] DESC
304 ) [count_subq]
64c7c000 305 )',
306 [
307 [ 'owner.name' => 'wiggle' ],
308 [ 'owner.name' => 'woggle' ],
309 [ 'source' => 'Library' ],
310 ],
311 );
618a0fe3 312 }
b2d16f1f 313
b9a2c3a5 314}
c1cac633 315
316# clean up our mess
317END {
ca791b95 318 if (my $dbh = eval { $schema->storage->_dbh }) {
319 eval { $dbh->do("DROP TABLE $_") }
320 for qw/artist money_test Books Owners/;
321 }
c1cac633 322}
fc85215b 323# vim:sw=2 sts=2