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