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 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  namespace core;
  18  
  19  use database_column_info;
  20  use moodle_database;
  21  use sql_generator;
  22  use xmldb_field;
  23  use xmldb_index;
  24  use xmldb_key;
  25  use xmldb_structure;
  26  use xmldb_table;
  27  
  28  /**
  29   * DDL layer tests.
  30   *
  31   * @package    core_ddl
  32   * @category   test
  33   * @copyright  2008 Nicolas Connault
  34   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  35   */
  36  class ddl_test extends \database_driver_testcase {
  37      /** @var xmldb_table[] keys are table name. Created in setUp. */
  38      private $tables = array();
  39      /** @var array table name => array of stdClass test records loaded into that table. Created in setUp. */
  40      private $records = array();
  41  
  42      protected function setUp(): void {
  43          parent::setUp();
  44          $dbman = $this->tdb->get_manager(); // Loads DDL libs.
  45  
  46          $table = new xmldb_table('test_table0');
  47          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
  48          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  49          $table->add_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'general');
  50          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null);
  51          $table->add_field('intro', XMLDB_TYPE_TEXT, 'small', null, XMLDB_NOTNULL, null, null);
  52          $table->add_field('logo', XMLDB_TYPE_BINARY, 'big', null, null, null);
  53          $table->add_field('assessed', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  54          $table->add_field('assesstimestart', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  55          $table->add_field('assesstimefinish', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  56          $table->add_field('scale', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  57          $table->add_field('maxbytes', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  58          $table->add_field('forcesubscribe', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, '0');
  59          $table->add_field('trackingtype', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '1');
  60          $table->add_field('rsstype', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
  61          $table->add_field('rssarticles', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
  62          $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  63          $table->add_field('grade', XMLDB_TYPE_NUMBER, '20,0', null, null, null, null);
  64          $table->add_field('percent', XMLDB_TYPE_NUMBER, '5,2', null, null, null, 66.6);
  65          $table->add_field('bignum', XMLDB_TYPE_NUMBER, '38,18', null, null, null, 1234567890.1234);
  66          $table->add_field('warnafter', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  67          $table->add_field('blockafter', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  68          $table->add_field('blockperiod', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  69          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
  70          $table->add_key('course', XMLDB_KEY_UNIQUE, array('course'));
  71          $table->add_index('type-name', XMLDB_INDEX_UNIQUE, array('type', 'name'));
  72          $table->add_index('rsstype', XMLDB_INDEX_NOTUNIQUE, array('rsstype'));
  73          $table->setComment("This is a test'n drop table. You can drop it safely");
  74  
  75          $this->tables[$table->getName()] = $table;
  76  
  77          // Define 2 initial records for this table.
  78          $this->records[$table->getName()] = array(
  79              (object)array(
  80                  'course' => '1',
  81                  'type'   => 'general',
  82                  'name'   => 'record',
  83                  'intro'  => 'first record'),
  84              (object)array(
  85                  'course' => '2',
  86                  'type'   => 'social',
  87                  'name'   => 'record',
  88                  'intro'  => 'second record'));
  89  
  90          // Second, smaller table.
  91          $table = new xmldb_table ('test_table1');
  92          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
  93          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
  94          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, null, null, 'Moodle');
  95          $table->add_field('secondname', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, null);
  96          $table->add_field('thirdname', XMLDB_TYPE_CHAR, '30', null, null, null, ''); // Nullable column with empty default.
  97          $table->add_field('intro', XMLDB_TYPE_TEXT, 'medium', null, XMLDB_NOTNULL, null, null);
  98          $table->add_field('avatar', XMLDB_TYPE_BINARY, 'medium', null, null, null, null);
  99          $table->add_field('grade', XMLDB_TYPE_NUMBER, '20,10', null, null, null);
 100          $table->add_field('gradefloat', XMLDB_TYPE_FLOAT, '20,0', null, null, null, null);
 101          $table->add_field('percentfloat', XMLDB_TYPE_FLOAT, '5,2', null, null, null, 99.9);
 102          $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 103          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 104          $table->add_key('course', XMLDB_KEY_FOREIGN_UNIQUE, array('course'), 'test_table0', array('course'));
 105          $table->setComment("This is a test'n drop table. You can drop it safely");
 106  
 107          $this->tables[$table->getName()] = $table;
 108  
 109          // Define 2 initial records for this table.
 110          $this->records[$table->getName()] = array(
 111              (object)array(
 112                  'course' => '1',
 113                  'secondname'   => 'first record', // Less than 10 cc, please don't modify. Some tests below depend of this.
 114                  'intro'  => 'first record'),
 115              (object)array(
 116                  'course'       => '2',
 117                  'secondname'   => 'second record', // More than 10 cc, please don't modify. Some tests below depend of this.
 118                  'intro'  => 'second record'));
 119      }
 120  
 121      private function create_deftable($tablename) {
 122          $dbman = $this->tdb->get_manager();
 123  
 124          if (!isset($this->tables[$tablename])) {
 125              return null;
 126          }
 127  
 128          $table = $this->tables[$tablename];
 129  
 130          if ($dbman->table_exists($table)) {
 131              $dbman->drop_table($table);
 132          }
 133          $dbman->create_table($table);
 134  
 135          return $table;
 136      }
 137  
 138      /**
 139       * Fill the given test table with some records, as far as
 140       * DDL behaviour must be tested both with real data and
 141       * with empty tables
 142       * @param string $tablename
 143       * @return int count of records
 144       */
 145      private function fill_deftable($tablename) {
 146          $DB = $this->tdb; // Do not use global $DB!
 147          $dbman = $this->tdb->get_manager();
 148  
 149          if (!isset($this->records[$tablename])) {
 150              return null;
 151          }
 152  
 153          if ($dbman->table_exists($tablename)) {
 154              foreach ($this->records[$tablename] as $row) {
 155                  $DB->insert_record($tablename, $row);
 156              }
 157          } else {
 158              return null;
 159          }
 160  
 161          return count($this->records[$tablename]);
 162      }
 163  
 164      /**
 165       * Test behaviour of table_exists()
 166       */
 167      public function test_table_exists() {
 168          $DB = $this->tdb; // Do not use global $DB!
 169          $dbman = $this->tdb->get_manager();
 170  
 171          // First make sure it returns false if table does not exist.
 172          $table = $this->tables['test_table0'];
 173  
 174          try {
 175              $result = $DB->get_records('test_table0');
 176          } catch (\dml_exception $e) {
 177              $result = false;
 178          }
 179          $this->resetDebugging();
 180  
 181          $this->assertFalse($result);
 182  
 183          $this->assertFalse($dbman->table_exists('test_table0')); // By name..
 184          $this->assertFalse($dbman->table_exists($table));        // By xmldb_table..
 185  
 186          // Create table and test again.
 187          $dbman->create_table($table);
 188  
 189          $this->assertSame(array(), $DB->get_records('test_table0'));
 190          $this->assertTrue($dbman->table_exists('test_table0')); // By name.
 191          $this->assertTrue($dbman->table_exists($table));        // By xmldb_table.
 192  
 193          // Drop table and test again.
 194          $dbman->drop_table($table);
 195  
 196          try {
 197              $result = $DB->get_records('test_table0');
 198          } catch (\dml_exception $e) {
 199              $result = false;
 200          }
 201          $this->resetDebugging();
 202  
 203          $this->assertFalse($result);
 204  
 205          $this->assertFalse($dbman->table_exists('test_table0')); // By name.
 206          $this->assertFalse($dbman->table_exists($table));        // By xmldb_table.
 207      }
 208  
 209      /**
 210       * Test behaviour of create_table()
 211       */
 212      public function test_create_table() {
 213  
 214          $DB = $this->tdb; // Do not use global $DB!
 215          $dbman = $this->tdb->get_manager();
 216  
 217          // Create table.
 218          $table = $this->tables['test_table1'];
 219  
 220          $dbman->create_table($table);
 221          $this->assertTrue($dbman->table_exists($table));
 222  
 223          // Basic get_tables() test.
 224          $tables = $DB->get_tables();
 225          $this->assertArrayHasKey('test_table1', $tables);
 226  
 227          // Basic get_columns() tests.
 228          $columns = $DB->get_columns('test_table1');
 229          $this->assertSame('R', $columns['id']->meta_type);
 230          $this->assertSame('I', $columns['course']->meta_type);
 231          $this->assertSame('C', $columns['name']->meta_type);
 232          $this->assertSame('C', $columns['secondname']->meta_type);
 233          $this->assertSame('C', $columns['thirdname']->meta_type);
 234          $this->assertSame('X', $columns['intro']->meta_type);
 235          $this->assertSame('B', $columns['avatar']->meta_type);
 236          $this->assertSame('N', $columns['grade']->meta_type);
 237          $this->assertSame('N', $columns['percentfloat']->meta_type);
 238          $this->assertSame('I', $columns['userid']->meta_type);
 239          // Some defaults.
 240          $this->assertTrue($columns['course']->has_default);
 241          $this->assertEquals(0, $columns['course']->default_value);
 242          $this->assertTrue($columns['name']->has_default);
 243          $this->assertSame('Moodle', $columns['name']->default_value);
 244          $this->assertTrue($columns['secondname']->has_default);
 245          $this->assertSame('', $columns['secondname']->default_value);
 246          $this->assertTrue($columns['thirdname']->has_default);
 247          $this->assertSame('', $columns['thirdname']->default_value);
 248          $this->assertTrue($columns['percentfloat']->has_default);
 249          $this->assertEquals(99.9, $columns['percentfloat']->default_value);
 250          $this->assertTrue($columns['userid']->has_default);
 251          $this->assertEquals(0, $columns['userid']->default_value);
 252  
 253          // Basic get_indexes() test.
 254          $indexes = $DB->get_indexes('test_table1');
 255          $courseindex = reset($indexes);
 256          $this->assertEquals(1, $courseindex['unique']);
 257          $this->assertSame('course', $courseindex['columns'][0]);
 258  
 259          // Check sequence returns 1 for first insert.
 260          $rec = (object)array(
 261              'course'     => 10,
 262              'secondname' => 'not important',
 263              'intro'      => 'not important');
 264          $this->assertSame(1, $DB->insert_record('test_table1', $rec));
 265  
 266          // Check defined defaults are working ok.
 267          $dbrec = $DB->get_record('test_table1', array('id' => 1));
 268          $this->assertSame('Moodle', $dbrec->name);
 269          $this->assertSame('', $dbrec->thirdname);
 270  
 271          // Check exceptions if multiple R columns.
 272          $table = new xmldb_table ('test_table2');
 273          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 274          $table->add_field('rid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 275          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 276          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 277          $table->add_key('primaryx', XMLDB_KEY_PRIMARY, array('id'));
 278          $table->setComment("This is a test'n drop table. You can drop it safely");
 279  
 280          $this->tables[$table->getName()] = $table;
 281  
 282          try {
 283              $dbman->create_table($table);
 284              $this->fail('Exception expected');
 285          } catch (\moodle_exception $e) {
 286              $this->assertInstanceOf('ddl_exception', $e);
 287          }
 288  
 289          // Check exceptions missing primary key on R column.
 290          $table = new xmldb_table ('test_table2');
 291          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 292          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 293          $table->setComment("This is a test'n drop table. You can drop it safely");
 294  
 295          $this->tables[$table->getName()] = $table;
 296  
 297          try {
 298              $dbman->create_table($table);
 299              $this->fail('Exception expected');
 300          } catch (\moodle_exception $e) {
 301              $this->assertInstanceOf('ddl_exception', $e);
 302          }
 303  
 304          // Long table name names - the largest allowed by the configuration which exclude the prefix to ensure it's created.
 305          $tablechars = str_repeat('a', xmldb_table::NAME_MAX_LENGTH);
 306          $table = new xmldb_table($tablechars);
 307          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 308          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 309          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 310          $table->setComment("This is a test'n drop table. You can drop it safely");
 311  
 312          $this->tables[$table->getName()] = $table;
 313  
 314          $dbman->create_table($table);
 315          $this->assertTrue($dbman->table_exists($table));
 316          $dbman->drop_table($table);
 317  
 318          // Table name is too long, ignoring any prefix size set.
 319          $tablechars = str_repeat('a', xmldb_table::NAME_MAX_LENGTH + 1);
 320          $table = new xmldb_table($tablechars);
 321          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 322          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 323          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 324          $table->setComment("This is a test'n drop table. You can drop it safely");
 325  
 326          $this->tables[$table->getName()] = $table;
 327  
 328          try {
 329              $dbman->create_table($table);
 330              $this->fail('Exception expected');
 331          } catch (\moodle_exception $e) {
 332              $this->assertInstanceOf('coding_exception', $e);
 333          }
 334  
 335          // Invalid table name.
 336          $table = new xmldb_table('test_tableCD');
 337          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 338          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 339          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 340          $table->setComment("This is a test'n drop table. You can drop it safely");
 341  
 342          $this->tables[$table->getName()] = $table;
 343  
 344          try {
 345              $dbman->create_table($table);
 346              $this->fail('Exception expected');
 347          } catch (\moodle_exception $e) {
 348              $this->assertInstanceOf('coding_exception', $e);
 349          }
 350  
 351          // Weird column names - the largest allowed.
 352          $table = new xmldb_table('test_table3');
 353          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 354          $table->add_field(str_repeat('b', xmldb_field::NAME_MAX_LENGTH), XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 355          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 356          $table->setComment("This is a test'n drop table. You can drop it safely");
 357  
 358          $this->tables[$table->getName()] = $table;
 359  
 360          $dbman->create_table($table);
 361          $this->assertTrue($dbman->table_exists($table));
 362          $dbman->drop_table($table);
 363  
 364          // Too long field name.
 365          $table = new xmldb_table('test_table4');
 366          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 367          $table->add_field(str_repeat('a', xmldb_field::NAME_MAX_LENGTH + 1), XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 368          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 369          $table->setComment("This is a test'n drop table. You can drop it safely");
 370  
 371          $this->tables[$table->getName()] = $table;
 372  
 373          try {
 374              $dbman->create_table($table);
 375              $this->fail('Exception expected');
 376          } catch (\moodle_exception $e) {
 377              $this->assertInstanceOf('coding_exception', $e);
 378          }
 379  
 380          // Invalid field name.
 381          $table = new xmldb_table('test_table4');
 382          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 383          $table->add_field('abCD', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
 384          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 385          $table->setComment("This is a test'n drop table. You can drop it safely");
 386  
 387          $this->tables[$table->getName()] = $table;
 388  
 389          try {
 390              $dbman->create_table($table);
 391              $this->fail('Exception expected');
 392          } catch (\moodle_exception $e) {
 393              $this->assertInstanceOf('coding_exception', $e);
 394          }
 395  
 396          // Invalid integer length.
 397          $table = new xmldb_table('test_table4');
 398          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 399          $table->add_field('course', XMLDB_TYPE_INTEGER, '21', null, XMLDB_NOTNULL, null, '2');
 400          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 401          $table->setComment("This is a test'n drop table. You can drop it safely");
 402  
 403          $this->tables[$table->getName()] = $table;
 404  
 405          try {
 406              $dbman->create_table($table);
 407              $this->fail('Exception expected');
 408          } catch (\moodle_exception $e) {
 409              $this->assertInstanceOf('coding_exception', $e);
 410          }
 411  
 412          // Invalid integer default.
 413          $table = new xmldb_table('test_table4');
 414          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 415          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 'x');
 416          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 417          $table->setComment("This is a test'n drop table. You can drop it safely");
 418  
 419          $this->tables[$table->getName()] = $table;
 420  
 421          try {
 422              $dbman->create_table($table);
 423              $this->fail('Exception expected');
 424          } catch (\moodle_exception $e) {
 425              $this->assertInstanceOf('coding_exception', $e);
 426          }
 427  
 428          // Invalid decimal length - max precision is 38 digits.
 429          $table = new xmldb_table('test_table4');
 430          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 431          $table->add_field('num', XMLDB_TYPE_NUMBER, '39,19', null, XMLDB_NOTNULL, null, null);
 432          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 433          $table->setComment("This is a test'n drop table. You can drop it safely");
 434  
 435          $this->tables[$table->getName()] = $table;
 436  
 437          try {
 438              $dbman->create_table($table);
 439              $this->fail('Exception expected');
 440          } catch (\moodle_exception $e) {
 441              $this->assertInstanceOf('coding_exception', $e);
 442          }
 443  
 444          // Invalid decimal decimals - number of decimals can't be higher than total number of digits.
 445          $table = new xmldb_table('test_table4');
 446          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 447          $table->add_field('num', XMLDB_TYPE_NUMBER, '10,11', null, XMLDB_NOTNULL, null, null);
 448          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 449          $table->setComment("This is a test'n drop table. You can drop it safely");
 450  
 451          $this->tables[$table->getName()] = $table;
 452  
 453          try {
 454              $dbman->create_table($table);
 455              $this->fail('Exception expected');
 456          } catch (\moodle_exception $e) {
 457              $this->assertInstanceOf('coding_exception', $e);
 458          }
 459  
 460          // Invalid decimal whole number - the whole number part can't have more digits than integer fields.
 461          $table = new xmldb_table('test_table4');
 462          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 463          $table->add_field('num', XMLDB_TYPE_NUMBER, '38,17', null, XMLDB_NOTNULL, null, null);
 464          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 465          $table->setComment("This is a test'n drop table. You can drop it safely");
 466  
 467          $this->tables[$table->getName()] = $table;
 468  
 469          try {
 470              $dbman->create_table($table);
 471              $this->fail('Exception expected');
 472          } catch (\moodle_exception $e) {
 473              $this->assertInstanceOf('coding_exception', $e);
 474          }
 475  
 476          // Invalid decimal decimals - negative scale not supported.
 477          $table = new xmldb_table('test_table4');
 478          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 479          $table->add_field('num', XMLDB_TYPE_NUMBER, '30,-5', null, XMLDB_NOTNULL, null, null);
 480          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 481          $table->setComment("This is a test'n drop table. You can drop it safely");
 482  
 483          $this->tables[$table->getName()] = $table;
 484  
 485          try {
 486              $dbman->create_table($table);
 487              $this->fail('Exception expected');
 488          } catch (\moodle_exception $e) {
 489              $this->assertInstanceOf('coding_exception', $e);
 490          }
 491  
 492          // Invalid decimal default.
 493          $table = new xmldb_table('test_table4');
 494          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 495          $table->add_field('num', XMLDB_TYPE_NUMBER, '10,5', null, XMLDB_NOTNULL, null, 'x');
 496          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 497          $table->setComment("This is a test'n drop table. You can drop it safely");
 498  
 499          $this->tables[$table->getName()] = $table;
 500  
 501          try {
 502              $dbman->create_table($table);
 503              $this->fail('Exception expected');
 504          } catch (\moodle_exception $e) {
 505              $this->assertInstanceOf('coding_exception', $e);
 506          }
 507  
 508          // Invalid float length.
 509          $table = new xmldb_table('test_table4');
 510          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 511          $table->add_field('num', XMLDB_TYPE_FLOAT, '21,10', null, XMLDB_NOTNULL, null, null);
 512          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 513          $table->setComment("This is a test'n drop table. You can drop it safely");
 514  
 515          $this->tables[$table->getName()] = $table;
 516  
 517          try {
 518              $dbman->create_table($table);
 519              $this->fail('Exception expected');
 520          } catch (\moodle_exception $e) {
 521              $this->assertInstanceOf('coding_exception', $e);
 522          }
 523  
 524          // Invalid float decimals.
 525          $table = new xmldb_table('test_table4');
 526          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 527          $table->add_field('num', XMLDB_TYPE_FLOAT, '10,11', null, XMLDB_NOTNULL, null, null);
 528          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 529          $table->setComment("This is a test'n drop table. You can drop it safely");
 530  
 531          $this->tables[$table->getName()] = $table;
 532  
 533          try {
 534              $dbman->create_table($table);
 535              $this->fail('Exception expected');
 536          } catch (\moodle_exception $e) {
 537              $this->assertInstanceOf('coding_exception', $e);
 538          }
 539  
 540          // Invalid float default.
 541          $table = new xmldb_table('test_table4');
 542          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 543          $table->add_field('num', XMLDB_TYPE_FLOAT, '10,5', null, XMLDB_NOTNULL, null, 'x');
 544          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 545          $table->setComment("This is a test'n drop table. You can drop it safely");
 546  
 547          $this->tables[$table->getName()] = $table;
 548  
 549          try {
 550              $dbman->create_table($table);
 551              $this->fail('Exception expected');
 552          } catch (\moodle_exception $e) {
 553              $this->assertInstanceOf('coding_exception', $e);
 554          }
 555      }
 556  
 557      /**
 558       * Test if database supports tables with many TEXT fields,
 559       * InnoDB is known to failed during data insertion instead
 560       * of table creation when text fields contain actual data.
 561       */
 562      public function test_row_size_limits() {
 563  
 564          $DB = $this->tdb; // Do not use global $DB!
 565          $dbman = $this->tdb->get_manager();
 566  
 567          $text = str_repeat('š', 1333);
 568  
 569          $data = new \stdClass();
 570          $data->name = 'test';
 571          $table = new xmldb_table('test_innodb');
 572          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 573          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, null, null, null);
 574          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 575          for ($i = 0; $i < 20; $i++) {
 576              $table->add_field('text'.$i, XMLDB_TYPE_TEXT, null, null, null, null, null);
 577              $data->{'text'.$i} = $text;
 578          }
 579          $dbman->create_table($table);
 580  
 581          try {
 582              $id = $DB->insert_record('test_innodb', $data);
 583              $expected = (array)$data;
 584              $expected['id'] = (string)$id;
 585              $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
 586          } catch (\dml_exception $e) {
 587              // Give some nice error message when known problematic MySQL with InnoDB detected.
 588              if ($DB->get_dbfamily() === 'mysql') {
 589                  $engine = strtolower($DB->get_dbengine());
 590                  if ($engine === 'innodb' or $engine === 'xtradb') {
 591                      if (!$DB->is_compressed_row_format_supported()) {
 592                          $this->fail("Row size limit reached in MySQL using InnoDB, configure server to use innodb_file_format=Barracuda and innodb_file_per_table=1");
 593                      }
 594                  }
 595              }
 596              throw $e;
 597          }
 598  
 599          $dbman->drop_table($table);
 600  
 601          $data = new \stdClass();
 602          $data->name = 'test';
 603          $table = new xmldb_table('test_innodb');
 604          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 605          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, null, null, null);
 606          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 607          $dbman->create_table($table);
 608          $DB->insert_record('test_innodb', array('name' => 'test'));
 609  
 610          for ($i = 0; $i < 20; $i++) {
 611              $field = new xmldb_field('text'.$i, XMLDB_TYPE_TEXT, null, null, null, null, null);
 612              $dbman->add_field($table, $field);
 613              $data->{'text'.$i} = $text;
 614  
 615              $id = $DB->insert_record('test_innodb', $data);
 616              $expected = (array)$data;
 617              $expected['id'] = (string)$id;
 618              $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
 619          }
 620  
 621          $dbman->drop_table($table);
 622  
 623          // MySQL VARCHAR fields may hit a different 65535 row size limit when creating tables.
 624          $data = new \stdClass();
 625          $data->name = 'test';
 626          $table = new xmldb_table('test_innodb');
 627          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 628          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, null, null, null);
 629          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 630          for ($i = 0; $i < 12; $i++) {
 631              $table->add_field('text'.$i, XMLDB_TYPE_CHAR, '1333', null, null, null, null);
 632              $data->{'text'.$i} = $text;
 633          }
 634          $dbman->create_table($table);
 635  
 636          $id = $DB->insert_record('test_innodb', $data);
 637          $expected = (array)$data;
 638          $expected['id'] = (string)$id;
 639          $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
 640  
 641          $dbman->drop_table($table);
 642      }
 643  
 644      /**
 645       * Test behaviour of drop_table()
 646       */
 647      public function test_drop_table() {
 648          $DB = $this->tdb; // Do not use global $DB!
 649          $dbman = $this->tdb->get_manager();
 650  
 651          // Initially table doesn't exist.
 652          $this->assertFalse($dbman->table_exists('test_table0'));
 653  
 654          // Create table with contents.
 655          $table = $this->create_deftable('test_table0');
 656          $this->assertTrue($dbman->table_exists('test_table0'));
 657  
 658          // Fill the table with some records before dropping it.
 659          $this->fill_deftable('test_table0');
 660  
 661          // Drop by xmldb_table object.
 662          $dbman->drop_table($table);
 663          $this->assertFalse($dbman->table_exists('test_table0'));
 664  
 665          // Basic get_tables() test.
 666          $tables = $DB->get_tables();
 667          $this->assertArrayNotHasKey('test_table0', $tables);
 668  
 669          // Columns cache must be empty.
 670          $columns = $DB->get_columns('test_table0');
 671          $this->assertEmpty($columns);
 672  
 673          $indexes = $DB->get_indexes('test_table0');
 674          $this->assertEmpty($indexes);
 675      }
 676  
 677      /**
 678       * Test behaviour of rename_table()
 679       */
 680      public function test_rename_table() {
 681          $DB = $this->tdb; // Do not use global $DB!
 682          $dbman = $this->tdb->get_manager();
 683  
 684          $table = $this->create_deftable('test_table1');
 685  
 686          // Fill the table with some records before renaming it.
 687          $insertedrows = $this->fill_deftable('test_table1');
 688  
 689          $this->assertFalse($dbman->table_exists('test_table_cust1'));
 690          $dbman->rename_table($table, 'test_table_cust1');
 691          $this->assertTrue($dbman->table_exists('test_table_cust1'));
 692  
 693          // Check sequence returns $insertedrows + 1 for this insert (after rename).
 694          $rec = (object)array(
 695              'course'     => 20,
 696              'secondname' => 'not important',
 697              'intro'      => 'not important');
 698          $this->assertSame($insertedrows+1, $DB->insert_record('test_table_cust1', $rec));
 699  
 700          // Verify behavior when target table already exists.
 701          $sourcetable = $this->create_deftable('test_table0');
 702          $targettable = $this->create_deftable('test_table1');
 703          try {
 704              $dbman->rename_table($sourcetable, $targettable->getName());
 705              $this->fail('Exception expected');
 706          } catch (\moodle_exception $e) {
 707              $this->assertInstanceOf('ddl_exception', $e);
 708              $this->assertEquals('Table "test_table1" already exists (can not rename table)', $e->getMessage());
 709          }
 710      }
 711  
 712      /**
 713       * Test behaviour of field_exists()
 714       */
 715      public function test_field_exists() {
 716          $dbman = $this->tdb->get_manager();
 717  
 718          $table = $this->create_deftable('test_table0');
 719  
 720          // String params.
 721          // Give a nonexistent table as first param (throw exception).
 722          try {
 723              $dbman->field_exists('nonexistenttable', 'id');
 724              $this->fail('Exception expected');
 725          } catch (\moodle_exception $e) {
 726              $this->assertInstanceOf('moodle_exception', $e);
 727          }
 728  
 729          // Give a nonexistent field as second param (return false).
 730          $this->assertFalse($dbman->field_exists('test_table0', 'nonexistentfield'));
 731  
 732          // Correct string params.
 733          $this->assertTrue($dbman->field_exists('test_table0', 'id'));
 734  
 735          // Object params.
 736          $realfield = $table->getField('id');
 737  
 738          // Give a nonexistent table as first param (throw exception).
 739          $nonexistenttable = new xmldb_table('nonexistenttable');
 740          try {
 741              $dbman->field_exists($nonexistenttable, $realfield);
 742              $this->fail('Exception expected');
 743          } catch (\moodle_exception $e) {
 744              $this->assertInstanceOf('moodle_exception', $e);
 745          }
 746  
 747          // Give a nonexistent field as second param (return false).
 748          $nonexistentfield = new xmldb_field('nonexistentfield');
 749          $this->assertFalse($dbman->field_exists($table, $nonexistentfield));
 750  
 751          // Correct object params.
 752          $this->assertTrue($dbman->field_exists($table, $realfield));
 753  
 754          // Mix string and object params.
 755          // Correct ones.
 756          $this->assertTrue($dbman->field_exists($table, 'id'));
 757          $this->assertTrue($dbman->field_exists('test_table0', $realfield));
 758          // Non existing tables (throw exception).
 759          try {
 760              $this->assertFalse($dbman->field_exists($nonexistenttable, 'id'));
 761              $this->fail('Exception expected');
 762          } catch (\moodle_exception $e) {
 763              $this->assertInstanceOf('moodle_exception', $e);
 764          }
 765          try {
 766              $this->assertFalse($dbman->field_exists('nonexistenttable', $realfield));
 767              $this->fail('Exception expected');
 768          } catch (\moodle_exception $e) {
 769              $this->assertInstanceOf('moodle_exception', $e);
 770          }
 771          // Non existing fields (return false).
 772          $this->assertFalse($dbman->field_exists($table, 'nonexistentfield'));
 773          $this->assertFalse($dbman->field_exists('test_table0', $nonexistentfield));
 774      }
 775  
 776      /**
 777       * Test behaviour of add_field()
 778       */
 779      public function test_add_field() {
 780          $DB = $this->tdb; // Do not use global $DB!
 781          $dbman = $this->tdb->get_manager();
 782  
 783          $table = $this->create_deftable('test_table1');
 784  
 785          // Fill the table with some records before adding fields.
 786          $this->fill_deftable('test_table1');
 787  
 788          // Add one not null field without specifying default value (throws ddl_exception).
 789          $field = new xmldb_field('onefield');
 790          $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, null);
 791          try {
 792              $dbman->add_field($table, $field);
 793              $this->fail('Exception expected');
 794          } catch (\moodle_exception $e) {
 795              $this->assertInstanceOf('ddl_exception', $e);
 796          }
 797  
 798          // Add one existing field (throws ddl_exception).
 799          $field = new xmldb_field('course');
 800          $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, 2);
 801          try {
 802              $dbman->add_field($table, $field);
 803              $this->fail('Exception expected');
 804          } catch (\moodle_exception $e) {
 805              $this->assertInstanceOf('ddl_exception', $e);
 806          }
 807  
 808          // TODO: add one field with invalid type, must throw exception.
 809          // TODO: add one text field with default, must throw exception.
 810          // TODO: add one binary field with default, must throw exception.
 811  
 812          // Add one integer field and check it.
 813          $field = new xmldb_field('oneinteger');
 814          $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, 2);
 815          $dbman->add_field($table, $field);
 816          $this->assertTrue($dbman->field_exists($table, 'oneinteger'));
 817          $columns = $DB->get_columns('test_table1');
 818          $this->assertEquals('oneinteger', $columns['oneinteger']->name);
 819          $this->assertTrue($columns['oneinteger']->not_null);
 820          // Max_length and scale cannot be checked under all DBs at all for integer fields.
 821          $this->assertFalse($columns['oneinteger']->primary_key);
 822          $this->assertFalse($columns['oneinteger']->binary);
 823          $this->assertTrue($columns['oneinteger']->has_default);
 824          $this->assertEquals(2, $columns['oneinteger']->default_value);
 825          $this->assertSame('I', $columns['oneinteger']->meta_type);
 826          $this->assertEquals(2, $DB->get_field('test_table1', 'oneinteger', array(), IGNORE_MULTIPLE)); // Check default has been applied.
 827  
 828          // Add one numeric field and check it.
 829          $field = new xmldb_field('onenumber');
 830          $field->set_attributes(XMLDB_TYPE_NUMBER, '6,3', null, XMLDB_NOTNULL, null, 2.55);
 831          $dbman->add_field($table, $field);
 832          $this->assertTrue($dbman->field_exists($table, 'onenumber'));
 833          $columns = $DB->get_columns('test_table1');
 834          $this->assertSame('onenumber', $columns['onenumber']->name);
 835          $this->assertEquals(6, $columns['onenumber']->max_length);
 836          $this->assertEquals(3, $columns['onenumber']->scale);
 837          $this->assertTrue($columns['onenumber']->not_null);
 838          $this->assertFalse($columns['onenumber']->primary_key);
 839          $this->assertFalse($columns['onenumber']->binary);
 840          $this->assertTrue($columns['onenumber']->has_default);
 841          $this->assertEquals(2.550, $columns['onenumber']->default_value);
 842          $this->assertSame('N', $columns['onenumber']->meta_type);
 843          $this->assertEquals(2.550, $DB->get_field('test_table1', 'onenumber', array(), IGNORE_MULTIPLE)); // Check default has been applied.
 844  
 845          // Add one numeric field with scale of 0 and check it.
 846          $field = new xmldb_field('onenumberwith0scale');
 847          $field->set_attributes(XMLDB_TYPE_NUMBER, '6,0', null, XMLDB_NOTNULL, null, 2);
 848          $dbman->add_field($table, $field);
 849          $this->assertTrue($dbman->field_exists($table, 'onenumberwith0scale'));
 850          $columns = $DB->get_columns('test_table1');
 851          $this->assertEquals(6, $columns['onenumberwith0scale']->max_length);
 852          // We can not use assertEquals as that accepts null/false as a valid value.
 853          $this->assertSame('0', strval($columns['onenumberwith0scale']->scale));
 854  
 855          // Add one float field and check it (not official type - must work as number).
 856          $field = new xmldb_field('onefloat');
 857          $field->set_attributes(XMLDB_TYPE_FLOAT, '6,3', null, XMLDB_NOTNULL, null, 3.550);
 858          $dbman->add_field($table, $field);
 859          $this->assertTrue($dbman->field_exists($table, 'onefloat'));
 860          $columns = $DB->get_columns('test_table1');
 861          $this->assertSame('onefloat', $columns['onefloat']->name);
 862          $this->assertTrue($columns['onefloat']->not_null);
 863          // Max_length and scale cannot be checked under all DBs at all for float fields.
 864          $this->assertFalse($columns['onefloat']->primary_key);
 865          $this->assertFalse($columns['onefloat']->binary);
 866          $this->assertTrue($columns['onefloat']->has_default);
 867          $this->assertEquals(3.550, $columns['onefloat']->default_value);
 868          $this->assertSame('N', $columns['onefloat']->meta_type);
 869          // Just rounding DB information to 7 decimal digits. Fair enough to test 3.550 and avoids one nasty bug
 870          // in MSSQL core returning wrong floats (http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5e08de63-16bb-4f24-b645-0cf8fc669de3)
 871          // In any case, floats aren't officially supported by Moodle, with number/decimal type being the correct ones, so
 872          // this isn't a real problem at all.
 873          $this->assertEquals(3.550, round($DB->get_field('test_table1', 'onefloat', array(), IGNORE_MULTIPLE), 7)); // Check default has been applied.
 874  
 875          // Add one char field and check it.
 876          $field = new xmldb_field('onechar');
 877          $field->set_attributes(XMLDB_TYPE_CHAR, '25', null, XMLDB_NOTNULL, null, 'Nice dflt!');
 878          $dbman->add_field($table, $field);
 879          $this->assertTrue($dbman->field_exists($table, 'onechar'));
 880          $columns = $DB->get_columns('test_table1');
 881          $this->assertSame('onechar', $columns['onechar']->name);
 882          $this->assertEquals(25, $columns['onechar']->max_length);
 883          $this->assertNull($columns['onechar']->scale);
 884          $this->assertTrue($columns['onechar']->not_null);
 885          $this->assertFalse($columns['onechar']->primary_key);
 886          $this->assertFalse($columns['onechar']->binary);
 887          $this->assertTrue($columns['onechar']->has_default);
 888          $this->assertSame('Nice dflt!', $columns['onechar']->default_value);
 889          $this->assertSame('C', $columns['onechar']->meta_type);
 890          $this->assertEquals('Nice dflt!', $DB->get_field('test_table1', 'onechar', array(), IGNORE_MULTIPLE)); // Check default has been applied.
 891  
 892          // Add one big text field and check it.
 893          $field = new xmldb_field('onetext');
 894          $field->set_attributes(XMLDB_TYPE_TEXT, 'big');
 895          $dbman->add_field($table, $field);
 896          $this->assertTrue($dbman->field_exists($table, 'onetext'));
 897          $columns = $DB->get_columns('test_table1');
 898          $this->assertSame('onetext', $columns['onetext']->name);
 899          $this->assertEquals(-1, $columns['onetext']->max_length); // -1 means unknown or big.
 900          $this->assertNull($columns['onetext']->scale);
 901          $this->assertFalse($columns['onetext']->not_null);
 902          $this->assertFalse($columns['onetext']->primary_key);
 903          $this->assertFalse($columns['onetext']->binary);
 904          $this->assertFalse($columns['onetext']->has_default);
 905          $this->assertNull($columns['onetext']->default_value);
 906          $this->assertSame('X', $columns['onetext']->meta_type);
 907  
 908          // Add one medium text field and check it.
 909          $field = new xmldb_field('mediumtext');
 910          $field->set_attributes(XMLDB_TYPE_TEXT, 'medium');
 911          $dbman->add_field($table, $field);
 912          $columns = $DB->get_columns('test_table1');
 913          $this->assertTrue(($columns['mediumtext']->max_length == -1) or ($columns['mediumtext']->max_length >= 16777215)); // -1 means unknown or big.
 914  
 915          // Add one small text field and check it.
 916          $field = new xmldb_field('smalltext');
 917          $field->set_attributes(XMLDB_TYPE_TEXT, 'small');
 918          $dbman->add_field($table, $field);
 919          $columns = $DB->get_columns('test_table1');
 920          $this->assertTrue(($columns['smalltext']->max_length == -1) or ($columns['smalltext']->max_length >= 65535)); // -1 means unknown or big.
 921  
 922          // Add one binary field and check it.
 923          $field = new xmldb_field('onebinary');
 924          $field->set_attributes(XMLDB_TYPE_BINARY);
 925          $dbman->add_field($table, $field);
 926          $this->assertTrue($dbman->field_exists($table, 'onebinary'));
 927          $columns = $DB->get_columns('test_table1');
 928          $this->assertSame('onebinary', $columns['onebinary']->name);
 929          $this->assertEquals(-1, $columns['onebinary']->max_length);
 930          $this->assertNull($columns['onebinary']->scale);
 931          $this->assertFalse($columns['onebinary']->not_null);
 932          $this->assertFalse($columns['onebinary']->primary_key);
 933          $this->assertTrue($columns['onebinary']->binary);
 934          $this->assertFalse($columns['onebinary']->has_default);
 935          $this->assertNull($columns['onebinary']->default_value);
 936          $this->assertSame('B', $columns['onebinary']->meta_type);
 937  
 938          // TODO: check datetime type. Although unused should be fully supported.
 939      }
 940  
 941      /**
 942       * Test behaviour of drop_field()
 943       */
 944      public function test_drop_field() {
 945          $DB = $this->tdb; // Do not use global $DB!
 946          $dbman = $this->tdb->get_manager();
 947  
 948          $table = $this->create_deftable('test_table0');
 949  
 950          // Fill the table with some records before dropping fields.
 951          $this->fill_deftable('test_table0');
 952  
 953          // Drop field with simple xmldb_field having indexes, must return exception.
 954          $field = new xmldb_field('type'); // Field has indexes and default clause.
 955          $this->assertTrue($dbman->field_exists($table, 'type'));
 956          try {
 957              $dbman->drop_field($table, $field);
 958              $this->fail('Exception expected');
 959          } catch (\moodle_exception $e) {
 960              $this->assertInstanceOf('ddl_dependency_exception', $e);
 961          }
 962          $this->assertTrue($dbman->field_exists($table, 'type')); // Continues existing, drop aborted.
 963  
 964          // Drop field with complete xmldb_field object and related indexes, must return exception.
 965          $field = $table->getField('course'); // Field has indexes and default clause.
 966          $this->assertTrue($dbman->field_exists($table, $field));
 967          try {
 968              $dbman->drop_field($table, $field);
 969              $this->fail('Exception expected');
 970          } catch (\moodle_exception $e) {
 971              $this->assertInstanceOf('ddl_dependency_exception', $e);
 972          }
 973          $this->assertTrue($dbman->field_exists($table, $field)); // Continues existing, drop aborted.
 974  
 975          // Drop one non-existing field, must return exception.
 976          $field = new xmldb_field('nonexistingfield');
 977          $this->assertFalse($dbman->field_exists($table, $field));
 978          try {
 979              $dbman->drop_field($table, $field);
 980              $this->fail('Exception expected');
 981          } catch (\moodle_exception $e) {
 982              $this->assertInstanceOf('ddl_field_missing_exception', $e);
 983          }
 984  
 985          // Drop field with simple xmldb_field, not having related indexes.
 986          $field = new xmldb_field('forcesubscribe'); // Field has default clause.
 987          $this->assertTrue($dbman->field_exists($table, 'forcesubscribe'));
 988          $dbman->drop_field($table, $field);
 989          $this->assertFalse($dbman->field_exists($table, 'forcesubscribe'));
 990  
 991          // Drop field with complete xmldb_field object, not having related indexes.
 992          $field = new xmldb_field('trackingtype'); // Field has default clause.
 993          $this->assertTrue($dbman->field_exists($table, $field));
 994          $dbman->drop_field($table, $field);
 995          $this->assertFalse($dbman->field_exists($table, $field));
 996      }
 997  
 998      /**
 999       * Test behaviour of change_field_type()
1000       */
1001      public function test_change_field_type() {
1002          $DB = $this->tdb; // Do not use global $DB!
1003          $dbman = $this->tdb->get_manager();
1004  
1005          // Create table with indexed field and not indexed field to
1006          // perform tests in both fields, both having defaults.
1007          $table = new xmldb_table('test_table_cust0');
1008          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1009          $table->add_field('onenumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
1010          $table->add_field('anothernumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '4');
1011          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1012          $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE, array('onenumber'));
1013          $dbman->create_table($table);
1014  
1015          $record = new \stdClass();
1016          $record->onenumber = 2;
1017          $record->anothernumber = 4;
1018          $recoriginal = $DB->insert_record('test_table_cust0', $record);
1019  
1020          // Change column from integer to varchar. Must return exception because of dependent index.
1021          $field = new xmldb_field('onenumber');
1022          $field->set_attributes(XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'test');
1023          try {
1024              $dbman->change_field_type($table, $field);
1025              $this->fail('Exception expected');
1026          } catch (\moodle_exception $e) {
1027              $this->assertInstanceOf('ddl_dependency_exception', $e);
1028          }
1029          // Column continues being integer 10 not null default 2.
1030          $columns = $DB->get_columns('test_table_cust0');
1031          $this->assertSame('I', $columns['onenumber']->meta_type);
1032          // TODO: check the rest of attributes.
1033  
1034          // Change column from integer to varchar. Must work because column has no dependencies.
1035          $field = new xmldb_field('anothernumber');
1036          $field->set_attributes(XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'test');
1037          $dbman->change_field_type($table, $field);
1038          // Column is char 30 not null default 'test' now.
1039          $columns = $DB->get_columns('test_table_cust0');
1040          $this->assertSame('C', $columns['anothernumber']->meta_type);
1041          // TODO: check the rest of attributes.
1042  
1043          // Change column back from char to integer.
1044          $field = new xmldb_field('anothernumber');
1045          $field->set_attributes(XMLDB_TYPE_INTEGER, '8', null, XMLDB_NOTNULL, null, '5');
1046          $dbman->change_field_type($table, $field);
1047          // Column is integer 8 not null default 5 now.
1048          $columns = $DB->get_columns('test_table_cust0');
1049          $this->assertSame('I', $columns['anothernumber']->meta_type);
1050          // TODO: check the rest of attributes.
1051  
1052          // Change column once more from integer to char.
1053          $field = new xmldb_field('anothernumber');
1054          $field->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, "test'n drop");
1055          $dbman->change_field_type($table, $field);
1056          // Column is char 30 not null default "test'n drop" now.
1057          $columns = $DB->get_columns('test_table_cust0');
1058          $this->assertSame('C', $columns['anothernumber']->meta_type);
1059          // TODO: check the rest of attributes.
1060  
1061          // Insert one string value and try to convert to integer. Must throw exception.
1062          $record = new \stdClass();
1063          $record->onenumber = 7;
1064          $record->anothernumber = 'string value';
1065          $rectodrop = $DB->insert_record('test_table_cust0', $record);
1066          $field = new xmldb_field('anothernumber');
1067          $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '5');
1068          try {
1069              $dbman->change_field_type($table, $field);
1070              $this->fail('Exception expected');
1071          } catch (\moodle_exception $e) {
1072              $this->assertInstanceOf('ddl_change_structure_exception', $e);
1073          }
1074          // Column continues being char 30 not null default "test'n drop" now.
1075          $this->assertSame('C', $columns['anothernumber']->meta_type);
1076          // TODO: check the rest of attributes.
1077          $DB->delete_records('test_table_cust0', array('id' => $rectodrop)); // Delete the string record.
1078  
1079          // Change the column from varchar to float.
1080          $field = new xmldb_field('anothernumber');
1081          $field->set_attributes(XMLDB_TYPE_FLOAT, '20,10', null, null, null, null);
1082          $dbman->change_field_type($table, $field);
1083          // Column is float 20,10 null default null.
1084          $columns = $DB->get_columns('test_table_cust0');
1085          $this->assertSame('N', $columns['anothernumber']->meta_type); // Floats are seen as number.
1086          // TODO: check the rest of attributes.
1087  
1088          // Change the column back from float to varchar.
1089          $field = new xmldb_field('anothernumber');
1090          $field->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'test');
1091          $dbman->change_field_type($table, $field);
1092          // Column is char 20 not null default "test" now.
1093          $columns = $DB->get_columns('test_table_cust0');
1094          $this->assertSame('C', $columns['anothernumber']->meta_type);
1095          // TODO: check the rest of attributes.
1096  
1097          // Change the column from varchar to number.
1098          $field = new xmldb_field('anothernumber');
1099          $field->set_attributes(XMLDB_TYPE_NUMBER, '20,10', null, null, null, null);
1100          $dbman->change_field_type($table, $field);
1101          // Column is number 20,10 null default null now.
1102          $columns = $DB->get_columns('test_table_cust0');
1103          $this->assertSame('N', $columns['anothernumber']->meta_type);
1104          // TODO: check the rest of attributes.
1105  
1106          // Change the column from number to integer.
1107          $field = new xmldb_field('anothernumber');
1108          $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, null, null, null);
1109          $dbman->change_field_type($table, $field);
1110          // Column is integer 2 null default null now.
1111          $columns = $DB->get_columns('test_table_cust0');
1112          $this->assertSame('I', $columns['anothernumber']->meta_type);
1113          // TODO: check the rest of attributes.
1114  
1115          // Change the column from integer to text.
1116          $field = new xmldb_field('anothernumber');
1117          $field->set_attributes(XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
1118          $dbman->change_field_type($table, $field);
1119          // Column is char text not null default null.
1120          $columns = $DB->get_columns('test_table_cust0');
1121          $this->assertSame('X', $columns['anothernumber']->meta_type);
1122  
1123          // Change the column back from text to number.
1124          $field = new xmldb_field('anothernumber');
1125          $field->set_attributes(XMLDB_TYPE_NUMBER, '20,10', null, null, null, null);
1126          $dbman->change_field_type($table, $field);
1127          // Column is number 20,10 null default null now.
1128          $columns = $DB->get_columns('test_table_cust0');
1129          $this->assertSame('N', $columns['anothernumber']->meta_type);
1130          // TODO: check the rest of attributes.
1131  
1132          // Change the column from number to text.
1133          $field = new xmldb_field('anothernumber');
1134          $field->set_attributes(XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
1135          $dbman->change_field_type($table, $field);
1136          // Column is char text not null default "test" now.
1137          $columns = $DB->get_columns('test_table_cust0');
1138          $this->assertSame('X', $columns['anothernumber']->meta_type);
1139          // TODO: check the rest of attributes.
1140  
1141          // Change the column back from text to integer.
1142          $field = new xmldb_field('anothernumber');
1143          $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 10);
1144          $dbman->change_field_type($table, $field);
1145          // Column is integer 10 not null default 10.
1146          $columns = $DB->get_columns('test_table_cust0');
1147          $this->assertSame('I', $columns['anothernumber']->meta_type);
1148          // TODO: check the rest of attributes.
1149  
1150          // Check original value has survived to all the type changes.
1151          $this->assertnotEmpty($rec = $DB->get_record('test_table_cust0', array('id' => $recoriginal)));
1152          $this->assertEquals(4, $rec->anothernumber);
1153  
1154          $dbman->drop_table($table);
1155          $this->assertFalse($dbman->table_exists($table));
1156      }
1157  
1158      /**
1159       * Test behaviour of test_change_field_precision()
1160       */
1161      public function test_change_field_precision() {
1162          $DB = $this->tdb; // Do not use global $DB!
1163          $dbman = $this->tdb->get_manager();
1164  
1165          $table = $this->create_deftable('test_table1');
1166  
1167          // Fill the table with some records before dropping fields.
1168          $this->fill_deftable('test_table1');
1169  
1170          // Change text field from medium to big.
1171          $field = new xmldb_field('intro');
1172          $field->set_attributes(XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
1173          $dbman->change_field_precision($table, $field);
1174          $columns = $DB->get_columns('test_table1');
1175          // Cannot check the text type, only the metatype.
1176          $this->assertSame('X', $columns['intro']->meta_type);
1177          // TODO: check the rest of attributes.
1178  
1179          // Change char field from 30 to 20.
1180          $field = new xmldb_field('secondname');
1181          $field->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, null);
1182          $dbman->change_field_precision($table, $field);
1183          $columns = $DB->get_columns('test_table1');
1184          $this->assertSame('C', $columns['secondname']->meta_type);
1185          // TODO: check the rest of attributes.
1186  
1187          // Change char field from 20 to 10, having contents > 10cc. Throw exception.
1188          $field = new xmldb_field('secondname');
1189          $field->set_attributes(XMLDB_TYPE_CHAR, '10', null, XMLDB_NOTNULL, null, null);
1190          try {
1191              $dbman->change_field_precision($table, $field);
1192              $this->fail('Exception expected');
1193          } catch (\moodle_exception $e) {
1194              $this->assertInstanceOf('ddl_change_structure_exception', $e);
1195          }
1196          // No changes in field specs at all.
1197          $columns = $DB->get_columns('test_table1');
1198          $this->assertSame('C', $columns['secondname']->meta_type);
1199          // TODO: check the rest of attributes.
1200  
1201          // Change number field from 20,10 to 10,2.
1202          $field = new xmldb_field('grade');
1203          $field->set_attributes(XMLDB_TYPE_NUMBER, '10,2', null, null, null, null);
1204          $dbman->change_field_precision($table, $field);
1205          $columns = $DB->get_columns('test_table1');
1206          $this->assertSame('N', $columns['grade']->meta_type);
1207          // TODO: check the rest of attributes.
1208  
1209          // Change integer field from 10 to 2.
1210          $field = new xmldb_field('userid');
1211          $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
1212          $dbman->change_field_precision($table, $field);
1213          $columns = $DB->get_columns('test_table1');
1214          $this->assertSame('I', $columns['userid']->meta_type);
1215          // TODO: check the rest of attributes.
1216  
1217          // Change the column from integer (2) to integer (6) (forces change of type in some DBs).
1218          $field = new xmldb_field('userid');
1219          $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, null, null, null);
1220          $dbman->change_field_precision($table, $field);
1221          // Column is integer 6 null default null now.
1222          $columns = $DB->get_columns('test_table1');
1223          $this->assertSame('I', $columns['userid']->meta_type);
1224          // TODO: check the rest of attributes.
1225  
1226          // Insert one record with 6-digit field.
1227          $record = new \stdClass();
1228          $record->course = 10;
1229          $record->secondname  = 'third record';
1230          $record->intro  = 'third record';
1231          $record->userid = 123456;
1232          $DB->insert_record('test_table1', $record);
1233          // Change integer field from 6 to 2, contents are bigger, must throw exception.
1234          $field = new xmldb_field('userid');
1235          $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
1236          try {
1237              $dbman->change_field_precision($table, $field);
1238              $this->fail('Exception expected');
1239          } catch (\moodle_exception $e) {
1240              $this->assertInstanceOf('ddl_change_structure_exception', $e);
1241          }
1242          // No changes in field specs at all.
1243          $columns = $DB->get_columns('test_table1');
1244          $this->assertSame('I', $columns['userid']->meta_type);
1245          // TODO: check the rest of attributes.
1246  
1247          // Change integer field from 10 to 3, in field used by index. must throw exception.
1248          $field = new xmldb_field('course');
1249          $field->set_attributes(XMLDB_TYPE_INTEGER, '3', null, XMLDB_NOTNULL, null, '0');
1250          try {
1251              $dbman->change_field_precision($table, $field);
1252              $this->fail('Exception expected');
1253          } catch (\moodle_exception $e) {
1254              $this->assertInstanceOf('ddl_dependency_exception', $e);
1255          }
1256          // No changes in field specs at all.
1257          $columns = $DB->get_columns('test_table1');
1258          $this->assertSame('I', $columns['course']->meta_type);
1259          // TODO: check the rest of attributes.
1260      }
1261  
1262      public function testChangeFieldNullability() {
1263          $DB = $this->tdb; // Do not use global $DB!
1264          $dbman = $this->tdb->get_manager();
1265  
1266          $table = new xmldb_table('test_table_cust0');
1267          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1268          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, null);
1269          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1270          $dbman->create_table($table);
1271  
1272          $record = new \stdClass();
1273          $record->name = null;
1274  
1275          try {
1276              $result = $DB->insert_record('test_table_cust0', $record, false);
1277          } catch (\dml_exception $e) {
1278              $result = false;
1279          }
1280          $this->resetDebugging();
1281          $this->assertFalse($result);
1282  
1283          $field = new xmldb_field('name');
1284          $field->set_attributes(XMLDB_TYPE_CHAR, '30', null, null, null, null);
1285          $dbman->change_field_notnull($table, $field);
1286  
1287          $this->assertTrue($DB->insert_record('test_table_cust0', $record, false));
1288  
1289          // TODO: add some tests with existing data in table.
1290          $DB->delete_records('test_table_cust0');
1291  
1292          $field = new xmldb_field('name');
1293          $field->set_attributes(XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, null);
1294          $dbman->change_field_notnull($table, $field);
1295  
1296          try {
1297              $result = $DB->insert_record('test_table_cust0', $record, false);
1298          } catch (\dml_exception $e) {
1299              $result = false;
1300          }
1301          $this->resetDebugging();
1302          $this->assertFalse($result);
1303  
1304          $dbman->drop_table($table);
1305      }
1306  
1307      public function testChangeFieldDefault() {
1308          $DB = $this->tdb; // Do not use global $DB!
1309          $dbman = $this->tdb->get_manager();
1310  
1311          $table = new xmldb_table('test_table_cust0');
1312          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1313          $table->add_field('onenumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1314          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'Moodle');
1315          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1316          $dbman->create_table($table);
1317  
1318          $field = new xmldb_field('name');
1319          $field->set_attributes(XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'Moodle2');
1320          $dbman->change_field_default($table, $field);
1321  
1322          $record = new \stdClass();
1323          $record->onenumber = 666;
1324          $id = $DB->insert_record('test_table_cust0', $record);
1325  
1326          $record = $DB->get_record('test_table_cust0', array('id'=>$id));
1327          $this->assertSame('Moodle2', $record->name);
1328  
1329          $field = new xmldb_field('onenumber');
1330          $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 666);
1331          $dbman->change_field_default($table, $field);
1332  
1333          $record = new \stdClass();
1334          $record->name = 'something';
1335          $id = $DB->insert_record('test_table_cust0', $record);
1336  
1337          $record = $DB->get_record('test_table_cust0', array('id'=>$id));
1338          $this->assertSame('666', $record->onenumber);
1339  
1340          $dbman->drop_table($table);
1341      }
1342  
1343      public function testAddUniqueIndex() {
1344          $DB = $this->tdb; // Do not use global $DB!
1345          $dbman = $this->tdb->get_manager();
1346  
1347          $table = new xmldb_table('test_table_cust0');
1348          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1349          $table->add_field('onenumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1350          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'Moodle');
1351          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1352          $dbman->create_table($table);
1353  
1354          $record = new \stdClass();
1355          $record->onenumber = 666;
1356          $record->name = 'something';
1357          $DB->insert_record('test_table_cust0', $record, false);
1358  
1359          $index = new xmldb_index('onenumber-name');
1360          $index->set_attributes(XMLDB_INDEX_UNIQUE, array('onenumber', 'name'));
1361          $dbman->add_index($table, $index);
1362  
1363          try {
1364              $result = $DB->insert_record('test_table_cust0', $record, false);
1365          } catch (\dml_exception $e) {
1366              $result = false;
1367          }
1368          $this->resetDebugging();
1369          $this->assertFalse($result);
1370  
1371          $dbman->drop_table($table);
1372      }
1373  
1374      public function testAddNonUniqueIndex() {
1375          $dbman = $this->tdb->get_manager();
1376  
1377          $table = $this->create_deftable('test_table1');
1378          $index = new xmldb_index('secondname');
1379          $index->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('course', 'name'));
1380          $dbman->add_index($table, $index);
1381          $this->assertTrue($dbman->index_exists($table, $index));
1382  
1383          try {
1384              $dbman->add_index($table, $index);
1385              $this->fail('Exception expected for duplicate indexes');
1386          } catch (\moodle_exception $e) {
1387              $this->assertInstanceOf('ddl_exception', $e);
1388          }
1389  
1390          $index = new xmldb_index('third');
1391          $index->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('course'));
1392          try {
1393              $dbman->add_index($table, $index);
1394              $this->fail('Exception expected for duplicate indexes');
1395          } catch (\moodle_exception $e) {
1396              $this->assertInstanceOf('ddl_exception', $e);
1397          }
1398  
1399          $table = new xmldb_table('test_table_cust0');
1400          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1401          $table->add_field('onenumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1402          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'Moodle');
1403          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1404          $table->add_key('onenumber', XMLDB_KEY_FOREIGN, array('onenumber'));
1405  
1406          try {
1407              $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE, array('onenumber'));
1408              $this->fail('Coding exception expected');
1409          } catch (\moodle_exception $e) {
1410              $this->assertInstanceOf('coding_exception', $e);
1411          }
1412  
1413          $table = new xmldb_table('test_table_cust0');
1414          $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1415          $table->add_field('onenumber', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1416          $table->add_field('name', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, 'Moodle');
1417          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1418          $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE, array('onenumber'));
1419  
1420          try {
1421              $table->add_key('onenumber', XMLDB_KEY_FOREIGN, array('onenumber'));
1422              $this->fail('Coding exception expected');
1423          } catch (\moodle_exception $e) {
1424              $this->assertInstanceOf('coding_exception', $e);
1425          }
1426  
1427      }
1428  
1429      public function testFindIndexName() {
1430          $dbman = $this->tdb->get_manager();
1431  
1432          $table = $this->create_deftable('test_table1');
1433          $index = new xmldb_index('secondname');
1434          $index->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('course', 'name'));
1435          $dbman->add_index($table, $index);
1436  
1437          // DBM Systems name their indices differently - do not test the actual index name.
1438          $result = $dbman->find_index_name($table, $index);
1439          $this->assertTrue(!empty($result));
1440  
1441          $nonexistentindex = new xmldb_index('nonexistentindex');
1442          $nonexistentindex->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('name'));
1443          $this->assertFalse($dbman->find_index_name($table, $nonexistentindex));
1444      }
1445  
1446      public function testDropIndex() {
1447          $DB = $this->tdb; // Do not use global $DB!
1448  
1449          $dbman = $this->tdb->get_manager();
1450  
1451          $table = $this->create_deftable('test_table1');
1452          $index = new xmldb_index('secondname');
1453          $index->set_attributes(XMLDB_INDEX_NOTUNIQUE, array('course', 'name'));
1454          $dbman->add_index($table, $index);
1455  
1456          $dbman->drop_index($table, $index);
1457          $this->assertFalse($dbman->find_index_name($table, $index));
1458  
1459          // Test we are able to drop indexes having hyphens MDL-22804.
1460          // Create index with hyphens (by hand).
1461          $indexname = 'test-index-with-hyphens';
1462          switch ($DB->get_dbfamily()) {
1463              case 'mysql':
1464                  $indexname = '`' . $indexname . '`';
1465                  break;
1466              default:
1467                  $indexname = '"' . $indexname . '"';
1468          }
1469          $stmt = "CREATE INDEX {$indexname} ON {$DB->get_prefix()}test_table1 (course, name)";
1470          $DB->change_database_structure($stmt);
1471          $this->assertNotEmpty($dbman->find_index_name($table, $index));
1472          // Index created, let's drop it using db manager stuff.
1473          $index = new xmldb_index('indexname', XMLDB_INDEX_NOTUNIQUE, array('course', 'name'));
1474          $dbman->drop_index($table, $index);
1475          $this->assertFalse($dbman->find_index_name($table, $index));
1476      }
1477  
1478      public function testAddUniqueKey() {
1479          $dbman = $this->tdb->get_manager();
1480  
1481          $table = $this->create_deftable('test_table1');
1482          $key = new xmldb_key('id-course-grade');
1483          $key->set_attributes(XMLDB_KEY_UNIQUE, array('id', 'course', 'grade'));
1484          $dbman->add_key($table, $key);
1485  
1486          // No easy way to test it, this just makes sure no errors are encountered.
1487          $this->assertTrue(true);
1488      }
1489  
1490      public function testAddForeignUniqueKey() {
1491          $dbman = $this->tdb->get_manager();
1492  
1493          $table = $this->create_deftable('test_table1');
1494          $this->create_deftable('test_table0');
1495  
1496          $key = new xmldb_key('course');
1497          $key->set_attributes(XMLDB_KEY_FOREIGN_UNIQUE, array('course'), 'test_table0', array('id'));
1498          $dbman->add_key($table, $key);
1499  
1500          // No easy way to test it, this just makes sure no errors are encountered.
1501          $this->assertTrue(true);
1502      }
1503  
1504      public function testDropKey() {
1505          $dbman = $this->tdb->get_manager();
1506  
1507          $table = $this->create_deftable('test_table1');
1508          $this->create_deftable('test_table0');
1509  
1510          $key = new xmldb_key('course');
1511          $key->set_attributes(XMLDB_KEY_FOREIGN_UNIQUE, array('course'), 'test_table0', array('id'));
1512          $dbman->add_key($table, $key);
1513  
1514          $dbman->drop_key($table, $key);
1515  
1516          // No easy way to test it, this just makes sure no errors are encountered.
1517          $this->assertTrue(true);
1518      }
1519  
1520      public function testAddForeignKey() {
1521          $dbman = $this->tdb->get_manager();
1522  
1523          $table = $this->create_deftable('test_table1');
1524          $this->create_deftable('test_table0');
1525  
1526          $key = new xmldb_key('course');
1527          $key->set_attributes(XMLDB_KEY_FOREIGN, array('course'), 'test_table0', array('id'));
1528          $dbman->add_key($table, $key);
1529  
1530          // No easy way to test it, this just makes sure no errors are encountered.
1531          $this->assertTrue(true);
1532      }
1533  
1534      public function testDropForeignKey() {
1535          $dbman = $this->tdb->get_manager();
1536  
1537          $table = $this->create_deftable('test_table1');
1538          $this->create_deftable('test_table0');
1539  
1540          $key = new xmldb_key('course');
1541          $key->set_attributes(XMLDB_KEY_FOREIGN, array('course'), 'test_table0', array('id'));
1542          $dbman->add_key($table, $key);
1543  
1544          $dbman->drop_key($table, $key);
1545  
1546          // No easy way to test it, this just makes sure no errors are encountered.
1547          $this->assertTrue(true);
1548      }
1549  
1550      public function testRenameField() {
1551          $DB = $this->tdb; // Do not use global $DB!
1552          $dbman = $this->tdb->get_manager();
1553  
1554          $table = $this->create_deftable('test_table0');
1555          $field = new xmldb_field('type');
1556          $field->set_attributes(XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'general', 'course');
1557  
1558          // 1. Rename the 'type' field into a generic new valid name.
1559          // This represents the standard use case.
1560          $dbman->rename_field($table, $field, 'newfieldname');
1561  
1562          $columns = $DB->get_columns('test_table0');
1563  
1564          $this->assertArrayNotHasKey('type', $columns);
1565          $this->assertArrayHasKey('newfieldname', $columns);
1566          $field->setName('newfieldname');
1567  
1568          // 2. Rename the 'newfieldname' field into a reserved word, for testing purposes.
1569          // This represents a questionable use case: we should support it but discourage the use of it on peer reviewing.
1570          $dbman->rename_field($table, $field, 'where');
1571  
1572          $columns = $DB->get_columns('test_table0');
1573  
1574          $this->assertArrayNotHasKey('newfieldname', $columns);
1575          $this->assertArrayHasKey('where', $columns);
1576  
1577          // 3. Create a table with a column name named w/ a reserved word and get rid of it.
1578          // This represents a "recovering" use case: a field name could be a reserved word in the future, at least for a DB type.
1579          $table = new xmldb_table('test_table_res_word');
1580          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1581          $table->add_field('where', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1582          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1583          $table->setComment("This is a test'n drop table. You can drop it safely");
1584          $dbman->create_table($table);
1585          $dbman->table_exists('test_table_res_word');
1586  
1587          $columns = $DB->get_columns('test_table_res_word');
1588          $this->assertArrayHasKey('where', $columns);
1589          $field = $table->getField('where');
1590  
1591          $dbman->rename_field($table, $field, 'newfieldname');
1592  
1593          $columns = $DB->get_columns('test_table_res_word');
1594  
1595          $this->assertArrayNotHasKey('where', $columns);
1596          $this->assertArrayHasKey('newfieldname', $columns);
1597      }
1598  
1599      public function testIndexExists() {
1600          // Skipping: this is just a test of find_index_name.
1601      }
1602  
1603      public function testFindKeyName() {
1604          $dbman = $this->tdb->get_manager();
1605  
1606          $table = $this->create_deftable('test_table0');
1607          $key = $table->getKey('primary');
1608  
1609          // With Mysql, the return value is actually "mdl_test_id_pk".
1610          $result = $dbman->find_key_name($table, $key);
1611          $this->assertTrue(!empty($result));
1612      }
1613  
1614      public function testDeleteTablesFromXmldbFile() {
1615          $dbman = $this->tdb->get_manager();
1616  
1617          $this->create_deftable('test_table1');
1618  
1619          $this->assertTrue($dbman->table_exists('test_table1'));
1620  
1621          // Feed nonexistent file.
1622          try {
1623              $dbman->delete_tables_from_xmldb_file('fpsoiudfposui');
1624              $this->fail('Exception expected');
1625          } catch (\moodle_exception $e) {
1626              $this->resetDebugging();
1627              $this->assertInstanceOf('moodle_exception', $e);
1628          }
1629  
1630          try {
1631              $dbman->delete_tables_from_xmldb_file(__DIR__ . '/fixtures/invalid.xml');
1632              $this->fail('Exception expected');
1633          } catch (\moodle_exception $e) {
1634              $this->resetDebugging();
1635              $this->assertInstanceOf('moodle_exception', $e);
1636          }
1637  
1638          // Check that the table has not been deleted from DB.
1639          $this->assertTrue($dbman->table_exists('test_table1'));
1640  
1641          // Real and valid xml file.
1642          // TODO: drop UNSINGED completely in Moodle 2.4.
1643          $dbman->delete_tables_from_xmldb_file(__DIR__ . '/fixtures/xmldb_table.xml');
1644  
1645          // Check that the table has been deleted from DB.
1646          $this->assertFalse($dbman->table_exists('test_table1'));
1647      }
1648  
1649      public function testInstallFromXmldbFile() {
1650          $dbman = $this->tdb->get_manager();
1651  
1652          // Feed nonexistent file.
1653          try {
1654              $dbman->install_from_xmldb_file('fpsoiudfposui');
1655              $this->fail('Exception expected');
1656          } catch (\moodle_exception $e) {
1657              $this->resetDebugging();
1658              $this->assertInstanceOf('moodle_exception', $e);
1659          }
1660  
1661          try {
1662              $dbman->install_from_xmldb_file(__DIR__ . '/fixtures/invalid.xml');
1663              $this->fail('Exception expected');
1664          } catch (\moodle_exception $e) {
1665              $this->resetDebugging();
1666              $this->assertInstanceOf('moodle_exception', $e);
1667          }
1668  
1669          // Check that the table has not yet been created in DB.
1670          $this->assertFalse($dbman->table_exists('test_table1'));
1671  
1672          // Real and valid xml file.
1673          $dbman->install_from_xmldb_file(__DIR__ . '/fixtures/xmldb_table.xml');
1674          $this->assertTrue($dbman->table_exists('test_table1'));
1675      }
1676  
1677      public function test_temp_tables() {
1678          $DB = $this->tdb; // Do not use global $DB!
1679          $dbman = $this->tdb->get_manager();
1680  
1681          // Create temp table0.
1682          $table0 = $this->tables['test_table0'];
1683          $dbman->create_temp_table($table0);
1684          $this->assertTrue($dbman->table_exists('test_table0'));
1685  
1686          // Try to create temp table with same name, must throw exception.
1687          $dupetable = $this->tables['test_table0'];
1688          try {
1689              $dbman->create_temp_table($dupetable);
1690              $this->fail('Exception expected');
1691          } catch (\moodle_exception $e) {
1692              $this->assertInstanceOf('ddl_exception', $e);
1693          }
1694  
1695          // Try to create table with same name, must throw exception.
1696          $dupetable = $this->tables['test_table0'];
1697          try {
1698              $dbman->create_table($dupetable);
1699              $this->fail('Exception expected');
1700          } catch (\moodle_exception $e) {
1701              $this->assertInstanceOf('ddl_exception', $e);
1702          }
1703  
1704          // Create another temp table1.
1705          $table1 = $this->tables['test_table1'];
1706          $dbman->create_temp_table($table1);
1707          $this->assertTrue($dbman->table_exists('test_table1'));
1708  
1709          // Get columns and perform some basic tests.
1710          $columns = $DB->get_columns('test_table1');
1711          $this->assertCount(11, $columns);
1712          $this->assertTrue($columns['name'] instanceof database_column_info);
1713          $this->assertEquals(30, $columns['name']->max_length);
1714          $this->assertTrue($columns['name']->has_default);
1715          $this->assertEquals('Moodle', $columns['name']->default_value);
1716  
1717          // Insert some records.
1718          $inserted = $this->fill_deftable('test_table1');
1719          $records = $DB->get_records('test_table1');
1720          $this->assertCount($inserted, $records);
1721          $this->assertSame($records[1]->course, $this->records['test_table1'][0]->course);
1722          $this->assertSame($records[1]->secondname, $this->records['test_table1'][0]->secondname);
1723          $this->assertSame($records[2]->intro, $this->records['test_table1'][1]->intro);
1724  
1725          // Collect statistics about the data in the temp table.
1726          $DB->update_temp_table_stats();
1727  
1728          // Drop table1.
1729          $dbman->drop_table($table1);
1730          $this->assertFalse($dbman->table_exists('test_table1'));
1731  
1732          // Try to drop non-existing temp table, must throw exception.
1733          $noetable = $this->tables['test_table1'];
1734          try {
1735              $dbman->drop_table($noetable);
1736              $this->fail('Exception expected');
1737          } catch (\moodle_exception $e) {
1738              $this->assertInstanceOf('ddl_table_missing_exception', $e);
1739          }
1740  
1741          // Collect statistics about the data in the temp table with less tables.
1742          $DB->update_temp_table_stats();
1743  
1744          // Fill/modify/delete a few table0 records.
1745  
1746          // Drop table0.
1747          $dbman->drop_table($table0);
1748          $this->assertFalse($dbman->table_exists('test_table0'));
1749  
1750          // Create another temp table1.
1751          $table1 = $this->tables['test_table1'];
1752          $dbman->create_temp_table($table1);
1753          $this->assertTrue($dbman->table_exists('test_table1'));
1754  
1755          // Make sure it can be dropped using deprecated drop_temp_table().
1756          $dbman->drop_temp_table($table1);
1757          $this->assertFalse($dbman->table_exists('test_table1'));
1758          $this->assertDebuggingCalled();
1759  
1760          // Try join with normal tables - MS SQL may use incompatible collation.
1761          $table1 = new xmldb_table('test_table');
1762          $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1763          $table1->add_field('name', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
1764          $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1765          $dbman->create_table($table1);
1766  
1767          $table2 = new xmldb_table('test_temp');
1768          $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1769          $table2->add_field('name', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
1770          $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1771          $dbman->create_temp_table($table2);
1772  
1773          $record = array('name' => 'a');
1774          $DB->insert_record('test_table', $record);
1775          $DB->insert_record('test_temp', $record);
1776  
1777          $record = array('name' => 'b');
1778          $DB->insert_record('test_table', $record);
1779  
1780          $record = array('name' => 'c');
1781          $DB->insert_record('test_temp', $record);
1782  
1783          $sql = "SELECT *
1784                    FROM {test_table} n
1785                    JOIN {test_temp} t ON t.name = n.name";
1786          $records = $DB->get_records_sql($sql);
1787          $this->assertCount(1, $records);
1788  
1789          // Drop temp table.
1790          $dbman->drop_table($table2);
1791          $this->assertFalse($dbman->table_exists('test_temp'));
1792      }
1793  
1794      public function test_concurrent_temp_tables() {
1795          $DB = $this->tdb; // Do not use global $DB!
1796          $dbman = $this->tdb->get_manager();
1797  
1798          // Define 2 records.
1799          $record1 = (object)array(
1800              'course'     =>  1,
1801              'secondname' => '11 important',
1802              'intro'      => '111 important');
1803          $record2 = (object)array(
1804              'course'     =>  2,
1805              'secondname' => '22 important',
1806              'intro'      => '222 important');
1807  
1808          // Create temp table1 and insert 1 record (in DB).
1809          $table = $this->tables['test_table1'];
1810          $dbman->create_temp_table($table);
1811          $this->assertTrue($dbman->table_exists('test_table1'));
1812          $inserted = $DB->insert_record('test_table1', $record1);
1813  
1814          // Switch to new connection.
1815          $cfg = $DB->export_dbconfig();
1816          if (!isset($cfg->dboptions)) {
1817              $cfg->dboptions = array();
1818          }
1819          $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
1820          $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
1821          $dbman2 = $DB2->get_manager();
1822          $this->assertFalse($dbman2->table_exists('test_table1')); // Temp table not exists in DB2.
1823  
1824          // Create temp table1 and insert 1 record (in DB2).
1825          $table = $this->tables['test_table1'];
1826          $dbman2->create_temp_table($table);
1827          $this->assertTrue($dbman2->table_exists('test_table1'));
1828          $inserted = $DB2->insert_record('test_table1', $record2);
1829  
1830          $dbman2->drop_table($table); // Drop temp table before closing DB2.
1831          $this->assertFalse($dbman2->table_exists('test_table1'));
1832          $DB2->dispose(); // Close DB2.
1833  
1834          $this->assertTrue($dbman->table_exists('test_table1')); // Check table continues existing for DB.
1835          $dbman->drop_table($table); // Drop temp table.
1836          $this->assertFalse($dbman->table_exists('test_table1'));
1837      }
1838  
1839      /**
1840       * get_columns should return an empty array for ex-temptables.
1841       */
1842      public function test_leftover_temp_tables_columns() {
1843          $DB = $this->tdb; // Do not use global $DB!
1844          $dbman = $this->tdb->get_manager();
1845  
1846          // Create temp table0.
1847          $table0 = $this->tables['test_table0'];
1848          $dbman->create_temp_table($table0);
1849  
1850          $dbman->drop_table($table0);
1851  
1852          // Get columns and perform some basic tests.
1853          $columns = $DB->get_columns('test_table0');
1854          $this->assertEquals([], $columns);
1855      }
1856  
1857      /**
1858       * Deleting a temp table should not purge the whole cache
1859       */
1860      public function test_leftover_temp_tables_cache() {
1861          $DB = $this->tdb; // Do not use global $DB!
1862          $dbman = $this->tdb->get_manager();
1863  
1864          // Create 2 temp tables.
1865          $table0 = $this->tables['test_table0'];
1866          $dbman->create_temp_table($table0);
1867          $table1 = $this->tables['test_table1'];
1868          $dbman->create_temp_table($table1);
1869  
1870          // Create a normal table.
1871          $table2 = new xmldb_table ('test_table2');
1872          $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1873          $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1874          $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1875          $table2->setComment("This is a test'n drop table. You can drop it safely");
1876          $this->tables[$table2->getName()] = $table2;
1877          $dbman->create_table($table2);
1878  
1879          // Get columns for the tables, so that relevant caches are populated with their data.
1880          $DB->get_columns('test_table0');
1881          $DB->get_columns('test_table1');
1882          $DB->get_columns('test_table2');
1883  
1884          $dbman->drop_table($table0);
1885  
1886          $rc = new \ReflectionClass('moodle_database');
1887          $rcm = $rc->getMethod('get_temp_tables_cache');
1888          $rcm->setAccessible(true);
1889          $metacachetemp = $rcm->invokeArgs($DB, []);
1890  
1891          // Data of test_table0 should be removed from the cache.
1892          $this->assertEquals(false, $metacachetemp->has('test_table0'));
1893  
1894          // Data of test_table1 should be intact.
1895          $this->assertEquals(true, $metacachetemp->has('test_table1'));
1896  
1897          $rc = new \ReflectionClass('moodle_database');
1898          $rcm = $rc->getMethod('get_metacache');
1899          $rcm->setAccessible(true);
1900          $metacache = $rcm->invokeArgs($DB, []);
1901  
1902          // Data of test_table2 should be intact.
1903          $this->assertEquals(true, $metacache->has('test_table2'));
1904  
1905          // Delete the leftover temp table.
1906          $dbman->drop_table($table1);
1907      }
1908  
1909      public function test_reset_sequence() {
1910          $DB = $this->tdb;
1911          $dbman = $DB->get_manager();
1912  
1913          $table = new xmldb_table('testtable');
1914          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1915          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1916          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1917  
1918          // Drop if exists.
1919          if ($dbman->table_exists($table)) {
1920              $dbman->drop_table($table);
1921          }
1922          $dbman->create_table($table);
1923          $tablename = $table->getName();
1924          $this->tables[$tablename] = $table;
1925  
1926          $record = (object)array('id'=>666, 'course'=>10);
1927          $DB->import_record('testtable', $record);
1928          $DB->delete_records('testtable'); // This delete performs one TRUNCATE.
1929  
1930          $dbman->reset_sequence($table); // Using xmldb object.
1931          $this->assertEquals(1, $DB->insert_record('testtable', (object)array('course'=>13)));
1932  
1933          $record = (object)array('id'=>666, 'course'=>10);
1934          $DB->import_record('testtable', $record);
1935          $DB->delete_records('testtable', array()); // This delete performs one DELETE.
1936  
1937          $dbman->reset_sequence($table); // Using xmldb object.
1938          $this->assertEquals(1, $DB->insert_record('testtable', (object)array('course'=>13)),
1939              'Some versions of MySQL 5.6.x are known to not support lowering of auto-increment numbers.');
1940  
1941          $DB->import_record('testtable', $record);
1942          $dbman->reset_sequence($tablename); // Using string.
1943          $this->assertEquals(667, $DB->insert_record('testtable', (object)array('course'=>13)));
1944  
1945          $dbman->drop_table($table);
1946      }
1947  
1948      public function test_reserved_words() {
1949          $reserved = sql_generator::getAllReservedWords();
1950          $this->assertTrue(count($reserved) > 1);
1951      }
1952  
1953      public function test_index_hints() {
1954          $DB = $this->tdb;
1955          $dbman = $DB->get_manager();
1956  
1957          $table = new xmldb_table('testtable');
1958          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1959          $table->add_field('name', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
1960          $table->add_field('path', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
1961          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1962          $table->add_index('name', XMLDB_INDEX_NOTUNIQUE, array('name'), array('xxxx,yyyy'));
1963          $table->add_index('path', XMLDB_INDEX_NOTUNIQUE, array('path'), array('varchar_pattern_ops'));
1964  
1965          // Drop if exists.
1966          if ($dbman->table_exists($table)) {
1967              $dbman->drop_table($table);
1968          }
1969          $dbman->create_table($table);
1970          $tablename = $table->getName();
1971          $this->tables[$tablename] = $table;
1972  
1973          $table = new xmldb_table('testtable');
1974          $index = new xmldb_index('name', XMLDB_INDEX_NOTUNIQUE, array('name'), array('xxxx,yyyy'));
1975          $this->assertTrue($dbman->index_exists($table, $index));
1976  
1977          $table = new xmldb_table('testtable');
1978          $index = new xmldb_index('path', XMLDB_INDEX_NOTUNIQUE, array('path'), array('varchar_pattern_ops'));
1979          $this->assertTrue($dbman->index_exists($table, $index));
1980  
1981          // Try unique indexes too.
1982          $dbman->drop_table($this->tables[$tablename]);
1983  
1984          $table = new xmldb_table('testtable');
1985          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1986          $table->add_field('path', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
1987          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1988          $table->add_index('path', XMLDB_INDEX_UNIQUE, array('path'), array('varchar_pattern_ops'));
1989          $dbman->create_table($table);
1990          $this->tables[$tablename] = $table;
1991  
1992          $table = new xmldb_table('testtable');
1993          $index = new xmldb_index('path', XMLDB_INDEX_UNIQUE, array('path'), array('varchar_pattern_ops'));
1994          $this->assertTrue($dbman->index_exists($table, $index));
1995      }
1996  
1997      public function test_index_max_bytes() {
1998          $DB = $this->tdb;
1999          $dbman = $DB->get_manager();
2000  
2001          $maxstr = '';
2002          for ($i=0; $i<255; $i++) {
2003              $maxstr .= '言'; // Random long string that should fix exactly the limit for one char column.
2004          }
2005  
2006          $table = new xmldb_table('testtable');
2007          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2008          $table->add_field('name', XMLDB_TYPE_CHAR, 255, null, XMLDB_NOTNULL, null);
2009          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2010          $table->add_index('name', XMLDB_INDEX_NOTUNIQUE, array('name'));
2011  
2012          // Drop if exists.
2013          if ($dbman->table_exists($table)) {
2014              $dbman->drop_table($table);
2015          }
2016          $dbman->create_table($table);
2017          $tablename = $table->getName();
2018          $this->tables[$tablename] = $table;
2019  
2020          $rec = new \stdClass();
2021          $rec->name = $maxstr;
2022  
2023          $id = $DB->insert_record($tablename, $rec);
2024          $this->assertTrue(!empty($id));
2025  
2026          $rec = $DB->get_record($tablename, array('id'=>$id));
2027          $this->assertSame($maxstr, $rec->name);
2028  
2029          $dbman->drop_table($table);
2030  
2031          $table = new xmldb_table('testtable');
2032          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2033          $table->add_field('name', XMLDB_TYPE_CHAR, 255+1, null, XMLDB_NOTNULL, null);
2034          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2035          $table->add_index('name', XMLDB_INDEX_NOTUNIQUE, array('name'));
2036  
2037          try {
2038              $dbman->create_table($table);
2039              $this->fail('Exception expected');
2040          } catch (\moodle_exception $e) {
2041              $this->assertInstanceOf('coding_exception', $e);
2042          }
2043      }
2044  
2045      public function test_index_composed_max_bytes() {
2046          $DB = $this->tdb;
2047          $dbman = $DB->get_manager();
2048  
2049          $maxstr = '';
2050          for ($i=0; $i<200; $i++) {
2051              $maxstr .= '言';
2052          }
2053          $reststr = '';
2054          for ($i=0; $i<133; $i++) {
2055              $reststr .= '言';
2056          }
2057  
2058          $table = new xmldb_table('testtable');
2059          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2060          $table->add_field('name1', XMLDB_TYPE_CHAR, 200, null, XMLDB_NOTNULL, null);
2061          $table->add_field('name2', XMLDB_TYPE_CHAR, 133, null, XMLDB_NOTNULL, null);
2062          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2063          $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE, array('name1', 'name2'));
2064  
2065          // Drop if exists.
2066          if ($dbman->table_exists($table)) {
2067              $dbman->drop_table($table);
2068          }
2069          $dbman->create_table($table);
2070          $tablename = $table->getName();
2071          $this->tables[$tablename] = $table;
2072  
2073          $rec = new \stdClass();
2074          $rec->name1 = $maxstr;
2075          $rec->name2 = $reststr;
2076  
2077          $id = $DB->insert_record($tablename, $rec);
2078          $this->assertTrue(!empty($id));
2079  
2080          $rec = $DB->get_record($tablename, array('id'=>$id));
2081          $this->assertSame($maxstr, $rec->name1);
2082          $this->assertSame($reststr, $rec->name2);
2083  
2084          $table = new xmldb_table('testtable');
2085          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2086          $table->add_field('name1', XMLDB_TYPE_CHAR, 201, null, XMLDB_NOTNULL, null);
2087          $table->add_field('name2', XMLDB_TYPE_CHAR, 133, null, XMLDB_NOTNULL, null);
2088          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2089          $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE, array('name1', 'name2'));
2090  
2091          // Drop if exists.
2092          if ($dbman->table_exists($table)) {
2093              $dbman->drop_table($table);
2094          }
2095  
2096          try {
2097              $dbman->create_table($table);
2098              $this->fail('Exception expected');
2099          } catch (\moodle_exception $e) {
2100              $this->assertInstanceOf('coding_exception', $e);
2101          }
2102      }
2103  
2104      public function test_char_size_limit() {
2105          $DB = $this->tdb;
2106          $dbman = $DB->get_manager();
2107  
2108          $table = new xmldb_table('testtable');
2109          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2110          $table->add_field('name', XMLDB_TYPE_CHAR, xmldb_field::CHAR_MAX_LENGTH, null, XMLDB_NOTNULL, null);
2111          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2112  
2113          // Drop if exists.
2114          if ($dbman->table_exists($table)) {
2115              $dbman->drop_table($table);
2116          }
2117          $dbman->create_table($table);
2118          $tablename = $table->getName();
2119          $this->tables[$tablename] = $table;
2120  
2121          // This has to work in all DBs.
2122          $maxstr = '';
2123          for ($i=0; $i<xmldb_field::CHAR_MAX_LENGTH; $i++) {
2124              $maxstr .= 'a'; // Ascii only.
2125          }
2126  
2127          $rec = new \stdClass();
2128          $rec->name = $maxstr;
2129  
2130          $id = $DB->insert_record($tablename, $rec);
2131          $this->assertTrue(!empty($id));
2132  
2133          $rec = $DB->get_record($tablename, array('id'=>$id));
2134          $this->assertSame($maxstr, $rec->name);
2135  
2136          // Following test is supposed to fail in oracle.
2137          $maxstr = '';
2138          for ($i=0; $i<xmldb_field::CHAR_MAX_LENGTH; $i++) {
2139              $maxstr .= '言'; // Random long string that should fix exactly the limit for one char column.
2140          }
2141  
2142          $rec = new \stdClass();
2143          $rec->name = $maxstr;
2144  
2145          try {
2146              $id = $DB->insert_record($tablename, $rec);
2147              $this->assertTrue(!empty($id));
2148  
2149              $rec = $DB->get_record($tablename, array('id'=>$id));
2150              $this->assertSame($maxstr, $rec->name);
2151          } catch (dml_exception $e) {
2152              if ($DB->get_dbfamily() === 'oracle') {
2153                  $this->fail('Oracle does not support text fields larger than 4000 bytes, this is not a big problem for mostly ascii based languages');
2154              } else {
2155                  throw $e;
2156              }
2157          }
2158  
2159          $table = new xmldb_table('testtable');
2160          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2161          $table->add_field('name', XMLDB_TYPE_CHAR, xmldb_field::CHAR_MAX_LENGTH+1, null, XMLDB_NOTNULL, null);
2162          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2163  
2164          // Drop if exists.
2165          if ($dbman->table_exists($table)) {
2166              $dbman->drop_table($table);
2167          }
2168          $tablename = $table->getName();
2169          $this->tables[$tablename] = $table;
2170  
2171          try {
2172              $dbman->create_table($table);
2173              $this->fail('Exception expected');
2174          } catch (\moodle_exception $e) {
2175              $this->assertInstanceOf('coding_exception', $e);
2176          }
2177      }
2178  
2179      public function test_object_name() {
2180          $gen = $this->tdb->get_manager()->generator;
2181  
2182          // This will form short object name and max length should not be exceeded.
2183          $table = 'tablename';
2184          $fields = 'id';
2185          $suffix = 'pk';
2186          for ($i=0; $i<12; $i++) {
2187              $this->assertLessThanOrEqual($gen->names_max_length,
2188                      strlen($gen->getNameForObject($table, $fields, $suffix)),
2189                      'Generated object name is too long. $i = '.$i);
2190          }
2191  
2192          // This will form too long object name always and it must be trimmed to exactly 30 chars.
2193          $table = 'aaaa_bbbb_cccc_dddd_eeee_ffff_gggg';
2194          $fields = 'aaaaa,bbbbb,ccccc,ddddd';
2195          $suffix = 'idx';
2196          for ($i=0; $i<12; $i++) {
2197              $this->assertEquals($gen->names_max_length,
2198                      strlen($gen->getNameForObject($table, $fields, $suffix)),
2199                      'Generated object name is too long. $i = '.$i);
2200          }
2201  
2202          // Same test without suffix.
2203          $table = 'bbbb_cccc_dddd_eeee_ffff_gggg_hhhh';
2204          $fields = 'aaaaa,bbbbb,ccccc,ddddd';
2205          $suffix = '';
2206          for ($i=0; $i<12; $i++) {
2207              $this->assertEquals($gen->names_max_length,
2208                      strlen($gen->getNameForObject($table, $fields, $suffix)),
2209                      'Generated object name is too long. $i = '.$i);
2210          }
2211  
2212          // This must only trim name when counter is 10 or more.
2213          $table = 'cccc_dddd_eeee_ffff_gggg_hhhh_iiii';
2214          $fields = 'id';
2215          $suffix = 'idx';
2216          // Since we don't know how long prefix is, loop to generate tablename that gives exactly maxlengh-1 length.
2217          // Skip this test if prefix is too long.
2218          while (strlen($table) && strlen($gen->prefix.preg_replace('/_/','',$table).'_id_'.$suffix) >= $gen->names_max_length) {
2219              $table = rtrim(substr($table, 0, strlen($table) - 1), '_');
2220          }
2221          if (strlen($table)) {
2222              $this->assertEquals($gen->names_max_length - 1,
2223                          strlen($gen->getNameForObject($table, $fields, $suffix)));
2224              for ($i=0; $i<12; $i++) {
2225                  $this->assertEquals($gen->names_max_length,
2226                          strlen($gen->getNameForObject($table, $fields, $suffix)),
2227                          'Generated object name is too long. $i = '.$i);
2228              }
2229  
2230              // Now test to confirm that a duplicate name isn't issued, even if they come from different root names.
2231              // Move to a new field.
2232              $fields = "fl";
2233  
2234              // Insert twice, moving is to a key with fl2.
2235              $this->assertEquals($gen->names_max_length - 1, strlen($gen->getNameForObject($table, $fields, $suffix)));
2236              $result1 = $gen->getNameForObject($table, $fields, $suffix);
2237  
2238              // Make sure we end up with _fl2_ in the result.
2239              $this->assertMatchesRegularExpression('/_fl2_/', $result1);
2240  
2241              // Now, use a field that would result in the same key if it wasn't already taken.
2242              $fields = "fl2";
2243              // Because we are now at the max key length, it will try:
2244              // - _fl2_ (the natural name)
2245              // - _fl2_ (removing the original 2, and adding a counter 2)
2246              // - then settle on _fl3_.
2247              $result2 = $gen->getNameForObject($table, $fields, $suffix);
2248              $this->assertMatchesRegularExpression('/_fl3_/', $result2);
2249  
2250              // Make sure they don't match.
2251              $this->assertNotEquals($result1, $result2);
2252              // But are only different in the way we expect. This confirms the test is working properly.
2253              $this->assertEquals(str_replace('_fl2_', '', $result1), str_replace('_fl3_', '', $result2));
2254  
2255              // Now go back. We would expect the next result to be fl3 again, but it is taken, so it should move to fl4.
2256              $fields = "fl";
2257              $result3 = $gen->getNameForObject($table, $fields, $suffix);
2258  
2259              $this->assertNotEquals($result2, $result3);
2260              $this->assertMatchesRegularExpression('/_fl4_/', $result3);
2261          }
2262      }
2263  
2264      /**
2265       * Data provider for test_get_enc_quoted().
2266       *
2267       * @return array The type-value pair fixture.
2268       */
2269      public function get_enc_quoted_provider() {
2270          return array(
2271              // Reserved: some examples from SQL-92.
2272              [true, 'from'],
2273              [true, 'table'],
2274              [true, 'where'],
2275              // Not reserved.
2276              [false, 'my_awesome_column_name']
2277          );
2278      }
2279  
2280      /**
2281       * This is a test for sql_generator::getEncQuoted().
2282       *
2283       * @dataProvider get_enc_quoted_provider
2284       * @param bool $reserved Whether the column name is reserved or not.
2285       * @param string $columnname The column name to be quoted, according to the value of $reserved.
2286       **/
2287      public function test_get_enc_quoted($reserved, $columnname) {
2288          $DB = $this->tdb;
2289          $gen = $DB->get_manager()->generator;
2290  
2291          if (!$reserved) {
2292              // No need to quote the column name.
2293              $this->assertSame($columnname, $gen->getEncQuoted($columnname));
2294          } else {
2295              // Column name should be quoted.
2296              $dbfamily = $DB->get_dbfamily();
2297  
2298              switch ($dbfamily) {
2299                  case 'mysql':
2300                      $this->assertSame("`$columnname`", $gen->getEncQuoted($columnname));
2301                      break;
2302                  case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2303                  case 'oracle':
2304                  case 'postgres':
2305                  case 'sqlite':
2306                  default:
2307                      $this->assertSame('"' . $columnname . '"', $gen->getEncQuoted($columnname));
2308                      break;
2309              }
2310          }
2311      }
2312  
2313      /**
2314       * Data provider for test_sql_generator_get_rename_field_sql().
2315       *
2316       * @return array The type-old-new tuple fixture.
2317       */
2318      public function sql_generator_get_rename_field_sql_provider() {
2319          return array(
2320              // Reserved: an example from SQL-92.
2321              // Both names should be reserved.
2322              [true, 'from', 'where'],
2323              // Not reserved.
2324              [false, 'my_old_column_name', 'my_awesome_column_name']
2325          );
2326      }
2327  
2328      /**
2329       * This is a unit test for sql_generator::getRenameFieldSQL().
2330       *
2331       * @dataProvider sql_generator_get_rename_field_sql_provider
2332       * @param bool $reserved Whether the column name is reserved or not.
2333       * @param string $oldcolumnname The column name to be renamed.
2334       * @param string $newcolumnname The new column name.
2335       **/
2336      public function test_sql_generator_get_rename_field_sql($reserved, $oldcolumnname, $newcolumnname) {
2337          $DB = $this->tdb;
2338          $gen = $DB->get_manager()->generator;
2339          $prefix = $DB->get_prefix();
2340  
2341          $tablename = 'test_get_rename_field_sql';
2342          $table = new xmldb_table($tablename);
2343          $field = new xmldb_field($oldcolumnname, XMLDB_TYPE_INTEGER, '11', null, XMLDB_NOTNULL, null, null, null, '0', 'previous');
2344  
2345          $dbfamily = $DB->get_dbfamily();
2346          if (!$reserved) {
2347              // No need to quote the column name.
2348              switch ($dbfamily) {
2349                  case 'mysql':
2350                      $this->assertSame(
2351                          [ "ALTER TABLE {$prefix}$tablename CHANGE $oldcolumnname $newcolumnname BIGINT(11) NOT NULL" ],
2352                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2353                      );
2354                      break;
2355                  case 'sqlite':
2356                      // Skip it, since the DB is not supported yet.
2357                      // BTW renaming a column name is already covered by the integration test 'testRenameField'.
2358                      break;
2359                  case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2360                      $this->assertSame(
2361                          [ "sp_rename '{$prefix}$tablename.[$oldcolumnname]', '$newcolumnname', 'COLUMN'" ],
2362                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2363                      );
2364                      break;
2365                  case 'oracle':
2366                  case 'postgres':
2367                  default:
2368                      $this->assertSame(
2369                          [ "ALTER TABLE {$prefix}$tablename RENAME COLUMN $oldcolumnname TO $newcolumnname" ],
2370                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2371                      );
2372                      break;
2373              }
2374          } else {
2375              // Column name should be quoted.
2376              switch ($dbfamily) {
2377                  case 'mysql':
2378                      $this->assertSame(
2379                          [ "ALTER TABLE {$prefix}$tablename CHANGE `$oldcolumnname` `$newcolumnname` BIGINT(11) NOT NULL" ],
2380                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2381                      );
2382                      break;
2383                  case 'sqlite':
2384                      // Skip it, since the DB is not supported yet.
2385                      // BTW renaming a column name is already covered by the integration test 'testRenameField'.
2386                  break;
2387                  case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2388                      $this->assertSame(
2389                          [ "sp_rename '{$prefix}$tablename.[$oldcolumnname]', '$newcolumnname', 'COLUMN'" ],
2390                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2391                      );
2392                      break;
2393                  case 'oracle':
2394                  case 'postgres':
2395                  default:
2396                      $this->assertSame(
2397                          [ "ALTER TABLE {$prefix}$tablename RENAME COLUMN \"$oldcolumnname\" TO \"$newcolumnname\"" ],
2398                          $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2399                      );
2400                      break;
2401              }
2402          }
2403      }
2404  
2405      public function test_get_nullable_fields_in_index() {
2406          $DB = $this->tdb;
2407          $gen = $DB->get_manager()->generator;
2408  
2409          $indexwithoutnulls = $this->tables['test_table0']->getIndex('type-name');
2410          $this->assertSame([], $gen->get_nullable_fields_in_index(
2411                  $this->tables['test_table0'], $indexwithoutnulls));
2412  
2413          $indexwithnulls = new xmldb_index('course-grade', XMLDB_INDEX_UNIQUE, ['course', 'grade']);
2414          $this->assertSame(['grade'], $gen->get_nullable_fields_in_index(
2415                  $this->tables['test_table0'], $indexwithnulls));
2416  
2417          $this->create_deftable('test_table0');
2418  
2419          // Now test using a minimal xmldb_table, to ensure we get the data from the DB.
2420          $table = new xmldb_table('test_table0');
2421          $this->assertSame([], $gen->get_nullable_fields_in_index(
2422                  $table, $indexwithoutnulls));
2423          $this->assertSame(['grade'], $gen->get_nullable_fields_in_index(
2424                  $table, $indexwithnulls));
2425      }
2426  
2427      // Following methods are not supported == Do not test.
2428      /*
2429          public function testRenameIndex() {
2430              // Unsupported!
2431              $dbman = $this->tdb->get_manager();
2432  
2433              $table = $this->create_deftable('test_table0');
2434              $index = new xmldb_index('course');
2435              $index->set_attributes(XMLDB_INDEX_UNIQUE, array('course'));
2436  
2437              $this->assertTrue($dbman->rename_index($table, $index, 'newindexname'));
2438          }
2439  
2440          public function testRenameKey() {
2441              // Unsupported!
2442               $dbman = $this->tdb->get_manager();
2443  
2444              $table = $this->create_deftable('test_table0');
2445              $key = new xmldb_key('course');
2446              $key->set_attributes(XMLDB_KEY_UNIQUE, array('course'));
2447  
2448              $this->assertTrue($dbman->rename_key($table, $key, 'newkeyname'));
2449          }
2450      */
2451  
2452      /**
2453       * Tests check_database_schema().
2454       */
2455      public function test_check_database_schema() {
2456          global $CFG, $DB;
2457  
2458          $dbmanager = $DB->get_manager();
2459  
2460          // Create a table in the database we will be using to compare with a schema.
2461          $table = new xmldb_table('test_check_db_schema');
2462          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2463          $table->add_field('extracolumn', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2464          $table->add_field('courseid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2465          $table->add_field('binaryfield', XMLDB_TYPE_BINARY, null, null, XMLDB_NOTNULL, null, null);
2466          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2467          $table->add_key('extraindex', XMLDB_KEY_UNIQUE, array('extracolumn'));
2468          $table->setComment("This is a test table, you can drop it safely.");
2469          $dbmanager->create_table($table);
2470  
2471          // Remove the column so it is not added to the schema and gets reported as an extra column.
2472          $table->deleteField('extracolumn');
2473  
2474          // Change the 'courseid' field to a float in the schema so it gets reported as different.
2475          $table->deleteField('courseid');
2476          $table->add_field('courseid', XMLDB_TYPE_NUMBER, '10, 2', null, XMLDB_NOTNULL, null, null);
2477  
2478          // Add another column to the schema that won't be present in the database and gets reported as missing.
2479          $table->add_field('missingcolumn', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2480  
2481          // Add another key to the schema that won't be present in the database and gets reported as missing.
2482          $table->add_key('missingkey', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
2483  
2484          // Remove the key from the schema which will still be present in the database and reported as extra.
2485          $table->deleteKey('extraindex');
2486  
2487          $schema = new xmldb_structure('testschema');
2488          $schema->addTable($table);
2489  
2490          // Things we want to check for -
2491          // 1. Changed columns.
2492          // 2. Missing columns.
2493          // 3. Missing indexes.
2494          // 4. Unexpected index.
2495          // 5. Extra columns.
2496          $errors = $dbmanager->check_database_schema($schema)['test_check_db_schema'];
2497          // Preprocess $errors to get rid of the non compatible (SQL-dialect dependent) parts.
2498          array_walk($errors, function(&$error) {
2499              $error = trim(strtok($error, PHP_EOL));
2500          });
2501          $this->assertCount(5, $errors);
2502          $this->assertContains("column 'courseid' has incorrect type 'I', expected 'N'", $errors);
2503          $this->assertContains("column 'missingcolumn' is missing", $errors);
2504          $this->assertContains("Missing index 'missingkey' (not unique (courseid)).", $errors);
2505          $this->assertContains("Unexpected index '{$CFG->prefix}testchecdbsche_ext_uix'.", $errors);
2506          $this->assertContains("column 'extracolumn' is not expected (I)", $errors);
2507      }
2508  }