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