DBIx::Class::Storage::User:Contributed PerlDBIx::Class::Storage::DBI::MSSQL(3)NAME
DBIx::Class::Storage::DBI::MSSQL - Base Class for Microsoft SQL Server
support in DBIx::Class
SYNOPSIS
This is the base class for Microsoft SQL Server support, used by
DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server and
DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server.
IMPLEMENTATION NOTES
IDENTITY information
Microsoft SQL Server supports three methods of retrieving the IDENTITY
value for inserted row: IDENT_CURRENT, @@IDENTITY, and
SCOPE_IDENTITY(). SCOPE_IDENTITY is used here because it is the
safest. However, it must be called is the same execute statement, not
just the same connection.
So, this implementation appends a SELECT SCOPE_IDENTITY() statement
onto each INSERT to accommodate that requirement.
"SELECT @@IDENTITY" can also be used by issuing:
$self->_identity_method('@@identity');
it will only be used if SCOPE_IDENTITY() fails.
This is more dangerous, as inserting into a table with an on insert
trigger that inserts into another table with an identity will give
erroneous results on recent versions of SQL Server.
identity insert
Be aware that we have tried to make things as simple as possible for
our users. For MSSQL that means that when a user tries to create a
row, while supplying an explicit value for an autoincrementing column,
we will try to issue the appropriate database call to make this
possible, namely "SET IDENTITY_INSERT $table_name ON". Unfortunately
this operation in MSSQL requires the "db_ddladmin" privilege, which is
normally not included in the standard write-permissions.
Ordered Subselects
If you attempted the following query (among many others) in Microsoft
SQL Server
$rs->search ({}, {
prefetch => 'relation',
rows => 2,
offset => 3,
});
You may be surprised to receive an exception. The reason for this is a
quirk in the MSSQL engine itself, and sadly doesn't have a sensible
workaround due to the way DBIC is built. DBIC can do truly wonderful
things with the aid of subselects, and does so automatically when
necessary. The list of situations when a subselect is necessary is long
and still changes often, so it can not be exhaustively enumerated here.
The general rule of thumb is a joined has_many relationship with
limit/group applied to the left part of the join.
In its "pursuit of standards" Microsft SQL Server goes to great lengths
to forbid the use of ordered subselects. This breaks a very useful
group of searches like "Give me things number 4 to 6 (ordered by name),
and prefetch all their relations, no matter how many". While there is a
hack which fools the syntax checker, the optimizer may still elect to
break the subselect. Testing has determined that while such breakage
does occur (the test suite contains an explicit test which demonstrates
the problem), it is relative rare. The benefits of ordered subselects
are on the other hand too great to be outright disabled for MSSQL.
Thus compromise between usability and perfection is the MSSQL-specific
resultset attribute "unsafe_subselect_ok". It is deliberately not
possible to set this on the Storage level, as the user should inspect
(and preferably regression-test) the return of every such ResultSet
individually. The example above would work if written like:
$rs->search ({}, {
unsafe_subselect_ok => 1,
prefetch => 'relation',
rows => 2,
offset => 3,
});
If it is possible to rewrite the search() in a way that will avoid the
need for this flag - you are urged to do so. If DBIC internals insist
that an ordered subselect is necessary for an operation, and you
believe there is a different/better way to get the same result - please
file a bugreport.
AUTHOR
See "AUTHOR" in DBIx::Class and "CONTRIBUTORS" in DBIx::Class.
LICENSE
You may distribute this code under the same terms as Perl itself.
perl v5.16.22012-08-2DBIx::Class::Storage::DBI::MSSQL(3)