1 package DBIx::Class::Storage::DBI::MSSQL;
7 DBIx::Class::Storage::DBI::UniqueIdentifier
8 DBIx::Class::Storage::DBI::IdentityInsert
13 use List::Util 'first';
16 __PACKAGE__->mk_group_accessors(simple => qw/
17 _identity _identity_method _no_scope_identity_query
20 __PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MSSQL');
22 __PACKAGE__->sql_quote_char([qw/[ ]/]);
24 __PACKAGE__->datetime_parser_type (
25 'DBIx::Class::Storage::DBI::MSSQL::DateTime::Format'
28 __PACKAGE__->new_guid('NEWID()');
30 sub _sql_server_2005_or_higher {
31 if (exists $_[0]->_server_info->{normalized_dbms_version}) {
32 if ($_[0]->_server_info->{normalized_dbms_version} >= 9) {
41 sub _prep_for_execute {
43 my ($op, $ident, $args) = @_;
45 # cast MONEY values properly
46 if ($op eq 'insert' || $op eq 'update') {
47 my $fields = $args->[0];
49 my $colinfo = $ident->columns_info([keys %$fields]);
51 for my $col (keys %$fields) {
52 # $ident is a result source object with INSERT/UPDATE ops
54 $colinfo->{$col}{data_type}
56 $colinfo->{$col}{data_type} =~ /^money\z/i
58 my $val = $fields->{$col};
59 $fields->{$col} = \['CAST(? AS MONEY)', [ $col => $val ]];
64 my ($sql, $bind) = $self->next::method (@_);
66 # SELECT SCOPE_IDENTITY only works within a statement scope. We
67 # must try to always use this particular idiom first, as it is the
68 # only one that guarantees retrieving the correct id under high
69 # concurrency. When this fails we will fall back to whatever secondary
70 # retrieval method is specified in _identity_method, but at this
71 # point we don't have many guarantees we will get what we expected.
72 # http://msdn.microsoft.com/en-us/library/ms190315.aspx
73 # http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx
74 if ($self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query) {
75 $sql .= "\nSELECT SCOPE_IDENTITY()";
84 # always list ctx - we need the $sth
85 my ($rv, $sth, @bind) = $self->next::method(@_);
87 if ($self->_perform_autoinc_retrieval) {
89 # attempt to bring back the result of SELECT SCOPE_IDENTITY() we tacked
90 # on in _prep_for_execute above
93 # we didn't even try on ftds
94 unless ($self->_no_scope_identity_query) {
95 ($identity) = try { $sth->fetchrow_array };
99 # SCOPE_IDENTITY failed, but we can do something else
100 if ( (! $identity) && $self->_identity_method) {
101 ($identity) = $self->_dbh->selectrow_array(
102 'select ' . $self->_identity_method
106 $self->_identity($identity);
109 return wantarray ? ($rv, $sth, @bind) : $rv;
112 sub last_insert_id { shift->_identity }
115 # MSSQL is retarded wrt ordered subselects. One needs to add a TOP
116 # to *all* subqueries, but one also *can't* use TOP 100 PERCENT
117 # http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
119 sub _select_args_to_query {
120 #my ($self, $ident, $select, $cond, $attrs) = @_;
124 my $sql_bind = $self->next::method (@_);
126 # see if this is an ordered subquery
128 $$sql_bind->[0] !~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
130 scalar $self->_extract_order_criteria ($attrs->{order_by})
132 $self->throw_exception(
133 'An ordered subselect encountered - this is not safe! Please see "Ordered Subselects" in DBIx::Class::Storage::DBI::MSSQL'
134 ) unless $attrs->{unsafe_subselect_ok};
136 $$sql_bind->[0] =~ s/^ \s* \( \s* SELECT (?=\s) / '(SELECT TOP ' . $self->sql_maker->__max_int /exi;
143 # savepoint syntax is the same as in Sybase ASE
145 sub _exec_svp_begin {
146 my ($self, $name) = @_;
148 $self->_dbh->do("SAVE TRANSACTION $name");
151 # A new SAVE TRANSACTION with the same name releases the previous one.
152 sub _exec_svp_release { 1 }
154 sub _exec_svp_rollback {
155 my ($self, $name) = @_;
157 $self->_dbh->do("ROLLBACK TRANSACTION $name");
160 sub sqlt_type { 'SQLServer' }
162 sub sql_limit_dialect {
165 my $supports_rno = $self->_sql_server_2005_or_higher;
167 unless (defined $supports_rno) {
168 # User is connecting via DBD::Sybase and has no permission to run
169 # stored procedures like xp_msver, or version detection failed for some
171 # So, we use a query to check if RNO is implemented.
173 $self->_get_dbh->selectrow_array('SELECT row_number() OVER (ORDER BY rand())');
178 return $supports_rno ? 'RowNumberOver' : 'Top';
184 my $dbh = $self->_dbh or return 0;
186 local $dbh->{RaiseError} = 1;
187 local $dbh->{PrintError} = 0;
190 $dbh->do('select 1');
197 package # hide from PAUSE
198 DBIx::Class::Storage::DBI::MSSQL::DateTime::Format;
200 my $datetime_format = '%Y-%m-%d %H:%M:%S.%3N'; # %F %T
201 my $smalldatetime_format = '%Y-%m-%d %H:%M:%S';
203 my ($datetime_parser, $smalldatetime_parser);
207 require DateTime::Format::Strptime;
208 $datetime_parser ||= DateTime::Format::Strptime->new(
209 pattern => $datetime_format,
212 return $datetime_parser->parse_datetime(shift);
215 sub format_datetime {
217 require DateTime::Format::Strptime;
218 $datetime_parser ||= DateTime::Format::Strptime->new(
219 pattern => $datetime_format,
222 return $datetime_parser->format_datetime(shift);
225 sub parse_smalldatetime {
227 require DateTime::Format::Strptime;
228 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
229 pattern => $smalldatetime_format,
232 return $smalldatetime_parser->parse_datetime(shift);
235 sub format_smalldatetime {
237 require DateTime::Format::Strptime;
238 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
239 pattern => $smalldatetime_format,
242 return $smalldatetime_parser->format_datetime(shift);
249 DBIx::Class::Storage::DBI::MSSQL - Base Class for Microsoft SQL Server support
254 This is the base class for Microsoft SQL Server support, used by
255 L<DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server> and
256 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
258 =head1 IMPLEMENTATION NOTES
260 =head2 IDENTITY information
262 Microsoft SQL Server supports three methods of retrieving the IDENTITY
263 value for inserted row: IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY().
264 SCOPE_IDENTITY is used here because it is the safest. However, it must
265 be called is the same execute statement, not just the same connection.
267 So, this implementation appends a SELECT SCOPE_IDENTITY() statement
268 onto each INSERT to accommodate that requirement.
270 C<SELECT @@IDENTITY> can also be used by issuing:
272 $self->_identity_method('@@identity');
274 it will only be used if SCOPE_IDENTITY() fails.
276 This is more dangerous, as inserting into a table with an on insert trigger that
277 inserts into another table with an identity will give erroneous results on
278 recent versions of SQL Server.
280 =head2 identity insert
282 Be aware that we have tried to make things as simple as possible for our users.
283 For MSSQL that means that when a user tries to create a row, while supplying an
284 explicit value for an autoincrementing column, we will try to issue the
285 appropriate database call to make this possible, namely C<SET IDENTITY_INSERT
286 $table_name ON>. Unfortunately this operation in MSSQL requires the
287 C<db_ddladmin> privilege, which is normally not included in the standard
290 =head2 Ordered Subselects
292 If you attempted the following query (among many others) in Microsoft SQL
296 prefetch => 'relation',
301 You may be surprised to receive an exception. The reason for this is a quirk
302 in the MSSQL engine itself, and sadly doesn't have a sensible workaround due
303 to the way DBIC is built. DBIC can do truly wonderful things with the aid of
304 subselects, and does so automatically when necessary. The list of situations
305 when a subselect is necessary is long and still changes often, so it can not
306 be exhaustively enumerated here. The general rule of thumb is a joined
307 L<has_many|DBIx::Class::Relationship/has_many> relationship with limit/group
308 applied to the left part of the join.
310 In its "pursuit of standards" Microsft SQL Server goes to great lengths to
311 forbid the use of ordered subselects. This breaks a very useful group of
312 searches like "Give me things number 4 to 6 (ordered by name), and prefetch
313 all their relations, no matter how many". While there is a hack which fools
314 the syntax checker, the optimizer may B<still elect to break the subselect>.
315 Testing has determined that while such breakage does occur (the test suite
316 contains an explicit test which demonstrates the problem), it is relative
317 rare. The benefits of ordered subselects are on the other hand too great to be
318 outright disabled for MSSQL.
320 Thus compromise between usability and perfection is the MSSQL-specific
321 L<resultset attribute|DBIx::Class::ResultSet/ATTRIBUTES> C<unsafe_subselect_ok>.
322 It is deliberately not possible to set this on the Storage level, as the user
323 should inspect (and preferably regression-test) the return of every such
324 ResultSet individually. The example above would work if written like:
327 unsafe_subselect_ok => 1,
328 prefetch => 'relation',
333 If it is possible to rewrite the search() in a way that will avoid the need
334 for this flag - you are urged to do so. If DBIC internals insist that an
335 ordered subselect is necessary for an operation, and you believe there is a
336 different/better way to get the same result - please file a bugreport.
340 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
344 You may distribute this code under the same terms as Perl itself.