Implement proper keys for adodb and pdo recordset iterators. MDL-15798
[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
451 public function test_get_recordset_list() {
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_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
459 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
460 $dbman->create_table($table);
461 $this->tables[$table->getName()] = $table;
462
463 $DB->insert_record('testtable', array('course' => 3));
464 $DB->insert_record('testtable', array('course' => 3));
465 $DB->insert_record('testtable', array('course' => 5));
466 $DB->insert_record('testtable', array('course' => 2));
467
468 $rs = $DB->get_recordset_list('testtable', 'course', array(3, 2));
469
470 $this->assertTrue($rs);
471
472 $counter = 0;
473 foreach ($rs as $record) {
474 $counter++;
475 }
476 $this->assertEqual(3, $counter);
477 }
478
479 public function test_get_recordset_select() {
480 $DB = $this->tdb;
481 $dbman = $DB->get_manager();
482
483 $table = new xmldb_table("testtable");
484 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
485 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
486 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
487 $dbman->create_table($table);
488 $this->tables[$table->getName()] = $table;
489
490 $DB->insert_record('testtable', array('course' => 3));
491 $DB->insert_record('testtable', array('course' => 3));
492 $DB->insert_record('testtable', array('course' => 5));
493 $DB->insert_record('testtable', array('course' => 2));
494
495 $this->assertTrue($rs = $DB->get_recordset_select('testtable', ''));
496 $counter = 0;
497 foreach ($rs as $record) {
498 $counter++;
499 }
500 $this->assertEqual(4, $counter);
501
502 $this->assertTrue($rs = $DB->get_recordset_select('testtable', 'course = 3'));
503 $counter = 0;
504 foreach ($rs as $record) {
505 $counter++;
506 }
507 $this->assertEqual(2, $counter);
508 }
509
510 public function test_get_recordset_sql() {
511 $DB = $this->tdb;
512 $dbman = $DB->get_manager();
513
514 $table = new xmldb_table("testtable");
515 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
516 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
517 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
518 $dbman->create_table($table);
519 $this->tables[$table->getName()] = $table;
520
521 $DB->insert_record('testtable', array('course' => 3));
522 $DB->insert_record('testtable', array('course' => 3));
523 $DB->insert_record('testtable', array('course' => 5));
524 $DB->insert_record('testtable', array('course' => 2));
525
526 $this->assertTrue($rs = $DB->get_recordset_sql('SELECT * FROM {testtable} WHERE course = ?', array(3)));
527 $counter = 0;
528 foreach ($rs as $record) {
529 $counter++;
530 }
531 $this->assertEqual(2, $counter);
532 }
533
534 public function test_get_records() {
535 $DB = $this->tdb;
536 $dbman = $DB->get_manager();
537
538 $table = new xmldb_table("testtable");
539 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
540 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
541 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
542 $dbman->create_table($table);
543 $this->tables[$table->getName()] = $table;
544
545 $DB->insert_record('testtable', array('course' => 3));
546 $DB->insert_record('testtable', array('course' => 3));
547 $DB->insert_record('testtable', array('course' => 5));
548 $DB->insert_record('testtable', array('course' => 2));
549
550 // All records
551 $records = $DB->get_records('testtable');
552 $this->assertEqual(4, count($records));
553 $this->assertEqual(3, $records[1]->course);
554 $this->assertEqual(3, $records[2]->course);
555 $this->assertEqual(5, $records[3]->course);
556 $this->assertEqual(2, $records[4]->course);
557
558 // Records matching certain conditions
559 $records = $DB->get_records('testtable', array('course' => 3));
560 $this->assertEqual(2, count($records));
561 $this->assertEqual(3, $records[1]->course);
562 $this->assertEqual(3, $records[2]->course);
563
564 // All records sorted by course
565 $records = $DB->get_records('testtable', null, 'course');
566 $this->assertEqual(4, count($records));
567 $current_record = reset($records);
568 $this->assertEqual(4, $current_record->id);
569 $current_record = next($records);
570 $this->assertEqual(1, $current_record->id);
571 $current_record = next($records);
572 $this->assertEqual(2, $current_record->id);
573 $current_record = next($records);
574 $this->assertEqual(3, $current_record->id);
575
576 // All records, but get only one field
577 $records = $DB->get_records('testtable', null, '', 'id');
578 $this->assertTrue(empty($records[1]->course));
579 $this->assertFalse(empty($records[1]->id));
580 $this->assertEqual(4, count($records));
581 }
582
583 public function test_get_records_list() {
50a12c87 584 $DB = $this->tdb;
585 $dbman = $DB->get_manager();
586
587 $table = new xmldb_table("testtable");
588 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
589 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
590 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
591 $dbman->create_table($table);
592 $this->tables[$table->getName()] = $table;
593
594 $DB->insert_record('testtable', array('course' => 3));
595 $DB->insert_record('testtable', array('course' => 3));
596 $DB->insert_record('testtable', array('course' => 5));
597 $DB->insert_record('testtable', array('course' => 2));
598
599 $this->assertTrue($records = $DB->get_records_list('testtable', 'course', array(3, 2)));
600 $this->assertEqual(3, count($records));
601 $this->assertEqual(1, reset($records)->id);
602 $this->assertEqual(2, next($records)->id);
603 $this->assertEqual(4, next($records)->id);
0088bd31 604
605 }
606
607 public function test_get_records_sql() {
50a12c87 608 $DB = $this->tdb;
609 $dbman = $DB->get_manager();
610
611 $table = new xmldb_table("testtable");
612 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
613 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
614 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
615 $dbman->create_table($table);
616 $this->tables[$table->getName()] = $table;
617
618 $DB->insert_record('testtable', array('course' => 3));
619 $DB->insert_record('testtable', array('course' => 3));
620 $DB->insert_record('testtable', array('course' => 5));
621 $DB->insert_record('testtable', array('course' => 2));
622
623 $this->assertTrue($records = $DB->get_records_sql('SELECT * FROM {testtable} WHERE course = ?', array(3)));
624 $this->assertEqual(2, count($records));
625 $this->assertEqual(1, reset($records)->id);
626 $this->assertEqual(2, next($records)->id);
0088bd31 627
628 }
629
630 public function test_get_records_menu() {
50a12c87 631 $DB = $this->tdb;
632 $dbman = $DB->get_manager();
633
634 $table = new xmldb_table("testtable");
635 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
636 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
637 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
638 $dbman->create_table($table);
639 $this->tables[$table->getName()] = $table;
640
641 $DB->insert_record('testtable', array('course' => 3));
642 $DB->insert_record('testtable', array('course' => 3));
643 $DB->insert_record('testtable', array('course' => 5));
644 $DB->insert_record('testtable', array('course' => 2));
645
646 $this->assertTrue($records = $DB->get_records_menu('testtable', array('course' => 3)));
647 $this->assertEqual(2, count($records));
648 $this->assertFalse(empty($records[1]));
649 $this->assertFalse(empty($records[2]));
650 $this->assertEqual(3, $records[1]);
651 $this->assertEqual(3, $records[2]);
0088bd31 652
653 }
654
655 public function test_get_records_select_menu() {
50a12c87 656 $DB = $this->tdb;
657 $dbman = $DB->get_manager();
658
659 $table = new xmldb_table("testtable");
660 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
661 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
662 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
663 $dbman->create_table($table);
664 $this->tables[$table->getName()] = $table;
665
666 $DB->insert_record('testtable', array('course' => 3));
667 $DB->insert_record('testtable', array('course' => 2));
668 $DB->insert_record('testtable', array('course' => 3));
669 $DB->insert_record('testtable', array('course' => 5));
670
671 $this->assertTrue($records = $DB->get_records_select_menu('testtable', "course > ?", array(2)));
672
673 $this->assertEqual(3, count($records));
674 $this->assertFalse(empty($records[1]));
675 $this->assertTrue(empty($records[2]));
676 $this->assertFalse(empty($records[3]));
677 $this->assertFalse(empty($records[4]));
678 $this->assertEqual(3, $records[1]);
679 $this->assertEqual(3, $records[3]);
680 $this->assertEqual(5, $records[4]);
0088bd31 681
682 }
683
684 public function test_get_records_sql_menu() {
50a12c87 685 $DB = $this->tdb;
686 $dbman = $DB->get_manager();
687
688 $table = new xmldb_table("testtable");
689 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
690 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
691 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
692 $dbman->create_table($table);
693 $this->tables[$table->getName()] = $table;
694
695 $DB->insert_record('testtable', array('course' => 3));
696 $DB->insert_record('testtable', array('course' => 2));
697 $DB->insert_record('testtable', array('course' => 3));
698 $DB->insert_record('testtable', array('course' => 5));
699
700 $this->assertTrue($records = $DB->get_records_sql_menu('SELECT * FROM {testtable} WHERE course > ?', array(2)));
701
702 $this->assertEqual(3, count($records));
703 $this->assertFalse(empty($records[1]));
704 $this->assertTrue(empty($records[2]));
705 $this->assertFalse(empty($records[3]));
706 $this->assertFalse(empty($records[4]));
707 $this->assertEqual(3, $records[1]);
708 $this->assertEqual(3, $records[3]);
709 $this->assertEqual(5, $records[4]);
0088bd31 710
711 }
712
713 public function test_get_record() {
50a12c87 714 $DB = $this->tdb;
715 $dbman = $DB->get_manager();
716
717 $table = new xmldb_table("testtable");
718 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
719 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
720 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
721 $dbman->create_table($table);
722 $this->tables[$table->getName()] = $table;
723
724 $DB->insert_record('testtable', array('course' => 3));
725 $DB->insert_record('testtable', array('course' => 2));
726
727 $this->assertTrue($record = $DB->get_record('testtable', array('id' => 2)));
0088bd31 728
50a12c87 729 $this->assertEqual(2, $record->course);
0088bd31 730 }
731
732 public function test_get_record_select() {
50a12c87 733 $DB = $this->tdb;
734 $dbman = $DB->get_manager();
735
736 $table = new xmldb_table("testtable");
737 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
738 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
739 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
740 $dbman->create_table($table);
741 $this->tables[$table->getName()] = $table;
742
743 $DB->insert_record('testtable', array('course' => 3));
744 $DB->insert_record('testtable', array('course' => 2));
745
746 $this->assertTrue($record = $DB->get_record_select('testtable', "id = ?", array(2)));
747
748 $this->assertEqual(2, $record->course);
0088bd31 749
750 }
751
752 public function test_get_record_sql() {
50a12c87 753 $DB = $this->tdb;
754 $dbman = $DB->get_manager();
755
756 $table = new xmldb_table("testtable");
757 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
758 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
759 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
760 $dbman->create_table($table);
761 $this->tables[$table->getName()] = $table;
762
763 $DB->insert_record('testtable', array('course' => 3));
764 $DB->insert_record('testtable', array('course' => 2));
765
766 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {testtable} WHERE id = ?", array(2)));
767
768 $this->assertEqual(2, $record->course);
0088bd31 769
770 }
771
772 public function test_get_field() {
50a12c87 773 $DB = $this->tdb;
774 $dbman = $DB->get_manager();
775
776 $table = new xmldb_table("testtable");
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;
0088bd31 782
50a12c87 783 $DB->insert_record('testtable', array('course' => 3));
784
785 $this->assertTrue($course = $DB->get_field('testtable', 'course', array('id' => 1)));
786 $this->assertEqual(3, $course);
0088bd31 787 }
788
789 public function test_get_field_select() {
50a12c87 790 $DB = $this->tdb;
791 $dbman = $DB->get_manager();
792
793 $table = new xmldb_table("testtable");
794 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
795 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
796 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
797 $dbman->create_table($table);
798 $this->tables[$table->getName()] = $table;
799
800 $DB->insert_record('testtable', array('course' => 3));
801
802 $this->assertTrue($course = $DB->get_field_select('testtable', 'course', "id = ?", array(1)));
803 $this->assertEqual(3, $course);
0088bd31 804
805 }
806
807 public function test_get_field_sql() {
50a12c87 808 $DB = $this->tdb;
809 $dbman = $DB->get_manager();
810
811 $table = new xmldb_table("testtable");
812 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
813 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
814 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
815 $dbman->create_table($table);
816 $this->tables[$table->getName()] = $table;
817
818 $DB->insert_record('testtable', array('course' => 3));
819
820 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {testtable} WHERE id = ?", array(1)));
821 $this->assertEqual(3, $course);
0088bd31 822
823 }
824
50a12c87 825 public function test_get_fieldset_select() {
826 $DB = $this->tdb;
827 $dbman = $DB->get_manager();
828
829 $table = new xmldb_table("testtable");
830 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
831 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
832 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
833 $dbman->create_table($table);
834 $this->tables[$table->getName()] = $table;
835
836 $DB->insert_record('testtable', array('course' => 1));
837 $DB->insert_record('testtable', array('course' => 3));
838 $DB->insert_record('testtable', array('course' => 2));
839 $DB->insert_record('testtable', array('course' => 6));
840
841 $this->assertTrue($fieldset = $DB->get_fieldset_select('testtable', 'course', "course > ?", array(1)));
842
843 $this->assertEqual(3, count($fieldset));
844 $this->assertEqual(3, $fieldset[0]);
845 $this->assertEqual(2, $fieldset[1]);
846 $this->assertEqual(6, $fieldset[2]);
0088bd31 847
848 }
849
850 public function test_get_fieldset_sql() {
50a12c87 851 $DB = $this->tdb;
852 $dbman = $DB->get_manager();
0088bd31 853
50a12c87 854 $table = new xmldb_table("testtable");
855 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
856 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
857 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
858 $dbman->create_table($table);
859 $this->tables[$table->getName()] = $table;
860
861 $DB->insert_record('testtable', array('course' => 1));
862 $DB->insert_record('testtable', array('course' => 3));
863 $DB->insert_record('testtable', array('course' => 2));
864 $DB->insert_record('testtable', array('course' => 6));
865
866 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {testtable} WHERE course > ?", array(1)));
867
868 $this->assertEqual(3, count($fieldset));
869 $this->assertEqual(2, $fieldset[0]);
870 $this->assertEqual(3, $fieldset[1]);
871 $this->assertEqual(4, $fieldset[2]);
0088bd31 872 }
873
874 public function test_insert_record_raw() {
50a12c87 875 $DB = $this->tdb;
876 $dbman = $DB->get_manager();
0088bd31 877
50a12c87 878 $table = new xmldb_table("testtable");
879 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
880 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
881 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
882 $dbman->create_table($table);
883 $this->tables[$table->getName()] = $table;
884
885 $this->assertTrue($DB->insert_record_raw('testtable', array('course' => 1)));
886 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 1)));
887 $this->assertEqual(1, $record->course);
0088bd31 888 }
889
890 public function test_insert_record() {
50a12c87 891 $DB = $this->tdb;
892 $dbman = $DB->get_manager();
893
894 $table = new xmldb_table("testtable");
895 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
896 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
897 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
898 $dbman->create_table($table);
899 $this->tables[$table->getName()] = $table;
900
901 $this->assertTrue($DB->insert_record('testtable', array('course' => 1)));
902 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 1)));
903 $this->assertEqual(1, $record->course);
0088bd31 904
905 }
906
907 public function test_update_record_raw() {
50a12c87 908 $DB = $this->tdb;
909 $dbman = $DB->get_manager();
0088bd31 910
50a12c87 911 $table = new xmldb_table("testtable");
912 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
913 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
914 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
915 $dbman->create_table($table);
916 $this->tables[$table->getName()] = $table;
917
918 $DB->insert_record('testtable', array('course' => 1));
919 $record = $DB->get_record('testtable', array('course' => 1));
920 $record->course = 2;
921 $this->assertTrue($DB->update_record_raw('testtable', $record));
922 $this->assertFalse($record = $DB->get_record('testtable', array('course' => 1)));
923 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 2)));
0088bd31 924 }
925
926 public function test_update_record() {
50a12c87 927 $DB = $this->tdb;
928 $dbman = $DB->get_manager();
929
930 $table = new xmldb_table("testtable");
931 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
932 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
933 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
934 $dbman->create_table($table);
935 $this->tables[$table->getName()] = $table;
0088bd31 936
50a12c87 937 $DB->insert_record('testtable', array('course' => 1));
938 $record = $DB->get_record('testtable', array('course' => 1));
939 $record->course = 2;
940 $this->assertTrue($DB->update_record('testtable', $record));
941 $this->assertFalse($record = $DB->get_record('testtable', array('course' => 1)));
942 $this->assertTrue($record = $DB->get_record('testtable', array('course' => 2)));
0088bd31 943 }
944
945 public function test_set_field() {
50a12c87 946 $DB = $this->tdb;
947 $dbman = $DB->get_manager();
948
949 $table = new xmldb_table("testtable");
950 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
951 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
952 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
953 $dbman->create_table($table);
954 $this->tables[$table->getName()] = $table;
0088bd31 955
50a12c87 956 $DB->insert_record('testtable', array('course' => 1));
957
958 $this->assertTrue($DB->set_field('testtable', 'course', 2, array('id' => 1)));
959 $this->assertEqual(2, $DB->get_field('testtable', 'course', array('id' => 1)));
0088bd31 960 }
961
962 public function test_set_field_select() {
50a12c87 963 $DB = $this->tdb;
964 $dbman = $DB->get_manager();
965
966 $table = new xmldb_table("testtable");
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 $DB->insert_record('testtable', array('course' => 1));
974
975 $this->assertTrue($DB->set_field_select('testtable', 'course', 2, 'id = ?', array(1)));
976 $this->assertEqual(2, $DB->get_field('testtable', 'course', array('id' => 1)));
0088bd31 977
978 }
979
980 public function test_count_records() {
981 $DB = $this->tdb;
982
983 $dbman = $DB->get_manager();
984
985 $table = new xmldb_table("testtable");
986 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
987 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
988 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
989 $dbman->create_table($table);
990 $this->tables[$table->getName()] = $table;
991
992 $this->assertEqual(0, $DB->count_records('testtable'));
993
994 $DB->insert_record('testtable', array('course' => 3));
995 $DB->insert_record('testtable', array('course' => 4));
996 $DB->insert_record('testtable', array('course' => 5));
997
998 $this->assertEqual(3, $DB->count_records('testtable'));
999 }
1000
1001 public function test_count_records_select() {
50a12c87 1002 $DB = $this->tdb;
0088bd31 1003
50a12c87 1004 $dbman = $DB->get_manager();
1005
1006 $table = new xmldb_table("testtable");
1007 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1008 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1009 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1010 $dbman->create_table($table);
1011 $this->tables[$table->getName()] = $table;
1012
1013 $this->assertEqual(0, $DB->count_records('testtable'));
1014
1015 $DB->insert_record('testtable', array('course' => 3));
1016 $DB->insert_record('testtable', array('course' => 4));
1017 $DB->insert_record('testtable', array('course' => 5));
1018
1019 $this->assertEqual(2, $DB->count_records_select('testtable', 'course > ?', array(3)));
0088bd31 1020 }
1021
1022 public function test_count_records_sql() {
50a12c87 1023 $DB = $this->tdb;
1024 $dbman = $DB->get_manager();
1025
1026 $table = new xmldb_table("testtable");
1027 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1028 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1029 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1030 $dbman->create_table($table);
1031 $this->tables[$table->getName()] = $table;
1032
1033 $this->assertEqual(0, $DB->count_records('testtable'));
1034
1035 $DB->insert_record('testtable', array('course' => 3));
1036 $DB->insert_record('testtable', array('course' => 4));
1037 $DB->insert_record('testtable', array('course' => 5));
0088bd31 1038
50a12c87 1039 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {testtable} WHERE course > ?", array(3)));
0088bd31 1040 }
1041
1042 public function test_record_exists() {
50a12c87 1043 $DB = $this->tdb;
1044 $dbman = $DB->get_manager();
1045
1046 $table = new xmldb_table("testtable");
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 $this->assertEqual(0, $DB->count_records('testtable'));
1054
1055 $this->assertFalse($DB->record_exists('testtable', array('course' => 3)));
1056 $DB->insert_record('testtable', array('course' => 3));
1057
1058 $this->assertTrue($DB->record_exists('testtable', array('course' => 3)));
0088bd31 1059
1060 }
1061
1062 public function test_record_exists_select() {
50a12c87 1063 $DB = $this->tdb;
1064 $dbman = $DB->get_manager();
0088bd31 1065
50a12c87 1066 $table = new xmldb_table("testtable");
1067 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1068 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1069 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1070 $dbman->create_table($table);
1071 $this->tables[$table->getName()] = $table;
1072
1073 $this->assertEqual(0, $DB->count_records('testtable'));
1074
1075 $this->assertFalse($DB->record_exists_select('testtable', "course = ?", array(3)));
1076 $DB->insert_record('testtable', array('course' => 3));
1077
1078 $this->assertTrue($DB->record_exists_select('testtable', "course = ?", array(3)));
0088bd31 1079 }
1080
1081 public function test_record_exists_sql() {
50a12c87 1082 $DB = $this->tdb;
1083 $dbman = $DB->get_manager();
0088bd31 1084
50a12c87 1085 $table = new xmldb_table("testtable");
1086 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1087 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1088 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1089 $dbman->create_table($table);
1090 $this->tables[$table->getName()] = $table;
1091
1092 $this->assertEqual(0, $DB->count_records('testtable'));
1093
1094 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {testtable} WHERE course = ?", array(3)));
1095 $DB->insert_record('testtable', array('course' => 3));
1096
1097 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {testtable} WHERE course = ?", array(3)));
0088bd31 1098 }
1099
1100 public function test_delete_records() {
50a12c87 1101 $DB = $this->tdb;
1102 $dbman = $DB->get_manager();
0088bd31 1103
50a12c87 1104 $table = new xmldb_table("testtable");
1105 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1106 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1107 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1108 $dbman->create_table($table);
1109 $this->tables[$table->getName()] = $table;
1110
1111 $DB->insert_record('testtable', array('course' => 3));
1112 $DB->insert_record('testtable', array('course' => 2));
1113 $DB->insert_record('testtable', array('course' => 2));
1114
1115 // Delete all records
1116 $this->assertTrue($DB->delete_records('testtable'));
1117 $this->assertEqual(0, $DB->count_records('testtable'));
1118
1119 // Delete subset of records
1120 $DB->insert_record('testtable', array('course' => 3));
1121 $DB->insert_record('testtable', array('course' => 2));
1122 $DB->insert_record('testtable', array('course' => 2));
1123
1124 $this->assertTrue($DB->delete_records('testtable', array('course' => 2)));
1125 $this->assertEqual(1, $DB->count_records('testtable'));
0088bd31 1126 }
1127
1128 public function test_delete_records_select() {
50a12c87 1129 $DB = $this->tdb;
1130 $dbman = $DB->get_manager();
0088bd31 1131
50a12c87 1132 $table = new xmldb_table("testtable");
1133 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1134 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1135 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1136 $dbman->create_table($table);
1137 $this->tables[$table->getName()] = $table;
0088bd31 1138
50a12c87 1139 $DB->insert_record('testtable', array('course' => 3));
1140 $DB->insert_record('testtable', array('course' => 2));
1141 $DB->insert_record('testtable', array('course' => 2));
0088bd31 1142
50a12c87 1143 $this->assertTrue($DB->delete_records_select('testtable', 'course = ?', array(2)));
1144 $this->assertEqual(1, $DB->count_records('testtable'));
0088bd31 1145 }
7f9f11b5 1146}
1147
1148/**
1149 * This class is not a proper subclass of moodle_database. It is
1150 * intended to be used only in unit tests, in order to gain access to the
1151 * protected methods of moodle_database, and unit test them.
1152 */
1153class moodle_database_for_testing extends moodle_database {
1154 protected $prefix = 'mdl_';
1155
1156 public function public_fix_table_names($sql) {
1157 return $this->fix_table_names($sql);
1158 }
1159
1160 public function driver_installed(){}
1161 public function get_dbfamily(){}
1162 protected function get_dbtype(){}
16a5642c 1163 protected function get_dblibrary(){}
7f9f11b5 1164 public function get_name(){}
1165 public function get_configuration_hints(){}
7f9f11b5 1166 public function connect($dbhost, $dbuser, $dbpass, $dbname, $dbpersist, $prefix, array $dboptions=null){}
1167 public function get_server_info(){}
1168 protected function allowed_param_types(){}
1169 public function get_last_error(){}
1170 public function get_tables(){}
1171 public function get_indexes($table){}
1172 public function get_columns($table, $usecache=true){}
1173 public function set_debug($state){}
1174 public function get_debug(){}
1175 public function set_logging($state){}
1176 public function change_database_structure($sql){}
1177 public function execute($sql, array $params=null){}
1178 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1179 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1180 public function get_fieldset_sql($sql, array $params=null){}
1181 public function insert_record_raw($table, $params, $returnid=true, $bulk=false){}
1182 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
1183 public function update_record_raw($table, $params, $bulk=false){}
1184 public function update_record($table, $dataobject, $bulk=false){}
1185 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
1186 public function delete_records_select($table, $select, array $params=null){}
1187 public function sql_concat(){}
1188 public function sql_concat_join($separator="' '", $elements=array()){}
1189 public function sql_substr(){}
bb78c788 1190}