Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * Test specific features of the Postgres dml support relating to recordsets.
 *
 * @package core
 * @category test
 * @copyright 2017 The Open University
 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

defined('MOODLE_INTERNAL') || die();

global $CFG;
require_once($CFG->dirroot.'/lib/dml/pgsql_native_moodle_database.php');

/**
 * Test specific features of the Postgres dml support relating to recordsets.
 *
 * @package core
 * @category test
 * @copyright 2017 The Open University
 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */
class pgsql_native_recordset_testcase extends basic_testcase {

    /** @var pgsql_native_moodle_database Special database connection */
    protected $specialdb;

    /**
     * Creates a second db connection and a temp table with values in for testing.
     */
< protected function setUp() {
> protected function setUp(): void {
global $DB; parent::setUp(); // Skip tests if not using Postgres. if (!($DB instanceof pgsql_native_moodle_database)) { $this->markTestSkipped('Postgres-only test'); } } /** * Initialises database connection with given fetch buffer size * @param int $fetchbuffersize Size of fetch buffer */ protected function init_db($fetchbuffersize) { global $CFG, $DB; // To make testing easier, create a database with the same dboptions as the real one, // but a low number for the cursor size. $this->specialdb = \moodle_database::get_driver_instance('pgsql', 'native', true); $dboptions = $CFG->dboptions; $dboptions['fetchbuffersize'] = $fetchbuffersize; $this->specialdb->connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass, $CFG->dbname, $DB->get_prefix(), $dboptions); // Create a temp table. $dbman = $this->specialdb->get_manager(); $table = new xmldb_table('silly_test_table'); $table->add_field('id', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, XMLDB_SEQUENCE); $table->add_field('msg', XMLDB_TYPE_CHAR, 255); $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']); $dbman->create_temp_table($table); // Add some records to the table. for ($index = 1; $index <= 7; $index++) { $this->specialdb->insert_record('silly_test_table', ['msg' => 'record' . $index]); } } /** * Gets rid of the second db connection. */
< protected function tearDown() {
> protected function tearDown(): void {
if ($this->specialdb) { $table = new xmldb_table('silly_test_table'); $this->specialdb->get_manager()->drop_table($table); $this->specialdb->dispose(); $this->specialdb = null; } parent::tearDown(); } /** * Tests that get_recordset_sql works when using cursors, which it does when no limit is * specified. */ public function test_recordset_cursors() { $this->init_db(3); // Query the table and check the actual queries using debug mode, also check the count. $this->specialdb->set_debug(true); $before = $this->specialdb->perf_get_queries(); ob_start(); $rs = $this->specialdb->get_recordset_sql('SELECT * FROM {silly_test_table} ORDER BY id'); $index = 0; foreach ($rs as $rec) { $index++; $this->assertEquals('record' . $index, $rec->msg); } $this->assertEquals(7, $index); $rs->close(); $debugging = ob_get_contents(); ob_end_clean(); // Expect 4 fetches - first three, next three, last one (with 2). $this->assert_query_regexps([ '~SELECT \* FROM~', '~FETCH 3 FROM crs1~', '~FETCH 3 FROM crs1~', '~FETCH 3 FROM crs1~', '~CLOSE crs1~'], $debugging); // There should have been 7 queries tracked for perf log. $this->assertEquals(5, $this->specialdb->perf_get_queries() - $before); // Try a second time - this time we'll request exactly 3 items so that it has to query // twice (as it can't tell if the first batch is the last). $before = $this->specialdb->perf_get_queries(); ob_start(); $rs = $this->specialdb->get_recordset_sql( 'SELECT * FROM {silly_test_table} WHERE id <= ? ORDER BY id', [3]); $index = 0; foreach ($rs as $rec) { $index++; $this->assertEquals('record' . $index, $rec->msg); } $this->assertEquals(3, $index); $rs->close(); $debugging = ob_get_contents(); ob_end_clean(); $this->specialdb->set_debug(false); // Expect 2 fetches - first three, then next one (empty). $this->assert_query_regexps([ '~SELECT \* FROM~', '~FETCH 3 FROM crs2~', '~FETCH 3 FROM crs2~', '~CLOSE crs2~'], $debugging); // There should have been 4 queries tracked for perf log. $this->assertEquals(4, $this->specialdb->perf_get_queries() - $before); } /** * Tests that get_recordset_sql works when using cursors and when there are two overlapping * recordsets being used. */ public function test_recordset_cursors_overlapping() { $this->init_db(3); $rs1 = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $rs2 = $this->specialdb->get_recordset('silly_test_table', null, 'id DESC'); // Read first 3 from first recordset. $read = []; $read[] = $rs1->current()->id; $rs1->next(); $read[] = $rs1->current()->id; $rs1->next(); $read[] = $rs1->current()->id; $rs1->next(); $this->assertEquals([1, 2, 3], $read); // Read 5 from second recordset. $read = []; $read[] = $rs2->current()->id; $rs2->next(); $read[] = $rs2->current()->id; $rs2->next(); $read[] = $rs2->current()->id; $rs2->next(); $read[] = $rs2->current()->id; $rs2->next(); $read[] = $rs2->current()->id; $rs2->next(); $this->assertEquals([7, 6, 5, 4, 3], $read); // Now read remainder of first recordset and close it. $read = []; $read[] = $rs1->current()->id; $rs1->next(); $read[] = $rs1->current()->id; $rs1->next(); $read[] = $rs1->current()->id; $rs1->next(); $read[] = $rs1->current()->id; $rs1->next(); $this->assertFalse($rs1->valid()); $this->assertEquals([4, 5, 6, 7], $read); $rs1->close(); // And remainder of second. $read = []; $read[] = $rs2->current()->id; $rs2->next(); $read[] = $rs2->current()->id; $rs2->next(); $this->assertFalse($rs2->valid()); $this->assertEquals([2, 1], $read); $rs2->close(); } /** * Tests that get_recordset_sql works when using cursors and transactions inside. */ public function test_recordset_cursors_transaction_inside() { $this->init_db(3); // Transaction inside the recordset processing. $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $read = []; foreach ($rs as $rec) { $read[] = $rec->id; $transaction = $this->specialdb->start_delegated_transaction(); $transaction->allow_commit(); } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); $rs->close(); } /** * Tests that get_recordset_sql works when using cursors and a transaction outside. */ public function test_recordset_cursors_transaction_outside() { $this->init_db(3); // Transaction outside the recordset processing. $transaction = $this->specialdb->start_delegated_transaction(); $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $read = []; foreach ($rs as $rec) { $read[] = $rec->id; } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); $rs->close(); $transaction->allow_commit(); } /** * Tests that get_recordset_sql works when using cursors and a transaction overlapping. */ public function test_recordset_cursors_transaction_overlapping_before() { $this->init_db(3); // Transaction outside the recordset processing. $transaction = $this->specialdb->start_delegated_transaction(); $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $transaction->allow_commit(); $read = []; foreach ($rs as $rec) { $read[] = $rec->id; } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); $rs->close(); } /** * Tests that get_recordset_sql works when using cursors and a transaction overlapping. */ public function test_recordset_cursors_transaction_overlapping_after() { $this->init_db(3); // Transaction outside the recordset processing. $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $transaction = $this->specialdb->start_delegated_transaction(); $read = []; foreach ($rs as $rec) { $read[] = $rec->id; } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); $rs->close(); $transaction->allow_commit(); } /** * Tests that get_recordset_sql works when using cursors and a transaction that 'fails' and gets * rolled back. */ public function test_recordset_cursors_transaction_rollback() { $this->init_db(3); try { $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $transaction = $this->specialdb->start_delegated_transaction(); $this->specialdb->delete_records('silly_test_table', ['id' => 5]); $transaction->rollback(new dml_transaction_exception('rollback please')); $this->fail('should not get here'); } catch (dml_transaction_exception $e) {
< $this->assertContains('rollback please', $e->getMessage());
> $this->assertStringContainsString('rollback please', $e->getMessage());
} finally { // Rollback should not kill our recordset. $read = []; foreach ($rs as $rec) { $read[] = $rec->id; } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); // This would happen in real code (that isn't within the same function) anyway because // it would go out of scope. $rs->close(); } // OK, transaction aborted, now get the recordset again and check nothing was deleted. $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id'); $read = []; foreach ($rs as $rec) { $read[] = $rec->id; } $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read); $rs->close(); } /** * Tests that get_recordset_sql works when not using cursors, because a limit is specified. */ public function test_recordset_no_cursors_limit() { $this->init_db(3); $this->specialdb->set_debug(true); $before = $this->specialdb->perf_get_queries(); ob_start(); $rs = $this->specialdb->get_recordset_sql( 'SELECT * FROM {silly_test_table} ORDER BY id', [], 0, 100); $index = 0; foreach ($rs as $rec) { $index++; $this->assertEquals('record' . $index, $rec->msg); } $this->assertEquals(7, $index); $rs->close(); $this->specialdb->set_debug(false); $debugging = ob_get_contents(); ob_end_clean(); // Expect direct request without using cursors. $this->assert_query_regexps(['~SELECT \* FROM~'], $debugging); // There should have been 1 query tracked for perf log. $this->assertEquals(1, $this->specialdb->perf_get_queries() - $before); } /** * Tests that get_recordset_sql works when not using cursors, because the config setting turns * them off. */ public function test_recordset_no_cursors_config() { $this->init_db(0); $this->specialdb->set_debug(true); $before = $this->specialdb->perf_get_queries(); ob_start(); $rs = $this->specialdb->get_recordset_sql('SELECT * FROM {silly_test_table} ORDER BY id'); $index = 0; foreach ($rs as $rec) { $index++; $this->assertEquals('record' . $index, $rec->msg); } $this->assertEquals(7, $index); $rs->close(); $this->specialdb->set_debug(false); $debugging = ob_get_contents(); ob_end_clean(); // Expect direct request without using cursors. $this->assert_query_regexps(['~SELECT \* FROM~'], $debugging); // There should have been 1 query tracked for perf log. $this->assertEquals(1, $this->specialdb->perf_get_queries() - $before); } /** * Asserts that database debugging output matches the expected list of SQL queries, specified * as an array of regular expressions. * * @param string[] $expected Expected regular expressions * @param string $debugging Debugging text from the database */ protected function assert_query_regexps(array $expected, $debugging) { $lines = explode("\n", $debugging); $index = 0; $params = false; foreach ($lines as $line) { if ($params) { if ($line === ')]') { $params = false; } continue; } // Skip irrelevant lines. if (preg_match('~^---~', $line)) { continue; } if (preg_match('~^Query took~', $line)) { continue; } if (trim($line) === '') { continue; } // Skip param lines. if ($line === '[array (') { $params = true; continue; } if (!array_key_exists($index, $expected)) { $this->fail('More queries than expected'); }
< $this->assertRegExp($expected[$index++], $line);
> $this->assertMatchesRegularExpression($expected[$index++], $line);
} if (array_key_exists($index, $expected)) { $this->fail('Fewer queries than expected'); } } }