Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * DML layer tests.
  19   *
  20   * @package    core
  21   * @category   test
  22   * @copyright  2008 Nicolas Connault
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  namespace core;
  27  
  28  use dml_exception;
  29  use dml_missing_record_exception;
  30  use dml_multiple_records_exception;
  31  use moodle_database;
  32  use moodle_transaction;
  33  use xmldb_key;
  34  use xmldb_table;
  35  
  36  defined('MOODLE_INTERNAL') || die();
  37  
  38  /**
  39   * DML layer tests.
  40   *
  41   * @package    core
  42   * @category   test
  43   * @copyright  2008 Nicolas Connault
  44   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  45   */
  46  class dml_test extends \database_driver_testcase {
  47  
  48      protected function setUp(): void {
  49          parent::setUp();
  50          $dbman = $this->tdb->get_manager(); // Loads DDL libs.
  51      }
  52  
  53      /**
  54       * Get a xmldb_table object for testing, deleting any existing table
  55       * of the same name, for example if one was left over from a previous test
  56       * run that crashed.
  57       *
  58       * @param string $suffix table name suffix, use if you need more test tables
  59       * @return xmldb_table the table object.
  60       */
  61      private function get_test_table($suffix = '') {
  62          $tablename = "test_table";
  63          if ($suffix !== '') {
  64              $tablename .= $suffix;
  65          }
  66  
  67          $table = new xmldb_table($tablename);
  68          $table->setComment("This is a test'n drop table. You can drop it safely");
  69          return $table;
  70      }
  71  
  72      /**
  73       * Convert a unix string to a OS (dir separator) dependent string.
  74       *
  75       * @param string $source the original srting, using unix dir separators and newlines.
  76       * @return string the resulting string, using current OS dir separators newlines.
  77       */
  78      private function unix_to_os_dirsep(string $source): string {
  79          if (DIRECTORY_SEPARATOR !== '/') {
  80              return str_replace('/', DIRECTORY_SEPARATOR, $source);
  81          }
  82          return $source; // No changes, so far.
  83      }
  84  
  85      public function test_diagnose() {
  86          $DB = $this->tdb;
  87          $result = $DB->diagnose();
  88          $this->assertNull($result, 'Database self diagnostics failed %s');
  89      }
  90  
  91      public function test_get_server_info() {
  92          $DB = $this->tdb;
  93          $result = $DB->get_server_info();
  94          $this->assertIsArray($result);
  95          $this->assertArrayHasKey('description', $result);
  96          $this->assertArrayHasKey('version', $result);
  97      }
  98  
  99      public function test_get_in_or_equal() {
 100          $DB = $this->tdb;
 101  
 102          // SQL_PARAMS_QM - IN or =.
 103  
 104          // Correct usage of multiple values.
 105          $in_values = array('value1', 'value2', '3', 4, null, false, true);
 106          list($usql, $params) = $DB->get_in_or_equal($in_values);
 107          $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql);
 108          $this->assertEquals(count($in_values), count($params));
 109          foreach ($params as $key => $value) {
 110              $this->assertSame($in_values[$key], $value);
 111          }
 112  
 113          // Correct usage of single value (in an array).
 114          $in_values = array('value1');
 115          list($usql, $params) = $DB->get_in_or_equal($in_values);
 116          $this->assertEquals("= ?", $usql);
 117          $this->assertCount(1, $params);
 118          $this->assertEquals($in_values[0], $params[0]);
 119  
 120          // Correct usage of single value.
 121          $in_value = 'value1';
 122          list($usql, $params) = $DB->get_in_or_equal($in_values);
 123          $this->assertEquals("= ?", $usql);
 124          $this->assertCount(1, $params);
 125          $this->assertEquals($in_value, $params[0]);
 126  
 127          // SQL_PARAMS_QM - NOT IN or <>.
 128  
 129          // Correct usage of multiple values.
 130          $in_values = array('value1', 'value2', 'value3', 'value4');
 131          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 132          $this->assertEquals("NOT IN (?,?,?,?)", $usql);
 133          $this->assertCount(4, $params);
 134          foreach ($params as $key => $value) {
 135              $this->assertEquals($in_values[$key], $value);
 136          }
 137  
 138          // Correct usage of single value (in array().
 139          $in_values = array('value1');
 140          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 141          $this->assertEquals("<> ?", $usql);
 142          $this->assertCount(1, $params);
 143          $this->assertEquals($in_values[0], $params[0]);
 144  
 145          // Correct usage of single value.
 146          $in_value = 'value1';
 147          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 148          $this->assertEquals("<> ?", $usql);
 149          $this->assertCount(1, $params);
 150          $this->assertEquals($in_value, $params[0]);
 151  
 152          // SQL_PARAMS_NAMED - IN or =.
 153  
 154          // Correct usage of multiple values.
 155          $in_values = array('value1', 'value2', 'value3', 'value4');
 156          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 157          $this->assertCount(4, $params);
 158          reset($in_values);
 159          $ps = array();
 160          foreach ($params as $key => $value) {
 161              $this->assertEquals(current($in_values), $value);
 162              next($in_values);
 163              $ps[] = ':'.$key;
 164          }
 165          $this->assertEquals("IN (".implode(',', $ps).")", $usql);
 166  
 167          // Correct usage of single values (in array).
 168          $in_values = array('value1');
 169          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 170          $this->assertCount(1, $params);
 171          $value = reset($params);
 172          $key = key($params);
 173          $this->assertEquals("= :$key", $usql);
 174          $this->assertEquals($in_value, $value);
 175  
 176          // Correct usage of single value.
 177          $in_value = 'value1';
 178          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 179          $this->assertCount(1, $params);
 180          $value = reset($params);
 181          $key = key($params);
 182          $this->assertEquals("= :$key", $usql);
 183          $this->assertEquals($in_value, $value);
 184  
 185          // SQL_PARAMS_NAMED - NOT IN or <>.
 186  
 187          // Correct usage of multiple values.
 188          $in_values = array('value1', 'value2', 'value3', 'value4');
 189          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 190          $this->assertCount(4, $params);
 191          reset($in_values);
 192          $ps = array();
 193          foreach ($params as $key => $value) {
 194              $this->assertEquals(current($in_values), $value);
 195              next($in_values);
 196              $ps[] = ':'.$key;
 197          }
 198          $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql);
 199  
 200          // Correct usage of single values (in array).
 201          $in_values = array('value1');
 202          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 203          $this->assertCount(1, $params);
 204          $value = reset($params);
 205          $key = key($params);
 206          $this->assertEquals("<> :$key", $usql);
 207          $this->assertEquals($in_value, $value);
 208  
 209          // Correct usage of single value.
 210          $in_value = 'value1';
 211          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 212          $this->assertCount(1, $params);
 213          $value = reset($params);
 214          $key = key($params);
 215          $this->assertEquals("<> :$key", $usql);
 216          $this->assertEquals($in_value, $value);
 217  
 218          // Make sure the param names are unique.
 219          list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
 220          list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
 221          $params1 = array_keys($params1);
 222          $params2 = array_keys($params2);
 223          $common = array_intersect($params1, $params2);
 224          $this->assertCount(0, $common);
 225  
 226          // Some incorrect tests.
 227  
 228          // Incorrect usage passing not-allowed params type.
 229          $in_values = array(1, 2, 3);
 230          try {
 231              list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);
 232              $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');
 233          } catch (\moodle_exception $e) {
 234              $this->assertInstanceOf('dml_exception', $e);
 235              $this->assertSame('typenotimplement', $e->errorcode);
 236          }
 237  
 238          // Incorrect usage passing empty array.
 239          $in_values = array();
 240          try {
 241              list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 242              $this->fail('An Exception is missing, expected due to empty array of items');
 243          } catch (\moodle_exception $e) {
 244              $this->assertInstanceOf('coding_exception', $e);
 245          }
 246  
 247          // Test using $onemptyitems.
 248  
 249          // Correct usage passing empty array and $onemptyitems = null (equal = true, QM).
 250          $in_values = array();
 251          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null);
 252          $this->assertSame(' IS NULL', $usql);
 253          $this->assertSame(array(), $params);
 254  
 255          // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED).
 256          $in_values = array();
 257          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null);
 258          $this->assertSame(' IS NOT NULL', $usql);
 259          $this->assertSame(array(), $params);
 260  
 261          // Correct usage passing empty array and $onemptyitems = true (equal = true, QM).
 262          $in_values = array();
 263          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);
 264          $this->assertSame('= ?', $usql);
 265          $this->assertSame(array(true), $params);
 266  
 267          // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED).
 268          $in_values = array();
 269          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);
 270          $this->assertCount(1, $params);
 271          $value = reset($params);
 272          $key = key($params);
 273          $this->assertSame('<> :'.$key, $usql);
 274          $this->assertSame($value, true);
 275  
 276          // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM).
 277          $in_values = array();
 278          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);
 279          $this->assertSame('= ?', $usql);
 280          $this->assertSame(array(-1), $params);
 281  
 282          // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED).
 283          $in_values = array();
 284          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);
 285          $this->assertCount(1, $params);
 286          $value = reset($params);
 287          $key = key($params);
 288          $this->assertSame('<> :'.$key, $usql);
 289          $this->assertSame($value, -1);
 290  
 291          // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM).
 292          $in_values = array();
 293          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');
 294          $this->assertSame('= ?', $usql);
 295          $this->assertSame(array('onevalue'), $params);
 296  
 297          // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED).
 298          $in_values = array();
 299          list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');
 300          $this->assertCount(1, $params);
 301          $value = reset($params);
 302          $key = key($params);
 303          $this->assertSame('<> :'.$key, $usql);
 304          $this->assertSame($value, 'onevalue');
 305      }
 306  
 307      public function test_fix_table_names() {
 308          $DB = new moodle_database_for_testing();
 309          $prefix = $DB->get_prefix();
 310  
 311          // Simple placeholder.
 312          $placeholder = "{user_123}";
 313          $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder));
 314  
 315          // Wrong table name.
 316          $placeholder = "{user-a}";
 317          $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
 318  
 319          // Wrong table name.
 320          $placeholder = "{123user}";
 321          $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
 322  
 323          // Full SQL.
 324          $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
 325          $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
 326          $this->assertSame($expected, $DB->public_fix_table_names($sql));
 327      }
 328  
 329      public function test_fix_sql_params() {
 330          $DB = $this->tdb;
 331          $prefix = $DB->get_prefix();
 332  
 333          $table = $this->get_test_table();
 334          $tablename = $table->getName();
 335  
 336          // Correct table placeholder substitution.
 337          $sql = "SELECT * FROM {{$tablename}}";
 338          $sqlarray = $DB->fix_sql_params($sql);
 339          $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]);
 340  
 341          // Conversions of all param types.
 342          $sql = array();
 343          $sql[SQL_PARAMS_NAMED]  = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2";
 344          $sql[SQL_PARAMS_QM]     = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?";
 345          $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2";
 346  
 347          $params = array();
 348          $params[SQL_PARAMS_NAMED]  = array('param1'=>'first record', 'param2'=>1);
 349          $params[SQL_PARAMS_QM]     = array('first record', 1);
 350          $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
 351  
 352          list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
 353          $this->assertSame($rsql, $sql[$rtype]);
 354          $this->assertSame($rparams, $params[$rtype]);
 355  
 356          list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
 357          $this->assertSame($rsql, $sql[$rtype]);
 358          $this->assertSame($rparams, $params[$rtype]);
 359  
 360          list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
 361          $this->assertSame($rsql, $sql[$rtype]);
 362          $this->assertSame($rparams, $params[$rtype]);
 363  
 364          // Malformed table placeholder.
 365          $sql = "SELECT * FROM [testtable]";
 366          $sqlarray = $DB->fix_sql_params($sql);
 367          $this->assertSame($sql, $sqlarray[0]);
 368  
 369          // Mixed param types (colon and dollar).
 370          $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";
 371          $params = array('param1' => 'record1', 'param2' => 3);
 372          try {
 373              $DB->fix_sql_params($sql, $params);
 374              $this->fail("Expecting an exception, none occurred");
 375          } catch (\moodle_exception $e) {
 376              $this->assertInstanceOf('dml_exception', $e);
 377          }
 378  
 379          // Mixed param types (question and dollar).
 380          $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";
 381          $params = array('param1' => 'record2', 'param2' => 5);
 382          try {
 383              $DB->fix_sql_params($sql, $params);
 384              $this->fail("Expecting an exception, none occurred");
 385          } catch (\moodle_exception $e) {
 386              $this->assertInstanceOf('dml_exception', $e);
 387          }
 388  
 389          // Too few params in sql.
 390          $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";
 391          $params = array('record2', 3);
 392          try {
 393              $DB->fix_sql_params($sql, $params);
 394              $this->fail("Expecting an exception, none occurred");
 395          } catch (\moodle_exception $e) {
 396              $this->assertInstanceOf('dml_exception', $e);
 397          }
 398  
 399          // Too many params in array: no error, just use what is necessary.
 400          $params[] = 1;
 401          $params[] = time();
 402          $sqlarray = $DB->fix_sql_params($sql, $params);
 403          $this->assertIsArray($sqlarray);
 404          $this->assertCount(3, $sqlarray[1]);
 405  
 406          // Named params missing from array.
 407          $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
 408          $params = array('wrongname' => 'record1', 'course' => 1);
 409          try {
 410              $DB->fix_sql_params($sql, $params);
 411              $this->fail("Expecting an exception, none occurred");
 412          } catch (\moodle_exception $e) {
 413              $this->assertInstanceOf('dml_exception', $e);
 414          }
 415  
 416          // Duplicate named param in query - this is a very important feature!!
 417          // it helps with debugging of sloppy code.
 418          $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";
 419          $params = array('name' => 'record2', 'course' => 3);
 420          try {
 421              $DB->fix_sql_params($sql, $params);
 422              $this->fail("Expecting an exception, none occurred");
 423          } catch (\moodle_exception $e) {
 424              $this->assertInstanceOf('dml_exception', $e);
 425          }
 426  
 427          // Extra named param is ignored.
 428          $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
 429          $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');
 430          $sqlarray = $DB->fix_sql_params($sql, $params);
 431          $this->assertIsArray($sqlarray);
 432          $this->assertCount(2, $sqlarray[1]);
 433  
 434          // Params exceeding 30 chars length.
 435          $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
 436          $params = array('long_placeholder_with_more_than_30' => 'record1');
 437          try {
 438              $DB->fix_sql_params($sql, $params);
 439              $this->fail("Expecting an exception, none occurred");
 440          } catch (\moodle_exception $e) {
 441              $this->assertInstanceOf('coding_exception', $e);
 442          }
 443  
 444          // Booleans in NAMED params are casting to 1/0 int.
 445          $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";
 446          $params = array(true, false);
 447          list($sql, $params) = $DB->fix_sql_params($sql, $params);
 448          $this->assertTrue(reset($params) === 1);
 449          $this->assertTrue(next($params) === 0);
 450  
 451          // Booleans in QM params are casting to 1/0 int.
 452          $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";
 453          $params = array('course1' => true, 'course2' => false);
 454          list($sql, $params) = $DB->fix_sql_params($sql, $params);
 455          $this->assertTrue(reset($params) === 1);
 456          $this->assertTrue(next($params) === 0);
 457  
 458          // Booleans in DOLLAR params are casting to 1/0 int.
 459          $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";
 460          $params = array(true, false);
 461          list($sql, $params) = $DB->fix_sql_params($sql, $params);
 462          $this->assertTrue(reset($params) === 1);
 463          $this->assertTrue(next($params) === 0);
 464  
 465          // No data types are touched except bool.
 466          $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";
 467          $inparams = array('abc', 'ABC', null, '1', 1, 1.4);
 468          list($sql, $params) = $DB->fix_sql_params($sql, $inparams);
 469          $this->assertSame(array_values($params), array_values($inparams));
 470      }
 471  
 472      /**
 473       * Test the database debugging as SQL comment.
 474       */
 475      public function test_add_sql_debugging() {
 476          global $CFG;
 477          $DB = $this->tdb;
 478  
 479          require_once($CFG->dirroot . '/lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php');
 480          $fixture = new \test_dml_sql_debugging_fixture($this);
 481  
 482          $sql = "SELECT * FROM {users}";
 483  
 484          $out = $fixture->four($sql);
 485  
 486          $CFG->debugsqltrace = 0;
 487          $this->assertEquals("SELECT * FROM {users}", $out);
 488  
 489          $CFG->debugsqltrace = 1;
 490          $out = $fixture->four($sql);
 491          $expected = <<<EOD
 492  SELECT * FROM {users}
 493  -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke()
 494  EOD;
 495          $this->assertEquals($this->unix_to_os_dirsep($expected), $out);
 496  
 497          $CFG->debugsqltrace = 2;
 498          $out = $fixture->four($sql);
 499          $expected = <<<EOD
 500  SELECT * FROM {users}
 501  -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke()
 502  -- line 74 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one()
 503  EOD;
 504          $this->assertEquals($this->unix_to_os_dirsep($expected), $out);
 505  
 506          $CFG->debugsqltrace = 5;
 507          $out = $fixture->four($sql);
 508          $expected = <<<EOD
 509  SELECT * FROM {users}
 510  -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke()
 511  -- line 74 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one()
 512  -- line 83 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->two()
 513  -- line 92 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->three()
 514  -- line 507 of /lib/dml/tests/dml_test.php: call to test_dml_sql_debugging_fixture->four()
 515  EOD;
 516          $this->assertEquals($this->unix_to_os_dirsep($expected), $out);
 517  
 518          $CFG->debugsqltrace = 0;
 519      }
 520  
 521      /**
 522       * Test the database debugging as SQL comment in anon class
 523       *
 524       * @covers ::add_sql_debugging
 525       */
 526      public function test_sql_debugging_anon_class() {
 527          global $CFG;
 528          $CFG->debugsqltrace = 100;
 529  
 530          // A anon class.
 531          $another = new class {
 532              /**
 533               * Just a test log function
 534               */
 535              public function get_site() {
 536                  global $DB;
 537  
 538                  return $DB->get_record('course', ['category' => 0]);
 539              }
 540          };
 541          $site = $another->get_site();
 542          $CFG->debugsqltrace = 0;
 543          $this->assertEquals(get_site(), $site);
 544      }
 545  
 546      public function test_strtok() {
 547          // Strtok was previously used by bound emulation, make sure it is not used any more.
 548          $DB = $this->tdb;
 549          $dbman = $this->tdb->get_manager();
 550  
 551          $table = $this->get_test_table();
 552          $tablename = $table->getName();
 553  
 554          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 555          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 556          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
 557          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 558          $dbman->create_table($table);
 559  
 560          $str = 'a?b?c?d';
 561          $this->assertSame(strtok($str, '?'), 'a');
 562  
 563          $DB->get_records($tablename, array('id'=>1));
 564  
 565          $this->assertSame(strtok('?'), 'b');
 566      }
 567  
 568      public function test_tweak_param_names() {
 569          // Note the tweak_param_names() method is only available in the oracle driver,
 570          // hence we look for expected results indirectly, by testing various DML methods.
 571          // with some "extreme" conditions causing the tweak to happen.
 572          $DB = $this->tdb;
 573          $dbman = $this->tdb->get_manager();
 574  
 575          $table = $this->get_test_table();
 576          $tablename = $table->getName();
 577  
 578          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 579          // Add some columns with 28 chars in the name.
 580          $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
 581          $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
 582          $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
 583          // Add some columns with 30 chars in the name.
 584          $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
 585          $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
 586          $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');
 587  
 588          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 589  
 590          $dbman->create_table($table);
 591  
 592          $this->assertTrue($dbman->table_exists($tablename));
 593  
 594          // Test insert record.
 595          $rec1 = new \stdClass();
 596          $rec1->long_int_columnname_with_28c = 28;
 597          $rec1->long_dec_columnname_with_28c = 28.28;
 598          $rec1->long_str_columnname_with_28c = '28';
 599          $rec1->long_int_columnname_with_30cxx = 30;
 600          $rec1->long_dec_columnname_with_30cxx = 30.30;
 601          $rec1->long_str_columnname_with_30cxx = '30';
 602  
 603          // Insert_record().
 604          $rec1->id = $DB->insert_record($tablename, $rec1);
 605          $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 606  
 607          // Update_record().
 608          $DB->update_record($tablename, $rec1);
 609          $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 610  
 611          // Set_field().
 612          $rec1->long_int_columnname_with_28c = 280;
 613          $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
 614              array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
 615          $rec1->long_dec_columnname_with_28c = 280.28;
 616          $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
 617              array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
 618          $rec1->long_str_columnname_with_28c = '280';
 619          $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
 620              array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
 621          $rec1->long_int_columnname_with_30cxx = 300;
 622          $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
 623              array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
 624          $rec1->long_dec_columnname_with_30cxx = 300.30;
 625          $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
 626              array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
 627          $rec1->long_str_columnname_with_30cxx = '300';
 628          $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
 629              array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
 630          $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 631  
 632          // Delete_records().
 633          $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
 634          $rec2->id = $DB->insert_record($tablename, $rec2);
 635          $this->assertEquals(2, $DB->count_records($tablename));
 636          $DB->delete_records($tablename, (array) $rec2);
 637          $this->assertEquals(1, $DB->count_records($tablename));
 638  
 639          // Get_recordset().
 640          $rs = $DB->get_recordset($tablename, (array) $rec1);
 641          $iterations = 0;
 642          foreach ($rs as $rec2) {
 643              $iterations++;
 644          }
 645          $rs->close();
 646          $this->assertEquals(1, $iterations);
 647          $this->assertEquals($rec1, $rec2);
 648  
 649          // Get_records().
 650          $recs = $DB->get_records($tablename, (array) $rec1);
 651          $this->assertCount(1, $recs);
 652          $this->assertEquals($rec1, reset($recs));
 653  
 654          // Get_fieldset_select().
 655          $select = 'id = :id AND
 656                     long_int_columnname_with_28c = :long_int_columnname_with_28c AND
 657                     long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
 658                     long_str_columnname_with_28c = :long_str_columnname_with_28c AND
 659                     long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
 660                     long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
 661                     long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
 662          $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
 663          $this->assertCount(1, $fields);
 664          $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));
 665          $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
 666          $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));
 667          $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
 668          $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields));
 669          $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
 670          $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields));
 671          $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
 672          $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields));
 673          $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
 674          $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));
 675  
 676          // Overlapping placeholders (progressive str_replace).
 677          $overlapselect = 'id = :p AND
 678                     long_int_columnname_with_28c = :param1 AND
 679                     long_dec_columnname_with_28c = :param2 AND
 680                     long_str_columnname_with_28c = :param_with_29_characters_long AND
 681                     long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
 682                     long_dec_columnname_with_30cxx = :param_ AND
 683                     long_str_columnname_with_30cxx = :param__';
 684          $overlapparams = array(
 685              'p' => $rec1->id,
 686              'param1' => $rec1->long_int_columnname_with_28c,
 687              'param2' => $rec1->long_dec_columnname_with_28c,
 688              'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
 689              'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
 690              'param_' => $rec1->long_dec_columnname_with_30cxx,
 691              'param__' => $rec1->long_str_columnname_with_30cxx);
 692          $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
 693          $this->assertCount(1, $recs);
 694          $this->assertEquals($rec1, reset($recs));
 695  
 696          // Execute().
 697          $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
 698          $this->assertEquals(0, $DB->count_records($tablename));
 699      }
 700  
 701      public function test_get_tables() {
 702          $DB = $this->tdb;
 703          $dbman = $this->tdb->get_manager();
 704  
 705          // Need to test with multiple DBs.
 706          $table = $this->get_test_table();
 707          $tablename = $table->getName();
 708  
 709          $original_count = count($DB->get_tables());
 710  
 711          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 712          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 713  
 714          $dbman->create_table($table);
 715          $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
 716  
 717          $dbman->drop_table($table);
 718          $this->assertTrue(count($DB->get_tables()) == $original_count);
 719      }
 720  
 721      public function test_get_indexes() {
 722          $DB = $this->tdb;
 723          $dbman = $this->tdb->get_manager();
 724  
 725          $table = $this->get_test_table();
 726          $tablename = $table->getName();
 727  
 728          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 729          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 730          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 731          $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
 732          $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
 733          $dbman->create_table($table);
 734  
 735          $indices = $DB->get_indexes($tablename);
 736          $this->assertIsArray($indices);
 737          $this->assertCount(2, $indices);
 738          // We do not care about index names for now.
 739          $first = array_shift($indices);
 740          $second = array_shift($indices);
 741          if (count($first['columns']) == 2) {
 742              $composed = $first;
 743              $single   = $second;
 744          } else {
 745              $composed = $second;
 746              $single   = $first;
 747          }
 748          $this->assertFalse($single['unique']);
 749          $this->assertTrue($composed['unique']);
 750          $this->assertCount(1, $single['columns']);
 751          $this->assertCount(2, $composed['columns']);
 752          $this->assertSame('course', $single['columns'][0]);
 753          $this->assertSame('course', $composed['columns'][0]);
 754          $this->assertSame('id', $composed['columns'][1]);
 755      }
 756  
 757      /**
 758       * Let's verify get_indexes() when we mix null and not null columns in unique indexes.
 759       *
 760       * Some databases, for unique indexes of this type, need to create function indexes to
 761       * provide cross-db behaviour. Here we check that those indexes don't break get_indexes().
 762       *
 763       * Note that, strictly speaking, unique indexes on null columns are far from ideal. Both
 764       * conceptually and also in practice, because they cause DBs to use full scans in a
 765       * number of situations. But if we support them, we need to ensure get_indexes() work on them.
 766       */
 767      public function test_get_indexes_unique_mixed_nullability() {
 768          $DB = $this->tdb;
 769          $dbman = $this->tdb->get_manager();
 770          $table = $this->get_test_table();
 771          $tablename = $table->getName();
 772  
 773          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 774          $table->add_field('nullable01', XMLDB_TYPE_INTEGER, 10, null, null, null, null);
 775          $table->add_field('nullable02', XMLDB_TYPE_INTEGER, 10, null, null, null, null);
 776          $table->add_field('nonullable01', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 777          $table->add_field('nonullable02', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 778          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 779          $indexcolumns = ['nullable01', 'nonullable01', 'nullable02', 'nonullable02'];
 780          $table->add_index('course-id', XMLDB_INDEX_UNIQUE, $indexcolumns);
 781          $dbman->create_table($table);
 782  
 783          $indexes = $DB->get_indexes($tablename);
 784          $this->assertIsArray($indexes);
 785          $this->assertCount(1, $indexes);
 786  
 787          $index = array_shift($indexes);
 788          $this->assertTrue($index['unique']);
 789          $this->assertSame($indexcolumns, $index['columns']);
 790      }
 791  
 792      public function test_get_columns() {
 793          $DB = $this->tdb;
 794          $dbman = $this->tdb->get_manager();
 795  
 796          $table = $this->get_test_table();
 797          $tablename = $table->getName();
 798  
 799          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 800          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 801          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
 802          $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
 803          $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 804          $table->add_field('oneintnodefault', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null);
 805          $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
 806          $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
 807          $table->add_field('onenumnodefault', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
 808          $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null, 300);
 809          $table->add_field('onefloatnodefault', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null);
 810          $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);
 811          $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');
 812          $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');
 813          $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');
 814          $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0');
 815          $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0');
 816          $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0');
 817          $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0');
 818          $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0');
 819          $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0');
 820          $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0');
 821          $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0');
 822          $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0');
 823          $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
 824          $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0');
 825          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 826          $dbman->create_table($table);
 827  
 828          $columns = $DB->get_columns($tablename);
 829          $this->assertIsArray($columns);
 830  
 831          $fields = $table->getFields();
 832          $this->assertCount(count($columns), $fields);
 833  
 834          $field = $columns['id'];
 835          $this->assertSame('R', $field->meta_type);
 836          $this->assertTrue($field->auto_increment);
 837          $this->assertTrue($field->unique);
 838  
 839          $field = $columns['course'];
 840          $this->assertSame('I', $field->meta_type);
 841          $this->assertFalse($field->auto_increment);
 842          $this->assertTrue($field->has_default);
 843          $this->assertEquals(0, $field->default_value);
 844          $this->assertTrue($field->not_null);
 845  
 846          for ($i=1; $i<=10; $i++) {
 847              $field = $columns['someint'.$i];
 848              $this->assertSame('I', $field->meta_type);
 849              $this->assertGreaterThanOrEqual($i, $field->max_length);
 850          }
 851          $field = $columns['someint18'];
 852          $this->assertSame('I', $field->meta_type);
 853          $this->assertGreaterThanOrEqual(18, $field->max_length);
 854  
 855          $field = $columns['name'];
 856          $this->assertSame('C', $field->meta_type);
 857          $this->assertFalse($field->auto_increment);
 858          $this->assertEquals(255, $field->max_length);
 859          $this->assertTrue($field->has_default);
 860          $this->assertSame('lala', $field->default_value);
 861          $this->assertFalse($field->not_null);
 862  
 863          $field = $columns['description'];
 864          $this->assertSame('X', $field->meta_type);
 865          $this->assertFalse($field->auto_increment);
 866          $this->assertFalse($field->has_default);
 867          $this->assertNull($field->default_value);
 868          $this->assertFalse($field->not_null);
 869  
 870          $field = $columns['oneint'];
 871          $this->assertSame('I', $field->meta_type);
 872          $this->assertFalse($field->auto_increment);
 873          $this->assertTrue($field->has_default);
 874          $this->assertEquals(0, $field->default_value);
 875          $this->assertTrue($field->not_null);
 876  
 877          $field = $columns['oneintnodefault'];
 878          $this->assertSame('I', $field->meta_type);
 879          $this->assertFalse($field->auto_increment);
 880          $this->assertFalse($field->has_default);
 881          $this->assertNull($field->default_value);
 882          $this->assertTrue($field->not_null);
 883  
 884          $field = $columns['enumfield'];
 885          $this->assertSame('C', $field->meta_type);
 886          $this->assertFalse($field->auto_increment);
 887          $this->assertSame('test2', $field->default_value);
 888          $this->assertTrue($field->not_null);
 889  
 890          $field = $columns['onenum'];
 891          $this->assertSame('N', $field->meta_type);
 892          $this->assertFalse($field->auto_increment);
 893          $this->assertEquals(10, $field->max_length);
 894          $this->assertEquals(2, $field->scale);
 895          $this->assertTrue($field->has_default);
 896          $this->assertEquals(200.0, $field->default_value);
 897          $this->assertFalse($field->not_null);
 898  
 899          $field = $columns['onenumnodefault'];
 900          $this->assertSame('N', $field->meta_type);
 901          $this->assertFalse($field->auto_increment);
 902          $this->assertEquals(10, $field->max_length);
 903          $this->assertEquals(2, $field->scale);
 904          $this->assertFalse($field->has_default);
 905          $this->assertNull($field->default_value);
 906          $this->assertFalse($field->not_null);
 907  
 908          $field = $columns['onefloat'];
 909          $this->assertSame('N', $field->meta_type);
 910          $this->assertFalse($field->auto_increment);
 911          $this->assertTrue($field->has_default);
 912          $this->assertEquals(300.0, $field->default_value);
 913          $this->assertTrue($field->not_null);
 914  
 915          $field = $columns['onefloatnodefault'];
 916          $this->assertSame('N', $field->meta_type);
 917          $this->assertFalse($field->auto_increment);
 918          $this->assertFalse($field->has_default);
 919          $this->assertNull($field->default_value);
 920          $this->assertTrue($field->not_null);
 921  
 922          $field = $columns['anotherfloat'];
 923          $this->assertSame('N', $field->meta_type);
 924          $this->assertFalse($field->auto_increment);
 925          $this->assertTrue($field->has_default);
 926          $this->assertEquals(400.0, $field->default_value);
 927          $this->assertFalse($field->not_null);
 928  
 929          // Test negative defaults in numerical columns.
 930          $field = $columns['negativedfltint'];
 931          $this->assertTrue($field->has_default);
 932          $this->assertEquals(-1, $field->default_value);
 933  
 934          $field = $columns['negativedfltnumber'];
 935          $this->assertTrue($field->has_default);
 936          $this->assertEquals(-2, $field->default_value);
 937  
 938          $field = $columns['negativedfltfloat'];
 939          $this->assertTrue($field->has_default);
 940          $this->assertEquals(-3, $field->default_value);
 941  
 942          for ($i = 0; $i < count($columns); $i++) {
 943              if ($i == 0) {
 944                  $next_column = reset($columns);
 945                  $next_field  = reset($fields);
 946              } else {
 947                  $next_column = next($columns);
 948                  $next_field  = next($fields);
 949              }
 950  
 951              $this->assertEquals($next_column->name, $next_field->getName());
 952          }
 953  
 954          // Test get_columns for non-existing table returns empty array. MDL-30147.
 955          $columns = $DB->get_columns('xxxx');
 956          $this->assertEquals(array(), $columns);
 957  
 958          // Create something similar to "context_temp" with id column without sequence.
 959          $dbman->drop_table($table);
 960          $table = $this->get_test_table();
 961          $tablename = $table->getName();
 962          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
 963          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 964          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 965          $dbman->create_table($table);
 966  
 967          $columns = $DB->get_columns($tablename);
 968          $this->assertFalse($columns['id']->auto_increment);
 969      }
 970  
 971      public function test_get_manager() {
 972          $DB = $this->tdb;
 973          $dbman = $this->tdb->get_manager();
 974  
 975          $this->assertInstanceOf('database_manager', $dbman);
 976      }
 977  
 978      public function test_setup_is_unicodedb() {
 979          $DB = $this->tdb;
 980          $this->assertTrue($DB->setup_is_unicodedb());
 981      }
 982  
 983      public function test_set_debug() { // Tests get_debug() too.
 984          $DB = $this->tdb;
 985          $dbman = $this->tdb->get_manager();
 986  
 987          $table = $this->get_test_table();
 988          $tablename = $table->getName();
 989  
 990          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 991          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 992          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 993          $dbman->create_table($table);
 994  
 995          $sql = "SELECT * FROM {{$tablename}}";
 996  
 997          $prevdebug = $DB->get_debug();
 998  
 999          ob_start();
1000          $DB->set_debug(true);
1001          $this->assertTrue($DB->get_debug());
1002          $DB->execute($sql);
1003          $DB->set_debug(false);
1004          $this->assertFalse($DB->get_debug());
1005          $debuginfo = ob_get_contents();
1006          ob_end_clean();
1007          $this->assertFalse($debuginfo === '');
1008  
1009          ob_start();
1010          $DB->execute($sql);
1011          $debuginfo = ob_get_contents();
1012          ob_end_clean();
1013          $this->assertTrue($debuginfo === '');
1014  
1015          $DB->set_debug($prevdebug);
1016      }
1017  
1018      public function test_execute() {
1019          $DB = $this->tdb;
1020          $dbman = $this->tdb->get_manager();
1021  
1022          $table1 = $this->get_test_table('1');
1023          $tablename1 = $table1->getName();
1024          $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1025          $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1026          $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1027          $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1028          $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1029          $dbman->create_table($table1);
1030  
1031          $table2 = $this->get_test_table('2');
1032          $tablename2 = $table2->getName();
1033          $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1034          $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1035          $table2->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1036          $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1037          $dbman->create_table($table2);
1038  
1039          $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));
1040          $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));
1041          $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));
1042          $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));
1043  
1044          // Select results are ignored.
1045          $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";
1046          $this->assertTrue($DB->execute($sql, array('course'=>3)));
1047  
1048          // Throw exception on error.
1049          $sql = "XXUPDATE SET XSSD";
1050          try {
1051              $DB->execute($sql);
1052              $this->fail("Expecting an exception, none occurred");
1053          } catch (\moodle_exception $e) {
1054              $this->assertInstanceOf('dml_exception', $e);
1055          }
1056  
1057          // Update records.
1058          $sql = "UPDATE {{$tablename1}}
1059                     SET course = 6
1060                   WHERE course = ?";
1061          $this->assertTrue($DB->execute($sql, array('3')));
1062          $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6)));
1063  
1064          // Update records with subquery condition.
1065          // Confirm that the option not using table aliases is cross-db.
1066          $sql = "UPDATE {{$tablename1}}
1067                     SET course = 0
1068                   WHERE NOT EXISTS (
1069                             SELECT course
1070                               FROM {{$tablename2}} tbl2
1071                              WHERE tbl2.course = {{$tablename1}}.course
1072                                AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed.
1073          $this->assertTrue($DB->execute($sql));
1074  
1075          // Insert from one into second table.
1076          $sql = "INSERT INTO {{$tablename2}} (course)
1077  
1078                  SELECT course
1079                    FROM {{$tablename1}}";
1080          $this->assertTrue($DB->execute($sql));
1081          $this->assertEquals(4, $DB->count_records($tablename2));
1082  
1083          // Insert a TEXT with raw SQL, binding TEXT params.
1084          $course = 9999;
1085          $onetext = file_get_contents(__DIR__ . '/fixtures/clob.txt');
1086          $sql = "INSERT INTO {{$tablename2}} (course, onetext)
1087                  VALUES (:course, :onetext)";
1088          $DB->execute($sql, array('course' => $course, 'onetext' => $onetext));
1089          $records = $DB->get_records($tablename2, array('course' => $course));
1090          $this->assertCount(1, $records);
1091          $record = reset($records);
1092          $this->assertSame($onetext, $record->onetext);
1093  
1094          // Update a TEXT with raw SQL, binding TEXT params.
1095          $newcourse = 10000;
1096          $newonetext = file_get_contents(__DIR__ . '/fixtures/clob.txt') . '- updated';
1097          $sql = "UPDATE {{$tablename2}} SET course = :newcourse, onetext = :newonetext
1098                  WHERE course = :oldcourse";
1099          $DB->execute($sql, array('oldcourse' => $course, 'newcourse' => $newcourse, 'newonetext' => $newonetext));
1100          $records = $DB->get_records($tablename2, array('course' => $course));
1101          $this->assertCount(0, $records);
1102          $records = $DB->get_records($tablename2, array('course' => $newcourse));
1103          $this->assertCount(1, $records);
1104          $record = reset($records);
1105          $this->assertSame($newonetext, $record->onetext);
1106      }
1107  
1108      public function test_get_recordset() {
1109          $DB = $this->tdb;
1110          $dbman = $DB->get_manager();
1111  
1112          $table = $this->get_test_table();
1113          $tablename = $table->getName();
1114  
1115          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1116          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1117          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1118          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1119          $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1120          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1121          $dbman->create_table($table);
1122  
1123          $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'),
1124              array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),
1125              array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));
1126  
1127          foreach ($data as $key => $record) {
1128              $data[$key]['id'] = $DB->insert_record($tablename, $record);
1129          }
1130  
1131          // Standard recordset iteration.
1132          $rs = $DB->get_recordset($tablename);
1133          $this->assertInstanceOf('moodle_recordset', $rs);
1134          reset($data);
1135          foreach ($rs as $record) {
1136              $data_record = current($data);
1137              foreach ($record as $k => $v) {
1138                  $this->assertEquals($data_record[$k], $v);
1139              }
1140              next($data);
1141          }
1142          $rs->close();
1143  
1144          // Iterator style usage.
1145          $rs = $DB->get_recordset($tablename);
1146          $this->assertInstanceOf('moodle_recordset', $rs);
1147          reset($data);
1148          while ($rs->valid()) {
1149              $record = $rs->current();
1150              $data_record = current($data);
1151              foreach ($record as $k => $v) {
1152                  $this->assertEquals($data_record[$k], $v);
1153              }
1154              next($data);
1155              $rs->next();
1156          }
1157          $rs->close();
1158  
1159          // Make sure rewind is ignored.
1160          $rs = $DB->get_recordset($tablename);
1161          $this->assertInstanceOf('moodle_recordset', $rs);
1162          reset($data);
1163          $i = 0;
1164          foreach ($rs as $record) {
1165              $i++;
1166              $rs->rewind();
1167              if ($i > 10) {
1168                  $this->fail('revind not ignored in recordsets');
1169                  break;
1170              }
1171              $data_record = current($data);
1172              foreach ($record as $k => $v) {
1173                  $this->assertEquals($data_record[$k], $v);
1174              }
1175              next($data);
1176          }
1177          $rs->close();
1178  
1179          // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
1180          $conditions = array('onetext' => '1');
1181          try {
1182              $rs = $DB->get_recordset($tablename, $conditions);
1183              $this->fail('An Exception is missing, expected due to equating of text fields');
1184          } catch (\moodle_exception $e) {
1185              $this->assertInstanceOf('dml_exception', $e);
1186              $this->assertSame('textconditionsnotallowed', $e->errorcode);
1187          }
1188  
1189          // Test nested iteration.
1190          $rs1 = $DB->get_recordset($tablename);
1191          $i = 0;
1192          foreach ($rs1 as $record1) {
1193              $rs2 = $DB->get_recordset($tablename);
1194              $i++;
1195              $j = 0;
1196              foreach ($rs2 as $record2) {
1197                  $j++;
1198              }
1199              $rs2->close();
1200              $this->assertCount($j, $data);
1201          }
1202          $rs1->close();
1203          $this->assertCount($i, $data);
1204  
1205          // Notes:
1206          //  * limits are tested in test_get_recordset_sql()
1207          //  * where_clause() is used internally and is tested in test_get_records()
1208      }
1209  
1210      public function test_get_recordset_static() {
1211          $DB = $this->tdb;
1212          $dbman = $DB->get_manager();
1213  
1214          $table = $this->get_test_table();
1215          $tablename = $table->getName();
1216  
1217          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1218          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1219          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1220          $dbman->create_table($table);
1221  
1222          $DB->insert_record($tablename, array('course' => 1));
1223          $DB->insert_record($tablename, array('course' => 2));
1224          $DB->insert_record($tablename, array('course' => 3));
1225          $DB->insert_record($tablename, array('course' => 4));
1226  
1227          $rs = $DB->get_recordset($tablename, array(), 'id');
1228  
1229          $DB->set_field($tablename, 'course', 666, array('course'=>1));
1230          $DB->delete_records($tablename, array('course'=>2));
1231  
1232          $i = 0;
1233          foreach ($rs as $record) {
1234              $i++;
1235              $this->assertEquals($i, $record->course);
1236          }
1237          $rs->close();
1238          $this->assertEquals(4, $i);
1239  
1240          // Now repeat with limits because it may use different code.
1241          $DB->delete_records($tablename, array());
1242  
1243          $DB->insert_record($tablename, array('course' => 1));
1244          $DB->insert_record($tablename, array('course' => 2));
1245          $DB->insert_record($tablename, array('course' => 3));
1246          $DB->insert_record($tablename, array('course' => 4));
1247  
1248          $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3);
1249  
1250          $DB->set_field($tablename, 'course', 666, array('course'=>1));
1251          $DB->delete_records($tablename, array('course'=>2));
1252  
1253          $i = 0;
1254          foreach ($rs as $record) {
1255              $i++;
1256              $this->assertEquals($i, $record->course);
1257          }
1258          $rs->close();
1259          $this->assertEquals(3, $i);
1260      }
1261  
1262      public function test_get_recordset_iterator_keys() {
1263          $DB = $this->tdb;
1264          $dbman = $DB->get_manager();
1265  
1266          $table = $this->get_test_table();
1267          $tablename = $table->getName();
1268  
1269          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1270          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1271          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1272          $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1273          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1274          $dbman->create_table($table);
1275  
1276          $data = array(array('course' => 3, 'name' => 'record1'),
1277              array('course' => 3, 'name' => 'record2'),
1278              array('course' => 5, 'name' => 'record3'));
1279          foreach ($data as $key => $record) {
1280              $data[$key]['id'] = $DB->insert_record($tablename, $record);
1281          }
1282  
1283          // Test repeated numeric keys are returned ok.
1284          $rs = $DB->get_recordset($tablename, null, null, 'course, name, id');
1285  
1286          reset($data);
1287          $count = 0;
1288          foreach ($rs as $key => $record) {
1289              $data_record = current($data);
1290              $this->assertEquals($data_record['course'], $key);
1291              next($data);
1292              $count++;
1293          }
1294          $rs->close();
1295          $this->assertEquals(3, $count);
1296  
1297          // Test string keys are returned ok.
1298          $rs = $DB->get_recordset($tablename, null, null, 'name, course, id');
1299  
1300          reset($data);
1301          $count = 0;
1302          foreach ($rs as $key => $record) {
1303              $data_record = current($data);
1304              $this->assertEquals($data_record['name'], $key);
1305              next($data);
1306              $count++;
1307          }
1308          $rs->close();
1309          $this->assertEquals(3, $count);
1310  
1311          // Test numeric not starting in 1 keys are returned ok.
1312          $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name');
1313  
1314          $data = array_reverse($data);
1315          reset($data);
1316          $count = 0;
1317          foreach ($rs as $key => $record) {
1318              $data_record = current($data);
1319              $this->assertEquals($data_record['id'], $key);
1320              next($data);
1321              $count++;
1322          }
1323          $rs->close();
1324          $this->assertEquals(3, $count);
1325      }
1326  
1327      public function test_get_recordset_list() {
1328          $DB = $this->tdb;
1329          $dbman = $DB->get_manager();
1330  
1331          $table = $this->get_test_table();
1332          $tablename = $table->getName();
1333  
1334          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1335          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
1336          $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1337          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1338          $dbman->create_table($table);
1339  
1340          $DB->insert_record($tablename, array('course' => 3));
1341          $DB->insert_record($tablename, array('course' => 3));
1342          $DB->insert_record($tablename, array('course' => 5));
1343          $DB->insert_record($tablename, array('course' => 2));
1344          $DB->insert_record($tablename, array('course' => null));
1345          $DB->insert_record($tablename, array('course' => 1));
1346          $DB->insert_record($tablename, array('course' => 0));
1347  
1348          $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
1349          $counter = 0;
1350          foreach ($rs as $record) {
1351              $counter++;
1352          }
1353          $this->assertEquals(3, $counter);
1354          $rs->close();
1355  
1356          $rs = $DB->get_recordset_list($tablename, 'course', array(3));
1357          $counter = 0;
1358          foreach ($rs as $record) {
1359              $counter++;
1360          }
1361          $this->assertEquals(2, $counter);
1362          $rs->close();
1363  
1364          $rs = $DB->get_recordset_list($tablename, 'course', array(null));
1365          $counter = 0;
1366          foreach ($rs as $record) {
1367              $counter++;
1368          }
1369          $this->assertEquals(1, $counter);
1370          $rs->close();
1371  
1372          $rs = $DB->get_recordset_list($tablename, 'course', array(6, null));
1373          $counter = 0;
1374          foreach ($rs as $record) {
1375              $counter++;
1376          }
1377          $this->assertEquals(1, $counter);
1378          $rs->close();
1379  
1380          $rs = $DB->get_recordset_list($tablename, 'course', array(null, 5, 5, 5));
1381          $counter = 0;
1382          foreach ($rs as $record) {
1383              $counter++;
1384          }
1385          $this->assertEquals(2, $counter);
1386          $rs->close();
1387  
1388          $rs = $DB->get_recordset_list($tablename, 'course', array(true));
1389          $counter = 0;
1390          foreach ($rs as $record) {
1391              $counter++;
1392          }
1393          $this->assertEquals(1, $counter);
1394          $rs->close();
1395  
1396          $rs = $DB->get_recordset_list($tablename, 'course', array(false));
1397          $counter = 0;
1398          foreach ($rs as $record) {
1399              $counter++;
1400          }
1401          $this->assertEquals(1, $counter);
1402          $rs->close();
1403  
1404          $rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645.
1405  
1406          $counter = 0;
1407          foreach ($rs as $record) {
1408              $counter++;
1409          }
1410          $rs->close();
1411          $this->assertEquals(0, $counter);
1412  
1413          // Notes:
1414          //  * limits are tested in test_get_recordset_sql()
1415          //  * where_clause() is used internally and is tested in test_get_records()
1416      }
1417  
1418      public function test_get_recordset_select() {
1419          $DB = $this->tdb;
1420          $dbman = $DB->get_manager();
1421  
1422          $table = $this->get_test_table();
1423          $tablename = $table->getName();
1424  
1425          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1426          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1427          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1428          $dbman->create_table($table);
1429  
1430          $DB->insert_record($tablename, array('course' => 3));
1431          $DB->insert_record($tablename, array('course' => 3));
1432          $DB->insert_record($tablename, array('course' => 5));
1433          $DB->insert_record($tablename, array('course' => 2));
1434  
1435          $rs = $DB->get_recordset_select($tablename, '');
1436          $counter = 0;
1437          foreach ($rs as $record) {
1438              $counter++;
1439          }
1440          $rs->close();
1441          $this->assertEquals(4, $counter);
1442  
1443          $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3'));
1444          $counter = 0;
1445          foreach ($rs as $record) {
1446              $counter++;
1447          }
1448          $rs->close();
1449          $this->assertEquals(2, $counter);
1450  
1451          // Notes:
1452          //  * limits are tested in test_get_recordset_sql()
1453      }
1454  
1455      public function test_get_recordset_sql() {
1456          $DB = $this->tdb;
1457          $dbman = $DB->get_manager();
1458  
1459          $table = $this->get_test_table();
1460          $tablename = $table->getName();
1461  
1462          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1463          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1464          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1465          $dbman->create_table($table);
1466  
1467          $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1468          $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1469          $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1470          $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1471          $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1472          $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1473          $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1474  
1475          $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1476          $counter = 0;
1477          foreach ($rs as $record) {
1478              $counter++;
1479          }
1480          $rs->close();
1481          $this->assertEquals(2, $counter);
1482  
1483          // Limits - only need to test this case, the rest have been tested by test_get_records_sql()
1484          // only limitfrom = skips that number of records.
1485          $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1486          $records = array();
1487          foreach ($rs as $key => $record) {
1488              $records[$key] = $record;
1489          }
1490          $rs->close();
1491          $this->assertCount(5, $records);
1492          $this->assertEquals($inskey3, reset($records)->id);
1493          $this->assertEquals($inskey7, end($records)->id);
1494  
1495          // Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here.
1496      }
1497  
1498      public function test_export_table_recordset() {
1499          $DB = $this->tdb;
1500          $dbman = $DB->get_manager();
1501  
1502          $table = $this->get_test_table();
1503          $tablename = $table->getName();
1504  
1505          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1506          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1507          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1508          $dbman->create_table($table);
1509  
1510          $ids = array();
1511          $ids[] = $DB->insert_record($tablename, array('course' => 3));
1512          $ids[] = $DB->insert_record($tablename, array('course' => 5));
1513          $ids[] = $DB->insert_record($tablename, array('course' => 4));
1514          $ids[] = $DB->insert_record($tablename, array('course' => 3));
1515          $ids[] = $DB->insert_record($tablename, array('course' => 2));
1516          $ids[] = $DB->insert_record($tablename, array('course' => 1));
1517          $ids[] = $DB->insert_record($tablename, array('course' => 0));
1518  
1519          $rs = $DB->export_table_recordset($tablename);
1520          $rids = array();
1521          foreach ($rs as $record) {
1522              $rids[] = $record->id;
1523          }
1524          $rs->close();
1525          $this->assertEqualsCanonicalizing($ids, $rids);
1526      }
1527  
1528      public function test_get_records() {
1529          $DB = $this->tdb;
1530          $dbman = $DB->get_manager();
1531  
1532          $table = $this->get_test_table();
1533          $tablename = $table->getName();
1534  
1535          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1536          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1537          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1538          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1539          $dbman->create_table($table);
1540  
1541          $DB->insert_record($tablename, array('course' => 3));
1542          $DB->insert_record($tablename, array('course' => 3));
1543          $DB->insert_record($tablename, array('course' => 5));
1544          $DB->insert_record($tablename, array('course' => 2));
1545  
1546          // All records.
1547          $records = $DB->get_records($tablename);
1548          $this->assertCount(4, $records);
1549          $this->assertEquals(3, $records[1]->course);
1550          $this->assertEquals(3, $records[2]->course);
1551          $this->assertEquals(5, $records[3]->course);
1552          $this->assertEquals(2, $records[4]->course);
1553  
1554          // Records matching certain conditions.
1555          $records = $DB->get_records($tablename, array('course' => 3));
1556          $this->assertCount(2, $records);
1557          $this->assertEquals(3, $records[1]->course);
1558          $this->assertEquals(3, $records[2]->course);
1559  
1560          // All records sorted by course.
1561          $records = $DB->get_records($tablename, null, 'course');
1562          $this->assertCount(4, $records);
1563          $current_record = reset($records);
1564          $this->assertEquals(4, $current_record->id);
1565          $current_record = next($records);
1566          $this->assertEquals(1, $current_record->id);
1567          $current_record = next($records);
1568          $this->assertEquals(2, $current_record->id);
1569          $current_record = next($records);
1570          $this->assertEquals(3, $current_record->id);
1571  
1572          // All records, but get only one field.
1573          $records = $DB->get_records($tablename, null, '', 'id');
1574          $this->assertFalse(isset($records[1]->course));
1575          $this->assertTrue(isset($records[1]->id));
1576          $this->assertCount(4, $records);
1577  
1578          // Booleans into params.
1579          $records = $DB->get_records($tablename, array('course' => true));
1580          $this->assertCount(0, $records);
1581          $records = $DB->get_records($tablename, array('course' => false));
1582          $this->assertCount(0, $records);
1583  
1584          // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
1585          $conditions = array('onetext' => '1');
1586          try {
1587              $records = $DB->get_records($tablename, $conditions);
1588              if (debugging()) {
1589                  // Only in debug mode - hopefully all devs test code in debug mode...
1590                  $this->fail('An Exception is missing, expected due to equating of text fields');
1591              }
1592          } catch (\moodle_exception $e) {
1593              $this->assertInstanceOf('dml_exception', $e);
1594              $this->assertSame('textconditionsnotallowed', $e->errorcode);
1595          }
1596  
1597          // Test get_records passing non-existing table.
1598          // with params.
1599          try {
1600              $records = $DB->get_records('xxxx', array('id' => 0));
1601              $this->fail('An Exception is missing, expected due to query against non-existing table');
1602          } catch (\moodle_exception $e) {
1603              $this->assertInstanceOf('dml_exception', $e);
1604              if (debugging()) {
1605                  // Information for developers only, normal users get general error message.
1606                  $this->assertSame('ddltablenotexist', $e->errorcode);
1607              }
1608          }
1609  
1610          try {
1611              $records = $DB->get_records('xxxx', array('id' => '1'));
1612              $this->fail('An Exception is missing, expected due to query against non-existing table');
1613          } catch (\moodle_exception $e) {
1614              $this->assertInstanceOf('dml_exception', $e);
1615              if (debugging()) {
1616                  // Information for developers only, normal users get general error message.
1617                  $this->assertSame('ddltablenotexist', $e->errorcode);
1618              }
1619          }
1620  
1621          // Test get_records passing non-existing column.
1622          try {
1623              $records = $DB->get_records($tablename, array('xxxx' => 0));
1624              $this->fail('An Exception is missing, expected due to query against non-existing column');
1625          } catch (\moodle_exception $e) {
1626              $this->assertInstanceOf('dml_exception', $e);
1627              if (debugging()) {
1628                  // Information for developers only, normal users get general error message.
1629                  $this->assertSame('ddlfieldnotexist', $e->errorcode);
1630              }
1631          }
1632  
1633          // Note: delegate limits testing to test_get_records_sql().
1634      }
1635  
1636      public function test_get_records_list() {
1637          $DB = $this->tdb;
1638          $dbman = $DB->get_manager();
1639  
1640          $table = $this->get_test_table();
1641          $tablename = $table->getName();
1642  
1643          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1644          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1645          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1646          $dbman->create_table($table);
1647  
1648          $DB->insert_record($tablename, array('course' => 3));
1649          $DB->insert_record($tablename, array('course' => 3));
1650          $DB->insert_record($tablename, array('course' => 5));
1651          $DB->insert_record($tablename, array('course' => 2));
1652  
1653          $records = $DB->get_records_list($tablename, 'course', array(3, 2));
1654          $this->assertIsArray($records);
1655          $this->assertCount(3, $records);
1656          $this->assertEquals(1, reset($records)->id);
1657          $this->assertEquals(2, next($records)->id);
1658          $this->assertEquals(4, next($records)->id);
1659  
1660          $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645.
1661          $this->assertCount(0, $records);
1662  
1663          // Note: delegate limits testing to test_get_records_sql().
1664      }
1665  
1666      public function test_get_records_sql() {
1667          $DB = $this->tdb;
1668          $dbman = $DB->get_manager();
1669  
1670          $table = $this->get_test_table();
1671          $tablename = $table->getName();
1672  
1673          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1674          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1675          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1676          $dbman->create_table($table);
1677  
1678          $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1679          $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1680          $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1681          $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1682          $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1683          $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1684          $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1685  
1686          $table2 = $this->get_test_table("2");
1687          $tablename2 = $table2->getName();
1688          $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1689          $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1690          $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
1691          $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1692          $dbman->create_table($table2);
1693  
1694          $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing'));
1695          $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang'));
1696          $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung'));
1697          $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));
1698  
1699          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1700          $this->assertCount(2, $records);
1701          $this->assertEquals($inskey1, reset($records)->id);
1702          $this->assertEquals($inskey4, next($records)->id);
1703  
1704          // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test.
1705          $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1706          $this->assertDebuggingCalled();
1707          $this->assertCount(6, $records);
1708          set_debugging(DEBUG_MINIMAL);
1709          $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1710          $this->assertDebuggingNotCalled();
1711          $this->assertCount(6, $records);
1712          set_debugging(DEBUG_DEVELOPER);
1713  
1714          // Negative limits = no limits.
1715          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
1716          $this->assertCount(7, $records);
1717  
1718          // Zero limits = no limits.
1719          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
1720          $this->assertCount(7, $records);
1721  
1722          // Only limitfrom = skips that number of records.
1723          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1724          $this->assertCount(5, $records);
1725          $this->assertEquals($inskey3, reset($records)->id);
1726          $this->assertEquals($inskey7, end($records)->id);
1727  
1728          // Only limitnum = fetches that number of records.
1729          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
1730          $this->assertCount(3, $records);
1731          $this->assertEquals($inskey1, reset($records)->id);
1732          $this->assertEquals($inskey3, end($records)->id);
1733  
1734          // Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones.
1735          $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
1736          $this->assertCount(2, $records);
1737          $this->assertEquals($inskey4, reset($records)->id);
1738          $this->assertEquals($inskey5, end($records)->id);
1739  
1740          // Both limitfrom and limitnum in query having subqueris.
1741          // Note the subquery skips records with course = 0 and 3.
1742          $sql = "SELECT * FROM {{$tablename}}
1743                   WHERE course NOT IN (
1744                       SELECT course FROM {{$tablename}}
1745                        WHERE course IN (0, 3))
1746                  ORDER BY course";
1747          $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2.
1748          $this->assertCount(2, $records);
1749          $this->assertEquals($inskey6, reset($records)->id);
1750          $this->assertEquals($inskey5, end($records)->id);
1751          $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2.
1752          $this->assertCount(2, $records);
1753          $this->assertEquals($inskey3, reset($records)->id);
1754          $this->assertEquals($inskey2, end($records)->id);
1755  
1756          // Test 2 tables with aliases and limits with order bys.
1757          $sql = "SELECT t1.id, t1.course AS cid, t2.nametext
1758                    FROM {{$tablename}} t1, {{$tablename2}} t2
1759                   WHERE t2.course=t1.course
1760                ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');
1761          $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5.
1762          $this->assertCount(2, $records);
1763          $this->assertSame('5', end($records)->cid);
1764          $this->assertSame('4', reset($records)->cid);
1765  
1766          // Test 2 tables with aliases and limits with the highest INT limit works.
1767          $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}.
1768          $this->assertCount(2, $records);
1769          $this->assertSame('5', end($records)->cid);
1770          $this->assertSame('4', reset($records)->cid);
1771  
1772          // Test 2 tables with aliases and limits with order bys (limit which is highest INT number).
1773          $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses.
1774          $this->assertCount(0, $records);
1775  
1776          // Test 2 tables with aliases and limits with order bys (limit which s highest INT number).
1777          $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses.
1778          $this->assertCount(0, $records);
1779  
1780          // TODO: Test limits in queries having DISTINCT clauses.
1781  
1782          // Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here.
1783      }
1784  
1785      public function test_get_records_menu() {
1786          $DB = $this->tdb;
1787          $dbman = $DB->get_manager();
1788  
1789          $table = $this->get_test_table();
1790          $tablename = $table->getName();
1791  
1792          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1793          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1794          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1795          $dbman->create_table($table);
1796  
1797          $DB->insert_record($tablename, array('course' => 3));
1798          $DB->insert_record($tablename, array('course' => 3));
1799          $DB->insert_record($tablename, array('course' => 5));
1800          $DB->insert_record($tablename, array('course' => 2));
1801  
1802          $records = $DB->get_records_menu($tablename, array('course' => 3));
1803          $this->assertIsArray($records);
1804          $this->assertCount(2, $records);
1805          $this->assertNotEmpty($records[1]);
1806          $this->assertNotEmpty($records[2]);
1807          $this->assertEquals(3, $records[1]);
1808          $this->assertEquals(3, $records[2]);
1809  
1810          // Note: delegate limits testing to test_get_records_sql().
1811      }
1812  
1813      public function test_get_records_select_menu() {
1814          $DB = $this->tdb;
1815          $dbman = $DB->get_manager();
1816  
1817          $table = $this->get_test_table();
1818          $tablename = $table->getName();
1819  
1820          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1821          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1822          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1823          $dbman->create_table($table);
1824  
1825          $DB->insert_record($tablename, array('course' => 3));
1826          $DB->insert_record($tablename, array('course' => 2));
1827          $DB->insert_record($tablename, array('course' => 3));
1828          $DB->insert_record($tablename, array('course' => 5));
1829  
1830          $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
1831          $this->assertIsArray($records);
1832  
1833          $this->assertCount(3, $records);
1834          $this->assertArrayHasKey(1, $records);
1835          $this->assertArrayNotHasKey(2, $records);
1836          $this->assertArrayHasKey(3, $records);
1837          $this->assertArrayHasKey(4, $records);
1838          $this->assertSame('3', $records[1]);
1839          $this->assertSame('3', $records[3]);
1840          $this->assertSame('5', $records[4]);
1841  
1842          // Note: delegate limits testing to test_get_records_sql().
1843      }
1844  
1845      public function test_get_records_sql_menu() {
1846          $DB = $this->tdb;
1847          $dbman = $DB->get_manager();
1848  
1849          $table = $this->get_test_table();
1850          $tablename = $table->getName();
1851  
1852          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1853          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1854          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1855          $dbman->create_table($table);
1856  
1857          $DB->insert_record($tablename, array('course' => 3));
1858          $DB->insert_record($tablename, array('course' => 2));
1859          $DB->insert_record($tablename, array('course' => 3));
1860          $DB->insert_record($tablename, array('course' => 5));
1861  
1862          $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
1863          $this->assertIsArray($records);
1864  
1865          $this->assertCount(3, $records);
1866          $this->assertArrayHasKey(1, $records);
1867          $this->assertArrayNotHasKey(2, $records);
1868          $this->assertArrayHasKey(3, $records);
1869          $this->assertArrayHasKey(4, $records);
1870          $this->assertSame('3', $records[1]);
1871          $this->assertSame('3', $records[3]);
1872          $this->assertSame('5', $records[4]);
1873  
1874          // Note: delegate limits testing to test_get_records_sql().
1875      }
1876  
1877      public function test_get_record() {
1878          $DB = $this->tdb;
1879          $dbman = $DB->get_manager();
1880  
1881          $table = $this->get_test_table();
1882          $tablename = $table->getName();
1883  
1884          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1885          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1886          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1887          $dbman->create_table($table);
1888  
1889          $DB->insert_record($tablename, array('course' => 3));
1890          $DB->insert_record($tablename, array('course' => 2));
1891  
1892          $record = $DB->get_record($tablename, array('id' => 2));
1893          $this->assertInstanceOf(\stdClass::class, $record);
1894  
1895          $this->assertEquals(2, $record->course);
1896          $this->assertEquals(2, $record->id);
1897      }
1898  
1899  
1900      public function test_get_record_select() {
1901          $DB = $this->tdb;
1902          $dbman = $DB->get_manager();
1903  
1904          $table = $this->get_test_table();
1905          $tablename = $table->getName();
1906  
1907          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1908          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1909          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1910          $dbman->create_table($table);
1911  
1912          $DB->insert_record($tablename, array('course' => 3));
1913          $DB->insert_record($tablename, array('course' => 2));
1914  
1915          $record = $DB->get_record_select($tablename, "id = ?", array(2));
1916          $this->assertInstanceOf(\stdClass::class, $record);
1917  
1918          $this->assertEquals(2, $record->course);
1919  
1920          // Note: delegates limit testing to test_get_records_sql().
1921      }
1922  
1923      public function test_get_record_sql() {
1924          $DB = $this->tdb;
1925          $dbman = $DB->get_manager();
1926  
1927          $table = $this->get_test_table();
1928          $tablename = $table->getName();
1929  
1930          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1931          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1932          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1933          $dbman->create_table($table);
1934  
1935          $DB->insert_record($tablename, array('course' => 3));
1936          $DB->insert_record($tablename, array('course' => 2));
1937  
1938          // Standard use.
1939          $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
1940          $this->assertInstanceOf(\stdClass::class, $record);
1941          $this->assertEquals(2, $record->course);
1942          $this->assertEquals(2, $record->id);
1943  
1944          // Backwards compatibility with $ignoremultiple.
1945          $this->assertFalse((bool)IGNORE_MISSING);
1946          $this->assertTrue((bool)IGNORE_MULTIPLE);
1947  
1948          // Record not found - ignore.
1949          $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1950          $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1951  
1952          // Record not found error.
1953          try {
1954              $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
1955              $this->fail("Exception expected");
1956          } catch (dml_missing_record_exception $e) {
1957              $this->assertTrue(true);
1958          }
1959  
1960          $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1961          $this->assertDebuggingCalled();
1962          set_debugging(DEBUG_MINIMAL);
1963          $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1964          $this->assertDebuggingNotCalled();
1965          set_debugging(DEBUG_DEVELOPER);
1966  
1967          // Multiple matches ignored.
1968          $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1969  
1970          // Multiple found error.
1971          try {
1972              $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
1973              $this->fail("Exception expected");
1974          } catch (dml_multiple_records_exception $e) {
1975              $this->assertTrue(true);
1976          }
1977      }
1978  
1979      public function test_get_field() {
1980          $DB = $this->tdb;
1981          $dbman = $DB->get_manager();
1982  
1983          $table = $this->get_test_table();
1984          $tablename = $table->getName();
1985  
1986          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1987          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1988          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1989          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1990          $dbman->create_table($table);
1991  
1992          $id1 = $DB->insert_record($tablename, array('course' => 3));
1993          $DB->insert_record($tablename, array('course' => 5));
1994          $DB->insert_record($tablename, array('course' => 5));
1995  
1996          $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
1997          $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3)));
1998  
1999          $this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
2000          try {
2001              $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
2002              $this->fail('Exception expected due to missing record');
2003          } catch (dml_exception $ex) {
2004              $this->assertTrue(true);
2005          }
2006  
2007          $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
2008          $this->assertDebuggingNotCalled();
2009  
2010          $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
2011          $this->assertDebuggingCalled();
2012  
2013          // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
2014          $conditions = array('onetext' => '1');
2015          try {
2016              $DB->get_field($tablename, 'course', $conditions);
2017              if (debugging()) {
2018                  // Only in debug mode - hopefully all devs test code in debug mode...
2019                  $this->fail('An Exception is missing, expected due to equating of text fields');
2020              }
2021          } catch (\moodle_exception $e) {
2022              $this->assertInstanceOf('dml_exception', $e);
2023              $this->assertSame('textconditionsnotallowed', $e->errorcode);
2024          }
2025      }
2026  
2027      public function test_get_field_select() {
2028          $DB = $this->tdb;
2029          $dbman = $DB->get_manager();
2030  
2031          $table = $this->get_test_table();
2032          $tablename = $table->getName();
2033  
2034          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2035          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2036          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2037          $dbman->create_table($table);
2038  
2039          $DB->insert_record($tablename, array('course' => 3));
2040  
2041          $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
2042      }
2043  
2044      public function test_get_field_sql() {
2045          $DB = $this->tdb;
2046          $dbman = $DB->get_manager();
2047  
2048          $table = $this->get_test_table();
2049          $tablename = $table->getName();
2050  
2051          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2052          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2053          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2054          $dbman->create_table($table);
2055  
2056          $DB->insert_record($tablename, array('course' => 3));
2057  
2058          $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
2059      }
2060  
2061      public function test_get_fieldset_select() {
2062          $DB = $this->tdb;
2063          $dbman = $DB->get_manager();
2064  
2065          $table = $this->get_test_table();
2066          $tablename = $table->getName();
2067  
2068          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2069          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2070          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2071          $dbman->create_table($table);
2072  
2073          $DB->insert_record($tablename, array('course' => 1));
2074          $DB->insert_record($tablename, array('course' => 3));
2075          $DB->insert_record($tablename, array('course' => 2));
2076          $DB->insert_record($tablename, array('course' => 6));
2077  
2078          $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
2079          $this->assertIsArray($fieldset);
2080  
2081          $this->assertCount(3, $fieldset);
2082          $this->assertEquals(3, $fieldset[0]);
2083          $this->assertEquals(2, $fieldset[1]);
2084          $this->assertEquals(6, $fieldset[2]);
2085      }
2086  
2087      public function test_get_fieldset_sql() {
2088          $DB = $this->tdb;
2089          $dbman = $DB->get_manager();
2090  
2091          $table = $this->get_test_table();
2092          $tablename = $table->getName();
2093  
2094          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2095          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2096          $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2097          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2098          $dbman->create_table($table);
2099  
2100          $binarydata = '\\'.chr(241);
2101  
2102          $DB->insert_record($tablename, array('course' => 1, 'onebinary' => $binarydata));
2103          $DB->insert_record($tablename, array('course' => 3, 'onebinary' => $binarydata));
2104          $DB->insert_record($tablename, array('course' => 2, 'onebinary' => $binarydata));
2105          $DB->insert_record($tablename, array('course' => 6, 'onebinary' => $binarydata));
2106  
2107          $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
2108          $this->assertIsArray($fieldset);
2109  
2110          $this->assertCount(3, $fieldset);
2111          $this->assertEquals(2, $fieldset[0]);
2112          $this->assertEquals(3, $fieldset[1]);
2113          $this->assertEquals(4, $fieldset[2]);
2114  
2115          $fieldset = $DB->get_fieldset_sql("SELECT onebinary FROM {{$tablename}} WHERE course > ?", array(1));
2116          $this->assertIsArray($fieldset);
2117  
2118          $this->assertCount(3, $fieldset);
2119          $this->assertEquals($binarydata, $fieldset[0]);
2120          $this->assertEquals($binarydata, $fieldset[1]);
2121          $this->assertEquals($binarydata, $fieldset[2]);
2122      }
2123  
2124      public function test_insert_record_raw() {
2125          $DB = $this->tdb;
2126          $dbman = $DB->get_manager();
2127  
2128          $table = $this->get_test_table();
2129          $tablename = $table->getName();
2130  
2131          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2132          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2133          $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2134          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2135          $dbman->create_table($table);
2136  
2137          $record = (object)array('course' => 1, 'onechar' => 'xx');
2138          $before = clone($record);
2139          $result = $DB->insert_record_raw($tablename, $record);
2140          $this->assertSame(1, $result);
2141          $this->assertEquals($record, $before);
2142  
2143          $record = $DB->get_record($tablename, array('course' => 1));
2144          $this->assertInstanceOf(\stdClass::class, $record);
2145          $this->assertSame('xx', $record->onechar);
2146  
2147          $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
2148          $this->assertTrue($result);
2149  
2150          // Note: bulk not implemented yet.
2151          $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
2152          $record = $DB->get_record($tablename, array('course' => 3));
2153          $this->assertInstanceOf(\stdClass::class, $record);
2154          $this->assertSame('zz', $record->onechar);
2155  
2156          // Custom sequence (id) - returnid is ignored.
2157          $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
2158          $this->assertTrue($result);
2159          $record = $DB->get_record($tablename, array('id' => 10));
2160          $this->assertInstanceOf(\stdClass::class, $record);
2161          $this->assertSame('bb', $record->onechar);
2162  
2163          // Custom sequence - missing id error.
2164          try {
2165              $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
2166              $this->fail('Exception expected due to missing record');
2167          } catch (\coding_exception $ex) {
2168              $this->assertTrue(true);
2169          }
2170  
2171          // Wrong column error.
2172          try {
2173              $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
2174              $this->fail('Exception expected due to invalid column');
2175          } catch (dml_exception $ex) {
2176              $this->assertTrue(true);
2177          }
2178  
2179          // Create something similar to "context_temp" with id column without sequence.
2180          $dbman->drop_table($table);
2181          $table = $this->get_test_table();
2182          $tablename = $table->getName();
2183          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2184          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2185          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2186          $dbman->create_table($table);
2187  
2188          $record = (object)array('id'=>5, 'course' => 1);
2189          $DB->insert_record_raw($tablename, $record, false, false, true);
2190          $record = $DB->get_record($tablename, array());
2191          $this->assertEquals(5, $record->id);
2192      }
2193  
2194      public function test_insert_record() {
2195          // All the information in this test is fetched from DB by get_recordset() so we
2196          // have such method properly tested against nulls, empties and friends...
2197  
2198          $DB = $this->tdb;
2199          $dbman = $DB->get_manager();
2200  
2201          $table = $this->get_test_table();
2202          $tablename = $table->getName();
2203  
2204          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2205          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2206          $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2207          $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2208          $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2209          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2210          $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2211          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2212          $dbman->create_table($table);
2213  
2214          $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2215          $record = $DB->get_record($tablename, array('course' => 1));
2216          $this->assertEquals(1, $record->id);
2217          $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
2218          $this->assertEquals(200, $record->onenum);
2219          $this->assertSame('onestring', $record->onechar);
2220          $this->assertNull($record->onetext);
2221          $this->assertNull($record->onebinary);
2222  
2223          // Without returning id, bulk not implemented.
2224          $result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true));
2225          $record = $DB->get_record($tablename, array('course' => 99));
2226          $this->assertEquals(2, $record->id);
2227          $this->assertEquals(99, $record->course);
2228  
2229          // Check nulls are set properly for all types.
2230          $record = new \stdClass();
2231          $record->oneint = null;
2232          $record->onenum = null;
2233          $record->onechar = null;
2234          $record->onetext = null;
2235          $record->onebinary = null;
2236          $recid = $DB->insert_record($tablename, $record);
2237          $record = $DB->get_record($tablename, array('id' => $recid));
2238          $this->assertEquals(0, $record->course);
2239          $this->assertNull($record->oneint);
2240          $this->assertNull($record->onenum);
2241          $this->assertNull($record->onechar);
2242          $this->assertNull($record->onetext);
2243          $this->assertNull($record->onebinary);
2244  
2245          // Check zeros are set properly for all types.
2246          $record = new \stdClass();
2247          $record->oneint = 0;
2248          $record->onenum = 0;
2249          $recid = $DB->insert_record($tablename, $record);
2250          $record = $DB->get_record($tablename, array('id' => $recid));
2251          $this->assertEquals(0, $record->oneint);
2252          $this->assertEquals(0, $record->onenum);
2253  
2254          // Check booleans are set properly for all types.
2255          $record = new \stdClass();
2256          $record->oneint = true; // Trues.
2257          $record->onenum = true;
2258          $record->onechar = true;
2259          $record->onetext = true;
2260          $recid = $DB->insert_record($tablename, $record);
2261          $record = $DB->get_record($tablename, array('id' => $recid));
2262          $this->assertEquals(1, $record->oneint);
2263          $this->assertEquals(1, $record->onenum);
2264          $this->assertEquals(1, $record->onechar);
2265          $this->assertEquals(1, $record->onetext);
2266  
2267          $record = new \stdClass();
2268          $record->oneint = false; // Falses.
2269          $record->onenum = false;
2270          $record->onechar = false;
2271          $record->onetext = false;
2272          $recid = $DB->insert_record($tablename, $record);
2273          $record = $DB->get_record($tablename, array('id' => $recid));
2274          $this->assertEquals(0, $record->oneint);
2275          $this->assertEquals(0, $record->onenum);
2276          $this->assertEquals(0, $record->onechar);
2277          $this->assertEquals(0, $record->onetext);
2278  
2279          // Check string data causes exception in numeric types.
2280          $record = new \stdClass();
2281          $record->oneint = 'onestring';
2282          $record->onenum = 0;
2283          try {
2284              $DB->insert_record($tablename, $record);
2285              $this->fail("Expecting an exception, none occurred");
2286          } catch (\moodle_exception $e) {
2287              $this->assertInstanceOf('dml_exception', $e);
2288          }
2289          $record = new \stdClass();
2290          $record->oneint = 0;
2291          $record->onenum = 'onestring';
2292          try {
2293              $DB->insert_record($tablename, $record);
2294              $this->fail("Expecting an exception, none occurred");
2295          } catch (\moodle_exception $e) {
2296              $this->assertInstanceOf('dml_exception', $e);
2297          }
2298  
2299          // Check empty string data is stored as 0 in numeric datatypes.
2300          $record = new \stdClass();
2301          $record->oneint = ''; // Empty string.
2302          $record->onenum = 0;
2303          $recid = $DB->insert_record($tablename, $record);
2304          $record = $DB->get_record($tablename, array('id' => $recid));
2305          $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2306  
2307          $record = new \stdClass();
2308          $record->oneint = 0;
2309          $record->onenum = ''; // Empty string.
2310          $recid = $DB->insert_record($tablename, $record);
2311          $record = $DB->get_record($tablename, array('id' => $recid));
2312          $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2313  
2314          // Check empty strings are set properly in string types.
2315          $record = new \stdClass();
2316          $record->oneint = 0;
2317          $record->onenum = 0;
2318          $record->onechar = '';
2319          $record->onetext = '';
2320          $recid = $DB->insert_record($tablename, $record);
2321          $record = $DB->get_record($tablename, array('id' => $recid));
2322          $this->assertTrue($record->onechar === '');
2323          $this->assertTrue($record->onetext === '');
2324  
2325          // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
2326          $record = new \stdClass();
2327          $record->oneint = ((210.10 + 39.92) - 150.02);
2328          $record->onenum = ((210.10 + 39.92) - 150.02);
2329          $recid = $DB->insert_record($tablename, $record);
2330          $record = $DB->get_record($tablename, array('id' => $recid));
2331          $this->assertEquals(100, $record->oneint);
2332          $this->assertEquals(100, $record->onenum);
2333  
2334          // Check various quotes/backslashes combinations in string types.
2335          $teststrings = array(
2336              'backslashes and quotes alone (even): "" \'\' \\\\',
2337              'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2338              'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2339              'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2340          foreach ($teststrings as $teststring) {
2341              $record = new \stdClass();
2342              $record->onechar = $teststring;
2343              $record->onetext = $teststring;
2344              $recid = $DB->insert_record($tablename, $record);
2345              $record = $DB->get_record($tablename, array('id' => $recid));
2346              $this->assertEquals($teststring, $record->onechar);
2347              $this->assertEquals($teststring, $record->onetext);
2348          }
2349  
2350          // Check LOBs in text/binary columns.
2351          $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2352          $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2353          $record = new \stdClass();
2354          $record->onetext = $clob;
2355          $record->onebinary = $blob;
2356          $recid = $DB->insert_record($tablename, $record);
2357          $rs = $DB->get_recordset($tablename, array('id' => $recid));
2358          $record = $rs->current();
2359          $rs->close();
2360          $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2361          $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2362  
2363          // And "small" LOBs too, just in case.
2364          $newclob = substr($clob, 0, 500);
2365          $newblob = substr($blob, 0, 250);
2366          $record = new \stdClass();
2367          $record->onetext = $newclob;
2368          $record->onebinary = $newblob;
2369          $recid = $DB->insert_record($tablename, $record);
2370          $rs = $DB->get_recordset($tablename, array('id' => $recid));
2371          $record = $rs->current();
2372          $rs->close();
2373          $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2374          $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2375          $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
2376  
2377          // And "diagnostic" LOBs too, just in case.
2378          $newclob = '\'"\\;/ěščřžýáíé';
2379          $newblob = '\'"\\;/ěščřžýáíé';
2380          $record = new \stdClass();
2381          $record->onetext = $newclob;
2382          $record->onebinary = $newblob;
2383          $recid = $DB->insert_record($tablename, $record);
2384          $rs = $DB->get_recordset($tablename, array('id' => $recid));
2385          $record = $rs->current();
2386          $rs->close();
2387          $this->assertSame($newclob, $record->onetext);
2388          $this->assertSame($newblob, $record->onebinary);
2389          $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
2390  
2391          // Test data is not modified.
2392          $record = new \stdClass();
2393          $record->id     = -1; // Has to be ignored.
2394          $record->course = 3;
2395          $record->lalala = 'lalal'; // Unused.
2396          $before = clone($record);
2397          $DB->insert_record($tablename, $record);
2398          $this->assertEquals($record, $before);
2399  
2400          // Make sure the id is always increasing and never reuses the same id.
2401          $id1 = $DB->insert_record($tablename, array('course' => 3));
2402          $id2 = $DB->insert_record($tablename, array('course' => 3));
2403          $this->assertTrue($id1 < $id2);
2404          $DB->delete_records($tablename, array('id'=>$id2));
2405          $id3 = $DB->insert_record($tablename, array('course' => 3));
2406          $this->assertTrue($id2 < $id3);
2407          $DB->delete_records($tablename, array());
2408          $id4 = $DB->insert_record($tablename, array('course' => 3));
2409          $this->assertTrue($id3 < $id4);
2410  
2411          // Test saving a float in a CHAR column, and reading it back.
2412          $id = $DB->insert_record($tablename, array('onechar' => 1.0));
2413          $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2414          $id = $DB->insert_record($tablename, array('onechar' => 1e20));
2415          $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2416          $id = $DB->insert_record($tablename, array('onechar' => 1e-4));
2417          $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2418          $id = $DB->insert_record($tablename, array('onechar' => 1e-5));
2419          $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2420          $id = $DB->insert_record($tablename, array('onechar' => 1e-300));
2421          $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2422          $id = $DB->insert_record($tablename, array('onechar' => 1e300));
2423          $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2424  
2425          // Test saving a float in a TEXT column, and reading it back.
2426          $id = $DB->insert_record($tablename, array('onetext' => 1.0));
2427          $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2428          $id = $DB->insert_record($tablename, array('onetext' => 1e20));
2429          $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2430          $id = $DB->insert_record($tablename, array('onetext' => 1e-4));
2431          $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2432          $id = $DB->insert_record($tablename, array('onetext' => 1e-5));
2433          $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2434          $id = $DB->insert_record($tablename, array('onetext' => 1e-300));
2435          $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2436          $id = $DB->insert_record($tablename, array('onetext' => 1e300));
2437          $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2438  
2439          // Test that inserting data violating one unique key leads to error.
2440          // Empty the table completely.
2441          $this->assertTrue($DB->delete_records($tablename));
2442  
2443          // Add one unique constraint (index).
2444          $key = new xmldb_key('testuk', XMLDB_KEY_UNIQUE, array('course', 'oneint'));
2445          $dbman->add_key($table, $key);
2446  
2447          // Let's insert one record violating the constraint multiple times.
2448          $record = (object)array('course' => 1, 'oneint' => 1);
2449          $this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected.
2450  
2451          // Re-insert same record, not returning id. dml_exception expected.
2452          try {
2453              $DB->insert_record($tablename, $record, false);
2454              $this->fail("Expecting an exception, none occurred");
2455          } catch (\moodle_exception $e) {
2456              $this->assertInstanceOf('dml_exception', $e);
2457          }
2458  
2459          // Re-insert same record, returning id. dml_exception expected.
2460          try {
2461              $DB->insert_record($tablename, $record, true);
2462              $this->fail("Expecting an exception, none occurred");
2463          } catch (\moodle_exception $e) {
2464              $this->assertInstanceOf('dml_exception', $e);
2465          }
2466  
2467          // Try to insert a record into a non-existent table. dml_exception expected.
2468          try {
2469              $DB->insert_record('nonexistenttable', $record, true);
2470              $this->fail("Expecting an exception, none occurred");
2471          } catch (\Exception $e) {
2472              $this->assertTrue($e instanceof dml_exception);
2473          }
2474      }
2475  
2476      public function test_insert_records() {
2477          $DB = $this->tdb;
2478          $dbman = $DB->get_manager();
2479  
2480          $table = $this->get_test_table();
2481          $tablename = $table->getName();
2482  
2483          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2484          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2485          $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2486          $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2487          $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2488          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2489          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2490          $dbman->create_table($table);
2491  
2492          $this->assertCount(0, $DB->get_records($tablename));
2493  
2494          $record = new \stdClass();
2495          $record->id = '1';
2496          $record->course = '1';
2497          $record->oneint = null;
2498          $record->onenum = 1.0;
2499          $record->onechar = 'a';
2500          $record->onetext = 'aaa';
2501  
2502          $expected = array();
2503          $records = array();
2504          for ($i = 1; $i <= 2000; $i++) { // This may take a while, it should be higher than defaults in DML drivers.
2505              $rec = clone($record);
2506              $rec->id = (string)$i;
2507              $rec->oneint = (string)$i;
2508              $expected[$i] = $rec;
2509              $rec = clone($rec);
2510              unset($rec->id);
2511              $records[$i] = $rec;
2512          }
2513  
2514          $DB->insert_records($tablename, $records);
2515          $stored = $DB->get_records($tablename, array(), 'id ASC');
2516          $this->assertEquals($expected, $stored);
2517  
2518          // Test there can be some extra properties including id.
2519          $count = $DB->count_records($tablename);
2520          $rec1 = (array)$record;
2521          $rec1['xxx'] = 1;
2522          $rec2 = (array)$record;
2523          $rec2['xxx'] = 2;
2524  
2525          $records = array($rec1, $rec2);
2526          $DB->insert_records($tablename, $records);
2527          $this->assertEquals($count + 2, $DB->count_records($tablename));
2528  
2529          // Test not all properties are necessary.
2530          $rec1 = (array)$record;
2531          unset($rec1['course']);
2532          $rec2 = (array)$record;
2533          unset($rec2['course']);
2534  
2535          $records = array($rec1, $rec2);
2536          $DB->insert_records($tablename, $records);
2537  
2538          // Make sure no changes in data object structure are tolerated.
2539          $rec1 = (array)$record;
2540          unset($rec1['id']);
2541          $rec2 = (array)$record;
2542          unset($rec2['id']);
2543  
2544          $records = array($rec1, $rec2);
2545          $DB->insert_records($tablename, $records);
2546  
2547          $rec2['xx'] = '1';
2548          $records = array($rec1, $rec2);
2549          try {
2550              $DB->insert_records($tablename, $records);
2551              $this->fail('coding_exception expected when insert_records receives different object data structures');
2552          } catch (\moodle_exception $e) {
2553              $this->assertInstanceOf('coding_exception', $e);
2554          }
2555  
2556          unset($rec2['xx']);
2557          unset($rec2['course']);
2558          $rec2['course'] = '1';
2559          $records = array($rec1, $rec2);
2560          try {
2561              $DB->insert_records($tablename, $records);
2562              $this->fail('coding_exception expected when insert_records receives different object data structures');
2563          } catch (\moodle_exception $e) {
2564              $this->assertInstanceOf('coding_exception', $e);
2565          }
2566  
2567          $records = 1;
2568          try {
2569              $DB->insert_records($tablename, $records);
2570              $this->fail('coding_exception expected when insert_records receives non-traversable data');
2571          } catch (\moodle_exception $e) {
2572              $this->assertInstanceOf('coding_exception', $e);
2573          }
2574  
2575          $records = array(1);
2576          try {
2577              $DB->insert_records($tablename, $records);
2578              $this->fail('coding_exception expected when insert_records receives non-objet record');
2579          } catch (\moodle_exception $e) {
2580              $this->assertInstanceOf('coding_exception', $e);
2581          }
2582      }
2583  
2584      public function test_insert_record_with_nullable_unique_index() {
2585          $DB = $this->tdb;
2586          $dbman = $DB->get_manager();
2587  
2588          $table = $this->get_test_table();
2589          $tablename = $table->getName();
2590  
2591          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2592          $table->add_field('notnull1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2593          $table->add_field('nullable1', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2594          $table->add_field('nullable2', XMLDB_TYPE_INTEGER, '10', null, null, null, null);
2595          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2596          $table->add_index('notnull1-nullable1-nullable2', XMLDB_INDEX_UNIQUE,
2597                  array('notnull1', 'nullable1', 'nullable2'));
2598          $dbman->create_table($table);
2599  
2600          // Insert one record. Should be OK (no exception).
2601          $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => 1, 'nullable2' => 1]);
2602  
2603          $this->assertEquals(1, $DB->count_records($table->getName()));
2604          $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1]));
2605  
2606          // Inserting a duplicate should fail.
2607          try {
2608              $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => 1, 'nullable2' => 1]);
2609              $this->fail('dml_write_exception expected when a record violates a unique index');
2610          } catch (\moodle_exception $e) {
2611              $this->assertInstanceOf('dml_write_exception', $e);
2612          }
2613  
2614          $this->assertEquals(1, $DB->count_records($table->getName()));
2615          $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1]));
2616  
2617          // Inserting a record with nulls in the nullable columns should work.
2618          $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => null, 'nullable2' => null]);
2619  
2620          $this->assertEquals(2, $DB->count_records($table->getName()));
2621          $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1]));
2622          $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => null]));
2623  
2624          // And it should be possible to insert a duplicate.
2625          $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => null, 'nullable2' => null]);
2626  
2627          $this->assertEquals(3, $DB->count_records($table->getName()));
2628          $this->assertEquals(1, $DB->