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