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