2 // This file is part of Moodle - http://moodle.org/
4 // Moodle is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
9 // Moodle is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 // GNU General Public License for more details.
14 // You should have received a copy of the GNU General Public License
15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
22 * @copyright 2008 Nicolas Connault
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 defined('MOODLE_INTERNAL') || die();
28 class core_dml_testcase extends database_driver_testcase {
30 protected function setUp() {
32 $dbman = $this->tdb->get_manager(); // Loads DDL libs.
36 * Get a xmldb_table object for testing, deleting any existing table
37 * of the same name, for example if one was left over from a previous test
40 * @param string $suffix table name suffix, use if you need more test tables
41 * @return xmldb_table the table object.
43 private function get_test_table($suffix = '') {
44 $tablename = "test_table";
46 $tablename .= $suffix;
49 $table = new xmldb_table($tablename);
50 $table->setComment("This is a test'n drop table. You can drop it safely");
54 public function test_diagnose() {
56 $result = $DB->diagnose();
57 $this->assertNull($result, 'Database self diagnostics failed %s');
60 public function test_get_server_info() {
62 $result = $DB->get_server_info();
63 $this->assertInternalType('array', $result);
64 $this->assertArrayHasKey('description', $result);
65 $this->assertArrayHasKey('version', $result);
68 public function test_get_in_or_equal() {
71 // SQL_PARAMS_QM - IN or =.
73 // Correct usage of multiple values.
74 $in_values = array('value1', 'value2', '3', 4, null, false, true);
75 list($usql, $params) = $DB->get_in_or_equal($in_values);
76 $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql);
77 $this->assertEquals(count($in_values), count($params));
78 foreach ($params as $key => $value) {
79 $this->assertSame($in_values[$key], $value);
82 // Correct usage of single value (in an array).
83 $in_values = array('value1');
84 list($usql, $params) = $DB->get_in_or_equal($in_values);
85 $this->assertEquals("= ?", $usql);
86 $this->assertCount(1, $params);
87 $this->assertEquals($in_values[0], $params[0]);
89 // Correct usage of single value.
91 list($usql, $params) = $DB->get_in_or_equal($in_values);
92 $this->assertEquals("= ?", $usql);
93 $this->assertCount(1, $params);
94 $this->assertEquals($in_value, $params[0]);
96 // SQL_PARAMS_QM - NOT IN or <>.
98 // Correct usage of multiple values.
99 $in_values = array('value1', 'value2', 'value3', 'value4');
100 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
101 $this->assertEquals("NOT IN (?,?,?,?)", $usql);
102 $this->assertCount(4, $params);
103 foreach ($params as $key => $value) {
104 $this->assertEquals($in_values[$key], $value);
107 // Correct usage of single value (in array().
108 $in_values = array('value1');
109 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
110 $this->assertEquals("<> ?", $usql);
111 $this->assertCount(1, $params);
112 $this->assertEquals($in_values[0], $params[0]);
114 // Correct usage of single value.
115 $in_value = 'value1';
116 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
117 $this->assertEquals("<> ?", $usql);
118 $this->assertCount(1, $params);
119 $this->assertEquals($in_value, $params[0]);
121 // SQL_PARAMS_NAMED - IN or =.
123 // Correct usage of multiple values.
124 $in_values = array('value1', 'value2', 'value3', 'value4');
125 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
126 $this->assertCount(4, $params);
129 foreach ($params as $key => $value) {
130 $this->assertEquals(current($in_values), $value);
134 $this->assertEquals("IN (".implode(',', $ps).")", $usql);
136 // Correct usage of single values (in array).
137 $in_values = array('value1');
138 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
139 $this->assertCount(1, $params);
140 $value = reset($params);
142 $this->assertEquals("= :$key", $usql);
143 $this->assertEquals($in_value, $value);
145 // Correct usage of single value.
146 $in_value = 'value1';
147 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
148 $this->assertCount(1, $params);
149 $value = reset($params);
151 $this->assertEquals("= :$key", $usql);
152 $this->assertEquals($in_value, $value);
154 // SQL_PARAMS_NAMED - NOT IN or <>.
156 // Correct usage of multiple values.
157 $in_values = array('value1', 'value2', 'value3', 'value4');
158 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
159 $this->assertCount(4, $params);
162 foreach ($params as $key => $value) {
163 $this->assertEquals(current($in_values), $value);
167 $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql);
169 // Correct usage of single values (in array).
170 $in_values = array('value1');
171 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
172 $this->assertCount(1, $params);
173 $value = reset($params);
175 $this->assertEquals("<> :$key", $usql);
176 $this->assertEquals($in_value, $value);
178 // Correct usage of single value.
179 $in_value = 'value1';
180 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
181 $this->assertCount(1, $params);
182 $value = reset($params);
184 $this->assertEquals("<> :$key", $usql);
185 $this->assertEquals($in_value, $value);
187 // Make sure the param names are unique.
188 list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
189 list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param');
190 $params1 = array_keys($params1);
191 $params2 = array_keys($params2);
192 $common = array_intersect($params1, $params2);
193 $this->assertCount(0, $common);
195 // Some incorrect tests.
197 // Incorrect usage passing not-allowed params type.
198 $in_values = array(1, 2, 3);
200 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);
201 $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');
202 } catch (moodle_exception $e) {
203 $this->assertInstanceOf('dml_exception', $e);
204 $this->assertSame('typenotimplement', $e->errorcode);
207 // Incorrect usage passing empty array.
208 $in_values = array();
210 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
211 $this->fail('An Exception is missing, expected due to empty array of items');
212 } catch (moodle_exception $e) {
213 $this->assertInstanceOf('coding_exception', $e);
216 // Test using $onemptyitems.
218 // Correct usage passing empty array and $onemptyitems = null (equal = true, QM).
219 $in_values = array();
220 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null);
221 $this->assertSame(' IS NULL', $usql);
222 $this->assertSame(array(), $params);
224 // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED).
225 $in_values = array();
226 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null);
227 $this->assertSame(' IS NOT NULL', $usql);
228 $this->assertSame(array(), $params);
230 // Correct usage passing empty array and $onemptyitems = true (equal = true, QM).
231 $in_values = array();
232 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);
233 $this->assertSame('= ?', $usql);
234 $this->assertSame(array(true), $params);
236 // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED).
237 $in_values = array();
238 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);
239 $this->assertCount(1, $params);
240 $value = reset($params);
242 $this->assertSame('<> :'.$key, $usql);
243 $this->assertSame($value, true);
245 // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM).
246 $in_values = array();
247 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);
248 $this->assertSame('= ?', $usql);
249 $this->assertSame(array(-1), $params);
251 // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED).
252 $in_values = array();
253 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);
254 $this->assertCount(1, $params);
255 $value = reset($params);
257 $this->assertSame('<> :'.$key, $usql);
258 $this->assertSame($value, -1);
260 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM).
261 $in_values = array();
262 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');
263 $this->assertSame('= ?', $usql);
264 $this->assertSame(array('onevalue'), $params);
266 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED).
267 $in_values = array();
268 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');
269 $this->assertCount(1, $params);
270 $value = reset($params);
272 $this->assertSame('<> :'.$key, $usql);
273 $this->assertSame($value, 'onevalue');
276 public function test_fix_table_names() {
277 $DB = new moodle_database_for_testing();
278 $prefix = $DB->get_prefix();
280 // Simple placeholder.
281 $placeholder = "{user_123}";
282 $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder));
285 $placeholder = "{user-a}";
286 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
289 $placeholder = "{123user}";
290 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
293 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
294 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
295 $this->assertSame($expected, $DB->public_fix_table_names($sql));
298 public function test_fix_sql_params() {
300 $prefix = $DB->get_prefix();
302 $table = $this->get_test_table();
303 $tablename = $table->getName();
305 // Correct table placeholder substitution.
306 $sql = "SELECT * FROM {{$tablename}}";
307 $sqlarray = $DB->fix_sql_params($sql);
308 $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]);
310 // Conversions of all param types.
312 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2";
313 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?";
314 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2";
317 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
318 $params[SQL_PARAMS_QM] = array('first record', 1);
319 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
321 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
322 $this->assertSame($rsql, $sql[$rtype]);
323 $this->assertSame($rparams, $params[$rtype]);
325 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
326 $this->assertSame($rsql, $sql[$rtype]);
327 $this->assertSame($rparams, $params[$rtype]);
329 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
330 $this->assertSame($rsql, $sql[$rtype]);
331 $this->assertSame($rparams, $params[$rtype]);
333 // Malformed table placeholder.
334 $sql = "SELECT * FROM [testtable]";
335 $sqlarray = $DB->fix_sql_params($sql);
336 $this->assertSame($sql, $sqlarray[0]);
338 // Mixed param types (colon and dollar).
339 $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";
340 $params = array('param1' => 'record1', 'param2' => 3);
342 $DB->fix_sql_params($sql, $params);
343 $this->fail("Expecting an exception, none occurred");
344 } catch (moodle_exception $e) {
345 $this->assertInstanceOf('dml_exception', $e);
348 // Mixed param types (question and dollar).
349 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";
350 $params = array('param1' => 'record2', 'param2' => 5);
352 $DB->fix_sql_params($sql, $params);
353 $this->fail("Expecting an exception, none occurred");
354 } catch (moodle_exception $e) {
355 $this->assertInstanceOf('dml_exception', $e);
358 // Too few params in sql.
359 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";
360 $params = array('record2', 3);
362 $DB->fix_sql_params($sql, $params);
363 $this->fail("Expecting an exception, none occurred");
364 } catch (moodle_exception $e) {
365 $this->assertInstanceOf('dml_exception', $e);
368 // Too many params in array: no error, just use what is necessary.
371 $sqlarray = $DB->fix_sql_params($sql, $params);
372 $this->assertInternalType('array', $sqlarray);
373 $this->assertCount(3, $sqlarray[1]);
375 // Named params missing from array.
376 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
377 $params = array('wrongname' => 'record1', 'course' => 1);
379 $DB->fix_sql_params($sql, $params);
380 $this->fail("Expecting an exception, none occurred");
381 } catch (moodle_exception $e) {
382 $this->assertInstanceOf('dml_exception', $e);
385 // Duplicate named param in query - this is a very important feature!!
386 // it helps with debugging of sloppy code.
387 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";
388 $params = array('name' => 'record2', 'course' => 3);
390 $DB->fix_sql_params($sql, $params);
391 $this->fail("Expecting an exception, none occurred");
392 } catch (moodle_exception $e) {
393 $this->assertInstanceOf('dml_exception', $e);
396 // Extra named param is ignored.
397 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
398 $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');
399 $sqlarray = $DB->fix_sql_params($sql, $params);
400 $this->assertInternalType('array', $sqlarray);
401 $this->assertCount(2, $sqlarray[1]);
403 // Params exceeding 30 chars length.
404 $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
405 $params = array('long_placeholder_with_more_than_30' => 'record1');
407 $DB->fix_sql_params($sql, $params);
408 $this->fail("Expecting an exception, none occurred");
409 } catch (moodle_exception $e) {
410 $this->assertInstanceOf('coding_exception', $e);
413 // Booleans in NAMED params are casting to 1/0 int.
414 $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";
415 $params = array(true, false);
416 list($sql, $params) = $DB->fix_sql_params($sql, $params);
417 $this->assertTrue(reset($params) === 1);
418 $this->assertTrue(next($params) === 0);
420 // Booleans in QM params are casting to 1/0 int.
421 $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";
422 $params = array('course1' => true, 'course2' => false);
423 list($sql, $params) = $DB->fix_sql_params($sql, $params);
424 $this->assertTrue(reset($params) === 1);
425 $this->assertTrue(next($params) === 0);
427 // Booleans in DOLLAR params are casting to 1/0 int.
428 $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";
429 $params = array(true, false);
430 list($sql, $params) = $DB->fix_sql_params($sql, $params);
431 $this->assertTrue(reset($params) === 1);
432 $this->assertTrue(next($params) === 0);
434 // No data types are touched except bool.
435 $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";
436 $inparams = array('abc', 'ABC', null, '1', 1, 1.4);
437 list($sql, $params) = $DB->fix_sql_params($sql, $inparams);
438 $this->assertSame(array_values($params), array_values($inparams));
441 public function test_strtok() {
442 // Strtok was previously used by bound emulation, make sure it is not used any more.
444 $dbman = $this->tdb->get_manager();
446 $table = $this->get_test_table();
447 $tablename = $table->getName();
449 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
450 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
451 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
452 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
453 $dbman->create_table($table);
456 $this->assertSame(strtok($str, '?'), 'a');
458 $DB->get_records($tablename, array('id'=>1));
460 $this->assertSame(strtok('?'), 'b');
463 public function test_tweak_param_names() {
464 // Note the tweak_param_names() method is only available in the oracle driver,
465 // hence we look for expected results indirectly, by testing various DML methods.
466 // with some "extreme" conditions causing the tweak to happen.
468 $dbman = $this->tdb->get_manager();
470 $table = $this->get_test_table();
471 $tablename = $table->getName();
473 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
474 // Add some columns with 28 chars in the name.
475 $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
476 $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
477 $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
478 // Add some columns with 30 chars in the name.
479 $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
480 $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
481 $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');
483 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
485 $dbman->create_table($table);
487 $this->assertTrue($dbman->table_exists($tablename));
489 // Test insert record.
490 $rec1 = new stdClass();
491 $rec1->long_int_columnname_with_28c = 28;
492 $rec1->long_dec_columnname_with_28c = 28.28;
493 $rec1->long_str_columnname_with_28c = '28';
494 $rec1->long_int_columnname_with_30cxx = 30;
495 $rec1->long_dec_columnname_with_30cxx = 30.30;
496 $rec1->long_str_columnname_with_30cxx = '30';
499 $rec1->id = $DB->insert_record($tablename, $rec1);
500 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
503 $DB->update_record($tablename, $rec1);
504 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
507 $rec1->long_int_columnname_with_28c = 280;
508 $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
509 array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
510 $rec1->long_dec_columnname_with_28c = 280.28;
511 $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
512 array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
513 $rec1->long_str_columnname_with_28c = '280';
514 $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
515 array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
516 $rec1->long_int_columnname_with_30cxx = 300;
517 $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
518 array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
519 $rec1->long_dec_columnname_with_30cxx = 300.30;
520 $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
521 array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
522 $rec1->long_str_columnname_with_30cxx = '300';
523 $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
524 array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
525 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
528 $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
529 $rec2->id = $DB->insert_record($tablename, $rec2);
530 $this->assertEquals(2, $DB->count_records($tablename));
531 $DB->delete_records($tablename, (array) $rec2);
532 $this->assertEquals(1, $DB->count_records($tablename));
535 $rs = $DB->get_recordset($tablename, (array) $rec1);
537 foreach ($rs as $rec2) {
541 $this->assertEquals(1, $iterations);
542 $this->assertEquals($rec1, $rec2);
545 $recs = $DB->get_records($tablename, (array) $rec1);
546 $this->assertCount(1, $recs);
547 $this->assertEquals($rec1, reset($recs));
549 // Get_fieldset_select().
550 $select = 'id = :id AND
551 long_int_columnname_with_28c = :long_int_columnname_with_28c AND
552 long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
553 long_str_columnname_with_28c = :long_str_columnname_with_28c AND
554 long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
555 long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
556 long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
557 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
558 $this->assertCount(1, $fields);
559 $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));
560 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
561 $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));
562 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
563 $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields));
564 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
565 $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields));
566 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
567 $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields));
568 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
569 $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));
571 // Overlapping placeholders (progressive str_replace).
572 $overlapselect = 'id = :p AND
573 long_int_columnname_with_28c = :param1 AND
574 long_dec_columnname_with_28c = :param2 AND
575 long_str_columnname_with_28c = :param_with_29_characters_long AND
576 long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
577 long_dec_columnname_with_30cxx = :param_ AND
578 long_str_columnname_with_30cxx = :param__';
579 $overlapparams = array(
581 'param1' => $rec1->long_int_columnname_with_28c,
582 'param2' => $rec1->long_dec_columnname_with_28c,
583 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
584 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
585 'param_' => $rec1->long_dec_columnname_with_30cxx,
586 'param__' => $rec1->long_str_columnname_with_30cxx);
587 $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
588 $this->assertCount(1, $recs);
589 $this->assertEquals($rec1, reset($recs));
592 $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
593 $this->assertEquals(0, $DB->count_records($tablename));
596 public function test_get_tables() {
598 $dbman = $this->tdb->get_manager();
600 // Need to test with multiple DBs.
601 $table = $this->get_test_table();
602 $tablename = $table->getName();
604 $original_count = count($DB->get_tables());
606 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
607 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
609 $dbman->create_table($table);
610 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
612 $dbman->drop_table($table);
613 $this->assertTrue(count($DB->get_tables()) == $original_count);
616 public function test_get_indexes() {
618 $dbman = $this->tdb->get_manager();
620 $table = $this->get_test_table();
621 $tablename = $table->getName();
623 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
624 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
625 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
626 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
627 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
628 $dbman->create_table($table);
630 $indices = $DB->get_indexes($tablename);
631 $this->assertInternalType('array', $indices);
632 $this->assertCount(2, $indices);
633 // We do not care about index names for now.
634 $first = array_shift($indices);
635 $second = array_shift($indices);
636 if (count($first['columns']) == 2) {
643 $this->assertFalse($single['unique']);
644 $this->assertTrue($composed['unique']);
645 $this->assertCount(1, $single['columns']);
646 $this->assertCount(2, $composed['columns']);
647 $this->assertSame('course', $single['columns'][0]);
648 $this->assertSame('course', $composed['columns'][0]);
649 $this->assertSame('id', $composed['columns'][1]);
652 public function test_get_columns() {
654 $dbman = $this->tdb->get_manager();
656 $table = $this->get_test_table();
657 $tablename = $table->getName();
659 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
660 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
661 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
662 $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
663 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
664 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
665 $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300);
666 $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);
667 $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');
668 $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');
669 $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');
670 $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0');
671 $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0');
672 $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0');
673 $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0');
674 $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0');
675 $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0');
676 $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0');
677 $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0');
678 $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0');
679 $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
680 $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0');
681 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
682 $dbman->create_table($table);
684 $columns = $DB->get_columns($tablename);
685 $this->assertInternalType('array', $columns);
687 $fields = $table->getFields();
688 $this->assertCount(count($columns), $fields);
690 $field = $columns['id'];
691 $this->assertSame('R', $field->meta_type);
692 $this->assertTrue($field->auto_increment);
693 $this->assertTrue($field->unique);
695 $field = $columns['course'];
696 $this->assertSame('I', $field->meta_type);
697 $this->assertFalse($field->auto_increment);
698 $this->assertTrue($field->has_default);
699 $this->assertEquals(0, $field->default_value);
700 $this->assertTrue($field->not_null);
702 for ($i=1; $i<=10; $i++) {
703 $field = $columns['someint'.$i];
704 $this->assertSame('I', $field->meta_type);
705 $this->assertGreaterThanOrEqual($i, $field->max_length);
707 $field = $columns['someint18'];
708 $this->assertSame('I', $field->meta_type);
709 $this->assertGreaterThanOrEqual(18, $field->max_length);
711 $field = $columns['name'];
712 $this->assertSame('C', $field->meta_type);
713 $this->assertFalse($field->auto_increment);
714 $this->assertEquals(255, $field->max_length);
715 $this->assertTrue($field->has_default);
716 $this->assertSame('lala', $field->default_value);
717 $this->assertFalse($field->not_null);
719 $field = $columns['description'];
720 $this->assertSame('X', $field->meta_type);
721 $this->assertFalse($field->auto_increment);
722 $this->assertFalse($field->has_default);
723 $this->assertNull($field->default_value);
724 $this->assertFalse($field->not_null);
726 $field = $columns['enumfield'];
727 $this->assertSame('C', $field->meta_type);
728 $this->assertFalse($field->auto_increment);
729 $this->assertSame('test2', $field->default_value);
730 $this->assertTrue($field->not_null);
732 $field = $columns['onenum'];
733 $this->assertSame('N', $field->meta_type);
734 $this->assertFalse($field->auto_increment);
735 $this->assertEquals(10, $field->max_length);
736 $this->assertEquals(2, $field->scale);
737 $this->assertTrue($field->has_default);
738 $this->assertEquals(200.0, $field->default_value);
739 $this->assertFalse($field->not_null);
741 $field = $columns['onefloat'];
742 $this->assertSame('N', $field->meta_type);
743 $this->assertFalse($field->auto_increment);
744 $this->assertTrue($field->has_default);
745 $this->assertEquals(300.0, $field->default_value);
746 $this->assertFalse($field->not_null);
748 $field = $columns['anotherfloat'];
749 $this->assertSame('N', $field->meta_type);
750 $this->assertFalse($field->auto_increment);
751 $this->assertTrue($field->has_default);
752 $this->assertEquals(400.0, $field->default_value);
753 $this->assertFalse($field->not_null);
755 // Test negative defaults in numerical columns.
756 $field = $columns['negativedfltint'];
757 $this->assertTrue($field->has_default);
758 $this->assertEquals(-1, $field->default_value);
760 $field = $columns['negativedfltnumber'];
761 $this->assertTrue($field->has_default);
762 $this->assertEquals(-2, $field->default_value);
764 $field = $columns['negativedfltfloat'];
765 $this->assertTrue($field->has_default);
766 $this->assertEquals(-3, $field->default_value);
768 for ($i = 0; $i < count($columns); $i++) {
770 $next_column = reset($columns);
771 $next_field = reset($fields);
773 $next_column = next($columns);
774 $next_field = next($fields);
777 $this->assertEquals($next_column->name, $next_field->getName());
780 // Test get_columns for non-existing table returns empty array. MDL-30147.
781 $columns = $DB->get_columns('xxxx');
782 $this->assertEquals(array(), $columns);
784 // Create something similar to "context_temp" with id column without sequence.
785 $dbman->drop_table($table);
786 $table = $this->get_test_table();
787 $tablename = $table->getName();
788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
791 $dbman->create_table($table);
793 $columns = $DB->get_columns($tablename);
794 $this->assertFalse($columns['id']->auto_increment);
797 public function test_get_manager() {
799 $dbman = $this->tdb->get_manager();
801 $this->assertInstanceOf('database_manager', $dbman);
804 public function test_setup_is_unicodedb() {
806 $this->assertTrue($DB->setup_is_unicodedb());
809 public function test_set_debug() { // Tests get_debug() too.
811 $dbman = $this->tdb->get_manager();
813 $table = $this->get_test_table();
814 $tablename = $table->getName();
816 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
817 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
818 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
819 $dbman->create_table($table);
821 $sql = "SELECT * FROM {{$tablename}}";
823 $prevdebug = $DB->get_debug();
826 $DB->set_debug(true);
827 $this->assertTrue($DB->get_debug());
829 $DB->set_debug(false);
830 $this->assertFalse($DB->get_debug());
831 $debuginfo = ob_get_contents();
833 $this->assertFalse($debuginfo === '');
837 $debuginfo = ob_get_contents();
839 $this->assertTrue($debuginfo === '');
841 $DB->set_debug($prevdebug);
844 public function test_execute() {
846 $dbman = $this->tdb->get_manager();
848 $table1 = $this->get_test_table('1');
849 $tablename1 = $table1->getName();
850 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
851 $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
852 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
853 $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
854 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
855 $dbman->create_table($table1);
857 $table2 = $this->get_test_table('2');
858 $tablename2 = $table2->getName();
859 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
860 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
861 $table2->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
862 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
863 $dbman->create_table($table2);
865 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));
866 $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));
867 $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));
868 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));
870 // Select results are ignored.
871 $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";
872 $this->assertTrue($DB->execute($sql, array('course'=>3)));
874 // Throw exception on error.
875 $sql = "XXUPDATE SET XSSD";
878 $this->fail("Expecting an exception, none occurred");
879 } catch (moodle_exception $e) {
880 $this->assertInstanceOf('dml_exception', $e);
884 $sql = "UPDATE {{$tablename1}}
887 $this->assertTrue($DB->execute($sql, array('3')));
888 $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6)));
890 // Update records with subquery condition.
891 // Confirm that the option not using table aliases is cross-db.
892 $sql = "UPDATE {{$tablename1}}
896 FROM {{$tablename2}} tbl2
897 WHERE tbl2.course = {{$tablename1}}.course
898 AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed.
899 $this->assertTrue($DB->execute($sql));
901 // Insert from one into second table.
902 $sql = "INSERT INTO {{$tablename2}} (course)
905 FROM {{$tablename1}}";
906 $this->assertTrue($DB->execute($sql));
907 $this->assertEquals(4, $DB->count_records($tablename2));
909 // Insert a TEXT with raw SQL, binding TEXT params.
911 $onetext = file_get_contents(__DIR__ . '/fixtures/clob.txt');
912 $sql = "INSERT INTO {{$tablename2}} (course, onetext)
913 VALUES (:course, :onetext)";
914 $DB->execute($sql, array('course' => $course, 'onetext' => $onetext));
915 $records = $DB->get_records($tablename2, array('course' => $course));
916 $this->assertCount(1, $records);
917 $record = reset($records);
918 $this->assertSame($onetext, $record->onetext);
920 // Update a TEXT with raw SQL, binding TEXT params.
922 $newonetext = file_get_contents(__DIR__ . '/fixtures/clob.txt') . '- updated';
923 $sql = "UPDATE {{$tablename2}} SET course = :newcourse, onetext = :newonetext
924 WHERE course = :oldcourse";
925 $DB->execute($sql, array('oldcourse' => $course, 'newcourse' => $newcourse, 'newonetext' => $newonetext));
926 $records = $DB->get_records($tablename2, array('course' => $course));
927 $this->assertCount(0, $records);
928 $records = $DB->get_records($tablename2, array('course' => $newcourse));
929 $this->assertCount(1, $records);
930 $record = reset($records);
931 $this->assertSame($newonetext, $record->onetext);
934 public function test_get_recordset() {
936 $dbman = $DB->get_manager();
938 $table = $this->get_test_table();
939 $tablename = $table->getName();
941 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
942 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
943 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
944 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
945 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
946 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
947 $dbman->create_table($table);
949 $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'),
950 array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),
951 array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));
953 foreach ($data as $key => $record) {
954 $data[$key]['id'] = $DB->insert_record($tablename, $record);
957 // Standard recordset iteration.
958 $rs = $DB->get_recordset($tablename);
959 $this->assertInstanceOf('moodle_recordset', $rs);
961 foreach ($rs as $record) {
962 $data_record = current($data);
963 foreach ($record as $k => $v) {
964 $this->assertEquals($data_record[$k], $v);
970 // Iterator style usage.
971 $rs = $DB->get_recordset($tablename);
972 $this->assertInstanceOf('moodle_recordset', $rs);
974 while ($rs->valid()) {
975 $record = $rs->current();
976 $data_record = current($data);
977 foreach ($record as $k => $v) {
978 $this->assertEquals($data_record[$k], $v);
985 // Make sure rewind is ignored.
986 $rs = $DB->get_recordset($tablename);
987 $this->assertInstanceOf('moodle_recordset', $rs);
990 foreach ($rs as $record) {
994 $this->fail('revind not ignored in recordsets');
997 $data_record = current($data);
998 foreach ($record as $k => $v) {
999 $this->assertEquals($data_record[$k], $v);
1005 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
1006 $conditions = array('onetext' => '1');
1008 $rs = $DB->get_recordset($tablename, $conditions);
1009 $this->fail('An Exception is missing, expected due to equating of text fields');
1010 } catch (moodle_exception $e) {
1011 $this->assertInstanceOf('dml_exception', $e);
1012 $this->assertSame('textconditionsnotallowed', $e->errorcode);
1015 // Test nested iteration.
1016 $rs1 = $DB->get_recordset($tablename);
1018 foreach ($rs1 as $record1) {
1019 $rs2 = $DB->get_recordset($tablename);
1022 foreach ($rs2 as $record2) {
1026 $this->assertCount($j, $data);
1029 $this->assertCount($i, $data);
1032 // * limits are tested in test_get_recordset_sql()
1033 // * where_clause() is used internally and is tested in test_get_records()
1036 public function test_get_recordset_static() {
1038 $dbman = $DB->get_manager();
1040 $table = $this->get_test_table();
1041 $tablename = $table->getName();
1043 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1044 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1045 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1046 $dbman->create_table($table);
1048 $DB->insert_record($tablename, array('course' => 1));
1049 $DB->insert_record($tablename, array('course' => 2));
1050 $DB->insert_record($tablename, array('course' => 3));
1051 $DB->insert_record($tablename, array('course' => 4));
1053 $rs = $DB->get_recordset($tablename, array(), 'id');
1055 $DB->set_field($tablename, 'course', 666, array('course'=>1));
1056 $DB->delete_records($tablename, array('course'=>2));
1059 foreach ($rs as $record) {
1061 $this->assertEquals($i, $record->course);
1064 $this->assertEquals(4, $i);
1066 // Now repeat with limits because it may use different code.
1067 $DB->delete_records($tablename, array());
1069 $DB->insert_record($tablename, array('course' => 1));
1070 $DB->insert_record($tablename, array('course' => 2));
1071 $DB->insert_record($tablename, array('course' => 3));
1072 $DB->insert_record($tablename, array('course' => 4));
1074 $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3);
1076 $DB->set_field($tablename, 'course', 666, array('course'=>1));
1077 $DB->delete_records($tablename, array('course'=>2));
1080 foreach ($rs as $record) {
1082 $this->assertEquals($i, $record->course);
1085 $this->assertEquals(3, $i);
1088 public function test_get_recordset_iterator_keys() {
1090 $dbman = $DB->get_manager();
1092 $table = $this->get_test_table();
1093 $tablename = $table->getName();
1095 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1096 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1097 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1098 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1099 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1100 $dbman->create_table($table);
1102 $data = array(array('course' => 3, 'name' => 'record1'),
1103 array('course' => 3, 'name' => 'record2'),
1104 array('course' => 5, 'name' => 'record3'));
1105 foreach ($data as $key => $record) {
1106 $data[$key]['id'] = $DB->insert_record($tablename, $record);
1109 // Test repeated numeric keys are returned ok.
1110 $rs = $DB->get_recordset($tablename, null, null, 'course, name, id');
1114 foreach ($rs as $key => $record) {
1115 $data_record = current($data);
1116 $this->assertEquals($data_record['course'], $key);
1121 $this->assertEquals(3, $count);
1123 // Test string keys are returned ok.
1124 $rs = $DB->get_recordset($tablename, null, null, 'name, course, id');
1128 foreach ($rs as $key => $record) {
1129 $data_record = current($data);
1130 $this->assertEquals($data_record['name'], $key);
1135 $this->assertEquals(3, $count);
1137 // Test numeric not starting in 1 keys are returned ok.
1138 $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name');
1140 $data = array_reverse($data);
1143 foreach ($rs as $key => $record) {
1144 $data_record = current($data);
1145 $this->assertEquals($data_record['id'], $key);
1150 $this->assertEquals(3, $count);
1153 public function test_get_recordset_list() {
1155 $dbman = $DB->get_manager();
1157 $table = $this->get_test_table();
1158 $tablename = $table->getName();
1160 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1161 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0');
1162 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1163 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1164 $dbman->create_table($table);
1166 $DB->insert_record($tablename, array('course' => 3));
1167 $DB->insert_record($tablename, array('course' => 3));
1168 $DB->insert_record($tablename, array('course' => 5));
1169 $DB->insert_record($tablename, array('course' => 2));
1170 $DB->insert_record($tablename, array('course' => null));
1171 $DB->insert_record($tablename, array('course' => 1));
1172 $DB->insert_record($tablename, array('course' => 0));
1174 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
1176 foreach ($rs as $record) {
1179 $this->assertEquals(3, $counter);
1182 $rs = $DB->get_recordset_list($tablename, 'course', array(3));
1184 foreach ($rs as $record) {
1187 $this->assertEquals(2, $counter);
1190 $rs = $DB->get_recordset_list($tablename, 'course', array(null));
1192 foreach ($rs as $record) {
1195 $this->assertEquals(1, $counter);
1198 $rs = $DB->get_recordset_list($tablename, 'course', array(6, null));
1200 foreach ($rs as $record) {
1203 $this->assertEquals(1, $counter);
1206 $rs = $DB->get_recordset_list($tablename, 'course', array(null, 5, 5, 5));
1208 foreach ($rs as $record) {
1211 $this->assertEquals(2, $counter);
1214 $rs = $DB->get_recordset_list($tablename, 'course', array(true));
1216 foreach ($rs as $record) {
1219 $this->assertEquals(1, $counter);
1222 $rs = $DB->get_recordset_list($tablename, 'course', array(false));
1224 foreach ($rs as $record) {
1227 $this->assertEquals(1, $counter);
1230 $rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645.
1233 foreach ($rs as $record) {
1237 $this->assertEquals(0, $counter);
1240 // * limits are tested in test_get_recordset_sql()
1241 // * where_clause() is used internally and is tested in test_get_records()
1244 public function test_get_recordset_select() {
1246 $dbman = $DB->get_manager();
1248 $table = $this->get_test_table();
1249 $tablename = $table->getName();
1251 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1252 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1253 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1254 $dbman->create_table($table);
1256 $DB->insert_record($tablename, array('course' => 3));
1257 $DB->insert_record($tablename, array('course' => 3));
1258 $DB->insert_record($tablename, array('course' => 5));
1259 $DB->insert_record($tablename, array('course' => 2));
1261 $rs = $DB->get_recordset_select($tablename, '');
1263 foreach ($rs as $record) {
1267 $this->assertEquals(4, $counter);
1269 $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3'));
1271 foreach ($rs as $record) {
1275 $this->assertEquals(2, $counter);
1278 // * limits are tested in test_get_recordset_sql()
1281 public function test_get_recordset_sql() {
1283 $dbman = $DB->get_manager();
1285 $table = $this->get_test_table();
1286 $tablename = $table->getName();
1288 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1289 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1290 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1291 $dbman->create_table($table);
1293 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1294 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1295 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1296 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1297 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1298 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1299 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1301 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1303 foreach ($rs as $record) {
1307 $this->assertEquals(2, $counter);
1309 // Limits - only need to test this case, the rest have been tested by test_get_records_sql()
1310 // only limitfrom = skips that number of records.
1311 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1313 foreach ($rs as $key => $record) {
1314 $records[$key] = $record;
1317 $this->assertCount(5, $records);
1318 $this->assertEquals($inskey3, reset($records)->id);
1319 $this->assertEquals($inskey7, end($records)->id);
1321 // Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here.
1324 public function test_export_table_recordset() {
1326 $dbman = $DB->get_manager();
1328 $table = $this->get_test_table();
1329 $tablename = $table->getName();
1331 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1332 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1333 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1334 $dbman->create_table($table);
1337 $ids[] = $DB->insert_record($tablename, array('course' => 3));
1338 $ids[] = $DB->insert_record($tablename, array('course' => 5));
1339 $ids[] = $DB->insert_record($tablename, array('course' => 4));
1340 $ids[] = $DB->insert_record($tablename, array('course' => 3));
1341 $ids[] = $DB->insert_record($tablename, array('course' => 2));
1342 $ids[] = $DB->insert_record($tablename, array('course' => 1));
1343 $ids[] = $DB->insert_record($tablename, array('course' => 0));
1345 $rs = $DB->export_table_recordset($tablename);
1347 foreach ($rs as $record) {
1348 $rids[] = $record->id;
1351 $this->assertEquals($ids, $rids, '', 0, 0, true);
1354 public function test_get_records() {
1356 $dbman = $DB->get_manager();
1358 $table = $this->get_test_table();
1359 $tablename = $table->getName();
1361 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1362 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1363 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1364 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1365 $dbman->create_table($table);
1367 $DB->insert_record($tablename, array('course' => 3));
1368 $DB->insert_record($tablename, array('course' => 3));
1369 $DB->insert_record($tablename, array('course' => 5));
1370 $DB->insert_record($tablename, array('course' => 2));
1373 $records = $DB->get_records($tablename);
1374 $this->assertCount(4, $records);
1375 $this->assertEquals(3, $records[1]->course);
1376 $this->assertEquals(3, $records[2]->course);
1377 $this->assertEquals(5, $records[3]->course);
1378 $this->assertEquals(2, $records[4]->course);
1380 // Records matching certain conditions.
1381 $records = $DB->get_records($tablename, array('course' => 3));
1382 $this->assertCount(2, $records);
1383 $this->assertEquals(3, $records[1]->course);
1384 $this->assertEquals(3, $records[2]->course);
1386 // All records sorted by course.
1387 $records = $DB->get_records($tablename, null, 'course');
1388 $this->assertCount(4, $records);
1389 $current_record = reset($records);
1390 $this->assertEquals(4, $current_record->id);
1391 $current_record = next($records);
1392 $this->assertEquals(1, $current_record->id);
1393 $current_record = next($records);
1394 $this->assertEquals(2, $current_record->id);
1395 $current_record = next($records);
1396 $this->assertEquals(3, $current_record->id);
1398 // All records, but get only one field.
1399 $records = $DB->get_records($tablename, null, '', 'id');
1400 $this->assertFalse(isset($records[1]->course));
1401 $this->assertTrue(isset($records[1]->id));
1402 $this->assertCount(4, $records);
1404 // Booleans into params.
1405 $records = $DB->get_records($tablename, array('course' => true));
1406 $this->assertCount(0, $records);
1407 $records = $DB->get_records($tablename, array('course' => false));
1408 $this->assertCount(0, $records);
1410 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
1411 $conditions = array('onetext' => '1');
1413 $records = $DB->get_records($tablename, $conditions);
1415 // Only in debug mode - hopefully all devs test code in debug mode...
1416 $this->fail('An Exception is missing, expected due to equating of text fields');
1418 } catch (moodle_exception $e) {
1419 $this->assertInstanceOf('dml_exception', $e);
1420 $this->assertSame('textconditionsnotallowed', $e->errorcode);
1423 // Test get_records passing non-existing table.
1426 $records = $DB->get_records('xxxx', array('id' => 0));
1427 $this->fail('An Exception is missing, expected due to query against non-existing table');
1428 } catch (moodle_exception $e) {
1429 $this->assertInstanceOf('dml_exception', $e);
1431 // Information for developers only, normal users get general error message.
1432 $this->assertSame('ddltablenotexist', $e->errorcode);
1437 $records = $DB->get_records('xxxx', array('id' => '1'));
1438 $this->fail('An Exception is missing, expected due to query against non-existing table');
1439 } catch (moodle_exception $e) {
1440 $this->assertInstanceOf('dml_exception', $e);
1442 // Information for developers only, normal users get general error message.
1443 $this->assertSame('ddltablenotexist', $e->errorcode);
1447 // Test get_records passing non-existing column.
1449 $records = $DB->get_records($tablename, array('xxxx' => 0));
1450 $this->fail('An Exception is missing, expected due to query against non-existing column');
1451 } catch (moodle_exception $e) {
1452 $this->assertInstanceOf('dml_exception', $e);
1454 // Information for developers only, normal users get general error message.
1455 $this->assertSame('ddlfieldnotexist', $e->errorcode);
1459 // Note: delegate limits testing to test_get_records_sql().
1462 public function test_get_records_list() {
1464 $dbman = $DB->get_manager();
1466 $table = $this->get_test_table();
1467 $tablename = $table->getName();
1469 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1470 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1471 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1472 $dbman->create_table($table);
1474 $DB->insert_record($tablename, array('course' => 3));
1475 $DB->insert_record($tablename, array('course' => 3));
1476 $DB->insert_record($tablename, array('course' => 5));
1477 $DB->insert_record($tablename, array('course' => 2));
1479 $records = $DB->get_records_list($tablename, 'course', array(3, 2));
1480 $this->assertInternalType('array', $records);
1481 $this->assertCount(3, $records);
1482 $this->assertEquals(1, reset($records)->id);
1483 $this->assertEquals(2, next($records)->id);
1484 $this->assertEquals(4, next($records)->id);
1486 $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645.
1487 $this->assertCount(0, $records);
1489 // Note: delegate limits testing to test_get_records_sql().
1492 public function test_get_records_sql() {
1494 $dbman = $DB->get_manager();
1496 $table = $this->get_test_table();
1497 $tablename = $table->getName();
1499 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1500 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1501 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1502 $dbman->create_table($table);
1504 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1505 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1506 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1507 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1508 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1509 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1510 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1512 $table2 = $this->get_test_table("2");
1513 $tablename2 = $table2->getName();
1514 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1515 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1516 $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
1517 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1518 $dbman->create_table($table2);
1520 $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing'));
1521 $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang'));
1522 $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung'));
1523 $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));
1525 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1526 $this->assertCount(2, $records);
1527 $this->assertEquals($inskey1, reset($records)->id);
1528 $this->assertEquals($inskey4, next($records)->id);
1530 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test.
1531 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1532 $this->assertDebuggingCalled();
1533 $this->assertCount(6, $records);
1534 set_debugging(DEBUG_MINIMAL);
1535 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1536 $this->assertDebuggingNotCalled();
1537 $this->assertCount(6, $records);
1538 set_debugging(DEBUG_DEVELOPER);
1540 // Negative limits = no limits.
1541 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
1542 $this->assertCount(7, $records);
1544 // Zero limits = no limits.
1545 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
1546 $this->assertCount(7, $records);
1548 // Only limitfrom = skips that number of records.
1549 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1550 $this->assertCount(5, $records);
1551 $this->assertEquals($inskey3, reset($records)->id);
1552 $this->assertEquals($inskey7, end($records)->id);
1554 // Only limitnum = fetches that number of records.
1555 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
1556 $this->assertCount(3, $records);
1557 $this->assertEquals($inskey1, reset($records)->id);
1558 $this->assertEquals($inskey3, end($records)->id);
1560 // Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones.
1561 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
1562 $this->assertCount(2, $records);
1563 $this->assertEquals($inskey4, reset($records)->id);
1564 $this->assertEquals($inskey5, end($records)->id);
1566 // Both limitfrom and limitnum in query having subqueris.
1567 // Note the subquery skips records with course = 0 and 3.
1568 $sql = "SELECT * FROM {{$tablename}}
1569 WHERE course NOT IN (
1570 SELECT course FROM {{$tablename}}
1571 WHERE course IN (0, 3))
1573 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2.
1574 $this->assertCount(2, $records);
1575 $this->assertEquals($inskey6, reset($records)->id);
1576 $this->assertEquals($inskey5, end($records)->id);
1577 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2.
1578 $this->assertCount(2, $records);
1579 $this->assertEquals($inskey3, reset($records)->id);
1580 $this->assertEquals($inskey2, end($records)->id);
1582 // Test 2 tables with aliases and limits with order bys.
1583 $sql = "SELECT t1.id, t1.course AS cid, t2.nametext
1584 FROM {{$tablename}} t1, {{$tablename2}} t2
1585 WHERE t2.course=t1.course
1586 ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');
1587 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5.
1588 $this->assertCount(2, $records);
1589 $this->assertSame('5', end($records)->cid);
1590 $this->assertSame('4', reset($records)->cid);
1592 // Test 2 tables with aliases and limits with the highest INT limit works.
1593 $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}.
1594 $this->assertCount(2, $records);
1595 $this->assertSame('5', end($records)->cid);
1596 $this->assertSame('4', reset($records)->cid);
1598 // Test 2 tables with aliases and limits with order bys (limit which is highest INT number).
1599 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses.
1600 $this->assertCount(0, $records);
1602 // Test 2 tables with aliases and limits with order bys (limit which s highest INT number).
1603 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses.
1604 $this->assertCount(0, $records);
1606 // TODO: Test limits in queries having DISTINCT clauses.
1608 // Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here.
1611 public function test_get_records_menu() {
1613 $dbman = $DB->get_manager();
1615 $table = $this->get_test_table();
1616 $tablename = $table->getName();
1618 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1619 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1620 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1621 $dbman->create_table($table);
1623 $DB->insert_record($tablename, array('course' => 3));
1624 $DB->insert_record($tablename, array('course' => 3));
1625 $DB->insert_record($tablename, array('course' => 5));
1626 $DB->insert_record($tablename, array('course' => 2));
1628 $records = $DB->get_records_menu($tablename, array('course' => 3));
1629 $this->assertInternalType('array', $records);
1630 $this->assertCount(2, $records);
1631 $this->assertNotEmpty($records[1]);
1632 $this->assertNotEmpty($records[2]);
1633 $this->assertEquals(3, $records[1]);
1634 $this->assertEquals(3, $records[2]);
1636 // Note: delegate limits testing to test_get_records_sql().
1639 public function test_get_records_select_menu() {
1641 $dbman = $DB->get_manager();
1643 $table = $this->get_test_table();
1644 $tablename = $table->getName();
1646 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1647 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1648 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1649 $dbman->create_table($table);
1651 $DB->insert_record($tablename, array('course' => 3));
1652 $DB->insert_record($tablename, array('course' => 2));
1653 $DB->insert_record($tablename, array('course' => 3));
1654 $DB->insert_record($tablename, array('course' => 5));
1656 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
1657 $this->assertInternalType('array', $records);
1659 $this->assertCount(3, $records);
1660 $this->assertArrayHasKey(1, $records);
1661 $this->assertArrayNotHasKey(2, $records);
1662 $this->assertArrayHasKey(3, $records);
1663 $this->assertArrayHasKey(4, $records);
1664 $this->assertSame('3', $records[1]);
1665 $this->assertSame('3', $records[3]);
1666 $this->assertSame('5', $records[4]);
1668 // Note: delegate limits testing to test_get_records_sql().
1671 public function test_get_records_sql_menu() {
1673 $dbman = $DB->get_manager();
1675 $table = $this->get_test_table();
1676 $tablename = $table->getName();
1678 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1679 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1680 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1681 $dbman->create_table($table);
1683 $DB->insert_record($tablename, array('course' => 3));
1684 $DB->insert_record($tablename, array('course' => 2));
1685 $DB->insert_record($tablename, array('course' => 3));
1686 $DB->insert_record($tablename, array('course' => 5));
1688 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
1689 $this->assertInternalType('array', $records);
1691 $this->assertCount(3, $records);
1692 $this->assertArrayHasKey(1, $records);
1693 $this->assertArrayNotHasKey(2, $records);
1694 $this->assertArrayHasKey(3, $records);
1695 $this->assertArrayHasKey(4, $records);
1696 $this->assertSame('3', $records[1]);
1697 $this->assertSame('3', $records[3]);
1698 $this->assertSame('5', $records[4]);
1700 // Note: delegate limits testing to test_get_records_sql().
1703 public function test_get_record() {
1705 $dbman = $DB->get_manager();
1707 $table = $this->get_test_table();
1708 $tablename = $table->getName();
1710 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1711 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1712 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1713 $dbman->create_table($table);
1715 $DB->insert_record($tablename, array('course' => 3));
1716 $DB->insert_record($tablename, array('course' => 2));
1718 $record = $DB->get_record($tablename, array('id' => 2));
1719 $this->assertInstanceOf('stdClass', $record);
1721 $this->assertEquals(2, $record->course);
1722 $this->assertEquals(2, $record->id);
1726 public function test_get_record_select() {
1728 $dbman = $DB->get_manager();
1730 $table = $this->get_test_table();
1731 $tablename = $table->getName();
1733 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1734 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1735 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1736 $dbman->create_table($table);
1738 $DB->insert_record($tablename, array('course' => 3));
1739 $DB->insert_record($tablename, array('course' => 2));
1741 $record = $DB->get_record_select($tablename, "id = ?", array(2));
1742 $this->assertInstanceOf('stdClass', $record);
1744 $this->assertEquals(2, $record->course);
1746 // Note: delegates limit testing to test_get_records_sql().
1749 public function test_get_record_sql() {
1751 $dbman = $DB->get_manager();
1753 $table = $this->get_test_table();
1754 $tablename = $table->getName();
1756 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1757 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1758 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1759 $dbman->create_table($table);
1761 $DB->insert_record($tablename, array('course' => 3));
1762 $DB->insert_record($tablename, array('course' => 2));
1765 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
1766 $this->assertInstanceOf('stdClass', $record);
1767 $this->assertEquals(2, $record->course);
1768 $this->assertEquals(2, $record->id);
1770 // Backwards compatibility with $ignoremultiple.
1771 $this->assertFalse((bool)IGNORE_MISSING);
1772 $this->assertTrue((bool)IGNORE_MULTIPLE);
1774 // Record not found - ignore.
1775 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1776 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1778 // Record not found error.
1780 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
1781 $this->fail("Exception expected");
1782 } catch (dml_missing_record_exception $e) {
1783 $this->assertTrue(true);
1786 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1787 $this->assertDebuggingCalled();
1788 set_debugging(DEBUG_MINIMAL);
1789 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1790 $this->assertDebuggingNotCalled();
1791 set_debugging(DEBUG_DEVELOPER);
1793 // Multiple matches ignored.
1794 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1796 // Multiple found error.
1798 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
1799 $this->fail("Exception expected");
1800 } catch (dml_multiple_records_exception $e) {
1801 $this->assertTrue(true);
1805 public function test_get_field() {
1807 $dbman = $DB->get_manager();
1809 $table = $this->get_test_table();
1810 $tablename = $table->getName();
1812 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1813 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1814 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1815 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1816 $dbman->create_table($table);
1818 $id1 = $DB->insert_record($tablename, array('course' => 3));
1819 $DB->insert_record($tablename, array('course' => 5));
1820 $DB->insert_record($tablename, array('course' => 5));
1822 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
1823 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3)));
1825 $this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
1827 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
1828 $this->fail('Exception expected due to missing record');
1829 } catch (dml_exception $ex) {
1830 $this->assertTrue(true);
1833 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
1834 $this->assertDebuggingNotCalled();
1836 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
1837 $this->assertDebuggingCalled();
1839 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
1840 $conditions = array('onetext' => '1');
1842 $DB->get_field($tablename, 'course', $conditions);
1844 // Only in debug mode - hopefully all devs test code in debug mode...
1845 $this->fail('An Exception is missing, expected due to equating of text fields');
1847 } catch (moodle_exception $e) {
1848 $this->assertInstanceOf('dml_exception', $e);
1849 $this->assertSame('textconditionsnotallowed', $e->errorcode);
1853 public function test_get_field_select() {
1855 $dbman = $DB->get_manager();
1857 $table = $this->get_test_table();
1858 $tablename = $table->getName();
1860 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1861 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1862 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1863 $dbman->create_table($table);
1865 $DB->insert_record($tablename, array('course' => 3));
1867 $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
1870 public function test_get_field_sql() {
1872 $dbman = $DB->get_manager();
1874 $table = $this->get_test_table();
1875 $tablename = $table->getName();
1877 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1878 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1879 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1880 $dbman->create_table($table);
1882 $DB->insert_record($tablename, array('course' => 3));
1884 $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
1887 public function test_get_fieldset_select() {
1889 $dbman = $DB->get_manager();
1891 $table = $this->get_test_table();
1892 $tablename = $table->getName();
1894 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1895 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1896 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1897 $dbman->create_table($table);
1899 $DB->insert_record($tablename, array('course' => 1));
1900 $DB->insert_record($tablename, array('course' => 3));
1901 $DB->insert_record($tablename, array('course' => 2));
1902 $DB->insert_record($tablename, array('course' => 6));
1904 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
1905 $this->assertInternalType('array', $fieldset);
1907 $this->assertCount(3, $fieldset);
1908 $this->assertEquals(3, $fieldset[0]);
1909 $this->assertEquals(2, $fieldset[1]);
1910 $this->assertEquals(6, $fieldset[2]);
1913 public function test_get_fieldset_sql() {
1915 $dbman = $DB->get_manager();
1917 $table = $this->get_test_table();
1918 $tablename = $table->getName();
1920 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1921 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1922 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1923 $dbman->create_table($table);
1925 $DB->insert_record($tablename, array('course' => 1));
1926 $DB->insert_record($tablename, array('course' => 3));
1927 $DB->insert_record($tablename, array('course' => 2));
1928 $DB->insert_record($tablename, array('course' => 6));
1930 $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
1931 $this->assertInternalType('array', $fieldset);
1933 $this->assertCount(3, $fieldset);
1934 $this->assertEquals(2, $fieldset[0]);
1935 $this->assertEquals(3, $fieldset[1]);
1936 $this->assertEquals(4, $fieldset[2]);
1939 public function test_insert_record_raw() {
1941 $dbman = $DB->get_manager();
1943 $table = $this->get_test_table();
1944 $tablename = $table->getName();
1946 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1947 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
1948 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1949 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1950 $dbman->create_table($table);
1952 $record = (object)array('course' => 1, 'onechar' => 'xx');
1953 $before = clone($record);
1954 $result = $DB->insert_record_raw($tablename, $record);
1955 $this->assertSame(1, $result);
1956 $this->assertEquals($record, $before);
1958 $record = $DB->get_record($tablename, array('course' => 1));
1959 $this->assertInstanceOf('stdClass', $record);
1960 $this->assertSame('xx', $record->onechar);
1962 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
1963 $this->assertTrue($result);
1965 // Note: bulk not implemented yet.
1966 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
1967 $record = $DB->get_record($tablename, array('course' => 3));
1968 $this->assertInstanceOf('stdClass', $record);
1969 $this->assertSame('zz', $record->onechar);
1971 // Custom sequence (id) - returnid is ignored.
1972 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
1973 $this->assertTrue($result);
1974 $record = $DB->get_record($tablename, array('id' => 10));
1975 $this->assertInstanceOf('stdClass', $record);
1976 $this->assertSame('bb', $record->onechar);
1978 // Custom sequence - missing id error.
1980 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
1981 $this->fail('Exception expected due to missing record');
1982 } catch (coding_exception $ex) {
1983 $this->assertTrue(true);
1986 // Wrong column error.
1988 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
1989 $this->fail('Exception expected due to invalid column');
1990 } catch (dml_exception $ex) {
1991 $this->assertTrue(true);
1994 // Create something similar to "context_temp" with id column without sequence.
1995 $dbman->drop_table($table);
1996 $table = $this->get_test_table();
1997 $tablename = $table->getName();
1998 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null);
1999 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2000 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2001 $dbman->create_table($table);
2003 $record = (object)array('id'=>5, 'course' => 1);
2004 $DB->insert_record_raw($tablename, $record, false, false, true);
2005 $record = $DB->get_record($tablename, array());
2006 $this->assertEquals(5, $record->id);
2009 public function test_insert_record() {
2010 // All the information in this test is fetched from DB by get_recordset() so we
2011 // have such method properly tested against nulls, empties and friends...
2014 $dbman = $DB->get_manager();
2016 $table = $this->get_test_table();
2017 $tablename = $table->getName();
2019 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2020 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2021 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2022 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2023 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2024 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2025 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2026 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2027 $dbman->create_table($table);
2029 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2030 $record = $DB->get_record($tablename, array('course' => 1));
2031 $this->assertEquals(1, $record->id);
2032 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
2033 $this->assertEquals(200, $record->onenum);
2034 $this->assertSame('onestring', $record->onechar);
2035 $this->assertNull($record->onetext);
2036 $this->assertNull($record->onebinary);
2038 // Without returning id, bulk not implemented.
2039 $result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true));
2040 $record = $DB->get_record($tablename, array('course' => 99));
2041 $this->assertEquals(2, $record->id);
2042 $this->assertEquals(99, $record->course);
2044 // Check nulls are set properly for all types.
2045 $record = new stdClass();
2046 $record->oneint = null;
2047 $record->onenum = null;
2048 $record->onechar = null;
2049 $record->onetext = null;
2050 $record->onebinary = null;
2051 $recid = $DB->insert_record($tablename, $record);
2052 $record = $DB->get_record($tablename, array('id' => $recid));
2053 $this->assertEquals(0, $record->course);
2054 $this->assertNull($record->oneint);
2055 $this->assertNull($record->onenum);
2056 $this->assertNull($record->onechar);
2057 $this->assertNull($record->onetext);
2058 $this->assertNull($record->onebinary);
2060 // Check zeros are set properly for all types.
2061 $record = new stdClass();
2062 $record->oneint = 0;
2063 $record->onenum = 0;
2064 $recid = $DB->insert_record($tablename, $record);
2065 $record = $DB->get_record($tablename, array('id' => $recid));
2066 $this->assertEquals(0, $record->oneint);
2067 $this->assertEquals(0, $record->onenum);
2069 // Check booleans are set properly for all types.
2070 $record = new stdClass();
2071 $record->oneint = true; // Trues.
2072 $record->onenum = true;
2073 $record->onechar = true;
2074 $record->onetext = true;
2075 $recid = $DB->insert_record($tablename, $record);
2076 $record = $DB->get_record($tablename, array('id' => $recid));
2077 $this->assertEquals(1, $record->oneint);
2078 $this->assertEquals(1, $record->onenum);
2079 $this->assertEquals(1, $record->onechar);
2080 $this->assertEquals(1, $record->onetext);
2082 $record = new stdClass();
2083 $record->oneint = false; // Falses.
2084 $record->onenum = false;
2085 $record->onechar = false;
2086 $record->onetext = false;
2087 $recid = $DB->insert_record($tablename, $record);
2088 $record = $DB->get_record($tablename, array('id' => $recid));
2089 $this->assertEquals(0, $record->oneint);
2090 $this->assertEquals(0, $record->onenum);
2091 $this->assertEquals(0, $record->onechar);
2092 $this->assertEquals(0, $record->onetext);
2094 // Check string data causes exception in numeric types.
2095 $record = new stdClass();
2096 $record->oneint = 'onestring';
2097 $record->onenum = 0;
2099 $DB->insert_record($tablename, $record);
2100 $this->fail("Expecting an exception, none occurred");
2101 } catch (moodle_exception $e) {
2102 $this->assertInstanceOf('dml_exception', $e);
2104 $record = new stdClass();
2105 $record->oneint = 0;
2106 $record->onenum = 'onestring';
2108 $DB->insert_record($tablename, $record);
2109 $this->fail("Expecting an exception, none occurred");
2110 } catch (moodle_exception $e) {
2111 $this->assertInstanceOf('dml_exception', $e);
2114 // Check empty string data is stored as 0 in numeric datatypes.
2115 $record = new stdClass();
2116 $record->oneint = ''; // Empty string.
2117 $record->onenum = 0;
2118 $recid = $DB->insert_record($tablename, $record);
2119 $record = $DB->get_record($tablename, array('id' => $recid));
2120 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2122 $record = new stdClass();
2123 $record->oneint = 0;
2124 $record->onenum = ''; // Empty string.
2125 $recid = $DB->insert_record($tablename, $record);
2126 $record = $DB->get_record($tablename, array('id' => $recid));
2127 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2129 // Check empty strings are set properly in string types.
2130 $record = new stdClass();
2131 $record->oneint = 0;
2132 $record->onenum = 0;
2133 $record->onechar = '';
2134 $record->onetext = '';
2135 $recid = $DB->insert_record($tablename, $record);
2136 $record = $DB->get_record($tablename, array('id' => $recid));
2137 $this->assertTrue($record->onechar === '');
2138 $this->assertTrue($record->onetext === '');
2140 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
2141 $record = new stdClass();
2142 $record->oneint = ((210.10 + 39.92) - 150.02);
2143 $record->onenum = ((210.10 + 39.92) - 150.02);
2144 $recid = $DB->insert_record($tablename, $record);
2145 $record = $DB->get_record($tablename, array('id' => $recid));
2146 $this->assertEquals(100, $record->oneint);
2147 $this->assertEquals(100, $record->onenum);
2149 // Check various quotes/backslashes combinations in string types.
2150 $teststrings = array(
2151 'backslashes and quotes alone (even): "" \'\' \\\\',
2152 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2153 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2154 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2155 foreach ($teststrings as $teststring) {
2156 $record = new stdClass();
2157 $record->onechar = $teststring;
2158 $record->onetext = $teststring;
2159 $recid = $DB->insert_record($tablename, $record);
2160 $record = $DB->get_record($tablename, array('id' => $recid));
2161 $this->assertEquals($teststring, $record->onechar);
2162 $this->assertEquals($teststring, $record->onetext);
2165 // Check LOBs in text/binary columns.
2166 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2167 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2168 $record = new stdClass();
2169 $record->onetext = $clob;
2170 $record->onebinary = $blob;
2171 $recid = $DB->insert_record($tablename, $record);
2172 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2173 $record = $rs->current();
2175 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2176 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2178 // And "small" LOBs too, just in case.
2179 $newclob = substr($clob, 0, 500);
2180 $newblob = substr($blob, 0, 250);
2181 $record = new stdClass();
2182 $record->onetext = $newclob;
2183 $record->onebinary = $newblob;
2184 $recid = $DB->insert_record($tablename, $record);
2185 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2186 $record = $rs->current();
2188 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2189 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2190 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
2192 // And "diagnostic" LOBs too, just in case.
2193 $newclob = '\'"\\;/ěščřžýáíé';
2194 $newblob = '\'"\\;/ěščřžýáíé';
2195 $record = new stdClass();
2196 $record->onetext = $newclob;
2197 $record->onebinary = $newblob;
2198 $recid = $DB->insert_record($tablename, $record);
2199 $rs = $DB->get_recordset($tablename, array('id' => $recid));
2200 $record = $rs->current();
2202 $this->assertSame($newclob, $record->onetext);
2203 $this->assertSame($newblob, $record->onebinary);
2204 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
2206 // Test data is not modified.
2207 $record = new stdClass();
2208 $record->id = -1; // Has to be ignored.
2209 $record->course = 3;
2210 $record->lalala = 'lalal'; // Unused.
2211 $before = clone($record);
2212 $DB->insert_record($tablename, $record);
2213 $this->assertEquals($record, $before);
2215 // Make sure the id is always increasing and never reuses the same id.
2216 $id1 = $DB->insert_record($tablename, array('course' => 3));
2217 $id2 = $DB->insert_record($tablename, array('course' => 3));
2218 $this->assertTrue($id1 < $id2);
2219 $DB->delete_records($tablename, array('id'=>$id2));
2220 $id3 = $DB->insert_record($tablename, array('course' => 3));
2221 $this->assertTrue($id2 < $id3);
2222 $DB->delete_records($tablename, array());
2223 $id4 = $DB->insert_record($tablename, array('course' => 3));
2224 $this->assertTrue($id3 < $id4);
2226 // Test saving a float in a CHAR column, and reading it back.
2227 $id = $DB->insert_record($tablename, array('onechar' => 1.0));
2228 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2229 $id = $DB->insert_record($tablename, array('onechar' => 1e20));
2230 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2231 $id = $DB->insert_record($tablename, array('onechar' => 1e-4));
2232 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2233 $id = $DB->insert_record($tablename, array('onechar' => 1e-5));
2234 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2235 $id = $DB->insert_record($tablename, array('onechar' => 1e-300));
2236 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2237 $id = $DB->insert_record($tablename, array('onechar' => 1e300));
2238 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2240 // Test saving a float in a TEXT column, and reading it back.
2241 $id = $DB->insert_record($tablename, array('onetext' => 1.0));
2242 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2243 $id = $DB->insert_record($tablename, array('onetext' => 1e20));
2244 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2245 $id = $DB->insert_record($tablename, array('onetext' => 1e-4));
2246 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2247 $id = $DB->insert_record($tablename, array('onetext' => 1e-5));
2248 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2249 $id = $DB->insert_record($tablename, array('onetext' => 1e-300));
2250 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2251 $id = $DB->insert_record($tablename, array('onetext' => 1e300));
2252 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2254 // Test that inserting data violating one unique key leads to error.
2255 // Empty the table completely.
2256 $this->assertTrue($DB->delete_records($tablename));
2258 // Add one unique constraint (index).
2259 $key = new xmldb_key('testuk', XMLDB_KEY_UNIQUE, array('course', 'oneint'));
2260 $dbman->add_key($table, $key);
2262 // Let's insert one record violating the constraint multiple times.
2263 $record = (object)array('course' => 1, 'oneint' => 1);
2264 $this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected.
2266 // Re-insert same record, not returning id. dml_exception expected.
2268 $DB->insert_record($tablename, $record, false);
2269 $this->fail("Expecting an exception, none occurred");
2270 } catch (moodle_exception $e) {
2271 $this->assertInstanceOf('dml_exception', $e);
2274 // Re-insert same record, returning id. dml_exception expected.
2276 $DB->insert_record($tablename, $record, true);
2277 $this->fail("Expecting an exception, none occurred");
2278 } catch (moodle_exception $e) {
2279 $this->assertInstanceOf('dml_exception', $e);
2282 // Try to insert a record into a non-existent table. dml_exception expected.
2284 $DB->insert_record('nonexistenttable', $record, true);
2285 $this->fail("Expecting an exception, none occurred");
2286 } catch (exception $e) {
2287 $this->assertTrue($e instanceof dml_exception);
2291 public function test_insert_records() {
2293 $dbman = $DB->get_manager();
2295 $table = $this->get_test_table();
2296 $tablename = $table->getName();
2298 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2299 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2300 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2301 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2302 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2303 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2304 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2305 $dbman->create_table($table);
2307 $this->assertCount(0, $DB->get_records($tablename));
2309 $record = new stdClass();
2311 $record->course = '1';
2312 $record->oneint = null;
2313 $record->onenum = '1.00';
2314 $record->onechar = 'a';
2315 $record->onetext = 'aaa';
2317 $expected = array();
2319 for ($i = 1; $i <= 2000; $i++) { // This may take a while, it should be higher than defaults in DML drivers.
2320 $rec = clone($record);
2321 $rec->id = (string)$i;
2322 $rec->oneint = (string)$i;
2323 $expected[$i] = $rec;
2326 $records[$i] = $rec;
2329 $DB->insert_records($tablename, $records);
2330 $stored = $DB->get_records($tablename, array(), 'id ASC');
2331 $this->assertEquals($expected, $stored);
2333 // Test there can be some extra properties including id.
2334 $count = $DB->count_records($tablename);
2335 $rec1 = (array)$record;
2337 $rec2 = (array)$record;
2340 $records = array($rec1, $rec2);
2341 $DB->insert_records($tablename, $records);
2342 $this->assertEquals($count + 2, $DB->count_records($tablename));
2344 // Test not all properties are necessary.
2345 $rec1 = (array)$record;
2346 unset($rec1['course']);
2347 $rec2 = (array)$record;
2348 unset($rec2['course']);
2350 $records = array($rec1, $rec2);
2351 $DB->insert_records($tablename, $records);
2353 // Make sure no changes in data object structure are tolerated.
2354 $rec1 = (array)$record;
2356 $rec2 = (array)$record;
2359 $records = array($rec1, $rec2);
2360 $DB->insert_records($tablename, $records);
2363 $records = array($rec1, $rec2);
2365 $DB->insert_records($tablename, $records);
2366 $this->fail('coding_exception expected when insert_records receives different object data structures');
2367 } catch (moodle_exception $e) {
2368 $this->assertInstanceOf('coding_exception', $e);
2372 unset($rec2['course']);
2373 $rec2['course'] = '1';
2374 $records = array($rec1, $rec2);
2376 $DB->insert_records($tablename, $records);
2377 $this->fail('coding_exception expected when insert_records receives different object data structures');
2378 } catch (moodle_exception $e) {
2379 $this->assertInstanceOf('coding_exception', $e);
2384 $DB->insert_records($tablename, $records);
2385 $this->fail('coding_exception expected when insert_records receives non-traversable data');
2386 } catch (moodle_exception $e) {
2387 $this->assertInstanceOf('coding_exception', $e);
2390 $records = array(1);
2392 $DB->insert_records($tablename, $records);
2393 $this->fail('coding_exception expected when insert_records receives non-objet record');
2394 } catch (moodle_exception $e) {
2395 $this->assertInstanceOf('coding_exception', $e);
2399 public function test_import_record() {
2400 // All the information in this test is fetched from DB by get_recordset() so we
2401 // have such method properly tested against nulls, empties and friends...
2404 $dbman = $DB->get_manager();
2406 $table = $this->get_test_table();
2407 $tablename = $table->getName();
2409 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2410 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2411 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2412 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2413 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2414 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2415 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2416 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2417 $dbman->create_table($table);
2419 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2420 $record = $DB->get_record($tablename, array('course' => 1));
2421 $this->assertEquals(1, $record->id);
2422 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
2423 $this->assertEquals(200, $record->onenum);
2424 $this->assertSame('onestring', $record->onechar);
2425 $this->assertNull($record->onetext);
2426 $this->assertNull($record->onebinary);
2428 // Ignore extra columns.
2429 $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);
2430 $before = clone($record);
2431 $this->assertTrue($DB->import_record($tablename, $record));
2432 $this->assertEquals($record, $before);
2433 $records = $DB->get_records($tablename);
2434 $this->assertEquals(2, $records[13]->course);
2436 // Check nulls are set properly for all types.
2437 $record = new stdClass();
2439 $record->oneint = null;
2440 $record->onenum = null;
2441 $record->onechar = null;
2442 $record->onetext = null;
2443 $record->onebinary = null;
2444 $this->assertTrue($DB->import_record($tablename, $record));
2445 $record = $DB->get_record($tablename, array('id' => 20));
2446 $this->assertEquals(0, $record->course);
2447 $this->assertNull($record->oneint);
2448 $this->assertNull($record->onenum);
2449 $this->assertNull($record->onechar);
2450 $this->assertNull($record->onetext);
2451 $this->assertNull($record->onebinary);
2453 // Check zeros are set properly for all types.
2454 $record = new stdClass();
2456 $record->oneint = 0;
2457 $record->onenum = 0;
2458 $this->assertTrue($DB->import_record($tablename, $record));
2459 $record = $DB->get_record($tablename, array('id' => 23));
2460 $this->assertEquals(0, $record->oneint);
2461 $this->assertEquals(0, $record->onenum);
2463 // Check string data causes exception in numeric types.
2464 $record = new stdClass();
2466 $record->oneint = 'onestring';
2467 $record->onenum = 0;
2469 $DB->import_record($tablename, $record);
2470 $this->fail("Expecting an exception, none occurred");
2471 } catch (moodle_exception $e) {
2472 $this->assertInstanceOf('dml_exception', $e);
2474 $record = new stdClass();
2476 $record->oneint = 0;
2477 $record->onenum = 'onestring';
2479 $DB->import_record($tablename, $record);
2480 $this->fail("Expecting an exception, none occurred");
2481 } catch (moodle_exception $e) {
2482 $this->assertInstanceOf('dml_exception', $e);
2485 // Check empty strings are set properly in string types.
2486 $record = new stdClass();
2488 $record->oneint = 0;
2489 $record->onenum = 0;
2490 $record->onechar = '';
2491 $record->onetext = '';
2492 $this->assertTrue($DB->import_record($tablename, $record));
2493 $record = $DB->get_record($tablename, array('id' => 44));
2494 $this->assertTrue($record->onechar === '');
2495 $this->assertTrue($record->onetext === '');
2497 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
2498 $record = new stdClass();
2500 $record->oneint = ((210.10 + 39.92) - 150.02);
2501 $record->onenum = ((210.10 + 39.92) - 150.02);
2502 $this->assertTrue($DB->import_record($tablename, $record));
2503 $record = $DB->get_record($tablename, array('id' => 47));
2504 $this->assertEquals(100, $record->oneint);
2505 $this->assertEquals(100, $record->onenum);
2507 // Check various quotes/backslashes combinations in string types.
2509 $teststrings = array(
2510 'backslashes and quotes alone (even): "" \'\' \\\\',
2511 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2512 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2513 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2514 foreach ($teststrings as $teststring) {
2515 $record = new stdClass();
2517 $record->onechar = $teststring;
2518 $record->onetext = $teststring;
2519 $this->assertTrue($DB->import_record($tablename, $record));
2520 $record = $DB->get_record($tablename, array('id' => $i));
2521 $this->assertEquals($teststring, $record->onechar);
2522 $this->assertEquals($teststring, $record->onetext);
2526 // Check LOBs in text/binary columns.
2527 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2528 $record = new stdClass();
2530 $record->onetext = $clob;
2531 $record->onebinary = '';
2532 $this->assertTrue($DB->import_record($tablename, $record));
2533 $rs = $DB->get_recordset($tablename, array('id' => 70));
2534 $record = $rs->current();
2536 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2538 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2539 $record = new stdClass();
2541 $record->onetext = '';
2542 $record->onebinary = $blob;
2543 $this->assertTrue($DB->import_record($tablename, $record));
2544 $rs = $DB->get_recordset($tablename, array('id' => 71));
2545 $record = $rs->current();
2547 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2549 // And "small" LOBs too, just in case.
2550 $newclob = substr($clob, 0, 500);
2551 $newblob = substr($blob, 0, 250);
2552 $record = new stdClass();
2554 $record->onetext = $newclob;
2555 $record->onebinary = $newblob;
2556 $this->assertTrue($DB->import_record($tablename, $record));
2557 $rs = $DB->get_recordset($tablename, array('id' => 73));
2558 $record = $rs->current();
2560 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2561 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2562 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing.
2565 public function test_update_record_raw() {
2567 $dbman = $DB->get_manager();
2569 $table = $this->get_test_table();
2570 $tablename = $table->getName();
2572 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2573 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2574 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2575 $dbman->create_table($table);
2577 $DB->insert_record($tablename, array('course' => 1));
2578 $DB->insert_record($tablename, array('course' => 3));
2580 $record = $DB->get_record($tablename, array('course' => 1));
2581 $record->course = 2;
2582 $this->assertTrue($DB->update_record_raw($tablename, $record));
2583 $this->assertEquals(0, $DB->count_records($tablename, array('course' => 1)));
2584 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 2)));
2585 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 3)));
2587 $record = $DB->get_record($tablename, array('course' => 3));
2590 $DB->update_record_raw($tablename, $record);
2591 $this->fail("Expecting an exception, none occurred");
2592 } catch (moodle_exception $e) {
2593 $this->assertInstanceOf('moodle_exception', $e);
2596 $record = $DB->get_record($tablename, array('course' => 3));
2599 $DB->update_record_raw($tablename, $record);
2600 $this->fail("Expecting an exception, none occurred");
2601 } catch (moodle_exception $e) {
2602 $this->assertInstanceOf('coding_exception', $e);
2606 public function test_update_record() {
2608 // All the information in this test is fetched from DB by get_record() so we
2609 // have such method properly tested against nulls, empties and friends...
2612 $dbman = $DB->get_manager();
2614 $table = $this->get_test_table();
2615 $tablename = $table->getName();
2617 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2618 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2619 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
2620 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2621 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2622 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2623 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2624 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2625 $dbman->create_table($table);
2627 $DB->insert_record($tablename, array('course' => 1));
2628 $record = $DB->get_record($tablename, array('course' => 1));
2629 $record->course = 2;
2631 $this->assertTrue($DB->update_record($tablename, $record));
2632 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
2633 $this->assertNotEmpty($record = $DB->get_record($tablename, array('course' => 2)));
2634 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied.
2635 $this->assertEquals(200, $record->onenum);
2636 $this->assertSame('onestring', $record->onechar);
2637 $this->assertNull($record->onetext);
2638 $this->assertNull($record->onebinary);
2640 // Check nulls are set properly for all types.
2641 $record->oneint = null;
2642 $record->onenum = null;
2643 $record->onechar = null;
2644 $record->onetext = null;
2645 $record->onebinary = null;
2646 $DB->update_record($tablename, $record);
2647 $record = $DB->get_record($tablename, array('course' => 2));
2648 $this->assertNull($record->oneint);
2649 $this->assertNull($record->onenum);
2650 $this->assertNull($record->onechar);
2651 $this->assertNull($record->onetext);
2652 $this->assertNull($record->onebinary);
2654 // Check zeros are set properly for all types.
2655 $record->oneint = 0;
2656 $record->onenum = 0;
2657 $DB->update_record($tablename, $record);
2658 $record = $DB->get_record($tablename, array('course' => 2));
2659 $this->assertEquals(0, $record->oneint);
2660 $this->assertEquals(0, $record->onenum);
2662 // Check booleans are set properly for all types.
2663 $record->oneint = true; // Trues.
2664 $record->onenum = true;
2665 $record->onechar = true;
2666 $record->onetext = true;
2667 $DB->update_record($tablename, $record);
2668 $record = $DB->get_record($tablename, array('course' => 2));
2669 $this->assertEquals(1, $record->oneint);
2670 $this->assertEquals(1, $record->onenum);
2671 $this->assertEquals(1, $record->onechar);
2672 $this->assertEquals(1, $record->onetext);
2674 $record->oneint = false; // Falses.
2675 $record->onenum = false;
2676 $record->onechar = false;
2677 $record->onetext = false;
2678 $DB->update_record($tablename, $record);
2679 $record = $DB->get_record($tablename, array('course' => 2));
2680 $this->assertEquals(0, $record->oneint);
2681 $this->assertEquals(0, $record->onenum);
2682 $this->assertEquals(0, $record->onechar);
2683 $this->assertEquals(0, $record->onetext);
2685 // Check string data causes exception in numeric types.
2686 $record->oneint = 'onestring';
2687 $record->onenum = 0;
2689 $DB->update_record($tablename, $record);
2690 $this->fail("Expecting an exception, none occurred");
2691 } catch (moodle_exception $e) {
2692 $this->assertInstanceOf('dml_exception', $e);
2694 $record->oneint = 0;
2695 $record->onenum = 'onestring';
2697 $DB->update_record($tablename, $record);
2698 $this->fail("Expecting an exception, none occurred");
2699 } catch (moodle_exception $e) {
2700 $this->assertInstanceOf('dml_exception', $e);
2703 // Check empty string data is stored as 0 in numeric datatypes.
2704 $record->oneint = ''; // Empty string.
2705 $record->onenum = 0;
2706 $DB->update_record($tablename, $record);
2707 $record = $DB->get_record($tablename, array('course' => 2));
2708 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2710 $record->oneint = 0;
2711 $record->onenum = ''; // Empty string.
2712 $DB->update_record($tablename, $record);
2713 $record = $DB->get_record($tablename, array('course' => 2));
2714 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2716 // Check empty strings are set properly in string types.
2717 $record->oneint = 0;
2718 $record->onenum = 0;
2719 $record->onechar = '';
2720 $record->onetext = '';
2721 $DB->update_record($tablename, $record);
2722 $record = $DB->get_record($tablename, array('course' => 2));
2723 $this->assertTrue($record->onechar === '');
2724 $this->assertTrue($record->onetext === '');
2726 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
2727 $record->oneint = ((210.10 + 39.92) - 150.02);
2728 $record->onenum = ((210.10 + 39.92) - 150.02);
2729 $DB->update_record($tablename, $record);
2730 $record = $DB->get_record($tablename, array('course' => 2));
2731 $this->assertEquals(100, $record->oneint);
2732 $this->assertEquals(100, $record->onenum);
2734 // Check various quotes/backslashes combinations in string types.
2735 $teststrings = array(
2736 'backslashes and quotes alone (even): "" \'\' \\\\',
2737 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2738 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2739 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2740 foreach ($teststrings as $teststring) {
2741 $record->onechar = $teststring;
2742 $record->onetext = $teststring;
2743 $DB->update_record($tablename, $record);
2744 $record = $DB->get_record($tablename, array('course' => 2));
2745 $this->assertEquals($teststring, $record->onechar);
2746 $this->assertEquals($teststring, $record->onetext);
2749 // Check LOBs in text/binary columns.
2750 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2751 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2752 $record->onetext = $clob;
2753 $record->onebinary = $blob;
2754 $DB->update_record($tablename, $record);
2755 $record = $DB->get_record($tablename, array('course' => 2));
2756 $this->assertEquals($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
2757 $this->assertEquals($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
2759 // And "small" LOBs too, just in case.
2760 $newclob = substr($clob, 0, 500);
2761 $newblob = substr($blob, 0, 250);
2762 $record->onetext = $newclob;
2763 $record->onebinary = $newblob;
2764 $DB->update_record($tablename, $record);
2765 $record = $DB->get_record($tablename, array('course' => 2));
2766 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
2767 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
2769 // Test saving a float in a CHAR column, and reading it back.
2770 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2771 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1.0));
2772 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2773 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e20));
2774 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2775 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-4));
2776 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2777 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-5));
2778 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2779 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-300));
2780 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2781 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e300));
2782 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2784 // Test saving a float in a TEXT column, and reading it back.
2785 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2786 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1.0));
2787 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2788 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e20));
2789 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2790 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-4));
2791 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2792 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-5));
2793 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2794 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-300));
2795 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2796 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e300));
2797 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2800 public function test_set_field() {
2802 $dbman = $DB->get_manager();
2804 $table = $this->get_test_table();
2805 $tablename = $table->getName();
2807 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2808 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2809 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2810 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2811 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2812 $dbman->create_table($table);
2814 // Simple set_field.
2815 $id1 = $DB->insert_record($tablename, array('course' => 1));
2816 $id2 = $DB->insert_record($tablename, array('course' => 1));
2817 $id3 = $DB->insert_record($tablename, array('course' => 3));
2818 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1)));
2819 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => $id1)));
2820 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2821 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2822 $DB->delete_records($tablename, array());
2824 // Multiple fields affected.
2825 $id1 = $DB->insert_record($tablename, array('course' => 1));
2826 $id2 = $DB->insert_record($tablename, array('course' => 1));
2827 $id3 = $DB->insert_record($tablename, array('course' => 3));
2828 $DB->set_field($tablename, 'course', '5', array('course' => 1));
2829 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2830 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2831 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2832 $DB->delete_records($tablename, array());
2834 // No field affected.
2835 $id1 = $DB->insert_record($tablename, array('course' => 1));
2836 $id2 = $DB->insert_record($tablename, array('course' => 1));
2837 $id3 = $DB->insert_record($tablename, array('course' => 3));
2838 $DB->set_field($tablename, 'course', '5', array('course' => 0));
2839 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id1)));
2840 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2841 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2842 $DB->delete_records($tablename, array());
2844 // All fields - no condition.
2845 $id1 = $DB->insert_record($tablename, array('course' => 1));
2846 $id2 = $DB->insert_record($tablename, array('course' => 1));
2847 $id3 = $DB->insert_record($tablename, array('course' => 3));
2848 $DB->set_field($tablename, 'course', 5, array());
2849 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2850 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2851 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id3)));
2853 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
2854 $conditions = array('onetext' => '1');
2856 $DB->set_field($tablename, 'onechar', 'frog', $conditions);
2858 // Only in debug mode - hopefully all devs test code in debug mode...
2859 $this->fail('An Exception is missing, expected due to equating of text fields');
2861 } catch (moodle_exception $e) {
2862 $this->assertInstanceOf('dml_exception', $e);
2863 $this->assertSame('textconditionsnotallowed', $e->errorcode);
2866 // Test saving a float in a CHAR column, and reading it back.
2867 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2868 $DB->set_field($tablename, 'onechar', 1.0, array('id' => $id));
2869 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2870 $DB->set_field($tablename, 'onechar', 1e20, array('id' => $id));
2871 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2872 $DB->set_field($tablename, 'onechar', 1e-4, array('id' => $id));
2873 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2874 $DB->set_field($tablename, 'onechar', 1e-5, array('id' => $id));
2875 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2876 $DB->set_field($tablename, 'onechar', 1e-300, array('id' => $id));
2877 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2878 $DB->set_field($tablename, 'onechar', 1e300, array('id' => $id));
2879 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2881 // Test saving a float in a TEXT column, and reading it back.
2882 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2883 $DB->set_field($tablename, 'onetext', 1.0, array('id' => $id));
2884 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2885 $DB->set_field($tablename, 'onetext', 1e20, array('id' => $id));
2886 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2887 $DB->set_field($tablename, 'onetext', 1e-4, array('id' => $id));
2888 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2889 $DB->set_field($tablename, 'onetext', 1e-5, array('id' => $id));
2890 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2891 $DB->set_field($tablename, 'onetext', 1e-300, array('id' => $id));
2892 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2893 $DB->set_field($tablename, 'onetext', 1e300, array('id' => $id));
2894 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2896 // Note: All the nulls, booleans, empties, quoted and backslashes tests
2897 // go to set_field_select() because set_field() is just one wrapper over it.
2900 public function test_set_field_select() {
2902 // All the information in this test is fetched from DB by get_field() so we
2903 // have such method properly tested against nulls, empties and friends...
2906 $dbman = $DB->get_manager();
2908 $table = $this->get_test_table();
2909 $tablename = $table->getName();
2911 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2912 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
2913 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null);
2914 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
2915 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2916 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2917 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2918 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2919 $dbman->create_table($table);
2921 $DB->insert_record($tablename, array('course' => 1));
2923 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
2924 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => 1)));
2926 // Check nulls are set properly for all types.
2927 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // Trues.
2928 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
2929 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
2930 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
2931 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
2932 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
2933 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
2934 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
2935 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
2936 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
2938 // Check zeros are set properly for all types.
2939 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
2940 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
2941 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2942 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2944 // Check booleans are set properly for all types.
2945 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // Trues.
2946 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
2947 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
2948 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
2949 $this->assertEquals(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2950 $this->assertEquals(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2951 $this->assertEquals(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2952 $this->assertEquals(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2954 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // Falses.
2955 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
2956 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
2957 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
2958 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2959 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2960 $this->assertEquals(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2961 $this->assertEquals(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2963 // Check string data causes exception in numeric types.
2965 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
2966 $this->fail("Expecting an exception, none occurred");
2967 } catch (moodle_exception $e) {
2968 $this->assertInstanceOf('dml_exception', $e);
2971 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
2972 $this->fail("Expecting an exception, none occurred");
2973 } catch (moodle_exception $e) {
2974 $this->assertInstanceOf('dml_exception', $e);
2977 // Check empty string data is stored as 0 in numeric datatypes.
2978 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
2979 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
2980 $this->assertTrue(is_numeric($field) && $field == 0);
2982 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
2983 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
2984 $this->assertTrue(is_numeric($field) && $field == 0);
2986 // Check empty strings are set properly in string types.
2987 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
2988 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
2989 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
2990 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
2992 // Check operation ((210.10 + 39.92) - 150.02) against numeric types.
2993 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2994 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2995 $this->assertEquals(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2996 $this->assertEquals(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2998 // Check various quotes/backslashes combinations in string types.
2999 $teststrings = array(
3000 'backslashes and quotes alone (even): "" \'\' \\\\',
3001 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
3002 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
3003 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
3004 foreach ($teststrings as $teststring) {
3005 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
3006 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
3007 $this->assertEquals($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
3008 $this->assertEquals($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
3011 // Check LOBs in text/binary columns.
3012 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
3013 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
3014 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
3015 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
3016 $this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
3017 $this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
3019 // And "small" LOBs too, just in case.
3020 $newclob = substr($clob, 0, 500);
3021 $newblob = substr($blob, 0, 250);
3022 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
3023 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
3024 $this->assertEquals($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
3025 $this->assertEquals($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
3027 // This is the failure from MDL-24863. This was giving an error on MSSQL,
3028 // which converts the '1' to an integer, which cannot then be compared with
3029 // onetext cast to a varchar. This should be fixed and working now.
3031 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
3032 $params = array('onetext' => '1');
3034 $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);
3035 $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.');
3036 } catch (dml_exception $e) {
3037 $this->assertFalse(true, 'We have an unexpected exception.');
3042 public function test_count_records() {
3045 $dbman = $DB->get_manager();
3047 $table = $this->get_test_table();
3048 $tablename = $table->getName();
3050 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3051 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3052 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
3053 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3054 $dbman->create_table($table);
3056 $this->assertSame(0, $DB->count_records($tablename));
3058 $DB->insert_record($tablename, array('course' => 3));
3059 $DB->insert_record($tablename, array('course' => 4));
3060 $DB->insert_record($tablename, array('course' => 5));
3062 $this->assertSame(3, $DB->count_records($tablename));
3064 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
3065 $conditions = array('onetext' => '1');
3067 $DB->count_records($tablename, $conditions);
3069 // Only in debug mode - hopefully all devs test code in debug mode...
3070 $this->fail('An Exception is missing, expected due to equating of text fields');
3072 } catch (moodle_exception $e) {
3073 $this->assertInstanceOf('dml_exception', $e);
3074 $this->assertSame('textconditionsnotallowed', $e->errorcode);
3078 public function test_count_records_select() {
3081 $dbman = $DB->get_manager();
3083 $table = $this->get_test_table();
3084 $tablename = $table->getName();
3086 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3087 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3088 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3089 $dbman->create_table($table);
3091 $this->assertSame(0, $DB->count_records($tablename));
3093 $DB->insert_record($tablename, array('course' => 3));
3094 $DB->insert_record($tablename, array('course' => 4));
3095 $DB->insert_record($tablename, array('course' => 5));
3097 $this->assertSame(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
3100 public function test_count_records_sql() {
3102 $dbman = $DB->get_manager();
3104 $table = $this->get_test_table();
3105 $tablename = $table->getName();
3107 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3108 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3109 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
3110 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3111 $dbman->create_table($table);
3113 $this->assertSame(0, $DB->count_records($tablename));
3115 $DB->insert_record($tablename, array('course' => 3, 'onechar' => 'a'));
3116 $DB->insert_record($tablename, array('course' => 4, 'onechar' => 'b'));
3117 $DB->insert_record($tablename, array('course' => 5, 'onechar' => 'c'));
3119 $this->assertSame(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));
3121 // Test invalid use.
3123 $DB->count_records_sql("SELECT onechar FROM {{$tablename}} WHERE course = ?", array(3));
3124 $this->fail('Exception expected when non-number field used in count_records_sql');
3125 } catch (moodle_exception $e) {
3126 $this->assertInstanceOf('coding_exception', $e);
3130 $DB->count_records_sql("SELECT course FROM {{$tablename}} WHERE 1 = 2");
3131 $this->fail('Exception expected when non-number field used in count_records_sql');
3132 } catch (moodle_exception $e) {
3133 $this->assertInstanceOf('coding_exception', $e);
3137 public function test_record_exists() {
3139 $dbman = $DB->get_manager();
3141 $table = $this->get_test_table();
3142 $tablename = $table->getName();
3144 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3145 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3146 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
3147 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3148 $dbman->create_table($table);
3150 $this->assertEquals(0, $DB->count_records($tablename));
3152 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
3153 $DB->insert_record($tablename, array('course' => 3));
3155 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
3157 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int).
3158 $conditions = array('onetext' => '1');
3160 $DB->record_exists($tablename, $conditions);
3162 // Only in debug mode - hopefully all devs test code in debug mode...
3163 $this->fail('An Exception is missing, expected due to equating of text fields');
3165 } catch (moodle_exception $e) {
3166 $this->assertInstanceOf('dml_exception', $e);
3167 $this->assertSame('textconditionsnotallowed', $e->errorcode);
3171 public function test_record_exists_select() {
3173 $dbman = $DB->get_manager();
3175 $table = $this->get_test_table();
3176 $tablename = $table->getName();
3178 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3179 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3180 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3181 $dbman->create_table($table);
3183 $this->assertEquals(0, $DB->count_records($tablename));
3185 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
3186 $DB->insert_record($tablename, array('course' => 3));
3188 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
3191 public function test_record_exists_sql() {
3193 $dbman = $DB->get_manager();
3195 $table = $this->get_test_table();
3196 $tablename = $table->getName();
3198 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3199 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3200 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3201 $dbman->create_table($table);
3203 $this->assertEquals(0, $DB->count_records($tablename));
3205 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
3206 $DB->insert_record($tablename, array('course' => 3));
3208 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
3211 public function test_recordset_locks_delete() {
3213 $dbman = $DB->get_manager();
3216 $table = $this->get_test_table();
3217 $tablename = $table->getName();
3219 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3220 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3221 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3222 $dbman->create_table($table);
3224 $DB->insert_record($tablename, array('course' => 1));
3225 $DB->insert_record($tablename, array('course' => 2));
3226 $DB->insert_record($tablename, array('course' => 3));
3227 $DB->insert_record($tablename, array('course' => 4));
3228 $DB->insert_record($tablename, array('course' => 5));
3229 $DB->insert_record($tablename, array('course' => 6));
3231 // Test against db write locking while on an open recordset.
3232 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}.
3233 foreach ($rs as $record) {
3234 $cid = $record->course;
3235 $DB->delete_records($tablename, array('course' => $cid));
3236 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
3240 $this->assertEquals(4, $DB->count_records($tablename, array()));
3243 public function test_recordset_locks_update() {
3245 $dbman = $DB->get_manager();
3248 $table = $this->get_test_table();
3249 $tablename = $table->getName();
3251 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3252 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3253 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3254 $dbman->create_table($table);
3256 $DB->insert_record($tablename, array('course' => 1));
3257 $DB->insert_record($tablename, array('course' => 2));
3258 $DB->insert_record($tablename, array('course' => 3));
3259 $DB->insert_record($tablename, array('course' => 4));
3260 $DB->insert_record($tablename, array('course' => 5));
3261 $DB->insert_record($tablename, array('course' => 6));
3263 // Test against db write locking while on an open recordset.
3264 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}.
3265 foreach ($rs as $record) {
3266 $cid = $record->course;
3267 $DB->set_field($tablename, 'course', 10, array('course' => $cid));
3268 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
3272 $this->assertEquals(2, $DB->count_records($tablename, array('course' => 10)));
3275 public function test_delete_records() {
3277 $dbman = $DB->get_manager();
3279 $table = $this->get_test_table();
3280 $tablename = $table->getName();
3282 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3283 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
3284 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
3285 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3286 $dbman->create_table($table);
3288 $DB->insert_record($tablename, array('course' => 3));
3289 $DB->insert_record($tablename, array('course' => 2));
3290 $DB->insert_record($tablename, array('course' => 2));
3292 // Delete all records.
3293 $this->assertTrue($DB->delete_records($tablename));
3294 $this->assertEquals(0, $DB->count_records($tablename));
3296 // Delete subset of records.
3297 $DB->insert_record($tablename, array('course' => 3));
3298 $DB->insert_record($tablename, array('course' => 2));
3299 $DB->insert_record($tablename, array('course' => 2));
3301 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
3302 $this->assertEquals(1, $DB->count_records($tablename));
3305 $this->assertTrue($DB->delete_records($tablenam