minor changes
[dbsrgits/DBIx-Class.git] / t / 74mssql.t
CommitLineData
70350518 1use strict;
2use warnings;
3
46e3af47 4# use this if you keep a copy of DBD::Sybase linked to FreeTDS somewhere else
5BEGIN {
6 if (my $lib_dirs = $ENV{DBICTEST_MSSQL_PERL5LIB}) {
7 unshift @INC, $_ for split /:/, $lib_dirs;
8 }
9}
10
70350518 11use Test::More;
8c52ffd3 12use Test::Exception;
70350518 13use lib qw(t/lib);
14use DBICTest;
eef2ff6c 15
16my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_${_}" } qw/DSN USER PASS/};
17
70350518 18plan skip_all => 'Set $ENV{DBICTEST_MSSQL_DSN}, _USER and _PASS to run this test'
eef2ff6c 19 unless ($dsn);
20
8a0720e2 21my $TESTS = 15;
eef2ff6c 22
7379eb67 23plan tests => $TESTS * 2;
eef2ff6c 24
7379eb67 25my @storage_types = (
c29ce317 26 'DBI::Sybase::Microsoft_SQL_Server',
27 'DBI::Sybase::Microsoft_SQL_Server::NoBindVars',
7379eb67 28);
29my $storage_idx = -1;
30my $schema;
31
32for my $storage_type (@storage_types) {
33 $storage_idx++;
b3f41261 34
7379eb67 35 $schema = DBICTest::Schema->clone;
36
37 if ($storage_idx != 0) { # autodetect
c29ce317 38 $schema->storage_type("::$storage_type");
7379eb67 39 }
651682ae 40
7379eb67 41 $schema->connection($dsn, $user, $pass);
8c52ffd3 42
7379eb67 43 $schema->storage->ensure_connected;
eef2ff6c 44
7379eb67 45 if ($storage_idx == 0 && ref($schema->storage) =~ /NoBindVars\z/) {
46 my $tb = Test::More->builder;
47 $tb->skip('no placeholders') for 1..$TESTS;
48 next;
49 }
50
c29ce317 51 isa_ok($schema->storage, "DBIx::Class::Storage::$storage_type");
7379eb67 52
53# start disconnected to test reconnection
54 $schema->storage->_dbh->disconnect;
55
56 my $dbh;
57 lives_ok (sub {
58 $dbh = $schema->storage->dbh;
59 }, 'reconnect works');
60
61 $dbh->do("IF OBJECT_ID('artist', 'U') IS NOT NULL
62 DROP TABLE artist");
63 $dbh->do("IF OBJECT_ID('cd', 'U') IS NOT NULL
64 DROP TABLE cd");
65
66 $dbh->do("CREATE TABLE artist (artistid INT IDENTITY PRIMARY KEY, name VARCHAR(100), rank INT DEFAULT '13', charfield CHAR(10) NULL);");
67 $dbh->do("CREATE TABLE cd (cdid INT IDENTITY PRIMARY KEY, artist INT, title VARCHAR(100), year VARCHAR(100), genreid INT NULL, single_track INT NULL);");
3ff5b740 68# Just to test compat shim, Auto is in Core
7379eb67 69 $schema->class('Artist')->load_components('PK::Auto::MSSQL');
eef2ff6c 70
71# Test PK
7379eb67 72 my $new = $schema->resultset('Artist')->create( { name => 'foo' } );
73 ok($new->artistid, "Auto-PK worked");
eef2ff6c 74
75# Test LIMIT
7379eb67 76 for (1..6) {
77 $schema->resultset('Artist')->create( { name => 'Artist ' . $_, rank => $_ } );
78 }
eef2ff6c 79
7379eb67 80 my $it = $schema->resultset('Artist')->search( { },
81 { rows => 3,
82 offset => 2,
83 order_by => 'artistid'
84 }
85 );
eef2ff6c 86
b4474f31 87# Test ? in data don't get treated as placeholders
7379eb67 88 my $cd = $schema->resultset('CD')->create( {
89 artist => 1,
90 title => 'Does this break things?',
91 year => 2007,
92 } );
93 ok($cd->id, 'Not treating ? in data as placeholders');
94
95 is( $it->count, 3, "LIMIT count ok" );
96 ok( $it->next->name, "iterator->next ok" );
97 $it->next;
98 $it->next;
99 is( $it->next, undef, "next past end of resultset ok" );
eef2ff6c 100
5064f5c3 101# test MONEY column support
7379eb67 102 $schema->storage->dbh_do (sub {
103 my ($storage, $dbh) = @_;
104 eval { $dbh->do("DROP TABLE money_test") };
105 $dbh->do(<<'SQL');
7379eb67 106 CREATE TABLE money_test (
107 id INT IDENTITY PRIMARY KEY,
108 amount MONEY NULL
109 )
5064f5c3 110SQL
111
7379eb67 112 });
5064f5c3 113
7379eb67 114 my $rs = $schema->resultset('Money');
5064f5c3 115
7379eb67 116 my $row;
117 lives_ok {
118 $row = $rs->create({ amount => 100 });
119 } 'inserted a money value';
5064f5c3 120
a33d2444 121 cmp_ok $rs->find($row->id)->amount, '==', 100, 'money value round-trip';
5064f5c3 122
7379eb67 123 lives_ok {
124 $row->update({ amount => 200 });
125 } 'updated a money value';
5064f5c3 126
a33d2444 127 cmp_ok $rs->find($row->id)->amount, '==', 200,
128 'updated money value round-trip';
5064f5c3 129
7379eb67 130 lives_ok {
131 $row->update({ amount => undef });
132 } 'updated a money value to NULL';
5064f5c3 133
c8365716 134 is $rs->find($row->id)->amount,
135 undef, 'updated money value to NULL round-trip';
a467a0c9 136
8a0720e2 137 $rs->create({ amount => 300 }) for (1..3);
138
a467a0c9 139 # test multiple active statements
140 lives_ok {
a467a0c9 141 my $artist_rs = $schema->resultset('Artist');
142 while (my $row = $rs->next) {
143 my $artist = $artist_rs->next;
144 }
8a0720e2 145 $rs->reset;
a467a0c9 146 } 'multiple active statements';
8a0720e2 147
148 # test multiple active statements in a transaction
149 TODO: {
150 local $TODO = 'needs similar FreeTDS fixes to the ones in Sybase.pm';
151 lives_ok {
152 $schema->txn_do(sub {
153 $rs->create({ amount => 400 });
154 });
155 } 'simple transaction';
156 }
7379eb67 157}
5064f5c3 158
3ff5b740 159# clean up our mess
160END {
7379eb67 161 if (my $dbh = eval { $schema->storage->dbh }) {
162 $dbh->do("IF OBJECT_ID('artist', 'U') IS NOT NULL DROP TABLE artist");
163 $dbh->do("IF OBJECT_ID('cd', 'U') IS NOT NULL DROP TABLE cd");
164 $dbh->do("IF OBJECT_ID('money_test', 'U') IS NOT NULL DROP TABLE money_test");
165 }
3ff5b740 166}