use strict;
use warnings;
-use base qw/DBIx::Class::Storage::DBI::AmbiguousGlob DBIx::Class::Storage::DBI/;
+use base qw/DBIx::Class::Storage::DBI::UniqueIdentifier/;
use mro 'c3';
-
-use List::Util();
+use Try::Tiny;
+use List::Util 'first';
+use namespace::clean;
__PACKAGE__->mk_group_accessors(simple => qw/
- _identity _identity_method
+ _identity _identity_method _pre_insert_sql _post_insert_sql
/);
-__PACKAGE__->sql_maker_class('DBIx::Class::SQLAHacks::MSSQL');
+__PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MSSQL');
-sub _set_identity_insert {
- my ($self, $table) = @_;
+__PACKAGE__->sql_quote_char([qw/[ ]/]);
- my $sql = sprintf (
- 'SET IDENTITY_INSERT %s ON',
- $self->sql_maker->_quote ($table),
- );
+__PACKAGE__->datetime_parser_type (
+ 'DBIx::Class::Storage::DBI::MSSQL::DateTime::Format'
+);
- my $dbh = $self->_get_dbh;
- eval { $dbh->do ($sql) };
- if ($@) {
- $self->throw_exception (sprintf "Error executing '%s': %s",
- $sql,
- $dbh->errstr,
- );
- }
-}
-sub _unset_identity_insert {
+__PACKAGE__->new_guid('NEWID()');
+
+sub _set_identity_insert {
my ($self, $table) = @_;
- my $sql = sprintf (
- 'SET IDENTITY_INSERT %s OFF',
- $self->sql_maker->_quote ($table),
- );
+ my $stmt = 'SET IDENTITY_INSERT %s %s';
+ $table = $self->sql_maker->_quote($table);
- my $dbh = $self->_get_dbh;
- $dbh->do ($sql);
+ $self->_pre_insert_sql (sprintf $stmt, $table, 'ON');
+ $self->_post_insert_sql(sprintf $stmt, $table, 'OFF');
}
sub insert_bulk {
my $self = shift;
my ($source, $cols, $data) = @_;
- my $is_identity_insert = (List::Util::first
- { $source->column_info ($_)->{is_auto_increment} }
- (@{$cols})
- )
- ? 1
- : 0;
+ my $is_identity_insert =
+ (first { $_->{is_auto_increment} } values %{ $source->columns_info($cols) } )
+ ? 1
+ : 0
+ ;
if ($is_identity_insert) {
$self->_set_identity_insert ($source->name);
}
$self->next::method(@_);
-
- if ($is_identity_insert) {
- $self->_unset_identity_insert ($source->name);
- }
}
-# support MSSQL GUID column types
-
sub insert {
my $self = shift;
my ($source, $to_insert) = @_;
my $supplied_col_info = $self->_resolve_column_info($source, [keys %$to_insert] );
- my %guid_cols;
- my @pk_cols = $source->primary_columns;
- my %pk_cols;
- @pk_cols{@pk_cols} = ();
-
- my @pk_guids = grep {
- $source->column_info($_)->{data_type}
- &&
- $source->column_info($_)->{data_type} =~ /^uniqueidentifier/i
- } @pk_cols;
-
- my @auto_guids = grep {
- $source->column_info($_)->{data_type}
- &&
- $source->column_info($_)->{data_type} =~ /^uniqueidentifier/i
- &&
- $source->column_info($_)->{auto_nextval}
- } grep { not exists $pk_cols{$_} } $source->columns;
-
- my @get_guids_for =
- grep { not exists $to_insert->{$_} } (@pk_guids, @auto_guids);
-
- my $updated_cols = {};
-
- for my $guid_col (@get_guids_for) {
- my ($new_guid) = $self->_get_dbh->selectrow_array('SELECT NEWID()');
- $updated_cols->{$guid_col} = $to_insert->{$guid_col} = $new_guid;
- }
-
- my $is_identity_insert = (List::Util::first { $_->{is_auto_increment} } (values %$supplied_col_info) )
- ? 1
- : 0;
+ my $is_identity_insert =
+ (first { $_->{is_auto_increment} } values %$supplied_col_info) ? 1 : 0;
if ($is_identity_insert) {
$self->_set_identity_insert ($source->name);
}
- $updated_cols = { %$updated_cols, %{ $self->next::method(@_) } };
-
- if ($is_identity_insert) {
- $self->_unset_identity_insert ($source->name);
- }
-
+ my $updated_cols = $self->next::method(@_);
return $updated_cols;
}
sub _prep_for_execute {
my $self = shift;
- my ($op, $extra_bind, $ident, $args) = @_;
+ my ($op, $ident, $args) = @_;
# cast MONEY values properly
if ($op eq 'insert' || $op eq 'update') {
my $fields = $args->[0];
+ my $colinfo = $ident->columns_info([keys %$fields]);
+
for my $col (keys %$fields) {
# $ident is a result source object with INSERT/UPDATE ops
- if ($ident->column_info ($col)->{data_type}
- &&
- $ident->column_info ($col)->{data_type} =~ /^money\z/i) {
+ if (
+ $colinfo->{$col}{data_type}
+ &&
+ $colinfo->{$col}{data_type} =~ /^money\z/i
+ ) {
my $val = $fields->{$col};
$fields->{$col} = \['CAST(? AS MONEY)', [ $col => $val ]];
}
my ($sql, $bind) = $self->next::method (@_);
if ($op eq 'insert') {
- $sql .= ';SELECT SCOPE_IDENTITY()';
-
+ if (my $prepend = $self->_pre_insert_sql) {
+ $sql = "${prepend}\n${sql}";
+ $self->_pre_insert_sql(undef);
+ }
+ if (my $append = $self->_post_insert_sql) {
+ $sql = "${sql}\n${append}";
+ $self->_post_insert_sql(undef);
+ }
+ $sql .= "\nSELECT SCOPE_IDENTITY()";
}
return ($sql, $bind);
my $self = shift;
my ($op) = @_;
- my ($rv, $sth, @bind) = $self->dbh_do($self->can('_dbh_execute'), @_);
+ my ($rv, $sth, @bind) = $self->next::method(@_);
if ($op eq 'insert') {
# this should bring back the result of SELECT SCOPE_IDENTITY() we tacked
# on in _prep_for_execute above
- my ($identity) = eval { $sth->fetchrow_array };
+ my ($identity) = try { $sth->fetchrow_array };
# SCOPE_IDENTITY failed, but we can do something else
if ( (! $identity) && $self->_identity_method) {
sub last_insert_id { shift->_identity }
#
-# MSSQL is retarded wrt ordered subselects. One needs to add a TOP 100%
-# to *all* subqueries, do it here.
+# MSSQL is retarded wrt ordered subselects. One needs to add a TOP
+# to *all* subqueries, but one also *can't* use TOP 100 PERCENT
+# http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
#
sub _select_args_to_query {
my $self = shift;
# see if this is an ordered subquery
my $attrs = $_[3];
- if ( scalar $self->sql_maker->_order_by_chunks ($attrs->{order_by}) ) {
- $sql =~ s/^ \s* SELECT \s/SELECT TOP 100 PERCENT /xi;
+ if (
+ $sql !~ /^ \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
+ &&
+ scalar $self->_extract_order_criteria ($attrs->{order_by})
+ ) {
+ $self->throw_exception(
+ 'An ordered subselect encountered - this is not safe! Please see "Ordered Subselects" in DBIx::Class::Storage::DBI::MSSQL
+ ') unless $attrs->{unsafe_subselect_ok};
+ my $max = $self->sql_maker->__max_int;
+ $sql =~ s/^ \s* SELECT \s/SELECT TOP $max /xi;
}
return wantarray
$self->_get_dbh->do("ROLLBACK TRANSACTION $name");
}
-sub build_datetime_parser {
- my $self = shift;
- my $type = "DateTime::Format::Strptime";
- eval "use ${type}";
- $self->throw_exception("Couldn't load ${type}: $@") if $@;
- return $type->new( pattern => '%Y-%m-%d %H:%M:%S' ); # %F %T
-}
-
sub sqlt_type { 'SQLServer' }
-sub _get_mssql_version {
+sub sql_limit_dialect {
my $self = shift;
- my $data = $self->_get_dbh->selectrow_hashref('xp_msver ProductVersion');
+ my $supports_rno = 0;
- if ($data->{Character_Value} =~ /^(\d+)\./) {
- return $1;
- } else {
- $self->throw_exception(q{your MSSQL server doesn't have a version!});
+ if (exists $self->_server_info->{normalized_dbms_version}) {
+ $supports_rno = 1 if $self->_server_info->{normalized_dbms_version} >= 9;
}
+ else {
+ # User is connecting via DBD::Sybase and has no permission to run
+ # stored procedures like xp_msver, or version detection failed for some
+ # other reason.
+ # So, we use a query to check if RNO is implemented.
+ try {
+ $self->_get_dbh->selectrow_array('SELECT row_number() OVER (ORDER BY rand())');
+ $supports_rno = 1;
+ };
+ }
+
+ return $supports_rno ? 'RowNumberOver' : 'Top';
}
-sub _sql_maker_opts {
- my ( $self, $opts ) = @_;
+sub _ping {
+ my $self = shift;
- if ( $opts ) {
- $self->{_sql_maker_opts} = { %$opts };
- }
+ my $dbh = $self->_dbh or return 0;
- my $version = $self->_get_mssql_version;
+ local $dbh->{RaiseError} = 1;
+ local $dbh->{PrintError} = 0;
- return {
- limit_dialect => ($version >= 9 ? 'RowNumberOver' : 'Top'),
- %{$self->{_sql_maker_opts}||{}}
+ return try {
+ $dbh->do('select 1');
+ 1;
+ } catch {
+ 0;
};
}
+package # hide from PAUSE
+ DBIx::Class::Storage::DBI::MSSQL::DateTime::Format;
+
+my $datetime_format = '%Y-%m-%d %H:%M:%S.%3N'; # %F %T
+my $smalldatetime_format = '%Y-%m-%d %H:%M:%S';
+
+my ($datetime_parser, $smalldatetime_parser);
+
+sub parse_datetime {
+ shift;
+ require DateTime::Format::Strptime;
+ $datetime_parser ||= DateTime::Format::Strptime->new(
+ pattern => $datetime_format,
+ on_error => 'croak',
+ );
+ return $datetime_parser->parse_datetime(shift);
+}
+
+sub format_datetime {
+ shift;
+ require DateTime::Format::Strptime;
+ $datetime_parser ||= DateTime::Format::Strptime->new(
+ pattern => $datetime_format,
+ on_error => 'croak',
+ );
+ return $datetime_parser->format_datetime(shift);
+}
+
+sub parse_smalldatetime {
+ shift;
+ require DateTime::Format::Strptime;
+ $smalldatetime_parser ||= DateTime::Format::Strptime->new(
+ pattern => $smalldatetime_format,
+ on_error => 'croak',
+ );
+ return $smalldatetime_parser->parse_datetime(shift);
+}
+
+sub format_smalldatetime {
+ shift;
+ require DateTime::Format::Strptime;
+ $smalldatetime_parser ||= DateTime::Format::Strptime->new(
+ pattern => $smalldatetime_format,
+ on_error => 'croak',
+ );
+ return $smalldatetime_parser->format_datetime(shift);
+}
+
1;
=head1 NAME
C<db_ddladmin> privilege, which is normally not included in the standard
write-permissions.
+=head2 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
+L<has_many|DBIx::Class::Relationship/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 B<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
+L<resultset attribute|DBIx::Class::ResultSet/ATTRIBUTES> C<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.
+
=head1 AUTHOR
-See L<DBIx::Class/CONTRIBUTORS>.
+See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
=head1 LICENSE
projects / dbsrgits/DBIx-Class.git / blobdiff