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