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