MDL-23898 backup - avoid role assignments for deleted users
[moodle.git] / lib / dml / simpletest / testdml.php
CommitLineData
73f7ad71 1<?php
49926145 2
63b3d8ab
EL
3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
73f7ad71 18/**
63b3d8ab
EL
19 * @package moodlecore
20 * @subpackage DML
21 * @copyright 2008 Petr Skoda (http://skodak.org)
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
73f7ad71 23 */
24
25if (!defined('MOODLE_INTERNAL')) {
26 die('Direct access to this script is forbidden.'); /// It must be included from a Moodle page
27}
28
c7d306e1 29class dml_test extends UnitTestCase {
73f7ad71 30 private $tables = array();
251387d0 31 private $tdb;
b579f0db 32 private $data;
16a2a412 33 public static $includecoverage = array('lib/dml');
34 public static $excludecoverage = array('lib/dml/simpletest');
73f7ad71 35
36 function setUp() {
a230012c 37 global $CFG, $DB, $UNITTEST;
e6b4f00e 38
a230012c 39 if (isset($UNITTEST->func_test_db)) {
251387d0 40 $this->tdb = $UNITTEST->func_test_db;
e6b4f00e 41 } else {
251387d0 42 $this->tdb = $DB;
e6b4f00e 43 }
73f7ad71 44
73f7ad71 45 }
46
47 function tearDown() {
251387d0 48 $dbman = $this->tdb->get_manager();
809df0e2 49
50 foreach ($this->tables as $table) {
251387d0 51 if ($dbman->table_exists($table)) {
eee5d9bb 52 $dbman->drop_table($table);
73f7ad71 53 }
54 }
e6b4f00e 55 $this->tables = array();
73f7ad71 56 }
57
1d861fce 58 /**
59 * Get a xmldb_table object for testing, deleting any existing table
60 * of the same name, for example if one was left over from a previous test
61 * run that crashed.
62 *
63 * @param database_manager $dbman the database_manager to use.
64 * @param string $tablename the name of the table to create.
65 * @return xmldb_table the table object.
66 */
66e75f8d 67 private function get_test_table($tablename="") {
515cb653 68 $DB = $this->tdb;
9d833e93 69 $dbman = $this->tdb->get_manager();
70
515cb653 71 if ($tablename == '') {
d578c197 72 $tablename = "unit_table";
66e75f8d 73 }
74
1d861fce 75 $table = new xmldb_table($tablename);
76 if ($dbman->table_exists($table)) {
77 $dbman->drop_table($table);
78 }
3ff8bf26 79 return new xmldb_table($tablename);
1d861fce 80 }
81
73f7ad71 82 function test_fix_sql_params() {
7f9f11b5 83 $DB = $this->tdb;
b579f0db 84
9d833e93 85 $table = $this->get_test_table();
86 $tablename = $table->getName();
3ff8bf26 87
334ce026 88 // Correct table placeholder substitution
3ff8bf26 89 $sql = "SELECT * FROM {".$tablename."}";
334ce026 90 $sqlarray = $DB->fix_sql_params($sql);
66e75f8d 91 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
334ce026 92
93 // Conversions of all param types
94 $sql = array();
3ff8bf26 95 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
334ce026 96 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
97 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
98
99 $params = array();
3ff8bf26 100 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
334ce026 101 $params[SQL_PARAMS_QM] = array('first record', 1);
102 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
103
104 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
105 $this->assertEqual($rsql, $sql[$rtype]);
106 $this->assertEqual($rparams, $params[$rtype]);
107
108 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
109 $this->assertEqual($rsql, $sql[$rtype]);
110 $this->assertEqual($rparams, $params[$rtype]);
111
112 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
113 $this->assertEqual($rsql, $sql[$rtype]);
114 $this->assertEqual($rparams, $params[$rtype]);
115
116
73f7ad71 117 // Malformed table placeholder
118 $sql = "SELECT * FROM [testtable]";
e6b4f00e 119 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 120 $this->assertEqual($sql, $sqlarray[0]);
121
73f7ad71 122
73f7ad71 123 // Mixed param types (colon and dollar)
3ff8bf26 124 $sql = "SELECT * FROM {".$tablename."} WHERE name = :param1, course = \$1";
b579f0db 125 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 126 try {
e6b4f00e 127 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 128 $this->fail("Expecting an exception, none occurred");
73f7ad71 129 } catch (Exception $e) {
251387d0 130 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 131 }
132
133 // Mixed param types (question and dollar)
3ff8bf26 134 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = \$1";
b579f0db 135 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 136 try {
e6b4f00e 137 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 138 $this->fail("Expecting an exception, none occurred");
73f7ad71 139 } catch (Exception $e) {
251387d0 140 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 141 }
73f7ad71 142
143 // Too many params in sql
3ff8bf26 144 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = ?, id = ?";
b579f0db 145 $params = array('record2', 3);
73f7ad71 146 try {
e6b4f00e 147 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 148 $this->fail("Expecting an exception, none occurred");
73f7ad71 149 } catch (Exception $e) {
251387d0 150 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 151 }
73f7ad71 152
153 // Too many params in array: no error
154 $params[] = 1;
155 $params[] = time();
73f7ad71 156 $sqlarray = null;
157
158 try {
e6b4f00e 159 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 160 $this->pass();
73f7ad71 161 } catch (Exception $e) {
6ff835b7 162 $this->fail("Unexpected ".get_class($e)." exception");
73f7ad71 163 }
73f7ad71 164 $this->assertTrue($sqlarray[0]);
165
166 // Named params missing from array
3ff8bf26 167 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :course";
b579f0db 168 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 169 try {
e6b4f00e 170 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 171 $this->fail("Expecting an exception, none occurred");
73f7ad71 172 } catch (Exception $e) {
251387d0 173 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 174 }
73f7ad71 175
176 // Duplicate named param in query
3ff8bf26 177 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :name";
b579f0db 178 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 179 try {
e6b4f00e 180 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 181 $this->fail("Expecting an exception, none occurred");
73f7ad71 182 } catch (Exception $e) {
251387d0 183 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 184 }
73f7ad71 185
73f7ad71 186 }
187
bb78c788 188 public function testGetTables() {
7f9f11b5 189 $DB = $this->tdb;
251387d0 190 $dbman = $this->tdb->get_manager();
a230012c 191
1d861fce 192 // Need to test with multiple DBs
9d833e93 193 $table = $this->get_test_table();
3ff8bf26 194 $tablename = $table->getName();
1d861fce 195
b579f0db 196 $original_count = count($DB->get_tables());
197
f9ecb171 198 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
b579f0db 199 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
200 $dbman->create_table($table);
3ff8bf26 201 $this->tables[$tablename] = $table;
767172e4 202
b579f0db 203 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 204 }
205
767172e4 206 public function testDefaults() {
207 $DB = $this->tdb;
208 $dbman = $this->tdb->get_manager();
209
9d833e93 210 $table = $this->get_test_table();
3ff8bf26 211 $tablename = $table->getName();
212
f9ecb171 213 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
214 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
767172e4 215 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
216 $dbman->create_table($table);
3ff8bf26 217 $this->tables[$tablename] = $table;
767172e4 218
3ff8bf26 219 $columns = $DB->get_columns($tablename);
767172e4 220
221 $enumfield = $columns['enumfield'];
222 $this->assertEqual('test2', $enumfield->default_value);
223 $this->assertEqual('C', $enumfield->meta_type);
224
225 }
226
bb78c788 227 public function testGetIndexes() {
7f9f11b5 228 $DB = $this->tdb;
251387d0 229 $dbman = $this->tdb->get_manager();
a230012c 230
9d833e93 231 $table = $this->get_test_table();
3ff8bf26 232 $tablename = $table->getName();
233
f9ecb171 234 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
235 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
80ffbad3 236 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
b579f0db 237 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
767172e4 238 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
b579f0db 239 $dbman->create_table($table);
3ff8bf26 240 $this->tables[$tablename] = $table;
b579f0db 241
3ff8bf26 242 $this->assertTrue($indices = $DB->get_indexes($tablename));
66e75f8d 243 $this->assertEqual(count($indices), 2);
244 // we do not care about index names for now
80ffbad3 245 $first = array_shift($indices);
246 $second = array_shift($indices);
247 if (count($first['columns']) == 2) {
248 $composed = $first;
249 $single = $second;
250 } else {
251 $composed = $second;
252 $single = $first;
253 }
254 $this->assertFalse($single['unique']);
255 $this->assertTrue($composed['unique']);
256 $this->assertEqual(1, count($single['columns']));
257 $this->assertEqual(2, count($composed['columns']));
258 $this->assertEqual('course', $single['columns'][0]);
259 $this->assertEqual('course', $composed['columns'][0]);
260 $this->assertEqual('id', $composed['columns'][1]);
bb78c788 261 }
262
263 public function testGetColumns() {
7f9f11b5 264 $DB = $this->tdb;
251387d0 265 $dbman = $this->tdb->get_manager();
bb78c788 266
9d833e93 267 $table = $this->get_test_table();
3ff8bf26 268 $tablename = $table->getName();
269
f9ecb171 270 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
271 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
272 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 273 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
274 $dbman->create_table($table);
3ff8bf26 275 $this->tables[$tablename] = $table;
b579f0db 276
3ff8bf26 277 $this->assertTrue($columns = $DB->get_columns($tablename));
278 $fields = $this->tables[$tablename]->getFields();
bb78c788 279 $this->assertEqual(count($columns), count($fields));
280
281 for ($i = 0; $i < count($columns); $i++) {
282 if ($i == 0) {
283 $next_column = reset($columns);
284 $next_field = reset($fields);
285 } else {
286 $next_column = next($columns);
287 $next_field = next($fields);
288 }
289
290 $this->assertEqual($next_column->name, $next_field->name);
291 }
292 }
293
294 public function testExecute() {
7f9f11b5 295 $DB = $this->tdb;
251387d0 296 $dbman = $this->tdb->get_manager();
a230012c 297
9d833e93 298 $table = $this->get_test_table();
3ff8bf26 299 $tablename = $table->getName();
300
f9ecb171 301 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
302 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
303 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 304 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
305 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
306 $dbman->create_table($table);
3ff8bf26 307 $this->tables[$tablename] = $table;
b579f0db 308
3ff8bf26 309 $sql = "SELECT * FROM {".$tablename."}";
b579f0db 310
bb78c788 311 $this->assertTrue($DB->execute($sql));
73f7ad71 312
b579f0db 313 $params = array('course' => 1, 'name' => 'test');
6807d2b3 314
3ff8bf26 315 $sql = "INSERT INTO {".$tablename."} (".implode(',', array_keys($params)).")
6807d2b3 316 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
317
318
319 $this->assertTrue($DB->execute($sql, $params));
bb78c788 320
3ff8bf26 321 $record = $DB->get_record($tablename, array('id' => 1));
bb78c788 322
6807d2b3 323 foreach ($params as $field => $value) {
bb78c788 324 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
325 }
326 }
327
7f9f11b5 328 public function test_get_in_or_equal() {
329 $DB = $this->tdb;
330
331 // SQL_PARAMS_QM - IN or =
332
333 // Correct usage of multiple values
334 $in_values = array('value1', 'value2', 'value3', 'value4');
335 list($usql, $params) = $DB->get_in_or_equal($in_values);
336 $this->assertEqual("IN (?,?,?,?)", $usql);
337 $this->assertEqual(4, count($params));
338 foreach ($params as $key => $value) {
339 $this->assertEqual($in_values[$key], $value);
340 }
341
342 // Correct usage of single value (in an array)
343 $in_values = array('value1');
344 list($usql, $params) = $DB->get_in_or_equal($in_values);
345 $this->assertEqual("= ?", $usql);
346 $this->assertEqual(1, count($params));
347 $this->assertEqual($in_values[0], $params[0]);
348
349 // Correct usage of single value
350 $in_value = 'value1';
351 list($usql, $params) = $DB->get_in_or_equal($in_values);
352 $this->assertEqual("= ?", $usql);
353 $this->assertEqual(1, count($params));
354 $this->assertEqual($in_value, $params[0]);
355
356 // SQL_PARAMS_QM - NOT IN or <>
357
358 // Correct usage of multiple values
359 $in_values = array('value1', 'value2', 'value3', 'value4');
360 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
361 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
362 $this->assertEqual(4, count($params));
363 foreach ($params as $key => $value) {
364 $this->assertEqual($in_values[$key], $value);
365 }
366
367 // Correct usage of single value (in array()
368 $in_values = array('value1');
369 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
370 $this->assertEqual("<> ?", $usql);
371 $this->assertEqual(1, count($params));
372 $this->assertEqual($in_values[0], $params[0]);
373
374 // Correct usage of single value
375 $in_value = 'value1';
376 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
377 $this->assertEqual("<> ?", $usql);
378 $this->assertEqual(1, count($params));
379 $this->assertEqual($in_value, $params[0]);
380
381 // SQL_PARAMS_NAMED - IN or =
382
383 // Correct usage of multiple values
384 $in_values = array('value1', 'value2', 'value3', 'value4');
385 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
386 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
387 $this->assertEqual(4, count($params));
388 reset($in_values);
389 foreach ($params as $key => $value) {
390 $this->assertEqual(current($in_values), $value);
391 next($in_values);
392 }
393
394 // Correct usage of single values (in array)
395 $in_values = array('value1');
396 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
397 $this->assertEqual("= :param01", $usql);
398 $this->assertEqual(1, count($params));
399 $this->assertEqual($in_values[0], $params['param01']);
400
401 // Correct usage of single value
402 $in_value = 'value1';
403 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
404 $this->assertEqual("= :param01", $usql);
405 $this->assertEqual(1, count($params));
406 $this->assertEqual($in_value, $params['param01']);
407
408 // SQL_PARAMS_NAMED - NOT IN or <>
409
410 // Correct usage of multiple values
411 $in_values = array('value1', 'value2', 'value3', 'value4');
412 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
413 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
414 $this->assertEqual(4, count($params));
415 reset($in_values);
416 foreach ($params as $key => $value) {
417 $this->assertEqual(current($in_values), $value);
418 next($in_values);
419 }
420
421 // Correct usage of single values (in array)
422 $in_values = array('value1');
423 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
424 $this->assertEqual("<> :param01", $usql);
425 $this->assertEqual(1, count($params));
426 $this->assertEqual($in_values[0], $params['param01']);
427
428 // Correct usage of single value
429 $in_value = 'value1';
430 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
431 $this->assertEqual("<> :param01", $usql);
432 $this->assertEqual(1, count($params));
433 $this->assertEqual($in_value, $params['param01']);
434
435 }
436
437 public function test_fix_table_names() {
438 $DB = new moodle_database_for_testing();
439 $prefix = $DB->get_prefix();
440
441 // Simple placeholder
442 $placeholder = "{user}";
6ff835b7 443 $this->assertEqual($prefix."user", $DB->public_fix_table_names($placeholder));
7f9f11b5 444
445 // Full SQL
446 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
447 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
448 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
449
b579f0db 450
451 }
452
453 public function test_get_recordset() {
454 $DB = $this->tdb;
455 $dbman = $DB->get_manager();
456
9d833e93 457 $table = $this->get_test_table();
3ff8bf26 458 $tablename = $table->getName();
459
f9ecb171 460 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
461 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
462 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 463 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
464 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
465 $dbman->create_table($table);
3ff8bf26 466 $this->tables[$tablename] = $table;
b579f0db 467
468 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
469 array('id' => 2, 'course' => 3, 'name' => 'record2'),
470 array('id' => 3, 'course' => 5, 'name' => 'record3'));
22d77567 471
b579f0db 472 foreach ($data as $record) {
3ff8bf26 473 $DB->insert_record($tablename, $record);
b579f0db 474 }
475
3ff8bf26 476 $rs = $DB->get_recordset($tablename);
b579f0db 477 $this->assertTrue($rs);
478
479 reset($data);
480 foreach($rs as $record) {
481 $data_record = current($data);
482 foreach ($record as $k => $v) {
483 $this->assertEqual($data_record[$k], $v);
484 }
485 next($data);
486 }
487 $rs->close();
5d91786a 488
489 // note: delegate limits testing to test_get_recordset_sql()
7f9f11b5 490 }
0088bd31 491
b0cb2290 492 public function test_get_recordset_iterator_keys() {
493 $DB = $this->tdb;
494 $dbman = $DB->get_manager();
495
9d833e93 496 $table = $this->get_test_table();
3ff8bf26 497 $tablename = $table->getName();
498
f9ecb171 499 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
500 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
501 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b0cb2290 502 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
503 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
504 $dbman->create_table($table);
3ff8bf26 505 $this->tables[$tablename] = $table;
b0cb2290 506
22d77567 507 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
508 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
509 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 510 foreach ($data as $record) {
3ff8bf26 511 $DB->insert_record($tablename, $record);
b0cb2290 512 }
513
514 /// Test repeated numeric keys are returned ok
3ff8bf26 515 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 516
517 reset($data);
518 $count = 0;
519 foreach($rs as $key => $record) {
520 $data_record = current($data);
521 $this->assertEqual($data_record['course'], $key);
522 next($data);
523 $count++;
524 }
525 $rs->close();
526
527 /// Test record returned are ok
528 $this->assertEqual($count, 3);
529
530 /// Test string keys are returned ok
3ff8bf26 531 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 532
533 reset($data);
534 $count = 0;
535 foreach($rs as $key => $record) {
536 $data_record = current($data);
537 $this->assertEqual($data_record['name'], $key);
538 next($data);
539 $count++;
540 }
541 $rs->close();
542
543 /// Test record returned are ok
544 $this->assertEqual($count, 3);
545
546 /// Test numeric not starting in 1 keys are returned ok
3ff8bf26 547 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 548
549 $data = array_reverse($data);
550 reset($data);
551 $count = 0;
552 foreach($rs as $key => $record) {
553 $data_record = current($data);
554 $this->assertEqual($data_record['id'], $key);
555 next($data);
556 $count++;
557 }
558 $rs->close();
559
560 /// Test record returned are ok
561 $this->assertEqual($count, 3);
562 }
563
0088bd31 564 public function test_get_recordset_list() {
565 $DB = $this->tdb;
566 $dbman = $DB->get_manager();
567
9d833e93 568 $table = $this->get_test_table();
3ff8bf26 569 $tablename = $table->getName();
570
f9ecb171 571 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
572 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 573 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
574 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
575 $dbman->create_table($table);
3ff8bf26 576 $this->tables[$tablename] = $table;
0088bd31 577
3ff8bf26 578 $DB->insert_record($tablename, array('course' => 3));
579 $DB->insert_record($tablename, array('course' => 3));
580 $DB->insert_record($tablename, array('course' => 5));
581 $DB->insert_record($tablename, array('course' => 2));
0088bd31 582
3ff8bf26 583 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 584
585 $this->assertTrue($rs);
586
587 $counter = 0;
588 foreach ($rs as $record) {
589 $counter++;
590 }
591 $this->assertEqual(3, $counter);
c362878e 592 $rs->close();
593
594 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
595
596 $counter = 0;
597 foreach ($rs as $record) {
598 $counter++;
599 }
c362878e 600 $rs->close();
5d91786a 601 $this->assertEqual(0, $counter);
602
603 // note: delegate limits testing to test_get_recordset_sql()
0088bd31 604 }
605
606 public function test_get_recordset_select() {
607 $DB = $this->tdb;
608 $dbman = $DB->get_manager();
609
9d833e93 610 $table = $this->get_test_table();
3ff8bf26 611 $tablename = $table->getName();
612
f9ecb171 613 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
614 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 615 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
616 $dbman->create_table($table);
3ff8bf26 617 $this->tables[$tablename] = $table;
0088bd31 618
3ff8bf26 619 $DB->insert_record($tablename, array('course' => 3));
620 $DB->insert_record($tablename, array('course' => 3));
621 $DB->insert_record($tablename, array('course' => 5));
622 $DB->insert_record($tablename, array('course' => 2));
0088bd31 623
3ff8bf26 624 $rs = $DB->get_recordset_select($tablename, '');
0088bd31 625 $counter = 0;
626 foreach ($rs as $record) {
627 $counter++;
628 }
515cb653 629 $rs->close();
0088bd31 630 $this->assertEqual(4, $counter);
631
3ff8bf26 632 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 633 $counter = 0;
634 foreach ($rs as $record) {
635 $counter++;
636 }
515cb653 637 $rs->close();
0088bd31 638 $this->assertEqual(2, $counter);
5d91786a 639
640 // note: delegate limits testing to test_get_recordset_sql()
0088bd31 641 }
642
643 public function test_get_recordset_sql() {
644 $DB = $this->tdb;
645 $dbman = $DB->get_manager();
646
9d833e93 647 $table = $this->get_test_table();
3ff8bf26 648 $tablename = $table->getName();
649
f9ecb171 650 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
651 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 652 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
653 $dbman->create_table($table);
3ff8bf26 654 $this->tables[$tablename] = $table;
0088bd31 655
5d91786a 656 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
657 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
658 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
659 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
660 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
661 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
662 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
0088bd31 663
3ff8bf26 664 $this->assertTrue($rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 665 $counter = 0;
666 foreach ($rs as $record) {
667 $counter++;
668 }
515cb653 669 $rs->close();
0088bd31 670 $this->assertEqual(2, $counter);
5d91786a 671
672 // limits - only need to test this case, the rest have been tested by test_get_records_sql()
673 // only limitfrom = skips that number of records
674 $rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 2, 0);
675 $records = array();
676 foreach($rs as $key => $record) {
677 $records[$key] = $record;
678 }
679 $rs->close();
680 $this->assertEqual(5, count($records));
681 $this->assertEqual($inskey3, reset($records)->id);
682 $this->assertEqual($inskey7, end($records)->id);
683
684 // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here
0088bd31 685 }
686
687 public function test_get_records() {
688 $DB = $this->tdb;
689 $dbman = $DB->get_manager();
690
9d833e93 691 $table = $this->get_test_table();
3ff8bf26 692 $tablename = $table->getName();
693
f9ecb171 694 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
695 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 696 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
697 $dbman->create_table($table);
3ff8bf26 698 $this->tables[$tablename] = $table;
0088bd31 699
3ff8bf26 700 $DB->insert_record($tablename, array('course' => 3));
701 $DB->insert_record($tablename, array('course' => 3));
702 $DB->insert_record($tablename, array('course' => 5));
703 $DB->insert_record($tablename, array('course' => 2));
0088bd31 704
705 // All records
3ff8bf26 706 $records = $DB->get_records($tablename);
0088bd31 707 $this->assertEqual(4, count($records));
708 $this->assertEqual(3, $records[1]->course);
709 $this->assertEqual(3, $records[2]->course);
710 $this->assertEqual(5, $records[3]->course);
711 $this->assertEqual(2, $records[4]->course);
712
713 // Records matching certain conditions
3ff8bf26 714 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 715 $this->assertEqual(2, count($records));
716 $this->assertEqual(3, $records[1]->course);
717 $this->assertEqual(3, $records[2]->course);
718
719 // All records sorted by course
3ff8bf26 720 $records = $DB->get_records($tablename, null, 'course');
0088bd31 721 $this->assertEqual(4, count($records));
722 $current_record = reset($records);
723 $this->assertEqual(4, $current_record->id);
724 $current_record = next($records);
725 $this->assertEqual(1, $current_record->id);
726 $current_record = next($records);
727 $this->assertEqual(2, $current_record->id);
728 $current_record = next($records);
729 $this->assertEqual(3, $current_record->id);
730
731 // All records, but get only one field
3ff8bf26 732 $records = $DB->get_records($tablename, null, '', 'id');
0088bd31 733 $this->assertTrue(empty($records[1]->course));
734 $this->assertFalse(empty($records[1]->id));
735 $this->assertEqual(4, count($records));
3ff8bf26 736
5d91786a 737 // note: delegate limits testing to test_get_records_sql()
0088bd31 738 }
739
740 public function test_get_records_list() {
50a12c87 741 $DB = $this->tdb;
742 $dbman = $DB->get_manager();
743
9d833e93 744 $table = $this->get_test_table();
3ff8bf26 745 $tablename = $table->getName();
746
f9ecb171 747 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
748 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 749 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
750 $dbman->create_table($table);
3ff8bf26 751 $this->tables[$tablename] = $table;
50a12c87 752
3ff8bf26 753 $DB->insert_record($tablename, array('course' => 3));
754 $DB->insert_record($tablename, array('course' => 3));
755 $DB->insert_record($tablename, array('course' => 5));
756 $DB->insert_record($tablename, array('course' => 2));
50a12c87 757
3ff8bf26 758 $this->assertTrue($records = $DB->get_records_list($tablename, 'course', array(3, 2)));
50a12c87 759 $this->assertEqual(3, count($records));
760 $this->assertEqual(1, reset($records)->id);
761 $this->assertEqual(2, next($records)->id);
762 $this->assertEqual(4, next($records)->id);
0088bd31 763
c362878e 764 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
765 $this->assertEqual(0, count($records));
766
5d91786a 767 // note: delegate limits testing to test_get_records_sql()
0088bd31 768 }
769
5d91786a 770 public function test_get_record_select() {
50a12c87 771 $DB = $this->tdb;
772 $dbman = $DB->get_manager();
773
9d833e93 774 $table = $this->get_test_table();
3ff8bf26 775 $tablename = $table->getName();
776
f9ecb171 777 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
778 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 779 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
780 $dbman->create_table($table);
3ff8bf26 781 $this->tables[$tablename] = $table;
50a12c87 782
3ff8bf26 783 $DB->insert_record($tablename, array('course' => 3));
3ff8bf26 784 $DB->insert_record($tablename, array('course' => 2));
50a12c87 785
5d91786a 786 $this->assertTrue($record = $DB->get_record_select($tablename, "id = ?", array(2)));
787
788 $this->assertEqual(2, $record->course);
789
790 // note: delegates limit testing to test_get_records_sql()
791 }
792
793 public function test_get_records_sql() {
794 global $CFG;
795 $DB = $this->tdb;
796 $dbman = $DB->get_manager();
797
798 $table = $this->get_test_table();
799 $tablename = $table->getName();
800
801 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
802 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
803 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
804 $dbman->create_table($table);
805 $this->tables[$tablename] = $table;
806
807 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
808 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
809 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
810 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
811 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
812 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
813 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
814
3ff8bf26 815 $this->assertTrue($records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
50a12c87 816 $this->assertEqual(2, count($records));
5d91786a 817 $this->assertEqual($inskey1, reset($records)->id);
818 $this->assertEqual($inskey4, next($records)->id);
0088bd31 819
16a2a412 820 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
821 $olddebug = $CFG->debug; // Save current debug settings
822 $olddisplay = $CFG->debugdisplay;
823 $CFG->debug = DEBUG_DEVELOPER;
824 $CFG->debugdisplay = true;
758ba89a 825 ob_start(); // hide debug warning
3ff8bf26 826 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {".$tablename."}", null);
758ba89a 827 ob_end_clean();
16a2a412 828 $debuginfo = ob_get_contents();
d83bcdfc 829 $CFG->debug = $olddebug; // Restore original debug settings
830 $CFG->debugdisplay = $olddisplay;
758ba89a 831
5d91786a 832 $this->assertEqual(6, count($records));
758ba89a 833 $this->assertFalse($debuginfo === '');
5d91786a 834
835 // negative limits = no limits
836 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, -1, -1);
837 $this->assertEqual(7, count($records));
838
839 // zero limits = no limits
840 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 0, 0);
841 $this->assertEqual(7, count($records));
842
843 // only limitfrom = skips that number of records
844 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 2, 0);
845 $this->assertEqual(5, count($records));
846 $this->assertEqual($inskey3, reset($records)->id);
847 $this->assertEqual($inskey7, end($records)->id);
848
849 // only limitnum = fetches that number of records
850 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 0, 3);
851 $this->assertEqual(3, count($records));
852 $this->assertEqual($inskey1, reset($records)->id);
853 $this->assertEqual($inskey3, end($records)->id);
854
855 // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones
856 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 3, 2);
857 $this->assertEqual(2, count($records));
858 $this->assertEqual($inskey4, reset($records)->id);
859 $this->assertEqual($inskey5, end($records)->id);
860
861 // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here
0088bd31 862 }
863
864 public function test_get_records_menu() {
50a12c87 865 $DB = $this->tdb;
866 $dbman = $DB->get_manager();
867
9d833e93 868 $table = $this->get_test_table();
3ff8bf26 869 $tablename = $table->getName();
870
f9ecb171 871 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
872 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 873 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
874 $dbman->create_table($table);
3ff8bf26 875 $this->tables[$tablename] = $table;
50a12c87 876
3ff8bf26 877 $DB->insert_record($tablename, array('course' => 3));
878 $DB->insert_record($tablename, array('course' => 3));
879 $DB->insert_record($tablename, array('course' => 5));
880 $DB->insert_record($tablename, array('course' => 2));
50a12c87 881
3ff8bf26 882 $this->assertTrue($records = $DB->get_records_menu($tablename, array('course' => 3)));
50a12c87 883 $this->assertEqual(2, count($records));
884 $this->assertFalse(empty($records[1]));
885 $this->assertFalse(empty($records[2]));
886 $this->assertEqual(3, $records[1]);
887 $this->assertEqual(3, $records[2]);
0088bd31 888
5d91786a 889 // note: delegate limits testing to test_get_records_sql()
0088bd31 890 }
891
892 public function test_get_records_select_menu() {
50a12c87 893 $DB = $this->tdb;
894 $dbman = $DB->get_manager();
895
9d833e93 896 $table = $this->get_test_table();
3ff8bf26 897 $tablename = $table->getName();
898
f9ecb171 899 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
900 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 901 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
902 $dbman->create_table($table);
3ff8bf26 903 $this->tables[$tablename] = $table;
50a12c87 904
3ff8bf26 905 $DB->insert_record($tablename, array('course' => 3));
906 $DB->insert_record($tablename, array('course' => 2));
907 $DB->insert_record($tablename, array('course' => 3));
908 $DB->insert_record($tablename, array('course' => 5));
50a12c87 909
3ff8bf26 910 $this->assertTrue($records = $DB->get_records_select_menu($tablename, "course > ?", array(2)));
50a12c87 911
912 $this->assertEqual(3, count($records));
913 $this->assertFalse(empty($records[1]));
914 $this->assertTrue(empty($records[2]));
915 $this->assertFalse(empty($records[3]));
916 $this->assertFalse(empty($records[4]));
917 $this->assertEqual(3, $records[1]);
918 $this->assertEqual(3, $records[3]);
919 $this->assertEqual(5, $records[4]);
0088bd31 920
5d91786a 921 // note: delegate limits testing to test_get_records_sql()
0088bd31 922 }
923
924 public function test_get_records_sql_menu() {
50a12c87 925 $DB = $this->tdb;
926 $dbman = $DB->get_manager();
927
9d833e93 928 $table = $this->get_test_table();
3ff8bf26 929 $tablename = $table->getName();
930
f9ecb171 931 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
932 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 933 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
934 $dbman->create_table($table);
3ff8bf26 935 $this->tables[$tablename] = $table;
50a12c87 936
3ff8bf26 937 $DB->insert_record($tablename, array('course' => 3));
938 $DB->insert_record($tablename, array('course' => 2));
939 $DB->insert_record($tablename, array('course' => 3));
940 $DB->insert_record($tablename, array('course' => 5));
50a12c87 941
3ff8bf26 942 $this->assertTrue($records = $DB->get_records_sql_menu("SELECT * FROM {".$tablename."} WHERE course > ?", array(2)));
50a12c87 943
944 $this->assertEqual(3, count($records));
945 $this->assertFalse(empty($records[1]));
946 $this->assertTrue(empty($records[2]));
947 $this->assertFalse(empty($records[3]));
948 $this->assertFalse(empty($records[4]));
949 $this->assertEqual(3, $records[1]);
950 $this->assertEqual(3, $records[3]);
951 $this->assertEqual(5, $records[4]);
0088bd31 952
5d91786a 953 // note: delegate limits testing to test_get_records_sql()
0088bd31 954 }
955
956 public function test_get_record() {
50a12c87 957 $DB = $this->tdb;
958 $dbman = $DB->get_manager();
959
9d833e93 960 $table = $this->get_test_table();
3ff8bf26 961 $tablename = $table->getName();
962
f9ecb171 963 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
964 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 965 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
966 $dbman->create_table($table);
3ff8bf26 967 $this->tables[$tablename] = $table;
50a12c87 968
3ff8bf26 969 $DB->insert_record($tablename, array('course' => 3));
970 $DB->insert_record($tablename, array('course' => 2));
50a12c87 971
3ff8bf26 972 $this->assertTrue($record = $DB->get_record($tablename, array('id' => 2)));
0088bd31 973
50a12c87 974 $this->assertEqual(2, $record->course);
0088bd31 975 }
976
0088bd31 977 public function test_get_record_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
f9ecb171 984 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
985 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 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));
991 $DB->insert_record($tablename, array('course' => 2));
50a12c87 992
3ff8bf26 993 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(2)));
50a12c87 994
995 $this->assertEqual(2, $record->course);
0088bd31 996
e6c6531c 997 // backwards compatibility with $ignoremultiple
998 $this->assertFalse(IGNORE_MISSING);
999 $this->assertTrue(IGNORE_MULTIPLE);
1000
af12ea93 1001 // record not found
e6c6531c 1002 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MISSING));
1003 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MULTIPLE));
af12ea93 1004 try {
e6c6531c 1005 $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), MUST_EXIST);
af12ea93 1006 $this->fail("Exception expected");
1007 } catch (dml_missing_record_exception $e) {
1008 $this->assertTrue(true);
1009 }
1010
1011 // multiple matches
1012 ob_start(); // hide debug warning
e6c6531c 1013 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MISSING));
af12ea93 1014 ob_end_clean();
e6c6531c 1015 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MULTIPLE));
af12ea93 1016 try {
e6c6531c 1017 $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), MUST_EXIST);
af12ea93 1018 $this->fail("Exception expected");
1019 } catch (dml_multiple_records_exception $e) {
1020 $this->assertTrue(true);
1021 }
0088bd31 1022 }
1023
1024 public function test_get_field() {
50a12c87 1025 $DB = $this->tdb;
1026 $dbman = $DB->get_manager();
1027
9d833e93 1028 $table = $this->get_test_table();
3ff8bf26 1029 $tablename = $table->getName();
1030
f9ecb171 1031 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1032 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1033 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1034 $dbman->create_table($table);
3ff8bf26 1035 $this->tables[$tablename] = $table;
0088bd31 1036
3ff8bf26 1037 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1038
3ff8bf26 1039 $this->assertTrue($course = $DB->get_field($tablename, 'course', array('id' => 1)));
50a12c87 1040 $this->assertEqual(3, $course);
bc4fd49d
EL
1041
1042 // Add one get_field() example where the field being get is also one condition
1043 // to check we haven't problem with any type of param (specially NAMED ones)
1044 $DB->get_field($tablename, 'course', array('course' => 3));
1045 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('course' => 3)));
0088bd31 1046 }
1047
1048 public function test_get_field_select() {
50a12c87 1049 $DB = $this->tdb;
1050 $dbman = $DB->get_manager();
1051
9d833e93 1052 $table = $this->get_test_table();
3ff8bf26 1053 $tablename = $table->getName();
1054
f9ecb171 1055 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1056 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1057 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1058 $dbman->create_table($table);
3ff8bf26 1059 $this->tables[$tablename] = $table;
50a12c87 1060
3ff8bf26 1061 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1062
3ff8bf26 1063 $this->assertTrue($course = $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
50a12c87 1064 $this->assertEqual(3, $course);
0088bd31 1065
1066 }
1067
1068 public function test_get_field_sql() {
50a12c87 1069 $DB = $this->tdb;
1070 $dbman = $DB->get_manager();
1071
9d833e93 1072 $table = $this->get_test_table();
3ff8bf26 1073 $tablename = $table->getName();
1074
f9ecb171 1075 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1076 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 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 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1082
3ff8bf26 1083 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {".$tablename."} WHERE id = ?", array(1)));
50a12c87 1084 $this->assertEqual(3, $course);
0088bd31 1085
1086 }
1087
50a12c87 1088 public function test_get_fieldset_select() {
1089 $DB = $this->tdb;
1090 $dbman = $DB->get_manager();
1091
9d833e93 1092 $table = $this->get_test_table();
3ff8bf26 1093 $tablename = $table->getName();
1094
f9ecb171 1095 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1096 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1097 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1098 $dbman->create_table($table);
3ff8bf26 1099 $this->tables[$tablename] = $table;
50a12c87 1100
3ff8bf26 1101 $DB->insert_record($tablename, array('course' => 1));
1102 $DB->insert_record($tablename, array('course' => 3));
1103 $DB->insert_record($tablename, array('course' => 2));
1104 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1105
3ff8bf26 1106 $this->assertTrue($fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)));
50a12c87 1107
1108 $this->assertEqual(3, count($fieldset));
1109 $this->assertEqual(3, $fieldset[0]);
1110 $this->assertEqual(2, $fieldset[1]);
1111 $this->assertEqual(6, $fieldset[2]);
0088bd31 1112
1113 }
1114
1115 public function test_get_fieldset_sql() {
50a12c87 1116 $DB = $this->tdb;
1117 $dbman = $DB->get_manager();
0088bd31 1118
9d833e93 1119 $table = $this->get_test_table();
3ff8bf26 1120 $tablename = $table->getName();
1121
f9ecb171 1122 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1123 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1124 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1125 $dbman->create_table($table);
3ff8bf26 1126 $this->tables[$tablename] = $table;
50a12c87 1127
3ff8bf26 1128 $DB->insert_record($tablename, array('course' => 1));
1129 $DB->insert_record($tablename, array('course' => 3));
1130 $DB->insert_record($tablename, array('course' => 2));
1131 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1132
3ff8bf26 1133 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {".$tablename."} WHERE course > ?", array(1)));
50a12c87 1134
1135 $this->assertEqual(3, count($fieldset));
1136 $this->assertEqual(2, $fieldset[0]);
1137 $this->assertEqual(3, $fieldset[1]);
1138 $this->assertEqual(4, $fieldset[2]);
0088bd31 1139 }
1140
1141 public function test_insert_record_raw() {
50a12c87 1142 $DB = $this->tdb;
1143 $dbman = $DB->get_manager();
0088bd31 1144
9d833e93 1145 $table = $this->get_test_table();
3ff8bf26 1146 $tablename = $table->getName();
1147
f9ecb171 1148 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1149 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1150 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1151 $dbman->create_table($table);
3ff8bf26 1152 $this->tables[$tablename] = $table;
50a12c87 1153
3ff8bf26 1154 $this->assertTrue($DB->insert_record_raw($tablename, array('course' => 1)));
1155 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 1)));
50a12c87 1156 $this->assertEqual(1, $record->course);
0088bd31 1157 }
1158
1159 public function test_insert_record() {
628dff25 1160
1161 // All the information in this test is fetched from DB by get_recordset() so we
1162 // have such method properly tested against nulls, empties and friends...
1163
1164 global $CFG;
1165
50a12c87 1166 $DB = $this->tdb;
1167 $dbman = $DB->get_manager();
1168
9d833e93 1169 $table = $this->get_test_table();
3ff8bf26 1170 $tablename = $table->getName();
1171
f9ecb171 1172 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1173 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1174 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1175 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1176 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1177 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1178 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1179 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1180 $dbman->create_table($table);
3ff8bf26 1181 $this->tables[$tablename] = $table;
50a12c87 1182
628dff25 1183 $this->assertTrue($DB->insert_record($tablename, array('course' => 1), false)); // Without returning id
1184 $rs = $DB->get_recordset($tablename, array('course' => 1));
1185 $record = $rs->current();
1186 $rs->close();
1187 $this->assertEqual(1, $record->id);
1188 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1189 $this->assertEqual(200, $record->onenum);
1190 $this->assertEqual('onestring', $record->onechar);
1191 $this->assertNull($record->onetext);
1192 $this->assertNull($record->onebinary);
1193
1194 // Check nulls are set properly for all types
1195 $record->oneint = null;
1196 $record->onenum = null;
1197 $record->onechar = null;
1198 $record->onetext = null;
1199 $record->onebinary = null;
1200 $recid = $DB->insert_record($tablename, $record);
1201 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1202 $record = $rs->current();
1203 $rs->close();
1204 $this->assertNull($record->oneint);
1205 $this->assertNull($record->onenum);
1206 $this->assertNull($record->onechar);
1207 $this->assertNull($record->onetext);
1208 $this->assertNull($record->onebinary);
1209
1210 // Check zeros are set properly for all types
1211 $record->oneint = 0;
1212 $record->onenum = 0;
1213 $recid = $DB->insert_record($tablename, $record);
1214 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1215 $record = $rs->current();
1216 $rs->close();
1217 $this->assertEqual(0, $record->oneint);
1218 $this->assertEqual(0, $record->onenum);
1219
1220 // Check booleans are set properly for all types
1221 $record->oneint = true; // trues
1222 $record->onenum = true;
1223 $record->onechar = true;
1224 $record->onetext = true;
1225 $recid = $DB->insert_record($tablename, $record);
1226 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1227 $record = $rs->current();
1228 $rs->close();
1229 $this->assertEqual(1, $record->oneint);
1230 $this->assertEqual(1, $record->onenum);
1231 $this->assertEqual(1, $record->onechar);
1232 $this->assertEqual(1, $record->onetext);
1233
1234 $record->oneint = false; // falses
1235 $record->onenum = false;
1236 $record->onechar = false;
1237 $record->onetext = false;
1238 $recid = $DB->insert_record($tablename, $record);
1239 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1240 $record = $rs->current();
1241 $rs->close();
1242 $this->assertEqual(0, $record->oneint);
1243 $this->assertEqual(0, $record->onenum);
1244 $this->assertEqual(0, $record->onechar);
1245 $this->assertEqual(0, $record->onetext);
1246
1247 // Check string data causes exception in numeric types
1248 $record->oneint = 'onestring';
1249 $record->onenum = 0;
1250 try {
1251 $DB->insert_record($tablename, $record);
1252 $this->fail("Expecting an exception, none occurred");
1253 } catch (exception $e) {
1254 $this->assertTrue($e instanceof dml_exception);
1255 }
1256 $record->oneint = 0;
1257 $record->onenum = 'onestring';
1258 try {
1259 $DB->insert_record($tablename, $record);
1260 $this->fail("Expecting an exception, none occurred");
1261 } catch (exception $e) {
1262 $this->assertTrue($e instanceof dml_exception);
1263 }
1264
2f9c1693 1265 // Check empty string data is stored as 0 in numeric datatypes
fdc45ac3
EL
1266 $record->oneint = ''; // empty string
1267 $record->onenum = 0;
2f9c1693
EL
1268 $recid = $DB->insert_record($tablename, $record);
1269 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1270 $record = $rs->current();
1271 $rs->close();
1272 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1273
fdc45ac3
EL
1274 $record->oneint = 0;
1275 $record->onenum = ''; // empty string
2f9c1693
EL
1276 $recid = $DB->insert_record($tablename, $record);
1277 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1278 $record = $rs->current();
1279 $rs->close();
1280 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
fdc45ac3 1281
628dff25 1282 // Check empty strings are set properly in string types
1283 $record->oneint = 0;
1284 $record->onenum = 0;
1285 $record->onechar = '';
1286 $record->onetext = '';
1287 $recid = $DB->insert_record($tablename, $record);
1288 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1289 $record = $rs->current();
1290 $rs->close();
1291 $this->assertTrue($record->onechar === '');
1292 $this->assertTrue($record->onetext === '');
1293
1294 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1295 $record->oneint = ((210.10 + 39.92) - 150.02);
1296 $record->onenum = ((210.10 + 39.92) - 150.02);
1297 $recid = $DB->insert_record($tablename, $record);
1298 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1299 $record = $rs->current();
1300 $rs->close();
1301 $this->assertEqual(100, $record->oneint);
1302 $this->assertEqual(100, $record->onenum);
1303
1304 // Check various quotes/backslashes combinations in string types
1305 $teststrings = array(
1306 'backslashes and quotes alone (even): "" \'\' \\\\',
1307 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1308 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1309 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1310 foreach ($teststrings as $teststring) {
1311 $record->onechar = $teststring;
1312 $record->onetext = $teststring;
1313 $recid = $DB->insert_record($tablename, $record);
1314 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1315 $record = $rs->current();
1316 $rs->close();
1317 $this->assertEqual($teststring, $record->onechar);
1318 $this->assertEqual($teststring, $record->onetext);
1319 }
1320
1321 // Check LOBs in text/binary columns
1322 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1323 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1324 $record->onetext = $clob;
1325 $record->onebinary = $blob;
1326 $recid = $DB->insert_record($tablename, $record);
1327 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1328 $record = $rs->current();
1329 $rs->close();
ff8c7b2a
EL
1330 $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
1331 $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
0088bd31 1332
628dff25 1333 // And "small" LOBs too, just in case
1334 $newclob = substr($clob, 0, 500);
1335 $newblob = substr($blob, 0, 250);
1336 $record->onetext = $newclob;
1337 $record->onebinary = $newblob;
1338 $recid = $DB->insert_record($tablename, $record);
1339 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1340 $record = $rs->current();
1341 $rs->close();
ff8c7b2a
EL
1342 $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
1343 $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
5d91786a 1344 $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
e9fec42f
PS
1345
1346 // test data is not modified
1347 $rec = new object();
1348 $rec->id = -1; // has to be ignored
1349 $rec->course = 3;
1350 $rec->lalala = 'lalal'; // unused
1351 $before = clone($rec);
1352 $DB->insert_record($tablename, $record);
1353 $this->assertEqual($rec, $before);
0088bd31 1354 }
1355
94898738 1356 public function test_import_record() {
1357 $DB = $this->tdb;
1358 $dbman = $DB->get_manager();
1359
9d833e93 1360 $table = $this->get_test_table();
3ff8bf26 1361 $tablename = $table->getName();
1362
f9ecb171 1363 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1364 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
94898738 1365 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1366 $dbman->create_table($table);
3ff8bf26 1367 $this->tables[$tablename] = $table;
94898738 1368
1369 $record = (object)array('id'=>666, 'course'=>10);
3ff8bf26 1370 $this->assertTrue($DB->import_record($tablename, $record));
1371 $records = $DB->get_records($tablename);
94898738 1372 $this->assertEqual(1, count($records));
1373 $this->assertEqual(10, $records[666]->course);
1374
1375 $record = (object)array('id'=>13, 'course'=>2);
3ff8bf26 1376 $this->assertTrue($DB->import_record($tablename, $record));
1377 $records = $DB->get_records($tablename);
94898738 1378 $this->assertEqual(2, $records[13]->course);
1379 }
1380
0088bd31 1381 public function test_update_record_raw() {
50a12c87 1382 $DB = $this->tdb;
1383 $dbman = $DB->get_manager();
0088bd31 1384
9d833e93 1385 $table = $this->get_test_table();
3ff8bf26 1386 $tablename = $table->getName();
1387
f9ecb171 1388 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1389 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1390 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1391 $dbman->create_table($table);
3ff8bf26 1392 $this->tables[$tablename] = $table;
50a12c87 1393
3ff8bf26 1394 $DB->insert_record($tablename, array('course' => 1));
1395 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1396 $record->course = 2;
3ff8bf26 1397 $this->assertTrue($DB->update_record_raw($tablename, $record));
1398 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1399 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1400 }
1401
1402 public function test_update_record() {
628dff25 1403
1404 // All the information in this test is fetched from DB by get_record() so we
1405 // have such method properly tested against nulls, empties and friends...
1406
1407 global $CFG;
1408
50a12c87 1409 $DB = $this->tdb;
1410 $dbman = $DB->get_manager();
1411
9d833e93 1412 $table = $this->get_test_table();
3ff8bf26 1413 $tablename = $table->getName();
1414
f9ecb171 1415 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1416 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1417 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1418 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1419 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1420 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1421 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1422 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1423 $dbman->create_table($table);
3ff8bf26 1424 $this->tables[$tablename] = $table;
0088bd31 1425
3ff8bf26 1426 $DB->insert_record($tablename, array('course' => 1));
1427 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1428 $record->course = 2;
628dff25 1429
3ff8bf26 1430 $this->assertTrue($DB->update_record($tablename, $record));
1431 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1432 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
628dff25 1433 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1434 $this->assertEqual(200, $record->onenum);
1435 $this->assertEqual('onestring', $record->onechar);
1436 $this->assertNull($record->onetext);
1437 $this->assertNull($record->onebinary);
c824e1e1 1438
628dff25 1439 // Check nulls are set properly for all types
1440 $record->oneint = null;
1441 $record->onenum = null;
1442 $record->onechar = null;
1443 $record->onetext = null;
1444 $record->onebinary = null;
1445 $DB->update_record($tablename, $record);
1446 $record = $DB->get_record($tablename, array('course' => 2));
1447 $this->assertNull($record->oneint);
1448 $this->assertNull($record->onenum);
1449 $this->assertNull($record->onechar);
1450 $this->assertNull($record->onetext);
1451 $this->assertNull($record->onebinary);
c824e1e1 1452
628dff25 1453 // Check zeros are set properly for all types
1454 $record->oneint = 0;
1455 $record->onenum = 0;
1456 $DB->update_record($tablename, $record);
1457 $record = $DB->get_record($tablename, array('course' => 2));
1458 $this->assertEqual(0, $record->oneint);
1459 $this->assertEqual(0, $record->onenum);
c824e1e1 1460
628dff25 1461 // Check booleans are set properly for all types
1462 $record->oneint = true; // trues
1463 $record->onenum = true;
1464 $record->onechar = true;
1465 $record->onetext = true;
1466 $DB->update_record($tablename, $record);
1467 $record = $DB->get_record($tablename, array('course' => 2));
1468 $this->assertEqual(1, $record->oneint);
1469 $this->assertEqual(1, $record->onenum);
1470 $this->assertEqual(1, $record->onechar);
1471 $this->assertEqual(1, $record->onetext);
1472
1473 $record->oneint = false; // falses
1474 $record->onenum = false;
1475 $record->onechar = false;
1476 $record->onetext = false;
1477 $DB->update_record($tablename, $record);
1478 $record = $DB->get_record($tablename, array('course' => 2));
1479 $this->assertEqual(0, $record->oneint);
1480 $this->assertEqual(0, $record->onenum);
1481 $this->assertEqual(0, $record->onechar);
1482 $this->assertEqual(0, $record->onetext);
c824e1e1 1483
628dff25 1484 // Check string data causes exception in numeric types
1485 $record->oneint = 'onestring';
1486 $record->onenum = 0;
1487 try {
1488 $DB->update_record($tablename, $record);
1489 $this->fail("Expecting an exception, none occurred");
1490 } catch (exception $e) {
1491 $this->assertTrue($e instanceof dml_exception);
1492 }
1493 $record->oneint = 0;
1494 $record->onenum = 'onestring';
1495 try {
1496 $DB->update_record($tablename, $record);
1497 $this->fail("Expecting an exception, none occurred");
1498 } catch (exception $e) {
1499 $this->assertTrue($e instanceof dml_exception);
1500 }
c824e1e1 1501
2f9c1693 1502 // Check empty string data is stored as 0 in numeric datatypes
fdc45ac3
EL
1503 $record->oneint = ''; // empty string
1504 $record->onenum = 0;
2f9c1693
EL
1505 $DB->update_record($tablename, $record);
1506 $record = $DB->get_record($tablename, array('course' => 2));
1507 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1508
fdc45ac3
EL
1509 $record->oneint = 0;
1510 $record->onenum = ''; // empty string
2f9c1693
EL
1511 $DB->update_record($tablename, $record);
1512 $record = $DB->get_record($tablename, array('course' => 2));
1513 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
fdc45ac3 1514
628dff25 1515 // Check empty strings are set properly in string types
1516 $record->oneint = 0;
1517 $record->onenum = 0;
1518 $record->onechar = '';
1519 $record->onetext = '';
1520 $DB->update_record($tablename, $record);
1521 $record = $DB->get_record($tablename, array('course' => 2));
1522 $this->assertTrue($record->onechar === '');
1523 $this->assertTrue($record->onetext === '');
c824e1e1 1524
628dff25 1525 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1526 $record->oneint = ((210.10 + 39.92) - 150.02);
1527 $record->onenum = ((210.10 + 39.92) - 150.02);
1528 $DB->update_record($tablename, $record);
1529 $record = $DB->get_record($tablename, array('course' => 2));
1530 $this->assertEqual(100, $record->oneint);
1531 $this->assertEqual(100, $record->onenum);
3ff8bf26 1532
628dff25 1533 // Check various quotes/backslashes combinations in string types
1534 $teststrings = array(
1535 'backslashes and quotes alone (even): "" \'\' \\\\',
1536 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1537 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1538 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1539 foreach ($teststrings as $teststring) {
1540 $record->onechar = $teststring;
1541 $record->onetext = $teststring;
1542 $DB->update_record($tablename, $record);
1543 $record = $DB->get_record($tablename, array('course' => 2));
1544 $this->assertEqual($teststring, $record->onechar);
1545 $this->assertEqual($teststring, $record->onetext);
1546 }
c824e1e1 1547
628dff25 1548 // Check LOBs in text/binary columns
6ff835b7 1549 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1550 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
628dff25 1551 $record->onetext = $clob;
1552 $record->onebinary = $blob;
1553 $DB->update_record($tablename, $record);
1554 $record = $DB->get_record($tablename, array('course' => 2));
ff8c7b2a
EL
1555 $this->assertEqual($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
1556 $this->assertEqual($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
628dff25 1557
1558 // And "small" LOBs too, just in case
d246cdd2 1559 $newclob = substr($clob, 0, 500);
1560 $newblob = substr($blob, 0, 250);
628dff25 1561 $record->onetext = $newclob;
1562 $record->onebinary = $newblob;
1563 $DB->update_record($tablename, $record);
1564 $record = $DB->get_record($tablename, array('course' => 2));
ff8c7b2a
EL
1565 $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
1566 $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
c824e1e1 1567 }
1568
0088bd31 1569 public function test_set_field() {
50a12c87 1570 $DB = $this->tdb;
1571 $dbman = $DB->get_manager();
1572
9d833e93 1573 $table = $this->get_test_table();
3ff8bf26 1574 $tablename = $table->getName();
1575
f9ecb171 1576 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1577 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1578 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1579 $dbman->create_table($table);
3ff8bf26 1580 $this->tables[$tablename] = $table;
0088bd31 1581
3ff8bf26 1582 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1583
3ff8bf26 1584 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1585 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
fbabe35a 1586
bc4fd49d
EL
1587 // Add one set_field() example where the field being set is also one condition
1588 // to check we haven't problem with any type of param (specially NAMED ones)
1589 $DB->set_field($tablename, 'course', '1', array('course' => 2));
1590 $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => 1)));
1591
fbabe35a 1592 // Note: All the nulls, booleans, empties, quoted and backslashes tests
1593 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 1594 }
1595
1596 public function test_set_field_select() {
628dff25 1597
1598 // All the information in this test is fetched from DB by get_field() so we
1599 // have such method properly tested against nulls, empties and friends...
1600
fbabe35a 1601 global $CFG;
1602
50a12c87 1603 $DB = $this->tdb;
1604 $dbman = $DB->get_manager();
1605
9d833e93 1606 $table = $this->get_test_table();
3ff8bf26 1607 $tablename = $table->getName();
1608
f9ecb171 1609 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1610 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 1611 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
1612 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
1613 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
1614 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1615 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1616 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1617 $dbman->create_table($table);
3ff8bf26 1618 $this->tables[$tablename] = $table;
50a12c87 1619
3ff8bf26 1620 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1621
3ff8bf26 1622 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1623 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1624
fbabe35a 1625 // Check nulls are set properly for all types
628dff25 1626 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
1627 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
1628 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
1629 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
1630 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
fbabe35a 1631 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
1632 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
1633 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
1634 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
1635 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
1636
1637 // Check zeros are set properly for all types
1638 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
1639 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
1640 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1641 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1642
1643 // Check booleans are set properly for all types
1644 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
1645 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
1646 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
1647 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
1648 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1649 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1650 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1651 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1652
1653 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
1654 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
1655 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
1656 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
1657 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1658 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1659 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1660 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1661
1662 // Check string data causes exception in numeric types
1663 try {
1664 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
1665 $this->fail("Expecting an exception, none occurred");
1666 } catch (exception $e) {
1667 $this->assertTrue($e instanceof dml_exception);
1668 }
1669 try {
1670 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
1671 $this->fail("Expecting an exception, none occurred");
1672 } catch (exception $e) {
1673 $this->assertTrue($e instanceof dml_exception);
1674 }
1675
2f9c1693
EL
1676 // Check empty string data is stored as 0 in numeric datatypes
1677 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
1678 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
1679 $this->assertTrue(is_numeric($field) && $field == 0);
1680
1681 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
1682 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
1683 $this->assertTrue(is_numeric($field) && $field == 0);
fdc45ac3 1684
fbabe35a 1685 // Check empty strings are set properly in string types
1686 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
1687 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
1688 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
1689 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
1690
1691 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1692 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1693 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1694 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1695 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1696
1697 // Check various quotes/backslashes combinations in string types
1698 $teststrings = array(
628dff25 1699 'backslashes and quotes alone (even): "" \'\' \\\\',
1700 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1701 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1702 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
fbabe35a 1703 foreach ($teststrings as $teststring) {
1704 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
1705 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
1706 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1707 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1708 }
1709
1710 // Check LOBs in text/binary columns
1711 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1712 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1713 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
1714 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
ff8c7b2a
EL
1715 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
1716 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
fbabe35a 1717
1718 // And "small" LOBs too, just in case
1719 $newclob = substr($clob, 0, 500);
1720 $newblob = substr($blob, 0, 250);
1721 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
1722 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
ff8c7b2a
EL
1723 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
1724 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
0088bd31 1725 }
1726
1727 public function test_count_records() {
1728 $DB = $this->tdb;
1729
1730 $dbman = $DB->get_manager();
1731
9d833e93 1732 $table = $this->get_test_table();
3ff8bf26 1733 $tablename = $table->getName();
1734
f9ecb171 1735 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1736 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1737 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1738 $dbman->create_table($table);
3ff8bf26 1739 $this->tables[$tablename] = $table;
0088bd31 1740
3ff8bf26 1741 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1742
3ff8bf26 1743 $DB->insert_record($tablename, array('course' => 3));
1744 $DB->insert_record($tablename, array('course' => 4));
1745 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1746
3ff8bf26 1747 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1748 }
1749
1750 public function test_count_records_select() {
50a12c87 1751 $DB = $this->tdb;
0088bd31 1752
50a12c87 1753 $dbman = $DB->get_manager();
1754
9d833e93 1755 $table = $this->get_test_table();
3ff8bf26 1756 $tablename = $table->getName();
1757
f9ecb171 1758 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1759 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1760 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1761 $dbman->create_table($table);
3ff8bf26 1762 $this->tables[$tablename] = $table;
50a12c87 1763
3ff8bf26 1764 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1765
3ff8bf26 1766 $DB->insert_record($tablename, array('course' => 3));
1767 $DB->insert_record($tablename, array('course' => 4));
1768 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1769
3ff8bf26 1770 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1771 }
1772
1773 public function test_count_records_sql() {
50a12c87 1774 $DB = $this->tdb;
1775 $dbman = $DB->get_manager();
1776
9d833e93 1777 $table = $this->get_test_table();
3ff8bf26 1778 $tablename = $table->getName();
1779
f9ecb171 1780 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1781 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1782 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1783 $dbman->create_table($table);
3ff8bf26 1784 $this->tables[$tablename] = $table;
50a12c87 1785
3ff8bf26 1786 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1787
3ff8bf26 1788 $DB->insert_record($tablename, array('course' => 3));
1789 $DB->insert_record($tablename, array('course' => 4));
1790 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1791
3ff8bf26 1792 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1793 }
1794
1795 public function test_record_exists() {
50a12c87 1796 $DB = $this->tdb;
1797 $dbman = $DB->get_manager();
1798
9d833e93 1799 $table = $this->get_test_table();
3ff8bf26 1800 $tablename = $table->getName();
1801
f9ecb171 1802 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1803 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1804 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1805 $dbman->create_table($table);
3ff8bf26 1806 $this->tables[$tablename] = $table;
50a12c87 1807
3ff8bf26 1808 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1809
3ff8bf26 1810 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1811 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1812
3ff8bf26 1813 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1814
1815 }
1816
1817 public function test_record_exists_select() {
50a12c87 1818 $DB = $this->tdb;
1819 $dbman = $DB->get_manager();
0088bd31 1820
9d833e93 1821 $table = $this->get_test_table();
3ff8bf26 1822 $tablename = $table->getName();
1823
f9ecb171 1824 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1825 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1826 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1827 $dbman->create_table($table);
3ff8bf26 1828 $this->tables[$tablename] = $table;
50a12c87 1829
3ff8bf26 1830 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1831
3ff8bf26 1832 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1833 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1834
3ff8bf26 1835 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1836 }
1837
1838 public function test_record_exists_sql() {
50a12c87 1839 $DB = $this->tdb;
1840 $dbman = $DB->get_manager();
0088bd31 1841
9d833e93 1842 $table = $this->get_test_table();
3ff8bf26 1843 $tablename = $table->getName();
1844
f9ecb171 1845 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1846 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1847 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1848 $dbman->create_table($table);
3ff8bf26 1849 $this->tables[$tablename] = $table;
50a12c87 1850
3ff8bf26 1851 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1852
3ff8bf26 1853 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1854 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1855
3ff8bf26 1856 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1857 }
1858
1859 public function test_delete_records() {
50a12c87 1860 $DB = $this->tdb;
1861 $dbman = $DB->get_manager();
0088bd31 1862
9d833e93 1863 $table = $this->get_test_table();
3ff8bf26 1864 $tablename = $table->getName();
1865
f9ecb171 1866 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1867 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1868 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1869 $dbman->create_table($table);
3ff8bf26 1870 $this->tables[$tablename] = $table;
50a12c87 1871
3ff8bf26 1872 $DB->insert_record($tablename, array('course' => 3));
1873 $DB->insert_record($tablename, array('course' => 2));
1874 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1875
1876 // Delete all records
3ff8bf26 1877 $this->assertTrue($DB->delete_records($tablename));
1878 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1879
1880 // Delete subset of records
3ff8bf26 1881 $DB->insert_record($tablename, array('course' => 3));
1882 $DB->insert_record($tablename, array('course' => 2));
1883 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1884
3ff8bf26 1885 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1886 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1887 }
1888
1889 public function test_delete_records_select() {
50a12c87 1890 $DB = $this->tdb;
1891 $dbman = $DB->get_manager();
0088bd31 1892
9d833e93 1893 $table = $this->get_test_table();
3ff8bf26 1894 $tablename = $table->getName();
1895
f9ecb171 1896 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1897 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1898 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1899 $dbman->create_table($table);
3ff8bf26 1900 $this->tables[$tablename] = $table;
0088bd31 1901
3ff8bf26 1902 $DB->insert_record($tablename, array('course' => 3));
1903 $DB->insert_record($tablename, array('course' => 2));
1904 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1905
3ff8bf26 1906 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1907 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1908 }
1d861fce 1909
c362878e 1910 public function test_delete_records_list() {
1911 $DB = $this->tdb;
1912 $dbman = $DB->get_manager();
1913
1914 $table = $this->get_test_table();
1915 $tablename = $table->getName();
1916
f9ecb171 1917 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1918 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 1919 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1920 $dbman->create_table($table);
1921 $this->tables[$tablename] = $table;
1922
1923 $DB->insert_record($tablename, array('course' => 1));
1924 $DB->insert_record($tablename, array('course' => 2));
1925 $DB->insert_record($tablename, array('course' => 3));
1926
1927 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1928 $this->assertEqual(1, $DB->count_records($tablename));
1929
1930 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1931 $this->assertEqual(1, $DB->count_records($tablename));
1932 }
1933
082ae821 1934 function test_sql_null_from_clause() {
655bbf51 1935 $DB = $this->tdb;
082ae821 1936 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1937 $this->assertEqual($DB->get_field_sql($sql), 1);
1938 }
655bbf51 1939
082ae821 1940 function test_sql_bitand() {
1941 $DB = $this->tdb;
9d833e93 1942 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1943 $this->assertEqual($DB->get_field_sql($sql), 2);
1944 }
655bbf51 1945
082ae821 1946 function test_sql_bitnot() {
1947 $DB = $this->tdb;
655bbf51 1948
082ae821 1949 $not = $DB->sql_bitnot(2);
1950 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1951
9d833e93 1952 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1953 $this->assertEqual($DB->get_field_sql($sql), 5);
1954 }
655bbf51 1955
082ae821 1956 function test_sql_bitor() {
1957 $DB = $this->tdb;
9d833e93 1958 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1959 $this->assertEqual($DB->get_field_sql($sql), 11);
1960 }
655bbf51 1961
082ae821 1962 function test_sql_bitxor() {
1963 $DB = $this->tdb;
9d833e93 1964 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1965 $this->assertEqual($DB->get_field_sql($sql), 9);
1966 }
1967
e6df3734 1968 function test_sql_modulo() {
1969 $DB = $this->tdb;
9d833e93 1970 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1971 $this->assertEqual($DB->get_field_sql($sql), 3);
1972 }
1973
082ae821 1974 function test_sql_ceil() {
1975 $DB = $this->tdb;
9d833e93 1976 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1977 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1978 }
1979
6ff835b7 1980 function test_cast_char2int() {
1981 $DB = $this->tdb;
1982 $dbman = $DB->get_manager();
1983
9d833e93 1984 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1985 $tablename1 = $table1->getName();
1986
f9ecb171 1987 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1988 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1989 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1990 $dbman->create_table($table1);
3ff8bf26 1991 $this->tables[$tablename1] = $table1;
6ff835b7 1992
3ff8bf26 1993 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1994
9d833e93 1995 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1996 $tablename2 = $table2->getName();
f9ecb171 1997 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1998 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 1999 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2000 $dbman->create_table($table2);
2001 $this->tables[$table2->getName()] = $table2;
2002
9d833e93 2003 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 2004
2005 try {
9d833e93 2006 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 2007 $records = $DB->get_records_sql($sql);
2008 $this->assertEqual(count($records), 1);
2009 } catch (dml_exception $e) {
2010 $this->fail("No exception expected");
2011 }
2012 }
2013
2014 function test_cast_char2real() {
2015 $DB = $this->tdb;
2016 $dbman = $DB->get_manager();
2017
9d833e93 2018 $table = $this->get_test_table();
3ff8bf26 2019 $tablename = $table->getName();
2020
f9ecb171 2021 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2022 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2023 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 2024 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2025 $dbman->create_table($table);
3ff8bf26 2026 $this->tables[$tablename] = $table;
6ff835b7 2027
9d833e93 2028 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
2029 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
2030 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 2031
9d833e93 2032 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 2033 $records = $DB->get_records_sql($sql);
2034 $this->assertEqual(count($records), 2);
2035 }
2036
082ae821 2037 function sql_compare_text() {
2038 $DB = $this->tdb;
2039 $dbman = $DB->get_manager();
2040
9d833e93 2041 $table = $this->get_test_table();
3ff8bf26 2042 $tablename = $table->getName();
2043
f9ecb171 2044 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2045 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2046 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2047 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2048 $dbman->create_table($table);
3ff8bf26 2049 $this->tables[$tablename] = $table;
082ae821 2050
3ff8bf26 2051 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
2052 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
2053 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 2054
3ff8bf26 2055 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 2056 $records = $DB->get_records_sql($sql);
2057 $this->assertEqual(count($records), 1);
2058
3ff8bf26 2059 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 2060 $records = $DB->get_records_sql($sql);
2061 $this->assertEqual(count($records), 2);
2062 }
2063
6ff835b7 2064 function test_ilike() {
2065 $DB = $this->tdb;
2066 $dbman = $DB->get_manager();
2067
9d833e93 2068 $table = $this->get_test_table();
3ff8bf26 2069 $tablename = $table->getName();
2070
f9ecb171 2071 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2072 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2073 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2074 $dbman->create_table($table);
3ff8bf26 2075 $this->tables[$tablename] = $table;
6ff835b7 2076
3ff8bf26 2077 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2078 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2079 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2080
3ff8bf26 2081 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2082 $params = array("%dup_r%");
2083 $records = $DB->get_records_sql($sql, $params);
2084 $this->assertEqual(count($records), 2);
2085 }
2086
71a71d59 2087 function test_sql_concat() {
6ff835b7 2088 $DB = $this->tdb;
71a71d59 2089 $dbman = $DB->get_manager();
2090
2091 /// Testing all sort of values
2092 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2093 // string, some unicode chars
2094 $params = array('name', 'áéíóú', 'name3');
2095 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2096 // string, spaces and numbers
2097 $params = array('name', ' ', 12345);
2098 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2099 // float, empty and strings
2100 $params = array(123.45, '', 'test');
2101 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2102 // float, null and strings
2103 $params = array(123.45, null, 'test');
52a01626 2104 $this->assertNull($DB->get_field_sql($sql, $params), 'ANSI behaviour: Concatenating NULL must return NULL - But in Oracle :-(. [%s]'); // Concatenate NULL with anything result = NULL
71a71d59 2105
2106 /// Testing fieldnames + values
2107 $table = $this->get_test_table();
2108 $tablename = $table->getName();
2109
2110 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2111 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2112 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2113 $dbman->create_table($table);
2114 $this->tables[$tablename] = $table;
2115
2116 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2117 $DB->insert_record($tablename, array('description'=>'dxxx'));
2118 $DB->insert_record($tablename, array('description'=>'bcde'));
2119
2120 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2121 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2122 $this->assertEqual(count($records), 3);
2123 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2124 }
2125
082ae821 2126 function test_concat_join() {
6ff835b7 2127 $DB = $this->tdb;
082ae821 2128 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2129 $params = array("name", "name2", "name3");
2130 $result = $DB->get_field_sql($sql, $params);
2131 $this->assertEqual("name name2 name3", $result);
2132 }
2133
2134 function test_sql_fullname() {
2135 $DB = $this->tdb;
2136 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2137 $params = array('first'=>'Firstname', 'last'=>'Surname');
2138 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2139 }
2140
2141 function sql_sql_order_by_text() {
2142 $DB = $this->tdb;
2143 $dbman = $DB->get_manager();
2144
9d833e93 2145 $table = $this->get_test_table();
3ff8bf26 2146 $tablename = $table->getName();
2147
f9ecb171 2148 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2149 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2150 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2151 $dbman->create_table($table);
3ff8bf26 2152 $this->tables[$tablename] = $table;
082ae821 2153
3ff8bf26 2154 $DB->insert_record($tablename, array('description'=>'abcd'));
2155 $DB->insert_record($tablename, array('description'=>'dxxx'));
2156 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2157
3ff8bf26 2158 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2159 $records = $DB->get_records_sql($sql);
2160 $first = array_unshift($records);
2161 $this->assertEqual(1, $first->id);
2162 $second = array_unshift($records);
2163 $this->assertEqual(3, $second->id);
2164 $last = array_unshift($records);
2165 $this->assertEqual(2, $last->id);
2166 }
2167
2168 function test_sql_substring() {
2169 $DB = $this->tdb;
2170 $dbman = $DB->get_manager();
2171
9d833e93 2172 $table = $this->get_test_table();
3ff8bf26 2173 $tablename = $table->getName();
2174
f9ecb171 2175 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2176 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 2177 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2178 $dbman->create_table($table);
3ff8bf26 2179 $this->tables[$tablename] = $table;
082ae821 2180
2181 $string = 'abcdefghij';
2182
3ff8bf26 2183 $DB->insert_record($tablename, array('name'=>$string));
082ae821 2184
3ff8bf26 2185 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 2186 $record = $DB->get_record_sql($sql);
2187 $this->assertEqual(substr($string, 5-1), $record->name);
2188
3ff8bf26 2189 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 2190 $record = $DB->get_record_sql($sql);
2191 $this->assertEqual(substr($string, 5-1, 2), $record->name);
2192
2193 try {
2194 // silence php warning ;-)
2195 @$DB->sql_substr("name");
2196 $this->fail("Expecting an exception, none occurred");
2197 } catch (Exception $e) {
2198 $this->assertTrue($e instanceof coding_exception);
2199 }
6ff835b7 2200 }
2201
37d975e1 2202 function test_sql_length() {
2203 $DB = $this->tdb;
2204 $this->assertEqual($DB->get_field_sql(
2205 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
2206 $this->assertEqual($DB->get_field_sql(
2207 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
2208 }
2209
1d861fce 2210 function test_sql_position() {
2211 $DB = $this->tdb;
2212 $this->assertEqual($DB->get_field_sql(
6ff835b7 2213 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 2214 $this->assertEqual($DB->get_field_sql(
6ff835b7 2215 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
2216 }
2217
082ae821 2218 function test_sql_empty() {
2219 $DB = $this->tdb;
2220 $dbman = $DB->get_manager();
2221
9d833e93 2222 $table = $this->get_test_table();
3ff8bf26 2223 $tablename = $table->getName();
2224
f9ecb171 2225 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2226 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 2227 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
2228 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 2229 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2230 $dbman->create_table($table);
3ff8bf26 2231 $this->tables[$tablename] = $table;
082ae821 2232
cf874b29 2233 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 2234 $DB->insert_record($tablename, array('name'=>null));
cf874b29 2235 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 2236 $DB->insert_record($tablename, array('name'=>0));
082ae821 2237
3ff8bf26 2238 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 2239 $this->assertEqual(count($records), 1);
2240 $record = reset($records);
2241 $this->assertEqual($record->name, '');
cf874b29 2242
2243 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
2244 $this->assertEqual(count($records), 1);
2245 $record = reset($records);
2246 $this->assertEqual($record->namenotnull, '');
2247
2248 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
2249 $this->assertEqual(count($records), 4);
2250 $record = reset($records);
2251 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 2252 }
2253
2254 function test_sql_isempty() {
2255 $DB = $this->tdb;
2256 $dbman = $DB->get_manager();
2257
9d833e93 2258 $table = $this->get_test_table();
3ff8bf26 2259 $tablename = $table->getName();
2260
f9ecb171 2261 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2262 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2263 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2264 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2265 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2266 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2267 $dbman->create_table($table);
3ff8bf26 2268 $this->tables[$tablename] = $table;
082ae821 2269
3ff8bf26 2270 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2271 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2272 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2273 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 2274
3ff8bf26 2275 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 2276 $this->assertEqual(count($records), 1);
2277 $record = reset($records);
2278 $this->assertEqual($record->name, '');
2279
3ff8bf26 2280 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 2281 $this->assertEqual(count($records), 1);
2282 $record = reset($records);
2283 $this->assertEqual($record->namenull, '');
2284
3ff8bf26 2285 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 2286 $this->assertEqual(count($records), 1);
2287 $record = reset($records);
2288 $this->assertEqual($record->description, '');
2289
3ff8bf26 2290 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 2291 $this->assertEqual(count($records), 1);
2292 $record = reset($records);
2293 $this->assertEqual($record->descriptionnull, '');
2294 }
2295
63b3d8ab
EL
2296 function test_sql_isnotempty() {
2297 $DB = $this->tdb;
2298 $dbman = $DB->get_manager();
2299
2300 $table = $this->get_test_table();
2301 $tablename = $table->getName();
2302
2303 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2304 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2305 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2306 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2307 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2308 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2309 $dbman->create_table($table);
2310 $this->tables[$tablename] = $table;
2311
2312 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2313 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2314 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2315 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
2316
2317 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));
2318 $this->assertEqual(count($records), 3);
2319 $record = reset($records);
2320 $this->assertEqual($record->name, '??');
2321
2322 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));
2323 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2324 $record = reset($records);
2325 $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record
2326
2327 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));
2328 $this->assertEqual(count($records), 3);
2329 $record = reset($records);
2330 $this->assertEqual($record->description, '??');
2331
2332 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));
2333 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2334 $record = reset($records);
2335 $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record
082ae821 2336 }
2337
2338 function test_sql_regex() {
6ff835b7 2339 $DB = $this->tdb;
2340 $dbman = $DB->get_manager();
2341
9d833e93 2342 $table = $this->get_test_table();
3ff8bf26 2343 $tablename = $table->getName();
2344
f9ecb171 2345 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2346 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2347 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2348 $dbman->create_table($table);
3ff8bf26 2349 $this->tables[$tablename] = $table;
6ff835b7 2350
3ff8bf26 2351 $DB->insert_record($tablename, array('name'=>'lalala'));
2352 $DB->insert_record($tablename, array('name'=>'holaaa'));
2353 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2354
3ff8bf26 2355 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2356 $params = array('a$');
dd1611a5 2357 if ($DB->sql_regex_supported()) {
2358 $records = $DB->get_records_sql($sql, $params);
2359 $this->assertEqual(count($records), 2);
2360 } else {
2361 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2362 }
6ff835b7 2363
3ff8bf26 2364 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2365 $params = array('.a');
dd1611a5 2366 if ($DB->sql_regex_supported()) {
2367 $records = $DB->get_records_sql($sql, $params);
2368 $this->assertEqual(count($records), 1);
2369 } else {
2370 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2371 }
6ff835b7 2372
1d861fce 2373 }
fb76304b 2374
d5a8d9aa
PS
2375 /**
2376 * Test some more complex SQL syntax which moodle uses and depends on to work
2377 * useful to determine if new database libraries can be supported.
2378 */
2379 public function test_get_records_sql_complicated() {
2380 global $CFG;
fb76304b 2381 $DB = $this->tdb;
2382 $dbman = $DB->get_manager();
2383
9d833e93 2384 $table = $this->get_test_table();
3ff8bf26 2385 $tablename = $table->getName();
2386
f9ecb171 2387 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2388 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
bc4fd49d 2389 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
fb76304b 2390 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2391 $dbman->create_table($table);
3ff8bf26 2392 $this->tables[$tablename] = $table;
fb76304b 2393
bc4fd49d
EL
2394 $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello'));
2395 $DB->insert_record($tablename, array('course' => 3, 'content' => 'world'));
2396 $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello'));
2397 $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe'));
d5a8d9aa
PS
2398
2399 // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
2400 $sql = 'SELECT a.id AS id, a.course AS course
2401 FROM {'.$tablename.'} a
2402 JOIN (SELECT * FROM {'.$tablename.'}) b
2403 ON a.id = b.id
2404 WHERE a.course = ?';
2405
2406 $this->assertTrue($records = $DB->get_records_sql($sql, array(3)));
2407 $this->assertEqual(2, count($records));
2408 $this->assertEqual(1, reset($records)->id);
2409 $this->assertEqual(2, next($records)->id);
bc4fd49d
EL
2410
2411 // try embeding sql_xxxx() helper functions, to check they don't break params/binding
2412 $count = $DB->count_records($tablename, array('course' => 3, $DB->sql_compare_text('content') => 'hello'));
2413 $this->assertEqual(1, $count);
d5a8d9aa
PS
2414 }
2415
2416 function test_onelevel_commit() {
2417 $DB = $this->tdb;
2418 $dbman = $DB->get_manager();
2419
2420 $table = $this->get_test_table();
2421 $tablename = $table->getName();
2422
2423 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2424 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2425 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2426 $dbman->create_table($table);
2427 $this->tables[$tablename] = $table;
2428
2429 $transaction = $DB->start_delegated_transaction();
2430 $data = (object)array('course'=>3);
2431 $this->assertEqual(0, $DB->count_records($tablename));
2432 $DB->insert_record($tablename, $data);
2433 $this->assertEqual(1, $DB->count_records($tablename));
2434 $transaction->allow_commit();
2435 $this->assertEqual(1, $DB->count_records($tablename));
2436 }
2437
2438 function test_onelevel_rollback() {
2439 $DB = $this->tdb;
2440 $dbman = $DB->get_manager();
2441
2442 $table = $this->get_test_table();
2443 $tablename = $table->getName();
2444
2445 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2446 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2447 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2448 $dbman->create_table($table);
2449 $this->tables[$tablename] = $table;
2450
2451 // this might in fact encourage ppl to migrate from myisam to innodb
2452
2453 $transaction = $DB->start_delegated_transaction();
2454 $data = (object)array('course'=>3);
2455 $this->assertEqual(0, $DB->count_records($tablename));
2456 $DB->insert_record($tablename, $data);
2457 $this->assertEqual(1, $DB->count_records($tablename));
2458 try {
2459 $transaction->rollback(new Exception('test'));
2460 $this->fail('transaction rollback must rethrow exception');
2461 } catch (Exception $e) {
fb76304b 2462 }
d5a8d9aa 2463 $this->assertEqual(0, $DB->count_records($tablename));
fb76304b 2464 }
2465
d5a8d9aa 2466 function test_nested_transactions() {
fb76304b 2467 $DB = $this->tdb;
2468 $dbman = $DB->get_manager();
2469
9d833e93 2470 $table = $this->get_test_table();
3ff8bf26 2471 $tablename = $table->getName();
2472
f9ecb171 2473 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2474 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2475 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2476 $dbman->create_table($table);
3ff8bf26 2477 $this->tables[$tablename] = $table;
fb76304b 2478
d5a8d9aa
PS
2479 // two level commit
2480 $this->assertFalse($DB->is_transaction_started());
2481 $transaction1 = $DB->start_delegated_transaction();
2482 $this->assertTrue($DB->is_transaction_started());
2483 $data = (object)array('course'=>3);
2484 $DB->insert_record($tablename, $data);
2485 $transaction2 = $DB->start_delegated_transaction();
2486 $data = (object)array('course'=>4);
2487 $DB->insert_record($tablename, $data);
2488 $transaction2->allow_commit();
2489 $this->assertTrue($DB->is_transaction_started());
2490 $transaction1->allow_commit();
2491 $this->assertFalse($DB->is_transaction_started());
2492 $this->assertEqual(2, $DB->count_records($tablename));
2493
2494 $DB->delete_records($tablename);
2495
2496 // rollback from top level
2497 $transaction1 = $DB->start_delegated_transaction();
2498 $data = (object)array('course'=>3);
2499 $DB->insert_record($tablename, $data);
2500 $transaction2 = $DB->start_delegated_transaction();
2501 $data = (object)array('course'=>4);
2502 $DB->insert_record($tablename, $data);
2503 $transaction2->allow_commit();
2504 try {
2505 $transaction1->rollback(new Exception('test'));
2506 $this->fail('transaction rollback must rethrow exception');
2507 } catch (Exception $e) {
2508 $this->assertEqual(get_class($e), 'Exception');
2509 }
2510 $this->assertEqual(0, $DB->count_records($tablename));
2511
2512 $DB->delete_records($tablename);
2513
2514 // rollback from nested level
2515 $transaction1 = $DB->start_delegated_transaction();
2516 $data = (object)array('course'=>3);
2517 $DB->insert_record($tablename, $data);
2518 $transaction2 = $DB->start_delegated_transaction();
2519 $data = (object)array('course'=>4);
2520 $DB->insert_record($tablename, $data);
2521 try {
2522 $transaction2->rollback(new Exception('test'));
2523 $this->fail('transaction rollback must rethrow exception');
2524 } catch (Exception $e) {
2525 $this->assertEqual(get_class($e), 'Exception');
fb76304b 2526 }
d5a8d9aa
PS
2527 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2528 try {
2529 $transaction1->allow_commit();
2530 } catch (Exception $e) {
2531 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2532 }
2533 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
3400bf6c 2534 // the forced rollback is done from the default_exception handler and similar places,
d5a8d9aa
PS
2535 // let's do it manually here
2536 $this->assertTrue($DB->is_transaction_started());
2537 $DB->force_transaction_rollback();
2538 $this->assertFalse($DB->is_transaction_started());
2539 $this->assertEqual(0, $DB->count_records($tablename)); // finally rolled back
2540
2541 $DB->delete_records($tablename);
fb76304b 2542 }
2543
d5a8d9aa 2544 function test_transactions_forbidden() {
fb76304b 2545 $DB = $this->tdb;
2546 $dbman = $DB->get_manager();
2547
9d833e93 2548 $table = $this->get_test_table();
3ff8bf26 2549 $tablename = $table->getName();
2550
f9ecb171 2551 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2552 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2553 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2554 $dbman->create_table($table);
3ff8bf26 2555 $this->tables[$tablename] = $table;
fb76304b 2556
d5a8d9aa
PS
2557 $DB->transactions_forbidden();
2558 $transaction = $DB->start_delegated_transaction();
2559 $data = (object)array('course'=>1);
2560 $DB->insert_record($tablename, $data);
2561 try {
2562 $DB->transactions_forbidden();
2563 } catch (Exception $e) {
2564 $this->assertEqual(get_class($e), 'dml_transaction_exception');
fb76304b 2565 }
d5a8d9aa
PS
2566 // the previous test does not force rollback
2567 $transaction->allow_commit();
2568 $this->assertFalse($DB->is_transaction_started());
2569 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2570 }
8ee8780e 2571
d5a8d9aa 2572 function test_wrong_transactions() {
8ee8780e
DP
2573 $DB = $this->tdb;
2574 $dbman = $DB->get_manager();
2575
2576 $table = $this->get_test_table();
2577 $tablename = $table->getName();
2578
2579 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2580 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
7c476352 2581 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
8ee8780e
DP
2582 $dbman->create_table($table);
2583 $this->tables[$tablename] = $table;
2584
8ee8780e 2585
d5a8d9aa
PS
2586 // wrong order of nested commits
2587 $transaction1 = $DB->start_delegated_transaction();
2588 $data = (object)array('course'=>3);
2589 $DB->insert_record($tablename, $data);
2590 $transaction2 = $DB->start_delegated_transaction();
2591 $data = (object)array('course'=>4);
2592 $DB->insert_record($tablename, $data);
2593 try {
2594 $transaction1->allow_commit();
2595 $this->fail('wrong order of commits must throw exception');
2596 } catch (Exception $e) {
2597 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2598 }
2599 try {
2600 $transaction2->allow_commit();
2601 $this->fail('first wrong commit forces rollback');
2602 } catch (Exception $e) {
2603 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2604 }
3400bf6c 2605 // this is done in default exception handler usually
d5a8d9aa
PS
2606 $this->assertTrue($DB->is_transaction_started());
2607 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2608 $DB->force_transaction_rollback();
2609 $this->assertEqual(0, $DB->count_records($tablename));
2610 $DB->delete_records($tablename);
8ee8780e 2611
d5a8d9aa
PS
2612
2613 // wrong order of nested rollbacks
2614 $transaction1 = $DB->start_delegated_transaction();
2615 $data = (object)array('course'=>3);
2616 $DB->insert_record($tablename, $data);
2617 $transaction2 = $DB->start_delegated_transaction();
2618 $data = (object)array('course'=>4);
2619 $DB->insert_record($tablename, $data);
2620 try {
2621 // this first rollback should prevent all otehr rollbacks
2622 $transaction1->rollback(new Exception('test'));
2623 } catch (Exception $e) {
2624 $this->assertEqual(get_class($e), 'Exception');
2625 }
2626 try {
2627 $transaction2->rollback(new Exception('test'));
2628 } catch (Exception $e) {
2629 $this->assertEqual(get_class($e), 'Exception');
2630 }
2631 try {
2632 $transaction1->rollback(new Exception('test'));
2633 } catch (Exception $e) {
2634 // the rollback was used already once, no way to use it again
2635 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2636 }
3400bf6c 2637 // this is done in default exception handler usually
d5a8d9aa
PS
2638 $this->assertTrue($DB->is_transaction_started());
2639 $DB->force_transaction_rollback();
2640 $DB->delete_records($tablename);
2641
2642
2643 // unknown transaction object
2644 $transaction1 = $DB->start_delegated_transaction();
2645 $data = (object)array('course'=>3);
2646 $DB->insert_record($tablename, $data);
2647 $transaction2 = new moodle_transaction($DB);
2648 try {
2649 $transaction2->allow_commit();
2650 $this->fail('foreign transaction must fail');
2651 } catch (Exception $e) {
2652 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2653 }
2654 try {
2655 $transaction1->allow_commit();
2656 $this->fail('first wrong commit forces rollback');
2657 } catch (Exception $e) {
2658 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2659 }
2660 $DB->force_transaction_rollback();
2661 $DB->delete_records($tablename);
8ee8780e 2662 }
c0829930
PS
2663
2664 function test_concurent_transactions() {
7baf4784
EL
2665 // Notes about this test:
2666 // 1- MySQL needs to use one engine with transactions support (InnoDB).
2667 // 2- MSSQL needs to have enabled versioning for read committed
2668 // transactions (ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON)
c0829930
PS
2669 $DB = $this->tdb;
2670 $dbman = $DB->get_manager();
2671
2672 $table = $this->get_test_table();
2673 $tablename = $table->getName();
2674
2675 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2676 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2677 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2678 $dbman->create_table($table);
2679 $this->tables[$tablename] = $table;
2680
2681 $transaction = $DB->start_delegated_transaction();
2682 $data = (object)array('course'=>1);
2683 $this->assertEqual(0, $DB->count_records($tablename));
2684 $DB->insert_record($tablename, $data);
2685 $this->assertEqual(1, $DB->count_records($tablename));
2686
2687 //open second connection
2688 $cfg = $DB->export_dbconfig();
2689 if (!isset($cfg->dboptions)) {
2690 $cfg->dboptions = array();
2691 }
2692 $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
2693 $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
2694
2695 // second instance should not see pending inserts
2696 $this->assertEqual(0, $DB2->count_records($tablename));
2697 $data = (object)array('course'=>2);
2698 $DB2->insert_record($tablename, $data);
2699 $this->assertEqual(1, $DB2->count_records($tablename));
2700
2701 // first should see the changes done from second
2702 $this->assertEqual(2, $DB->count_records($tablename));
2703
2704 // now commit and we should see it finally in second connections
2705 $transaction->allow_commit();
2706 $this->assertEqual(2, $DB2->count_records($tablename));
2707
2708 $DB2->dispose();
2709 }
7f9f11b5 2710}
2711
2712/**
2713 * This class is not a proper subclass of moodle_database. It is
2714 * intended to be used only in unit tests, in order to gain access to the
2715 * protected methods of moodle_database, and unit test them.
2716 */
2717class moodle_database_for_testing extends moodle_database {
2718 protected $prefix = 'mdl_';
2719
2720 public function public_fix_table_names($sql) {
2721 return $this->fix_table_names($sql);
2722 }
2723
2724 public function driver_installed(){}
2725 public function get_dbfamily(){}
2726 protected function get_dbtype(){}
16a5642c 2727 protected function get_dblibrary(){}
7f9f11b5 2728 public function get_name(){}
37d975e1 2729 public function get_configuration_help(){}
7f9f11b5 2730 public function get_configuration_hints(){}
beaa43db 2731 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2732 public function get_server_info(){}
2733 protected function allowed_param_types(){}
2734 public function get_last_error(){}
117679db 2735 public function get_tables($usecache=true){}
7f9f11b5 2736 public function get_indexes($table){}
2737 public function get_columns($table, $usecache=true){}
e3acc8af 2738 protected function normalise_value($column, $value){}
7f9f11b5 2739 public function set_debug($state){}
2740 public function get_debug(){}
2741 public function set_logging($state){}
2742 public function change_database_structure($sql){}
2743 public function execute($sql, array $params=null){}
2744 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2745 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2746 public function get_fieldset_sql($sql, array $params=null){}
94898738 2747 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 2748 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 2749 public function import_record($table, $dataobject){}
7f9f11b5 2750 public function update_record_raw($table, $params, $bulk=false){}
2751 public function update_record($table, $dataobject, $bulk=false){}
2752 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2753 public function delete_records_select($table, $select, array $params=null){}
2754 public function sql_concat(){}
2755 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2756 public function sql_substr($expr, $start, $length=false){}
d5a8d9aa
PS
2757 public function begin_transaction() {}
2758 public function commit_transaction() {}
2759 public function rollback_transaction() {}
bb78c788 2760}