Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
- Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
- PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
/lib/dml/tests/ -> dml_test.php (source)
Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 39 and 311]
1 <?php 2 // This file is part of Moodle - http://moodle.org/ 3 // 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. 8 // 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. 13 // 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/>. 16 17 /** 18 * DML layer tests. 19 * 20 * @package core 21 * @category test 22 * @copyright 2008 Nicolas Connault 23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 24 */ 25 26 namespace core; 27 28 use dml_exception; 29 use dml_missing_record_exception; 30 use dml_multiple_records_exception; 31 use moodle_database; 32 use moodle_transaction; 33 use xmldb_key; 34 use xmldb_table; 35 36 defined('MOODLE_INTERNAL') || die(); 37 38 /** 39 * DML layer tests. 40 * 41 * @package core 42 * @category test 43 * @copyright 2008 Nicolas Connault 44 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 45 */ 46 class dml_test extends \database_driver_testcase { 47 48 protected function setUp(): void { 49 parent::setUp(); 50 $dbman = $this->tdb->get_manager(); // Loads DDL libs. 51 } 52 53 /** 54 * Get a xmldb_table object for testing, deleting any existing table 55 * of the same name, for example if one was left over from a previous test 56 * run that crashed. 57 * 58 * @param string $suffix table name suffix, use if you need more test tables 59 * @return xmldb_table the table object. 60 */ 61 private function get_test_table($suffix = '') { 62 $tablename = "test_table"; 63 if ($suffix !== '') { 64 $tablename .= $suffix; 65 } 66 67 $table = new xmldb_table($tablename); 68 $table->setComment("This is a test'n drop table. You can drop it safely"); 69 return $table; 70 } 71 72 /** 73 * Convert a unix string to a OS (dir separator) dependent string. 74 * 75 * @param string $source the original srting, using unix dir separators and newlines. 76 * @return string the resulting string, using current OS dir separators newlines. 77 */ 78 private function unix_to_os_dirsep(string $source): string { 79 if (DIRECTORY_SEPARATOR !== '/') { 80 return str_replace('/', DIRECTORY_SEPARATOR, $source); 81 } 82 return $source; // No changes, so far. 83 } 84 85 public function test_diagnose() { 86 $DB = $this->tdb; 87 $result = $DB->diagnose(); 88 $this->assertNull($result, 'Database self diagnostics failed %s'); 89 } 90 91 public function test_get_server_info() { 92 $DB = $this->tdb; 93 $result = $DB->get_server_info(); 94 $this->assertIsArray($result); 95 $this->assertArrayHasKey('description', $result); 96 $this->assertArrayHasKey('version', $result); 97 } 98 99 public function test_get_in_or_equal() { 100 $DB = $this->tdb; 101 102 // SQL_PARAMS_QM - IN or =. 103 104 // Correct usage of multiple values. 105 $in_values = array('value1', 'value2', '3', 4, null, false, true); 106 list($usql, $params) = $DB->get_in_or_equal($in_values); 107 $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql); 108 $this->assertEquals(count($in_values), count($params)); 109 foreach ($params as $key => $value) { 110 $this->assertSame($in_values[$key], $value); 111 } 112 113 // Correct usage of single value (in an array). 114 $in_values = array('value1'); 115 list($usql, $params) = $DB->get_in_or_equal($in_values); 116 $this->assertEquals("= ?", $usql); 117 $this->assertCount(1, $params); 118 $this->assertEquals($in_values[0], $params[0]); 119 120 // Correct usage of single value. 121 $in_value = 'value1'; 122 list($usql, $params) = $DB->get_in_or_equal($in_values); 123 $this->assertEquals("= ?", $usql); 124 $this->assertCount(1, $params); 125 $this->assertEquals($in_value, $params[0]); 126 127 // SQL_PARAMS_QM - NOT IN or <>. 128 129 // Correct usage of multiple values. 130 $in_values = array('value1', 'value2', 'value3', 'value4'); 131 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 132 $this->assertEquals("NOT IN (?,?,?,?)", $usql); 133 $this->assertCount(4, $params); 134 foreach ($params as $key => $value) { 135 $this->assertEquals($in_values[$key], $value); 136 } 137 138 // Correct usage of single value (in array(). 139 $in_values = array('value1'); 140 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 141 $this->assertEquals("<> ?", $usql); 142 $this->assertCount(1, $params); 143 $this->assertEquals($in_values[0], $params[0]); 144 145 // Correct usage of single value. 146 $in_value = 'value1'; 147 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 148 $this->assertEquals("<> ?", $usql); 149 $this->assertCount(1, $params); 150 $this->assertEquals($in_value, $params[0]); 151 152 // SQL_PARAMS_NAMED - IN or =. 153 154 // Correct usage of multiple values. 155 $in_values = array('value1', 'value2', 'value3', 'value4'); 156 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 157 $this->assertCount(4, $params); 158 reset($in_values); 159 $ps = array(); 160 foreach ($params as $key => $value) { 161 $this->assertEquals(current($in_values), $value); 162 next($in_values); 163 $ps[] = ':'.$key; 164 } 165 $this->assertEquals("IN (".implode(',', $ps).")", $usql); 166 167 // Correct usage of single values (in array). 168 $in_values = array('value1'); 169 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 170 $this->assertCount(1, $params); 171 $value = reset($params); 172 $key = key($params); 173 $this->assertEquals("= :$key", $usql); 174 $this->assertEquals($in_value, $value); 175 176 // Correct usage of single value. 177 $in_value = 'value1'; 178 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 179 $this->assertCount(1, $params); 180 $value = reset($params); 181 $key = key($params); 182 $this->assertEquals("= :$key", $usql); 183 $this->assertEquals($in_value, $value); 184 185 // SQL_PARAMS_NAMED - NOT IN or <>. 186 187 // Correct usage of multiple values. 188 $in_values = array('value1', 'value2', 'value3', 'value4'); 189 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 190 $this->assertCount(4, $params); 191 reset($in_values); 192 $ps = array(); 193 foreach ($params as $key => $value) { 194 $this->assertEquals(current($in_values), $value); 195 next($in_values); 196 $ps[] = ':'.$key; 197 } 198 $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql); 199 200 // Correct usage of single values (in array). 201 $in_values = array('value1'); 202 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 203 $this->assertCount(1, $params); 204 $value = reset($params); 205 $key = key($params); 206 $this->assertEquals("<> :$key", $usql); 207 $this->assertEquals($in_value, $value); 208 209 // Correct usage of single value. 210 $in_value = 'value1'; 211 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 212 $this->assertCount(1, $params); 213 $value = reset($params); 214 $key = key($params); 215 $this->assertEquals("<> :$key", $usql); 216 $this->assertEquals($in_value, $value); 217 218 // Make sure the param names are unique. 219 list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param'); 220 list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param'); 221 $params1 = array_keys($params1); 222 $params2 = array_keys($params2); 223 $common = array_intersect($params1, $params2); 224 $this->assertCount(0, $common); 225 226 // Some incorrect tests. 227 228 // Incorrect usage passing not-allowed params type. 229 $in_values = array(1, 2, 3); 230 try { 231 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false); 232 $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR'); 233 } catch (\moodle_exception $e) { 234 $this->assertInstanceOf('dml_exception', $e); 235 $this->assertSame('typenotimplement', $e->errorcode); 236 } 237 238 // Incorrect usage passing empty array. 239 $in_values = array(); 240 try { 241 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 242 $this->fail('An Exception is missing, expected due to empty array of items'); 243 } catch (\moodle_exception $e) { 244 $this->assertInstanceOf('coding_exception', $e); 245 } 246 247 // Test using $onemptyitems. 248 249 // Correct usage passing empty array and $onemptyitems = null (equal = true, QM). 250 $in_values = array(); 251 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null); 252 $this->assertSame(' IS NULL', $usql); 253 $this->assertSame(array(), $params); 254 255 // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED). 256 $in_values = array(); 257 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null); 258 $this->assertSame(' IS NOT NULL', $usql); 259 $this->assertSame(array(), $params); 260 261 // Correct usage passing empty array and $onemptyitems = true (equal = true, QM). 262 $in_values = array(); 263 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true); 264 $this->assertSame('= ?', $usql); 265 $this->assertSame(array(true), $params); 266 267 // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED). 268 $in_values = array(); 269 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true); 270 $this->assertCount(1, $params); 271 $value = reset($params); 272 $key = key($params); 273 $this->assertSame('<> :'.$key, $usql); 274 $this->assertSame($value, true); 275 276 // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM). 277 $in_values = array(); 278 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1); 279 $this->assertSame('= ?', $usql); 280 $this->assertSame(array(-1), $params); 281 282 // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED). 283 $in_values = array(); 284 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1); 285 $this->assertCount(1, $params); 286 $value = reset($params); 287 $key = key($params); 288 $this->assertSame('<> :'.$key, $usql); 289 $this->assertSame($value, -1); 290 291 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM). 292 $in_values = array(); 293 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue'); 294 $this->assertSame('= ?', $usql); 295 $this->assertSame(array('onevalue'), $params); 296 297 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED). 298 $in_values = array(); 299 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue'); 300 $this->assertCount(1, $params); 301 $value = reset($params); 302 $key = key($params); 303 $this->assertSame('<> :'.$key, $usql); 304 $this->assertSame($value, 'onevalue'); 305 } 306 307 public function test_fix_table_names() { 308 $DB = new moodle_database_for_testing(); 309 $prefix = $DB->get_prefix(); 310 311 // Simple placeholder. 312 $placeholder = "{user_123}"; 313 $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder)); 314 315 // Wrong table name. 316 $placeholder = "{user-a}"; 317 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder)); 318 319 // Wrong table name. 320 $placeholder = "{123user}"; 321 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder)); 322 323 // Full SQL. 324 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid"; 325 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid"; 326 $this->assertSame($expected, $DB->public_fix_table_names($sql)); 327 } 328 329 public function test_fix_sql_params() { 330 $DB = $this->tdb; 331 $prefix = $DB->get_prefix(); 332 333 $table = $this->get_test_table(); 334 $tablename = $table->getName(); 335 336 // Correct table placeholder substitution. 337 $sql = "SELECT * FROM {{$tablename}}"; 338 $sqlarray = $DB->fix_sql_params($sql); 339 $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]); 340 341 // Conversions of all param types. 342 $sql = array(); 343 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2"; 344 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?"; 345 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2"; 346 347 $params = array(); 348 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1); 349 $params[SQL_PARAMS_QM] = array('first record', 1); 350 $params[SQL_PARAMS_DOLLAR] = array('first record', 1); 351 352 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]); 353 $this->assertSame($rsql, $sql[$rtype]); 354 $this->assertSame($rparams, $params[$rtype]); 355 356 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]); 357 $this->assertSame($rsql, $sql[$rtype]); 358 $this->assertSame($rparams, $params[$rtype]); 359 360 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]); 361 $this->assertSame($rsql, $sql[$rtype]); 362 $this->assertSame($rparams, $params[$rtype]); 363 364 // Malformed table placeholder. 365 $sql = "SELECT * FROM [testtable]"; 366 $sqlarray = $DB->fix_sql_params($sql); 367 $this->assertSame($sql, $sqlarray[0]); 368 369 // Mixed param types (colon and dollar). 370 $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1"; 371 $params = array('param1' => 'record1', 'param2' => 3); 372 try { 373 $DB->fix_sql_params($sql, $params); 374 $this->fail("Expecting an exception, none occurred"); 375 } catch (\moodle_exception $e) { 376 $this->assertInstanceOf('dml_exception', $e); 377 } 378 379 // Mixed param types (question and dollar). 380 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1"; 381 $params = array('param1' => 'record2', 'param2' => 5); 382 try { 383 $DB->fix_sql_params($sql, $params); 384 $this->fail("Expecting an exception, none occurred"); 385 } catch (\moodle_exception $e) { 386 $this->assertInstanceOf('dml_exception', $e); 387 } 388 389 // Too few params in sql. 390 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?"; 391 $params = array('record2', 3); 392 try { 393 $DB->fix_sql_params($sql, $params); 394 $this->fail("Expecting an exception, none occurred"); 395 } catch (\moodle_exception $e) { 396 $this->assertInstanceOf('dml_exception', $e); 397 } 398 399 // Too many params in array: no error, just use what is necessary. 400 $params[] = 1; 401 $params[] = time(); 402 $sqlarray = $DB->fix_sql_params($sql, $params); 403 $this->assertIsArray($sqlarray); 404 $this->assertCount(3, $sqlarray[1]); 405 406 // Named params missing from array. 407 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; 408 $params = array('wrongname' => 'record1', 'course' => 1); 409 try { 410 $DB->fix_sql_params($sql, $params); 411 $this->fail("Expecting an exception, none occurred"); 412 } catch (\moodle_exception $e) { 413 $this->assertInstanceOf('dml_exception', $e); 414 } 415 416 // Duplicate named param in query - this is a very important feature!! 417 // it helps with debugging of sloppy code. 418 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name"; 419 $params = array('name' => 'record2', 'course' => 3); 420 try { 421 $DB->fix_sql_params($sql, $params); 422 $this->fail("Expecting an exception, none occurred"); 423 } catch (\moodle_exception $e) { 424 $this->assertInstanceOf('dml_exception', $e); 425 } 426 427 // Extra named param is ignored. 428 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; 429 $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha'); 430 $sqlarray = $DB->fix_sql_params($sql, $params); 431 $this->assertIsArray($sqlarray); 432 $this->assertCount(2, $sqlarray[1]); 433 434 // Params exceeding 30 chars length. 435 $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30"; 436 $params = array('long_placeholder_with_more_than_30' => 'record1'); 437 try { 438 $DB->fix_sql_params($sql, $params); 439 $this->fail("Expecting an exception, none occurred"); 440 } catch (\moodle_exception $e) { 441 $this->assertInstanceOf('coding_exception', $e); 442 } 443 444 // Booleans in NAMED params are casting to 1/0 int. 445 $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?"; 446 $params = array(true, false); 447 list($sql, $params) = $DB->fix_sql_params($sql, $params); 448 $this->assertTrue(reset($params) === 1); 449 $this->assertTrue(next($params) === 0); 450 451 // Booleans in QM params are casting to 1/0 int. 452 $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2"; 453 $params = array('course1' => true, 'course2' => false); 454 list($sql, $params) = $DB->fix_sql_params($sql, $params); 455 $this->assertTrue(reset($params) === 1); 456 $this->assertTrue(next($params) === 0); 457 458 // Booleans in DOLLAR params are casting to 1/0 int. 459 $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2"; 460 $params = array(true, false); 461 list($sql, $params) = $DB->fix_sql_params($sql, $params); 462 $this->assertTrue(reset($params) === 1); 463 $this->assertTrue(next($params) === 0); 464 465 // No data types are touched except bool. 466 $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)"; 467 $inparams = array('abc', 'ABC', null, '1', 1, 1.4); 468 list($sql, $params) = $DB->fix_sql_params($sql, $inparams); 469 $this->assertSame(array_values($params), array_values($inparams)); 470 } 471 472 /** 473 * Test the database debugging as SQL comment. 474 */ 475 public function test_add_sql_debugging() { 476 global $CFG; 477 $DB = $this->tdb; 478 479 require_once($CFG->dirroot . '/lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php'); 480 $fixture = new \test_dml_sql_debugging_fixture($this); 481 482 $sql = "SELECT * FROM {users}"; 483 484 $out = $fixture->four($sql); 485 486 $CFG->debugsqltrace = 0; 487 $this->assertEquals("SELECT * FROM {users}", $out); 488 489 $CFG->debugsqltrace = 1; 490 $out = $fixture->four($sql); 491 $expected = <<<EOD 492 SELECT * FROM {users} 493 -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke() 494 EOD; 495 $this->assertEquals($this->unix_to_os_dirsep($expected), $out); 496 497 $CFG->debugsqltrace = 2; 498 $out = $fixture->four($sql); 499 $expected = <<<EOD 500 SELECT * FROM {users} 501 -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke() 502 -- line 74 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one() 503 EOD; 504 $this->assertEquals($this->unix_to_os_dirsep($expected), $out); 505 506 $CFG->debugsqltrace = 5; 507 $out = $fixture->four($sql); 508 $expected = <<<EOD 509 SELECT * FROM {users} 510 -- line 65 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to ReflectionMethod->invoke() 511 -- line 74 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->one() 512 -- line 83 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->two() 513 -- line 92 of /lib/dml/tests/fixtures/test_dml_sql_debugging_fixture.php: call to test_dml_sql_debugging_fixture->three() 514 -- line 507 of /lib/dml/tests/dml_test.php: call to test_dml_sql_debugging_fixture->four() 515 EOD; 516 $this->assertEquals($this->unix_to_os_dirsep($expected), $out); 517 518 $CFG->debugsqltrace = 0; 519 } 520 521 /** 522 * Test the database debugging as SQL comment in anon class 523 * 524 * @covers ::add_sql_debugging 525 */ 526 public function test_sql_debugging_anon_class() { 527 global $CFG; 528 $CFG->debugsqltrace = 100; 529 530 // A anon class. 531 $another = new class { 532 /** 533 * Just a test log function 534 */ 535 public function get_site() { 536 global $DB; 537 538 return $DB->get_record('course', ['category' => 0]); 539 } 540 }; 541 $site = $another->get_site(); 542 $CFG->debugsqltrace = 0; 543 $this->assertEquals(get_site(), $site); 544 } 545 546 public function test_strtok() { 547 // Strtok was previously used by bound emulation, make sure it is not used any more. 548 $DB = $this->tdb; 549 $dbman = $this->tdb->get_manager(); 550 551 $table = $this->get_test_table(); 552 $tablename = $table->getName(); 553 554 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 555 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 556 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala'); 557 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 558 $dbman->create_table($table); 559 560 $str = 'a?b?c?d'; 561 $this->assertSame(strtok($str, '?'), 'a'); 562 563 $DB->get_records($tablename, array('id'=>1)); 564 565 $this->assertSame(strtok('?'), 'b'); 566 } 567 568 public function test_tweak_param_names() { 569 // Note the tweak_param_names() method is only available in the oracle driver, 570 // hence we look for expected results indirectly, by testing various DML methods. 571 // with some "extreme" conditions causing the tweak to happen. 572 $DB = $this->tdb; 573 $dbman = $this->tdb->get_manager(); 574 575 $table = $this->get_test_table(); 576 $tablename = $table->getName(); 577 578 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 579 // Add some columns with 28 chars in the name. 580 $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10'); 581 $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2'); 582 $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100'); 583 // Add some columns with 30 chars in the name. 584 $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10'); 585 $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2'); 586 $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100'); 587 588 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 589 590 $dbman->create_table($table); 591 592 $this->assertTrue($dbman->table_exists($tablename)); 593 594 // Test insert record. 595 $rec1 = new \stdClass(); 596 $rec1->long_int_columnname_with_28c = 28; 597 $rec1->long_dec_columnname_with_28c = 28.28; 598 $rec1->long_str_columnname_with_28c = '28'; 599 $rec1->long_int_columnname_with_30cxx = 30; 600 $rec1->long_dec_columnname_with_30cxx = 30.30; 601 $rec1->long_str_columnname_with_30cxx = '30'; 602 603 // Insert_record(). 604 $rec1->id = $DB->insert_record($tablename, $rec1); 605 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 606 607 // Update_record(). 608 $DB->update_record($tablename, $rec1); 609 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 610 611 // Set_field(). 612 $rec1->long_int_columnname_with_28c = 280; 613 $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c, 614 array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28)); 615 $rec1->long_dec_columnname_with_28c = 280.28; 616 $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c, 617 array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28)); 618 $rec1->long_str_columnname_with_28c = '280'; 619 $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c, 620 array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28')); 621 $rec1->long_int_columnname_with_30cxx = 300; 622 $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx, 623 array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30)); 624 $rec1->long_dec_columnname_with_30cxx = 300.30; 625 $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx, 626 array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30)); 627 $rec1->long_str_columnname_with_30cxx = '300'; 628 $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx, 629 array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30')); 630 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 631 632 // Delete_records(). 633 $rec2 = $DB->get_record($tablename, array('id' => $rec1->id)); 634 $rec2->id = $DB->insert_record($tablename, $rec2); 635 $this->assertEquals(2, $DB->count_records($tablename)); 636 $DB->delete_records($tablename, (array) $rec2); 637 $this->assertEquals(1, $DB->count_records($tablename)); 638 639 // Get_recordset(). 640 $rs = $DB->get_recordset($tablename, (array) $rec1); 641 $iterations = 0; 642 foreach ($rs as $rec2) { 643 $iterations++; 644 } 645 $rs->close(); 646 $this->assertEquals(1, $iterations); 647 $this->assertEquals($rec1, $rec2); 648 649 // Get_records(). 650 $recs = $DB->get_records($tablename, (array) $rec1); 651 $this->assertCount(1, $recs); 652 $this->assertEquals($rec1, reset($recs)); 653 654 // Get_fieldset_select(). 655 $select = 'id = :id AND 656 long_int_columnname_with_28c = :long_int_columnname_with_28c AND 657 long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND 658 long_str_columnname_with_28c = :long_str_columnname_with_28c AND 659 long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND 660 long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND 661 long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx'; 662 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1); 663 $this->assertCount(1, $fields); 664 $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields)); 665 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1); 666 $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields)); 667 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1); 668 $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields)); 669 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1); 670 $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields)); 671 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1); 672 $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields)); 673 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1); 674 $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields)); 675 676 // Overlapping placeholders (progressive str_replace). 677 $overlapselect = 'id = :p AND 678 long_int_columnname_with_28c = :param1 AND 679 long_dec_columnname_with_28c = :param2 AND 680 long_str_columnname_with_28c = :param_with_29_characters_long AND 681 long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND 682 long_dec_columnname_with_30cxx = :param_ AND 683 long_str_columnname_with_30cxx = :param__'; 684 $overlapparams = array( 685 'p' => $rec1->id, 686 'param1' => $rec1->long_int_columnname_with_28c, 687 'param2' => $rec1->long_dec_columnname_with_28c, 688 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c, 689 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx, 690 'param_' => $rec1->long_dec_columnname_with_30cxx, 691 'param__' => $rec1->long_str_columnname_with_30cxx); 692 $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams); 693 $this->assertCount(1, $recs); 694 $this->assertEquals($rec1, reset($recs)); 695 696 // Execute(). 697 $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1); 698 $this->assertEquals(0, $DB->count_records($tablename)); 699 } 700 701 public function test_get_tables() { 702 $DB = $this->tdb; 703 $dbman = $this->tdb->get_manager(); 704 705 // Need to test with multiple DBs. 706 $table = $this->get_test_table(); 707 $tablename = $table->getName(); 708 709 $original_count = count($DB->get_tables()); 710 711 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 712 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 713 714 $dbman->create_table($table); 715 $this->assertTrue(count($DB->get_tables()) == $original_count + 1); 716 717 $dbman->drop_table($table); 718 $this->assertTrue(count($DB->get_tables()) == $original_count); 719 } 720 721 public function test_get_indexes() { 722 $DB = $this->tdb; 723 $dbman = $this->tdb->get_manager(); 724 725 $table = $this->get_test_table(); 726 $tablename = $table->getName(); 727 728 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 729 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 730 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 731 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 732 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id')); 733 $dbman->create_table($table); 734 735 $indices = $DB->get_indexes($tablename); 736 $this->assertIsArray($indices); 737 $this->assertCount(2, $indices); 738 // We do not care about index names for now. 739 $first = array_shift($indices); 740 $second = array_shift($indices); 741 if (count($first['columns']) == 2) { 742 $composed = $first; 743 $single = $second; 744 } else { 745 $composed = $second; 746 $single = $first; 747 } 748 $this->assertFalse($single['unique']); 749 $this->assertTrue($composed['unique']); 750 $this->assertCount(1, $single['columns']); 751 $this->assertCount(2, $composed['columns']); 752 $this->assertSame('course', $single['columns'][0]); 753 $this->assertSame('course', $composed['columns'][0]); 754 $this->assertSame('id', $composed['columns'][1]); 755 } 756 757 /** 758 * Let's verify get_indexes() when we mix null and not null columns in unique indexes. 759 * 760 * Some databases, for unique indexes of this type, need to create function indexes to 761 * provide cross-db behaviour. Here we check that those indexes don't break get_indexes(). 762 * 763 * Note that, strictly speaking, unique indexes on null columns are far from ideal. Both 764 * conceptually and also in practice, because they cause DBs to use full scans in a 765 * number of situations. But if we support them, we need to ensure get_indexes() work on them. 766 */ 767 public function test_get_indexes_unique_mixed_nullability() { 768 $DB = $this->tdb; 769 $dbman = $this->tdb->get_manager(); 770 $table = $this->get_test_table(); 771 $tablename = $table->getName(); 772 773 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 774 $table->add_field('nullable01', XMLDB_TYPE_INTEGER, 10, null, null, null, null); 775 $table->add_field('nullable02', XMLDB_TYPE_INTEGER, 10, null, null, null, null); 776 $table->add_field('nonullable01', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 777 $table->add_field('nonullable02', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 778 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 779 $indexcolumns = ['nullable01', 'nonullable01', 'nullable02', 'nonullable02']; 780 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, $indexcolumns); 781 $dbman->create_table($table); 782 783 $indexes = $DB->get_indexes($tablename); 784 $this->assertIsArray($indexes); 785 $this->assertCount(1, $indexes); 786 787 $index = array_shift($indexes); 788 $this->assertTrue($index['unique']); 789 $this->assertSame($indexcolumns, $index['columns']); 790 } 791 792 public function test_get_columns() { 793 $DB = $this->tdb; 794 $dbman = $this->tdb->get_manager(); 795 796 $table = $this->get_test_table(); 797 $tablename = $table->getName(); 798 799 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 800 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 801 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala'); 802 $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 803 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 804 $table->add_field('oneintnodefault', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null); 805 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2'); 806 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 807 $table->add_field('onenumnodefault', XMLDB_TYPE_NUMBER, '10,2', null, null, null); 808 $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null, 300); 809 $table->add_field('onefloatnodefault', XMLDB_TYPE_FLOAT, '10,2', null, XMLDB_NOTNULL, null); 810 $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400); 811 $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1'); 812 $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2'); 813 $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3'); 814 $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0'); 815 $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0'); 816 $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0'); 817 $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0'); 818 $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0'); 819 $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0'); 820 $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0'); 821 $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0'); 822 $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0'); 823 $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0'); 824 $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0'); 825 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 826 $dbman->create_table($table); 827 828 $columns = $DB->get_columns($tablename); 829 $this->assertIsArray($columns); 830 831 $fields = $table->getFields(); 832 $this->assertCount(count($columns), $fields); 833 834 $field = $columns['id']; 835 $this->assertSame('R', $field->meta_type); 836 $this->assertTrue($field->auto_increment); 837 $this->assertTrue($field->unique); 838 839 $field = $columns['course']; 840 $this->assertSame('I', $field->meta_type); 841 $this->assertFalse($field->auto_increment); 842 $this->assertTrue($field->has_default); 843 $this->assertEquals(0, $field->default_value); 844 $this->assertTrue($field->not_null); 845 846 for ($i=1; $i<=10; $i++) { 847 $field = $columns['someint'.$i]; 848 $this->assertSame('I', $field->meta_type); 849 $this->assertGreaterThanOrEqual($i, $field->max_length); 850 } 851 $field = $columns['someint18']; 852 $this->assertSame('I', $field->meta_type); 853 $this->assertGreaterThanOrEqual(18, $field->max_length); 854 855 $field = $columns['name']; 856 $this->assertSame('C', $field->meta_type); 857 $this->assertFalse($field->auto_increment); 858 $this->assertEquals(255, $field->max_length); 859 $this->assertTrue($field->has_default); 860 $this->assertSame('lala', $field->default_value); 861 $this->assertFalse($field->not_null); 862 863 $field = $columns['description']; 864 $this->assertSame('X', $field->meta_type); 865 $this->assertFalse($field->auto_increment); 866 $this->assertFalse($field->has_default); 867 $this->assertNull($field->default_value); 868 $this->assertFalse($field->not_null); 869 870 $field = $columns['oneint']; 871 $this->assertSame('I', $field->meta_type); 872 $this->assertFalse($field->auto_increment); 873 $this->assertTrue($field->has_default); 874 $this->assertEquals(0, $field->default_value); 875 $this->assertTrue($field->not_null); 876 877 $field = $columns['oneintnodefault']; 878 $this->assertSame('I', $field->meta_type); 879 $this->assertFalse($field->auto_increment); 880 $this->assertFalse($field->has_default); 881 $this->assertNull($field->default_value); 882 $this->assertTrue($field->not_null); 883 884 $field = $columns['enumfield']; 885 $this->assertSame('C', $field->meta_type); 886 $this->assertFalse($field->auto_increment); 887 $this->assertSame('test2', $field->default_value); 888 $this->assertTrue($field->not_null); 889 890 $field = $columns['onenum']; 891 $this->assertSame('N', $field->meta_type); 892 $this->assertFalse($field->auto_increment); 893 $this->assertEquals(10, $field->max_length); 894 $this->assertEquals(2, $field->scale); 895 $this->assertTrue($field->has_default); 896 $this->assertEquals(200.0, $field->default_value); 897 $this->assertFalse($field->not_null); 898 899 $field = $columns['onenumnodefault']; 900 $this->assertSame('N', $field->meta_type); 901 $this->assertFalse($field->auto_increment); 902 $this->assertEquals(10, $field->max_length); 903 $this->assertEquals(2, $field->scale); 904 $this->assertFalse($field->has_default); 905 $this->assertNull($field->default_value); 906 $this->assertFalse($field->not_null); 907 908 $field = $columns['onefloat']; 909 $this->assertSame('N', $field->meta_type); 910 $this->assertFalse($field->auto_increment); 911 $this->assertTrue($field->has_default); 912 $this->assertEquals(300.0, $field->default_value); 913 $this->assertTrue($field->not_null); 914 915 $field = $columns['onefloatnodefault']; 916 $this->assertSame('N', $field->meta_type); 917 $this->assertFalse($field->auto_increment); 918 $this->assertFalse($field->has_default); 919 $this->assertNull($field->default_value); 920 $this->assertTrue($field->not_null); 921 922 $field = $columns['anotherfloat']; 923 $this->assertSame('N', $field->meta_type); 924 $this->assertFalse($field->auto_increment); 925 $this->assertTrue($field->has_default); 926 $this->assertEquals(400.0, $field->default_value); 927 $this->assertFalse($field->not_null); 928 929 // Test negative defaults in numerical columns. 930 $field = $columns['negativedfltint']; 931 $this->assertTrue($field->has_default); 932 $this->assertEquals(-1, $field->default_value); 933 934 $field = $columns['negativedfltnumber']; 935 $this->assertTrue($field->has_default); 936 $this->assertEquals(-2, $field->default_value); 937 938 $field = $columns['negativedfltfloat']; 939 $this->assertTrue($field->has_default); 940 $this->assertEquals(-3, $field->default_value); 941 942 for ($i = 0; $i < count($columns); $i++) { 943 if ($i == 0) { 944 $next_column = reset($columns); 945 $next_field = reset($fields); 946 } else { 947 $next_column = next($columns); 948 $next_field = next($fields); 949 } 950 951 $this->assertEquals($next_column->name, $next_field->getName()); 952 } 953 954 // Test get_columns for non-existing table returns empty array. MDL-30147. 955 $columns = $DB->get_columns('xxxx'); 956 $this->assertEquals(array(), $columns); 957 958 // Create something similar to "context_temp" with id column without sequence. 959 $dbman->drop_table($table); 960 $table = $this->get_test_table(); 961 $tablename = $table->getName(); 962 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); 963 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 964 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 965 $dbman->create_table($table); 966 967 $columns = $DB->get_columns($tablename); 968 $this->assertFalse($columns['id']->auto_increment); 969 } 970 971 public function test_get_manager() { 972 $DB = $this->tdb; 973 $dbman = $this->tdb->get_manager(); 974 975 $this->assertInstanceOf('database_manager', $dbman); 976 } 977 978 public function test_setup_is_unicodedb() { 979 $DB = $this->tdb; 980 $this->assertTrue($DB->setup_is_unicodedb()); 981 } 982 983 public function test_set_debug() { // Tests get_debug() too. 984 $DB = $this->tdb; 985 $dbman = $this->tdb->get_manager(); 986 987 $table = $this->get_test_table(); 988 $tablename = $table->getName(); 989 990 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 991 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 992 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 993 $dbman->create_table($table); 994 995 $sql = "SELECT * FROM {{$tablename}}"; 996 997 $prevdebug = $DB->get_debug(); 998 999 ob_start(); 1000 $DB->set_debug(true); 1001 $this->assertTrue($DB->get_debug()); 1002 $DB->execute($sql); 1003 $DB->set_debug(false); 1004 $this->assertFalse($DB->get_debug()); 1005 $debuginfo = ob_get_contents(); 1006 ob_end_clean(); 1007 $this->assertFalse($debuginfo === ''); 1008 1009 ob_start(); 1010 $DB->execute($sql); 1011 $debuginfo = ob_get_contents(); 1012 ob_end_clean(); 1013 $this->assertTrue($debuginfo === ''); 1014 1015 $DB->set_debug($prevdebug); 1016 } 1017 1018 public function test_execute() { 1019 $DB = $this->tdb; 1020 $dbman = $this->tdb->get_manager(); 1021 1022 $table1 = $this->get_test_table('1'); 1023 $tablename1 = $table1->getName(); 1024 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1025 $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1026 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 1027 $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1028 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1029 $dbman->create_table($table1); 1030 1031 $table2 = $this->get_test_table('2'); 1032 $tablename2 = $table2->getName(); 1033 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1034 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1035 $table2->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1036 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1037 $dbman->create_table($table2); 1038 1039 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa')); 1040 $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb')); 1041 $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc')); 1042 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd')); 1043 1044 // Select results are ignored. 1045 $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course"; 1046 $this->assertTrue($DB->execute($sql, array('course'=>3))); 1047 1048 // Throw exception on error. 1049 $sql = "XXUPDATE SET XSSD"; 1050 try { 1051 $DB->execute($sql); 1052 $this->fail("Expecting an exception, none occurred"); 1053 } catch (\moodle_exception $e) { 1054 $this->assertInstanceOf('dml_exception', $e); 1055 } 1056 1057 // Update records. 1058 $sql = "UPDATE {{$tablename1}} 1059 SET course = 6 1060 WHERE course = ?"; 1061 $this->assertTrue($DB->execute($sql, array('3'))); 1062 $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6))); 1063 1064 // Update records with subquery condition. 1065 // Confirm that the option not using table aliases is cross-db. 1066 $sql = "UPDATE {{$tablename1}} 1067 SET course = 0 1068 WHERE NOT EXISTS ( 1069 SELECT course 1070 FROM {{$tablename2}} tbl2 1071 WHERE tbl2.course = {{$tablename1}}.course 1072 AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed. 1073 $this->assertTrue($DB->execute($sql)); 1074 1075 // Insert from one into second table. 1076 $sql = "INSERT INTO {{$tablename2}} (course) 1077 1078 SELECT course 1079 FROM {{$tablename1}}"; 1080 $this->assertTrue($DB->execute($sql)); 1081 $this->assertEquals(4, $DB->count_records($tablename2)); 1082 1083 // Insert a TEXT with raw SQL, binding TEXT params. 1084 $course = 9999; 1085 $onetext = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 1086 $sql = "INSERT INTO {{$tablename2}} (course, onetext) 1087 VALUES (:course, :onetext)"; 1088 $DB->execute($sql, array('course' => $course, 'onetext' => $onetext)); 1089 $records = $DB->get_records($tablename2, array('course' => $course)); 1090 $this->assertCount(1, $records); 1091 $record = reset($records); 1092 $this->assertSame($onetext, $record->onetext); 1093 1094 // Update a TEXT with raw SQL, binding TEXT params. 1095 $newcourse = 10000; 1096 $newonetext = file_get_contents(__DIR__ . '/fixtures/clob.txt') . '- updated'; 1097 $sql = "UPDATE {{$tablename2}} SET course = :newcourse, onetext = :newonetext 1098 WHERE course = :oldcourse"; 1099 $DB->execute($sql, array('oldcourse' => $course, 'newcourse' => $newcourse, 'newonetext' => $newonetext)); 1100 $records = $DB->get_records($tablename2, array('course' => $course)); 1101 $this->assertCount(0, $records); 1102 $records = $DB->get_records($tablename2, array('course' => $newcourse)); 1103 $this->assertCount(1, $records); 1104 $record = reset($records); 1105 $this->assertSame($newonetext, $record->onetext); 1106 } 1107 1108 public function test_get_recordset() { 1109 $DB = $this->tdb; 1110 $dbman = $DB->get_manager(); 1111 1112 $table = $this->get_test_table(); 1113 $tablename = $table->getName(); 1114 1115 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1116 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1117 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 1118 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1119 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1120 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1121 $dbman->create_table($table); 1122 1123 $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'), 1124 array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'), 1125 array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde')); 1126 1127 foreach ($data as $key => $record) { 1128 $data[$key]['id'] = $DB->insert_record($tablename, $record); 1129 } 1130 1131 // Standard recordset iteration. 1132 $rs = $DB->get_recordset($tablename); 1133 $this->assertInstanceOf('moodle_recordset', $rs); 1134 reset($data); 1135 foreach ($rs as $record) { 1136 $data_record = current($data); 1137 foreach ($record as $k => $v) { 1138 $this->assertEquals($data_record[$k], $v); 1139 } 1140 next($data); 1141 } 1142 $rs->close(); 1143 1144 // Iterator style usage. 1145 $rs = $DB->get_recordset($tablename); 1146 $this->assertInstanceOf('moodle_recordset', $rs); 1147 reset($data); 1148 while ($rs->valid()) { 1149 $record = $rs->current(); 1150 $data_record = current($data); 1151 foreach ($record as $k => $v) { 1152 $this->assertEquals($data_record[$k], $v); 1153 } 1154 next($data); 1155 $rs->next(); 1156 } 1157 $rs->close(); 1158 1159 // Make sure rewind is ignored. 1160 $rs = $DB->get_recordset($tablename); 1161 $this->assertInstanceOf('moodle_recordset', $rs); 1162 reset($data); 1163 $i = 0; 1164 foreach ($rs as $record) { 1165 $i++; 1166 $rs->rewind(); 1167 if ($i > 10) { 1168 $this->fail('revind not ignored in recordsets'); 1169 break; 1170 } 1171 $data_record = current($data); 1172 foreach ($record as $k => $v) { 1173 $this->assertEquals($data_record[$k], $v); 1174 } 1175 next($data); 1176 } 1177 $rs->close(); 1178 1179 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 1180 $conditions = array('onetext' => '1'); 1181 try { 1182 $rs = $DB->get_recordset($tablename, $conditions); 1183 $this->fail('An Exception is missing, expected due to equating of text fields'); 1184 } catch (\moodle_exception $e) { 1185 $this->assertInstanceOf('dml_exception', $e); 1186 $this->assertSame('textconditionsnotallowed', $e->errorcode); 1187 } 1188 1189 // Test nested iteration. 1190 $rs1 = $DB->get_recordset($tablename); 1191 $i = 0; 1192 foreach ($rs1 as $record1) { 1193 $rs2 = $DB->get_recordset($tablename); 1194 $i++; 1195 $j = 0; 1196 foreach ($rs2 as $record2) { 1197 $j++; 1198 } 1199 $rs2->close(); 1200 $this->assertCount($j, $data); 1201 } 1202 $rs1->close(); 1203 $this->assertCount($i, $data); 1204 1205 // Notes: 1206 // * limits are tested in test_get_recordset_sql() 1207 // * where_clause() is used internally and is tested in test_get_records() 1208 } 1209 1210 public function test_get_recordset_static() { 1211 $DB = $this->tdb; 1212 $dbman = $DB->get_manager(); 1213 1214 $table = $this->get_test_table(); 1215 $tablename = $table->getName(); 1216 1217 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1218 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1219 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1220 $dbman->create_table($table); 1221 1222 $DB->insert_record($tablename, array('course' => 1)); 1223 $DB->insert_record($tablename, array('course' => 2)); 1224 $DB->insert_record($tablename, array('course' => 3)); 1225 $DB->insert_record($tablename, array('course' => 4)); 1226 1227 $rs = $DB->get_recordset($tablename, array(), 'id'); 1228 1229 $DB->set_field($tablename, 'course', 666, array('course'=>1)); 1230 $DB->delete_records($tablename, array('course'=>2)); 1231 1232 $i = 0; 1233 foreach ($rs as $record) { 1234 $i++; 1235 $this->assertEquals($i, $record->course); 1236 } 1237 $rs->close(); 1238 $this->assertEquals(4, $i); 1239 1240 // Now repeat with limits because it may use different code. 1241 $DB->delete_records($tablename, array()); 1242 1243 $DB->insert_record($tablename, array('course' => 1)); 1244 $DB->insert_record($tablename, array('course' => 2)); 1245 $DB->insert_record($tablename, array('course' => 3)); 1246 $DB->insert_record($tablename, array('course' => 4)); 1247 1248 $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3); 1249 1250 $DB->set_field($tablename, 'course', 666, array('course'=>1)); 1251 $DB->delete_records($tablename, array('course'=>2)); 1252 1253 $i = 0; 1254 foreach ($rs as $record) { 1255 $i++; 1256 $this->assertEquals($i, $record->course); 1257 } 1258 $rs->close(); 1259 $this->assertEquals(3, $i); 1260 } 1261 1262 public function test_get_recordset_iterator_keys() { 1263 $DB = $this->tdb; 1264 $dbman = $DB->get_manager(); 1265 1266 $table = $this->get_test_table(); 1267 $tablename = $table->getName(); 1268 1269 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1270 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1271 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 1272 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1273 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1274 $dbman->create_table($table); 1275 1276 $data = array(array('course' => 3, 'name' => 'record1'), 1277 array('course' => 3, 'name' => 'record2'), 1278 array('course' => 5, 'name' => 'record3')); 1279 foreach ($data as $key => $record) { 1280 $data[$key]['id'] = $DB->insert_record($tablename, $record); 1281 } 1282 1283 // Test repeated numeric keys are returned ok. 1284 $rs = $DB->get_recordset($tablename, null, null, 'course, name, id'); 1285 1286 reset($data); 1287 $count = 0; 1288 foreach ($rs as $key => $record) { 1289 $data_record = current($data); 1290 $this->assertEquals($data_record['course'], $key); 1291 next($data); 1292 $count++; 1293 } 1294 $rs->close(); 1295 $this->assertEquals(3, $count); 1296 1297 // Test string keys are returned ok. 1298 $rs = $DB->get_recordset($tablename, null, null, 'name, course, id'); 1299 1300 reset($data); 1301 $count = 0; 1302 foreach ($rs as $key => $record) { 1303 $data_record = current($data); 1304 $this->assertEquals($data_record['name'], $key); 1305 next($data); 1306 $count++; 1307 } 1308 $rs->close(); 1309 $this->assertEquals(3, $count); 1310 1311 // Test numeric not starting in 1 keys are returned ok. 1312 $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name'); 1313 1314 $data = array_reverse($data); 1315 reset($data); 1316 $count = 0; 1317 foreach ($rs as $key => $record) { 1318 $data_record = current($data); 1319 $this->assertEquals($data_record['id'], $key); 1320 next($data); 1321 $count++; 1322 } 1323 $rs->close(); 1324 $this->assertEquals(3, $count); 1325 } 1326 1327 public function test_get_recordset_list() { 1328 $DB = $this->tdb; 1329 $dbman = $DB->get_manager(); 1330 1331 $table = $this->get_test_table(); 1332 $tablename = $table->getName(); 1333 1334 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1335 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0'); 1336 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1337 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1338 $dbman->create_table($table); 1339 1340 $DB->insert_record($tablename, array('course' => 3)); 1341 $DB->insert_record($tablename, array('course' => 3)); 1342 $DB->insert_record($tablename, array('course' => 5)); 1343 $DB->insert_record($tablename, array('course' => 2)); 1344 $DB->insert_record($tablename, array('course' => null)); 1345 $DB->insert_record($tablename, array('course' => 1)); 1346 $DB->insert_record($tablename, array('course' => 0)); 1347 1348 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2)); 1349 $counter = 0; 1350 foreach ($rs as $record) { 1351 $counter++; 1352 } 1353 $this->assertEquals(3, $counter); 1354 $rs->close(); 1355 1356 $rs = $DB->get_recordset_list($tablename, 'course', array(3)); 1357 $counter = 0; 1358 foreach ($rs as $record) { 1359 $counter++; 1360 } 1361 $this->assertEquals(2, $counter); 1362 $rs->close(); 1363 1364 $rs = $DB->get_recordset_list($tablename, 'course', array(null)); 1365 $counter = 0; 1366 foreach ($rs as $record) { 1367 $counter++; 1368 } 1369 $this->assertEquals(1, $counter); 1370 $rs->close(); 1371 1372 $rs = $DB->get_recordset_list($tablename, 'course', array(6, null)); 1373 $counter = 0; 1374 foreach ($rs as $record) { 1375 $counter++; 1376 } 1377 $this->assertEquals(1, $counter); 1378 $rs->close(); 1379 1380 $rs = $DB->get_recordset_list($tablename, 'course', array(null, 5, 5, 5)); 1381 $counter = 0; 1382 foreach ($rs as $record) { 1383 $counter++; 1384 } 1385 $this->assertEquals(2, $counter); 1386 $rs->close(); 1387 1388 $rs = $DB->get_recordset_list($tablename, 'course', array(true)); 1389 $counter = 0; 1390 foreach ($rs as $record) { 1391 $counter++; 1392 } 1393 $this->assertEquals(1, $counter); 1394 $rs->close(); 1395 1396 $rs = $DB->get_recordset_list($tablename, 'course', array(false)); 1397 $counter = 0; 1398 foreach ($rs as $record) { 1399 $counter++; 1400 } 1401 $this->assertEquals(1, $counter); 1402 $rs->close(); 1403 1404 $rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645. 1405 1406 $counter = 0; 1407 foreach ($rs as $record) { 1408 $counter++; 1409 } 1410 $rs->close(); 1411 $this->assertEquals(0, $counter); 1412 1413 // Notes: 1414 // * limits are tested in test_get_recordset_sql() 1415 // * where_clause() is used internally and is tested in test_get_records() 1416 } 1417 1418 public function test_get_recordset_select() { 1419 $DB = $this->tdb; 1420 $dbman = $DB->get_manager(); 1421 1422 $table = $this->get_test_table(); 1423 $tablename = $table->getName(); 1424 1425 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1426 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1427 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1428 $dbman->create_table($table); 1429 1430 $DB->insert_record($tablename, array('course' => 3)); 1431 $DB->insert_record($tablename, array('course' => 3)); 1432 $DB->insert_record($tablename, array('course' => 5)); 1433 $DB->insert_record($tablename, array('course' => 2)); 1434 1435 $rs = $DB->get_recordset_select($tablename, ''); 1436 $counter = 0; 1437 foreach ($rs as $record) { 1438 $counter++; 1439 } 1440 $rs->close(); 1441 $this->assertEquals(4, $counter); 1442 1443 $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3')); 1444 $counter = 0; 1445 foreach ($rs as $record) { 1446 $counter++; 1447 } 1448 $rs->close(); 1449 $this->assertEquals(2, $counter); 1450 1451 // Notes: 1452 // * limits are tested in test_get_recordset_sql() 1453 } 1454 1455 public function test_get_recordset_sql() { 1456 $DB = $this->tdb; 1457 $dbman = $DB->get_manager(); 1458 1459 $table = $this->get_test_table(); 1460 $tablename = $table->getName(); 1461 1462 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1463 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1464 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1465 $dbman->create_table($table); 1466 1467 $inskey1 = $DB->insert_record($tablename, array('course' => 3)); 1468 $inskey2 = $DB->insert_record($tablename, array('course' => 5)); 1469 $inskey3 = $DB->insert_record($tablename, array('course' => 4)); 1470 $inskey4 = $DB->insert_record($tablename, array('course' => 3)); 1471 $inskey5 = $DB->insert_record($tablename, array('course' => 2)); 1472 $inskey6 = $DB->insert_record($tablename, array('course' => 1)); 1473 $inskey7 = $DB->insert_record($tablename, array('course' => 0)); 1474 1475 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); 1476 $counter = 0; 1477 foreach ($rs as $record) { 1478 $counter++; 1479 } 1480 $rs->close(); 1481 $this->assertEquals(2, $counter); 1482 1483 // Limits - only need to test this case, the rest have been tested by test_get_records_sql() 1484 // only limitfrom = skips that number of records. 1485 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); 1486 $records = array(); 1487 foreach ($rs as $key => $record) { 1488 $records[$key] = $record; 1489 } 1490 $rs->close(); 1491 $this->assertCount(5, $records); 1492 $this->assertEquals($inskey3, reset($records)->id); 1493 $this->assertEquals($inskey7, end($records)->id); 1494 1495 // Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here. 1496 } 1497 1498 public function test_export_table_recordset() { 1499 $DB = $this->tdb; 1500 $dbman = $DB->get_manager(); 1501 1502 $table = $this->get_test_table(); 1503 $tablename = $table->getName(); 1504 1505 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1506 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1507 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1508 $dbman->create_table($table); 1509 1510 $ids = array(); 1511 $ids[] = $DB->insert_record($tablename, array('course' => 3)); 1512 $ids[] = $DB->insert_record($tablename, array('course' => 5)); 1513 $ids[] = $DB->insert_record($tablename, array('course' => 4)); 1514 $ids[] = $DB->insert_record($tablename, array('course' => 3)); 1515 $ids[] = $DB->insert_record($tablename, array('course' => 2)); 1516 $ids[] = $DB->insert_record($tablename, array('course' => 1)); 1517 $ids[] = $DB->insert_record($tablename, array('course' => 0)); 1518 1519 $rs = $DB->export_table_recordset($tablename); 1520 $rids = array(); 1521 foreach ($rs as $record) { 1522 $rids[] = $record->id; 1523 } 1524 $rs->close(); 1525 $this->assertEqualsCanonicalizing($ids, $rids); 1526 } 1527 1528 public function test_get_records() { 1529 $DB = $this->tdb; 1530 $dbman = $DB->get_manager(); 1531 1532 $table = $this->get_test_table(); 1533 $tablename = $table->getName(); 1534 1535 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1536 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1537 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1538 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1539 $dbman->create_table($table); 1540 1541 $DB->insert_record($tablename, array('course' => 3)); 1542 $DB->insert_record($tablename, array('course' => 3)); 1543 $DB->insert_record($tablename, array('course' => 5)); 1544 $DB->insert_record($tablename, array('course' => 2)); 1545 1546 // All records. 1547 $records = $DB->get_records($tablename); 1548 $this->assertCount(4, $records); 1549 $this->assertEquals(3, $records[1]->course); 1550 $this->assertEquals(3, $records[2]->course); 1551 $this->assertEquals(5, $records[3]->course); 1552 $this->assertEquals(2, $records[4]->course); 1553 1554 // Records matching certain conditions. 1555 $records = $DB->get_records($tablename, array('course' => 3)); 1556 $this->assertCount(2, $records); 1557 $this->assertEquals(3, $records[1]->course); 1558 $this->assertEquals(3, $records[2]->course); 1559 1560 // All records sorted by course. 1561 $records = $DB->get_records($tablename, null, 'course'); 1562 $this->assertCount(4, $records); 1563 $current_record = reset($records); 1564 $this->assertEquals(4, $current_record->id); 1565 $current_record = next($records); 1566 $this->assertEquals(1, $current_record->id); 1567 $current_record = next($records); 1568 $this->assertEquals(2, $current_record->id); 1569 $current_record = next($records); 1570 $this->assertEquals(3, $current_record->id); 1571 1572 // All records, but get only one field. 1573 $records = $DB->get_records($tablename, null, '', 'id'); 1574 $this->assertFalse(isset($records[1]->course)); 1575 $this->assertTrue(isset($records[1]->id)); 1576 $this->assertCount(4, $records); 1577 1578 // Booleans into params. 1579 $records = $DB->get_records($tablename, array('course' => true)); 1580 $this->assertCount(0, $records); 1581 $records = $DB->get_records($tablename, array('course' => false)); 1582 $this->assertCount(0, $records); 1583 1584 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 1585 $conditions = array('onetext' => '1'); 1586 try { 1587 $records = $DB->get_records($tablename, $conditions); 1588 if (debugging()) { 1589 // Only in debug mode - hopefully all devs test code in debug mode... 1590 $this->fail('An Exception is missing, expected due to equating of text fields'); 1591 } 1592 } catch (\moodle_exception $e) { 1593 $this->assertInstanceOf('dml_exception', $e); 1594 $this->assertSame('textconditionsnotallowed', $e->errorcode); 1595 } 1596 1597 // Test get_records passing non-existing table. 1598 // with params. 1599 try { 1600 $records = $DB->get_records('xxxx', array('id' => 0)); 1601 $this->fail('An Exception is missing, expected due to query against non-existing table'); 1602 } catch (\moodle_exception $e) { 1603 $this->assertInstanceOf('dml_exception', $e); 1604 if (debugging()) { 1605 // Information for developers only, normal users get general error message. 1606 $this->assertSame('ddltablenotexist', $e->errorcode); 1607 } 1608 } 1609 1610 try { 1611 $records = $DB->get_records('xxxx', array('id' => '1')); 1612 $this->fail('An Exception is missing, expected due to query against non-existing table'); 1613 } catch (\moodle_exception $e) { 1614 $this->assertInstanceOf('dml_exception', $e); 1615 if (debugging()) { 1616 // Information for developers only, normal users get general error message. 1617 $this->assertSame('ddltablenotexist', $e->errorcode); 1618 } 1619 } 1620 1621 // Test get_records passing non-existing column. 1622 try { 1623 $records = $DB->get_records($tablename, array('xxxx' => 0)); 1624 $this->fail('An Exception is missing, expected due to query against non-existing column'); 1625 } catch (\moodle_exception $e) { 1626 $this->assertInstanceOf('dml_exception', $e); 1627 if (debugging()) { 1628 // Information for developers only, normal users get general error message. 1629 $this->assertSame('ddlfieldnotexist', $e->errorcode); 1630 } 1631 } 1632 1633 // Note: delegate limits testing to test_get_records_sql(). 1634 } 1635 1636 public function test_get_records_list() { 1637 $DB = $this->tdb; 1638 $dbman = $DB->get_manager(); 1639 1640 $table = $this->get_test_table(); 1641 $tablename = $table->getName(); 1642 1643 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1644 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1645 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1646 $dbman->create_table($table); 1647 1648 $DB->insert_record($tablename, array('course' => 3)); 1649 $DB->insert_record($tablename, array('course' => 3)); 1650 $DB->insert_record($tablename, array('course' => 5)); 1651 $DB->insert_record($tablename, array('course' => 2)); 1652 1653 $records = $DB->get_records_list($tablename, 'course', array(3, 2)); 1654 $this->assertIsArray($records); 1655 $this->assertCount(3, $records); 1656 $this->assertEquals(1, reset($records)->id); 1657 $this->assertEquals(2, next($records)->id); 1658 $this->assertEquals(4, next($records)->id); 1659 1660 $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645. 1661 $this->assertCount(0, $records); 1662 1663 // Note: delegate limits testing to test_get_records_sql(). 1664 } 1665 1666 public function test_get_records_sql() { 1667 $DB = $this->tdb; 1668 $dbman = $DB->get_manager(); 1669 1670 $table = $this->get_test_table(); 1671 $tablename = $table->getName(); 1672 1673 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1674 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1675 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1676 $dbman->create_table($table); 1677 1678 $inskey1 = $DB->insert_record($tablename, array('course' => 3)); 1679 $inskey2 = $DB->insert_record($tablename, array('course' => 5)); 1680 $inskey3 = $DB->insert_record($tablename, array('course' => 4)); 1681 $inskey4 = $DB->insert_record($tablename, array('course' => 3)); 1682 $inskey5 = $DB->insert_record($tablename, array('course' => 2)); 1683 $inskey6 = $DB->insert_record($tablename, array('course' => 1)); 1684 $inskey7 = $DB->insert_record($tablename, array('course' => 0)); 1685 1686 $table2 = $this->get_test_table("2"); 1687 $tablename2 = $table2->getName(); 1688 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1689 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1690 $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 1691 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1692 $dbman->create_table($table2); 1693 1694 $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing')); 1695 $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang')); 1696 $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung')); 1697 $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong')); 1698 1699 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); 1700 $this->assertCount(2, $records); 1701 $this->assertEquals($inskey1, reset($records)->id); 1702 $this->assertEquals($inskey4, next($records)->id); 1703 1704 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test. 1705 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null); 1706 $this->assertDebuggingCalled(); 1707 $this->assertCount(6, $records); 1708 set_debugging(DEBUG_MINIMAL); 1709 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null); 1710 $this->assertDebuggingNotCalled(); 1711 $this->assertCount(6, $records); 1712 set_debugging(DEBUG_DEVELOPER); 1713 1714 // Negative limits = no limits. 1715 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1); 1716 $this->assertCount(7, $records); 1717 1718 // Zero limits = no limits. 1719 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0); 1720 $this->assertCount(7, $records); 1721 1722 // Only limitfrom = skips that number of records. 1723 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); 1724 $this->assertCount(5, $records); 1725 $this->assertEquals($inskey3, reset($records)->id); 1726 $this->assertEquals($inskey7, end($records)->id); 1727 1728 // Only limitnum = fetches that number of records. 1729 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3); 1730 $this->assertCount(3, $records); 1731 $this->assertEquals($inskey1, reset($records)->id); 1732 $this->assertEquals($inskey3, end($records)->id); 1733 1734 // Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones. 1735 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2); 1736 $this->assertCount(2, $records); 1737 $this->assertEquals($inskey4, reset($records)->id); 1738 $this->assertEquals($inskey5, end($records)->id); 1739 1740 // Both limitfrom and limitnum in query having subqueris. 1741 // Note the subquery skips records with course = 0 and 3. 1742 $sql = "SELECT * FROM {{$tablename}} 1743 WHERE course NOT IN ( 1744 SELECT course FROM {{$tablename}} 1745 WHERE course IN (0, 3)) 1746 ORDER BY course"; 1747 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2. 1748 $this->assertCount(2, $records); 1749 $this->assertEquals($inskey6, reset($records)->id); 1750 $this->assertEquals($inskey5, end($records)->id); 1751 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2. 1752 $this->assertCount(2, $records); 1753 $this->assertEquals($inskey3, reset($records)->id); 1754 $this->assertEquals($inskey2, end($records)->id); 1755 1756 // Test 2 tables with aliases and limits with order bys. 1757 $sql = "SELECT t1.id, t1.course AS cid, t2.nametext 1758 FROM {{$tablename}} t1, {{$tablename2}} t2 1759 WHERE t2.course=t1.course 1760 ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext'); 1761 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5. 1762 $this->assertCount(2, $records); 1763 $this->assertSame('5', end($records)->cid); 1764 $this->assertSame('4', reset($records)->cid); 1765 1766 // Test 2 tables with aliases and limits with the highest INT limit works. 1767 $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}. 1768 $this->assertCount(2, $records); 1769 $this->assertSame('5', end($records)->cid); 1770 $this->assertSame('4', reset($records)->cid); 1771 1772 // Test 2 tables with aliases and limits with order bys (limit which is highest INT number). 1773 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses. 1774 $this->assertCount(0, $records); 1775 1776 // Test 2 tables with aliases and limits with order bys (limit which s highest INT number). 1777 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses. 1778 $this->assertCount(0, $records); 1779 1780 // TODO: Test limits in queries having DISTINCT clauses. 1781 1782 // Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here. 1783 } 1784 1785 public function test_get_records_menu() { 1786 $DB = $this->tdb; 1787 $dbman = $DB->get_manager(); 1788 1789 $table = $this->get_test_table(); 1790 $tablename = $table->getName(); 1791 1792 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1793 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1794 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1795 $dbman->create_table($table); 1796 1797 $DB->insert_record($tablename, array('course' => 3)); 1798 $DB->insert_record($tablename, array('course' => 3)); 1799 $DB->insert_record($tablename, array('course' => 5)); 1800 $DB->insert_record($tablename, array('course' => 2)); 1801 1802 $records = $DB->get_records_menu($tablename, array('course' => 3)); 1803 $this->assertIsArray($records); 1804 $this->assertCount(2, $records); 1805 $this->assertNotEmpty($records[1]); 1806 $this->assertNotEmpty($records[2]); 1807 $this->assertEquals(3, $records[1]); 1808 $this->assertEquals(3, $records[2]); 1809 1810 // Note: delegate limits testing to test_get_records_sql(). 1811 } 1812 1813 public function test_get_records_select_menu() { 1814 $DB = $this->tdb; 1815 $dbman = $DB->get_manager(); 1816 1817 $table = $this->get_test_table(); 1818 $tablename = $table->getName(); 1819 1820 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1821 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1822 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1823 $dbman->create_table($table); 1824 1825 $DB->insert_record($tablename, array('course' => 3)); 1826 $DB->insert_record($tablename, array('course' => 2)); 1827 $DB->insert_record($tablename, array('course' => 3)); 1828 $DB->insert_record($tablename, array('course' => 5)); 1829 1830 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2)); 1831 $this->assertIsArray($records); 1832 1833 $this->assertCount(3, $records); 1834 $this->assertArrayHasKey(1, $records); 1835 $this->assertArrayNotHasKey(2, $records); 1836 $this->assertArrayHasKey(3, $records); 1837 $this->assertArrayHasKey(4, $records); 1838 $this->assertSame('3', $records[1]); 1839 $this->assertSame('3', $records[3]); 1840 $this->assertSame('5', $records[4]); 1841 1842 // Note: delegate limits testing to test_get_records_sql(). 1843 } 1844 1845 public function test_get_records_sql_menu() { 1846 $DB = $this->tdb; 1847 $dbman = $DB->get_manager(); 1848 1849 $table = $this->get_test_table(); 1850 $tablename = $table->getName(); 1851 1852 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1853 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1854 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1855 $dbman->create_table($table); 1856 1857 $DB->insert_record($tablename, array('course' => 3)); 1858 $DB->insert_record($tablename, array('course' => 2)); 1859 $DB->insert_record($tablename, array('course' => 3)); 1860 $DB->insert_record($tablename, array('course' => 5)); 1861 1862 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2)); 1863 $this->assertIsArray($records); 1864 1865 $this->assertCount(3, $records); 1866 $this->assertArrayHasKey(1, $records); 1867 $this->assertArrayNotHasKey(2, $records); 1868 $this->assertArrayHasKey(3, $records); 1869 $this->assertArrayHasKey(4, $records); 1870 $this->assertSame('3', $records[1]); 1871 $this->assertSame('3', $records[3]); 1872 $this->assertSame('5', $records[4]); 1873 1874 // Note: delegate limits testing to test_get_records_sql(). 1875 } 1876 1877 public function test_get_record() { 1878 $DB = $this->tdb; 1879 $dbman = $DB->get_manager(); 1880 1881 $table = $this->get_test_table(); 1882 $tablename = $table->getName(); 1883 1884 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1885 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1886 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1887 $dbman->create_table($table); 1888 1889 $DB->insert_record($tablename, array('course' => 3)); 1890 $DB->insert_record($tablename, array('course' => 2)); 1891 1892 $record = $DB->get_record($tablename, array('id' => 2)); 1893 $this->assertInstanceOf(\stdClass::class, $record); 1894 1895 $this->assertEquals(2, $record->course); 1896 $this->assertEquals(2, $record->id); 1897 } 1898 1899 1900 public function test_get_record_select() { 1901 $DB = $this->tdb; 1902 $dbman = $DB->get_manager(); 1903 1904 $table = $this->get_test_table(); 1905 $tablename = $table->getName(); 1906 1907 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1908 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1909 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1910 $dbman->create_table($table); 1911 1912 $DB->insert_record($tablename, array('course' => 3)); 1913 $DB->insert_record($tablename, array('course' => 2)); 1914 1915 $record = $DB->get_record_select($tablename, "id = ?", array(2)); 1916 $this->assertInstanceOf(\stdClass::class, $record); 1917 1918 $this->assertEquals(2, $record->course); 1919 1920 // Note: delegates limit testing to test_get_records_sql(). 1921 } 1922 1923 public function test_get_record_sql() { 1924 $DB = $this->tdb; 1925 $dbman = $DB->get_manager(); 1926 1927 $table = $this->get_test_table(); 1928 $tablename = $table->getName(); 1929 1930 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1931 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1932 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1933 $dbman->create_table($table); 1934 1935 $DB->insert_record($tablename, array('course' => 3)); 1936 $DB->insert_record($tablename, array('course' => 2)); 1937 1938 // Standard use. 1939 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2)); 1940 $this->assertInstanceOf(\stdClass::class, $record); 1941 $this->assertEquals(2, $record->course); 1942 $this->assertEquals(2, $record->id); 1943 1944 // Backwards compatibility with $ignoremultiple. 1945 $this->assertFalse((bool)IGNORE_MISSING); 1946 $this->assertTrue((bool)IGNORE_MULTIPLE); 1947 1948 // Record not found - ignore. 1949 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING)); 1950 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE)); 1951 1952 // Record not found error. 1953 try { 1954 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST); 1955 $this->fail("Exception expected"); 1956 } catch (dml_missing_record_exception $e) { 1957 $this->assertTrue(true); 1958 } 1959 1960 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING)); 1961 $this->assertDebuggingCalled(); 1962 set_debugging(DEBUG_MINIMAL); 1963 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING)); 1964 $this->assertDebuggingNotCalled(); 1965 set_debugging(DEBUG_DEVELOPER); 1966 1967 // Multiple matches ignored. 1968 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE)); 1969 1970 // Multiple found error. 1971 try { 1972 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST); 1973 $this->fail("Exception expected"); 1974 } catch (dml_multiple_records_exception $e) { 1975 $this->assertTrue(true); 1976 } 1977 } 1978 1979 public function test_get_field() { 1980 $DB = $this->tdb; 1981 $dbman = $DB->get_manager(); 1982 1983 $table = $this->get_test_table(); 1984 $tablename = $table->getName(); 1985 1986 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1987 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1988 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1989 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1990 $dbman->create_table($table); 1991 1992 $id1 = $DB->insert_record($tablename, array('course' => 3)); 1993 $DB->insert_record($tablename, array('course' => 5)); 1994 $DB->insert_record($tablename, array('course' => 5)); 1995 1996 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1))); 1997 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3))); 1998 1999 $this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING)); 2000 try { 2001 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST); 2002 $this->fail('Exception expected due to missing record'); 2003 } catch (dml_exception $ex) { 2004 $this->assertTrue(true); 2005 } 2006 2007 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE)); 2008 $this->assertDebuggingNotCalled(); 2009 2010 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING)); 2011 $this->assertDebuggingCalled(); 2012 2013 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 2014 $conditions = array('onetext' => '1'); 2015 try { 2016 $DB->get_field($tablename, 'course', $conditions); 2017 if (debugging()) { 2018 // Only in debug mode - hopefully all devs test code in debug mode... 2019 $this->fail('An Exception is missing, expected due to equating of text fields'); 2020 } 2021 } catch (\moodle_exception $e) { 2022 $this->assertInstanceOf('dml_exception', $e); 2023 $this->assertSame('textconditionsnotallowed', $e->errorcode); 2024 } 2025 } 2026 2027 public function test_get_field_select() { 2028 $DB = $this->tdb; 2029 $dbman = $DB->get_manager(); 2030 2031 $table = $this->get_test_table(); 2032 $tablename = $table->getName(); 2033 2034 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2035 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2036 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2037 $dbman->create_table($table); 2038 2039 $DB->insert_record($tablename, array('course' => 3)); 2040 2041 $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1))); 2042 } 2043 2044 public function test_get_field_sql() { 2045 $DB = $this->tdb; 2046 $dbman = $DB->get_manager(); 2047 2048 $table = $this->get_test_table(); 2049 $tablename = $table->getName(); 2050 2051 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2052 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2053 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2054 $dbman->create_table($table); 2055 2056 $DB->insert_record($tablename, array('course' => 3)); 2057 2058 $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1))); 2059 } 2060 2061 public function test_get_fieldset_select() { 2062 $DB = $this->tdb; 2063 $dbman = $DB->get_manager(); 2064 2065 $table = $this->get_test_table(); 2066 $tablename = $table->getName(); 2067 2068 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2069 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2070 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2071 $dbman->create_table($table); 2072 2073 $DB->insert_record($tablename, array('course' => 1)); 2074 $DB->insert_record($tablename, array('course' => 3)); 2075 $DB->insert_record($tablename, array('course' => 2)); 2076 $DB->insert_record($tablename, array('course' => 6)); 2077 2078 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)); 2079 $this->assertIsArray($fieldset); 2080 2081 $this->assertCount(3, $fieldset); 2082 $this->assertEquals(3, $fieldset[0]); 2083 $this->assertEquals(2, $fieldset[1]); 2084 $this->assertEquals(6, $fieldset[2]); 2085 } 2086 2087 public function test_get_fieldset_sql() { 2088 $DB = $this->tdb; 2089 $dbman = $DB->get_manager(); 2090 2091 $table = $this->get_test_table(); 2092 $tablename = $table->getName(); 2093 2094 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2095 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2096 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2097 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2098 $dbman->create_table($table); 2099 2100 $binarydata = '\\'.chr(241); 2101 2102 $DB->insert_record($tablename, array('course' => 1, 'onebinary' => $binarydata)); 2103 $DB->insert_record($tablename, array('course' => 3, 'onebinary' => $binarydata)); 2104 $DB->insert_record($tablename, array('course' => 2, 'onebinary' => $binarydata)); 2105 $DB->insert_record($tablename, array('course' => 6, 'onebinary' => $binarydata)); 2106 2107 $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1)); 2108 $this->assertIsArray($fieldset); 2109 2110 $this->assertCount(3, $fieldset); 2111 $this->assertEquals(2, $fieldset[0]); 2112 $this->assertEquals(3, $fieldset[1]); 2113 $this->assertEquals(4, $fieldset[2]); 2114 2115 $fieldset = $DB->get_fieldset_sql("SELECT onebinary FROM {{$tablename}} WHERE course > ?", array(1)); 2116 $this->assertIsArray($fieldset); 2117 2118 $this->assertCount(3, $fieldset); 2119 $this->assertEquals($binarydata, $fieldset[0]); 2120 $this->assertEquals($binarydata, $fieldset[1]); 2121 $this->assertEquals($binarydata, $fieldset[2]); 2122 } 2123 2124 public function test_insert_record_raw() { 2125 $DB = $this->tdb; 2126 $dbman = $DB->get_manager(); 2127 2128 $table = $this->get_test_table(); 2129 $tablename = $table->getName(); 2130 2131 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2132 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2133 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2134 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2135 $dbman->create_table($table); 2136 2137 $record = (object)array('course' => 1, 'onechar' => 'xx'); 2138 $before = clone($record); 2139 $result = $DB->insert_record_raw($tablename, $record); 2140 $this->assertSame(1, $result); 2141 $this->assertEquals($record, $before); 2142 2143 $record = $DB->get_record($tablename, array('course' => 1)); 2144 $this->assertInstanceOf(\stdClass::class, $record); 2145 $this->assertSame('xx', $record->onechar); 2146 2147 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false); 2148 $this->assertTrue($result); 2149 2150 // Note: bulk not implemented yet. 2151 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true); 2152 $record = $DB->get_record($tablename, array('course' => 3)); 2153 $this->assertInstanceOf(\stdClass::class, $record); 2154 $this->assertSame('zz', $record->onechar); 2155 2156 // Custom sequence (id) - returnid is ignored. 2157 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true); 2158 $this->assertTrue($result); 2159 $record = $DB->get_record($tablename, array('id' => 10)); 2160 $this->assertInstanceOf(\stdClass::class, $record); 2161 $this->assertSame('bb', $record->onechar); 2162 2163 // Custom sequence - missing id error. 2164 try { 2165 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true); 2166 $this->fail('Exception expected due to missing record'); 2167 } catch (\coding_exception $ex) { 2168 $this->assertTrue(true); 2169 } 2170 2171 // Wrong column error. 2172 try { 2173 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb')); 2174 $this->fail('Exception expected due to invalid column'); 2175 } catch (dml_exception $ex) { 2176 $this->assertTrue(true); 2177 } 2178 2179 // Create something similar to "context_temp" with id column without sequence. 2180 $dbman->drop_table($table); 2181 $table = $this->get_test_table(); 2182 $tablename = $table->getName(); 2183 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); 2184 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2185 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2186 $dbman->create_table($table); 2187 2188 $record = (object)array('id'=>5, 'course' => 1); 2189 $DB->insert_record_raw($tablename, $record, false, false, true); 2190 $record = $DB->get_record($tablename, array()); 2191 $this->assertEquals(5, $record->id); 2192 } 2193 2194 public function test_insert_record() { 2195 // All the information in this test is fetched from DB by get_recordset() so we 2196 // have such method properly tested against nulls, empties and friends... 2197 2198 $DB = $this->tdb; 2199 $dbman = $DB->get_manager(); 2200 2201 $table = $this->get_test_table(); 2202 $tablename = $table->getName(); 2203 2204 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2205 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2206 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2207 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2208 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2209 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2210 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2211 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2212 $dbman->create_table($table); 2213 2214 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true)); 2215 $record = $DB->get_record($tablename, array('course' => 1)); 2216 $this->assertEquals(1, $record->id); 2217 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied. 2218 $this->assertEquals(200, $record->onenum); 2219 $this->assertSame('onestring', $record->onechar); 2220 $this->assertNull($record->onetext); 2221 $this->assertNull($record->onebinary); 2222 2223 // Without returning id, bulk not implemented. 2224 $result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true)); 2225 $record = $DB->get_record($tablename, array('course' => 99)); 2226 $this->assertEquals(2, $record->id); 2227 $this->assertEquals(99, $record->course); 2228 2229 // Check nulls are set properly for all types. 2230 $record = new \stdClass(); 2231 $record->oneint = null; 2232 $record->onenum = null; 2233 $record->onechar = null; 2234 $record->onetext = null; 2235 $record->onebinary = null; 2236 $recid = $DB->insert_record($tablename, $record); 2237 $record = $DB->get_record($tablename, array('id' => $recid)); 2238 $this->assertEquals(0, $record->course); 2239 $this->assertNull($record->oneint); 2240 $this->assertNull($record->onenum); 2241 $this->assertNull($record->onechar); 2242 $this->assertNull($record->onetext); 2243 $this->assertNull($record->onebinary); 2244 2245 // Check zeros are set properly for all types. 2246 $record = new \stdClass(); 2247 $record->oneint = 0; 2248 $record->onenum = 0; 2249 $recid = $DB->insert_record($tablename, $record); 2250 $record = $DB->get_record($tablename, array('id' => $recid)); 2251 $this->assertEquals(0, $record->oneint); 2252 $this->assertEquals(0, $record->onenum); 2253 2254 // Check booleans are set properly for all types. 2255 $record = new \stdClass(); 2256 $record->oneint = true; // Trues. 2257 $record->onenum = true; 2258 $record->onechar = true; 2259 $record->onetext = true; 2260 $recid = $DB->insert_record($tablename, $record); 2261 $record = $DB->get_record($tablename, array('id' => $recid)); 2262 $this->assertEquals(1, $record->oneint); 2263 $this->assertEquals(1, $record->onenum); 2264 $this->assertEquals(1, $record->onechar); 2265 $this->assertEquals(1, $record->onetext); 2266 2267 $record = new \stdClass(); 2268 $record->oneint = false; // Falses. 2269 $record->onenum = false; 2270 $record->onechar = false; 2271 $record->onetext = false; 2272 $recid = $DB->insert_record($tablename, $record); 2273 $record = $DB->get_record($tablename, array('id' => $recid)); 2274 $this->assertEquals(0, $record->oneint); 2275 $this->assertEquals(0, $record->onenum); 2276 $this->assertEquals(0, $record->onechar); 2277 $this->assertEquals(0, $record->onetext); 2278 2279 // Check string data causes exception in numeric types. 2280 $record = new \stdClass(); 2281 $record->oneint = 'onestring'; 2282 $record->onenum = 0; 2283 try { 2284 $DB->insert_record($tablename, $record); 2285 $this->fail("Expecting an exception, none occurred"); 2286 } catch (\moodle_exception $e) { 2287 $this->assertInstanceOf('dml_exception', $e); 2288 } 2289 $record = new \stdClass(); 2290 $record->oneint = 0; 2291 $record->onenum = 'onestring'; 2292 try { 2293 $DB->insert_record($tablename, $record); 2294 $this->fail("Expecting an exception, none occurred"); 2295 } catch (\moodle_exception $e) { 2296 $this->assertInstanceOf('dml_exception', $e); 2297 } 2298 2299 // Check empty string data is stored as 0 in numeric datatypes. 2300 $record = new \stdClass(); 2301 $record->oneint = ''; // Empty string. 2302 $record->onenum = 0; 2303 $recid = $DB->insert_record($tablename, $record); 2304 $record = $DB->get_record($tablename, array('id' => $recid)); 2305 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0); 2306 2307 $record = new \stdClass(); 2308 $record->oneint = 0; 2309 $record->onenum = ''; // Empty string. 2310 $recid = $DB->insert_record($tablename, $record); 2311 $record = $DB->get_record($tablename, array('id' => $recid)); 2312 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0); 2313 2314 // Check empty strings are set properly in string types. 2315 $record = new \stdClass(); 2316 $record->oneint = 0; 2317 $record->onenum = 0; 2318 $record->onechar = ''; 2319 $record->onetext = ''; 2320 $recid = $DB->insert_record($tablename, $record); 2321 $record = $DB->get_record($tablename, array('id' => $recid)); 2322 $this->assertTrue($record->onechar === ''); 2323 $this->assertTrue($record->onetext === ''); 2324 2325 // Check operation ((210.10 + 39.92) - 150.02) against numeric types. 2326 $record = new \stdClass(); 2327 $record->oneint = ((210.10 + 39.92) - 150.02); 2328 $record->onenum = ((210.10 + 39.92) - 150.02); 2329 $recid = $DB->insert_record($tablename, $record); 2330 $record = $DB->get_record($tablename, array('id' => $recid)); 2331 $this->assertEquals(100, $record->oneint); 2332 $this->assertEquals(100, $record->onenum); 2333 2334 // Check various quotes/backslashes combinations in string types. 2335 $teststrings = array( 2336 'backslashes and quotes alone (even): "" \'\' \\\\', 2337 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', 2338 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', 2339 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); 2340 foreach ($teststrings as $teststring) { 2341 $record = new \stdClass(); 2342 $record->onechar = $teststring; 2343 $record->onetext = $teststring; 2344 $recid = $DB->insert_record($tablename, $record); 2345 $record = $DB->get_record($tablename, array('id' => $recid)); 2346 $this->assertEquals($teststring, $record->onechar); 2347 $this->assertEquals($teststring, $record->onetext); 2348 } 2349 2350 // Check LOBs in text/binary columns. 2351 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 2352 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary'); 2353 $record = new \stdClass(); 2354 $record->onetext = $clob; 2355 $record->onebinary = $blob; 2356 $recid = $DB->insert_record($tablename, $record); 2357 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2358 $record = $rs->current(); 2359 $rs->close(); 2360 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)'); 2361 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)'); 2362 2363 // And "small" LOBs too, just in case. 2364 $newclob = substr($clob, 0, 500); 2365 $newblob = substr($blob, 0, 250); 2366 $record = new \stdClass(); 2367 $record->onetext = $newclob; 2368 $record->onebinary = $newblob; 2369 $recid = $DB->insert_record($tablename, $record); 2370 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2371 $record = $rs->current(); 2372 $rs->close(); 2373 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)'); 2374 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)'); 2375 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing. 2376 2377 // And "diagnostic" LOBs too, just in case. 2378 $newclob = '\'"\\;/ěščřžýáíé'; 2379 $newblob = '\'"\\;/ěščřžýáíé'; 2380 $record = new \stdClass(); 2381 $record->onetext = $newclob; 2382 $record->onebinary = $newblob; 2383 $recid = $DB->insert_record($tablename, $record); 2384 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2385 $record = $rs->current(); 2386 $rs->close(); 2387 $this->assertSame($newclob, $record->onetext); 2388 $this->assertSame($newblob, $record->onebinary); 2389 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing. 2390 2391 // Test data is not modified. 2392 $record = new \stdClass(); 2393 $record->id = -1; // Has to be ignored. 2394 $record->course = 3; 2395 $record->lalala = 'lalal'; // Unused. 2396 $before = clone($record); 2397 $DB->insert_record($tablename, $record); 2398 $this->assertEquals($record, $before); 2399 2400 // Make sure the id is always increasing and never reuses the same id. 2401 $id1 = $DB->insert_record($tablename, array('course' => 3)); 2402 $id2 = $DB->insert_record($tablename, array('course' => 3)); 2403 $this->assertTrue($id1 < $id2); 2404 $DB->delete_records($tablename, array('id'=>$id2)); 2405 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2406 $this->assertTrue($id2 < $id3); 2407 $DB->delete_records($tablename, array()); 2408 $id4 = $DB->insert_record($tablename, array('course' => 3)); 2409 $this->assertTrue($id3 < $id4); 2410 2411 // Test saving a float in a CHAR column, and reading it back. 2412 $id = $DB->insert_record($tablename, array('onechar' => 1.0)); 2413 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2414 $id = $DB->insert_record($tablename, array('onechar' => 1e20)); 2415 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2416 $id = $DB->insert_record($tablename, array('onechar' => 1e-4)); 2417 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2418 $id = $DB->insert_record($tablename, array('onechar' => 1e-5)); 2419 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2420 $id = $DB->insert_record($tablename, array('onechar' => 1e-300)); 2421 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2422 $id = $DB->insert_record($tablename, array('onechar' => 1e300)); 2423 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2424 2425 // Test saving a float in a TEXT column, and reading it back. 2426 $id = $DB->insert_record($tablename, array('onetext' => 1.0)); 2427 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2428 $id = $DB->insert_record($tablename, array('onetext' => 1e20)); 2429 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2430 $id = $DB->insert_record($tablename, array('onetext' => 1e-4)); 2431 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2432 $id = $DB->insert_record($tablename, array('onetext' => 1e-5)); 2433 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2434 $id = $DB->insert_record($tablename, array('onetext' => 1e-300)); 2435 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2436 $id = $DB->insert_record($tablename, array('onetext' => 1e300)); 2437 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2438 2439 // Test that inserting data violating one unique key leads to error. 2440 // Empty the table completely. 2441 $this->assertTrue($DB->delete_records($tablename)); 2442 2443 // Add one unique constraint (index). 2444 $key = new xmldb_key('testuk', XMLDB_KEY_UNIQUE, array('course', 'oneint')); 2445 $dbman->add_key($table, $key); 2446 2447 // Let's insert one record violating the constraint multiple times. 2448 $record = (object)array('course' => 1, 'oneint' => 1); 2449 $this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected. 2450 2451 // Re-insert same record, not returning id. dml_exception expected. 2452 try { 2453 $DB->insert_record($tablename, $record, false); 2454 $this->fail("Expecting an exception, none occurred"); 2455 } catch (\moodle_exception $e) { 2456 $this->assertInstanceOf('dml_exception', $e); 2457 } 2458 2459 // Re-insert same record, returning id. dml_exception expected. 2460 try { 2461 $DB->insert_record($tablename, $record, true); 2462 $this->fail("Expecting an exception, none occurred"); 2463 } catch (\moodle_exception $e) { 2464 $this->assertInstanceOf('dml_exception', $e); 2465 } 2466 2467 // Try to insert a record into a non-existent table. dml_exception expected. 2468 try { 2469 $DB->insert_record('nonexistenttable', $record, true); 2470 $this->fail("Expecting an exception, none occurred"); 2471 } catch (\Exception $e) { 2472 $this->assertTrue($e instanceof dml_exception); 2473 } 2474 } 2475 2476 public function test_insert_records() { 2477 $DB = $this->tdb; 2478 $dbman = $DB->get_manager(); 2479 2480 $table = $this->get_test_table(); 2481 $tablename = $table->getName(); 2482 2483 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2484 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2485 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2486 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2487 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2488 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2489 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2490 $dbman->create_table($table); 2491 2492 $this->assertCount(0, $DB->get_records($tablename)); 2493 2494 $record = new \stdClass(); 2495 $record->id = '1'; 2496 $record->course = '1'; 2497 $record->oneint = null; 2498 $record->onenum = 1.0; 2499 $record->onechar = 'a'; 2500 $record->onetext = 'aaa'; 2501 2502 $expected = array(); 2503 $records = array(); 2504 for ($i = 1; $i <= 2000; $i++) { // This may take a while, it should be higher than defaults in DML drivers. 2505 $rec = clone($record); 2506 $rec->id = (string)$i; 2507 $rec->oneint = (string)$i; 2508 $expected[$i] = $rec; 2509 $rec = clone($rec); 2510 unset($rec->id); 2511 $records[$i] = $rec; 2512 } 2513 2514 $DB->insert_records($tablename, $records); 2515 $stored = $DB->get_records($tablename, array(), 'id ASC'); 2516 $this->assertEquals($expected, $stored); 2517 2518 // Test there can be some extra properties including id. 2519 $count = $DB->count_records($tablename); 2520 $rec1 = (array)$record; 2521 $rec1['xxx'] = 1; 2522 $rec2 = (array)$record; 2523 $rec2['xxx'] = 2; 2524 2525 $records = array($rec1, $rec2); 2526 $DB->insert_records($tablename, $records); 2527 $this->assertEquals($count + 2, $DB->count_records($tablename)); 2528 2529 // Test not all properties are necessary. 2530 $rec1 = (array)$record; 2531 unset($rec1['course']); 2532 $rec2 = (array)$record; 2533 unset($rec2['course']); 2534 2535 $records = array($rec1, $rec2); 2536 $DB->insert_records($tablename, $records); 2537 2538 // Make sure no changes in data object structure are tolerated. 2539 $rec1 = (array)$record; 2540 unset($rec1['id']); 2541 $rec2 = (array)$record; 2542 unset($rec2['id']); 2543 2544 $records = array($rec1, $rec2); 2545 $DB->insert_records($tablename, $records); 2546 2547 $rec2['xx'] = '1'; 2548 $records = array($rec1, $rec2); 2549 try { 2550 $DB->insert_records($tablename, $records); 2551 $this->fail('coding_exception expected when insert_records receives different object data structures'); 2552 } catch (\moodle_exception $e) { 2553 $this->assertInstanceOf('coding_exception', $e); 2554 } 2555 2556 unset($rec2['xx']); 2557 unset($rec2['course']); 2558 $rec2['course'] = '1'; 2559 $records = array($rec1, $rec2); 2560 try { 2561 $DB->insert_records($tablename, $records); 2562 $this->fail('coding_exception expected when insert_records receives different object data structures'); 2563 } catch (\moodle_exception $e) { 2564 $this->assertInstanceOf('coding_exception', $e); 2565 } 2566 2567 $records = 1; 2568 try { 2569 $DB->insert_records($tablename, $records); 2570 $this->fail('coding_exception expected when insert_records receives non-traversable data'); 2571 } catch (\moodle_exception $e) { 2572 $this->assertInstanceOf('coding_exception', $e); 2573 } 2574 2575 $records = array(1); 2576 try { 2577 $DB->insert_records($tablename, $records); 2578 $this->fail('coding_exception expected when insert_records receives non-objet record'); 2579 } catch (\moodle_exception $e) { 2580 $this->assertInstanceOf('coding_exception', $e); 2581 } 2582 } 2583 2584 public function test_insert_record_with_nullable_unique_index() { 2585 $DB = $this->tdb; 2586 $dbman = $DB->get_manager(); 2587 2588 $table = $this->get_test_table(); 2589 $tablename = $table->getName(); 2590 2591 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2592 $table->add_field('notnull1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2593 $table->add_field('nullable1', XMLDB_TYPE_INTEGER, '10', null, null, null, null); 2594 $table->add_field('nullable2', XMLDB_TYPE_INTEGER, '10', null, null, null, null); 2595 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2596 $table->add_index('notnull1-nullable1-nullable2', XMLDB_INDEX_UNIQUE, 2597 array('notnull1', 'nullable1', 'nullable2')); 2598 $dbman->create_table($table); 2599 2600 // Insert one record. Should be OK (no exception). 2601 $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => 1, 'nullable2' => 1]); 2602 2603 $this->assertEquals(1, $DB->count_records($table->getName())); 2604 $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1])); 2605 2606 // Inserting a duplicate should fail. 2607 try { 2608 $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => 1, 'nullable2' => 1]); 2609 $this->fail('dml_write_exception expected when a record violates a unique index'); 2610 } catch (\moodle_exception $e) { 2611 $this->assertInstanceOf('dml_write_exception', $e); 2612 } 2613 2614 $this->assertEquals(1, $DB->count_records($table->getName())); 2615 $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1])); 2616 2617 // Inserting a record with nulls in the nullable columns should work. 2618 $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => null, 'nullable2' => null]); 2619 2620 $this->assertEquals(2, $DB->count_records($table->getName())); 2621 $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1])); 2622 $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => null])); 2623 2624 // And it should be possible to insert a duplicate. 2625 $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => null, 'nullable2' => null]); 2626 2627 $this->assertEquals(3, $DB->count_records($table->getName())); 2628 $this->assertEquals(1, $DB->count_records($table->getName(), ['nullable1' => 1])); 2629 $this->assertEquals(2, $DB->count_records($table->getName(), ['nullable1' => null])); 2630 2631 // Same, but with only one of the nullable columns being null. 2632 $DB->insert_record($tablename, (object) ['notnull1' => 1, 'nullable1' => 1, 'nullable2' => null]); 2633 2634 $this->assertEquals(4, $DB->count_records($table->getName())); 2635 $this->assertEquals(2, $DB->count_records($table->getName(), ['nullable1' => 1])); 2636 $this->assertEquals(2, $DB->count_records(