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