Missing langauge string from the unit test report.
[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)) {
eee5d9bb 32 $dbman->drop_table($table);
73f7ad71 33 }
34 }
e6b4f00e 35 $this->tables = array();
73f7ad71 36 }
37
38 function test_fix_sql_params() {
7f9f11b5 39 $DB = $this->tdb;
b579f0db 40
73f7ad71 41 // Malformed table placeholder
42 $sql = "SELECT * FROM [testtable]";
e6b4f00e 43 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 44 $this->assertEqual($sql, $sqlarray[0]);
45
46 // Correct table placeholder substitution
47 $sql = "SELECT * FROM {testtable}";
e6b4f00e 48 $sqlarray = $DB->fix_sql_params($sql);
49 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable", $sqlarray[0]);
73f7ad71 50
51 // Malformed param placeholders
52 $sql = "SELECT * FROM {testtable} WHERE name = ?param1";
b579f0db 53 $params = array('param1' => 'record2');
e6b4f00e 54 $sqlarray = $DB->fix_sql_params($sql, $params);
55 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?param1", $sqlarray[0]);
73f7ad71 56
57 // Mixed param types (colon and dollar)
b579f0db 58 $sql = "SELECT * FROM {testtable} WHERE name = :param1, course = \$1";
59 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 60 try {
e6b4f00e 61 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 62 $this->fail("Expecting an exception, none occurred");
73f7ad71 63 } catch (Exception $e) {
251387d0 64 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 65 }
66
67 // Mixed param types (question and dollar)
b579f0db 68 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = \$1";
69 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 70 try {
e6b4f00e 71 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 72 $this->fail("Expecting an exception, none occurred");
73f7ad71 73 } catch (Exception $e) {
251387d0 74 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 75 }
73f7ad71 76
77 // Too many params in sql
b579f0db 78 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = ?, id = ?";
79 $params = array('record2', 3);
73f7ad71 80 try {
e6b4f00e 81 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 82 $this->fail("Expecting an exception, none occurred");
73f7ad71 83 } catch (Exception $e) {
251387d0 84 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 85 }
73f7ad71 86
87 // Too many params in array: no error
88 $params[] = 1;
89 $params[] = time();
73f7ad71 90 $sqlarray = null;
91
92 try {
e6b4f00e 93 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 94 $this->pass();
73f7ad71 95 } catch (Exception $e) {
b579f0db 96 $this->fail("Unexpected " . get_class($e) . " exception");
73f7ad71 97 }
73f7ad71 98 $this->assertTrue($sqlarray[0]);
99
100 // Named params missing from array
b579f0db 101 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :course";
102 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 103 try {
e6b4f00e 104 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 105 $this->fail("Expecting an exception, none occurred");
73f7ad71 106 } catch (Exception $e) {
251387d0 107 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 108 }
73f7ad71 109
110 // Duplicate named param in query
b579f0db 111 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :name";
112 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 113 try {
e6b4f00e 114 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 115 $this->fail("Expecting an exception, none occurred");
73f7ad71 116 } catch (Exception $e) {
251387d0 117 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 118 }
73f7ad71 119
120 // Unsupported Bound params
b579f0db 121 $sql = "SELECT * FROM {testtable} WHERE name = $1, course = $2";
73f7ad71 122 $params = array('first record', 1);
73f7ad71 123 try {
e6b4f00e 124 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 125 $this->fail("Expecting an exception, none occurred");
73f7ad71 126 } catch (Exception $e) {
251387d0 127 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 128 }
73f7ad71 129
130 // Correct named param placeholders
b579f0db 131 $sql = "SELECT * FROM {testtable} WHERE name = :name, course = :course";
132 $params = array('name' => 'first record', 'course' => 1);
e6b4f00e 133 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 134 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?", $sqlarray[0]);
73f7ad71 135 $this->assertEqual(2, count($sqlarray[1]));
136
137 // Correct ? params
b579f0db 138 $sql = "SELECT * FROM {testtable} WHERE name = ?, course = ?";
73f7ad71 139 $params = array('first record', 1);
e6b4f00e 140 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 141 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?", $sqlarray[0]);
73f7ad71 142 $this->assertEqual(2, count($sqlarray[1]));
143
144 }
145
bb78c788 146 public function testGetTables() {
7f9f11b5 147 $DB = $this->tdb;
251387d0 148 $dbman = $this->tdb->get_manager();
a230012c 149
b579f0db 150 $original_count = count($DB->get_tables());
151
bb78c788 152 // Need to test with multiple DBs
b579f0db 153 $table = new xmldb_table("testtable");
154 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
b579f0db 155 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
156 $dbman->create_table($table);
157 $this->tables[$table->getName()] = $table;
767172e4 158
b579f0db 159 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 160 }
161
767172e4 162 public function testEnums() {
163 $DB = $this->tdb;
164 $dbman = $this->tdb->get_manager();
165
166 $table = new xmldb_table("testtable");
167 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, null, null, XMLDB_ENUM, array('test','test2','test3'),null);
168 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
169 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
170 $dbman->create_table($table);
171 $this->tables[$table->getName()] = $table;
172
173 $columns = $DB->get_columns('testtable');
174
175 $enumfield = $columns['enumfield'];
176 $this->assertEqual('enumfield', $enumfield->name);
177 $this->assertEqual('enum', $enumfield->type);
178 $this->assertEqual(3, count($enumfield->enums));
179 $this->assertEqual('test', $enumfield->enums[0]);
180 $this->assertEqual('test2', $enumfield->enums[1]);
181 $this->assertEqual('test3', $enumfield->enums[2]);
182
183 }
184
185 public function testDefaults() {
186 $DB = $this->tdb;
187 $dbman = $this->tdb->get_manager();
188
189 $table = new xmldb_table("testtable");
190 $enumvalues = array('test','test2','test3');
191 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, XMLDB_ENUM, $enumvalues, 'test2');
192 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
193 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
194 $dbman->create_table($table);
195 $this->tables[$table->getName()] = $table;
196
197 $columns = $DB->get_columns('testtable');
198
199 $enumfield = $columns['enumfield'];
200 $this->assertEqual('test2', $enumfield->default_value);
201 $this->assertEqual('C', $enumfield->meta_type);
202
203 }
204
bb78c788 205 public function testGetIndexes() {
7f9f11b5 206 $DB = $this->tdb;
251387d0 207 $dbman = $this->tdb->get_manager();
a230012c 208
b579f0db 209 $table = new xmldb_table("testtable");
210 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
211 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
b579f0db 212 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
767172e4 213 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
b579f0db 214 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
215 $dbman->create_table($table);
216 $this->tables[$table->getName()] = $table;
217
bb78c788 218 $this->assertTrue($indices = $DB->get_indexes('testtable'));
767172e4 219 $this->assertTrue(count($indices) == 2);
220 sort($indices);
221
222 $this->assertFalse($indices[0]['unique']);
223 $this->assertTrue($indices[1]['unique']);
224 $this->assertEqual(1, count($indices[0]['columns']));
225 $this->assertEqual(2, count($indices[1]['columns']));
226 $this->assertEqual('course', $indices[0]['columns'][0]);
227 $this->assertEqual('course', $indices[1]['columns'][0]);
228 $this->assertEqual('id', $indices[1]['columns'][1]);
bb78c788 229 }
230
231 public function testGetColumns() {
7f9f11b5 232 $DB = $this->tdb;
251387d0 233 $dbman = $this->tdb->get_manager();
bb78c788 234
b579f0db 235 $table = new xmldb_table("testtable");
236 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
237 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
238 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
b579f0db 239 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
240 $dbman->create_table($table);
241 $this->tables[$table->getName()] = $table;
242
bb78c788 243 $this->assertTrue($columns = $DB->get_columns('testtable'));
244 $fields = $this->tables['testtable']->getFields();
245 $this->assertEqual(count($columns), count($fields));
246
247 for ($i = 0; $i < count($columns); $i++) {
248 if ($i == 0) {
249 $next_column = reset($columns);
250 $next_field = reset($fields);
251 } else {
252 $next_column = next($columns);
253 $next_field = next($fields);
254 }
255
256 $this->assertEqual($next_column->name, $next_field->name);
257 }
258 }
259
260 public function testExecute() {
7f9f11b5 261 $DB = $this->tdb;
251387d0 262 $dbman = $this->tdb->get_manager();
a230012c 263
b579f0db 264 $table = new xmldb_table("testtable");
265 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
266 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
267 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
268 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
269 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
270 $dbman->create_table($table);
271 $this->tables[$table->getName()] = $table;
272
bb78c788 273 $sql = "SELECT * FROM {testtable}";
b579f0db 274
bb78c788 275 $this->assertTrue($DB->execute($sql));
73f7ad71 276
b579f0db 277 $params = array('course' => 1, 'name' => 'test');
6807d2b3 278
279 $sql = "INSERT INTO {testtable} (".implode(',', array_keys($params)).")
280 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
281
282
283 $this->assertTrue($DB->execute($sql, $params));
bb78c788 284
b579f0db 285 $record = $DB->get_record('testtable', array('id' => 1));
bb78c788 286
6807d2b3 287 foreach ($params as $field => $value) {
bb78c788 288 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
289 }
290 }
291
7f9f11b5 292 public function test_get_in_or_equal() {
293 $DB = $this->tdb;
294
295 // SQL_PARAMS_QM - IN or =
296
297 // Correct usage of multiple values
298 $in_values = array('value1', 'value2', 'value3', 'value4');
299 list($usql, $params) = $DB->get_in_or_equal($in_values);
300 $this->assertEqual("IN (?,?,?,?)", $usql);
301 $this->assertEqual(4, count($params));
302 foreach ($params as $key => $value) {
303 $this->assertEqual($in_values[$key], $value);
304 }
305
306 // Correct usage of single value (in an array)
307 $in_values = array('value1');
308 list($usql, $params) = $DB->get_in_or_equal($in_values);
309 $this->assertEqual("= ?", $usql);
310 $this->assertEqual(1, count($params));
311 $this->assertEqual($in_values[0], $params[0]);
312
313 // Correct usage of single value
314 $in_value = 'value1';
315 list($usql, $params) = $DB->get_in_or_equal($in_values);
316 $this->assertEqual("= ?", $usql);
317 $this->assertEqual(1, count($params));
318 $this->assertEqual($in_value, $params[0]);
319
320 // SQL_PARAMS_QM - NOT IN or <>
321
322 // Correct usage of multiple values
323 $in_values = array('value1', 'value2', 'value3', 'value4');
324 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
325 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
326 $this->assertEqual(4, count($params));
327 foreach ($params as $key => $value) {
328 $this->assertEqual($in_values[$key], $value);
329 }
330
331 // Correct usage of single value (in array()
332 $in_values = array('value1');
333 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
334 $this->assertEqual("<> ?", $usql);
335 $this->assertEqual(1, count($params));
336 $this->assertEqual($in_values[0], $params[0]);
337
338 // Correct usage of single value
339 $in_value = 'value1';
340 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
341 $this->assertEqual("<> ?", $usql);
342 $this->assertEqual(1, count($params));
343 $this->assertEqual($in_value, $params[0]);
344
345 // SQL_PARAMS_NAMED - IN or =
346
347 // Correct usage of multiple values
348 $in_values = array('value1', 'value2', 'value3', 'value4');
349 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
350 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
351 $this->assertEqual(4, count($params));
352 reset($in_values);
353 foreach ($params as $key => $value) {
354 $this->assertEqual(current($in_values), $value);
355 next($in_values);
356 }
357
358 // Correct usage of single values (in array)
359 $in_values = array('value1');
360 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
361 $this->assertEqual("= :param01", $usql);
362 $this->assertEqual(1, count($params));
363 $this->assertEqual($in_values[0], $params['param01']);
364
365 // Correct usage of single value
366 $in_value = 'value1';
367 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
368 $this->assertEqual("= :param01", $usql);
369 $this->assertEqual(1, count($params));
370 $this->assertEqual($in_value, $params['param01']);
371
372 // SQL_PARAMS_NAMED - NOT IN or <>
373
374 // Correct usage of multiple values
375 $in_values = array('value1', 'value2', 'value3', 'value4');
376 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
377 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
378 $this->assertEqual(4, count($params));
379 reset($in_values);
380 foreach ($params as $key => $value) {
381 $this->assertEqual(current($in_values), $value);
382 next($in_values);
383 }
384
385 // Correct usage of single values (in array)
386 $in_values = array('value1');
387 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
388 $this->assertEqual("<> :param01", $usql);
389 $this->assertEqual(1, count($params));
390 $this->assertEqual($in_values[0], $params['param01']);
391
392 // Correct usage of single value
393 $in_value = 'value1';
394 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
395 $this->assertEqual("<> :param01", $usql);
396 $this->assertEqual(1, count($params));
397 $this->assertEqual($in_value, $params['param01']);
398
399 }
400
401 public function test_fix_table_names() {
402 $DB = new moodle_database_for_testing();
403 $prefix = $DB->get_prefix();
404
405 // Simple placeholder
406 $placeholder = "{user}";
407 $this->assertEqual($prefix . "user", $DB->public_fix_table_names($placeholder));
408
409 // Full SQL
410 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
411 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
412 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
413
b579f0db 414
415 }
416
417 public function test_get_recordset() {
418 $DB = $this->tdb;
419 $dbman = $DB->get_manager();
420
421 $table = new xmldb_table("testtable");
422 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
423 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
424 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
425 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
426 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
427 $dbman->create_table($table);
428 $this->tables[$table->getName()] = $table;
429
430 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
431 array('id' => 2, 'course' => 3, 'name' => 'record2'),
432 array('id' => 3, 'course' => 5, 'name' => 'record3'));
433 foreach ($data as $record) {
434 $DB->insert_record('testtable', $record);
435 }
436
437 $rs = $DB->get_recordset('testtable');
438 $this->assertTrue($rs);
439
440 reset($data);
441 foreach($rs as $record) {
442 $data_record = current($data);
443 foreach ($record as $k => $v) {
444 $this->assertEqual($data_record[$k], $v);
445 }
446 next($data);
447 }
448 $rs->close();
7f9f11b5 449 }
0088bd31 450
b0cb2290 451 public function test_get_recordset_iterator_keys() {
452 $DB = $this->tdb;
453 $dbman = $DB->get_manager();
454
455 $table = new xmldb_table("testtable");
456 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
457 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
458 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
459 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
460 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
461 $dbman->create_table($table);
462 $this->tables[$table->getName()] = $table;
463
464 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
465 array('id' => 2, 'course' => 3, 'name' => 'record2'),
466 array('id' => 3, 'course' => 5, 'name' => 'record3'));
467 foreach ($data as $record) {
468 $DB->insert_record('testtable', $record);
469 }
470
471 /// Test repeated numeric keys are returned ok
472 $rs = $DB->get_recordset('testtable', NULL, NULL, 'course, name, id');
473
474 reset($data);
475 $count = 0;
476 foreach($rs as $key => $record) {
477 $data_record = current($data);
478 $this->assertEqual($data_record['course'], $key);
479 next($data);
480 $count++;
481 }
482 $rs->close();
483
484 /// Test record returned are ok
485 $this->assertEqual($count, 3);
486
487 /// Test string keys are returned ok
488 $rs = $DB->get_recordset('testtable', NULL, NULL, 'name, course, id');
489
490 reset($data);
491 $count = 0;
492 foreach($rs as $key => $record) {
493 $data_record = current($data);
494 $this->assertEqual($data_record['name'], $key);
495 next($data);
496 $count++;
497 }
498 $rs->close();
499
500 /// Test record returned are ok
501 $this->assertEqual($count, 3);
502
503 /// Test numeric not starting in 1 keys are returned ok
504 $rs = $DB->get_recordset('testtable', NULL, 'id DESC', 'id, course, name');
505
506 $data = array_reverse($data);
507 reset($data);
508 $count = 0;
509 foreach($rs as $key => $record) {
510 $data_record = current($data);
511 $this->assertEqual($data_record['id'], $key);
512 next($data);
513 $count++;
514 }
515 $rs->close();
516
517 /// Test record returned are ok
518 $this->assertEqual($count, 3);
519 }
520
0088bd31 521 public function test_get_recordset_list() {
522 $DB = $this->tdb;
523 $dbman = $DB->get_manager();
524
525 $table = new xmldb_table("testtable");
526 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
527 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
528 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
529 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
530 $dbman->create_table($table);
531 $this->tables[$table->getName()] = $table;
532
533 $DB->insert_record('testtable', array('course' => 3));
534 $DB->insert_record('testtable', array('course' => 3));
535 $DB->insert_record('testtable', array('course' => 5));
536 $DB->insert_record('testtable', array('course' => 2));
537
538 $rs = $DB->get_recordset_list('testtable', 'course', array(3, 2));
539
540 $this->assertTrue($rs);
541
542 $counter = 0;
543 foreach ($rs as $record) {
544 $counter++;
545 }
546 $this->assertEqual(3, $counter);
547 }
548
549 public function test_get_recordset_select() {
550 $DB = $this->tdb;
551 $dbman = $DB->get_manager();
552
553 $table = new xmldb_table("testtable");
554 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
555 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
556 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
557 $dbman->create_table($table);
558 $this->tables[$table->getName()] = $table;
559
560 $DB->insert_record('testtable', array('course' => 3));
561 $DB->insert_record('testtable', array('course' => 3));
562 $DB->insert_record('testtable', array('course' => 5));
563 $DB->insert_record('testtable', array('course' => 2));
564
565 $this->assertTrue($rs = $DB->get_recordset_select('testtable', ''));
566 $counter = 0;
567 foreach ($rs as $record) {
568 $counter++;
569 }
570 $this->assertEqual(4, $counter);
571
572 $this->assertTrue($rs = $DB->get_recordset_select('testtable', 'course = 3'));
573 $counter = 0;
574 foreach ($rs as $record) {
575 $counter++;
576 }
577 $this->assertEqual(2, $counter);
578 }
579
580 public function test_get_recordset_sql() {
581 $DB = $this->tdb;
582 $dbman = $DB->get_manager();
583
584 $table = new xmldb_table("testtable");
585 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
586 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
587 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
588 $dbman->create_table($table);
589 $this->tables[$table->getName()] = $table;
590
591 $DB->insert_record('testtable', array('course' => 3));
592 $DB->insert_record('testtable', array('course' => 3));
593 $DB->insert_record('testtable', array('course' => 5));
594 $DB->insert_record('testtable', array('course' => 2));
595
596 $this->assertTrue($rs = $DB->get_recordset_sql('SELECT * FROM {testtable} WHERE course = ?', array(3)));
597 $counter = 0;
598 foreach ($rs as $record) {
599 $counter++;
600 }
601 $this->assertEqual(2, $counter);
602 }
603
604 public function test_get_records() {
605 $DB = $this->tdb;
606 $dbman = $DB->get_manager();
607
608 $table = new xmldb_table("testtable");
609 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
610 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
611 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
612 $dbman->create_table($table);
613 $this->tables[$table->getName()] = $table;
614
615 $DB->insert_record('testtable', array('course' => 3));
616 $DB->insert_record('testtable', array('course' => 3));
617 $DB->insert_record('testtable', array('course' => 5));
618 $DB->insert_record('testtable', array('course' => 2));
619
620 // All records
621 $records = $DB->get_records('testtable');
622 $this->assertEqual(4, count($records));
623 $this->assertEqual(3, $records[1]->course);
624 $this->assertEqual(3, $records[2]->course);
625 $this->assertEqual(5, $records[3]->course);
626 $this->assertEqual(2, $records[4]->course);
627
628 // Records matching certain conditions
629 $records = $DB->get_records('testtable', array('course' => 3));
630 $this->assertEqual(2, count($records));
631 $this->assertEqual(3, $records[1]->course);
632 $this->assertEqual(3, $records[2]->course);
633
634 // All records sorted by course
635 $records = $DB->get_records('testtable', null, 'course');
636 $this->assertEqual(4, count($records));
637 $current_record = reset($records);
638 $this->assertEqual(4, $current_record->id);
639 $current_record = next($records);
640 $this->assertEqual(1, $current_record->id);
641 $current_record = next($records);
642 $this->assertEqual(2, $current_record->id);
643 $current_record = next($records);
644 $this->assertEqual(3, $current_record->id);
645
646 // All records, but get only one field
647 $records = $DB->get_records('testtable', null, '', 'id');
648 $this->assertTrue(empty($records[1]->course));
649 $this->assertFalse(empty($records[1]->id));
650 $this->assertEqual(4, count($records));
651 }
652
653 public function test_get_records_list() {
50a12c87 654 $DB = $this->tdb;
655 $dbman = $DB->get_manager();
656
657 $table = new xmldb_table("testtable");
658 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
659 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
660 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
661 $dbman->create_table($table);
662 $this->tables[$table->getName()] = $table;
663
664 $DB->insert_record('testtable', array('course' => 3));
665 $DB->insert_record('testtable', array('course' => 3));
666 $DB->insert_record('testtable', array('course' => 5));
667 $DB->insert_record('testtable', array('course' => 2));
668
669 $this->assertTrue($records = $DB->get_records_list('testtable', 'course', array(3, 2)));
670 $this->assertEqual(3, count($records));
671 $this->assertEqual(1, reset($records)->id);
672 $this->assertEqual(2, next($records)->id);
673 $this->assertEqual(4, next($records)->id);
0088bd31 674
675 }
676
677 public function test_get_records_sql() {
50a12c87 678 $DB = $this->tdb;
679 $dbman = $DB->get_manager();
680
681 $table = new xmldb_table("testtable");
682 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
683 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
684 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
685 $dbman->create_table($table);
686 $this->tables[$table->getName()] = $table;
687
688 $DB->insert_record('testtable', array('course' => 3));
689 $DB->insert_record('testtable', array('course' => 3));
690 $DB->insert_record('testtable', array('course' => 5));
691 $DB->insert_record('testtable', array('course' => 2));
692
693 $this->assertTrue($records = $DB->get_records_sql('SELECT * FROM {testtable} WHERE course = ?', array(3)));
694 $this->assertEqual(2, count($records));
695 $this->assertEqual(1, reset($records)->id);
696 $this->assertEqual(2, next($records)->id);
0088bd31 697
698 }
699
700 public function test_get_records_menu() {
50a12c87 701 $DB = $this->tdb;
702 $dbman = $DB->get_manager();
703
704 $table = new xmldb_table("testtable");
705 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
706 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
707 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
708 $dbman->create_table($table);
709 $this->tables[$table->getName()] = $table;
710
711 $DB->insert_record('testtable', array('course' => 3));
712 $DB->insert_record('testtable', array('course' => 3));
713 $DB->insert_record('testtable', array('course' => 5));
714 $DB->insert_record('testtable', array('course' => 2));
715
716 $this->assertTrue($records = $DB->get_records_menu('testtable', array('course' => 3)));
717 $this->assertEqual(2, count($records));
718 $this->assertFalse(empty($records[1]));
719 $this->assertFalse(empty($records[2]));
720 $this->assertEqual(3, $records[1]);
721 $this->assertEqual(3, $records[2]);
0088bd31 722
723 }
724
725 public function test_get_records_select_menu() {
50a12c87 726 $DB = $this->tdb;
727 $dbman = $DB->get_manager();
728
729 $table = new xmldb_table("testtable");
730 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
731 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
732 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
733 $dbman->create_table($table);
734 $this->tables[$table->getName()] = $table;
735
736 $DB->insert_record('testtable', array('course' => 3));
737 $DB->insert_record('testtable', array('course' => 2));
738 $DB->insert_record('testtable', array('course' => 3));
739 $DB->insert_record('testtable', array('course' => 5));
740
741 $this->assertTrue($records = $DB->get_records_select_menu('testtable', "course > ?", array(2)));
742
743 $this->assertEqual(3, count($records));
744 $this->assertFalse(empty($records[1]));
745 $this->assertTrue(empty($records[2]));
746 $this->assertFalse(empty($records[3]));
747 $this->assertFalse(empty($records[4]));
748 $this->assertEqual(3, $records[1]);
749 $this->assertEqual(3, $records[3]);
750 $this->assertEqual(5, $records[4]);
0088bd31 751
752 }
753
754 public function test_get_records_sql_menu() {
50a12c87 755 $DB = $this->tdb;
756 $dbman = $DB->get_manager();
757
758 $table = new xmldb_table("testtable");
759 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
760 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
761 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
762 $dbman->create_table($table);
763 $this->tables[$table->getName()] = $table;
764
765 $DB->insert_record('testtable', array('course' => 3));
766 $DB->insert_record('testtable', array('course' => 2));
767 $DB->insert_record('testtable', array('course' => 3));
768 $DB->insert_record('testtable', array('course' => 5));
769
770 $this->assertTrue($records = $DB->get_records_sql_menu('SELECT * FROM {testtable} WHERE course > ?', array(2)));
771
772 $this->assertEqual(3, count($records));
773 $this->assertFalse(empty($records[1]));
774 $this->assertTrue(empty($records[2]));
775 $this->assertFalse(empty($records[3]));
776 $this->assertFalse(empty($records[4]));
777 $this->assertEqual(3, $records[1]);
778 $this->assertEqual(3, $records[3]);
779 $this->assertEqual(5, $records[4]);
0088bd31 780
781 }
782
783 public function test_get_record() {
50a12c87 784 $DB = $this->tdb;
785 $dbman = $DB->get_manager();
786
787 $table = new xmldb_table("testtable");
788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
791 $dbman->create_table($table);
792 $this->tables[$table->getName()] = $table;
793
794 $DB->insert_record('testtable', array('course' => 3));
795 $DB->insert_record('testtable', array('course' => 2));
796
797 $this->assertTrue($record = $DB->get_record('testtable', array('id' => 2)));
0088bd31 798
50a12c87 799 $this->assertEqual(2, $record->course);
0088bd31 800 }
801
802 public function test_get_record_select() {
50a12c87 803 $DB = $this->tdb;
804 $dbman = $DB->get_manager();
805
806 $table = new xmldb_table("testtable");
807 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
808 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
809 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
810 $dbman->create_table($table);
811 $this->tables[$table->getName()] = $table;
812
813 $DB->insert_record('testtable', array('course' => 3));
814 $DB->insert_record('testtable', array('course' => 2));
815
816 $this->assertTrue($record = $DB->get_record_select('testtable', "id = ?", array(2)));
817
818 $this->assertEqual(2, $record->course);
0088bd31 819
820 }
821
822 public function test_get_record_sql() {
50a12c87 823 $DB = $this->tdb;
824 $dbman = $DB->get_manager();
825
826 $table = new xmldb_table("testtable");
827 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
828 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
829 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
830 $dbman->create_table($table);
831 $this->tables[$table->getName()] = $table;
832
833 $DB->insert_record('testtable', array('course' => 3));
834 $DB->insert_record('testtable', array('course' => 2));
835
836 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {testtable} WHERE id = ?", array(2)));
837
838 $this->assertEqual(2, $record->course);
0088bd31 839
840 }
841
842 public function test_get_field() {
50a12c87 843 $DB = $this->tdb;
844 $dbman = $DB->get_manager();
845
846 $table = new xmldb_table("testtable");
847 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
848 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
849 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
850 $dbman->create_table($table);
851 $this->tables[$table->getName()] = $table;
0088bd31 852
50a12c87 853 $DB->insert_record('testtable', array('course' => 3));
854
855 $this->assertTrue($course = $DB->get_field('testtable', 'course', array('id' => 1)));
856 $this->assertEqual(3, $course);
0088bd31 857 }
858
859 public function test_get_field_select() {
50a12c87 860 $DB = $this->tdb;
861 $dbman = $DB->get_manager();
862
863 $table = new xmldb_table("testtable");
864 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
865 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
866 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
867 $dbman->create_table($table);
868 $this->tables[$table->getName()] = $table;
869
870 $DB->insert_record('testtable', array('course' => 3));
871
872 $this->assertTrue($course = $DB->get_field_select('testtable', 'course', "id = ?", array(1)));
873 $this->assertEqual(3, $course);
0088bd31 874
875 }
876
877 public function test_get_field_sql() {
50a12c87 878 $DB = $this->tdb;
879 $dbman = $DB->get_manager();
880
881 $table = new xmldb_table("testtable");
882 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
883 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
884 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
885 $dbman->create_table($table);
886 $this->tables[$table->getName()] = $table;
887
888 $DB->insert_record('testtable', array('course' => 3));
889
890 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {testtable} WHERE id = ?", array(1)));
891 $this->assertEqual(3, $course);
0088bd31 892
893 }
894
50a12c87 895 public function test_get_fieldset_select() {
896 $DB = $this->tdb;
897 $dbman = $DB->get_manager();
898
899 $table = new xmldb_table("testtable");
900 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
901 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
902 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
903 $dbman->create_table($table);
904 $this->tables[$table->getName()] = $table;
905
906 $DB->insert_record('testtable', array('course' => 1));
907 $DB->insert_record('testtable', array('course' => 3));
908 $DB->insert_record('testtable', array('course' => 2));
909 $DB->insert_record('testtable', array('course' => 6));
910
911 $this->assertTrue($fieldset = $DB->get_fieldset_select('testtable', 'course', "course > ?", array(1)));
912
913 $this->assertEqual(3, count($fieldset));
914 $this->assertEqual(3, $fieldset[0]);
915 $this->assertEqual(2, $fieldset[1]);
916 $this->assertEqual(6, $fieldset[2]);
0088bd31 917
918 }
919
920 public function test_get_fieldset_sql() {
50a12c87 921 $DB = $this->tdb;
922 $dbman = $DB->get_manager();
0088bd31 923
50a12c87 924 $table = new xmldb_table("testtable");
925 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
926 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
927 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
928 $dbman->create_table($table);
929 $this->tables[$table->getName()] = $table;
930
931 $DB->insert_record('testtable', array('course' => 1));
932 $DB->insert_record('testtable', array('course' => 3));
933 $DB->insert_record('testtable', array('course' => 2));
934 $DB->insert_record('testtable', array('course' => 6));
935
936 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {testtable} WHERE course > ?", array(1)));
937
938 $this->assertEqual(3, count($fieldset));
939 $this->assertEqual(2, $fieldset[0]);
940 $this->assertEqual(3, $fieldset[1]);
941 $this->assertEqual(4, $fieldset[2]);
0088bd31 942 }
943
944 public function test_insert_record_raw() {
50a12c87 945 $DB = $this->tdb;
946 $dbman = $DB->get_manager();
0088bd31 947
50a12c87 948 $table = new xmldb_table("testtable");
949 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
950 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
951 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
952 $dbman->create_table($table);
953 $this->tables[$table->getName()] = $table;
954
955 $this->assertTrue($DB->insert_record_raw('testtable', array('course' => 1)));
956 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 1)));
957 $this->assertEqual(1, $record->course);
0088bd31 958 }
959
960 public function test_insert_record() {
50a12c87 961 $DB = $this->tdb;
962 $dbman = $DB->get_manager();
963
964 $table = new xmldb_table("testtable");
965 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
966 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
967 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
968 $dbman->create_table($table);
969 $this->tables[$table->getName()] = $table;
970
971 $this->assertTrue($DB->insert_record('testtable', array('course' => 1)));
972 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 1)));
973 $this->assertEqual(1, $record->course);
0088bd31 974
975 }
976
c824e1e1 977 public function test_insert_record_clob() {
978 global $CFG;
979
980 $DB = $this->tdb;
981 $dbman = $DB->get_manager();
982
983 $table = new xmldb_table("testtable");
984 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
985 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
986 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
987 $dbman->create_table($table);
988 $this->tables[$table->getName()] = $table;
989
990 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/clob.txt');
991
992 $this->assertTrue($id = $DB->insert_record('testtable', array('description' => $clob)));
993 $this->assertTrue($record = $DB->get_record('testtable', array('id' => $id)));
994 $this->assertEqual($clob, $record->description);
995
996 }
997
998 public function test_insert_record_multiple_clobs() {
999 global $CFG;
1000
1001 $DB = $this->tdb;
1002 $dbman = $DB->get_manager();
1003
1004 $table = new xmldb_table("testtable");
1005 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1006 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1007 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null, null, null);
1008 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1009 $dbman->create_table($table);
1010 $this->tables[$table->getName()] = $table;
1011
3ee3b8c5 1012 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/clob.txt');
1013 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/randombinary');
c824e1e1 1014
1015 $this->assertTrue($id = $DB->insert_record('testtable', array('description' => $clob, 'image' => $blob)));
1016 $this->assertTrue($record = $DB->get_record('testtable', array('id' => $id)));
1017 $this->assertEqual($clob, $record->description);
1018 $this->assertEqual($blob, $record->image);
3ee3b8c5 1019 $this->assertEqual($clob, $DB->get_field('testtable', 'description', array('id' => $id)));
1020 $this->assertEqual($blob, $DB->get_field('testtable', 'image', array('id' => $id)));
c824e1e1 1021 }
1022
1023
0088bd31 1024 public function test_update_record_raw() {
50a12c87 1025 $DB = $this->tdb;
1026 $dbman = $DB->get_manager();
0088bd31 1027
50a12c87 1028 $table = new xmldb_table("testtable");
1029 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1030 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1031 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1032 $dbman->create_table($table);
1033 $this->tables[$table->getName()] = $table;
1034
1035 $DB->insert_record('testtable', array('course' => 1));
1036 $record = $DB->get_record('testtable', array('course' => 1));
1037 $record->course = 2;
1038 $this->assertTrue($DB->update_record_raw('testtable', $record));
1039 $this->assertFalse($record = $DB->get_record('testtable', array('course' => 1)));
1040 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 2)));
0088bd31 1041 }
1042
1043 public function test_update_record() {
50a12c87 1044 $DB = $this->tdb;
1045 $dbman = $DB->get_manager();
1046
1047 $table = new xmldb_table("testtable");
1048 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1049 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1050 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1051 $dbman->create_table($table);
1052 $this->tables[$table->getName()] = $table;
0088bd31 1053
50a12c87 1054 $DB->insert_record('testtable', array('course' => 1));
1055 $record = $DB->get_record('testtable', array('course' => 1));
1056 $record->course = 2;
1057 $this->assertTrue($DB->update_record('testtable', $record));
1058 $this->assertFalse($record = $DB->get_record('testtable', array('course' => 1)));
1059 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 2)));
0088bd31 1060 }
1061
c824e1e1 1062 public function test_update_record_clob() {
1063 global $CFG;
1064
1065 $DB = $this->tdb;
1066 $dbman = $DB->get_manager();
1067
1068 $table = new xmldb_table("testtable");
1069 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1070 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1071 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1072 $dbman->create_table($table);
1073 $this->tables[$table->getName()] = $table;
1074
1075 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/clob.txt');
1076
1077 $id = $DB->insert_record('testtable', array('description' => $clob));
1078 $record = $DB->get_record('testtable', array('id' => $id));
1079 $record->description = substr($clob, 0, 500);
1080 $this->assertTrue($DB->update_record('testtable', $record));
1081
1082 $record = $DB->get_record('testtable', array('id' => $id));
1083 $this->assertEqual(substr($clob, 0, 500), $record->description);
1084 }
1085
1086 public function test_update_record_multiple_clobs() {
1087 global $CFG;
1088
1089 $DB = $this->tdb;
1090 $dbman = $DB->get_manager();
1091
1092 $table = new xmldb_table("testtable");
1093 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1094 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1095 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null, null, null);
1096 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1097 $dbman->create_table($table);
1098 $this->tables[$table->getName()] = $table;
1099
1100 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/clob.txt');
1101 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/randombinary');
1102
1103 $id = $DB->insert_record('testtable', array('description' => $clob, 'image' => $blob));
1104 $record = $DB->get_record('testtable', array('id' => $id));
1105 $record->description = substr($clob, 0, 500);
1106 $record->image = substr($blob, 0, 250);
1107 $this->assertTrue($DB->update_record('testtable', $record));
1108
1109 $record = $DB->get_record('testtable', array('id' => $id));
1110 $this->assertEqual(substr($clob, 0, 500), $record->description);
1111 $this->assertEqual(substr($blob, 0, 250), $record->image);
3ee3b8c5 1112 $this->assertEqual(substr($clob, 0, 500), $DB->get_field('testtable', 'description', array('id' => $id)));
1113 $this->assertEqual(substr($blob, 0, 250), $DB->get_field('testtable', 'image', array('id' => $id)));
c824e1e1 1114
1115 }
1116
0088bd31 1117 public function test_set_field() {
50a12c87 1118 $DB = $this->tdb;
1119 $dbman = $DB->get_manager();
1120
1121 $table = new xmldb_table("testtable");
1122 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1123 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1124 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1125 $dbman->create_table($table);
1126 $this->tables[$table->getName()] = $table;
0088bd31 1127
50a12c87 1128 $DB->insert_record('testtable', array('course' => 1));
1129
1130 $this->assertTrue($DB->set_field('testtable', 'course', 2, array('id' => 1)));
1131 $this->assertEqual(2, $DB->get_field('testtable', 'course', array('id' => 1)));
0088bd31 1132 }
1133
1134 public function test_set_field_select() {
50a12c87 1135 $DB = $this->tdb;
1136 $dbman = $DB->get_manager();
1137
1138 $table = new xmldb_table("testtable");
1139 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1140 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1141 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1142 $dbman->create_table($table);
1143 $this->tables[$table->getName()] = $table;
1144
1145 $DB->insert_record('testtable', array('course' => 1));
1146
1147 $this->assertTrue($DB->set_field_select('testtable', 'course', 2, 'id = ?', array(1)));
1148 $this->assertEqual(2, $DB->get_field('testtable', 'course', array('id' => 1)));
0088bd31 1149
1150 }
1151
1152 public function test_count_records() {
1153 $DB = $this->tdb;
1154
1155 $dbman = $DB->get_manager();
1156
1157 $table = new xmldb_table("testtable");
1158 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1159 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1160 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1161 $dbman->create_table($table);
1162 $this->tables[$table->getName()] = $table;
1163
1164 $this->assertEqual(0, $DB->count_records('testtable'));
1165
1166 $DB->insert_record('testtable', array('course' => 3));
1167 $DB->insert_record('testtable', array('course' => 4));
1168 $DB->insert_record('testtable', array('course' => 5));
1169
1170 $this->assertEqual(3, $DB->count_records('testtable'));
1171 }
1172
1173 public function test_count_records_select() {
50a12c87 1174 $DB = $this->tdb;
0088bd31 1175
50a12c87 1176 $dbman = $DB->get_manager();
1177
1178 $table = new xmldb_table("testtable");
1179 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1180 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1181 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1182 $dbman->create_table($table);
1183 $this->tables[$table->getName()] = $table;
1184
1185 $this->assertEqual(0, $DB->count_records('testtable'));
1186
1187 $DB->insert_record('testtable', array('course' => 3));
1188 $DB->insert_record('testtable', array('course' => 4));
1189 $DB->insert_record('testtable', array('course' => 5));
1190
1191 $this->assertEqual(2, $DB->count_records_select('testtable', 'course > ?', array(3)));
0088bd31 1192 }
1193
1194 public function test_count_records_sql() {
50a12c87 1195 $DB = $this->tdb;
1196 $dbman = $DB->get_manager();
1197
1198 $table = new xmldb_table("testtable");
1199 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1200 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1201 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1202 $dbman->create_table($table);
1203 $this->tables[$table->getName()] = $table;
1204
1205 $this->assertEqual(0, $DB->count_records('testtable'));
1206
1207 $DB->insert_record('testtable', array('course' => 3));
1208 $DB->insert_record('testtable', array('course' => 4));
1209 $DB->insert_record('testtable', array('course' => 5));
0088bd31 1210
50a12c87 1211 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {testtable} WHERE course > ?", array(3)));
0088bd31 1212 }
1213
1214 public function test_record_exists() {
50a12c87 1215 $DB = $this->tdb;
1216 $dbman = $DB->get_manager();
1217
1218 $table = new xmldb_table("testtable");
1219 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1220 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1221 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1222 $dbman->create_table($table);
1223 $this->tables[$table->getName()] = $table;
1224
1225 $this->assertEqual(0, $DB->count_records('testtable'));
1226
1227 $this->assertFalse($DB->record_exists('testtable', array('course' => 3)));
1228 $DB->insert_record('testtable', array('course' => 3));
1229
1230 $this->assertTrue($DB->record_exists('testtable', array('course' => 3)));
0088bd31 1231
1232 }
1233
1234 public function test_record_exists_select() {
50a12c87 1235 $DB = $this->tdb;
1236 $dbman = $DB->get_manager();
0088bd31 1237
50a12c87 1238 $table = new xmldb_table("testtable");
1239 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1240 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1241 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1242 $dbman->create_table($table);
1243 $this->tables[$table->getName()] = $table;
1244
1245 $this->assertEqual(0, $DB->count_records('testtable'));
1246
1247 $this->assertFalse($DB->record_exists_select('testtable', "course = ?", array(3)));
1248 $DB->insert_record('testtable', array('course' => 3));
1249
1250 $this->assertTrue($DB->record_exists_select('testtable', "course = ?", array(3)));
0088bd31 1251 }
1252
1253 public function test_record_exists_sql() {
50a12c87 1254 $DB = $this->tdb;
1255 $dbman = $DB->get_manager();
0088bd31 1256
50a12c87 1257 $table = new xmldb_table("testtable");
1258 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1259 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1260 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1261 $dbman->create_table($table);
1262 $this->tables[$table->getName()] = $table;
1263
1264 $this->assertEqual(0, $DB->count_records('testtable'));
1265
1266 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {testtable} WHERE course = ?", array(3)));
1267 $DB->insert_record('testtable', array('course' => 3));
1268
1269 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {testtable} WHERE course = ?", array(3)));
0088bd31 1270 }
1271
1272 public function test_delete_records() {
50a12c87 1273 $DB = $this->tdb;
1274 $dbman = $DB->get_manager();
0088bd31 1275
50a12c87 1276 $table = new xmldb_table("testtable");
1277 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1278 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1279 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1280 $dbman->create_table($table);
1281 $this->tables[$table->getName()] = $table;
1282
1283 $DB->insert_record('testtable', array('course' => 3));
1284 $DB->insert_record('testtable', array('course' => 2));
1285 $DB->insert_record('testtable', array('course' => 2));
1286
1287 // Delete all records
1288 $this->assertTrue($DB->delete_records('testtable'));
1289 $this->assertEqual(0, $DB->count_records('testtable'));
1290
1291 // Delete subset of records
1292 $DB->insert_record('testtable', array('course' => 3));
1293 $DB->insert_record('testtable', array('course' => 2));
1294 $DB->insert_record('testtable', array('course' => 2));
1295
1296 $this->assertTrue($DB->delete_records('testtable', array('course' => 2)));
1297 $this->assertEqual(1, $DB->count_records('testtable'));
0088bd31 1298 }
1299
1300 public function test_delete_records_select() {
50a12c87 1301 $DB = $this->tdb;
1302 $dbman = $DB->get_manager();
0088bd31 1303
50a12c87 1304 $table = new xmldb_table("testtable");
1305 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1306 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1307 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1308 $dbman->create_table($table);
1309 $this->tables[$table->getName()] = $table;
0088bd31 1310
50a12c87 1311 $DB->insert_record('testtable', array('course' => 3));
1312 $DB->insert_record('testtable', array('course' => 2));
1313 $DB->insert_record('testtable', array('course' => 2));
0088bd31 1314
50a12c87 1315 $this->assertTrue($DB->delete_records_select('testtable', 'course = ?', array(2)));
1316 $this->assertEqual(1, $DB->count_records('testtable'));
0088bd31 1317 }
7f9f11b5 1318}
1319
1320/**
1321 * This class is not a proper subclass of moodle_database. It is
1322 * intended to be used only in unit tests, in order to gain access to the
1323 * protected methods of moodle_database, and unit test them.
1324 */
1325class moodle_database_for_testing extends moodle_database {
1326 protected $prefix = 'mdl_';
1327
1328 public function public_fix_table_names($sql) {
1329 return $this->fix_table_names($sql);
1330 }
1331
1332 public function driver_installed(){}
1333 public function get_dbfamily(){}
1334 protected function get_dbtype(){}
16a5642c 1335 protected function get_dblibrary(){}
7f9f11b5 1336 public function get_name(){}
1337 public function get_configuration_hints(){}
7f9f11b5 1338 public function connect($dbhost, $dbuser, $dbpass, $dbname, $dbpersist, $prefix, array $dboptions=null){}
1339 public function get_server_info(){}
1340 protected function allowed_param_types(){}
1341 public function get_last_error(){}
1342 public function get_tables(){}
1343 public function get_indexes($table){}
1344 public function get_columns($table, $usecache=true){}
1345 public function set_debug($state){}
1346 public function get_debug(){}
1347 public function set_logging($state){}
1348 public function change_database_structure($sql){}
1349 public function execute($sql, array $params=null){}
1350 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1351 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1352 public function get_fieldset_sql($sql, array $params=null){}
1353 public function insert_record_raw($table, $params, $returnid=true, $bulk=false){}
1354 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
1355 public function update_record_raw($table, $params, $bulk=false){}
1356 public function update_record($table, $dataobject, $bulk=false){}
1357 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
1358 public function delete_records_select($table, $select, array $params=null){}
1359 public function sql_concat(){}
1360 public function sql_concat_join($separator="' '", $elements=array()){}
1361 public function sql_substr(){}
bb78c788 1362}