<?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 {



> class pgsql_native_recordset_test 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(): 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(): 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->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->assertMatchesRegularExpression($expected[$index++], $line);
        }
        if (array_key_exists($index, $expected)) {
            $this->fail('Fewer queries than expected');
        }
    }

}