fixing regression in the last commit
[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);
702 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
703 } catch (dml_exception $e) {
704 if ($e->errorcode == 'textconditionsnotallowed') {
705 $this->assertTrue(true, 'The Expected exception was caught.');
706 } else {
707 throw $e;
708 }
709 }
710
dec32529
PS
711 // notes:
712 // * limits are tested in test_get_recordset_sql()
713 // * where_clause() is used internally and is tested in test_get_records()
7f9f11b5 714 }
0088bd31 715
b0cb2290 716 public function test_get_recordset_iterator_keys() {
717 $DB = $this->tdb;
718 $dbman = $DB->get_manager();
719
9d833e93 720 $table = $this->get_test_table();
3ff8bf26 721 $tablename = $table->getName();
722
f9ecb171 723 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
724 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
725 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b0cb2290 726 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
727 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
728 $dbman->create_table($table);
b0cb2290 729
22d77567 730 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
731 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
732 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 733 foreach ($data as $record) {
3ff8bf26 734 $DB->insert_record($tablename, $record);
b0cb2290 735 }
736
dec32529 737 // Test repeated numeric keys are returned ok
3ff8bf26 738 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 739
740 reset($data);
741 $count = 0;
742 foreach($rs as $key => $record) {
743 $data_record = current($data);
744 $this->assertEqual($data_record['course'], $key);
745 next($data);
746 $count++;
747 }
748 $rs->close();
b0cb2290 749 $this->assertEqual($count, 3);
750
dec32529 751 // Test string keys are returned ok
3ff8bf26 752 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 753
754 reset($data);
755 $count = 0;
756 foreach($rs as $key => $record) {
757 $data_record = current($data);
758 $this->assertEqual($data_record['name'], $key);
759 next($data);
760 $count++;
761 }
762 $rs->close();
b0cb2290 763 $this->assertEqual($count, 3);
764
dec32529 765 // Test numeric not starting in 1 keys are returned ok
3ff8bf26 766 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 767
768 $data = array_reverse($data);
769 reset($data);
770 $count = 0;
771 foreach($rs as $key => $record) {
772 $data_record = current($data);
773 $this->assertEqual($data_record['id'], $key);
774 next($data);
775 $count++;
776 }
777 $rs->close();
b0cb2290 778 $this->assertEqual($count, 3);
779 }
780
0088bd31 781 public function test_get_recordset_list() {
782 $DB = $this->tdb;
783 $dbman = $DB->get_manager();
784
9d833e93 785 $table = $this->get_test_table();
3ff8bf26 786 $tablename = $table->getName();
787
f9ecb171 788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 790 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
791 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
792 $dbman->create_table($table);
0088bd31 793
3ff8bf26 794 $DB->insert_record($tablename, array('course' => 3));
795 $DB->insert_record($tablename, array('course' => 3));
796 $DB->insert_record($tablename, array('course' => 5));
797 $DB->insert_record($tablename, array('course' => 2));
0088bd31 798
3ff8bf26 799 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 800
0088bd31 801 $counter = 0;
802 foreach ($rs as $record) {
803 $counter++;
804 }
805 $this->assertEqual(3, $counter);
c362878e 806 $rs->close();
807
808 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
809
810 $counter = 0;
811 foreach ($rs as $record) {
812 $counter++;
813 }
c362878e 814 $rs->close();
5d91786a 815 $this->assertEqual(0, $counter);
816
dec32529
PS
817 // notes:
818 // * limits are tested in test_get_recordset_sql()
819 // * where_clause() is used internally and is tested in test_get_records()
0088bd31 820 }
821
822 public function test_get_recordset_select() {
823 $DB = $this->tdb;
824 $dbman = $DB->get_manager();
825
9d833e93 826 $table = $this->get_test_table();
3ff8bf26 827 $tablename = $table->getName();
828
f9ecb171 829 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
830 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 831 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
832 $dbman->create_table($table);
0088bd31 833
3ff8bf26 834 $DB->insert_record($tablename, array('course' => 3));
835 $DB->insert_record($tablename, array('course' => 3));
836 $DB->insert_record($tablename, array('course' => 5));
837 $DB->insert_record($tablename, array('course' => 2));
0088bd31 838
3ff8bf26 839 $rs = $DB->get_recordset_select($tablename, '');
0088bd31 840 $counter = 0;
841 foreach ($rs as $record) {
842 $counter++;
843 }
515cb653 844 $rs->close();
0088bd31 845 $this->assertEqual(4, $counter);
846
3ff8bf26 847 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 848 $counter = 0;
849 foreach ($rs as $record) {
850 $counter++;
851 }
515cb653 852 $rs->close();
0088bd31 853 $this->assertEqual(2, $counter);
5d91786a 854
dec32529
PS
855 // notes:
856 // * limits are tested in test_get_recordset_sql()
0088bd31 857 }
858
859 public function test_get_recordset_sql() {
860 $DB = $this->tdb;
861 $dbman = $DB->get_manager();
862
9d833e93 863 $table = $this->get_test_table();
3ff8bf26 864 $tablename = $table->getName();
865
f9ecb171 866 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
867 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 868 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
869 $dbman->create_table($table);
0088bd31 870
5d91786a 871 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
872 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
873 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
874 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
875 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
876 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
877 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
0088bd31 878
76f8ddc0 879 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
0088bd31 880 $counter = 0;
881 foreach ($rs as $record) {
882 $counter++;
883 }
515cb653 884 $rs->close();
0088bd31 885 $this->assertEqual(2, $counter);
5d91786a 886
887 // limits - only need to test this case, the rest have been tested by test_get_records_sql()
888 // only limitfrom = skips that number of records
40d3df5c 889 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
5d91786a 890 $records = array();
891 foreach($rs as $key => $record) {
892 $records[$key] = $record;
893 }
894 $rs->close();
895 $this->assertEqual(5, count($records));
896 $this->assertEqual($inskey3, reset($records)->id);
897 $this->assertEqual($inskey7, end($records)->id);
898
899 // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here
0088bd31 900 }
901
902 public function test_get_records() {
903 $DB = $this->tdb;
904 $dbman = $DB->get_manager();
905
9d833e93 906 $table = $this->get_test_table();
3ff8bf26 907 $tablename = $table->getName();
908
f9ecb171 909 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
910 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
011bfd2a 911 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
0088bd31 912 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
913 $dbman->create_table($table);
0088bd31 914
3ff8bf26 915 $DB->insert_record($tablename, array('course' => 3));
916 $DB->insert_record($tablename, array('course' => 3));
917 $DB->insert_record($tablename, array('course' => 5));
918 $DB->insert_record($tablename, array('course' => 2));
0088bd31 919
920 // All records
3ff8bf26 921 $records = $DB->get_records($tablename);
0088bd31 922 $this->assertEqual(4, count($records));
923 $this->assertEqual(3, $records[1]->course);
924 $this->assertEqual(3, $records[2]->course);
925 $this->assertEqual(5, $records[3]->course);
926 $this->assertEqual(2, $records[4]->course);
927
928 // Records matching certain conditions
3ff8bf26 929 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 930 $this->assertEqual(2, count($records));
931 $this->assertEqual(3, $records[1]->course);
932 $this->assertEqual(3, $records[2]->course);
933
934 // All records sorted by course
3ff8bf26 935 $records = $DB->get_records($tablename, null, 'course');
0088bd31 936 $this->assertEqual(4, count($records));
937 $current_record = reset($records);
938 $this->assertEqual(4, $current_record->id);
939 $current_record = next($records);
940 $this->assertEqual(1, $current_record->id);
941 $current_record = next($records);
942 $this->assertEqual(2, $current_record->id);
943 $current_record = next($records);
944 $this->assertEqual(3, $current_record->id);
945
946 // All records, but get only one field
3ff8bf26 947 $records = $DB->get_records($tablename, null, '', 'id');
76f8ddc0
PS
948 $this->assertFalse(isset($records[1]->course));
949 $this->assertTrue(isset($records[1]->id));
0088bd31 950 $this->assertEqual(4, count($records));
3ff8bf26 951
0e6e9051
PS
952 // Booleans into params
953 $records = $DB->get_records($tablename, array('course' => true));
954 $this->assertEqual(0, count($records));
955 $records = $DB->get_records($tablename, array('course' => false));
956 $this->assertEqual(0, count($records));
957
011bfd2a
AB
958 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
959 $conditions = array('onetext' => '1');
960 try {
961 $records = $DB->get_records($tablename, $conditions);
962 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
963 } catch (dml_exception $e) {
964 if ($e->errorcode == 'textconditionsnotallowed') {
965 $this->assertTrue(true, 'The Expected exception was caught.');
966 } else {
967 throw $e;
968 }
969 }
970
5d91786a 971 // note: delegate limits testing to test_get_records_sql()
0088bd31 972 }
973
974 public function test_get_records_list() {
50a12c87 975 $DB = $this->tdb;
976 $dbman = $DB->get_manager();
977
9d833e93 978 $table = $this->get_test_table();
3ff8bf26 979 $tablename = $table->getName();
980
f9ecb171 981 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
982 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 983 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
984 $dbman->create_table($table);
50a12c87 985
3ff8bf26 986 $DB->insert_record($tablename, array('course' => 3));
987 $DB->insert_record($tablename, array('course' => 3));
988 $DB->insert_record($tablename, array('course' => 5));
989 $DB->insert_record($tablename, array('course' => 2));
50a12c87 990
76f8ddc0
PS
991 $records = $DB->get_records_list($tablename, 'course', array(3, 2));
992 $this->assertTrue(is_array($records));
50a12c87 993 $this->assertEqual(3, count($records));
994 $this->assertEqual(1, reset($records)->id);
995 $this->assertEqual(2, next($records)->id);
996 $this->assertEqual(4, next($records)->id);
0088bd31 997
c362878e 998 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
999 $this->assertEqual(0, count($records));
1000
5d91786a 1001 // note: delegate limits testing to test_get_records_sql()
0088bd31 1002 }
1003
5d91786a 1004 public function test_get_records_sql() {
5d91786a 1005 $DB = $this->tdb;
1006 $dbman = $DB->get_manager();
1007
1008 $table = $this->get_test_table();
1009 $tablename = $table->getName();
1010
1011 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1012 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1013 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1014 $dbman->create_table($table);
5d91786a 1015
1016 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1017 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1018 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1019 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1020 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1021 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1022 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1023
76f8ddc0 1024 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
50a12c87 1025 $this->assertEqual(2, count($records));
5d91786a 1026 $this->assertEqual($inskey1, reset($records)->id);
1027 $this->assertEqual($inskey4, next($records)->id);
0088bd31 1028
16a2a412 1029 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
ebdf7f7e 1030 $this->enable_debugging();
40d3df5c 1031 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
ebdf7f7e 1032 $this->assertFalse($this->get_debugging() === '');
5d91786a 1033 $this->assertEqual(6, count($records));
5d91786a 1034
1035 // negative limits = no limits
40d3df5c 1036 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
5d91786a 1037 $this->assertEqual(7, count($records));
1038
1039 // zero limits = no limits
40d3df5c 1040 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
5d91786a 1041 $this->assertEqual(7, count($records));
1042
1043 // only limitfrom = skips that number of records
40d3df5c 1044 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
5d91786a 1045 $this->assertEqual(5, count($records));
1046 $this->assertEqual($inskey3, reset($records)->id);
1047 $this->assertEqual($inskey7, end($records)->id);
1048
1049 // only limitnum = fetches that number of records
40d3df5c 1050 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
5d91786a 1051 $this->assertEqual(3, count($records));
1052 $this->assertEqual($inskey1, reset($records)->id);
1053 $this->assertEqual($inskey3, end($records)->id);
1054
1055 // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones
40d3df5c 1056 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
5d91786a 1057 $this->assertEqual(2, count($records));
1058 $this->assertEqual($inskey4, reset($records)->id);
1059 $this->assertEqual($inskey5, end($records)->id);
1060
5d31799a
EL
1061 // both limitfrom and limitnum in query having subqueris
1062 // note the subquery skips records with course = 0 and 3
1063 $sql = "SELECT * FROM {{$tablename}}
1064 WHERE course NOT IN (
1065 SELECT course FROM {{$tablename}}
1066 WHERE course IN (0, 3))
1067 ORDER BY course";
1068 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2
1069 $this->assertEqual(2, count($records));
1070 $this->assertEqual($inskey6, reset($records)->id);
1071 $this->assertEqual($inskey5, end($records)->id);
1072 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2
1073 $this->assertEqual(2, count($records));
1074 $this->assertEqual($inskey3, reset($records)->id);
1075 $this->assertEqual($inskey2, end($records)->id);
1076
1077 // TODO: Test limits in queries having DISTINCT clauses
1078
5d91786a 1079 // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here
0088bd31 1080 }
1081
1082 public function test_get_records_menu() {
50a12c87 1083 $DB = $this->tdb;
1084 $dbman = $DB->get_manager();
1085
9d833e93 1086 $table = $this->get_test_table();
3ff8bf26 1087 $tablename = $table->getName();
1088
f9ecb171 1089 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1090 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1091 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1092 $dbman->create_table($table);
50a12c87 1093
3ff8bf26 1094 $DB->insert_record($tablename, array('course' => 3));
1095 $DB->insert_record($tablename, array('course' => 3));
1096 $DB->insert_record($tablename, array('course' => 5));
1097 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1098
76f8ddc0
PS
1099 $records = $DB->get_records_menu($tablename, array('course' => 3));
1100 $this->assertTrue(is_array($records));
50a12c87 1101 $this->assertEqual(2, count($records));
1102 $this->assertFalse(empty($records[1]));
1103 $this->assertFalse(empty($records[2]));
1104 $this->assertEqual(3, $records[1]);
1105 $this->assertEqual(3, $records[2]);
0088bd31 1106
5d91786a 1107 // note: delegate limits testing to test_get_records_sql()
0088bd31 1108 }
1109
1110 public function test_get_records_select_menu() {
50a12c87 1111 $DB = $this->tdb;
1112 $dbman = $DB->get_manager();
1113
9d833e93 1114 $table = $this->get_test_table();
3ff8bf26 1115 $tablename = $table->getName();
1116
f9ecb171 1117 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1118 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1119 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1120 $dbman->create_table($table);
50a12c87 1121
3ff8bf26 1122 $DB->insert_record($tablename, array('course' => 3));
1123 $DB->insert_record($tablename, array('course' => 2));
1124 $DB->insert_record($tablename, array('course' => 3));
1125 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1126
76f8ddc0
PS
1127 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
1128 $this->assertTrue(is_array($records));
50a12c87 1129
1130 $this->assertEqual(3, count($records));
1131 $this->assertFalse(empty($records[1]));
1132 $this->assertTrue(empty($records[2]));
1133 $this->assertFalse(empty($records[3]));
1134 $this->assertFalse(empty($records[4]));
1135 $this->assertEqual(3, $records[1]);
1136 $this->assertEqual(3, $records[3]);
1137 $this->assertEqual(5, $records[4]);
0088bd31 1138
5d91786a 1139 // note: delegate limits testing to test_get_records_sql()
0088bd31 1140 }
1141
1142 public function test_get_records_sql_menu() {
50a12c87 1143 $DB = $this->tdb;
1144 $dbman = $DB->get_manager();
1145
9d833e93 1146 $table = $this->get_test_table();
3ff8bf26 1147 $tablename = $table->getName();
1148
f9ecb171 1149 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1150 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1151 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1152 $dbman->create_table($table);
50a12c87 1153
3ff8bf26 1154 $DB->insert_record($tablename, array('course' => 3));
1155 $DB->insert_record($tablename, array('course' => 2));
1156 $DB->insert_record($tablename, array('course' => 3));
1157 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1158
76f8ddc0
PS
1159 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
1160 $this->assertTrue(is_array($records));
50a12c87 1161
1162 $this->assertEqual(3, count($records));
1163 $this->assertFalse(empty($records[1]));
1164 $this->assertTrue(empty($records[2]));
1165 $this->assertFalse(empty($records[3]));
1166 $this->assertFalse(empty($records[4]));
1167 $this->assertEqual(3, $records[1]);
1168 $this->assertEqual(3, $records[3]);
1169 $this->assertEqual(5, $records[4]);
0088bd31 1170
5d91786a 1171 // note: delegate limits testing to test_get_records_sql()
0088bd31 1172 }
1173
1174 public function test_get_record() {
50a12c87 1175 $DB = $this->tdb;
1176 $dbman = $DB->get_manager();
1177
9d833e93 1178 $table = $this->get_test_table();
3ff8bf26 1179 $tablename = $table->getName();
1180
f9ecb171 1181 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1182 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1183 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1184 $dbman->create_table($table);
50a12c87 1185
3ff8bf26 1186 $DB->insert_record($tablename, array('course' => 3));
1187 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1188
76f8ddc0
PS
1189 $record = $DB->get_record($tablename, array('id' => 2));
1190 $this->assertTrue($record instanceof stdClass);
0088bd31 1191
50a12c87 1192 $this->assertEqual(2, $record->course);
76f8ddc0
PS
1193 $this->assertEqual(2, $record->id);
1194 }
1195
1196
1197 public function test_get_record_select() {
1198 $DB = $this->tdb;
1199 $dbman = $DB->get_manager();
1200
1201 $table = $this->get_test_table();
1202 $tablename = $table->getName();
1203
1204 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1205 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1206 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1207 $dbman->create_table($table);
76f8ddc0
PS
1208
1209 $DB->insert_record($tablename, array('course' => 3));
1210 $DB->insert_record($tablename, array('course' => 2));
1211
1212 $record = $DB->get_record_select($tablename, "id = ?", array(2));
1213 $this->assertTrue($record instanceof stdClass);
1214
1215 $this->assertEqual(2, $record->course);
1216
1217 // note: delegates limit testing to test_get_records_sql()
0088bd31 1218 }
1219
0088bd31 1220 public function test_get_record_sql() {
50a12c87 1221 $DB = $this->tdb;
1222 $dbman = $DB->get_manager();
1223
9d833e93 1224 $table = $this->get_test_table();
3ff8bf26 1225 $tablename = $table->getName();
1226
f9ecb171 1227 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1228 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1229 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1230 $dbman->create_table($table);
50a12c87 1231
3ff8bf26 1232 $DB->insert_record($tablename, array('course' => 3));
1233 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1234
66556e7a
PS
1235 // standard use
1236 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
1237 $this->assertTrue($record instanceof stdClass);
50a12c87 1238 $this->assertEqual(2, $record->course);
66556e7a 1239 $this->assertEqual(2, $record->id);
0088bd31 1240
e6c6531c 1241 // backwards compatibility with $ignoremultiple
1242 $this->assertFalse(IGNORE_MISSING);
1243 $this->assertTrue(IGNORE_MULTIPLE);
1244
66556e7a
PS
1245 // record not found - ignore
1246 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1247 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1248
1249 // record not found error
af12ea93 1250 try {
40d3df5c 1251 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
af12ea93 1252 $this->fail("Exception expected");
1253 } catch (dml_missing_record_exception $e) {
1254 $this->assertTrue(true);
1255 }
1256
ebdf7f7e 1257 $this->enable_debugging();
66556e7a 1258 $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
ebdf7f7e 1259 $this->assertFalse($this->get_debugging() === '');
66556e7a
PS
1260
1261 // multiple matches ignored
1262 $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1263
1264 // multiple found error
af12ea93 1265 try {
40d3df5c 1266 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
af12ea93 1267 $this->fail("Exception expected");
1268 } catch (dml_multiple_records_exception $e) {
1269 $this->assertTrue(true);
1270 }
0088bd31 1271 }
1272
1273 public function test_get_field() {
50a12c87 1274 $DB = $this->tdb;
1275 $dbman = $DB->get_manager();
1276
9d833e93 1277 $table = $this->get_test_table();
3ff8bf26 1278 $tablename = $table->getName();
1279
f9ecb171 1280 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1281 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
011bfd2a 1282 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
50a12c87 1283 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1284 $dbman->create_table($table);
0088bd31 1285
d7f42e77
PS
1286 $id1 = $DB->insert_record($tablename, array('course' => 3));
1287 $DB->insert_record($tablename, array('course' => 5));
2b3ccae5 1288 $DB->insert_record($tablename, array('course' => 5));
bc4fd49d 1289
2b3ccae5 1290 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
bc4fd49d 1291 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('course' => 3)));
2b3ccae5
PS
1292
1293 $this->assertIdentical(false, $DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
1294 try {
1295 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
1296 $this->assertFail('Exception expected due to missing record');
1297 } catch (dml_exception $ex) {
1298 $this->assertTrue(true);
1299 }
1300
ebdf7f7e 1301 $this->enable_debugging();
2b3ccae5 1302 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
ebdf7f7e 1303 $this->assertIdentical($this->get_debugging(), '');
2b3ccae5 1304
ebdf7f7e 1305 $this->enable_debugging();
2b3ccae5 1306 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
ebdf7f7e 1307 $this->assertFalse($this->get_debugging() === '');
011bfd2a
AB
1308
1309 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1310 $conditions = array('onetext' => '1');
1311 try {
1312 $DB->get_field($tablename, 'course', $conditions);
1313 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
1314 } catch (dml_exception $e) {
1315 if ($e->errorcode == 'textconditionsnotallowed') {
1316 $this->assertTrue(true, 'The Expected exception was caught.');
1317 } else {
1318 throw $e;
1319 }
1320 }
0088bd31 1321 }
1322
1323 public function test_get_field_select() {
50a12c87 1324 $DB = $this->tdb;
1325 $dbman = $DB->get_manager();
1326
9d833e93 1327 $table = $this->get_test_table();
3ff8bf26 1328 $tablename = $table->getName();
1329
f9ecb171 1330 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1331 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1332 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1333 $dbman->create_table($table);
50a12c87 1334
3ff8bf26 1335 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1336
66556e7a 1337 $this->assertEqual(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
0088bd31 1338 }
1339
1340 public function test_get_field_sql() {
50a12c87 1341 $DB = $this->tdb;
1342 $dbman = $DB->get_manager();
1343
9d833e93 1344 $table = $this->get_test_table();
3ff8bf26 1345 $tablename = $table->getName();
1346
f9ecb171 1347 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1348 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1349 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1350 $dbman->create_table($table);
50a12c87 1351
3ff8bf26 1352 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1353
66556e7a 1354 $this->assertEqual(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
0088bd31 1355 }
1356
50a12c87 1357 public function test_get_fieldset_select() {
1358 $DB = $this->tdb;
1359 $dbman = $DB->get_manager();
1360
9d833e93 1361 $table = $this->get_test_table();
3ff8bf26 1362 $tablename = $table->getName();
1363
f9ecb171 1364 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1365 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1366 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1367 $dbman->create_table($table);
50a12c87 1368
3ff8bf26 1369 $DB->insert_record($tablename, array('course' => 1));
1370 $DB->insert_record($tablename, array('course' => 3));
1371 $DB->insert_record($tablename, array('course' => 2));
1372 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1373
66556e7a
PS
1374 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
1375 $this->assertTrue(is_array($fieldset));
50a12c87 1376
1377 $this->assertEqual(3, count($fieldset));
1378 $this->assertEqual(3, $fieldset[0]);
1379 $this->assertEqual(2, $fieldset[1]);
1380 $this->assertEqual(6, $fieldset[2]);
0088bd31 1381 }
1382
1383 public function test_get_fieldset_sql() {
50a12c87 1384 $DB = $this->tdb;
1385 $dbman = $DB->get_manager();
0088bd31 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_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
1401 $this->assertTrue(is_array($fieldset));
50a12c87 1402
1403 $this->assertEqual(3, count($fieldset));
1404 $this->assertEqual(2, $fieldset[0]);
1405 $this->assertEqual(3, $fieldset[1]);
1406 $this->assertEqual(4, $fieldset[2]);
0088bd31 1407 }
1408
1409 public function test_insert_record_raw() {
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');
7374c35c 1418 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
50a12c87 1419 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1420 $dbman->create_table($table);
50a12c87 1421
8ca7b3df
PS
1422 $record = (object)array('course' => 1, 'onechar' => 'xx');
1423 $before = clone($record);
1424 $result = $DB->insert_record_raw($tablename, $record);
4d7b0467 1425 $this->assertIdentical(1, $result);
8ca7b3df 1426 $this->assertIdentical($record, $before);
7374c35c
PS
1427
1428 $record = $DB->get_record($tablename, array('course' => 1));
1429 $this->assertTrue($record instanceof stdClass);
1430 $this->assertIdentical('xx', $record->onechar);
1431
1432 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
1433 $this->assertIdentical(true, $result);
1434
1435 // note: bulk not implemented yet
1436 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
1437 $record = $DB->get_record($tablename, array('course' => 3));
1438 $this->assertTrue($record instanceof stdClass);
1439 $this->assertIdentical('zz', $record->onechar);
1440
1441 // custom sequence (id) - returnid is ignored
1442 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
1443 $this->assertIdentical(true, $result);
1444 $record = $DB->get_record($tablename, array('id' => 10));
1445 $this->assertTrue($record instanceof stdClass);
1446 $this->assertIdentical('bb', $record->onechar);
4d7b0467
PS
1447
1448 // custom sequence - missing id error
1449 try {
1450 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
1451 $this->assertFail('Exception expected due to missing record');
1452 } catch (coding_exception $ex) {
1453 $this->assertTrue(true);
1454 }
3b633796
PS
1455
1456 // wrong column error
1457 try {
1458 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
1459 $this->assertFail('Exception expected due to invalid column');
1460 } catch (dml_write_exception $ex) {
1461 $this->assertTrue(true);
1462 }
0088bd31 1463 }
1464
1465 public function test_insert_record() {
628dff25 1466 // All the information in this test is fetched from DB by get_recordset() so we
1467 // have such method properly tested against nulls, empties and friends...
1468
50a12c87 1469 $DB = $this->tdb;
1470 $dbman = $DB->get_manager();
1471
9d833e93 1472 $table = $this->get_test_table();
3ff8bf26 1473 $tablename = $table->getName();
1474
f9ecb171 1475 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1476 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1477 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1478 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1479 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1480 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1481 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1482 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1483 $dbman->create_table($table);
50a12c87 1484
4d7b0467
PS
1485 $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true));
1486 $record = $DB->get_record($tablename, array('course' => 1));
628dff25 1487 $this->assertEqual(1, $record->id);
1488 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1489 $this->assertEqual(200, $record->onenum);
4d7b0467 1490 $this->assertIdentical('onestring', $record->onechar);
628dff25 1491 $this->assertNull($record->onetext);
1492 $this->assertNull($record->onebinary);
1493
4d7b0467
PS
1494 // without returning id, bulk not implemented
1495 $result = $this->assertIdentical(true, $DB->insert_record($tablename, array('course' => 99), false, true));
1496 $record = $DB->get_record($tablename, array('course' => 99));
1497 $this->assertEqual(2, $record->id);
1498 $this->assertEqual(99, $record->course);
1499
628dff25 1500 // Check nulls are set properly for all types
ac6f1a82 1501 $record = new stdClass();
628dff25 1502 $record->oneint = null;
1503 $record->onenum = null;
1504 $record->onechar = null;
1505 $record->onetext = null;
1506 $record->onebinary = null;
1507 $recid = $DB->insert_record($tablename, $record);
4d7b0467
PS
1508 $record = $DB->get_record($tablename, array('id' => $recid));
1509 $this->assertEqual(0, $record->course);
628dff25 1510 $this->assertNull($record->oneint);
1511 $this->assertNull($record->onenum);
1512 $this->assertNull($record->onechar);
1513 $this->assertNull($record->onetext);
1514 $this->assertNull($record->onebinary);
1515
1516 // Check zeros are set properly for all types
ac6f1a82 1517 $record = new stdClass();
628dff25 1518 $record->oneint = 0;
1519 $record->onenum = 0;
1520 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1521 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1522 $this->assertEqual(0, $record->oneint);
1523 $this->assertEqual(0, $record->onenum);
1524
1525 // Check booleans are set properly for all types
ac6f1a82 1526 $record = new stdClass();
628dff25 1527 $record->oneint = true; // trues
1528 $record->onenum = true;
1529 $record->onechar = true;
1530 $record->onetext = true;
1531 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1532 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1533 $this->assertEqual(1, $record->oneint);
1534 $this->assertEqual(1, $record->onenum);
1535 $this->assertEqual(1, $record->onechar);
1536 $this->assertEqual(1, $record->onetext);
1537
ac6f1a82 1538 $record = new stdClass();
628dff25 1539 $record->oneint = false; // falses
1540 $record->onenum = false;
1541 $record->onechar = false;
1542 $record->onetext = false;
1543 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1544 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1545 $this->assertEqual(0, $record->oneint);
1546 $this->assertEqual(0, $record->onenum);
1547 $this->assertEqual(0, $record->onechar);
1548 $this->assertEqual(0, $record->onetext);
1549
1550 // Check string data causes exception in numeric types
ac6f1a82 1551 $record = new stdClass();
628dff25 1552 $record->oneint = 'onestring';
1553 $record->onenum = 0;
1554 try {
1555 $DB->insert_record($tablename, $record);
1556 $this->fail("Expecting an exception, none occurred");
1557 } catch (exception $e) {
1558 $this->assertTrue($e instanceof dml_exception);
1559 }
ac6f1a82 1560 $record = new stdClass();
628dff25 1561 $record->oneint = 0;
1562 $record->onenum = 'onestring';
1563 try {
1564 $DB->insert_record($tablename, $record);
1565 $this->fail("Expecting an exception, none occurred");
1566 } catch (exception $e) {
1567 $this->assertTrue($e instanceof dml_exception);
1568 }
1569
2f9c1693 1570 // Check empty string data is stored as 0 in numeric datatypes
ac6f1a82 1571 $record = new stdClass();
fdc45ac3
EL
1572 $record->oneint = ''; // empty string
1573 $record->onenum = 0;
2f9c1693 1574 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1575 $record = $DB->get_record($tablename, array('id' => $recid));
2f9c1693
EL
1576 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1577
ac6f1a82 1578 $record = new stdClass();
fdc45ac3
EL
1579 $record->oneint = 0;
1580 $record->onenum = ''; // empty string
2f9c1693 1581 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1582 $record = $DB->get_record($tablename, array('id' => $recid));
2f9c1693 1583 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
fdc45ac3 1584
628dff25 1585 // Check empty strings are set properly in string types
ac6f1a82 1586 $record = new stdClass();
628dff25 1587 $record->oneint = 0;
1588 $record->onenum = 0;
1589 $record->onechar = '';
1590 $record->onetext = '';
1591 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1592 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1593 $this->assertTrue($record->onechar === '');
1594 $this->assertTrue($record->onetext === '');
1595
1596 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
ac6f1a82 1597 $record = new stdClass();
628dff25 1598 $record->oneint = ((210.10 + 39.92) - 150.02);
1599 $record->onenum = ((210.10 + 39.92) - 150.02);
1600 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1601 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1602 $this->assertEqual(100, $record->oneint);
1603 $this->assertEqual(100, $record->onenum);
1604
1605 // Check various quotes/backslashes combinations in string types
1606 $teststrings = array(
1607 'backslashes and quotes alone (even): "" \'\' \\\\',
1608 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1609 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1610 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1611 foreach ($teststrings as $teststring) {
ac6f1a82 1612 $record = new stdClass();
628dff25 1613 $record->onechar = $teststring;
1614 $record->onetext = $teststring;
1615 $recid = $DB->insert_record($tablename, $record);
4d7b0467 1616 $record = $DB->get_record($tablename, array('id' => $recid));
628dff25 1617 $this->assertEqual($teststring, $record->onechar);
1618 $this->assertEqual($teststring, $record->onetext);
1619 }
1620
1621 // Check LOBs in text/binary columns
ebdf7f7e
PS
1622 $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
1623 $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
ac6f1a82 1624 $record = new stdClass();
628dff25 1625 $record->onetext = $clob;
1626 $record->onebinary = $blob;
1627 $recid = $DB->insert_record($tablename, $record);
1628 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1629 $record = $rs->current();
1630 $rs->close();
ff8c7b2a
EL
1631 $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
1632 $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
0088bd31 1633
628dff25 1634 // And "small" LOBs too, just in case
1635 $newclob = substr($clob, 0, 500);
1636 $newblob = substr($blob, 0, 250);
ac6f1a82 1637 $record = new stdClass();
628dff25 1638 $record->onetext = $newclob;
1639 $record->onebinary = $newblob;
1640 $recid = $DB->insert_record($tablename, $record);
1641 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1642 $record = $rs->current();
1643 $rs->close();
ff8c7b2a
EL
1644 $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
1645 $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
5d91786a 1646 $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
e9fec42f 1647
4e999612
PS
1648 // And "diagnostic" LOBs too, just in case
1649 $newclob = '\'"\\;/ěščřžýáíé';
1650 $newblob = '\'"\\;/ěščřžýáíé';
ac6f1a82 1651 $record = new stdClass();
4e999612
PS
1652 $record->onetext = $newclob;
1653 $record->onebinary = $newblob;
1654 $recid = $DB->insert_record($tablename, $record);
1655 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1656 $record = $rs->current();
1657 $rs->close();
1658 $this->assertIdentical($newclob, $record->onetext);
1659 $this->assertIdentical($newblob, $record->onebinary);
1660 $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
1661
e9fec42f 1662 // test data is not modified
ac6f1a82 1663 $record = new stdClass();
4d7b0467
PS
1664 $record->id = -1; // has to be ignored
1665 $record->course = 3;
1666 $record->lalala = 'lalal'; // unused
1667 $before = clone($record);
e9fec42f 1668 $DB->insert_record($tablename, $record);
4d7b0467 1669 $this->assertEqual($record, $before);
27323c97
PS
1670
1671 // make sure the id is always increasing and never reuses the same id
1672 $id1 = $DB->insert_record($tablename, array('course' => 3));
1673 $id2 = $DB->insert_record($tablename, array('course' => 3));
1674 $this->assertTrue($id1 < $id2);
1675 $DB->delete_records($tablename, array('id'=>$id2));
1676 $id3 = $DB->insert_record($tablename, array('course' => 3));
1677 $this->assertTrue($id2 < $id3);
1678 $DB->delete_records($tablename, array());
1679 $id4 = $DB->insert_record($tablename, array('course' => 3));
1680 $this->assertTrue($id3 < $id4);
0088bd31 1681 }
1682
94898738 1683 public function test_import_record() {
969cb35f
PS
1684 // All the information in this test is fetched from DB by get_recordset() so we
1685 // have such method properly tested against nulls, empties and friends...
1686
94898738 1687 $DB = $this->tdb;
1688 $dbman = $DB->get_manager();
1689
9d833e93 1690 $table = $this->get_test_table();
3ff8bf26 1691 $tablename = $table->getName();
1692
f9ecb171 1693 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1694 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
969cb35f
PS
1695 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1696 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1697 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1698 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1699 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
94898738 1700 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1701 $dbman->create_table($table);
94898738 1702
969cb35f
PS
1703 $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true));
1704 $record = $DB->get_record($tablename, array('course' => 1));
1705 $this->assertEqual(1, $record->id);
1706 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1707 $this->assertEqual(200, $record->onenum);
1708 $this->assertIdentical('onestring', $record->onechar);
1709 $this->assertNull($record->onetext);
1710 $this->assertNull($record->onebinary);
94898738 1711
8ca7b3df
PS
1712 // ignore extra columns
1713 $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);
1714 $before = clone($record);
1715 $this->assertIdentical(true, $DB->import_record($tablename, $record));
1716 $this->assertIdentical($record, $before);
3ff8bf26 1717 $records = $DB->get_records($tablename);
94898738 1718 $this->assertEqual(2, $records[13]->course);
969cb35f
PS
1719
1720 // Check nulls are set properly for all types
ac6f1a82 1721 $record = new stdClass();
969cb35f
PS
1722 $record->id = 20;
1723 $record->oneint = null;
1724 $record->onenum = null;
1725 $record->onechar = null;
1726 $record->onetext = null;
1727 $record->onebinary = null;
1728 $this->assertTrue($DB->import_record($tablename, $record));
1729 $record = $DB->get_record($tablename, array('id' => 20));
1730 $this->assertEqual(0, $record->course);
1731 $this->assertNull($record->oneint);
1732 $this->assertNull($record->onenum);
1733 $this->assertNull($record->onechar);
1734 $this->assertNull($record->onetext);
1735 $this->assertNull($record->onebinary);
1736
1737 // Check zeros are set properly for all types
ac6f1a82 1738 $record = new stdClass();
969cb35f
PS
1739 $record->id = 23;
1740 $record->oneint = 0;
1741 $record->onenum = 0;
1742 $this->assertTrue($DB->import_record($tablename, $record));
1743 $record = $DB->get_record($tablename, array('id' => 23));
1744 $this->assertEqual(0, $record->oneint);
1745 $this->assertEqual(0, $record->onenum);
1746
1747 // Check string data causes exception in numeric types
ac6f1a82 1748 $record = new stdClass();
969cb35f
PS
1749 $record->id = 32;
1750 $record->oneint = 'onestring';
1751 $record->onenum = 0;
1752 try {
1753 $DB->import_record($tablename, $record);
1754 $this->fail("Expecting an exception, none occurred");
1755 } catch (exception $e) {
1756 $this->assertTrue($e instanceof dml_exception);
1757 }
ac6f1a82 1758 $record = new stdClass();
969cb35f
PS
1759 $record->id = 35;
1760 $record->oneint = 0;
1761 $record->onenum = 'onestring';
1762 try {
1763 $DB->import_record($tablename, $record);
1764 $this->fail("Expecting an exception, none occurred");
1765 } catch (exception $e) {
1766 $this->assertTrue($e instanceof dml_exception);
1767 }
1768
1769 // Check empty strings are set properly in string types
ac6f1a82 1770 $record = new stdClass();
969cb35f
PS
1771 $record->id = 44;
1772 $record->oneint = 0;
1773 $record->onenum = 0;
1774 $record->onechar = '';
1775 $record->onetext = '';
1776 $this->assertTrue($DB->import_record($tablename, $record));
1777 $record = $DB->get_record($tablename, array('id' => 44));
1778 $this->assertTrue($record->onechar === '');
1779 $this->assertTrue($record->onetext === '');
1780
1781 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
ac6f1a82 1782 $record = new stdClass();
969cb35f
PS
1783 $record->id = 47;
1784 $record->oneint = ((210.10 + 39.92) - 150.02);
1785 $record->onenum = ((210.10 + 39.92) - 150.02);
1786 $this->assertTrue($DB->import_record($tablename, $record));
1787 $record = $DB->get_record($tablename, array('id' => 47));
1788 $this->assertEqual(100, $record->oneint);
1789 $this->assertEqual(100, $record->onenum);
1790
1791 // Check various quotes/backslashes combinations in string types
1792 $i = 50;
1793 $teststrings = array(
1794 'backslashes and quotes alone (even): "" \'\' \\\\',
1795 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1796 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1797 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1798 foreach ($teststrings as $teststring) {
ac6f1a82 1799 $record = new stdClass();
969cb35f
PS
1800 $record->id = $i;
1801 $record->onechar = $teststring;
1802 $record->onetext = $teststring;
1803 $this->assertTrue($DB->import_record($tablename, $record));
1804 $record = $DB->get_record($tablename, array('id' => $i));
1805 $this->assertEqual($teststring, $record->onechar);
1806 $this->assertEqual($teststring, $record->onetext);
1807 $i = $i + 3;
1808 }
1809
1810 // Check LOBs in text/binary columns
ebdf7f7e 1811 $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
ac6f1a82 1812 $record = new stdClass();
969cb35f
PS
1813 $record->id = 70;
1814 $record->onetext = $clob;
1815 $record->onebinary = '';
1816 $this->assertTrue($DB->import_record($tablename, $record));
1817 $rs = $DB->get_recordset($tablename, array('id' => 70));
1818 $record = $rs->current();
1819 $rs->close();
1820 $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
1821
ebdf7f7e 1822 $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
ac6f1a82 1823 $record = new stdClass();
969cb35f
PS
1824 $record->id = 71;
1825 $record->onetext = '';
1826 $record->onebinary = $blob;
1827 $this->assertTrue($DB->import_record($tablename, $record));
1828 $rs = $DB->get_recordset($tablename, array('id' => 71));
1829 $record = $rs->current();
1830 $rs->close();
1831 $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
1832
1833 // And "small" LOBs too, just in case
1834 $newclob = substr($clob, 0, 500);
1835 $newblob = substr($blob, 0, 250);
ac6f1a82 1836 $record = new stdClass();
969cb35f
PS
1837 $record->id = 73;
1838 $record->onetext = $newclob;
1839 $record->onebinary = $newblob;
1840 $this->assertTrue($DB->import_record($tablename, $record));
1841 $rs = $DB->get_recordset($tablename, array('id' => 73));
1842 $record = $rs->current();
1843 $rs->close();
1844 $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
1845 $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
1846 $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
94898738 1847 }
1848
0088bd31 1849 public function test_update_record_raw() {
50a12c87 1850 $DB = $this->tdb;
1851 $dbman = $DB->get_manager();
0088bd31 1852
9d833e93 1853 $table = $this->get_test_table();
3ff8bf26 1854 $tablename = $table->getName();
1855
f9ecb171 1856 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1857 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1858 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1859 $dbman->create_table($table);
50a12c87 1860
3ff8bf26 1861 $DB->insert_record($tablename, array('course' => 1));
5ab41c27
PS
1862 $DB->insert_record($tablename, array('course' => 3));
1863
3ff8bf26 1864 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1865 $record->course = 2;
3ff8bf26 1866 $this->assertTrue($DB->update_record_raw($tablename, $record));
5ab41c27
PS
1867 $this->assertEqual(0, $DB->count_records($tablename, array('course' => 1)));
1868 $this->assertEqual(1, $DB->count_records($tablename, array('course' => 2)));
1869 $this->assertEqual(1, $DB->count_records($tablename, array('course' => 3)));
1870
1871 $record = $DB->get_record($tablename, array('course' => 1));
1872 $record->xxxxx = 2;
1873 try {
1874 $DB->update_record_raw($tablename, $record);
1875 $this->fail("Expecting an exception, none occurred");
1876 } catch (Exception $e) {
1877 $this->assertTrue($e instanceof coding_exception);
1878 }
1879
1880 $record = $DB->get_record($tablename, array('course' => 3));
1881 unset($record->id);
1882 try {
1883 $DB->update_record_raw($tablename, $record);
1884 $this->fail("Expecting an exception, none occurred");
1885 } catch (Exception $e) {
1886 $this->assertTrue($e instanceof coding_exception);
1887 }
0088bd31 1888 }
1889
1890 public function test_update_record() {
628dff25 1891
1892 // All the information in this test is fetched from DB by get_record() so we
1893 // have such method properly tested against nulls, empties and friends...
1894
50a12c87 1895 $DB = $this->tdb;
1896 $dbman = $DB->get_manager();
1897
9d833e93 1898 $table = $this->get_test_table();
3ff8bf26 1899 $tablename = $table->getName();
1900
f9ecb171 1901 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1902 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1903 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1904 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1905 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1906 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1907 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1908 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1909 $dbman->create_table($table);
0088bd31 1910
3ff8bf26 1911 $DB->insert_record($tablename, array('course' => 1));
1912 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1913 $record->course = 2;
628dff25 1914
3ff8bf26 1915 $this->assertTrue($DB->update_record($tablename, $record));
1916 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1917 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
628dff25 1918 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1919 $this->assertEqual(200, $record->onenum);
1920 $this->assertEqual('onestring', $record->onechar);
1921 $this->assertNull($record->onetext);
1922 $this->assertNull($record->onebinary);
c824e1e1 1923
628dff25 1924 // Check nulls are set properly for all types
1925 $record->oneint = null;
1926 $record->onenum = null;
1927 $record->onechar = null;
1928 $record->onetext = null;
1929 $record->onebinary = null;
1930 $DB->update_record($tablename, $record);
1931 $record = $DB->get_record($tablename, array('course' => 2));
1932 $this->assertNull($record->oneint);
1933 $this->assertNull($record->onenum);
1934 $this->assertNull($record->onechar);
1935 $this->assertNull($record->onetext);
1936 $this->assertNull($record->onebinary);
c824e1e1 1937
628dff25 1938 // Check zeros are set properly for all types
1939 $record->oneint = 0;
1940 $record->onenum = 0;
1941 $DB->update_record($tablename, $record);
1942 $record = $DB->get_record($tablename, array('course' => 2));
1943 $this->assertEqual(0, $record->oneint);
1944 $this->assertEqual(0, $record->onenum);
c824e1e1 1945
628dff25 1946 // Check booleans are set properly for all types
1947 $record->oneint = true; // trues
1948 $record->onenum = true;
1949 $record->onechar = true;
1950 $record->onetext = true;
1951 $DB->update_record($tablename, $record);
1952 $record = $DB->get_record($tablename, array('course' => 2));
1953 $this->assertEqual(1, $record->oneint);
1954 $this->assertEqual(1, $record->onenum);
1955 $this->assertEqual(1, $record->onechar);
1956 $this->assertEqual(1, $record->onetext);
1957
1958 $record->oneint = false; // falses
1959 $record->onenum = false;
1960 $record->onechar = false;
1961 $record->onetext = false;
1962 $DB->update_record($tablename, $record);
1963 $record = $DB->get_record($tablename, array('course' => 2));
1964 $this->assertEqual(0, $record->oneint);
1965 $this->assertEqual(0, $record->onenum);
1966 $this->assertEqual(0, $record->onechar);
1967 $this->assertEqual(0, $record->onetext);
c824e1e1 1968
628dff25 1969 // Check string data causes exception in numeric types
1970 $record->oneint = 'onestring';
1971 $record->onenum = 0;
1972 try {
1973 $DB->update_record($tablename, $record);
1974 $this->fail("Expecting an exception, none occurred");
1975 } catch (exception $e) {
1976 $this->assertTrue($e instanceof dml_exception);
1977 }
1978 $record->oneint = 0;
1979 $record->onenum = 'onestring';
1980 try {
1981 $DB->update_record($tablename, $record);
1982 $this->fail("Expecting an exception, none occurred");
1983 } catch (exception $e) {
1984 $this->assertTrue($e instanceof dml_exception);
1985 }
c824e1e1 1986
2f9c1693 1987 // Check empty string data is stored as 0 in numeric datatypes
fdc45ac3
EL
1988 $record->oneint = ''; // empty string
1989 $record->onenum = 0;
2f9c1693
EL
1990 $DB->update_record($tablename, $record);
1991 $record = $DB->get_record($tablename, array('course' => 2));
1992 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1993
fdc45ac3
EL
1994 $record->oneint = 0;
1995 $record->onenum = ''; // empty string
2f9c1693
EL
1996 $DB->update_record($tablename, $record);
1997 $record = $DB->get_record($tablename, array('course' => 2));
1998 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
fdc45ac3 1999
628dff25 2000 // Check empty strings are set properly in string types
2001 $record->oneint = 0;
2002 $record->onenum = 0;
2003 $record->onechar = '';
2004 $record->onetext = '';
2005 $DB->update_record($tablename, $record);
2006 $record = $DB->get_record($tablename, array('course' => 2));
2007 $this->assertTrue($record->onechar === '');
2008 $this->assertTrue($record->onetext === '');
c824e1e1 2009
628dff25 2010 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2011 $record->oneint = ((210.10 + 39.92) - 150.02);
2012 $record->onenum = ((210.10 + 39.92) - 150.02);
2013 $DB->update_record($tablename, $record);
2014 $record = $DB->get_record($tablename, array('course' => 2));
2015 $this->assertEqual(100, $record->oneint);
2016 $this->assertEqual(100, $record->onenum);
3ff8bf26 2017
628dff25 2018 // Check various quotes/backslashes combinations in string types
2019 $teststrings = array(
2020 'backslashes and quotes alone (even): "" \'\' \\\\',
2021 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2022 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2023 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2024 foreach ($teststrings as $teststring) {
2025 $record->onechar = $teststring;
2026 $record->onetext = $teststring;
2027 $DB->update_record($tablename, $record);
2028 $record = $DB->get_record($tablename, array('course' => 2));
2029 $this->assertEqual($teststring, $record->onechar);
2030 $this->assertEqual($teststring, $record->onetext);
2031 }
c824e1e1 2032
628dff25 2033 // Check LOBs in text/binary columns
ebdf7f7e
PS
2034 $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2035 $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
628dff25 2036 $record->onetext = $clob;
2037 $record->onebinary = $blob;
2038 $DB->update_record($tablename, $record);
2039 $record = $DB->get_record($tablename, array('course' => 2));
ff8c7b2a
EL
2040 $this->assertEqual($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
2041 $this->assertEqual($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
628dff25 2042
2043 // And "small" LOBs too, just in case
d246cdd2 2044 $newclob = substr($clob, 0, 500);
2045 $newblob = substr($blob, 0, 250);
628dff25 2046 $record->onetext = $newclob;
2047 $record->onebinary = $newblob;
2048 $DB->update_record($tablename, $record);
2049 $record = $DB->get_record($tablename, array('course' => 2));
ff8c7b2a
EL
2050 $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
2051 $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
c824e1e1 2052 }
2053
0088bd31 2054 public function test_set_field() {
50a12c87 2055 $DB = $this->tdb;
2056 $dbman = $DB->get_manager();
2057
9d833e93 2058 $table = $this->get_test_table();
3ff8bf26 2059 $tablename = $table->getName();
2060
f9ecb171 2061 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2062 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
011bfd2a
AB
2063 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2064 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
50a12c87 2065 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2066 $dbman->create_table($table);
0088bd31 2067
f1276879
PS
2068 // simple set_field
2069 $id1 = $DB->insert_record($tablename, array('course' => 1));
2070 $id2 = $DB->insert_record($tablename, array('course' => 1));
2071 $id3 = $DB->insert_record($tablename, array('course' => 3));
2072 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1)));
2073 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => $id1)));
2074 $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2075 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2076 $DB->delete_records($tablename, array());
2077
2078 // multiple fields affected
2079 $id1 = $DB->insert_record($tablename, array('course' => 1));
2080 $id2 = $DB->insert_record($tablename, array('course' => 1));
2081 $id3 = $DB->insert_record($tablename, array('course' => 3));
2082 $DB->set_field($tablename, 'course', '5', array('course' => 1));
2083 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2084 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2085 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2086 $DB->delete_records($tablename, array());
2087
2088 // no field affected
2089 $id1 = $DB->insert_record($tablename, array('course' => 1));
2090 $id2 = $DB->insert_record($tablename, array('course' => 1));
2091 $id3 = $DB->insert_record($tablename, array('course' => 3));
2092 $DB->set_field($tablename, 'course', '5', array('course' => 0));
2093 $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id1)));
2094 $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2095 $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2096 $DB->delete_records($tablename, array());
2097
2098 // all fields - no condition
2099 $id1 = $DB->insert_record($tablename, array('course' => 1));
2100 $id2 = $DB->insert_record($tablename, array('course' => 1));
2101 $id3 = $DB->insert_record($tablename, array('course' => 3));
2102 $DB->set_field($tablename, 'course', 5, array());
2103 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2104 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2105 $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id3)));
bc4fd49d 2106
011bfd2a
AB
2107 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2108 $conditions = array('onetext' => '1');
2109 try {
2110 $DB->set_field($tablename, 'onechar', 'frog', $conditions);
2111 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
2112 } catch (dml_exception $e) {
2113 if ($e->errorcode == 'textconditionsnotallowed') {
2114 $this->assertTrue(true, 'The Expected exception was caught.');
2115 } else {
2116 throw $e;
2117 }
2118 }
2119
fbabe35a 2120 // Note: All the nulls, booleans, empties, quoted and backslashes tests
2121 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 2122 }
2123
2124 public function test_set_field_select() {
628dff25 2125
2126 // All the information in this test is fetched from DB by get_field() so we
2127 // have such method properly tested against nulls, empties and friends...
2128
50a12c87 2129 $DB = $this->tdb;
2130 $dbman = $DB->get_manager();
2131
9d833e93 2132 $table = $this->get_test_table();
3ff8bf26 2133 $tablename = $table->getName();
2134
f9ecb171 2135 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2136 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 2137 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
2138 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
2139 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2140 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2141 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 2142 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2143 $dbman->create_table($table);
50a12c87 2144
3ff8bf26 2145 $DB->insert_record($tablename, array('course' => 1));
50a12c87 2146
3ff8bf26 2147 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
2148 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 2149
fbabe35a 2150 // Check nulls are set properly for all types
628dff25 2151 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
2152 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
2153 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
2154 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
2155 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
fbabe35a 2156 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
2157 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
2158 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
2159 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
2160 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
2161
2162 // Check zeros are set properly for all types
2163 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
2164 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
2165 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2166 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2167
2168 // Check booleans are set properly for all types
2169 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
2170 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
2171 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
2172 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
2173 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2174 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2175 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2176 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2177
2178 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
2179 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
2180 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
2181 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
2182 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2183 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2184 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2185 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2186
2187 // Check string data causes exception in numeric types
2188 try {
2189 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
2190 $this->fail("Expecting an exception, none occurred");
2191 } catch (exception $e) {
2192 $this->assertTrue($e instanceof dml_exception);
2193 }
2194 try {
2195 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
2196 $this->fail("Expecting an exception, none occurred");
2197 } catch (exception $e) {
2198 $this->assertTrue($e instanceof dml_exception);
2199 }
2200
2f9c1693
EL
2201 // Check empty string data is stored as 0 in numeric datatypes
2202 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
2203 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
2204 $this->assertTrue(is_numeric($field) && $field == 0);
2205
2206 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
2207 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
2208 $this->assertTrue(is_numeric($field) && $field == 0);
fdc45ac3 2209
fbabe35a 2210 // Check empty strings are set properly in string types
2211 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
2212 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
2213 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
2214 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
2215
2216 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2217 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2218 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2219 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2220 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2221
2222 // Check various quotes/backslashes combinations in string types
2223 $teststrings = array(
628dff25 2224 'backslashes and quotes alone (even): "" \'\' \\\\',
2225 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2226 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2227 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
fbabe35a 2228 foreach ($teststrings as $teststring) {
2229 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
2230 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
2231 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2232 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2233 }
2234
2235 // Check LOBs in text/binary columns
ebdf7f7e
PS
2236 $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2237 $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
fbabe35a 2238 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
2239 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
ff8c7b2a
EL
2240 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
2241 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
fbabe35a 2242
2243 // And "small" LOBs too, just in case
2244 $newclob = substr($clob, 0, 500);
2245 $newblob = substr($blob, 0, 250);
2246 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
2247 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
ff8c7b2a
EL
2248 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
2249 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
011bfd2a
AB
2250
2251 // This is the failure from MDL-24863. This was giving an error on MSSQL,
2252 // which converts the '1' to an integer, which cannot then be compared with
2253 // onetext cast to a varchar. This should be fixed and working now.
2254 $newchar = 'frog';
2255 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2256 $params = array('onetext' => '1');
2257 try {
2258 $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);
2259 $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.');
2260 } catch (dml_exception $e) {
2261 $this->assertFalse(true, 'We have an unexpected exception.');
2262 throw $e;
2263 }
2264
2265
0088bd31 2266 }
2267
2268 public function test_count_records() {
2269 $DB = $this->tdb;
2270
2271 $dbman = $DB->get_manager();
2272
9d833e93 2273 $table = $this->get_test_table();
3ff8bf26 2274 $tablename = $table->getName();
2275
f9ecb171 2276 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2277 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
011bfd2a 2278 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
0088bd31 2279 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2280 $dbman->create_table($table);
0088bd31 2281
3ff8bf26 2282 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 2283
3ff8bf26 2284 $DB->insert_record($tablename, array('course' => 3));
2285 $DB->insert_record($tablename, array('course' => 4));
2286 $DB->insert_record($tablename, array('course' => 5));
0088bd31 2287
3ff8bf26 2288 $this->assertEqual(3, $DB->count_records($tablename));
011bfd2a
AB
2289
2290 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2291 $conditions = array('onetext' => '1');
2292 try {
2293 $DB->count_records($tablename, $conditions);
2294 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
2295 } catch (dml_exception $e) {
2296 if ($e->errorcode == 'textconditionsnotallowed') {
2297 $this->assertTrue(true, 'The Expected exception was caught.');
2298 } else {
2299 throw $e;
2300 }
2301 }
0088bd31 2302 }
2303
2304 public function test_count_records_select() {
50a12c87 2305 $DB = $this->tdb;
0088bd31 2306
50a12c87 2307 $dbman = $DB->get_manager();
2308
9d833e93 2309 $table = $this->get_test_table();
3ff8bf26 2310 $tablename = $table->getName();
2311
f9ecb171 2312 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2313 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 2314 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2315 $dbman->create_table($table);
50a12c87 2316
3ff8bf26 2317 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 2318
3ff8bf26 2319 $DB->insert_record($tablename, array('course' => 3));
2320 $DB->insert_record($tablename, array('course' => 4));
2321 $DB->insert_record($tablename, array('course' => 5));
50a12c87 2322
3ff8bf26 2323 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 2324 }
2325
2326 public function test_count_records_sql() {
50a12c87 2327 $DB = $this->tdb;
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));
0088bd31 2343
40d3df5c 2344 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));
0088bd31 2345 }
2346
2347 public function test_record_exists() {
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');
011bfd2a 2356 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
50a12c87 2357 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2358 $dbman->create_table($table);
50a12c87 2359
3ff8bf26 2360 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 2361
3ff8bf26 2362 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
2363 $DB->insert_record($tablename, array('course' => 3));
50a12c87 2364
3ff8bf26 2365 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 2366
011bfd2a
AB
2367
2368 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2369 $conditions = array('onetext' => '1');
2370 try {
2371 $DB->record_exists($tablename, $conditions);
2372 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
2373 } catch (dml_exception $e) {
2374 if ($e->errorcode == 'textconditionsnotallowed') {
2375 $this->assertTrue(true, 'The Expected exception was caught.');
2376 } else {
2377 throw $e;
2378 }
2379 }
0088bd31 2380 }
2381
2382 public function test_record_exists_select() {
50a12c87 2383 $DB = $this->tdb;
2384 $dbman = $DB->get_manager();
0088bd31 2385
9d833e93 2386 $table = $this->get_test_table();
3ff8bf26 2387 $tablename = $table->getName();
2388
f9ecb171 2389 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2390 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 2391 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2392 $dbman->create_table($table);
50a12c87 2393
3ff8bf26 2394 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 2395
3ff8bf26 2396 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
2397 $DB->insert_record($tablename, array('course' => 3));
50a12c87 2398
3ff8bf26 2399 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 2400 }
2401
2402 public function test_record_exists_sql() {
50a12c87 2403 $DB = $this->tdb;
2404 $dbman = $DB->get_manager();
0088bd31 2405
9d833e93 2406 $table = $this->get_test_table();
3ff8bf26 2407 $tablename = $table->getName();
2408
f9ecb171 2409 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2410 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 2411 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2412 $dbman->create_table($table);
50a12c87 2413
3ff8bf26 2414 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 2415
40d3df5c 2416 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
3ff8bf26 2417 $DB->insert_record($tablename, array('course' => 3));
50a12c87 2418
40d3df5c 2419 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
0088bd31 2420 }
2421
2422 public function test_delete_records() {
50a12c87 2423 $DB = $this->tdb;
2424 $dbman = $DB->get_manager();
0088bd31 2425
9d833e93 2426 $table = $this->get_test_table();
3ff8bf26 2427 $tablename = $table->getName();
2428
f9ecb171 2429 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2430 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
011bfd2a 2431 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
50a12c87 2432 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2433 $dbman->create_table($table);
50a12c87 2434
3ff8bf26 2435 $DB->insert_record($tablename, array('course' => 3));
2436 $DB->insert_record($tablename, array('course' => 2));
2437 $DB->insert_record($tablename, array('course' => 2));
50a12c87 2438
2439 // Delete all records
3ff8bf26 2440 $this->assertTrue($DB->delete_records($tablename));
2441 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 2442
2443 // Delete subset of records
3ff8bf26 2444 $DB->insert_record($tablename, array('course' => 3));
2445 $DB->insert_record($tablename, array('course' => 2));
2446 $DB->insert_record($tablename, array('course' => 2));
50a12c87 2447
3ff8bf26 2448 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
2449 $this->assertEqual(1, $DB->count_records($tablename));
a189ac81
PS
2450
2451 // delete all
2452 $this->assertTrue($DB->delete_records($tablename, array()));
2453 $this->assertEqual(0, $DB->count_records($tablename));
011bfd2a
AB
2454
2455 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2456 $conditions = array('onetext'=>'1');
2457 try {
2458 $DB->delete_records($tablename, $conditions);
2459 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
2460 } catch (dml_exception $e) {
2461 if ($e->errorcode == 'textconditionsnotallowed') {
2462 $this->assertTrue(true, 'The Expected exception was caught.');
2463 } else {
2464 throw $e;
2465 }
2466 }
2467
2468 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2469 $conditions = array('onetext' => 1);
2470 try {
2471 $DB->delete_records($tablename, $conditions);
2472 $this->assertFalse(true, 'An Exception is missing, expected due to equating of text fields');
2473 } catch (dml_exception $e) {
2474 if ($e->errorcode == 'textconditionsnotallowed') {
2475 $this->assertTrue(true, 'The Expected exception was caught.');
2476 } else {
2477 throw $e;
2478 }
2479 }
0088bd31 2480 }
2481
2482 public function test_delete_records_select() {
50a12c87 2483 $DB = $this->tdb;
2484 $dbman = $DB->get_manager();
0088bd31 2485
9d833e93 2486 $table = $this->get_test_table();
3ff8bf26 2487 $tablename = $table->getName();
2488
f9ecb171 2489 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2490 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 2491 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2492 $dbman->create_table($table);
0088bd31 2493
3ff8bf26 2494 $DB->insert_record($tablename, array('course' => 3));
2495 $DB->insert_record($tablename, array('course' => 2));
2496 $DB->insert_record($tablename, array('course' => 2));
0088bd31 2497
3ff8bf26 2498 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
2499 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 2500 }
1d861fce 2501
c362878e 2502 public function test_delete_records_list() {
2503 $DB = $this->tdb;
2504 $dbman = $DB->get_manager();
2505
2506 $table = $this->get_test_table();
2507 $tablename = $table->getName();
2508
f9ecb171 2509 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2510 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 2511 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2512 $dbman->create_table($table);
c362878e 2513
2514 $DB->insert_record($tablename, array('course' => 1));
2515 $DB->insert_record($tablename, array('course' => 2));
2516 $DB->insert_record($tablename, array('course' => 3));
2517
2518 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
2519 $this->assertEqual(1, $DB->count_records($tablename));
2520
2521 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
2522 $this->assertEqual(1, $DB->count_records($tablename));
2523 }
2524
082ae821 2525 function test_sql_null_from_clause() {
655bbf51 2526 $DB = $this->tdb;
082ae821 2527 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
2528 $this->assertEqual($DB->get_field_sql($sql), 1);
2529 }
655bbf51 2530
082ae821 2531 function test_sql_bitand() {
2532 $DB = $this->tdb;
c72b9900
PS
2533 $dbman = $DB->get_manager();
2534
2535 $table = $this->get_test_table();
2536 $tablename = $table->getName();
2537
2538 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2539 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2540 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2541 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2542 $dbman->create_table($table);
2543
e586aa39 2544 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
c72b9900 2545
9d833e93 2546 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 2547 $this->assertEqual($DB->get_field_sql($sql), 2);
c72b9900
PS
2548
2549 $sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}";
2550 $result = $DB->get_records_sql($sql);
2551 $this->assertEqual(count($result), 1);
2552 $this->assertEqual(reset($result)->res, 2);
2553
2554 $sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}";
2555 $result = $DB->get_records_sql($sql, array(10));
2556 $this->assertEqual(count($result), 1);
2557 $this->assertEqual(reset($result)->res, 2);
082ae821 2558 }
655bbf51 2559
082ae821 2560 function test_sql_bitnot() {
2561 $DB = $this->tdb;
655bbf51 2562
082ae821 2563 $not = $DB->sql_bitnot(2);
2564 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 2565
9d833e93 2566 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 2567 $this->assertEqual($DB->get_field_sql($sql), 5);
2568 }
655bbf51 2569
082ae821 2570 function test_sql_bitor() {
2571 $DB = $this->tdb;
e586aa39
PS
2572 $dbman = $DB->get_manager();
2573
2574 $table = $this->get_test_table();
2575 $tablename = $table->getName();
2576
2577 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2578 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2579 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2580 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2581 $dbman->create_table($table);
2582
2583 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
2584
9d833e93 2585 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 2586 $this->assertEqual($DB->get_field_sql($sql), 11);
e586aa39
PS
2587
2588 $sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}";
2589 $result = $DB->get_records_sql($sql);
2590 $this->assertEqual(count($result), 1);
2591 $this->assertEqual(reset($result)->res, 11);
2592
2593 $sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}";
2594 $result = $DB->get_records_sql($sql, array(10));
2595 $this->assertEqual(count($result), 1);
2596 $this->assertEqual(reset($result)->res, 11);
082ae821 2597 }
655bbf51 2598
082ae821 2599 function test_sql_bitxor() {
2600 $DB = $this->tdb;
e586aa39
PS
2601 $dbman = $DB->get_manager();
2602
2603 $table = $this->get_test_table();
2604 $tablename = $table->getName();
2605
2606 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2607 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2608 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2609 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2610 $dbman->create_table($table);
2611
2612 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
2613
9d833e93 2614 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 2615 $this->assertEqual($DB->get_field_sql($sql), 9);
e586aa39
PS
2616
2617 $sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}";
2618 $result = $DB->get_records_sql($sql);
2619 $this->assertEqual(count($result), 1);
2620 $this->assertEqual(reset($result)->res, 9);
2621
2622 $sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}";
2623 $result = $DB->get_records_sql($sql, array(10));
2624 $this->assertEqual(count($result), 1);
2625 $this->assertEqual(reset($result)->res, 9);
082ae821 2626 }
2627
e6df3734 2628 function test_sql_modulo() {
2629 $DB = $this->tdb;
9d833e93 2630 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 2631 $this->assertEqual($DB->get_field_sql($sql), 3);
2632 }
2633
082ae821 2634 function test_sql_ceil() {
2635 $DB = $this->tdb;
9d833e93 2636 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 2637 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 2638 }
2639
6ff835b7 2640 function test_cast_char2int() {
2641 $DB = $this->tdb;
2642 $dbman = $DB->get_manager();
2643
98cf713b 2644 $table1 = $this->get_test_table("1");
3ff8bf26 2645 $tablename1 = $table1->getName();
2646
f9ecb171 2647 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2648 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
a018adf0 2649 $table1->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
6ff835b7 2650 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2651 $dbman->create_table($table1);
6ff835b7 2652
a018adf0
EL
2653 $DB->insert_record($tablename1, array('name'=>'0100', 'nametext'=>'0200'));
2654 $DB->insert_record($tablename1, array('name'=>'10', 'nametext'=>'20'));
6ff835b7 2655
98cf713b 2656 $table2 = $this->get_test_table("2");
3ff8bf26 2657 $tablename2 = $table2->getName();
f9ecb171 2658 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2659 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
a018adf0 2660 $table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 2661 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2662 $dbman->create_table($table2);
6ff835b7 2663
a018adf0 2664 $DB->insert_record($tablename2, array('res'=>100, 'restext'=>200));
6ff835b7 2665
a018adf0
EL
2666 // casting varchar field
2667 $sql = "SELECT *
2668 FROM {".$tablename1."} t1
2669 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
2670 $records = $DB->get_records_sql($sql);
2671 $this->assertEqual(count($records), 1);
2672 // also test them in order clauses
2673 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('name');
2674 $records = $DB->get_records_sql($sql);
2675 $this->assertEqual(count($records), 2);
2676 $this->assertEqual(reset($records)->name, '10');
2677 $this->assertEqual(next($records)->name, '0100');
2678
2679 // casting text field
2680 $sql = "SELECT *
2681 FROM {".$tablename1."} t1
2682 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.nametext", true)." = t2.restext ";
2683 $records = $DB->get_records_sql($sql);
2684 $this->assertEqual(count($records), 1);
2685 // also test them in order clauses
2686 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('nametext', true);
2687 $records = $DB->get_records_sql($sql);
2688 $this->assertEqual(count($records), 2);
2689 $this->assertEqual(reset($records)->nametext, '20');
2690 $this->assertEqual(next($records)->nametext, '0200');
6ff835b7 2691 }
2692
2693 function test_cast_char2real() {
2694 $DB = $this->tdb;
2695 $dbman = $DB->get_manager();
2696
9d833e93 2697 $table = $this->get_test_table();
3ff8bf26 2698 $tablename = $table->getName();
2699
f9ecb171 2700 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2701 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
a018adf0 2702 $table->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
f9ecb171 2703 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 2704 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2705 $dbman->create_table($table);
6ff835b7 2706
a018adf0
EL
2707 $DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1));
2708 $DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666));
2709 $DB->insert_record($tablename, array('name'=>'011.10','nametext'=>'011.10','res'=>10.1));
6ff835b7 2710
a018adf0 2711 // casting varchar field
40d3df5c 2712 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 2713 $records = $DB->get_records_sql($sql);
2714 $this->assertEqual(count($records), 2);
a018adf0
EL
2715 // also test them in order clauses
2716 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('name');
2717 $records = $DB->get_records_sql($sql);
2718 $this->assertEqual(count($records), 3);
2719 $this->assertEqual(reset($records)->name, '10.10');
2720 $this->assertEqual(next($records)->name, '011.10');
2721 $this->assertEqual(next($records)->name, '91.10');
2722
2723 // casting text field
2724 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res";
2725 $records = $DB->get_records_sql($sql);
2726 $this->assertEqual(count($records), 2);
2727 // also test them in order clauses
2728 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('nametext', true);
2729 $records = $DB->get_records_sql($sql);
2730 $this->assertEqual(count($records), 3);
2731 $this->assertEqual(reset($records)->nametext, '10.10');
2732 $this->assertEqual(next($records)->nametext, '011.10');
2733 $this->assertEqual(next($records)->nametext, '91.10');
6ff835b7 2734 }
2735
082ae821 2736 function sql_compare_text() {
2737 $DB = $this->tdb;
2738 $dbman = $DB->get_manager();
2739
9d833e93 2740 $table = $this->get_test_table();
3ff8bf26 2741 $tablename = $table->getName();
2742
f9ecb171 2743 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2744 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2745 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2746 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2747 $dbman->create_table($table);
082ae821 2748
3ff8bf26 2749 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
2750 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
2751 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 2752
40d3df5c 2753 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description');
082ae821 2754 $records = $DB->get_records_sql($sql);
2755 $this->assertEqual(count($records), 1);
2756
40d3df5c 2757 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 2758 $records = $DB->get_records_sql($sql);
2759 $this->assertEqual(count($records), 2);
2760 }
2761
d533e6d7 2762 function test_unique_index_collation_trouble() {
5a9c25e8
PS
2763 // note: this is a work in progress, we should probably move this to ddl test
2764
2765 $DB = $this->tdb;
2766 $dbman = $DB->get_manager();
2767
2768 $table = $this->get_test_table();
2769 $tablename = $table->getName();
2770
2771 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2772 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2773 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2774 $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));
2775 $dbman->create_table($table);
5a9c25e8 2776
d533e6d7
PS
2777 $DB->insert_record($tablename, array('name'=>'aaa'));
2778
2779 try {
2780 $DB->insert_record($tablename, array('name'=>'AAA'));
2781 } catch (Exception $e) {
2782 //TODO: ignore case insensitive uniqueness problems for now
2783 //$this->fail("Unique index is case sensitive - this may cause problems in some tables");
2784 }
2785
5a9c25e8 2786 try {
5a9c25e8
PS
2787 $DB->insert_record($tablename, array('name'=>'aäa'));
2788 $DB->insert_record($tablename, array('name'=>'aáa'));
2789 $this->assertTrue(true);
2790 } catch (Exception $e) {
d533e6d7
PS
2791 $family = $DB->get_dbfamily();
2792 if ($family === 'mysql' or $family === 'mssql') {
2793 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation.");
2794 } else {
2795 // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.
2796 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages.");
2797 }
5a9c25e8
PS
2798 throw($e);
2799 }
2800 }
6055f89d
PS
2801
2802 function test_sql_binary_equal() {
2803 $DB = $this->tdb;
2804 $dbman = $DB->get_manager();
2805
2806 $table = $this->get_test_table();
2807 $tablename = $table->getName();
2808
2809 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2810 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6055f89d
PS
2811 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2812 $dbman->create_table($table);
6055f89d 2813
d533e6d7
PS
2814 $DB->insert_record($tablename, array('name'=>'aaa'));
2815 $DB->insert_record($tablename, array('name'=>'aáa'));
2816 $DB->insert_record($tablename, array('name'=>'aäa'));
2817 $DB->insert_record($tablename, array('name'=>'bbb'));
2818 $DB->insert_record($tablename, array('name'=>'BBB'));
2819
2820 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa'));
2821 $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be accent sensitive');
2822
2823 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb'));
2824 $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be case sensitive');
6055f89d
PS
2825 }
2826
2827 function test_sql_like() {
2828 $DB = $this->tdb;
2829 $dbman = $DB->get_manager();
2830
2831 $table = $this->get_test_table();
2832 $tablename = $table->getName();
2833
2834 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2835 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2836 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2837 $dbman->create_table($table);
6055f89d
PS
2838
2839 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2840 $DB->insert_record($tablename, array('name'=>'Nodupor'));
2841 $DB->insert_record($tablename, array('name'=>'ouch'));
2842 $DB->insert_record($tablename, array('name'=>'ouc_'));
2843 $DB->insert_record($tablename, array('name'=>'ouc%'));
2844 $DB->insert_record($tablename, array('name'=>'aui'));
2845 $DB->insert_record($tablename, array('name'=>'aüi'));
2846 $DB->insert_record($tablename, array('name'=>'aÜi'));
2847
d533e6d7 2848 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false);
6055f89d
PS
2849 $records = $DB->get_records_sql($sql, array("%dup_r%"));
2850 $this->assertEqual(count($records), 2);
2851
d533e6d7 2852 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
6055f89d
PS
2853 $records = $DB->get_records_sql($sql, array("%dup%"));
2854 $this->assertEqual(count($records), 1);
2855
d533e6d7 2856 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults
6055f89d
PS
2857 $records = $DB->get_records_sql($sql, array("%dup%"));
2858 $this->assertEqual(count($records), 1);
2859
d533e6d7 2860 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
6055f89d
PS
2861 $records = $DB->get_records_sql($sql, array("ouc\\_"));
2862 $this->assertEqual(count($records), 1);
2863
16114b9d 2864 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');
6055f89d
PS
2865 $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
2866 $this->assertEqual(count($records), 1);
2867
d533e6d7 2868 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true);
6055f89d
PS
2869 $records = $DB->get_records_sql($sql, array('aui'));
2870 $this->assertEqual(count($records), 1);
2871
16114b9d
PS
2872 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE
2873 $records = $DB->get_records_sql($sql, array("%o%"));
2874 $this->assertEqual(count($records), 3);
2875
2876 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE
2877 $records = $DB->get_records_sql($sql, array("%D%"));
2878 $this->assertEqual(count($records), 6);
2879
d533e6d7
PS
2880 // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors
2881 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false);
6055f89d 2882 $records = $DB->get_records_sql($sql, array('aui'));
d533e6d7
PS
2883 //$this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
2884 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false);
6055f89d 2885 $records = $DB->get_records_sql($sql, array('aui'));
d533e6d7 2886 //$this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
6055f89d
PS
2887 }
2888
2889 function test_sql_ilike() {
2f8eea34 2890 // note: this is deprecated, just make sure it does not throw error
6ff835b7 2891 $DB = $this->tdb;
2892 $dbman = $DB->get_manager();
2893
9d833e93 2894 $table = $this->get_test_table();
3ff8bf26 2895 $tablename = $table->getName();
2896
f9ecb171 2897 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2898 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2899 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2900 $dbman->create_table($table);
6ff835b7 2901
3ff8bf26 2902 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2903 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2904 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2905
2f8eea34 2906 // make sure it prints debug message
ebdf7f7e 2907 $this->enable_debugging();
40d3df5c 2908 $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2909 $params = array("%dup_r%");
ebdf7f7e 2910 $this->assertFalse($this->get_debugging() === '');
2f8eea34
PS
2911
2912 // following must not throw exception, we ignore result
2913 $DB->get_records_sql($sql, $params);
6ff835b7 2914 }
2915
71a71d59 2916 function test_sql_concat() {
6ff835b7 2917 $DB = $this->tdb;
71a71d59 2918 $dbman = $DB->get_manager();
2919
2920 /// Testing all sort of values
2921 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2922 // string, some unicode chars
2923 $params = array('name', 'áéíóú', 'name3');
2924 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2925 // string, spaces and numbers
2926 $params = array('name', ' ', 12345);
2927 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2928 // float, empty and strings
2929 $params = array(123.45, '', 'test');
2930 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2931 // float, null and strings
2932 $params = array(123.45, null, 'test');
52a01626 2933 $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 2934
2935 /// Testing fieldnames + values
2936 $table = $this->get_test_table();
2937 $tablename = $table->getName();
2938
2939 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2940 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2941 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2942 $dbman->create_table($table);
71a71d59 2943
2944 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2945 $DB->insert_record($tablename, array('description'=>'dxxx'));
2946 $DB->insert_record($tablename, array('description'=>'bcde'));
2947
2948 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2949 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2950 $this->assertEqual(count($records), 3);
2951 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2952 }
2953
082ae821 2954 function test_concat_join() {
6ff835b7 2955 $DB = $this->tdb;
082ae821 2956 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2957 $params = array("name", "name2", "name3");
2958 $result = $DB->get_field_sql($sql, $params);
2959 $this->assertEqual("name name2 name3", $result);
2960 }
2961
2962 function test_sql_fullname() {
2963 $DB = $this->tdb;
2964 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2965 $params = array('first'=>'Firstname', 'last'=>'Surname');
2966 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2967 }
2968
2969 function sql_sql_order_by_text() {
2970 $DB = $this->tdb;
2971 $dbman = $DB->get_manager();
2972
9d833e93 2973 $table = $this->get_test_table();
3ff8bf26 2974 $tablename = $table->getName();
2975
f9ecb171 2976 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2977 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2978 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2979 $dbman->create_table($table);
082ae821 2980
3ff8bf26 2981 $DB->insert_record($tablename, array('description'=>'abcd'));
2982 $DB->insert_record($tablename, array('description'=>'dxxx'));
2983 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2984
40d3df5c 2985 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2986 $records = $DB->get_records_sql($sql);
258a66dc 2987 $first = array_shift($records);
082ae821 2988 $this->assertEqual(1, $first->id);
258a66dc 2989 $second = array_shift($records);
082ae821 2990 $this->assertEqual(3, $second->id);
258a66dc 2991 $last = array_shift($records);
082ae821 2992 $this->assertEqual(2, $last->id);
2993 }
2994
2995 function test_sql_substring() {
2996 $DB = $this->tdb;
2997 $dbman = $DB->get_manager();
2998
9d833e93 2999 $table = $this->get_test_table();
3ff8bf26 3000 $tablename = $table->getName();
3001
f9ecb171 3002 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3003 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 3004 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3005 $dbman->create_table($table);
082ae821 3006
3007 $string = 'abcdefghij';
3008
3ff8bf26 3009 $DB->insert_record($tablename, array('name'=>$string));
082ae821 3010
40d3df5c 3011 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {{$tablename}}";
082ae821 3012 $record = $DB->get_record_sql($sql);
3013 $this->assertEqual(substr($string, 5-1), $record->name);
3014
40d3df5c 3015 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {{$tablename}}";
082ae821 3016 $record = $DB->get_record_sql($sql);
3017 $this->assertEqual(substr($string, 5-1, 2), $record->name);
3018
3019 try {
3020 // silence php warning ;-)
3021 @$DB->sql_substr("name");
3022 $this->fail("Expecting an exception, none occurred");
3023 } catch (Exception $e) {
3024 $this->assertTrue($e instanceof coding_exception);
3025 }
6ff835b7 3026 }
3027
37d975e1 3028 function test_sql_length() {
3029 $DB = $this->tdb;
3030 $this->assertEqual($DB->get_field_sql(
3031 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
3032 $this->assertEqual($DB->get_field_sql(
3033 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
3034 }
3035
1d861fce 3036 function test_sql_position() {
3037 $DB = $this->tdb;
3038 $this->assertEqual($DB->get_field_sql(
6ff835b7 3039 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 3040 $this->assertEqual($DB->get_field_sql(
6ff835b7 3041 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
3042 }
3043
082ae821 3044 function test_sql_empty() {
3045 $DB = $this->tdb;
3046 $dbman = $DB->get_manager();
3047
9d833e93 3048 $table = $this->get_test_table();
3ff8bf26 3049 $tablename = $table->getName();
3050
f9ecb171 3051 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3052 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 3053 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');