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