| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
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_dml 21 * @category phpunit 22 * @copyright 2008 Nicolas Connault 23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 24 */ 25 26 defined('MOODLE_INTERNAL') || die(); 27 28 class core_dml_testcase extends database_driver_testcase { 29 30 protected function setUp() { 31 parent::setUp(); 32 $dbman = $this->tdb->get_manager(); // Loads DDL libs. 33 } 34 35 /** 36 * Get a xmldb_table object for testing, deleting any existing table 37 * of the same name, for example if one was left over from a previous test 38 * run that crashed. 39 * 40 * @param string $suffix table name suffix, use if you need more test tables 41 * @return xmldb_table the table object. 42 */ 43 private function get_test_table($suffix = '') { 44 $tablename = "test_table"; 45 if ($suffix !== '') { 46 $tablename .= $suffix; 47 } 48 49 $table = new xmldb_table($tablename); 50 $table->setComment("This is a test'n drop table. You can drop it safely"); 51 return $table; 52 } 53 54 public function test_diagnose() { 55 $DB = $this->tdb; 56 $result = $DB->diagnose(); 57 $this->assertNull($result, 'Database self diagnostics failed %s'); 58 } 59 60 public function test_get_server_info() { 61 $DB = $this->tdb; 62 $result = $DB->get_server_info(); 63 $this->assertInternalType('array', $result); 64 $this->assertArrayHasKey('description', $result); 65 $this->assertArrayHasKey('version', $result); 66 } 67 68 public function test_get_in_or_equal() { 69 $DB = $this->tdb; 70 71 // SQL_PARAMS_QM - IN or =. 72 73 // Correct usage of multiple values. 74 $in_values = array('value1', 'value2', '3', 4, null, false, true); 75 list($usql, $params) = $DB->get_in_or_equal($in_values); 76 $this->assertSame('IN ('.implode(',', array_fill(0, count($in_values), '?')).')', $usql); 77 $this->assertEquals(count($in_values), count($params)); 78 foreach ($params as $key => $value) { 79 $this->assertSame($in_values[$key], $value); 80 } 81 82 // Correct usage of single value (in an array). 83 $in_values = array('value1'); 84 list($usql, $params) = $DB->get_in_or_equal($in_values); 85 $this->assertEquals("= ?", $usql); 86 $this->assertCount(1, $params); 87 $this->assertEquals($in_values[0], $params[0]); 88 89 // Correct usage of single value. 90 $in_value = 'value1'; 91 list($usql, $params) = $DB->get_in_or_equal($in_values); 92 $this->assertEquals("= ?", $usql); 93 $this->assertCount(1, $params); 94 $this->assertEquals($in_value, $params[0]); 95 96 // SQL_PARAMS_QM - NOT IN or <>. 97 98 // Correct usage of multiple values. 99 $in_values = array('value1', 'value2', 'value3', 'value4'); 100 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 101 $this->assertEquals("NOT IN (?,?,?,?)", $usql); 102 $this->assertCount(4, $params); 103 foreach ($params as $key => $value) { 104 $this->assertEquals($in_values[$key], $value); 105 } 106 107 // Correct usage of single value (in array(). 108 $in_values = array('value1'); 109 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 110 $this->assertEquals("<> ?", $usql); 111 $this->assertCount(1, $params); 112 $this->assertEquals($in_values[0], $params[0]); 113 114 // Correct usage of single value. 115 $in_value = 'value1'; 116 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); 117 $this->assertEquals("<> ?", $usql); 118 $this->assertCount(1, $params); 119 $this->assertEquals($in_value, $params[0]); 120 121 // SQL_PARAMS_NAMED - IN or =. 122 123 // Correct usage of multiple values. 124 $in_values = array('value1', 'value2', 'value3', 'value4'); 125 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 126 $this->assertCount(4, $params); 127 reset($in_values); 128 $ps = array(); 129 foreach ($params as $key => $value) { 130 $this->assertEquals(current($in_values), $value); 131 next($in_values); 132 $ps[] = ':'.$key; 133 } 134 $this->assertEquals("IN (".implode(',', $ps).")", $usql); 135 136 // Correct usage of single values (in array). 137 $in_values = array('value1'); 138 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 139 $this->assertCount(1, $params); 140 $value = reset($params); 141 $key = key($params); 142 $this->assertEquals("= :$key", $usql); 143 $this->assertEquals($in_value, $value); 144 145 // Correct usage of single value. 146 $in_value = 'value1'; 147 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true); 148 $this->assertCount(1, $params); 149 $value = reset($params); 150 $key = key($params); 151 $this->assertEquals("= :$key", $usql); 152 $this->assertEquals($in_value, $value); 153 154 // SQL_PARAMS_NAMED - NOT IN or <>. 155 156 // Correct usage of multiple values. 157 $in_values = array('value1', 'value2', 'value3', 'value4'); 158 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 159 $this->assertCount(4, $params); 160 reset($in_values); 161 $ps = array(); 162 foreach ($params as $key => $value) { 163 $this->assertEquals(current($in_values), $value); 164 next($in_values); 165 $ps[] = ':'.$key; 166 } 167 $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql); 168 169 // Correct usage of single values (in array). 170 $in_values = array('value1'); 171 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 172 $this->assertCount(1, $params); 173 $value = reset($params); 174 $key = key($params); 175 $this->assertEquals("<> :$key", $usql); 176 $this->assertEquals($in_value, $value); 177 178 // Correct usage of single value. 179 $in_value = 'value1'; 180 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 181 $this->assertCount(1, $params); 182 $value = reset($params); 183 $key = key($params); 184 $this->assertEquals("<> :$key", $usql); 185 $this->assertEquals($in_value, $value); 186 187 // Make sure the param names are unique. 188 list($usql1, $params1) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param'); 189 list($usql2, $params2) = $DB->get_in_or_equal(array(1, 2, 3), SQL_PARAMS_NAMED, 'param'); 190 $params1 = array_keys($params1); 191 $params2 = array_keys($params2); 192 $common = array_intersect($params1, $params2); 193 $this->assertCount(0, $common); 194 195 // Some incorrect tests. 196 197 // Incorrect usage passing not-allowed params type. 198 $in_values = array(1, 2, 3); 199 try { 200 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false); 201 $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR'); 202 } catch (moodle_exception $e) { 203 $this->assertInstanceOf('dml_exception', $e); 204 $this->assertSame('typenotimplement', $e->errorcode); 205 } 206 207 // Incorrect usage passing empty array. 208 $in_values = array(); 209 try { 210 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false); 211 $this->fail('An Exception is missing, expected due to empty array of items'); 212 } catch (moodle_exception $e) { 213 $this->assertInstanceOf('coding_exception', $e); 214 } 215 216 // Test using $onemptyitems. 217 218 // Correct usage passing empty array and $onemptyitems = null (equal = true, QM). 219 $in_values = array(); 220 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, null); 221 $this->assertSame(' IS NULL', $usql); 222 $this->assertSame(array(), $params); 223 224 // Correct usage passing empty array and $onemptyitems = null (equal = false, NAMED). 225 $in_values = array(); 226 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, null); 227 $this->assertSame(' IS NOT NULL', $usql); 228 $this->assertSame(array(), $params); 229 230 // Correct usage passing empty array and $onemptyitems = true (equal = true, QM). 231 $in_values = array(); 232 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true); 233 $this->assertSame('= ?', $usql); 234 $this->assertSame(array(true), $params); 235 236 // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED). 237 $in_values = array(); 238 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true); 239 $this->assertCount(1, $params); 240 $value = reset($params); 241 $key = key($params); 242 $this->assertSame('<> :'.$key, $usql); 243 $this->assertSame($value, true); 244 245 // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM). 246 $in_values = array(); 247 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1); 248 $this->assertSame('= ?', $usql); 249 $this->assertSame(array(-1), $params); 250 251 // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED). 252 $in_values = array(); 253 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1); 254 $this->assertCount(1, $params); 255 $value = reset($params); 256 $key = key($params); 257 $this->assertSame('<> :'.$key, $usql); 258 $this->assertSame($value, -1); 259 260 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM). 261 $in_values = array(); 262 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue'); 263 $this->assertSame('= ?', $usql); 264 $this->assertSame(array('onevalue'), $params); 265 266 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED). 267 $in_values = array(); 268 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue'); 269 $this->assertCount(1, $params); 270 $value = reset($params); 271 $key = key($params); 272 $this->assertSame('<> :'.$key, $usql); 273 $this->assertSame($value, 'onevalue'); 274 } 275 276 public function test_fix_table_names() { 277 $DB = new moodle_database_for_testing(); 278 $prefix = $DB->get_prefix(); 279 280 // Simple placeholder. 281 $placeholder = "{user_123}"; 282 $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder)); 283 284 // Wrong table name. 285 $placeholder = "{user-a}"; 286 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder)); 287 288 // Wrong table name. 289 $placeholder = "{123user}"; 290 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder)); 291 292 // Full SQL. 293 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid"; 294 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid"; 295 $this->assertSame($expected, $DB->public_fix_table_names($sql)); 296 } 297 298 public function test_fix_sql_params() { 299 $DB = $this->tdb; 300 $prefix = $DB->get_prefix(); 301 302 $table = $this->get_test_table(); 303 $tablename = $table->getName(); 304 305 // Correct table placeholder substitution. 306 $sql = "SELECT * FROM {{$tablename}}"; 307 $sqlarray = $DB->fix_sql_params($sql); 308 $this->assertEquals("SELECT * FROM {$prefix}".$tablename, $sqlarray[0]); 309 310 // Conversions of all param types. 311 $sql = array(); 312 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$prefix}testtable WHERE name = :param1, course = :param2"; 313 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$prefix}testtable WHERE name = ?, course = ?"; 314 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$prefix}testtable WHERE name = \$1, course = \$2"; 315 316 $params = array(); 317 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1); 318 $params[SQL_PARAMS_QM] = array('first record', 1); 319 $params[SQL_PARAMS_DOLLAR] = array('first record', 1); 320 321 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]); 322 $this->assertSame($rsql, $sql[$rtype]); 323 $this->assertSame($rparams, $params[$rtype]); 324 325 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]); 326 $this->assertSame($rsql, $sql[$rtype]); 327 $this->assertSame($rparams, $params[$rtype]); 328 329 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]); 330 $this->assertSame($rsql, $sql[$rtype]); 331 $this->assertSame($rparams, $params[$rtype]); 332 333 // Malformed table placeholder. 334 $sql = "SELECT * FROM [testtable]"; 335 $sqlarray = $DB->fix_sql_params($sql); 336 $this->assertSame($sql, $sqlarray[0]); 337 338 // Mixed param types (colon and dollar). 339 $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1"; 340 $params = array('param1' => 'record1', 'param2' => 3); 341 try { 342 $DB->fix_sql_params($sql, $params); 343 $this->fail("Expecting an exception, none occurred"); 344 } catch (moodle_exception $e) { 345 $this->assertInstanceOf('dml_exception', $e); 346 } 347 348 // Mixed param types (question and dollar). 349 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1"; 350 $params = array('param1' => 'record2', 'param2' => 5); 351 try { 352 $DB->fix_sql_params($sql, $params); 353 $this->fail("Expecting an exception, none occurred"); 354 } catch (moodle_exception $e) { 355 $this->assertInstanceOf('dml_exception', $e); 356 } 357 358 // Too few params in sql. 359 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?"; 360 $params = array('record2', 3); 361 try { 362 $DB->fix_sql_params($sql, $params); 363 $this->fail("Expecting an exception, none occurred"); 364 } catch (moodle_exception $e) { 365 $this->assertInstanceOf('dml_exception', $e); 366 } 367 368 // Too many params in array: no error, just use what is necessary. 369 $params[] = 1; 370 $params[] = time(); 371 $sqlarray = $DB->fix_sql_params($sql, $params); 372 $this->assertInternalType('array', $sqlarray); 373 $this->assertCount(3, $sqlarray[1]); 374 375 // Named params missing from array. 376 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; 377 $params = array('wrongname' => 'record1', 'course' => 1); 378 try { 379 $DB->fix_sql_params($sql, $params); 380 $this->fail("Expecting an exception, none occurred"); 381 } catch (moodle_exception $e) { 382 $this->assertInstanceOf('dml_exception', $e); 383 } 384 385 // Duplicate named param in query - this is a very important feature!! 386 // it helps with debugging of sloppy code. 387 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name"; 388 $params = array('name' => 'record2', 'course' => 3); 389 try { 390 $DB->fix_sql_params($sql, $params); 391 $this->fail("Expecting an exception, none occurred"); 392 } catch (moodle_exception $e) { 393 $this->assertInstanceOf('dml_exception', $e); 394 } 395 396 // Extra named param is ignored. 397 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; 398 $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha'); 399 $sqlarray = $DB->fix_sql_params($sql, $params); 400 $this->assertInternalType('array', $sqlarray); 401 $this->assertCount(2, $sqlarray[1]); 402 403 // Params exceeding 30 chars length. 404 $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30"; 405 $params = array('long_placeholder_with_more_than_30' => 'record1'); 406 try { 407 $DB->fix_sql_params($sql, $params); 408 $this->fail("Expecting an exception, none occurred"); 409 } catch (moodle_exception $e) { 410 $this->assertInstanceOf('coding_exception', $e); 411 } 412 413 // Booleans in NAMED params are casting to 1/0 int. 414 $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?"; 415 $params = array(true, false); 416 list($sql, $params) = $DB->fix_sql_params($sql, $params); 417 $this->assertTrue(reset($params) === 1); 418 $this->assertTrue(next($params) === 0); 419 420 // Booleans in QM params are casting to 1/0 int. 421 $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2"; 422 $params = array('course1' => true, 'course2' => false); 423 list($sql, $params) = $DB->fix_sql_params($sql, $params); 424 $this->assertTrue(reset($params) === 1); 425 $this->assertTrue(next($params) === 0); 426 427 // Booleans in DOLLAR params are casting to 1/0 int. 428 $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2"; 429 $params = array(true, false); 430 list($sql, $params) = $DB->fix_sql_params($sql, $params); 431 $this->assertTrue(reset($params) === 1); 432 $this->assertTrue(next($params) === 0); 433 434 // No data types are touched except bool. 435 $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)"; 436 $inparams = array('abc', 'ABC', null, '1', 1, 1.4); 437 list($sql, $params) = $DB->fix_sql_params($sql, $inparams); 438 $this->assertSame(array_values($params), array_values($inparams)); 439 } 440 441 public function test_strtok() { 442 // Strtok was previously used by bound emulation, make sure it is not used any more. 443 $DB = $this->tdb; 444 $dbman = $this->tdb->get_manager(); 445 446 $table = $this->get_test_table(); 447 $tablename = $table->getName(); 448 449 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 450 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 451 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala'); 452 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 453 $dbman->create_table($table); 454 455 $str = 'a?b?c?d'; 456 $this->assertSame(strtok($str, '?'), 'a'); 457 458 $DB->get_records($tablename, array('id'=>1)); 459 460 $this->assertSame(strtok('?'), 'b'); 461 } 462 463 public function test_tweak_param_names() { 464 // Note the tweak_param_names() method is only available in the oracle driver, 465 // hence we look for expected results indirectly, by testing various DML methods. 466 // with some "extreme" conditions causing the tweak to happen. 467 $DB = $this->tdb; 468 $dbman = $this->tdb->get_manager(); 469 470 $table = $this->get_test_table(); 471 $tablename = $table->getName(); 472 473 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 474 // Add some columns with 28 chars in the name. 475 $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10'); 476 $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2'); 477 $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100'); 478 // Add some columns with 30 chars in the name. 479 $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10'); 480 $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2'); 481 $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100'); 482 483 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 484 485 $dbman->create_table($table); 486 487 $this->assertTrue($dbman->table_exists($tablename)); 488 489 // Test insert record. 490 $rec1 = new stdClass(); 491 $rec1->long_int_columnname_with_28c = 28; 492 $rec1->long_dec_columnname_with_28c = 28.28; 493 $rec1->long_str_columnname_with_28c = '28'; 494 $rec1->long_int_columnname_with_30cxx = 30; 495 $rec1->long_dec_columnname_with_30cxx = 30.30; 496 $rec1->long_str_columnname_with_30cxx = '30'; 497 498 // Insert_record(). 499 $rec1->id = $DB->insert_record($tablename, $rec1); 500 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 501 502 // Update_record(). 503 $DB->update_record($tablename, $rec1); 504 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 505 506 // Set_field(). 507 $rec1->long_int_columnname_with_28c = 280; 508 $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c, 509 array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28)); 510 $rec1->long_dec_columnname_with_28c = 280.28; 511 $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c, 512 array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28)); 513 $rec1->long_str_columnname_with_28c = '280'; 514 $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c, 515 array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28')); 516 $rec1->long_int_columnname_with_30cxx = 300; 517 $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx, 518 array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30)); 519 $rec1->long_dec_columnname_with_30cxx = 300.30; 520 $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx, 521 array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30)); 522 $rec1->long_str_columnname_with_30cxx = '300'; 523 $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx, 524 array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30')); 525 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id))); 526 527 // Delete_records(). 528 $rec2 = $DB->get_record($tablename, array('id' => $rec1->id)); 529 $rec2->id = $DB->insert_record($tablename, $rec2); 530 $this->assertEquals(2, $DB->count_records($tablename)); 531 $DB->delete_records($tablename, (array) $rec2); 532 $this->assertEquals(1, $DB->count_records($tablename)); 533 534 // Get_recordset(). 535 $rs = $DB->get_recordset($tablename, (array) $rec1); 536 $iterations = 0; 537 foreach ($rs as $rec2) { 538 $iterations++; 539 } 540 $rs->close(); 541 $this->assertEquals(1, $iterations); 542 $this->assertEquals($rec1, $rec2); 543 544 // Get_records(). 545 $recs = $DB->get_records($tablename, (array) $rec1); 546 $this->assertCount(1, $recs); 547 $this->assertEquals($rec1, reset($recs)); 548 549 // Get_fieldset_select(). 550 $select = 'id = :id AND 551 long_int_columnname_with_28c = :long_int_columnname_with_28c AND 552 long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND 553 long_str_columnname_with_28c = :long_str_columnname_with_28c AND 554 long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND 555 long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND 556 long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx'; 557 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1); 558 $this->assertCount(1, $fields); 559 $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields)); 560 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1); 561 $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields)); 562 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1); 563 $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields)); 564 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1); 565 $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields)); 566 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1); 567 $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields)); 568 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1); 569 $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields)); 570 571 // Overlapping placeholders (progressive str_replace). 572 $overlapselect = 'id = :p AND 573 long_int_columnname_with_28c = :param1 AND 574 long_dec_columnname_with_28c = :param2 AND 575 long_str_columnname_with_28c = :param_with_29_characters_long AND 576 long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND 577 long_dec_columnname_with_30cxx = :param_ AND 578 long_str_columnname_with_30cxx = :param__'; 579 $overlapparams = array( 580 'p' => $rec1->id, 581 'param1' => $rec1->long_int_columnname_with_28c, 582 'param2' => $rec1->long_dec_columnname_with_28c, 583 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c, 584 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx, 585 'param_' => $rec1->long_dec_columnname_with_30cxx, 586 'param__' => $rec1->long_str_columnname_with_30cxx); 587 $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams); 588 $this->assertCount(1, $recs); 589 $this->assertEquals($rec1, reset($recs)); 590 591 // Execute(). 592 $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1); 593 $this->assertEquals(0, $DB->count_records($tablename)); 594 } 595 596 public function test_get_tables() { 597 $DB = $this->tdb; 598 $dbman = $this->tdb->get_manager(); 599 600 // Need to test with multiple DBs. 601 $table = $this->get_test_table(); 602 $tablename = $table->getName(); 603 604 $original_count = count($DB->get_tables()); 605 606 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 607 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 608 609 $dbman->create_table($table); 610 $this->assertTrue(count($DB->get_tables()) == $original_count + 1); 611 612 $dbman->drop_table($table); 613 $this->assertTrue(count($DB->get_tables()) == $original_count); 614 } 615 616 public function test_get_indexes() { 617 $DB = $this->tdb; 618 $dbman = $this->tdb->get_manager(); 619 620 $table = $this->get_test_table(); 621 $tablename = $table->getName(); 622 623 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 624 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 625 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 626 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 627 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id')); 628 $dbman->create_table($table); 629 630 $indices = $DB->get_indexes($tablename); 631 $this->assertInternalType('array', $indices); 632 $this->assertCount(2, $indices); 633 // We do not care about index names for now. 634 $first = array_shift($indices); 635 $second = array_shift($indices); 636 if (count($first['columns']) == 2) { 637 $composed = $first; 638 $single = $second; 639 } else { 640 $composed = $second; 641 $single = $first; 642 } 643 $this->assertFalse($single['unique']); 644 $this->assertTrue($composed['unique']); 645 $this->assertCount(1, $single['columns']); 646 $this->assertCount(2, $composed['columns']); 647 $this->assertSame('course', $single['columns'][0]); 648 $this->assertSame('course', $composed['columns'][0]); 649 $this->assertSame('id', $composed['columns'][1]); 650 } 651 652 public function test_get_columns() { 653 $DB = $this->tdb; 654 $dbman = $this->tdb->get_manager(); 655 656 $table = $this->get_test_table(); 657 $tablename = $table->getName(); 658 659 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 660 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 661 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala'); 662 $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 663 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2'); 664 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 665 $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300); 666 $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400); 667 $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1'); 668 $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2'); 669 $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3'); 670 $table->add_field('someint1', XMLDB_TYPE_INTEGER, '1', null, null, null, '0'); 671 $table->add_field('someint2', XMLDB_TYPE_INTEGER, '2', null, null, null, '0'); 672 $table->add_field('someint3', XMLDB_TYPE_INTEGER, '3', null, null, null, '0'); 673 $table->add_field('someint4', XMLDB_TYPE_INTEGER, '4', null, null, null, '0'); 674 $table->add_field('someint5', XMLDB_TYPE_INTEGER, '5', null, null, null, '0'); 675 $table->add_field('someint6', XMLDB_TYPE_INTEGER, '6', null, null, null, '0'); 676 $table->add_field('someint7', XMLDB_TYPE_INTEGER, '7', null, null, null, '0'); 677 $table->add_field('someint8', XMLDB_TYPE_INTEGER, '8', null, null, null, '0'); 678 $table->add_field('someint9', XMLDB_TYPE_INTEGER, '9', null, null, null, '0'); 679 $table->add_field('someint10', XMLDB_TYPE_INTEGER, '10', null, null, null, '0'); 680 $table->add_field('someint18', XMLDB_TYPE_INTEGER, '18', null, null, null, '0'); 681 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 682 $dbman->create_table($table); 683 684 $columns = $DB->get_columns($tablename); 685 $this->assertInternalType('array', $columns); 686 687 $fields = $table->getFields(); 688 $this->assertCount(count($columns), $fields); 689 690 $field = $columns['id']; 691 $this->assertSame('R', $field->meta_type); 692 $this->assertTrue($field->auto_increment); 693 $this->assertTrue($field->unique); 694 695 $field = $columns['course']; 696 $this->assertSame('I', $field->meta_type); 697 $this->assertFalse($field->auto_increment); 698 $this->assertTrue($field->has_default); 699 $this->assertEquals(0, $field->default_value); 700 $this->assertTrue($field->not_null); 701 702 for ($i=1; $i<=10; $i++) { 703 $field = $columns['someint'.$i]; 704 $this->assertSame('I', $field->meta_type); 705 $this->assertGreaterThanOrEqual($i, $field->max_length); 706 } 707 $field = $columns['someint18']; 708 $this->assertSame('I', $field->meta_type); 709 $this->assertGreaterThanOrEqual(18, $field->max_length); 710 711 $field = $columns['name']; 712 $this->assertSame('C', $field->meta_type); 713 $this->assertFalse($field->auto_increment); 714 $this->assertEquals(255, $field->max_length); 715 $this->assertTrue($field->has_default); 716 $this->assertSame('lala', $field->default_value); 717 $this->assertFalse($field->not_null); 718 719 $field = $columns['description']; 720 $this->assertSame('X', $field->meta_type); 721 $this->assertFalse($field->auto_increment); 722 $this->assertFalse($field->has_default); 723 $this->assertNull($field->default_value); 724 $this->assertFalse($field->not_null); 725 726 $field = $columns['enumfield']; 727 $this->assertSame('C', $field->meta_type); 728 $this->assertFalse($field->auto_increment); 729 $this->assertSame('test2', $field->default_value); 730 $this->assertTrue($field->not_null); 731 732 $field = $columns['onenum']; 733 $this->assertSame('N', $field->meta_type); 734 $this->assertFalse($field->auto_increment); 735 $this->assertEquals(10, $field->max_length); 736 $this->assertEquals(2, $field->scale); 737 $this->assertTrue($field->has_default); 738 $this->assertEquals(200.0, $field->default_value); 739 $this->assertFalse($field->not_null); 740 741 $field = $columns['onefloat']; 742 $this->assertSame('N', $field->meta_type); 743 $this->assertFalse($field->auto_increment); 744 $this->assertTrue($field->has_default); 745 $this->assertEquals(300.0, $field->default_value); 746 $this->assertFalse($field->not_null); 747 748 $field = $columns['anotherfloat']; 749 $this->assertSame('N', $field->meta_type); 750 $this->assertFalse($field->auto_increment); 751 $this->assertTrue($field->has_default); 752 $this->assertEquals(400.0, $field->default_value); 753 $this->assertFalse($field->not_null); 754 755 // Test negative defaults in numerical columns. 756 $field = $columns['negativedfltint']; 757 $this->assertTrue($field->has_default); 758 $this->assertEquals(-1, $field->default_value); 759 760 $field = $columns['negativedfltnumber']; 761 $this->assertTrue($field->has_default); 762 $this->assertEquals(-2, $field->default_value); 763 764 $field = $columns['negativedfltfloat']; 765 $this->assertTrue($field->has_default); 766 $this->assertEquals(-3, $field->default_value); 767 768 for ($i = 0; $i < count($columns); $i++) { 769 if ($i == 0) { 770 $next_column = reset($columns); 771 $next_field = reset($fields); 772 } else { 773 $next_column = next($columns); 774 $next_field = next($fields); 775 } 776 777 $this->assertEquals($next_column->name, $next_field->getName()); 778 } 779 780 // Test get_columns for non-existing table returns empty array. MDL-30147. 781 $columns = $DB->get_columns('xxxx'); 782 $this->assertEquals(array(), $columns); 783 784 // Create something similar to "context_temp" with id column without sequence. 785 $dbman->drop_table($table); 786 $table = $this->get_test_table(); 787 $tablename = $table->getName(); 788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); 789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 791 $dbman->create_table($table); 792 793 $columns = $DB->get_columns($tablename); 794 $this->assertFalse($columns['id']->auto_increment); 795 } 796 797 public function test_get_manager() { 798 $DB = $this->tdb; 799 $dbman = $this->tdb->get_manager(); 800 801 $this->assertInstanceOf('database_manager', $dbman); 802 } 803 804 public function test_setup_is_unicodedb() { 805 $DB = $this->tdb; 806 $this->assertTrue($DB->setup_is_unicodedb()); 807 } 808 809 public function test_set_debug() { // Tests get_debug() too. 810 $DB = $this->tdb; 811 $dbman = $this->tdb->get_manager(); 812 813 $table = $this->get_test_table(); 814 $tablename = $table->getName(); 815 816 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 817 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 818 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 819 $dbman->create_table($table); 820 821 $sql = "SELECT * FROM {{$tablename}}"; 822 823 $prevdebug = $DB->get_debug(); 824 825 ob_start(); 826 $DB->set_debug(true); 827 $this->assertTrue($DB->get_debug()); 828 $DB->execute($sql); 829 $DB->set_debug(false); 830 $this->assertFalse($DB->get_debug()); 831 $debuginfo = ob_get_contents(); 832 ob_end_clean(); 833 $this->assertFalse($debuginfo === ''); 834 835 ob_start(); 836 $DB->execute($sql); 837 $debuginfo = ob_get_contents(); 838 ob_end_clean(); 839 $this->assertTrue($debuginfo === ''); 840 841 $DB->set_debug($prevdebug); 842 } 843 844 public function test_execute() { 845 $DB = $this->tdb; 846 $dbman = $this->tdb->get_manager(); 847 848 $table1 = $this->get_test_table('1'); 849 $tablename1 = $table1->getName(); 850 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 851 $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 852 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 853 $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 854 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 855 $dbman->create_table($table1); 856 857 $table2 = $this->get_test_table('2'); 858 $tablename2 = $table2->getName(); 859 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 860 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 861 $table2->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 862 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 863 $dbman->create_table($table2); 864 865 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa')); 866 $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb')); 867 $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc')); 868 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd')); 869 870 // Select results are ignored. 871 $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course"; 872 $this->assertTrue($DB->execute($sql, array('course'=>3))); 873 874 // Throw exception on error. 875 $sql = "XXUPDATE SET XSSD"; 876 try { 877 $DB->execute($sql); 878 $this->fail("Expecting an exception, none occurred"); 879 } catch (moodle_exception $e) { 880 $this->assertInstanceOf('dml_exception', $e); 881 } 882 883 // Update records. 884 $sql = "UPDATE {{$tablename1}} 885 SET course = 6 886 WHERE course = ?"; 887 $this->assertTrue($DB->execute($sql, array('3'))); 888 $this->assertEquals(2, $DB->count_records($tablename1, array('course' => 6))); 889 890 // Update records with subquery condition. 891 // Confirm that the option not using table aliases is cross-db. 892 $sql = "UPDATE {{$tablename1}} 893 SET course = 0 894 WHERE NOT EXISTS ( 895 SELECT course 896 FROM {{$tablename2}} tbl2 897 WHERE tbl2.course = {{$tablename1}}.course 898 AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed. 899 $this->assertTrue($DB->execute($sql)); 900 901 // Insert from one into second table. 902 $sql = "INSERT INTO {{$tablename2}} (course) 903 904 SELECT course 905 FROM {{$tablename1}}"; 906 $this->assertTrue($DB->execute($sql)); 907 $this->assertEquals(4, $DB->count_records($tablename2)); 908 909 // Insert a TEXT with raw SQL, binding TEXT params. 910 $course = 9999; 911 $onetext = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 912 $sql = "INSERT INTO {{$tablename2}} (course, onetext) 913 VALUES (:course, :onetext)"; 914 $DB->execute($sql, array('course' => $course, 'onetext' => $onetext)); 915 $records = $DB->get_records($tablename2, array('course' => $course)); 916 $this->assertCount(1, $records); 917 $record = reset($records); 918 $this->assertSame($onetext, $record->onetext); 919 920 // Update a TEXT with raw SQL, binding TEXT params. 921 $newcourse = 10000; 922 $newonetext = file_get_contents(__DIR__ . '/fixtures/clob.txt') . '- updated'; 923 $sql = "UPDATE {{$tablename2}} SET course = :newcourse, onetext = :newonetext 924 WHERE course = :oldcourse"; 925 $DB->execute($sql, array('oldcourse' => $course, 'newcourse' => $newcourse, 'newonetext' => $newonetext)); 926 $records = $DB->get_records($tablename2, array('course' => $course)); 927 $this->assertCount(0, $records); 928 $records = $DB->get_records($tablename2, array('course' => $newcourse)); 929 $this->assertCount(1, $records); 930 $record = reset($records); 931 $this->assertSame($newonetext, $record->onetext); 932 } 933 934 public function test_get_recordset() { 935 $DB = $this->tdb; 936 $dbman = $DB->get_manager(); 937 938 $table = $this->get_test_table(); 939 $tablename = $table->getName(); 940 941 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 942 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 943 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 944 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 945 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 946 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 947 $dbman->create_table($table); 948 949 $data = array(array('course' => 3, 'name' => 'record1', 'onetext'=>'abc'), 950 array('course' => 3, 'name' => 'record2', 'onetext'=>'abcd'), 951 array('course' => 5, 'name' => 'record3', 'onetext'=>'abcde')); 952 953 foreach ($data as $key => $record) { 954 $data[$key]['id'] = $DB->insert_record($tablename, $record); 955 } 956 957 // Standard recordset iteration. 958 $rs = $DB->get_recordset($tablename); 959 $this->assertInstanceOf('moodle_recordset', $rs); 960 reset($data); 961 foreach ($rs as $record) { 962 $data_record = current($data); 963 foreach ($record as $k => $v) { 964 $this->assertEquals($data_record[$k], $v); 965 } 966 next($data); 967 } 968 $rs->close(); 969 970 // Iterator style usage. 971 $rs = $DB->get_recordset($tablename); 972 $this->assertInstanceOf('moodle_recordset', $rs); 973 reset($data); 974 while ($rs->valid()) { 975 $record = $rs->current(); 976 $data_record = current($data); 977 foreach ($record as $k => $v) { 978 $this->assertEquals($data_record[$k], $v); 979 } 980 next($data); 981 $rs->next(); 982 } 983 $rs->close(); 984 985 // Make sure rewind is ignored. 986 $rs = $DB->get_recordset($tablename); 987 $this->assertInstanceOf('moodle_recordset', $rs); 988 reset($data); 989 $i = 0; 990 foreach ($rs as $record) { 991 $i++; 992 $rs->rewind(); 993 if ($i > 10) { 994 $this->fail('revind not ignored in recordsets'); 995 break; 996 } 997 $data_record = current($data); 998 foreach ($record as $k => $v) { 999 $this->assertEquals($data_record[$k], $v); 1000 } 1001 next($data); 1002 } 1003 $rs->close(); 1004 1005 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 1006 $conditions = array('onetext' => '1'); 1007 try { 1008 $rs = $DB->get_recordset($tablename, $conditions); 1009 $this->fail('An Exception is missing, expected due to equating of text fields'); 1010 } catch (moodle_exception $e) { 1011 $this->assertInstanceOf('dml_exception', $e); 1012 $this->assertSame('textconditionsnotallowed', $e->errorcode); 1013 } 1014 1015 // Test nested iteration. 1016 $rs1 = $DB->get_recordset($tablename); 1017 $i = 0; 1018 foreach ($rs1 as $record1) { 1019 $rs2 = $DB->get_recordset($tablename); 1020 $i++; 1021 $j = 0; 1022 foreach ($rs2 as $record2) { 1023 $j++; 1024 } 1025 $rs2->close(); 1026 $this->assertCount($j, $data); 1027 } 1028 $rs1->close(); 1029 $this->assertCount($i, $data); 1030 1031 // Notes: 1032 // * limits are tested in test_get_recordset_sql() 1033 // * where_clause() is used internally and is tested in test_get_records() 1034 } 1035 1036 public function test_get_recordset_static() { 1037 $DB = $this->tdb; 1038 $dbman = $DB->get_manager(); 1039 1040 $table = $this->get_test_table(); 1041 $tablename = $table->getName(); 1042 1043 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1044 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1045 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1046 $dbman->create_table($table); 1047 1048 $DB->insert_record($tablename, array('course' => 1)); 1049 $DB->insert_record($tablename, array('course' => 2)); 1050 $DB->insert_record($tablename, array('course' => 3)); 1051 $DB->insert_record($tablename, array('course' => 4)); 1052 1053 $rs = $DB->get_recordset($tablename, array(), 'id'); 1054 1055 $DB->set_field($tablename, 'course', 666, array('course'=>1)); 1056 $DB->delete_records($tablename, array('course'=>2)); 1057 1058 $i = 0; 1059 foreach ($rs as $record) { 1060 $i++; 1061 $this->assertEquals($i, $record->course); 1062 } 1063 $rs->close(); 1064 $this->assertEquals(4, $i); 1065 1066 // Now repeat with limits because it may use different code. 1067 $DB->delete_records($tablename, array()); 1068 1069 $DB->insert_record($tablename, array('course' => 1)); 1070 $DB->insert_record($tablename, array('course' => 2)); 1071 $DB->insert_record($tablename, array('course' => 3)); 1072 $DB->insert_record($tablename, array('course' => 4)); 1073 1074 $rs = $DB->get_recordset($tablename, array(), 'id', '*', 0, 3); 1075 1076 $DB->set_field($tablename, 'course', 666, array('course'=>1)); 1077 $DB->delete_records($tablename, array('course'=>2)); 1078 1079 $i = 0; 1080 foreach ($rs as $record) { 1081 $i++; 1082 $this->assertEquals($i, $record->course); 1083 } 1084 $rs->close(); 1085 $this->assertEquals(3, $i); 1086 } 1087 1088 public function test_get_recordset_iterator_keys() { 1089 $DB = $this->tdb; 1090 $dbman = $DB->get_manager(); 1091 1092 $table = $this->get_test_table(); 1093 $tablename = $table->getName(); 1094 1095 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1096 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1097 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 1098 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1099 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1100 $dbman->create_table($table); 1101 1102 $data = array(array('course' => 3, 'name' => 'record1'), 1103 array('course' => 3, 'name' => 'record2'), 1104 array('course' => 5, 'name' => 'record3')); 1105 foreach ($data as $key => $record) { 1106 $data[$key]['id'] = $DB->insert_record($tablename, $record); 1107 } 1108 1109 // Test repeated numeric keys are returned ok. 1110 $rs = $DB->get_recordset($tablename, null, null, 'course, name, id'); 1111 1112 reset($data); 1113 $count = 0; 1114 foreach ($rs as $key => $record) { 1115 $data_record = current($data); 1116 $this->assertEquals($data_record['course'], $key); 1117 next($data); 1118 $count++; 1119 } 1120 $rs->close(); 1121 $this->assertEquals(3, $count); 1122 1123 // Test string keys are returned ok. 1124 $rs = $DB->get_recordset($tablename, null, null, 'name, course, id'); 1125 1126 reset($data); 1127 $count = 0; 1128 foreach ($rs as $key => $record) { 1129 $data_record = current($data); 1130 $this->assertEquals($data_record['name'], $key); 1131 next($data); 1132 $count++; 1133 } 1134 $rs->close(); 1135 $this->assertEquals(3, $count); 1136 1137 // Test numeric not starting in 1 keys are returned ok. 1138 $rs = $DB->get_recordset($tablename, null, 'id DESC', 'id, course, name'); 1139 1140 $data = array_reverse($data); 1141 reset($data); 1142 $count = 0; 1143 foreach ($rs as $key => $record) { 1144 $data_record = current($data); 1145 $this->assertEquals($data_record['id'], $key); 1146 next($data); 1147 $count++; 1148 } 1149 $rs->close(); 1150 $this->assertEquals(3, $count); 1151 } 1152 1153 public function test_get_recordset_list() { 1154 $DB = $this->tdb; 1155 $dbman = $DB->get_manager(); 1156 1157 $table = $this->get_test_table(); 1158 $tablename = $table->getName(); 1159 1160 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1161 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, null, null, '0'); 1162 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); 1163 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1164 $dbman->create_table($table); 1165 1166 $DB->insert_record($tablename, array('course' => 3)); 1167 $DB->insert_record($tablename, array('course' => 3)); 1168 $DB->insert_record($tablename, array('course' => 5)); 1169 $DB->insert_record($tablename, array('course' => 2)); 1170 $DB->insert_record($tablename, array('course' => null)); 1171 $DB->insert_record($tablename, array('course' => 1)); 1172 $DB->insert_record($tablename, array('course' => 0)); 1173 1174 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2)); 1175 $counter = 0; 1176 foreach ($rs as $record) { 1177 $counter++; 1178 } 1179 $this->assertEquals(3, $counter); 1180 $rs->close(); 1181 1182 $rs = $DB->get_recordset_list($tablename, 'course', array(3)); 1183 $counter = 0; 1184 foreach ($rs as $record) { 1185 $counter++; 1186 } 1187 $this->assertEquals(2, $counter); 1188 $rs->close(); 1189 1190 $rs = $DB->get_recordset_list($tablename, 'course', array(null)); 1191 $counter = 0; 1192 foreach ($rs as $record) { 1193 $counter++; 1194 } 1195 $this->assertEquals(1, $counter); 1196 $rs->close(); 1197 1198 $rs = $DB->get_recordset_list($tablename, 'course', array(6, null)); 1199 $counter = 0; 1200 foreach ($rs as $record) { 1201 $counter++; 1202 } 1203 $this->assertEquals(1, $counter); 1204 $rs->close(); 1205 1206 $rs = $DB->get_recordset_list($tablename, 'course', array(null, 5, 5, 5)); 1207 $counter = 0; 1208 foreach ($rs as $record) { 1209 $counter++; 1210 } 1211 $this->assertEquals(2, $counter); 1212 $rs->close(); 1213 1214 $rs = $DB->get_recordset_list($tablename, 'course', array(true)); 1215 $counter = 0; 1216 foreach ($rs as $record) { 1217 $counter++; 1218 } 1219 $this->assertEquals(1, $counter); 1220 $rs->close(); 1221 1222 $rs = $DB->get_recordset_list($tablename, 'course', array(false)); 1223 $counter = 0; 1224 foreach ($rs as $record) { 1225 $counter++; 1226 } 1227 $this->assertEquals(1, $counter); 1228 $rs->close(); 1229 1230 $rs = $DB->get_recordset_list($tablename, 'course', array()); // Must return 0 rows without conditions. MDL-17645. 1231 1232 $counter = 0; 1233 foreach ($rs as $record) { 1234 $counter++; 1235 } 1236 $rs->close(); 1237 $this->assertEquals(0, $counter); 1238 1239 // Notes: 1240 // * limits are tested in test_get_recordset_sql() 1241 // * where_clause() is used internally and is tested in test_get_records() 1242 } 1243 1244 public function test_get_recordset_select() { 1245 $DB = $this->tdb; 1246 $dbman = $DB->get_manager(); 1247 1248 $table = $this->get_test_table(); 1249 $tablename = $table->getName(); 1250 1251 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1252 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1253 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1254 $dbman->create_table($table); 1255 1256 $DB->insert_record($tablename, array('course' => 3)); 1257 $DB->insert_record($tablename, array('course' => 3)); 1258 $DB->insert_record($tablename, array('course' => 5)); 1259 $DB->insert_record($tablename, array('course' => 2)); 1260 1261 $rs = $DB->get_recordset_select($tablename, ''); 1262 $counter = 0; 1263 foreach ($rs as $record) { 1264 $counter++; 1265 } 1266 $rs->close(); 1267 $this->assertEquals(4, $counter); 1268 1269 $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3')); 1270 $counter = 0; 1271 foreach ($rs as $record) { 1272 $counter++; 1273 } 1274 $rs->close(); 1275 $this->assertEquals(2, $counter); 1276 1277 // Notes: 1278 // * limits are tested in test_get_recordset_sql() 1279 } 1280 1281 public function test_get_recordset_sql() { 1282 $DB = $this->tdb; 1283 $dbman = $DB->get_manager(); 1284 1285 $table = $this->get_test_table(); 1286 $tablename = $table->getName(); 1287 1288 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1289 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1290 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1291 $dbman->create_table($table); 1292 1293 $inskey1 = $DB->insert_record($tablename, array('course' => 3)); 1294 $inskey2 = $DB->insert_record($tablename, array('course' => 5)); 1295 $inskey3 = $DB->insert_record($tablename, array('course' => 4)); 1296 $inskey4 = $DB->insert_record($tablename, array('course' => 3)); 1297 $inskey5 = $DB->insert_record($tablename, array('course' => 2)); 1298 $inskey6 = $DB->insert_record($tablename, array('course' => 1)); 1299 $inskey7 = $DB->insert_record($tablename, array('course' => 0)); 1300 1301 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); 1302 $counter = 0; 1303 foreach ($rs as $record) { 1304 $counter++; 1305 } 1306 $rs->close(); 1307 $this->assertEquals(2, $counter); 1308 1309 // Limits - only need to test this case, the rest have been tested by test_get_records_sql() 1310 // only limitfrom = skips that number of records. 1311 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); 1312 $records = array(); 1313 foreach ($rs as $key => $record) { 1314 $records[$key] = $record; 1315 } 1316 $rs->close(); 1317 $this->assertCount(5, $records); 1318 $this->assertEquals($inskey3, reset($records)->id); 1319 $this->assertEquals($inskey7, end($records)->id); 1320 1321 // Note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here. 1322 } 1323 1324 public function test_export_table_recordset() { 1325 $DB = $this->tdb; 1326 $dbman = $DB->get_manager(); 1327 1328 $table = $this->get_test_table(); 1329 $tablename = $table->getName(); 1330 1331 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1332 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1333 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1334 $dbman->create_table($table); 1335 1336 $ids = array(); 1337 $ids[] = $DB->insert_record($tablename, array('course' => 3)); 1338 $ids[] = $DB->insert_record($tablename, array('course' => 5)); 1339 $ids[] = $DB->insert_record($tablename, array('course' => 4)); 1340 $ids[] = $DB->insert_record($tablename, array('course' => 3)); 1341 $ids[] = $DB->insert_record($tablename, array('course' => 2)); 1342 $ids[] = $DB->insert_record($tablename, array('course' => 1)); 1343 $ids[] = $DB->insert_record($tablename, array('course' => 0)); 1344 1345 $rs = $DB->export_table_recordset($tablename); 1346 $rids = array(); 1347 foreach ($rs as $record) { 1348 $rids[] = $record->id; 1349 } 1350 $rs->close(); 1351 $this->assertEquals($ids, $rids, '', 0, 0, true); 1352 } 1353 1354 public function test_get_records() { 1355 $DB = $this->tdb; 1356 $dbman = $DB->get_manager(); 1357 1358 $table = $this->get_test_table(); 1359 $tablename = $table->getName(); 1360 1361 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1362 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1363 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1364 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1365 $dbman->create_table($table); 1366 1367 $DB->insert_record($tablename, array('course' => 3)); 1368 $DB->insert_record($tablename, array('course' => 3)); 1369 $DB->insert_record($tablename, array('course' => 5)); 1370 $DB->insert_record($tablename, array('course' => 2)); 1371 1372 // All records. 1373 $records = $DB->get_records($tablename); 1374 $this->assertCount(4, $records); 1375 $this->assertEquals(3, $records[1]->course); 1376 $this->assertEquals(3, $records[2]->course); 1377 $this->assertEquals(5, $records[3]->course); 1378 $this->assertEquals(2, $records[4]->course); 1379 1380 // Records matching certain conditions. 1381 $records = $DB->get_records($tablename, array('course' => 3)); 1382 $this->assertCount(2, $records); 1383 $this->assertEquals(3, $records[1]->course); 1384 $this->assertEquals(3, $records[2]->course); 1385 1386 // All records sorted by course. 1387 $records = $DB->get_records($tablename, null, 'course'); 1388 $this->assertCount(4, $records); 1389 $current_record = reset($records); 1390 $this->assertEquals(4, $current_record->id); 1391 $current_record = next($records); 1392 $this->assertEquals(1, $current_record->id); 1393 $current_record = next($records); 1394 $this->assertEquals(2, $current_record->id); 1395 $current_record = next($records); 1396 $this->assertEquals(3, $current_record->id); 1397 1398 // All records, but get only one field. 1399 $records = $DB->get_records($tablename, null, '', 'id'); 1400 $this->assertFalse(isset($records[1]->course)); 1401 $this->assertTrue(isset($records[1]->id)); 1402 $this->assertCount(4, $records); 1403 1404 // Booleans into params. 1405 $records = $DB->get_records($tablename, array('course' => true)); 1406 $this->assertCount(0, $records); 1407 $records = $DB->get_records($tablename, array('course' => false)); 1408 $this->assertCount(0, $records); 1409 1410 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 1411 $conditions = array('onetext' => '1'); 1412 try { 1413 $records = $DB->get_records($tablename, $conditions); 1414 if (debugging()) { 1415 // Only in debug mode - hopefully all devs test code in debug mode... 1416 $this->fail('An Exception is missing, expected due to equating of text fields'); 1417 } 1418 } catch (moodle_exception $e) { 1419 $this->assertInstanceOf('dml_exception', $e); 1420 $this->assertSame('textconditionsnotallowed', $e->errorcode); 1421 } 1422 1423 // Test get_records passing non-existing table. 1424 // with params. 1425 try { 1426 $records = $DB->get_records('xxxx', array('id' => 0)); 1427 $this->fail('An Exception is missing, expected due to query against non-existing table'); 1428 } catch (moodle_exception $e) { 1429 $this->assertInstanceOf('dml_exception', $e); 1430 if (debugging()) { 1431 // Information for developers only, normal users get general error message. 1432 $this->assertSame('ddltablenotexist', $e->errorcode); 1433 } 1434 } 1435 1436 try { 1437 $records = $DB->get_records('xxxx', array('id' => '1')); 1438 $this->fail('An Exception is missing, expected due to query against non-existing table'); 1439 } catch (moodle_exception $e) { 1440 $this->assertInstanceOf('dml_exception', $e); 1441 if (debugging()) { 1442 // Information for developers only, normal users get general error message. 1443 $this->assertSame('ddltablenotexist', $e->errorcode); 1444 } 1445 } 1446 1447 // Test get_records passing non-existing column. 1448 try { 1449 $records = $DB->get_records($tablename, array('xxxx' => 0)); 1450 $this->fail('An Exception is missing, expected due to query against non-existing column'); 1451 } catch (moodle_exception $e) { 1452 $this->assertInstanceOf('dml_exception', $e); 1453 if (debugging()) { 1454 // Information for developers only, normal users get general error message. 1455 $this->assertSame('ddlfieldnotexist', $e->errorcode); 1456 } 1457 } 1458 1459 // Note: delegate limits testing to test_get_records_sql(). 1460 } 1461 1462 public function test_get_records_list() { 1463 $DB = $this->tdb; 1464 $dbman = $DB->get_manager(); 1465 1466 $table = $this->get_test_table(); 1467 $tablename = $table->getName(); 1468 1469 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1470 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1471 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1472 $dbman->create_table($table); 1473 1474 $DB->insert_record($tablename, array('course' => 3)); 1475 $DB->insert_record($tablename, array('course' => 3)); 1476 $DB->insert_record($tablename, array('course' => 5)); 1477 $DB->insert_record($tablename, array('course' => 2)); 1478 1479 $records = $DB->get_records_list($tablename, 'course', array(3, 2)); 1480 $this->assertInternalType('array', $records); 1481 $this->assertCount(3, $records); 1482 $this->assertEquals(1, reset($records)->id); 1483 $this->assertEquals(2, next($records)->id); 1484 $this->assertEquals(4, next($records)->id); 1485 1486 $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); // Must return 0 rows without conditions. MDL-17645. 1487 $this->assertCount(0, $records); 1488 1489 // Note: delegate limits testing to test_get_records_sql(). 1490 } 1491 1492 public function test_get_records_sql() { 1493 $DB = $this->tdb; 1494 $dbman = $DB->get_manager(); 1495 1496 $table = $this->get_test_table(); 1497 $tablename = $table->getName(); 1498 1499 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1500 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1501 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1502 $dbman->create_table($table); 1503 1504 $inskey1 = $DB->insert_record($tablename, array('course' => 3)); 1505 $inskey2 = $DB->insert_record($tablename, array('course' => 5)); 1506 $inskey3 = $DB->insert_record($tablename, array('course' => 4)); 1507 $inskey4 = $DB->insert_record($tablename, array('course' => 3)); 1508 $inskey5 = $DB->insert_record($tablename, array('course' => 2)); 1509 $inskey6 = $DB->insert_record($tablename, array('course' => 1)); 1510 $inskey7 = $DB->insert_record($tablename, array('course' => 0)); 1511 1512 $table2 = $this->get_test_table("2"); 1513 $tablename2 = $table2->getName(); 1514 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1515 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1516 $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 1517 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1518 $dbman->create_table($table2); 1519 1520 $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing')); 1521 $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang')); 1522 $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung')); 1523 $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong')); 1524 1525 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); 1526 $this->assertCount(2, $records); 1527 $this->assertEquals($inskey1, reset($records)->id); 1528 $this->assertEquals($inskey4, next($records)->id); 1529 1530 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test. 1531 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null); 1532 $this->assertDebuggingCalled(); 1533 $this->assertCount(6, $records); 1534 set_debugging(DEBUG_MINIMAL); 1535 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null); 1536 $this->assertDebuggingNotCalled(); 1537 $this->assertCount(6, $records); 1538 set_debugging(DEBUG_DEVELOPER); 1539 1540 // Negative limits = no limits. 1541 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1); 1542 $this->assertCount(7, $records); 1543 1544 // Zero limits = no limits. 1545 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0); 1546 $this->assertCount(7, $records); 1547 1548 // Only limitfrom = skips that number of records. 1549 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); 1550 $this->assertCount(5, $records); 1551 $this->assertEquals($inskey3, reset($records)->id); 1552 $this->assertEquals($inskey7, end($records)->id); 1553 1554 // Only limitnum = fetches that number of records. 1555 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3); 1556 $this->assertCount(3, $records); 1557 $this->assertEquals($inskey1, reset($records)->id); 1558 $this->assertEquals($inskey3, end($records)->id); 1559 1560 // Both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones. 1561 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2); 1562 $this->assertCount(2, $records); 1563 $this->assertEquals($inskey4, reset($records)->id); 1564 $this->assertEquals($inskey5, end($records)->id); 1565 1566 // Both limitfrom and limitnum in query having subqueris. 1567 // Note the subquery skips records with course = 0 and 3. 1568 $sql = "SELECT * FROM {{$tablename}} 1569 WHERE course NOT IN ( 1570 SELECT course FROM {{$tablename}} 1571 WHERE course IN (0, 3)) 1572 ORDER BY course"; 1573 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2. 1574 $this->assertCount(2, $records); 1575 $this->assertEquals($inskey6, reset($records)->id); 1576 $this->assertEquals($inskey5, end($records)->id); 1577 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2. 1578 $this->assertCount(2, $records); 1579 $this->assertEquals($inskey3, reset($records)->id); 1580 $this->assertEquals($inskey2, end($records)->id); 1581 1582 // Test 2 tables with aliases and limits with order bys. 1583 $sql = "SELECT t1.id, t1.course AS cid, t2.nametext 1584 FROM {{$tablename}} t1, {{$tablename2}} t2 1585 WHERE t2.course=t1.course 1586 ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext'); 1587 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5. 1588 $this->assertCount(2, $records); 1589 $this->assertSame('5', end($records)->cid); 1590 $this->assertSame('4', reset($records)->cid); 1591 1592 // Test 2 tables with aliases and limits with the highest INT limit works. 1593 $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}. 1594 $this->assertCount(2, $records); 1595 $this->assertSame('5', end($records)->cid); 1596 $this->assertSame('4', reset($records)->cid); 1597 1598 // Test 2 tables with aliases and limits with order bys (limit which is highest INT number). 1599 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses. 1600 $this->assertCount(0, $records); 1601 1602 // Test 2 tables with aliases and limits with order bys (limit which s highest INT number). 1603 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses. 1604 $this->assertCount(0, $records); 1605 1606 // TODO: Test limits in queries having DISTINCT clauses. 1607 1608 // Note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here. 1609 } 1610 1611 public function test_get_records_menu() { 1612 $DB = $this->tdb; 1613 $dbman = $DB->get_manager(); 1614 1615 $table = $this->get_test_table(); 1616 $tablename = $table->getName(); 1617 1618 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1619 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1620 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1621 $dbman->create_table($table); 1622 1623 $DB->insert_record($tablename, array('course' => 3)); 1624 $DB->insert_record($tablename, array('course' => 3)); 1625 $DB->insert_record($tablename, array('course' => 5)); 1626 $DB->insert_record($tablename, array('course' => 2)); 1627 1628 $records = $DB->get_records_menu($tablename, array('course' => 3)); 1629 $this->assertInternalType('array', $records); 1630 $this->assertCount(2, $records); 1631 $this->assertNotEmpty($records[1]); 1632 $this->assertNotEmpty($records[2]); 1633 $this->assertEquals(3, $records[1]); 1634 $this->assertEquals(3, $records[2]); 1635 1636 // Note: delegate limits testing to test_get_records_sql(). 1637 } 1638 1639 public function test_get_records_select_menu() { 1640 $DB = $this->tdb; 1641 $dbman = $DB->get_manager(); 1642 1643 $table = $this->get_test_table(); 1644 $tablename = $table->getName(); 1645 1646 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1647 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1648 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1649 $dbman->create_table($table); 1650 1651 $DB->insert_record($tablename, array('course' => 3)); 1652 $DB->insert_record($tablename, array('course' => 2)); 1653 $DB->insert_record($tablename, array('course' => 3)); 1654 $DB->insert_record($tablename, array('course' => 5)); 1655 1656 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2)); 1657 $this->assertInternalType('array', $records); 1658 1659 $this->assertCount(3, $records); 1660 $this->assertArrayHasKey(1, $records); 1661 $this->assertArrayNotHasKey(2, $records); 1662 $this->assertArrayHasKey(3, $records); 1663 $this->assertArrayHasKey(4, $records); 1664 $this->assertSame('3', $records[1]); 1665 $this->assertSame('3', $records[3]); 1666 $this->assertSame('5', $records[4]); 1667 1668 // Note: delegate limits testing to test_get_records_sql(). 1669 } 1670 1671 public function test_get_records_sql_menu() { 1672 $DB = $this->tdb; 1673 $dbman = $DB->get_manager(); 1674 1675 $table = $this->get_test_table(); 1676 $tablename = $table->getName(); 1677 1678 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1679 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1680 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1681 $dbman->create_table($table); 1682 1683 $DB->insert_record($tablename, array('course' => 3)); 1684 $DB->insert_record($tablename, array('course' => 2)); 1685 $DB->insert_record($tablename, array('course' => 3)); 1686 $DB->insert_record($tablename, array('course' => 5)); 1687 1688 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2)); 1689 $this->assertInternalType('array', $records); 1690 1691 $this->assertCount(3, $records); 1692 $this->assertArrayHasKey(1, $records); 1693 $this->assertArrayNotHasKey(2, $records); 1694 $this->assertArrayHasKey(3, $records); 1695 $this->assertArrayHasKey(4, $records); 1696 $this->assertSame('3', $records[1]); 1697 $this->assertSame('3', $records[3]); 1698 $this->assertSame('5', $records[4]); 1699 1700 // Note: delegate limits testing to test_get_records_sql(). 1701 } 1702 1703 public function test_get_record() { 1704 $DB = $this->tdb; 1705 $dbman = $DB->get_manager(); 1706 1707 $table = $this->get_test_table(); 1708 $tablename = $table->getName(); 1709 1710 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1711 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1712 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1713 $dbman->create_table($table); 1714 1715 $DB->insert_record($tablename, array('course' => 3)); 1716 $DB->insert_record($tablename, array('course' => 2)); 1717 1718 $record = $DB->get_record($tablename, array('id' => 2)); 1719 $this->assertInstanceOf('stdClass', $record); 1720 1721 $this->assertEquals(2, $record->course); 1722 $this->assertEquals(2, $record->id); 1723 } 1724 1725 1726 public function test_get_record_select() { 1727 $DB = $this->tdb; 1728 $dbman = $DB->get_manager(); 1729 1730 $table = $this->get_test_table(); 1731 $tablename = $table->getName(); 1732 1733 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1734 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1735 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1736 $dbman->create_table($table); 1737 1738 $DB->insert_record($tablename, array('course' => 3)); 1739 $DB->insert_record($tablename, array('course' => 2)); 1740 1741 $record = $DB->get_record_select($tablename, "id = ?", array(2)); 1742 $this->assertInstanceOf('stdClass', $record); 1743 1744 $this->assertEquals(2, $record->course); 1745 1746 // Note: delegates limit testing to test_get_records_sql(). 1747 } 1748 1749 public function test_get_record_sql() { 1750 $DB = $this->tdb; 1751 $dbman = $DB->get_manager(); 1752 1753 $table = $this->get_test_table(); 1754 $tablename = $table->getName(); 1755 1756 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1757 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1758 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1759 $dbman->create_table($table); 1760 1761 $DB->insert_record($tablename, array('course' => 3)); 1762 $DB->insert_record($tablename, array('course' => 2)); 1763 1764 // Standard use. 1765 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2)); 1766 $this->assertInstanceOf('stdClass', $record); 1767 $this->assertEquals(2, $record->course); 1768 $this->assertEquals(2, $record->id); 1769 1770 // Backwards compatibility with $ignoremultiple. 1771 $this->assertFalse((bool)IGNORE_MISSING); 1772 $this->assertTrue((bool)IGNORE_MULTIPLE); 1773 1774 // Record not found - ignore. 1775 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING)); 1776 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE)); 1777 1778 // Record not found error. 1779 try { 1780 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST); 1781 $this->fail("Exception expected"); 1782 } catch (dml_missing_record_exception $e) { 1783 $this->assertTrue(true); 1784 } 1785 1786 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING)); 1787 $this->assertDebuggingCalled(); 1788 set_debugging(DEBUG_MINIMAL); 1789 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING)); 1790 $this->assertDebuggingNotCalled(); 1791 set_debugging(DEBUG_DEVELOPER); 1792 1793 // Multiple matches ignored. 1794 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE)); 1795 1796 // Multiple found error. 1797 try { 1798 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST); 1799 $this->fail("Exception expected"); 1800 } catch (dml_multiple_records_exception $e) { 1801 $this->assertTrue(true); 1802 } 1803 } 1804 1805 public function test_get_field() { 1806 $DB = $this->tdb; 1807 $dbman = $DB->get_manager(); 1808 1809 $table = $this->get_test_table(); 1810 $tablename = $table->getName(); 1811 1812 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1813 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1814 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 1815 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1816 $dbman->create_table($table); 1817 1818 $id1 = $DB->insert_record($tablename, array('course' => 3)); 1819 $DB->insert_record($tablename, array('course' => 5)); 1820 $DB->insert_record($tablename, array('course' => 5)); 1821 1822 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1))); 1823 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3))); 1824 1825 $this->assertFalse($DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING)); 1826 try { 1827 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST); 1828 $this->fail('Exception expected due to missing record'); 1829 } catch (dml_exception $ex) { 1830 $this->assertTrue(true); 1831 } 1832 1833 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE)); 1834 $this->assertDebuggingNotCalled(); 1835 1836 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING)); 1837 $this->assertDebuggingCalled(); 1838 1839 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 1840 $conditions = array('onetext' => '1'); 1841 try { 1842 $DB->get_field($tablename, 'course', $conditions); 1843 if (debugging()) { 1844 // Only in debug mode - hopefully all devs test code in debug mode... 1845 $this->fail('An Exception is missing, expected due to equating of text fields'); 1846 } 1847 } catch (moodle_exception $e) { 1848 $this->assertInstanceOf('dml_exception', $e); 1849 $this->assertSame('textconditionsnotallowed', $e->errorcode); 1850 } 1851 } 1852 1853 public function test_get_field_select() { 1854 $DB = $this->tdb; 1855 $dbman = $DB->get_manager(); 1856 1857 $table = $this->get_test_table(); 1858 $tablename = $table->getName(); 1859 1860 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1861 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1862 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1863 $dbman->create_table($table); 1864 1865 $DB->insert_record($tablename, array('course' => 3)); 1866 1867 $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1))); 1868 } 1869 1870 public function test_get_field_sql() { 1871 $DB = $this->tdb; 1872 $dbman = $DB->get_manager(); 1873 1874 $table = $this->get_test_table(); 1875 $tablename = $table->getName(); 1876 1877 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1878 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1879 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1880 $dbman->create_table($table); 1881 1882 $DB->insert_record($tablename, array('course' => 3)); 1883 1884 $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1))); 1885 } 1886 1887 public function test_get_fieldset_select() { 1888 $DB = $this->tdb; 1889 $dbman = $DB->get_manager(); 1890 1891 $table = $this->get_test_table(); 1892 $tablename = $table->getName(); 1893 1894 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1895 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1896 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1897 $dbman->create_table($table); 1898 1899 $DB->insert_record($tablename, array('course' => 1)); 1900 $DB->insert_record($tablename, array('course' => 3)); 1901 $DB->insert_record($tablename, array('course' => 2)); 1902 $DB->insert_record($tablename, array('course' => 6)); 1903 1904 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)); 1905 $this->assertInternalType('array', $fieldset); 1906 1907 $this->assertCount(3, $fieldset); 1908 $this->assertEquals(3, $fieldset[0]); 1909 $this->assertEquals(2, $fieldset[1]); 1910 $this->assertEquals(6, $fieldset[2]); 1911 } 1912 1913 public function test_get_fieldset_sql() { 1914 $DB = $this->tdb; 1915 $dbman = $DB->get_manager(); 1916 1917 $table = $this->get_test_table(); 1918 $tablename = $table->getName(); 1919 1920 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1921 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1922 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 1923 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1924 $dbman->create_table($table); 1925 1926 $binarydata = '\\'.chr(241); 1927 1928 $DB->insert_record($tablename, array('course' => 1, 'onebinary' => $binarydata)); 1929 $DB->insert_record($tablename, array('course' => 3, 'onebinary' => $binarydata)); 1930 $DB->insert_record($tablename, array('course' => 2, 'onebinary' => $binarydata)); 1931 $DB->insert_record($tablename, array('course' => 6, 'onebinary' => $binarydata)); 1932 1933 $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1)); 1934 $this->assertInternalType('array', $fieldset); 1935 1936 $this->assertCount(3, $fieldset); 1937 $this->assertEquals(2, $fieldset[0]); 1938 $this->assertEquals(3, $fieldset[1]); 1939 $this->assertEquals(4, $fieldset[2]); 1940 1941 $fieldset = $DB->get_fieldset_sql("SELECT onebinary FROM {{$tablename}} WHERE course > ?", array(1)); 1942 $this->assertInternalType('array', $fieldset); 1943 1944 $this->assertCount(3, $fieldset); 1945 $this->assertEquals($binarydata, $fieldset[0]); 1946 $this->assertEquals($binarydata, $fieldset[1]); 1947 $this->assertEquals($binarydata, $fieldset[2]); 1948 } 1949 1950 public function test_insert_record_raw() { 1951 $DB = $this->tdb; 1952 $dbman = $DB->get_manager(); 1953 1954 $table = $this->get_test_table(); 1955 $tablename = $table->getName(); 1956 1957 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 1958 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 1959 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 1960 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 1961 $dbman->create_table($table); 1962 1963 $record = (object)array('course' => 1, 'onechar' => 'xx'); 1964 $before = clone($record); 1965 $result = $DB->insert_record_raw($tablename, $record); 1966 $this->assertSame(1, $result); 1967 $this->assertEquals($record, $before); 1968 1969 $record = $DB->get_record($tablename, array('course' => 1)); 1970 $this->assertInstanceOf('stdClass', $record); 1971 $this->assertSame('xx', $record->onechar); 1972 1973 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false); 1974 $this->assertTrue($result); 1975 1976 // Note: bulk not implemented yet. 1977 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true); 1978 $record = $DB->get_record($tablename, array('course' => 3)); 1979 $this->assertInstanceOf('stdClass', $record); 1980 $this->assertSame('zz', $record->onechar); 1981 1982 // Custom sequence (id) - returnid is ignored. 1983 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true); 1984 $this->assertTrue($result); 1985 $record = $DB->get_record($tablename, array('id' => 10)); 1986 $this->assertInstanceOf('stdClass', $record); 1987 $this->assertSame('bb', $record->onechar); 1988 1989 // Custom sequence - missing id error. 1990 try { 1991 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true); 1992 $this->fail('Exception expected due to missing record'); 1993 } catch (coding_exception $ex) { 1994 $this->assertTrue(true); 1995 } 1996 1997 // Wrong column error. 1998 try { 1999 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb')); 2000 $this->fail('Exception expected due to invalid column'); 2001 } catch (dml_exception $ex) { 2002 $this->assertTrue(true); 2003 } 2004 2005 // Create something similar to "context_temp" with id column without sequence. 2006 $dbman->drop_table($table); 2007 $table = $this->get_test_table(); 2008 $tablename = $table->getName(); 2009 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); 2010 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2011 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2012 $dbman->create_table($table); 2013 2014 $record = (object)array('id'=>5, 'course' => 1); 2015 $DB->insert_record_raw($tablename, $record, false, false, true); 2016 $record = $DB->get_record($tablename, array()); 2017 $this->assertEquals(5, $record->id); 2018 } 2019 2020 public function test_insert_record() { 2021 // All the information in this test is fetched from DB by get_recordset() so we 2022 // have such method properly tested against nulls, empties and friends... 2023 2024 $DB = $this->tdb; 2025 $dbman = $DB->get_manager(); 2026 2027 $table = $this->get_test_table(); 2028 $tablename = $table->getName(); 2029 2030 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2031 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2032 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2033 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2034 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2035 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2036 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2037 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2038 $dbman->create_table($table); 2039 2040 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true)); 2041 $record = $DB->get_record($tablename, array('course' => 1)); 2042 $this->assertEquals(1, $record->id); 2043 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied. 2044 $this->assertEquals(200, $record->onenum); 2045 $this->assertSame('onestring', $record->onechar); 2046 $this->assertNull($record->onetext); 2047 $this->assertNull($record->onebinary); 2048 2049 // Without returning id, bulk not implemented. 2050 $result = $this->assertTrue($DB->insert_record($tablename, array('course' => 99), false, true)); 2051 $record = $DB->get_record($tablename, array('course' => 99)); 2052 $this->assertEquals(2, $record->id); 2053 $this->assertEquals(99, $record->course); 2054 2055 // Check nulls are set properly for all types. 2056 $record = new stdClass(); 2057 $record->oneint = null; 2058 $record->onenum = null; 2059 $record->onechar = null; 2060 $record->onetext = null; 2061 $record->onebinary = null; 2062 $recid = $DB->insert_record($tablename, $record); 2063 $record = $DB->get_record($tablename, array('id' => $recid)); 2064 $this->assertEquals(0, $record->course); 2065 $this->assertNull($record->oneint); 2066 $this->assertNull($record->onenum); 2067 $this->assertNull($record->onechar); 2068 $this->assertNull($record->onetext); 2069 $this->assertNull($record->onebinary); 2070 2071 // Check zeros are set properly for all types. 2072 $record = new stdClass(); 2073 $record->oneint = 0; 2074 $record->onenum = 0; 2075 $recid = $DB->insert_record($tablename, $record); 2076 $record = $DB->get_record($tablename, array('id' => $recid)); 2077 $this->assertEquals(0, $record->oneint); 2078 $this->assertEquals(0, $record->onenum); 2079 2080 // Check booleans are set properly for all types. 2081 $record = new stdClass(); 2082 $record->oneint = true; // Trues. 2083 $record->onenum = true; 2084 $record->onechar = true; 2085 $record->onetext = true; 2086 $recid = $DB->insert_record($tablename, $record); 2087 $record = $DB->get_record($tablename, array('id' => $recid)); 2088 $this->assertEquals(1, $record->oneint); 2089 $this->assertEquals(1, $record->onenum); 2090 $this->assertEquals(1, $record->onechar); 2091 $this->assertEquals(1, $record->onetext); 2092 2093 $record = new stdClass(); 2094 $record->oneint = false; // Falses. 2095 $record->onenum = false; 2096 $record->onechar = false; 2097 $record->onetext = false; 2098 $recid = $DB->insert_record($tablename, $record); 2099 $record = $DB->get_record($tablename, array('id' => $recid)); 2100 $this->assertEquals(0, $record->oneint); 2101 $this->assertEquals(0, $record->onenum); 2102 $this->assertEquals(0, $record->onechar); 2103 $this->assertEquals(0, $record->onetext); 2104 2105 // Check string data causes exception in numeric types. 2106 $record = new stdClass(); 2107 $record->oneint = 'onestring'; 2108 $record->onenum = 0; 2109 try { 2110 $DB->insert_record($tablename, $record); 2111 $this->fail("Expecting an exception, none occurred"); 2112 } catch (moodle_exception $e) { 2113 $this->assertInstanceOf('dml_exception', $e); 2114 } 2115 $record = new stdClass(); 2116 $record->oneint = 0; 2117 $record->onenum = 'onestring'; 2118 try { 2119 $DB->insert_record($tablename, $record); 2120 $this->fail("Expecting an exception, none occurred"); 2121 } catch (moodle_exception $e) { 2122 $this->assertInstanceOf('dml_exception', $e); 2123 } 2124 2125 // Check empty string data is stored as 0 in numeric datatypes. 2126 $record = new stdClass(); 2127 $record->oneint = ''; // Empty string. 2128 $record->onenum = 0; 2129 $recid = $DB->insert_record($tablename, $record); 2130 $record = $DB->get_record($tablename, array('id' => $recid)); 2131 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0); 2132 2133 $record = new stdClass(); 2134 $record->oneint = 0; 2135 $record->onenum = ''; // Empty string. 2136 $recid = $DB->insert_record($tablename, $record); 2137 $record = $DB->get_record($tablename, array('id' => $recid)); 2138 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0); 2139 2140 // Check empty strings are set properly in string types. 2141 $record = new stdClass(); 2142 $record->oneint = 0; 2143 $record->onenum = 0; 2144 $record->onechar = ''; 2145 $record->onetext = ''; 2146 $recid = $DB->insert_record($tablename, $record); 2147 $record = $DB->get_record($tablename, array('id' => $recid)); 2148 $this->assertTrue($record->onechar === ''); 2149 $this->assertTrue($record->onetext === ''); 2150 2151 // Check operation ((210.10 + 39.92) - 150.02) against numeric types. 2152 $record = new stdClass(); 2153 $record->oneint = ((210.10 + 39.92) - 150.02); 2154 $record->onenum = ((210.10 + 39.92) - 150.02); 2155 $recid = $DB->insert_record($tablename, $record); 2156 $record = $DB->get_record($tablename, array('id' => $recid)); 2157 $this->assertEquals(100, $record->oneint); 2158 $this->assertEquals(100, $record->onenum); 2159 2160 // Check various quotes/backslashes combinations in string types. 2161 $teststrings = array( 2162 'backslashes and quotes alone (even): "" \'\' \\\\', 2163 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', 2164 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', 2165 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); 2166 foreach ($teststrings as $teststring) { 2167 $record = new stdClass(); 2168 $record->onechar = $teststring; 2169 $record->onetext = $teststring; 2170 $recid = $DB->insert_record($tablename, $record); 2171 $record = $DB->get_record($tablename, array('id' => $recid)); 2172 $this->assertEquals($teststring, $record->onechar); 2173 $this->assertEquals($teststring, $record->onetext); 2174 } 2175 2176 // Check LOBs in text/binary columns. 2177 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 2178 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary'); 2179 $record = new stdClass(); 2180 $record->onetext = $clob; 2181 $record->onebinary = $blob; 2182 $recid = $DB->insert_record($tablename, $record); 2183 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2184 $record = $rs->current(); 2185 $rs->close(); 2186 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)'); 2187 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)'); 2188 2189 // And "small" LOBs too, just in case. 2190 $newclob = substr($clob, 0, 500); 2191 $newblob = substr($blob, 0, 250); 2192 $record = new stdClass(); 2193 $record->onetext = $newclob; 2194 $record->onebinary = $newblob; 2195 $recid = $DB->insert_record($tablename, $record); 2196 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2197 $record = $rs->current(); 2198 $rs->close(); 2199 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)'); 2200 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)'); 2201 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing. 2202 2203 // And "diagnostic" LOBs too, just in case. 2204 $newclob = '\'"\\;/ěščřžýáíé'; 2205 $newblob = '\'"\\;/ěščřžýáíé'; 2206 $record = new stdClass(); 2207 $record->onetext = $newclob; 2208 $record->onebinary = $newblob; 2209 $recid = $DB->insert_record($tablename, $record); 2210 $rs = $DB->get_recordset($tablename, array('id' => $recid)); 2211 $record = $rs->current(); 2212 $rs->close(); 2213 $this->assertSame($newclob, $record->onetext); 2214 $this->assertSame($newblob, $record->onebinary); 2215 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing. 2216 2217 // Test data is not modified. 2218 $record = new stdClass(); 2219 $record->id = -1; // Has to be ignored. 2220 $record->course = 3; 2221 $record->lalala = 'lalal'; // Unused. 2222 $before = clone($record); 2223 $DB->insert_record($tablename, $record); 2224 $this->assertEquals($record, $before); 2225 2226 // Make sure the id is always increasing and never reuses the same id. 2227 $id1 = $DB->insert_record($tablename, array('course' => 3)); 2228 $id2 = $DB->insert_record($tablename, array('course' => 3)); 2229 $this->assertTrue($id1 < $id2); 2230 $DB->delete_records($tablename, array('id'=>$id2)); 2231 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2232 $this->assertTrue($id2 < $id3); 2233 $DB->delete_records($tablename, array()); 2234 $id4 = $DB->insert_record($tablename, array('course' => 3)); 2235 $this->assertTrue($id3 < $id4); 2236 2237 // Test saving a float in a CHAR column, and reading it back. 2238 $id = $DB->insert_record($tablename, array('onechar' => 1.0)); 2239 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2240 $id = $DB->insert_record($tablename, array('onechar' => 1e20)); 2241 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2242 $id = $DB->insert_record($tablename, array('onechar' => 1e-4)); 2243 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2244 $id = $DB->insert_record($tablename, array('onechar' => 1e-5)); 2245 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2246 $id = $DB->insert_record($tablename, array('onechar' => 1e-300)); 2247 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2248 $id = $DB->insert_record($tablename, array('onechar' => 1e300)); 2249 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2250 2251 // Test saving a float in a TEXT column, and reading it back. 2252 $id = $DB->insert_record($tablename, array('onetext' => 1.0)); 2253 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2254 $id = $DB->insert_record($tablename, array('onetext' => 1e20)); 2255 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2256 $id = $DB->insert_record($tablename, array('onetext' => 1e-4)); 2257 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2258 $id = $DB->insert_record($tablename, array('onetext' => 1e-5)); 2259 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2260 $id = $DB->insert_record($tablename, array('onetext' => 1e-300)); 2261 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2262 $id = $DB->insert_record($tablename, array('onetext' => 1e300)); 2263 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2264 2265 // Test that inserting data violating one unique key leads to error. 2266 // Empty the table completely. 2267 $this->assertTrue($DB->delete_records($tablename)); 2268 2269 // Add one unique constraint (index). 2270 $key = new xmldb_key('testuk', XMLDB_KEY_UNIQUE, array('course', 'oneint')); 2271 $dbman->add_key($table, $key); 2272 2273 // Let's insert one record violating the constraint multiple times. 2274 $record = (object)array('course' => 1, 'oneint' => 1); 2275 $this->assertTrue($DB->insert_record($tablename, $record, false)); // Insert 1st. No problem expected. 2276 2277 // Re-insert same record, not returning id. dml_exception expected. 2278 try { 2279 $DB->insert_record($tablename, $record, false); 2280 $this->fail("Expecting an exception, none occurred"); 2281 } catch (moodle_exception $e) { 2282 $this->assertInstanceOf('dml_exception', $e); 2283 } 2284 2285 // Re-insert same record, returning id. dml_exception expected. 2286 try { 2287 $DB->insert_record($tablename, $record, true); 2288 $this->fail("Expecting an exception, none occurred"); 2289 } catch (moodle_exception $e) { 2290 $this->assertInstanceOf('dml_exception', $e); 2291 } 2292 2293 // Try to insert a record into a non-existent table. dml_exception expected. 2294 try { 2295 $DB->insert_record('nonexistenttable', $record, true); 2296 $this->fail("Expecting an exception, none occurred"); 2297 } catch (exception $e) { 2298 $this->assertTrue($e instanceof dml_exception); 2299 } 2300 } 2301 2302 public function test_insert_records() { 2303 $DB = $this->tdb; 2304 $dbman = $DB->get_manager(); 2305 2306 $table = $this->get_test_table(); 2307 $tablename = $table->getName(); 2308 2309 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2310 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2311 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2312 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2313 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2314 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2315 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2316 $dbman->create_table($table); 2317 2318 $this->assertCount(0, $DB->get_records($tablename)); 2319 2320 $record = new stdClass(); 2321 $record->id = '1'; 2322 $record->course = '1'; 2323 $record->oneint = null; 2324 $record->onenum = '1.00'; 2325 $record->onechar = 'a'; 2326 $record->onetext = 'aaa'; 2327 2328 $expected = array(); 2329 $records = array(); 2330 for ($i = 1; $i <= 2000; $i++) { // This may take a while, it should be higher than defaults in DML drivers. 2331 $rec = clone($record); 2332 $rec->id = (string)$i; 2333 $rec->oneint = (string)$i; 2334 $expected[$i] = $rec; 2335 $rec = clone($rec); 2336 unset($rec->id); 2337 $records[$i] = $rec; 2338 } 2339 2340 $DB->insert_records($tablename, $records); 2341 $stored = $DB->get_records($tablename, array(), 'id ASC'); 2342 $this->assertEquals($expected, $stored); 2343 2344 // Test there can be some extra properties including id. 2345 $count = $DB->count_records($tablename); 2346 $rec1 = (array)$record; 2347 $rec1['xxx'] = 1; 2348 $rec2 = (array)$record; 2349 $rec2['xxx'] = 2; 2350 2351 $records = array($rec1, $rec2); 2352 $DB->insert_records($tablename, $records); 2353 $this->assertEquals($count + 2, $DB->count_records($tablename)); 2354 2355 // Test not all properties are necessary. 2356 $rec1 = (array)$record; 2357 unset($rec1['course']); 2358 $rec2 = (array)$record; 2359 unset($rec2['course']); 2360 2361 $records = array($rec1, $rec2); 2362 $DB->insert_records($tablename, $records); 2363 2364 // Make sure no changes in data object structure are tolerated. 2365 $rec1 = (array)$record; 2366 unset($rec1['id']); 2367 $rec2 = (array)$record; 2368 unset($rec2['id']); 2369 2370 $records = array($rec1, $rec2); 2371 $DB->insert_records($tablename, $records); 2372 2373 $rec2['xx'] = '1'; 2374 $records = array($rec1, $rec2); 2375 try { 2376 $DB->insert_records($tablename, $records); 2377 $this->fail('coding_exception expected when insert_records receives different object data structures'); 2378 } catch (moodle_exception $e) { 2379 $this->assertInstanceOf('coding_exception', $e); 2380 } 2381 2382 unset($rec2['xx']); 2383 unset($rec2['course']); 2384 $rec2['course'] = '1'; 2385 $records = array($rec1, $rec2); 2386 try { 2387 $DB->insert_records($tablename, $records); 2388 $this->fail('coding_exception expected when insert_records receives different object data structures'); 2389 } catch (moodle_exception $e) { 2390 $this->assertInstanceOf('coding_exception', $e); 2391 } 2392 2393 $records = 1; 2394 try { 2395 $DB->insert_records($tablename, $records); 2396 $this->fail('coding_exception expected when insert_records receives non-traversable data'); 2397 } catch (moodle_exception $e) { 2398 $this->assertInstanceOf('coding_exception', $e); 2399 } 2400 2401 $records = array(1); 2402 try { 2403 $DB->insert_records($tablename, $records); 2404 $this->fail('coding_exception expected when insert_records receives non-objet record'); 2405 } catch (moodle_exception $e) { 2406 $this->assertInstanceOf('coding_exception', $e); 2407 } 2408 } 2409 2410 public function test_import_record() { 2411 // All the information in this test is fetched from DB by get_recordset() so we 2412 // have such method properly tested against nulls, empties and friends... 2413 2414 $DB = $this->tdb; 2415 $dbman = $DB->get_manager(); 2416 2417 $table = $this->get_test_table(); 2418 $tablename = $table->getName(); 2419 2420 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2421 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2422 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2423 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2424 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2425 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2426 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2427 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2428 $dbman->create_table($table); 2429 2430 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true)); 2431 $record = $DB->get_record($tablename, array('course' => 1)); 2432 $this->assertEquals(1, $record->id); 2433 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied. 2434 $this->assertEquals(200, $record->onenum); 2435 $this->assertSame('onestring', $record->onechar); 2436 $this->assertNull($record->onetext); 2437 $this->assertNull($record->onebinary); 2438 2439 // Ignore extra columns. 2440 $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778); 2441 $before = clone($record); 2442 $this->assertTrue($DB->import_record($tablename, $record)); 2443 $this->assertEquals($record, $before); 2444 $records = $DB->get_records($tablename); 2445 $this->assertEquals(2, $records[13]->course); 2446 2447 // Check nulls are set properly for all types. 2448 $record = new stdClass(); 2449 $record->id = 20; 2450 $record->oneint = null; 2451 $record->onenum = null; 2452 $record->onechar = null; 2453 $record->onetext = null; 2454 $record->onebinary = null; 2455 $this->assertTrue($DB->import_record($tablename, $record)); 2456 $record = $DB->get_record($tablename, array('id' => 20)); 2457 $this->assertEquals(0, $record->course); 2458 $this->assertNull($record->oneint); 2459 $this->assertNull($record->onenum); 2460 $this->assertNull($record->onechar); 2461 $this->assertNull($record->onetext); 2462 $this->assertNull($record->onebinary); 2463 2464 // Check zeros are set properly for all types. 2465 $record = new stdClass(); 2466 $record->id = 23; 2467 $record->oneint = 0; 2468 $record->onenum = 0; 2469 $this->assertTrue($DB->import_record($tablename, $record)); 2470 $record = $DB->get_record($tablename, array('id' => 23)); 2471 $this->assertEquals(0, $record->oneint); 2472 $this->assertEquals(0, $record->onenum); 2473 2474 // Check string data causes exception in numeric types. 2475 $record = new stdClass(); 2476 $record->id = 32; 2477 $record->oneint = 'onestring'; 2478 $record->onenum = 0; 2479 try { 2480 $DB->import_record($tablename, $record); 2481 $this->fail("Expecting an exception, none occurred"); 2482 } catch (moodle_exception $e) { 2483 $this->assertInstanceOf('dml_exception', $e); 2484 } 2485 $record = new stdClass(); 2486 $record->id = 35; 2487 $record->oneint = 0; 2488 $record->onenum = 'onestring'; 2489 try { 2490 $DB->import_record($tablename, $record); 2491 $this->fail("Expecting an exception, none occurred"); 2492 } catch (moodle_exception $e) { 2493 $this->assertInstanceOf('dml_exception', $e); 2494 } 2495 2496 // Check empty strings are set properly in string types. 2497 $record = new stdClass(); 2498 $record->id = 44; 2499 $record->oneint = 0; 2500 $record->onenum = 0; 2501 $record->onechar = ''; 2502 $record->onetext = ''; 2503 $this->assertTrue($DB->import_record($tablename, $record)); 2504 $record = $DB->get_record($tablename, array('id' => 44)); 2505 $this->assertTrue($record->onechar === ''); 2506 $this->assertTrue($record->onetext === ''); 2507 2508 // Check operation ((210.10 + 39.92) - 150.02) against numeric types. 2509 $record = new stdClass(); 2510 $record->id = 47; 2511 $record->oneint = ((210.10 + 39.92) - 150.02); 2512 $record->onenum = ((210.10 + 39.92) - 150.02); 2513 $this->assertTrue($DB->import_record($tablename, $record)); 2514 $record = $DB->get_record($tablename, array('id' => 47)); 2515 $this->assertEquals(100, $record->oneint); 2516 $this->assertEquals(100, $record->onenum); 2517 2518 // Check various quotes/backslashes combinations in string types. 2519 $i = 50; 2520 $teststrings = array( 2521 'backslashes and quotes alone (even): "" \'\' \\\\', 2522 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', 2523 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', 2524 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); 2525 foreach ($teststrings as $teststring) { 2526 $record = new stdClass(); 2527 $record->id = $i; 2528 $record->onechar = $teststring; 2529 $record->onetext = $teststring; 2530 $this->assertTrue($DB->import_record($tablename, $record)); 2531 $record = $DB->get_record($tablename, array('id' => $i)); 2532 $this->assertEquals($teststring, $record->onechar); 2533 $this->assertEquals($teststring, $record->onetext); 2534 $i = $i + 3; 2535 } 2536 2537 // Check LOBs in text/binary columns. 2538 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 2539 $record = new stdClass(); 2540 $record->id = 70; 2541 $record->onetext = $clob; 2542 $record->onebinary = ''; 2543 $this->assertTrue($DB->import_record($tablename, $record)); 2544 $rs = $DB->get_recordset($tablename, array('id' => 70)); 2545 $record = $rs->current(); 2546 $rs->close(); 2547 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)'); 2548 2549 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary'); 2550 $record = new stdClass(); 2551 $record->id = 71; 2552 $record->onetext = ''; 2553 $record->onebinary = $blob; 2554 $this->assertTrue($DB->import_record($tablename, $record)); 2555 $rs = $DB->get_recordset($tablename, array('id' => 71)); 2556 $record = $rs->current(); 2557 $rs->close(); 2558 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)'); 2559 2560 // And "small" LOBs too, just in case. 2561 $newclob = substr($clob, 0, 500); 2562 $newblob = substr($blob, 0, 250); 2563 $record = new stdClass(); 2564 $record->id = 73; 2565 $record->onetext = $newclob; 2566 $record->onebinary = $newblob; 2567 $this->assertTrue($DB->import_record($tablename, $record)); 2568 $rs = $DB->get_recordset($tablename, array('id' => 73)); 2569 $record = $rs->current(); 2570 $rs->close(); 2571 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)'); 2572 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)'); 2573 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing. 2574 } 2575 2576 public function test_update_record_raw() { 2577 $DB = $this->tdb; 2578 $dbman = $DB->get_manager(); 2579 2580 $table = $this->get_test_table(); 2581 $tablename = $table->getName(); 2582 2583 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2584 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2585 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2586 $dbman->create_table($table); 2587 2588 $DB->insert_record($tablename, array('course' => 1)); 2589 $DB->insert_record($tablename, array('course' => 3)); 2590 2591 $record = $DB->get_record($tablename, array('course' => 1)); 2592 $record->course = 2; 2593 $this->assertTrue($DB->update_record_raw($tablename, $record)); 2594 $this->assertEquals(0, $DB->count_records($tablename, array('course' => 1))); 2595 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 2))); 2596 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 3))); 2597 2598 $record = $DB->get_record($tablename, array('course' => 3)); 2599 $record->xxxxx = 2; 2600 try { 2601 $DB->update_record_raw($tablename, $record); 2602 $this->fail("Expecting an exception, none occurred"); 2603 } catch (moodle_exception $e) { 2604 $this->assertInstanceOf('moodle_exception', $e); 2605 } 2606 2607 $record = $DB->get_record($tablename, array('course' => 3)); 2608 unset($record->id); 2609 try { 2610 $DB->update_record_raw($tablename, $record); 2611 $this->fail("Expecting an exception, none occurred"); 2612 } catch (moodle_exception $e) { 2613 $this->assertInstanceOf('coding_exception', $e); 2614 } 2615 } 2616 2617 public function test_update_record() { 2618 2619 // All the information in this test is fetched from DB by get_record() so we 2620 // have such method properly tested against nulls, empties and friends... 2621 2622 $DB = $this->tdb; 2623 $dbman = $DB->get_manager(); 2624 2625 $table = $this->get_test_table(); 2626 $tablename = $table->getName(); 2627 2628 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2629 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2630 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100); 2631 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); 2632 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); 2633 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2634 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2635 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2636 $dbman->create_table($table); 2637 2638 $DB->insert_record($tablename, array('course' => 1)); 2639 $record = $DB->get_record($tablename, array('course' => 1)); 2640 $record->course = 2; 2641 2642 $this->assertTrue($DB->update_record($tablename, $record)); 2643 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1))); 2644 $this->assertNotEmpty($record = $DB->get_record($tablename, array('course' => 2))); 2645 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied. 2646 $this->assertEquals(200, $record->onenum); 2647 $this->assertSame('onestring', $record->onechar); 2648 $this->assertNull($record->onetext); 2649 $this->assertNull($record->onebinary); 2650 2651 // Check nulls are set properly for all types. 2652 $record->oneint = null; 2653 $record->onenum = null; 2654 $record->onechar = null; 2655 $record->onetext = null; 2656 $record->onebinary = null; 2657 $DB->update_record($tablename, $record); 2658 $record = $DB->get_record($tablename, array('course' => 2)); 2659 $this->assertNull($record->oneint); 2660 $this->assertNull($record->onenum); 2661 $this->assertNull($record->onechar); 2662 $this->assertNull($record->onetext); 2663 $this->assertNull($record->onebinary); 2664 2665 // Check zeros are set properly for all types. 2666 $record->oneint = 0; 2667 $record->onenum = 0; 2668 $DB->update_record($tablename, $record); 2669 $record = $DB->get_record($tablename, array('course' => 2)); 2670 $this->assertEquals(0, $record->oneint); 2671 $this->assertEquals(0, $record->onenum); 2672 2673 // Check booleans are set properly for all types. 2674 $record->oneint = true; // Trues. 2675 $record->onenum = true; 2676 $record->onechar = true; 2677 $record->onetext = true; 2678 $DB->update_record($tablename, $record); 2679 $record = $DB->get_record($tablename, array('course' => 2)); 2680 $this->assertEquals(1, $record->oneint); 2681 $this->assertEquals(1, $record->onenum); 2682 $this->assertEquals(1, $record->onechar); 2683 $this->assertEquals(1, $record->onetext); 2684 2685 $record->oneint = false; // Falses. 2686 $record->onenum = false; 2687 $record->onechar = false; 2688 $record->onetext = false; 2689 $DB->update_record($tablename, $record); 2690 $record = $DB->get_record($tablename, array('course' => 2)); 2691 $this->assertEquals(0, $record->oneint); 2692 $this->assertEquals(0, $record->onenum); 2693 $this->assertEquals(0, $record->onechar); 2694 $this->assertEquals(0, $record->onetext); 2695 2696 // Check string data causes exception in numeric types. 2697 $record->oneint = 'onestring'; 2698 $record->onenum = 0; 2699 try { 2700 $DB->update_record($tablename, $record); 2701 $this->fail("Expecting an exception, none occurred"); 2702 } catch (moodle_exception $e) { 2703 $this->assertInstanceOf('dml_exception', $e); 2704 } 2705 $record->oneint = 0; 2706 $record->onenum = 'onestring'; 2707 try { 2708 $DB->update_record($tablename, $record); 2709 $this->fail("Expecting an exception, none occurred"); 2710 } catch (moodle_exception $e) { 2711 $this->assertInstanceOf('dml_exception', $e); 2712 } 2713 2714 // Check empty string data is stored as 0 in numeric datatypes. 2715 $record->oneint = ''; // Empty string. 2716 $record->onenum = 0; 2717 $DB->update_record($tablename, $record); 2718 $record = $DB->get_record($tablename, array('course' => 2)); 2719 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0); 2720 2721 $record->oneint = 0; 2722 $record->onenum = ''; // Empty string. 2723 $DB->update_record($tablename, $record); 2724 $record = $DB->get_record($tablename, array('course' => 2)); 2725 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0); 2726 2727 // Check empty strings are set properly in string types. 2728 $record->oneint = 0; 2729 $record->onenum = 0; 2730 $record->onechar = ''; 2731 $record->onetext = ''; 2732 $DB->update_record($tablename, $record); 2733 $record = $DB->get_record($tablename, array('course' => 2)); 2734 $this->assertTrue($record->onechar === ''); 2735 $this->assertTrue($record->onetext === ''); 2736 2737 // Check operation ((210.10 + 39.92) - 150.02) against numeric types. 2738 $record->oneint = ((210.10 + 39.92) - 150.02); 2739 $record->onenum = ((210.10 + 39.92) - 150.02); 2740 $DB->update_record($tablename, $record); 2741 $record = $DB->get_record($tablename, array('course' => 2)); 2742 $this->assertEquals(100, $record->oneint); 2743 $this->assertEquals(100, $record->onenum); 2744 2745 // Check various quotes/backslashes combinations in string types. 2746 $teststrings = array( 2747 'backslashes and quotes alone (even): "" \'\' \\\\', 2748 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', 2749 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', 2750 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); 2751 foreach ($teststrings as $teststring) { 2752 $record->onechar = $teststring; 2753 $record->onetext = $teststring; 2754 $DB->update_record($tablename, $record); 2755 $record = $DB->get_record($tablename, array('course' => 2)); 2756 $this->assertEquals($teststring, $record->onechar); 2757 $this->assertEquals($teststring, $record->onetext); 2758 } 2759 2760 // Check LOBs in text/binary columns. 2761 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 2762 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary'); 2763 $record->onetext = $clob; 2764 $record->onebinary = $blob; 2765 $DB->update_record($tablename, $record); 2766 $record = $DB->get_record($tablename, array('course' => 2)); 2767 $this->assertEquals($clob, $record->onetext, 'Test CLOB update (full contents output disabled)'); 2768 $this->assertEquals($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)'); 2769 2770 // And "small" LOBs too, just in case. 2771 $newclob = substr($clob, 0, 500); 2772 $newblob = substr($blob, 0, 250); 2773 $record->onetext = $newclob; 2774 $record->onebinary = $newblob; 2775 $DB->update_record($tablename, $record); 2776 $record = $DB->get_record($tablename, array('course' => 2)); 2777 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)'); 2778 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)'); 2779 2780 // Test saving a float in a CHAR column, and reading it back. 2781 $id = $DB->insert_record($tablename, array('onechar' => 'X')); 2782 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1.0)); 2783 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2784 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e20)); 2785 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2786 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-4)); 2787 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2788 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-5)); 2789 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2790 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-300)); 2791 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2792 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e300)); 2793 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2794 2795 // Test saving a float in a TEXT column, and reading it back. 2796 $id = $DB->insert_record($tablename, array('onetext' => 'X')); 2797 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1.0)); 2798 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2799 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e20)); 2800 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2801 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-4)); 2802 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2803 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-5)); 2804 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2805 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-300)); 2806 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2807 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e300)); 2808 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2809 } 2810 2811 public function test_set_field() { 2812 $DB = $this->tdb; 2813 $dbman = $DB->get_manager(); 2814 2815 $table = $this->get_test_table(); 2816 $tablename = $table->getName(); 2817 2818 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2819 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2820 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null); 2821 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2822 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2823 $dbman->create_table($table); 2824 2825 // Simple set_field. 2826 $id1 = $DB->insert_record($tablename, array('course' => 1)); 2827 $id2 = $DB->insert_record($tablename, array('course' => 1)); 2828 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2829 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1))); 2830 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => $id1))); 2831 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2))); 2832 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3))); 2833 $DB->delete_records($tablename, array()); 2834 2835 // Multiple fields affected. 2836 $id1 = $DB->insert_record($tablename, array('course' => 1)); 2837 $id2 = $DB->insert_record($tablename, array('course' => 1)); 2838 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2839 $DB->set_field($tablename, 'course', '5', array('course' => 1)); 2840 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1))); 2841 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2))); 2842 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3))); 2843 $DB->delete_records($tablename, array()); 2844 2845 // No field affected. 2846 $id1 = $DB->insert_record($tablename, array('course' => 1)); 2847 $id2 = $DB->insert_record($tablename, array('course' => 1)); 2848 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2849 $DB->set_field($tablename, 'course', '5', array('course' => 0)); 2850 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id1))); 2851 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2))); 2852 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3))); 2853 $DB->delete_records($tablename, array()); 2854 2855 // All fields - no condition. 2856 $id1 = $DB->insert_record($tablename, array('course' => 1)); 2857 $id2 = $DB->insert_record($tablename, array('course' => 1)); 2858 $id3 = $DB->insert_record($tablename, array('course' => 3)); 2859 $DB->set_field($tablename, 'course', 5, array()); 2860 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1))); 2861 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2))); 2862 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id3))); 2863 2864 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 2865 $conditions = array('onetext' => '1'); 2866 try { 2867 $DB->set_field($tablename, 'onechar', 'frog', $conditions); 2868 if (debugging()) { 2869 // Only in debug mode - hopefully all devs test code in debug mode... 2870 $this->fail('An Exception is missing, expected due to equating of text fields'); 2871 } 2872 } catch (moodle_exception $e) { 2873 $this->assertInstanceOf('dml_exception', $e); 2874 $this->assertSame('textconditionsnotallowed', $e->errorcode); 2875 } 2876 2877 // Test saving a float in a CHAR column, and reading it back. 2878 $id = $DB->insert_record($tablename, array('onechar' => 'X')); 2879 $DB->set_field($tablename, 'onechar', 1.0, array('id' => $id)); 2880 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2881 $DB->set_field($tablename, 'onechar', 1e20, array('id' => $id)); 2882 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2883 $DB->set_field($tablename, 'onechar', 1e-4, array('id' => $id)); 2884 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2885 $DB->set_field($tablename, 'onechar', 1e-5, array('id' => $id)); 2886 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2887 $DB->set_field($tablename, 'onechar', 1e-300, array('id' => $id)); 2888 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2889 $DB->set_field($tablename, 'onechar', 1e300, array('id' => $id)); 2890 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id))); 2891 2892 // Test saving a float in a TEXT column, and reading it back. 2893 $id = $DB->insert_record($tablename, array('onetext' => 'X')); 2894 $DB->set_field($tablename, 'onetext', 1.0, array('id' => $id)); 2895 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2896 $DB->set_field($tablename, 'onetext', 1e20, array('id' => $id)); 2897 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2898 $DB->set_field($tablename, 'onetext', 1e-4, array('id' => $id)); 2899 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2900 $DB->set_field($tablename, 'onetext', 1e-5, array('id' => $id)); 2901 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2902 $DB->set_field($tablename, 'onetext', 1e-300, array('id' => $id)); 2903 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2904 $DB->set_field($tablename, 'onetext', 1e300, array('id' => $id)); 2905 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id))); 2906 2907 // Note: All the nulls, booleans, empties, quoted and backslashes tests 2908 // go to set_field_select() because set_field() is just one wrapper over it. 2909 } 2910 2911 public function test_set_field_select() { 2912 2913 // All the information in this test is fetched from DB by get_field() so we 2914 // have such method properly tested against nulls, empties and friends... 2915 2916 $DB = $this->tdb; 2917 $dbman = $DB->get_manager(); 2918 2919 $table = $this->get_test_table(); 2920 $tablename = $table->getName(); 2921 2922 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 2923 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 2924 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null); 2925 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null); 2926 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null); 2927 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 2928 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); 2929 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 2930 $dbman->create_table($table); 2931 2932 $DB->insert_record($tablename, array('course' => 1)); 2933 2934 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1))); 2935 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => 1))); 2936 2937 // Check nulls are set properly for all types. 2938 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // Trues. 2939 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1)); 2940 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1)); 2941 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1)); 2942 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1)); 2943 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1))); 2944 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1))); 2945 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1))); 2946 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1))); 2947 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1))); 2948 2949 // Check zeros are set properly for all types. 2950 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1)); 2951 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1)); 2952 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1))); 2953 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1))); 2954 2955 // Check booleans are set properly for all types. 2956 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // Trues. 2957 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1)); 2958 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1)); 2959 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1)); 2960 $this->assertEquals(1, $DB->get_field($tablename, 'oneint', array('id' => 1))); 2961 $this->assertEquals(1, $DB->get_field($tablename, 'onenum', array('id' => 1))); 2962 $this->assertEquals(1, $DB->get_field($tablename, 'onechar', array('id' => 1))); 2963 $this->assertEquals(1, $DB->get_field($tablename, 'onetext', array('id' => 1))); 2964 2965 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // Falses. 2966 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1)); 2967 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1)); 2968 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1)); 2969 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1))); 2970 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1))); 2971 $this->assertEquals(0, $DB->get_field($tablename, 'onechar', array('id' => 1))); 2972 $this->assertEquals(0, $DB->get_field($tablename, 'onetext', array('id' => 1))); 2973 2974 // Check string data causes exception in numeric types. 2975 try { 2976 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1)); 2977 $this->fail("Expecting an exception, none occurred"); 2978 } catch (moodle_exception $e) { 2979 $this->assertInstanceOf('dml_exception', $e); 2980 } 2981 try { 2982 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1)); 2983 $this->fail("Expecting an exception, none occurred"); 2984 } catch (moodle_exception $e) { 2985 $this->assertInstanceOf('dml_exception', $e); 2986 } 2987 2988 // Check empty string data is stored as 0 in numeric datatypes. 2989 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1)); 2990 $field = $DB->get_field($tablename, 'oneint', array('id' => 1)); 2991 $this->assertTrue(is_numeric($field) && $field == 0); 2992 2993 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1)); 2994 $field = $DB->get_field($tablename, 'onenum', array('id' => 1)); 2995 $this->assertTrue(is_numeric($field) && $field == 0); 2996 2997 // Check empty strings are set properly in string types. 2998 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1)); 2999 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1)); 3000 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === ''); 3001 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === ''); 3002 3003 // Check operation ((210.10 + 39.92) - 150.02) against numeric types. 3004 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1)); 3005 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1)); 3006 $this->assertEquals(100, $DB->get_field($tablename, 'oneint', array('id' => 1))); 3007 $this->assertEquals(100, $DB->get_field($tablename, 'onenum', array('id' => 1))); 3008 3009 // Check various quotes/backslashes combinations in string types. 3010 $teststrings = array( 3011 'backslashes and quotes alone (even): "" \'\' \\\\', 3012 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', 3013 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', 3014 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); 3015 foreach ($teststrings as $teststring) { 3016 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1)); 3017 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1)); 3018 $this->assertEquals($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1))); 3019 $this->assertEquals($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1))); 3020 } 3021 3022 // Check LOBs in text/binary columns. 3023 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 3024 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary'); 3025 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1)); 3026 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1)); 3027 $this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)'); 3028 $this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)'); 3029 3030 // Empty data in binary columns works. 3031 $DB->set_field_select($tablename, 'onebinary', '', 'id = ?', array(1)); 3032 $this->assertEquals('', $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Blobs need to accept empty values.'); 3033 3034 // And "small" LOBs too, just in case. 3035 $newclob = substr($clob, 0, 500); 3036 $newblob = substr($blob, 0, 250); 3037 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1)); 3038 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1)); 3039 $this->assertEquals($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)'); 3040 $this->assertEquals($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)'); 3041 3042 // This is the failure from MDL-24863. This was giving an error on MSSQL, 3043 // which converts the '1' to an integer, which cannot then be compared with 3044 // onetext cast to a varchar. This should be fixed and working now. 3045 $newchar = 'frog'; 3046 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 3047 $params = array('onetext' => '1'); 3048 try { 3049 $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params); 3050 $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.'); 3051 } catch (dml_exception $e) { 3052 $this->assertFalse(true, 'We have an unexpected exception.'); 3053 throw $e; 3054 } 3055 } 3056 3057 public function test_count_records() { 3058 $DB = $this->tdb; 3059 3060 $dbman = $DB->get_manager(); 3061 3062 $table = $this->get_test_table(); 3063 $tablename = $table->getName(); 3064 3065 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3066 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3067 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 3068 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3069 $dbman->create_table($table); 3070 3071 $this->assertSame(0, $DB->count_records($tablename)); 3072 3073 $DB->insert_record($tablename, array('course' => 3)); 3074 $DB->insert_record($tablename, array('course' => 4)); 3075 $DB->insert_record($tablename, array('course' => 5)); 3076 3077 $this->assertSame(3, $DB->count_records($tablename)); 3078 3079 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 3080 $conditions = array('onetext' => '1'); 3081 try { 3082 $DB->count_records($tablename, $conditions); 3083 if (debugging()) { 3084 // Only in debug mode - hopefully all devs test code in debug mode... 3085 $this->fail('An Exception is missing, expected due to equating of text fields'); 3086 } 3087 } catch (moodle_exception $e) { 3088 $this->assertInstanceOf('dml_exception', $e); 3089 $this->assertSame('textconditionsnotallowed', $e->errorcode); 3090 } 3091 } 3092 3093 public function test_count_records_select() { 3094 $DB = $this->tdb; 3095 3096 $dbman = $DB->get_manager(); 3097 3098 $table = $this->get_test_table(); 3099 $tablename = $table->getName(); 3100 3101 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3102 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3103 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3104 $dbman->create_table($table); 3105 3106 $this->assertSame(0, $DB->count_records($tablename)); 3107 3108 $DB->insert_record($tablename, array('course' => 3)); 3109 $DB->insert_record($tablename, array('course' => 4)); 3110 $DB->insert_record($tablename, array('course' => 5)); 3111 3112 $this->assertSame(2, $DB->count_records_select($tablename, 'course > ?', array(3))); 3113 } 3114 3115 public function test_count_records_sql() { 3116 $DB = $this->tdb; 3117 $dbman = $DB->get_manager(); 3118 3119 $table = $this->get_test_table(); 3120 $tablename = $table->getName(); 3121 3122 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3123 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3124 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null); 3125 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3126 $dbman->create_table($table); 3127 3128 $this->assertSame(0, $DB->count_records($tablename)); 3129 3130 $DB->insert_record($tablename, array('course' => 3, 'onechar' => 'a')); 3131 $DB->insert_record($tablename, array('course' => 4, 'onechar' => 'b')); 3132 $DB->insert_record($tablename, array('course' => 5, 'onechar' => 'c')); 3133 3134 $this->assertSame(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3))); 3135 3136 // Test invalid use. 3137 try { 3138 $DB->count_records_sql("SELECT onechar FROM {{$tablename}} WHERE course = ?", array(3)); 3139 $this->fail('Exception expected when non-number field used in count_records_sql'); 3140 } catch (moodle_exception $e) { 3141 $this->assertInstanceOf('coding_exception', $e); 3142 } 3143 3144 try { 3145 $DB->count_records_sql("SELECT course FROM {{$tablename}} WHERE 1 = 2"); 3146 $this->fail('Exception expected when non-number field used in count_records_sql'); 3147 } catch (moodle_exception $e) { 3148 $this->assertInstanceOf('coding_exception', $e); 3149 } 3150 } 3151 3152 public function test_record_exists() { 3153 $DB = $this->tdb; 3154 $dbman = $DB->get_manager(); 3155 3156 $table = $this->get_test_table(); 3157 $tablename = $table->getName(); 3158 3159 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3160 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3161 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 3162 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3163 $dbman->create_table($table); 3164 3165 $this->assertEquals(0, $DB->count_records($tablename)); 3166 3167 $this->assertFalse($DB->record_exists($tablename, array('course' => 3))); 3168 $DB->insert_record($tablename, array('course' => 3)); 3169 3170 $this->assertTrue($DB->record_exists($tablename, array('course' => 3))); 3171 3172 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 3173 $conditions = array('onetext' => '1'); 3174 try { 3175 $DB->record_exists($tablename, $conditions); 3176 if (debugging()) { 3177 // Only in debug mode - hopefully all devs test code in debug mode... 3178 $this->fail('An Exception is missing, expected due to equating of text fields'); 3179 } 3180 } catch (moodle_exception $e) { 3181 $this->assertInstanceOf('dml_exception', $e); 3182 $this->assertSame('textconditionsnotallowed', $e->errorcode); 3183 } 3184 } 3185 3186 public function test_record_exists_select() { 3187 $DB = $this->tdb; 3188 $dbman = $DB->get_manager(); 3189 3190 $table = $this->get_test_table(); 3191 $tablename = $table->getName(); 3192 3193 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3194 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3195 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3196 $dbman->create_table($table); 3197 3198 $this->assertEquals(0, $DB->count_records($tablename)); 3199 3200 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3))); 3201 $DB->insert_record($tablename, array('course' => 3)); 3202 3203 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3))); 3204 } 3205 3206 public function test_record_exists_sql() { 3207 $DB = $this->tdb; 3208 $dbman = $DB->get_manager(); 3209 3210 $table = $this->get_test_table(); 3211 $tablename = $table->getName(); 3212 3213 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3214 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3215 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3216 $dbman->create_table($table); 3217 3218 $this->assertEquals(0, $DB->count_records($tablename)); 3219 3220 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3))); 3221 $DB->insert_record($tablename, array('course' => 3)); 3222 3223 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3))); 3224 } 3225 3226 public function test_recordset_locks_delete() { 3227 $DB = $this->tdb; 3228 $dbman = $DB->get_manager(); 3229 3230 // Setup. 3231 $table = $this->get_test_table(); 3232 $tablename = $table->getName(); 3233 3234 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3235 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3236 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3237 $dbman->create_table($table); 3238 3239 $DB->insert_record($tablename, array('course' => 1)); 3240 $DB->insert_record($tablename, array('course' => 2)); 3241 $DB->insert_record($tablename, array('course' => 3)); 3242 $DB->insert_record($tablename, array('course' => 4)); 3243 $DB->insert_record($tablename, array('course' => 5)); 3244 $DB->insert_record($tablename, array('course' => 6)); 3245 3246 // Test against db write locking while on an open recordset. 3247 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}. 3248 foreach ($rs as $record) { 3249 $cid = $record->course; 3250 $DB->delete_records($tablename, array('course' => $cid)); 3251 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid))); 3252 } 3253 $rs->close(); 3254 3255 $this->assertEquals(4, $DB->count_records($tablename, array())); 3256 } 3257 3258 public function test_recordset_locks_update() { 3259 $DB = $this->tdb; 3260 $dbman = $DB->get_manager(); 3261 3262 // Setup. 3263 $table = $this->get_test_table(); 3264 $tablename = $table->getName(); 3265 3266 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3267 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3268 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3269 $dbman->create_table($table); 3270 3271 $DB->insert_record($tablename, array('course' => 1)); 3272 $DB->insert_record($tablename, array('course' => 2)); 3273 $DB->insert_record($tablename, array('course' => 3)); 3274 $DB->insert_record($tablename, array('course' => 4)); 3275 $DB->insert_record($tablename, array('course' => 5)); 3276 $DB->insert_record($tablename, array('course' => 6)); 3277 3278 // Test against db write locking while on an open recordset. 3279 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // Get courses = {3,4}. 3280 foreach ($rs as $record) { 3281 $cid = $record->course; 3282 $DB->set_field($tablename, 'course', 10, array('course' => $cid)); 3283 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid))); 3284 } 3285 $rs->close(); 3286 3287 $this->assertEquals(2, $DB->count_records($tablename, array('course' => 10))); 3288 } 3289 3290 public function test_delete_records() { 3291 $DB = $this->tdb; 3292 $dbman = $DB->get_manager(); 3293 3294 $table = $this->get_test_table(); 3295 $tablename = $table->getName(); 3296 3297 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3298 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3299 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); 3300 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3301 $dbman->create_table($table); 3302 3303 $DB->insert_record($tablename, array('course' => 3)); 3304 $DB->insert_record($tablename, array('course' => 2)); 3305 $DB->insert_record($tablename, array('course' => 2)); 3306 3307 // Delete all records. 3308 $this->assertTrue($DB->delete_records($tablename)); 3309 $this->assertEquals(0, $DB->count_records($tablename)); 3310 3311 // Delete subset of records. 3312 $DB->insert_record($tablename, array('course' => 3)); 3313 $DB->insert_record($tablename, array('course' => 2)); 3314 $DB->insert_record($tablename, array('course' => 2)); 3315 3316 $this->assertTrue($DB->delete_records($tablename, array('course' => 2))); 3317 $this->assertEquals(1, $DB->count_records($tablename)); 3318 3319 // Delete all. 3320 $this->assertTrue($DB->delete_records($tablename, array())); 3321 $this->assertEquals(0, $DB->count_records($tablename)); 3322 3323 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 3324 $conditions = array('onetext'=>'1'); 3325 try { 3326 $DB->delete_records($tablename, $conditions); 3327 if (debugging()) { 3328 // Only in debug mode - hopefully all devs test code in debug mode... 3329 $this->fail('An Exception is missing, expected due to equating of text fields'); 3330 } 3331 } catch (moodle_exception $e) { 3332 $this->assertInstanceOf('dml_exception', $e); 3333 $this->assertSame('textconditionsnotallowed', $e->errorcode); 3334 } 3335 3336 // Test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int). 3337 $conditions = array('onetext' => 1); 3338 try { 3339 $DB->delete_records($tablename, $conditions); 3340 if (debugging()) { 3341 // Only in debug mode - hopefully all devs test code in debug mode... 3342 $this->fail('An Exception is missing, expected due to equating of text fields'); 3343 } 3344 } catch (moodle_exception $e) { 3345 $this->assertInstanceOf('dml_exception', $e); 3346 $this->assertSame('textconditionsnotallowed', $e->errorcode); 3347 } 3348 } 3349 3350 public function test_delete_records_select() { 3351 $DB = $this->tdb; 3352 $dbman = $DB->get_manager(); 3353 3354 $table = $this->get_test_table(); 3355 $tablename = $table->getName(); 3356 3357 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3358 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3359 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3360 $dbman->create_table($table); 3361 3362 $DB->insert_record($tablename, array('course' => 3)); 3363 $DB->insert_record($tablename, array('course' => 2)); 3364 $DB->insert_record($tablename, array('course' => 2)); 3365 3366 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2))); 3367 $this->assertEquals(1, $DB->count_records($tablename)); 3368 } 3369 3370 public function test_delete_records_list() { 3371 $DB = $this->tdb; 3372 $dbman = $DB->get_manager(); 3373 3374 $table = $this->get_test_table(); 3375 $tablename = $table->getName(); 3376 3377 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3378 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3379 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3380 $dbman->create_table($table); 3381 3382 $DB->insert_record($tablename, array('course' => 1)); 3383 $DB->insert_record($tablename, array('course' => 2)); 3384 $DB->insert_record($tablename, array('course' => 3)); 3385 3386 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3))); 3387 $this->assertEquals(1, $DB->count_records($tablename)); 3388 3389 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); // Must delete 0 rows without conditions. MDL-17645. 3390 $this->assertEquals(1, $DB->count_records($tablename)); 3391 } 3392 3393 public function test_object_params() { 3394 $DB = $this->tdb; 3395 $dbman = $DB->get_manager(); 3396 3397 $table = $this->get_test_table(); 3398 $tablename = $table->getName(); 3399 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3400 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3401 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3402 $dbman->create_table($table); 3403 3404 $o = new stdClass(); // Objects without __toString - never worked. 3405 try { 3406 $DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o)); 3407 $this->fail('coding_exception expected'); 3408 } catch (moodle_exception $e) { 3409 $this->assertInstanceOf('coding_exception', $e); 3410 } 3411 3412 // Objects with __toString() forbidden everywhere since 2.3. 3413 $o = new dml_test_object_one(); 3414 try { 3415 $DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o)); 3416 $this->fail('coding_exception expected'); 3417 } catch (moodle_exception $e) { 3418 $this->assertInstanceOf('coding_exception', $e); 3419 } 3420 3421 try { 3422 $DB->execute("SELECT {{$tablename}} WHERE course = ? ", array($o)); 3423 $this->fail('coding_exception expected'); 3424 } catch (moodle_exception $e) { 3425 $this->assertInstanceOf('coding_exception', $e); 3426 } 3427 3428 try { 3429 $DB->get_recordset_sql("SELECT {{$tablename}} WHERE course = ? ", array($o)); 3430 $this->fail('coding_exception expected'); 3431 } catch (moodle_exception $e) { 3432 $this->assertInstanceOf('coding_exception', $e); 3433 } 3434 3435 try { 3436 $DB->get_records_sql("SELECT {{$tablename}} WHERE course = ? ", array($o)); 3437 $this->fail('coding_exception expected'); 3438 } catch (moodle_exception $e) { 3439 $this->assertInstanceOf('coding_exception', $e); 3440 } 3441 3442 try { 3443 $record = new stdClass(); 3444 $record->course = $o; 3445 $DB->insert_record_raw($tablename, $record); 3446 $this->fail('coding_exception expected'); 3447 } catch (moodle_exception $e) { 3448 $this->assertInstanceOf('coding_exception', $e); 3449 } 3450 3451 try { 3452 $record = new stdClass(); 3453 $record->course = $o; 3454 $DB->insert_record($tablename, $record); 3455 $this->fail('coding_exception expected'); 3456 } catch (moodle_exception $e) { 3457 $this->assertInstanceOf('coding_exception', $e); 3458 } 3459 3460 try { 3461 $record = new stdClass(); 3462 $record->course = $o; 3463 $DB->import_record($tablename, $record); 3464 $this->fail('coding_exception expected'); 3465 } catch (moodle_exception $e) { 3466 $this->assertInstanceOf('coding_exception', $e); 3467 } 3468 3469 try { 3470 $record = new stdClass(); 3471 $record->id = 1; 3472 $record->course = $o; 3473 $DB->update_record_raw($tablename, $record); 3474 $this->fail('coding_exception expected'); 3475 } catch (moodle_exception $e) { 3476 $this->assertInstanceOf('coding_exception', $e); 3477 } 3478 3479 try { 3480 $record = new stdClass(); 3481 $record->id = 1; 3482 $record->course = $o; 3483 $DB->update_record($tablename, $record); 3484 $this->fail('coding_exception expected'); 3485 } catch (moodle_exception $e) { 3486 $this->assertInstanceOf('coding_exception', $e); 3487 } 3488 3489 try { 3490 $DB->set_field_select($tablename, 'course', 1, "course = ? ", array($o)); 3491 $this->fail('coding_exception expected'); 3492 } catch (moodle_exception $e) { 3493 $this->assertInstanceOf('coding_exception', $e); 3494 } 3495 3496 try { 3497 $DB->delete_records_select($tablename, "course = ? ", array($o)); 3498 $this->fail('coding_exception expected'); 3499 } catch (moodle_exception $e) { 3500 $this->assertInstanceOf('coding_exception', $e); 3501 } 3502 } 3503 3504 public function test_sql_null_from_clause() { 3505 $DB = $this->tdb; 3506 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause(); 3507 $this->assertEquals(1, $DB->get_field_sql($sql)); 3508 } 3509 3510 public function test_sql_bitand() { 3511 $DB = $this->tdb; 3512 $dbman = $DB->get_manager(); 3513 3514 $table = $this->get_test_table(); 3515 $tablename = $table->getName(); 3516 3517 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3518 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3519 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3520 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3521 $dbman->create_table($table); 3522 3523 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); 3524 3525 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause(); 3526 $this->assertEquals(2, $DB->get_field_sql($sql)); 3527 3528 $sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}"; 3529 $result = $DB->get_records_sql($sql); 3530 $this->assertCount(1, $result); 3531 $this->assertEquals(2, reset($result)->res); 3532 3533 $sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}"; 3534 $result = $DB->get_records_sql($sql, array(10)); 3535 $this->assertCount(1, $result); 3536 $this->assertEquals(2, reset($result)->res); 3537 } 3538 3539 public function test_sql_bitnot() { 3540 $DB = $this->tdb; 3541 3542 $not = $DB->sql_bitnot(2); 3543 $notlimited = $DB->sql_bitand($not, 7); // Might be positive or negative number which can not fit into PHP INT! 3544 3545 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause(); 3546 $this->assertEquals(5, $DB->get_field_sql($sql)); 3547 } 3548 3549 public function test_sql_bitor() { 3550 $DB = $this->tdb; 3551 $dbman = $DB->get_manager(); 3552 3553 $table = $this->get_test_table(); 3554 $tablename = $table->getName(); 3555 3556 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3557 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3558 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3559 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3560 $dbman->create_table($table); 3561 3562 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); 3563 3564 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause(); 3565 $this->assertEquals(11, $DB->get_field_sql($sql)); 3566 3567 $sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}"; 3568 $result = $DB->get_records_sql($sql); 3569 $this->assertCount(1, $result); 3570 $this->assertEquals(11, reset($result)->res); 3571 3572 $sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}"; 3573 $result = $DB->get_records_sql($sql, array(10)); 3574 $this->assertCount(1, $result); 3575 $this->assertEquals(11, reset($result)->res); 3576 } 3577 3578 public function test_sql_bitxor() { 3579 $DB = $this->tdb; 3580 $dbman = $DB->get_manager(); 3581 3582 $table = $this->get_test_table(); 3583 $tablename = $table->getName(); 3584 3585 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3586 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3587 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3588 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3589 $dbman->create_table($table); 3590 3591 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); 3592 3593 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause(); 3594 $this->assertEquals(9, $DB->get_field_sql($sql)); 3595 3596 $sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}"; 3597 $result = $DB->get_records_sql($sql); 3598 $this->assertCount(1, $result); 3599 $this->assertEquals(9, reset($result)->res); 3600 3601 $sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}"; 3602 $result = $DB->get_records_sql($sql, array(10)); 3603 $this->assertCount(1, $result); 3604 $this->assertEquals(9, reset($result)->res); 3605 } 3606 3607 public function test_sql_modulo() { 3608 $DB = $this->tdb; 3609 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause(); 3610 $this->assertEquals(3, $DB->get_field_sql($sql)); 3611 } 3612 3613 public function test_sql_ceil() { 3614 $DB = $this->tdb; 3615 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause(); 3616 $this->assertEquals(666, $DB->get_field_sql($sql)); 3617 } 3618 3619 public function test_cast_char2int() { 3620 $DB = $this->tdb; 3621 $dbman = $DB->get_manager(); 3622 3623 $table1 = $this->get_test_table("1"); 3624 $tablename1 = $table1->getName(); 3625 3626 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3627 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3628 $table1->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 3629 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3630 $dbman->create_table($table1); 3631 3632 $DB->insert_record($tablename1, array('name'=>'0100', 'nametext'=>'0200')); 3633 $DB->insert_record($tablename1, array('name'=>'10', 'nametext'=>'20')); 3634 3635 $table2 = $this->get_test_table("2"); 3636 $tablename2 = $table2->getName(); 3637 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3638 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3639 $table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 3640 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3641 $dbman->create_table($table2); 3642 3643 $DB->insert_record($tablename2, array('res'=>100, 'restext'=>200)); 3644 3645 // Casting varchar field. 3646 $sql = "SELECT * 3647 FROM {".$tablename1."} t1 3648 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.name")." = t2.res "; 3649 $records = $DB->get_records_sql($sql); 3650 $this->assertCount(1, $records); 3651 // Also test them in order clauses. 3652 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('name'); 3653 $records = $DB->get_records_sql($sql); 3654 $this->assertCount(2, $records); 3655 $this->assertSame('10', reset($records)->name); 3656 $this->assertSame('0100', next($records)->name); 3657 3658 // Casting text field. 3659 $sql = "SELECT * 3660 FROM {".$tablename1."} t1 3661 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.nametext", true)." = t2.restext "; 3662 $records = $DB->get_records_sql($sql); 3663 $this->assertCount(1, $records); 3664 // Also test them in order clauses. 3665 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('nametext', true); 3666 $records = $DB->get_records_sql($sql); 3667 $this->assertCount(2, $records); 3668 $this->assertSame('20', reset($records)->nametext); 3669 $this->assertSame('0200', next($records)->nametext); 3670 } 3671 3672 public function test_cast_char2real() { 3673 $DB = $this->tdb; 3674 $dbman = $DB->get_manager(); 3675 3676 $table = $this->get_test_table(); 3677 $tablename = $table->getName(); 3678 3679 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3680 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3681 $table->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null); 3682 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null); 3683 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3684 $dbman->create_table($table); 3685 3686 $DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1)); 3687 $DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666)); 3688 $DB->insert_record($tablename, array('name'=>'011.13333333', 'nametext'=>'011.13333333', 'res'=>10.1)); 3689 3690 // Casting varchar field. 3691 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res"; 3692 $records = $DB->get_records_sql($sql); 3693 $this->assertCount(2, $records); 3694 // Also test them in order clauses. 3695 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('name'); 3696 $records = $DB->get_records_sql($sql); 3697 $this->assertCount(3, $records); 3698 $this->assertSame('10.10', reset($records)->name); 3699 $this->assertSame('011.13333333', next($records)->name); 3700 $this->assertSame('91.10', next($records)->name); 3701 // And verify we can operate with them without too much problem with at least 6 decimals scale accuracy. 3702 $sql = "SELECT AVG(" . $DB->sql_cast_char2real('name') . ") FROM {{$tablename}}"; 3703 $this->assertEquals(37.44444443333333, (float)$DB->get_field_sql($sql), '', 1.0E-6); 3704 3705 // Casting text field. 3706 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res"; 3707 $records = $DB->get_records_sql($sql); 3708 $this->assertCount(2, $records); 3709 // Also test them in order clauses. 3710 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('nametext', true); 3711 $records = $DB->get_records_sql($sql); 3712 $this->assertCount(3, $records); 3713 $this->assertSame('10.10', reset($records)->nametext); 3714 $this->assertSame('011.13333333', next($records)->nametext); 3715 $this->assertSame('91.10', next($records)->nametext); 3716 // And verify we can operate with them without too much problem with at least 6 decimals scale accuracy. 3717 $sql = "SELECT AVG(" . $DB->sql_cast_char2real('nametext', true) . ") FROM {{$tablename}}"; 3718 $this->assertEquals(37.44444443333333, (float)$DB->get_field_sql($sql), '', 1.0E-6); 3719 3720 // Check it works with values passed as param. 3721 $sql = "SELECT name FROM {{$tablename}} WHERE FLOOR(res - " . $DB->sql_cast_char2real(':param') . ") = 0"; 3722 $this->assertEquals('011.13333333', $DB->get_field_sql($sql, array('param' => '10.09999'))); 3723 3724 // And also, although not recommended, with directly passed values. 3725 $sql = "SELECT name FROM {{$tablename}} WHERE FLOOR(res - " . $DB->sql_cast_char2real('10.09999') . ") = 0"; 3726 $this->assertEquals('011.13333333', $DB->get_field_sql($sql)); 3727 } 3728 3729 public function test_sql_compare_text() { 3730 $DB = $this->tdb; 3731 $dbman = $DB->get_manager(); 3732 3733 $table = $this->get_test_table(); 3734 $tablename = $table->getName(); 3735 3736 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3737 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3738 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); 3739 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3740 $dbman->create_table($table); 3741 3742 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd')); 3743 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef')); 3744 $DB->insert_record($tablename, array('name'=>'aaaa', 'description'=>'aaaacccccccccccccccccc')); 3745 $DB->insert_record($tablename, array('name'=>'xxxx', 'description'=>'123456789a123456789b123456789c123456789d')); 3746 3747 // Only some supported databases truncate TEXT fields for comparisons, currently MSSQL and Oracle. 3748 $dbtruncatestextfields = ($DB->get_dbfamily() == 'mssql' || $DB->get_dbfamily() == 'oracle'); 3749 3750 if ($dbtruncatestextfields) { 3751 // Ensure truncation behaves as expected. 3752 3753 $sql = "SELECT " . $DB->sql_compare_text('description') . " AS field FROM {{$tablename}} WHERE name = ?"; 3754 $description = $DB->get_field_sql($sql, array('xxxx')); 3755 3756 // Should truncate to 32 chars (the default). 3757 $this->assertEquals('123456789a123456789b123456789c12', $description); 3758 3759 $sql = "SELECT " . $DB->sql_compare_text('description', 35) . " AS field FROM {{$tablename}} WHERE name = ?"; 3760 $description = $DB->get_field_sql($sql, array('xxxx')); 3761 3762 // Should truncate to the specified number of chars. 3763 $this->assertEquals('123456789a123456789b123456789c12345', $description); 3764 } 3765 3766 // Ensure text field comparison is successful. 3767 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description'); 3768 $records = $DB->get_records_sql($sql); 3769 $this->assertCount(1, $records); 3770 3771 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description', 4); 3772 $records = $DB->get_records_sql($sql); 3773 if ($dbtruncatestextfields) { 3774 // Should truncate description to 4 characters before comparing. 3775 $this->assertCount(2, $records); 3776 } else { 3777 // Should leave untruncated, so one less match. 3778 $this->assertCount(1, $records); 3779 } 3780 3781 // Now test the function with really big content and params. 3782 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt'); 3783 $DB->insert_record($tablename, array('name' => 'zzzz', 'description' => $clob)); 3784 $sql = "SELECT * FROM {{$tablename}} 3785 WHERE " . $DB->sql_compare_text('description') . " = " . $DB->sql_compare_text(':clob'); 3786 $records = $DB->get_records_sql($sql, array('clob' => $clob)); 3787 $this->assertCount(1, $records); 3788 $record = reset($records); 3789 $this->assertSame($clob, $record->description); 3790 } 3791 3792 public function test_unique_index_collation_trouble() { 3793 // Note: this is a work in progress, we should probably move this to ddl test. 3794 3795 $DB = $this->tdb; 3796 $dbman = $DB->get_manager(); 3797 3798 $table = $this->get_test_table(); 3799 $tablename = $table->getName(); 3800 3801 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3802 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3803 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3804 $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name')); 3805 $dbman->create_table($table); 3806 3807 $DB->insert_record($tablename, array('name'=>'aaa')); 3808 3809 try { 3810 $DB->insert_record($tablename, array('name'=>'AAA')); 3811 } catch (moodle_exception $e) { 3812 // TODO: ignore case insensitive uniqueness problems for now. 3813 // $this->fail("Unique index is case sensitive - this may cause problems in some tables"); 3814 } 3815 3816 try { 3817 $DB->insert_record($tablename, array('name'=>'aäa')); 3818 $DB->insert_record($tablename, array('name'=>'aáa')); 3819 $this->assertTrue(true); 3820 } catch (moodle_exception $e) { 3821 $family = $DB->get_dbfamily(); 3822 if ($family === 'mysql' or $family === 'mssql') { 3823 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation."); 3824 } else { 3825 // This should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness. 3826 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages."); 3827 } 3828 throw($e); 3829 } 3830 } 3831 3832 public function test_sql_binary_equal() { 3833 $DB = $this->tdb; 3834 $dbman = $DB->get_manager(); 3835 3836 $table = $this->get_test_table(); 3837 $tablename = $table->getName(); 3838 3839 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3840 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3841 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3842 $dbman->create_table($table); 3843 3844 $DB->insert_record($tablename, array('name'=>'aaa')); 3845 $DB->insert_record($tablename, array('name'=>'aáa')); 3846 $DB->insert_record($tablename, array('name'=>'aäa')); 3847 $DB->insert_record($tablename, array('name'=>'bbb')); 3848 $DB->insert_record($tablename, array('name'=>'BBB')); 3849 3850 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb')); 3851 $this->assertEquals(1, count($records), 'SQL operator "=" is expected to be case sensitive'); 3852 3853 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa')); 3854 $this->assertEquals(1, count($records), 'SQL operator "=" is expected to be accent sensitive'); 3855 } 3856 3857 public function test_sql_like() { 3858 $DB = $this->tdb; 3859 $dbman = $DB->get_manager(); 3860 3861 $table = $this->get_test_table(); 3862 $tablename = $table->getName(); 3863 3864 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3865 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 3866 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 3867 $dbman->create_table($table); 3868 3869 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord')); 3870 $DB->insert_record($tablename, array('name'=>'Nodupor')); 3871 $DB->insert_record($tablename, array('name'=>'ouch')); 3872 $DB->insert_record($tablename, array('name'=>'ouc_')); 3873 $DB->insert_record($tablename, array('name'=>'ouc%')); 3874 $DB->insert_record($tablename, array('name'=>'aui')); 3875 $DB->insert_record($tablename, array('name'=>'aüi')); 3876 $DB->insert_record($tablename, array('name'=>'aÜi')); 3877 3878 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false); 3879 $records = $DB->get_records_sql($sql, array("%dup_r%")); 3880 $this->assertCount(2, $records); 3881 3882 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); 3883 $records = $DB->get_records_sql($sql, array("%dup%")); 3884 $this->assertCount(1, $records); 3885 3886 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // Defaults. 3887 $records = $DB->get_records_sql($sql, array("%dup%")); 3888 $this->assertCount(1, $records); 3889 3890 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); 3891 $records = $DB->get_records_sql($sql, array("ouc\\_")); 3892 $this->assertCount(1, $records); 3893 3894 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|'); 3895 $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|'))); 3896 $this->assertCount(1, $records); 3897 3898 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true); 3899 $records = $DB->get_records_sql($sql, array('aui')); 3900 $this->assertCount(1, $records); 3901 3902 // Test LIKE under unusual collations. 3903 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false); 3904 $records = $DB->get_records_sql($sql, array("%dup_r%")); 3905 $this->assertCount(2, $records); 3906 3907 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE. 3908 $records = $DB->get_records_sql($sql, array("%o%")); 3909 $this->assertCount(3, $records); 3910 3911 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE. 3912 $records = $DB->get_records_sql($sql, array("%D%")); 3913 $this->assertCount(6, $records); 3914 3915 // Verify usual escaping characters work fine. 3916 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '\\'); 3917 $records = $DB->get_records_sql($sql, array("ouc\\_")); 3918 $this->assertCount(1, $records); 3919 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|'); 3920 $records = $DB->get_records_sql($sql, array("ouc|%")); 3921 $this->assertCount(1, $records); 3922 3923 // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors. 3924 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false); 3925 $records = $DB->get_records_sql($sql, array('aui')); 3926 // $this->assertEquals(2, count($records), 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); 3927 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false); 3928 $records = $DB->get_records_sql($sql, array('aui')); 3929 // $this->assertEquals(3, count($records), 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); 3930 } 3931 3932 public function test_coalesce() { 3933 $DB = $this->tdb; 3934 3935 // Testing not-null occurrences, return 1st. 3936 $sql = "SELECT COALESCE('returnthis', 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause(); 3937 $this->assertSame('returnthis', $DB->get_field_sql($sql, array())); 3938 $sql = "SELECT COALESCE(:paramvalue, 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause(); 3939 $this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis'))); 3940 3941 // Testing null occurrences, return 2nd. 3942 $sql = "SELECT COALESCE(null, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause(); 3943 $this->assertSame('returnthis', $DB->get_field_sql($sql, array())); 3944 $sql = "SELECT COALESCE(:paramvalue, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause(); 3945 $this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null))); 3946 $sql = "SELECT COALESCE(null, :paramvalue, 'orthis') AS test" . $DB->sql_null_from_clause(); 3947 $this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis'))); 3948 3949 // Testing null occurrences, return 3rd. 3950 $sql = "SELECT COALESCE(null, null, 'returnthis') AS test" . $DB->sql_null_from_clause(); 3951 $this->assertSame('returnthis', $DB->get_field_sql($sql, array())); 3952 $sql = "SELECT COALESCE(null, :paramvalue, 'returnthis') AS test" . $DB->sql_null_from_clause(); 3953 $this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null))); 3954 $sql = "SELECT COALESCE(null, null, :paramvalue) AS test" . $DB->sql_null_from_clause(); 3955 $this->assertSame('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis'))); 3956 3957 // Testing all null occurrences, return null. 3958 // Note: under mssql, if all elements are nulls, at least one must be a "typed" null, hence 3959 // we cannot test this in a cross-db way easily, so next 2 tests are using 3960 // different queries depending of the DB family. 3961 $customnull = $DB->get_dbfamily() == 'mssql' ? 'CAST(null AS varchar)' : 'null'; 3962 $sql = "SELECT COALESCE(null, null, " . $customnull . ") AS test" . $DB->sql_null_from_clause(); 3963 $this->assertNull($DB->get_field_sql($sql, array())); 3964 $sql = "SELECT COALESCE(null, :paramvalue, " . $customnull . ") AS test" . $DB->sql_null_from_clause(); 3965 $this->assertNull($DB->get_field_sql($sql, array('paramvalue' => null))); 3966 3967 // Check there are not problems with whitespace strings. 3968 $sql = "SELECT COALESCE(null, :paramvalue, null) AS test" . $DB->sql_null_from_clause(); 3969 $this->assertSame('', $DB->get_field_sql($sql, array('paramvalue' => ''))); 3970 } 3971 3972 public function test_sql_concat() { 3973 $DB = $this->tdb; 3974 $dbman = $DB->get_manager(); 3975 3976 // Testing all sort of values. 3977 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause(); 3978 // String, some unicode chars. 3979 $params = array('name', 'áéíóú', 'name3'); 3980 $this->assertSame('nameáéíóúname3', $DB->get_field_sql($sql, $params)); 3981 // String, spaces and numbers. 3982 $params = array('name', ' ', 12345); 3983 $this->assertSame('name 12345', $DB->get_field_sql($sql, $params)); 3984 // Float, empty and strings. 3985 $params = array(123.45, '', 'test'); 3986 $this->assertSame('123.45test', $DB->get_field_sql($sql, $params)); 3987 // Only integers. 3988 $params = array(12, 34, 56); 3989 $this->assertSame('123456', $DB->get_field_sql($sql, $params)); 3990 // Float, null and strings. 3991 $params = array(123.45, null, 'test'); 3992 $this->assertNull($DB->get_field_sql($sql, $params)); // Concatenate null with anything result = null. 3993 3994 // Testing fieldnames + values and also integer fieldnames. 3995 $table = $this->get_test_table(); 3996 $tablename = $table->getName(); 3997 3998 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 3999 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); 4000 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4001 $dbman->create_table($table); 4002 4003 $DB->insert_record($tablename, array('description'=>'áéíóú')); 4004 $DB->insert_record($tablename, array('description'=>'dxxx')); 4005 $DB->insert_record($tablename, array('description'=>'bcde')); 4006 4007 // Fieldnames and values mixed. 4008 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}'; 4009 $records = $DB->get_records_sql($sql, array(123.45, 'test')); 4010 $this->assertCount(3, $records); 4011 $this->assertSame('áéíóúharcoded123.45test', $records[1]->result); 4012 // Integer fieldnames and values. 4013 $sql = 'SELECT id, ' . $DB->sql_concat('id', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}'; 4014 $records = $DB->get_records_sql($sql, array(123.45, 'test')); 4015 $this->assertCount(3, $records); 4016 $this->assertSame('1harcoded123.45test', $records[1]->result); 4017 // All integer fieldnames. 4018 $sql = 'SELECT id, ' . $DB->sql_concat('id', 'id', 'id') . ' AS result FROM {' . $tablename . '}'; 4019 $records = $DB->get_records_sql($sql, array()); 4020 $this->assertCount(3, $records); 4021 $this->assertSame('111', $records[1]->result); 4022 4023 } 4024 4025 public function sql_concat_join_provider() { 4026 return array( 4027 // All strings. 4028 array( 4029 "' '", 4030 array("'name'", "'name2'", "'name3'"), 4031 array(), 4032 'name name2 name3', 4033 ), 4034 // All strings using placeholders 4035 array( 4036 "' '", 4037 array("?", "?", "?"), 4038 array('name', 'name2', 'name3'), 4039 'name name2 name3', 4040 ), 4041 // All integers. 4042 array( 4043 "' '", 4044 array(1, 2, 3), 4045 array(), 4046 '1 2 3', 4047 ), 4048 // All integers using placeholders 4049 array( 4050 "' '", 4051 array("?", "?", "?"), 4052 array(1, 2, 3), 4053 '1 2 3', 4054 ), 4055 // Mix of strings and integers. 4056 array( 4057 "' '", 4058 array(1, "'2'", 3), 4059 array(), 4060 '1 2 3', 4061 ), 4062 // Mix of strings and integers using placeholders. 4063 array( 4064 "' '", 4065 array(1, '2', 3), 4066 array(), 4067 '1 2 3', 4068 ), 4069 ); 4070 } 4071 4072 /** 4073 * @dataProvider sql_concat_join_provider 4074 * @param string $concat The string to use when concatanating. 4075 * @param array $fields The fields to concatanate 4076 * @param array $params Any parameters to provide to the query 4077 * @param @string $expected The expected result 4078 */ 4079 public function test_concat_join($concat, $fields, $params, $expected) { 4080 $DB = $this->tdb; 4081 $sql = "SELECT " . $DB->sql_concat_join($concat, $fields) . " AS result" . $DB->sql_null_from_clause(); 4082 $result = $DB->get_field_sql($sql, $params); 4083 $this->assertEquals($expected, $result); 4084 } 4085 4086 public function test_sql_fullname() { 4087 $DB = $this->tdb; 4088 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause(); 4089 $params = array('first'=>'Firstname', 'last'=>'Surname'); 4090 $this->assertEquals("Firstname Surname", $DB->get_field_sql($sql, $params)); 4091 } 4092 4093 public function test_sql_order_by_text() { 4094 $DB = $this->tdb; 4095 $dbman = $DB->get_manager(); 4096 4097 $table = $this->get_test_table(); 4098 $tablename = $table->getName(); 4099 4100 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4101 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); 4102 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4103 $dbman->create_table($table); 4104 4105 $DB->insert_record($tablename, array('description'=>'abcd')); 4106 $DB->insert_record($tablename, array('description'=>'dxxx')); 4107 $DB->insert_record($tablename, array('description'=>'bcde')); 4108 4109 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_order_by_text('description'); 4110 $records = $DB->get_records_sql($sql); 4111 $first = array_shift($records); 4112 $this->assertEquals(1, $first->id); 4113 $second = array_shift($records); 4114 $this->assertEquals(3, $second->id); 4115 $last = array_shift($records); 4116 $this->assertEquals(2, $last->id); 4117 } 4118 4119 public function test_sql_substring() { 4120 $DB = $this->tdb; 4121 $dbman = $DB->get_manager(); 4122 4123 $table = $this->get_test_table(); 4124 $tablename = $table->getName(); 4125 4126 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4127 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4128 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4129 $dbman->create_table($table); 4130 4131 $string = 'abcdefghij'; 4132 4133 $DB->insert_record($tablename, array('name'=>$string)); 4134 4135 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {{$tablename}}"; 4136 $record = $DB->get_record_sql($sql); 4137 $this->assertEquals(substr($string, 5-1), $record->name); 4138 4139 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {{$tablename}}"; 4140 $record = $DB->get_record_sql($sql); 4141 $this->assertEquals(substr($string, 5-1, 2), $record->name); 4142 4143 try { 4144 // Silence php warning. 4145 @$DB->sql_substr("name"); 4146 $this->fail("Expecting an exception, none occurred"); 4147 } catch (moodle_exception $e) { 4148 $this->assertInstanceOf('coding_exception', $e); 4149 } 4150 4151 // Cover the function using placeholders in all positions. 4152 $start = 4; 4153 $length = 2; 4154 // 1st param (target). 4155 $sql = "SELECT id, ".$DB->sql_substr(":param1", $start)." AS name FROM {{$tablename}}"; 4156 $record = $DB->get_record_sql($sql, array('param1' => $string)); 4157 $this->assertEquals(substr($string, $start - 1), $record->name); // PHP's substr is 0-based. 4158 // 2nd param (start). 4159 $sql = "SELECT id, ".$DB->sql_substr("name", ":param1")." AS name FROM {{$tablename}}"; 4160 $record = $DB->get_record_sql($sql, array('param1' => $start)); 4161 $this->assertEquals(substr($string, $start - 1), $record->name); // PHP's substr is 0-based. 4162 // 3rd param (length). 4163 $sql = "SELECT id, ".$DB->sql_substr("name", $start, ":param1")." AS name FROM {{$tablename}}"; 4164 $record = $DB->get_record_sql($sql, array('param1' => $length)); 4165 $this->assertEquals(substr($string, $start - 1, $length), $record->name); // PHP's substr is 0-based. 4166 // All together. 4167 $sql = "SELECT id, ".$DB->sql_substr(":param1", ":param2", ":param3")." AS name FROM {{$tablename}}"; 4168 $record = $DB->get_record_sql($sql, array('param1' => $string, 'param2' => $start, 'param3' => $length)); 4169 $this->assertEquals(substr($string, $start - 1, $length), $record->name); // PHP's substr is 0-based. 4170 4171 // Try also with some expression passed. 4172 $sql = "SELECT id, ".$DB->sql_substr("name", "(:param1 + 1) - 1")." AS name FROM {{$tablename}}"; 4173 $record = $DB->get_record_sql($sql, array('param1' => $start)); 4174 $this->assertEquals(substr($string, $start - 1), $record->name); // PHP's substr is 0-based. 4175 } 4176 4177 public function test_sql_length() { 4178 $DB = $this->tdb; 4179 $this->assertEquals($DB->get_field_sql( 4180 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5); 4181 $this->assertEquals($DB->get_field_sql( 4182 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5); 4183 } 4184 4185 public function test_sql_position() { 4186 $DB = $this->tdb; 4187 $this->assertEquals($DB->get_field_sql( 4188 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2); 4189 $this->assertEquals($DB->get_field_sql( 4190 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0); 4191 } 4192 4193 public function test_sql_empty() { 4194 $DB = $this->tdb; 4195 $dbman = $DB->get_manager(); 4196 4197 $table = $this->get_test_table(); 4198 $tablename = $table->getName(); 4199 4200 $this->assertSame('', $DB->sql_empty()); // Since 2.5 the hack is applied automatically to all bound params. 4201 $this->assertDebuggingCalled(); 4202 4203 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4204 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4205 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value'); 4206 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); 4207 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4208 $dbman->create_table($table); 4209 4210 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>'')); 4211 $DB->insert_record($tablename, array('name'=>null)); 4212 $DB->insert_record($tablename, array('name'=>'lalala')); 4213 $DB->insert_record($tablename, array('name'=>0)); 4214 4215 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('')); 4216 $this->assertCount(1, $records); 4217 $record = reset($records); 4218 $this->assertSame('', $record->name); 4219 4220 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnull = ?", array('')); 4221 $this->assertCount(1, $records); 4222 $record = reset($records); 4223 $this->assertSame('', $record->namenotnull); 4224 4225 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnullnodeflt = ?", array('')); 4226 $this->assertCount(4, $records); 4227 $record = reset($records); 4228 $this->assertSame('', $record->namenotnullnodeflt); 4229 } 4230 4231 public function test_sql_isempty() { 4232 $DB = $this->tdb; 4233 $dbman = $DB->get_manager(); 4234 4235 $table = $this->get_test_table(); 4236 $tablename = $table->getName(); 4237 4238 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4239 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); 4240 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4241 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null); 4242 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null); 4243 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4244 $dbman->create_table($table); 4245 4246 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>'')); 4247 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null)); 4248 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala')); 4249 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0)); 4250 4251 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'name', false, false)); 4252 $this->assertCount(1, $records); 4253 $record = reset($records); 4254 $this->assertSame('', $record->name); 4255 4256 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false)); 4257 $this->assertCount(1, $records); 4258 $record = reset($records); 4259 $this->assertSame('', $record->namenull); 4260 4261 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'description', false, true)); 4262 $this->assertCount(1, $records); 4263 $record = reset($records); 4264 $this->assertSame('', $record->description); 4265 4266 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true)); 4267 $this->assertCount(1, $records); 4268 $record = reset($records); 4269 $this->assertSame('', $record->descriptionnull); 4270 } 4271 4272 public function test_sql_isnotempty() { 4273 $DB = $this->tdb; 4274 $dbman = $DB->get_manager(); 4275 4276 $table = $this->get_test_table(); 4277 $tablename = $table->getName(); 4278 4279 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4280 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); 4281 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4282 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null); 4283 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null); 4284 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4285 $dbman->create_table($table); 4286 4287 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>'')); 4288 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null)); 4289 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala')); 4290 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0)); 4291 4292 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false)); 4293 $this->assertCount(3, $records); 4294 $record = reset($records); 4295 $this->assertSame('??', $record->name); 4296 4297 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false)); 4298 $this->assertCount(2, $records); // Nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour. 4299 $record = reset($records); 4300 $this->assertSame('la', $record->namenull); // So 'la' is the first non-empty 'namenull' record. 4301 4302 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true)); 4303 $this->assertCount(3, $records); 4304 $record = reset($records); 4305 $this->assertSame('??', $record->description); 4306 4307 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true)); 4308 $this->assertCount(2, $records); // Nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour. 4309 $record = reset($records); 4310 $this->assertSame('lalala', $record->descriptionnull); // So 'lalala' is the first non-empty 'descriptionnull' record. 4311 } 4312 4313 public function test_sql_regex() { 4314 $DB = $this->tdb; 4315 $dbman = $DB->get_manager(); 4316 4317 $table = $this->get_test_table(); 4318 $tablename = $table->getName(); 4319 4320 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4321 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4322 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4323 $dbman->create_table($table); 4324 4325 $DB->insert_record($tablename, array('name'=>'lalala')); 4326 $DB->insert_record($tablename, array('name'=>'holaaa')); 4327 $DB->insert_record($tablename, array('name'=>'aouch')); 4328 4329 $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex()." ?"; 4330 $params = array('a$'); 4331 if ($DB->sql_regex_supported()) { 4332 $records = $DB->get_records_sql($sql, $params); 4333 $this->assertCount(2, $records); 4334 } else { 4335 $this->assertTrue(true, 'Regexp operations not supported. Test skipped'); 4336 } 4337 4338 $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex(false)." ?"; 4339 $params = array('.a'); 4340 if ($DB->sql_regex_supported()) { 4341 $records = $DB->get_records_sql($sql, $params); 4342 $this->assertCount(1, $records); 4343 } else { 4344 $this->assertTrue(true, 'Regexp operations not supported. Test skipped'); 4345 } 4346 4347 } 4348 4349 /** 4350 * Test some complicated variations of set_field_select. 4351 */ 4352 public function test_set_field_select_complicated() { 4353 $DB = $this->tdb; 4354 $dbman = $DB->get_manager(); 4355 4356 $table = $this->get_test_table(); 4357 $tablename = $table->getName(); 4358 4359 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4360 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4361 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4362 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL); 4363 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4364 $dbman->create_table($table); 4365 4366 $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello', 'name'=>'xyz')); 4367 $DB->insert_record($tablename, array('course' => 3, 'content' => 'world', 'name'=>'abc')); 4368 $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello', 'name'=>'def')); 4369 $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe', 'name'=>'abc')); 4370 // This SQL is a tricky case because we are selecting from the same table we are updating. 4371 $sql = 'id IN (SELECT outerq.id from (SELECT innerq.id from {' . $tablename . '} innerq WHERE course = 3) outerq)'; 4372 $DB->set_field_select($tablename, 'name', 'ghi', $sql); 4373 4374 $this->assertSame(2, $DB->count_records_select($tablename, 'name = ?', array('ghi'))); 4375 4376 } 4377 4378 /** 4379 * Test some more complex SQL syntax which moodle uses and depends on to work 4380 * useful to determine if new database libraries can be supported. 4381 */ 4382 public function test_get_records_sql_complicated() { 4383 $DB = $this->tdb; 4384 $dbman = $DB->get_manager(); 4385 4386 $table = $this->get_test_table(); 4387 $tablename = $table->getName(); 4388 4389 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4390 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4391 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 4392 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL); 4393 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4394 $dbman->create_table($table); 4395 4396 $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello', 'name'=>'xyz')); 4397 $DB->insert_record($tablename, array('course' => 3, 'content' => 'world', 'name'=>'abc')); 4398 $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello', 'name'=>'def')); 4399 $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe', 'name'=>'abc')); 4400 4401 // Test grouping by expressions in the query. MDL-26819. Note that there are 4 ways: 4402 // - By column position (GROUP by 1) - Not supported by mssql & oracle 4403 // - By column name (GROUP by course) - Supported by all, but leading to wrong results 4404 // - By column alias (GROUP by casecol) - Not supported by mssql & oracle 4405 // - By complete expression (GROUP BY CASE ...) - 100% cross-db, this test checks it 4406 $sql = "SELECT (CASE WHEN course = 3 THEN 1 ELSE 0 END) AS casecol, 4407 COUNT(1) AS countrecs, 4408 MAX(name) AS maxname 4409 FROM {{$tablename}} 4410 GROUP BY CASE WHEN course = 3 THEN 1 ELSE 0 END 4411 ORDER BY casecol DESC"; 4412 $result = array( 4413 1 => (object)array('casecol' => 1, 'countrecs' => 2, 'maxname' => 'xyz'), 4414 0 => (object)array('casecol' => 0, 'countrecs' => 2, 'maxname' => 'def')); 4415 $records = $DB->get_records_sql($sql, null); 4416 $this->assertEquals($result, $records); 4417 4418 // Another grouping by CASE expression just to ensure it works ok for multiple WHEN. 4419 $sql = "SELECT CASE name 4420 WHEN 'xyz' THEN 'last' 4421 WHEN 'def' THEN 'mid' 4422 WHEN 'abc' THEN 'first' 4423 END AS casecol, 4424 COUNT(1) AS countrecs, 4425 MAX(name) AS maxname 4426 FROM {{$tablename}} 4427 GROUP BY CASE name 4428 WHEN 'xyz' THEN 'last' 4429 WHEN 'def' THEN 'mid' 4430 WHEN 'abc' THEN 'first' 4431 END 4432 ORDER BY casecol DESC"; 4433 $result = array( 4434 'mid' => (object)array('casecol' => 'mid', 'countrecs' => 1, 'maxname' => 'def'), 4435 'last' => (object)array('casecol' => 'last', 'countrecs' => 1, 'maxname' => 'xyz'), 4436 'first'=> (object)array('casecol' => 'first', 'countrecs' => 2, 'maxname' => 'abc')); 4437 $records = $DB->get_records_sql($sql, null); 4438 $this->assertEquals($result, $records); 4439 4440 // Test CASE expressions in the ORDER BY clause - used by MDL-34657. 4441 $sql = "SELECT id, course, name 4442 FROM {{$tablename}} 4443 ORDER BY CASE WHEN (course = 5 OR name = 'xyz') THEN 0 ELSE 1 END, name, course"; 4444 // First, records matching the course = 5 OR name = 'xyz', then the rest. Each. 4445 // group ordered by name and course. 4446 $result = array( 4447 3 => (object)array('id' => 3, 'course' => 5, 'name' => 'def'), 4448 1 => (object)array('id' => 1, 'course' => 3, 'name' => 'xyz'), 4449 4 => (object)array('id' => 4, 'course' => 2, 'name' => 'abc'), 4450 2 => (object)array('id' => 2, 'course' => 3, 'name' => 'abc')); 4451 $records = $DB->get_records_sql($sql, null); 4452 $this->assertEquals($result, $records); 4453 // Verify also array keys, order is important in this test. 4454 $this->assertEquals(array_keys($result), array_keys($records)); 4455 4456 // Test limits in queries with DISTINCT/ALL clauses and multiple whitespace. MDL-25268. 4457 $sql = "SELECT DISTINCT course 4458 FROM {{$tablename}} 4459 ORDER BY course"; 4460 // Only limitfrom. 4461 $records = $DB->get_records_sql($sql, null, 1); 4462 $this->assertCount(2, $records); 4463 $this->assertEquals(3, reset($records)->course); 4464 $this->assertEquals(5, next($records)->course); 4465 // Only limitnum. 4466 $records = $DB->get_records_sql($sql, null, 0, 2); 4467 $this->assertCount(2, $records); 4468 $this->assertEquals(2, reset($records)->course); 4469 $this->assertEquals(3, next($records)->course); 4470 // Both limitfrom and limitnum. 4471 $records = $DB->get_records_sql($sql, null, 2, 2); 4472 $this->assertCount(1, $records); 4473 $this->assertEquals(5, reset($records)->course); 4474 4475 // We have sql like this in moodle, this syntax breaks on older versions of sqlite for example.. 4476 $sql = "SELECT a.id AS id, a.course AS course 4477 FROM {{$tablename}} a 4478 JOIN (SELECT * FROM {{$tablename}}) b ON a.id = b.id 4479 WHERE a.course = ?"; 4480 4481 $records = $DB->get_records_sql($sql, array(3)); 4482 $this->assertCount(2, $records); 4483 $this->assertEquals(1, reset($records)->id); 4484 $this->assertEquals(2, next($records)->id); 4485 4486 // Do NOT try embedding sql_xxxx() helper functions in conditions array of count_records(), they don't break params/binding! 4487 $count = $DB->count_records_select($tablename, "course = :course AND ".$DB->sql_compare_text('content')." = :content", array('course' => 3, 'content' => 'hello')); 4488 $this->assertEquals(1, $count); 4489 4490 // Test int x string comparison. 4491 $sql = "SELECT * 4492 FROM {{$tablename}} c 4493 WHERE name = ?"; 4494 $this->assertCount(0, $DB->get_records_sql($sql, array(10))); 4495 $this->assertCount(0, $DB->get_records_sql($sql, array("10"))); 4496 $DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1')); 4497 $DB->insert_record($tablename, array('course' => 7, 'content' => 'yy', 'name'=>'2')); 4498 $this->assertCount(1, $DB->get_records_sql($sql, array(1))); 4499 $this->assertCount(1, $DB->get_records_sql($sql, array("1"))); 4500 $this->assertCount(0, $DB->get_records_sql($sql, array(10))); 4501 $this->assertCount(0, $DB->get_records_sql($sql, array("10"))); 4502 $DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1abc')); 4503 $this->assertCount(1, $DB->get_records_sql($sql, array(1))); 4504 $this->assertCount(1, $DB->get_records_sql($sql, array("1"))); 4505 4506 // Test get_in_or_equal() with a big number of elements. Note that ideally 4507 // we should be detecting and warning about any use over, say, 200 elements 4508 // And recommend to change code to use subqueries and/or chunks instead. 4509 $currentcount = $DB->count_records($tablename); 4510 $numelements = 10000; // Verify that we can handle 10000 elements (crazy!) 4511 $values = range(1, $numelements); 4512 4513 list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM); // With QM params. 4514 $sql = "SELECT * 4515 FROM {{$tablename}} 4516 WHERE id $insql"; 4517 $results = $DB->get_records_sql($sql, $inparams); 4518 $this->assertCount($currentcount, $results); 4519 4520 list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED); // With NAMED params. 4521 $sql = "SELECT * 4522 FROM {{$tablename}} 4523 WHERE id $insql"; 4524 $results = $DB->get_records_sql($sql, $inparams); 4525 $this->assertCount($currentcount, $results); 4526 } 4527 4528 public function test_replace_all_text() { 4529 $DB = $this->tdb; 4530 $dbman = $DB->get_manager(); 4531 4532 if (!$DB->replace_all_text_supported()) { 4533 $this->markTestSkipped($DB->get_name().' does not support replacing of texts'); 4534 } 4535 4536 $table = $this->get_test_table(); 4537 $tablename = $table->getName(); 4538 4539 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4540 $table->add_field('name', XMLDB_TYPE_CHAR, '20', null, null); 4541 $table->add_field('intro', XMLDB_TYPE_TEXT, 'big', null, null); 4542 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4543 $dbman->create_table($table); 4544 4545 $id1 = (string)$DB->insert_record($tablename, array('name' => null, 'intro' => null)); 4546 $id2 = (string)$DB->insert_record($tablename, array('name' => '', 'intro' => '')); 4547 $id3 = (string)$DB->insert_record($tablename, array('name' => 'xxyy', 'intro' => 'vvzz')); 4548 $id4 = (string)$DB->insert_record($tablename, array('name' => 'aa bb aa bb', 'intro' => 'cc dd cc aa')); 4549 $id5 = (string)$DB->insert_record($tablename, array('name' => 'kkllll', 'intro' => 'kkllll')); 4550 4551 $expected = $DB->get_records($tablename, array(), 'id ASC'); 4552 4553 $columns = $DB->get_columns($tablename); 4554 4555 $DB->replace_all_text($tablename, $columns['name'], 'aa', 'o'); 4556 $result = $DB->get_records($tablename, array(), 'id ASC'); 4557 $expected[$id4]->name = 'o bb o bb'; 4558 $this->assertEquals($expected, $result); 4559 4560 $DB->replace_all_text($tablename, $columns['intro'], 'aa', 'o'); 4561 $result = $DB->get_records($tablename, array(), 'id ASC'); 4562 $expected[$id4]->intro = 'cc dd cc o'; 4563 $this->assertEquals($expected, $result); 4564 4565 $DB->replace_all_text($tablename, $columns['name'], '_', '*'); 4566 $DB->replace_all_text($tablename, $columns['name'], '?', '*'); 4567 $DB->replace_all_text($tablename, $columns['name'], '%', '*'); 4568 $DB->replace_all_text($tablename, $columns['intro'], '_', '*'); 4569 $DB->replace_all_text($tablename, $columns['intro'], '?', '*'); 4570 $DB->replace_all_text($tablename, $columns['intro'], '%', '*'); 4571 $result = $DB->get_records($tablename, array(), 'id ASC'); 4572 $this->assertEquals($expected, $result); 4573 4574 $long = '1234567890123456789'; 4575 $DB->replace_all_text($tablename, $columns['name'], 'kk', $long); 4576 $result = $DB->get_records($tablename, array(), 'id ASC'); 4577 $expected[$id5]->name = core_text::substr($long.'llll', 0, 20); 4578 $this->assertEquals($expected, $result); 4579 4580 $DB->replace_all_text($tablename, $columns['intro'], 'kk', $long); 4581 $result = $DB->get_records($tablename, array(), 'id ASC'); 4582 $expected[$id5]->intro = $long.'llll'; 4583 $this->assertEquals($expected, $result); 4584 } 4585 4586 public function test_onelevel_commit() { 4587 $DB = $this->tdb; 4588 $dbman = $DB->get_manager(); 4589 4590 $table = $this->get_test_table(); 4591 $tablename = $table->getName(); 4592 4593 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4594 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4595 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4596 $dbman->create_table($table); 4597 4598 $transaction = $DB->start_delegated_transaction(); 4599 $data = (object)array('course'=>3); 4600 $this->assertEquals(0, $DB->count_records($tablename)); 4601 $DB->insert_record($tablename, $data); 4602 $this->assertEquals(1, $DB->count_records($tablename)); 4603 $transaction->allow_commit(); 4604 $this->assertEquals(1, $DB->count_records($tablename)); 4605 } 4606 4607 public function test_transaction_ignore_error_trouble() { 4608 $DB = $this->tdb; 4609 $dbman = $DB->get_manager(); 4610 4611 $table = $this->get_test_table(); 4612 $tablename = $table->getName(); 4613 4614 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4615 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4616 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4617 $table->add_index('course', XMLDB_INDEX_UNIQUE, array('course')); 4618 $dbman->create_table($table); 4619 4620 // Test error on SQL_QUERY_INSERT. 4621 $transaction = $DB->start_delegated_transaction(); 4622 $this->assertEquals(0, $DB->count_records($tablename)); 4623 $DB->insert_record($tablename, (object)array('course'=>1)); 4624 $this->assertEquals(1, $DB->count_records($tablename)); 4625 try { 4626 $DB->insert_record($tablename, (object)array('course'=>1)); 4627 } catch (Exception $e) { 4628 // This must be ignored and it must not roll back the whole transaction. 4629 } 4630 $DB->insert_record($tablename, (object)array('course'=>2)); 4631 $this->assertEquals(2, $DB->count_records($tablename)); 4632 $transaction->allow_commit(); 4633 $this->assertEquals(2, $DB->count_records($tablename)); 4634 $this->assertFalse($DB->is_transaction_started()); 4635 4636 // Test error on SQL_QUERY_SELECT. 4637 $DB->delete_records($tablename); 4638 $transaction = $DB->start_delegated_transaction(); 4639 $this->assertEquals(0, $DB->count_records($tablename)); 4640 $DB->insert_record($tablename, (object)array('course'=>1)); 4641 $this->assertEquals(1, $DB->count_records($tablename)); 4642 try { 4643 $DB->get_records_sql('s e l e c t'); 4644 } catch (moodle_exception $e) { 4645 // This must be ignored and it must not roll back the whole transaction. 4646 } 4647 $DB->insert_record($tablename, (object)array('course'=>2)); 4648 $this->assertEquals(2, $DB->count_records($tablename)); 4649 $transaction->allow_commit(); 4650 $this->assertEquals(2, $DB->count_records($tablename)); 4651 $this->assertFalse($DB->is_transaction_started()); 4652 4653 // Test error on structure SQL_QUERY_UPDATE. 4654 $DB->delete_records($tablename); 4655 $transaction = $DB->start_delegated_transaction(); 4656 $this->assertEquals(0, $DB->count_records($tablename)); 4657 $DB->insert_record($tablename, (object)array('course'=>1)); 4658 $this->assertEquals(1, $DB->count_records($tablename)); 4659 try { 4660 $DB->execute('xxxx'); 4661 } catch (moodle_exception $e) { 4662 // This must be ignored and it must not roll back the whole transaction. 4663 } 4664 $DB->insert_record($tablename, (object)array('course'=>2)); 4665 $this->assertEquals(2, $DB->count_records($tablename)); 4666 $transaction->allow_commit(); 4667 $this->assertEquals(2, $DB->count_records($tablename)); 4668 $this->assertFalse($DB->is_transaction_started()); 4669 4670 // Test error on structure SQL_QUERY_STRUCTURE. 4671 $DB->delete_records($tablename); 4672 $transaction = $DB->start_delegated_transaction(); 4673 $this->assertEquals(0, $DB->count_records($tablename)); 4674 $DB->insert_record($tablename, (object)array('course'=>1)); 4675 $this->assertEquals(1, $DB->count_records($tablename)); 4676 try { 4677 $DB->change_database_structure('xxxx'); 4678 } catch (moodle_exception $e) { 4679 // This must be ignored and it must not roll back the whole transaction. 4680 } 4681 $DB->insert_record($tablename, (object)array('course'=>2)); 4682 $this->assertEquals(2, $DB->count_records($tablename)); 4683 $transaction->allow_commit(); 4684 $this->assertEquals(2, $DB->count_records($tablename)); 4685 $this->assertFalse($DB->is_transaction_started()); 4686 4687 // NOTE: SQL_QUERY_STRUCTURE is intentionally not tested here because it should never fail. 4688 } 4689 4690 public function test_onelevel_rollback() { 4691 $DB = $this->tdb; 4692 $dbman = $DB->get_manager(); 4693 4694 $table = $this->get_test_table(); 4695 $tablename = $table->getName(); 4696 4697 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4698 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4699 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4700 $dbman->create_table($table); 4701 4702 // This might in fact encourage ppl to migrate from myisam to innodb. 4703 4704 $transaction = $DB->start_delegated_transaction(); 4705 $data = (object)array('course'=>3); 4706 $this->assertEquals(0, $DB->count_records($tablename)); 4707 $DB->insert_record($tablename, $data); 4708 $this->assertEquals(1, $DB->count_records($tablename)); 4709 try { 4710 $transaction->rollback(new Exception('test')); 4711 $this->fail('transaction rollback must rethrow exception'); 4712 } catch (Exception $e) { 4713 // Ignored. 4714 } 4715 $this->assertEquals(0, $DB->count_records($tablename)); 4716 } 4717 4718 public function test_nested_transactions() { 4719 $DB = $this->tdb; 4720 $dbman = $DB->get_manager(); 4721 4722 $table = $this->get_test_table(); 4723 $tablename = $table->getName(); 4724 4725 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4726 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4727 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4728 $dbman->create_table($table); 4729 4730 // Two level commit. 4731 $this->assertFalse($DB->is_transaction_started()); 4732 $transaction1 = $DB->start_delegated_transaction(); 4733 $this->assertTrue($DB->is_transaction_started()); 4734 $data = (object)array('course'=>3); 4735 $DB->insert_record($tablename, $data); 4736 $transaction2 = $DB->start_delegated_transaction(); 4737 $data = (object)array('course'=>4); 4738 $DB->insert_record($tablename, $data); 4739 $transaction2->allow_commit(); 4740 $this->assertTrue($DB->is_transaction_started()); 4741 $transaction1->allow_commit(); 4742 $this->assertFalse($DB->is_transaction_started()); 4743 $this->assertEquals(2, $DB->count_records($tablename)); 4744 4745 $DB->delete_records($tablename); 4746 4747 // Rollback from top level. 4748 $transaction1 = $DB->start_delegated_transaction(); 4749 $data = (object)array('course'=>3); 4750 $DB->insert_record($tablename, $data); 4751 $transaction2 = $DB->start_delegated_transaction(); 4752 $data = (object)array('course'=>4); 4753 $DB->insert_record($tablename, $data); 4754 $transaction2->allow_commit(); 4755 try { 4756 $transaction1->rollback(new Exception('test')); 4757 $this->fail('transaction rollback must rethrow exception'); 4758 } catch (Exception $e) { 4759 $this->assertEquals(get_class($e), 'Exception'); 4760 } 4761 $this->assertEquals(0, $DB->count_records($tablename)); 4762 4763 $DB->delete_records($tablename); 4764 4765 // Rollback from nested level. 4766 $transaction1 = $DB->start_delegated_transaction(); 4767 $data = (object)array('course'=>3); 4768 $DB->insert_record($tablename, $data); 4769 $transaction2 = $DB->start_delegated_transaction(); 4770 $data = (object)array('course'=>4); 4771 $DB->insert_record($tablename, $data); 4772 try { 4773 $transaction2->rollback(new Exception('test')); 4774 $this->fail('transaction rollback must rethrow exception'); 4775 } catch (Exception $e) { 4776 $this->assertEquals(get_class($e), 'Exception'); 4777 } 4778 $this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet. 4779 try { 4780 $transaction1->allow_commit(); 4781 } catch (moodle_exception $e) { 4782 $this->assertInstanceOf('dml_transaction_exception', $e); 4783 } 4784 $this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet. 4785 // The forced rollback is done from the default_exception handler and similar places, 4786 // let's do it manually here. 4787 $this->assertTrue($DB->is_transaction_started()); 4788 $DB->force_transaction_rollback(); 4789 $this->assertFalse($DB->is_transaction_started()); 4790 $this->assertEquals(0, $DB->count_records($tablename)); // Finally rolled back. 4791 4792 $DB->delete_records($tablename); 4793 4794 // Test interactions of recordset and transactions - this causes problems in SQL Server. 4795 $table2 = $this->get_test_table('2'); 4796 $tablename2 = $table2->getName(); 4797 4798 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4799 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4800 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4801 $dbman->create_table($table2); 4802 4803 $DB->insert_record($tablename, array('course'=>1)); 4804 $DB->insert_record($tablename, array('course'=>2)); 4805 $DB->insert_record($tablename, array('course'=>3)); 4806 4807 $DB->insert_record($tablename2, array('course'=>5)); 4808 $DB->insert_record($tablename2, array('course'=>6)); 4809 $DB->insert_record($tablename2, array('course'=>7)); 4810 $DB->insert_record($tablename2, array('course'=>8)); 4811 4812 $rs1 = $DB->get_recordset($tablename); 4813 $i = 0; 4814 foreach ($rs1 as $record1) { 4815 $i++; 4816 $rs2 = $DB->get_recordset($tablename2); 4817 $j = 0; 4818 foreach ($rs2 as $record2) { 4819 $t = $DB->start_delegated_transaction(); 4820 $DB->set_field($tablename, 'course', $record1->course+1, array('id'=>$record1->id)); 4821 $DB->set_field($tablename2, 'course', $record2->course+1, array('id'=>$record2->id)); 4822 $t->allow_commit(); 4823 $j++; 4824 } 4825 $rs2->close(); 4826 $this->assertEquals(4, $j); 4827 } 4828 $rs1->close(); 4829 $this->assertEquals(3, $i); 4830 4831 // Test nested recordsets isolation without transaction. 4832 $DB->delete_records($tablename); 4833 $DB->insert_record($tablename, array('course'=>1)); 4834 $DB->insert_record($tablename, array('course'=>2)); 4835 $DB->insert_record($tablename, array('course'=>3)); 4836 4837 $DB->delete_records($tablename2); 4838 $DB->insert_record($tablename2, array('course'=>5)); 4839 $DB->insert_record($tablename2, array('course'=>6)); 4840 $DB->insert_record($tablename2, array('course'=>7)); 4841 $DB->insert_record($tablename2, array('course'=>8)); 4842 4843 $rs1 = $DB->get_recordset($tablename); 4844 $i = 0; 4845 foreach ($rs1 as $record1) { 4846 $i++; 4847 $rs2 = $DB->get_recordset($tablename2); 4848 $j = 0; 4849 foreach ($rs2 as $record2) { 4850 $DB->set_field($tablename, 'course', $record1->course+1, array('id'=>$record1->id)); 4851 $DB->set_field($tablename2, 'course', $record2->course+1, array('id'=>$record2->id)); 4852 $j++; 4853 } 4854 $rs2->close(); 4855 $this->assertEquals(4, $j); 4856 } 4857 $rs1->close(); 4858 $this->assertEquals(3, $i); 4859 } 4860 4861 public function test_transactions_forbidden() { 4862 $DB = $this->tdb; 4863 $dbman = $DB->get_manager(); 4864 4865 $table = $this->get_test_table(); 4866 $tablename = $table->getName(); 4867 4868 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4869 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4870 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4871 $dbman->create_table($table); 4872 4873 $DB->transactions_forbidden(); 4874 $transaction = $DB->start_delegated_transaction(); 4875 $data = (object)array('course'=>1); 4876 $DB->insert_record($tablename, $data); 4877 try { 4878 $DB->transactions_forbidden(); 4879 } catch (moodle_exception $e) { 4880 $this->assertInstanceOf('dml_transaction_exception', $e); 4881 } 4882 // The previous test does not force rollback. 4883 $transaction->allow_commit(); 4884 $this->assertFalse($DB->is_transaction_started()); 4885 $this->assertEquals(1, $DB->count_records($tablename)); 4886 } 4887 4888 public function test_wrong_transactions() { 4889 $DB = $this->tdb; 4890 $dbman = $DB->get_manager(); 4891 4892 $table = $this->get_test_table(); 4893 $tablename = $table->getName(); 4894 4895 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4896 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4897 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4898 $dbman->create_table($table); 4899 4900 // Wrong order of nested commits. 4901 $transaction1 = $DB->start_delegated_transaction(); 4902 $data = (object)array('course'=>3); 4903 $DB->insert_record($tablename, $data); 4904 $transaction2 = $DB->start_delegated_transaction(); 4905 $data = (object)array('course'=>4); 4906 $DB->insert_record($tablename, $data); 4907 try { 4908 $transaction1->allow_commit(); 4909 $this->fail('wrong order of commits must throw exception'); 4910 } catch (moodle_exception $e) { 4911 $this->assertInstanceOf('dml_transaction_exception', $e); 4912 } 4913 try { 4914 $transaction2->allow_commit(); 4915 $this->fail('first wrong commit forces rollback'); 4916 } catch (moodle_exception $e) { 4917 $this->assertInstanceOf('dml_transaction_exception', $e); 4918 } 4919 // This is done in default exception handler usually. 4920 $this->assertTrue($DB->is_transaction_started()); 4921 $this->assertEquals(2, $DB->count_records($tablename)); // Not rolled back yet. 4922 $DB->force_transaction_rollback(); 4923 $this->assertEquals(0, $DB->count_records($tablename)); 4924 $DB->delete_records($tablename); 4925 4926 // Wrong order of nested rollbacks. 4927 $transaction1 = $DB->start_delegated_transaction(); 4928 $data = (object)array('course'=>3); 4929 $DB->insert_record($tablename, $data); 4930 $transaction2 = $DB->start_delegated_transaction(); 4931 $data = (object)array('course'=>4); 4932 $DB->insert_record($tablename, $data); 4933 try { 4934 // This first rollback should prevent all other rollbacks. 4935 $transaction1->rollback(new Exception('test')); 4936 } catch (Exception $e) { 4937 $this->assertEquals(get_class($e), 'Exception'); 4938 } 4939 try { 4940 $transaction2->rollback(new Exception('test')); 4941 } catch (Exception $e) { 4942 $this->assertEquals(get_class($e), 'Exception'); 4943 } 4944 try { 4945 $transaction1->rollback(new Exception('test')); 4946 } catch (moodle_exception $e) { 4947 $this->assertInstanceOf('dml_transaction_exception', $e); 4948 } 4949 // This is done in default exception handler usually. 4950 $this->assertTrue($DB->is_transaction_started()); 4951 $DB->force_transaction_rollback(); 4952 $DB->delete_records($tablename); 4953 4954 // Unknown transaction object. 4955 $transaction1 = $DB->start_delegated_transaction(); 4956 $data = (object)array('course'=>3); 4957 $DB->insert_record($tablename, $data); 4958 $transaction2 = new moodle_transaction($DB); 4959 try { 4960 $transaction2->allow_commit(); 4961 $this->fail('foreign transaction must fail'); 4962 } catch (moodle_exception $e) { 4963 $this->assertInstanceOf('dml_transaction_exception', $e); 4964 } 4965 try { 4966 $transaction1->allow_commit(); 4967 $this->fail('first wrong commit forces rollback'); 4968 } catch (moodle_exception $e) { 4969 $this->assertInstanceOf('dml_transaction_exception', $e); 4970 } 4971 $DB->force_transaction_rollback(); 4972 $DB->delete_records($tablename); 4973 } 4974 4975 public function test_concurent_transactions() { 4976 // Notes about this test: 4977 // 1- MySQL needs to use one engine with transactions support (InnoDB). 4978 // 2- MSSQL needs to have enabled versioning for read committed 4979 // transactions (ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON) 4980 $DB = $this->tdb; 4981 $dbman = $DB->get_manager(); 4982 4983 $table = $this->get_test_table(); 4984 $tablename = $table->getName(); 4985 4986 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 4987 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 4988 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 4989 $dbman->create_table($table); 4990 4991 $transaction = $DB->start_delegated_transaction(); 4992 $data = (object)array('course'=>1); 4993 $this->assertEquals(0, $DB->count_records($tablename)); 4994 $DB->insert_record($tablename, $data); 4995 $this->assertEquals(1, $DB->count_records($tablename)); 4996 4997 // Open second connection. 4998 $cfg = $DB->export_dbconfig(); 4999 if (!isset($cfg->dboptions)) { 5000 $cfg->dboptions = array(); 5001 } 5002 $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary); 5003 $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions); 5004 5005 // Second instance should not see pending inserts. 5006 $this->assertEquals(0, $DB2->count_records($tablename)); 5007 $data = (object)array('course'=>2); 5008 $DB2->insert_record($tablename, $data); 5009 $this->assertEquals(1, $DB2->count_records($tablename)); 5010 5011 // First should see the changes done from second. 5012 $this->assertEquals(2, $DB->count_records($tablename)); 5013 5014 // Now commit and we should see it finally in second connections. 5015 $transaction->allow_commit(); 5016 $this->assertEquals(2, $DB2->count_records($tablename)); 5017 5018 // Let's try delete all is also working on (this checks MDL-29198). 5019 // Initially both connections see all the records in the table (2). 5020 $this->assertEquals(2, $DB->count_records($tablename)); 5021 $this->assertEquals(2, $DB2->count_records($tablename)); 5022 $transaction = $DB->start_delegated_transaction(); 5023 5024 // Delete all from within transaction. 5025 $DB->delete_records($tablename); 5026 5027 // Transactional $DB, sees 0 records now. 5028 $this->assertEquals(0, $DB->count_records($tablename)); 5029 5030 // Others ($DB2) get no changes yet. 5031 $this->assertEquals(2, $DB2->count_records($tablename)); 5032 5033 // Now commit and we should see changes. 5034 $transaction->allow_commit(); 5035 $this->assertEquals(0, $DB2->count_records($tablename)); 5036 5037 $DB2->dispose(); 5038 } 5039 5040 public function test_session_locks() { 5041 $DB = $this->tdb; 5042 $dbman = $DB->get_manager(); 5043 5044 // Open second connection. 5045 $cfg = $DB->export_dbconfig(); 5046 if (!isset($cfg->dboptions)) { 5047 $cfg->dboptions = array(); 5048 } 5049 $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary); 5050 $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions); 5051 5052 // Testing that acquiring a lock effectively locks. 5053 // Get a session lock on connection1. 5054 $rowid = rand(100, 200); 5055 $timeout = 1; 5056 $DB->get_session_lock($rowid, $timeout); 5057 5058 // Try to get the same session lock on connection2. 5059 try { 5060 $DB2->get_session_lock($rowid, $timeout); 5061 $DB2->release_session_lock($rowid); // Should not be executed, but here for safety. 5062 $this->fail('An Exception is missing, expected due to session lock acquired.'); 5063 } catch (moodle_exception $e) { 5064 $this->assertInstanceOf('dml_sessionwait_exception', $e); 5065 $DB->release_session_lock($rowid); // Release lock on connection1. 5066 } 5067 5068 // Testing that releasing a lock effectively frees. 5069 // Get a session lock on connection1. 5070 $rowid = rand(100, 200); 5071 $timeout = 1; 5072 $DB->get_session_lock($rowid, $timeout); 5073 // Release the lock on connection1. 5074 $DB->release_session_lock($rowid); 5075 5076 // Get the just released lock on connection2. 5077 $DB2->get_session_lock($rowid, $timeout); 5078 // Release the lock on connection2. 5079 $DB2->release_session_lock($rowid); 5080 5081 $DB2->dispose(); 5082 } 5083 5084 public function test_bound_param_types() { 5085 $DB = $this->tdb; 5086 $dbman = $DB->get_manager(); 5087 5088 $table = $this->get_test_table(); 5089 $tablename = $table->getName(); 5090 5091 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5092 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 5093 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL); 5094 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5095 $dbman->create_table($table); 5096 5097 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => '1', 'content'=>'xx'))); 5098 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 2, 'content'=>'yy'))); 5099 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'somestring', 'content'=>'zz'))); 5100 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'aa', 'content'=>'1'))); 5101 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'bb', 'content'=>2))); 5102 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'cc', 'content'=>'sometext'))); 5103 5104 // Conditions in CHAR columns. 5105 $this->assertTrue($DB->record_exists($tablename, array('name'=>1))); 5106 $this->assertTrue($DB->record_exists($tablename, array('name'=>'1'))); 5107 $this->assertFalse($DB->record_exists($tablename, array('name'=>111))); 5108 $this->assertNotEmpty($DB->get_record($tablename, array('name'=>1))); 5109 $this->assertNotEmpty($DB->get_record($tablename, array('name'=>'1'))); 5110 $this->assertEmpty($DB->get_record($tablename, array('name'=>111))); 5111 $sqlqm = "SELECT * 5112 FROM {{$tablename}} 5113 WHERE name = ?"; 5114 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array(1))); 5115 $this->assertCount(1, $records); 5116 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array('1'))); 5117 $this->assertCount(1, $records); 5118 $records = $DB->get_records_sql($sqlqm, array(222)); 5119 $this->assertCount(0, $records); 5120 $sqlnamed = "SELECT * 5121 FROM {{$tablename}} 5122 WHERE name = :name"; 5123 $this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('name' => 2))); 5124 $this->assertCount(1, $records); 5125 $this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('name' => '2'))); 5126 $this->assertCount(1, $records); 5127 5128 // Conditions in TEXT columns always must be performed with the sql_compare_text 5129 // helper function on both sides of the condition. 5130 $sqlqm = "SELECT * 5131 FROM {{$tablename}} 5132 WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text('?'); 5133 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array('1'))); 5134 $this->assertCount(1, $records); 5135 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, array(1))); 5136 $this->assertCount(1, $records); 5137 $sqlnamed = "SELECT * 5138 FROM {{$tablename}} 5139 WHERE " . $DB->sql_compare_text('content') . " = " . $DB->sql_compare_text(':content'); 5140 $this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('content' => 2))); 5141 $this->assertCount(1, $records); 5142 $this->assertNotEmpty($records = $DB->get_records_sql($sqlnamed, array('content' => '2'))); 5143 $this->assertCount(1, $records); 5144 } 5145 5146 public function test_bound_param_reserved() { 5147 $DB = $this->tdb; 5148 $dbman = $DB->get_manager(); 5149 5150 $table = $this->get_test_table(); 5151 $tablename = $table->getName(); 5152 5153 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5154 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 5155 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5156 $dbman->create_table($table); 5157 5158 $DB->insert_record($tablename, array('course' => '1')); 5159 5160 // Make sure reserved words do not cause fatal problems in query parameters. 5161 5162 $DB->execute("UPDATE {{$tablename}} SET course = 1 WHERE id = :select", array('select'=>1)); 5163 $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = :select", array('select'=>1)); 5164 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = :select", array('select'=>1)); 5165 $rs->close(); 5166 $DB->get_fieldset_sql("SELECT id FROM {{$tablename}} WHERE course = :select", array('select'=>1)); 5167 $DB->set_field_select($tablename, 'course', '1', "id = :select", array('select'=>1)); 5168 $DB->delete_records_select($tablename, "id = :select", array('select'=>1)); 5169 5170 // If we get here test passed ok. 5171 $this->assertTrue(true); 5172 } 5173 5174 public function test_limits_and_offsets() { 5175 $DB = $this->tdb; 5176 $dbman = $DB->get_manager(); 5177 5178 $table = $this->get_test_table(); 5179 $tablename = $table->getName(); 5180 5181 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5182 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); 5183 $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL); 5184 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5185 $dbman->create_table($table); 5186 5187 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one'))); 5188 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two'))); 5189 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three'))); 5190 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'd', 'content'=>'four'))); 5191 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'e', 'content'=>'five'))); 5192 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'f', 'content'=>'six'))); 5193 5194 $sqlqm = "SELECT * 5195 FROM {{$tablename}}"; 5196 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4)); 5197 $this->assertCount(2, $records); 5198 $this->assertSame('e', reset($records)->name); 5199 $this->assertSame('f', end($records)->name); 5200 5201 $sqlqm = "SELECT * 5202 FROM {{$tablename}}"; 5203 $this->assertEmpty($records = $DB->get_records_sql($sqlqm, null, 8)); 5204 5205 $sqlqm = "SELECT * 5206 FROM {{$tablename}}"; 5207 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 4)); 5208 $this->assertCount(4, $records); 5209 $this->assertSame('a', reset($records)->name); 5210 $this->assertSame('d', end($records)->name); 5211 5212 $sqlqm = "SELECT * 5213 FROM {{$tablename}}"; 5214 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8)); 5215 $this->assertCount(6, $records); 5216 $this->assertSame('a', reset($records)->name); 5217 $this->assertSame('f', end($records)->name); 5218 5219 $sqlqm = "SELECT * 5220 FROM {{$tablename}}"; 5221 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 1, 4)); 5222 $this->assertCount(4, $records); 5223 $this->assertSame('b', reset($records)->name); 5224 $this->assertSame('e', end($records)->name); 5225 5226 $sqlqm = "SELECT * 5227 FROM {{$tablename}}"; 5228 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); 5229 $this->assertCount(2, $records); 5230 $this->assertSame('e', reset($records)->name); 5231 $this->assertSame('f', end($records)->name); 5232 5233 $sqlqm = "SELECT t.*, t.name AS test 5234 FROM {{$tablename}} t 5235 ORDER BY t.id ASC"; 5236 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); 5237 $this->assertCount(2, $records); 5238 $this->assertSame('e', reset($records)->name); 5239 $this->assertSame('f', end($records)->name); 5240 5241 $sqlqm = "SELECT DISTINCT t.name, t.name AS test 5242 FROM {{$tablename}} t 5243 ORDER BY t.name DESC"; 5244 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); 5245 $this->assertCount(2, $records); 5246 $this->assertSame('b', reset($records)->name); 5247 $this->assertSame('a', end($records)->name); 5248 5249 $sqlqm = "SELECT 1 5250 FROM {{$tablename}} t 5251 WHERE t.name = 'a'"; 5252 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 1)); 5253 $this->assertCount(1, $records); 5254 5255 $sqlqm = "SELECT 'constant' 5256 FROM {{$tablename}} t 5257 WHERE t.name = 'a'"; 5258 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8)); 5259 $this->assertCount(1, $records); 5260 5261 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one'))); 5262 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two'))); 5263 $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three'))); 5264 5265 $sqlqm = "SELECT t.name, COUNT(DISTINCT t2.id) AS count, 'Test' AS teststring 5266 FROM {{$tablename}} t 5267 LEFT JOIN ( 5268 SELECT t.id, t.name 5269 FROM {{$tablename}} t 5270 ) t2 ON t2.name = t.name 5271 GROUP BY t.name 5272 ORDER BY t.name ASC"; 5273 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm)); 5274 $this->assertCount(6, $records); // a,b,c,d,e,f. 5275 $this->assertEquals(2, reset($records)->count); // a has 2 records now. 5276 $this->assertEquals(1, end($records)->count); // f has 1 record still. 5277 5278 $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 2)); 5279 $this->assertCount(2, $records); 5280 $this->assertEquals(2, reset($records)->count); 5281 $this->assertEquals(2, end($records)->count); 5282 } 5283 5284 /** 5285 * Test debugging messages about invalid limit number values. 5286 */ 5287 public function test_invalid_limits_debugging() { 5288 $DB = $this->tdb; 5289 $dbman = $DB->get_manager(); 5290 5291 // Setup test data. 5292 $table = $this->get_test_table(); 5293 $tablename = $table->getName(); 5294 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5295 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 5296 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5297 $dbman->create_table($table); 5298 $DB->insert_record($tablename, array('course' => '1')); 5299 5300 // Verify that get_records_sql throws debug notices with invalid limit params. 5301 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 'invalid'); 5302 $this->assertDebuggingCalled("Non-numeric limitfrom parameter detected: 'invalid', did you pass the correct arguments?"); 5303 5304 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, 'invalid'); 5305 $this->assertDebuggingCalled("Non-numeric limitnum parameter detected: 'invalid', did you pass the correct arguments?"); 5306 5307 // Verify that get_recordset_sql throws debug notices with invalid limit params. 5308 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}}", null, 'invalid'); 5309 $this->assertDebuggingCalled("Non-numeric limitfrom parameter detected: 'invalid', did you pass the correct arguments?"); 5310 $rs->close(); 5311 5312 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}}", null, 1, 'invalid'); 5313 $this->assertDebuggingCalled("Non-numeric limitnum parameter detected: 'invalid', did you pass the correct arguments?"); 5314 $rs->close(); 5315 5316 // Verify that some edge cases do no create debugging messages. 5317 // String form of integer values. 5318 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, '1'); 5319 $this->assertDebuggingNotCalled(); 5320 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, '2'); 5321 $this->assertDebuggingNotCalled(); 5322 // Empty strings. 5323 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, ''); 5324 $this->assertDebuggingNotCalled(); 5325 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, ''); 5326 $this->assertDebuggingNotCalled(); 5327 // Null values. 5328 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, null); 5329 $this->assertDebuggingNotCalled(); 5330 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, null); 5331 $this->assertDebuggingNotCalled(); 5332 5333 // Verify that empty arrays DO create debugging mesages. 5334 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, array()); 5335 $this->assertDebuggingCalled("Non-numeric limitfrom parameter detected: array (\n), did you pass the correct arguments?"); 5336 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, array()); 5337 $this->assertDebuggingCalled("Non-numeric limitnum parameter detected: array (\n), did you pass the correct arguments?"); 5338 5339 // Verify Negative number handling: 5340 // -1 is explicitly treated as 0 for historical reasons. 5341 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, -1); 5342 $this->assertDebuggingNotCalled(); 5343 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, -1); 5344 $this->assertDebuggingNotCalled(); 5345 // Any other negative values should throw debugging messages. 5346 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, -2); 5347 $this->assertDebuggingCalled("Negative limitfrom parameter detected: -2, did you pass the correct arguments?"); 5348 $DB->get_records_sql("SELECT * FROM {{$tablename}}", null, 1, -2); 5349 $this->assertDebuggingCalled("Negative limitnum parameter detected: -2, did you pass the correct arguments?"); 5350 } 5351 5352 public function test_queries_counter() { 5353 5354 $DB = $this->tdb; 5355 $dbman = $this->tdb->get_manager(); 5356 5357 // Test database. 5358 $table = $this->get_test_table(); 5359 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5360 $table->add_field('fieldvalue', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); 5361 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5362 5363 $dbman->create_table($table); 5364 $tablename = $table->getName(); 5365 5366 // Initial counters values. 5367 $initreads = $DB->perf_get_reads(); 5368 $initwrites = $DB->perf_get_writes(); 5369 $previousqueriestime = $DB->perf_get_queries_time(); 5370 5371 // Selects counts as reads. 5372 5373 // The get_records_sql() method generates only 1 db query. 5374 $whatever = $DB->get_records_sql("SELECT * FROM {{$tablename}}"); 5375 $this->assertEquals($initreads + 1, $DB->perf_get_reads()); 5376 5377 // The get_records() method generates 2 queries the first time is called 5378 // as it is fetching the table structure. 5379 $whatever = $DB->get_records($tablename, array('id' => '1')); 5380 $this->assertEquals($initreads + 3, $DB->perf_get_reads()); 5381 $this->assertEquals($initwrites, $DB->perf_get_writes()); 5382 5383 // The elapsed time is counted. 5384 $lastqueriestime = $DB->perf_get_queries_time(); 5385 $this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime); 5386 $previousqueriestime = $lastqueriestime; 5387 5388 // Only 1 now, it already fetched the table columns. 5389 $whatever = $DB->get_records($tablename); 5390 $this->assertEquals($initreads + 4, $DB->perf_get_reads()); 5391 5392 // And only 1 more from now. 5393 $whatever = $DB->get_records($tablename); 5394 $this->assertEquals($initreads + 5, $DB->perf_get_reads()); 5395 5396 // Inserts counts as writes. 5397 5398 $rec1 = new stdClass(); 5399 $rec1->fieldvalue = 11; 5400 $rec1->id = $DB->insert_record($tablename, $rec1); 5401 $this->assertEquals($initwrites + 1, $DB->perf_get_writes()); 5402 $this->assertEquals($initreads + 5, $DB->perf_get_reads()); 5403 5404 // The elapsed time is counted. 5405 $lastqueriestime = $DB->perf_get_queries_time(); 5406 $this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime); 5407 $previousqueriestime = $lastqueriestime; 5408 5409 $rec2 = new stdClass(); 5410 $rec2->fieldvalue = 22; 5411 $rec2->id = $DB->insert_record($tablename, $rec2); 5412 $this->assertEquals($initwrites + 2, $DB->perf_get_writes()); 5413 5414 // Updates counts as writes. 5415 5416 $rec1->fieldvalue = 111; 5417 $DB->update_record($tablename, $rec1); 5418 $this->assertEquals($initwrites + 3, $DB->perf_get_writes()); 5419 $this->assertEquals($initreads + 5, $DB->perf_get_reads()); 5420 5421 // The elapsed time is counted. 5422 $lastqueriestime = $DB->perf_get_queries_time(); 5423 $this->assertGreaterThanOrEqual($previousqueriestime, $lastqueriestime); 5424 $previousqueriestime = $lastqueriestime; 5425 5426 // Sum of them. 5427 $totaldbqueries = $DB->perf_get_reads() + $DB->perf_get_writes(); 5428 $this->assertEquals($totaldbqueries, $DB->perf_get_queries()); 5429 } 5430 5431 public function test_sql_intersect() { 5432 $DB = $this->tdb; 5433 $dbman = $this->tdb->get_manager(); 5434 5435 $tables = array(); 5436 for ($i = 0; $i < 3; $i++) { 5437 $table = $this->get_test_table('i'.$i); 5438 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); 5439 $table->add_field('ival', XMLDB_TYPE_INTEGER, '10', null, null, null, null); 5440 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); 5441 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); 5442 $dbman->create_table($table); 5443 $tables[$i] = $table; 5444 } 5445 $DB->insert_record($tables[0]->getName(), array('ival' => 1, 'name' => 'One'), false); 5446 $DB->insert_record($tables[0]->getName(), array('ival' => 2, 'name' => 'Two'), false); 5447 $DB->insert_record($tables[0]->getName(), array('ival' => 3, 'name' => 'Three'), false); 5448 $DB->insert_record($tables[0]->getName(), array('ival' => 4, 'name' => 'Four'), false); 5449 5450 $DB->insert_record($tables[1]->getName(), array('ival' => 1, 'name' => 'One'), false); 5451 $DB->insert_record($tables[1]->getName(), array('ival' => 2, 'name' => 'Two'), false); 5452 $DB->insert_record($tables[1]->getName(), array('ival' => 3, 'name' => 'Three'), false); 5453 5454 $DB->insert_record($tables[2]->getName(), array('ival' => 1, 'name' => 'One'), false); 5455 $DB->insert_record($tables[2]->getName(), array('ival' => 2, 'name' => 'Two'), false); 5456 $DB->insert_record($tables[2]->getName(), array('ival' => 5, 'name' => 'Five'), false); 5457 5458 // Intersection on the int column. 5459 $params = array('excludename' => 'Two'); 5460 $sql1 = 'SELECT ival FROM {'.$tables[0]->getName().'}'; 5461 $sql2 = 'SELECT ival FROM {'.$tables[1]->getName().'} WHERE name <> :excludename'; 5462 $sql3 = 'SELECT ival FROM {'.$tables[2]->getName().'}'; 5463 5464 $sql = $DB->sql_intersect(array($sql1), 'ival') . ' ORDER BY ival'; 5465 $this->assertEquals(array(1, 2, 3, 4), $DB->get_fieldset_sql($sql, $params)); 5466 5467 $sql = $DB->sql_intersect(array($sql1, $sql2), 'ival') . ' ORDER BY ival'; 5468 $this->assertEquals(array(1, 3), $DB->get_fieldset_sql($sql, $params)); 5469 5470 $sql = $DB->sql_intersect(array($sql1, $sql2, $sql3), 'ival') . ' ORDER BY ival'; 5471 $this->assertEquals(array(1), 5472 $DB->get_fieldset_sql($sql, $params)); 5473 5474 // Intersection on the char column. 5475 $params = array('excludeival' => 2); 5476 $sql1 = 'SELECT name FROM {'.$tables[0]->getName().'}'; 5477 $sql2 = 'SELECT name FROM {'.$tables[1]->getName().'} WHERE ival <> :excludeival'; 5478 $sql3 = 'SELECT name FROM {'.$tables[2]->getName().'}'; 5479 5480 $sql = $DB->sql_intersect(array($sql1), 'name') . ' ORDER BY name'; 5481 $this->assertEquals(array('Four', 'One', 'Three', 'Two'), $DB->get_fieldset_sql($sql, $params)); 5482 5483 $sql = $DB->sql_intersect(array($sql1, $sql2), 'name') . ' ORDER BY name'; 5484 $this->assertEquals(array('One', 'Three'), $DB->get_fieldset_sql($sql, $params)); 5485 5486 $sql = $DB->sql_intersect(array($sql1, $sql2, $sql3), 'name') . ' ORDER BY name'; 5487 $this->assertEquals(array('One'), $DB->get_fieldset_sql($sql, $params)); 5488 5489 // Intersection on the several columns. 5490 $params = array('excludename' => 'Two'); 5491 $sql1 = 'SELECT ival, name FROM {'.$tables[0]->getName().'}'; 5492 $sql2 = 'SELECT ival, name FROM {'.$tables[1]->getName().'} WHERE name <> :excludename'; 5493 $sql3 = 'SELECT ival, name FROM {'.$tables[2]->getName().'}'; 5494 5495 $sql = $DB->sql_intersect(array($sql1), 'ival, name') . ' ORDER BY ival'; 5496 $this->assertEquals(array(1 => 'One', 2 => 'Two', 3 => 'Three', 4 => 'Four'), 5497 $DB->get_records_sql_menu($sql, $params)); 5498 5499 $sql = $DB->sql_intersect(array($sql1, $sql2), 'ival, name') . ' ORDER BY ival'; 5500 $this->assertEquals(array(1 => 'One', 3 => 'Three'), 5501 $DB->get_records_sql_menu($sql, $params)); 5502 5503 $sql = $DB->sql_intersect(array($sql1, $sql2, $sql3), 'ival, name') . ' ORDER BY ival'; 5504 $this->assertEquals(array(1 => 'One'), 5505 $DB->get_records_sql_menu($sql, $params)); 5506 5507 // Drop temporary tables. 5508 foreach ($tables as $table) { 5509 $dbman->drop_table($table); 5510 } 5511 } 5512 } 5513 5514 /** 5515 * This class is not a proper subclass of moodle_database. It is 5516 * intended to be used only in unit tests, in order to gain access to the 5517 * protected methods of moodle_database, and unit test them. 5518 */ 5519 class moodle_database_for_testing extends moodle_database { 5520 protected $prefix = 'mdl_'; 5521 5522 public function public_fix_table_names($sql) { 5523 return $this->fix_table_names($sql); 5524 } 5525 5526 public function driver_installed() {} 5527 public function get_dbfamily() {} 5528 protected function get_dbtype() {} 5529 protected function get_dblibrary() {} 5530 public function get_name() {} 5531 public function get_configuration_help() {} 5532 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {} 5533 public function get_server_info() {} 5534 protected function allowed_param_types() {} 5535 public function get_last_error() {} 5536 public function get_tables($usecache=true) {} 5537 public function get_indexes($table) {} 5538 public function get_columns($table, $usecache=true) {} 5539 protected function normalise_value($column, $value) {} 5540 public function set_debug($state) {} 5541 public function get_debug() {} 5542 public function change_database_structure($sql, $tablenames = null) {} 5543 public function execute($sql, array $params=null) {} 5544 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {} 5545 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {} 5546 public function get_fieldset_sql($sql, array $params=null) {} 5547 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {} 5548 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {} 5549 public function import_record($table, $dataobject) {} 5550 public function update_record_raw($table, $params, $bulk=false) {} 5551 public function update_record($table, $dataobject, $bulk=false) {} 5552 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {} 5553 public function delete_records_select($table, $select, array $params=null) {} 5554 public function sql_concat() {} 5555 public function sql_concat_join($separator="' '", $elements=array()) {} 5556 public function sql_substr($expr, $start, $length=false) {} 5557 public function begin_transaction() {} 5558 public function commit_transaction() {} 5559 public function rollback_transaction() {} 5560 } 5561 5562 5563 /** 5564 * Dumb test class with toString() returning 1. 5565 */ 5566 class dml_test_object_one { 5567 public function __toString() { 5568 return 1; 5569 } 5570 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |