X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F746mssql.t;h=0d530681698006fb2e5dd5a6d123caecf6640aa5;hb=aafe4014b04490189cb7a43087d3dcfc73287ec4;hp=40a61575f8e1b0dbfacbe6a4bf6a1d8e59b54a5e;hpb=a33d2444e0981129eb060521de27c5e2104f37ba;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/746mssql.t b/t/746mssql.t
index 40a6157..0d53068 100644
--- a/t/746mssql.t
+++ b/t/746mssql.t
@@ -12,8 +12,6 @@ my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PA
 plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ODBC_DSN}, _USER and _PASS to run this test'
   unless ($dsn && $user);
 
-plan tests => 39;
-
 DBICTest::Schema->load_classes('ArtistGUID');
 my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
 
@@ -30,6 +28,11 @@ my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
 
 isa_ok( $schema->storage, 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' );
 
+{
+  my $schema2 = $schema->connect ($schema->storage->connect_info);
+  ok (! $schema2->storage->connected, 'a re-connected cloned schema starts unconnected');
+}
+
 $schema->storage->dbh_do (sub {
     my ($storage, $dbh) = @_;
     eval { $dbh->do("DROP TABLE artist") };
@@ -145,14 +148,11 @@ $schema->storage->dbh_do (sub {
     my ($storage, $dbh) = @_;
     eval { $dbh->do("DROP TABLE money_test") };
     $dbh->do(<<'SQL');
-
 CREATE TABLE money_test (
    id INT IDENTITY PRIMARY KEY,
    amount MONEY NULL
 )
-
 SQL
-
 });
 
 my $rs = $schema->resultset('Money');
@@ -198,15 +198,17 @@ SQL
 });
 
 lives_ok ( sub {
+  # start a new connection, make sure rebless works
+  my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
   $schema->populate ('Owners', [
     [qw/id  name  /],
     [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/],
@@ -218,25 +220,54 @@ 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) {
+    my $id = $_ * 20 ;
+    $schema->resultset ('Owners')->create ({ id => $id, name => "troglodoogle $id" });
+    $schema->resultset ('Owners')->create ({ name => "troglodoogle " . ($id + 1) });
+  }
+}, 'create with/without PKs ok' );
+
+is ($schema->resultset ('Owners')->count, 19, 'owner rows really in db' );
+
 lives_ok ( sub {
+  # start a new connection, make sure rebless works
+  my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
   $schema->populate ('BooksInLibrary', [
     [qw/source  owner title   /],
-    [qw/Library 1     secrets0/],
-    [qw/Library 1     secrets1/],
+    [qw/Library 7     secrets10/],
     [qw/Eatery  1     secrets2/],
-    [qw/Library 2     secrets3/],
     [qw/Library 3     secrets4/],
-    [qw/Eatery  3     secrets5/],
-    [qw/Library 4     secrets6/],
-    [qw/Library 5     secrets7/],
     [qw/Eatery  5     secrets8/],
-    [qw/Library 6     secrets9/],
-    [qw/Library 7     secrets10/],
+    [qw/Library 5     secrets7/],
+    [qw/Library 2     secrets3/],
     [qw/Eatery  7     secrets11/],
+    [qw/Library 4     secrets6/],
+    [qw/Library 1     secrets0/],
+    [qw/Eatery  3     secrets5/],
+    [qw/Library 6     secrets9/],
+    [qw/Library 1     secrets1/],
     [qw/Library 8     secrets12/],
   ]);
 }, 'populate without PKs supplied ok' );
 
+# make sure ordered subselects work
+{
+  my $book_owner_ids = $schema->resultset ('BooksInLibrary')
+                               ->search ({}, { join => 'owner', distinct => 1, order_by => { -desc => 'owner'} })
+                                ->get_column ('owner');
+
+  my $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');
+}
+
 #
 # try a prefetch on tables with identically named columns
 #
@@ -247,87 +278,174 @@ $schema->storage->_sql_maker->{name_sep} = '.';
 
 {
   # try a ->has_many direction
-  my $owners = $schema->resultset ('Owners')->search ({
-      'books.id' => { '!=', undef }
-    }, {
+  my $owners = $schema->resultset ('Owners')->search (
+    {
+      'books.id' => { '!=', undef },
+      'me.name' => { '!=', 'somebogusstring' },
+    },
+    {
       prefetch => 'books',
-      order_by => 'name',
+      order_by => { -asc => \['name + ?', [ test => 'xxx' ]] }, # test bindvar propagation
       rows     => 3,  # 8 results total
-    });
+    },
+  );
+
+  my ($sql, @bind) = @${$owners->page(3)->as_query};
+  is_deeply (
+    \@bind,
+    [ ([ 'me.name' => 'somebogusstring' ], [ test => 'xxx' ]) x 2 ],  # double because of the prefetch subq
+  );
 
   is ($owners->page(1)->all, 3, 'has_many prefetch returns correct number of rows');
   is ($owners->page(1)->count, 3, 'has-many prefetch returns correct count');
 
-  TODO: {
-    local $TODO = 'limit past end of resultset problem';
-    is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows');
-    is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count');
-    is ($owners->page(3)->count_rs->next, 2, 'has-many prefetch returns correct count_rs');
-
-    # make sure count does not become overly complex
-    is_same_sql_bind (
-      $owners->page(3)->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM (
-            SELECT TOP 3 [me].[id]
-              FROM [owners] [me]
-              LEFT JOIN [books] [books] ON [books].[owner] = [me].[id]
-            WHERE ( [books].[id] IS NOT NULL )
-            GROUP BY [me].[id]
-            ORDER BY [me].[id] DESC
-          ) [count_subq]
-      )',
-      [],
-    );
-  }
+  is ($owners->page(3)->all, 2, 'has_many prefetch returns correct number of rows');
+  is ($owners->page(3)->count, 2, 'has-many prefetch returns correct count');
+  is ($owners->page(3)->count_rs->next, 2, 'has-many prefetch returns correct count_rs');
+
 
   # try a ->belongs_to direction (no select collapse, group_by should work)
-  my $books = $schema->resultset ('BooksInLibrary')->search ({
+  my $books = $schema->resultset ('BooksInLibrary')->search (
+    {
       'owner.name' => [qw/wiggle woggle/],
-    }, {
+    },
+    {
       distinct => 1,
+      having => \['1 = ?', [ test => 1 ] ], #test having propagation
       prefetch => 'owner',
       rows     => 2,  # 3 results total
       order_by => { -desc => 'owner' },
-      # there is no sane way to order by the right side of a grouped prefetch currently :(
-      #order_by => { -desc => 'owner.name' },
-    });
-
+    },
+  );
+
+  ($sql, @bind) = @${$books->page(3)->as_query};
+  is_deeply (
+    \@bind,
+    [
+      # inner
+      [ 'owner.name' => 'wiggle' ], [ 'owner.name' => 'woggle' ], [ source => 'Library' ], [ test => '1' ],
+      # outer
+      [ 'owner.name' => 'wiggle' ], [ 'owner.name' => 'woggle' ], [ source => 'Library' ],
+    ],
+  );
 
   is ($books->page(1)->all, 2, 'Prefetched grouped search returns correct number of rows');
   is ($books->page(1)->count, 2, 'Prefetched grouped search returns correct count');
 
-  TODO: {
-    local $TODO = 'limit past end of resultset problem';
-    is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows');
-    is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count');
-    is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
-
-    # make sure count does not become overly complex (FIXME - the distinct-induced group_by is incorrect)
-    is_same_sql_bind (
-      $books->page(2)->count_rs->as_query,
-      '(
-        SELECT COUNT( * )
-          FROM (
-            SELECT TOP 2 [me].[id]
-              FROM [books] [me]
-              JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
-            WHERE ( ( ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? ) )
-            GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
-            ORDER BY [me].[id] DESC
-          ) [count_subq]
-      )',
-      [
-        [ 'owner.name' => 'wiggle' ],
-        [ 'owner.name' => 'woggle' ],
-        [ 'source' => 'Library' ],
-      ],
-    );
-  }
+  is ($books->page(2)->all, 1, 'Prefetched grouped search returns correct number of rows');
+  is ($books->page(2)->count, 1, 'Prefetched grouped search returns correct count');
+  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 => '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});
+  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'
+  );
+
+
+# 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]
+                    ) [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]
+    )',
+    [ ([ 'owner.name' => 'woggle' ], [ source => 'Library' ]) x 2 ],
+    'Expected SQL executed',
+  );
 }
 
+}
+
+done_testing;
+
 # clean up our mess
 END {
   if (my $dbh = eval { $schema->storage->_dbh }) {