MDL-67886 phpunit: Make tests cross-db
[moodle.git] / lib / ddl / tests / ddl_test.php
CommitLineData
a3d5830a
PS
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/**
d5ded4e7 18 * DDL layer tests.
a3d5830a 19 *
5a070f04 20 * @package core_ddl
a3d5830a
PS
21 * @category phpunit
22 * @copyright 2008 Nicolas Connault
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
24 */
25
26defined('MOODLE_INTERNAL') || die();
27
8252b7c2 28class core_ddl_testcase extends database_driver_testcase {
54b2b1d2 29 /** @var xmldb_table[] keys are table name. Created in setUp. */
a3d5830a 30 private $tables = array();
54b2b1d2
TH
31 /** @var array table name => array of stdClass test records loaded into that table. Created in setUp. */
32 private $records = array();
a3d5830a 33
1cbf2a20 34 protected function setUp() {
a3d5830a 35 parent::setUp();
d5ded4e7 36 $dbman = $this->tdb->get_manager(); // Loads DDL libs.
a3d5830a
PS
37
38 $table = new xmldb_table('test_table0');
d5ded4e7
PS
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');
a3d5830a
PS
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);
d5ded4e7
PS
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');
a3d5830a 48 $table->add_field('scale', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
d5ded4e7
PS
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);
a3d5830a 56 $table->add_field('percent', XMLDB_TYPE_NUMBER, '5,2', null, null, null, 66.6);
7cdb4266 57 $table->add_field('bignum', XMLDB_TYPE_NUMBER, '38,18', null, null, null, 1234567890.1234);
d5ded4e7
PS
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');
a3d5830a
PS
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
d5ded4e7 69 // Define 2 initial records for this table.
a3d5830a
PS
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
d5ded4e7 82 // Second, smaller table.
a3d5830a 83 $table = new xmldb_table ('test_table1');
d5ded4e7
PS
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');
a3d5830a
PS
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);
d5ded4e7 88 $table->add_field('thirdname', XMLDB_TYPE_CHAR, '30', null, null, null, ''); // Nullable column with empty default.
a3d5830a
PS
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);
d5ded4e7 92 $table->add_field('gradefloat', XMLDB_TYPE_FLOAT, '20,0', null, null, null, null);
a3d5830a 93 $table->add_field('percentfloat', XMLDB_TYPE_FLOAT, '5,2', null, null, null, 99.9);
d5ded4e7 94 $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS
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
d5ded4e7 101 // Define 2 initial records for this table.
a3d5830a
PS
102 $this->records[$table->getName()] = array(
103 (object)array(
104 'course' => '1',
d5ded4e7 105 'secondname' => 'first record', // Less than 10 cc, please don't modify. Some tests below depend of this.
a3d5830a
PS
106 'intro' => 'first record'),
107 (object)array(
108 'course' => '2',
d5ded4e7 109 'secondname' => 'second record', // More than 10 cc, please don't modify. Some tests below depend of this.
a3d5830a
PS
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
a0c5affe
PS
134 * @param string $tablename
135 * @return int count of records
a3d5830a
PS
136 */
137 private function fill_deftable($tablename) {
d5ded4e7 138 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
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() {
d5ded4e7 160 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
161 $dbman = $this->tdb->get_manager();
162
d5ded4e7 163 // First make sure it returns false if table does not exist.
a3d5830a
PS
164 $table = $this->tables['test_table0'];
165
a3d5830a
PS
166 try {
167 $result = $DB->get_records('test_table0');
168 } catch (dml_exception $e) {
169 $result = false;
170 }
d9ba3c4e 171 $this->resetDebugging();
a3d5830a
PS
172
173 $this->assertFalse($result);
174
d5ded4e7
PS
175 $this->assertFalse($dbman->table_exists('test_table0')); // By name..
176 $this->assertFalse($dbman->table_exists($table)); // By xmldb_table..
a3d5830a 177
d5ded4e7 178 // Create table and test again.
a3d5830a
PS
179 $dbman->create_table($table);
180
d5ded4e7
PS
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.
a3d5830a 184
d5ded4e7 185 // Drop table and test again.
a3d5830a
PS
186 $dbman->drop_table($table);
187
a3d5830a
PS
188 try {
189 $result = $DB->get_records('test_table0');
190 } catch (dml_exception $e) {
191 $result = false;
192 }
d9ba3c4e 193 $this->resetDebugging();
a3d5830a
PS
194
195 $this->assertFalse($result);
196
d5ded4e7
PS
197 $this->assertFalse($dbman->table_exists('test_table0')); // By name.
198 $this->assertFalse($dbman->table_exists($table)); // By xmldb_table.
a3d5830a
PS
199 }
200
201 /**
202 * Test behaviour of create_table()
203 */
204 public function test_create_table() {
0b768e74 205
d5ded4e7 206 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
207 $dbman = $this->tdb->get_manager();
208
d5ded4e7 209 // Create table.
a3d5830a
PS
210 $table = $this->tables['test_table1'];
211
212 $dbman->create_table($table);
213 $this->assertTrue($dbman->table_exists($table));
214
d5ded4e7 215 // Basic get_tables() test.
a3d5830a 216 $tables = $DB->get_tables();
d5ded4e7 217 $this->assertArrayHasKey('test_table1', $tables);
a3d5830a 218
d5ded4e7 219 // Basic get_columns() tests.
a3d5830a 220 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
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.
a3d5830a 232 $this->assertTrue($columns['course']->has_default);
d5ded4e7 233 $this->assertEquals(0, $columns['course']->default_value);
a3d5830a 234 $this->assertTrue($columns['name']->has_default);
d5ded4e7 235 $this->assertSame('Moodle', $columns['name']->default_value);
a3d5830a 236 $this->assertTrue($columns['secondname']->has_default);
d5ded4e7 237 $this->assertSame('', $columns['secondname']->default_value);
a3d5830a 238 $this->assertTrue($columns['thirdname']->has_default);
d5ded4e7 239 $this->assertSame('', $columns['thirdname']->default_value);
a3d5830a 240 $this->assertTrue($columns['percentfloat']->has_default);
d5ded4e7 241 $this->assertEquals(99.9, $columns['percentfloat']->default_value);
a3d5830a 242 $this->assertTrue($columns['userid']->has_default);
d5ded4e7 243 $this->assertEquals(0, $columns['userid']->default_value);
a3d5830a 244
d5ded4e7 245 // Basic get_indexes() test.
a3d5830a
PS
246 $indexes = $DB->get_indexes('test_table1');
247 $courseindex = reset($indexes);
d5ded4e7
PS
248 $this->assertEquals(1, $courseindex['unique']);
249 $this->assertSame('course', $courseindex['columns'][0]);
a3d5830a 250
d5ded4e7 251 // Check sequence returns 1 for first insert.
a3d5830a
PS
252 $rec = (object)array(
253 'course' => 10,
254 'secondname' => 'not important',
255 'intro' => 'not important');
d5ded4e7 256 $this->assertSame(1, $DB->insert_record('test_table1', $rec));
a3d5830a 257
d5ded4e7 258 // Check defined defaults are working ok.
a3d5830a 259 $dbrec = $DB->get_record('test_table1', array('id' => 1));
d5ded4e7
PS
260 $this->assertSame('Moodle', $dbrec->name);
261 $this->assertSame('', $dbrec->thirdname);
a3d5830a 262
d5ded4e7 263 // Check exceptions if multiple R columns.
a3d5830a 264 $table = new xmldb_table ('test_table2');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
277 } catch (moodle_exception $e) {
278 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
279 }
280
d5ded4e7 281 // Check exceptions missing primary key on R column.
a3d5830a 282 $table = new xmldb_table ('test_table2');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
292 } catch (moodle_exception $e) {
293 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
294 }
295
0b768e74
RS
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);
d5ded4e7
PS
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');
a3d5830a
PS
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
0b768e74
RS
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);
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
323 } catch (moodle_exception $e) {
324 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
325 }
326
d5ded4e7 327 // Invalid table name.
a3d5830a 328 $table = new xmldb_table('test_tableCD');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
339 } catch (moodle_exception $e) {
340 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
341 }
342
d5ded4e7 343 // Weird column names - the largest allowed.
a3d5830a 344 $table = new xmldb_table('test_table3');
d5ded4e7 345 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
848d212e 346 $table->add_field(str_repeat('b', xmldb_field::NAME_MAX_LENGTH), XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
a3d5830a
PS
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
848d212e 356 // Too long field name.
a3d5830a 357 $table = new xmldb_table('test_table4');
d5ded4e7 358 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
848d212e 359 $table->add_field(str_repeat('a', xmldb_field::NAME_MAX_LENGTH + 1), XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
a3d5830a
PS
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');
d5ded4e7
PS
368 } catch (moodle_exception $e) {
369 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
370 }
371
d5ded4e7 372 // Invalid field name.
a3d5830a 373 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
384 } catch (moodle_exception $e) {
385 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
386 }
387
d5ded4e7 388 // Invalid integer length.
a3d5830a 389 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
400 } catch (moodle_exception $e) {
401 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
402 }
403
d5ded4e7 404 // Invalid integer default.
a3d5830a 405 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
416 } catch (moodle_exception $e) {
417 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
418 }
419
7cdb4266 420 // Invalid decimal length - max precision is 38 digits.
a3d5830a 421 $table = new xmldb_table('test_table4');
d5ded4e7 422 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
7cdb4266 423 $table->add_field('num', XMLDB_TYPE_NUMBER, '39,19', null, XMLDB_NOTNULL, null, null);
a3d5830a
PS
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');
d5ded4e7
PS
432 } catch (moodle_exception $e) {
433 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
434 }
435
7cdb4266 436 // Invalid decimal decimals - number of decimals can't be higher than total number of digits.
a3d5830a 437 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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);
a3d5830a
PS
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');
d5ded4e7
PS
448 } catch (moodle_exception $e) {
449 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
450 }
451
7cdb4266
DM
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
d5ded4e7 484 // Invalid decimal default.
a3d5830a 485 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
496 } catch (moodle_exception $e) {
497 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
498 }
499
d5ded4e7 500 // Invalid float length.
a3d5830a 501 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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);
a3d5830a
PS
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');
d5ded4e7
PS
512 } catch (moodle_exception $e) {
513 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
514 }
515
d5ded4e7 516 // Invalid float decimals.
a3d5830a 517 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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);
a3d5830a
PS
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');
d5ded4e7
PS
528 } catch (moodle_exception $e) {
529 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
530 }
531
d5ded4e7 532 // Invalid float default.
a3d5830a 533 $table = new xmldb_table('test_table4');
d5ded4e7
PS
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');
a3d5830a
PS
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');
d5ded4e7
PS
544 } catch (moodle_exception $e) {
545 $this->assertInstanceOf('coding_exception', $e);
a3d5830a 546 }
a3d5830a
PS
547 }
548
a45d54b8
PS
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->assertEquals($expected, (array)$DB->get_record('test_innodb', array('id' => $id)), '', 0, 10, true);
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->assertEquals($expected, (array)$DB->get_record('test_innodb', array('id' => $id)), '', 0, 10, true);
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'));
8c1288db 622 for ($i = 0; $i < 12; $i++) {
a45d54b8
PS
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->assertEquals($expected, (array)$DB->get_record('test_innodb', array('id' => $id)), '', 0, 10, true);
632
633 $dbman->drop_table($table);
634 }
635
a3d5830a
PS
636 /**
637 * Test behaviour of drop_table()
638 */
639 public function test_drop_table() {
d5ded4e7 640 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
641 $dbman = $this->tdb->get_manager();
642
d5ded4e7 643 // Initially table doesn't exist.
a3d5830a
PS
644 $this->assertFalse($dbman->table_exists('test_table0'));
645
d5ded4e7 646 // Create table with contents.
a3d5830a
PS
647 $table = $this->create_deftable('test_table0');
648 $this->assertTrue($dbman->table_exists('test_table0'));
649
d5ded4e7 650 // Fill the table with some records before dropping it.
a3d5830a
PS
651 $this->fill_deftable('test_table0');
652
d5ded4e7 653 // Drop by xmldb_table object.
a3d5830a
PS
654 $dbman->drop_table($table);
655 $this->assertFalse($dbman->table_exists('test_table0'));
656
d5ded4e7 657 // Basic get_tables() test.
a3d5830a 658 $tables = $DB->get_tables();
d5ded4e7 659 $this->assertArrayNotHasKey('test_table0', $tables);
a3d5830a 660
d5ded4e7 661 // Columns cache must be empty.
a3d5830a
PS
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() {
d5ded4e7 673 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
674 $dbman = $this->tdb->get_manager();
675
676 $table = $this->create_deftable('test_table1');
677
d5ded4e7 678 // Fill the table with some records before renaming it.
a3d5830a
PS
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
d5ded4e7 685 // Check sequence returns $insertedrows + 1 for this insert (after rename).
a3d5830a
PS
686 $rec = (object)array(
687 'course' => 20,
688 'secondname' => 'not important',
689 'intro' => 'not important');
d5ded4e7 690 $this->assertSame($insertedrows+1, $DB->insert_record('test_table_cust1', $rec));
52953ff9
EL
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 }
a3d5830a
PS
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
d5ded4e7
PS
712 // String params.
713 // Give a nonexistent table as first param (throw exception).
a3d5830a
PS
714 try {
715 $dbman->field_exists('nonexistenttable', 'id');
d5ded4e7
PS
716 $this->fail('Exception expected');
717 } catch (moodle_exception $e) {
718 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS
719 }
720
d5ded4e7 721 // Give a nonexistent field as second param (return false).
a3d5830a
PS
722 $this->assertFalse($dbman->field_exists('test_table0', 'nonexistentfield'));
723
d5ded4e7 724 // Correct string params.
a3d5830a
PS
725 $this->assertTrue($dbman->field_exists('test_table0', 'id'));
726
d5ded4e7 727 // Object params.
a3d5830a
PS
728 $realfield = $table->getField('id');
729
d5ded4e7 730 // Give a nonexistent table as first param (throw exception).
a3d5830a
PS
731 $nonexistenttable = new xmldb_table('nonexistenttable');
732 try {
733 $dbman->field_exists($nonexistenttable, $realfield);
d5ded4e7
PS
734 $this->fail('Exception expected');
735 } catch (moodle_exception $e) {
736 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS
737 }
738
d5ded4e7 739 // Give a nonexistent field as second param (return false).
a3d5830a
PS
740 $nonexistentfield = new xmldb_field('nonexistentfield');
741 $this->assertFalse($dbman->field_exists($table, $nonexistentfield));
742
d5ded4e7 743 // Correct object params.
a3d5830a
PS
744 $this->assertTrue($dbman->field_exists($table, $realfield));
745
d5ded4e7
PS
746 // Mix string and object params.
747 // Correct ones.
a3d5830a
PS
748 $this->assertTrue($dbman->field_exists($table, 'id'));
749 $this->assertTrue($dbman->field_exists('test_table0', $realfield));
d5ded4e7 750 // Non existing tables (throw exception).
a3d5830a
PS
751 try {
752 $this->assertFalse($dbman->field_exists($nonexistenttable, 'id'));
d5ded4e7
PS
753 $this->fail('Exception expected');
754 } catch (moodle_exception $e) {
755 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS
756 }
757 try {
758 $this->assertFalse($dbman->field_exists('nonexistenttable', $realfield));
d5ded4e7
PS
759 $this->fail('Exception expected');
760 } catch (moodle_exception $e) {
761 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a 762 }
d5ded4e7 763 // Non existing fields (return false).
a3d5830a
PS
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() {
d5ded4e7 772 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
773 $dbman = $this->tdb->get_manager();
774
775 $table = $this->create_deftable('test_table1');
776
d5ded4e7 777 // Fill the table with some records before adding fields.
a3d5830a
PS
778 $this->fill_deftable('test_table1');
779
d5ded4e7 780 // Add one not null field without specifying default value (throws ddl_exception).
a3d5830a 781 $field = new xmldb_field('onefield');
d5ded4e7 782 $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, null);
a3d5830a
PS
783 try {
784 $dbman->add_field($table, $field);
d5ded4e7
PS
785 $this->fail('Exception expected');
786 } catch (moodle_exception $e) {
787 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
788 }
789
d5ded4e7 790 // Add one existing field (throws ddl_exception).
a3d5830a 791 $field = new xmldb_field('course');
d5ded4e7 792 $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, 2);
a3d5830a
PS
793 try {
794 $dbman->add_field($table, $field);
d5ded4e7
PS
795 $this->fail('Exception expected');
796 } catch (moodle_exception $e) {
797 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
798 }
799
d5ded4e7
PS
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.
a3d5830a 803
d5ded4e7 804 // Add one integer field and check it.
a3d5830a 805 $field = new xmldb_field('oneinteger');
d5ded4e7 806 $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, XMLDB_NOTNULL, null, 2);
a3d5830a
PS
807 $dbman->add_field($table, $field);
808 $this->assertTrue($dbman->field_exists($table, 'oneinteger'));
809 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
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.
a3d5830a 821 $field = new xmldb_field('onenumber');
d5ded4e7 822 $field->set_attributes(XMLDB_TYPE_NUMBER, '6,3', null, XMLDB_NOTNULL, null, 2.55);
a3d5830a
PS
823 $dbman->add_field($table, $field);
824 $this->assertTrue($dbman->field_exists($table, 'onenumber'));
825 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
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
e3359b87
AA
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
d5ded4e7 847 // Add one float field and check it (not official type - must work as number).
a3d5830a 848 $field = new xmldb_field('onefloat');
d5ded4e7 849 $field->set_attributes(XMLDB_TYPE_FLOAT, '6,3', null, XMLDB_NOTNULL, null, 3.550);
a3d5830a
PS
850 $dbman->add_field($table, $field);
851 $this->assertTrue($dbman->field_exists($table, 'onefloat'));
852 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
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);
a3d5830a
PS
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.
d5ded4e7 865 $this->assertEquals(3.550, round($DB->get_field('test_table1', 'onefloat', array(), IGNORE_MULTIPLE), 7)); // Check default has been applied.
a3d5830a 866
d5ded4e7 867 // Add one char field and check it.
a3d5830a 868 $field = new xmldb_field('onechar');
d5ded4e7 869 $field->set_attributes(XMLDB_TYPE_CHAR, '25', null, XMLDB_NOTNULL, null, 'Nice dflt!');
a3d5830a
PS
870 $dbman->add_field($table, $field);
871 $this->assertTrue($dbman->field_exists($table, 'onechar'));
872 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
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.
a3d5830a
PS
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');
d5ded4e7
PS
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.
a3d5830a
PS
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');
d5ded4e7 905 $this->assertTrue(($columns['mediumtext']->max_length == -1) or ($columns['mediumtext']->max_length >= 16777215)); // -1 means unknown or big.
a3d5830a 906
d5ded4e7 907 // Add one small text field and check it.
a3d5830a
PS
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');
d5ded4e7 912 $this->assertTrue(($columns['smalltext']->max_length == -1) or ($columns['smalltext']->max_length >= 65535)); // -1 means unknown or big.
a3d5830a 913
d5ded4e7 914 // Add one binary field and check it.
a3d5830a
PS
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');
d5ded4e7
PS
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);
a3d5830a
PS
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() {
d5ded4e7 937 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
938 $dbman = $this->tdb->get_manager();
939
940 $table = $this->create_deftable('test_table0');
941
d5ded4e7 942 // Fill the table with some records before dropping fields.
a3d5830a
PS
943 $this->fill_deftable('test_table0');
944
d5ded4e7
PS
945 // Drop field with simple xmldb_field having indexes, must return exception.
946 $field = new xmldb_field('type'); // Field has indexes and default clause.
a3d5830a
PS
947 $this->assertTrue($dbman->field_exists($table, 'type'));
948 try {
949 $dbman->drop_field($table, $field);
d5ded4e7
PS
950 $this->fail('Exception expected');
951 } catch (moodle_exception $e) {
952 $this->assertInstanceOf('ddl_dependency_exception', $e);
a3d5830a 953 }
d5ded4e7 954 $this->assertTrue($dbman->field_exists($table, 'type')); // Continues existing, drop aborted.
a3d5830a 955
d5ded4e7
PS
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.
a3d5830a
PS
958 $this->assertTrue($dbman->field_exists($table, $field));
959 try {
960 $dbman->drop_field($table, $field);
d5ded4e7
PS
961 $this->fail('Exception expected');
962 } catch (moodle_exception $e) {
963 $this->assertInstanceOf('ddl_dependency_exception', $e);
a3d5830a 964 }
d5ded4e7 965 $this->assertTrue($dbman->field_exists($table, $field)); // Continues existing, drop aborted.
a3d5830a 966
d5ded4e7 967 // Drop one non-existing field, must return exception.
a3d5830a
PS
968 $field = new xmldb_field('nonexistingfield');
969 $this->assertFalse($dbman->field_exists($table, $field));
970 try {
971 $dbman->drop_field($table, $field);
d5ded4e7
PS
972 $this->fail('Exception expected');
973 } catch (moodle_exception $e) {
974 $this->assertInstanceOf('ddl_field_missing_exception', $e);
a3d5830a
PS
975 }
976
d5ded4e7
PS
977 // Drop field with simple xmldb_field, not having related indexes.
978 $field = new xmldb_field('forcesubscribe'); // Field has default clause.
a3d5830a
PS
979 $this->assertTrue($dbman->field_exists($table, 'forcesubscribe'));
980 $dbman->drop_field($table, $field);
981 $this->assertFalse($dbman->field_exists($table, 'forcesubscribe'));
982
d5ded4e7
PS
983 // Drop field with complete xmldb_field object, not having related indexes.
984 $field = new xmldb_field('trackingtype'); // Field has default clause.
a3d5830a
PS
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() {
d5ded4e7 994 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
995 $dbman = $this->tdb->get_manager();
996
d5ded4e7
PS
997 // Create table with indexed field and not indexed field to
998 // perform tests in both fields, both having defaults.
a3d5830a 999 $table = new xmldb_table('test_table_cust0');
d5ded4e7
PS
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');
a3d5830a
PS
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
d5ded4e7 1012 // Change column from integer to varchar. Must return exception because of dependent index.
a3d5830a
PS
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);
d5ded4e7
PS
1017 $this->fail('Exception expected');
1018 } catch (moodle_exception $e) {
1019 $this->assertInstanceOf('ddl_dependency_exception', $e);
a3d5830a 1020 }
d5ded4e7 1021 // Column continues being integer 10 not null default 2.
a3d5830a 1022 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1023 $this->assertSame('I', $columns['onenumber']->meta_type);
1024 // TODO: check the rest of attributes.
a3d5830a 1025
d5ded4e7 1026 // Change column from integer to varchar. Must work because column has no dependencies.
a3d5830a
PS
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);
d5ded4e7 1030 // Column is char 30 not null default 'test' now.
a3d5830a 1031 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1032 $this->assertSame('C', $columns['anothernumber']->meta_type);
1033 // TODO: check the rest of attributes.
a3d5830a 1034
d5ded4e7 1035 // Change column back from char to integer.
a3d5830a 1036 $field = new xmldb_field('anothernumber');
d5ded4e7 1037 $field->set_attributes(XMLDB_TYPE_INTEGER, '8', null, XMLDB_NOTNULL, null, '5');
a3d5830a 1038 $dbman->change_field_type($table, $field);
d5ded4e7 1039 // Column is integer 8 not null default 5 now.
a3d5830a 1040 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1041 $this->assertSame('I', $columns['anothernumber']->meta_type);
1042 // TODO: check the rest of attributes.
a3d5830a 1043
d5ded4e7 1044 // Change column once more from integer to char.
a3d5830a
PS
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);
d5ded4e7 1048 // Column is char 30 not null default "test'n drop" now.
a3d5830a 1049 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1050 $this->assertSame('C', $columns['anothernumber']->meta_type);
1051 // TODO: check the rest of attributes.
a3d5830a 1052
d5ded4e7 1053 // Insert one string value and try to convert to integer. Must throw exception.
a3d5830a
PS
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');
d5ded4e7 1059 $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '5');
a3d5830a
PS
1060 try {
1061 $dbman->change_field_type($table, $field);
d5ded4e7
PS
1062 $this->fail('Exception expected');
1063 } catch (moodle_exception $e) {
1064 $this->assertInstanceOf('ddl_change_structure_exception', $e);
a3d5830a 1065 }
d5ded4e7
PS
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.
a3d5830a 1070
d5ded4e7 1071 // Change the column from varchar to float.
a3d5830a 1072 $field = new xmldb_field('anothernumber');
d5ded4e7 1073 $field->set_attributes(XMLDB_TYPE_FLOAT, '20,10', null, null, null, null);
a3d5830a 1074 $dbman->change_field_type($table, $field);
d5ded4e7 1075 // Column is float 20,10 null default null.
a3d5830a 1076 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1077 $this->assertSame('N', $columns['anothernumber']->meta_type); // Floats are seen as number.
1078 // TODO: check the rest of attributes.
a3d5830a 1079
d5ded4e7 1080 // Change the column back from float to varchar.
a3d5830a
PS
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);
d5ded4e7 1084 // Column is char 20 not null default "test" now.
a3d5830a 1085 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1086 $this->assertSame('C', $columns['anothernumber']->meta_type);
1087 // TODO: check the rest of attributes.
a3d5830a 1088
d5ded4e7 1089 // Change the column from varchar to number.
a3d5830a 1090 $field = new xmldb_field('anothernumber');
d5ded4e7 1091 $field->set_attributes(XMLDB_TYPE_NUMBER, '20,10', null, null, null, null);
a3d5830a 1092 $dbman->change_field_type($table, $field);
d5ded4e7 1093 // Column is number 20,10 null default null now.
a3d5830a 1094 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1095 $this->assertSame('N', $columns['anothernumber']->meta_type);
1096 // TODO: check the rest of attributes.
a3d5830a 1097
d5ded4e7 1098 // Change the column from number to integer.
a3d5830a 1099 $field = new xmldb_field('anothernumber');
d5ded4e7 1100 $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, null, null, null);
a3d5830a 1101 $dbman->change_field_type($table, $field);
d5ded4e7 1102 // Column is integer 2 null default null now.
a3d5830a 1103 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1104 $this->assertSame('I', $columns['anothernumber']->meta_type);
1105 // TODO: check the rest of attributes.
a3d5830a 1106
d5ded4e7 1107 // Change the column from integer to text.
a3d5830a
PS
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);
d5ded4e7 1111 // Column is char text not null default null.
a3d5830a 1112 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7 1113 $this->assertSame('X', $columns['anothernumber']->meta_type);
a3d5830a 1114
d5ded4e7 1115 // Change the column back from text to number.
a3d5830a 1116 $field = new xmldb_field('anothernumber');
d5ded4e7 1117 $field->set_attributes(XMLDB_TYPE_NUMBER, '20,10', null, null, null, null);
a3d5830a 1118 $dbman->change_field_type($table, $field);
d5ded4e7 1119 // Column is number 20,10 null default null now.
a3d5830a 1120 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1121 $this->assertSame('N', $columns['anothernumber']->meta_type);
1122 // TODO: check the rest of attributes.
a3d5830a 1123
d5ded4e7 1124 // Change the column from number to text.
a3d5830a
PS
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);
d5ded4e7 1128 // Column is char text not null default "test" now.
a3d5830a 1129 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1130 $this->assertSame('X', $columns['anothernumber']->meta_type);
1131 // TODO: check the rest of attributes.
a3d5830a 1132
d5ded4e7 1133 // Change the column back from text to integer.
a3d5830a 1134 $field = new xmldb_field('anothernumber');
d5ded4e7 1135 $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 10);
a3d5830a 1136 $dbman->change_field_type($table, $field);
d5ded4e7 1137 // Column is integer 10 not null default 10.
a3d5830a 1138 $columns = $DB->get_columns('test_table_cust0');
d5ded4e7
PS
1139 $this->assertSame('I', $columns['anothernumber']->meta_type);
1140 // TODO: check the rest of attributes.
a3d5830a 1141
d5ded4e7 1142 // Check original value has survived to all the type changes.
a3d5830a 1143 $this->assertnotEmpty($rec = $DB->get_record('test_table_cust0', array('id' => $recoriginal)));
d5ded4e7 1144 $this->assertEquals(4, $rec->anothernumber);
a3d5830a
PS
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() {
d5ded4e7 1154 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1155 $dbman = $this->tdb->get_manager();
1156
1157 $table = $this->create_deftable('test_table1');
1158
d5ded4e7 1159 // Fill the table with some records before dropping fields.
a3d5830a
PS
1160 $this->fill_deftable('test_table1');
1161
d5ded4e7 1162 // Change text field from medium to big.
a3d5830a
PS
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');
d5ded4e7
PS
1167 // Cannot check the text type, only the metatype.
1168 $this->assertSame('X', $columns['intro']->meta_type);
1169 // TODO: check the rest of attributes.
a3d5830a 1170
d5ded4e7 1171 // Change char field from 30 to 20.
a3d5830a
PS
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');
d5ded4e7
PS
1176 $this->assertSame('C', $columns['secondname']->meta_type);
1177 // TODO: check the rest of attributes.
a3d5830a 1178
d5ded4e7 1179 // Change char field from 20 to 10, having contents > 10cc. Throw exception.
a3d5830a
PS
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);
d5ded4e7
PS
1184 $this->fail('Exception expected');
1185 } catch (moodle_exception $e) {
1186 $this->assertInstanceOf('ddl_change_structure_exception', $e);
a3d5830a 1187 }
d5ded4e7 1188 // No changes in field specs at all.
a3d5830a 1189 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
1190 $this->assertSame('C', $columns['secondname']->meta_type);
1191 // TODO: check the rest of attributes.
a3d5830a 1192
d5ded4e7 1193 // Change number field from 20,10 to 10,2.
a3d5830a
PS
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');
d5ded4e7
PS
1198 $this->assertSame('N', $columns['grade']->meta_type);
1199 // TODO: check the rest of attributes.
a3d5830a 1200
d5ded4e7 1201 // Change integer field from 10 to 2.
a3d5830a 1202 $field = new xmldb_field('userid');
d5ded4e7 1203 $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS
1204 $dbman->change_field_precision($table, $field);
1205 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
1206 $this->assertSame('I', $columns['userid']->meta_type);
1207 // TODO: check the rest of attributes.
a3d5830a 1208
d5ded4e7 1209 // Change the column from integer (2) to integer (6) (forces change of type in some DBs).
a3d5830a 1210 $field = new xmldb_field('userid');
d5ded4e7 1211 $field->set_attributes(XMLDB_TYPE_INTEGER, '6', null, null, null, null);
a3d5830a 1212 $dbman->change_field_precision($table, $field);
d5ded4e7 1213 // Column is integer 6 null default null now.
a3d5830a 1214 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
1215 $this->assertSame('I', $columns['userid']->meta_type);
1216 // TODO: check the rest of attributes.
a3d5830a 1217
d5ded4e7 1218 // Insert one record with 6-digit field.
a3d5830a
PS
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);
d5ded4e7 1225 // Change integer field from 6 to 2, contents are bigger, must throw exception.
a3d5830a 1226 $field = new xmldb_field('userid');
d5ded4e7 1227 $field->set_attributes(XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS
1228 try {
1229 $dbman->change_field_precision($table, $field);
d5ded4e7
PS
1230 $this->fail('Exception expected');
1231 } catch (moodle_exception $e) {
1232 $this->assertInstanceOf('ddl_change_structure_exception', $e);
a3d5830a 1233 }
d5ded4e7 1234 // No changes in field specs at all.
a3d5830a 1235 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
1236 $this->assertSame('I', $columns['userid']->meta_type);
1237 // TODO: check the rest of attributes.
a3d5830a 1238
d5ded4e7 1239 // Change integer field from 10 to 3, in field used by index. must throw exception.
a3d5830a 1240 $field = new xmldb_field('course');
d5ded4e7 1241 $field->set_attributes(XMLDB_TYPE_INTEGER, '3', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS
1242 try {
1243 $dbman->change_field_precision($table, $field);
d5ded4e7
PS
1244 $this->fail('Exception expected');
1245 } catch (moodle_exception $e) {
1246 $this->assertInstanceOf('ddl_dependency_exception', $e);
a3d5830a 1247 }
d5ded4e7 1248 // No changes in field specs at all.
a3d5830a 1249 $columns = $DB->get_columns('test_table1');
d5ded4e7
PS
1250 $this->assertSame('I', $columns['course']->meta_type);
1251 // TODO: check the rest of attributes.
a3d5830a
PS
1252 }
1253
1254 public function testChangeFieldNullability() {
d5ded4e7 1255 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1256 $dbman = $this->tdb->get_manager();
1257
1258 $table = new xmldb_table('test_table_cust0');
d5ded4e7 1259 $table->add_field('id', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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();
d5ded4e7 1265 $record->name = null;
a3d5830a 1266
a3d5830a
PS
1267 try {
1268 $result = $DB->insert_record('test_table_cust0', $record, false);
1269 } catch (dml_exception $e) {
1270 $result = false;
1271 }
d9ba3c4e 1272 $this->resetDebugging();
a3d5830a
PS
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
d5ded4e7 1281 // TODO: add some tests with existing data in table.
a3d5830a
PS
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
a3d5830a
PS
1288 try {
1289 $result = $DB->insert_record('test_table_cust0', $record, false);
1290 } catch (dml_exception $e) {
1291 $result = false;
1292 }
d9ba3c4e 1293 $this->resetDebugging();
a3d5830a
PS
1294 $this->assertFalse($result);
1295
1296 $dbman->drop_table($table);
1297 }
1298
1299 public function testChangeFieldDefault() {
d5ded4e7 1300 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1301 $dbman = $this->tdb->get_manager();
1302
1303 $table = new xmldb_table('test_table_cust0');
d5ded4e7
PS
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');
a3d5830a
PS
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));
d5ded4e7 1319 $this->assertSame('Moodle2', $record->name);
a3d5830a
PS
1320
1321 $field = new xmldb_field('onenumber');
d5ded4e7 1322 $field->set_attributes(XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 666);
a3d5830a
PS
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));
d5ded4e7 1330 $this->assertSame('666', $record->onenumber);
a3d5830a
PS
1331
1332 $dbman->drop_table($table);
1333 }
1334
1335 public function testAddUniqueIndex() {
d5ded4e7 1336 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1337 $dbman = $this->tdb->get_manager();
1338
1339 $table = new xmldb_table('test_table_cust0');
d5ded4e7
PS
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');
a3d5830a
PS
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
a3d5830a
PS
1355 try {
1356 $result = $DB->insert_record('test_table_cust0', $record, false);
1357 } catch (dml_exception $e) {
0e35ba6f 1358 $result = false;
a3d5830a 1359 }
d9ba3c4e 1360 $this->resetDebugging();
a3d5830a
PS
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);
a6d9d4ef 1373 $this->assertTrue($dbman->index_exists($table, $index));
02e5f81c
PS
1374
1375 try {
1376 $dbman->add_index($table, $index);
1377 $this->fail('Exception expected for duplicate indexes');
d5ded4e7 1378 } catch (moodle_exception $e) {
02e5f81c
PS
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');
d5ded4e7 1387 } catch (moodle_exception $e) {
02e5f81c
PS
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');
d5ded4e7 1401 } catch (moodle_exception $e) {
02e5f81c
PS
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');
d5ded4e7 1415 } catch (moodle_exception $e) {
02e5f81c
PS
1416 $this->assertInstanceOf('coding_exception', $e);
1417 }
1418
a3d5830a
PS
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
d5ded4e7 1429 // DBM Systems name their indices differently - do not test the actual index name.
a3d5830a
PS
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() {
d5ded4e7 1439 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
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
d5ded4e7
PS
1451 // Test we are able to drop indexes having hyphens MDL-22804.
1452 // Create index with hyphens (by hand).
a3d5830a
PS
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));
d5ded4e7 1464 // Index created, let's drop it using db manager stuff.
a3d5830a
PS
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);
a6d9d4ef
PS
1477
1478 // No easy way to test it, this just makes sure no errors are encountered.
1479 $this->assertTrue(true);
a3d5830a
PS
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);
a6d9d4ef
PS
1491
1492 // No easy way to test it, this just makes sure no errors are encountered.
1493 $this->assertTrue(true);
a3d5830a
PS
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);
a6d9d4ef
PS
1507
1508 // No easy way to test it, this just makes sure no errors are encountered.
1509 $this->assertTrue(true);
a3d5830a
PS
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);
a6d9d4ef
PS
1521
1522 // No easy way to test it, this just makes sure no errors are encountered.
1523 $this->assertTrue(true);
a3d5830a
PS
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);
a6d9d4ef
PS
1537
1538 // No easy way to test it, this just makes sure no errors are encountered.
1539 $this->assertTrue(true);
a3d5830a
PS
1540 }
1541
1542 public function testRenameField() {
d5ded4e7 1543 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
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
66c34d35
MS
1550 // 1. Rename the 'type' field into a generic new valid name.
1551 // This represents the standard use case.
a3d5830a
PS
1552 $dbman->rename_field($table, $field, 'newfieldname');
1553
1554 $columns = $DB->get_columns('test_table0');
1555
d5ded4e7
PS
1556 $this->assertArrayNotHasKey('type', $columns);
1557 $this->assertArrayHasKey('newfieldname', $columns);
66c34d35
MS
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);
a3d5830a
PS
1589 }
1590
a3d5830a 1591 public function testIndexExists() {
d5ded4e7 1592 // Skipping: this is just a test of find_index_name.
a3d5830a
PS
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
d5ded4e7 1601 // With Mysql, the return value is actually "mdl_test_id_pk".
a3d5830a
PS
1602 $result = $dbman->find_key_name($table, $key);
1603 $this->assertTrue(!empty($result));
1604 }
1605
1606 public function testDeleteTablesFromXmldbFile() {
a3d5830a
PS
1607 $dbman = $this->tdb->get_manager();
1608
1609 $this->create_deftable('test_table1');
1610
1611 $this->assertTrue($dbman->table_exists('test_table1'));
1612
d5ded4e7 1613 // Feed nonexistent file.
a3d5830a 1614 try {
a3d5830a 1615 $dbman->delete_tables_from_xmldb_file('fpsoiudfposui');
d5ded4e7
PS
1616 $this->fail('Exception expected');
1617 } catch (moodle_exception $e) {
d9ba3c4e 1618 $this->resetDebugging();
d5ded4e7 1619 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS
1620 }
1621
a3d5830a 1622 try {
a3d5830a 1623 $dbman->delete_tables_from_xmldb_file(__DIR__ . '/fixtures/invalid.xml');
d5ded4e7
PS
1624 $this->fail('Exception expected');
1625 } catch (moodle_exception $e) {
d9ba3c4e 1626 $this->resetDebugging();
d5ded4e7 1627 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a 1628 }
a3d5830a 1629
d5ded4e7 1630 // Check that the table has not been deleted from DB.
a3d5830a
PS
1631 $this->assertTrue($dbman->table_exists('test_table1'));
1632
d5ded4e7
PS
1633 // Real and valid xml file.
1634 // TODO: drop UNSINGED completely in Moodle 2.4.
a3d5830a
PS
1635 $dbman->delete_tables_from_xmldb_file(__DIR__ . '/fixtures/xmldb_table.xml');
1636
d5ded4e7 1637 // Check that the table has been deleted from DB.
a3d5830a
PS
1638 $this->assertFalse($dbman->table_exists('test_table1'));
1639 }
1640
1641 public function testInstallFromXmldbFile() {
a3d5830a
PS
1642 $dbman = $this->tdb->get_manager();
1643
d5ded4e7 1644 // Feed nonexistent file.
a3d5830a 1645 try {
a3d5830a 1646 $dbman->install_from_xmldb_file('fpsoiudfposui');
d5ded4e7
PS
1647 $this->fail('Exception expected');
1648 } catch (moodle_exception $e) {
d9ba3c4e 1649 $this->resetDebugging();
d5ded4e7 1650 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS
1651 }
1652
a3d5830a 1653 try {
a3d5830a 1654 $dbman->install_from_xmldb_file(__DIR__ . '/fixtures/invalid.xml');
d5ded4e7
PS
1655 $this->fail('Exception expected');
1656 } catch (moodle_exception $e) {
d9ba3c4e 1657 $this->resetDebugging();
d5ded4e7 1658 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a 1659 }
a3d5830a 1660
d5ded4e7 1661 // Check that the table has not yet been created in DB.
a3d5830a
PS
1662 $this->assertFalse($dbman->table_exists('test_table1'));
1663
d5ded4e7 1664 // Real and valid xml file.
a3d5830a
PS
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() {
d5ded4e7 1670 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1671 $dbman = $this->tdb->get_manager();
1672
d5ded4e7 1673 // Create temp table0.
a3d5830a
PS
1674 $table0 = $this->tables['test_table0'];
1675 $dbman->create_temp_table($table0);
1676 $this->assertTrue($dbman->table_exists('test_table0'));
1677
d5ded4e7 1678 // Try to create temp table with same name, must throw exception.
a3d5830a
PS
1679 $dupetable = $this->tables['test_table0'];
1680 try {
1681 $dbman->create_temp_table($dupetable);
d5ded4e7
PS
1682 $this->fail('Exception expected');
1683 } catch (moodle_exception $e) {
1684 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
1685 }
1686
d5ded4e7 1687 // Try to create table with same name, must throw exception.
a3d5830a
PS
1688 $dupetable = $this->tables['test_table0'];
1689 try {
1690 $dbman->create_table($dupetable);
d5ded4e7
PS
1691 $this->fail('Exception expected');
1692 } catch (moodle_exception $e) {
1693 $this->assertInstanceOf('ddl_exception', $e);
a3d5830a
PS
1694 }
1695
d5ded4e7 1696 // Create another temp table1.
a3d5830a
PS
1697 $table1 = $this->tables['test_table1'];
1698 $dbman->create_temp_table($table1);
1699 $this->assertTrue($dbman->table_exists('test_table1'));
1700
d5ded4e7 1701 // Get columns and perform some basic tests.
a3d5830a 1702 $columns = $DB->get_columns('test_table1');
d5ded4e7 1703 $this->assertCount(11, $columns);
a3d5830a 1704 $this->assertTrue($columns['name'] instanceof database_column_info);
d5ded4e7 1705 $this->assertEquals(30, $columns['name']->max_length);
a3d5830a 1706 $this->assertTrue($columns['name']->has_default);
d5ded4e7 1707 $this->assertEquals('Moodle', $columns['name']->default_value);
a3d5830a 1708
d5ded4e7 1709 // Insert some records.
a3d5830a
PS
1710 $inserted = $this->fill_deftable('test_table1');
1711 $records = $DB->get_records('test_table1');
d5ded4e7
PS
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);
a3d5830a 1716
8343b340
RS
1717 // Collect statistics about the data in the temp table.
1718 $DB->update_temp_table_stats();
1719
d5ded4e7 1720 // Drop table1.
a66b2ae4 1721 $dbman->drop_table($table1);
a3d5830a
PS
1722 $this->assertFalse($dbman->table_exists('test_table1'));
1723
d5ded4e7 1724 // Try to drop non-existing temp table, must throw exception.
a3d5830a
PS
1725 $noetable = $this->tables['test_table1'];
1726 try {
a66b2ae4 1727 $dbman->drop_table($noetable);
d5ded4e7
PS
1728 $this->fail('Exception expected');
1729 } catch (moodle_exception $e) {
1730 $this->assertInstanceOf('ddl_table_missing_exception', $e);
a3d5830a
PS
1731 }
1732
8343b340
RS
1733 // Collect statistics about the data in the temp table with less tables.
1734 $DB->update_temp_table_stats();
1735
d5ded4e7 1736 // Fill/modify/delete a few table0 records.
a3d5830a 1737
d5ded4e7 1738 // Drop table0.
a66b2ae4 1739 $dbman->drop_table($table0);
a3d5830a
PS
1740 $this->assertFalse($dbman->table_exists('test_table0'));
1741
d5ded4e7 1742 // Create another temp table1.
9b3323b8
PS
1743 $table1 = $this->tables['test_table1'];
1744 $dbman->create_temp_table($table1);
1745 $this->assertTrue($dbman->table_exists('test_table1'));
1746
d5ded4e7 1747 // Make sure it can be dropped using deprecated drop_temp_table().
a66b2ae4 1748 $dbman->drop_temp_table($table1);
9b3323b8 1749 $this->assertFalse($dbman->table_exists('test_table1'));
a3b6e311 1750 $this->assertDebuggingCalled();
b8691243
PS
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);
c1dc3f47
RT
1780
1781 // Drop temp table.
a9950b49 1782 $dbman->drop_table($table2);
c1dc3f47 1783 $this->assertFalse($dbman->table_exists('test_temp'));
a3d5830a
PS
1784 }
1785
1786 public function test_concurrent_temp_tables() {
d5ded4e7 1787 $DB = $this->tdb; // Do not use global $DB!
a3d5830a
PS
1788 $dbman = $this->tdb->get_manager();
1789
d5ded4e7 1790 // Define 2 records.
a3d5830a
PS
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
d5ded4e7 1800 // Create temp table1 and insert 1 record (in DB).
a3d5830a
PS
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
d5ded4e7 1806 // Switch to new connection.
a3d5830a
PS
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();
d5ded4e7 1814 $this->assertFalse($dbman2->table_exists('test_table1')); // Temp table not exists in DB2.
a3d5830a 1815
d5ded4e7 1816 // Create temp table1 and insert 1 record (in DB2).
a3d5830a
PS
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
d5ded4e7 1822 $dbman2->drop_table($table); // Drop temp table before closing DB2.
a3d5830a 1823 $this->assertFalse($dbman2->table_exists('test_table1'));
d5ded4e7 1824 $DB2->dispose(); // Close DB2.
a3d5830a 1825
d5ded4e7
PS
1826 $this->assertTrue($dbman->table_exists('test_table1')); // Check table continues existing for DB.
1827 $dbman->drop_table($table); // Drop temp table.
a3d5830a
PS
1828 $this->assertFalse($dbman->table_exists('test_table1'));
1829 }
1830
934c2ee1
SR
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
94e25092
SR
1897 // Delete the leftover temp table.
1898 $dbman->drop_table($table1);
934c2ee1
SR
1899 }
1900
a3d5830a
PS
1901 public function test_reset_sequence() {
1902 $DB = $this->tdb;
1903 $dbman = $DB->get_manager();
1904
1905 $table = new xmldb_table('testtable');
d5ded4e7
PS
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');
a3d5830a
PS
1908 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1909
d5ded4e7 1910 // Drop if exists.
a3d5830a
PS
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);
d5ded4e7 1920 $DB->delete_records('testtable'); // This delete performs one TRUNCATE.
7823af09 1921
d5ded4e7 1922 $dbman->reset_sequence($table); // Using xmldb object.
7823af09
EL
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);
d5ded4e7 1927 $DB->delete_records('testtable', array()); // This delete performs one DELETE.
a3d5830a 1928
d5ded4e7 1929 $dbman->reset_sequence($table); // Using xmldb object.
5d4c8256
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.');
a3d5830a
PS
1932
1933 $DB->import_record('testtable', $record);
d5ded4e7 1934 $dbman->reset_sequence($tablename); // Using string.
a3d5830a
PS
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
bd991d03
PS
1945 public function test_index_hints() {
1946 $DB = $this->tdb;
1947 $dbman = $DB->get_manager();
1948
1949 $table = new xmldb_table('testtable');
d5ded4e7 1950 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
bd991d03
PS
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
d5ded4e7 1957 // Drop if exists.
bd991d03
PS
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));
068cf0e5
1972
1973 // Try unique indexes too.
1974 $dbman->drop_table($this->tables[$tablename]);
1975
1976 $table = new xmldb_table('testtable');
d5ded4e7 1977 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
068cf0e5
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));
bd991d03
PS
1987 }
1988
a3d5830a
PS
1989 public function test_index_max_bytes() {
1990 $DB = $this->tdb;
1991 $dbman = $DB->get_manager();
1992
1993 $maxstr = '';
d5ded4e7
PS
1994 for ($i=0; $i<255; $i++) {
1995 $maxstr .= '言'; // Random long string that should fix exactly the limit for one char column.
a3d5830a
PS
1996 }
1997
1998 $table = new xmldb_table('testtable');
d5ded4e7 1999 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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
d5ded4e7 2004 // Drop if exists.
a3d5830a
PS
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));
d5ded4e7 2019 $this->assertSame($maxstr, $rec->name);
a3d5830a
PS
2020
2021 $dbman->drop_table($table);
2022
a3d5830a 2023 $table = new xmldb_table('testtable');
d5ded4e7 2024 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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);
d5ded4e7
PS
2031 $this->fail('Exception expected');
2032 } catch (moodle_exception $e) {
2033 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
2034 }
2035 }
2036
2037 public function test_index_composed_max_bytes() {
2038 $DB = $this->tdb;
2039 $dbman = $DB->get_manager();
2040
2041 $maxstr = '';
d5ded4e7 2042 for ($i=0; $i<200; $i++) {
a3d5830a
PS
2043 $maxstr .= '言';
2044 }
2045 $reststr = '';
d5ded4e7 2046 for ($i=0; $i<133; $i++) {
a3d5830a
PS
2047 $reststr .= '言';
2048 }
2049
2050 $table = new xmldb_table('testtable');
d5ded4e7 2051 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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'));
d5ded4e7 2055 $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE, array('name1', 'name2'));
a3d5830a 2056
d5ded4e7 2057 // Drop if exists.
a3d5830a
PS
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));
d5ded4e7
PS
2073 $this->assertSame($maxstr, $rec->name1);
2074 $this->assertSame($reststr, $rec->name2);
a3d5830a
PS
2075
2076 $table = new xmldb_table('testtable');
d5ded4e7 2077 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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'));
d5ded4e7 2081 $table->add_index('name1-name2', XMLDB_INDEX_NOTUNIQUE, array('name1', 'name2'));
a3d5830a 2082
d5ded4e7 2083 // Drop if exists.
a3d5830a
PS
2084 if ($dbman->table_exists($table)) {
2085 $dbman->drop_table($table);
2086 }
2087
2088 try {
2089 $dbman->create_table($table);
d5ded4e7
PS
2090 $this->fail('Exception expected');
2091 } catch (moodle_exception $e) {
2092 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
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');
d5ded4e7 2101 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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
d5ded4e7 2105 // Drop if exists.
a3d5830a
PS
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
d5ded4e7 2113 // This has to work in all DBs.
a3d5830a 2114 $maxstr = '';
d5ded4e7
PS
2115 for ($i=0; $i<xmldb_field::CHAR_MAX_LENGTH; $i++) {
2116 $maxstr .= 'a'; // Ascii only.
a3d5830a
PS
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));
d5ded4e7 2126 $this->assertSame($maxstr, $rec->name);
a3d5830a 2127
d5ded4e7 2128 // Following test is supposed to fail in oracle.
a3d5830a 2129 $maxstr = '';
d5ded4e7
PS
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.
a3d5830a
PS
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));
d5ded4e7 2142 $this->assertSame($maxstr, $rec->name);
a3d5830a
PS
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
a3d5830a 2151 $table = new xmldb_table('testtable');
d5ded4e7 2152 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS
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
d5ded4e7 2156 // Drop if exists.
a3d5830a
PS
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);
d5ded4e7
PS
2165 $this->fail('Exception expected');
2166 } catch (moodle_exception $e) {
2167 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS
2168 }
2169 }
2170
a9d11e3c
MG
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 }
cd729dc9
EM
2221
2222 // Now test to confirm that a duplicate name isn't issued, even if they come from different root names.
2223 // Move to a new field.
2224 $fields = "fl";
2225
2226 // Insert twice, moving is to a key with fl2.
2227 $this->assertEquals($gen->names_max_length - 1, strlen($gen->getNameForObject($table, $fields, $suffix)));
2228 $result1 = $gen->getNameForObject($table, $fields, $suffix);
2229
2230 // Make sure we end up with _fl2_ in the result.
2231 $this->assertRegExp('/_fl2_/', $result1);
2232
2233 // Now, use a field that would result in the same key if it wasn't already taken.
2234 $fields = "fl2";
2235 // Because we are now at the max key length, it will try:
2236 // - _fl2_ (the natural name)
2237 // - _fl2_ (removing the original 2, and adding a counter 2)
2238 // - then settle on _fl3_.
2239 $result2 = $gen->getNameForObject($table, $fields, $suffix);
2240 $this->assertRegExp('/_fl3_/', $result2);
2241
2242 // Make sure they don't match.
2243 $this->assertNotEquals($result1, $result2);
2244 // But are only different in the way we expect. This confirms the test is working properly.
2245 $this->assertEquals(str_replace('_fl2_', '', $result1), str_replace('_fl3_', '', $result2));
2246
2247 // Now go back. We would expect the next result to be fl3 again, but it is taken, so it should move to fl4.
2248 $fields = "fl";
2249 $result3 = $gen->getNameForObject($table, $fields, $suffix);
2250
2251 $this->assertNotEquals($result2, $result3);
2252 $this->assertRegExp('/_fl4_/', $result3);
a9d11e3c
MG
2253 }
2254 }
2255
596f4e6a
MS
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
211d04f4 2276 * @param bool $reserved Whether the column name is reserved or not.
596f4e6a
MS
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
211d04f4
MS
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
5abc4313
TH
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
d5ded4e7 2419 // Following methods are not supported == Do not test.
a3d5830a
PS
2420 /*
2421 public function testRenameIndex() {
d5ded4e7 2422 // Unsupported!
a3d5830a
PS
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() {
d5ded4e7 2433 // Unsupported!
a3d5830a
PS
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
a8c38ce8
MN
2444 /**
2445 * Tests check_database_schema().
2446 */
2447 public function test_check_database_schema() {
2448 global $CFG, $DB;
2449
2450 $dbmanager = $DB->get_manager();
2451
2452 // Create a table in the database we will be using to compare with a schema.
2453 $table = new xmldb_table('test_check_db_schema');
2454 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2455 $table->add_field('extracolumn', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2456 $table->add_field('courseid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2457 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
47f3fa13 2458 $table->add_key('extraindex', XMLDB_KEY_UNIQUE, array('extracolumn'));
a8c38ce8
MN
2459 $table->setComment("This is a test table, you can drop it safely.");
2460 $dbmanager->create_table($table);
2461
2462 // Remove the column so it is not added to the schema and gets reported as an extra column.
2463 $table->deleteField('extracolumn');
2464
2465 // Change the 'courseid' field to a float in the schema so it gets reported as different.
2466 $table->deleteField('courseid');
2467 $table->add_field('courseid', XMLDB_TYPE_NUMBER, '10, 2', null, XMLDB_NOTNULL, null, null);
2468
2469 // Add another column to the schema that won't be present in the database and gets reported as missing.
2470 $table->add_field('missingcolumn', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
2471
2472 // Add another key to the schema that won't be present in the database and gets reported as missing.
2473 $table->add_key('missingkey', XMLDB_KEY_FOREIGN, array('courseid'), 'course', array('id'));
2474
47f3fa13
MN
2475 // Remove the key from the schema which will still be present in the database and reported as extra.
2476 $table->deleteKey('extraindex');
2477
a8c38ce8
MN
2478 $schema = new xmldb_structure('testschema');
2479 $schema->addTable($table);
2480
2481 // Things we want to check for -
2482 // 1. Changed columns.
2483 // 2. Missing columns.
2484 // 3. Missing indexes.
47f3fa13
MN
2485 // 4. Unexpected index.
2486 // 5. Extra columns.
a8c38ce8 2487 $errors = $dbmanager->check_database_schema($schema)['test_check_db_schema'];
d584d349
EL
2488 // Preprocess $errors to get rid of the non compatible (SQL-dialect dependent) parts.
2489 array_walk($errors, function(&$error) {
2490 $error = trim(strtok($error, PHP_EOL));
2491 });
47f3fa13 2492 $this->assertCount(5, $errors);
a8c38ce8
MN
2493 $this->assertContains("column 'courseid' has incorrect type 'I', expected 'N'", $errors);
2494 $this->assertContains("column 'missingcolumn' is missing", $errors);
d584d349 2495 $this->assertContains("Missing index 'missingkey' (not unique (courseid)).", $errors);
47f3fa13 2496 $this->assertContains("Unexpected index '{$CFG->prefix}testchecdbsche_ext_uix'.", $errors);
a8c38ce8
MN
2497 $this->assertContains("column 'extracolumn' is not expected (I)", $errors);
2498 }
a3d5830a 2499}