lib/dml/simpletest/testdml.php

   1 <?php
   2
   3 // This file is part of Moodle - http://moodle.org/
   4 //
   5 // Moodle is free software: you can redistribute it and/or modify
   6 // it under the terms of the GNU General Public License as published by
   7 // the Free Software Foundation, either version 3 of the License, or
   8 // (at your option) any later version.
   9 //
  10 // Moodle is distributed in the hope that it will be useful,
  11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 // GNU General Public License for more details.
  14 //
  15 // You should have received a copy of the GNU General Public License
  16 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  17
  18 /**
  19  * @package    core
  20  * @subpackage dml
  21  * @copyright  2008 Petr Skoda (http://skodak.org)
  22  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23  */
  24
  25 defined('MOODLE_INTERNAL') || die();
  26
  27 class dml_test extends UnitTestCase {
  28     private $tables = array();
  29     /** @var moodle_database */
  30     private $tdb;
  31     private $data;
  32     public  static $includecoverage = array('lib/dml');
  33     public  static $excludecoverage = array('lib/dml/simpletest');
  34
  35     protected $olddebug;
  36     protected $olddisplay;
  37
  38     function setUp() {
  39         global $DB, $UNITTEST;
  40
  41         if (isset($UNITTEST->func_test_db)) {
  42             $this->tdb = $UNITTEST->func_test_db;
  43         } else {
  44             $this->tdb = $DB;
  45         }
  46     }
  47
  48     function tearDown() {
  49         $dbman = $this->tdb->get_manager();
  50
  51         foreach ($this->tables as $tablename) {
  52             if ($dbman->table_exists($tablename)) {
  53                 $table = new xmldb_table($tablename);
  54                 $dbman->drop_table($table);
  55             }
  56         }
  57         $this->tables = array();
  58     }
  59
  60     /**
  61      * Get a xmldb_table object for testing, deleting any existing table
  62      * of the same name, for example if one was left over from a previous test
  63      * run that crashed.
  64      *
  65      * @param database_manager $dbman the database_manager to use.
  66      * @param string $suffix table name suffix, use if you need more test tables
  67      * @return xmldb_table the table object.
  68      */
  69     private function get_test_table($suffix = '') {
  70         $dbman = $this->tdb->get_manager();
  71
  72         $tablename = "unit_table";
  73         if ($suffix !== '') {
  74             $tablename .= $suffix;
  75         }
  76
  77         $table = new xmldb_table($tablename);
  78         if ($dbman->table_exists($table)) {
  79             $dbman->drop_table($table);
  80         }
  81         $table->setComment("This is a test'n drop table. You can drop it safely");
  82         $this->tables[$tablename] = $tablename;
  83         return new xmldb_table($tablename);
  84     }
  85
  86     protected function enable_debugging() {
  87         global $CFG;
  88
  89         $this->olddebug   = $CFG->debug;       // Save current debug settings
  90         $this->olddisplay = $CFG->debugdisplay;
  91         $CFG->debug = DEBUG_DEVELOPER;
  92         $CFG->debugdisplay = true;
  93         ob_start(); // hide debug warning
  94
  95     }
  96
  97     protected function get_debugging() {
  98         global $CFG;
  99
 100         $debuginfo = ob_get_contents();
 101         ob_end_clean();
 102         $CFG->debug = $this->olddebug;         // Restore original debug settings
 103         $CFG->debugdisplay = $this->olddisplay;
 104
 105         return $debuginfo;
 106     }
 107
 108     // NOTE: please keep order of test methods here matching the order of moodle_database class methods
 109
 110     function test_diagnose() {
 111         $DB = $this->tdb;
 112         $result = $DB->diagnose();
 113         $this->assertNull($result, 'Database self diagnostics failed %s');
 114     }
 115
 116     function test_get_server_info() {
 117         $DB = $this->tdb;
 118         $result = $DB->get_server_info();
 119         $this->assertTrue(is_array($result));
 120         $this->assertTrue(array_key_exists('description', $result));
 121         $this->assertTrue(array_key_exists('version', $result));
 122     }
 123
 124     public function test_get_in_or_equal() {
 125         $DB = $this->tdb;
 126
 127         // SQL_PARAMS_QM - IN or =
 128
 129         // Correct usage of multiple values
 130         $in_values = array('value1', 'value2', '3', 4, null, false, true);
 131         list($usql, $params) = $DB->get_in_or_equal($in_values);
 132         $this->assertEqual('IN ('.implode(',',array_fill(0, count($in_values), '?')).')', $usql);
 133         $this->assertEqual(count($in_values), count($params));
 134         foreach ($params as $key => $value) {
 135             $this->assertIdentical($in_values[$key], $value);
 136         }
 137
 138         // Correct usage of single value (in an array)
 139         $in_values = array('value1');
 140         list($usql, $params) = $DB->get_in_or_equal($in_values);
 141         $this->assertEqual("= ?", $usql);
 142         $this->assertEqual(1, count($params));
 143         $this->assertEqual($in_values[0], $params[0]);
 144
 145         // Correct usage of single value
 146         $in_value = 'value1';
 147         list($usql, $params) = $DB->get_in_or_equal($in_values);
 148         $this->assertEqual("= ?", $usql);
 149         $this->assertEqual(1, count($params));
 150         $this->assertEqual($in_value, $params[0]);
 151
 152         // SQL_PARAMS_QM - NOT IN or <>
 153
 154         // Correct usage of multiple values
 155         $in_values = array('value1', 'value2', 'value3', 'value4');
 156         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 157         $this->assertEqual("NOT IN (?,?,?,?)", $usql);
 158         $this->assertEqual(4, count($params));
 159         foreach ($params as $key => $value) {
 160             $this->assertEqual($in_values[$key], $value);
 161         }
 162
 163         // Correct usage of single value (in array()
 164         $in_values = array('value1');
 165         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 166         $this->assertEqual("<> ?", $usql);
 167         $this->assertEqual(1, count($params));
 168         $this->assertEqual($in_values[0], $params[0]);
 169
 170         // Correct usage of single value
 171         $in_value = 'value1';
 172         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
 173         $this->assertEqual("<> ?", $usql);
 174         $this->assertEqual(1, count($params));
 175         $this->assertEqual($in_value, $params[0]);
 176
 177         // SQL_PARAMS_NAMED - IN or =
 178
 179         // Correct usage of multiple values
 180         $in_values = array('value1', 'value2', 'value3', 'value4');
 181         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 182         $this->assertEqual(4, count($params));
 183         reset($in_values);
 184         $ps = array();
 185         foreach ($params as $key => $value) {
 186             $this->assertEqual(current($in_values), $value);
 187             next($in_values);
 188             $ps[] = ':'.$key;
 189         }
 190         $this->assertEqual("IN (".implode(',', $ps).")", $usql);
 191
 192         // Correct usage of single values (in array)
 193         $in_values = array('value1');
 194         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 195         $this->assertEqual(1, count($params));
 196         $value = reset($params);
 197         $key = key($params);
 198         $this->assertEqual("= :$key", $usql);
 199         $this->assertEqual($in_value, $value);
 200
 201         // Correct usage of single value
 202         $in_value = 'value1';
 203         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
 204         $this->assertEqual(1, count($params));
 205         $value = reset($params);
 206         $key = key($params);
 207         $this->assertEqual("= :$key", $usql);
 208         $this->assertEqual($in_value, $value);
 209
 210         // SQL_PARAMS_NAMED - NOT IN or <>
 211
 212         // Correct usage of multiple values
 213         $in_values = array('value1', 'value2', 'value3', 'value4');
 214         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 215         $this->assertEqual(4, count($params));
 216         reset($in_values);
 217         $ps = array();
 218         foreach ($params as $key => $value) {
 219             $this->assertEqual(current($in_values), $value);
 220             next($in_values);
 221             $ps[] = ':'.$key;
 222         }
 223         $this->assertEqual("NOT IN (".implode(',', $ps).")", $usql);
 224
 225         // Correct usage of single values (in array)
 226         $in_values = array('value1');
 227         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 228         $this->assertEqual(1, count($params));
 229         $value = reset($params);
 230         $key = key($params);
 231         $this->assertEqual("<> :$key", $usql);
 232         $this->assertEqual($in_value, $value);
 233
 234         // Correct usage of single value
 235         $in_value = 'value1';
 236         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 237         $this->assertEqual(1, count($params));
 238         $value = reset($params);
 239         $key = key($params);
 240         $this->assertEqual("<> :$key", $usql);
 241         $this->assertEqual($in_value, $value);
 242
 243         // make sure the param names are unique
 244         list($usql1, $params1) = $DB->get_in_or_equal(array(1,2,3), SQL_PARAMS_NAMED, 'param');
 245         list($usql2, $params2) = $DB->get_in_or_equal(array(1,2,3), SQL_PARAMS_NAMED, 'param');
 246         $params1 = array_keys($params1);
 247         $params2 = array_keys($params2);
 248         $common = array_intersect($params1, $params2);
 249         $this->assertEqual(count($common), 0);
 250
 251         // Some incorrect tests
 252
 253         // Incorrect usage passing not-allowed params type
 254         $in_values = array(1, 2, 3);
 255         try {
 256             list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);
 257             $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');
 258         } catch (exception $e) {
 259             $this->assertTrue($e instanceof dml_exception);
 260             $this->assertEqual($e->errorcode, 'typenotimplement');
 261         }
 262
 263         // Incorrect usage passing empty array
 264         $in_values = array();
 265         try {
 266             list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
 267             $this->fail('An Exception is missing, expected due to empty array of items');
 268         } catch (exception $e) {
 269             $this->assertTrue($e instanceof coding_exception);
 270         }
 271
 272         // Test using $onemptyitems
 273
 274         // Correct usage passing empty array and $onemptyitems = NULL (equal = true, QM)
 275         $in_values = array();
 276         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, NULL);
 277         $this->assertEqual(' IS NULL', $usql);
 278         $this->assertIdentical(array(), $params);
 279
 280         // Correct usage passing empty array and $onemptyitems = NULL (equal = false, NAMED)
 281         $in_values = array();
 282         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, NULL);
 283         $this->assertEqual(' IS NOT NULL', $usql);
 284         $this->assertIdentical(array(), $params);
 285
 286         // Correct usage passing empty array and $onemptyitems = true (equal = true, QM)
 287         $in_values = array();
 288         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);
 289         $this->assertEqual('= ?', $usql);
 290         $this->assertIdentical(array(true), $params);
 291
 292         // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED)
 293         $in_values = array();
 294         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);
 295         $this->assertEqual(1, count($params));
 296         $value = reset($params);
 297         $key = key($params);
 298         $this->assertEqual('<> :'.$key, $usql);
 299         $this->assertIdentical($value, true);
 300
 301         // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM)
 302         $in_values = array();
 303         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);
 304         $this->assertEqual('= ?', $usql);
 305         $this->assertIdentical(array(-1), $params);
 306
 307         // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED)
 308         $in_values = array();
 309         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);
 310         $this->assertEqual(1, count($params));
 311         $value = reset($params);
 312         $key = key($params);
 313         $this->assertEqual('<> :'.$key, $usql);
 314         $this->assertIdentical($value, -1);
 315
 316         // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM)
 317         $in_values = array();
 318         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');
 319         $this->assertEqual('= ?', $usql);
 320         $this->assertIdentical(array('onevalue'), $params);
 321
 322         // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED)
 323         $in_values = array();
 324         list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');
 325         $this->assertEqual(1, count($params));
 326         $value = reset($params);
 327         $key = key($params);
 328         $this->assertEqual('<> :'.$key, $usql);
 329         $this->assertIdentical($value, 'onevalue');
 330     }
 331
 332     public function test_fix_table_names() {
 333         $DB = new moodle_database_for_testing();
 334         $prefix = $DB->get_prefix();
 335
 336         // Simple placeholder
 337         $placeholder = "{user_123}";
 338         $this->assertIdentical($prefix."user_123", $DB->public_fix_table_names($placeholder));
 339
 340         // wrong table name
 341         $placeholder = "{user-a}";
 342         $this->assertIdentical($placeholder, $DB->public_fix_table_names($placeholder));
 343
 344         // wrong table name
 345         $placeholder = "{123user}";
 346         $this->assertIdentical($placeholder, $DB->public_fix_table_names($placeholder));
 347
 348         // Full SQL
 349         $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
 350         $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
 351         $this->assertIdentical($expected, $DB->public_fix_table_names($sql));
 352     }
 353
 354     function test_fix_sql_params() {
 355         $DB = $this->tdb;
 356
 357         $table = $this->get_test_table();
 358         $tablename = $table->getName();
 359
 360         // Correct table placeholder substitution
 361         $sql = "SELECT * FROM {{$tablename}}";
 362         $sqlarray = $DB->fix_sql_params($sql);
 363         $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
 364
 365         // Conversions of all param types
 366         $sql = array();
 367         $sql[SQL_PARAMS_NAMED]  = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
 368         $sql[SQL_PARAMS_QM]     = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
 369         $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
 370
 371         $params = array();
 372         $params[SQL_PARAMS_NAMED]  = array('param1'=>'first record', 'param2'=>1);
 373         $params[SQL_PARAMS_QM]     = array('first record', 1);
 374         $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
 375
 376         list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
 377         $this->assertIdentical($rsql, $sql[$rtype]);
 378         $this->assertIdentical($rparams, $params[$rtype]);
 379
 380         list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
 381         $this->assertIdentical($rsql, $sql[$rtype]);
 382         $this->assertIdentical($rparams, $params[$rtype]);
 383
 384         list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
 385         $this->assertIdentical($rsql, $sql[$rtype]);
 386         $this->assertIdentical($rparams, $params[$rtype]);
 387
 388
 389         // Malformed table placeholder
 390         $sql = "SELECT * FROM [testtable]";
 391         $sqlarray = $DB->fix_sql_params($sql);
 392         $this->assertIdentical($sql, $sqlarray[0]);
 393
 394
 395         // Mixed param types (colon and dollar)
 396         $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";
 397         $params = array('param1' => 'record1', 'param2' => 3);
 398         try {
 399             $DB->fix_sql_params($sql, $params);
 400             $this->fail("Expecting an exception, none occurred");
 401         } catch (Exception $e) {
 402             $this->assertTrue($e instanceof dml_exception);
 403         }
 404
 405         // Mixed param types (question and dollar)
 406         $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";
 407         $params = array('param1' => 'record2', 'param2' => 5);
 408         try {
 409             $DB->fix_sql_params($sql, $params);
 410             $this->fail("Expecting an exception, none occurred");
 411         } catch (Exception $e) {
 412             $this->assertTrue($e instanceof dml_exception);
 413         }
 414
 415         // Too few params in sql
 416         $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";
 417         $params = array('record2', 3);
 418         try {
 419             $DB->fix_sql_params($sql, $params);
 420             $this->fail("Expecting an exception, none occurred");
 421         } catch (Exception $e) {
 422             $this->assertTrue($e instanceof dml_exception);
 423         }
 424
 425         // Too many params in array: no error, just use what is necessary
 426         $params[] = 1;
 427         $params[] = time();
 428         try {
 429             $sqlarray = $DB->fix_sql_params($sql, $params);
 430             $this->assertTrue(is_array($sqlarray));
 431             $this->assertEqual(count($sqlarray[1]), 3);
 432         } catch (Exception $e) {
 433             $this->fail("Unexpected ".get_class($e)." exception");
 434         }
 435
 436         // Named params missing from array
 437         $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
 438         $params = array('wrongname' => 'record1', 'course' => 1);
 439         try {
 440             $DB->fix_sql_params($sql, $params);
 441             $this->fail("Expecting an exception, none occurred");
 442         } catch (Exception $e) {
 443             $this->assertTrue($e instanceof dml_exception);
 444         }
 445
 446         // Duplicate named param in query - this is a very important feature!!
 447         // it helps with debugging of sloppy code
 448         $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";
 449         $params = array('name' => 'record2', 'course' => 3);
 450         try {
 451             $DB->fix_sql_params($sql, $params);
 452             $this->fail("Expecting an exception, none occurred");
 453         } catch (Exception $e) {
 454             $this->assertTrue($e instanceof dml_exception);
 455         }
 456
 457         // Extra named param is ignored
 458         $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
 459         $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');
 460         try {
 461             $sqlarray = $DB->fix_sql_params($sql, $params);
 462             $this->assertTrue(is_array($sqlarray));
 463             $this->assertEqual(count($sqlarray[1]), 2);
 464         } catch (Exception $e) {
 465             $this->fail("Unexpected ".get_class($e)." exception");
 466         }
 467
 468         // Params exceeding 30 chars length
 469         $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
 470         $params = array('long_placeholder_with_more_than_30' => 'record1');
 471         try {
 472             $DB->fix_sql_params($sql, $params);
 473             $this->fail("Expecting an exception, none occurred");
 474         } catch (Exception $e) {
 475             $this->assertTrue($e instanceof coding_exception);
 476         }
 477
 478         // Booleans in NAMED params are casting to 1/0 int
 479         $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";
 480         $params = array(true, false);
 481         list($sql, $params) = $DB->fix_sql_params($sql, $params);
 482         $this->assertTrue(reset($params) === 1);
 483         $this->assertTrue(next($params) === 0);
 484
 485         // Booleans in QM params are casting to 1/0 int
 486         $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";
 487         $params = array('course1' => true, 'course2' => false);
 488         list($sql, $params) = $DB->fix_sql_params($sql, $params);
 489         $this->assertTrue(reset($params) === 1);
 490         $this->assertTrue(next($params) === 0);
 491
 492         // Booleans in DOLLAR params are casting to 1/0 int
 493         $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";
 494         $params = array(true, false);
 495         list($sql, $params) = $DB->fix_sql_params($sql, $params);
 496         $this->assertTrue(reset($params) === 1);
 497         $this->assertTrue(next($params) === 0);
 498
 499         // No data types are touched except bool
 500         $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";
 501         $inparams = array('abc', 'ABC', NULL, '1', 1, 1.4);
 502         list($sql, $params) = $DB->fix_sql_params($sql, $inparams);
 503         $this->assertIdentical(array_values($params), array_values($inparams));
 504     }
 505
 506     public function test_strtok() {
 507         // strtok was previously used by bound emulation, make sure it is not used any more
 508         $DB = $this->tdb;
 509         $dbman = $this->tdb->get_manager();
 510
 511         $table = $this->get_test_table();
 512         $tablename = $table->getName();
 513
 514         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 515         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 516         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
 517         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 518         $dbman->create_table($table);
 519
 520         $str = 'a?b?c?d';
 521         $this->assertIdentical(strtok($str, '?'), 'a');
 522
 523         $DB->get_records($tablename, array('id'=>1));
 524
 525         $this->assertIdentical(strtok('?'), 'b');
 526     }
 527
 528     public function test_tweak_param_names() {
 529         // Note the tweak_param_names() method is only available in the oracle driver,
 530         // hence we look for expected results indirectly, by testing various DML methods
 531         // with some "extreme" conditions causing the tweak to happen.
 532         $DB = $this->tdb;
 533         $dbman = $this->tdb->get_manager();
 534
 535         $table = $this->get_test_table();
 536         $tablename = $table->getName();
 537
 538         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 539         // Add some columns with 28 chars in the name
 540         $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
 541         $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
 542         $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
 543         // Add some columns with 30 chars in the name
 544         $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
 545         $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
 546         $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');
 547
 548         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 549
 550         $dbman->create_table($table);
 551
 552         $this->assertTrue($dbman->table_exists($tablename));
 553
 554         // Test insert record
 555         $rec1 = new stdClass();
 556         $rec1->long_int_columnname_with_28c = 28;
 557         $rec1->long_dec_columnname_with_28c = 28.28;
 558         $rec1->long_str_columnname_with_28c = '28';
 559         $rec1->long_int_columnname_with_30cxx = 30;
 560         $rec1->long_dec_columnname_with_30cxx = 30.30;
 561         $rec1->long_str_columnname_with_30cxx = '30';
 562
 563         // insert_record()
 564         $rec1->id = $DB->insert_record($tablename, $rec1);
 565         $this->assertEqual($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 566
 567         // update_record()
 568         $DB->update_record($tablename, $rec1);
 569         $this->assertEqual($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 570
 571         // set_field()
 572         $rec1->long_int_columnname_with_28c = 280;
 573         $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
 574             array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
 575         $rec1->long_dec_columnname_with_28c = 280.28;
 576         $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
 577             array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
 578         $rec1->long_str_columnname_with_28c = '280';
 579         $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
 580             array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
 581         $rec1->long_int_columnname_with_30cxx = 300;
 582         $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
 583             array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
 584         $rec1->long_dec_columnname_with_30cxx = 300.30;
 585         $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
 586             array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
 587         $rec1->long_str_columnname_with_30cxx = '300';
 588         $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
 589             array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
 590         $this->assertEqual($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
 591
 592         // delete_records()
 593         $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
 594         $rec2->id = $DB->insert_record($tablename, $rec2);
 595         $this->assertEqual(2, $DB->count_records($tablename));
 596         $DB->delete_records($tablename, (array) $rec2);
 597         $this->assertEqual(1, $DB->count_records($tablename));
 598
 599         // get_recordset()
 600         $rs = $DB->get_recordset($tablename, (array) $rec1);
 601         $iterations = 0;
 602         foreach ($rs as $rec2) {
 603             $iterations++;
 604         }
 605         $rs->close();
 606         $this->assertEqual(1, $iterations);
 607         $this->assertEqual($rec1, $rec2);
 608
 609         // get_records()
 610         $recs = $DB->get_records($tablename, (array) $rec1);
 611         $this->assertEqual(1, count($recs));
 612         $this->assertEqual($rec1, reset($recs));
 613
 614         // get_fieldset_select()
 615         $select = 'id = :id AND
 616                    long_int_columnname_with_28c = :long_int_columnname_with_28c AND
 617                    long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
 618                    long_str_columnname_with_28c = :long_str_columnname_with_28c AND
 619                    long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
 620                    long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
 621                    long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
 622         $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
 623         $this->assertEqual(1, count($fields));
 624         $this->assertEqual($rec1->long_int_columnname_with_28c, reset($fields));
 625         $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
 626         $this->assertEqual($rec1->long_dec_columnname_with_28c, reset($fields));
 627         $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
 628         $this->assertEqual($rec1->long_str_columnname_with_28c, reset($fields));
 629         $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
 630         $this->assertEqual($rec1->long_int_columnname_with_30cxx, reset($fields));
 631         $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
 632         $this->assertEqual($rec1->long_dec_columnname_with_30cxx, reset($fields));
 633         $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
 634         $this->assertEqual($rec1->long_str_columnname_with_30cxx, reset($fields));
 635
 636         // overlapping placeholders (progressive str_replace)
 637         $overlapselect = 'id = :p AND
 638                    long_int_columnname_with_28c = :param1 AND
 639                    long_dec_columnname_with_28c = :param2 AND
 640                    long_str_columnname_with_28c = :param_with_29_characters_long AND
 641                    long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
 642                    long_dec_columnname_with_30cxx = :param_ AND
 643                    long_str_columnname_with_30cxx = :param__';
 644         $overlapparams = array(
 645                 'p' => $rec1->id,
 646                 'param1' => $rec1->long_int_columnname_with_28c,
 647                 'param2' => $rec1->long_dec_columnname_with_28c,
 648                 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
 649                 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
 650                 'param_' => $rec1->long_dec_columnname_with_30cxx,
 651                 'param__' => $rec1->long_str_columnname_with_30cxx);
 652         $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
 653         $this->assertEqual(1, count($recs));
 654         $this->assertEqual($rec1, reset($recs));
 655
 656         // execute()
 657         $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
 658         $this->assertEqual(0, $DB->count_records($tablename));
 659     }
 660
 661     public function test_get_tables() {
 662         $DB = $this->tdb;
 663         $dbman = $this->tdb->get_manager();
 664
 665         // Need to test with multiple DBs
 666         $table = $this->get_test_table();
 667         $tablename = $table->getName();
 668
 669         $original_count = count($DB->get_tables());
 670
 671         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 672         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 673
 674         $dbman->create_table($table);
 675         $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
 676
 677         $dbman->drop_table($table);
 678         $this->assertTrue(count($DB->get_tables()) == $original_count);
 679     }
 680
 681     public function test_get_indexes() {
 682         $DB = $this->tdb;
 683         $dbman = $this->tdb->get_manager();
 684
 685         $table = $this->get_test_table();
 686         $tablename = $table->getName();
 687
 688         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 689         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 690         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 691         $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
 692         $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
 693         $dbman->create_table($table);
 694
 695         $indices = $DB->get_indexes($tablename);
 696         $this->assertTrue(is_array($indices));
 697         $this->assertEqual(count($indices), 2);
 698         // we do not care about index names for now
 699         $first = array_shift($indices);
 700         $second = array_shift($indices);
 701         if (count($first['columns']) == 2) {
 702             $composed = $first;
 703             $single   = $second;
 704         } else {
 705             $composed = $second;
 706             $single   = $first;
 707         }
 708         $this->assertFalse($single['unique']);
 709         $this->assertTrue($composed['unique']);
 710         $this->assertEqual(1, count($single['columns']));
 711         $this->assertEqual(2, count($composed['columns']));
 712         $this->assertEqual('course', $single['columns'][0]);
 713         $this->assertEqual('course', $composed['columns'][0]);
 714         $this->assertEqual('id', $composed['columns'][1]);
 715     }
 716
 717     public function test_get_columns() {
 718         $DB = $this->tdb;
 719         $dbman = $this->tdb->get_manager();
 720
 721         $table = $this->get_test_table();
 722         $tablename = $table->getName();
 723
 724         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 725         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 726         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
 727         $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
 728         $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
 729         $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
 730         $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300);
 731         $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);
 732         $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');
 733         $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');
 734         $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');
 735         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 736         $dbman->create_table($table);
 737
 738         $columns = $DB->get_columns($tablename);
 739         $this->assertTrue(is_array($columns));
 740
 741         $fields = $table->getFields();
 742         $this->assertEqual(count($columns), count($fields));
 743
 744         $field = $columns['id'];
 745         $this->assertEqual('R', $field->meta_type);
 746         $this->assertTrue($field->auto_increment);
 747         $this->assertTrue($field->unique);
 748
 749         $field = $columns['course'];
 750         $this->assertEqual('I', $field->meta_type);
 751         $this->assertFalse($field->auto_increment);
 752         $this->assertTrue($field->has_default);
 753         $this->assertEqual(0, $field->default_value);
 754         $this->assertTrue($field->not_null);
 755
 756         $field = $columns['name'];
 757         $this->assertEqual('C', $field->meta_type);
 758         $this->assertFalse($field->auto_increment);
 759         $this->assertEqual(255, $field->max_length);
 760         $this->assertTrue($field->has_default);
 761         $this->assertIdentical('lala', $field->default_value);
 762         $this->assertFalse($field->not_null);
 763
 764         $field = $columns['description'];
 765         $this->assertEqual('X', $field->meta_type);
 766         $this->assertFalse($field->auto_increment);
 767         $this->assertFalse($field->has_default);
 768         $this->assertIdentical(null, $field->default_value);
 769         $this->assertFalse($field->not_null);
 770
 771         $field = $columns['enumfield'];
 772         $this->assertEqual('C', $field->meta_type);
 773         $this->assertFalse($field->auto_increment);
 774         $this->assertIdentical('test2', $field->default_value);
 775         $this->assertTrue($field->not_null);
 776
 777         $field = $columns['onenum'];
 778         $this->assertEqual('N', $field->meta_type);
 779         $this->assertFalse($field->auto_increment);
 780         $this->assertEqual(10, $field->max_length);
 781         $this->assertEqual(2, $field->scale);
 782         $this->assertTrue($field->has_default);
 783         $this->assertEqual(200.0, $field->default_value);
 784         $this->assertFalse($field->not_null);
 785
 786         $field = $columns['onefloat'];
 787         $this->assertEqual('N', $field->meta_type);
 788         $this->assertFalse($field->auto_increment);
 789         $this->assertTrue($field->has_default);
 790         $this->assertEqual(300.0, $field->default_value);
 791         $this->assertFalse($field->not_null);
 792
 793         $field = $columns['anotherfloat'];
 794         $this->assertEqual('N', $field->meta_type);
 795         $this->assertFalse($field->auto_increment);
 796         $this->assertTrue($field->has_default);
 797         $this->assertEqual(400.0, $field->default_value);
 798         $this->assertFalse($field->not_null);
 799
 800         // Test negative defaults in numerical columns
 801         $field = $columns['negativedfltint'];
 802         $this->assertTrue($field->has_default);
 803         $this->assertEqual(-1, $field->default_value);
 804
 805         $field = $columns['negativedfltnumber'];
 806         $this->assertTrue($field->has_default);
 807         $this->assertEqual(-2, $field->default_value);
 808
 809         $field = $columns['negativedfltfloat'];
 810         $this->assertTrue($field->has_default);
 811         $this->assertEqual(-3, $field->default_value);
 812
 813         for ($i = 0; $i < count($columns); $i++) {
 814             if ($i == 0) {
 815                 $next_column = reset($columns);
 816                 $next_field  = reset($fields);
 817             } else {
 818                 $next_column = next($columns);
 819                 $next_field  = next($fields);
 820             }
 821
 822             $this->assertEqual($next_column->name, $next_field->name);
 823         }
 824
 825         // Test get_columns for non-existing table returns empty array. MDL-30147
 826         $columns = $DB->get_columns('xxxx');
 827         $this->assertEqual(array(), $columns);
 828     }
 829
 830     public function test_get_manager() {
 831         $DB = $this->tdb;
 832         $dbman = $this->tdb->get_manager();
 833
 834         $this->assertTrue($dbman instanceof database_manager);
 835     }
 836
 837     public function test_setup_is_unicodedb() {
 838         $DB = $this->tdb;
 839         $this->assertTrue($DB->setup_is_unicodedb());
 840     }
 841
 842     public function test_set_debug() { //tests get_debug() too
 843         $DB = $this->tdb;
 844         $dbman = $this->tdb->get_manager();
 845
 846         $table = $this->get_test_table();
 847         $tablename = $table->getName();
 848
 849         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 850         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 851         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 852         $dbman->create_table($table);
 853
 854         $sql = "SELECT * FROM {{$tablename}}";
 855
 856         $prevdebug = $DB->get_debug();
 857
 858         ob_start();
 859         $DB->set_debug(true);
 860         $this->assertTrue($DB->get_debug());
 861         $DB->execute($sql);
 862         $DB->set_debug(false);
 863         $this->assertFalse($DB->get_debug());
 864         $debuginfo = ob_get_contents();
 865         ob_end_clean();
 866         $this->assertFalse($debuginfo === '');
 867
 868         ob_start();
 869         $DB->execute($sql);
 870         $debuginfo = ob_get_contents();
 871         ob_end_clean();
 872         $this->assertTrue($debuginfo === '');
 873
 874         $DB->set_debug($prevdebug);
 875     }
 876
 877     public function test_execute() {
 878         $DB = $this->tdb;
 879         $dbman = $this->tdb->get_manager();
 880
 881         $table1 = $this->get_test_table('1');
 882         $tablename1 = $table1->getName();
 883         $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 884         $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 885         $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
 886         $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
 887         $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 888         $dbman->create_table($table1);
 889
 890         $table2 = $this->get_test_table('2');
 891         $tablename2 = $table2->getName();
 892         $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 893         $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 894         $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 895         $dbman->create_table($table2);
 896
 897         $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));
 898         $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));
 899         $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));
 900         $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));
 901
 902         // select results are ignored
 903         $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";
 904         $this->assertTrue($DB->execute($sql, array('course'=>3)));
 905
 906         // throw exception on error
 907         $sql = "XXUPDATE SET XSSD";
 908         try {
 909             $DB->execute($sql);
 910             $this->fail("Expecting an exception, none occurred");
 911         } catch (Exception $e) {
 912             $this->assertTrue($e instanceof dml_exception);
 913         }
 914
 915         // update records
 916         $sql = "UPDATE {{$tablename1}}
 917                    SET course = 6
 918                  WHERE course = ?";
 919         $this->assertTrue($DB->execute($sql, array('3')));
 920         $this->assertEqual($DB->count_records($tablename1, array('course' => 6)), 2);
 921
 922         // insert from one into second table
 923         $sql = "INSERT INTO {{$tablename2}} (course)
 924
 925                 SELECT course
 926                   FROM {{$tablename1}}";
 927         $this->assertTrue($DB->execute($sql));
 928         $this->assertEqual($DB->count_records($tablename2), 4);
 929     }
 930
 931     public function test_get_recordset() {
 932         $DB = $this->tdb;
 933         $dbman = $DB->get_manager();
 934
 935         $table = $this->get_test_table();
 936         $tablename = $table->getName();
 937
 938         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 939         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
 940         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
 941         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
 942         $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
 943         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 944         $dbman->create_table($table);
 945
 946         $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1', 'onetext'=>'abc'),
 947                       array('id' => 2, 'course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),
 948                       array('id' => 3, 'course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));
 949
 950         foreach ($data as $record) {
 951             $DB->insert_record($tablename, $record);
 952         }
 953
 954         // standard recordset iteration
 955         $rs = $DB->get_recordset($tablename);
 956         $this->assertTrue($rs instanceof moodle_recordset);
 957         reset($data);
 958         foreach($rs as $record) {
 959             $data_record = current($data);
 960             foreach ($record as $k => $v) {
 961                 $this->assertEqual($data_record[$k], $v);
 962             }
 963             next($data);
 964         }
 965         $rs->close();
 966
 967         // iterator style usage
 968         $rs = $DB->get_recordset($tablename);
 969         $this->assertTrue($rs instanceof moodle_recordset);
 970         reset($data);
 971         while ($rs->valid()) {
 972             $record = $rs->current();
 973             $data_record = current($data);
 974             foreach ($record as $k => $v) {
 975                 $this->assertEqual($data_record[$k], $v);
 976             }
 977             next($data);
 978             $rs->next();
 979         }
 980         $rs->close();
 981
 982         // make sure rewind is ignored
 983         $rs = $DB->get_recordset($tablename);
 984         $this->assertTrue($rs instanceof moodle_recordset);
 985         reset($data);
 986         $i = 0;
 987         foreach($rs as $record) {
 988             $i++;
 989             $rs->rewind();
 990             if ($i > 10) {
 991                 $this->fail('revind not ignored in recordsets');
 992                 break;
 993             }
 994             $data_record = current($data);
 995             foreach ($record as $k => $v) {
 996                 $this->assertEqual($data_record[$k], $v);
 997             }
 998             next($data);
 999         }
1000         $rs->close();
1001
1002         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1003         $conditions = array('onetext' => '1');
1004         try {
1005             $rs = $DB->get_recordset($tablename, $conditions);
1006             if (debugging()) {
1007                 // only in debug mode - hopefully all devs test code in debug mode...
1008                 $this->fail('An Exception is missing, expected due to equating of text fields');
1009             }
1010         } catch (exception $e) {
1011             $this->assertTrue($e instanceof dml_exception);
1012             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
1013         }
1014
1015         // notes:
1016         //  * limits are tested in test_get_recordset_sql()
1017         //  * where_clause() is used internally and is tested in test_get_records()
1018     }
1019
1020     public function test_get_recordset_iterator_keys() {
1021         $DB = $this->tdb;
1022         $dbman = $DB->get_manager();
1023
1024         $table = $this->get_test_table();
1025         $tablename = $table->getName();
1026
1027         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1028         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1029         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
1030         $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1031         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1032         $dbman->create_table($table);
1033
1034         $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
1035                       array('id'=> 2, 'course' => 3, 'name' => 'record2'),
1036                       array('id'=> 3, 'course' => 5, 'name' => 'record3'));
1037         foreach ($data as $record) {
1038             $DB->insert_record($tablename, $record);
1039         }
1040
1041         // Test repeated numeric keys are returned ok
1042         $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
1043
1044         reset($data);
1045         $count = 0;
1046         foreach($rs as $key => $record) {
1047             $data_record = current($data);
1048             $this->assertEqual($data_record['course'], $key);
1049             next($data);
1050             $count++;
1051         }
1052         $rs->close();
1053         $this->assertEqual($count, 3);
1054
1055         // Test string keys are returned ok
1056         $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
1057
1058         reset($data);
1059         $count = 0;
1060         foreach($rs as $key => $record) {
1061             $data_record = current($data);
1062             $this->assertEqual($data_record['name'], $key);
1063             next($data);
1064             $count++;
1065         }
1066         $rs->close();
1067         $this->assertEqual($count, 3);
1068
1069         // Test numeric not starting in 1 keys are returned ok
1070         $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
1071
1072         $data = array_reverse($data);
1073         reset($data);
1074         $count = 0;
1075         foreach($rs as $key => $record) {
1076             $data_record = current($data);
1077             $this->assertEqual($data_record['id'], $key);
1078             next($data);
1079             $count++;
1080         }
1081         $rs->close();
1082         $this->assertEqual($count, 3);
1083     }
1084
1085     public function test_get_recordset_list() {
1086         $DB = $this->tdb;
1087         $dbman = $DB->get_manager();
1088
1089         $table = $this->get_test_table();
1090         $tablename = $table->getName();
1091
1092         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1093         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1094         $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1095         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1096         $dbman->create_table($table);
1097
1098         $DB->insert_record($tablename, array('course' => 3));
1099         $DB->insert_record($tablename, array('course' => 3));
1100         $DB->insert_record($tablename, array('course' => 5));
1101         $DB->insert_record($tablename, array('course' => 2));
1102
1103         $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
1104
1105         $counter = 0;
1106         foreach ($rs as $record) {
1107             $counter++;
1108         }
1109         $this->assertEqual(3, $counter);
1110         $rs->close();
1111
1112         $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
1113
1114         $counter = 0;
1115         foreach ($rs as $record) {
1116             $counter++;
1117         }
1118         $rs->close();
1119         $this->assertEqual(0, $counter);
1120
1121         // notes:
1122         //  * limits are tested in test_get_recordset_sql()
1123         //  * where_clause() is used internally and is tested in test_get_records()
1124     }
1125
1126     public function test_get_recordset_select() {
1127         $DB = $this->tdb;
1128         $dbman = $DB->get_manager();
1129
1130         $table = $this->get_test_table();
1131         $tablename = $table->getName();
1132
1133         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1134         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1135         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1136         $dbman->create_table($table);
1137
1138         $DB->insert_record($tablename, array('course' => 3));
1139         $DB->insert_record($tablename, array('course' => 3));
1140         $DB->insert_record($tablename, array('course' => 5));
1141         $DB->insert_record($tablename, array('course' => 2));
1142
1143         $rs = $DB->get_recordset_select($tablename, '');
1144         $counter = 0;
1145         foreach ($rs as $record) {
1146             $counter++;
1147         }
1148         $rs->close();
1149         $this->assertEqual(4, $counter);
1150
1151         $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
1152         $counter = 0;
1153         foreach ($rs as $record) {
1154             $counter++;
1155         }
1156         $rs->close();
1157         $this->assertEqual(2, $counter);
1158
1159         // notes:
1160         //  * limits are tested in test_get_recordset_sql()
1161     }
1162
1163     public function test_get_recordset_sql() {
1164         $DB = $this->tdb;
1165         $dbman = $DB->get_manager();
1166
1167         $table = $this->get_test_table();
1168         $tablename = $table->getName();
1169
1170         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1171         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1172         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1173         $dbman->create_table($table);
1174
1175         $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1176         $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1177         $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1178         $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1179         $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1180         $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1181         $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1182
1183         $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1184         $counter = 0;
1185         foreach ($rs as $record) {
1186             $counter++;
1187         }
1188         $rs->close();
1189         $this->assertEqual(2, $counter);
1190
1191         // limits - only need to test this case, the rest have been tested by test_get_records_sql()
1192         // only limitfrom = skips that number of records
1193         $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1194         $records = array();
1195         foreach($rs as $key => $record) {
1196             $records[$key] = $record;
1197         }
1198         $rs->close();
1199         $this->assertEqual(5, count($records));
1200         $this->assertEqual($inskey3, reset($records)->id);
1201         $this->assertEqual($inskey7, end($records)->id);
1202
1203         // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here
1204     }
1205
1206     public function test_get_records() {
1207         $DB = $this->tdb;
1208         $dbman = $DB->get_manager();
1209
1210         $table = $this->get_test_table();
1211         $tablename = $table->getName();
1212
1213         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1214         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1215         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1216         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1217         $dbman->create_table($table);
1218
1219         $DB->insert_record($tablename, array('course' => 3));
1220         $DB->insert_record($tablename, array('course' => 3));
1221         $DB->insert_record($tablename, array('course' => 5));
1222         $DB->insert_record($tablename, array('course' => 2));
1223
1224         // All records
1225         $records = $DB->get_records($tablename);
1226         $this->assertEqual(4, count($records));
1227         $this->assertEqual(3, $records[1]->course);
1228         $this->assertEqual(3, $records[2]->course);
1229         $this->assertEqual(5, $records[3]->course);
1230         $this->assertEqual(2, $records[4]->course);
1231
1232         // Records matching certain conditions
1233         $records = $DB->get_records($tablename, array('course' => 3));
1234         $this->assertEqual(2, count($records));
1235         $this->assertEqual(3, $records[1]->course);
1236         $this->assertEqual(3, $records[2]->course);
1237
1238         // All records sorted by course
1239         $records = $DB->get_records($tablename, null, 'course');
1240         $this->assertEqual(4, count($records));
1241         $current_record = reset($records);
1242         $this->assertEqual(4, $current_record->id);
1243         $current_record = next($records);
1244         $this->assertEqual(1, $current_record->id);
1245         $current_record = next($records);
1246         $this->assertEqual(2, $current_record->id);
1247         $current_record = next($records);
1248         $this->assertEqual(3, $current_record->id);
1249
1250         // All records, but get only one field
1251         $records = $DB->get_records($tablename, null, '', 'id');
1252         $this->assertFalse(isset($records[1]->course));
1253         $this->assertTrue(isset($records[1]->id));
1254         $this->assertEqual(4, count($records));
1255
1256         // Booleans into params
1257         $records = $DB->get_records($tablename, array('course' => true));
1258         $this->assertEqual(0, count($records));
1259         $records = $DB->get_records($tablename, array('course' => false));
1260         $this->assertEqual(0, count($records));
1261
1262         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1263         $conditions = array('onetext' => '1');
1264         try {
1265             $records = $DB->get_records($tablename, $conditions);
1266             if (debugging()) {
1267                 // only in debug mode - hopefully all devs test code in debug mode...
1268                 $this->fail('An Exception is missing, expected due to equating of text fields');
1269             }
1270         } catch (exception $e) {
1271             $this->assertTrue($e instanceof dml_exception);
1272             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
1273         }
1274
1275         // test get_records passing non-existing table
1276         // with params
1277         try {
1278             $records = $DB->get_records('xxxx', array('id' => 0));
1279             $this->fail('An Exception is missing, expected due to query against non-existing table');
1280         } catch (exception $e) {
1281             $this->assertTrue($e instanceof dml_exception);
1282             if (debugging()) {
1283                 // information for developers only, normal users get general error message
1284                 $this->assertEqual($e->errorcode, 'ddltablenotexist');
1285             }
1286         }
1287         // and without params
1288         try {
1289             $records = $DB->get_records('xxxx', array());
1290             $this->fail('An Exception is missing, expected due to query against non-existing table');
1291         } catch (exception $e) {
1292             $this->assertTrue($e instanceof dml_exception);
1293             if (debugging()) {
1294                 // information for developers only, normal users get general error message
1295                 $this->assertEqual($e->errorcode, 'ddltablenotexist');
1296             }
1297         }
1298
1299         // test get_records passing non-existing column
1300         try {
1301             $records = $DB->get_records($tablename, array('xxxx' => 0));
1302             $this->fail('An Exception is missing, expected due to query against non-existing column');
1303         } catch (exception $e) {
1304             $this->assertTrue($e instanceof dml_exception);
1305             if (debugging()) {
1306                 // information for developers only, normal users get general error message
1307                 $this->assertEqual($e->errorcode, 'ddlfieldnotexist');
1308             }
1309         }
1310
1311         // note: delegate limits testing to test_get_records_sql()
1312     }
1313
1314     public function test_get_records_list() {
1315         $DB = $this->tdb;
1316         $dbman = $DB->get_manager();
1317
1318         $table = $this->get_test_table();
1319         $tablename = $table->getName();
1320
1321         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1322         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1323         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1324         $dbman->create_table($table);
1325
1326         $DB->insert_record($tablename, array('course' => 3));
1327         $DB->insert_record($tablename, array('course' => 3));
1328         $DB->insert_record($tablename, array('course' => 5));
1329         $DB->insert_record($tablename, array('course' => 2));
1330
1331         $records = $DB->get_records_list($tablename, 'course', array(3, 2));
1332         $this->assertTrue(is_array($records));
1333         $this->assertEqual(3, count($records));
1334         $this->assertEqual(1, reset($records)->id);
1335         $this->assertEqual(2, next($records)->id);
1336         $this->assertEqual(4, next($records)->id);
1337
1338         $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
1339         $this->assertEqual(0, count($records));
1340
1341         // note: delegate limits testing to test_get_records_sql()
1342     }
1343
1344     public function test_get_records_sql() {
1345         $DB = $this->tdb;
1346         $dbman = $DB->get_manager();
1347
1348         $table = $this->get_test_table();
1349         $tablename = $table->getName();
1350
1351         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1352         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1353         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1354         $dbman->create_table($table);
1355
1356         $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1357         $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1358         $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1359         $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1360         $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1361         $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1362         $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1363
1364         $table2 = $this->get_test_table("2");
1365         $tablename2 = $table2->getName();
1366         $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1367         $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1368         $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
1369         $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1370         $dbman->create_table($table2);
1371
1372         $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing'));
1373         $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang'));
1374         $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung'));
1375         $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));
1376
1377         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1378         $this->assertEqual(2, count($records));
1379         $this->assertEqual($inskey1, reset($records)->id);
1380         $this->assertEqual($inskey4, next($records)->id);
1381
1382         // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
1383         $this->enable_debugging();
1384         $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1385         $this->assertFalse($this->get_debugging() === '');
1386         $this->assertEqual(6, count($records));
1387
1388         // negative limits = no limits
1389         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
1390         $this->assertEqual(7, count($records));
1391
1392         // zero limits = no limits
1393         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
1394         $this->assertEqual(7, count($records));
1395
1396         // only limitfrom = skips that number of records
1397         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1398         $this->assertEqual(5, count($records));
1399         $this->assertEqual($inskey3, reset($records)->id);
1400         $this->assertEqual($inskey7, end($records)->id);
1401
1402         // only limitnum = fetches that number of records
1403         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
1404         $this->assertEqual(3, count($records));
1405         $this->assertEqual($inskey1, reset($records)->id);
1406         $this->assertEqual($inskey3, end($records)->id);
1407
1408         // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones
1409         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
1410         $this->assertEqual(2, count($records));
1411         $this->assertEqual($inskey4, reset($records)->id);
1412         $this->assertEqual($inskey5, end($records)->id);
1413
1414         // both limitfrom and limitnum in query having subqueris
1415         // note the subquery skips records with course = 0 and 3
1416         $sql = "SELECT * FROM {{$tablename}}
1417                  WHERE course NOT IN (
1418                      SELECT course FROM {{$tablename}}
1419                       WHERE course IN (0, 3))
1420                 ORDER BY course";
1421         $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2
1422         $this->assertEqual(2, count($records));
1423         $this->assertEqual($inskey6, reset($records)->id);
1424         $this->assertEqual($inskey5, end($records)->id);
1425         $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2
1426         $this->assertEqual(2, count($records));
1427         $this->assertEqual($inskey3, reset($records)->id);
1428         $this->assertEqual($inskey2, end($records)->id);
1429
1430         // test 2 tables with aliases and limits with order bys
1431         $sql = "SELECT t1.id, t1.course AS cid, t2.nametext
1432                   FROM {{$tablename}} t1, {{$tablename2}} t2
1433                  WHERE t2.course=t1.course
1434               ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');
1435         $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5
1436         $this->assertEqual(2, count($records));
1437         $this->assertEqual('5', end($records)->cid);
1438         $this->assertEqual('4', reset($records)->cid);
1439
1440         // test 2 tables with aliases and limits with the highest INT limit works
1441         $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}
1442         $this->assertEqual(2, count($records));
1443         $this->assertEqual('5', end($records)->cid);
1444         $this->assertEqual('4', reset($records)->cid);
1445
1446         // test 2 tables with aliases and limits with order bys (limit which is highest INT number)
1447         $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses
1448         $this->assertEqual(0, count($records));
1449
1450         // test 2 tables with aliases and limits with order bys (limit which s highest INT number)
1451         $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses
1452         $this->assertEqual(0, count($records));
1453
1454         // TODO: Test limits in queries having DISTINCT clauses
1455
1456         // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here
1457     }
1458
1459     public function test_get_records_menu() {
1460         $DB = $this->tdb;
1461         $dbman = $DB->get_manager();
1462
1463         $table = $this->get_test_table();
1464         $tablename = $table->getName();
1465
1466         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1467         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1468         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1469         $dbman->create_table($table);
1470
1471         $DB->insert_record($tablename, array('course' => 3));
1472         $DB->insert_record($tablename, array('course' => 3));
1473         $DB->insert_record($tablename, array('course' => 5));
1474         $DB->insert_record($tablename, array('course' => 2));
1475
1476         $records = $DB->get_records_menu($tablename, array('course' => 3));
1477         $this->assertTrue(is_array($records));
1478         $this->assertEqual(2, count($records));
1479         $this->assertFalse(empty($records[1]));
1480         $this->assertFalse(empty($records[2]));
1481         $this->assertEqual(3, $records[1]);
1482         $this->assertEqual(3, $records[2]);
1483
1484         // note: delegate limits testing to test_get_records_sql()
1485     }
1486
1487     public function test_get_records_select_menu() {
1488         $DB = $this->tdb;
1489         $dbman = $DB->get_manager();
1490
1491         $table = $this->get_test_table();
1492         $tablename = $table->getName();
1493
1494         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1495         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1496         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1497         $dbman->create_table($table);
1498
1499         $DB->insert_record($tablename, array('course' => 3));
1500         $DB->insert_record($tablename, array('course' => 2));
1501         $DB->insert_record($tablename, array('course' => 3));
1502         $DB->insert_record($tablename, array('course' => 5));
1503
1504         $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
1505         $this->assertTrue(is_array($records));
1506
1507         $this->assertEqual(3, count($records));
1508         $this->assertFalse(empty($records[1]));
1509         $this->assertTrue(empty($records[2]));
1510         $this->assertFalse(empty($records[3]));
1511         $this->assertFalse(empty($records[4]));
1512         $this->assertEqual(3, $records[1]);
1513         $this->assertEqual(3, $records[3]);
1514         $this->assertEqual(5, $records[4]);
1515
1516         // note: delegate limits testing to test_get_records_sql()
1517     }
1518
1519     public function test_get_records_sql_menu() {
1520         $DB = $this->tdb;
1521         $dbman = $DB->get_manager();
1522
1523         $table = $this->get_test_table();
1524         $tablename = $table->getName();
1525
1526         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1527         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1528         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1529         $dbman->create_table($table);
1530
1531         $DB->insert_record($tablename, array('course' => 3));
1532         $DB->insert_record($tablename, array('course' => 2));
1533         $DB->insert_record($tablename, array('course' => 3));
1534         $DB->insert_record($tablename, array('course' => 5));
1535
1536         $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
1537         $this->assertTrue(is_array($records));
1538
1539         $this->assertEqual(3, count($records));
1540         $this->assertFalse(empty($records[1]));
1541         $this->assertTrue(empty($records[2]));
1542         $this->assertFalse(empty($records[3]));
1543         $this->assertFalse(empty($records[4]));
1544         $this->assertEqual(3, $records[1]);
1545         $this->assertEqual(3, $records[3]);
1546         $this->assertEqual(5, $records[4]);
1547
1548         // note: delegate limits testing to test_get_records_sql()
1549     }
1550
1551     public function test_get_record() {
1552         $DB = $this->tdb;
1553         $dbman = $DB->get_manager();
1554
1555         $table = $this->get_test_table();
1556         $tablename = $table->getName();
1557
1558         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1559         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1560         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1561         $dbman->create_table($table);
1562
1563         $DB->insert_record($tablename, array('course' => 3));
1564         $DB->insert_record($tablename, array('course' => 2));
1565
1566         $record = $DB->get_record($tablename, array('id' => 2));
1567         $this->assertTrue($record instanceof stdClass);
1568
1569         $this->assertEqual(2, $record->course);
1570         $this->assertEqual(2, $record->id);
1571     }
1572
1573
1574     public function test_get_record_select() {
1575         $DB = $this->tdb;
1576         $dbman = $DB->get_manager();
1577
1578         $table = $this->get_test_table();
1579         $tablename = $table->getName();
1580
1581         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1582         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1583         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1584         $dbman->create_table($table);
1585
1586         $DB->insert_record($tablename, array('course' => 3));
1587         $DB->insert_record($tablename, array('course' => 2));
1588
1589         $record = $DB->get_record_select($tablename, "id = ?", array(2));
1590         $this->assertTrue($record instanceof stdClass);
1591
1592         $this->assertEqual(2, $record->course);
1593
1594         // note: delegates limit testing to test_get_records_sql()
1595     }
1596
1597     public function test_get_record_sql() {
1598         $DB = $this->tdb;
1599         $dbman = $DB->get_manager();
1600
1601         $table = $this->get_test_table();
1602         $tablename = $table->getName();
1603
1604         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1605         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1606         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1607         $dbman->create_table($table);
1608
1609         $DB->insert_record($tablename, array('course' => 3));
1610         $DB->insert_record($tablename, array('course' => 2));
1611
1612         // standard use
1613         $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
1614         $this->assertTrue($record instanceof stdClass);
1615         $this->assertEqual(2, $record->course);
1616         $this->assertEqual(2, $record->id);
1617
1618         // backwards compatibility with $ignoremultiple
1619         $this->assertFalse(IGNORE_MISSING);
1620         $this->assertTrue(IGNORE_MULTIPLE);
1621
1622         // record not found - ignore
1623         $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1624         $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1625
1626         // record not found error
1627         try {
1628             $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
1629             $this->fail("Exception expected");
1630         } catch (dml_missing_record_exception $e) {
1631             $this->assertTrue(true);
1632         }
1633
1634         $this->enable_debugging();
1635         $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1636         $this->assertFalse($this->get_debugging() === '');
1637
1638         // multiple matches ignored
1639         $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1640
1641         // multiple found error
1642         try {
1643             $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
1644             $this->fail("Exception expected");
1645         } catch (dml_multiple_records_exception $e) {
1646             $this->assertTrue(true);
1647         }
1648     }
1649
1650     public function test_get_field() {
1651         $DB = $this->tdb;
1652         $dbman = $DB->get_manager();
1653
1654         $table = $this->get_test_table();
1655         $tablename = $table->getName();
1656
1657         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1658         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1659         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1660         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1661         $dbman->create_table($table);
1662
1663         $id1 = $DB->insert_record($tablename, array('course' => 3));
1664         $DB->insert_record($tablename, array('course' => 5));
1665         $DB->insert_record($tablename, array('course' => 5));
1666
1667         $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
1668         $this->assertEqual(3, $DB->get_field($tablename, 'course', array('course' => 3)));
1669
1670         $this->assertIdentical(false, $DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
1671         try {
1672             $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
1673             $this->assertFail('Exception expected due to missing record');
1674         } catch (dml_exception $ex) {
1675             $this->assertTrue(true);
1676         }
1677
1678         $this->enable_debugging();
1679         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
1680         $this->assertIdentical($this->get_debugging(), '');
1681
1682         $this->enable_debugging();
1683         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
1684         $this->assertFalse($this->get_debugging() === '');
1685
1686         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1687         $conditions = array('onetext' => '1');
1688         try {
1689             $DB->get_field($tablename, 'course', $conditions);
1690             if (debugging()) {
1691                 // only in debug mode - hopefully all devs test code in debug mode...
1692                 $this->fail('An Exception is missing, expected due to equating of text fields');
1693             }
1694         } catch (exception $e) {
1695             $this->assertTrue($e instanceof dml_exception);
1696             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
1697         }
1698     }
1699
1700     public function test_get_field_select() {
1701         $DB = $this->tdb;
1702         $dbman = $DB->get_manager();
1703
1704         $table = $this->get_test_table();
1705         $tablename = $table->getName();
1706
1707         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1708         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1709         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1710         $dbman->create_table($table);
1711
1712         $DB->insert_record($tablename, array('course' => 3));
1713
1714         $this->assertEqual(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
1715     }
1716
1717     public function test_get_field_sql() {
1718         $DB = $this->tdb;
1719         $dbman = $DB->get_manager();
1720
1721         $table = $this->get_test_table();
1722         $tablename = $table->getName();
1723
1724         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1725         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1726         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1727         $dbman->create_table($table);
1728
1729         $DB->insert_record($tablename, array('course' => 3));
1730
1731         $this->assertEqual(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
1732     }
1733
1734     public function test_get_fieldset_select() {
1735         $DB = $this->tdb;
1736         $dbman = $DB->get_manager();
1737
1738         $table = $this->get_test_table();
1739         $tablename = $table->getName();
1740
1741         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1742         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1743         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1744         $dbman->create_table($table);
1745
1746         $DB->insert_record($tablename, array('course' => 1));
1747         $DB->insert_record($tablename, array('course' => 3));
1748         $DB->insert_record($tablename, array('course' => 2));
1749         $DB->insert_record($tablename, array('course' => 6));
1750
1751         $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
1752         $this->assertTrue(is_array($fieldset));
1753
1754         $this->assertEqual(3, count($fieldset));
1755         $this->assertEqual(3, $fieldset[0]);
1756         $this->assertEqual(2, $fieldset[1]);
1757         $this->assertEqual(6, $fieldset[2]);
1758     }
1759
1760     public function test_get_fieldset_sql() {
1761         $DB = $this->tdb;
1762         $dbman = $DB->get_manager();
1763
1764         $table = $this->get_test_table();
1765         $tablename = $table->getName();
1766
1767         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1768         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1769         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1770         $dbman->create_table($table);
1771
1772         $DB->insert_record($tablename, array('course' => 1));
1773         $DB->insert_record($tablename, array('course' => 3));
1774         $DB->insert_record($tablename, array('course' => 2));
1775         $DB->insert_record($tablename, array('course' => 6));
1776
1777         $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
1778         $this->assertTrue(is_array($fieldset));
1779
1780         $this->assertEqual(3, count($fieldset));
1781         $this->assertEqual(2, $fieldset[0]);
1782         $this->assertEqual(3, $fieldset[1]);
1783         $this->assertEqual(4, $fieldset[2]);
1784     }
1785
1786     public function test_insert_record_raw() {
1787         $DB = $this->tdb;
1788         $dbman = $DB->get_manager();
1789
1790         $table = $this->get_test_table();
1791         $tablename = $table->getName();
1792
1793         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1794         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1795         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1796         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1797         $dbman->create_table($table);
1798
1799         $record = (object)array('course' => 1, 'onechar' => 'xx');
1800         $before = clone($record);
1801         $result = $DB->insert_record_raw($tablename, $record);
1802         $this->assertIdentical(1, $result);
1803         $this->assertIdentical($record, $before);
1804
1805         $record = $DB->get_record($tablename, array('course' => 1));
1806         $this->assertTrue($record instanceof stdClass);
1807         $this->assertIdentical('xx', $record->onechar);
1808
1809         $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
1810         $this->assertIdentical(true, $result);
1811
1812         // note: bulk not implemented yet
1813         $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
1814         $record = $DB->get_record($tablename, array('course' => 3));
1815         $this->assertTrue($record instanceof stdClass);
1816         $this->assertIdentical('zz', $record->onechar);
1817
1818         // custom sequence (id) - returnid is ignored
1819         $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
1820         $this->assertIdentical(true, $result);
1821         $record = $DB->get_record($tablename, array('id' => 10));
1822         $this->assertTrue($record instanceof stdClass);
1823         $this->assertIdentical('bb', $record->onechar);
1824
1825         // custom sequence - missing id error
1826         try {
1827             $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
1828             $this->assertFail('Exception expected due to missing record');
1829         } catch (coding_exception $ex) {
1830             $this->assertTrue(true);
1831         }
1832
1833         // wrong column error
1834         try {
1835             $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
1836             $this->assertFail('Exception expected due to invalid column');
1837         } catch (dml_exception $ex) {
1838             $this->assertTrue(true);
1839         }
1840     }
1841
1842     public function test_insert_record() {
1843         // All the information in this test is fetched from DB by get_recordset() so we
1844         // have such method properly tested against nulls, empties and friends...
1845
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', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1853         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1854         $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1855         $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1856         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1857         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1858         $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
1859         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1860         $dbman->create_table($table);
1861
1862         $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true));
1863         $record = $DB->get_record($tablename, array('course' => 1));
1864         $this->assertEqual(1, $record->id);
1865         $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1866         $this->assertEqual(200, $record->onenum);
1867         $this->assertIdentical('onestring', $record->onechar);
1868         $this->assertNull($record->onetext);
1869         $this->assertNull($record->onebinary);
1870
1871         // without returning id, bulk not implemented
1872         $result = $this->assertIdentical(true, $DB->insert_record($tablename, array('course' => 99), false, true));
1873         $record = $DB->get_record($tablename, array('course' => 99));
1874         $this->assertEqual(2, $record->id);
1875         $this->assertEqual(99, $record->course);
1876
1877         // Check nulls are set properly for all types
1878         $record = new stdClass();
1879         $record->oneint = null;
1880         $record->onenum = null;
1881         $record->onechar = null;
1882         $record->onetext = null;
1883         $record->onebinary = null;
1884         $recid = $DB->insert_record($tablename, $record);
1885         $record = $DB->get_record($tablename, array('id' => $recid));
1886         $this->assertEqual(0, $record->course);
1887         $this->assertNull($record->oneint);
1888         $this->assertNull($record->onenum);
1889         $this->assertNull($record->onechar);
1890         $this->assertNull($record->onetext);
1891         $this->assertNull($record->onebinary);
1892
1893         // Check zeros are set properly for all types
1894         $record = new stdClass();
1895         $record->oneint = 0;
1896         $record->onenum = 0;
1897         $recid = $DB->insert_record($tablename, $record);
1898         $record = $DB->get_record($tablename, array('id' => $recid));
1899         $this->assertEqual(0, $record->oneint);
1900         $this->assertEqual(0, $record->onenum);
1901
1902         // Check booleans are set properly for all types
1903         $record = new stdClass();
1904         $record->oneint = true; // trues
1905         $record->onenum = true;
1906         $record->onechar = true;
1907         $record->onetext = true;
1908         $recid = $DB->insert_record($tablename, $record);
1909         $record = $DB->get_record($tablename, array('id' => $recid));
1910         $this->assertEqual(1, $record->oneint);
1911         $this->assertEqual(1, $record->onenum);
1912         $this->assertEqual(1, $record->onechar);
1913         $this->assertEqual(1, $record->onetext);
1914
1915         $record = new stdClass();
1916         $record->oneint = false; // falses
1917         $record->onenum = false;
1918         $record->onechar = false;
1919         $record->onetext = false;
1920         $recid = $DB->insert_record($tablename, $record);
1921         $record = $DB->get_record($tablename, array('id' => $recid));
1922         $this->assertEqual(0, $record->oneint);
1923         $this->assertEqual(0, $record->onenum);
1924         $this->assertEqual(0, $record->onechar);
1925         $this->assertEqual(0, $record->onetext);
1926
1927         // Check string data causes exception in numeric types
1928         $record = new stdClass();
1929         $record->oneint = 'onestring';
1930         $record->onenum = 0;
1931         try {
1932             $DB->insert_record($tablename, $record);
1933             $this->fail("Expecting an exception, none occurred");
1934         } catch (exception $e) {
1935             $this->assertTrue($e instanceof dml_exception);
1936         }
1937         $record = new stdClass();
1938         $record->oneint = 0;
1939         $record->onenum = 'onestring';
1940         try {
1941            $DB->insert_record($tablename, $record);
1942            $this->fail("Expecting an exception, none occurred");
1943         } catch (exception $e) {
1944             $this->assertTrue($e instanceof dml_exception);
1945         }
1946
1947         // Check empty string data is stored as 0 in numeric datatypes
1948         $record = new stdClass();
1949         $record->oneint = ''; // empty string
1950         $record->onenum = 0;
1951         $recid = $DB->insert_record($tablename, $record);
1952         $record = $DB->get_record($tablename, array('id' => $recid));
1953         $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1954
1955         $record = new stdClass();
1956         $record->oneint = 0;
1957         $record->onenum = ''; // empty string
1958         $recid = $DB->insert_record($tablename, $record);
1959         $record = $DB->get_record($tablename, array('id' => $recid));
1960         $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
1961
1962         // Check empty strings are set properly in string types
1963         $record = new stdClass();
1964         $record->oneint = 0;
1965         $record->onenum = 0;
1966         $record->onechar = '';
1967         $record->onetext = '';
1968         $recid = $DB->insert_record($tablename, $record);
1969         $record = $DB->get_record($tablename, array('id' => $recid));
1970         $this->assertTrue($record->onechar === '');
1971         $this->assertTrue($record->onetext === '');
1972
1973         // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1974         $record = new stdClass();
1975         $record->oneint = ((210.10 + 39.92) - 150.02);
1976         $record->onenum = ((210.10 + 39.92) - 150.02);
1977         $recid = $DB->insert_record($tablename, $record);
1978         $record = $DB->get_record($tablename, array('id' => $recid));
1979         $this->assertEqual(100, $record->oneint);
1980         $this->assertEqual(100, $record->onenum);
1981
1982         // Check various quotes/backslashes combinations in string types
1983         $teststrings = array(
1984             'backslashes and quotes alone (even): "" \'\' \\\\',
1985             'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1986             'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1987             'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1988         foreach ($teststrings as $teststring) {
1989             $record = new stdClass();
1990             $record->onechar = $teststring;
1991             $record->onetext = $teststring;
1992             $recid = $DB->insert_record($tablename, $record);
1993             $record = $DB->get_record($tablename, array('id' => $recid));
1994             $this->assertEqual($teststring, $record->onechar);
1995             $this->assertEqual($teststring, $record->onetext);
1996         }
1997
1998         // Check LOBs in text/binary columns
1999         $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2000         $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
2001         $record = new stdClass();
2002         $record->onetext = $clob;
2003         $record->onebinary = $blob;
2004         $recid = $DB->insert_record($tablename, $record);
2005         $rs = $DB->get_recordset($tablename, array('id' => $recid));
2006         $record = $rs->current();
2007         $rs->close();
2008         $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2009         $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2010
2011         // And "small" LOBs too, just in case
2012         $newclob = substr($clob, 0, 500);
2013         $newblob = substr($blob, 0, 250);
2014         $record = new stdClass();
2015         $record->onetext = $newclob;
2016         $record->onebinary = $newblob;
2017         $recid = $DB->insert_record($tablename, $record);
2018         $rs = $DB->get_recordset($tablename, array('id' => $recid));
2019         $record = $rs->current();
2020         $rs->close();
2021         $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2022         $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2023         $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
2024
2025         // And "diagnostic" LOBs too, just in case
2026         $newclob = '\'"\\;/ěščřžýáíé';
2027         $newblob = '\'"\\;/ěščřžýáíé';
2028         $record = new stdClass();
2029         $record->onetext = $newclob;
2030         $record->onebinary = $newblob;
2031         $recid = $DB->insert_record($tablename, $record);
2032         $rs = $DB->get_recordset($tablename, array('id' => $recid));
2033         $record = $rs->current();
2034         $rs->close();
2035         $this->assertIdentical($newclob, $record->onetext);
2036         $this->assertIdentical($newblob, $record->onebinary);
2037         $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
2038
2039         // test data is not modified
2040         $record = new stdClass();
2041         $record->id     = -1; // has to be ignored
2042         $record->course = 3;
2043         $record->lalala = 'lalal'; // unused
2044         $before = clone($record);
2045         $DB->insert_record($tablename, $record);
2046         $this->assertEqual($record, $before);
2047
2048         // make sure the id is always increasing and never reuses the same id
2049         $id1 = $DB->insert_record($tablename, array('course' => 3));
2050         $id2 = $DB->insert_record($tablename, array('course' => 3));
2051         $this->assertTrue($id1 < $id2);
2052         $DB->delete_records($tablename, array('id'=>$id2));
2053         $id3 = $DB->insert_record($tablename, array('course' => 3));
2054         $this->assertTrue($id2 < $id3);
2055         $DB->delete_records($tablename, array());
2056         $id4 = $DB->insert_record($tablename, array('course' => 3));
2057         $this->assertTrue($id3 < $id4);
2058
2059         // Test saving a float in a CHAR column, and reading it back.
2060         $id = $DB->insert_record($tablename, array('onechar' => 1.0));
2061         $this->assertEqual(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2062         $id = $DB->insert_record($tablename, array('onechar' => 1e20));
2063         $this->assertEqual(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2064         $id = $DB->insert_record($tablename, array('onechar' => 1e-4));
2065         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2066         $id = $DB->insert_record($tablename, array('onechar' => 1e-5));
2067         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2068         $id = $DB->insert_record($tablename, array('onechar' => 1e-300));
2069         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2070         $id = $DB->insert_record($tablename, array('onechar' => 1e300));
2071         $this->assertEqual(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2072
2073         // Test saving a float in a TEXT column, and reading it back.
2074         $id = $DB->insert_record($tablename, array('onetext' => 1.0));
2075         $this->assertEqual(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2076         $id = $DB->insert_record($tablename, array('onetext' => 1e20));
2077         $this->assertEqual(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2078         $id = $DB->insert_record($tablename, array('onetext' => 1e-4));
2079         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2080         $id = $DB->insert_record($tablename, array('onetext' => 1e-5));
2081         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2082         $id = $DB->insert_record($tablename, array('onetext' => 1e-300));
2083         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2084         $id = $DB->insert_record($tablename, array('onetext' => 1e300));
2085         $this->assertEqual(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2086     }
2087
2088     public function test_import_record() {
2089         // All the information in this test is fetched from DB by get_recordset() so we
2090         // have such method properly tested against nulls, empties and friends...
2091
2092         $DB = $this->tdb;
2093         $dbman = $DB->get_manager();
2094
2095         $table = $this->get_test_table();
2096         $tablename = $table->getName();
2097
2098         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2099         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2100         $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
2101         $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2102         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2103         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2104         $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2105         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2106         $dbman->create_table($table);
2107
2108         $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true));
2109         $record = $DB->get_record($tablename, array('course' => 1));
2110         $this->assertEqual(1, $record->id);
2111         $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
2112         $this->assertEqual(200, $record->onenum);
2113         $this->assertIdentical('onestring', $record->onechar);
2114         $this->assertNull($record->onetext);
2115         $this->assertNull($record->onebinary);
2116
2117         // ignore extra columns
2118         $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);
2119         $before = clone($record);
2120         $this->assertIdentical(true, $DB->import_record($tablename, $record));
2121         $this->assertIdentical($record, $before);
2122         $records = $DB->get_records($tablename);
2123         $this->assertEqual(2, $records[13]->course);
2124
2125         // Check nulls are set properly for all types
2126         $record = new stdClass();
2127         $record->id = 20;
2128         $record->oneint = null;
2129         $record->onenum = null;
2130         $record->onechar = null;
2131         $record->onetext = null;
2132         $record->onebinary = null;
2133         $this->assertTrue($DB->import_record($tablename, $record));
2134         $record = $DB->get_record($tablename, array('id' => 20));
2135         $this->assertEqual(0, $record->course);
2136         $this->assertNull($record->oneint);
2137         $this->assertNull($record->onenum);
2138         $this->assertNull($record->onechar);
2139         $this->assertNull($record->onetext);
2140         $this->assertNull($record->onebinary);
2141
2142         // Check zeros are set properly for all types
2143         $record = new stdClass();
2144         $record->id = 23;
2145         $record->oneint = 0;
2146         $record->onenum = 0;
2147         $this->assertTrue($DB->import_record($tablename, $record));
2148         $record = $DB->get_record($tablename, array('id' => 23));
2149         $this->assertEqual(0, $record->oneint);
2150         $this->assertEqual(0, $record->onenum);
2151
2152         // Check string data causes exception in numeric types
2153         $record = new stdClass();
2154         $record->id = 32;
2155         $record->oneint = 'onestring';
2156         $record->onenum = 0;
2157         try {
2158             $DB->import_record($tablename, $record);
2159             $this->fail("Expecting an exception, none occurred");
2160         } catch (exception $e) {
2161             $this->assertTrue($e instanceof dml_exception);
2162         }
2163         $record = new stdClass();
2164         $record->id = 35;
2165         $record->oneint = 0;
2166         $record->onenum = 'onestring';
2167         try {
2168            $DB->import_record($tablename, $record);
2169            $this->fail("Expecting an exception, none occurred");
2170         } catch (exception $e) {
2171             $this->assertTrue($e instanceof dml_exception);
2172         }
2173
2174         // Check empty strings are set properly in string types
2175         $record = new stdClass();
2176         $record->id = 44;
2177         $record->oneint = 0;
2178         $record->onenum = 0;
2179         $record->onechar = '';
2180         $record->onetext = '';
2181         $this->assertTrue($DB->import_record($tablename, $record));
2182         $record = $DB->get_record($tablename, array('id' => 44));
2183         $this->assertTrue($record->onechar === '');
2184         $this->assertTrue($record->onetext === '');
2185
2186         // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2187         $record = new stdClass();
2188         $record->id = 47;
2189         $record->oneint = ((210.10 + 39.92) - 150.02);
2190         $record->onenum = ((210.10 + 39.92) - 150.02);
2191         $this->assertTrue($DB->import_record($tablename, $record));
2192         $record = $DB->get_record($tablename, array('id' => 47));
2193         $this->assertEqual(100, $record->oneint);
2194         $this->assertEqual(100, $record->onenum);
2195
2196         // Check various quotes/backslashes combinations in string types
2197         $i = 50;
2198         $teststrings = array(
2199             'backslashes and quotes alone (even): "" \'\' \\\\',
2200             'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2201             'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2202             'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2203         foreach ($teststrings as $teststring) {
2204             $record = new stdClass();
2205             $record->id = $i;
2206             $record->onechar = $teststring;
2207             $record->onetext = $teststring;
2208             $this->assertTrue($DB->import_record($tablename, $record));
2209             $record = $DB->get_record($tablename, array('id' => $i));
2210             $this->assertEqual($teststring, $record->onechar);
2211             $this->assertEqual($teststring, $record->onetext);
2212             $i = $i + 3;
2213         }
2214
2215         // Check LOBs in text/binary columns
2216         $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2217         $record = new stdClass();
2218         $record->id = 70;
2219         $record->onetext = $clob;
2220         $record->onebinary = '';
2221         $this->assertTrue($DB->import_record($tablename, $record));
2222         $rs = $DB->get_recordset($tablename, array('id' => 70));
2223         $record = $rs->current();
2224         $rs->close();
2225         $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2226
2227         $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
2228         $record = new stdClass();
2229         $record->id = 71;
2230         $record->onetext = '';
2231         $record->onebinary = $blob;
2232         $this->assertTrue($DB->import_record($tablename, $record));
2233         $rs = $DB->get_recordset($tablename, array('id' => 71));
2234         $record = $rs->current();
2235         $rs->close();
2236         $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2237
2238         // And "small" LOBs too, just in case
2239         $newclob = substr($clob, 0, 500);
2240         $newblob = substr($blob, 0, 250);
2241         $record = new stdClass();
2242         $record->id = 73;
2243         $record->onetext = $newclob;
2244         $record->onebinary = $newblob;
2245         $this->assertTrue($DB->import_record($tablename, $record));
2246         $rs = $DB->get_recordset($tablename, array('id' => 73));
2247         $record = $rs->current();
2248         $rs->close();
2249         $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2250         $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2251         $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
2252     }
2253
2254     public function test_update_record_raw() {
2255         $DB = $this->tdb;
2256         $dbman = $DB->get_manager();
2257
2258         $table = $this->get_test_table();
2259         $tablename = $table->getName();
2260
2261         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2262         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2263         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2264         $dbman->create_table($table);
2265
2266         $DB->insert_record($tablename, array('course' => 1));
2267         $DB->insert_record($tablename, array('course' => 3));
2268
2269         $record = $DB->get_record($tablename, array('course' => 1));
2270         $record->course = 2;
2271         $this->assertTrue($DB->update_record_raw($tablename, $record));
2272         $this->assertEqual(0, $DB->count_records($tablename, array('course' => 1)));
2273         $this->assertEqual(1, $DB->count_records($tablename, array('course' => 2)));
2274         $this->assertEqual(1, $DB->count_records($tablename, array('course' => 3)));
2275
2276         $record = $DB->get_record($tablename, array('course' => 1));
2277         $record->xxxxx = 2;
2278         try {
2279            $DB->update_record_raw($tablename, $record);
2280            $this->fail("Expecting an exception, none occurred");
2281         } catch (Exception $e) {
2282             $this->assertTrue($e instanceof coding_exception);
2283         }
2284
2285         $record = $DB->get_record($tablename, array('course' => 3));
2286         unset($record->id);
2287         try {
2288            $DB->update_record_raw($tablename, $record);
2289            $this->fail("Expecting an exception, none occurred");
2290         } catch (Exception $e) {
2291             $this->assertTrue($e instanceof coding_exception);
2292         }
2293     }
2294
2295     public function test_update_record() {
2296
2297         // All the information in this test is fetched from DB by get_record() so we
2298         // have such method properly tested against nulls, empties and friends...
2299
2300         $DB = $this->tdb;
2301         $dbman = $DB->get_manager();
2302
2303         $table = $this->get_test_table();
2304         $tablename = $table->getName();
2305
2306         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2307         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2308         $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
2309         $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2310         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2311         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2312         $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2313         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2314         $dbman->create_table($table);
2315
2316         $DB->insert_record($tablename, array('course' => 1));
2317         $record = $DB->get_record($tablename, array('course' => 1));
2318         $record->course = 2;
2319
2320         $this->assertTrue($DB->update_record($tablename, $record));
2321         $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
2322         $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
2323         $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
2324         $this->assertEqual(200, $record->onenum);
2325         $this->assertEqual('onestring', $record->onechar);
2326         $this->assertNull($record->onetext);
2327         $this->assertNull($record->onebinary);
2328
2329         // Check nulls are set properly for all types
2330         $record->oneint = null;
2331         $record->onenum = null;
2332         $record->onechar = null;
2333         $record->onetext = null;
2334         $record->onebinary = null;
2335         $DB->update_record($tablename, $record);
2336         $record = $DB->get_record($tablename, array('course' => 2));
2337         $this->assertNull($record->oneint);
2338         $this->assertNull($record->onenum);
2339         $this->assertNull($record->onechar);
2340         $this->assertNull($record->onetext);
2341         $this->assertNull($record->onebinary);
2342
2343         // Check zeros are set properly for all types
2344         $record->oneint = 0;
2345         $record->onenum = 0;
2346         $DB->update_record($tablename, $record);
2347         $record = $DB->get_record($tablename, array('course' => 2));
2348         $this->assertEqual(0, $record->oneint);
2349         $this->assertEqual(0, $record->onenum);
2350
2351         // Check booleans are set properly for all types
2352         $record->oneint = true; // trues
2353         $record->onenum = true;
2354         $record->onechar = true;
2355         $record->onetext = true;
2356         $DB->update_record($tablename, $record);
2357         $record = $DB->get_record($tablename, array('course' => 2));
2358         $this->assertEqual(1, $record->oneint);
2359         $this->assertEqual(1, $record->onenum);
2360         $this->assertEqual(1, $record->onechar);
2361         $this->assertEqual(1, $record->onetext);
2362
2363         $record->oneint = false; // falses
2364         $record->onenum = false;
2365         $record->onechar = false;
2366         $record->onetext = false;
2367         $DB->update_record($tablename, $record);
2368         $record = $DB->get_record($tablename, array('course' => 2));
2369         $this->assertEqual(0, $record->oneint);
2370         $this->assertEqual(0, $record->onenum);
2371         $this->assertEqual(0, $record->onechar);
2372         $this->assertEqual(0, $record->onetext);
2373
2374         // Check string data causes exception in numeric types
2375         $record->oneint = 'onestring';
2376         $record->onenum = 0;
2377         try {
2378             $DB->update_record($tablename, $record);
2379             $this->fail("Expecting an exception, none occurred");
2380         } catch (exception $e) {
2381             $this->assertTrue($e instanceof dml_exception);
2382         }
2383         $record->oneint = 0;
2384         $record->onenum = 'onestring';
2385         try {
2386             $DB->update_record($tablename, $record);
2387             $this->fail("Expecting an exception, none occurred");
2388         } catch (exception $e) {
2389             $this->assertTrue($e instanceof dml_exception);
2390         }
2391
2392         // Check empty string data is stored as 0 in numeric datatypes
2393         $record->oneint = ''; // empty string
2394         $record->onenum = 0;
2395         $DB->update_record($tablename, $record);
2396         $record = $DB->get_record($tablename, array('course' => 2));
2397         $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2398
2399         $record->oneint = 0;
2400         $record->onenum = ''; // empty string
2401         $DB->update_record($tablename, $record);
2402         $record = $DB->get_record($tablename, array('course' => 2));
2403         $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2404
2405         // Check empty strings are set properly in string types
2406         $record->oneint = 0;
2407         $record->onenum = 0;
2408         $record->onechar = '';
2409         $record->onetext = '';
2410         $DB->update_record($tablename, $record);
2411         $record = $DB->get_record($tablename, array('course' => 2));
2412         $this->assertTrue($record->onechar === '');
2413         $this->assertTrue($record->onetext === '');
2414
2415         // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2416         $record->oneint = ((210.10 + 39.92) - 150.02);
2417         $record->onenum = ((210.10 + 39.92) - 150.02);
2418         $DB->update_record($tablename, $record);
2419         $record = $DB->get_record($tablename, array('course' => 2));
2420         $this->assertEqual(100, $record->oneint);
2421         $this->assertEqual(100, $record->onenum);
2422
2423         // Check various quotes/backslashes combinations in string types
2424         $teststrings = array(
2425             'backslashes and quotes alone (even): "" \'\' \\\\',
2426             'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2427             'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2428             'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2429         foreach ($teststrings as $teststring) {
2430             $record->onechar = $teststring;
2431             $record->onetext = $teststring;
2432             $DB->update_record($tablename, $record);
2433             $record = $DB->get_record($tablename, array('course' => 2));
2434             $this->assertEqual($teststring, $record->onechar);
2435             $this->assertEqual($teststring, $record->onetext);
2436         }
2437
2438         // Check LOBs in text/binary columns
2439         $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2440         $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
2441         $record->onetext = $clob;
2442         $record->onebinary = $blob;
2443         $DB->update_record($tablename, $record);
2444         $record = $DB->get_record($tablename, array('course' => 2));
2445         $this->assertEqual($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
2446         $this->assertEqual($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
2447
2448         // And "small" LOBs too, just in case
2449         $newclob = substr($clob, 0, 500);
2450         $newblob = substr($blob, 0, 250);
2451         $record->onetext = $newclob;
2452         $record->onebinary = $newblob;
2453         $DB->update_record($tablename, $record);
2454         $record = $DB->get_record($tablename, array('course' => 2));
2455         $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
2456         $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
2457
2458         // Test saving a float in a CHAR column, and reading it back.
2459         $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2460         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1.0));
2461         $this->assertEqual(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2462         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e20));
2463         $this->assertEqual(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2464         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-4));
2465         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2466         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-5));
2467         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2468         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-300));
2469         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2470         $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e300));
2471         $this->assertEqual(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2472
2473         // Test saving a float in a TEXT column, and reading it back.
2474         $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2475         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1.0));
2476         $this->assertEqual(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2477         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e20));
2478         $this->assertEqual(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2479         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-4));
2480         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2481         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-5));
2482         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2483         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-300));
2484         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2485         $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e300));
2486         $this->assertEqual(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2487     }
2488
2489     public function test_set_field() {
2490         $DB = $this->tdb;
2491         $dbman = $DB->get_manager();
2492
2493         $table = $this->get_test_table();
2494         $tablename = $table->getName();
2495
2496         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2497         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2498         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2499         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2500         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2501         $dbman->create_table($table);
2502
2503         // simple set_field
2504         $id1 = $DB->insert_record($tablename, array('course' => 1));
2505         $id2 = $DB->insert_record($tablename, array('course' => 1));
2506         $id3 = $DB->insert_record($tablename, array('course' => 3));
2507         $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1)));
2508         $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => $id1)));
2509         $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2510         $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2511         $DB->delete_records($tablename, array());
2512
2513         // multiple fields affected
2514         $id1 = $DB->insert_record($tablename, array('course' => 1));
2515         $id2 = $DB->insert_record($tablename, array('course' => 1));
2516         $id3 = $DB->insert_record($tablename, array('course' => 3));
2517         $DB->set_field($tablename, 'course', '5', array('course' => 1));
2518         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2519         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2520         $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2521         $DB->delete_records($tablename, array());
2522
2523         // no field affected
2524         $id1 = $DB->insert_record($tablename, array('course' => 1));
2525         $id2 = $DB->insert_record($tablename, array('course' => 1));
2526         $id3 = $DB->insert_record($tablename, array('course' => 3));
2527         $DB->set_field($tablename, 'course', '5', array('course' => 0));
2528         $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id1)));
2529         $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2530         $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2531         $DB->delete_records($tablename, array());
2532
2533         // all fields - no condition
2534         $id1 = $DB->insert_record($tablename, array('course' => 1));
2535         $id2 = $DB->insert_record($tablename, array('course' => 1));
2536         $id3 = $DB->insert_record($tablename, array('course' => 3));
2537         $DB->set_field($tablename, 'course', 5, array());
2538         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2539         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2540         $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id3)));
2541
2542         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2543         $conditions = array('onetext' => '1');
2544         try {
2545             $DB->set_field($tablename, 'onechar', 'frog', $conditions);
2546             if (debugging()) {
2547                 // only in debug mode - hopefully all devs test code in debug mode...
2548                 $this->fail('An Exception is missing, expected due to equating of text fields');
2549             }
2550         } catch (exception $e) {
2551             $this->assertTrue($e instanceof dml_exception);
2552             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
2553         }
2554
2555         // Test saving a float in a CHAR column, and reading it back.
2556         $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2557         $DB->set_field($tablename, 'onechar', 1.0, array('id' => $id));
2558         $this->assertEqual(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2559         $DB->set_field($tablename, 'onechar', 1e20, array('id' => $id));
2560         $this->assertEqual(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2561         $DB->set_field($tablename, 'onechar', 1e-4, array('id' => $id));
2562         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2563         $DB->set_field($tablename, 'onechar', 1e-5, array('id' => $id));
2564         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2565         $DB->set_field($tablename, 'onechar', 1e-300, array('id' => $id));
2566         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2567         $DB->set_field($tablename, 'onechar', 1e300, array('id' => $id));
2568         $this->assertEqual(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2569
2570         // Test saving a float in a TEXT column, and reading it back.
2571         $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2572         $DB->set_field($tablename, 'onetext', 1.0, array('id' => $id));
2573         $this->assertEqual(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2574         $DB->set_field($tablename, 'onetext', 1e20, array('id' => $id));
2575         $this->assertEqual(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2576         $DB->set_field($tablename, 'onetext', 1e-4, array('id' => $id));
2577         $this->assertEqual(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2578         $DB->set_field($tablename, 'onetext', 1e-5, array('id' => $id));
2579         $this->assertEqual(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2580         $DB->set_field($tablename, 'onetext', 1e-300, array('id' => $id));
2581         $this->assertEqual(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2582         $DB->set_field($tablename, 'onetext', 1e300, array('id' => $id));
2583         $this->assertEqual(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2584
2585         // Note: All the nulls, booleans, empties, quoted and backslashes tests
2586         // go to set_field_select() because set_field() is just one wrapper over it
2587     }
2588
2589     public function test_set_field_select() {
2590
2591         // All the information in this test is fetched from DB by get_field() so we
2592         // have such method properly tested against nulls, empties and friends...
2593
2594         $DB = $this->tdb;
2595         $dbman = $DB->get_manager();
2596
2597         $table = $this->get_test_table();
2598         $tablename = $table->getName();
2599
2600         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2601         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2602         $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
2603         $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
2604         $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2605         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2606         $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2607         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2608         $dbman->create_table($table);
2609
2610         $DB->insert_record($tablename, array('course' => 1));
2611
2612         $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
2613         $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
2614
2615         // Check nulls are set properly for all types
2616         $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
2617         $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
2618         $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
2619         $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
2620         $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
2621         $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
2622         $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
2623         $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
2624         $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
2625         $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
2626
2627         // Check zeros are set properly for all types
2628         $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
2629         $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
2630         $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2631         $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2632
2633         // Check booleans are set properly for all types
2634         $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
2635         $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
2636         $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
2637         $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
2638         $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2639         $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2640         $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2641         $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2642
2643         $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
2644         $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
2645         $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
2646         $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
2647         $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2648         $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2649         $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2650         $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2651
2652         // Check string data causes exception in numeric types
2653         try {
2654             $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
2655             $this->fail("Expecting an exception, none occurred");
2656         } catch (exception $e) {
2657             $this->assertTrue($e instanceof dml_exception);
2658         }
2659         try {
2660             $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
2661             $this->fail("Expecting an exception, none occurred");
2662         } catch (exception $e) {
2663             $this->assertTrue($e instanceof dml_exception);
2664         }
2665
2666         // Check empty string data is stored as 0 in numeric datatypes
2667         $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
2668         $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
2669         $this->assertTrue(is_numeric($field) && $field == 0);
2670
2671         $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
2672         $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
2673         $this->assertTrue(is_numeric($field) && $field == 0);
2674
2675         // Check empty strings are set properly in string types
2676         $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
2677         $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
2678         $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
2679         $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
2680
2681         // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2682         $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2683         $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2684         $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2685         $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2686
2687         // Check various quotes/backslashes combinations in string types
2688         $teststrings = array(
2689             'backslashes and quotes alone (even): "" \'\' \\\\',
2690             'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2691             'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2692             'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2693         foreach ($teststrings as $teststring) {
2694             $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
2695             $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
2696             $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2697             $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2698         }
2699
2700         // Check LOBs in text/binary columns
2701         $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt');
2702         $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary');
2703         $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
2704         $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
2705         $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
2706         $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
2707
2708         // And "small" LOBs too, just in case
2709         $newclob = substr($clob, 0, 500);
2710         $newblob = substr($blob, 0, 250);
2711         $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
2712         $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
2713         $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
2714         $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
2715
2716         // This is the failure from MDL-24863. This was giving an error on MSSQL,
2717         // which converts the '1' to an integer, which cannot then be compared with
2718         // onetext cast to a varchar. This should be fixed and working now.
2719         $newchar = 'frog';
2720         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2721         $params = array('onetext' => '1');
2722         try {
2723             $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);
2724             $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.');
2725         } catch (dml_exception $e) {
2726             $this->assertFalse(true, 'We have an unexpected exception.');
2727             throw $e;
2728         }
2729
2730
2731     }
2732
2733     public function test_count_records() {
2734         $DB = $this->tdb;
2735
2736         $dbman = $DB->get_manager();
2737
2738         $table = $this->get_test_table();
2739         $tablename = $table->getName();
2740
2741         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2742         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2743         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2744         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2745         $dbman->create_table($table);
2746
2747         $this->assertEqual(0, $DB->count_records($tablename));
2748
2749         $DB->insert_record($tablename, array('course' => 3));
2750         $DB->insert_record($tablename, array('course' => 4));
2751         $DB->insert_record($tablename, array('course' => 5));
2752
2753         $this->assertEqual(3, $DB->count_records($tablename));
2754
2755         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2756         $conditions = array('onetext' => '1');
2757         try {
2758             $DB->count_records($tablename, $conditions);
2759             if (debugging()) {
2760                 // only in debug mode - hopefully all devs test code in debug mode...
2761                 $this->fail('An Exception is missing, expected due to equating of text fields');
2762             }
2763         } catch (exception $e) {
2764             $this->assertTrue($e instanceof dml_exception);
2765             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
2766         }
2767     }
2768
2769     public function test_count_records_select() {
2770         $DB = $this->tdb;
2771
2772         $dbman = $DB->get_manager();
2773
2774         $table = $this->get_test_table();
2775         $tablename = $table->getName();
2776
2777         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2778         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2779         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2780         $dbman->create_table($table);
2781
2782         $this->assertEqual(0, $DB->count_records($tablename));
2783
2784         $DB->insert_record($tablename, array('course' => 3));
2785         $DB->insert_record($tablename, array('course' => 4));
2786         $DB->insert_record($tablename, array('course' => 5));
2787
2788         $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
2789     }
2790
2791     public function test_count_records_sql() {
2792         $DB = $this->tdb;
2793         $dbman = $DB->get_manager();
2794
2795         $table = $this->get_test_table();
2796         $tablename = $table->getName();
2797
2798         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2799         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2800         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2801         $dbman->create_table($table);
2802
2803         $this->assertEqual(0, $DB->count_records($tablename));
2804
2805         $DB->insert_record($tablename, array('course' => 3));
2806         $DB->insert_record($tablename, array('course' => 4));
2807         $DB->insert_record($tablename, array('course' => 5));
2808
2809         $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));
2810     }
2811
2812     public function test_record_exists() {
2813         $DB = $this->tdb;
2814         $dbman = $DB->get_manager();
2815
2816         $table = $this->get_test_table();
2817         $tablename = $table->getName();
2818
2819         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2820         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2821         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2822         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2823         $dbman->create_table($table);
2824
2825         $this->assertEqual(0, $DB->count_records($tablename));
2826
2827         $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
2828         $DB->insert_record($tablename, array('course' => 3));
2829
2830         $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
2831
2832
2833         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2834         $conditions = array('onetext' => '1');
2835         try {
2836             $DB->record_exists($tablename, $conditions);
2837             if (debugging()) {
2838                 // only in debug mode - hopefully all devs test code in debug mode...
2839                 $this->fail('An Exception is missing, expected due to equating of text fields');
2840             }
2841         } catch (exception $e) {
2842             $this->assertTrue($e instanceof dml_exception);
2843             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
2844         }
2845     }
2846
2847     public function test_record_exists_select() {
2848         $DB = $this->tdb;
2849         $dbman = $DB->get_manager();
2850
2851         $table = $this->get_test_table();
2852         $tablename = $table->getName();
2853
2854         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2855         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2856         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2857         $dbman->create_table($table);
2858
2859         $this->assertEqual(0, $DB->count_records($tablename));
2860
2861         $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
2862         $DB->insert_record($tablename, array('course' => 3));
2863
2864         $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
2865     }
2866
2867     public function test_record_exists_sql() {
2868         $DB = $this->tdb;
2869         $dbman = $DB->get_manager();
2870
2871         $table = $this->get_test_table();
2872         $tablename = $table->getName();
2873
2874         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2875         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2876         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2877         $dbman->create_table($table);
2878
2879         $this->assertEqual(0, $DB->count_records($tablename));
2880
2881         $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
2882         $DB->insert_record($tablename, array('course' => 3));
2883
2884         $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
2885     }
2886
2887     public function test_recordset_locks_delete() {
2888         $DB = $this->tdb;
2889         $dbman = $DB->get_manager();
2890
2891         //Setup
2892         $table = $this->get_test_table();
2893         $tablename = $table->getName();
2894
2895         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2896         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2897         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2898         $dbman->create_table($table);
2899
2900         $DB->insert_record($tablename, array('course' => 1));
2901         $DB->insert_record($tablename, array('course' => 2));
2902         $DB->insert_record($tablename, array('course' => 3));
2903         $DB->insert_record($tablename, array('course' => 4));
2904         $DB->insert_record($tablename, array('course' => 5));
2905         $DB->insert_record($tablename, array('course' => 6));
2906
2907         // Test against db write locking while on an open recordset
2908         $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}
2909         foreach ($rs as $record) {
2910             $cid = $record->course;
2911             $DB->delete_records($tablename, array('course' => $cid));
2912             $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
2913         }
2914         $rs->close();
2915
2916         $this->assertEqual(4, $DB->count_records($tablename, array()));
2917     }
2918
2919     public function test_recordset_locks_update() {
2920         $DB = $this->tdb;
2921         $dbman = $DB->get_manager();
2922
2923         //Setup
2924         $table = $this->get_test_table();
2925         $tablename = $table->getName();
2926
2927         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2928         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2929         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2930         $dbman->create_table($table);
2931
2932         $DB->insert_record($tablename, array('course' => 1));
2933         $DB->insert_record($tablename, array('course' => 2));
2934         $DB->insert_record($tablename, array('course' => 3));
2935         $DB->insert_record($tablename, array('course' => 4));
2936         $DB->insert_record($tablename, array('course' => 5));
2937         $DB->insert_record($tablename, array('course' => 6));
2938
2939         // Test against db write locking while on an open recordset
2940         $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}
2941         foreach ($rs as $record) {
2942             $cid = $record->course;
2943             $DB->set_field($tablename, 'course', 10, array('course' => $cid));
2944             $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
2945         }
2946         $rs->close();
2947
2948         $this->assertEqual(2, $DB->count_records($tablename, array('course' => 10)));
2949     }
2950
2951     public function test_delete_records() {
2952         $DB = $this->tdb;
2953         $dbman = $DB->get_manager();
2954
2955         $table = $this->get_test_table();
2956         $tablename = $table->getName();
2957
2958         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2959         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2960         $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2961         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2962         $dbman->create_table($table);
2963
2964         $DB->insert_record($tablename, array('course' => 3));
2965         $DB->insert_record($tablename, array('course' => 2));
2966         $DB->insert_record($tablename, array('course' => 2));
2967
2968         // Delete all records
2969         $this->assertTrue($DB->delete_records($tablename));
2970         $this->assertEqual(0, $DB->count_records($tablename));
2971
2972         // Delete subset of records
2973         $DB->insert_record($tablename, array('course' => 3));
2974         $DB->insert_record($tablename, array('course' => 2));
2975         $DB->insert_record($tablename, array('course' => 2));
2976
2977         $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
2978         $this->assertEqual(1, $DB->count_records($tablename));
2979
2980         // delete all
2981         $this->assertTrue($DB->delete_records($tablename, array()));
2982         $this->assertEqual(0, $DB->count_records($tablename));
2983
2984         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2985         $conditions = array('onetext'=>'1');
2986         try {
2987             $DB->delete_records($tablename, $conditions);
2988             if (debugging()) {
2989                 // only in debug mode - hopefully all devs test code in debug mode...
2990                 $this->fail('An Exception is missing, expected due to equating of text fields');
2991             }
2992         } catch (exception $e) {
2993             $this->assertTrue($e instanceof dml_exception);
2994             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
2995         }
2996
2997         // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2998         $conditions = array('onetext' => 1);
2999         try {
3000             $DB->delete_records($tablename, $conditions);
3001             if (debugging()) {
3002                 // only in debug mode - hopefully all devs test code in debug mode...
3003                 $this->fail('An Exception is missing, expected due to equating of text fields');
3004             }
3005         } catch (exception $e) {
3006             $this->assertTrue($e instanceof dml_exception);
3007             $this->assertEqual($e->errorcode, 'textconditionsnotallowed');
3008         }
3009     }
3010
3011     public function test_delete_records_select() {
3012         $DB = $this->tdb;
3013         $dbman = $DB->get_manager();
3014
3015         $table = $this->get_test_table();
3016         $tablename = $table->getName();
3017
3018         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3019         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3020         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3021         $dbman->create_table($table);
3022
3023         $DB->insert_record($tablename, array('course' => 3));
3024         $DB->insert_record($tablename, array('course' => 2));
3025         $DB->insert_record($tablename, array('course' => 2));
3026
3027         $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
3028         $this->assertEqual(1, $DB->count_records($tablename));
3029     }
3030
3031     public function test_delete_records_list() {
3032         $DB = $this->tdb;
3033         $dbman = $DB->get_manager();
3034
3035         $table = $this->get_test_table();
3036         $tablename = $table->getName();
3037
3038         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3039         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3040         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3041         $dbman->create_table($table);
3042
3043         $DB->insert_record($tablename, array('course' => 1));
3044         $DB->insert_record($tablename, array('course' => 2));
3045         $DB->insert_record($tablename, array('course' => 3));
3046
3047         $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
3048         $this->assertEqual(1, $DB->count_records($tablename));
3049
3050         $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
3051         $this->assertEqual(1, $DB->count_records($tablename));
3052     }
3053
3054     function test_sql_null_from_clause() {
3055         $DB = $this->tdb;
3056         $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
3057         $this->assertEqual($DB->get_field_sql($sql), 1);
3058     }
3059
3060     function test_sql_bitand() {
3061         $DB = $this->tdb;
3062         $dbman = $DB->get_manager();
3063
3064         $table = $this->get_test_table();
3065         $tablename = $table->getName();
3066
3067         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3068         $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3069         $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3070         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3071         $dbman->create_table($table);
3072
3073         $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3074
3075         $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
3076         $this->assertEqual($DB->get_field_sql($sql), 2);
3077
3078         $sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}";
3079         $result = $DB->get_records_sql($sql);
3080         $this->assertEqual(count($result), 1);
3081         $this->assertEqual(reset($result)->res, 2);
3082
3083         $sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}";
3084         $result = $DB->get_records_sql($sql, array(10));
3085         $this->assertEqual(count($result), 1);
3086         $this->assertEqual(reset($result)->res, 2);
3087     }
3088
3089     function test_sql_bitnot() {
3090         $DB = $this->tdb;
3091
3092         $not = $DB->sql_bitnot(2);
3093         $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
3094
3095         $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
3096         $this->assertEqual($DB->get_field_sql($sql), 5);
3097     }
3098
3099     function test_sql_bitor() {
3100         $DB = $this->tdb;
3101         $dbman = $DB->get_manager();
3102
3103         $table = $this->get_test_table();
3104         $tablename = $table->getName();
3105
3106         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3107         $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3108         $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3109         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3110         $dbman->create_table($table);
3111
3112         $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3113
3114         $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
3115         $this->assertEqual($DB->get_field_sql($sql), 11);
3116
3117         $sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}";
3118         $result = $DB->get_records_sql($sql);
3119         $this->assertEqual(count($result), 1);
3120         $this->assertEqual(reset($result)->res, 11);
3121
3122         $sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}";
3123         $result = $DB->get_records_sql($sql, array(10));
3124         $this->assertEqual(count($result), 1);
3125         $this->assertEqual(reset($result)->res, 11);
3126     }
3127
3128     function test_sql_bitxor() {
3129         $DB = $this->tdb;
3130         $dbman = $DB->get_manager();
3131
3132         $table = $this->get_test_table();
3133         $tablename = $table->getName();
3134
3135         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3136         $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3137         $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3138         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3139         $dbman->create_table($table);
3140
3141         $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3142
3143         $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
3144         $this->assertEqual($DB->get_field_sql($sql), 9);
3145
3146         $sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}";
3147         $result = $DB->get_records_sql($sql);
3148         $this->assertEqual(count($result), 1);
3149         $this->assertEqual(reset($result)->res, 9);
3150
3151         $sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}";
3152         $result = $DB->get_records_sql($sql, array(10));
3153         $this->assertEqual(count($result), 1);
3154         $this->assertEqual(reset($result)->res, 9);
3155     }
3156
3157     function test_sql_modulo() {
3158         $DB = $this->tdb;
3159         $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
3160         $this->assertEqual($DB->get_field_sql($sql), 3);
3161     }
3162
3163     function test_sql_ceil() {
3164         $DB = $this->tdb;
3165         $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
3166         $this->assertEqual($DB->get_field_sql($sql), 666);
3167     }
3168
3169     function test_cast_char2int() {
3170         $DB = $this->tdb;
3171         $dbman = $DB->get_manager();
3172
3173         $table1 = $this->get_test_table("1");
3174         $tablename1 = $table1->getName();
3175
3176         $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3177         $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3178         $table1->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
3179         $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3180         $dbman->create_table($table1);
3181
3182         $DB->insert_record($tablename1, array('name'=>'0100', 'nametext'=>'0200'));
3183         $DB->insert_record($tablename1, array('name'=>'10',   'nametext'=>'20'));
3184
3185         $table2 = $this->get_test_table("2");
3186         $tablename2 = $table2->getName();
3187         $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3188         $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3189         $table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3190         $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3191         $dbman->create_table($table2);
3192
3193         $DB->insert_record($tablename2, array('res'=>100, 'restext'=>200));
3194
3195         // casting varchar field
3196         $sql = "SELECT *
3197                   FROM {".$tablename1."} t1
3198                   JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
3199         $records = $DB->get_records_sql($sql);
3200         $this->assertEqual(count($records), 1);
3201         // also test them in order clauses
3202         $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('name');
3203         $records = $DB->get_records_sql($sql);
3204         $this->assertEqual(count($records), 2);
3205         $this->assertEqual(reset($records)->name, '10');
3206         $this->assertEqual(next($records)->name, '0100');
3207
3208         // casting text field
3209         $sql = "SELECT *
3210                   FROM {".$tablename1."} t1
3211                   JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.nametext", true)." = t2.restext ";
3212         $records = $DB->get_records_sql($sql);
3213         $this->assertEqual(count($records), 1);
3214         // also test them in order clauses
3215         $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('nametext', true);
3216         $records = $DB->get_records_sql($sql);
3217         $this->assertEqual(count($records), 2);
3218         $this->assertEqual(reset($records)->nametext, '20');
3219         $this->assertEqual(next($records)->nametext, '0200');
3220     }
3221
3222     function test_cast_char2real() {
3223         $DB = $this->tdb;
3224         $dbman = $DB->get_manager();
3225
3226         $table = $this->get_test_table();
3227         $tablename = $table->getName();
3228
3229         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3230         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3231         $table->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
3232         $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
3233         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3234         $dbman->create_table($table);
3235
3236         $DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1));
3237         $DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666));
3238         $DB->insert_record($tablename, array('name'=>'011.10','nametext'=>'011.10','res'=>10.1));
3239
3240         // casting varchar field
3241         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res";
3242         $records = $DB->get_records_sql($sql);
3243         $this->assertEqual(count($records), 2);
3244         // also test them in order clauses
3245         $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('name');
3246         $records = $DB->get_records_sql($sql);
3247         $this->assertEqual(count($records), 3);
3248         $this->assertEqual(reset($records)->name, '10.10');
3249         $this->assertEqual(next($records)->name, '011.10');
3250         $this->assertEqual(next($records)->name, '91.10');
3251
3252         // casting text field
3253         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res";
3254         $records = $DB->get_records_sql($sql);
3255         $this->assertEqual(count($records), 2);
3256         // also test them in order clauses
3257         $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('nametext', true);
3258         $records = $DB->get_records_sql($sql);
3259         $this->assertEqual(count($records), 3);
3260         $this->assertEqual(reset($records)->nametext, '10.10');
3261         $this->assertEqual(next($records)->nametext, '011.10');
3262         $this->assertEqual(next($records)->nametext, '91.10');
3263     }
3264
3265     function sql_compare_text() {
3266         $DB = $this->tdb;
3267         $dbman = $DB->get_manager();
3268
3269         $table = $this->get_test_table();
3270         $tablename = $table->getName();
3271
3272         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3273         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3274         $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3275         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3276         $dbman->create_table($table);
3277
3278         $DB->insert_record($tablename, array('name'=>'abcd',   'description'=>'abcd'));
3279         $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
3280         $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
3281
3282         $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description');
3283         $records = $DB->get_records_sql($sql);
3284         $this->assertEqual(count($records), 1);
3285
3286         $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description', 4);
3287         $records = $DB->get_records_sql($sql);
3288         $this->assertEqual(count($records), 2);
3289     }
3290
3291     function test_unique_index_collation_trouble() {
3292         // note: this is a work in progress, we should probably move this to ddl test
3293
3294         $DB = $this->tdb;
3295         $dbman = $DB->get_manager();
3296
3297         $table = $this->get_test_table();
3298         $tablename = $table->getName();
3299
3300         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3301         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3302         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3303         $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));
3304         $dbman->create_table($table);
3305
3306         $DB->insert_record($tablename, array('name'=>'aaa'));
3307
3308         try {
3309             $DB->insert_record($tablename, array('name'=>'AAA'));
3310         } catch (Exception $e) {
3311             //TODO: ignore case insensitive uniqueness problems for now
3312             //$this->fail("Unique index is case sensitive - this may cause problems in some tables");
3313         }
3314
3315         try {
3316             $DB->insert_record($tablename, array('name'=>'aäa'));
3317             $DB->insert_record($tablename, array('name'=>'aáa'));
3318             $this->assertTrue(true);
3319         } catch (Exception $e) {
3320             $family = $DB->get_dbfamily();
3321             if ($family === 'mysql' or $family === 'mssql') {
3322                 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation.");
3323             } else {
3324                 // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.
3325                 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages.");
3326             }
3327             throw($e);
3328         }
3329     }
3330
3331     function test_sql_binary_equal() {
3332         $DB = $this->tdb;
3333         $dbman = $DB->get_manager();
3334
3335         $table = $this->get_test_table();
3336         $tablename = $table->getName();
3337
3338         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3339         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3340         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3341         $dbman->create_table($table);
3342
3343         $DB->insert_record($tablename, array('name'=>'aaa'));
3344         $DB->insert_record($tablename, array('name'=>'aáa'));
3345         $DB->insert_record($tablename, array('name'=>'aäa'));
3346         $DB->insert_record($tablename, array('name'=>'bbb'));
3347         $DB->insert_record($tablename, array('name'=>'BBB'));
3348
3349         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa'));
3350         $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be accent sensitive');
3351
3352         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb'));
3353         $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be case sensitive');
3354     }
3355
3356     function test_sql_like() {
3357         $DB = $this->tdb;
3358         $dbman = $DB->get_manager();
3359
3360         $table = $this->get_test_table();
3361         $tablename = $table->getName();
3362
3363         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3364         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3365         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3366         $dbman->create_table($table);
3367
3368         $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
3369         $DB->insert_record($tablename, array('name'=>'Nodupor'));
3370         $DB->insert_record($tablename, array('name'=>'ouch'));
3371         $DB->insert_record($tablename, array('name'=>'ouc_'));
3372         $DB->insert_record($tablename, array('name'=>'ouc%'));
3373         $DB->insert_record($tablename, array('name'=>'aui'));
3374         $DB->insert_record($tablename, array('name'=>'aüi'));
3375         $DB->insert_record($tablename, array('name'=>'aÜi'));
3376
3377         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false);
3378         $records = $DB->get_records_sql($sql, array("%dup_r%"));
3379         $this->assertEqual(count($records), 2);
3380
3381         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
3382         $records = $DB->get_records_sql($sql, array("%dup%"));
3383         $this->assertEqual(count($records), 1);
3384
3385         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults
3386         $records = $DB->get_records_sql($sql, array("%dup%"));
3387         $this->assertEqual(count($records), 1);
3388
3389         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
3390         $records = $DB->get_records_sql($sql, array("ouc\\_"));
3391         $this->assertEqual(count($records), 1);
3392
3393         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');
3394         $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
3395         $this->assertEqual(count($records), 1);
3396
3397         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true);
3398         $records = $DB->get_records_sql($sql, array('aui'));
3399         $this->assertEqual(count($records), 1);
3400
3401         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE
3402         $records = $DB->get_records_sql($sql, array("%o%"));
3403         $this->assertEqual(count($records), 3);
3404
3405         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE
3406         $records = $DB->get_records_sql($sql, array("%D%"));
3407         $this->assertEqual(count($records), 6);
3408
3409         // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors
3410         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false);
3411         $records = $DB->get_records_sql($sql, array('aui'));
3412         //$this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
3413         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false);
3414         $records = $DB->get_records_sql($sql, array('aui'));
3415         //$this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
3416     }
3417
3418     function test_sql_ilike() {
3419         // note: this is deprecated, just make sure it does not throw error
3420         $DB = $this->tdb;
3421         $dbman = $DB->get_manager();
3422
3423         $table = $this->get_test_table();
3424         $tablename = $table->getName();
3425
3426         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3427         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3428         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3429         $dbman->create_table($table);
3430
3431         $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
3432         $DB->insert_record($tablename, array('name'=>'NoDupor'));
3433         $DB->insert_record($tablename, array('name'=>'ouch'));
3434
3435         // make sure it prints debug message
3436         $this->enable_debugging();
3437         $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_ilike()." ?";
3438         $params = array("%dup_r%");
3439         $this->assertFalse($this->get_debugging() === '');
3440
3441         // following must not throw exception, we ignore result
3442         $DB->get_records_sql($sql, $params);
3443     }
3444
3445     function test_coalesce() {
3446         $DB = $this->tdb;
3447
3448         // Testing not-null ocurrences, return 1st
3449         $sql = "SELECT COALESCE('returnthis', 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();
3450         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array()));
3451         $sql = "SELECT COALESCE(:paramvalue, 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();
3452         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3453
3454         // Testing null ocurrences, return 2nd
3455         $sql = "SELECT COALESCE(null, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();
3456         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array()));
3457         $sql = "SELECT COALESCE(:paramvalue, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();
3458         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));
3459         $sql = "SELECT COALESCE(null, :paramvalue, 'orthis') AS test" . $DB->sql_null_from_clause();
3460         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3461
3462         // Testing null ocurrences, return 3rd
3463         $sql = "SELECT COALESCE(null, null, 'returnthis') AS test" . $DB->sql_null_from_clause();
3464         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array()));
3465         $sql = "SELECT COALESCE(null, :paramvalue, 'returnthis') AS test" . $DB->sql_null_from_clause();
3466         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));
3467         $sql = "SELECT COALESCE(null, null, :paramvalue) AS test" . $DB->sql_null_from_clause();
3468         $this->assertEqual('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3469
3470         // Testing all null ocurrences, return null
3471         // Note: under mssql, if all elements are nulls, at least one must be a "typed" null, hence
3472         // we cannot test this in a cross-db way easily, so next 2 tests are using
3473         // different queries depending of the DB family
3474         $customnull = $DB->get_dbfamily() == 'mssql' ? 'CAST(null AS varchar)' : 'null';
3475         $sql = "SELECT COALESCE(null, null, " . $customnull . ") AS test" . $DB->sql_null_from_clause();
3476         $this->assertNull($DB->get_field_sql($sql, array()));
3477         $sql = "SELECT COALESCE(null, :paramvalue, " . $customnull . ") AS test" . $DB->sql_null_from_clause();
3478         $this->assertNull($DB->get_field_sql($sql, array('paramvalue' => null)));
3479
3480         // Check there are not problems with whitespace strings
3481         $sql = "SELECT COALESCE(null, '', null) AS test" . $DB->sql_null_from_clause();
3482         $this->assertEqual('', $DB->get_field_sql($sql, array()));
3483         $sql = "SELECT COALESCE(null, :paramvalue, null) AS test" . $DB->sql_null_from_clause();
3484         $this->assertEqual('', $DB->get_field_sql($sql, array('paramvalue' => '')));
3485     }
3486
3487     function test_sql_concat() {
3488         $DB = $this->tdb;
3489         $dbman = $DB->get_manager();
3490
3491         /// Testing all sort of values
3492         $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
3493         // string, some unicode chars
3494         $params = array('name', 'áéíóú', 'name3');
3495         $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
3496         // string, spaces and numbers
3497         $params = array('name', '  ', 12345);
3498         $this->assertEqual('name  12345', $DB->get_field_sql($sql, $params));
3499         // float, empty and strings
3500         $params = array(123.45, '', 'test');
3501         $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
3502         // only integers
3503         $params = array(12, 34, 56);
3504         $this->assertEqual('123456', $DB->get_field_sql($sql, $params));
3505         // float, null and strings
3506         $params = array(123.45, null, 'test');
3507         $this->assertNull($DB->get_field_sql($sql, $params), 'ANSI behaviour: Concatenating NULL must return NULL - But in Oracle :-(. [%s]'); // Concatenate NULL with anything result = NULL
3508
3509         /// Testing fieldnames + values and also integer fieldnames
3510         $table = $this->get_test_table();
3511         $tablename = $table->getName();
3512
3513         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3514         $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3515         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3516         $dbman->create_table($table);
3517
3518         $DB->insert_record($tablename, array('description'=>'áéíóú'));
3519         $DB->insert_record($tablename, array('description'=>'dxxx'));
3520         $DB->insert_record($tablename, array('description'=>'bcde'));
3521
3522         // fieldnames and values mixed
3523         $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
3524         $records = $DB->get_records_sql($sql, array(123.45, 'test'));
3525         $this->assertEqual(count($records), 3);
3526         $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
3527         // integer fieldnames and values
3528         $sql = 'SELECT id, ' . $DB->sql_concat('id', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
3529         $records = $DB->get_records_sql($sql, array(123.45, 'test'));
3530         $this->assertEqual(count($records), 3);
3531         $this->assertEqual($records[1]->result, '1harcoded123.45test');
3532         // all integer fieldnames
3533         $sql = 'SELECT id, ' . $DB->sql_concat('id', 'id', 'id') . ' AS result FROM {' . $tablename . '}';
3534         $records = $DB->get_records_sql($sql, array());
3535         $this->assertEqual(count($records), 3);
3536         $this->assertEqual($records[1]->result, '111');
3537
3538     }
3539
3540     function test_concat_join() {
3541         $DB = $this->tdb;
3542         $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
3543         $params = array("name", "name2", "name3");
3544         $result = $DB->get_field_sql($sql, $params);
3545         $this->assertEqual("name name2 name3", $result);
3546     }
3547
3548     function test_sql_fullname() {
3549         $DB = $this->tdb;
3550         $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
3551         $params = array('first'=>'Firstname', 'last'=>'Surname');
3552         $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
3553     }
3554
3555     function sql_sql_order_by_text() {
3556         $DB = $this->tdb;
3557         $dbman = $DB->get_manager();
3558
3559         $table = $this->get_test_table();
3560         $tablename = $table->getName();
3561
3562         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3563         $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3564         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3565         $dbman->create_table($table);
3566
3567         $DB->insert_record($tablename, array('description'=>'abcd'));
3568         $DB->insert_record($tablename, array('description'=>'dxxx'));
3569         $DB->insert_record($tablename, array('description'=>'bcde'));
3570
3571         $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_order_by_text('description');
3572         $records = $DB->get_records_sql($sql);
3573         $first = array_shift($records);
3574         $this->assertEqual(1, $first->id);
3575         $second = array_shift($records);
3576         $this->assertEqual(3, $second->id);
3577         $last = array_shift($records);
3578         $this->assertEqual(2, $last->id);
3579     }
3580
3581     function test_sql_substring() {
3582         $DB = $this->tdb;
3583         $dbman = $DB->get_manager();
3584
3585         $table = $this->get_test_table();
3586         $tablename = $table->getName();
3587
3588         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3589         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3590         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3591         $dbman->create_table($table);
3592
3593         $string = 'abcdefghij';
3594
3595         $DB->insert_record($tablename, array('name'=>$string));
3596
3597         $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {{$tablename}}";
3598         $record = $DB->get_record_sql($sql);
3599         $this->assertEqual(substr($string, 5-1), $record->name);
3600
3601         $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {{$tablename}}";
3602         $record = $DB->get_record_sql($sql);
3603         $this->assertEqual(substr($string, 5-1, 2), $record->name);
3604
3605         try {
3606             // silence php warning ;-)
3607             @$DB->sql_substr("name");
3608             $this->fail("Expecting an exception, none occurred");
3609         } catch (Exception $e) {
3610             $this->assertTrue($e instanceof coding_exception);
3611         }
3612     }
3613
3614     function test_sql_length() {
3615         $DB = $this->tdb;
3616         $this->assertEqual($DB->get_field_sql(
3617                 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
3618         $this->assertEqual($DB->get_field_sql(
3619                 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
3620     }
3621
3622     function test_sql_position() {
3623         $DB = $this->tdb;
3624         $this->assertEqual($DB->get_field_sql(
3625                 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
3626         $this->assertEqual($DB->get_field_sql(
3627                 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
3628     }
3629
3630     function test_sql_empty() {
3631         $DB = $this->tdb;
3632         $dbman = $DB->get_manager();
3633
3634         $table = $this->get_test_table();
3635         $tablename = $table->getName();
3636
3637         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3638         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3639         $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
3640         $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
3641         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3642         $dbman->create_table($table);
3643
3644         $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3645         $DB->insert_record($tablename, array('name'=>null));
3646         $DB->insert_record($tablename, array('name'=>'lalala'));
3647         $DB->insert_record($tablename, array('name'=>0));
3648
3649         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = '".$DB->sql_empty()."'");
3650         $this->assertEqual(count($records), 1);
3651         $record = reset($records);
3652         $this->assertEqual($record->name, '');
3653
3654         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnull = '".$DB->sql_empty()."'");
3655         $this->assertEqual(count($records), 1);
3656         $record = reset($records);
3657         $this->assertEqual($record->namenotnull, '');
3658
3659         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
3660         $this->assertEqual(count($records), 4);
3661         $record = reset($records);
3662         $this->assertEqual($record->namenotnullnodeflt, '');
3663     }
3664
3665     function test_sql_isempty() {
3666         $DB = $this->tdb;
3667         $dbman = $DB->get_manager();
3668
3669         $table = $this->get_test_table();
3670         $tablename = $table->getName();
3671
3672         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3673         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
3674         $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3675         $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
3676         $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3677         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3678         $dbman->create_table($table);
3679
3680         $DB->insert_record($tablename, array('name'=>'',   'namenull'=>'',   'description'=>'',   'descriptionnull'=>''));
3681         $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
3682         $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
3683         $DB->insert_record($tablename, array('name'=>0,    'namenull'=>0,    'description'=>0,    'descriptionnull'=>0));
3684
3685         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
3686         $this->assertEqual(count($records), 1);
3687         $record = reset($records);
3688         $this->assertEqual($record->name, '');
3689
3690         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
3691         $this->assertEqual(count($records), 1);
3692         $record = reset($records);
3693         $this->assertEqual($record->namenull, '');
3694
3695         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
3696         $this->assertEqual(count($records), 1);
3697         $record = reset($records);
3698         $this->assertEqual($record->description, '');
3699
3700         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
3701         $this->assertEqual(count($records), 1);
3702         $record = reset($records);
3703         $this->assertEqual($record->descriptionnull, '');
3704     }
3705
3706     function test_sql_isnotempty() {
3707         $DB = $this->tdb;
3708         $dbman = $DB->get_manager();
3709
3710         $table = $this->get_test_table();
3711         $tablename = $table->getName();
3712
3713         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3714         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
3715         $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3716         $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
3717         $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3718         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3719         $dbman->create_table($table);
3720
3721         $DB->insert_record($tablename, array('name'=>'',   'namenull'=>'',   'description'=>'',   'descriptionnull'=>''));
3722         $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
3723         $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
3724         $DB->insert_record($tablename, array('name'=>0,    'namenull'=>0,    'description'=>0,    'descriptionnull'=>0));
3725
3726         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));
3727         $this->assertEqual(count($records), 3);
3728         $record = reset($records);
3729         $this->assertEqual($record->name, '??');
3730
3731         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));
3732         $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
3733         $record = reset($records);
3734         $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record
3735
3736         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));
3737         $this->assertEqual(count($records), 3);
3738         $record = reset($records);
3739         $this->assertEqual($record->description, '??');
3740
3741         $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));
3742         $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
3743         $record = reset($records);
3744         $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record
3745     }
3746
3747     function test_sql_regex() {
3748         $DB = $this->tdb;
3749         $dbman = $DB->get_manager();
3750
3751         $table = $this->get_test_table();
3752         $tablename = $table->getName();
3753
3754         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3755         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3756         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3757         $dbman->create_table($table);
3758
3759         $DB->insert_record($tablename, array('name'=>'lalala'));
3760         $DB->insert_record($tablename, array('name'=>'holaaa'));
3761         $DB->insert_record($tablename, array('name'=>'aouch'));
3762
3763         $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex()." ?";
3764         $params = array('a$');
3765         if ($DB->sql_regex_supported()) {
3766             $records = $DB->get_records_sql($sql, $params);
3767             $this->assertEqual(count($records), 2);
3768         } else {
3769             $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
3770         }
3771
3772         $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex(false)." ?";
3773         $params = array('.a');
3774         if ($DB->sql_regex_supported()) {
3775             $records = $DB->get_records_sql($sql, $params);
3776             $this->assertEqual(count($records), 1);
3777         } else {
3778             $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
3779         }
3780
3781     }
3782
3783     /**
3784      * Test some more complex SQL syntax which moodle uses and depends on to work
3785      * useful to determine if new database libraries can be supported.
3786      */
3787     public function test_get_records_sql_complicated() {
3788         $DB = $this->tdb;
3789         $dbman = $DB->get_manager();
3790
3791         $table = $this->get_test_table();
3792         $tablename = $table->getName();
3793
3794         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3795         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3796         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3797         $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
3798         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3799         $dbman->create_table($table);
3800
3801         $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello', 'name'=>'xyz'));
3802         $DB->insert_record($tablename, array('course' => 3, 'content' => 'world', 'name'=>'abc'));
3803         $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello', 'name'=>'def'));
3804         $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe', 'name'=>'abc'));
3805
3806         // test grouping by expressions in the query. MDL-26819. Note that there are 4 ways:
3807         // - By column position (GROUP by 1) - Not supported by mssql & oracle
3808         // - By column name (GROUP by course) - Supported by all, but leading to wrong results
3809         // - By column alias (GROUP by casecol) - Not supported by mssql & oracle
3810         // - By complete expression (GROUP BY CASE ...) - 100% cross-db, this test checks it
3811         $sql = "SELECT (CASE WHEN course = 3 THEN 1 ELSE 0 END) AS casecol,
3812                        COUNT(1) AS countrecs,
3813                        MAX(name) AS maxname
3814                   FROM {{$tablename}}
3815               GROUP BY CASE WHEN course = 3 THEN 1 ELSE 0 END
3816               ORDER BY casecol DESC";
3817         $result = array(
3818                 1 => (object)array('casecol' => 1, 'countrecs' => 2, 'maxname' => 'xyz'),
3819                 0 => (object)array('casecol' => 0, 'countrecs' => 2, 'maxname' => 'def'));
3820         $records = $DB->get_records_sql($sql, null);
3821         $this->assertEqual($result, $records);
3822
3823         // another grouping by CASE expression just to ensure it works ok for multiple WHEN
3824         $sql = "SELECT CASE name
3825                             WHEN 'xyz' THEN 'last'
3826                             WHEN 'def' THEN 'mid'
3827                             WHEN 'abc' THEN 'first'
3828                        END AS casecol,
3829                        COUNT(1) AS countrecs,
3830                        MAX(name) AS maxname
3831                   FROM {{$tablename}}
3832               GROUP BY CASE name
3833                            WHEN 'xyz' THEN 'last'
3834                            WHEN 'def' THEN 'mid'
3835                            WHEN 'abc' THEN 'first'
3836                        END
3837               ORDER BY casecol DESC";
3838         $result = array(
3839                 'mid'  => (object)array('casecol' => 'mid', 'countrecs' => 1, 'maxname' => 'def'),
3840                 'last' => (object)array('casecol' => 'last', 'countrecs' => 1, 'maxname' => 'xyz'),
3841                 'first'=> (object)array('casecol' => 'first', 'countrecs' => 2, 'maxname' => 'abc'));
3842         $records = $DB->get_records_sql($sql, null);
3843         $this->assertEqual($result, $records);
3844
3845         // test limits in queries with DISTINCT/ALL clauses and multiple whitespace. MDL-25268
3846         $sql = "SELECT   DISTINCT   course
3847                   FROM {{$tablename}}
3848                  ORDER BY course";
3849         // only limitfrom
3850         $records = $DB->get_records_sql($sql, null, 1);
3851         $this->assertEqual(2, count($records));
3852         $this->assertEqual(3, reset($records)->course);
3853         $this->assertEqual(5, next($records)->course);
3854         // only limitnum
3855         $records = $DB->get_records_sql($sql, null, 0, 2);
3856         $this->assertEqual(2, count($records));
3857         $this->assertEqual(2, reset($records)->course);
3858         $this->assertEqual(3, next($records)->course);
3859         // both limitfrom and limitnum
3860         $records = $DB->get_records_sql($sql, null, 2, 2);
3861         $this->assertEqual(1, count($records));
3862         $this->assertEqual(5, reset($records)->course);
3863
3864         // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
3865         $sql = "SELECT a.id AS id, a.course AS course
3866                   FROM {{$tablename}} a
3867                   JOIN (SELECT * FROM {{$tablename}}) b ON a.id = b.id
3868                  WHERE a.course = ?";
3869
3870         $records = $DB->get_records_sql($sql, array(3));
3871         $this->assertEqual(2, count($records));
3872         $this->assertEqual(1, reset($records)->id);
3873         $this->assertEqual(2, next($records)->id);
3874
3875         // do NOT try embedding sql_xxxx() helper functions in conditions array of count_records(), they don't break params/binding!
3876         $count = $DB->count_records_select($tablename, "course = :course AND ".$DB->sql_compare_text('content')." = :content", array('course' => 3, 'content' => 'hello'));
3877         $this->assertEqual(1, $count);
3878
3879         // test int x string comparison
3880         $sql = "SELECT *
3881                   FROM {{$tablename}} c
3882                  WHERE name = ?";
3883         $this->assertEqual(count($DB->get_records_sql($sql, array(10))), 0);
3884         $this->assertEqual(count($DB->get_records_sql($sql, array("10"))), 0);
3885         $DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1'));
3886         $DB->insert_record($tablename, array('course' => 7, 'content' => 'yy', 'name'=>'2'));
3887         $this->assertEqual(count($DB->get_records_sql($sql, array(1))), 1);
3888         $this->assertEqual(count($DB->get_records_sql($sql, array("1"))), 1);
3889         $this->assertEqual(count($DB->get_records_sql($sql, array(10))), 0);
3890         $this->assertEqual(count($DB->get_records_sql($sql, array("10"))), 0);
3891         $DB->insert_record($tablename, array('course' => 7, 'content' => 'xx', 'name'=>'1abc'));
3892         $this->assertEqual(count($DB->get_records_sql($sql, array(1))), 1);
3893         $this->assertEqual(count($DB->get_records_sql($sql, array("1"))), 1);
3894     }
3895
3896     function test_onelevel_commit() {
3897         $DB = $this->tdb;
3898         $dbman = $DB->get_manager();
3899
3900         $table = $this->get_test_table();
3901         $tablename = $table->getName();
3902
3903         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3904         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3905         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3906         $dbman->create_table($table);
3907
3908         $transaction = $DB->start_delegated_transaction();
3909         $data = (object)array('course'=>3);
3910         $this->assertEqual(0, $DB->count_records($tablename));
3911         $DB->insert_record($tablename, $data);
3912         $this->assertEqual(1, $DB->count_records($tablename));
3913         $transaction->allow_commit();
3914         $this->assertEqual(1, $DB->count_records($tablename));
3915     }
3916
3917     function test_onelevel_rollback() {
3918         $DB = $this->tdb;
3919         $dbman = $DB->get_manager();
3920
3921         $table = $this->get_test_table();
3922         $tablename = $table->getName();
3923
3924         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3925         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3926         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3927         $dbman->create_table($table);
3928
3929         // this might in fact encourage ppl to migrate from myisam to innodb
3930
3931         $transaction = $DB->start_delegated_transaction();
3932         $data = (object)array('course'=>3);
3933         $this->assertEqual(0, $DB->count_records($tablename));
3934         $DB->insert_record($tablename, $data);
3935         $this->assertEqual(1, $DB->count_records($tablename));
3936         try {
3937             $transaction->rollback(new Exception('test'));
3938             $this->fail('transaction rollback must rethrow exception');
3939         } catch (Exception $e) {
3940         }
3941         $this->assertEqual(0, $DB->count_records($tablename));
3942     }
3943
3944     function test_nested_transactions() {
3945         $DB = $this->tdb;
3946         $dbman = $DB->get_manager();
3947
3948         $table = $this->get_test_table();
3949         $tablename = $table->getName();
3950
3951         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3952         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3953         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3954         $dbman->create_table($table);
3955
3956         // two level commit
3957         $this->assertFalse($DB->is_transaction_started());
3958         $transaction1 = $DB->start_delegated_transaction();
3959         $this->assertTrue($DB->is_transaction_started());
3960         $data = (object)array('course'=>3);
3961         $DB->insert_record($tablename, $data);
3962         $transaction2 = $DB->start_delegated_transaction();
3963         $data = (object)array('course'=>4);
3964         $DB->insert_record($tablename, $data);
3965         $transaction2->allow_commit();
3966         $this->assertTrue($DB->is_transaction_started());
3967         $transaction1->allow_commit();
3968         $this->assertFalse($DB->is_transaction_started());
3969         $this->assertEqual(2, $DB->count_records($tablename));
3970
3971         $DB->delete_records($tablename);
3972
3973         // rollback from top level
3974         $transaction1 = $DB->start_delegated_transaction();
3975         $data = (object)array('course'=>3);
3976         $DB->insert_record($tablename, $data);
3977         $transaction2 = $DB->start_delegated_transaction();
3978         $data = (object)array('course'=>4);
3979         $DB->insert_record($tablename, $data);
3980         $transaction2->allow_commit();
3981         try {
3982             $transaction1->rollback(new Exception('test'));
3983             $this->fail('transaction rollback must rethrow exception');
3984         } catch (Exception $e) {
3985             $this->assertEqual(get_class($e), 'Exception');
3986         }
3987         $this->assertEqual(0, $DB->count_records($tablename));
3988
3989         $DB->delete_records($tablename);
3990
3991         // rollback from nested level
3992         $transaction1 = $DB->start_delegated_transaction();
3993         $data = (object)array('course'=>3);
3994         $DB->insert_record($tablename, $data);
3995         $transaction2 = $DB->start_delegated_transaction();
3996         $data = (object)array('course'=>4);
3997         $DB->insert_record($tablename, $data);
3998         try {
3999             $transaction2->rollback(new Exception('test'));
4000             $this->fail('transaction rollback must rethrow exception');
4001         } catch (Exception $e) {
4002             $this->assertEqual(get_class($e), 'Exception');
4003         }
4004         $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
4005         try {
4006             $transaction1->allow_commit();
4007         } catch (Exception $e) {
4008             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4009         }
4010         $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
4011         // the forced rollback is done from the default_exception handler and similar places,
4012         // let's do it manually here
4013         $this->assertTrue($DB->is_transaction_started());
4014         $DB->force_transaction_rollback();
4015         $this->assertFalse($DB->is_transaction_started());
4016         $this->assertEqual(0, $DB->count_records($tablename)); // finally rolled back
4017
4018         $DB->delete_records($tablename);
4019     }
4020
4021     function test_transactions_forbidden() {
4022         $DB = $this->tdb;
4023         $dbman = $DB->get_manager();
4024
4025         $table = $this->get_test_table();
4026         $tablename = $table->getName();
4027
4028         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4029         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
4030         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4031         $dbman->create_table($table);
4032
4033         $DB->transactions_forbidden();
4034         $transaction = $DB->start_delegated_transaction();
4035         $data = (object)array('course'=>1);
4036         $DB->insert_record($tablename, $data);
4037         try {
4038             $DB->transactions_forbidden();
4039         } catch (Exception $e) {
4040             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4041         }
4042         // the previous test does not force rollback
4043         $transaction->allow_commit();
4044         $this->assertFalse($DB->is_transaction_started());
4045         $this->assertEqual(1, $DB->count_records($tablename));
4046     }
4047
4048     function test_wrong_transactions() {
4049         $DB = $this->tdb;
4050         $dbman = $DB->get_manager();
4051
4052         $table = $this->get_test_table();
4053         $tablename = $table->getName();
4054
4055         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4056         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
4057         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4058         $dbman->create_table($table);
4059
4060
4061         // wrong order of nested commits
4062         $transaction1 = $DB->start_delegated_transaction();
4063         $data = (object)array('course'=>3);
4064         $DB->insert_record($tablename, $data);
4065         $transaction2 = $DB->start_delegated_transaction();
4066         $data = (object)array('course'=>4);
4067         $DB->insert_record($tablename, $data);
4068         try {
4069             $transaction1->allow_commit();
4070             $this->fail('wrong order of commits must throw exception');
4071         } catch (Exception $e) {
4072             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4073         }
4074         try {
4075             $transaction2->allow_commit();
4076             $this->fail('first wrong commit forces rollback');
4077         } catch (Exception $e) {
4078             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4079         }
4080         // this is done in default exception handler usually
4081         $this->assertTrue($DB->is_transaction_started());
4082         $this->assertEqual(2, $DB->count_records($tablename)); // not rolled back yet
4083         $DB->force_transaction_rollback();
4084         $this->assertEqual(0, $DB->count_records($tablename));
4085         $DB->delete_records($tablename);
4086
4087
4088         // wrong order of nested rollbacks
4089         $transaction1 = $DB->start_delegated_transaction();
4090         $data = (object)array('course'=>3);
4091         $DB->insert_record($tablename, $data);
4092         $transaction2 = $DB->start_delegated_transaction();
4093         $data = (object)array('course'=>4);
4094         $DB->insert_record($tablename, $data);
4095         try {
4096             // this first rollback should prevent all other rollbacks
4097             $transaction1->rollback(new Exception('test'));
4098         } catch (Exception $e) {
4099             $this->assertEqual(get_class($e), 'Exception');
4100         }
4101         try {
4102             $transaction2->rollback(new Exception('test'));
4103         } catch (Exception $e) {
4104             $this->assertEqual(get_class($e), 'Exception');
4105         }
4106         try {
4107             $transaction1->rollback(new Exception('test'));
4108         } catch (Exception $e) {
4109             // the rollback was used already once, no way to use it again
4110             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4111         }
4112         // this is done in default exception handler usually
4113         $this->assertTrue($DB->is_transaction_started());
4114         $DB->force_transaction_rollback();
4115         $DB->delete_records($tablename);
4116
4117
4118         // unknown transaction object
4119         $transaction1 = $DB->start_delegated_transaction();
4120         $data = (object)array('course'=>3);
4121         $DB->insert_record($tablename, $data);
4122         $transaction2 = new moodle_transaction($DB);
4123         try {
4124             $transaction2->allow_commit();
4125             $this->fail('foreign transaction must fail');
4126         } catch (Exception $e) {
4127             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4128         }
4129         try {
4130             $transaction1->allow_commit();
4131             $this->fail('first wrong commit forces rollback');
4132         } catch (Exception $e) {
4133             $this->assertEqual(get_class($e), 'dml_transaction_exception');
4134         }
4135         $DB->force_transaction_rollback();
4136         $DB->delete_records($tablename);
4137     }
4138
4139     function test_concurent_transactions() {
4140         // Notes about this test:
4141         // 1- MySQL needs to use one engine with transactions support (InnoDB).
4142         // 2- MSSQL needs to have enabled versioning for read committed
4143         //    transactions (ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON)
4144         $DB = $this->tdb;
4145         $dbman = $DB->get_manager();
4146
4147         $table = $this->get_test_table();
4148         $tablename = $table->getName();
4149
4150         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4151         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
4152         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4153         $dbman->create_table($table);
4154
4155         $transaction = $DB->start_delegated_transaction();
4156         $data = (object)array('course'=>1);
4157         $this->assertEqual(0, $DB->count_records($tablename));
4158         $DB->insert_record($tablename, $data);
4159         $this->assertEqual(1, $DB->count_records($tablename));
4160
4161         //open second connection
4162         $cfg = $DB->export_dbconfig();
4163         if (!isset($cfg->dboptions)) {
4164             $cfg->dboptions = array();
4165         }
4166         $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
4167         $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
4168
4169         // second instance should not see pending inserts
4170         $this->assertEqual(0, $DB2->count_records($tablename));
4171         $data = (object)array('course'=>2);
4172         $DB2->insert_record($tablename, $data);
4173         $this->assertEqual(1, $DB2->count_records($tablename));
4174
4175         // first should see the changes done from second
4176         $this->assertEqual(2, $DB->count_records($tablename));
4177
4178         // now commit and we should see it finally in second connections
4179         $transaction->allow_commit();
4180         $this->assertEqual(2, $DB2->count_records($tablename));
4181
4182         // let's try delete all is also working on (this checks MDL-29198)
4183         // initially both connections see all the records in the table (2)
4184         $this->assertEqual(2, $DB->count_records($tablename));
4185         $this->assertEqual(2, $DB2->count_records($tablename));
4186         $transaction = $DB->start_delegated_transaction();
4187
4188         // delete all from within transaction
4189         $DB->delete_records($tablename);
4190
4191         // transactional $DB, sees 0 records now
4192         $this->assertEqual(0, $DB->count_records($tablename));
4193
4194         // others ($DB2) get no changes yet
4195         $this->assertEqual(2, $DB2->count_records($tablename));
4196
4197         // now commit and we should see changes
4198         $transaction->allow_commit();
4199         $this->assertEqual(0, $DB2->count_records($tablename));
4200
4201         $DB2->dispose();
4202     }
4203
4204     public function test_session_locks() {
4205         $DB = $this->tdb;
4206         $dbman = $DB->get_manager();
4207
4208         // Open second connection
4209         $cfg = $DB->export_dbconfig();
4210         if (!isset($cfg->dboptions)) {
4211             $cfg->dboptions = array();
4212         }
4213         $DB2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
4214         $DB2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
4215
4216         // Testing that acquiring a lock efectively locks
4217         // Get a session lock on connection1
4218         $rowid = rand(100, 200);
4219         $timeout = 1;
4220         $DB->get_session_lock($rowid, $timeout);
4221
4222         // Try to get the same session lock on connection2
4223         try {
4224             $DB2->get_session_lock($rowid, $timeout);
4225             $DB2->release_session_lock($rowid); // Should not be excuted, but here for safety
4226             $this->fail('An Exception is missing, expected due to session lock acquired.');
4227         } catch (exception $e) {
4228             $this->assertTrue($e instanceof dml_sessionwait_exception);
4229             $DB->release_session_lock($rowid); // Release lock on connection1
4230         }
4231
4232         // Testing that releasing a lock efectively frees
4233         // Get a session lock on connection1
4234         $rowid = rand(100, 200);
4235         $timeout = 1;
4236         $DB->get_session_lock($rowid, $timeout);
4237         // Release the lock on connection1
4238         $DB->release_session_lock($rowid);
4239
4240         // Get the just released lock on connection2
4241         $DB2->get_session_lock($rowid, $timeout);
4242         // Release the lock on connection2
4243         $DB2->release_session_lock($rowid);
4244
4245         $DB2->dispose();
4246     }
4247
4248     public function test_bound_param_types() {
4249         $DB = $this->tdb;
4250         $dbman = $DB->get_manager();
4251
4252         $table = $this->get_test_table();
4253         $tablename = $table->getName();
4254
4255         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4256         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
4257         $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
4258         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4259         $dbman->create_table($table);
4260
4261         $this->assertTrue($DB->insert_record($tablename, array('name' => '1', 'content'=>'xx')));
4262         $this->assertTrue($DB->insert_record($tablename, array('name' => 2, 'content'=>'yy')));
4263         $this->assertTrue($DB->insert_record($tablename, array('name' => 'somestring', 'content'=>'zz')));
4264         $this->assertTrue($DB->insert_record($tablename, array('name' => 'aa', 'content'=>'1')));
4265         $this->assertTrue($DB->insert_record($tablename, array('name' => 'bb', 'content'=>2)));
4266         $this->assertTrue($DB->insert_record($tablename, array('name' => 'cc', 'content'=>'sometext')));
4267
4268
4269         // Conditions in CHAR columns
4270         $this->assertTrue($DB->record_exists($tablename, array('name'=>1)));
4271         $this->assertTrue($DB->record_exists($tablename, array('name'=>'1')));
4272         $this->assertFalse($DB->record_exists($tablename, array('name'=>111)));
4273         $this->assertTrue($DB->get_record($tablename, array('name'=>1)));
4274         $this->assertTrue($DB->get_record($tablename, array('name'=>'1')));
4275         $this->assertFalse($DB->get_record($tablename, array('name'=>111)));
4276         $sqlqm = "SELECT *
4277                     FROM {{$tablename}}
4278                    WHERE name = ?";
4279         $this->assertTrue($records = $DB->get_records_sql($sqlqm, array(1)));
4280         $this->assertEqual(1, count($records));
4281         $this->assertTrue($records = $DB->get_records_sql($sqlqm, array('1')));
4282         $this->assertEqual(1, count($records));
4283         $records = $DB->get_records_sql($sqlqm, array(222));
4284         $this->assertEqual(0, count($records));
4285         $sqlnamed = "SELECT *
4286                        FROM {{$tablename}}
4287                       WHERE name = :name";
4288         $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('name' => 2)));
4289         $this->assertEqual(1, count($records));
4290         $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('name' => '2')));
4291         $this->assertEqual(1, count($records));
4292
4293         // Conditions in TEXT columns always must be performed with the sql_compare_text
4294         // helper function on both sides of the condition
4295         $sqlqm = "SELECT *
4296                     FROM {{$tablename}}
4297                    WHERE " . $DB->sql_compare_text('content') . " =  " . $DB->sql_compare_text('?');
4298         $this->assertTrue($records = $DB->get_records_sql($sqlqm, array('1')));
4299         $this->assertEqual(1, count($records));
4300         $this->assertTrue($records = $DB->get_records_sql($sqlqm, array(1)));
4301         $this->assertEqual(1, count($records));
4302         $sqlnamed = "SELECT *
4303                        FROM {{$tablename}}
4304                       WHERE " . $DB->sql_compare_text('content') . " =  " . $DB->sql_compare_text(':content');
4305         $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('content' => 2)));
4306         $this->assertEqual(1, count($records));
4307         $this->assertTrue($records = $DB->get_records_sql($sqlnamed, array('content' => '2')));
4308         $this->assertEqual(1, count($records));
4309     }
4310
4311     public function test_bound_param_reserved() {
4312         $DB = $this->tdb;
4313         $dbman = $DB->get_manager();
4314
4315         $table = $this->get_test_table();
4316         $tablename = $table->getName();
4317
4318         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4319         $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
4320         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4321         $dbman->create_table($table);
4322
4323         $DB->insert_record($tablename, array('course' => '1'));
4324
4325         // make sure reserved words do not cause fatal problems in query parameters
4326
4327         $DB->execute("UPDATE {{$tablename}} SET course = 1 WHERE ID = :select", array('select'=>1));
4328         $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = :select", array('select'=>1));
4329         $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = :select", array('select'=>1));
4330         $rs->close();
4331         $DB->get_fieldset_sql("SELECT id FROM {{$tablename}} WHERE course = :select", array('select'=>1));
4332         $DB->set_field_select($tablename, 'course', '1', "id = :select", array('select'=>1));
4333         $DB->delete_records_select($tablename, "id = :select", array('select'=>1));
4334     }
4335
4336     public function test_limits_and_offsets() {
4337         $DB = $this->tdb;
4338         $dbman = $DB->get_manager();
4339
4340         if (false) $DB = new moodle_database ();
4341
4342         $table = $this->get_test_table();
4343         $tablename = $table->getName();
4344
4345         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
4346         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
4347         $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL);
4348         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
4349         $dbman->create_table($table);
4350
4351         $this->assertTrue($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one')));
4352         $this->assertTrue($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two')));
4353         $this->assertTrue($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three')));
4354         $this->assertTrue($DB->insert_record($tablename, array('name' => 'd', 'content'=>'four')));
4355         $this->assertTrue($DB->insert_record($tablename, array('name' => 'e', 'content'=>'five')));
4356         $this->assertTrue($DB->insert_record($tablename, array('name' => 'f', 'content'=>'six')));
4357
4358         $sqlqm = "SELECT *
4359                     FROM {{$tablename}}";
4360         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4));
4361         $this->assertEqual(2, count($records));
4362         $this->assertEqual('e', reset($records)->name);
4363         $this->assertEqual('f', end($records)->name);
4364
4365         $sqlqm = "SELECT *
4366                     FROM {{$tablename}}";
4367         $this->assertFalse($records = $DB->get_records_sql($sqlqm, null, 8));
4368
4369         $sqlqm = "SELECT *
4370                     FROM {{$tablename}}";
4371         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 4));
4372         $this->assertEqual(4, count($records));
4373         $this->assertEqual('a', reset($records)->name);
4374         $this->assertEqual('d', end($records)->name);
4375
4376         $sqlqm = "SELECT *
4377                     FROM {{$tablename}}";
4378         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 8));
4379         $this->assertEqual(6, count($records));
4380         $this->assertEqual('a', reset($records)->name);
4381         $this->assertEqual('f', end($records)->name);
4382
4383         $sqlqm = "SELECT *
4384                     FROM {{$tablename}}";
4385         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 1, 4));
4386         $this->assertEqual(4, count($records));
4387         $this->assertEqual('b', reset($records)->name);
4388         $this->assertEqual('e', end($records)->name);
4389
4390         $sqlqm = "SELECT *
4391                     FROM {{$tablename}}";
4392         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
4393         $this->assertEqual(2, count($records));
4394         $this->assertEqual('e', reset($records)->name);
4395         $this->assertEqual('f', end($records)->name);
4396
4397         $sqlqm = "SELECT t.*, t.name AS test
4398                     FROM {{$tablename}} t
4399                     ORDER BY t.id ASC";
4400         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
4401         $this->assertEqual(2, count($records));
4402         $this->assertEqual('e', reset($records)->name);
4403         $this->assertEqual('f', end($records)->name);
4404
4405         $sqlqm = "SELECT DISTINCT t.name, t.name AS test
4406                     FROM {{$tablename}} t
4407                     ORDER BY t.name DESC";
4408         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 4, 4));
4409         $this->assertEqual(2, count($records));
4410         $this->assertEqual('b', reset($records)->name);
4411         $this->assertEqual('a', end($records)->name);
4412
4413         $sqlqm = "SELECT 1
4414                     FROM {{$tablename}} t
4415                     WHERE t.name = 'a'";
4416         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 1));
4417         $this->assertEqual(1, count($records));
4418
4419         $sqlqm = "SELECT 'constant'
4420                     FROM {{$tablename}} t
4421                     WHERE t.name = 'a'";
4422         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 8));
4423         $this->assertEqual(1, count($records));
4424
4425         $this->assertTrue($DB->insert_record($tablename, array('name' => 'a', 'content'=>'one')));
4426         $this->assertTrue($DB->insert_record($tablename, array('name' => 'b', 'content'=>'two')));
4427         $this->assertTrue($DB->insert_record($tablename, array('name' => 'c', 'content'=>'three')));
4428
4429         $sqlqm = "SELECT t.name, COUNT(DISTINCT t2.id) AS count, 'Test' AS teststring
4430                     FROM {{$tablename}} t
4431                     LEFT JOIN (
4432                         SELECT t.id, t.name
4433                         FROM {{$tablename}} t
4434                     ) t2 ON t2.name = t.name
4435                     GROUP BY t.name
4436                     ORDER BY t.name ASC";
4437         $this->assertTrue($records = $DB->get_records_sql($sqlqm));
4438         $this->assertEqual(6, count($records));         // a,b,c,d,e,f
4439         $this->assertEqual(2, reset($records)->count);  // a has 2 records now
4440         $this->assertEqual(1, end($records)->count);    // f has 1 record still
4441
4442         $this->assertTrue($records = $DB->get_records_sql($sqlqm, null, 0, 2));
4443         $this->assertEqual(2, count($records));
4444         $this->assertEqual(2, reset($records)->count);
4445         $this->assertEqual(2, end($records)->count);
4446     }
4447 }
4448
4449 /**
4450  * This class is not a proper subclass of moodle_database. It is
4451  * intended to be used only in unit tests, in order to gain access to the
4452  * protected methods of moodle_database, and unit test them.
4453  */
4454 class moodle_database_for_testing extends moodle_database {
4455     protected $prefix = 'mdl_';
4456
4457     public function public_fix_table_names($sql) {
4458         return $this->fix_table_names($sql);
4459     }
4460
4461     public function driver_installed(){}
4462     public function get_dbfamily(){}
4463     protected function get_dbtype(){}
4464     protected function get_dblibrary(){}
4465     public function get_name(){}
4466     public function get_configuration_help(){}
4467     public function get_configuration_hints(){}
4468     public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
4469     public function get_server_info(){}
4470     protected function allowed_param_types(){}
4471     public function get_last_error(){}
4472     public function get_tables($usecache=true){}
4473     public function get_indexes($table){}
4474     public function get_columns($table, $usecache=true){}
4475     protected function normalise_value($column, $value){}
4476     public function set_debug($state){}
4477     public function get_debug(){}
4478     public function set_logging($state){}
4479     public function change_database_structure($sql){}
4480     public function execute($sql, array $params=null){}
4481     public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
4482     public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
4483     public function get_fieldset_sql($sql, array $params=null){}
4484     public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
4485     public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
4486     public function import_record($table, $dataobject){}
4487     public function update_record_raw($table, $params, $bulk=false){}
4488     public function update_record($table, $dataobject, $bulk=false){}
4489     public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
4490     public function delete_records_select($table, $select, array $params=null){}
4491     public function sql_concat(){}
4492     public function sql_concat_join($separator="' '", $elements=array()){}
4493     public function sql_substr($expr, $start, $length=false){}
4494     public function begin_transaction() {}
4495     public function commit_transaction() {}
4496     public function rollback_transaction() {}
4497 }