X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F746mssql.t;h=ca92a41a9603f5492498023b902ea8982a010d35;hb=fecb38cba68186d579ef96f89bf6ce557e55ab1c;hp=88a6f7defc0abe3445ff6aeef6e3e91ef24fea9b;hpb=1f36ab6755c16cae2d4c3d0099d68cd11948f0ff;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/746mssql.t b/t/746mssql.t
index 88a6f7d..ca92a41 100644
--- a/t/746mssql.t
+++ b/t/746mssql.t
@@ -33,6 +33,12 @@ isa_ok( $schema->storage, 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server
   ok (! $schema2->storage->connected, 'a re-connected cloned schema starts unconnected');
 }
 
+$schema->storage->_dbh->disconnect;
+
+lives_ok {
+  $schema->storage->dbh_do(sub { $_[1]->do('select 1') })
+} '_ping works';
+
 $schema->storage->dbh_do (sub {
     my ($storage, $dbh) = @_;
     eval { $dbh->do("DROP TABLE artist") };
@@ -178,10 +184,10 @@ is $rs->find($row->id)->amount, undef,'updated money value to NULL round-trip';
 
 $schema->storage->dbh_do (sub {
     my ($storage, $dbh) = @_;
-    eval { $dbh->do("DROP TABLE Owners") };
-    eval { $dbh->do("DROP TABLE Books") };
+    eval { $dbh->do("DROP TABLE owners") };
+    eval { $dbh->do("DROP TABLE books") };
     $dbh->do(<<'SQL');
-CREATE TABLE Books (
+CREATE TABLE books (
    id INT IDENTITY (1, 1) NOT NULL,
    source VARCHAR(100),
    owner INT,
@@ -189,7 +195,7 @@ CREATE TABLE Books (
    price INT NULL
 )
 
-CREATE TABLE Owners (
+CREATE TABLE owners (
    id INT IDENTITY (1, 1) NOT NULL,
    name VARCHAR(100),
 )
@@ -205,10 +211,10 @@ lives_ok ( sub {
     [qw/1   wiggle/],
     [qw/2   woggle/],
     [qw/3   boggle/],
-    [qw/4   fREW/],
-    [qw/5   fRIOUX/],
-    [qw/6   fROOH/],
-    [qw/7   fRUE/],
+    [qw/4   fRIOUX/],
+    [qw/5   fRUE/],
+    [qw/6   fREW/],
+    [qw/7   fROOH/],
     [qw/8   fISMBoC/],
     [qw/9   station/],
     [qw/10   mirror/],
@@ -220,11 +226,12 @@ lives_ok ( sub {
   ]);
 }, 'populate with PKs supplied ok' );
 
+
 lives_ok (sub {
   # start a new connection, make sure rebless works
   # test an insert with a supplied identity, followed by one without
   my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
-  for (1..2) {
+  for (2, 1) {
     my $id = $_ * 20 ;
     $schema->resultset ('Owners')->create ({ id => $id, name => "troglodoogle $id" });
     $schema->resultset ('Owners')->create ({ name => "troglodoogle " . ($id + 1) });
@@ -254,20 +261,129 @@ lives_ok ( sub {
   ]);
 }, 'populate without PKs supplied ok' );
 
-# make sure ordered subselects work
+# plain ordered subqueries throw
+throws_ok (sub {
+  $schema->resultset('Owners')->search ({}, { order_by => 'name' })->as_query
+}, qr/ordered subselect encountered/, 'Ordered Subselect detection throws ok');
+
+# make sure ordered subselects *somewhat* work
 {
+  my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 });
+
+  my $al = $owners->current_source_alias;
+  my $sealed_owners = $owners->result_source->resultset->search (
+    {},
+    {
+      alias => $al,
+      from => [{
+        -alias => $al,
+        -source_handle => $owners->result_source->handle,
+        $al => $owners->as_query,
+      }],
+    },
+  );
+
+  is_deeply (
+    [ map { $_->name } ($sealed_owners->all) ],
+    [ map { $_->name } ($owners->all) ],
+    'Sort preserved from within a subquery',
+  );
+}
+
+TODO: {
+  local $TODO = "This porbably will never work, but it isn't critical either afaik";
+
   my $book_owner_ids = $schema->resultset ('BooksInLibrary')
-                               ->search ({}, { join => 'owner', distinct => 1, order_by => { -desc => 'owner'} })
+                               ->search ({}, { join => 'owner', distinct => 1, order_by => 'owner.name', unsafe_subselect_ok => 1 })
                                 ->get_column ('owner');
 
-  my $owners = $schema->resultset ('Owners')->search ({
+  my $book_owners = $schema->resultset ('Owners')->search ({
     id => { -in => $book_owner_ids->as_query }
   });
 
-  is ($owners->count, 8, 'Correct amount of book owners');
-  is ($owners->all, 8, 'Correct amount of book owner objects');
+  is_deeply (
+    [ map { $_->id } ($book_owners->all) ],
+    [ $book_owner_ids->all ],
+    'Sort is preserved across IN subqueries',
+  );
+}
+
+# This is known not to work - thus the negative test
+{
+  my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 });
+  my $corelated_owners = $owners->result_source->resultset->search (
+    {
+      id => { -in => $owners->get_column('id')->as_query },
+    },
+    {
+      order_by => 'name' #reorder because of what is shown above
+    },
+  );
+
+  cmp_ok (
+    join ("\x00", map { $_->name } ($corelated_owners->all) ),
+      'ne',
+    join ("\x00", map { $_->name } ($owners->all) ),
+    'Sadly sort not preserved from within a corelated subquery',
+  );
+
+  cmp_ok (
+    join ("\x00", sort map { $_->name } ($corelated_owners->all) ),
+      'ne',
+    join ("\x00", sort map { $_->name } ($owners->all) ),
+    'Which in fact gives a completely wrong dataset',
+  );
+}
+
+
+# make sure right-join-side single-prefetch ordering limit works
+{
+  my $rs = $schema->resultset ('BooksInLibrary')->search (
+    {
+      'owner.name' => { '!=', 'woggle' },
+    },
+    {
+      prefetch => 'owner',
+      order_by => 'owner.name',
+    }
+  );
+  # this is the order in which they should come from the above query
+  my @owner_names = qw/boggle fISMBoC fREW fRIOUX fROOH fRUE wiggle wiggle/;
+
+  is ($rs->all, 8, 'Correct amount of objects from right-sorted joined resultset');
+  is_deeply (
+    [map { $_->owner->name } ($rs->all) ],
+    \@owner_names,
+    'Rows were properly ordered'
+  );
+
+  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2, unsafe_subselect_ok => 1});
+  is ($limited_rs->count, 6, 'Correct count of limited right-sorted joined resultset');
+  is ($limited_rs->count_rs->next, 6, 'Correct count_rs of limited right-sorted joined resultset');
+
+  my $queries;
+  $schema->storage->debugcb(sub { $queries++; });
+  $schema->storage->debug(1);
+
+  is_deeply (
+    [map { $_->owner->name } ($limited_rs->all) ],
+    [@owner_names[2 .. 7]],
+    'Limited rows were properly ordered'
+  );
+  is ($queries, 1, 'Only one query with prefetch');
+
+  $schema->storage->debugcb(undef);
+  $schema->storage->debug(0);
+
+
+  is_deeply (
+    [map { $_->name } ($limited_rs->search_related ('owner')->all) ],
+    [@owner_names[2 .. 7]],
+    'Rows are still properly ordered after search_related'
+  );
 }
 
+
 #
 # try a prefetch on tables with identically named columns
 #
@@ -287,6 +403,7 @@ $schema->storage->_sql_maker->{name_sep} = '.';
       prefetch => 'books',
       order_by => { -asc => \['name + ?', [ test => 'xxx' ]] }, # test bindvar propagation
       rows     => 3,  # 8 results total
+      unsafe_subselect_ok => 1,
     },
   );
 
@@ -314,7 +431,8 @@ $schema->storage->_sql_maker->{name_sep} = '.';
       having => \['1 = ?', [ test => 1 ] ], #test having propagation
       prefetch => 'owner',
       rows     => 2,  # 3 results total
-      order_by => { -desc => 'owner' },
+      order_by => { -desc => 'me.owner' },
+      unsafe_subselect_ok => 1,
     },
   );
 
@@ -337,118 +455,13 @@ $schema->storage->_sql_maker->{name_sep} = '.';
   is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
 }
 
