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