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