MDL-14679 more get_field() unittests
[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
3ff8bf26 1632 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1633
3ff8bf26 1634 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1635 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
fbabe35a 1636
bc4fd49d
EL
1637 // Add one set_field() example where the field being set is also one condition
1638 // to check we haven't problem with any type of param (specially NAMED ones)
1639 $DB->set_field($tablename, 'course', '1', array('course' => 2));
1640 $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => 1)));
1641
fbabe35a 1642 // Note: All the nulls, booleans, empties, quoted and backslashes tests
1643 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 1644 }
1645
1646 public function test_set_field_select() {
628dff25 1647
1648 // All the information in this test is fetched from DB by get_field() so we
1649 // have such method properly tested against nulls, empties and friends...
1650
fbabe35a 1651 global $CFG;
1652
50a12c87 1653 $DB = $this->tdb;
1654 $dbman = $DB->get_manager();
1655
9d833e93 1656 $table = $this->get_test_table();
3ff8bf26 1657 $tablename = $table->getName();
1658
f9ecb171 1659 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1660 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 1661 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
1662 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
1663 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
1664 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1665 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1666 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1667 $dbman->create_table($table);
3ff8bf26 1668 $this->tables[$tablename] = $table;
50a12c87 1669
3ff8bf26 1670 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1671
3ff8bf26 1672 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1673 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1674
fbabe35a 1675 // Check nulls are set properly for all types
628dff25 1676 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
1677 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
1678 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
1679 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
1680 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
fbabe35a 1681 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
1682 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
1683 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
1684 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
1685 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
1686
1687 // Check zeros are set properly for all types
1688 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
1689 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
1690 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1691 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1692
1693 // Check booleans are set properly for all types
1694 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
1695 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
1696 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
1697 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
1698 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1699 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1700 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1701 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1702
1703 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
1704 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
1705 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
1706 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
1707 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1708 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1709 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1710 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1711
1712 // Check string data causes exception in numeric types
1713 try {
1714 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
1715 $this->fail("Expecting an exception, none occurred");
1716 } catch (exception $e) {
1717 $this->assertTrue($e instanceof dml_exception);
1718 }
1719 try {
1720 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
1721 $this->fail("Expecting an exception, none occurred");
1722 } catch (exception $e) {
1723 $this->assertTrue($e instanceof dml_exception);
1724 }
1725
2f9c1693
EL
1726 // Check empty string data is stored as 0 in numeric datatypes
1727 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
1728 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
1729 $this->assertTrue(is_numeric($field) && $field == 0);
1730
1731 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
1732 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
1733 $this->assertTrue(is_numeric($field) && $field == 0);
fdc45ac3 1734
fbabe35a 1735 // Check empty strings are set properly in string types
1736 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
1737 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
1738 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
1739 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
1740
1741 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1742 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1743 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1744 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1745 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1746
1747 // Check various quotes/backslashes combinations in string types
1748 $teststrings = array(
628dff25 1749 'backslashes and quotes alone (even): "" \'\' \\\\',
1750 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1751 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1752 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
fbabe35a 1753 foreach ($teststrings as $teststring) {
1754 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
1755 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
1756 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1757 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1758 }
1759
1760 // Check LOBs in text/binary columns
1761 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1762 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1763 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
1764 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
ff8c7b2a
EL
1765 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
1766 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
fbabe35a 1767
1768 // And "small" LOBs too, just in case
1769 $newclob = substr($clob, 0, 500);
1770 $newblob = substr($blob, 0, 250);
1771 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
1772 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
ff8c7b2a
EL
1773 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
1774 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
0088bd31 1775 }
1776
1777 public function test_count_records() {
1778 $DB = $this->tdb;
1779
1780 $dbman = $DB->get_manager();
1781
9d833e93 1782 $table = $this->get_test_table();
3ff8bf26 1783 $tablename = $table->getName();
1784
f9ecb171 1785 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1786 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1787 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1788 $dbman->create_table($table);
3ff8bf26 1789 $this->tables[$tablename] = $table;
0088bd31 1790
3ff8bf26 1791 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1792
3ff8bf26 1793 $DB->insert_record($tablename, array('course' => 3));
1794 $DB->insert_record($tablename, array('course' => 4));
1795 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1796
3ff8bf26 1797 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1798 }
1799
1800 public function test_count_records_select() {
50a12c87 1801 $DB = $this->tdb;
0088bd31 1802
50a12c87 1803 $dbman = $DB->get_manager();
1804
9d833e93 1805 $table = $this->get_test_table();
3ff8bf26 1806 $tablename = $table->getName();
1807
f9ecb171 1808 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1809 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1810 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1811 $dbman->create_table($table);
3ff8bf26 1812 $this->tables[$tablename] = $table;
50a12c87 1813
3ff8bf26 1814 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1815
3ff8bf26 1816 $DB->insert_record($tablename, array('course' => 3));
1817 $DB->insert_record($tablename, array('course' => 4));
1818 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1819
3ff8bf26 1820 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1821 }
1822
1823 public function test_count_records_sql() {
50a12c87 1824 $DB = $this->tdb;
1825 $dbman = $DB->get_manager();
1826
9d833e93 1827 $table = $this->get_test_table();
3ff8bf26 1828 $tablename = $table->getName();
1829
f9ecb171 1830 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1831 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1832 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1833 $dbman->create_table($table);
3ff8bf26 1834 $this->tables[$tablename] = $table;
50a12c87 1835
3ff8bf26 1836 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1837
3ff8bf26 1838 $DB->insert_record($tablename, array('course' => 3));
1839 $DB->insert_record($tablename, array('course' => 4));
1840 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1841
3ff8bf26 1842 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1843 }
1844
1845 public function test_record_exists() {
50a12c87 1846 $DB = $this->tdb;
1847 $dbman = $DB->get_manager();
1848
9d833e93 1849 $table = $this->get_test_table();
3ff8bf26 1850 $tablename = $table->getName();
1851
f9ecb171 1852 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1853 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1854 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1855 $dbman->create_table($table);
3ff8bf26 1856 $this->tables[$tablename] = $table;
50a12c87 1857
3ff8bf26 1858 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1859
3ff8bf26 1860 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1861 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1862
3ff8bf26 1863 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1864
1865 }
1866
1867 public function test_record_exists_select() {
50a12c87 1868 $DB = $this->tdb;
1869 $dbman = $DB->get_manager();
0088bd31 1870
9d833e93 1871 $table = $this->get_test_table();
3ff8bf26 1872 $tablename = $table->getName();
1873
f9ecb171 1874 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1875 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1876 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1877 $dbman->create_table($table);
3ff8bf26 1878 $this->tables[$tablename] = $table;
50a12c87 1879
3ff8bf26 1880 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1881
3ff8bf26 1882 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1883 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1884
3ff8bf26 1885 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1886 }
1887
1888 public function test_record_exists_sql() {
50a12c87 1889 $DB = $this->tdb;
1890 $dbman = $DB->get_manager();
0088bd31 1891
9d833e93 1892 $table = $this->get_test_table();
3ff8bf26 1893 $tablename = $table->getName();
1894
f9ecb171 1895 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1896 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1897 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1898 $dbman->create_table($table);
3ff8bf26 1899 $this->tables[$tablename] = $table;
50a12c87 1900
3ff8bf26 1901 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1902
3ff8bf26 1903 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1904 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1905
3ff8bf26 1906 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1907 }
1908
1909 public function test_delete_records() {
50a12c87 1910 $DB = $this->tdb;
1911 $dbman = $DB->get_manager();
0088bd31 1912
9d833e93 1913 $table = $this->get_test_table();
3ff8bf26 1914 $tablename = $table->getName();
1915
f9ecb171 1916 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1917 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1918 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1919 $dbman->create_table($table);
3ff8bf26 1920 $this->tables[$tablename] = $table;
50a12c87 1921
3ff8bf26 1922 $DB->insert_record($tablename, array('course' => 3));
1923 $DB->insert_record($tablename, array('course' => 2));
1924 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1925
1926 // Delete all records
3ff8bf26 1927 $this->assertTrue($DB->delete_records($tablename));
1928 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1929
1930 // Delete subset of records
3ff8bf26 1931 $DB->insert_record($tablename, array('course' => 3));
1932 $DB->insert_record($tablename, array('course' => 2));
1933 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1934
3ff8bf26 1935 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1936 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1937 }
1938
1939 public function test_delete_records_select() {
50a12c87 1940 $DB = $this->tdb;
1941 $dbman = $DB->get_manager();
0088bd31 1942
9d833e93 1943 $table = $this->get_test_table();
3ff8bf26 1944 $tablename = $table->getName();
1945
f9ecb171 1946 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1947 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1948 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1949 $dbman->create_table($table);
3ff8bf26 1950 $this->tables[$tablename] = $table;
0088bd31 1951
3ff8bf26 1952 $DB->insert_record($tablename, array('course' => 3));
1953 $DB->insert_record($tablename, array('course' => 2));
1954 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1955
3ff8bf26 1956 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1957 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1958 }
1d861fce 1959
c362878e 1960 public function test_delete_records_list() {
1961 $DB = $this->tdb;
1962 $dbman = $DB->get_manager();
1963
1964 $table = $this->get_test_table();
1965 $tablename = $table->getName();
1966
f9ecb171 1967 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1968 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 1969 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1970 $dbman->create_table($table);
1971 $this->tables[$tablename] = $table;
1972
1973 $DB->insert_record($tablename, array('course' => 1));
1974 $DB->insert_record($tablename, array('course' => 2));
1975 $DB->insert_record($tablename, array('course' => 3));
1976
1977 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1978 $this->assertEqual(1, $DB->count_records($tablename));
1979
1980 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1981 $this->assertEqual(1, $DB->count_records($tablename));
1982 }
1983
082ae821 1984 function test_sql_null_from_clause() {
655bbf51 1985 $DB = $this->tdb;
082ae821 1986 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1987 $this->assertEqual($DB->get_field_sql($sql), 1);
1988 }
655bbf51 1989
082ae821 1990 function test_sql_bitand() {
1991 $DB = $this->tdb;
9d833e93 1992 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1993 $this->assertEqual($DB->get_field_sql($sql), 2);
1994 }
655bbf51 1995
082ae821 1996 function test_sql_bitnot() {
1997 $DB = $this->tdb;
655bbf51 1998
082ae821 1999 $not = $DB->sql_bitnot(2);
2000 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 2001
9d833e93 2002 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 2003 $this->assertEqual($DB->get_field_sql($sql), 5);
2004 }
655bbf51 2005
082ae821 2006 function test_sql_bitor() {
2007 $DB = $this->tdb;
9d833e93 2008 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 2009 $this->assertEqual($DB->get_field_sql($sql), 11);
2010 }
655bbf51 2011
082ae821 2012 function test_sql_bitxor() {
2013 $DB = $this->tdb;
9d833e93 2014 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 2015 $this->assertEqual($DB->get_field_sql($sql), 9);
2016 }
2017
e6df3734 2018 function test_sql_modulo() {
2019 $DB = $this->tdb;
9d833e93 2020 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 2021 $this->assertEqual($DB->get_field_sql($sql), 3);
2022 }
2023
082ae821 2024 function test_sql_ceil() {
2025 $DB = $this->tdb;
9d833e93 2026 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 2027 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 2028 }
2029
6ff835b7 2030 function test_cast_char2int() {
2031 $DB = $this->tdb;
2032 $dbman = $DB->get_manager();
2033
9d833e93 2034 $table1 = $this->get_test_table("testtable1");
3ff8bf26 2035 $tablename1 = $table1->getName();
2036
f9ecb171 2037 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2038 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2039 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2040 $dbman->create_table($table1);
3ff8bf26 2041 $this->tables[$tablename1] = $table1;
6ff835b7 2042
3ff8bf26 2043 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 2044
9d833e93 2045 $table2 = $this->get_test_table("testtable2");
3ff8bf26 2046 $tablename2 = $table2->getName();
f9ecb171 2047 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2048 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 2049 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2050 $dbman->create_table($table2);
2051 $this->tables[$table2->getName()] = $table2;
2052
9d833e93 2053 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 2054
2055 try {
9d833e93 2056 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 2057 $records = $DB->get_records_sql($sql);
2058 $this->assertEqual(count($records), 1);
2059 } catch (dml_exception $e) {
2060 $this->fail("No exception expected");
2061 }
2062 }
2063
2064 function test_cast_char2real() {
2065 $DB = $this->tdb;
2066 $dbman = $DB->get_manager();
2067
9d833e93 2068 $table = $this->get_test_table();
3ff8bf26 2069 $tablename = $table->getName();
2070
f9ecb171 2071 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2072 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2073 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 2074 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2075 $dbman->create_table($table);
3ff8bf26 2076 $this->tables[$tablename] = $table;
6ff835b7 2077
9d833e93 2078 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
2079 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
2080 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 2081
9d833e93 2082 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 2083 $records = $DB->get_records_sql($sql);
2084 $this->assertEqual(count($records), 2);
2085 }
2086
082ae821 2087 function sql_compare_text() {
2088 $DB = $this->tdb;
2089 $dbman = $DB->get_manager();
2090
9d833e93 2091 $table = $this->get_test_table();
3ff8bf26 2092 $tablename = $table->getName();
2093
f9ecb171 2094 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2095 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2096 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2097 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2098 $dbman->create_table($table);
3ff8bf26 2099 $this->tables[$tablename] = $table;
082ae821 2100
3ff8bf26 2101 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
2102 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
2103 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 2104
3ff8bf26 2105 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 2106 $records = $DB->get_records_sql($sql);
2107 $this->assertEqual(count($records), 1);
2108
3ff8bf26 2109 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 2110 $records = $DB->get_records_sql($sql);
2111 $this->assertEqual(count($records), 2);
2112 }
2113
d533e6d7 2114 function test_unique_index_collation_trouble() {
5a9c25e8
PS
2115 // note: this is a work in progress, we should probably move this to ddl test
2116
2117 $DB = $this->tdb;
2118 $dbman = $DB->get_manager();
2119
2120 $table = $this->get_test_table();
2121 $tablename = $table->getName();
2122
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_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2126 $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));
2127 $dbman->create_table($table);
2128 $this->tables[$tablename] = $table;
2129
d533e6d7
PS
2130 $DB->insert_record($tablename, array('name'=>'aaa'));
2131
2132 try {
2133 $DB->insert_record($tablename, array('name'=>'AAA'));
2134 } catch (Exception $e) {
2135 //TODO: ignore case insensitive uniqueness problems for now
2136 //$this->fail("Unique index is case sensitive - this may cause problems in some tables");
2137 }
2138
5a9c25e8 2139 try {
5a9c25e8
PS
2140 $DB->insert_record($tablename, array('name'=>'aäa'));
2141 $DB->insert_record($tablename, array('name'=>'aáa'));
2142 $this->assertTrue(true);
2143 } catch (Exception $e) {
d533e6d7
PS
2144 $family = $DB->get_dbfamily();
2145 if ($family === 'mysql' or $family === 'mssql') {
2146 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation.");
2147 } else {
2148 // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.
2149 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages.");
2150 }
5a9c25e8
PS
2151 throw($e);
2152 }
2153 }
6055f89d
PS
2154
2155 function test_sql_binary_equal() {
2156 $DB = $this->tdb;
2157 $dbman = $DB->get_manager();
2158
2159 $table = $this->get_test_table();
2160 $tablename = $table->getName();
2161
2162 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2163 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6055f89d
PS
2164 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2165 $dbman->create_table($table);
2166 $this->tables[$tablename] = $table;
2167
d533e6d7
PS
2168 $DB->insert_record($tablename, array('name'=>'aaa'));
2169 $DB->insert_record($tablename, array('name'=>'aáa'));
2170 $DB->insert_record($tablename, array('name'=>'aäa'));
2171 $DB->insert_record($tablename, array('name'=>'bbb'));
2172 $DB->insert_record($tablename, array('name'=>'BBB'));
2173
2174 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa'));
2175 $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be accent sensitive');
2176
2177 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb'));
2178 $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be case sensitive');
6055f89d
PS
2179 }
2180
2181 function test_sql_like() {
2182 $DB = $this->tdb;
2183 $dbman = $DB->get_manager();
2184
2185 $table = $this->get_test_table();
2186 $tablename = $table->getName();
2187
2188 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2189 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2190 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2191 $dbman->create_table($table);
2192 $this->tables[$tablename] = $table;
2193
2194 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2195 $DB->insert_record($tablename, array('name'=>'Nodupor'));
2196 $DB->insert_record($tablename, array('name'=>'ouch'));
2197 $DB->insert_record($tablename, array('name'=>'ouc_'));
2198 $DB->insert_record($tablename, array('name'=>'ouc%'));
2199 $DB->insert_record($tablename, array('name'=>'aui'));
2200 $DB->insert_record($tablename, array('name'=>'aüi'));
2201 $DB->insert_record($tablename, array('name'=>'aÜi'));
2202
d533e6d7 2203 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false);
6055f89d
PS
2204 $records = $DB->get_records_sql($sql, array("%dup_r%"));
2205 $this->assertEqual(count($records), 2);
2206
d533e6d7 2207 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
6055f89d
PS
2208 $records = $DB->get_records_sql($sql, array("%dup%"));
2209 $this->assertEqual(count($records), 1);
2210
d533e6d7 2211 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults
6055f89d
PS
2212 $records = $DB->get_records_sql($sql, array("%dup%"));
2213 $this->assertEqual(count($records), 1);
2214
d533e6d7 2215 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
6055f89d
PS
2216 $records = $DB->get_records_sql($sql, array("ouc\\_"));
2217 $this->assertEqual(count($records), 1);
2218
16114b9d 2219 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');
6055f89d
PS
2220 $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
2221 $this->assertEqual(count($records), 1);
2222
d533e6d7 2223 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true);
6055f89d
PS
2224 $records = $DB->get_records_sql($sql, array('aui'));
2225 $this->assertEqual(count($records), 1);
2226
16114b9d
PS
2227 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE
2228 $records = $DB->get_records_sql($sql, array("%o%"));
2229 $this->assertEqual(count($records), 3);
2230
2231 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE
2232 $records = $DB->get_records_sql($sql, array("%D%"));
2233 $this->assertEqual(count($records), 6);
2234
d533e6d7
PS
2235 // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors
2236 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false);
6055f89d 2237 $records = $DB->get_records_sql($sql, array('aui'));
d533e6d7
PS
2238 //$this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
2239 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false);
6055f89d 2240 $records = $DB->get_records_sql($sql, array('aui'));
d533e6d7 2241 //$this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
6055f89d
PS
2242 }
2243
2244 function test_sql_ilike() {
2f8eea34
PS
2245 global $CFG;
2246
2247 // note: this is deprecated, just make sure it does not throw error
6ff835b7 2248 $DB = $this->tdb;
2249 $dbman = $DB->get_manager();
2250
9d833e93 2251 $table = $this->get_test_table();
3ff8bf26 2252 $tablename = $table->getName();
2253
f9ecb171 2254 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2255 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2256 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2257 $dbman->create_table($table);
3ff8bf26 2258 $this->tables[$tablename] = $table;
6ff835b7 2259
3ff8bf26 2260 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2261 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2262 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2263
2f8eea34
PS
2264 // make sure it prints debug message
2265 $olddebug = $CFG->debug; // Save current debug settings
2266 $olddisplay = $CFG->debugdisplay;
2267 $CFG->debug = DEBUG_DEVELOPER;
2268 $CFG->debugdisplay = true;
2269 ob_start(); // hide debug warning
3ff8bf26 2270 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2271 $params = array("%dup_r%");
2f8eea34
PS
2272 ob_end_clean();
2273 $debuginfo = ob_get_contents();
2274 $CFG->debug = $olddebug; // Restore original debug settings
2275 $CFG->debugdisplay = $olddisplay;
2276 $this->assertFalse($debuginfo === '');
2277
2278 // following must not throw exception, we ignore result
2279 $DB->get_records_sql($sql, $params);
6ff835b7 2280 }
2281
71a71d59 2282 function test_sql_concat() {
6ff835b7 2283 $DB = $this->tdb;
71a71d59 2284 $dbman = $DB->get_manager();
2285
2286 /// Testing all sort of values
2287 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2288 // string, some unicode chars
2289 $params = array('name', 'áéíóú', 'name3');
2290 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2291 // string, spaces and numbers
2292 $params = array('name', ' ', 12345);
2293 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2294 // float, empty and strings
2295 $params = array(123.45, '', 'test');
2296 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2297 // float, null and strings
2298 $params = array(123.45, null, 'test');
52a01626 2299 $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 2300
2301 /// Testing fieldnames + values
2302 $table = $this->get_test_table();
2303 $tablename = $table->getName();
2304
2305 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2306 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2307 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2308 $dbman->create_table($table);
2309 $this->tables[$tablename] = $table;
2310
2311 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2312 $DB->insert_record($tablename, array('description'=>'dxxx'));
2313 $DB->insert_record($tablename, array('description'=>'bcde'));
2314
2315 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2316 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2317 $this->assertEqual(count($records), 3);
2318 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2319 }
2320
082ae821 2321 function test_concat_join() {
6ff835b7 2322 $DB = $this->tdb;
082ae821 2323 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2324 $params = array("name", "name2", "name3");
2325 $result = $DB->get_field_sql($sql, $params);
2326 $this->assertEqual("name name2 name3", $result);
2327 }
2328
2329 function test_sql_fullname() {
2330 $DB = $this->tdb;
2331 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2332 $params = array('first'=>'Firstname', 'last'=>'Surname');
2333 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2334 }
2335
2336 function sql_sql_order_by_text() {
2337 $DB = $this->tdb;
2338 $dbman = $DB->get_manager();
2339
9d833e93 2340 $table = $this->get_test_table();
3ff8bf26 2341 $tablename = $table->getName();
2342
f9ecb171 2343 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2344 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2345 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2346 $dbman->create_table($table);
3ff8bf26 2347 $this->tables[$tablename] = $table;
082ae821 2348
3ff8bf26 2349 $DB->insert_record($tablename, array('description'=>'abcd'));
2350 $DB->insert_record($tablename, array('description'=>'dxxx'));
2351 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2352
3ff8bf26 2353 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2354 $records = $DB->get_records_sql($sql);
2355 $first = array_unshift($records);
2356 $this->assertEqual(1, $first->id);
2357 $second = array_unshift($records);
2358 $this->assertEqual(3, $second->id);
2359 $last = array_unshift($records);
2360 $this->assertEqual(2, $last->id);
2361 }
2362
2363 function test_sql_substring() {
2364 $DB = $this->tdb;
2365 $dbman = $DB->get_manager();
2366
9d833e93 2367 $table = $this->get_test_table();
3ff8bf26 2368 $tablename = $table->getName();
2369
f9ecb171 2370 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2371 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 2372 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2373 $dbman->create_table($table);
3ff8bf26 2374 $this->tables[$tablename] = $table;
082ae821 2375
2376 $string = 'abcdefghij';
2377
3ff8bf26 2378 $DB->insert_record($tablename, array('name'=>$string));
082ae821 2379
3ff8bf26 2380 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 2381 $record = $DB->get_record_sql($sql);
2382 $this->assertEqual(substr($string, 5-1), $record->name);
2383
3ff8bf26 2384 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 2385 $record = $DB->get_record_sql($sql);
2386 $this->assertEqual(substr($string, 5-1, 2), $record->name);
2387
2388 try {
2389 // silence php warning ;-)
2390 @$DB->sql_substr("name");
2391 $this->fail("Expecting an exception, none occurred");
2392 } catch (Exception $e) {
2393 $this->assertTrue($e instanceof coding_exception);
2394 }
6ff835b7 2395 }
2396
37d975e1 2397 function test_sql_length() {
2398 $DB = $this->tdb;
2399 $this->assertEqual($DB->get_field_sql(
2400 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
2401 $this->assertEqual($DB->get_field_sql(
2402 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
2403 }
2404
1d861fce 2405 function test_sql_position() {
2406 $DB = $this->tdb;
2407 $this->assertEqual($DB->get_field_sql(
6ff835b7 2408 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 2409 $this->assertEqual($DB->get_field_sql(
6ff835b7 2410 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
2411 }
2412
082ae821 2413 function test_sql_empty() {
2414 $DB = $this->tdb;
2415 $dbman = $DB->get_manager();
2416
9d833e93 2417 $table = $this->get_test_table();
3ff8bf26 2418 $tablename = $table->getName();
2419
f9ecb171 2420 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2421 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 2422 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
2423 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 2424 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2425 $dbman->create_table($table);
3ff8bf26 2426 $this->tables[$tablename] = $table;
082ae821 2427
cf874b29 2428 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 2429 $DB->insert_record($tablename, array('name'=>null));
cf874b29 2430 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 2431 $DB->insert_record($tablename, array('name'=>0));
082ae821 2432
3ff8bf26 2433 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 2434 $this->assertEqual(count($records), 1);
2435 $record = reset($records);
2436 $this->assertEqual($record->name, '');
cf874b29 2437
2438 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
2439 $this->assertEqual(count($records), 1);
2440 $record = reset($records);
2441 $this->assertEqual($record->namenotnull, '');
2442
2443 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
2444 $this->assertEqual(count($records), 4);
2445 $record = reset($records);
2446 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 2447 }
2448
2449 function test_sql_isempty() {
2450 $DB = $this->tdb;
2451 $dbman = $DB->get_manager();
2452
9d833e93 2453 $table = $this->get_test_table();
3ff8bf26 2454 $tablename = $table->getName();
2455
f9ecb171 2456 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2457 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2458 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2459 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2460 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2461 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2462 $dbman->create_table($table);
3ff8bf26 2463 $this->tables[$tablename] = $table;
082ae821 2464
3ff8bf26 2465 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2466 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2467 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2468 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 2469
3ff8bf26 2470 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 2471 $this->assertEqual(count($records), 1);
2472 $record = reset($records);
2473 $this->assertEqual($record->name, '');
2474
3ff8bf26 2475 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 2476 $this->assertEqual(count($records), 1);
2477 $record = reset($records);
2478 $this->assertEqual($record->namenull, '');
2479
3ff8bf26 2480 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 2481 $this->assertEqual(count($records), 1);
2482 $record = reset($records);
2483 $this->assertEqual($record->description, '');
2484
3ff8bf26 2485 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 2486 $this->assertEqual(count($records), 1);
2487 $record = reset($records);
2488 $this->assertEqual($record->descriptionnull, '');
2489 }
2490
63b3d8ab
EL
2491 function test_sql_isnotempty() {
2492 $DB = $this->tdb;
2493 $dbman = $DB->get_manager();
2494
2495 $table = $this->get_test_table();
2496 $tablename = $table->getName();
2497
2498 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2499 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2500 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2501 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2502 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2503 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2504 $dbman->create_table($table);
2505 $this->tables[$tablename] = $table;
2506
2507 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2508 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2509 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2510 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
2511
2512 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));
2513 $this->assertEqual(count($records), 3);
2514 $record = reset($records);
2515 $this->assertEqual($record->name, '??');
2516
2517 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));
2518 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2519 $record = reset($records);
2520 $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record
2521
2522 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));
2523 $this->assertEqual(count($records), 3);
2524 $record = reset($records);
2525 $this->assertEqual($record->description, '??');
2526
2527 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));
2528 $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
2529 $record = reset($records);
2530 $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record
082ae821 2531 }
2532
2533 function test_sql_regex() {
6ff835b7 2534 $DB = $this->tdb;
2535 $dbman = $DB->get_manager();
2536
9d833e93 2537 $table = $this->get_test_table();
3ff8bf26 2538 $tablename = $table->getName();
2539
f9ecb171 2540 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2541 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2542 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2543 $dbman->create_table($table);
3ff8bf26 2544 $this->tables[$tablename] = $table;
6ff835b7 2545
3ff8bf26 2546 $DB->insert_record($tablename, array('name'=>'lalala'));
2547 $DB->insert_record($tablename, array('name'=>'holaaa'));
2548 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2549
3ff8bf26 2550 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2551 $params = array('a$');
dd1611a5 2552 if ($DB->sql_regex_supported()) {
2553 $records = $DB->get_records_sql($sql, $params);
2554 $this->assertEqual(count($records), 2);
2555 } else {
2556 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2557 }
6ff835b7 2558
3ff8bf26 2559 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2560 $params = array('.a');
dd1611a5 2561 if ($DB->sql_regex_supported()) {
2562 $records = $DB->get_records_sql($sql, $params);
2563 $this->assertEqual(count($records), 1);
2564 } else {
2565 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2566 }
6ff835b7 2567
1d861fce 2568 }
fb76304b 2569
d5a8d9aa
PS
2570 /**
2571 * Test some more complex SQL syntax which moodle uses and depends on to work
2572 * useful to determine if new database libraries can be supported.
2573 */
2574 public function test_get_records_sql_complicated() {
2575 global $CFG;
fb76304b 2576 $DB = $this->tdb;
2577 $dbman = $DB->get_manager();
2578
9d833e93 2579 $table = $this->get_test_table();
3ff8bf26 2580 $tablename = $table->getName();
2581
f9ecb171 2582 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2583 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
bc4fd49d 2584 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
fb76304b 2585 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2586 $dbman->create_table($table);
3ff8bf26 2587 $this->tables[$tablename] = $table;
fb76304b 2588
bc4fd49d
EL
2589 $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello'));
2590 $DB->insert_record($tablename, array('course' => 3, 'content' => 'world'));
2591 $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello'));
2592 $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe'));
d5a8d9aa
PS
2593
2594 // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
2595 $sql = 'SELECT a.id AS id, a.course AS course
2596 FROM {'.$tablename.'} a
2597 JOIN (SELECT * FROM {'.$tablename.'}) b
2598 ON a.id = b.id
2599 WHERE a.course = ?';
2600
2601 $this->assertTrue($records = $DB->get_records_sql($sql, array(3)));
2602 $this->assertEqual(2, count($records));
2603 $this->assertEqual(1, reset($records)->id);
2604 $this->assertEqual(2, next($records)->id);
bc4fd49d
EL
2605
2606 // try embeding sql_xxxx() helper functions, to check they don't break params/binding
2607 $count = $DB->count_records($tablename, array('course' => 3, $DB->sql_compare_text('content') => 'hello'));
2608 $this->assertEqual(1, $count);
d5a8d9aa
PS
2609 }
2610
2611 function test_onelevel_commit() {
2612 $DB = $this->tdb;
2613 $dbman = $DB->get_manager();
2614
2615 $table = $this->get_test_table();
2616 $tablename = $table->getName();
2617
2618 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2619 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2620 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2621 $dbman->create_table($table);
2622 $this->tables[$tablename] = $table;
2623
2624 $transaction = $DB->start_delegated_transaction();
2625 $data = (object)array('course'=>3);
2626 $this->assertEqual(0, $DB->count_records($tablename));
2627 $DB->insert_record($tablename, $data);
2628 $this->assertEqual(1, $DB->count_records($tablename));
2629 $transaction->allow_commit();
2630 $this->assertEqual(1, $DB->count_records($tablename));
2631 }
2632
2633 function test_onelevel_rollback() {
2634 $DB = $this->tdb;
2635 $dbman = $DB->get_manager();
2636
2637 $table = $this->get_test_table();
2638 $tablename = $table->getName();
2639
2640 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2641 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2642 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2643 $dbman->create_table($table);
2644 $this->tables[$tablename] = $table;
2645
2646 // this might in fact encourage ppl to migrate from myisam to innodb
2647
2648 $transaction = $DB->start_delegated_transaction();
2649 $data = (object)array('course'=>3);
2650 $this->assertEqual(0, $DB->count_records($tablename));
2651 $DB->insert_record($tablename, $data);
2652 $this->assertEqual(1, $DB->count_records($tablename));
2653 try {
2654 $transaction->rollback(new Exception('test'));
2655 $this->fail('transaction rollback must rethrow exception');
2656 } catch (Exception $e) {
fb76304b 2657 }
d5a8d9aa 2658 $this->assertEqual(0, $DB->count_records($tablename));
fb76304b 2659 }
2660
d5a8d9aa 2661 function test_nested_transactions() {
fb76304b 2662 $DB = $this->tdb;
2663 $dbman = $DB->get_manager();
2664
9d833e93 2665 $table = $this->get_test_table();
3ff8bf26 2666 $tablename = $table->getName();
2667
f9ecb171 2668 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2669 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2670 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2671 $dbman->create_table($table);
3ff8bf26 2672 $this->tables[$tablename] = $table;
fb76304b 2673
d5a8d9aa
PS
2674 // two level commit
2675 $this->assertFalse($DB->is_transaction_started());
2676 $transaction1 = $DB->start_delegated_transaction();
2677 $this->assertTrue($DB->is_transaction_started());
2678 $data = (object)array('course'=>3);
2679 $DB->insert_record($tablename, $data);
2680 $transaction2 = $DB->start_delegated_transaction();
2681 $data = (object)array('course'=>4);
2682 $DB->insert_record($tablename, $data);
2683 $transaction2->allow_commit();
2684 $this->assertTrue($DB->is_transaction_started());
2685 $transaction1->allow_commit();
2686 $this->assertFalse($DB->is_transaction_started());
2687 $this->assertEqual(2, $DB->count_records($tablename));
2688
2689 $DB->delete_records($tablename);
2690
2691 // rollback from top level
2692 $transaction1 = $DB->start_delegated_transaction();
2693 $data = (object)array('course'=>3);
2694 $DB->insert_record($tablename, $data);
2695 $transaction2 = $DB->start_delegated_transaction();
2696 $data = (object)array('course'=>4);
2697 $DB->insert_record($tablename, $data);
2698 $transaction2->allow_commit();
2699 try {
2700 $transaction1->rollback(new Exception('test'));
2701 $this->fail('transaction rollback must rethrow exception');
2702 } catch (Exception $e) {
2703 $this->assertEqual(get_class($e), 'Exception');
2704 }
2705 $this->assertEqual(0, $DB->count_records($tablename));
2706
2707 $DB->delete_records($tablename);
2708
2709 // rollback from nested level
2710 $transaction1 = $DB->start_delegated_transaction();
2711 $data = (object)array('course'=>3);
2712 $DB->insert_record($tablename, $data);
2713 $transaction2 = $DB->start_delegated_transaction();
2714 $data = (object)array('course'=>4);
2715 $DB->insert_record($tablename, $data);
2716 try {
2717 $transaction2->rollback(new Exception('test'));
2718 $this->fail('transaction rollback must rethrow exception');
2719 } catch (Exception $e) {
2720 $this->assertEqual(get_class($e), 'Exception');
fb76304b 2721 }
d5a8d9aa
PS
2722 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2723 try {
2724 $transaction1->allow_commit();
2725 } catch (Exception $e) {
2726 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2727 }
2728 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
3400bf6c 2729 // the forced rollback is done from the default_exception handler and similar places,
d5a8d9aa
PS
2730 // let's do it manually here
2731 $this->assertTrue($DB->is_transaction_started());
2732 $DB->force_transaction_rollback();
2733 $this->assertFalse($DB->is_transaction_started());
2734 $this->assertEqual(0, $DB->count_records($tablename)); // finally rolled back
2735
2736 $DB->delete_records($tablename);
fb76304b 2737 }
2738
d5a8d9aa 2739 function test_transactions_forbidden() {
fb76304b 2740 $DB = $this->tdb;
2741 $dbman = $DB->get_manager();
2742
9d833e93 2743 $table = $this->get_test_table();
3ff8bf26 2744 $tablename = $table->getName();
2745
f9ecb171 2746 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2747 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2748 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2749 $dbman->create_table($table);
3ff8bf26 2750 $this->tables[$tablename] = $table;
fb76304b 2751
d5a8d9aa
PS
2752 $DB->transactions_forbidden();
2753 $transaction = $DB->start_delegated_transaction();
2754 $data = (object)array('course'=>1);
2755 $DB->insert_record($tablename, $data);
2756 try {
2757 $DB->transactions_forbidden();
2758 } catch (Exception $e) {
2759 $this->assertEqual(get_class($e), 'dml_transaction_exception');
fb76304b 2760 }
d5a8d9aa
PS
2761 // the previous test does not force rollback
2762 $transaction->allow_commit();
2763 $this->assertFalse($DB->is_transaction_started());
2764 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2765 }
8ee8780e 2766
d5a8d9aa 2767 function test_wrong_transactions() {
8ee8780e
DP
2768 $DB = $this->tdb;
2769 $dbman = $DB->get_manager();
2770
2771 $table = $this->get_test_table();
2772 $tablename = $table->getName();
2773
2774 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2775 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
7c476352 2776 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
8ee8780e
DP
2777 $dbman->create_table($table);
2778 $this->tables[$tablename] = $table;
2779
8ee8780e 2780
d5a8d9aa
PS
2781 // wrong order of nested commits
2782 $transaction1 = $DB->start_delegated_transaction();
2783 $data = (object)array('course'=>3);
2784 $DB->insert_record($tablename, $data);
2785 $transaction2 = $DB->start_delegated_transaction();
2786 $data = (object)array('course'=>4);
2787 $DB->insert_record($tablename, $data);
2788 try {
2789 $transaction1->allow_commit();
2790 $this->fail('wrong order of commits must throw exception');
2791 } catch (Exception $e) {
2792 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2793 }
2794 try {
2795 $transaction2->allow_commit();
2796 $this->fail('first wrong commit forces rollback');
2797 } catch (Exception $e) {
2798 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2799 }
3400bf6c 2800 // this is done in default exception handler usually
d5a8d9aa
PS
2801 $this->assertTrue($DB->is_transaction_started());
2802 $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
2803 $DB->force_transaction_rollback();
2804 $this->assertEqual(0, $DB->count_records($tablename));
2805 $DB->delete_records($tablename);
8ee8780e 2806
d5a8d9aa
PS
2807
2808 // wrong order of nested rollbacks
2809 $transaction1 = $DB->start_delegated_transaction();
2810 $data = (object)array('course'=>3);
2811 $DB->insert_record($tablename, $data);
2812 $transaction2 = $DB->start_delegated_transaction();
2813 $data = (object)array('course'=>4);
2814 $DB->insert_record($tablename, $data);
2815 try {
2816 // this first rollback should prevent all otehr rollbacks
2817 $transaction1->rollback(new Exception('test'));
2818 } catch (Exception $e) {
2819 $this->assertEqual(get_class($e), 'Exception');
2820 }
2821 try {
2822 $transaction2->rollback(new Exception('test'));
2823 } catch (Exception $e) {
2824 $this->assertEqual(get_class($e), 'Exception');
2825 }
2826 try {
2827 $transaction1->rollback(new Exception('test'));
2828 } catch (Exception $e) {
2829 // the rollback was used already once, no way to use it again
2830 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2831 }
3400bf6c 2832 // this is done in default exception handler usually
d5a8d9aa
PS
2833 $this->assertTrue($DB->is_transaction_started());
2834 $DB->force_transaction_rollback();
2835 $DB->delete_records($tablename);
2836
2837
2838 // unknown transaction object
2839 $transaction1 = $DB->start_delegated_transaction();
2840 $data = (object)array('course'=>3);
2841 $DB->insert_record($tablename, $data);
2842 $transaction2 = new moodle_transaction($DB);
2843 try {
2844 $transaction2->allow_commit();
2845 $this->fail('foreign transaction must fail');
2846 } catch (Exception $e) {
2847 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2848 }
2849 try {
2850 $transaction1->allow_commit();
2851 $this->fail('first wrong commit forces rollback');
2852 } catch (Exception $e) {
2853 $this->assertEqual(get_class($e), 'dml_transaction_exception');
2854 }
2855 $DB->force_transaction_rollback();
2856 $DB->delete_records($tablename);
8ee8780e 2857 }
c0829930
PS
2858
2859 function test_concurent_transactions() {
7baf4784
EL
2860 // Notes about this test:
2861 // 1- MySQL needs to use one engine with transactions support (InnoDB).
2862 // 2- MSSQL needs to have enabled versioning for read committed
2863 // transactions (ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON)
c0829930
PS
2864 $DB = $this->tdb;
2865 $dbman = $DB->get_manager();
2866
2867 $table = $this->get_test_table();
2868 $tablename = $table->getName();
2869
2870 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2871 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2872 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2873 $dbman->create_table($table);
2874 $this->tables[$tablename] = $table;
2875
2876 $transaction = $DB->start_delegated_transaction();
2877 $data = (object)array('course'=>1);
2878 $this->assertEqual(0, $DB->count_records($tablename));
2879 $DB->insert_record($tablename, $data);
2880 $this->assertEqual(1, $DB->count_records($tablename));
2881
2882 //open second connection
2883 $cfg = $DB->export_dbconfig();
2884 if (!isset($cfg->dboptions)) {
2885 $cfg->dboptions = array();
2886 }
2887 $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
2888 $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
2889
2890 // second instance should not see pending inserts
2891 $this->assertEqual(0, $DB2->count_records($tablename));
2892 $data = (object)array('course'=>2);
2893 $DB2->insert_record($tablename, $data);
2894 $this->assertEqual(1, $DB2->count_records($tablename));
2895
2896 // first should see the changes done from second
2897 $this->assertEqual(2, $DB->count_records($tablename));
2898
2899 // now commit and we should see it finally in second connections
2900 $transaction->allow_commit();
2901 $this->assertEqual(2, $DB2->count_records($tablename));
2902
2903 $DB2->dispose();
2904 }
9ac5fdf8
PS
2905
2906 public function test_bound_param_types() {
2907 $DB = $this->tdb;
2908 $dbman = $DB->get_manager();
2909
2910 $table = $this->get_test_table();
2911 $tablename = $table->getName();
2912
2913 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2914 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2915 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
2916 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2917 $dbman->create_table($table);
2918 $this->tables[$tablename] = $table;
2919
2920 $this->assertTrue($DB->insert_record($tablename, array('name' => '1', 'content'=>'xx')));
2921 $this->assertTrue($DB->insert_record($tablename, array('name' => 2, 'content'=>'yy')));
41276c9e 2922 $this->assertTrue($DB->insert_record($tablename, array('name' => 'somestring', 'content'=>'zz')));
9ac5fdf8
PS
2923 $this->assertTrue($DB->insert_record($tablename, array('name' => 'aa', 'content'=>'1')));
2924 $this->assertTrue($DB->insert_record($tablename, array('name' => 'bb', 'content'=>2)));
41276c9e 2925 $this->assertTrue($DB->insert_record($tablename, array('name' => 'cc', 'content'=>'sometext')));
9ac5fdf8
PS
2926
2927
057865c0
PS
2928 // Conditions in CHAR columns
2929 $this->assertTrue($DB->record_exists($tablename, array('name'=>1)));
2930 $this->assertTrue($DB->record_exists($tablename, array('name'=>'1')));
2931 $this->assertFalse($DB->record_exists($tablename, array('name'=>111)));
bb980cce
PS
2932 $this->assertTrue($DB->get_record($tablename, array('name'=>1)));
2933 $this->assertTrue($DB->get_record($tablename, array('name'=>'1')));
2934 $this->assertFalse($DB->get_record($tablename, array('name'=>111)));
7a45a5f1
EL
2935 $sqlqm = "SELECT *
2936 FROM {{$tablename}}
2937 WHERE name = ?";
2938 $this->assertTrue($records = $DB->get_records_sql($sqlqm, array(1)));
9ac5fdf8 2939 $this->assertEqual(1, count($records));
7a45a5f1 2940 $this->assertTrue($records = $DB->get_records_sql($sqlqm, array('1')));
9ac5fdf8 2941 $this->assertEqual(1, count($records));
057865c0
PS
2942 $records = $DB->get_records_sql($sqlqm, array(222));
2943 $this->assertEqual(0, count($records));
7a45a5f1
EL
2944 $sqlnamed = "SELECT *
2945 FROM {{$tablename}}
2946 WHERE name = :name";
2947 $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('name' => 2)));
9ac5fdf8 2948 $this->assertEqual(1, count($records));
7a45a5f1 2949 $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('name' => '2')));
9ac5fdf8 2950 $this->assertEqual(1, count($records));
2a72392d 2951
c899cbea
EL
2952 // Conditions in TEXT columns always must be performed with the sql_compare_text
2953 // helper function on both sides of the condition
2954 $sqlqm = "SELECT *
2955 FROM {{$tablename}}
2956 WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text('?');
2957 $this->assertTrue($records = $DB->get_records_sql($sqlqm, array('1')));
9ac5fdf8 2958 $this->assertEqual(1, count($records));
c899cbea 2959 $this->assertTrue($records = $DB->get_records_sql($sqlqm, array(1)));
9ac5fdf8 2960 $this->assertEqual(1, count($records));
c899cbea
EL
2961 $sqlnamed = "SELECT *
2962 FROM {{$tablename}}
2963 WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text(':content');
2964 $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('content' => 2)));
9ac5fdf8 2965 $this->assertEqual(1, count($records));
c899cbea 2966 $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('content' => '2')));
9ac5fdf8
PS
2967 $this->assertEqual(1, count($records));
2968 }
7f9f11b5 2969}
2970
2971/**
2972 * This class is not a proper subclass of moodle_database. It is
2973 * intended to be used only in unit tests, in order to gain access to the
2974 * protected methods of moodle_database, and unit test them.
2975 */
2976class moodle_database_for_testing extends moodle_database {
2977 protected $prefix = 'mdl_';
2978
2979 public function public_fix_table_names($sql) {
2980 return $this->fix_table_names($sql);
2981 }
2982
2983 public function driver_installed(){}
2984 public function get_dbfamily(){}
2985 protected function get_dbtype(){}
16a5642c 2986 protected function get_dblibrary(){}
7f9f11b5 2987 public function get_name(){}
37d975e1 2988 public function get_configuration_help(){}
7f9f11b5 2989 public function get_configuration_hints(){}
beaa43db 2990 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2991 public function get_server_info(){}
2992 protected function allowed_param_types(){}
2993 public function get_last_error(){}
117679db 2994 public function get_tables($usecache=true){}
7f9f11b5 2995 public function get_indexes($table){}
2996 public function get_columns($table, $usecache=true){}
e3acc8af 2997 protected function normalise_value($column, $value){}
7f9f11b5 2998 public function set_debug($state){}
2999 public function get_debug(){}
3000 public function set_logging($state){}
3001 public function change_database_structure($sql){}
3002 public function execute($sql, array $params=null){}
3003 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
3004 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
3005 public function get_fieldset_sql($sql, array $params=null){}
94898738 3006 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 3007 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 3008 public function import_record($table, $dataobject){}
7f9f11b5 3009 public function update_record_raw($table, $params, $bulk=false){}
3010 public function update_record($table, $dataobject, $bulk=false){}
3011 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
3012 public function delete_records_select($table, $select, array $params=null){}
3013 public function sql_concat(){}
3014 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 3015 public function sql_substr($expr, $start, $length=false){}
d5a8d9aa
PS
3016 public function begin_transaction() {}
3017 public function commit_transaction() {}
3018 public function rollback_transaction() {}
bb78c788 3019}