DBIx/Class/Storage/DBI/MSSQL.pm

filter: Statementstmt 40.4% 40 / 99 Branchbran 19.2% 5 / 26 Conditioncond 3.8% 1 / 26 MC/DCmcdc 0.0% 0 / 17 Subroutinesub 33.3% 8 / 24 Podpod 100% 3 / 3 totaltotal 29.2% 57 / 195 CC 24 SCAR 30.1
CC
24
Cov
30%
_prep_for_execute
72.0
_execute
42.0
_ping
6.0
CRAP 20.2
« DBIx/Class/CDBICompat/LazyLoading.pm ↑ Summary DBIx/Class/Storage/DBI/SQLAnywhere/Cursor.pm »
1 package DBIx::Class::Storage::DBI::MSSQL;
2  
3 5 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 5 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 5 ▶use base qw/
Subroutine BEGIN: called CC=1 SCAR=0.0
7 DBIx::Class::Storage::DBI::UniqueIdentifier
8 DBIx::Class::Storage::DBI::IdentityInsert
9 5 /;
10 5 ▶use mro 'c3';
Subroutine BEGIN: called CC=1 SCAR=0.0
11  
12 5 ▶use Try::Tiny;
Subroutine BEGIN: called CC=1 SCAR=0.0
13 5 ▶use namespace::clean;
Subroutine BEGIN: called CC=1 SCAR=0.0
14  
15 5 __PACKAGE__->mk_group_accessors(simple => qw/
16 _identity _identity_method _no_scope_identity_query
17 /);
18  
19 5 __PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MSSQL');
20  
21 5 __PACKAGE__->sql_quote_char([qw/[ ]/]);
22  
23 5 __PACKAGE__->datetime_parser_type (
24 'DBIx::Class::Storage::DBI::MSSQL::DateTime::Format'
25 );
26  
27 5 __PACKAGE__->new_guid('NEWID()');
28  
29 0 ▶sub _prep_for_execute {
Subroutine _prep_for_execute: not called CC=8 SCAR=42.8
30 0 my $self = shift;
31 0 my ($op, $ident, $args) = @_;
32  
33 # cast MONEY values properly
34 0 ▶ if ($op eq 'insert' || $op eq 'update') {
Branch: 1 branch
BranchTrueFalseTotal
if ($op eq "insert" or $op eq "update") 0 0 0
Condition0%0/3
$op eq "insert" or $op eq "update"
l!l&&r!l&&!r
000
MC/DC0%0/2
$op eq "insert" or $op eq "update"
$op eq "insert" $op eq "update"
Truth table0%0/3
A or B
A= $op eq "insert"B= $op eq "update"
ABresult
1X1
011
000
35 0 my $fields = $args->[0];
36  
37 0 my $colinfo = $ident->columns_info([keys %$fields]);
38  
39 0 for my $col (keys %$fields) {
40 # $ident is a result source object with INSERT/UPDATE ops
41 0 ▶ if (
Branch: 1 branch
BranchTrueFalseTotal
if ($colinfo->{$col}{"data_type"} and $colinfo->{$col}{"data_type"} =~ /^money\z/i) 0 0 0
Condition0%0/3
$colinfo->{$col}{"data_type"} and $colinfo->{$col}{"data_type"} =~ /^money\z/i
!ll&&!rl&&r
000
MC/DC0%0/2
$colinfo->{$col}{"data_type"} and $colinfo->{$col}{"data_type"} =~ /^money\z/i
$colinfo->{$col}{"data_type"} $colinfo->{$col}{"data_type"} =~ /^money\z/i
Truth table0%0/3
A and B
A= $colinfo->{$col}{"data_type"}B= $colinfo->{$col}{"data_type"} =~ /^money\z/i
ABresult
0X0
100
111
42 $colinfo->{$col}{data_type}
43 &&
44 $colinfo->{$col}{data_type} =~ /^money\z/i
45 ) {
46 0 my $val = $fields->{$col};
47 0 $fields->{$col} = \['CAST(? AS MONEY)', [ $col => $val ]];
48 }
49 }
50 }
51  
52 0 my ($sql, $bind) = $self->next::method (@_);
53  
54 # SELECT SCOPE_IDENTITY only works within a statement scope. We
55 # must try to always use this particular idiom first, as it is the
56 # only one that guarantees retrieving the correct id under high
57 # concurrency. When this fails we will fall back to whatever secondary
58 # retrieval method is specified in _identity_method, but at this
59 # point we don't have many guarantees we will get what we expected.
60 # http://msdn.microsoft.com/en-us/library/ms190315.aspx
61 # http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx
62 0 ▶ if ($self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query) 0 0 0
Condition0%0/3
$self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query
!ll&&!rl&&r
000
MC/DC0%0/2
$self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query
$self->_perform_autoinc_retrieval not $self->_no_scope_identity_query
Truth table0%0/3
A and not(B)
A= $self->_perform_autoinc_retrievalB= not $self->_no_scope_identity_query
ABresult
0X0
100
111
63 0 $sql .= "\nSELECT SCOPE_IDENTITY()";
64 }
65  
66 0 return ($sql, $bind);
67 }
68  
69 0 ▶sub _execute {
Subroutine _execute: not called CC=6 SCAR=37.4
70 0 my $self = shift;
71  
72 # always list ctx - we need the $sth
73 0 my ($rv, $sth, @bind) = $self->next::method(@_);
74  
75 0 ▶ if ($self->_perform_autoinc_retrieval) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->_perform_autoinc_retrieval) 0 0 0
76  
77 # attempt to bring back the result of SELECT SCOPE_IDENTITY() we tacked
78 # on in _prep_for_execute above
79 0 my $identity;
80  
81 # we didn't even try on ftds
82 0 ▶ unless ($self->_no_scope_identity_query) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($self->_no_scope_identity_query) 0 0 0
83 0 ▶ ($identity) = try { $sth->fetchrow_array };
Subroutine __ANON__: not called CC=1 SCAR=6.9
84 0 $sth->finish;
85 }
86  
87 # SCOPE_IDENTITY failed, but we can do something else
88 0 ▶ if ( (! $identity) && $self->_identity_method) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $identity and $self->_identity_method) 0 0 0
Condition0%0/3
not $identity and $self->_identity_method
!ll&&!rl&&r
000
MC/DC0%0/2
not $identity and $self->_identity_method
not $identity $self->_identity_method
Truth table0%0/3
not(A) and B
A= not $identityB= $self->_identity_method
ABresult
0X0
100
111
89 0 ($identity) = $self->_dbh->selectrow_array(
90 'select ' . $self->_identity_method
91 );
92 }
93  
94 0 $self->_identity($identity);
95 }
96  
97 0 ▶ return wantarray ? ($rv, $sth, @bind) : $rv;
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 0 0 0
98 }
99  
100 0 ▶sub last_insert_id { shift->_identity }
Subroutine last_insert_id: not called CC=1 SCAR=6.9
Pod documented
101  
102 #
103 # MSSQL is retarded wrt ordered subselects. One needs to add a TOP
104 # to *all* subqueries, but one also *can't* use TOP 100 PERCENT
105 # http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
106 #
107 8 ▶sub _select_args_to_query {
Subroutine _select_args_to_query: called CC=4 SCAR=14.2
108 #my ($self, $ident, $select, $cond, $attrs) = @_;
109 8 my $self = shift;
110 8 my $attrs = $_[3];
111  
112 8 my $sql_bind = $self->next::method (@_);
113  
114 # see if this is an ordered subquery
115 8 ▶ if (
Branch: 1 branch
BranchTrueFalseTotal
if (not $$sql_bind->[0] =~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi and scalar $self->_extract_order_criteria($attrs->{"order_by"})) 8 0 8
Condition50%1/2
not $$sql_bind->[0] =~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi and scalar $self->_extract_order_criteria($attrs->{"order_by"})
!ll
08
MC/DC0%0/1
not $$sql_bind->[0] =~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi and scalar $self->_extract_order_criteria($attrs->{"order_by"})
not $$sql_bind->[0] =~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
Truth table50%1/2
not(A)
A= not $$sql_bind->[0] =~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
Aresult
00
11
116 $$sql_bind->[0] !~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
117 and
118 scalar $self->_extract_order_criteria ($attrs->{order_by})
119 ) {
120 $self->throw_exception(
121 'An ordered subselect encountered - this is not safe! Please see "Ordered Subselects" in DBIx::Class::Storage::DBI::MSSQL'
122 8 ▶ ) unless $attrs->{unsafe_subselect_ok};
Branch: 1 branch
BranchTrueFalseTotal
unless $attrs->{"unsafe_subselect_ok"} 0 8 8
123  
124 8 $$sql_bind->[0] =~ s/^ \s* \( \s* SELECT (?=\s) / '(SELECT TOP ' . $self->sql_maker->__max_int /exi;
125 }
126  
127 8 $sql_bind;
128 }
129  
130  
131 # savepoint syntax is the same as in Sybase ASE
132  
133 0 ▶sub _exec_svp_begin {
Subroutine _exec_svp_begin: not called CC=1 SCAR=6.9
134 0 my ($self, $name) = @_;
135  
136 0 $self->_dbh->do("SAVE TRANSACTION $name");
137 }
138  
139 # A new SAVE TRANSACTION with the same name releases the previous one.
140 0 ▶sub _exec_svp_release { 1 }
Subroutine _exec_svp_release: not called CC=1 SCAR=6.9
141  
142 0 ▶sub _exec_svp_rollback {
Subroutine _exec_svp_rollback: not called CC=1 SCAR=6.9
143 0 my ($self, $name) = @_;
144  
145 0 $self->_dbh->do("ROLLBACK TRANSACTION $name");
146 }
147  
148 0 ▶sub sqlt_type { 'SQLServer' }
Subroutine sqlt_type: not called CC=1 SCAR=6.9
Pod documented
149  
150 1 ▶sub sql_limit_dialect {
Subroutine sql_limit_dialect: called CC=4 SCAR=16.6
Pod documented
151 1 my $self = shift;
152  
153 1 my $supports_rno = 0;
154  
155 1 ▶ if (exists $self->_server_info->{normalized_dbms_version}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $self->_server_info->{'normalized_dbms_version'}) { } 1 0 1
156 1 ▶ $supports_rno = 1 if $self->_server_info->{normalized_dbms_version} >= 9;
Branch: 1 branch
BranchTrueFalseTotal
if $self->_server_info->{"normalized_dbms_version"} >= 9 0 1 1
157 }
158 else {
159 # User is connecting via DBD::Sybase and has no permission to run
160 # stored procedures like xp_msver, or version detection failed for some
161 # other reason.
162 # So, we use a query to check if RNO is implemented.
163 0 ▶ try {
Subroutine __ANON__: not called CC=1 SCAR=6.9
164 0 $self->_get_dbh->selectrow_array('SELECT row_number() OVER (ORDER BY rand())');
165 0 $supports_rno = 1;
166 0 };
167 }
168  
169 1 ▶ return $supports_rno ? 'RowNumberOver' : 'Top';
Branch: 1 branch
BranchTrueFalseTotal
$supports_rno ? : 0 1 1
170 }
171  
172 0 ▶sub _ping {
Subroutine _ping: not called CC=2 SCAR=17.9
173 0 my $self = shift;
174  
175 0 ▶ my $dbh = $self->_dbh or return 0;
Branch: 1 branch
BranchTrueFalseTotal
my $dbh = $self->_dbh or return 0 0 0 0
176  
177 0 local $dbh->{RaiseError} = 1;
178 0 local $dbh->{PrintError} = 0;
179  
180 0 ▶ return try {
Subroutine __ANON__: not called CC=1 SCAR=6.9
181 0 $dbh->do('select 1');
182 0 1;
183 0 ▶ } catch {
Subroutine __ANON__: not called CC=1 SCAR=6.9
184 0 0;
185 0 };
186 }
187  
188 package # hide from PAUSE
189 DBIx::Class::Storage::DBI::MSSQL::DateTime::Format;
190  
191 5 my $datetime_format = '%Y-%m-%d %H:%M:%S.%3N'; # %F %T
192 5 my $smalldatetime_format = '%Y-%m-%d %H:%M:%S';
193  
194 5 my ($datetime_parser, $smalldatetime_parser);
195  
196 0 ▶sub parse_datetime {
Subroutine parse_datetime: not called CC=2 SCAR=17.9
197 0 shift;
198 0 require DateTime::Format::Strptime;
199 0 ▶ $datetime_parser ||= DateTime::Format::Strptime->new(
Condition0%0/3
$datetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
l!l&&r!l&&!r
000
MC/DC0%0/2
$datetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
$datetime_parser "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
Truth table0%0/3
A ||= B
A= $datetime_parserB= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
ABresult
1X1
011
000
200 pattern => $datetime_format,
201 on_error => 'croak',
202 );
203 0 return $datetime_parser->parse_datetime(shift);
204 }
205  
206 0 ▶sub format_datetime {
Subroutine format_datetime: not called CC=2 SCAR=17.9
207 0 shift;
208 0 require DateTime::Format::Strptime;
209 0 ▶ $datetime_parser ||= DateTime::Format::Strptime->new(
Condition0%0/3
$datetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
l!l&&r!l&&!r
000
MC/DC0%0/2
$datetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
$datetime_parser "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
Truth table0%0/3
A ||= B
A= $datetime_parserB= "DateTime::Format::Strptime"->new("pattern", $datetime_format, "on_error", "croak")
ABresult
1X1
011
000
210 pattern => $datetime_format,
211 on_error => 'croak',
212 );
213 0 return $datetime_parser->format_datetime(shift);
214 }
215  
216 0 ▶sub parse_smalldatetime {
Subroutine parse_smalldatetime: not called CC=2 SCAR=17.9
217 0 shift;
218 0 require DateTime::Format::Strptime;
219 0 ▶ $smalldatetime_parser ||= DateTime::Format::Strptime->new(
Condition0%0/3
$smalldatetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
l!l&&r!l&&!r
000
MC/DC0%0/2
$smalldatetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
$smalldatetime_parser "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
Truth table0%0/3
A ||= B
A= $smalldatetime_parserB= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
ABresult
1X1
011
000
220 pattern => $smalldatetime_format,
221 on_error => 'croak',
222 );
223 0 return $smalldatetime_parser->parse_datetime(shift);
224 }
225  
226 0 ▶sub format_smalldatetime {
Subroutine format_smalldatetime: not called CC=2 SCAR=17.9
227 0 shift;
228 0 require DateTime::Format::Strptime;
229 0 ▶ $smalldatetime_parser ||= DateTime::Format::Strptime->new(
Condition0%0/3
$smalldatetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
l!l&&r!l&&!r
000
MC/DC0%0/2
$smalldatetime_parser ||= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
$smalldatetime_parser "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
Truth table0%0/3
A ||= B
A= $smalldatetime_parserB= "DateTime::Format::Strptime"->new("pattern", $smalldatetime_format, "on_error", "croak")
ABresult
1X1
011
000
230 pattern => $smalldatetime_format,
231 on_error => 'croak',
232 );
233 0 return $smalldatetime_parser->format_datetime(shift);
234 }
235  
236 5 1;
237  
238 =head1 NAME
239  
240 DBIx::Class::Storage::DBI::MSSQL - Base Class for Microsoft SQL Server support
241 in DBIx::Class
242  
243 =head1 SYNOPSIS
244  
245 This is the base class for Microsoft SQL Server support, used by
246 L<DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server> and
247 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
248  
249 =head1 IMPLEMENTATION NOTES
250  
251 =head2 IDENTITY information
252  
253 Microsoft SQL Server supports three methods of retrieving the IDENTITY
254 value for inserted row: IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY().
255 SCOPE_IDENTITY is used here because it is the safest. However, it must
256 be called is the same execute statement, not just the same connection.
257  
258 So, this implementation appends a SELECT SCOPE_IDENTITY() statement
259 onto each INSERT to accommodate that requirement.
260  
261 C<SELECT @@IDENTITY> can also be used by issuing:
262  
263 $self->_identity_method('@@identity');
264  
265 it will only be used if SCOPE_IDENTITY() fails.
266  
267 This is more dangerous, as inserting into a table with an on insert trigger that
268 inserts into another table with an identity will give erroneous results on
269 recent versions of SQL Server.
270  
271 =head2 identity insert
272  
273 Be aware that we have tried to make things as simple as possible for our users.
274 For MSSQL that means that when a user tries to create a row, while supplying an
275 explicit value for an autoincrementing column, we will try to issue the
276 appropriate database call to make this possible, namely C<SET IDENTITY_INSERT
277 $table_name ON>. Unfortunately this operation in MSSQL requires the
278 C<db_ddladmin> privilege, which is normally not included in the standard
279 write-permissions.
280  
281 =head2 Ordered Subselects
282  
283 If you attempted the following query (among many others) in Microsoft SQL
284 Server
285  
286 $rs->search ({}, {
287 prefetch => 'relation',
288 rows => 2,
289 offset => 3,
290 });
291  
292 You may be surprised to receive an exception. The reason for this is a quirk
293 in the MSSQL engine itself, and sadly doesn't have a sensible workaround due
294 to the way DBIC is built. DBIC can do truly wonderful things with the aid of
295 subselects, and does so automatically when necessary. The list of situations
296 when a subselect is necessary is long and still changes often, so it can not
297 be exhaustively enumerated here. The general rule of thumb is a joined
298 L<has_many|DBIx::Class::Relationship/has_many> relationship with limit/group
299 applied to the left part of the join.
300  
301 In its "pursuit of standards" Microsft SQL Server goes to great lengths to
302 forbid the use of ordered subselects. This breaks a very useful group of
303 searches like "Give me things number 4 to 6 (ordered by name), and prefetch
304 all their relations, no matter how many". While there is a hack which fools
305 the syntax checker, the optimizer may B<still elect to break the subselect>.
306 Testing has determined that while such breakage does occur (the test suite
307 contains an explicit test which demonstrates the problem), it is relative
308 rare. The benefits of ordered subselects are on the other hand too great to be
309 outright disabled for MSSQL.
310  
311 Thus compromise between usability and perfection is the MSSQL-specific
312 L<resultset attribute|DBIx::Class::ResultSet/ATTRIBUTES> C<unsafe_subselect_ok>.
313 It is deliberately not possible to set this on the Storage level, as the user
314 should inspect (and preferably regression-test) the return of every such
315 ResultSet individually. The example above would work if written like:
316  
317 $rs->search ({}, {
318 unsafe_subselect_ok => 1,
319 prefetch => 'relation',
320 rows => 2,
321 offset => 3,
322 });
323  
324 If it is possible to rewrite the search() in a way that will avoid the need
325 for this flag - you are urged to do so. If DBIC internals insist that an
326 ordered subselect is necessary for an operation, and you believe there is a
327 different/better way to get the same result - please file a bugreport.
328  
329 =head1 FURTHER QUESTIONS?
330  
331 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
332  
333 =head1 COPYRIGHT AND LICENSE
334  
335 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
336 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
337 redistribute it and/or modify it under the same terms as the
338 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.
« DBIx/Class/CDBICompat/LazyLoading.pm ↑ Summary DBIx/Class/Storage/DBI/SQLAnywhere/Cursor.pm »