package App::Schema;

=head2 set_limit_dialect_scope_guard

  {
    my $dialect_scope= $schema->set_limit_dialect_scope_guard('Top');
    # make DBIC searches with "SELECT TOP $n" syntax
    ...
  }
  # back to normal

By default, DBIC generates ugly nested queries with a row_id range selected in
the outer where clause.  This is actually the canonical way to do it on
SQL Server, but can create all sorts of trouble with namespace between the
different layers of the query.  Sometimes for sub-queries it is nice to have
DBIC generate "SELECT TOP 1 foo FROM bar" instead of the usual mess.  DBIC has
a C<< sql_maker->limit_dialect >> option for this, but there is no way to
constrain it to a single statement.

This "scope guard" object allows you to control the exact scope in which you
want DBIC to use the alternate dialect without worrying about exceptions.

=cut

# This scope guard object sets the "limit_dialect" of the storage engine back to whatever
# it was originally when the object goes out of scope.
{package MSSQL_LimitDialectFlipper;
   sub new {
      my ($class, $storage, $value)= @_;
      my $self= { storage => $storage, prev_value => $storage->sql_maker->limit_dialect };
      Scalar::Util::weaken($self->{storage});
      $storage->sql_maker->limit_dialect($value);
      bless $self, $class
   }
   sub DESTROY {
      $_[0]{storage}->sql_maker->limit_dialect($_[0]{prev_value}) if $_[0]{storage};
   }
}

sub set_limit_dialect_scope_guard {
   my ($self, $new_dialect_value)= @_;
   MSSQL_LimitDialectFlipper->new($self->storage, $new_dialect_value);
}