Proyectos de Subversion Moodle

<?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/>.

/**

 * DML layer tests.

 *

 * @package    core

 * @category   test

 * @copyright  2008 Nicolas Connault

 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later

 */

namespace core;

use dml_exception;

use dml_missing_record_exception;

use dml_multiple_records_exception;

use moodle_database;

use moodle_transaction;

use xmldb_key;

use xmldb_table;

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

/**

 * DML layer tests.

 *

 * @package    core

 * @category   test

 * @copyright  2008 Nicolas Connault

 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later

 * @covers \moodle_database

 */

final class dml_test extends \database_driver_testcase {

    protected function setUp(): void {

        parent::setUp();

        $dbman = $this->tdb->get_manager(); // Loads DDL libs.

    }

    /**

     * Get a xmldb_table object for testing, deleting any existing table

     * of the same name, for example if one was left over from a previous test

     * run that crashed.

     *

     * @param string $suffix table name suffix, use if you need more test tables

     * @return xmldb_table the table object.

     */

    private function get_test_table($suffix = '') {

        $tablename = "test_table";

        if ($suffix !== '') {

            $tablename .= $suffix;

        }

        $table = new xmldb_table($tablename);

        $table->setComment("This is a test'n drop table. You can drop it safely");

        return $table;

    }

    /**

     * Convert a unix string to a OS (dir separator) dependent string.

     *

     * @param string $source the original srting, using unix dir separators and newlines.

     * @return string the resulting string, using current OS dir separators newlines.

     */

    private function unix_to_os_dirsep(string $source): string {

        if (DIRECTORY_SEPARATOR !== '/') {

            return str_replace('/', DIRECTORY_SEPARATOR, $source);

        }

        return $source; // No changes, so far.

    }

    public function test_diagnose(): void {

        $DB = $this->tdb;

        $result = $DB->diagnose();

        $this->assertNull($result, 'Database self diagnostics failed %s');

    }

    public function test_get_server_info(): void {

        $DB = $this->tdb;

        $result = $DB->get_server_info();

        $this->assertIsArray($result);

        $this->assertArrayHasKey('description', $result);

        $this->assertArrayHasKey('version', $result);

    }

    public function test_get_in_or_equal(): void {

        $DB = $this->tdb;

        // SQL_PARAMS_QM - IN or =.

        // Correct usage of multiple values.

        $in_values = array('value1', 'value2', '3', 4, null, false, true);

        list($usql, $params) = $DB->get_in_or_equal($in_values);

        $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql);

        $this->assertEquals(count($in_values), count($params));

        foreach ($params as $key => $value) {

            $this->assertSame($in_values[$key], $value);

        }

        // Correct usage of single value (in an array).

        $in_values = array('value1');

        list($usql, $params) = $DB->get_in_or_equal($in_values);

        $this->assertEquals("= ?", $usql);

        $this->assertCount(1, $params);

        $this->assertEquals($in_values[0], $params[0]);

        // Correct usage of single value.

        $in_value = 'value1';

        list($usql, $params) = $DB->get_in_or_equal($in_values);

        $this->assertEquals("= ?", $usql);

        $this->assertCount(1, $params);

        $this->assertEquals($in_value, $params[0]);

        // SQL_PARAMS_QM - NOT IN or <>.

        // Correct usage of multiple values.

