[MDL-15300] Remove temporary files created while exporting a preset as ZIP file....
[moodle.git] / lib / dml / simpletest / testdml.php
CommitLineData
73f7ad71 1<?php
2/**
3dce78e1 3 * Unit tests for dml
4 * @package dml
73f7ad71 5 */
6
7if (!defined('MOODLE_INTERNAL')) {
8 die('Direct access to this script is forbidden.'); /// It must be included from a Moodle page
9}
10
3dce78e1 11class dml_test extends UnitTestCase {
73f7ad71 12 private $tables = array();
251387d0 13 private $tdb;
b579f0db 14 private $data;
73f7ad71 15
16 function setUp() {
a230012c 17 global $CFG, $DB, $UNITTEST;
e6b4f00e 18
a230012c 19 if (isset($UNITTEST->func_test_db)) {
251387d0 20 $this->tdb = $UNITTEST->func_test_db;
e6b4f00e 21 } else {
251387d0 22 $this->tdb = $DB;
e6b4f00e 23 }
73f7ad71 24
73f7ad71 25 }
26
27 function tearDown() {
251387d0 28 $dbman = $this->tdb->get_manager();
809df0e2 29
30 foreach ($this->tables as $table) {
251387d0 31 if ($dbman->table_exists($table)) {
32 $dbman->drop_table($table, true, false);
73f7ad71 33 }
34 }
e6b4f00e 35 $this->tables = array();
73f7ad71 36 }
37
38 function test_fix_sql_params() {
7f9f11b5 39 $DB = $this->tdb;
251387d0 40 $dbman = $this->tdb->get_manager();
e6b4f00e 41
b579f0db 42 $table = new xmldb_table("testtable");
43 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
44 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
45 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
46 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
47 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
48 $table->setComment("This is a test'n drop table. You can drop it safely");
49 $dbman->create_table($table);
50 $this->tables[$table->getName()] = $table;
51
52 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
53 array('id' => 2, 'course' => 3, 'name' => 'record2'),
54 array('id' => 3, 'course' => 5, 'name' => 'record3'));
55 foreach ($data as $record) {
56 $DB->insert_record('testtable', $record);
57 }
58
73f7ad71 59 // Malformed table placeholder
60 $sql = "SELECT * FROM [testtable]";
e6b4f00e 61 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 62 $this->assertEqual($sql, $sqlarray[0]);
63
64 // Correct table placeholder substitution
65 $sql = "SELECT * FROM {testtable}";
e6b4f00e 66 $sqlarray = $DB->fix_sql_params($sql);
67 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable", $sqlarray[0]);
73f7ad71 68
69 // Malformed param placeholders
70 $sql = "SELECT * FROM {testtable} WHERE name = ?param1";
b579f0db 71 $params = array('param1' => 'record2');
e6b4f00e 72 $sqlarray = $DB->fix_sql_params($sql, $params);
73 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?param1", $sqlarray[0]);
73f7ad71 74
75 // Mixed param types (colon and dollar)
b579f0db 76 $sql = "SELECT * FROM {testtable} WHERE name = :param1, course = \$1";
77 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 78 try {
e6b4f00e 79 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 80 $this->fail("Expecting an exception, none occurred");
73f7ad71 81 } catch (Exception $e) {
251387d0 82 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 83 }
84
85 // Mixed param types (question and dollar)
b579f0db 86 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = \$1";
87 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 88 try {
e6b4f00e 89 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 90 $this->fail("Expecting an exception, none occurred");
73f7ad71 91 } catch (Exception $e) {
251387d0 92 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 93 }
73f7ad71 94
95 // Too many params in sql
b579f0db 96 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = ?, id = ?";
97 $params = array('record2', 3);
73f7ad71 98 try {
e6b4f00e 99 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 100 $this->fail("Expecting an exception, none occurred");
73f7ad71 101 } catch (Exception $e) {
251387d0 102 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 103 }
73f7ad71 104
105 // Too many params in array: no error
106 $params[] = 1;
107 $params[] = time();
73f7ad71 108 $sqlarray = null;
109
110 try {
e6b4f00e 111 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 112 $this->pass();
73f7ad71 113 } catch (Exception $e) {
b579f0db 114 $this->fail("Unexpected " . get_class($e) . " exception");
73f7ad71 115 }
73f7ad71 116 $this->assertTrue($sqlarray[0]);
117
118 // Named params missing from array
b579f0db 119 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :course";
120 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 121 try {
e6b4f00e 122 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 123 $this->fail("Expecting an exception, none occurred");
73f7ad71 124 } catch (Exception $e) {
251387d0 125 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 126 }
73f7ad71 127
128 // Duplicate named param in query
b579f0db 129 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :name";
130 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 131 try {
e6b4f00e 132 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 133 $this->fail("Expecting an exception, none occurred");
73f7ad71 134 } catch (Exception $e) {
251387d0 135 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 136 }
73f7ad71 137
138 // Unsupported Bound params
b579f0db 139 $sql = "SELECT * FROM {testtable} WHERE name = $1, course = $2";
73f7ad71 140 $params = array('first record', 1);
73f7ad71 141 try {
e6b4f00e 142 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 143 $this->fail("Expecting an exception, none occurred");
73f7ad71 144 } catch (Exception $e) {
251387d0 145 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 146 }
73f7ad71 147
148 // Correct named param placeholders
b579f0db 149 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :course";
150 $params = array('name' => 'first record', 'course' => 1);
e6b4f00e 151 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 152 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?", $sqlarray[0]);
73f7ad71 153 $this->assertEqual(2, count($sqlarray[1]));
154
155 // Correct ? params
b579f0db 156 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = ?";
73f7ad71 157 $params = array('first record', 1);
e6b4f00e 158 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 159 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?", $sqlarray[0]);
73f7ad71 160 $this->assertEqual(2, count($sqlarray[1]));
161
162 }
163
bb78c788 164 public function testGetTables() {
7f9f11b5 165 $DB = $this->tdb;
251387d0 166 $dbman = $this->tdb->get_manager();
a230012c 167
b579f0db 168 $original_count = count($DB->get_tables());
169
bb78c788 170 // Need to test with multiple DBs
b579f0db 171 $table = new xmldb_table("testtable");
172 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
173 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
174 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
175 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
176 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
177 $dbman->create_table($table);
178 $this->tables[$table->getName()] = $table;
179 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 180 }
181
182 public function testGetIndexes() {
7f9f11b5 183 $DB = $this->tdb;
251387d0 184 $dbman = $this->tdb->get_manager();
a230012c 185
b579f0db 186 $table = new xmldb_table("testtable");
187 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
188 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
189 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
190 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
191 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
192 $dbman->create_table($table);
193 $this->tables[$table->getName()] = $table;
194
bb78c788 195 $this->assertTrue($indices = $DB->get_indexes('testtable'));
196 $this->assertTrue(count($indices) == 1);
197
198 $xmldb_indexes = $this->tables['testtable']->getIndexes();
199 $this->assertEqual(count($indices), count($xmldb_indexes));
200
201 for ($i = 0; $i < count($indices); $i++) {
202 if ($i == 0) {
203 $next_index = reset($indices);
204 $next_xmldb_index = reset($xmldb_indexes);
205 } else {
206 $next_index = next($indices);
207 $next_xmldb_index = next($xmldb_indexes);
208 }
209
210 $this->assertEqual($next_index['columns'][0], $next_xmldb_index->name);
211 }
212 }
213
214 public function testGetColumns() {
7f9f11b5 215 $DB = $this->tdb;
251387d0 216 $dbman = $this->tdb->get_manager();
bb78c788 217
b579f0db 218 $table = new xmldb_table("testtable");
219 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
220 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
221 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
222 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
223 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
224 $dbman->create_table($table);
225 $this->tables[$table->getName()] = $table;
226
bb78c788 227 $this->assertTrue($columns = $DB->get_columns('testtable'));
228 $fields = $this->tables['testtable']->getFields();
229 $this->assertEqual(count($columns), count($fields));
230
231 for ($i = 0; $i < count($columns); $i++) {
232 if ($i == 0) {
233 $next_column = reset($columns);
234 $next_field = reset($fields);
235 } else {
236 $next_column = next($columns);
237 $next_field = next($fields);
238 }
239
240 $this->assertEqual($next_column->name, $next_field->name);
241 }
242 }
243
244 public function testExecute() {
7f9f11b5 245 $DB = $this->tdb;
251387d0 246 $dbman = $this->tdb->get_manager();
a230012c 247
b579f0db 248 $table = new xmldb_table("testtable");
249 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
250 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
251 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
252 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
253 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
254 $dbman->create_table($table);
255 $this->tables[$table->getName()] = $table;
256
bb78c788 257 $sql = "SELECT * FROM {testtable}";
b579f0db 258
bb78c788 259 $this->assertTrue($DB->execute($sql));
73f7ad71 260
b579f0db 261 $params = array('course' => 1, 'name' => 'test');
6807d2b3 262
263 $sql = "INSERT INTO {testtable} (".implode(',', array_keys($params)).")
264 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
265
266
267 $this->assertTrue($DB->execute($sql, $params));
bb78c788 268
b579f0db 269 $record = $DB->get_record('testtable', array('id' => 1));
bb78c788 270
6807d2b3 271 foreach ($params as $field => $value) {
bb78c788 272 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
273 }
274 }
275
7f9f11b5 276 public function test_get_in_or_equal() {
277 $DB = $this->tdb;
278
279 // SQL_PARAMS_QM - IN or =
280
281 // Correct usage of multiple values
282 $in_values = array('value1', 'value2', 'value3', 'value4');
283 list($usql, $params) = $DB->get_in_or_equal($in_values);
284 $this->assertEqual("IN (?,?,?,?)", $usql);
285 $this->assertEqual(4, count($params));
286 foreach ($params as $key => $value) {
287 $this->assertEqual($in_values[$key], $value);
288 }
289
290 // Correct usage of single value (in an array)
291 $in_values = array('value1');
292 list($usql, $params) = $DB->get_in_or_equal($in_values);
293 $this->assertEqual("= ?", $usql);
294 $this->assertEqual(1, count($params));
295 $this->assertEqual($in_values[0], $params[0]);
296
297 // Correct usage of single value
298 $in_value = 'value1';
299 list($usql, $params) = $DB->get_in_or_equal($in_values);
300 $this->assertEqual("= ?", $usql);
301 $this->assertEqual(1, count($params));
302 $this->assertEqual($in_value, $params[0]);
303
304 // SQL_PARAMS_QM - NOT IN or <>
305
306 // Correct usage of multiple values
307 $in_values = array('value1', 'value2', 'value3', 'value4');
308 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
309 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
310 $this->assertEqual(4, count($params));
311 foreach ($params as $key => $value) {
312 $this->assertEqual($in_values[$key], $value);
313 }
314
315 // Correct usage of single value (in array()
316 $in_values = array('value1');
317 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
318 $this->assertEqual("<> ?", $usql);
319 $this->assertEqual(1, count($params));
320 $this->assertEqual($in_values[0], $params[0]);
321
322 // Correct usage of single value
323 $in_value = 'value1';
324 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
325 $this->assertEqual("<> ?", $usql);
326 $this->assertEqual(1, count($params));
327 $this->assertEqual($in_value, $params[0]);
328
329 // SQL_PARAMS_NAMED - IN or =
330
331 // Correct usage of multiple values
332 $in_values = array('value1', 'value2', 'value3', 'value4');
333 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
334 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
335 $this->assertEqual(4, count($params));
336 reset($in_values);
337 foreach ($params as $key => $value) {
338 $this->assertEqual(current($in_values), $value);
339 next($in_values);
340 }
341
342 // Correct usage of single values (in array)
343 $in_values = array('value1');
344 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
345 $this->assertEqual("= :param01", $usql);
346 $this->assertEqual(1, count($params));
347 $this->assertEqual($in_values[0], $params['param01']);
348
349 // Correct usage of single value
350 $in_value = 'value1';
351 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
352 $this->assertEqual("= :param01", $usql);
353 $this->assertEqual(1, count($params));
354 $this->assertEqual($in_value, $params['param01']);
355
356 // SQL_PARAMS_NAMED - NOT IN or <>
357
358 // Correct usage of multiple values
359 $in_values = array('value1', 'value2', 'value3', 'value4');
360 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
361 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
362 $this->assertEqual(4, count($params));
363 reset($in_values);
364 foreach ($params as $key => $value) {
365 $this->assertEqual(current($in_values), $value);
366 next($in_values);
367 }
368
369 // Correct usage of single values (in array)
370 $in_values = array('value1');
371 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
372 $this->assertEqual("<> :param01", $usql);
373 $this->assertEqual(1, count($params));
374 $this->assertEqual($in_values[0], $params['param01']);
375
376 // Correct usage of single value
377 $in_value = 'value1';
378 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
379 $this->assertEqual("<> :param01", $usql);
380 $this->assertEqual(1, count($params));
381 $this->assertEqual($in_value, $params['param01']);
382
383 }
384
385 public function test_fix_table_names() {
386 $DB = new moodle_database_for_testing();
387 $prefix = $DB->get_prefix();
388
389 // Simple placeholder
390 $placeholder = "{user}";
391 $this->assertEqual($prefix . "user", $DB->public_fix_table_names($placeholder));
392
393 // Full SQL
394 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
395 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
396 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
397
b579f0db 398
399 }
400
401 public function test_get_recordset() {
402 $DB = $this->tdb;
403 $dbman = $DB->get_manager();
404
405 $table = new xmldb_table("testtable");
406 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
407 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
408 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
409 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
410 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
411 $dbman->create_table($table);
412 $this->tables[$table->getName()] = $table;
413
414 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
415 array('id' => 2, 'course' => 3, 'name' => 'record2'),
416 array('id' => 3, 'course' => 5, 'name' => 'record3'));
417 foreach ($data as $record) {
418 $DB->insert_record('testtable', $record);
419 }
420
421 $rs = $DB->get_recordset('testtable');
422 $this->assertTrue($rs);
423
424 reset($data);
425 foreach($rs as $record) {
426 $data_record = current($data);
427 foreach ($record as $k => $v) {
428 $this->assertEqual($data_record[$k], $v);
429 }
430 next($data);
431 }
432 $rs->close();
7f9f11b5 433 }
434}
435
436/**
437 * This class is not a proper subclass of moodle_database. It is
438 * intended to be used only in unit tests, in order to gain access to the
439 * protected methods of moodle_database, and unit test them.
440 */
441class moodle_database_for_testing extends moodle_database {
442 protected $prefix = 'mdl_';
443
444 public function public_fix_table_names($sql) {
445 return $this->fix_table_names($sql);
446 }
447
448 public function driver_installed(){}
449 public function get_dbfamily(){}
450 protected function get_dbtype(){}
451 public function get_name(){}
452 public function get_configuration_hints(){}
453 public function export_dbconfig(){}
454 public function connect($dbhost, $dbuser, $dbpass, $dbname, $dbpersist, $prefix, array $dboptions=null){}
455 public function get_server_info(){}
456 protected function allowed_param_types(){}
457 public function get_last_error(){}
458 public function get_tables(){}
459 public function get_indexes($table){}
460 public function get_columns($table, $usecache=true){}
461 public function set_debug($state){}
462 public function get_debug(){}
463 public function set_logging($state){}
464 public function change_database_structure($sql){}
465 public function execute($sql, array $params=null){}
466 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
467 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
468 public function get_fieldset_sql($sql, array $params=null){}
469 public function insert_record_raw($table, $params, $returnid=true, $bulk=false){}
470 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
471 public function update_record_raw($table, $params, $bulk=false){}
472 public function update_record($table, $dataobject, $bulk=false){}
473 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
474 public function delete_records_select($table, $select, array $params=null){}
475 public function sql_concat(){}
476 public function sql_concat_join($separator="' '", $elements=array()){}
477 public function sql_substr(){}
bb78c788 478}