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