        $in_values = array('value1', 'value2', 'value3', 'value4');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);

        $this->assertEquals("NOT IN (?,?,?,?)", $usql);

        $this->assertCount(4, $params);

        foreach ($params as $key => $value) {

            $this->assertEquals($in_values[$key], $value);

        }

        // Correct usage of single value (in array().

        $in_values = array('value1');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);

        $this->assertEquals("<> ?", $usql);

        $this->assertCount(1, $params);

        $this->assertEquals($in_values[0], $params[0]);

        // Correct usage of single value.

        $in_value = 'value1';

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);

        $this->assertEquals("<> ?", $usql);

        $this->assertCount(1, $params);

        $this->assertEquals($in_value, $params[0]);

        // SQL_PARAMS_NAMED - IN or =.

        // Correct usage of multiple values.

        $in_values = array('value1', 'value2', 'value3', 'value4');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);

        $this->assertCount(4, $params);

        reset($in_values);

        $ps = array();

        foreach ($params as $key => $value) {

            $this->assertEquals(current($in_values), $value);

            next($in_values);

            $ps[] = ':'.$key;

        }

        $this->assertEquals("IN (".implode(',', $ps).")", $usql);

        // Correct usage of single values (in array).

        $in_values = array('value1');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertEquals("= :$key", $usql);

        $this->assertEquals($in_value, $value);

        // Correct usage of single value.

        $in_value = 'value1';

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertEquals("= :$key", $usql);

        $this->assertEquals($in_value, $value);

        // SQL_PARAMS_NAMED - NOT IN or <>.

        // Correct usage of multiple values.

        $in_values = array('value1', 'value2', 'value3', 'value4');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);

        $this->assertCount(4, $params);

        reset($in_values);

        $ps = array();

        foreach ($params as $key => $value) {

            $this->assertEquals(current($in_values), $value);

            next($in_values);

            $ps[] = ':'.$key;

        }

        $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql);

        // Correct usage of single values (in array).

        $in_values = array('value1');

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertEquals("<> :$key", $usql);

        $this->assertEquals($in_value, $value);

        // Correct usage of single value.

        $in_value = 'value1';

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertEquals("<> :$key", $usql);

        $this->assertEquals($in_value, $value);

        // Make sure the param names are unique.

        list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');

        list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');

        $params1 = array_keys($params1);

        $params2 = array_keys($params2);

        $common = array_intersect($params1, $params2);

        $this->assertCount(0, $common);

        // Some incorrect tests.

        // Incorrect usage passing not-allowed params type.

        $in_values = array(1, 2, 3);

        try {

            list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);

            $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

            $this->assertSame('typenotimplement', $e->errorcode);

        }

        // Incorrect usage passing empty array.

        $in_values = array();

        try {

            list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);

            $this->fail('An Exception is missing, expected due to empty array of items');

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('coding_exception', $e);

        }

        // Test using $onemptyitems.

        // Correct usage passing empty array and $onemptyitems = null (equal = true, QM).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null);

        $this->assertSame(' IS NULL', $usql);

        $this->assertSame(array(), $params);

        // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null);

        $this->assertSame(' IS NOT NULL', $usql);

        $this->assertSame(array(), $params);

        // Correct usage passing empty array and $onemptyitems = true (equal = true, QM).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);

        $this->assertSame('= ?', $usql);

        $this->assertSame(array(true), $params);

        // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertSame('<> :'.$key, $usql);

        $this->assertSame($value, true);

        // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);

        $this->assertSame('= ?', $usql);

        $this->assertSame(array(-1), $params);

        // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertSame('<> :'.$key, $usql);

        $this->assertSame($value, -1);

        // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');

        $this->assertSame('= ?', $usql);

        $this->assertSame(array('onevalue'), $params);

        // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED).

        $in_values = array();

        list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');

        $this->assertCount(1, $params);

        $value = reset($params);

        $key = key($params);

        $this->assertSame('<> :'.$key, $usql);

        $this->assertSame($value, 'onevalue');

    }

    public function test_fix_table_names(): void {

        $DB = new moodle_database_for_testing();

        $prefix = $DB->get_prefix();

        // Simple placeholder.

        $placeholder = "{user_123}";

        $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder));

        // Wrong table name.

        $placeholder = "{user-a}";

        $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));

        // Wrong table name.

        $placeholder = "{123user}";

        $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));

        // Full SQL.

        $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";

        $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";

        $this->assertSame($expected, $DB->public_fix_table_names($sql));

    }

    public function test_fix_sql_params(): void {

        $DB = $this->tdb;

        $prefix = $DB->get_prefix();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        // Correct table placeholder substitution.

        $sql = "SELECT * FROM {{$tablename}}";

        $sqlarray = $DB->fix_sql_params($sql);

        $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]);

        // Conversions of all param types.

        $sql = array();

        $sql[SQL_PARAMS_NAMED]  = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2";

        $sql[SQL_PARAMS_QM]     = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?";

        $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2";

        $params = array();

        $params[SQL_PARAMS_NAMED]  = array('param1'=>'first record', 'param2'=>1);

        $params[SQL_PARAMS_QM]     = array('first record', 1);

        $params[SQL_PARAMS_DOLLAR] = array('first record', 1);

        list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);

        $this->assertSame($rsql, $sql[$rtype]);

        $this->assertSame($rparams, $params[$rtype]);

        list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);

        $this->assertSame($rsql, $sql[$rtype]);

        $this->assertSame($rparams, $params[$rtype]);

        list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);

        $this->assertSame($rsql, $sql[$rtype]);

        $this->assertSame($rparams, $params[$rtype]);

        // Malformed table placeholder.

        $sql = "SELECT * FROM [testtable]";

        $sqlarray = $DB->fix_sql_params($sql);

        $this->assertSame($sql, $sqlarray[0]);

        // Mixed param types (colon and dollar).

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";

        $params = array('param1' => 'record1', 'param2' => 3);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Mixed param types (question and dollar).

        $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";

        $params = array('param1' => 'record2', 'param2' => 5);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Too few params in sql.

        $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";

        $params = array('record2', 3);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Too many params in array: no error, just use what is necessary.

        $params[] = 1;

        $params[] = time();

        $sqlarray = $DB->fix_sql_params($sql, $params);

        $this->assertIsArray($sqlarray);

        $this->assertCount(3, $sqlarray[1]);

        // Named params missing from array.

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";

        $params = array('wrongname' => 'record1', 'course' => 1);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Duplicate named param in query - this is a very important feature!!

        // it helps with debugging of sloppy code.

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";

        $params = array('name' => 'record2', 'course' => 3);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Extra named param is ignored.

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";

        $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');

        $sqlarray = $DB->fix_sql_params($sql, $params);

        $this->assertIsArray($sqlarray);

        $this->assertCount(2, $sqlarray[1]);

        // Correct param with xmldb_field::NAME_MAX_LENGTH works ok.

        $correctparam = str_pad('allowed_long_param', \xmldb_field::NAME_MAX_LENGTH, 'x');

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :{$correctparam} AND course = :course";

        $params = array($correctparam => 'record1', 'course' => 1);

        $sqlarray = $DB->fix_sql_params($sql, $params);

        $this->assertIsArray($sqlarray);

        $this->assertCount(2, $sqlarray[1]);

        // Incorrect param exceeding xmldb_field::NAME_MAX_LENGTH chars length.

        $incorrectparam = str_pad('allowed_long_param', \xmldb_field::NAME_MAX_LENGTH + 1, 'x');

        $sql = "SELECT * FROM {{$tablename}} WHERE name = :{$incorrectparam} AND course = :course";

        $params = array($incorrectparam => 'record1', 'course' => 1);

        try {

            $DB->fix_sql_params($sql, $params);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('coding_exception', $e);

        }

        // Booleans in NAMED params are casting to 1/0 int.

        $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";

        $params = array(true, false);

        list($sql, $params) = $DB->fix_sql_params($sql, $params);

        $this->assertTrue(reset($params) === 1);

        $this->assertTrue(next($params) === 0);

        // Booleans in QM params are casting to 1/0 int.

        $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";

        $params = array('course1' => true, 'course2' => false);

        list($sql, $params) = $DB->fix_sql_params($sql, $params);

        $this->assertTrue(reset($params) === 1);

        $this->assertTrue(next($params) === 0);

        // Booleans in DOLLAR params are casting to 1/0 int.

        $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";

        $params = array(true, false);

        list($sql, $params) = $DB->fix_sql_params($sql, $params);

        $this->assertTrue(reset($params) === 1);

        $this->assertTrue(next($params) === 0);

        // No data types are touched except bool.

        $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";

        $inparams = array('abc', 'ABC', null, '1', 1, 1.4);

        list($sql, $params) = $DB->fix_sql_params($sql, $inparams);

        $this->assertSame(array_values($params), array_values($inparams));

    }

    /**

     * Test the database debugging as SQL comment.

     */

    public function test_add_sql_debugging(): void {

        global $CFG;

        $DB = $this->tdb;

        require_once($CFG->dirroot . '/lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php');

        $databasemock = $this->getMockBuilder(\moodle_database::class)->getMock();

        $fixture = new \test_dml_sql_debugging_fixture($databasemock);

        $sql = "SELECT * FROM {users}";

        $out = $fixture->four($sql);

        $CFG->debugsqltrace = 0;

        $this->assertEquals("SELECT * FROM {users}", $out);

        $CFG->debugsqltrace = 1;

        $out = $fixture->four($sql);

        $expected = <<<EOD

SELECT \* FROM {users}

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke\(\)

EOD;

        $this->assertMatchesRegularExpression('@' . $this->unix_to_os_dirsep($expected) . '@', $out);

        $CFG->debugsqltrace = 2;

        $out = $fixture->four($sql);

        $expected = <<<EOD

SELECT \* FROM {users}

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke\(\)

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one\(\)

EOD;

        $this->assertMatchesRegularExpression('@' . $this->unix_to_os_dirsep($expected) . '@', $out);

        $CFG->debugsqltrace = 5;

        $out = $fixture->four($sql);

        $expected = <<<EOD

SELECT \* FROM {users}

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke\(\)

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one\(\)

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->two\(\)

-- line \d+ of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->three\(\)

-- line \d+ of /lib/dml/tests/dml_test.php: call to test_dml_sql_debugging_fixture->four\(\)

EOD;

        $this->assertMatchesRegularExpression('@' . $this->unix_to_os_dirsep($expected) . '@', $out);

        $CFG->debugsqltrace = 0;

    }

    /**

     * Test the database debugging as SQL comment in anon class

     */

    public function test_sql_debugging_anon_class(): void {

        global $CFG;

        $CFG->debugsqltrace = 100;

        // A anon class.

        $another = new class {

            /**

             * Just a test log function

             */

            public function get_site() {

                global $DB;

                return $DB->get_record('course', ['category' => 0]);

            }

        };

        $site = $another->get_site();

        $CFG->debugsqltrace = 0;

        $this->assertEquals(get_site(), $site);

    }

    public function test_strtok(): void {

        // Strtok was previously used by bound emulation, make sure it is not used any more.

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $str = 'a?b?c?d';

        $this->assertSame(strtok($str, '?'), 'a');

        $DB->get_records($tablename, array('id'=>1));

        $this->assertSame(strtok('?'), 'b');

    }

    public function test_get_tables(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        // Need to test with multiple DBs.

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $original_count = count($DB->get_tables());

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $this->assertTrue(count($DB->get_tables()) == $original_count + 1);

        $dbman->drop_table($table);

        $this->assertTrue(count($DB->get_tables()) == $original_count);

    }

    public function test_get_indexes(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

        $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));

        $dbman->create_table($table);

        $indices = $DB->get_indexes($tablename);

        $this->assertIsArray($indices);

        $this->assertCount(2, $indices);

        // We do not care about index names for now.

        $first = array_shift($indices);

        $second = array_shift($indices);

        if (count($first['columns']) == 2) {

            $composed = $first;

            $single   = $second;

        } else {

            $composed = $second;

            $single   = $first;

        }

        $this->assertFalse($single['unique']);

        $this->assertTrue($composed['unique']);

        $this->assertCount(1, $single['columns']);

        $this->assertCount(2, $composed['columns']);

        $this->assertSame('course', $single['columns'][0]);

        $this->assertSame('course', $composed['columns'][0]);

        $this->assertSame('id', $composed['columns'][1]);

    }

    /**

     * Let's verify get_indexes() when we mix null and not null columns in unique indexes.

     *

     * Some databases, for unique indexes of this type, need to create function indexes to

     * provide cross-db behaviour. Here we check that those indexes don't break get_indexes().

     *

     * Note that, strictly speaking, unique indexes on null columns are far from ideal. Both

     * conceptually and also in practice, because they cause DBs to use full scans in a

     * number of situations. But if we support them, we need to ensure get_indexes() work on them.

     */

    public function test_get_indexes_unique_mixed_nullability(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('nullable01', XMLDB_TYPE_INTEGER, 10, null, null, null, null);

        $table->add_field('nullable02', XMLDB_TYPE_INTEGER, 10, null, null, null, null);

        $table->add_field('nonullable01', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_field('nonullable02', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $indexcolumns = ['nullable01', 'nonullable01', 'nullable02', 'nonullable02'];

        $table->add_index('course-id', XMLDB_INDEX_UNIQUE, $indexcolumns);

        $dbman->create_table($table);

        $indexes = $DB->get_indexes($tablename);

        $this->assertIsArray($indexes);

        $this->assertCount(1, $indexes);

        $index = array_shift($indexes);

        $this->assertTrue($index['unique']);

        $this->assertSame($indexcolumns, $index['columns']);

    }

    public function test_get_columns(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');

        $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);

        $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_field('oneintnodefault', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null);

        $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');

        $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);

        $table->add_field('onenumnodefault', XMLDB_TYPE_NUMBER, '10,2', null, null, null);

        $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null, 300);

        $table->add_field('onefloatnodefault', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null);

        $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);

        $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');

        $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');

        $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');

        $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0');

        $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0');

        $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0');

        $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0');

        $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0');

        $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0');

        $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0');

        $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0');

        $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0');

        $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');

        $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $columns = $DB->get_columns($tablename);

        $this->assertIsArray($columns);

        $fields = $table->getFields();

        $this->assertCount(count($columns), $fields);

        $field = $columns['id'];

        $this->assertSame('R', $field->meta_type);

        $this->assertTrue($field->auto_increment);

        $this->assertTrue($field->unique);

        $field = $columns['course'];

        $this->assertSame('I', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertTrue($field->has_default);

        $this->assertEquals(0, $field->default_value);

        $this->assertTrue($field->not_null);

        for ($i=1; $i<=10; $i++) {

            $field = $columns['someint'.$i];

            $this->assertSame('I', $field->meta_type);

            $this->assertGreaterThanOrEqual($i, $field->max_length);

        }

        $field = $columns['someint18'];

        $this->assertSame('I', $field->meta_type);

        $this->assertGreaterThanOrEqual(18, $field->max_length);

        $field = $columns['name'];

        $this->assertSame('C', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertEquals(255, $field->max_length);

        $this->assertTrue($field->has_default);

        $this->assertSame('lala', $field->default_value);

        $this->assertFalse($field->not_null);

        $field = $columns['description'];

        $this->assertSame('X', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertFalse($field->has_default);

        $this->assertNull($field->default_value);

        $this->assertFalse($field->not_null);

        $field = $columns['oneint'];

        $this->assertSame('I', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertTrue($field->has_default);

        $this->assertEquals(0, $field->default_value);

        $this->assertTrue($field->not_null);

        $field = $columns['oneintnodefault'];

        $this->assertSame('I', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertFalse($field->has_default);

        $this->assertNull($field->default_value);

        $this->assertTrue($field->not_null);

        $field = $columns['enumfield'];

        $this->assertSame('C', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertSame('test2', $field->default_value);

        $this->assertTrue($field->not_null);

        $field = $columns['onenum'];

        $this->assertSame('N', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertEquals(10, $field->max_length);

        $this->assertEquals(2, $field->scale);

        $this->assertTrue($field->has_default);

        $this->assertEquals(200.0, $field->default_value);

        $this->assertFalse($field->not_null);

        $field = $columns['onenumnodefault'];

        $this->assertSame('N', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertEquals(10, $field->max_length);

        $this->assertEquals(2, $field->scale);

        $this->assertFalse($field->has_default);

        $this->assertNull($field->default_value);

        $this->assertFalse($field->not_null);

        $field = $columns['onefloat'];

        $this->assertSame('N', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertTrue($field->has_default);

        $this->assertEquals(300.0, $field->default_value);

        $this->assertTrue($field->not_null);

        $field = $columns['onefloatnodefault'];

        $this->assertSame('N', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertFalse($field->has_default);

        $this->assertNull($field->default_value);

        $this->assertTrue($field->not_null);

        $field = $columns['anotherfloat'];

        $this->assertSame('N', $field->meta_type);

        $this->assertFalse($field->auto_increment);

        $this->assertTrue($field->has_default);

        $this->assertEquals(400.0, $field->default_value);

        $this->assertFalse($field->not_null);

        // Test negative defaults in numerical columns.

        $field = $columns['negativedfltint'];

        $this->assertTrue($field->has_default);

        $this->assertEquals(-1, $field->default_value);

        $field = $columns['negativedfltnumber'];

        $this->assertTrue($field->has_default);

        $this->assertEquals(-2, $field->default_value);

        $field = $columns['negativedfltfloat'];

        $this->assertTrue($field->has_default);

        $this->assertEquals(-3, $field->default_value);

        for ($i = 0; $i < count($columns); $i++) {

            if ($i == 0) {

                $next_column = reset($columns);

                $next_field  = reset($fields);

            } else {

                $next_column = next($columns);

                $next_field  = next($fields);

            }

            $this->assertEquals($next_column->name, $next_field->getName());

        }

        // Test get_columns for non-existing table returns empty array. MDL-30147.

        $columns = $DB->get_columns('xxxx');

        $this->assertEquals(array(), $columns);

        // Create something similar to "context_temp" with id column without sequence.

        $dbman->drop_table($table);

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $columns = $DB->get_columns($tablename);

        $this->assertFalse($columns['id']->auto_increment);

    }

    public function test_get_manager(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $this->assertInstanceOf('database_manager', $dbman);

    }

    public function test_setup_is_unicodedb(): void {

        $DB = $this->tdb;

        $this->assertTrue($DB->setup_is_unicodedb());

    }

    public function test_set_debug(): void { // Tests get_debug() too.

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $sql = "SELECT * FROM {{$tablename}}";

        $prevdebug = $DB->get_debug();

        ob_start();

        $DB->set_debug(true);

        $this->assertTrue($DB->get_debug());

        $DB->execute($sql);

        $DB->set_debug(false);

        $this->assertFalse($DB->get_debug());

        $debuginfo = ob_get_contents();

        ob_end_clean();

        $this->assertFalse($debuginfo === '');

        ob_start();

        $DB->execute($sql);

        $debuginfo = ob_get_contents();

        ob_end_clean();

        $this->assertTrue($debuginfo === '');

        $DB->set_debug($prevdebug);

    }

    public function test_execute(): void {

        $DB = $this->tdb;

        $dbman = $this->tdb->get_manager();

        $table1 = $this->get_test_table('1');

        $tablename1 = $table1->getName();

        $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');

        $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

        $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table1);

        $table2 = $this->get_test_table('2');

        $tablename2 = $table2->getName();

        $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table2->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

        $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table2);

        $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));

        $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));

        $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));

        $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));

        // Select results are ignored.

        $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";

        $this->assertTrue($DB->execute($sql, array('course'=>3)));

        // Throw exception on error.

        $sql = "XXUPDATE SET XSSD";

        try {

            $DB->execute($sql);

            $this->fail("Expecting an exception, none occurred");

        } catch (\moodle_exception $e) {

            $this->assertInstanceOf('dml_exception', $e);

        }

        // Update records.

        $sql = "UPDATE {{$tablename1}}

                   SET course = 6

                 WHERE course = ?";

        $this->assertTrue($DB->execute($sql, array('3')));

        $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6)));

        // Update records with subquery condition.

        // Confirm that the option not using table aliases is cross-db.

        $sql = "UPDATE {{$tablename1}}

                   SET course = 0

                 WHERE NOT EXISTS (

                           SELECT course

                             FROM {{$tablename2}} tbl2

                            WHERE tbl2.course = {{$tablename1}}.course

                              AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed.

        $this->assertTrue($DB->execute($sql));

        // Insert from one into second table.

        $sql = "INSERT INTO {{$tablename2}} (course)

                SELECT course

                  FROM {{$tablename1}}";

        $this->assertTrue($DB->execute($sql));

        $this->assertEquals(4, $DB->count_records($tablename2));

        // Insert a TEXT with raw SQL, binding TEXT params.

        $course = 9999;

        $onetext = file_get_contents(__DIR__ . '/fixtures/clob.txt');

        $sql = "INSERT INTO {{$tablename2}} (course, onetext)

                VALUES (:course, :onetext)";

        $DB->execute($sql, array('course' => $course, 'onetext' => $onetext));

        $records = $DB->get_records($tablename2, array('course' => $course));

        $this->assertCount(1, $records);

        $record = reset($records);

        $this->assertSame($onetext, $record->onetext);

        // Update a TEXT with raw SQL, binding TEXT params.

        $newcourse = 10000;

        $newonetext = file_get_contents(__DIR__ . '/fixtures/clob.txt') . '- updated';

        $sql = "UPDATE {{$tablename2}} SET course = :newcourse, onetext = :newonetext

                WHERE course = :oldcourse";

        $DB->execute($sql, array('oldcourse' => $course, 'newcourse' => $newcourse, 'newonetext' => $newonetext));

        $records = $DB->get_records($tablename2, array('course' => $course));

        $this->assertCount(0, $records);

        $records = $DB->get_records($tablename2, array('course' => $newcourse));

        $this->assertCount(1, $records);

        $record = reset($records);

        $this->assertSame($newonetext, $record->onetext);

    }

    public function test_get_recordset(): void {

        $DB = $this->tdb;

        $dbman = $DB->get_manager();

        $table = $this->get_test_table();

        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);

        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');

        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');

        $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);

        $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));

        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));

        $dbman->create_table($table);

        $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'),

            array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),