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->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content' => 'one')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content' => 'two')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content' => 'three')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'd', 'content' => 'four')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'e', 'content' => 'five')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'f', 'content' => 'six')));
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4));
$this->assertEquals(2, count($records));
$this->assertEquals('e', reset($records)->name);
$this->assertEquals('f', end($records)->name);
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertEmpty($records = $DB->get_records_sql($sqlqm, null, 8));
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 4));
$this->assertEquals(4, count($records));
$this->assertEquals('a', reset($records)->name);
$this->assertEquals('d', end($records)->name);
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8));
$this->assertEquals(6, count($records));
$this->assertEquals('a', reset($records)->name);
$this->assertEquals('f', end($records)->name);
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 1, 4));
$this->assertEquals(4, count($records));
$this->assertEquals('b', reset($records)->name);
$this->assertEquals('e', end($records)->name);
$sqlqm = "SELECT *\n FROM {{$tablename}}";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));
$this->assertEquals(2, count($records));
$this->assertEquals('e', reset($records)->name);
$this->assertEquals('f', end($records)->name);
$sqlqm = "SELECT t.*, t.name AS test\n FROM {{$tablename}} t\n ORDER BY t.id ASC";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));
$this->assertEquals(2, count($records));
$this->assertEquals('e', reset($records)->name);
$this->assertEquals('f', end($records)->name);
$sqlqm = "SELECT DISTINCT t.name, t.name AS test\n FROM {{$tablename}} t\n ORDER BY t.name DESC";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4));
$this->assertEquals(2, count($records));
$this->assertEquals('b', reset($records)->name);
$this->assertEquals('a', end($records)->name);
$sqlqm = "SELECT 1\n FROM {{$tablename}} t\n WHERE t.name = 'a'";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 1));
$this->assertEquals(1, count($records));
$sqlqm = "SELECT 'constant'\n FROM {{$tablename}} t\n WHERE t.name = 'a'";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8));
$this->assertEquals(1, count($records));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content' => 'one')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content' => 'two')));
$this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content' => 'three')));
$sqlqm = "SELECT t.name, COUNT(DISTINCT t2.id) AS count, 'Test' AS teststring\n FROM {{$tablename}} t\n LEFT JOIN (\n SELECT t.id, t.name\n FROM {{$tablename}} t\n ) t2 ON t2.name = t.name\n GROUP BY t.name\n ORDER BY t.name ASC";
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm));
$this->assertEquals(6, count($records));
// a,b,c,d,e,f
$this->assertEquals(2, reset($records)->count);
// a has 2 records now
$this->assertEquals(1, end($records)->count);
// f has 1 record still
$this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 2));
$this->assertEquals(2, count($records));
$this->assertEquals(2, reset($records)->count);
$this->assertEquals(2, end($records)->count);
}