-# make sure right-join-side single-prefetch ordering limit works
-{
-  my $rs = $schema->resultset ('BooksInLibrary')->search (
-    {
-      'owner.name' => { '!=', 'woggle' },
-    },
-    {
-      prefetch => 'owner',
-      order_by => { -desc => 'owner.name' },
-    }
-  );
-
-  is ($rs->all, 8, 'Correct amount of objects from right-sorted joined resultset');
-  is_deeply (
-    [map { $_->owner->name } ($rs->all) ],
-    [qw/wiggle wiggle fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Rows were properly ordered'
-  );
-
-  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2});
-  is ($limited_rs->count, 6, 'Correct count of limited right-sorted joined resultset');
-  is ($limited_rs->count_rs->next, 6, 'Correct count_rs of limited right-sorted joined resultset');
-
-  my $queries;
-  $schema->storage->debugcb(sub { $queries++; });
-  $schema->storage->debug(1);
-
-  is_deeply (
-    [map { $_->owner->name } ($limited_rs->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Limited rows were properly ordered'
-  );
-  is ($queries, 1, 'Only one query with prefetch');
-
-  $schema->storage->debugcb(undef);
-  $schema->storage->debug(0);
-
-
-  is_deeply (
-    [map { $_->name } ($limited_rs->search_related ('owner')->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
-    'Rows are still properly ordered after search_related'
-  );
-
-
-# Just to aid bug-hunting, delete block before merging
-{
-
-=begin
-
-Alan's SQL:
-
-      SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined
-        FROM (
-          SELECT *
-            FROM (
-              SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index
-                FROM (
-                  SELECT me.id, me.surveyor_id, me.survey_site_id, me.year
-                    FROM (
-                      SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year
-                        FROM surveyors_survey_sites me
-                        JOIN surveyors surveyor ON surveyor.id = me.surveyor_id
-                      ORDER BY surveyor.name
-                    ) me
-                ) orig_query
-            ) rno_subq
-          WHERE rno__row__index BETWEEN 136 AND 150 
-        ) me
-        JOIN surveyors surveyor ON surveyor.id = me.surveyor_id
-      ORDER BY surveyor.name
-=cut
-
-  is_same_sql_bind (
-    $limited_rs->as_query,
-    '(
-      SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[id], [owner].[name]
-        FROM (
-          SELECT *
-            FROM (
-              SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index 
-                FROM (
-                  SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
-                    FROM (
-                      SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
-                        FROM [books] [me]
-                        JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
-                      WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-                      ORDER BY [owner].[name] DESC
-                    ) [me]
-                ) [me]
-            ) rno_subq
-          WHERE rno__row__index BETWEEN 3 AND 9
-        ) [me]
-        JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
-      WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-      ORDER BY [owner].[name] DESC
-    )',
-    [ ([ 'owner.name' => 'woggle' ], [ source => 'Library' ]) x 2 ],
-    'Expected SQL executed',
-  );
-}
-
-}
-
 done_testing;
 
 # clean up our mess
 END {
   if (my $dbh = eval { $schema->storage->_dbh }) {
     eval { $dbh->do("DROP TABLE $_") }
-      for qw/artist money_test Books Owners/;
+      for qw/artist money_test books owners/;
   }
 }
 # vim:sw=2 sts=2