projects / moodle.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
MDL-41022 cleanup core_dml_testcase
[moodle.git] / lib / dml / tests / dml_test.php
CommitLineData
a3d5830a
PS		 1<?php
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
9d6d32b6 18 * DML layer tests.
a3d5830a 19 *
00902cd9 20 * @package core_dml
a3d5830a
PS		 21 * @category phpunit
22 * @copyright 2008 Nicolas Connault
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
24 */
25
26defined('MOODLE_INTERNAL') || die();
27
8252b7c2 28class core_dml_testcase extends database_driver_testcase {
a3d5830a 29
4787e466
PS		 30 protected function setUp() {
31 parent::setUp();
9d6d32b6 32 $dbman = $this->tdb->get_manager(); // Loads DDL libs.
4787e466
PS		 33 }
34
a3d5830a
PS		 35 /**
36 * Get a xmldb_table object for testing, deleting any existing table
37 * of the same name, for example if one was left over from a previous test
38 * run that crashed.
39 *
40 * @param string $suffix table name suffix, use if you need more test tables
41 * @return xmldb_table the table object.
42 */
43 private function get_test_table($suffix = '') {
44 $tablename = "test_table";
45 if ($suffix !== '') {
46 $tablename .= $suffix;
47 }
48
49 $table = new xmldb_table($tablename);
50 $table->setComment("This is a test'n drop table. You can drop it safely");
51 return new xmldb_table($tablename);
52 }
53
9d6d32b6 54 public function test_diagnose() {
a3d5830a
PS		 55 $DB = $this->tdb;
56 $result = $DB->diagnose();
57 $this->assertNull($result, 'Database self diagnostics failed %s');
58 }
59
9d6d32b6 60 public function test_get_server_info() {
a3d5830a
PS		 61 $DB = $this->tdb;
62 $result = $DB->get_server_info();
9d6d32b6
PS		 63 $this->assertInternalType('array', $result);
64 $this->assertArrayHasKey('description', $result);
65 $this->assertArrayHasKey('version', $result);
a3d5830a
PS		 66 }
67
68 public function test_get_in_or_equal() {
69 $DB = $this->tdb;
70
9d6d32b6 71 // SQL_PARAMS_QM - IN or =.
a3d5830a 72
9d6d32b6 73 // Correct usage of multiple values.
a3d5830a
PS		 74 $in_values = array('value1', 'value2', '3', 4, null, false, true);
75 list($usql, $params) = $DB->get_in_or_equal($in_values);
9d6d32b6 76 $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql);
a3d5830a
PS		 77 $this->assertEquals(count($in_values), count($params));
78 foreach ($params as $key => $value) {
79 $this->assertSame($in_values[$key], $value);
80 }
81
9d6d32b6 82 // Correct usage of single value (in an array).
a3d5830a
PS		 83 $in_values = array('value1');
84 list($usql, $params) = $DB->get_in_or_equal($in_values);
85 $this->assertEquals("= ?", $usql);
9d6d32b6 86 $this->assertCount(1, $params);
a3d5830a
PS		 87 $this->assertEquals($in_values[0], $params[0]);
88
9d6d32b6 89 // Correct usage of single value.
a3d5830a
PS		 90 $in_value = 'value1';
91 list($usql, $params) = $DB->get_in_or_equal($in_values);
92 $this->assertEquals("= ?", $usql);
9d6d32b6 93 $this->assertCount(1, $params);
a3d5830a
PS		 94 $this->assertEquals($in_value, $params[0]);
95
9d6d32b6 96 // SQL_PARAMS_QM - NOT IN or <>.
a3d5830a 97
9d6d32b6 98 // Correct usage of multiple values.
a3d5830a
PS		 99 $in_values = array('value1', 'value2', 'value3', 'value4');
100 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
101 $this->assertEquals("NOT IN (?,?,?,?)", $usql);
9d6d32b6 102 $this->assertCount(4, $params);
a3d5830a
PS		 103 foreach ($params as $key => $value) {
104 $this->assertEquals($in_values[$key], $value);
105 }
106
9d6d32b6 107 // Correct usage of single value (in array().
a3d5830a
PS		 108 $in_values = array('value1');
109 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
110 $this->assertEquals("<> ?", $usql);
9d6d32b6 111 $this->assertCount(1, $params);
a3d5830a
PS		 112 $this->assertEquals($in_values[0], $params[0]);
113
9d6d32b6 114 // Correct usage of single value.
a3d5830a
PS		 115 $in_value = 'value1';
116 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
117 $this->assertEquals("<> ?", $usql);
9d6d32b6 118 $this->assertCount(1, $params);
a3d5830a
PS		 119 $this->assertEquals($in_value, $params[0]);
120
9d6d32b6 121 // SQL_PARAMS_NAMED - IN or =.
a3d5830a 122
9d6d32b6 123 // Correct usage of multiple values.
a3d5830a
PS		 124 $in_values = array('value1', 'value2', 'value3', 'value4');
125 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
9d6d32b6 126 $this->assertCount(4, $params);
a3d5830a
PS		 127 reset($in_values);
128 $ps = array();
129 foreach ($params as $key => $value) {
130 $this->assertEquals(current($in_values), $value);
131 next($in_values);
132 $ps[] = ':'.$key;
133 }
134 $this->assertEquals("IN (".implode(',', $ps).")", $usql);
135
9d6d32b6 136 // Correct usage of single values (in array).
a3d5830a
PS		 137 $in_values = array('value1');
138 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
9d6d32b6 139 $this->assertCount(1, $params);
a3d5830a
PS		 140 $value = reset($params);
141 $key = key($params);
142 $this->assertEquals("= :$key", $usql);
143 $this->assertEquals($in_value, $value);
144
9d6d32b6 145 // Correct usage of single value.
a3d5830a
PS		 146 $in_value = 'value1';
147 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
9d6d32b6 148 $this->assertCount(1, $params);
a3d5830a
PS		 149 $value = reset($params);
150 $key = key($params);
151 $this->assertEquals("= :$key", $usql);
152 $this->assertEquals($in_value, $value);
153
9d6d32b6 154 // SQL_PARAMS_NAMED - NOT IN or <>.
a3d5830a 155
9d6d32b6 156 // Correct usage of multiple values.
a3d5830a
PS		 157 $in_values = array('value1', 'value2', 'value3', 'value4');
158 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
9d6d32b6 159 $this->assertCount(4, $params);
a3d5830a
PS		 160 reset($in_values);
161 $ps = array();
162 foreach ($params as $key => $value) {
163 $this->assertEquals(current($in_values), $value);
164 next($in_values);
165 $ps[] = ':'.$key;
166 }
167 $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql);
168
9d6d32b6 169 // Correct usage of single values (in array).
a3d5830a
PS		 170 $in_values = array('value1');
171 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
9d6d32b6 172 $this->assertCount(1, $params);
a3d5830a
PS		 173 $value = reset($params);
174 $key = key($params);
175 $this->assertEquals("<> :$key", $usql);
176 $this->assertEquals($in_value, $value);
177
9d6d32b6 178 // Correct usage of single value.
a3d5830a
PS		 179 $in_value = 'value1';
180 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
9d6d32b6 181 $this->assertCount(1, $params);
a3d5830a
PS		 182 $value = reset($params);
183 $key = key($params);
184 $this->assertEquals("<> :$key", $usql);
185 $this->assertEquals($in_value, $value);
186
9d6d32b6
PS		 187 // Make sure the param names are unique.
188 list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
189 list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
a3d5830a
PS		 190 $params1 = array_keys($params1);
191 $params2 = array_keys($params2);
192 $common = array_intersect($params1, $params2);
9d6d32b6 193 $this->assertCount(0, $common);
a3d5830a 194
9d6d32b6 195 // Some incorrect tests.
a3d5830a 196
9d6d32b6 197 // Incorrect usage passing not-allowed params type.
a3d5830a
PS		 198 $in_values = array(1, 2, 3);
199 try {
200 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);
201 $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');
9d6d32b6
PS		 202 } catch (moodle_exception $e) {
203 $this->assertInstanceOf('dml_exception', $e);
204 $this->assertSame('typenotimplement', $e->errorcode);
a3d5830a
PS		 205 }
206
9d6d32b6 207 // Incorrect usage passing empty array.
a3d5830a
PS		 208 $in_values = array();
209 try {
210 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
211 $this->fail('An Exception is missing, expected due to empty array of items');
9d6d32b6
PS		 212 } catch (moodle_exception $e) {
213 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS		 214 }
215
9d6d32b6 216 // Test using $onemptyitems.
a3d5830a 217
9d6d32b6 218 // Correct usage passing empty array and $onemptyitems = null (equal = true, QM).
a3d5830a 219 $in_values = array();
9d6d32b6
PS		 220 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null);
221 $this->assertSame(' IS NULL', $usql);
a3d5830a
PS		 222 $this->assertSame(array(), $params);
223
9d6d32b6 224 // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED).
a3d5830a 225 $in_values = array();
9d6d32b6
PS		 226 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null);
227 $this->assertSame(' IS NOT NULL', $usql);
a3d5830a
PS		 228 $this->assertSame(array(), $params);
229
9d6d32b6 230 // Correct usage passing empty array and $onemptyitems = true (equal = true, QM).
a3d5830a
PS		 231 $in_values = array();
232 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);
9d6d32b6 233 $this->assertSame('= ?', $usql);
a3d5830a
PS		 234 $this->assertSame(array(true), $params);
235
9d6d32b6 236 // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED).
a3d5830a
PS		 237 $in_values = array();
238 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);
9d6d32b6 239 $this->assertCount(1, $params);
a3d5830a
PS		 240 $value = reset($params);
241 $key = key($params);
9d6d32b6 242 $this->assertSame('<> :'.$key, $usql);
a3d5830a
PS		 243 $this->assertSame($value, true);
244
9d6d32b6 245 // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM).
a3d5830a
PS		 246 $in_values = array();
247 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);
9d6d32b6 248 $this->assertSame('= ?', $usql);
a3d5830a
PS		 249 $this->assertSame(array(-1), $params);
250
9d6d32b6 251 // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED).
a3d5830a
PS		 252 $in_values = array();
253 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);
9d6d32b6 254 $this->assertCount(1, $params);
a3d5830a
PS		 255 $value = reset($params);
256 $key = key($params);
9d6d32b6 257 $this->assertSame('<> :'.$key, $usql);
a3d5830a
PS		 258 $this->assertSame($value, -1);
259
9d6d32b6 260 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM).
a3d5830a
PS		 261 $in_values = array();
262 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');
9d6d32b6 263 $this->assertSame('= ?', $usql);
a3d5830a
PS		 264 $this->assertSame(array('onevalue'), $params);
265
9d6d32b6 266 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED).
a3d5830a
PS		 267 $in_values = array();
268 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');
9d6d32b6 269 $this->assertCount(1, $params);
a3d5830a
PS		 270 $value = reset($params);
271 $key = key($params);
9d6d32b6 272 $this->assertSame('<> :'.$key, $usql);
a3d5830a
PS		 273 $this->assertSame($value, 'onevalue');
274 }
275
276 public function test_fix_table_names() {
277 $DB = new moodle_database_for_testing();
278 $prefix = $DB->get_prefix();
279
9d6d32b6 280 // Simple placeholder.
a3d5830a
PS		 281 $placeholder = "{user_123}";
282 $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder));
283
9d6d32b6 284 // Wrong table name.
a3d5830a
PS		 285 $placeholder = "{user-a}";
286 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
287
9d6d32b6 288 // Wrong table name.
a3d5830a
PS		 289 $placeholder = "{123user}";
290 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
291
9d6d32b6 292 // Full SQL.
a3d5830a
PS		 293 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
294 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
295 $this->assertSame($expected, $DB->public_fix_table_names($sql));
296 }
297
9d6d32b6 298 public function test_fix_sql_params() {
a3d5830a 299 $DB = $this->tdb;
c56cdae9 300 $prefix = $DB->get_prefix();
a3d5830a
PS		 301
302 $table = $this->get_test_table();
303 $tablename = $table->getName();
304
9d6d32b6 305 // Correct table placeholder substitution.
a3d5830a
PS		 306 $sql = "SELECT * FROM {{$tablename}}";
307 $sqlarray = $DB->fix_sql_params($sql);
c56cdae9 308 $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]);
a3d5830a 309
9d6d32b6 310 // Conversions of all param types.
a3d5830a 311 $sql = array();
c56cdae9
PŠ		 312 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2";
313 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?";
314 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2";
a3d5830a
PS		 315
316 $params = array();
317 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
318 $params[SQL_PARAMS_QM] = array('first record', 1);
319 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
320
321 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
322 $this->assertSame($rsql, $sql[$rtype]);
323 $this->assertSame($rparams, $params[$rtype]);
324
325 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
326 $this->assertSame($rsql, $sql[$rtype]);
327 $this->assertSame($rparams, $params[$rtype]);
328
329 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
330 $this->assertSame($rsql, $sql[$rtype]);
331 $this->assertSame($rparams, $params[$rtype]);
332
9d6d32b6 333 // Malformed table placeholder.
a3d5830a
PS		 334 $sql = "SELECT * FROM [testtable]";
335 $sqlarray = $DB->fix_sql_params($sql);
336 $this->assertSame($sql, $sqlarray[0]);
337
9d6d32b6 338 // Mixed param types (colon and dollar).
a3d5830a
PS		 339 $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";
340 $params = array('param1' => 'record1', 'param2' => 3);
341 try {
342 $DB->fix_sql_params($sql, $params);
343 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 344 } catch (moodle_exception $e) {
345 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 346 }
347
9d6d32b6 348 // Mixed param types (question and dollar).
a3d5830a
PS		 349 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";
350 $params = array('param1' => 'record2', 'param2' => 5);
351 try {
352 $DB->fix_sql_params($sql, $params);
353 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 354 } catch (moodle_exception $e) {
355 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 356 }
357
9d6d32b6 358 // Too few params in sql.
a3d5830a
PS		 359 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";
360 $params = array('record2', 3);
361 try {
362 $DB->fix_sql_params($sql, $params);
363 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 364 } catch (moodle_exception $e) {
365 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 366 }
367
9d6d32b6 368 // Too many params in array: no error, just use what is necessary.
a3d5830a
PS		 369 $params[] = 1;
370 $params[] = time();
9d6d32b6
PS		 371 $sqlarray = $DB->fix_sql_params($sql, $params);
372 $this->assertInternalType('array', $sqlarray);
373 $this->assertCount(3, $sqlarray[1]);
a3d5830a 374
9d6d32b6 375 // Named params missing from array.
a3d5830a
PS		 376 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
377 $params = array('wrongname' => 'record1', 'course' => 1);
378 try {
379 $DB->fix_sql_params($sql, $params);
380 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 381 } catch (moodle_exception $e) {
382 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 383 }
384
385 // Duplicate named param in query - this is a very important feature!!
9d6d32b6 386 // it helps with debugging of sloppy code.
a3d5830a
PS		 387 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";
388 $params = array('name' => 'record2', 'course' => 3);
389 try {
390 $DB->fix_sql_params($sql, $params);
391 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 392 } catch (moodle_exception $e) {
393 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 394 }
395
9d6d32b6 396 // Extra named param is ignored.
a3d5830a
PS		 397 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
398 $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');
9d6d32b6
PS		 399 $sqlarray = $DB->fix_sql_params($sql, $params);
400 $this->assertInternalType('array', $sqlarray);
401 $this->assertCount(2, $sqlarray[1]);
a3d5830a 402
9d6d32b6 403 // Params exceeding 30 chars length.
a3d5830a
PS		 404 $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
405 $params = array('long_placeholder_with_more_than_30' => 'record1');
406 try {
407 $DB->fix_sql_params($sql, $params);
408 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 409 } catch (moodle_exception $e) {
410 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS		 411 }
412
9d6d32b6 413 // Booleans in NAMED params are casting to 1/0 int.
a3d5830a
PS		 414 $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";
415 $params = array(true, false);
416 list($sql, $params) = $DB->fix_sql_params($sql, $params);
417 $this->assertTrue(reset($params) === 1);
418 $this->assertTrue(next($params) === 0);
419
9d6d32b6 420 // Booleans in QM params are casting to 1/0 int.
a3d5830a
PS		 421 $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";
422 $params = array('course1' => true, 'course2' => false);
423 list($sql, $params) = $DB->fix_sql_params($sql, $params);
424 $this->assertTrue(reset($params) === 1);
425 $this->assertTrue(next($params) === 0);
426
9d6d32b6 427 // Booleans in DOLLAR params are casting to 1/0 int.
a3d5830a
PS		 428 $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";
429 $params = array(true, false);
430 list($sql, $params) = $DB->fix_sql_params($sql, $params);
431 $this->assertTrue(reset($params) === 1);
432 $this->assertTrue(next($params) === 0);
433
9d6d32b6 434 // No data types are touched except bool.
a3d5830a 435 $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";
9d6d32b6 436 $inparams = array('abc', 'ABC', null, '1', 1, 1.4);
a3d5830a
PS		 437 list($sql, $params) = $DB->fix_sql_params($sql, $inparams);
438 $this->assertSame(array_values($params), array_values($inparams));
439 }
440
441 public function test_strtok() {
9d6d32b6 442 // Strtok was previously used by bound emulation, make sure it is not used any more.
a3d5830a
PS		 443 $DB = $this->tdb;
444 $dbman = $this->tdb->get_manager();
445
446 $table = $this->get_test_table();
447 $tablename = $table->getName();
448
9d6d32b6
PS		 449 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
450 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 451 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
452 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
453 $dbman->create_table($table);
454
455 $str = 'a?b?c?d';
456 $this->assertSame(strtok($str, '?'), 'a');
457
458 $DB->get_records($tablename, array('id'=>1));
459
460 $this->assertSame(strtok('?'), 'b');
461 }
462
463 public function test_tweak_param_names() {
464 // Note the tweak_param_names() method is only available in the oracle driver,
9d6d32b6 465 // hence we look for expected results indirectly, by testing various DML methods.
a3d5830a
PS		 466 // with some "extreme" conditions causing the tweak to happen.
467 $DB = $this->tdb;
468 $dbman = $this->tdb->get_manager();
469
470 $table = $this->get_test_table();
471 $tablename = $table->getName();
472
9d6d32b6
PS		 473 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
474 // Add some columns with 28 chars in the name.
a3d5830a
PS		 475 $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
476 $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
477 $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
9d6d32b6 478 // Add some columns with 30 chars in the name.
a3d5830a
PS		 479 $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
480 $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
481 $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');
482
483 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
484
485 $dbman->create_table($table);
486
487 $this->assertTrue($dbman->table_exists($tablename));
488
9d6d32b6 489 // Test insert record.
a3d5830a
PS		 490 $rec1 = new stdClass();
491 $rec1->long_int_columnname_with_28c = 28;
492 $rec1->long_dec_columnname_with_28c = 28.28;
493 $rec1->long_str_columnname_with_28c = '28';
494 $rec1->long_int_columnname_with_30cxx = 30;
495 $rec1->long_dec_columnname_with_30cxx = 30.30;
496 $rec1->long_str_columnname_with_30cxx = '30';
497
9d6d32b6 498 // Insert_record().
a3d5830a
PS		 499 $rec1->id = $DB->insert_record($tablename, $rec1);
500 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
501
9d6d32b6 502 // Update_record().
a3d5830a
PS		 503 $DB->update_record($tablename, $rec1);
504 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
505
9d6d32b6 506 // Set_field().
a3d5830a
PS		 507 $rec1->long_int_columnname_with_28c = 280;
508 $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
509 array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
510 $rec1->long_dec_columnname_with_28c = 280.28;
511 $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
512 array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
513 $rec1->long_str_columnname_with_28c = '280';
514 $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
515 array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
516 $rec1->long_int_columnname_with_30cxx = 300;
517 $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
518 array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
519 $rec1->long_dec_columnname_with_30cxx = 300.30;
520 $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
521 array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
522 $rec1->long_str_columnname_with_30cxx = '300';
523 $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
524 array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
525 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
526
9d6d32b6 527 // Delete_records().
a3d5830a
PS		 528 $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
529 $rec2->id = $DB->insert_record($tablename, $rec2);
530 $this->assertEquals(2, $DB->count_records($tablename));
531 $DB->delete_records($tablename, (array) $rec2);
532 $this->assertEquals(1, $DB->count_records($tablename));
533
9d6d32b6 534 // Get_recordset().
a3d5830a
PS		 535 $rs = $DB->get_recordset($tablename, (array) $rec1);
536 $iterations = 0;
537 foreach ($rs as $rec2) {
538 $iterations++;
539 }
540 $rs->close();
541 $this->assertEquals(1, $iterations);
542 $this->assertEquals($rec1, $rec2);
543
9d6d32b6 544 // Get_records().
a3d5830a 545 $recs = $DB->get_records($tablename, (array) $rec1);
9d6d32b6 546 $this->assertCount(1, $recs);
a3d5830a
PS		 547 $this->assertEquals($rec1, reset($recs));
548
9d6d32b6 549 // Get_fieldset_select().
a3d5830a
PS		 550 $select = 'id = :id AND
551 long_int_columnname_with_28c = :long_int_columnname_with_28c AND
552 long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
553 long_str_columnname_with_28c = :long_str_columnname_with_28c AND
554 long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
555 long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
556 long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
557 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
9d6d32b6 558 $this->assertCount(1, $fields);
a3d5830a
PS		 559 $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));
560 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
561 $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));
562 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
563 $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields));
564 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
565 $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields));
566 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
567 $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields));
568 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
569 $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));
570
9d6d32b6 571 // Overlapping placeholders (progressive str_replace).
a3d5830a
PS		 572 $overlapselect = 'id = :p AND
573 long_int_columnname_with_28c = :param1 AND
574 long_dec_columnname_with_28c = :param2 AND
575 long_str_columnname_with_28c = :param_with_29_characters_long AND
576 long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
577 long_dec_columnname_with_30cxx = :param_ AND
578 long_str_columnname_with_30cxx = :param__';
579 $overlapparams = array(
580 'p' => $rec1->id,
581 'param1' => $rec1->long_int_columnname_with_28c,
582 'param2' => $rec1->long_dec_columnname_with_28c,
583 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
584 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
585 'param_' => $rec1->long_dec_columnname_with_30cxx,
586 'param__' => $rec1->long_str_columnname_with_30cxx);
587 $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
9d6d32b6 588 $this->assertCount(1, $recs);
a3d5830a
PS		 589 $this->assertEquals($rec1, reset($recs));
590
9d6d32b6 591 // Execute().
a3d5830a
PS		 592 $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
593 $this->assertEquals(0, $DB->count_records($tablename));
594 }
595
596 public function test_get_tables() {
597 $DB = $this->tdb;
598 $dbman = $this->tdb->get_manager();
599
9d6d32b6 600 // Need to test with multiple DBs.
a3d5830a
PS		 601 $table = $this->get_test_table();
602 $tablename = $table->getName();
603
604 $original_count = count($DB->get_tables());
605
9d6d32b6 606 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
a3d5830a
PS		 607 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
608
609 $dbman->create_table($table);
610 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
611
612 $dbman->drop_table($table);
613 $this->assertTrue(count($DB->get_tables()) == $original_count);
614 }
615
616 public function test_get_indexes() {
617 $DB = $this->tdb;
618 $dbman = $this->tdb->get_manager();
619
620 $table = $this->get_test_table();
621 $tablename = $table->getName();
622
9d6d32b6
PS		 623 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
624 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 625 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
626 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
627 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
628 $dbman->create_table($table);
629
630 $indices = $DB->get_indexes($tablename);
9d6d32b6
PS		 631 $this->assertInternalType('array', $indices);
632 $this->assertCount(2, $indices);
633 // We do not care about index names for now.
a3d5830a
PS		 634 $first = array_shift($indices);
635 $second = array_shift($indices);
636 if (count($first['columns']) == 2) {
637 $composed = $first;
638 $single = $second;
639 } else {
640 $composed = $second;
641 $single = $first;
642 }
643 $this->assertFalse($single['unique']);
644 $this->assertTrue($composed['unique']);
9d6d32b6
PS		 645 $this->assertCount(1, $single['columns']);
646 $this->assertCount(2, $composed['columns']);
647 $this->assertSame('course', $single['columns'][0]);
648 $this->assertSame('course', $composed['columns'][0]);
649 $this->assertSame('id', $composed['columns'][1]);
a3d5830a
PS		 650 }
651
652 public function test_get_columns() {
653 $DB = $this->tdb;
654 $dbman = $this->tdb->get_manager();
655
656 $table = $this->get_test_table();
657 $tablename = $table->getName();
658
9d6d32b6
PS		 659 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
660 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 661 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
662 $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
663 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
664 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
665 $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300);
666 $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);
667 $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');
668 $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');
669 $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');
bc5a5527
PS		 670 $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0');
671 $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0');
672 $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0');
673 $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0');
674 $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0');
675 $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0');
676 $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0');
677 $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0');
678 $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0');
679 $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
680 $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0');
a3d5830a
PS		 681 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
682 $dbman->create_table($table);
683
684 $columns = $DB->get_columns($tablename);
9d6d32b6 685 $this->assertInternalType('array', $columns);
a3d5830a
PS		 686
687 $fields = $table->getFields();
9d6d32b6 688 $this->assertCount(count($columns), $fields);
a3d5830a
PS		 689
690 $field = $columns['id'];
9d6d32b6 691 $this->assertSame('R', $field->meta_type);
a3d5830a
PS		 692 $this->assertTrue($field->auto_increment);
693 $this->assertTrue($field->unique);
694
695 $field = $columns['course'];
9d6d32b6 696 $this->assertSame('I', $field->meta_type);
a3d5830a
PS		 697 $this->assertFalse($field->auto_increment);
698 $this->assertTrue($field->has_default);
699 $this->assertEquals(0, $field->default_value);
700 $this->assertTrue($field->not_null);
701
9d6d32b6 702 for ($i=1; $i<=10; $i++) {
bc5a5527 703 $field = $columns['someint'.$i];
9d6d32b6 704 $this->assertSame('I', $field->meta_type);
bc5a5527
PS		 705 $this->assertGreaterThanOrEqual($i, $field->max_length);
706 }
707 $field = $columns['someint18'];
9d6d32b6 708 $this->assertSame('I', $field->meta_type);
bc5a5527
PS		 709 $this->assertGreaterThanOrEqual(18, $field->max_length);
710
a3d5830a 711 $field = $columns['name'];
9d6d32b6 712 $this->assertSame('C', $field->meta_type);
a3d5830a
PS		 713 $this->assertFalse($field->auto_increment);
714 $this->assertEquals(255, $field->max_length);
715 $this->assertTrue($field->has_default);
716 $this->assertSame('lala', $field->default_value);
717 $this->assertFalse($field->not_null);
718
719 $field = $columns['description'];
9d6d32b6 720 $this->assertSame('X', $field->meta_type);
a3d5830a
PS		 721 $this->assertFalse($field->auto_increment);
722 $this->assertFalse($field->has_default);
9d6d32b6 723 $this->assertNull($field->default_value);
a3d5830a
PS		 724 $this->assertFalse($field->not_null);
725
726 $field = $columns['enumfield'];
9d6d32b6 727 $this->assertSame('C', $field->meta_type);
a3d5830a
PS		 728 $this->assertFalse($field->auto_increment);
729 $this->assertSame('test2', $field->default_value);
730 $this->assertTrue($field->not_null);
731
732 $field = $columns['onenum'];
9d6d32b6 733 $this->assertSame('N', $field->meta_type);
a3d5830a
PS		 734 $this->assertFalse($field->auto_increment);
735 $this->assertEquals(10, $field->max_length);
736 $this->assertEquals(2, $field->scale);
737 $this->assertTrue($field->has_default);
738 $this->assertEquals(200.0, $field->default_value);
739 $this->assertFalse($field->not_null);
740
741 $field = $columns['onefloat'];
9d6d32b6 742 $this->assertSame('N', $field->meta_type);
a3d5830a
PS		 743 $this->assertFalse($field->auto_increment);
744 $this->assertTrue($field->has_default);
745 $this->assertEquals(300.0, $field->default_value);
746 $this->assertFalse($field->not_null);
747
748 $field = $columns['anotherfloat'];
9d6d32b6 749 $this->assertSame('N', $field->meta_type);
a3d5830a
PS		 750 $this->assertFalse($field->auto_increment);
751 $this->assertTrue($field->has_default);
752 $this->assertEquals(400.0, $field->default_value);
753 $this->assertFalse($field->not_null);
754
9d6d32b6 755 // Test negative defaults in numerical columns.
a3d5830a
PS		 756 $field = $columns['negativedfltint'];
757 $this->assertTrue($field->has_default);
758 $this->assertEquals(-1, $field->default_value);
759
760 $field = $columns['negativedfltnumber'];
761 $this->assertTrue($field->has_default);
762 $this->assertEquals(-2, $field->default_value);
763
764 $field = $columns['negativedfltfloat'];
765 $this->assertTrue($field->has_default);
766 $this->assertEquals(-3, $field->default_value);
767
768 for ($i = 0; $i < count($columns); $i++) {
769 if ($i == 0) {
770 $next_column = reset($columns);
771 $next_field = reset($fields);
772 } else {
773 $next_column = next($columns);
774 $next_field = next($fields);
775 }
776
94417438 777 $this->assertEquals($next_column->name, $next_field->getName());
a3d5830a
PS		 778 }
779
9d6d32b6 780 // Test get_columns for non-existing table returns empty array. MDL-30147.
a3d5830a
PS		 781 $columns = $DB->get_columns('xxxx');
782 $this->assertEquals(array(), $columns);
4bf85577 783
9d6d32b6 784 // Create something similar to "context_temp" with id column without sequence.
4bf85577
PS		 785 $dbman->drop_table($table);
786 $table = $this->get_test_table();
787 $tablename = $table->getName();
9d6d32b6
PS		 788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
4bf85577
PS		 790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
791 $dbman->create_table($table);
792
793 $columns = $DB->get_columns($tablename);
794 $this->assertFalse($columns['id']->auto_increment);
a3d5830a
PS		 795 }
796
797 public function test_get_manager() {
798 $DB = $this->tdb;
799 $dbman = $this->tdb->get_manager();
800
9d6d32b6 801 $this->assertInstanceOf('database_manager', $dbman);
a3d5830a
PS		 802 }
803
804 public function test_setup_is_unicodedb() {
805 $DB = $this->tdb;
806 $this->assertTrue($DB->setup_is_unicodedb());
807 }
808
9d6d32b6 809 public function test_set_debug() { // Tests get_debug() too.
a3d5830a
PS		 810 $DB = $this->tdb;
811 $dbman = $this->tdb->get_manager();
812
813 $table = $this->get_test_table();
814 $tablename = $table->getName();
815
9d6d32b6
PS		 816 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
817 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 818 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
819 $dbman->create_table($table);
820
821 $sql = "SELECT * FROM {{$tablename}}";
822
823 $prevdebug = $DB->get_debug();
824
825 ob_start();
826 $DB->set_debug(true);
827 $this->assertTrue($DB->get_debug());
828 $DB->execute($sql);
829 $DB->set_debug(false);
830 $this->assertFalse($DB->get_debug());
831 $debuginfo = ob_get_contents();
832 ob_end_clean();
833 $this->assertFalse($debuginfo === '');
834
835 ob_start();
836 $DB->execute($sql);
837 $debuginfo = ob_get_contents();
838 ob_end_clean();
839 $this->assertTrue($debuginfo === '');
840
841 $DB->set_debug($prevdebug);
842 }
843
844 public function test_execute() {
845 $DB = $this->tdb;
846 $dbman = $this->tdb->get_manager();
847
848 $table1 = $this->get_test_table('1');
849 $tablename1 = $table1->getName();
9d6d32b6
PS		 850 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
851 $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 852 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
853 $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
854 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
855 $dbman->create_table($table1);
856
857 $table2 = $this->get_test_table('2');
858 $tablename2 = $table2->getName();
9d6d32b6
PS		 859 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
860 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 861 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
862 $dbman->create_table($table2);
863
864 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));
865 $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));
866 $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));
867 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));
868
9d6d32b6 869 // Select results are ignored.
a3d5830a
PS		 870 $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";
871 $this->assertTrue($DB->execute($sql, array('course'=>3)));
872
9d6d32b6 873 // Throw exception on error.
a3d5830a
PS		 874 $sql = "XXUPDATE SET XSSD";
875 try {
876 $DB->execute($sql);
877 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 878 } catch (moodle_exception $e) {
879 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 880 }
881
9d6d32b6 882 // Update records.
a3d5830a
PS		 883 $sql = "UPDATE {{$tablename1}}
884 SET course = 6
885 WHERE course = ?";
886 $this->assertTrue($DB->execute($sql, array('3')));
9d6d32b6 887 $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6)));
a3d5830a 888
9d6d32b6
PS		 889 // Update records with subquery condition.
890 // Confirm that the option not using table aliases is cross-db.
a3d5830a
PS		 891 $sql = "UPDATE {{$tablename1}}
892 SET course = 0
893 WHERE NOT EXISTS (
894 SELECT course
895 FROM {{$tablename2}} tbl2
896 WHERE tbl2.course = {{$tablename1}}.course
9d6d32b6 897 AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed.
a3d5830a
PS		 898 $this->assertTrue($DB->execute($sql));
899
9d6d32b6 900 // Insert from one into second table.
a3d5830a
PS		 901 $sql = "INSERT INTO {{$tablename2}} (course)
902
903 SELECT course
904 FROM {{$tablename1}}";
905 $this->assertTrue($DB->execute($sql));
9d6d32b6 906 $this->assertEquals(4, $DB->count_records($tablename2));
a3d5830a
PS		 907 }
908
909 public function test_get_recordset() {
910 $DB = $this->tdb;
911 $dbman = $DB->get_manager();
912
913 $table = $this->get_test_table();
914 $tablename = $table->getName();
915
9d6d32b6
PS		 916 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
917 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 918 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
919 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
920 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
921 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
922 $dbman->create_table($table);
923
401793a3
PŠ		 924 $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'),
925 array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),
926 array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));
a3d5830a 927
9d6d32b6 928 foreach ($data as $key => $record) {
401793a3 929 $data[$key]['id'] = $DB->insert_record($tablename, $record);
a3d5830a
PS		 930 }
931
9d6d32b6 932 // Standard recordset iteration.
a3d5830a 933 $rs = $DB->get_recordset($tablename);
9d6d32b6 934 $this->assertInstanceOf('moodle_recordset', $rs);
a3d5830a 935 reset($data);
9d6d32b6 936 foreach ($rs as $record) {
a3d5830a
PS		 937 $data_record = current($data);
938 foreach ($record as $k => $v) {
939 $this->assertEquals($data_record[$k], $v);
940 }
941 next($data);
942 }
943 $rs->close();
944
9d6d32b6 945 // Iterator style usage.
a3d5830a 946 $rs = $DB->get_recordset($tablename);
9d6d32b6 947 $this->assertInstanceOf('moodle_recordset', $rs);
a3d5830a
PS		 948 reset($data);
949 while ($rs->valid()) {
950 $record = $rs->current();
951 $data_record = current($data);
952 foreach ($record as $k => $v) {
953 $this->assertEquals($data_record[$k], $v);
954 }
955 next($data);
956 $rs->next();
957 }
958 $rs->close();
959
9d6d32b6 960 // Make sure rewind is ignored.
a3d5830a 961 $rs = $DB->get_recordset($tablename);
9d6d32b6 962 $this->assertInstanceOf('moodle_recordset', $rs);
a3d5830a
PS		 963 reset($data);
964 $i = 0;
9d6d32b6 965 foreach ($rs as $record) {
a3d5830a
PS		 966 $i++;
967 $rs->rewind();
968 if ($i > 10) {
969 $this->fail('revind not ignored in recordsets');
970 break;
971 }
972 $data_record = current($data);
973 foreach ($record as $k => $v) {
974 $this->assertEquals($data_record[$k], $v);
975 }
976 next($data);
977 }
978 $rs->close();
979
9d6d32b6 980 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 981 $conditions = array('onetext' => '1');
982 try {
983 $rs = $DB->get_recordset($tablename, $conditions);
eb10d554 984 $this->fail('An Exception is missing, expected due to equating of text fields');
9d6d32b6
PS		 985 } catch (moodle_exception $e) {
986 $this->assertInstanceOf('dml_exception', $e);
987 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 988 }
989
541ca062
PŠ		 990 // Test nested iteration.
991 $rs1 = $DB->get_recordset($tablename);
992 $i = 0;
9d6d32b6 993 foreach ($rs1 as $record1) {
541ca062
PŠ		 994 $rs2 = $DB->get_recordset($tablename);
995 $i++;
996 $j = 0;
9d6d32b6 997 foreach ($rs2 as $record2) {
541ca062
PŠ		 998 $j++;
999 }
1000 $rs2->close();
9d6d32b6 1001 $this->assertCount($j, $data);
541ca062
PŠ		 1002 }
1003 $rs1->close();
9d6d32b6 1004 $this->assertCount($i, $data);
541ca062 1005
9d6d32b6 1006 // Notes:
a3d5830a
PS		 1007 // * limits are tested in test_get_recordset_sql()
1008 // * where_clause() is used internally and is tested in test_get_records()
1009 }
1010
3b5f6e6c
PŠ		 1011 public function test_get_recordset_static() {
1012 $DB = $this->tdb;
1013 $dbman = $DB->get_manager();
1014
1015 $table = $this->get_test_table();
1016 $tablename = $table->getName();
1017
9d6d32b6
PS		 1018 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1019 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3b5f6e6c
PŠ		 1020 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1021 $dbman->create_table($table);
1022
1023 $DB->insert_record($tablename, array('course' => 1));
1024 $DB->insert_record($tablename, array('course' => 2));
1025 $DB->insert_record($tablename, array('course' => 3));
1026 $DB->insert_record($tablename, array('course' => 4));
1027
1028 $rs = $DB->get_recordset($tablename, array(), 'id');
1029
1030 $DB->set_field($tablename, 'course', 666, array('course'=>1));
1031 $DB->delete_records($tablename, array('course'=>2));
1032
1033 $i = 0;
9d6d32b6 1034 foreach ($rs as $record) {
3b5f6e6c
PŠ		 1035 $i++;
1036 $this->assertEquals($i, $record->course);
1037 }
1038 $rs->close();
1039 $this->assertEquals(4, $i);
1040
1041 // Now repeat with limits because it may use different code.
1042 $DB->delete_records($tablename, array());
1043
1044 $DB->insert_record($tablename, array('course' => 1));
1045 $DB->insert_record($tablename, array('course' => 2));
1046 $DB->insert_record($tablename, array('course' => 3));
1047 $DB->insert_record($tablename, array('course' => 4));
1048
1049 $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3);
1050
1051 $DB->set_field($tablename, 'course', 666, array('course'=>1));
1052 $DB->delete_records($tablename, array('course'=>2));
1053
1054 $i = 0;
9d6d32b6 1055 foreach ($rs as $record) {
3b5f6e6c
PŠ		 1056 $i++;
1057 $this->assertEquals($i, $record->course);
1058 }
1059 $rs->close();
1060 $this->assertEquals(3, $i);
1061 }
1062
a3d5830a
PS		 1063 public function test_get_recordset_iterator_keys() {
1064 $DB = $this->tdb;
1065 $dbman = $DB->get_manager();
1066
1067 $table = $this->get_test_table();
1068 $tablename = $table->getName();
1069
9d6d32b6
PS		 1070 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1071 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1072 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1073 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1074 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1075 $dbman->create_table($table);
1076
401793a3
PŠ		 1077 $data = array(array('course' => 3, 'name' => 'record1'),
1078 array('course' => 3, 'name' => 'record2'),
1079 array('course' => 5, 'name' => 'record3'));
9d6d32b6 1080 foreach ($data as $key => $record) {
401793a3 1081 $data[$key]['id'] = $DB->insert_record($tablename, $record);
a3d5830a
PS		 1082 }
1083
9d6d32b6
PS		 1084 // Test repeated numeric keys are returned ok.
1085 $rs = $DB->get_recordset($tablename, null, null, 'course, name, id');
a3d5830a
PS		 1086
1087 reset($data);
1088 $count = 0;
9d6d32b6 1089 foreach ($rs as $key => $record) {
a3d5830a
PS		 1090 $data_record = current($data);
1091 $this->assertEquals($data_record['course'], $key);
1092 next($data);
1093 $count++;
1094 }
1095 $rs->close();
9d6d32b6 1096 $this->assertEquals(3, $count);
a3d5830a 1097
9d6d32b6
PS		 1098 // Test string keys are returned ok.
1099 $rs = $DB->get_recordset($tablename, null, null, 'name, course, id');
a3d5830a
PS		 1100
1101 reset($data);
1102 $count = 0;
9d6d32b6 1103 foreach ($rs as $key => $record) {
a3d5830a
PS		 1104 $data_record = current($data);
1105 $this->assertEquals($data_record['name'], $key);
1106 next($data);
1107 $count++;
1108 }
1109 $rs->close();
9d6d32b6 1110 $this->assertEquals(3, $count);
a3d5830a 1111
9d6d32b6
PS		 1112 // Test numeric not starting in 1 keys are returned ok.
1113 $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name');
a3d5830a
PS		 1114
1115 $data = array_reverse($data);
1116 reset($data);
1117 $count = 0;
9d6d32b6 1118 foreach ($rs as $key => $record) {
a3d5830a
PS		 1119 $data_record = current($data);
1120 $this->assertEquals($data_record['id'], $key);
1121 next($data);
1122 $count++;
1123 }
1124 $rs->close();
9d6d32b6 1125 $this->assertEquals(3, $count);
a3d5830a
PS		 1126 }
1127
1128 public function test_get_recordset_list() {
1129 $DB = $this->tdb;
1130 $dbman = $DB->get_manager();
1131
1132 $table = $this->get_test_table();
1133 $tablename = $table->getName();
1134
9d6d32b6
PS		 1135 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1136 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
a3d5830a
PS		 1137 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1138 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1139 $dbman->create_table($table);
1140
1141 $DB->insert_record($tablename, array('course' => 3));
1142 $DB->insert_record($tablename, array('course' => 3));
1143 $DB->insert_record($tablename, array('course' => 5));
1144 $DB->insert_record($tablename, array('course' => 2));
7f22fb4d
PŠ		 1145 $DB->insert_record($tablename, array('course' => null));
1146 $DB->insert_record($tablename, array('course' => 1));
1147 $DB->insert_record($tablename, array('course' => 0));
a3d5830a
PS		 1148
1149 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
a3d5830a
PS		 1150 $counter = 0;
1151 foreach ($rs as $record) {
1152 $counter++;
1153 }
1154 $this->assertEquals(3, $counter);
1155 $rs->close();
1156
7f22fb4d
PŠ		 1157 $rs = $DB->get_recordset_list($tablename, 'course', array(3));
1158 $counter = 0;
1159 foreach ($rs as $record) {
1160 $counter++;
1161 }
1162 $this->assertEquals(2, $counter);
1163 $rs->close();
1164
1165 $rs = $DB->get_recordset_list($tablename, 'course', array(null));
1166 $counter = 0;
1167 foreach ($rs as $record) {
1168 $counter++;
1169 }
1170 $this->assertEquals(1, $counter);
1171 $rs->close();
1172
1173 $rs = $DB->get_recordset_list($tablename, 'course', array(6, null));
1174 $counter = 0;
1175 foreach ($rs as $record) {
1176 $counter++;
1177 }
1178 $this->assertEquals(1, $counter);
1179 $rs->close();
1180
1181 $rs = $DB->get_recordset_list($tablename, 'course', array(null, 5, 5, 5));
1182 $counter = 0;
1183 foreach ($rs as $record) {
1184 $counter++;
1185 }
1186 $this->assertEquals(2, $counter);
1187 $rs->close();
1188
1189 $rs = $DB->get_recordset_list($tablename, 'course', array(true));
1190 $counter = 0;
1191 foreach ($rs as $record) {
1192 $counter++;
1193 }
1194 $this->assertEquals(1, $counter);
1195 $rs->close();
1196
1197 $rs = $DB->get_recordset_list($tablename, 'course', array(false));
1198 $counter = 0;
1199 foreach ($rs as $record) {
1200 $counter++;
1201 }
1202 $this->assertEquals(1, $counter);
1203 $rs->close();
1204
9d6d32b6 1205 $rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645.
a3d5830a
PS		 1206
1207 $counter = 0;
1208 foreach ($rs as $record) {
1209 $counter++;
1210 }
1211 $rs->close();
1212 $this->assertEquals(0, $counter);
1213
9d6d32b6 1214 // Notes:
a3d5830a
PS		 1215 // * limits are tested in test_get_recordset_sql()
1216 // * where_clause() is used internally and is tested in test_get_records()
1217 }
1218
1219 public function test_get_recordset_select() {
1220 $DB = $this->tdb;
1221 $dbman = $DB->get_manager();
1222
1223 $table = $this->get_test_table();
1224 $tablename = $table->getName();
1225
9d6d32b6
PS		 1226 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1227 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1228 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1229 $dbman->create_table($table);
1230
1231 $DB->insert_record($tablename, array('course' => 3));
1232 $DB->insert_record($tablename, array('course' => 3));
1233 $DB->insert_record($tablename, array('course' => 5));
1234 $DB->insert_record($tablename, array('course' => 2));
1235
1236 $rs = $DB->get_recordset_select($tablename, '');
1237 $counter = 0;
1238 foreach ($rs as $record) {
1239 $counter++;
1240 }
1241 $rs->close();
1242 $this->assertEquals(4, $counter);
1243
1244 $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3'));
1245 $counter = 0;
1246 foreach ($rs as $record) {
1247 $counter++;
1248 }
1249 $rs->close();
1250 $this->assertEquals(2, $counter);
1251
9d6d32b6 1252 // Notes:
a3d5830a
PS		 1253 // * limits are tested in test_get_recordset_sql()
1254 }
1255
1256 public function test_get_recordset_sql() {
1257 $DB = $this->tdb;
1258 $dbman = $DB->get_manager();
1259
1260 $table = $this->get_test_table();
1261 $tablename = $table->getName();
1262
9d6d32b6
PS		 1263 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1264 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1265 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1266 $dbman->create_table($table);
1267
1268 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1269 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1270 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1271 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1272 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1273 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1274 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1275
1276 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1277 $counter = 0;
1278 foreach ($rs as $record) {
1279 $counter++;
1280 }
1281 $rs->close();
1282 $this->assertEquals(2, $counter);
1283
9d6d32b6
PS		 1284 // Limits - only need to test this case, the rest have been tested by test_get_records_sql()
1285 // only limitfrom = skips that number of records.
a3d5830a
PS		 1286 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1287 $records = array();
9d6d32b6 1288 foreach ($rs as $key => $record) {
a3d5830a
PS		 1289 $records[$key] = $record;
1290 }
1291 $rs->close();
9d6d32b6 1292 $this->assertCount(5, $records);
a3d5830a
PS		 1293 $this->assertEquals($inskey3, reset($records)->id);
1294 $this->assertEquals($inskey7, end($records)->id);
1295
9d6d32b6 1296 // Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here.
a3d5830a
PS		 1297 }
1298
cabc4112
PS		 1299 public function test_export_table_recordset() {
1300 $DB = $this->tdb;
1301 $dbman = $DB->get_manager();
1302
1303 $table = $this->get_test_table();
1304 $tablename = $table->getName();
1305
1306 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1307 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1308 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1309 $dbman->create_table($table);
1310
1311 $ids = array();
1312 $ids[] = $DB->insert_record($tablename, array('course' => 3));
1313 $ids[] = $DB->insert_record($tablename, array('course' => 5));
1314 $ids[] = $DB->insert_record($tablename, array('course' => 4));
1315 $ids[] = $DB->insert_record($tablename, array('course' => 3));
1316 $ids[] = $DB->insert_record($tablename, array('course' => 2));
1317 $ids[] = $DB->insert_record($tablename, array('course' => 1));
1318 $ids[] = $DB->insert_record($tablename, array('course' => 0));
1319
1320 $rs = $DB->export_table_recordset($tablename);
1321 $rids = array();
1322 foreach ($rs as $record) {
1323 $rids[] = $record->id;
1324 }
1325 $rs->close();
1326 $this->assertEquals($ids, $rids, '', 0, 0, true);
1327 }
1328
a3d5830a
PS		 1329 public function test_get_records() {
1330 $DB = $this->tdb;
1331 $dbman = $DB->get_manager();
1332
1333 $table = $this->get_test_table();
1334 $tablename = $table->getName();
1335
9d6d32b6
PS		 1336 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1337 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1338 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1339 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1340 $dbman->create_table($table);
1341
1342 $DB->insert_record($tablename, array('course' => 3));
1343 $DB->insert_record($tablename, array('course' => 3));
1344 $DB->insert_record($tablename, array('course' => 5));
1345 $DB->insert_record($tablename, array('course' => 2));
1346
9d6d32b6 1347 // All records.
a3d5830a 1348 $records = $DB->get_records($tablename);
9d6d32b6 1349 $this->assertCount(4, $records);
a3d5830a
PS		 1350 $this->assertEquals(3, $records[1]->course);
1351 $this->assertEquals(3, $records[2]->course);
1352 $this->assertEquals(5, $records[3]->course);
1353 $this->assertEquals(2, $records[4]->course);
1354
9d6d32b6 1355 // Records matching certain conditions.
a3d5830a 1356 $records = $DB->get_records($tablename, array('course' => 3));
9d6d32b6 1357 $this->assertCount(2, $records);
a3d5830a
PS		 1358 $this->assertEquals(3, $records[1]->course);
1359 $this->assertEquals(3, $records[2]->course);
1360
9d6d32b6 1361 // All records sorted by course.
a3d5830a 1362 $records = $DB->get_records($tablename, null, 'course');
9d6d32b6 1363 $this->assertCount(4, $records);
a3d5830a
PS		 1364 $current_record = reset($records);
1365 $this->assertEquals(4, $current_record->id);
1366 $current_record = next($records);
1367 $this->assertEquals(1, $current_record->id);
1368 $current_record = next($records);
1369 $this->assertEquals(2, $current_record->id);
1370 $current_record = next($records);
1371 $this->assertEquals(3, $current_record->id);
1372
9d6d32b6 1373 // All records, but get only one field.
a3d5830a
PS		 1374 $records = $DB->get_records($tablename, null, '', 'id');
1375 $this->assertFalse(isset($records[1]->course));
1376 $this->assertTrue(isset($records[1]->id));
9d6d32b6 1377 $this->assertCount(4, $records);
a3d5830a 1378
9d6d32b6 1379 // Booleans into params.
a3d5830a 1380 $records = $DB->get_records($tablename, array('course' => true));
9d6d32b6 1381 $this->assertCount(0, $records);
a3d5830a 1382 $records = $DB->get_records($tablename, array('course' => false));
9d6d32b6 1383 $this->assertCount(0, $records);
a3d5830a 1384
9d6d32b6 1385 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 1386 $conditions = array('onetext' => '1');
1387 try {
1388 $records = $DB->get_records($tablename, $conditions);
1389 if (debugging()) {
9d6d32b6 1390 // Only in debug mode - hopefully all devs test code in debug mode...
a3d5830a
PS		 1391 $this->fail('An Exception is missing, expected due to equating of text fields');
1392 }
9d6d32b6
PS		 1393 } catch (moodle_exception $e) {
1394 $this->assertInstanceOf('dml_exception', $e);
1395 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 1396 }
1397
9d6d32b6
PS		 1398 // Test get_records passing non-existing table.
1399 // with params.
a3d5830a
PS		 1400 try {
1401 $records = $DB->get_records('xxxx', array('id' => 0));
1402 $this->fail('An Exception is missing, expected due to query against non-existing table');
9d6d32b6
PS		 1403 } catch (moodle_exception $e) {
1404 $this->assertInstanceOf('dml_exception', $e);
a3d5830a 1405 if (debugging()) {
9d6d32b6
PS		 1406 // Information for developers only, normal users get general error message.
1407 $this->assertSame('ddltablenotexist', $e->errorcode);
a3d5830a
PS		 1408 }
1409 }
9d6d32b6 1410 // And without params.
a3d5830a
PS		 1411 try {
1412 $records = $DB->get_records('xxxx', array());
1413 $this->fail('An Exception is missing, expected due to query against non-existing table');
9d6d32b6
PS		 1414 } catch (moodle_exception $e) {
1415 $this->assertInstanceOf('dml_exception', $e);
a3d5830a 1416 if (debugging()) {
9d6d32b6
PS		 1417 // Information for developers only, normal users get general error message.
1418 $this->assertSame('ddltablenotexist', $e->errorcode);
a3d5830a
PS		 1419 }
1420 }
1421
9d6d32b6 1422 // Test get_records passing non-existing column.
a3d5830a
PS		 1423 try {
1424 $records = $DB->get_records($tablename, array('xxxx' => 0));
1425 $this->fail('An Exception is missing, expected due to query against non-existing column');
9d6d32b6
PS		 1426 } catch (moodle_exception $e) {
1427 $this->assertInstanceOf('dml_exception', $e);
a3d5830a 1428 if (debugging()) {
9d6d32b6
PS		 1429 // Information for developers only, normal users get general error message.
1430 $this->assertSame('ddlfieldnotexist', $e->errorcode);
a3d5830a
PS		 1431 }
1432 }
1433
9d6d32b6 1434 // Note: delegate limits testing to test_get_records_sql().
a3d5830a
PS		 1435 }
1436
1437 public function test_get_records_list() {
1438 $DB = $this->tdb;
1439 $dbman = $DB->get_manager();
1440
1441 $table = $this->get_test_table();
1442 $tablename = $table->getName();
1443
9d6d32b6
PS		 1444 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1445 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1446 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1447 $dbman->create_table($table);
1448
1449 $DB->insert_record($tablename, array('course' => 3));
1450 $DB->insert_record($tablename, array('course' => 3));
1451 $DB->insert_record($tablename, array('course' => 5));
1452 $DB->insert_record($tablename, array('course' => 2));
1453
1454 $records = $DB->get_records_list($tablename, 'course', array(3, 2));
9d6d32b6
PS		 1455 $this->assertInternalType('array', $records);
1456 $this->assertCount(3, $records);
a3d5830a
PS		 1457 $this->assertEquals(1, reset($records)->id);
1458 $this->assertEquals(2, next($records)->id);
1459 $this->assertEquals(4, next($records)->id);
1460
9d6d32b6
PS		 1461 $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645.
1462 $this->assertCount(0, $records);
a3d5830a 1463
9d6d32b6 1464 // Note: delegate limits testing to test_get_records_sql().
a3d5830a
PS		 1465 }
1466
1467 public function test_get_records_sql() {
1468 $DB = $this->tdb;
1469 $dbman = $DB->get_manager();
1470
1471 $table = $this->get_test_table();
1472 $tablename = $table->getName();
1473
9d6d32b6
PS		 1474 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1475 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1476 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1477 $dbman->create_table($table);
1478
1479 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1480 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1481 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1482 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1483 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1484 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1485 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1486
1487 $table2 = $this->get_test_table("2");
1488 $tablename2 = $table2->getName();
9d6d32b6
PS		 1489 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1490 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1491 $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
1492 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1493 $dbman->create_table($table2);
1494
1495 $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing'));
1496 $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang'));
1497 $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung'));
1498 $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));
1499
1500 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
9d6d32b6 1501 $this->assertCount(2, $records);
a3d5830a
PS		 1502 $this->assertEquals($inskey1, reset($records)->id);
1503 $this->assertEquals($inskey4, next($records)->id);
1504
9d6d32b6 1505 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test.
a3d5830a 1506 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
eb10d554 1507 $this->assertDebuggingCalled();
9d6d32b6 1508 $this->assertCount(6, $records);
96f81ea3 1509 set_debugging(DEBUG_MINIMAL);
eb10d554
PS		 1510 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1511 $this->assertDebuggingNotCalled();
9d6d32b6 1512 $this->assertCount(6, $records);
96f81ea3 1513 set_debugging(DEBUG_DEVELOPER);
a3d5830a 1514
9d6d32b6 1515 // Negative limits = no limits.
a3d5830a 1516 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
9d6d32b6 1517 $this->assertCount(7, $records);
a3d5830a 1518
9d6d32b6 1519 // Zero limits = no limits.
a3d5830a 1520 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
9d6d32b6 1521 $this->assertCount(7, $records);
a3d5830a 1522
9d6d32b6 1523 // Only limitfrom = skips that number of records.
a3d5830a 1524 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
9d6d32b6 1525 $this->assertCount(5, $records);
a3d5830a
PS		 1526 $this->assertEquals($inskey3, reset($records)->id);
1527 $this->assertEquals($inskey7, end($records)->id);
1528
9d6d32b6 1529 // Only limitnum = fetches that number of records.
a3d5830a 1530 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
9d6d32b6 1531 $this->assertCount(3, $records);
a3d5830a
PS		 1532 $this->assertEquals($inskey1, reset($records)->id);
1533 $this->assertEquals($inskey3, end($records)->id);
1534
9d6d32b6 1535 // Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones.
a3d5830a 1536 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
9d6d32b6 1537 $this->assertCount(2, $records);
a3d5830a
PS		 1538 $this->assertEquals($inskey4, reset($records)->id);
1539 $this->assertEquals($inskey5, end($records)->id);
1540
9d6d32b6
PS		 1541 // Both limitfrom and limitnum in query having subqueris.
1542 // Note the subquery skips records with course = 0 and 3.
a3d5830a
PS		 1543 $sql = "SELECT * FROM {{$tablename}}
1544 WHERE course NOT IN (
1545 SELECT course FROM {{$tablename}}
1546 WHERE course IN (0, 3))
1547 ORDER BY course";
9d6d32b6
PS		 1548 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2.
1549 $this->assertCount(2, $records);
a3d5830a
PS		 1550 $this->assertEquals($inskey6, reset($records)->id);
1551 $this->assertEquals($inskey5, end($records)->id);
9d6d32b6
PS		 1552 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2.
1553 $this->assertCount(2, $records);
a3d5830a
PS		 1554 $this->assertEquals($inskey3, reset($records)->id);
1555 $this->assertEquals($inskey2, end($records)->id);
1556
9d6d32b6 1557 // Test 2 tables with aliases and limits with order bys.
a3d5830a
PS		 1558 $sql = "SELECT t1.id, t1.course AS cid, t2.nametext
1559 FROM {{$tablename}} t1, {{$tablename2}} t2
1560 WHERE t2.course=t1.course
1561 ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');
9d6d32b6
PS		 1562 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5.
1563 $this->assertCount(2, $records);
1564 $this->assertSame('5', end($records)->cid);
1565 $this->assertSame('4', reset($records)->cid);
a3d5830a 1566
9d6d32b6
PS		 1567 // Test 2 tables with aliases and limits with the highest INT limit works.
1568 $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}.
1569 $this->assertCount(2, $records);
1570 $this->assertSame('5', end($records)->cid);
1571 $this->assertSame('4', reset($records)->cid);
a3d5830a 1572
9d6d32b6
PS		 1573 // Test 2 tables with aliases and limits with order bys (limit which is highest INT number).
1574 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses.
1575 $this->assertCount(0, $records);
a3d5830a 1576
9d6d32b6
PS		 1577 // Test 2 tables with aliases and limits with order bys (limit which s highest INT number).
1578 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses.
1579 $this->assertCount(0, $records);
a3d5830a 1580
9d6d32b6 1581 // TODO: Test limits in queries having DISTINCT clauses.
a3d5830a 1582
9d6d32b6 1583 // Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here.
a3d5830a
PS		 1584 }
1585
1586 public function test_get_records_menu() {
1587 $DB = $this->tdb;
1588 $dbman = $DB->get_manager();
1589
1590 $table = $this->get_test_table();
1591 $tablename = $table->getName();
1592
9d6d32b6
PS		 1593 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1594 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1595 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1596 $dbman->create_table($table);
1597
1598 $DB->insert_record($tablename, array('course' => 3));
1599 $DB->insert_record($tablename, array('course' => 3));
1600 $DB->insert_record($tablename, array('course' => 5));
1601 $DB->insert_record($tablename, array('course' => 2));
1602
1603 $records = $DB->get_records_menu($tablename, array('course' => 3));
9d6d32b6
PS		 1604 $this->assertInternalType('array', $records);
1605 $this->assertCount(2, $records);
1606 $this->assertNotEmpty($records[1]);
1607 $this->assertNotEmpty($records[2]);
a3d5830a
PS		 1608 $this->assertEquals(3, $records[1]);
1609 $this->assertEquals(3, $records[2]);
1610
9d6d32b6 1611 // Note: delegate limits testing to test_get_records_sql().
a3d5830a
PS		 1612 }
1613
1614 public function test_get_records_select_menu() {
1615 $DB = $this->tdb;
1616 $dbman = $DB->get_manager();
1617
1618 $table = $this->get_test_table();
1619 $tablename = $table->getName();
1620
9d6d32b6
PS		 1621 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1622 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1623 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1624 $dbman->create_table($table);
1625
1626 $DB->insert_record($tablename, array('course' => 3));
1627 $DB->insert_record($tablename, array('course' => 2));
1628 $DB->insert_record($tablename, array('course' => 3));
1629 $DB->insert_record($tablename, array('course' => 5));
1630
1631 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
9d6d32b6
PS		 1632 $this->assertInternalType('array', $records);
1633
1634 $this->assertCount(3, $records);
1635 $this->assertArrayHasKey(1, $records);
1636 $this->assertArrayNotHasKey(2, $records);
1637 $this->assertArrayHasKey(3, $records);
1638 $this->assertArrayHasKey(4, $records);
1639 $this->assertSame('3', $records[1]);
1640 $this->assertSame('3', $records[3]);
1641 $this->assertSame('5', $records[4]);
1642
1643 // Note: delegate limits testing to test_get_records_sql().
a3d5830a
PS		 1644 }
1645
1646 public function test_get_records_sql_menu() {
1647 $DB = $this->tdb;
1648 $dbman = $DB->get_manager();
1649
1650 $table = $this->get_test_table();
1651 $tablename = $table->getName();
1652
9d6d32b6
PS		 1653 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1654 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1655 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1656 $dbman->create_table($table);
1657
1658 $DB->insert_record($tablename, array('course' => 3));
1659 $DB->insert_record($tablename, array('course' => 2));
1660 $DB->insert_record($tablename, array('course' => 3));
1661 $DB->insert_record($tablename, array('course' => 5));
1662
1663 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
9d6d32b6
PS		 1664 $this->assertInternalType('array', $records);
1665
1666 $this->assertCount(3, $records);
1667 $this->assertArrayHasKey(1, $records);
1668 $this->assertArrayNotHasKey(2, $records);
1669 $this->assertArrayHasKey(3, $records);
1670 $this->assertArrayHasKey(4, $records);
1671 $this->assertSame('3', $records[1]);
1672 $this->assertSame('3', $records[3]);
1673 $this->assertSame('5', $records[4]);
1674
1675 // Note: delegate limits testing to test_get_records_sql().
a3d5830a
PS		 1676 }
1677
1678 public function test_get_record() {
1679 $DB = $this->tdb;
1680 $dbman = $DB->get_manager();
1681
1682 $table = $this->get_test_table();
1683 $tablename = $table->getName();
1684
9d6d32b6
PS		 1685 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1686 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1687 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1688 $dbman->create_table($table);
1689
1690 $DB->insert_record($tablename, array('course' => 3));
1691 $DB->insert_record($tablename, array('course' => 2));
1692
1693 $record = $DB->get_record($tablename, array('id' => 2));
9d6d32b6 1694 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1695
1696 $this->assertEquals(2, $record->course);
1697 $this->assertEquals(2, $record->id);
1698 }
1699
1700
1701 public function test_get_record_select() {
1702 $DB = $this->tdb;
1703 $dbman = $DB->get_manager();
1704
1705 $table = $this->get_test_table();
1706 $tablename = $table->getName();
1707
9d6d32b6
PS		 1708 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1709 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1710 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1711 $dbman->create_table($table);
1712
1713 $DB->insert_record($tablename, array('course' => 3));
1714 $DB->insert_record($tablename, array('course' => 2));
1715
1716 $record = $DB->get_record_select($tablename, "id = ?", array(2));
9d6d32b6 1717 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1718
1719 $this->assertEquals(2, $record->course);
1720
9d6d32b6 1721 // Note: delegates limit testing to test_get_records_sql().
a3d5830a
PS		 1722 }
1723
1724 public function test_get_record_sql() {
1725 $DB = $this->tdb;
1726 $dbman = $DB->get_manager();
1727
1728 $table = $this->get_test_table();
1729 $tablename = $table->getName();
1730
9d6d32b6
PS		 1731 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1732 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1733 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1734 $dbman->create_table($table);
1735
1736 $DB->insert_record($tablename, array('course' => 3));
1737 $DB->insert_record($tablename, array('course' => 2));
1738
9d6d32b6 1739 // Standard use.
a3d5830a 1740 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
9d6d32b6 1741 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1742 $this->assertEquals(2, $record->course);
1743 $this->assertEquals(2, $record->id);
1744
9d6d32b6 1745 // Backwards compatibility with $ignoremultiple.
a3d5830a
PS		 1746 $this->assertFalse((bool)IGNORE_MISSING);
1747 $this->assertTrue((bool)IGNORE_MULTIPLE);
1748
9d6d32b6 1749 // Record not found - ignore.
a3d5830a
PS		 1750 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1751 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1752
9d6d32b6 1753 // Record not found error.
a3d5830a
PS		 1754 try {
1755 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
1756 $this->fail("Exception expected");
1757 } catch (dml_missing_record_exception $e) {
1758 $this->assertTrue(true);
1759 }
1760
a3d5830a 1761 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
eb10d554 1762 $this->assertDebuggingCalled();
96f81ea3 1763 set_debugging(DEBUG_MINIMAL);
eb10d554
PS		 1764 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1765 $this->assertDebuggingNotCalled();
96f81ea3 1766 set_debugging(DEBUG_DEVELOPER);
a3d5830a 1767
9d6d32b6 1768 // Multiple matches ignored.
a3d5830a
PS		 1769 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1770
9d6d32b6 1771 // Multiple found error.
a3d5830a
PS		 1772 try {
1773 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
1774 $this->fail("Exception expected");
1775 } catch (dml_multiple_records_exception $e) {
1776 $this->assertTrue(true);
1777 }
1778 }
1779
1780 public function test_get_field() {
1781 $DB = $this->tdb;
1782 $dbman = $DB->get_manager();
1783
1784 $table = $this->get_test_table();
1785 $tablename = $table->getName();
1786
9d6d32b6
PS		 1787 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1788 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1789 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1791 $dbman->create_table($table);
1792
1793 $id1 = $DB->insert_record($tablename, array('course' => 3));
1794 $DB->insert_record($tablename, array('course' => 5));
1795 $DB->insert_record($tablename, array('course' => 5));
1796
1797 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
1798 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3)));
1799
9d6d32b6 1800 $this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
a3d5830a
PS		 1801 try {
1802 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
17222a4a 1803 $this->fail('Exception expected due to missing record');
a3d5830a
PS		 1804 } catch (dml_exception $ex) {
1805 $this->assertTrue(true);
1806 }
1807
a3d5830a 1808 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
eb10d554 1809 $this->assertDebuggingNotCalled();
a3d5830a 1810
a3d5830a 1811 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
eb10d554 1812 $this->assertDebuggingCalled();
a3d5830a 1813
9d6d32b6 1814 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 1815 $conditions = array('onetext' => '1');
1816 try {
1817 $DB->get_field($tablename, 'course', $conditions);
1818 if (debugging()) {
9d6d32b6 1819 // Only in debug mode - hopefully all devs test code in debug mode...
a3d5830a
PS		 1820 $this->fail('An Exception is missing, expected due to equating of text fields');
1821 }
9d6d32b6
PS		 1822 } catch (moodle_exception $e) {
1823 $this->assertInstanceOf('dml_exception', $e);
1824 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 1825 }
1826 }
1827
1828 public function test_get_field_select() {
1829 $DB = $this->tdb;
1830 $dbman = $DB->get_manager();
1831
1832 $table = $this->get_test_table();
1833 $tablename = $table->getName();
1834
9d6d32b6
PS		 1835 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1836 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1837 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1838 $dbman->create_table($table);
1839
1840 $DB->insert_record($tablename, array('course' => 3));
1841
1842 $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
1843 }
1844
1845 public function test_get_field_sql() {
1846 $DB = $this->tdb;
1847 $dbman = $DB->get_manager();
1848
1849 $table = $this->get_test_table();
1850 $tablename = $table->getName();
1851
9d6d32b6
PS		 1852 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1853 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1854 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1855 $dbman->create_table($table);
1856
1857 $DB->insert_record($tablename, array('course' => 3));
1858
1859 $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
1860 }
1861
1862 public function test_get_fieldset_select() {
1863 $DB = $this->tdb;
1864 $dbman = $DB->get_manager();
1865
1866 $table = $this->get_test_table();
1867 $tablename = $table->getName();
1868
9d6d32b6
PS		 1869 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1870 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1871 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1872 $dbman->create_table($table);
1873
1874 $DB->insert_record($tablename, array('course' => 1));
1875 $DB->insert_record($tablename, array('course' => 3));
1876 $DB->insert_record($tablename, array('course' => 2));
1877 $DB->insert_record($tablename, array('course' => 6));
1878
1879 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
9d6d32b6 1880 $this->assertInternalType('array', $fieldset);
a3d5830a 1881
9d6d32b6 1882 $this->assertCount(3, $fieldset);
a3d5830a
PS		 1883 $this->assertEquals(3, $fieldset[0]);
1884 $this->assertEquals(2, $fieldset[1]);
1885 $this->assertEquals(6, $fieldset[2]);
1886 }
1887
1888 public function test_get_fieldset_sql() {
1889 $DB = $this->tdb;
1890 $dbman = $DB->get_manager();
1891
1892 $table = $this->get_test_table();
1893 $tablename = $table->getName();
1894
9d6d32b6
PS		 1895 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1896 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1897 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1898 $dbman->create_table($table);
1899
1900 $DB->insert_record($tablename, array('course' => 1));
1901 $DB->insert_record($tablename, array('course' => 3));
1902 $DB->insert_record($tablename, array('course' => 2));
1903 $DB->insert_record($tablename, array('course' => 6));
1904
1905 $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
9d6d32b6 1906 $this->assertInternalType('array', $fieldset);
a3d5830a 1907
9d6d32b6 1908 $this->assertCount(3, $fieldset);
a3d5830a
PS		 1909 $this->assertEquals(2, $fieldset[0]);
1910 $this->assertEquals(3, $fieldset[1]);
1911 $this->assertEquals(4, $fieldset[2]);
1912 }
1913
1914 public function test_insert_record_raw() {
1915 $DB = $this->tdb;
1916 $dbman = $DB->get_manager();
1917
1918 $table = $this->get_test_table();
1919 $tablename = $table->getName();
1920
9d6d32b6
PS		 1921 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1922 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 1923 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1924 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1925 $dbman->create_table($table);
1926
1927 $record = (object)array('course' => 1, 'onechar' => 'xx');
1928 $before = clone($record);
1929 $result = $DB->insert_record_raw($tablename, $record);
1930 $this->assertSame(1, $result);
1931 $this->assertEquals($record, $before);
1932
1933 $record = $DB->get_record($tablename, array('course' => 1));
9d6d32b6 1934 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1935 $this->assertSame('xx', $record->onechar);
1936
1937 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
9d6d32b6 1938 $this->assertTrue($result);
a3d5830a 1939
9d6d32b6 1940 // Note: bulk not implemented yet.
a3d5830a
PS		 1941 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
1942 $record = $DB->get_record($tablename, array('course' => 3));
9d6d32b6 1943 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1944 $this->assertSame('zz', $record->onechar);
1945
9d6d32b6 1946 // Custom sequence (id) - returnid is ignored.
a3d5830a 1947 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
9d6d32b6 1948 $this->assertTrue($result);
a3d5830a 1949 $record = $DB->get_record($tablename, array('id' => 10));
9d6d32b6 1950 $this->assertInstanceOf('stdClass', $record);
a3d5830a
PS		 1951 $this->assertSame('bb', $record->onechar);
1952
9d6d32b6 1953 // Custom sequence - missing id error.
a3d5830a
PS		 1954 try {
1955 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
17222a4a 1956 $this->fail('Exception expected due to missing record');
a3d5830a
PS		 1957 } catch (coding_exception $ex) {
1958 $this->assertTrue(true);
1959 }
1960
9d6d32b6 1961 // Wrong column error.
a3d5830a
PS		 1962 try {
1963 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
17222a4a 1964 $this->fail('Exception expected due to invalid column');
a3d5830a
PS		 1965 } catch (dml_exception $ex) {
1966 $this->assertTrue(true);
1967 }
3b4f7338 1968
9d6d32b6 1969 // Create something similar to "context_temp" with id column without sequence.
3b4f7338
PS		 1970 $dbman->drop_table($table);
1971 $table = $this->get_test_table();
1972 $tablename = $table->getName();
9d6d32b6
PS		 1973 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1974 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3b4f7338
PS		 1975 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1976 $dbman->create_table($table);
1977
1978 $record = (object)array('id'=>5, 'course' => 1);
1979 $DB->insert_record_raw($tablename, $record, false, false, true);
1980 $record = $DB->get_record($tablename, array());
1981 $this->assertEquals(5, $record->id);
a3d5830a
PS		 1982 }
1983
1984 public function test_insert_record() {
1985 // All the information in this test is fetched from DB by get_recordset() so we
1986 // have such method properly tested against nulls, empties and friends...
1987
1988 $DB = $this->tdb;
1989 $dbman = $DB->get_manager();
1990
1991 $table = $this->get_test_table();
1992 $tablename = $table->getName();
1993
9d6d32b6
PS		 1994 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1995 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1996 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
a3d5830a
PS		 1997 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1998 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1999 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2000 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2001 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2002 $dbman->create_table($table);
2003
2004 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2005 $record = $DB->get_record($tablename, array('course' => 1));
2006 $this->assertEquals(1, $record->id);
9d6d32b6 2007 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
a3d5830a
PS		 2008 $this->assertEquals(200, $record->onenum);
2009 $this->assertSame('onestring', $record->onechar);
2010 $this->assertNull($record->onetext);
2011 $this->assertNull($record->onebinary);
2012
9d6d32b6
PS		 2013 // Without returning id, bulk not implemented.
2014 $result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true));
a3d5830a
PS		 2015 $record = $DB->get_record($tablename, array('course' => 99));
2016 $this->assertEquals(2, $record->id);
2017 $this->assertEquals(99, $record->course);
2018
9d6d32b6 2019 // Check nulls are set properly for all types.
a3d5830a
PS		 2020 $record = new stdClass();
2021 $record->oneint = null;
2022 $record->onenum = null;
2023 $record->onechar = null;
2024 $record->onetext = null;
2025 $record->onebinary = null;
2026 $recid = $DB->insert_record($tablename, $record);
2027 $record = $DB->get_record($tablename, array('id' => $recid));
2028 $this->assertEquals(0, $record->course);
2029 $this->assertNull($record->oneint);
2030 $this->assertNull($record->onenum);
2031 $this->assertNull($record->onechar);
2032 $this->assertNull($record->onetext);
2033 $this->assertNull($record->onebinary);
2034
9d6d32b6 2035 // Check zeros are set properly for all types.
a3d5830a
PS		 2036 $record = new stdClass();
2037 $record->oneint = 0;
2038 $record->onenum = 0;
2039 $recid = $DB->insert_record($tablename, $record);
2040 $record = $DB->get_record($tablename, array('id' => $recid));
2041 $this->assertEquals(0, $record->oneint);
2042 $this->assertEquals(0, $record->onenum);
2043
9d6d32b6 2044 // Check booleans are set properly for all types.
a3d5830a 2045 $record = new stdClass();
9d6d32b6 2046 $record->oneint = true; // Trues.
a3d5830a
PS		 2047 $record->onenum = true;
2048 $record->onechar = true;
2049 $record->onetext = true;
2050 $recid = $DB->insert_record($tablename, $record);
2051 $record = $DB->get_record($tablename, array('id' => $recid));
2052 $this->assertEquals(1, $record->oneint);
2053 $this->assertEquals(1, $record->onenum);
2054 $this->assertEquals(1, $record->onechar);
2055 $this->assertEquals(1, $record->onetext);
2056
2057 $record = new stdClass();
9d6d32b6 2058 $record->oneint = false; // Falses.
a3d5830a
PS		 2059 $record->onenum = false;
2060 $record->onechar = false;
2061 $record->onetext = false;
2062 $recid = $DB->insert_record($tablename, $record);
2063 $record = $DB->get_record($tablename, array('id' => $recid));
2064 $this->assertEquals(0, $record->oneint);
2065 $this->assertEquals(0, $record->onenum);
2066 $this->assertEquals(0, $record->onechar);
2067 $this->assertEquals(0, $record->onetext);
2068
9d6d32b6 2069 // Check string data causes exception in numeric types.
a3d5830a
PS		 2070 $record = new stdClass();
2071 $record->oneint = 'onestring';
2072 $record->onenum = 0;
2073 try {
2074 $DB->insert_record($tablename, $record);
2075 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2076 } catch (moodle_exception $e) {
2077 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2078 }
2079 $record = new stdClass();
2080 $record->oneint = 0;
2081 $record->onenum = 'onestring';
2082 try {
2083 $DB->insert_record($tablename, $record);
2084 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2085 } catch (moodle_exception $e) {
2086 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2087 }
2088
9d6d32b6 2089 // Check empty string data is stored as 0 in numeric datatypes.
a3d5830a 2090 $record = new stdClass();
9d6d32b6 2091 $record->oneint = ''; // Empty string.
a3d5830a
PS		 2092 $record->onenum = 0;
2093 $recid = $DB->insert_record($tablename, $record);
2094 $record = $DB->get_record($tablename, array('id' => $recid));
2095 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2096
2097 $record = new stdClass();
2098 $record->oneint = 0;
9d6d32b6 2099 $record->onenum = ''; // Empty string.
a3d5830a
PS		 2100 $recid = $DB->insert_record($tablename, $record);
2101 $record = $DB->get_record($tablename, array('id' => $recid));
2102 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2103
9d6d32b6 2104 // Check empty strings are set properly in string types.
a3d5830a
PS		 2105 $record = new stdClass();
2106 $record->oneint = 0;
2107 $record->onenum = 0;
2108 $record->onechar = '';
2109 $record->onetext = '';
2110 $recid = $DB->insert_record($tablename, $record);
2111 $record = $DB->get_record($tablename, array('id' => $recid));
2112 $this->assertTrue($record->onechar === '');
2113 $this->assertTrue($record->onetext === '');
2114
9d6d32b6 2115 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
a3d5830a
PS		 2116 $record = new stdClass();
2117 $record->oneint = ((210.10 + 39.92) - 150.02);
2118 $record->onenum = ((210.10 + 39.92) - 150.02);
2119 $recid = $DB->insert_record($tablename, $record);
2120 $record = $DB->get_record($tablename, array('id' => $recid));
2121 $this->assertEquals(100, $record->oneint);
2122 $this->assertEquals(100, $record->onenum);
2123
9d6d32b6 2124 // Check various quotes/backslashes combinations in string types.
a3d5830a
PS		 2125 $teststrings = array(
2126 'backslashes and quotes alone (even): "" \'\' \\\\',
2127 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2128 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2129 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2130 foreach ($teststrings as $teststring) {
2131 $record = new stdClass();
2132 $record->onechar = $teststring;
2133 $record->onetext = $teststring;
2134 $recid = $DB->insert_record($tablename, $record);
2135 $record = $DB->get_record($tablename, array('id' => $recid));
2136 $this->assertEquals($teststring, $record->onechar);
2137 $this->assertEquals($teststring, $record->onetext);
2138 }
2139
9d6d32b6 2140 // Check LOBs in text/binary columns.
a3d5830a
PS		 2141 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2142 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2143 $record = new stdClass();
2144 $record->onetext = $clob;
2145 $record->onebinary = $blob;
2146 $recid = $DB->insert_record($tablename, $record);
2147 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2148 $record = $rs->current();
2149 $rs->close();
2150 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2151 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2152
9d6d32b6 2153 // And "small" LOBs too, just in case.
a3d5830a
PS		 2154 $newclob = substr($clob, 0, 500);
2155 $newblob = substr($blob, 0, 250);
2156 $record = new stdClass();
2157 $record->onetext = $newclob;
2158 $record->onebinary = $newblob;
2159 $recid = $DB->insert_record($tablename, $record);
2160 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2161 $record = $rs->current();
2162 $rs->close();
2163 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2164 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
9d6d32b6 2165 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
a3d5830a 2166
9d6d32b6 2167 // And "diagnostic" LOBs too, just in case.
a3d5830a
PS		 2168 $newclob = '\'"\\;/ěščřžýáíé';
2169 $newblob = '\'"\\;/ěščřžýáíé';
2170 $record = new stdClass();
2171 $record->onetext = $newclob;
2172 $record->onebinary = $newblob;
2173 $recid = $DB->insert_record($tablename, $record);
2174 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2175 $record = $rs->current();
2176 $rs->close();
2177 $this->assertSame($newclob, $record->onetext);
2178 $this->assertSame($newblob, $record->onebinary);
9d6d32b6 2179 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
a3d5830a 2180
9d6d32b6 2181 // Test data is not modified.
a3d5830a 2182 $record = new stdClass();
9d6d32b6 2183 $record->id = -1; // Has to be ignored.
a3d5830a 2184 $record->course = 3;
9d6d32b6 2185 $record->lalala = 'lalal'; // Unused.
a3d5830a
PS		 2186 $before = clone($record);
2187 $DB->insert_record($tablename, $record);
2188 $this->assertEquals($record, $before);
2189
9d6d32b6 2190 // Make sure the id is always increasing and never reuses the same id.
a3d5830a
PS		 2191 $id1 = $DB->insert_record($tablename, array('course' => 3));
2192 $id2 = $DB->insert_record($tablename, array('course' => 3));
2193 $this->assertTrue($id1 < $id2);
2194 $DB->delete_records($tablename, array('id'=>$id2));
2195 $id3 = $DB->insert_record($tablename, array('course' => 3));
2196 $this->assertTrue($id2 < $id3);
2197 $DB->delete_records($tablename, array());
2198 $id4 = $DB->insert_record($tablename, array('course' => 3));
2199 $this->assertTrue($id3 < $id4);
2200
2201 // Test saving a float in a CHAR column, and reading it back.
2202 $id = $DB->insert_record($tablename, array('onechar' => 1.0));
2203 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2204 $id = $DB->insert_record($tablename, array('onechar' => 1e20));
2205 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2206 $id = $DB->insert_record($tablename, array('onechar' => 1e-4));
2207 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2208 $id = $DB->insert_record($tablename, array('onechar' => 1e-5));
2209 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2210 $id = $DB->insert_record($tablename, array('onechar' => 1e-300));
2211 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2212 $id = $DB->insert_record($tablename, array('onechar' => 1e300));
2213 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2214
2215 // Test saving a float in a TEXT column, and reading it back.
2216 $id = $DB->insert_record($tablename, array('onetext' => 1.0));
2217 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2218 $id = $DB->insert_record($tablename, array('onetext' => 1e20));
2219 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2220 $id = $DB->insert_record($tablename, array('onetext' => 1e-4));
2221 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2222 $id = $DB->insert_record($tablename, array('onetext' => 1e-5));
2223 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2224 $id = $DB->insert_record($tablename, array('onetext' => 1e-300));
2225 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2226 $id = $DB->insert_record($tablename, array('onetext' => 1e300));
2227 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
cbdcdd47
EL		 2228
2229 // Test that inserting data violating one unique key leads to error.
2230 // Empty the table completely.
2231 $this->assertTrue($DB->delete_records($tablename));
2232
2233 // Add one unique constraint (index).
2234 $key = new xmldb_key('testuk', XMLDB_KEY_UNIQUE, array('course', 'oneint'));
2235 $dbman->add_key($table, $key);
2236
2237 // Let's insert one record violating the constraint multiple times.
2238 $record = (object)array('course' => 1, 'oneint' => 1);
9d6d32b6 2239 $this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected.
cbdcdd47
EL		 2240
2241 // Re-insert same record, not returning id. dml_exception expected.
2242 try {
2243 $DB->insert_record($tablename, $record, false);
2244 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2245 } catch (moodle_exception $e) {
2246 $this->assertInstanceOf('dml_exception', $e);
cbdcdd47
EL		 2247 }
2248
2249 // Re-insert same record, returning id. dml_exception expected.
2250 try {
2251 $DB->insert_record($tablename, $record, true);
2252 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2253 } catch (moodle_exception $e) {
2254 $this->assertInstanceOf('dml_exception', $e);
cbdcdd47 2255 }
a3d5830a
PS		 2256 }
2257
2258 public function test_import_record() {
2259 // All the information in this test is fetched from DB by get_recordset() so we
2260 // have such method properly tested against nulls, empties and friends...
2261
2262 $DB = $this->tdb;
2263 $dbman = $DB->get_manager();
2264
2265 $table = $this->get_test_table();
2266 $tablename = $table->getName();
2267
9d6d32b6
PS		 2268 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2269 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2270 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
a3d5830a
PS		 2271 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2272 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2273 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2274 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2275 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2276 $dbman->create_table($table);
2277
2278 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2279 $record = $DB->get_record($tablename, array('course' => 1));
2280 $this->assertEquals(1, $record->id);
9d6d32b6 2281 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
a3d5830a
PS		 2282 $this->assertEquals(200, $record->onenum);
2283 $this->assertSame('onestring', $record->onechar);
2284 $this->assertNull($record->onetext);
2285 $this->assertNull($record->onebinary);
2286
9d6d32b6 2287 // Ignore extra columns.
a3d5830a
PS		 2288 $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);
2289 $before = clone($record);
9d6d32b6 2290 $this->assertTrue($DB->import_record($tablename, $record));
a3d5830a
PS		 2291 $this->assertEquals($record, $before);
2292 $records = $DB->get_records($tablename);
2293 $this->assertEquals(2, $records[13]->course);
2294
9d6d32b6 2295 // Check nulls are set properly for all types.
a3d5830a
PS		 2296 $record = new stdClass();
2297 $record->id = 20;
2298 $record->oneint = null;
2299 $record->onenum = null;
2300 $record->onechar = null;
2301 $record->onetext = null;
2302 $record->onebinary = null;
2303 $this->assertTrue($DB->import_record($tablename, $record));
2304 $record = $DB->get_record($tablename, array('id' => 20));
2305 $this->assertEquals(0, $record->course);
2306 $this->assertNull($record->oneint);
2307 $this->assertNull($record->onenum);
2308 $this->assertNull($record->onechar);
2309 $this->assertNull($record->onetext);
2310 $this->assertNull($record->onebinary);
2311
9d6d32b6 2312 // Check zeros are set properly for all types.
a3d5830a
PS		 2313 $record = new stdClass();
2314 $record->id = 23;
2315 $record->oneint = 0;
2316 $record->onenum = 0;
2317 $this->assertTrue($DB->import_record($tablename, $record));
2318 $record = $DB->get_record($tablename, array('id' => 23));
2319 $this->assertEquals(0, $record->oneint);
2320 $this->assertEquals(0, $record->onenum);
2321
9d6d32b6 2322 // Check string data causes exception in numeric types.
a3d5830a
PS		 2323 $record = new stdClass();
2324 $record->id = 32;
2325 $record->oneint = 'onestring';
2326 $record->onenum = 0;
2327 try {
2328 $DB->import_record($tablename, $record);
2329 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2330 } catch (moodle_exception $e) {
2331 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2332 }
2333 $record = new stdClass();
2334 $record->id = 35;
2335 $record->oneint = 0;
2336 $record->onenum = 'onestring';
2337 try {
2338 $DB->import_record($tablename, $record);
2339 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2340 } catch (moodle_exception $e) {
2341 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2342 }
2343
9d6d32b6 2344 // Check empty strings are set properly in string types.
a3d5830a
PS		 2345 $record = new stdClass();
2346 $record->id = 44;
2347 $record->oneint = 0;
2348 $record->onenum = 0;
2349 $record->onechar = '';
2350 $record->onetext = '';
2351 $this->assertTrue($DB->import_record($tablename, $record));
2352 $record = $DB->get_record($tablename, array('id' => 44));
2353 $this->assertTrue($record->onechar === '');
2354 $this->assertTrue($record->onetext === '');
2355
9d6d32b6 2356 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
a3d5830a
PS		 2357 $record = new stdClass();
2358 $record->id = 47;
2359 $record->oneint = ((210.10 + 39.92) - 150.02);
2360 $record->onenum = ((210.10 + 39.92) - 150.02);
2361 $this->assertTrue($DB->import_record($tablename, $record));
2362 $record = $DB->get_record($tablename, array('id' => 47));
2363 $this->assertEquals(100, $record->oneint);
2364 $this->assertEquals(100, $record->onenum);
2365
9d6d32b6 2366 // Check various quotes/backslashes combinations in string types.
a3d5830a
PS		 2367 $i = 50;
2368 $teststrings = array(
2369 'backslashes and quotes alone (even): "" \'\' \\\\',
2370 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2371 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2372 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2373 foreach ($teststrings as $teststring) {
2374 $record = new stdClass();
2375 $record->id = $i;
2376 $record->onechar = $teststring;
2377 $record->onetext = $teststring;
2378 $this->assertTrue($DB->import_record($tablename, $record));
2379 $record = $DB->get_record($tablename, array('id' => $i));
2380 $this->assertEquals($teststring, $record->onechar);
2381 $this->assertEquals($teststring, $record->onetext);
2382 $i = $i + 3;
2383 }
2384
9d6d32b6 2385 // Check LOBs in text/binary columns.
a3d5830a
PS		 2386 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2387 $record = new stdClass();
2388 $record->id = 70;
2389 $record->onetext = $clob;
2390 $record->onebinary = '';
2391 $this->assertTrue($DB->import_record($tablename, $record));
2392 $rs = $DB->get_recordset($tablename, array('id' => 70));
2393 $record = $rs->current();
2394 $rs->close();
2395 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2396
2397 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2398 $record = new stdClass();
2399 $record->id = 71;
2400 $record->onetext = '';
2401 $record->onebinary = $blob;
2402 $this->assertTrue($DB->import_record($tablename, $record));
2403 $rs = $DB->get_recordset($tablename, array('id' => 71));
2404 $record = $rs->current();
2405 $rs->close();
2406 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2407
9d6d32b6 2408 // And "small" LOBs too, just in case.
a3d5830a
PS		 2409 $newclob = substr($clob, 0, 500);
2410 $newblob = substr($blob, 0, 250);
2411 $record = new stdClass();
2412 $record->id = 73;
2413 $record->onetext = $newclob;
2414 $record->onebinary = $newblob;
2415 $this->assertTrue($DB->import_record($tablename, $record));
2416 $rs = $DB->get_recordset($tablename, array('id' => 73));
2417 $record = $rs->current();
2418 $rs->close();
2419 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2420 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
9d6d32b6 2421 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
a3d5830a
PS		 2422 }
2423
2424 public function test_update_record_raw() {
2425 $DB = $this->tdb;
2426 $dbman = $DB->get_manager();
2427
2428 $table = $this->get_test_table();
2429 $tablename = $table->getName();
2430
9d6d32b6
PS		 2431 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2432 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 2433 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2434 $dbman->create_table($table);
2435
2436 $DB->insert_record($tablename, array('course' => 1));
2437 $DB->insert_record($tablename, array('course' => 3));
2438
2439 $record = $DB->get_record($tablename, array('course' => 1));
2440 $record->course = 2;
2441 $this->assertTrue($DB->update_record_raw($tablename, $record));
2442 $this->assertEquals(0, $DB->count_records($tablename, array('course' => 1)));
2443 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 2)));
2444 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 3)));
2445
2446 $record = $DB->get_record($tablename, array('course' => 3));
2447 $record->xxxxx = 2;
2448 try {
2449 $DB->update_record_raw($tablename, $record);
2450 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2451 } catch (moodle_exception $e) {
2452 $this->assertInstanceOf('moodle_exception', $e);
a3d5830a
PS		 2453 }
2454
2455 $record = $DB->get_record($tablename, array('course' => 3));
2456 unset($record->id);
2457 try {
2458 $DB->update_record_raw($tablename, $record);
2459 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2460 } catch (moodle_exception $e) {
2461 $this->assertInstanceOf('coding_exception', $e);
a3d5830a
PS		 2462 }
2463 }
2464
2465 public function test_update_record() {
2466
2467 // All the information in this test is fetched from DB by get_record() so we
2468 // have such method properly tested against nulls, empties and friends...
2469
2470 $DB = $this->tdb;
2471 $dbman = $DB->get_manager();
2472
2473 $table = $this->get_test_table();
2474 $tablename = $table->getName();
2475
9d6d32b6
PS		 2476 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2477 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2478 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
a3d5830a
PS		 2479 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2480 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2481 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2482 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2483 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2484 $dbman->create_table($table);
2485
2486 $DB->insert_record($tablename, array('course' => 1));
2487 $record = $DB->get_record($tablename, array('course' => 1));
2488 $record->course = 2;
2489
2490 $this->assertTrue($DB->update_record($tablename, $record));
2491 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
2492 $this->assertNotEmpty($record = $DB->get_record($tablename, array('course' => 2)));
9d6d32b6 2493 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
a3d5830a 2494 $this->assertEquals(200, $record->onenum);
9d6d32b6 2495 $this->assertSame('onestring', $record->onechar);
a3d5830a
PS		 2496 $this->assertNull($record->onetext);
2497 $this->assertNull($record->onebinary);
2498
9d6d32b6 2499 // Check nulls are set properly for all types.
a3d5830a
PS		 2500 $record->oneint = null;
2501 $record->onenum = null;
2502 $record->onechar = null;
2503 $record->onetext = null;
2504 $record->onebinary = null;
2505 $DB->update_record($tablename, $record);
2506 $record = $DB->get_record($tablename, array('course' => 2));
2507 $this->assertNull($record->oneint);
2508 $this->assertNull($record->onenum);
2509 $this->assertNull($record->onechar);
2510 $this->assertNull($record->onetext);
2511 $this->assertNull($record->onebinary);
2512
9d6d32b6 2513 // Check zeros are set properly for all types.
a3d5830a
PS		 2514 $record->oneint = 0;
2515 $record->onenum = 0;
2516 $DB->update_record($tablename, $record);
2517 $record = $DB->get_record($tablename, array('course' => 2));
2518 $this->assertEquals(0, $record->oneint);
2519 $this->assertEquals(0, $record->onenum);
2520
9d6d32b6
PS		 2521 // Check booleans are set properly for all types.
2522 $record->oneint = true; // Trues.
a3d5830a
PS		 2523 $record->onenum = true;
2524 $record->onechar = true;
2525 $record->onetext = true;
2526 $DB->update_record($tablename, $record);
2527 $record = $DB->get_record($tablename, array('course' => 2));
2528 $this->assertEquals(1, $record->oneint);
2529 $this->assertEquals(1, $record->onenum);
2530 $this->assertEquals(1, $record->onechar);
2531 $this->assertEquals(1, $record->onetext);
2532
9d6d32b6 2533 $record->oneint = false; // Falses.
a3d5830a
PS		 2534 $record->onenum = false;
2535 $record->onechar = false;
2536 $record->onetext = false;
2537 $DB->update_record($tablename, $record);
2538 $record = $DB->get_record($tablename, array('course' => 2));
2539 $this->assertEquals(0, $record->oneint);
2540 $this->assertEquals(0, $record->onenum);
2541 $this->assertEquals(0, $record->onechar);
2542 $this->assertEquals(0, $record->onetext);
2543
9d6d32b6 2544 // Check string data causes exception in numeric types.
a3d5830a
PS		 2545 $record->oneint = 'onestring';
2546 $record->onenum = 0;
2547 try {
2548 $DB->update_record($tablename, $record);
2549 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2550 } catch (moodle_exception $e) {
2551 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2552 }
2553 $record->oneint = 0;
2554 $record->onenum = 'onestring';
2555 try {
2556 $DB->update_record($tablename, $record);
2557 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2558 } catch (moodle_exception $e) {
2559 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2560 }
2561
9d6d32b6
PS		 2562 // Check empty string data is stored as 0 in numeric datatypes.
2563 $record->oneint = ''; // Empty string.
a3d5830a
PS		 2564 $record->onenum = 0;
2565 $DB->update_record($tablename, $record);
2566 $record = $DB->get_record($tablename, array('course' => 2));
2567 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2568
2569 $record->oneint = 0;
9d6d32b6 2570 $record->onenum = ''; // Empty string.
a3d5830a
PS		 2571 $DB->update_record($tablename, $record);
2572 $record = $DB->get_record($tablename, array('course' => 2));
2573 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2574
9d6d32b6 2575 // Check empty strings are set properly in string types.
a3d5830a
PS		 2576 $record->oneint = 0;
2577 $record->onenum = 0;
2578 $record->onechar = '';
2579 $record->onetext = '';
2580 $DB->update_record($tablename, $record);
2581 $record = $DB->get_record($tablename, array('course' => 2));
2582 $this->assertTrue($record->onechar === '');
2583 $this->assertTrue($record->onetext === '');
2584
9d6d32b6 2585 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
a3d5830a
PS		 2586 $record->oneint = ((210.10 + 39.92) - 150.02);
2587 $record->onenum = ((210.10 + 39.92) - 150.02);
2588 $DB->update_record($tablename, $record);
2589 $record = $DB->get_record($tablename, array('course' => 2));
2590 $this->assertEquals(100, $record->oneint);
2591 $this->assertEquals(100, $record->onenum);
2592
9d6d32b6 2593 // Check various quotes/backslashes combinations in string types.
a3d5830a
PS		 2594 $teststrings = array(
2595 'backslashes and quotes alone (even): "" \'\' \\\\',
2596 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2597 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2598 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2599 foreach ($teststrings as $teststring) {
2600 $record->onechar = $teststring;
2601 $record->onetext = $teststring;
2602 $DB->update_record($tablename, $record);
2603 $record = $DB->get_record($tablename, array('course' => 2));
2604 $this->assertEquals($teststring, $record->onechar);
2605 $this->assertEquals($teststring, $record->onetext);
2606 }
2607
9d6d32b6 2608 // Check LOBs in text/binary columns.
a3d5830a
PS		 2609 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2610 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2611 $record->onetext = $clob;
2612 $record->onebinary = $blob;
2613 $DB->update_record($tablename, $record);
2614 $record = $DB->get_record($tablename, array('course' => 2));
2615 $this->assertEquals($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
2616 $this->assertEquals($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
2617
9d6d32b6 2618 // And "small" LOBs too, just in case.
a3d5830a
PS		 2619 $newclob = substr($clob, 0, 500);
2620 $newblob = substr($blob, 0, 250);
2621 $record->onetext = $newclob;
2622 $record->onebinary = $newblob;
2623 $DB->update_record($tablename, $record);
2624 $record = $DB->get_record($tablename, array('course' => 2));
2625 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
2626 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
2627
2628 // Test saving a float in a CHAR column, and reading it back.
2629 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2630 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1.0));
2631 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2632 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e20));
2633 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2634 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-4));
2635 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2636 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-5));
2637 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2638 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-300));
2639 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2640 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e300));
2641 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2642
2643 // Test saving a float in a TEXT column, and reading it back.
2644 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2645 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1.0));
2646 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2647 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e20));
2648 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2649 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-4));
2650 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2651 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-5));
2652 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2653 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-300));
2654 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2655 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e300));
2656 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2657 }
2658
2659 public function test_set_field() {
2660 $DB = $this->tdb;
2661 $dbman = $DB->get_manager();
2662
2663 $table = $this->get_test_table();
2664 $tablename = $table->getName();
2665
9d6d32b6
PS		 2666 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2667 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 2668 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2669 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2670 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2671 $dbman->create_table($table);
2672
9d6d32b6 2673 // Simple set_field.
a3d5830a
PS		 2674 $id1 = $DB->insert_record($tablename, array('course' => 1));
2675 $id2 = $DB->insert_record($tablename, array('course' => 1));
2676 $id3 = $DB->insert_record($tablename, array('course' => 3));
2677 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1)));
2678 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => $id1)));
2679 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2680 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2681 $DB->delete_records($tablename, array());
2682
9d6d32b6 2683 // Multiple fields affected.
a3d5830a
PS		 2684 $id1 = $DB->insert_record($tablename, array('course' => 1));
2685 $id2 = $DB->insert_record($tablename, array('course' => 1));
2686 $id3 = $DB->insert_record($tablename, array('course' => 3));
2687 $DB->set_field($tablename, 'course', '5', array('course' => 1));
2688 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2689 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2690 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2691 $DB->delete_records($tablename, array());
2692
9d6d32b6 2693 // No field affected.
a3d5830a
PS		 2694 $id1 = $DB->insert_record($tablename, array('course' => 1));
2695 $id2 = $DB->insert_record($tablename, array('course' => 1));
2696 $id3 = $DB->insert_record($tablename, array('course' => 3));
2697 $DB->set_field($tablename, 'course', '5', array('course' => 0));
2698 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id1)));
2699 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2700 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2701 $DB->delete_records($tablename, array());
2702
9d6d32b6 2703 // All fields - no condition.
a3d5830a
PS		 2704 $id1 = $DB->insert_record($tablename, array('course' => 1));
2705 $id2 = $DB->insert_record($tablename, array('course' => 1));
2706 $id3 = $DB->insert_record($tablename, array('course' => 3));
2707 $DB->set_field($tablename, 'course', 5, array());
2708 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2709 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2710 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id3)));
2711
9d6d32b6 2712 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 2713 $conditions = array('onetext' => '1');
2714 try {
2715 $DB->set_field($tablename, 'onechar', 'frog', $conditions);
2716 if (debugging()) {
9d6d32b6 2717 // Only in debug mode - hopefully all devs test code in debug mode...
a3d5830a
PS		 2718 $this->fail('An Exception is missing, expected due to equating of text fields');
2719 }
9d6d32b6
PS		 2720 } catch (moodle_exception $e) {
2721 $this->assertInstanceOf('dml_exception', $e);
2722 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 2723 }
2724
2725 // Test saving a float in a CHAR column, and reading it back.
2726 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2727 $DB->set_field($tablename, 'onechar', 1.0, array('id' => $id));
2728 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2729 $DB->set_field($tablename, 'onechar', 1e20, array('id' => $id));
2730 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2731 $DB->set_field($tablename, 'onechar', 1e-4, array('id' => $id));
2732 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2733 $DB->set_field($tablename, 'onechar', 1e-5, array('id' => $id));
2734 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2735 $DB->set_field($tablename, 'onechar', 1e-300, array('id' => $id));
2736 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2737 $DB->set_field($tablename, 'onechar', 1e300, array('id' => $id));
2738 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2739
2740 // Test saving a float in a TEXT column, and reading it back.
2741 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2742 $DB->set_field($tablename, 'onetext', 1.0, array('id' => $id));
2743 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2744 $DB->set_field($tablename, 'onetext', 1e20, array('id' => $id));
2745 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2746 $DB->set_field($tablename, 'onetext', 1e-4, array('id' => $id));
2747 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2748 $DB->set_field($tablename, 'onetext', 1e-5, array('id' => $id));
2749 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2750 $DB->set_field($tablename, 'onetext', 1e-300, array('id' => $id));
2751 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2752 $DB->set_field($tablename, 'onetext', 1e300, array('id' => $id));
2753 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2754
2755 // Note: All the nulls, booleans, empties, quoted and backslashes tests
9d6d32b6 2756 // go to set_field_select() because set_field() is just one wrapper over it.
a3d5830a
PS		 2757 }
2758
2759 public function test_set_field_select() {
2760
2761 // All the information in this test is fetched from DB by get_field() so we
2762 // have such method properly tested against nulls, empties and friends...
2763
2764 $DB = $this->tdb;
2765 $dbman = $DB->get_manager();
2766
2767 $table = $this->get_test_table();
2768 $tablename = $table->getName();
2769
9d6d32b6
PS		 2770 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2771 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2772 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null);
a3d5830a
PS		 2773 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
2774 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2775 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2776 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2777 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2778 $dbman->create_table($table);
2779
2780 $DB->insert_record($tablename, array('course' => 1));
2781
2782 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
2783 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => 1)));
2784
9d6d32b6
PS		 2785 // Check nulls are set properly for all types.
2786 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // Trues.
a3d5830a
PS		 2787 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
2788 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
2789 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
2790 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
2791 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
2792 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
2793 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
2794 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
2795 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
2796
9d6d32b6 2797 // Check zeros are set properly for all types.
a3d5830a
PS		 2798 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
2799 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
2800 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2801 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2802
9d6d32b6
PS		 2803 // Check booleans are set properly for all types.
2804 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // Trues.
a3d5830a
PS		 2805 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
2806 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
2807 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
2808 $this->assertEquals(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2809 $this->assertEquals(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2810 $this->assertEquals(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2811 $this->assertEquals(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2812
9d6d32b6 2813 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // Falses.
a3d5830a
PS		 2814 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
2815 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
2816 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
2817 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2818 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2819 $this->assertEquals(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2820 $this->assertEquals(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2821
9d6d32b6 2822 // Check string data causes exception in numeric types.
a3d5830a
PS		 2823 try {
2824 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
2825 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2826 } catch (moodle_exception $e) {
2827 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2828 }
2829 try {
2830 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
2831 $this->fail("Expecting an exception, none occurred");
9d6d32b6
PS		 2832 } catch (moodle_exception $e) {
2833 $this->assertInstanceOf('dml_exception', $e);
a3d5830a
PS		 2834 }
2835
9d6d32b6 2836 // Check empty string data is stored as 0 in numeric datatypes.
a3d5830a
PS		 2837 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
2838 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
2839 $this->assertTrue(is_numeric($field) && $field == 0);
2840
2841 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
2842 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
2843 $this->assertTrue(is_numeric($field) && $field == 0);
2844
9d6d32b6 2845 // Check empty strings are set properly in string types.
a3d5830a
PS		 2846 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
2847 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
2848 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
2849 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
2850
9d6d32b6 2851 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
a3d5830a
PS		 2852 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2853 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2854 $this->assertEquals(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2855 $this->assertEquals(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2856
9d6d32b6 2857 // Check various quotes/backslashes combinations in string types.
a3d5830a
PS		 2858 $teststrings = array(
2859 'backslashes and quotes alone (even): "" \'\' \\\\',
2860 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2861 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2862 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2863 foreach ($teststrings as $teststring) {
2864 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
2865 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
2866 $this->assertEquals($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2867 $this->assertEquals($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2868 }
2869
9d6d32b6 2870 // Check LOBs in text/binary columns.
a3d5830a
PS		 2871 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2872 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2873 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
2874 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
2875 $this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
2876 $this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
2877
9d6d32b6 2878 // And "small" LOBs too, just in case.
a3d5830a
PS		 2879 $newclob = substr($clob, 0, 500);
2880 $newblob = substr($blob, 0, 250);
2881 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
2882 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
2883 $this->assertEquals($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
2884 $this->assertEquals($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
2885
2886 // This is the failure from MDL-24863. This was giving an error on MSSQL,
2887 // which converts the '1' to an integer, which cannot then be compared with
2888 // onetext cast to a varchar. This should be fixed and working now.
2889 $newchar = 'frog';
9d6d32b6 2890 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 2891 $params = array('onetext' => '1');
2892 try {
2893 $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);
2894 $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.');
2895 } catch (dml_exception $e) {
2896 $this->assertFalse(true, 'We have an unexpected exception.');
2897 throw $e;
2898 }
a3d5830a
PS		 2899 }
2900
2901 public function test_count_records() {
2902 $DB = $this->tdb;
2903
2904 $dbman = $DB->get_manager();
2905
2906 $table = $this->get_test_table();
2907 $tablename = $table->getName();
2908
9d6d32b6
PS		 2909 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2910 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 2911 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2912 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2913 $dbman->create_table($table);
2914
63224dee 2915 $this->assertSame(0, $DB->count_records($tablename));
a3d5830a
PS		 2916
2917 $DB->insert_record($tablename, array('course' => 3));
2918 $DB->insert_record($tablename, array('course' => 4));
2919 $DB->insert_record($tablename, array('course' => 5));
2920
63224dee 2921 $this->assertSame(3, $DB->count_records($tablename));
a3d5830a 2922
9d6d32b6 2923 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 2924 $conditions = array('onetext' => '1');
2925 try {
2926 $DB->count_records($tablename, $conditions);
2927 if (debugging()) {
9d6d32b6 2928 // Only in debug mode - hopefully all devs test code in debug mode...
a3d5830a
PS		 2929 $this->fail('An Exception is missing, expected due to equating of text fields');
2930 }
9d6d32b6
PS		 2931 } catch (moodle_exception $e) {
2932 $this->assertInstanceOf('dml_exception', $e);
2933 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 2934 }
2935 }
2936
2937 public function test_count_records_select() {
2938 $DB = $this->tdb;
2939
2940 $dbman = $DB->get_manager();
2941
2942 $table = $this->get_test_table();
2943 $tablename = $table->getName();
2944
9d6d32b6
PS		 2945 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2946 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 2947 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2948 $dbman->create_table($table);
2949
63224dee 2950 $this->assertSame(0, $DB->count_records($tablename));
a3d5830a
PS		 2951
2952 $DB->insert_record($tablename, array('course' => 3));
2953 $DB->insert_record($tablename, array('course' => 4));
2954 $DB->insert_record($tablename, array('course' => 5));
2955
63224dee 2956 $this->assertSame(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
a3d5830a
PS		 2957 }
2958
2959 public function test_count_records_sql() {
2960 $DB = $this->tdb;
2961 $dbman = $DB->get_manager();
2962
2963 $table = $this->get_test_table();
2964 $tablename = $table->getName();
2965
9d6d32b6
PS		 2966 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2967 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
63224dee 2968 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
a3d5830a
PS		 2969 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2970 $dbman->create_table($table);
2971
63224dee 2972 $this->assertSame(0, $DB->count_records($tablename));
a3d5830a 2973
63224dee
PS		 2974 $DB->insert_record($tablename, array('course' => 3, 'onechar' => 'a'));
2975 $DB->insert_record($tablename, array('course' => 4, 'onechar' => 'b'));
2976 $DB->insert_record($tablename, array('course' => 5, 'onechar' => 'c'));
2977
2978 $this->assertSame(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));
a3d5830a 2979
9d6d32b6 2980 // Test invalid use.
63224dee
PS		 2981 try {
2982 $DB->count_records_sql("SELECT onechar FROM {{$tablename}} WHERE course = ?", array(3));
2983 $this->fail('Exception expected when non-number field used in count_records_sql');
9d6d32b6 2984 } catch (moodle_exception $e) {
63224dee
PS		 2985 $this->assertInstanceOf('coding_exception', $e);
2986 }
2987
2988 try {
2989 $DB->count_records_sql("SELECT course FROM {{$tablename}} WHERE 1 = 2");
2990 $this->fail('Exception expected when non-number field used in count_records_sql');
9d6d32b6 2991 } catch (moodle_exception $e) {
63224dee
PS		 2992 $this->assertInstanceOf('coding_exception', $e);
2993 }
a3d5830a
PS		 2994 }
2995
2996 public function test_record_exists() {
2997 $DB = $this->tdb;
2998 $dbman = $DB->get_manager();
2999
3000 $table = $this->get_test_table();
3001 $tablename = $table->getName();
3002
9d6d32b6
PS		 3003 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3004 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 3005 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
3006 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3007 $dbman->create_table($table);
3008
3009 $this->assertEquals(0, $DB->count_records($tablename));
3010
3011 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
3012 $DB->insert_record($tablename, array('course' => 3));
3013
3014 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
3015
9d6d32b6 3016 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
a3d5830a
PS		 3017 $conditions = array('onetext' => '1');
3018 try {
3019 $DB->record_exists($tablename, $conditions);
3020 if (debugging()) {
9d6d32b6 3021 // Only in debug mode - hopefully all devs test code in debug mode...
a3d5830a
PS		 3022 $this->fail('An Exception is missing, expected due to equating of text fields');
3023 }
9d6d32b6
PS		 3024 } catch (moodle_exception $e) {
3025 $this->assertInstanceOf('dml_exception', $e);
3026 $this->assertSame('textconditionsnotallowed', $e->errorcode);
a3d5830a
PS		 3027 }
3028 }
3029
3030 public function test_record_exists_select() {
3031 $DB = $this->tdb;
3032 $dbman = $DB->get_manager();
3033
3034 $table = $this->get_test_table();
3035 $tablename = $table->getName();
3036
9d6d32b6
PS		 3037 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3038 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 3039 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3040 $dbman->create_table($table);
3041
3042 $this->assertEquals(0, $DB->count_records($tablename));
3043
3044 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
3045 $DB->insert_record($tablename, array('course' => 3));
3046
3047 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
3048 }
3049
3050 public function test_record_exists_sql() {
3051 $DB = $this->tdb;
3052 $dbman = $DB->get_manager();
3053
3054 $table = $this->get_test_table();
3055 $tablename = $table->getName();
3056
9d6d32b6
PS		 3057 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3058 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 3059 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3060 $dbman->create_table($table);
3061
3062 $this->assertEquals(0, $DB->count_records($tablename));
3063
3064 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
3065 $DB->insert_record($tablename, array('course' => 3));
3066
3067 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
3068 }
3069
3070 public function test_recordset_locks_delete() {
3071 $DB = $this->tdb;
3072 $dbman = $DB->get_manager();
3073
9d6d32b6 3074 // Setup.
a3d5830a
PS		 3075 $table = $this->get_test_table();
3076 $tablename = $table->getName();
3077
9d6d32b6
PS		 3078 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3079 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 3080 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3081 $dbman->create_table($table);
3082
3083 $DB->insert_record($tablename, array('course' => 1));
3084 $DB->insert_record($tablename, array('course' => 2));
3085 $DB->insert_record($tablename, array('course' => 3));
3086 $DB->insert_record($tablename, array('course' => 4));
3087 $DB->insert_record($tablename, array('course' => 5));
3088 $DB->insert_record($tablename, array('course' => 6));
3089
9d6d32b6
PS		 3090 // Test against db write locking while on an open recordset.
3091 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}.
a3d5830a
PS		 3092 foreach ($rs as $record) {
3093 $cid = $record->course;
3094 $DB->delete_records($tablename, array('course' => $cid));
3095 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
3096 }
3097 $rs->close();
3098
3099 $this->assertEquals(4, $DB->count_records($tablename, array()));
3100 }
3101
3102 public function test_recordset_locks_update() {
3103 $DB = $this->tdb;
3104 $dbman = $DB->get_manager();
3105
9d6d32b6 3106 // Setup.
a3d5830a
PS		 3107 $table = $this->get_test_table();
3108 $tablename = $table->getName();
3109
9d6d32b6
PS		 3110 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3111 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
a3d5830a
PS		 3112 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3113 $dbman->create_table($table);
3114
3115 $DB->insert_record($tablename, array('course' => 1));
3116 $DB->insert_record($tablename, array('course' => 2));
3117 $DB->insert_record($tablename, array('course' => 3));
3118 $DB->insert_record($tablename, array('course' => 4));
3119 $DB->insert_record($tablename, array('course' => 5));
3120 $DB->insert_record($tablename, array('course' => 6));