2 // This file is part of Moodle - http://moodle.org/
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.
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.
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/>.
22 * @copyright 2008 Nicolas Connault
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 defined('MOODLE_INTERNAL') ||
die();
28 class core_ddl_testcase
extends database_driver_testcase
{
29 /** @var xmldb_table[] keys are table name. Created in setUp. */
30 private $tables = array();
31 /** @var array table name => array of stdClass test records loaded into that table. Created in setUp. */
32 private $records = array();
34 protected function setUp(): void
{
36 $dbman = $this->tdb
->get_manager(); // Loads DDL libs.
38 $table = new xmldb_table('test_table0');
39 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
40 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
41 $table->add_field('type', XMLDB_TYPE_CHAR
, '20', null, XMLDB_NOTNULL
, null, 'general');
42 $table->add_field('name', XMLDB_TYPE_CHAR
, '255', null, XMLDB_NOTNULL
, null);
43 $table->add_field('intro', XMLDB_TYPE_TEXT
, 'small', null, XMLDB_NOTNULL
, null, null);
44 $table->add_field('logo', XMLDB_TYPE_BINARY
, 'big', null, null, null);
45 $table->add_field('assessed', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
46 $table->add_field('assesstimestart', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
47 $table->add_field('assesstimefinish', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
48 $table->add_field('scale', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
49 $table->add_field('maxbytes', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
50 $table->add_field('forcesubscribe', XMLDB_TYPE_INTEGER
, '1', null, XMLDB_NOTNULL
, null, '0');
51 $table->add_field('trackingtype', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, null, '1');
52 $table->add_field('rsstype', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, null, '0');
53 $table->add_field('rssarticles', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, null, '0');
54 $table->add_field('timemodified', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
55 $table->add_field('grade', XMLDB_TYPE_NUMBER
, '20,0', null, null, null, null);
56 $table->add_field('percent', XMLDB_TYPE_NUMBER
, '5,2', null, null, null, 66.6);
57 $table->add_field('bignum', XMLDB_TYPE_NUMBER
, '38,18', null, null, null, 1234567890.1234);
58 $table->add_field('warnafter', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
59 $table->add_field('blockafter', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
60 $table->add_field('blockperiod', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
61 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
62 $table->add_key('course', XMLDB_KEY_UNIQUE
, array('course'));
63 $table->add_index('type-name', XMLDB_INDEX_UNIQUE
, array('type', 'name'));
64 $table->add_index('rsstype', XMLDB_INDEX_NOTUNIQUE
, array('rsstype'));
65 $table->setComment("This is a test'n drop table. You can drop it safely");
67 $this->tables
[$table->getName()] = $table;
69 // Define 2 initial records for this table.
70 $this->records
[$table->getName()] = array(
75 'intro' => 'first record'),
80 'intro' => 'second record'));
82 // Second, smaller table.
83 $table = new xmldb_table ('test_table1');
84 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
85 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
86 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, null, null, 'Moodle');
87 $table->add_field('secondname', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, null);
88 $table->add_field('thirdname', XMLDB_TYPE_CHAR
, '30', null, null, null, ''); // Nullable column with empty default.
89 $table->add_field('intro', XMLDB_TYPE_TEXT
, 'medium', null, XMLDB_NOTNULL
, null, null);
90 $table->add_field('avatar', XMLDB_TYPE_BINARY
, 'medium', null, null, null, null);
91 $table->add_field('grade', XMLDB_TYPE_NUMBER
, '20,10', null, null, null);
92 $table->add_field('gradefloat', XMLDB_TYPE_FLOAT
, '20,0', null, null, null, null);
93 $table->add_field('percentfloat', XMLDB_TYPE_FLOAT
, '5,2', null, null, null, 99.9);
94 $table->add_field('userid', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
95 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
96 $table->add_key('course', XMLDB_KEY_FOREIGN_UNIQUE
, array('course'), 'test_table0', array('course'));
97 $table->setComment("This is a test'n drop table. You can drop it safely");
99 $this->tables
[$table->getName()] = $table;
101 // Define 2 initial records for this table.
102 $this->records
[$table->getName()] = array(
105 'secondname' => 'first record', // Less than 10 cc, please don't modify. Some tests below depend of this.
106 'intro' => 'first record'),
109 'secondname' => 'second record', // More than 10 cc, please don't modify. Some tests below depend of this.
110 'intro' => 'second record'));
113 private function create_deftable($tablename) {
114 $dbman = $this->tdb
->get_manager();
116 if (!isset($this->tables
[$tablename])) {
120 $table = $this->tables
[$tablename];
122 if ($dbman->table_exists($table)) {
123 $dbman->drop_table($table);
125 $dbman->create_table($table);
131 * Fill the given test table with some records, as far as
132 * DDL behaviour must be tested both with real data and
134 * @param string $tablename
135 * @return int count of records
137 private function fill_deftable($tablename) {
138 $DB = $this->tdb
; // Do not use global $DB!
139 $dbman = $this->tdb
->get_manager();
141 if (!isset($this->records
[$tablename])) {
145 if ($dbman->table_exists($tablename)) {
146 foreach ($this->records
[$tablename] as $row) {
147 $DB->insert_record($tablename, $row);
153 return count($this->records
[$tablename]);
157 * Test behaviour of table_exists()
159 public function test_table_exists() {
160 $DB = $this->tdb
; // Do not use global $DB!
161 $dbman = $this->tdb
->get_manager();
163 // First make sure it returns false if table does not exist.
164 $table = $this->tables
['test_table0'];
167 $result = $DB->get_records('test_table0');
168 } catch (dml_exception
$e) {
171 $this->resetDebugging();
173 $this->assertFalse($result);
175 $this->assertFalse($dbman->table_exists('test_table0')); // By name..
176 $this->assertFalse($dbman->table_exists($table)); // By xmldb_table..
178 // Create table and test again.
179 $dbman->create_table($table);
181 $this->assertSame(array(), $DB->get_records('test_table0'));
182 $this->assertTrue($dbman->table_exists('test_table0')); // By name.
183 $this->assertTrue($dbman->table_exists($table)); // By xmldb_table.
185 // Drop table and test again.
186 $dbman->drop_table($table);
189 $result = $DB->get_records('test_table0');
190 } catch (dml_exception
$e) {
193 $this->resetDebugging();
195 $this->assertFalse($result);
197 $this->assertFalse($dbman->table_exists('test_table0')); // By name.
198 $this->assertFalse($dbman->table_exists($table)); // By xmldb_table.
202 * Test behaviour of create_table()
204 public function test_create_table() {
206 $DB = $this->tdb
; // Do not use global $DB!
207 $dbman = $this->tdb
->get_manager();
210 $table = $this->tables
['test_table1'];
212 $dbman->create_table($table);
213 $this->assertTrue($dbman->table_exists($table));
215 // Basic get_tables() test.
216 $tables = $DB->get_tables();
217 $this->assertArrayHasKey('test_table1', $tables);
219 // Basic get_columns() tests.
220 $columns = $DB->get_columns('test_table1');
221 $this->assertSame('R', $columns['id']->meta_type
);
222 $this->assertSame('I', $columns['course']->meta_type
);
223 $this->assertSame('C', $columns['name']->meta_type
);
224 $this->assertSame('C', $columns['secondname']->meta_type
);
225 $this->assertSame('C', $columns['thirdname']->meta_type
);
226 $this->assertSame('X', $columns['intro']->meta_type
);
227 $this->assertSame('B', $columns['avatar']->meta_type
);
228 $this->assertSame('N', $columns['grade']->meta_type
);
229 $this->assertSame('N', $columns['percentfloat']->meta_type
);
230 $this->assertSame('I', $columns['userid']->meta_type
);
232 $this->assertTrue($columns['course']->has_default
);
233 $this->assertEquals(0, $columns['course']->default_value
);
234 $this->assertTrue($columns['name']->has_default
);
235 $this->assertSame('Moodle', $columns['name']->default_value
);
236 $this->assertTrue($columns['secondname']->has_default
);
237 $this->assertSame('', $columns['secondname']->default_value
);
238 $this->assertTrue($columns['thirdname']->has_default
);
239 $this->assertSame('', $columns['thirdname']->default_value
);
240 $this->assertTrue($columns['percentfloat']->has_default
);
241 $this->assertEquals(99.9, $columns['percentfloat']->default_value
);
242 $this->assertTrue($columns['userid']->has_default
);
243 $this->assertEquals(0, $columns['userid']->default_value
);
245 // Basic get_indexes() test.
246 $indexes = $DB->get_indexes('test_table1');
247 $courseindex = reset($indexes);
248 $this->assertEquals(1, $courseindex['unique']);
249 $this->assertSame('course', $courseindex['columns'][0]);
251 // Check sequence returns 1 for first insert.
252 $rec = (object)array(
254 'secondname' => 'not important',
255 'intro' => 'not important');
256 $this->assertSame(1, $DB->insert_record('test_table1', $rec));
258 // Check defined defaults are working ok.
259 $dbrec = $DB->get_record('test_table1', array('id' => 1));
260 $this->assertSame('Moodle', $dbrec->name
);
261 $this->assertSame('', $dbrec->thirdname
);
263 // Check exceptions if multiple R columns.
264 $table = new xmldb_table ('test_table2');
265 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
266 $table->add_field('rid', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
267 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
268 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
269 $table->add_key('primaryx', XMLDB_KEY_PRIMARY
, array('id'));
270 $table->setComment("This is a test'n drop table. You can drop it safely");
272 $this->tables
[$table->getName()] = $table;
275 $dbman->create_table($table);
276 $this->fail('Exception expected');
277 } catch (moodle_exception
$e) {
278 $this->assertInstanceOf('ddl_exception', $e);
281 // Check exceptions missing primary key on R column.
282 $table = new xmldb_table ('test_table2');
283 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
284 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
285 $table->setComment("This is a test'n drop table. You can drop it safely");
287 $this->tables
[$table->getName()] = $table;
290 $dbman->create_table($table);
291 $this->fail('Exception expected');
292 } catch (moodle_exception
$e) {
293 $this->assertInstanceOf('ddl_exception', $e);
296 // Long table name names - the largest allowed by the configuration which exclude the prefix to ensure it's created.
297 $tablechars = str_repeat('a', xmldb_table
::NAME_MAX_LENGTH
);
298 $table = new xmldb_table($tablechars);
299 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
300 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
301 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
302 $table->setComment("This is a test'n drop table. You can drop it safely");
304 $this->tables
[$table->getName()] = $table;
306 $dbman->create_table($table);
307 $this->assertTrue($dbman->table_exists($table));
308 $dbman->drop_table($table);
310 // Table name is too long, ignoring any prefix size set.
311 $tablechars = str_repeat('a', xmldb_table
::NAME_MAX_LENGTH +
1);
312 $table = new xmldb_table($tablechars);
313 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
314 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
315 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
316 $table->setComment("This is a test'n drop table. You can drop it safely");
318 $this->tables
[$table->getName()] = $table;
321 $dbman->create_table($table);
322 $this->fail('Exception expected');
323 } catch (moodle_exception
$e) {
324 $this->assertInstanceOf('coding_exception', $e);
327 // Invalid table name.
328 $table = new xmldb_table('test_tableCD');
329 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
330 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
331 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
332 $table->setComment("This is a test'n drop table. You can drop it safely");
334 $this->tables
[$table->getName()] = $table;
337 $dbman->create_table($table);
338 $this->fail('Exception expected');
339 } catch (moodle_exception
$e) {
340 $this->assertInstanceOf('coding_exception', $e);
343 // Weird column names - the largest allowed.
344 $table = new xmldb_table('test_table3');
345 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
346 $table->add_field(str_repeat('b', xmldb_field
::NAME_MAX_LENGTH
), XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
347 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
348 $table->setComment("This is a test'n drop table. You can drop it safely");
350 $this->tables
[$table->getName()] = $table;
352 $dbman->create_table($table);
353 $this->assertTrue($dbman->table_exists($table));
354 $dbman->drop_table($table);
356 // Too long field name.
357 $table = new xmldb_table('test_table4');
358 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
359 $table->add_field(str_repeat('a', xmldb_field
::NAME_MAX_LENGTH +
1), XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
360 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
361 $table->setComment("This is a test'n drop table. You can drop it safely");
363 $this->tables
[$table->getName()] = $table;
366 $dbman->create_table($table);
367 $this->fail('Exception expected');
368 } catch (moodle_exception
$e) {
369 $this->assertInstanceOf('coding_exception', $e);
372 // Invalid field name.
373 $table = new xmldb_table('test_table4');
374 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
375 $table->add_field('abCD', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
376 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
377 $table->setComment("This is a test'n drop table. You can drop it safely");
379 $this->tables
[$table->getName()] = $table;
382 $dbman->create_table($table);
383 $this->fail('Exception expected');
384 } catch (moodle_exception
$e) {
385 $this->assertInstanceOf('coding_exception', $e);
388 // Invalid integer length.
389 $table = new xmldb_table('test_table4');
390 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
391 $table->add_field('course', XMLDB_TYPE_INTEGER
, '21', null, XMLDB_NOTNULL
, null, '2');
392 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
393 $table->setComment("This is a test'n drop table. You can drop it safely");
395 $this->tables
[$table->getName()] = $table;
398 $dbman->create_table($table);
399 $this->fail('Exception expected');
400 } catch (moodle_exception
$e) {
401 $this->assertInstanceOf('coding_exception', $e);
404 // Invalid integer default.
405 $table = new xmldb_table('test_table4');
406 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
407 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, 'x');
408 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
409 $table->setComment("This is a test'n drop table. You can drop it safely");
411 $this->tables
[$table->getName()] = $table;
414 $dbman->create_table($table);
415 $this->fail('Exception expected');
416 } catch (moodle_exception
$e) {
417 $this->assertInstanceOf('coding_exception', $e);
420 // Invalid decimal length - max precision is 38 digits.
421 $table = new xmldb_table('test_table4');
422 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
423 $table->add_field('num', XMLDB_TYPE_NUMBER
, '39,19', null, XMLDB_NOTNULL
, null, null);
424 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
425 $table->setComment("This is a test'n drop table. You can drop it safely");
427 $this->tables
[$table->getName()] = $table;
430 $dbman->create_table($table);
431 $this->fail('Exception expected');
432 } catch (moodle_exception
$e) {
433 $this->assertInstanceOf('coding_exception', $e);
436 // Invalid decimal decimals - number of decimals can't be higher than total number of digits.
437 $table = new xmldb_table('test_table4');
438 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
439 $table->add_field('num', XMLDB_TYPE_NUMBER
, '10,11', null, XMLDB_NOTNULL
, null, null);
440 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
441 $table->setComment("This is a test'n drop table. You can drop it safely");
443 $this->tables
[$table->getName()] = $table;
446 $dbman->create_table($table);
447 $this->fail('Exception expected');
448 } catch (moodle_exception
$e) {
449 $this->assertInstanceOf('coding_exception', $e);
452 // Invalid decimal whole number - the whole number part can't have more digits than integer fields.
453 $table = new xmldb_table('test_table4');
454 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
455 $table->add_field('num', XMLDB_TYPE_NUMBER
, '38,17', null, XMLDB_NOTNULL
, null, null);
456 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
457 $table->setComment("This is a test'n drop table. You can drop it safely");
459 $this->tables
[$table->getName()] = $table;
462 $dbman->create_table($table);
463 $this->fail('Exception expected');
464 } catch (moodle_exception
$e) {
465 $this->assertInstanceOf('coding_exception', $e);
468 // Invalid decimal decimals - negative scale not supported.
469 $table = new xmldb_table('test_table4');
470 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
471 $table->add_field('num', XMLDB_TYPE_NUMBER
, '30,-5', null, XMLDB_NOTNULL
, null, null);
472 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
473 $table->setComment("This is a test'n drop table. You can drop it safely");
475 $this->tables
[$table->getName()] = $table;
478 $dbman->create_table($table);
479 $this->fail('Exception expected');
480 } catch (moodle_exception
$e) {
481 $this->assertInstanceOf('coding_exception', $e);
484 // Invalid decimal default.
485 $table = new xmldb_table('test_table4');
486 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
487 $table->add_field('num', XMLDB_TYPE_NUMBER
, '10,5', null, XMLDB_NOTNULL
, null, 'x');
488 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
489 $table->setComment("This is a test'n drop table. You can drop it safely");
491 $this->tables
[$table->getName()] = $table;
494 $dbman->create_table($table);
495 $this->fail('Exception expected');
496 } catch (moodle_exception
$e) {
497 $this->assertInstanceOf('coding_exception', $e);
500 // Invalid float length.
501 $table = new xmldb_table('test_table4');
502 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
503 $table->add_field('num', XMLDB_TYPE_FLOAT
, '21,10', null, XMLDB_NOTNULL
, null, null);
504 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
505 $table->setComment("This is a test'n drop table. You can drop it safely");
507 $this->tables
[$table->getName()] = $table;
510 $dbman->create_table($table);
511 $this->fail('Exception expected');
512 } catch (moodle_exception
$e) {
513 $this->assertInstanceOf('coding_exception', $e);
516 // Invalid float decimals.
517 $table = new xmldb_table('test_table4');
518 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
519 $table->add_field('num', XMLDB_TYPE_FLOAT
, '10,11', null, XMLDB_NOTNULL
, null, null);
520 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
521 $table->setComment("This is a test'n drop table. You can drop it safely");
523 $this->tables
[$table->getName()] = $table;
526 $dbman->create_table($table);
527 $this->fail('Exception expected');
528 } catch (moodle_exception
$e) {
529 $this->assertInstanceOf('coding_exception', $e);
532 // Invalid float default.
533 $table = new xmldb_table('test_table4');
534 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
535 $table->add_field('num', XMLDB_TYPE_FLOAT
, '10,5', null, XMLDB_NOTNULL
, null, 'x');
536 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
537 $table->setComment("This is a test'n drop table. You can drop it safely");
539 $this->tables
[$table->getName()] = $table;
542 $dbman->create_table($table);
543 $this->fail('Exception expected');
544 } catch (moodle_exception
$e) {
545 $this->assertInstanceOf('coding_exception', $e);
550 * Test if database supports tables with many TEXT fields,
551 * InnoDB is known to failed during data insertion instead
552 * of table creation when text fields contain actual data.
554 public function test_row_size_limits() {
556 $DB = $this->tdb
; // Do not use global $DB!
557 $dbman = $this->tdb
->get_manager();
559 $text = str_repeat('Å¡', 1333);
561 $data = new stdClass();
562 $data->name
= 'test';
563 $table = new xmldb_table('test_innodb');
564 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
565 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, null, null, null);
566 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
567 for ($i = 0; $i < 20; $i++
) {
568 $table->add_field('text'.$i, XMLDB_TYPE_TEXT
, null, null, null, null, null);
569 $data->{'text'.$i} = $text;
571 $dbman->create_table($table);
574 $id = $DB->insert_record('test_innodb', $data);
575 $expected = (array)$data;
576 $expected['id'] = (string)$id;
577 $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
578 } catch (dml_exception
$e) {
579 // Give some nice error message when known problematic MySQL with InnoDB detected.
580 if ($DB->get_dbfamily() === 'mysql') {
581 $engine = strtolower($DB->get_dbengine());
582 if ($engine === 'innodb' or $engine === 'xtradb') {
583 if (!$DB->is_compressed_row_format_supported()) {
584 $this->fail("Row size limit reached in MySQL using InnoDB, configure server to use innodb_file_format=Barracuda and innodb_file_per_table=1");
591 $dbman->drop_table($table);
593 $data = new stdClass();
594 $data->name
= 'test';
595 $table = new xmldb_table('test_innodb');
596 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
597 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, null, null, null);
598 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
599 $dbman->create_table($table);
600 $DB->insert_record('test_innodb', array('name' => 'test'));
602 for ($i = 0; $i < 20; $i++
) {
603 $field = new xmldb_field('text'.$i, XMLDB_TYPE_TEXT
, null, null, null, null, null);
604 $dbman->add_field($table, $field);
605 $data->{'text'.$i} = $text;
607 $id = $DB->insert_record('test_innodb', $data);
608 $expected = (array)$data;
609 $expected['id'] = (string)$id;
610 $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
613 $dbman->drop_table($table);
615 // MySQL VARCHAR fields may hit a different 65535 row size limit when creating tables.
616 $data = new stdClass();
617 $data->name
= 'test';
618 $table = new xmldb_table('test_innodb');
619 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
620 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, null, null, null);
621 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
622 for ($i = 0; $i < 12; $i++
) {
623 $table->add_field('text'.$i, XMLDB_TYPE_CHAR
, '1333', null, null, null, null);
624 $data->{'text'.$i} = $text;
626 $dbman->create_table($table);
628 $id = $DB->insert_record('test_innodb', $data);
629 $expected = (array)$data;
630 $expected['id'] = (string)$id;
631 $this->assertEqualsCanonicalizing($expected, (array)$DB->get_record('test_innodb', array('id' => $id)));
633 $dbman->drop_table($table);
637 * Test behaviour of drop_table()
639 public function test_drop_table() {
640 $DB = $this->tdb
; // Do not use global $DB!
641 $dbman = $this->tdb
->get_manager();
643 // Initially table doesn't exist.
644 $this->assertFalse($dbman->table_exists('test_table0'));
646 // Create table with contents.
647 $table = $this->create_deftable('test_table0');
648 $this->assertTrue($dbman->table_exists('test_table0'));
650 // Fill the table with some records before dropping it.
651 $this->fill_deftable('test_table0');
653 // Drop by xmldb_table object.
654 $dbman->drop_table($table);
655 $this->assertFalse($dbman->table_exists('test_table0'));
657 // Basic get_tables() test.
658 $tables = $DB->get_tables();
659 $this->assertArrayNotHasKey('test_table0', $tables);
661 // Columns cache must be empty.
662 $columns = $DB->get_columns('test_table0');
663 $this->assertEmpty($columns);
665 $indexes = $DB->get_indexes('test_table0');
666 $this->assertEmpty($indexes);
670 * Test behaviour of rename_table()
672 public function test_rename_table() {
673 $DB = $this->tdb
; // Do not use global $DB!
674 $dbman = $this->tdb
->get_manager();
676 $table = $this->create_deftable('test_table1');
678 // Fill the table with some records before renaming it.
679 $insertedrows = $this->fill_deftable('test_table1');
681 $this->assertFalse($dbman->table_exists('test_table_cust1'));
682 $dbman->rename_table($table, 'test_table_cust1');
683 $this->assertTrue($dbman->table_exists('test_table_cust1'));
685 // Check sequence returns $insertedrows + 1 for this insert (after rename).
686 $rec = (object)array(
688 'secondname' => 'not important',
689 'intro' => 'not important');
690 $this->assertSame($insertedrows+
1, $DB->insert_record('test_table_cust1', $rec));
692 // Verify behavior when target table already exists.
693 $sourcetable = $this->create_deftable('test_table0');
694 $targettable = $this->create_deftable('test_table1');
696 $dbman->rename_table($sourcetable, $targettable->getName());
697 $this->fail('Exception expected');
698 } catch (moodle_exception
$e) {
699 $this->assertInstanceOf('ddl_exception', $e);
700 $this->assertEquals('Table "test_table1" already exists (can not rename table)', $e->getMessage());
705 * Test behaviour of field_exists()
707 public function test_field_exists() {
708 $dbman = $this->tdb
->get_manager();
710 $table = $this->create_deftable('test_table0');
713 // Give a nonexistent table as first param (throw exception).
715 $dbman->field_exists('nonexistenttable', 'id');
716 $this->fail('Exception expected');
717 } catch (moodle_exception
$e) {
718 $this->assertInstanceOf('moodle_exception', $e);
721 // Give a nonexistent field as second param (return false).
722 $this->assertFalse($dbman->field_exists('test_table0', 'nonexistentfield'));
724 // Correct string params.
725 $this->assertTrue($dbman->field_exists('test_table0', 'id'));
728 $realfield = $table->getField('id');
730 // Give a nonexistent table as first param (throw exception).
731 $nonexistenttable = new xmldb_table('nonexistenttable');
733 $dbman->field_exists($nonexistenttable, $realfield);
734 $this->fail('Exception expected');
735 } catch (moodle_exception
$e) {
736 $this->assertInstanceOf('moodle_exception', $e);
739 // Give a nonexistent field as second param (return false).
740 $nonexistentfield = new xmldb_field('nonexistentfield');
741 $this->assertFalse($dbman->field_exists($table, $nonexistentfield));
743 // Correct object params.
744 $this->assertTrue($dbman->field_exists($table, $realfield));
746 // Mix string and object params.
748 $this->assertTrue($dbman->field_exists($table, 'id'));
749 $this->assertTrue($dbman->field_exists('test_table0', $realfield));
750 // Non existing tables (throw exception).
752 $this->assertFalse($dbman->field_exists($nonexistenttable, 'id'));
753 $this->fail('Exception expected');
754 } catch (moodle_exception
$e) {
755 $this->assertInstanceOf('moodle_exception', $e);
758 $this->assertFalse($dbman->field_exists('nonexistenttable', $realfield));
759 $this->fail('Exception expected');
760 } catch (moodle_exception
$e) {
761 $this->assertInstanceOf('moodle_exception', $e);
763 // Non existing fields (return false).
764 $this->assertFalse($dbman->field_exists($table, 'nonexistentfield'));
765 $this->assertFalse($dbman->field_exists('test_table0', $nonexistentfield));
769 * Test behaviour of add_field()
771 public function test_add_field() {
772 $DB = $this->tdb
; // Do not use global $DB!
773 $dbman = $this->tdb
->get_manager();
775 $table = $this->create_deftable('test_table1');
777 // Fill the table with some records before adding fields.
778 $this->fill_deftable('test_table1');
780 // Add one not null field without specifying default value (throws ddl_exception).
781 $field = new xmldb_field('onefield');
782 $field->set_attributes(XMLDB_TYPE_INTEGER
, '6', null, XMLDB_NOTNULL
, null, null);
784 $dbman->add_field($table, $field);
785 $this->fail('Exception expected');
786 } catch (moodle_exception
$e) {
787 $this->assertInstanceOf('ddl_exception', $e);
790 // Add one existing field (throws ddl_exception).
791 $field = new xmldb_field('course');
792 $field->set_attributes(XMLDB_TYPE_INTEGER
, '6', null, XMLDB_NOTNULL
, null, 2);
794 $dbman->add_field($table, $field);
795 $this->fail('Exception expected');
796 } catch (moodle_exception
$e) {
797 $this->assertInstanceOf('ddl_exception', $e);
800 // TODO: add one field with invalid type, must throw exception.
801 // TODO: add one text field with default, must throw exception.
802 // TODO: add one binary field with default, must throw exception.
804 // Add one integer field and check it.
805 $field = new xmldb_field('oneinteger');
806 $field->set_attributes(XMLDB_TYPE_INTEGER
, '6', null, XMLDB_NOTNULL
, null, 2);
807 $dbman->add_field($table, $field);
808 $this->assertTrue($dbman->field_exists($table, 'oneinteger'));
809 $columns = $DB->get_columns('test_table1');
810 $this->assertEquals('oneinteger', $columns['oneinteger']->name
);
811 $this->assertTrue($columns['oneinteger']->not_null
);
812 // Max_length and scale cannot be checked under all DBs at all for integer fields.
813 $this->assertFalse($columns['oneinteger']->primary_key
);
814 $this->assertFalse($columns['oneinteger']->binary
);
815 $this->assertTrue($columns['oneinteger']->has_default
);
816 $this->assertEquals(2, $columns['oneinteger']->default_value
);
817 $this->assertSame('I', $columns['oneinteger']->meta_type
);
818 $this->assertEquals(2, $DB->get_field('test_table1', 'oneinteger', array(), IGNORE_MULTIPLE
)); // Check default has been applied.
820 // Add one numeric field and check it.
821 $field = new xmldb_field('onenumber');
822 $field->set_attributes(XMLDB_TYPE_NUMBER
, '6,3', null, XMLDB_NOTNULL
, null, 2.55);
823 $dbman->add_field($table, $field);
824 $this->assertTrue($dbman->field_exists($table, 'onenumber'));
825 $columns = $DB->get_columns('test_table1');
826 $this->assertSame('onenumber', $columns['onenumber']->name
);
827 $this->assertEquals(6, $columns['onenumber']->max_length
);
828 $this->assertEquals(3, $columns['onenumber']->scale
);
829 $this->assertTrue($columns['onenumber']->not_null
);
830 $this->assertFalse($columns['onenumber']->primary_key
);
831 $this->assertFalse($columns['onenumber']->binary
);
832 $this->assertTrue($columns['onenumber']->has_default
);
833 $this->assertEquals(2.550, $columns['onenumber']->default_value
);
834 $this->assertSame('N', $columns['onenumber']->meta_type
);
835 $this->assertEquals(2.550, $DB->get_field('test_table1', 'onenumber', array(), IGNORE_MULTIPLE
)); // Check default has been applied.
837 // Add one numeric field with scale of 0 and check it.
838 $field = new xmldb_field('onenumberwith0scale');
839 $field->set_attributes(XMLDB_TYPE_NUMBER
, '6,0', null, XMLDB_NOTNULL
, null, 2);
840 $dbman->add_field($table, $field);
841 $this->assertTrue($dbman->field_exists($table, 'onenumberwith0scale'));
842 $columns = $DB->get_columns('test_table1');
843 $this->assertEquals(6, $columns['onenumberwith0scale']->max_length
);
844 // We can not use assertEquals as that accepts null/false as a valid value.
845 $this->assertSame('0', strval($columns['onenumberwith0scale']->scale
));
847 // Add one float field and check it (not official type - must work as number).
848 $field = new xmldb_field('onefloat');
849 $field->set_attributes(XMLDB_TYPE_FLOAT
, '6,3', null, XMLDB_NOTNULL
, null, 3.550);
850 $dbman->add_field($table, $field);
851 $this->assertTrue($dbman->field_exists($table, 'onefloat'));
852 $columns = $DB->get_columns('test_table1');
853 $this->assertSame('onefloat', $columns['onefloat']->name
);
854 $this->assertTrue($columns['onefloat']->not_null
);
855 // Max_length and scale cannot be checked under all DBs at all for float fields.
856 $this->assertFalse($columns['onefloat']->primary_key
);
857 $this->assertFalse($columns['onefloat']->binary
);
858 $this->assertTrue($columns['onefloat']->has_default
);
859 $this->assertEquals(3.550, $columns['onefloat']->default_value
);
860 $this->assertSame('N', $columns['onefloat']->meta_type
);
861 // Just rounding DB information to 7 decimal digits. Fair enough to test 3.550 and avoids one nasty bug
862 // in MSSQL core returning wrong floats (http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5e08de63-16bb-4f24-b645-0cf8fc669de3)
863 // In any case, floats aren't officially supported by Moodle, with number/decimal type being the correct ones, so
864 // this isn't a real problem at all.
865 $this->assertEquals(3.550, round($DB->get_field('test_table1', 'onefloat', array(), IGNORE_MULTIPLE
), 7)); // Check default has been applied.
867 // Add one char field and check it.
868 $field = new xmldb_field('onechar');
869 $field->set_attributes(XMLDB_TYPE_CHAR
, '25', null, XMLDB_NOTNULL
, null, 'Nice dflt!');
870 $dbman->add_field($table, $field);
871 $this->assertTrue($dbman->field_exists($table, 'onechar'));
872 $columns = $DB->get_columns('test_table1');
873 $this->assertSame('onechar', $columns['onechar']->name
);
874 $this->assertEquals(25, $columns['onechar']->max_length
);
875 $this->assertNull($columns['onechar']->scale
);
876 $this->assertTrue($columns['onechar']->not_null
);
877 $this->assertFalse($columns['onechar']->primary_key
);
878 $this->assertFalse($columns['onechar']->binary
);
879 $this->assertTrue($columns['onechar']->has_default
);
880 $this->assertSame('Nice dflt!', $columns['onechar']->default_value
);
881 $this->assertSame('C', $columns['onechar']->meta_type
);
882 $this->assertEquals('Nice dflt!', $DB->get_field('test_table1', 'onechar', array(), IGNORE_MULTIPLE
)); // Check default has been applied.
884 // Add one big text field and check it.
885 $field = new xmldb_field('onetext');
886 $field->set_attributes(XMLDB_TYPE_TEXT
, 'big');
887 $dbman->add_field($table, $field);
888 $this->assertTrue($dbman->field_exists($table, 'onetext'));
889 $columns = $DB->get_columns('test_table1');
890 $this->assertSame('onetext', $columns['onetext']->name
);
891 $this->assertEquals(-1, $columns['onetext']->max_length
); // -1 means unknown or big.
892 $this->assertNull($columns['onetext']->scale
);
893 $this->assertFalse($columns['onetext']->not_null
);
894 $this->assertFalse($columns['onetext']->primary_key
);
895 $this->assertFalse($columns['onetext']->binary
);
896 $this->assertFalse($columns['onetext']->has_default
);
897 $this->assertNull($columns['onetext']->default_value
);
898 $this->assertSame('X', $columns['onetext']->meta_type
);
900 // Add one medium text field and check it.
901 $field = new xmldb_field('mediumtext');
902 $field->set_attributes(XMLDB_TYPE_TEXT
, 'medium');
903 $dbman->add_field($table, $field);
904 $columns = $DB->get_columns('test_table1');
905 $this->assertTrue(($columns['mediumtext']->max_length
== -1) or ($columns['mediumtext']->max_length
>= 16777215)); // -1 means unknown or big.
907 // Add one small text field and check it.
908 $field = new xmldb_field('smalltext');
909 $field->set_attributes(XMLDB_TYPE_TEXT
, 'small');
910 $dbman->add_field($table, $field);
911 $columns = $DB->get_columns('test_table1');
912 $this->assertTrue(($columns['smalltext']->max_length
== -1) or ($columns['smalltext']->max_length
>= 65535)); // -1 means unknown or big.
914 // Add one binary field and check it.
915 $field = new xmldb_field('onebinary');
916 $field->set_attributes(XMLDB_TYPE_BINARY
);
917 $dbman->add_field($table, $field);
918 $this->assertTrue($dbman->field_exists($table, 'onebinary'));
919 $columns = $DB->get_columns('test_table1');
920 $this->assertSame('onebinary', $columns['onebinary']->name
);
921 $this->assertEquals(-1, $columns['onebinary']->max_length
);
922 $this->assertNull($columns['onebinary']->scale
);
923 $this->assertFalse($columns['onebinary']->not_null
);
924 $this->assertFalse($columns['onebinary']->primary_key
);
925 $this->assertTrue($columns['onebinary']->binary
);
926 $this->assertFalse($columns['onebinary']->has_default
);
927 $this->assertNull($columns['onebinary']->default_value
);
928 $this->assertSame('B', $columns['onebinary']->meta_type
);
930 // TODO: check datetime type. Although unused should be fully supported.
934 * Test behaviour of drop_field()
936 public function test_drop_field() {
937 $DB = $this->tdb
; // Do not use global $DB!
938 $dbman = $this->tdb
->get_manager();
940 $table = $this->create_deftable('test_table0');
942 // Fill the table with some records before dropping fields.
943 $this->fill_deftable('test_table0');
945 // Drop field with simple xmldb_field having indexes, must return exception.
946 $field = new xmldb_field('type'); // Field has indexes and default clause.
947 $this->assertTrue($dbman->field_exists($table, 'type'));
949 $dbman->drop_field($table, $field);
950 $this->fail('Exception expected');
951 } catch (moodle_exception
$e) {
952 $this->assertInstanceOf('ddl_dependency_exception', $e);
954 $this->assertTrue($dbman->field_exists($table, 'type')); // Continues existing, drop aborted.
956 // Drop field with complete xmldb_field object and related indexes, must return exception.
957 $field = $table->getField('course'); // Field has indexes and default clause.
958 $this->assertTrue($dbman->field_exists($table, $field));
960 $dbman->drop_field($table, $field);
961 $this->fail('Exception expected');
962 } catch (moodle_exception
$e) {
963 $this->assertInstanceOf('ddl_dependency_exception', $e);
965 $this->assertTrue($dbman->field_exists($table, $field)); // Continues existing, drop aborted.
967 // Drop one non-existing field, must return exception.
968 $field = new xmldb_field('nonexistingfield');
969 $this->assertFalse($dbman->field_exists($table, $field));
971 $dbman->drop_field($table, $field);
972 $this->fail('Exception expected');
973 } catch (moodle_exception
$e) {
974 $this->assertInstanceOf('ddl_field_missing_exception', $e);
977 // Drop field with simple xmldb_field, not having related indexes.
978 $field = new xmldb_field('forcesubscribe'); // Field has default clause.
979 $this->assertTrue($dbman->field_exists($table, 'forcesubscribe'));
980 $dbman->drop_field($table, $field);
981 $this->assertFalse($dbman->field_exists($table, 'forcesubscribe'));
983 // Drop field with complete xmldb_field object, not having related indexes.
984 $field = new xmldb_field('trackingtype'); // Field has default clause.
985 $this->assertTrue($dbman->field_exists($table, $field));
986 $dbman->drop_field($table, $field);
987 $this->assertFalse($dbman->field_exists($table, $field));
991 * Test behaviour of change_field_type()
993 public function test_change_field_type() {
994 $DB = $this->tdb
; // Do not use global $DB!
995 $dbman = $this->tdb
->get_manager();
997 // Create table with indexed field and not indexed field to
998 // perform tests in both fields, both having defaults.
999 $table = new xmldb_table('test_table_cust0');
1000 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1001 $table->add_field('onenumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '2');
1002 $table->add_field('anothernumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '4');
1003 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1004 $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE
, array('onenumber'));
1005 $dbman->create_table($table);
1007 $record = new stdClass();
1008 $record->onenumber
= 2;
1009 $record->anothernumber
= 4;
1010 $recoriginal = $DB->insert_record('test_table_cust0', $record);
1012 // Change column from integer to varchar. Must return exception because of dependent index.
1013 $field = new xmldb_field('onenumber');
1014 $field->set_attributes(XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'test');
1016 $dbman->change_field_type($table, $field);
1017 $this->fail('Exception expected');
1018 } catch (moodle_exception
$e) {
1019 $this->assertInstanceOf('ddl_dependency_exception', $e);
1021 // Column continues being integer 10 not null default 2.
1022 $columns = $DB->get_columns('test_table_cust0');
1023 $this->assertSame('I', $columns['onenumber']->meta_type
);
1024 // TODO: check the rest of attributes.
1026 // Change column from integer to varchar. Must work because column has no dependencies.
1027 $field = new xmldb_field('anothernumber');
1028 $field->set_attributes(XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'test');
1029 $dbman->change_field_type($table, $field);
1030 // Column is char 30 not null default 'test' now.
1031 $columns = $DB->get_columns('test_table_cust0');
1032 $this->assertSame('C', $columns['anothernumber']->meta_type
);
1033 // TODO: check the rest of attributes.
1035 // Change column back from char to integer.
1036 $field = new xmldb_field('anothernumber');
1037 $field->set_attributes(XMLDB_TYPE_INTEGER
, '8', null, XMLDB_NOTNULL
, null, '5');
1038 $dbman->change_field_type($table, $field);
1039 // Column is integer 8 not null default 5 now.
1040 $columns = $DB->get_columns('test_table_cust0');
1041 $this->assertSame('I', $columns['anothernumber']->meta_type
);
1042 // TODO: check the rest of attributes.
1044 // Change column once more from integer to char.
1045 $field = new xmldb_field('anothernumber');
1046 $field->set_attributes(XMLDB_TYPE_CHAR
, '20', null, XMLDB_NOTNULL
, null, "test'n drop");
1047 $dbman->change_field_type($table, $field);
1048 // Column is char 30 not null default "test'n drop" now.
1049 $columns = $DB->get_columns('test_table_cust0');
1050 $this->assertSame('C', $columns['anothernumber']->meta_type
);
1051 // TODO: check the rest of attributes.
1053 // Insert one string value and try to convert to integer. Must throw exception.
1054 $record = new stdClass();
1055 $record->onenumber
= 7;
1056 $record->anothernumber
= 'string value';
1057 $rectodrop = $DB->insert_record('test_table_cust0', $record);
1058 $field = new xmldb_field('anothernumber');
1059 $field->set_attributes(XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '5');
1061 $dbman->change_field_type($table, $field);
1062 $this->fail('Exception expected');
1063 } catch (moodle_exception
$e) {
1064 $this->assertInstanceOf('ddl_change_structure_exception', $e);
1066 // Column continues being char 30 not null default "test'n drop" now.
1067 $this->assertSame('C', $columns['anothernumber']->meta_type
);
1068 // TODO: check the rest of attributes.
1069 $DB->delete_records('test_table_cust0', array('id' => $rectodrop)); // Delete the string record.
1071 // Change the column from varchar to float.
1072 $field = new xmldb_field('anothernumber');
1073 $field->set_attributes(XMLDB_TYPE_FLOAT
, '20,10', null, null, null, null);
1074 $dbman->change_field_type($table, $field);
1075 // Column is float 20,10 null default null.
1076 $columns = $DB->get_columns('test_table_cust0');
1077 $this->assertSame('N', $columns['anothernumber']->meta_type
); // Floats are seen as number.
1078 // TODO: check the rest of attributes.
1080 // Change the column back from float to varchar.
1081 $field = new xmldb_field('anothernumber');
1082 $field->set_attributes(XMLDB_TYPE_CHAR
, '20', null, XMLDB_NOTNULL
, null, 'test');
1083 $dbman->change_field_type($table, $field);
1084 // Column is char 20 not null default "test" now.
1085 $columns = $DB->get_columns('test_table_cust0');
1086 $this->assertSame('C', $columns['anothernumber']->meta_type
);
1087 // TODO: check the rest of attributes.
1089 // Change the column from varchar to number.
1090 $field = new xmldb_field('anothernumber');
1091 $field->set_attributes(XMLDB_TYPE_NUMBER
, '20,10', null, null, null, null);
1092 $dbman->change_field_type($table, $field);
1093 // Column is number 20,10 null default null now.
1094 $columns = $DB->get_columns('test_table_cust0');
1095 $this->assertSame('N', $columns['anothernumber']->meta_type
);
1096 // TODO: check the rest of attributes.
1098 // Change the column from number to integer.
1099 $field = new xmldb_field('anothernumber');
1100 $field->set_attributes(XMLDB_TYPE_INTEGER
, '2', null, null, null, null);
1101 $dbman->change_field_type($table, $field);
1102 // Column is integer 2 null default null now.
1103 $columns = $DB->get_columns('test_table_cust0');
1104 $this->assertSame('I', $columns['anothernumber']->meta_type
);
1105 // TODO: check the rest of attributes.
1107 // Change the column from integer to text.
1108 $field = new xmldb_field('anothernumber');
1109 $field->set_attributes(XMLDB_TYPE_TEXT
, 'big', null, XMLDB_NOTNULL
, null, null);
1110 $dbman->change_field_type($table, $field);
1111 // Column is char text not null default null.
1112 $columns = $DB->get_columns('test_table_cust0');
1113 $this->assertSame('X', $columns['anothernumber']->meta_type
);
1115 // Change the column back from text to number.
1116 $field = new xmldb_field('anothernumber');
1117 $field->set_attributes(XMLDB_TYPE_NUMBER
, '20,10', null, null, null, null);
1118 $dbman->change_field_type($table, $field);
1119 // Column is number 20,10 null default null now.
1120 $columns = $DB->get_columns('test_table_cust0');
1121 $this->assertSame('N', $columns['anothernumber']->meta_type
);
1122 // TODO: check the rest of attributes.
1124 // Change the column from number to text.
1125 $field = new xmldb_field('anothernumber');
1126 $field->set_attributes(XMLDB_TYPE_TEXT
, 'big', null, XMLDB_NOTNULL
, null, null);
1127 $dbman->change_field_type($table, $field);
1128 // Column is char text not null default "test" now.
1129 $columns = $DB->get_columns('test_table_cust0');
1130 $this->assertSame('X', $columns['anothernumber']->meta_type
);
1131 // TODO: check the rest of attributes.
1133 // Change the column back from text to integer.
1134 $field = new xmldb_field('anothernumber');
1135 $field->set_attributes(XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, 10);
1136 $dbman->change_field_type($table, $field);
1137 // Column is integer 10 not null default 10.
1138 $columns = $DB->get_columns('test_table_cust0');
1139 $this->assertSame('I', $columns['anothernumber']->meta_type
);
1140 // TODO: check the rest of attributes.
1142 // Check original value has survived to all the type changes.
1143 $this->assertnotEmpty($rec = $DB->get_record('test_table_cust0', array('id' => $recoriginal)));
1144 $this->assertEquals(4, $rec->anothernumber
);
1146 $dbman->drop_table($table);
1147 $this->assertFalse($dbman->table_exists($table));
1151 * Test behaviour of test_change_field_precision()
1153 public function test_change_field_precision() {
1154 $DB = $this->tdb
; // Do not use global $DB!
1155 $dbman = $this->tdb
->get_manager();
1157 $table = $this->create_deftable('test_table1');
1159 // Fill the table with some records before dropping fields.
1160 $this->fill_deftable('test_table1');
1162 // Change text field from medium to big.
1163 $field = new xmldb_field('intro');
1164 $field->set_attributes(XMLDB_TYPE_TEXT
, 'big', null, XMLDB_NOTNULL
, null, null);
1165 $dbman->change_field_precision($table, $field);
1166 $columns = $DB->get_columns('test_table1');
1167 // Cannot check the text type, only the metatype.
1168 $this->assertSame('X', $columns['intro']->meta_type
);
1169 // TODO: check the rest of attributes.
1171 // Change char field from 30 to 20.
1172 $field = new xmldb_field('secondname');
1173 $field->set_attributes(XMLDB_TYPE_CHAR
, '20', null, XMLDB_NOTNULL
, null, null);
1174 $dbman->change_field_precision($table, $field);
1175 $columns = $DB->get_columns('test_table1');
1176 $this->assertSame('C', $columns['secondname']->meta_type
);
1177 // TODO: check the rest of attributes.
1179 // Change char field from 20 to 10, having contents > 10cc. Throw exception.
1180 $field = new xmldb_field('secondname');
1181 $field->set_attributes(XMLDB_TYPE_CHAR
, '10', null, XMLDB_NOTNULL
, null, null);
1183 $dbman->change_field_precision($table, $field);
1184 $this->fail('Exception expected');
1185 } catch (moodle_exception
$e) {
1186 $this->assertInstanceOf('ddl_change_structure_exception', $e);
1188 // No changes in field specs at all.
1189 $columns = $DB->get_columns('test_table1');
1190 $this->assertSame('C', $columns['secondname']->meta_type
);
1191 // TODO: check the rest of attributes.
1193 // Change number field from 20,10 to 10,2.
1194 $field = new xmldb_field('grade');
1195 $field->set_attributes(XMLDB_TYPE_NUMBER
, '10,2', null, null, null, null);
1196 $dbman->change_field_precision($table, $field);
1197 $columns = $DB->get_columns('test_table1');
1198 $this->assertSame('N', $columns['grade']->meta_type
);
1199 // TODO: check the rest of attributes.
1201 // Change integer field from 10 to 2.
1202 $field = new xmldb_field('userid');
1203 $field->set_attributes(XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, null, '0');
1204 $dbman->change_field_precision($table, $field);
1205 $columns = $DB->get_columns('test_table1');
1206 $this->assertSame('I', $columns['userid']->meta_type
);
1207 // TODO: check the rest of attributes.
1209 // Change the column from integer (2) to integer (6) (forces change of type in some DBs).
1210 $field = new xmldb_field('userid');
1211 $field->set_attributes(XMLDB_TYPE_INTEGER
, '6', null, null, null, null);
1212 $dbman->change_field_precision($table, $field);
1213 // Column is integer 6 null default null now.
1214 $columns = $DB->get_columns('test_table1');
1215 $this->assertSame('I', $columns['userid']->meta_type
);
1216 // TODO: check the rest of attributes.
1218 // Insert one record with 6-digit field.
1219 $record = new stdClass();
1220 $record->course
= 10;
1221 $record->secondname
= 'third record';
1222 $record->intro
= 'third record';
1223 $record->userid
= 123456;
1224 $DB->insert_record('test_table1', $record);
1225 // Change integer field from 6 to 2, contents are bigger, must throw exception.
1226 $field = new xmldb_field('userid');
1227 $field->set_attributes(XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, null, '0');
1229 $dbman->change_field_precision($table, $field);
1230 $this->fail('Exception expected');
1231 } catch (moodle_exception
$e) {
1232 $this->assertInstanceOf('ddl_change_structure_exception', $e);
1234 // No changes in field specs at all.
1235 $columns = $DB->get_columns('test_table1');
1236 $this->assertSame('I', $columns['userid']->meta_type
);
1237 // TODO: check the rest of attributes.
1239 // Change integer field from 10 to 3, in field used by index. must throw exception.
1240 $field = new xmldb_field('course');
1241 $field->set_attributes(XMLDB_TYPE_INTEGER
, '3', null, XMLDB_NOTNULL
, null, '0');
1243 $dbman->change_field_precision($table, $field);
1244 $this->fail('Exception expected');
1245 } catch (moodle_exception
$e) {
1246 $this->assertInstanceOf('ddl_dependency_exception', $e);
1248 // No changes in field specs at all.
1249 $columns = $DB->get_columns('test_table1');
1250 $this->assertSame('I', $columns['course']->meta_type
);
1251 // TODO: check the rest of attributes.
1254 public function testChangeFieldNullability() {
1255 $DB = $this->tdb
; // Do not use global $DB!
1256 $dbman = $this->tdb
->get_manager();
1258 $table = new xmldb_table('test_table_cust0');
1259 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1260 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, null);
1261 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1262 $dbman->create_table($table);
1264 $record = new stdClass();
1265 $record->name
= null;
1268 $result = $DB->insert_record('test_table_cust0', $record, false);
1269 } catch (dml_exception
$e) {
1272 $this->resetDebugging();
1273 $this->assertFalse($result);
1275 $field = new xmldb_field('name');
1276 $field->set_attributes(XMLDB_TYPE_CHAR
, '30', null, null, null, null);
1277 $dbman->change_field_notnull($table, $field);
1279 $this->assertTrue($DB->insert_record('test_table_cust0', $record, false));
1281 // TODO: add some tests with existing data in table.
1282 $DB->delete_records('test_table_cust0');
1284 $field = new xmldb_field('name');
1285 $field->set_attributes(XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, null);
1286 $dbman->change_field_notnull($table, $field);
1289 $result = $DB->insert_record('test_table_cust0', $record, false);
1290 } catch (dml_exception
$e) {
1293 $this->resetDebugging();
1294 $this->assertFalse($result);
1296 $dbman->drop_table($table);
1299 public function testChangeFieldDefault() {
1300 $DB = $this->tdb
; // Do not use global $DB!
1301 $dbman = $this->tdb
->get_manager();
1303 $table = new xmldb_table('test_table_cust0');
1304 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1305 $table->add_field('onenumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1306 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'Moodle');
1307 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1308 $dbman->create_table($table);
1310 $field = new xmldb_field('name');
1311 $field->set_attributes(XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'Moodle2');
1312 $dbman->change_field_default($table, $field);
1314 $record = new stdClass();
1315 $record->onenumber
= 666;
1316 $id = $DB->insert_record('test_table_cust0', $record);
1318 $record = $DB->get_record('test_table_cust0', array('id'=>$id));
1319 $this->assertSame('Moodle2', $record->name
);
1321 $field = new xmldb_field('onenumber');
1322 $field->set_attributes(XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, 666);
1323 $dbman->change_field_default($table, $field);
1325 $record = new stdClass();
1326 $record->name
= 'something';
1327 $id = $DB->insert_record('test_table_cust0', $record);
1329 $record = $DB->get_record('test_table_cust0', array('id'=>$id));
1330 $this->assertSame('666', $record->onenumber
);
1332 $dbman->drop_table($table);
1335 public function testAddUniqueIndex() {
1336 $DB = $this->tdb
; // Do not use global $DB!
1337 $dbman = $this->tdb
->get_manager();
1339 $table = new xmldb_table('test_table_cust0');
1340 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1341 $table->add_field('onenumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1342 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'Moodle');
1343 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1344 $dbman->create_table($table);
1346 $record = new stdClass();
1347 $record->onenumber
= 666;
1348 $record->name
= 'something';
1349 $DB->insert_record('test_table_cust0', $record, false);
1351 $index = new xmldb_index('onenumber-name');
1352 $index->set_attributes(XMLDB_INDEX_UNIQUE
, array('onenumber', 'name'));
1353 $dbman->add_index($table, $index);
1356 $result = $DB->insert_record('test_table_cust0', $record, false);
1357 } catch (dml_exception
$e) {
1360 $this->resetDebugging();
1361 $this->assertFalse($result);
1363 $dbman->drop_table($table);
1366 public function testAddNonUniqueIndex() {
1367 $dbman = $this->tdb
->get_manager();
1369 $table = $this->create_deftable('test_table1');
1370 $index = new xmldb_index('secondname');
1371 $index->set_attributes(XMLDB_INDEX_NOTUNIQUE
, array('course', 'name'));
1372 $dbman->add_index($table, $index);
1373 $this->assertTrue($dbman->index_exists($table, $index));
1376 $dbman->add_index($table, $index);
1377 $this->fail('Exception expected for duplicate indexes');
1378 } catch (moodle_exception
$e) {
1379 $this->assertInstanceOf('ddl_exception', $e);
1382 $index = new xmldb_index('third');
1383 $index->set_attributes(XMLDB_INDEX_NOTUNIQUE
, array('course'));
1385 $dbman->add_index($table, $index);
1386 $this->fail('Exception expected for duplicate indexes');
1387 } catch (moodle_exception
$e) {
1388 $this->assertInstanceOf('ddl_exception', $e);
1391 $table = new xmldb_table('test_table_cust0');
1392 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1393 $table->add_field('onenumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1394 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'Moodle');
1395 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1396 $table->add_key('onenumber', XMLDB_KEY_FOREIGN
, array('onenumber'));
1399 $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE
, array('onenumber'));
1400 $this->fail('Coding exception expected');
1401 } catch (moodle_exception
$e) {
1402 $this->assertInstanceOf('coding_exception', $e);
1405 $table = new xmldb_table('test_table_cust0');
1406 $table->add_field('id', XMLDB_TYPE_INTEGER
, '2', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1407 $table->add_field('onenumber', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1408 $table->add_field('name', XMLDB_TYPE_CHAR
, '30', null, XMLDB_NOTNULL
, null, 'Moodle');
1409 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1410 $table->add_index('onenumber', XMLDB_INDEX_NOTUNIQUE
, array('onenumber'));
1413 $table->add_key('onenumber', XMLDB_KEY_FOREIGN
, array('onenumber'));
1414 $this->fail('Coding exception expected');
1415 } catch (moodle_exception
$e) {
1416 $this->assertInstanceOf('coding_exception', $e);
1421 public function testFindIndexName() {
1422 $dbman = $this->tdb
->get_manager();
1424 $table = $this->create_deftable('test_table1');
1425 $index = new xmldb_index('secondname');
1426 $index->set_attributes(XMLDB_INDEX_NOTUNIQUE
, array('course', 'name'));
1427 $dbman->add_index($table, $index);
1429 // DBM Systems name their indices differently - do not test the actual index name.
1430 $result = $dbman->find_index_name($table, $index);
1431 $this->assertTrue(!empty($result));
1433 $nonexistentindex = new xmldb_index('nonexistentindex');
1434 $nonexistentindex->set_attributes(XMLDB_INDEX_NOTUNIQUE
, array('name'));
1435 $this->assertFalse($dbman->find_index_name($table, $nonexistentindex));
1438 public function testDropIndex() {
1439 $DB = $this->tdb
; // Do not use global $DB!
1441 $dbman = $this->tdb
->get_manager();
1443 $table = $this->create_deftable('test_table1');
1444 $index = new xmldb_index('secondname');
1445 $index->set_attributes(XMLDB_INDEX_NOTUNIQUE
, array('course', 'name'));
1446 $dbman->add_index($table, $index);
1448 $dbman->drop_index($table, $index);
1449 $this->assertFalse($dbman->find_index_name($table, $index));
1451 // Test we are able to drop indexes having hyphens MDL-22804.
1452 // Create index with hyphens (by hand).
1453 $indexname = 'test-index-with-hyphens';
1454 switch ($DB->get_dbfamily()) {
1456 $indexname = '`' . $indexname . '`';
1459 $indexname = '"' . $indexname . '"';
1461 $stmt = "CREATE INDEX {$indexname} ON {$DB->get_prefix()}test_table1 (course, name)";
1462 $DB->change_database_structure($stmt);
1463 $this->assertNotEmpty($dbman->find_index_name($table, $index));
1464 // Index created, let's drop it using db manager stuff.
1465 $index = new xmldb_index('indexname', XMLDB_INDEX_NOTUNIQUE
, array('course', 'name'));
1466 $dbman->drop_index($table, $index);
1467 $this->assertFalse($dbman->find_index_name($table, $index));
1470 public function testAddUniqueKey() {
1471 $dbman = $this->tdb
->get_manager();
1473 $table = $this->create_deftable('test_table1');
1474 $key = new xmldb_key('id-course-grade');
1475 $key->set_attributes(XMLDB_KEY_UNIQUE
, array('id', 'course', 'grade'));
1476 $dbman->add_key($table, $key);
1478 // No easy way to test it, this just makes sure no errors are encountered.
1479 $this->assertTrue(true);
1482 public function testAddForeignUniqueKey() {
1483 $dbman = $this->tdb
->get_manager();
1485 $table = $this->create_deftable('test_table1');
1486 $this->create_deftable('test_table0');
1488 $key = new xmldb_key('course');
1489 $key->set_attributes(XMLDB_KEY_FOREIGN_UNIQUE
, array('course'), 'test_table0', array('id'));
1490 $dbman->add_key($table, $key);
1492 // No easy way to test it, this just makes sure no errors are encountered.
1493 $this->assertTrue(true);
1496 public function testDropKey() {
1497 $dbman = $this->tdb
->get_manager();
1499 $table = $this->create_deftable('test_table1');
1500 $this->create_deftable('test_table0');
1502 $key = new xmldb_key('course');
1503 $key->set_attributes(XMLDB_KEY_FOREIGN_UNIQUE
, array('course'), 'test_table0', array('id'));
1504 $dbman->add_key($table, $key);
1506 $dbman->drop_key($table, $key);
1508 // No easy way to test it, this just makes sure no errors are encountered.
1509 $this->assertTrue(true);
1512 public function testAddForeignKey() {
1513 $dbman = $this->tdb
->get_manager();
1515 $table = $this->create_deftable('test_table1');
1516 $this->create_deftable('test_table0');
1518 $key = new xmldb_key('course');
1519 $key->set_attributes(XMLDB_KEY_FOREIGN
, array('course'), 'test_table0', array('id'));
1520 $dbman->add_key($table, $key);
1522 // No easy way to test it, this just makes sure no errors are encountered.
1523 $this->assertTrue(true);
1526 public function testDropForeignKey() {
1527 $dbman = $this->tdb
->get_manager();
1529 $table = $this->create_deftable('test_table1');
1530 $this->create_deftable('test_table0');
1532 $key = new xmldb_key('course');
1533 $key->set_attributes(XMLDB_KEY_FOREIGN
, array('course'), 'test_table0', array('id'));
1534 $dbman->add_key($table, $key);
1536 $dbman->drop_key($table, $key);
1538 // No easy way to test it, this just makes sure no errors are encountered.
1539 $this->assertTrue(true);
1542 public function testRenameField() {
1543 $DB = $this->tdb
; // Do not use global $DB!
1544 $dbman = $this->tdb
->get_manager();
1546 $table = $this->create_deftable('test_table0');
1547 $field = new xmldb_field('type');
1548 $field->set_attributes(XMLDB_TYPE_CHAR
, '20', null, XMLDB_NOTNULL
, null, 'general', 'course');
1550 // 1. Rename the 'type' field into a generic new valid name.
1551 // This represents the standard use case.
1552 $dbman->rename_field($table, $field, 'newfieldname');
1554 $columns = $DB->get_columns('test_table0');
1556 $this->assertArrayNotHasKey('type', $columns);
1557 $this->assertArrayHasKey('newfieldname', $columns);
1558 $field->setName('newfieldname');
1560 // 2. Rename the 'newfieldname' field into a reserved word, for testing purposes.
1561 // This represents a questionable use case: we should support it but discourage the use of it on peer reviewing.
1562 $dbman->rename_field($table, $field, 'where');
1564 $columns = $DB->get_columns('test_table0');
1566 $this->assertArrayNotHasKey('newfieldname', $columns);
1567 $this->assertArrayHasKey('where', $columns);
1569 // 3. Create a table with a column name named w/ a reserved word and get rid of it.
1570 // This represents a "recovering" use case: a field name could be a reserved word in the future, at least for a DB type.
1571 $table = new xmldb_table('test_table_res_word');
1572 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1573 $table->add_field('where', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1574 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1575 $table->setComment("This is a test'n drop table. You can drop it safely");
1576 $dbman->create_table($table);
1577 $dbman->table_exists('test_table_res_word');
1579 $columns = $DB->get_columns('test_table_res_word');
1580 $this->assertArrayHasKey('where', $columns);
1581 $field = $table->getField('where');
1583 $dbman->rename_field($table, $field, 'newfieldname');
1585 $columns = $DB->get_columns('test_table_res_word');
1587 $this->assertArrayNotHasKey('where', $columns);
1588 $this->assertArrayHasKey('newfieldname', $columns);
1591 public function testIndexExists() {
1592 // Skipping: this is just a test of find_index_name.
1595 public function testFindKeyName() {
1596 $dbman = $this->tdb
->get_manager();
1598 $table = $this->create_deftable('test_table0');
1599 $key = $table->getKey('primary');
1601 // With Mysql, the return value is actually "mdl_test_id_pk".
1602 $result = $dbman->find_key_name($table, $key);
1603 $this->assertTrue(!empty($result));
1606 public function testDeleteTablesFromXmldbFile() {
1607 $dbman = $this->tdb
->get_manager();
1609 $this->create_deftable('test_table1');
1611 $this->assertTrue($dbman->table_exists('test_table1'));
1613 // Feed nonexistent file.
1615 $dbman->delete_tables_from_xmldb_file('fpsoiudfposui');
1616 $this->fail('Exception expected');
1617 } catch (moodle_exception
$e) {
1618 $this->resetDebugging();
1619 $this->assertInstanceOf('moodle_exception', $e);
1623 $dbman->delete_tables_from_xmldb_file(__DIR__
. '/fixtures/invalid.xml');
1624 $this->fail('Exception expected');
1625 } catch (moodle_exception
$e) {
1626 $this->resetDebugging();
1627 $this->assertInstanceOf('moodle_exception', $e);
1630 // Check that the table has not been deleted from DB.
1631 $this->assertTrue($dbman->table_exists('test_table1'));
1633 // Real and valid xml file.
1634 // TODO: drop UNSINGED completely in Moodle 2.4.
1635 $dbman->delete_tables_from_xmldb_file(__DIR__
. '/fixtures/xmldb_table.xml');
1637 // Check that the table has been deleted from DB.
1638 $this->assertFalse($dbman->table_exists('test_table1'));
1641 public function testInstallFromXmldbFile() {
1642 $dbman = $this->tdb
->get_manager();
1644 // Feed nonexistent file.
1646 $dbman->install_from_xmldb_file('fpsoiudfposui');
1647 $this->fail('Exception expected');
1648 } catch (moodle_exception
$e) {
1649 $this->resetDebugging();
1650 $this->assertInstanceOf('moodle_exception', $e);
1654 $dbman->install_from_xmldb_file(__DIR__
. '/fixtures/invalid.xml');
1655 $this->fail('Exception expected');
1656 } catch (moodle_exception
$e) {
1657 $this->resetDebugging();
1658 $this->assertInstanceOf('moodle_exception', $e);
1661 // Check that the table has not yet been created in DB.
1662 $this->assertFalse($dbman->table_exists('test_table1'));
1664 // Real and valid xml file.
1665 $dbman->install_from_xmldb_file(__DIR__
. '/fixtures/xmldb_table.xml');
1666 $this->assertTrue($dbman->table_exists('test_table1'));
1669 public function test_temp_tables() {
1670 $DB = $this->tdb
; // Do not use global $DB!
1671 $dbman = $this->tdb
->get_manager();
1673 // Create temp table0.
1674 $table0 = $this->tables
['test_table0'];
1675 $dbman->create_temp_table($table0);
1676 $this->assertTrue($dbman->table_exists('test_table0'));
1678 // Try to create temp table with same name, must throw exception.
1679 $dupetable = $this->tables
['test_table0'];
1681 $dbman->create_temp_table($dupetable);
1682 $this->fail('Exception expected');
1683 } catch (moodle_exception
$e) {
1684 $this->assertInstanceOf('ddl_exception', $e);
1687 // Try to create table with same name, must throw exception.
1688 $dupetable = $this->tables
['test_table0'];
1690 $dbman->create_table($dupetable);
1691 $this->fail('Exception expected');
1692 } catch (moodle_exception
$e) {
1693 $this->assertInstanceOf('ddl_exception', $e);
1696 // Create another temp table1.
1697 $table1 = $this->tables
['test_table1'];
1698 $dbman->create_temp_table($table1);
1699 $this->assertTrue($dbman->table_exists('test_table1'));
1701 // Get columns and perform some basic tests.
1702 $columns = $DB->get_columns('test_table1');
1703 $this->assertCount(11, $columns);
1704 $this->assertTrue($columns['name'] instanceof database_column_info
);
1705 $this->assertEquals(30, $columns['name']->max_length
);
1706 $this->assertTrue($columns['name']->has_default
);
1707 $this->assertEquals('Moodle', $columns['name']->default_value
);
1709 // Insert some records.
1710 $inserted = $this->fill_deftable('test_table1');
1711 $records = $DB->get_records('test_table1');
1712 $this->assertCount($inserted, $records);
1713 $this->assertSame($records[1]->course
, $this->records
['test_table1'][0]->course
);
1714 $this->assertSame($records[1]->secondname
, $this->records
['test_table1'][0]->secondname
);
1715 $this->assertSame($records[2]->intro
, $this->records
['test_table1'][1]->intro
);
1717 // Collect statistics about the data in the temp table.
1718 $DB->update_temp_table_stats();
1721 $dbman->drop_table($table1);
1722 $this->assertFalse($dbman->table_exists('test_table1'));
1724 // Try to drop non-existing temp table, must throw exception.
1725 $noetable = $this->tables
['test_table1'];
1727 $dbman->drop_table($noetable);
1728 $this->fail('Exception expected');
1729 } catch (moodle_exception
$e) {
1730 $this->assertInstanceOf('ddl_table_missing_exception', $e);
1733 // Collect statistics about the data in the temp table with less tables.
1734 $DB->update_temp_table_stats();
1736 // Fill/modify/delete a few table0 records.
1739 $dbman->drop_table($table0);
1740 $this->assertFalse($dbman->table_exists('test_table0'));
1742 // Create another temp table1.
1743 $table1 = $this->tables
['test_table1'];
1744 $dbman->create_temp_table($table1);
1745 $this->assertTrue($dbman->table_exists('test_table1'));
1747 // Make sure it can be dropped using deprecated drop_temp_table().
1748 $dbman->drop_temp_table($table1);
1749 $this->assertFalse($dbman->table_exists('test_table1'));
1750 $this->assertDebuggingCalled();
1752 // Try join with normal tables - MS SQL may use incompatible collation.
1753 $table1 = new xmldb_table('test_table');
1754 $table1->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1755 $table1->add_field('name', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
1756 $table1->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1757 $dbman->create_table($table1);
1759 $table2 = new xmldb_table('test_temp');
1760 $table2->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1761 $table2->add_field('name', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
1762 $table2->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1763 $dbman->create_temp_table($table2);
1765 $record = array('name' => 'a');
1766 $DB->insert_record('test_table', $record);
1767 $DB->insert_record('test_temp', $record);
1769 $record = array('name' => 'b');
1770 $DB->insert_record('test_table', $record);
1772 $record = array('name' => 'c');
1773 $DB->insert_record('test_temp', $record);
1777 JOIN {test_temp} t ON t.name = n.name";
1778 $records = $DB->get_records_sql($sql);
1779 $this->assertCount(1, $records);
1782 $dbman->drop_table($table2);
1783 $this->assertFalse($dbman->table_exists('test_temp'));
1786 public function test_concurrent_temp_tables() {
1787 $DB = $this->tdb
; // Do not use global $DB!
1788 $dbman = $this->tdb
->get_manager();
1790 // Define 2 records.
1791 $record1 = (object)array(
1793 'secondname' => '11 important',
1794 'intro' => '111 important');
1795 $record2 = (object)array(
1797 'secondname' => '22 important',
1798 'intro' => '222 important');
1800 // Create temp table1 and insert 1 record (in DB).
1801 $table = $this->tables
['test_table1'];
1802 $dbman->create_temp_table($table);
1803 $this->assertTrue($dbman->table_exists('test_table1'));
1804 $inserted = $DB->insert_record('test_table1', $record1);
1806 // Switch to new connection.
1807 $cfg = $DB->export_dbconfig();
1808 if (!isset($cfg->dboptions
)) {
1809 $cfg->dboptions
= array();
1811 $DB2 = moodle_database
::get_driver_instance($cfg->dbtype
, $cfg->dblibrary
);
1812 $DB2->connect($cfg->dbhost
, $cfg->dbuser
, $cfg->dbpass
, $cfg->dbname
, $cfg->prefix
, $cfg->dboptions
);
1813 $dbman2 = $DB2->get_manager();
1814 $this->assertFalse($dbman2->table_exists('test_table1')); // Temp table not exists in DB2.
1816 // Create temp table1 and insert 1 record (in DB2).
1817 $table = $this->tables
['test_table1'];
1818 $dbman2->create_temp_table($table);
1819 $this->assertTrue($dbman2->table_exists('test_table1'));
1820 $inserted = $DB2->insert_record('test_table1', $record2);
1822 $dbman2->drop_table($table); // Drop temp table before closing DB2.
1823 $this->assertFalse($dbman2->table_exists('test_table1'));
1824 $DB2->dispose(); // Close DB2.
1826 $this->assertTrue($dbman->table_exists('test_table1')); // Check table continues existing for DB.
1827 $dbman->drop_table($table); // Drop temp table.
1828 $this->assertFalse($dbman->table_exists('test_table1'));
1832 * get_columns should return an empty array for ex-temptables.
1834 public function test_leftover_temp_tables_columns() {
1835 $DB = $this->tdb
; // Do not use global $DB!
1836 $dbman = $this->tdb
->get_manager();
1838 // Create temp table0.
1839 $table0 = $this->tables
['test_table0'];
1840 $dbman->create_temp_table($table0);
1842 $dbman->drop_table($table0);
1844 // Get columns and perform some basic tests.
1845 $columns = $DB->get_columns('test_table0');
1846 $this->assertEquals([], $columns);
1850 * Deleting a temp table should not purge the whole cache
1852 public function test_leftover_temp_tables_cache() {
1853 $DB = $this->tdb
; // Do not use global $DB!
1854 $dbman = $this->tdb
->get_manager();
1856 // Create 2 temp tables.
1857 $table0 = $this->tables
['test_table0'];
1858 $dbman->create_temp_table($table0);
1859 $table1 = $this->tables
['test_table1'];
1860 $dbman->create_temp_table($table1);
1862 // Create a normal table.
1863 $table2 = new xmldb_table ('test_table2');
1864 $table2->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1865 $table2->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1866 $table2->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1867 $table2->setComment("This is a test'n drop table. You can drop it safely");
1868 $this->tables
[$table2->getName()] = $table2;
1869 $dbman->create_table($table2);
1871 // Get columns for the tables, so that relevant caches are populated with their data.
1872 $DB->get_columns('test_table0');
1873 $DB->get_columns('test_table1');
1874 $DB->get_columns('test_table2');
1876 $dbman->drop_table($table0);
1878 $rc = new ReflectionClass('moodle_database');
1879 $rcm = $rc->getMethod('get_temp_tables_cache');
1880 $rcm->setAccessible(true);
1881 $metacachetemp = $rcm->invokeArgs($DB, []);
1883 // Data of test_table0 should be removed from the cache.
1884 $this->assertEquals(false, $metacachetemp->has('test_table0'));
1886 // Data of test_table1 should be intact.
1887 $this->assertEquals(true, $metacachetemp->has('test_table1'));
1889 $rc = new ReflectionClass('moodle_database');
1890 $rcm = $rc->getMethod('get_metacache');
1891 $rcm->setAccessible(true);
1892 $metacache = $rcm->invokeArgs($DB, []);
1894 // Data of test_table2 should be intact.
1895 $this->assertEquals(true, $metacache->has('test_table2'));
1897 // Delete the leftover temp table.
1898 $dbman->drop_table($table1);
1901 public function test_reset_sequence() {
1903 $dbman = $DB->get_manager();
1905 $table = new xmldb_table('testtable');
1906 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1907 $table->add_field('course', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, '0');
1908 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1911 if ($dbman->table_exists($table)) {
1912 $dbman->drop_table($table);
1914 $dbman->create_table($table);
1915 $tablename = $table->getName();
1916 $this->tables
[$tablename] = $table;
1918 $record = (object)array('id'=>666, 'course'=>10);
1919 $DB->import_record('testtable', $record);
1920 $DB->delete_records('testtable'); // This delete performs one TRUNCATE.
1922 $dbman->reset_sequence($table); // Using xmldb object.
1923 $this->assertEquals(1, $DB->insert_record('testtable', (object)array('course'=>13)));
1925 $record = (object)array('id'=>666, 'course'=>10);
1926 $DB->import_record('testtable', $record);
1927 $DB->delete_records('testtable', array()); // This delete performs one DELETE.
1929 $dbman->reset_sequence($table); // Using xmldb object.
1930 $this->assertEquals(1, $DB->insert_record('testtable', (object)array('course'=>13)),
1931 'Some versions of MySQL 5.6.x are known to not support lowering of auto-increment numbers.');
1933 $DB->import_record('testtable', $record);
1934 $dbman->reset_sequence($tablename); // Using string.
1935 $this->assertEquals(667, $DB->insert_record('testtable', (object)array('course'=>13)));
1937 $dbman->drop_table($table);
1940 public function test_reserved_words() {
1941 $reserved = sql_generator
::getAllReservedWords();
1942 $this->assertTrue(count($reserved) > 1);
1945 public function test_index_hints() {
1947 $dbman = $DB->get_manager();
1949 $table = new xmldb_table('testtable');
1950 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1951 $table->add_field('name', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
1952 $table->add_field('path', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
1953 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1954 $table->add_index('name', XMLDB_INDEX_NOTUNIQUE
, array('name'), array('xxxx,yyyy'));
1955 $table->add_index('path', XMLDB_INDEX_NOTUNIQUE
, array('path'), array('varchar_pattern_ops'));
1958 if ($dbman->table_exists($table)) {
1959 $dbman->drop_table($table);
1961 $dbman->create_table($table);
1962 $tablename = $table->getName();
1963 $this->tables
[$tablename] = $table;
1965 $table = new xmldb_table('testtable');
1966 $index = new xmldb_index('name', XMLDB_INDEX_NOTUNIQUE
, array('name'), array('xxxx,yyyy'));
1967 $this->assertTrue($dbman->index_exists($table, $index));
1969 $table = new xmldb_table('testtable');
1970 $index = new xmldb_index('path', XMLDB_INDEX_NOTUNIQUE
, array('path'), array('varchar_pattern_ops'));
1971 $this->assertTrue($dbman->index_exists($table, $index));
1973 // Try unique indexes too.
1974 $dbman->drop_table($this->tables
[$tablename]);
1976 $table = new xmldb_table('testtable');
1977 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
1978 $table->add_field('path', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
1979 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
1980 $table->add_index('path', XMLDB_INDEX_UNIQUE
, array('path'), array('varchar_pattern_ops'));
1981 $dbman->create_table($table);
1982 $this->tables
[$tablename] = $table;
1984 $table = new xmldb_table('testtable');
1985 $index = new xmldb_index('path', XMLDB_INDEX_UNIQUE
, array('path'), array('varchar_pattern_ops'));
1986 $this->assertTrue($dbman->index_exists($table, $index));
1989 public function test_index_max_bytes() {
1991 $dbman = $DB->get_manager();
1994 for ($i=0; $i<255; $i++
) {
1995 $maxstr .= '言'; // Random long string that should fix exactly the limit for one char column.
1998 $table = new xmldb_table('testtable');
1999 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2000 $table->add_field('name', XMLDB_TYPE_CHAR
, 255, null, XMLDB_NOTNULL
, null);
2001 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2002 $table->add_index('name', XMLDB_INDEX_NOTUNIQUE
, array('name'));
2005 if ($dbman->table_exists($table)) {
2006 $dbman->drop_table($table);
2008 $dbman->create_table($table);
2009 $tablename = $table->getName();
2010 $this->tables
[$tablename] = $table;
2012 $rec = new stdClass();
2013 $rec->name
= $maxstr;
2015 $id = $DB->insert_record($tablename, $rec);
2016 $this->assertTrue(!empty($id));
2018 $rec = $DB->get_record($tablename, array('id'=>$id));
2019 $this->assertSame($maxstr, $rec->name
);
2021 $dbman->drop_table($table);
2023 $table = new xmldb_table('testtable');
2024 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2025 $table->add_field('name', XMLDB_TYPE_CHAR
, 255+
1, null, XMLDB_NOTNULL
, null);
2026 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2027 $table->add_index('name', XMLDB_INDEX_NOTUNIQUE
, array('name'));
2030 $dbman->create_table($table);
2031 $this->fail('Exception expected');
2032 } catch (moodle_exception
$e) {
2033 $this->assertInstanceOf('coding_exception', $e);
2037 public function test_index_composed_max_bytes() {
2039 $dbman = $DB->get_manager();
2042 for ($i=0; $i<200; $i++
) {
2046 for ($i=0; $i<133; $i++
) {
2050 $table = new xmldb_table('testtable');
2051 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2052 $table->add_field('name1', XMLDB_TYPE_CHAR
, 200, null, XMLDB_NOTNULL
, null);
2053 $table->add_field('name2', XMLDB_TYPE_CHAR
, 133, null, XMLDB_NOTNULL
, null);
2054 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2055 $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE
, array('name1', 'name2'));
2058 if ($dbman->table_exists($table)) {
2059 $dbman->drop_table($table);
2061 $dbman->create_table($table);
2062 $tablename = $table->getName();
2063 $this->tables
[$tablename] = $table;
2065 $rec = new stdClass();
2066 $rec->name1
= $maxstr;
2067 $rec->name2
= $reststr;
2069 $id = $DB->insert_record($tablename, $rec);
2070 $this->assertTrue(!empty($id));
2072 $rec = $DB->get_record($tablename, array('id'=>$id));
2073 $this->assertSame($maxstr, $rec->name1
);
2074 $this->assertSame($reststr, $rec->name2
);
2076 $table = new xmldb_table('testtable');
2077 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2078 $table->add_field('name1', XMLDB_TYPE_CHAR
, 201, null, XMLDB_NOTNULL
, null);
2079 $table->add_field('name2', XMLDB_TYPE_CHAR
, 133, null, XMLDB_NOTNULL
, null);
2080 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2081 $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE
, array('name1', 'name2'));
2084 if ($dbman->table_exists($table)) {
2085 $dbman->drop_table($table);
2089 $dbman->create_table($table);
2090 $this->fail('Exception expected');
2091 } catch (moodle_exception
$e) {
2092 $this->assertInstanceOf('coding_exception', $e);
2096 public function test_char_size_limit() {
2098 $dbman = $DB->get_manager();
2100 $table = new xmldb_table('testtable');
2101 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2102 $table->add_field('name', XMLDB_TYPE_CHAR
, xmldb_field
::CHAR_MAX_LENGTH
, null, XMLDB_NOTNULL
, null);
2103 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2106 if ($dbman->table_exists($table)) {
2107 $dbman->drop_table($table);
2109 $dbman->create_table($table);
2110 $tablename = $table->getName();
2111 $this->tables
[$tablename] = $table;
2113 // This has to work in all DBs.
2115 for ($i=0; $i<xmldb_field
::CHAR_MAX_LENGTH
; $i++
) {
2116 $maxstr .= 'a'; // Ascii only.
2119 $rec = new stdClass();
2120 $rec->name
= $maxstr;
2122 $id = $DB->insert_record($tablename, $rec);
2123 $this->assertTrue(!empty($id));
2125 $rec = $DB->get_record($tablename, array('id'=>$id));
2126 $this->assertSame($maxstr, $rec->name
);
2128 // Following test is supposed to fail in oracle.
2130 for ($i=0; $i<xmldb_field
::CHAR_MAX_LENGTH
; $i++
) {
2131 $maxstr .= '言'; // Random long string that should fix exactly the limit for one char column.
2134 $rec = new stdClass();
2135 $rec->name
= $maxstr;
2138 $id = $DB->insert_record($tablename, $rec);
2139 $this->assertTrue(!empty($id));
2141 $rec = $DB->get_record($tablename, array('id'=>$id));
2142 $this->assertSame($maxstr, $rec->name
);
2143 } catch (dml_exception
$e) {
2144 if ($DB->get_dbfamily() === 'oracle') {
2145 $this->fail('Oracle does not support text fields larger than 4000 bytes, this is not a big problem for mostly ascii based languages');
2151 $table = new xmldb_table('testtable');
2152 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2153 $table->add_field('name', XMLDB_TYPE_CHAR
, xmldb_field
::CHAR_MAX_LENGTH+
1, null, XMLDB_NOTNULL
, null);
2154 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2157 if ($dbman->table_exists($table)) {
2158 $dbman->drop_table($table);
2160 $tablename = $table->getName();
2161 $this->tables
[$tablename] = $table;
2164 $dbman->create_table($table);
2165 $this->fail('Exception expected');
2166 } catch (moodle_exception
$e) {
2167 $this->assertInstanceOf('coding_exception', $e);
2171 public function test_object_name() {
2172 $gen = $this->tdb
->get_manager()->generator
;
2174 // This will form short object name and max length should not be exceeded.
2175 $table = 'tablename';
2178 for ($i=0; $i<12; $i++
) {
2179 $this->assertLessThanOrEqual($gen->names_max_length
,
2180 strlen($gen->getNameForObject($table, $fields, $suffix)),
2181 'Generated object name is too long. $i = '.$i);
2184 // This will form too long object name always and it must be trimmed to exactly 30 chars.
2185 $table = 'aaaa_bbbb_cccc_dddd_eeee_ffff_gggg';
2186 $fields = 'aaaaa,bbbbb,ccccc,ddddd';
2188 for ($i=0; $i<12; $i++
) {
2189 $this->assertEquals($gen->names_max_length
,
2190 strlen($gen->getNameForObject($table, $fields, $suffix)),
2191 'Generated object name is too long. $i = '.$i);
2194 // Same test without suffix.
2195 $table = 'bbbb_cccc_dddd_eeee_ffff_gggg_hhhh';
2196 $fields = 'aaaaa,bbbbb,ccccc,ddddd';
2198 for ($i=0; $i<12; $i++
) {
2199 $this->assertEquals($gen->names_max_length
,
2200 strlen($gen->getNameForObject($table, $fields, $suffix)),
2201 'Generated object name is too long. $i = '.$i);
2204 // This must only trim name when counter is 10 or more.
2205 $table = 'cccc_dddd_eeee_ffff_gggg_hhhh_iiii';
2208 // Since we don't know how long prefix is, loop to generate tablename that gives exactly maxlengh-1 length.
2209 // Skip this test if prefix is too long.
2210 while (strlen($table) && strlen($gen->prefix
.preg_replace('/_/','',$table).'_id_'.$suffix) >= $gen->names_max_length
) {
2211 $table = rtrim(substr($table, 0, strlen($table) - 1), '_');
2213 if (strlen($table)) {
2214 $this->assertEquals($gen->names_max_length
- 1,
2215 strlen($gen->getNameForObject($table, $fields, $suffix)));
2216 for ($i=0; $i<12; $i++
) {
2217 $this->assertEquals($gen->names_max_length
,
2218 strlen($gen->getNameForObject($table, $fields, $suffix)),
2219 'Generated object name is too long. $i = '.$i);
2222 // Now test to confirm that a duplicate name isn't issued, even if they come from different root names.
2223 // Move to a new field.
2226 // Insert twice, moving is to a key with fl2.
2227 $this->assertEquals($gen->names_max_length
- 1, strlen($gen->getNameForObject($table, $fields, $suffix)));
2228 $result1 = $gen->getNameForObject($table, $fields, $suffix);
2230 // Make sure we end up with _fl2_ in the result.
2231 $this->assertMatchesRegularExpression('/_fl2_/', $result1);
2233 // Now, use a field that would result in the same key if it wasn't already taken.
2235 // Because we are now at the max key length, it will try:
2236 // - _fl2_ (the natural name)
2237 // - _fl2_ (removing the original 2, and adding a counter 2)
2238 // - then settle on _fl3_.
2239 $result2 = $gen->getNameForObject($table, $fields, $suffix);
2240 $this->assertMatchesRegularExpression('/_fl3_/', $result2);
2242 // Make sure they don't match.
2243 $this->assertNotEquals($result1, $result2);
2244 // But are only different in the way we expect. This confirms the test is working properly.
2245 $this->assertEquals(str_replace('_fl2_', '', $result1), str_replace('_fl3_', '', $result2));
2247 // Now go back. We would expect the next result to be fl3 again, but it is taken, so it should move to fl4.
2249 $result3 = $gen->getNameForObject($table, $fields, $suffix);
2251 $this->assertNotEquals($result2, $result3);
2252 $this->assertMatchesRegularExpression('/_fl4_/', $result3);
2257 * Data provider for test_get_enc_quoted().
2259 * @return array The type-value pair fixture.
2261 public function test_get_enc_quoted_provider() {
2263 // Reserved: some examples from SQL-92.
2268 [false, 'my_awesome_column_name']
2273 * This is a test for sql_generator::getEncQuoted().
2275 * @dataProvider test_get_enc_quoted_provider
2276 * @param bool $reserved Whether the column name is reserved or not.
2277 * @param string $columnname The column name to be quoted, according to the value of $reserved.
2279 public function test_get_enc_quoted($reserved, $columnname) {
2281 $gen = $DB->get_manager()->generator
;
2284 // No need to quote the column name.
2285 $this->assertSame($columnname, $gen->getEncQuoted($columnname));
2287 // Column name should be quoted.
2288 $dbfamily = $DB->get_dbfamily();
2290 switch ($dbfamily) {
2292 $this->assertSame("`$columnname`", $gen->getEncQuoted($columnname));
2294 case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2299 $this->assertSame('"' . $columnname . '"', $gen->getEncQuoted($columnname));
2306 * Data provider for test_sql_generator_get_rename_field_sql().
2308 * @return array The type-old-new tuple fixture.
2310 public function test_sql_generator_get_rename_field_sql_provider() {
2312 // Reserved: an example from SQL-92.
2313 // Both names should be reserved.
2314 [true, 'from', 'where'],
2316 [false, 'my_old_column_name', 'my_awesome_column_name']
2321 * This is a unit test for sql_generator::getRenameFieldSQL().
2323 * @dataProvider test_sql_generator_get_rename_field_sql_provider
2324 * @param bool $reserved Whether the column name is reserved or not.
2325 * @param string $oldcolumnname The column name to be renamed.
2326 * @param string $newcolumnname The new column name.
2328 public function test_sql_generator_get_rename_field_sql($reserved, $oldcolumnname, $newcolumnname) {
2330 $gen = $DB->get_manager()->generator
;
2331 $prefix = $DB->get_prefix();
2333 $tablename = 'test_get_rename_field_sql';
2334 $table = new xmldb_table($tablename);
2335 $field = new xmldb_field($oldcolumnname, XMLDB_TYPE_INTEGER
, '11', null, XMLDB_NOTNULL
, null, null, null, '0', 'previous');
2337 $dbfamily = $DB->get_dbfamily();
2339 // No need to quote the column name.
2340 switch ($dbfamily) {
2343 [ "ALTER TABLE {$prefix}$tablename CHANGE $oldcolumnname $newcolumnname BIGINT(11) NOT NULL" ],
2344 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2348 // Skip it, since the DB is not supported yet.
2349 // BTW renaming a column name is already covered by the integration test 'testRenameField'.
2351 case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2353 [ "sp_rename '{$prefix}$tablename.[$oldcolumnname]', '$newcolumnname', 'COLUMN'" ],
2354 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2361 [ "ALTER TABLE {$prefix}$tablename RENAME COLUMN $oldcolumnname TO $newcolumnname" ],
2362 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2367 // Column name should be quoted.
2368 switch ($dbfamily) {
2371 [ "ALTER TABLE {$prefix}$tablename CHANGE `$oldcolumnname` `$newcolumnname` BIGINT(11) NOT NULL" ],
2372 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2376 // Skip it, since the DB is not supported yet.
2377 // BTW renaming a column name is already covered by the integration test 'testRenameField'.
2379 case 'mssql': // The Moodle connection runs under 'QUOTED_IDENTIFIER ON'.
2381 [ "sp_rename '{$prefix}$tablename.[$oldcolumnname]', '$newcolumnname', 'COLUMN'" ],
2382 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2389 [ "ALTER TABLE {$prefix}$tablename RENAME COLUMN \"$oldcolumnname\" TO \"$newcolumnname\"" ],
2390 $gen->getRenameFieldSQL($table, $field, $newcolumnname)
2397 public function test_get_nullable_fields_in_index() {
2399 $gen = $DB->get_manager()->generator
;
2401 $indexwithoutnulls = $this->tables
['test_table0']->getIndex('type-name');
2402 $this->assertSame([], $gen->get_nullable_fields_in_index(
2403 $this->tables
['test_table0'], $indexwithoutnulls));
2405 $indexwithnulls = new xmldb_index('course-grade', XMLDB_INDEX_UNIQUE
, ['course', 'grade']);
2406 $this->assertSame(['grade'], $gen->get_nullable_fields_in_index(
2407 $this->tables
['test_table0'], $indexwithnulls));
2409 $this->create_deftable('test_table0');
2411 // Now test using a minimal xmldb_table, to ensure we get the data from the DB.
2412 $table = new xmldb_table('test_table0');
2413 $this->assertSame([], $gen->get_nullable_fields_in_index(
2414 $table, $indexwithoutnulls));
2415 $this->assertSame(['grade'], $gen->get_nullable_fields_in_index(
2416 $table, $indexwithnulls));
2419 // Following methods are not supported == Do not test.
2421 public function testRenameIndex() {
2423 $dbman = $this->tdb->get_manager();
2425 $table = $this->create_deftable('test_table0');
2426 $index = new xmldb_index('course');
2427 $index->set_attributes(XMLDB_INDEX_UNIQUE, array('course'));
2429 $this->assertTrue($dbman->rename_index($table, $index, 'newindexname'));
2432 public function testRenameKey() {
2434 $dbman = $this->tdb->get_manager();
2436 $table = $this->create_deftable('test_table0');
2437 $key = new xmldb_key('course');
2438 $key->set_attributes(XMLDB_KEY_UNIQUE, array('course'));
2440 $this->assertTrue($dbman->rename_key($table, $key, 'newkeyname'));
2445 * Tests check_database_schema().
2447 public function test_check_database_schema() {
2450 $dbmanager = $DB->get_manager();
2452 // Create a table in the database we will be using to compare with a schema.
2453 $table = new xmldb_table('test_check_db_schema');
2454 $table->add_field('id', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, XMLDB_SEQUENCE
, null);
2455 $table->add_field('extracolumn', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, null);
2456 $table->add_field('courseid', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, null);
2457 $table->add_field('binaryfield', XMLDB_TYPE_BINARY
, null, null, XMLDB_NOTNULL
, null, null);
2458 $table->add_key('primary', XMLDB_KEY_PRIMARY
, array('id'));
2459 $table->add_key('extraindex', XMLDB_KEY_UNIQUE
, array('extracolumn'));
2460 $table->setComment("This is a test table, you can drop it safely.");
2461 $dbmanager->create_table($table);
2463 // Remove the column so it is not added to the schema and gets reported as an extra column.
2464 $table->deleteField('extracolumn');
2466 // Change the 'courseid' field to a float in the schema so it gets reported as different.
2467 $table->deleteField('courseid');
2468 $table->add_field('courseid', XMLDB_TYPE_NUMBER
, '10, 2', null, XMLDB_NOTNULL
, null, null);
2470 // Add another column to the schema that won't be present in the database and gets reported as missing.
2471 $table->add_field('missingcolumn', XMLDB_TYPE_INTEGER
, '10', null, XMLDB_NOTNULL
, null, null);
2473 // Add another key to the schema that won't be present in the database and gets reported as missing.
2474 $table->add_key('missingkey', XMLDB_KEY_FOREIGN
, array('courseid'), 'course', array('id'));
2476 // Remove the key from the schema which will still be present in the database and reported as extra.
2477 $table->deleteKey('extraindex');
2479 $schema = new xmldb_structure('testschema');
2480 $schema->addTable($table);
2482 // Things we want to check for -
2483 // 1. Changed columns.
2484 // 2. Missing columns.
2485 // 3. Missing indexes.
2486 // 4. Unexpected index.
2487 // 5. Extra columns.
2488 $errors = $dbmanager->check_database_schema($schema)['test_check_db_schema'];
2489 // Preprocess $errors to get rid of the non compatible (SQL-dialect dependent) parts.
2490 array_walk($errors, function(&$error) {
2491 $error = trim(strtok($error, PHP_EOL
));
2493 $this->assertCount(5, $errors);
2494 $this->assertContains("column 'courseid' has incorrect type 'I', expected 'N'", $errors);
2495 $this->assertContains("column 'missingcolumn' is missing", $errors);
2496 $this->assertContains("Missing index 'missingkey' (not unique (courseid)).", $errors);
2497 $this->assertContains("Unexpected index '{$CFG->prefix}testchecdbsche_ext_uix'.", $errors);
2498 $this->assertContains("column 'extracolumn' is not expected (I)", $errors);