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