sqlsrv MDL-26025 Fixed up numerous issues with the limit_to_top_n function
authorSam Hemelryk <sam@moodle.com>
Tue, 25 Jan 2011 09:13:00 +0000 (17:13 +0800)
committerSam Hemelryk <sam@moodle.com>
Mon, 7 Feb 2011 02:33:49 +0000 (10:33 +0800)
lib/dml/simpletest/testdml.php
lib/dml/sqlsrv_native_moodle_database.php
lib/dml/sqlsrv_native_moodle_recordset.php

index a302282..efbd2bc 100755 (executable)
@@ -3610,6 +3610,113 @@ class dml_test extends UnitTestCase {
         $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('content' => '2')));
         $this->assertEqual(1, count($records));
     }
+
+    public function test_limits_and_offsets() {
+        $DB = $this->tdb;
+        $dbman = $DB->get_manager();
+
+        if (false) $DB = new moodle_database ();
+
+        $table = $this->get_test_table();
+        $tablename = $table->getName();
+
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+        $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
+        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+        $dbman->create_table($table);
+
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'd', 'content'=>'four')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'e', 'content'=>'five')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'f', 'content'=>'six')));
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual('e', reset($records)->name);
+        $this->assertEqual('f', end($records)->name);
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertFalse($records = $DB->get_records_sql($sqlqm, null, 8));
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 4));
+        $this->assertEqual(4, count($records));
+        $this->assertEqual('a', reset($records)->name);
+        $this->assertEqual('d', end($records)->name);
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 8));
+        $this->assertEqual(6, count($records));
+        $this->assertEqual('a', reset($records)->name);
+        $this->assertEqual('f', end($records)->name);
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 1, 4));
+        $this->assertEqual(4, count($records));
+        $this->assertEqual('b', reset($records)->name);
+        $this->assertEqual('e', end($records)->name);
+
+        $sqlqm = "SELECT *
+                    FROM {{$tablename}}";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual('e', reset($records)->name);
+        $this->assertEqual('f', end($records)->name);
+
+        $sqlqm = "SELECT t.*, t.name AS test
+                    FROM {{$tablename}} t
+                    ORDER BY t.id ASC";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual('e', reset($records)->name);
+        $this->assertEqual('f', end($records)->name);
+
+        $sqlqm = "SELECT DISTINCT t.name, t.name AS test
+                    FROM {{$tablename}} t
+                    ORDER BY t.name DESC";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual('b', reset($records)->name);
+        $this->assertEqual('a', end($records)->name);
+
+        $sqlqm = "SELECT 1
+                    FROM {{$tablename}} t
+                    WHERE t.name = 'a'";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 1));
+        $this->assertEqual(1, count($records));
+
+        $sqlqm = "SELECT 'constant'
+                    FROM {{$tablename}} t
+                    WHERE t.name = 'a'";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 8));
+        $this->assertEqual(1, count($records));
+
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two')));
+        $this->assertTrue($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three')));
+
+        $sqlqm = "SELECT t.name, COUNT(DISTINCT t2.id) as count
+                    FROM {{$tablename}} t
+                    LEFT JOIN (
+                        SELECT t.id, t.name
+                        FROM {{$tablename}} t
+                    ) AS t2 ON t2.name = t.name
+                    GROUP BY t2.name
+                    ORDER BY t.name ASC";
+        $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 2));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual(2, reset($records)->count);
+        $this->assertEqual(2, end($records)->count);
+    }
 }
 
 /**
index 88539aa..d932e87 100644 (file)
@@ -797,23 +797,17 @@ class sqlsrv_native_moodle_database extends moodle_database {
         $firstcolumn = 'id'; // The default first column is id
         $orderby = '';       // The order by of the main query
 
-        // We need to find all the columns so that we can request JUST the desired
-        // columns in the end transaction. We also need to do a couple of maintenance
-        // tasks on the columns seeing as they are going to be wrapped in a 
-        // sub select.
+        // We need to do a couple of maintenance tasks on the columns seeing as
+        // they are going to be wrapped in a sub select.
         //
         // 1. Collect the first column to use for an order by if there isn't an
         //    explicit order by within the query.
-        // 2. Make we use the aliases for columns that have it because of next point.
-        // 3. Strip off table aliases they will be incorrect outside of the immediate
-        //    sub select.
-        // 4. Give all constant columns a proper alias, this is required because
+        // 2. Give all constant columns a proper alias, this is required because
         //    of the subselect.
         if (preg_match('#^(\s*SELECT\s+)(DISTINCT\s+)?(.*?)(\sFROM\s)#is', $sql, $columnmatches)) {
             // Make sure we have some columns
             if (!empty($columnmatches[3])) {
                 $columns = explode(',', $columnmatches[3]);
-                $columnnames = array();
                 $firstcolumn = null;
                 $constantcount = 0;
                 foreach ($columns as $key=>$column) {
@@ -822,7 +816,7 @@ class sqlsrv_native_moodle_database extends moodle_database {
                     if (preg_match('#\sAS\s+(\w+)\s*$#si', $column, $matches)) {
                         // Make sure we use the column alias if available.
                         $column = $matches[1];
-                    } else if (preg_match("#^'[^']*'$#", $column)) {
+                    } else if (preg_match("#^('[^']*'|\d+)$#", $column)) {
                         // Give constants an alias in the main query and use the
                         // alias for the new outer queries.
                         $constantcount++;
@@ -834,16 +828,8 @@ class sqlsrv_native_moodle_database extends moodle_database {
                     if ($firstcolumn === null) {
                         $firstcolumn = $column;
                     }
-
-                    // Remove any table aliases from the column name for the outer
-                    // queries.
-                    if (preg_match('#^\w+\.(.*)$#s', $column, $matches)) {
-                        $column = $matches[1];
-                    }
-                    $columnnames[] = $column;
                 }
                 // Glue things back together
-                $columnnames = join(', ', $columnnames);
                 $columns = join(', ', $columns);
                 // Switch out the fixed main columns (added constant aliases).
                 $sql = str_replace($columnmatches[0], $columnmatches[1].$columnmatches[2].$columns.$columnmatches[4], $sql);
@@ -867,10 +853,18 @@ class sqlsrv_native_moodle_database extends moodle_database {
             $orderby = 'id';
         }
 
+        // Now we need to build the queries up so that we collect a row count field and then sort on it.
+        // To do this we need to nest the query twice. The first nesting selects all the rows from the
+        // query and then proceeds to use OVER to generate a row number.
+        // The second nesting we limit by selecting where rownumber between offset and limit
+        // In both cases we will select the original query fields using q.* this is important
+        // as there can be any number of crafty things going on. It does however mean that we
+        // end up with the first field being sqlsrvrownumber however sqlsrv_native_moodle_recordset
+        // strips that off during processing if it exists.
         // Build the inner outer query.
-        $sql = "SELECT TOP $bigint ROW_NUMBER() OVER(ORDER BY $orderby) AS sqlsrvrownumber, $columnnames FROM ($sql) AS q";
+        $sql = "SELECT TOP $bigint ROW_NUMBER() OVER(ORDER BY $orderby) AS sqlsrvrownumber, q.* FROM ($sql) AS q";
         // Build the outer most query.
-        $sql = "SELECT $columnnames FROM ($sql) AS q WHERE q.sqlsrvrownumber > $offset AND q.sqlsrvrownumber <= $limit";
+        $sql = "SELECT q.* FROM ($sql) AS q WHERE q.sqlsrvrownumber > $offset AND q.sqlsrvrownumber <= $limit";
 
         // Return the now mangled query for use.
         return $sql;
index 815bfed..3b7e36c 100644 (file)
@@ -32,6 +32,7 @@ class sqlsrv_native_moodle_recordset extends moodle_recordset {
 
     protected $rsrc;
     protected $current;
+    protected $hasrownumber = null;
 
     public function __construct($rsrc) {
         $this->rsrc  = $rsrc;
@@ -44,6 +45,12 @@ class sqlsrv_native_moodle_recordset extends moodle_recordset {
 
     private function fetch_next() {
         if ($row = sqlsrv_fetch_array($this->rsrc, SQLSRV_FETCH_ASSOC)) {
+            if ($this->hasrownumber === null) {
+                $this->hasrownumber = array_key_exists('sqlsrvrownumber', $row);
+            }
+            if ($this->hasrownumber) {
+                unset($row['sqlsrvrownumber']);
+            }
             $row = array_change_key_case($row, CASE_LOWER);
         }
         return $row;