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