MDL-23925 more unit tests that should help with diagnostics of collation issues;...
[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
5a9c25e8
PS
2064 function test_unique_binary() {
2065 // note: this is a work in progress, we should probably move this to ddl test
2066
2067 $DB = $this->tdb;
2068 $dbman = $DB->get_manager();
2069
2070 $table = $this->get_test_table();
2071 $tablename = $table->getName();
2072
2073 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2074 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2075 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2076 $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));
2077 $dbman->create_table($table);
2078 $this->tables[$tablename] = $table;
2079
2080 try {
2081 $DB->insert_record($tablename, array('name'=>'aaa'));
2082 $DB->insert_record($tablename, array('name'=>'aäa'));
2083 $DB->insert_record($tablename, array('name'=>'aáa'));
2084 $this->assertTrue(true);
2085 } catch (Exception $e) {
2086 $this->fail("Database collation is not supposed to make unique index accent insensitive!");
2087 throw($e);
2088 }
2089 }
6055f89d
PS
2090
2091 function test_sql_binary_equal() {
2092 $DB = $this->tdb;
2093 $dbman = $DB->get_manager();
2094
2095 $table = $this->get_test_table();
2096 $tablename = $table->getName();
2097
2098 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2099 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2100 $table->add_field('descr', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2101 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2102 $dbman->create_table($table);
2103 $this->tables[$tablename] = $table;
2104
5a9c25e8
PS
2105 $DB->insert_record($tablename, array('name'=>'aaa', 'descr'=>'Aaa'));
2106 $DB->insert_record($tablename, array('name'=>'aáa', 'descr'=>'ááá'));
2107 $DB->insert_record($tablename, array('name'=>'aäa', 'descr'=>'aäa'));
2108 $DB->insert_record($tablename, array('name'=>'AAA', 'descr'=>'AAA'));
6055f89d
PS
2109
2110 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
5a9c25e8 2111 $records = $DB->get_records_sql($sql, array("aaa"));
6055f89d
PS
2112 $this->assertEqual(count($records), 1);
2113
2114 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
5a9c25e8 2115 $records = $DB->get_records_sql($sql, array("aáa"));
6055f89d
PS
2116 $this->assertEqual(count($records), 1);
2117
2118 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', 'descr');
2119 $records = $DB->get_records_sql($sql, array());
5a9c25e8 2120 $this->assertEqual(count($records), 2);
6055f89d
PS
2121
2122 // get_records() is supposed to use binary comparison too
5a9c25e8 2123 $records = $DB->get_records($tablename, array('name'=>"aaa"));
6055f89d 2124 $this->assertEqual(count($records), 1);
5a9c25e8 2125 $records = $DB->get_records($tablename, array('name'=>"aäa"));
6055f89d
PS
2126 $this->assertEqual(count($records), 1);
2127
5a9c25e8 2128 $bool = $DB->record_exists($tablename, array('name'=>"aaa"));
6055f89d 2129 $this->assertTrue($bool);
5a9c25e8 2130 $bool = $DB->record_exists($tablename, array('name'=>"AaA"));
6055f89d
PS
2131 $this->assertFalse($bool);
2132 }
2133
2134 function test_sql_like() {
2135 $DB = $this->tdb;
2136 $dbman = $DB->get_manager();
2137
2138 $table = $this->get_test_table();
2139 $tablename = $table->getName();
2140
2141 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2142 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2143 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2144 $dbman->create_table($table);
2145 $this->tables[$tablename] = $table;
2146
2147 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2148 $DB->insert_record($tablename, array('name'=>'Nodupor'));
2149 $DB->insert_record($tablename, array('name'=>'ouch'));
2150 $DB->insert_record($tablename, array('name'=>'ouc_'));
2151 $DB->insert_record($tablename, array('name'=>'ouc%'));
2152 $DB->insert_record($tablename, array('name'=>'aui'));
2153 $DB->insert_record($tablename, array('name'=>'aüi'));
2154 $DB->insert_record($tablename, array('name'=>'aÜi'));
2155
2156 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false);
2157 $records = $DB->get_records_sql($sql, array("%dup_r%"));
2158 $this->assertEqual(count($records), 2);
2159
2160 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
2161 $records = $DB->get_records_sql($sql, array("%dup%"));
2162 $this->assertEqual(count($records), 1);
2163
2164 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?'); // defaults
2165 $records = $DB->get_records_sql($sql, array("%dup%"));
2166 $this->assertEqual(count($records), 1);
2167
2168 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
2169 $records = $DB->get_records_sql($sql, array("ouc\\_"));
2170 $this->assertEqual(count($records), 1);
2171
2172 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true, '|');
2173 $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
2174 $this->assertEqual(count($records), 1);
2175
2176 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true);
2177 $records = $DB->get_records_sql($sql, array('aui'));
2178 $this->assertEqual(count($records), 1);
2179
2180 // we do not require accent insensitivness yet, just make sure it does not throw errors
2181 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, false);
2182 $records = $DB->get_records_sql($sql, array('aui'));
5a9c25e8 2183 $this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not really a problem now.');
6055f89d
PS
2184 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false, false);
2185 $records = $DB->get_records_sql($sql, array('aui'));
5a9c25e8 2186 $this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not really a problem now.');
6055f89d
PS
2187 }
2188
2189 function test_sql_ilike() {
6ff835b7 2190 $DB = $this->tdb;
2191 $dbman = $DB->get_manager();
2192
9d833e93 2193 $table = $this->get_test_table();
3ff8bf26 2194 $tablename = $table->getName();
2195
f9ecb171 2196 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2197 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2198 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2199 $dbman->create_table($table);
3ff8bf26 2200 $this->tables[$tablename] = $table;
6ff835b7 2201
3ff8bf26 2202 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2203 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2204 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2205
3ff8bf26 2206 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2207 $params = array("%dup_r%");
2208 $records = $DB->get_records_sql($sql, $params);
6055f89d 2209 $this->assertEqual(count($records), 2, 'DB->sql_ilike() is deprecated, ignore this problem.');
6ff835b7 2210 }
2211
71a71d59 2212 function test_sql_concat() {
6ff835b7 2213 $DB = $this->tdb;
71a71d59 2214 $dbman = $DB->get_manager();
2215
2216 /// Testing all sort of values
2217 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2218 // string, some unicode chars
2219 $params = array('name', 'áéíóú', 'name3');
2220 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2221 // string, spaces and numbers
2222 $params = array('name', ' ', 12345);
2223 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2224 // float, empty and strings
2225 $params = array(123.45, '', 'test');
2226 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2227 // float, null and strings
2228 $params = array(123.45, null, 'test');
52a01626 2229 $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 2230
2231 /// Testing fieldnames + values
2232 $table = $this->get_test_table();
2233 $tablename = $table->getName();
2234
2235 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2236 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2237 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2238 $dbman->create_table($table);
2239 $this->tables[$tablename] = $table;
2240
2241 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2242 $DB->insert_record($tablename, array('description'=>'dxxx'));
2243 $DB->insert_record($tablename, array('description'=>'bcde'));
2244
2245 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2246 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2247 $this->assertEqual(count($records), 3);
2248 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2249 }
2250
082ae821 2251 function test_concat_join() {
6ff835b7 2252 $DB = $this->tdb;
082ae821 2253 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2254 $params = array("name", "name2", "name3");
2255 $result = $DB->get_field_sql($sql, $params);
2256 $this->assertEqual("name name2 name3", $result);
2257 }
2258
2259 function test_sql_fullname() {
2260 $DB = $this->tdb;
2261 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2262 $params = array('first'=>'Firstname', 'last'=>'Surname');
2263 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2264 }
2265
2266 function sql_sql_order_by_text() {
2267 $DB = $this->tdb;
2268 $dbman = $DB->get_manager();
2269
9d833e93 2270 $table = $this->get_test_table();
3ff8bf26 2271 $tablename = $table->getName();
2272
f9ecb171 2273 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2274 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2275 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2276 $dbman->create_table($table);
3ff8bf26 2277 $this->tables[$tablename] = $table;
082ae821 2278
3ff8bf26 2279 $DB->insert_record($tablename, array('description'=>'abcd'));
2280 $DB->insert_record($tablename, array('description'=>'dxxx'));
2281 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2282
3ff8bf26 2283 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2284 $records = $DB->get_records_sql($sql);
2285 $first = array_unshift($records);
2286 $this->assertEqual(1, $first->id);
2287 $second = array_unshift($records);
2288 $this->assertEqual(3, $second->id);
2289 $last = array_unshift($records);
2290 $this->assertEqual(2, $last->id);
2291 }
2292
2293 function test_sql_substring() {
2294 $DB = $this->tdb;
2295 $dbman = $DB->get_manager();
2296
9d833e93 2297 $table = $this->get_test_table();
3ff8bf26 2298 $tablename = $table->getName();
2299
f9ecb171 2300 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2301 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 2302 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2303 $dbman->create_table($table);
3ff8bf26 2304 $this->tables[$tablename] = $table;
082ae821 2305
2306 $string = 'abcdefghij';
2307
3ff8bf26 2308 $DB->insert_record($tablename, array('name'=>$string));
082ae821 2309
3ff8bf26 2310 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 2311 $record = $DB->get_record_sql($sql);
2312 $this->assertEqual(substr($string, 5-1), $record->name);
2313
3ff8bf26 2314 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 2315 $record = $DB->get_record_sql($sql);
2316 $this->assertEqual(substr($string, 5-1, 2), $record->name);
2317
2318 try {
2319 // silence php warning ;-)
2320 @$DB->sql_substr("name");
2321 $this->fail("Expecting an exception, none occurred");
2322 } catch (Exception $e) {
2323 $this->assertTrue($e instanceof coding_exception);
2324 }
6ff835b7 2325 }
2326
37d975e1 2327 function test_sql_length() {
2328 $DB = $this->tdb;
2329 $this->assertEqual($DB->get_field_sql(
2330 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
2331 $this->assertEqual($DB->get_field_sql(
2332 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
2333 }
2334
1d861fce 2335 function test_sql_position() {
2336 $DB = $this->tdb;
2337 $this->assertEqual($DB->get_field_sql(
6ff835b7 2338 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 2339 $this->assertEqual($DB->get_field_sql(
6ff835b7 2340 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
2341 }
2342
082ae821 2343 function test_sql_empty() {
2344 $DB = $this->tdb;
2345 $dbman = $DB->get_manager();
2346
9d833e93 2347 $table = $this->get_test_table();
3ff8bf26 2348 $tablename = $table->getName();
2349
f9ecb171 2350 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2351 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 2352 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
2353 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 2354 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2355 $dbman->create_table($table);
3ff8bf26 2356 $this->tables[$tablename] = $table;
082ae821 2357
cf874b29 2358 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 2359 $DB->insert_record($tablename, array('name'=>null));
cf874b29 2360 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 2361 $DB->insert_record($tablename, array('name'=>0));
082ae821 2362
3ff8bf26 2363 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 2364 $this->assertEqual(count($records), 1);
2365 $record = reset($records);
2366 $this->assertEqual($record->name, '');
cf874b29 2367
2368 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
2369 $this->assertEqual(count($records), 1);
2370 $record = reset($records);
2371 $this->assertEqual($record->namenotnull, '');
2372
2373 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
2374 $this->assertEqual(count($records), 4);
2375 $record = reset($records);
2376 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 2377 }
2378
2379 function test_sql_isempty() {
2380 $DB = $this->tdb;
2381 $dbman = $DB->get_manager();
2382
9d833e93 2383 $table = $this->get_test_table();
3ff8bf26 2384 $tablename = $table->getName();
2385
f9ecb171 2386 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2387 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2388 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2389 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2390 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2391 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2392 $dbman->create_table($table);
3ff8bf26 2393 $this->tables[$tablename] = $table;
082ae821 2394
3ff8bf26 2395 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2396 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2397 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2398 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 2399
3ff8bf26 2400 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 2401 $this->assertEqual(count($records), 1);
2402 $record = reset($records);
2403 $this->assertEqual($record->name, '');
2404
3ff8bf26 2405 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 2406 $this->assertEqual(count($records), 1);
2407 $record = reset($records);
2408 $this->assertEqual($record->namenull, '');
2409
3ff8bf26 2410 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 2411 $this->assertEqual(count($records), 1);
2412 $record = reset($records);
2413 $this->assertEqual($record->description, '');
2414
3ff8bf26 2415 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 2416 $this->assertEqual(count($records), 1);
2417 $record = reset($records);
2418 $this->assertEqual($record->descriptionnull, '');
2419 }
2420
63b3d8ab
EL
2421 function test_sql_isnotempty() {
2422 $DB = $this->tdb;
2423 $dbman = $DB->get_manager();
2424
2425 $table = $this->get_test_table();
2426 $tablename = $table->getName();
2427
2428 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2429 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2430 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2431 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2432 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2433 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2434 $dbman->create_table($table);
2435 $this->tables[$tablename] = $table;
2436
2437 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2438 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2439 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2440 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
2441
2442 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));
2443 $this->assertEqual(count($records), 3);
2444 $record = reset($records);
2445 $this->assertEqual($record->name, '??');
2446
2447 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));
2448 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2449 $record = reset($records);
2450 $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record
2451
2452 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));
2453 $this->assertEqual(count($records), 3);
2454 $record = reset($records);
2455 $this->assertEqual($record->description, '??');
2456
2457 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));
2458 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2459 $record = reset($records);
2460 $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record
082ae821 2461 }
2462
2463 function test_sql_regex() {
6ff835b7 2464 $DB = $this->tdb;
2465 $dbman = $DB->get_manager();
2466
9d833e93 2467 $table = $this->get_test_table();
3ff8bf26 2468 $tablename = $table->getName();
2469
f9ecb171 2470 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2471 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2472 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2473 $dbman->create_table($table);
3ff8bf26 2474 $this->tables[$tablename] = $table;
6ff835b7 2475
3ff8bf26 2476 $DB->insert_record($tablename, array('name'=>'lalala'));
2477 $DB->insert_record($tablename, array('name'=>'holaaa'));
2478 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2479
3ff8bf26 2480 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2481 $params = array('a$');
dd1611a5 2482 if ($DB->sql_regex_supported()) {
2483 $records = $DB->get_records_sql($sql, $params);
2484 $this->assertEqual(count($records), 2);
2485 } else {
2486 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2487 }
6ff835b7 2488
3ff8bf26 2489 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2490 $params = array('.a');
dd1611a5 2491 if ($DB->sql_regex_supported()) {
2492 $records = $DB->get_records_sql($sql, $params);
2493 $this->assertEqual(count($records), 1);
2494 } else {
2495 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2496 }
6ff835b7 2497
1d861fce 2498 }
fb76304b 2499
d5a8d9aa
PS
2500 /**
2501 * Test some more complex SQL syntax which moodle uses and depends on to work
2502 * useful to determine if new database libraries can be supported.
2503 */
2504 public function test_get_records_sql_complicated() {
2505 global $CFG;
fb76304b 2506 $DB = $this->tdb;
2507 $dbman = $DB->get_manager();
2508
9d833e93 2509 $table = $this->get_test_table();
3ff8bf26 2510 $tablename = $table->getName();
2511
f9ecb171 2512 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2513 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
bc4fd49d 2514 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
fb76304b 2515 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2516 $dbman->create_table($table);
3ff8bf26 2517 $this->tables[$tablename] = $table;
fb76304b 2518
bc4fd49d
EL
2519 $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello'));
2520 $DB->insert_record($tablename, array('course' => 3, 'content' => 'world'));
2521 $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello'));
2522 $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe'));
d5a8d9aa
PS
2523
2524 // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
2525 $sql = 'SELECT a.id AS id, a.course AS course
2526 FROM {'.$tablename.'} a
2527 JOIN (SELECT * FROM {'.$tablename.'}) b
2528 ON a.id = b.id
2529 WHERE a.course = ?';
2530
2531 $this->assertTrue($records = $DB->get_records_sql($sql, array(3)));
2532 $this->assertEqual(2, count($records));
2533 $this->assertEqual(1, reset($records)->id);
2534 $this->assertEqual(2, next($records)->id);
bc4fd49d
EL
2535
2536 // try embeding sql_xxxx() helper functions, to check they don't break params/binding
2537 $count = $DB->count_records($tablename, array('course' => 3, $DB->sql_compare_text('content') => 'hello'));
2538 $this->assertEqual(1, $count);
d5a8d9aa
PS
2539 }
2540
2541 function test_onelevel_commit() {
2542 $DB = $this->tdb;
2543 $dbman = $DB->get_manager();
2544
2545 $table = $this->get_test_table();
2546 $tablename = $table->getName();
2547
2548 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2549 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2550 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2551 $dbman->create_table($table);
2552 $this->tables[$tablename] = $table;
2553
2554 $transaction = $DB->start_delegated_transaction();
2555 $data = (object)array('course'=>3);
2556 $this->assertEqual(0, $DB->count_records($tablename));
2557 $DB->insert_record($tablename, $data);
2558 $this->assertEqual(1, $DB->count_records($tablename));
2559 $transaction->allow_commit();
2560 $this->assertEqual(1, $DB->count_records($tablename));
2561 }
2562
2563 function test_onelevel_rollback() {
2564 $DB = $this->tdb;
2565 $dbman = $DB->get_manager();
2566
2567 $table = $this->get_test_table();
2568 $tablename = $table->getName();
2569
2570 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2571 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2572 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2573 $dbman->create_table($table);
2574 $this->tables[$tablename] = $table;
2575
2576 // this might in fact encourage ppl to migrate from myisam to innodb
2577
2578 $transaction = $DB->start_delegated_transaction();
2579 $data = (object)array('course'=>3);
2580 $this->assertEqual(0, $DB->count_records($tablename));
2581 $DB->insert_record($tablename, $data);
2582 $this->assertEqual(1, $DB->count_records($tablename));
2583 try {
2584 $transaction->rollback(new Exception('test'));
2585 $this->fail('transaction rollback must rethrow exception');
2586 } catch (Exception $e) {
fb76304b 2587 }
d5a8d9aa 2588 $this->assertEqual(0, $DB->count_records($tablename));
fb76304b 2589 }
2590
d5a8d9aa 2591 function test_nested_transactions() {
fb76304b 2592 $DB = $this->tdb;
2593 $dbman = $DB->get_manager();
2594
9d833e93 2595 $table = $this->get_test_table();
3ff8bf26 2596 $tablename = $table->getName();
2597
f9ecb171 2598 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2599 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2600 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2601 $dbman->create_table($table);
3ff8bf26 2602 $this->tables[$tablename] = $table;
fb76304b 2603
d5a8d9aa
PS
2604 // two level commit
2605 $this->assertFalse($DB->is_transaction_started());
2606 $transaction1 = $DB->start_delegated_transaction();
2607 $this->assertTrue($DB->is_transaction_started());
2608 $data = (object)array('course'=>3);
2609 $DB->insert_record($tablename, $data);
2610 $transaction2 = $DB->start_delegated_transaction();
2611 $data = (object)array('course'=>4);
2612 $DB->insert_record($tablename, $data);
2613 $transaction2->allow_commit();
2614 $this->assertTrue($DB->is_transaction_started());
2615 $transaction1->allow_commit();
2616 $this->assertFalse($DB->is_transaction_started());
2617 $this->assertEqual(2, $DB->count_records($tablename));
2618
2619 $DB->delete_records($tablename);
2620
2621 // rollback from top level
2622 $transaction1 = $DB->start_delegated_transaction();
2623 $data = (object)array('course'=>3);
2624 $DB->insert_record($tablename, $data);
2625 $transaction2 = $DB->start_delegated_transaction();
2626 $data = (object)array('course'=>4);
2627 $DB->insert_record($tablename, $data);
2628 $transaction2->allow_commit();
2629 try {
2630 $transaction1->rollback(new Exception('test'));
2631 $this->fail('transaction rollback must rethrow exception');
2632 } catch (Exception $e) {
2633 $this->assertEqual(get_class($e), 'Exception');
2634 }
2635 $this->assertEqual(0, $DB->count_records($tablename));
2636
2637 $DB->delete_records($tablename);
2638
2639 // rollback from nested level
2640 $transaction1 = $DB->start_delegated_transaction();
2641 $data = (object)array('course'=>3);
2642 $DB->insert_record($tablename, $data);
2643 $transaction2 = $DB->start_delegated_transaction();
2644 $data = (object)array('course'=>4);
2645 $DB->insert_record($tablename, $data);
2646 try {
2647 $transaction2->rollback(new Exception('test'));
2648 $this->fail('transaction rollback must rethrow exception');
2649 } catch (Exception $e) {
2650 $this->assertEqual(get_class($e), 'Exception');
fb76304b 2651 }
d5a8d9aa
PS
2652 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2653 try {
2654 $transaction1->allow_commit();
2655 } catch (Exception $e) {
2656 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2657 }
2658 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
3400bf6c 2659 // the forced rollback is done from the default_exception handler and similar places,
d5a8d9aa
PS
2660 // let's do it manually here
2661 $this->assertTrue($DB->is_transaction_started());
2662 $DB->force_transaction_rollback();
2663 $this->assertFalse($DB->is_transaction_started());
2664 $this->assertEqual(0, $DB->count_records($tablename)); // finally rolled back
2665
2666 $DB->delete_records($tablename);
fb76304b 2667 }
2668
d5a8d9aa 2669 function test_transactions_forbidden() {
fb76304b 2670 $DB = $this->tdb;
2671 $dbman = $DB->get_manager();
2672
9d833e93 2673 $table = $this->get_test_table();
3ff8bf26 2674 $tablename = $table->getName();
2675
f9ecb171 2676 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2677 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2678 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2679 $dbman->create_table($table);
3ff8bf26 2680 $this->tables[$tablename] = $table;
fb76304b 2681
d5a8d9aa
PS
2682 $DB->transactions_forbidden();
2683 $transaction = $DB->start_delegated_transaction();
2684 $data = (object)array('course'=>1);
2685 $DB->insert_record($tablename, $data);
2686 try {
2687 $DB->transactions_forbidden();
2688 } catch (Exception $e) {
2689 $this->assertEqual(get_class($e), 'dml_transaction_exception');
fb76304b 2690 }
d5a8d9aa
PS
2691 // the previous test does not force rollback
2692 $transaction->allow_commit();
2693 $this->assertFalse($DB->is_transaction_started());
2694 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2695 }
8ee8780e 2696
d5a8d9aa 2697 function test_wrong_transactions() {
8ee8780e
DP
2698 $DB = $this->tdb;
2699 $dbman = $DB->get_manager();
2700
2701 $table = $this->get_test_table();
2702 $tablename = $table->getName();
2703
2704 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2705 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
7c476352 2706 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
8ee8780e
DP
2707 $dbman->create_table($table);
2708 $this->tables[$tablename] = $table;
2709
8ee8780e 2710
d5a8d9aa
PS
2711 // wrong order of nested commits
2712 $transaction1 = $DB->start_delegated_transaction();
2713 $data = (object)array('course'=>3);
2714 $DB->insert_record($tablename, $data);
2715 $transaction2 = $DB->start_delegated_transaction();
2716 $data = (object)array('course'=>4);
2717 $DB->insert_record($tablename, $data);
2718 try {
2719 $transaction1->allow_commit();
2720 $this->fail('wrong order of commits must throw exception');
2721 } catch (Exception $e) {
2722 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2723 }
2724 try {
2725 $transaction2->allow_commit();
2726 $this->fail('first wrong commit forces rollback');
2727 } catch (Exception $e) {
2728 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2729 }
3400bf6c 2730 // this is done in default exception handler usually
d5a8d9aa
PS
2731 $this->assertTrue($DB->is_transaction_started());
2732 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2733 $DB->force_transaction_rollback();
2734 $this->assertEqual(0, $DB->count_records($tablename));
2735 $DB->delete_records($tablename);
8ee8780e 2736
d5a8d9aa
PS
2737
2738 // wrong order of nested rollbacks
2739 $transaction1 = $DB->start_delegated_transaction();
2740 $data = (object)array('course'=>3);
2741 $DB->insert_record($tablename, $data);
2742 $transaction2 = $DB->start_delegated_transaction();
2743 $data = (object)array('course'=>4);
2744 $DB->insert_record($tablename, $data);
2745 try {
2746 // this first rollback should prevent all otehr rollbacks
2747 $transaction1->rollback(new Exception('test'));
2748 } catch (Exception $e) {
2749 $this->assertEqual(get_class($e), 'Exception');
2750 }
2751 try {
2752 $transaction2->rollback(new Exception('test'));
2753 } catch (Exception $e) {
2754 $this->assertEqual(get_class($e), 'Exception');
2755 }
2756 try {
2757 $transaction1->rollback(new Exception('test'));
2758 } catch (Exception $e) {
2759 // the rollback was used already once, no way to use it again
2760 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2761 }
3400bf6c 2762 // this is done in default exception handler usually
d5a8d9aa
PS
2763 $this->assertTrue($DB->is_transaction_started());
2764 $DB->force_transaction_rollback();
2765 $DB->delete_records($tablename);
2766
2767
2768 // unknown transaction object
2769 $transaction1 = $DB->start_delegated_transaction();
2770 $data = (object)array('course'=>3);
2771 $DB->insert_record($tablename, $data);
2772 $transaction2 = new moodle_transaction($DB);
2773 try {
2774 $transaction2->allow_commit();
2775 $this->fail('foreign transaction must fail');
2776 } catch (Exception $e) {
2777 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2778 }
2779 try {
2780 $transaction1->allow_commit();
2781 $this->fail('first wrong commit forces rollback');
2782 } catch (Exception $e) {
2783 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2784 }
2785 $DB->force_transaction_rollback();
2786 $DB->delete_records($tablename);
8ee8780e 2787 }
c0829930
PS
2788
2789 function test_concurent_transactions() {
7baf4784
EL
2790 // Notes about this test:
2791 // 1- MySQL needs to use one engine with transactions support (InnoDB).
2792 // 2- MSSQL needs to have enabled versioning for read committed
2793 // transactions (ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON)
c0829930
PS
2794 $DB = $this->tdb;
2795 $dbman = $DB->get_manager();
2796
2797 $table = $this->get_test_table();
2798 $tablename = $table->getName();
2799
2800 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2801 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2802 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2803 $dbman->create_table($table);
2804 $this->tables[$tablename] = $table;
2805
2806 $transaction = $DB->start_delegated_transaction();
2807 $data = (object)array('course'=>1);
2808 $this->assertEqual(0, $DB->count_records($tablename));
2809 $DB->insert_record($tablename, $data);
2810 $this->assertEqual(1, $DB->count_records($tablename));
2811
2812 //open second connection
2813 $cfg = $DB->export_dbconfig();
2814 if (!isset($cfg->dboptions)) {
2815 $cfg->dboptions = array();
2816 }
2817 $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
2818 $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
2819
2820 // second instance should not see pending inserts
2821 $this->assertEqual(0, $DB2->count_records($tablename));
2822 $data = (object)array('course'=>2);
2823 $DB2->insert_record($tablename, $data);
2824 $this->assertEqual(1, $DB2->count_records($tablename));
2825
2826 // first should see the changes done from second
2827 $this->assertEqual(2, $DB->count_records($tablename));
2828
2829 // now commit and we should see it finally in second connections
2830 $transaction->allow_commit();
2831 $this->assertEqual(2, $DB2->count_records($tablename));
2832
2833 $DB2->dispose();
2834 }
7f9f11b5 2835}
2836
2837/**
2838 * This class is not a proper subclass of moodle_database. It is
2839 * intended to be used only in unit tests, in order to gain access to the
2840 * protected methods of moodle_database, and unit test them.
2841 */
2842class moodle_database_for_testing extends moodle_database {
2843 protected $prefix = 'mdl_';
2844
2845 public function public_fix_table_names($sql) {
2846 return $this->fix_table_names($sql);
2847 }
2848
2849 public function driver_installed(){}
2850 public function get_dbfamily(){}
2851 protected function get_dbtype(){}
16a5642c 2852 protected function get_dblibrary(){}
7f9f11b5 2853 public function get_name(){}
37d975e1 2854 public function get_configuration_help(){}
7f9f11b5 2855 public function get_configuration_hints(){}
beaa43db 2856 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2857 public function get_server_info(){}
2858 protected function allowed_param_types(){}
2859 public function get_last_error(){}
117679db 2860 public function get_tables($usecache=true){}
7f9f11b5 2861 public function get_indexes($table){}
2862 public function get_columns($table, $usecache=true){}
e3acc8af 2863 protected function normalise_value($column, $value){}
7f9f11b5 2864 public function set_debug($state){}
2865 public function get_debug(){}
2866 public function set_logging($state){}
2867 public function change_database_structure($sql){}
2868 public function execute($sql, array $params=null){}
2869 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2870 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2871 public function get_fieldset_sql($sql, array $params=null){}
94898738 2872 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 2873 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 2874 public function import_record($table, $dataobject){}
7f9f11b5 2875 public function update_record_raw($table, $params, $bulk=false){}
2876 public function update_record($table, $dataobject, $bulk=false){}
2877 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2878 public function delete_records_select($table, $select, array $params=null){}
2879 public function sql_concat(){}
2880 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2881 public function sql_substr($expr, $start, $length=false){}
d5a8d9aa
PS
2882 public function begin_transaction() {}
2883 public function commit_transaction() {}
2884 public function rollback_transaction() {}
bb78c788 2885}