/lib/dml/tests/dml_test.php

Large files files are truncated, but you can click here to view the full file

<?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_dml
 * @category   phpunit
 * @copyright  2008 Nicolas Connault
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

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

class core_dml_testcase extends database_driver_testcase {

    protected function setUp() {
        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 new xmldb_table($tablename);
    }

    public function test_diagnose() {
        $DB = $this->tdb;
        $result = $DB->diagnose();
        $this->assertNull($result, 'Database self diagnostics failed %s');
    }

    public function test_get_server_info() {
        $DB = $this->tdb;
        $result = $DB->get_server_info();
        $this->assertInternalType('array', $result);
        $this->assertArrayHasKey('description', $result);
        $this->assertArrayHasKey('version', $result);
    }

    public function test_get_in_or_equal() {
        $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() {
        $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() {
        $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->assertInternalType('array', $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->assertInternalType('array', $sqlarray);
        $this->assertCount(2, $sqlarray[1]);

        // Params exceeding 30 chars length.
        $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
        $params = array('long_placeholder_with_more_than_30' => 'record1');
        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));
    }

    public function test_strtok() {
        // 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_tweak_param_names() {
        // Note the tweak_param_names() method is only available in the oracle driver,
        // hence we look for expected results indirectly, by testing various DML methods.
        // with some "extreme" conditions causing the tweak to happen.
        $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);
        // Add some columns with 28 chars in the name.
        $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
        $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
        $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
        // Add some columns with 30 chars in the name.
        $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
        $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
        $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');

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

        $dbman->create_table($table);

        $this->assertTrue($dbman->table_exists($tablename));

        // Test insert record.
        $rec1 = new stdClass();
        $rec1->long_int_columnname_with_28c = 28;
        $rec1->long_dec_columnname_with_28c = 28.28;
        $rec1->long_str_columnname_with_28c = '28';
        $rec1->long_int_columnname_with_30cxx = 30;
        $rec1->long_dec_columnname_with_30cxx = 30.30;
        $rec1->long_str_columnname_with_30cxx = '30';

        // Insert_record().
        $rec1->id = $DB->insert_record($tablename, $rec1);
        $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

        // Update_record().
        $DB->update_record($tablename, $rec1);
        $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

        // Set_field().
        $rec1->long_int_columnname_with_28c = 280;
        $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
            array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
        $rec1->long_dec_columnname_with_28c = 280.28;
        $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
            array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
        $rec1->long_str_columnname_with_28c = '280';
        $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
            array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
        $rec1->long_int_columnname_with_30cxx = 300;
        $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
            array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
        $rec1->long_dec_columnname_with_30cxx = 300.30;
        $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
            array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
        $rec1->long_str_columnname_with_30cxx = '300';
        $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
            array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
        $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));

        // Delete_records().
        $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
        $rec2->id = $DB->insert_record($tablename, $rec2);
        $this->assertEquals(2, $DB->count_records($tablename));
        $DB->delete_records($tablename, (array) $rec2);
        $this->assertEquals(1, $DB->count_records($tablename));

        // Get_recordset().
        $rs = $DB->get_recordset($tablename, (array) $rec1);
        $iterations = 0;
        foreach ($rs as $rec2) {
            $iterations++;
        }
        $rs->close();
        $this->assertEquals(1, $iterations);
        $this->assertEquals($rec1, $rec2);

        // Get_records().
        $recs = $DB->get_records($tablename, (array) $rec1);
        $this->assertCount(1, $recs);
        $this->assertEquals($rec1, reset($recs));

        // Get_fieldset_select().
        $select = 'id = :id AND
                   long_int_columnname_with_28c = :long_int_columnname_with_28c AND
                   long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
                   long_str_columnname_with_28c = :long_str_columnname_with_28c AND
                   long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
                   long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
                   long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
        $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
        $this->assertCount(1, $fields);
        $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));
        $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
        $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));
        $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
        $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields));
        $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
        $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields));
        $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
        $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields));
        $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
        $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));

        // Overlapping placeholders (progressive str_replace).
        $overlapselect = 'id = :p AND
                   long_int_columnname_with_28c = :param1 AND
                   long_dec_columnname_with_28c = :param2 AND
                   long_str_columnname_with_28c = :param_with_29_characters_long AND
                   long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
                   long_dec_columnname_with_30cxx = :param_ AND
                   long_str_columnname_with_30cxx = :param__';
        $overlapparams = array(
            'p' => $rec1->id,
            'param1' => $rec1->long_int_columnname_with_28c,
            'param2' => $rec1->long_dec_columnname_with_28c,
            'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
            'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
            'param_' => $rec1->long_dec_columnname_with_30cxx,
            'param__' => $rec1->long_str_columnname_with_30cxx);
        $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
        $this->assertCount(1, $recs);
        $this->assertEquals($rec1, reset($recs));

        // Execute().
        $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
        $this->assertEquals(0, $DB->count_records($tablename));
    }

    public function test_get_tables() {
        $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() {
        $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->assertInternalType('array', $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]);
    }

    public function test_get_columns() {
        $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('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('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300);
        $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->assertInternalType('array', $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['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['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->assertFalse($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() {
        $DB = $this->tdb;
        $dbman = $this->tdb->get_manager();

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

    public function test_setup_is_unicodedb() {
        $DB = $this->tdb;
        $this->assertTrue($DB->setup_is_unicodedb());
    }

    public function test_set_debug() { // 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() {
        $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_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));
    }

    public function test_get_recordset() {
        $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'),
            array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));

        foreach ($data as $key => $record) {
            $data[$key]['id'] = $DB->insert_record($tablename, $record);
        }

        // Standard recordset iteration.
        $rs = $DB->get_recordset($tablename);
        $this->assertInstanceOf('moodle_recordset', $rs);
        reset($data);
        foreach ($rs as $record) {
            $data_record = current($data);
            foreach ($record as $k => $v) {
                $this->assertEquals($data_record[$k], $v);
            }
            next($data);
        }
        $rs->close();

        // Iterator style usage.
        $rs = $DB->get_recordset($tablename);
        $this->assertInstanceOf('moodle_recordset', $rs);
        reset($data);
        while ($rs->valid()) {
            $record = $rs->current();
            $data_record = current($data);
            foreach ($record as $k => $v) {
                $this->assertEquals($data_record[$k], $v);
            }
            next($data);
            $rs->next();
        }
        $rs->close();

        // Make sure rewind is ignored.
        $rs = $DB->get_recordset($tablename);
        $this->assertInstanceOf('moodle_recordset', $rs);
        reset($data);
        $i = 0;
        foreach ($rs as $record) {
            $i++;
            $rs->rewind();
            if ($i > 10) {
                $this->fail('revind not ignored in recordsets');
                break;
            }
            $data_record = current($data);
            foreach ($record as $k => $v) {
                $this->assertEquals($data_record[$k], $v);
            }
            next($data);
        }
        $rs->close();

        // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
        $conditions = array('onetext' => '1');
        try {
            $rs = $DB->get_recordset($tablename, $conditions);
            $this->fail('An Exception is missing, expected due to equating of text fields');
        } catch (moodle_exception $e) {
            $this->assertInstanceOf('dml_exception', $e);
            $this->assertSame('textconditionsnotallowed', $e->errorcode);
        }

        // Test nested iteration.
        $rs1 = $DB->get_recordset($tablename);
        $i = 0;
        foreach ($rs1 as $record1) {
            $rs2 = $DB->get_recordset($tablename);
            $i++;
            $j = 0;
            foreach ($rs2 as $record2) {
                $j++;
            }
            $rs2->close();
            $this->assertCount($j, $data);
        }
        $rs1->close();
        $this->assertCount($i, $data);

        // Notes:
        //  * limits are tested in test_get_recordset_sql()
        //  * where_clause() is used internally and is tested in test_get_records()
    }

    public function test_get_recordset_static() {
        $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_key('primary', XMLDB_KEY_PRIMARY, array('id'));
        $dbman->create_table($table);

        $DB->insert_record($tablename, array('course' => 1));
        $DB->insert_record($tablename, array('course' => 2));
        $DB->insert_record($tablename, array('course' => 3));
        $DB->insert_record($tablename, array('course' => 4));

        $rs = $DB->get_recordset($tablename, array(), 'id');

        $DB->set_field($tablename, 'course', 666, array('course'=>1));
        $DB->delete_records($tablename, array('course'=>2));

        $i = 0;
        foreach ($rs as $record) {
            $i++;
            $this->assertEquals($i, $record->course);
        }
        $rs->close();
        $this->assertEquals(4, $i);

        // Now repeat with limits because it may use different code.
        $DB->delete_records($tablename, array());

        $DB->insert_record($tablename, array('course' => 1));
        $DB->insert_record($tablename, array('course' => 2));
        $DB->insert_record($tablename, array('course' => 3));
        $DB->insert_record($tablename, array('course' => 4));

        $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3);

        $DB->set_field($tablename, 'course', 666, array('course'=>1));
        $DB->delete_records($tablename, array('course'=>2));

        $i = 0;
        foreach ($rs as $record) {
            $i++;
            $this->assertEquals($i, $record->course);
        }
        $rs->close();
        $this->assertEquals(3, $i);
    }

    public function test_get_recordset_iterator_keys() {
        $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_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'),
            array('course' => 3, 'name' => 'record2'),
            array('course' => 5, 'name' => 'record3'));
        foreach ($data as $key => $record) {
            $data[$key]['id'] = $DB->insert_record($tablename, $record);
        }

        // Test repeated numeric keys are returned ok.
        $rs = $DB->get_recordset($tablename, null, null, 'course, name, id');

        reset($data);
        $count = 0;
        foreach ($rs as $key => $record) {
            $data_record = current($data);
            $this->assertEquals($data_record['course'], $key);
            next($data);
            $count++;
        }
        $rs->close();
        $this->assertEquals(3, $count);

        // Test string keys are returned ok.
        $rs = $DB->get_recordset($tablename, null, null, 'name, course, id');

        reset($data);
        $count = 0;
        foreach ($rs as $key => $record) {
            $data_record = current($data);
            $this->assertEquals($data_record['name'], $key);
            next($data);
            $count++;
        }
        $rs->close();
        $this->assertEquals(3, $count);

        // Test numeric not starting in 1 keys are returned ok.
        $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name');

        $data = array_reverse($data);
        reset($data);
        $count = 0;
        foreach ($rs as $key => $record) {
            $data_record = current($data);
            $this->assertEquals($data_record['id'], $key);
            next($data);
            $count++;
        }
        $rs->close();
        $this->assertEquals(3, $count);
    }

    public function test_get_recordset_list() {
        $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, null, null, '0');
        $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
        $dbman->create_table($table);

        $DB->insert_record($tablename, array('course' => 3));
        $DB->insert_record($tablename, array('course' => 3));
        $DB->insert_record($tablename, array('course' => 5));
        $DB->insert_record($tablename, array('course' => 2));
        $DB->insert_record($tablename, array('course' => null));
        $DB->insert_record($tablename, array('course' => 1));
        $DB->insert_record($tablename, array('course' => 0));

        $rs = $DB->get_recordset_list($tablename, 'co…