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