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