| Commit | Line | Data |
|---|---|---|
| 73f7ad71 | 1 | <?php |
| 49926145 | 2 | |
| 63b3d8ab EL |
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 | ||
| 73f7ad71 | 18 | /** |
| 9ac5fdf8 PS |
19 | * @package core |
| 20 | * @subpackage dml | |
| 63b3d8ab EL |
21 | * @copyright 2008 Petr Skoda (http://skodak.org) |
| 22 | * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later | |
| 73f7ad71 | 23 | */ |
| 24 | ||
| 9ac5fdf8 | 25 | defined('MOODLE_INTERNAL') || die(); |
| 73f7ad71 | 26 | |
| c7d306e1 | 27 | class dml_test extends UnitTestCase { |
| 73f7ad71 | 28 | private $tables = array(); |
| 251387d0 | 29 | private $tdb; |
| b579f0db | 30 | private $data; |
| 16a2a412 | 31 | public static $includecoverage = array('lib/dml'); |
| 32 | public static $excludecoverage = array('lib/dml/simpletest'); | |
| 73f7ad71 | 33 | |
| ebdf7f7e PS |
34 | protected $olddebug; |
| 35 | protected $olddisplay; | |
| 36 | ||
| 73f7ad71 | 37 | function setUp() { |
| ebdf7f7e | 38 | global $DB, $UNITTEST; |
| e6b4f00e | 39 | |
| a230012c | 40 | if (isset($UNITTEST->func_test_db)) { |
| 251387d0 | 41 | $this->tdb = $UNITTEST->func_test_db; |
| e6b4f00e | 42 | } else { |
| 251387d0 | 43 | $this->tdb = $DB; |
| e6b4f00e | 44 | } |
| 73f7ad71 | 45 | } |
| 46 | ||
| 47 | function tearDown() { | |
| 251387d0 | 48 | $dbman = $this->tdb->get_manager(); |
| 809df0e2 | 49 | |
| 463776ca PS |
50 | foreach ($this->tables as $tablename) { |
| 51 | if ($dbman->table_exists($tablename)) { | |
| 52 | $table = new xmldb_table($tablename); | |
| eee5d9bb | 53 | $dbman->drop_table($table); |
| 73f7ad71 | 54 | } |
| 55 | } | |
| e6b4f00e | 56 | $this->tables = array(); |
| 73f7ad71 | 57 | } |
| 58 | ||
| 1d861fce | 59 | /** |
| 60 | * Get a xmldb_table object for testing, deleting any existing table | |
| 61 | * of the same name, for example if one was left over from a previous test | |
| 62 | * run that crashed. | |
| 63 | * | |
| 64 | * @param database_manager $dbman the database_manager to use. | |
| 98cf713b | 65 | * @param string $suffix table name suffix, use if you need more test tables |
| 1d861fce | 66 | * @return xmldb_table the table object. |
| 67 | */ | |
| 98cf713b | 68 | private function get_test_table($suffix = '') { |
| 9d833e93 | 69 | $dbman = $this->tdb->get_manager(); |
| 70 | ||
| 98cf713b PS |
71 | $tablename = "unit_table"; |
| 72 | if ($suffix !== '') { | |
| 73 | $tablename .= $suffix; | |
| 66e75f8d | 74 | } |
| 75 | ||
| 1d861fce | 76 | $table = new xmldb_table($tablename); |
| 77 | if ($dbman->table_exists($table)) { | |
| 78 | $dbman->drop_table($table); | |
| 79 | } | |
| 4a79777c | 80 | $table->setComment("This is a test'n drop table. You can drop it safely"); |
| 463776ca | 81 | $this->tables[$tablename] = $tablename; |
| 3ff8bf26 | 82 | return new xmldb_table($tablename); |
| 1d861fce | 83 | } |
| 84 | ||
| ebdf7f7e PS |
85 | protected function enable_debugging() { |
| 86 | global $CFG; | |
| 87 | ||
| 88 | $this->olddebug = $CFG->debug; // Save current debug settings | |
| 89 | $this->olddisplay = $CFG->debugdisplay; | |
| 90 | $CFG->debug = DEBUG_DEVELOPER; | |
| 91 | $CFG->debugdisplay = true; | |
| 92 | ob_start(); // hide debug warning | |
| 93 | ||
| 94 | } | |
| 95 | ||
| 96 | protected function get_debugging() { | |
| 97 | global $CFG; | |
| 98 | ||
| 99 | $debuginfo = ob_get_contents(); | |
| 100 | ob_end_clean(); | |
| 101 | $CFG->debug = $this->olddebug; // Restore original debug settings | |
| 102 | $CFG->debugdisplay = $this->olddisplay; | |
| 103 | ||
| 104 | return $debuginfo; | |
| 105 | } | |
| 106 | ||
| dec32529 | 107 | // NOTE: please keep order of test methods here matching the order of moodle_database class methods |
| a1782c36 | 108 | |
| 324715c8 PS |
109 | function test_diagnose() { |
| 110 | $DB = $this->tdb; | |
| 111 | $result = $DB->diagnose(); | |
| 112 | $this->assertNull($result, 'Database self diagnostics failed %s'); | |
| 113 | } | |
| 114 | ||
| 73040360 PS |
115 | function test_get_server_info() { |
| 116 | $DB = $this->tdb; | |
| 117 | $result = $DB->get_server_info(); | |
| 118 | $this->assertTrue(is_array($result)); | |
| 119 | $this->assertTrue(array_key_exists('description', $result)); | |
| 120 | $this->assertTrue(array_key_exists('version', $result)); | |
| 121 | } | |
| 122 | ||
| a1782c36 PS |
123 | public function test_get_in_or_equal() { |
| 124 | $DB = $this->tdb; | |
| 125 | ||
| 126 | // SQL_PARAMS_QM - IN or = | |
| 127 | ||
| 128 | // Correct usage of multiple values | |
| 7395209a | 129 | $in_values = array('value1', 'value2', '3', 4, null, false, true); |
| a1782c36 | 130 | list($usql, $params) = $DB->get_in_or_equal($in_values); |
| 7395209a PS |
131 | $this->assertEqual('IN ('.implode(',',array_fill(0, count($in_values), '?')).')', $usql); |
| 132 | $this->assertEqual(count($in_values), count($params)); | |
| a1782c36 | 133 | foreach ($params as $key => $value) { |
| 7395209a | 134 | $this->assertIdentical($in_values[$key], $value); |
| a1782c36 PS |
135 | } |
| 136 | ||
| 137 | // Correct usage of single value (in an array) | |
| 138 | $in_values = array('value1'); | |
| 139 | list($usql, $params) = $DB->get_in_or_equal($in_values); | |
| 140 | $this->assertEqual("= ?", $usql); | |
| 141 | $this->assertEqual(1, count($params)); | |
| 142 | $this->assertEqual($in_values[0], $params[0]); | |
| 143 | ||
| 144 | // Correct usage of single value | |
| 145 | $in_value = 'value1'; | |
| 146 | list($usql, $params) = $DB->get_in_or_equal($in_values); | |
| 147 | $this->assertEqual("= ?", $usql); | |
| 148 | $this->assertEqual(1, count($params)); | |
| 149 | $this->assertEqual($in_value, $params[0]); | |
| 150 | ||
| 151 | // SQL_PARAMS_QM - NOT IN or <> | |
| 152 | ||
| 153 | // Correct usage of multiple values | |
| 154 | $in_values = array('value1', 'value2', 'value3', 'value4'); | |
| 155 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); | |
| 156 | $this->assertEqual("NOT IN (?,?,?,?)", $usql); | |
| 157 | $this->assertEqual(4, count($params)); | |
| 158 | foreach ($params as $key => $value) { | |
| 159 | $this->assertEqual($in_values[$key], $value); | |
| 160 | } | |
| 161 | ||
| 162 | // Correct usage of single value (in array() | |
| 163 | $in_values = array('value1'); | |
| 164 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); | |
| 165 | $this->assertEqual("<> ?", $usql); | |
| 166 | $this->assertEqual(1, count($params)); | |
| 167 | $this->assertEqual($in_values[0], $params[0]); | |
| 168 | ||
| 169 | // Correct usage of single value | |
| 170 | $in_value = 'value1'; | |
| 171 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false); | |
| 172 | $this->assertEqual("<> ?", $usql); | |
| 173 | $this->assertEqual(1, count($params)); | |
| 174 | $this->assertEqual($in_value, $params[0]); | |
| 175 | ||
| 176 | // SQL_PARAMS_NAMED - IN or = | |
| 177 | ||
| 178 | // Correct usage of multiple values | |
| 179 | $in_values = array('value1', 'value2', 'value3', 'value4'); | |
| 180 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true); | |
| 181 | $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql); | |
| 182 | $this->assertEqual(4, count($params)); | |
| 183 | reset($in_values); | |
| 184 | foreach ($params as $key => $value) { | |
| 185 | $this->assertEqual(current($in_values), $value); | |
| 186 | next($in_values); | |
| 187 | } | |
| 188 | ||
| 189 | // Correct usage of single values (in array) | |
| 190 | $in_values = array('value1'); | |
| 191 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true); | |
| 192 | $this->assertEqual("= :param01", $usql); | |
| 193 | $this->assertEqual(1, count($params)); | |
| 194 | $this->assertEqual($in_values[0], $params['param01']); | |
| 195 | ||
| 196 | // Correct usage of single value | |
| 197 | $in_value = 'value1'; | |
| 198 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true); | |
| 199 | $this->assertEqual("= :param01", $usql); | |
| 200 | $this->assertEqual(1, count($params)); | |
| 201 | $this->assertEqual($in_value, $params['param01']); | |
| 202 | ||
| 203 | // SQL_PARAMS_NAMED - NOT IN or <> | |
| 204 | ||
| 205 | // Correct usage of multiple values | |
| 206 | $in_values = array('value1', 'value2', 'value3', 'value4'); | |
| 207 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false); | |
| 208 | $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql); | |
| 209 | $this->assertEqual(4, count($params)); | |
| 210 | reset($in_values); | |
| 211 | foreach ($params as $key => $value) { | |
| 212 | $this->assertEqual(current($in_values), $value); | |
| 213 | next($in_values); | |
| 214 | } | |
| 215 | ||
| 216 | // Correct usage of single values (in array) | |
| 217 | $in_values = array('value1'); | |
| 218 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false); | |
| 219 | $this->assertEqual("<> :param01", $usql); | |
| 220 | $this->assertEqual(1, count($params)); | |
| 221 | $this->assertEqual($in_values[0], $params['param01']); | |
| 222 | ||
| 223 | // Correct usage of single value | |
| 224 | $in_value = 'value1'; | |
| 225 | list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false); | |
| 226 | $this->assertEqual("<> :param01", $usql); | |
| 227 | $this->assertEqual(1, count($params)); | |
| 228 | $this->assertEqual($in_value, $params['param01']); | |
| 229 | ||
| 230 | } | |
| 231 | ||
| 4fd07498 PS |
232 | public function test_fix_table_names() { |
| 233 | $DB = new moodle_database_for_testing(); | |
| 234 | $prefix = $DB->get_prefix(); | |
| 235 | ||
| 236 | // Simple placeholder | |
| 237 | $placeholder = "{user_123}"; | |
| 238 | $this->assertIdentical($prefix."user_123", $DB->public_fix_table_names($placeholder)); | |
| 239 | ||
| 240 | // wrong table name | |
| 241 | $placeholder = "{user-a}"; | |
| 242 | $this->assertIdentical($placeholder, $DB->public_fix_table_names($placeholder)); | |
| 243 | ||
| 244 | // wrong table name | |
| 245 | $placeholder = "{123user}"; | |
| 246 | $this->assertIdentical($placeholder, $DB->public_fix_table_names($placeholder)); | |
| 247 | ||
| 248 | // Full SQL | |
| 249 | $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid"; | |
| 250 | $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid"; | |
| 251 | $this->assertIdentical($expected, $DB->public_fix_table_names($sql)); | |
| 252 | } | |
| 253 | ||
| 73f7ad71 | 254 | function test_fix_sql_params() { |
| 7f9f11b5 | 255 | $DB = $this->tdb; |
| b579f0db | 256 | |
| 9d833e93 | 257 | $table = $this->get_test_table(); |
| 258 | $tablename = $table->getName(); | |
| 3ff8bf26 | 259 | |
| 334ce026 | 260 | // Correct table placeholder substitution |
| 40d3df5c | 261 | $sql = "SELECT * FROM {{$tablename}}"; |
| 334ce026 | 262 | $sqlarray = $DB->fix_sql_params($sql); |
| 66e75f8d | 263 | $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]); |
| 334ce026 | 264 | |
| 265 | // Conversions of all param types | |
| 266 | $sql = array(); | |
| 3ff8bf26 | 267 | $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2"; |
| 334ce026 | 268 | $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?"; |
| 269 | $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2"; | |
| 270 | ||
| 271 | $params = array(); | |
| 3ff8bf26 | 272 | $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1); |
| 334ce026 | 273 | $params[SQL_PARAMS_QM] = array('first record', 1); |
| 274 | $params[SQL_PARAMS_DOLLAR] = array('first record', 1); | |
| 275 | ||
| 276 | list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]); | |
| 8ba0e4a0 PS |
277 | $this->assertIdentical($rsql, $sql[$rtype]); |
| 278 | $this->assertIdentical($rparams, $params[$rtype]); | |
| 334ce026 | 279 | |
| 280 | list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]); | |
| 8ba0e4a0 PS |
281 | $this->assertIdentical($rsql, $sql[$rtype]); |
| 282 | $this->assertIdentical($rparams, $params[$rtype]); | |
| 334ce026 | 283 | |
| 284 | list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]); | |
| 8ba0e4a0 PS |
285 | $this->assertIdentical($rsql, $sql[$rtype]); |
| 286 | $this->assertIdentical($rparams, $params[$rtype]); | |
| 334ce026 | 287 | |
| 288 | ||
| 73f7ad71 | 289 | // Malformed table placeholder |
| 290 | $sql = "SELECT * FROM [testtable]"; | |
| e6b4f00e | 291 | $sqlarray = $DB->fix_sql_params($sql); |
| 8ba0e4a0 | 292 | $this->assertIdentical($sql, $sqlarray[0]); |
| 73f7ad71 | 293 | |
| 73f7ad71 | 294 | |
| 73f7ad71 | 295 | // Mixed param types (colon and dollar) |
| 40d3df5c | 296 | $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1"; |
| b579f0db | 297 | $params = array('param1' => 'record1', 'param2' => 3); |
| 73f7ad71 | 298 | try { |
| 8ba0e4a0 | 299 | $DB->fix_sql_params($sql, $params); |
| b579f0db | 300 | $this->fail("Expecting an exception, none occurred"); |
| 73f7ad71 | 301 | } catch (Exception $e) { |
| 8ba0e4a0 | 302 | $this->assertTrue($e instanceof dml_exception); |
| 73f7ad71 | 303 | } |
| 304 | ||
| 305 | // Mixed param types (question and dollar) | |
| 40d3df5c | 306 | $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1"; |
| b579f0db | 307 | $params = array('param1' => 'record2', 'param2' => 5); |
| 73f7ad71 | 308 | try { |
| 8ba0e4a0 | 309 | $DB->fix_sql_params($sql, $params); |
| b579f0db | 310 | $this->fail("Expecting an exception, none occurred"); |
| 73f7ad71 | 311 | } catch (Exception $e) { |
| 8ba0e4a0 | 312 | $this->assertTrue($e instanceof dml_exception); |
| 73f7ad71 | 313 | } |
| 73f7ad71 | 314 | |
| 8ba0e4a0 | 315 | // Too few params in sql |
| 40d3df5c | 316 | $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?"; |
| b579f0db | 317 | $params = array('record2', 3); |
| 73f7ad71 | 318 | try { |
| 8ba0e4a0 | 319 | $DB->fix_sql_params($sql, $params); |
| b579f0db | 320 | $this->fail("Expecting an exception, none occurred"); |
| 73f7ad71 | 321 | } catch (Exception $e) { |
| 8ba0e4a0 | 322 | $this->assertTrue($e instanceof dml_exception); |
| 73f7ad71 | 323 | } |
| 73f7ad71 | 324 | |
| 8ba0e4a0 | 325 | // Too many params in array: no error, just use what is necessary |
| 73f7ad71 | 326 | $params[] = 1; |
| 327 | $params[] = time(); | |
| 73f7ad71 | 328 | try { |
| e6b4f00e | 329 | $sqlarray = $DB->fix_sql_params($sql, $params); |
| 8ba0e4a0 PS |
330 | $this->assertTrue(is_array($sqlarray)); |
| 331 | $this->assertEqual(count($sqlarray[1]), 3); | |
| 73f7ad71 | 332 | } catch (Exception $e) { |
| 6ff835b7 | 333 | $this->fail("Unexpected ".get_class($e)." exception"); |
| 73f7ad71 | 334 | } |
| 73f7ad71 | 335 | |
| 336 | // Named params missing from array | |
| 40d3df5c | 337 | $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; |
| b579f0db | 338 | $params = array('wrongname' => 'record1', 'course' => 1); |
| 73f7ad71 | 339 | try { |
| 8ba0e4a0 | 340 | $DB->fix_sql_params($sql, $params); |
| b579f0db | 341 | $this->fail("Expecting an exception, none occurred"); |
| 73f7ad71 | 342 | } catch (Exception $e) { |
| 8ba0e4a0 | 343 | $this->assertTrue($e instanceof dml_exception); |
| 73f7ad71 | 344 | } |
| 73f7ad71 | 345 | |
| 8ba0e4a0 PS |
346 | // Duplicate named param in query - this is a very important feature!! |
| 347 | // it helps with debugging of sloppy code | |
| 40d3df5c | 348 | $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name"; |
| b579f0db | 349 | $params = array('name' => 'record2', 'course' => 3); |
| 73f7ad71 | 350 | try { |
| 8ba0e4a0 | 351 | $DB->fix_sql_params($sql, $params); |
| b579f0db | 352 | $this->fail("Expecting an exception, none occurred"); |
| 73f7ad71 | 353 | } catch (Exception $e) { |
| 8ba0e4a0 | 354 | $this->assertTrue($e instanceof dml_exception); |
| 73f7ad71 | 355 | } |
| 8ba0e4a0 PS |
356 | |
| 357 | // Extra named param is ignored | |
| 358 | $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course"; | |
| 359 | $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha'); | |
| 360 | try { | |
| 361 | $sqlarray = $DB->fix_sql_params($sql, $params); | |
| 362 | $this->assertTrue(is_array($sqlarray)); | |
| 363 | $this->assertEqual(count($sqlarray[1]), 2); | |
| 364 | } catch (Exception $e) { | |
| 365 | $this->fail("Unexpected ".get_class($e)." exception"); | |
| 366 | } | |
| 367 | ||
| 0e6e9051 | 368 | // Booleans in NAMED params are casting to 1/0 int |
| 40d3df5c | 369 | $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?"; |
| 0e6e9051 PS |
370 | $params = array(true, false); |
| 371 | list($sql, $params) = $DB->fix_sql_params($sql, $params); | |
| 372 | $this->assertTrue(reset($params) === 1); | |
| 373 | $this->assertTrue(next($params) === 0); | |
| 374 | ||
| 375 | // Booleans in QM params are casting to 1/0 int | |
| 40d3df5c | 376 | $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2"; |
| 0e6e9051 PS |
377 | $params = array('course1' => true, 'course2' => false); |
| 378 | list($sql, $params) = $DB->fix_sql_params($sql, $params); | |
| 379 | $this->assertTrue(reset($params) === 1); | |
| 380 | $this->assertTrue(next($params) === 0); | |
| 381 | ||
| 382 | // Booleans in DOLLAR params are casting to 1/0 int | |
| 40d3df5c | 383 | $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2"; |
| 0e6e9051 PS |
384 | $params = array(true, false); |
| 385 | list($sql, $params) = $DB->fix_sql_params($sql, $params); | |
| 386 | $this->assertTrue(reset($params) === 1); | |
| 387 | $this->assertTrue(next($params) === 0); | |
| 8ba0e4a0 | 388 | |
| e0962d20 | 389 | // No data types are touched except bool |
| 8ba0e4a0 PS |
390 | $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)"; |
| 391 | $inparams = array('abc', 'ABC', NULL, '1', 1, 1.4); | |
| 392 | list($sql, $params) = $DB->fix_sql_params($sql, $inparams); | |
| ebb8d358 | 393 | $this->assertIdentical(array_values($params), array_values($inparams)); |
| 73f7ad71 | 394 | } |
| 395 | ||
| e0962d20 | 396 | public function test_get_tables() { |
| 7f9f11b5 | 397 | $DB = $this->tdb; |
| 251387d0 | 398 | $dbman = $this->tdb->get_manager(); |
| a230012c | 399 | |
| 1d861fce | 400 | // Need to test with multiple DBs |
| 9d833e93 | 401 | $table = $this->get_test_table(); |
| 3ff8bf26 | 402 | $tablename = $table->getName(); |
| 1d861fce | 403 | |
| b579f0db | 404 | $original_count = count($DB->get_tables()); |
| 405 | ||
| f9ecb171 | 406 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| b579f0db | 407 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| e0962d20 | 408 | |
| b579f0db | 409 | $dbman->create_table($table); |
| b579f0db | 410 | $this->assertTrue(count($DB->get_tables()) == $original_count + 1); |
| e0962d20 PS |
411 | |
| 412 | $dbman->drop_table($table); | |
| 413 | $this->assertTrue(count($DB->get_tables()) == $original_count); | |
| bb78c788 | 414 | } |
| 415 | ||
| 5d1e1ad0 | 416 | public function test_get_indexes() { |
| 7f9f11b5 | 417 | $DB = $this->tdb; |
| 251387d0 | 418 | $dbman = $this->tdb->get_manager(); |
| a230012c | 419 | |
| 9d833e93 | 420 | $table = $this->get_test_table(); |
| 3ff8bf26 | 421 | $tablename = $table->getName(); |
| 422 | ||
| f9ecb171 | 423 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 424 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 80ffbad3 | 425 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| b579f0db | 426 | $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); |
| 767172e4 | 427 | $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id')); |
| b579f0db | 428 | $dbman->create_table($table); |
| b579f0db | 429 | |
| 1a7fa6bb PS |
430 | $indices = $DB->get_indexes($tablename); |
| 431 | $this->assertTrue(is_array($indices)); | |
| 66e75f8d | 432 | $this->assertEqual(count($indices), 2); |
| 433 | // we do not care about index names for now | |
| 80ffbad3 | 434 | $first = array_shift($indices); |
| 435 | $second = array_shift($indices); | |
| 436 | if (count($first['columns']) == 2) { | |
| 437 | $composed = $first; | |
| 438 | $single = $second; | |
| 439 | } else { | |
| 440 | $composed = $second; | |
| 441 | $single = $first; | |
| 442 | } | |
| 443 | $this->assertFalse($single['unique']); | |
| 444 | $this->assertTrue($composed['unique']); | |
| 445 | $this->assertEqual(1, count($single['columns'])); | |
| 446 | $this->assertEqual(2, count($composed['columns'])); | |
| 447 | $this->assertEqual('course', $single['columns'][0]); | |
| 448 | $this->assertEqual('course', $composed['columns'][0]); | |
| 449 | $this->assertEqual('id', $composed['columns'][1]); | |
| bb78c788 | 450 | } |
| 451 | ||
| 1a7fa6bb | 452 | public function test_get_columns() { |
| 5d1e1ad0 PS |
453 | $DB = $this->tdb; |
| 454 | $dbman = $this->tdb->get_manager(); | |
| 455 | ||
| 456 | $table = $this->get_test_table(); | |
| 457 | $tablename = $table->getName(); | |
| 458 | ||
| 5d1e1ad0 | 459 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1a7fa6bb PS |
460 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); |
| 461 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala'); | |
| 462 | $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null); | |
| 463 | $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2'); | |
| 464 | $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); | |
| 5d1e1ad0 PS |
465 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 466 | $dbman->create_table($table); | |
| 5d1e1ad0 PS |
467 | |
| 468 | $columns = $DB->get_columns($tablename); | |
| 1a7fa6bb | 469 | $this->assertTrue(is_array($columns)); |
| 5d1e1ad0 | 470 | |
| 463776ca | 471 | $fields = $table->getFields(); |
| bb78c788 | 472 | $this->assertEqual(count($columns), count($fields)); |
| 473 | ||
| 1a7fa6bb PS |
474 | $field = $columns['id']; |
| 475 | $this->assertEqual('R', $field->meta_type); | |
| 476 | $this->assertTrue($field->auto_increment); | |
| 477 | $this->assertTrue($field->unique); | |
| 478 | ||
| 479 | $field = $columns['course']; | |
| 480 | $this->assertEqual('I', $field->meta_type); | |
| 481 | $this->assertFalse($field->auto_increment); | |
| 482 | $this->assertTrue($field->has_default); | |
| 483 | $this->assertEqual(0, $field->default_value); | |
| 484 | $this->assertTrue($field->not_null); | |
| 485 | ||
| 486 | $field = $columns['name']; | |
| 487 | $this->assertEqual('C', $field->meta_type); | |
| 488 | $this->assertFalse($field->auto_increment); | |
| 489 | $this->assertTrue($field->has_default); | |
| 490 | $this->assertIdentical('lala', $field->default_value); | |
| 491 | $this->assertFalse($field->not_null); | |
| 492 | ||
| 493 | $field = $columns['description']; | |
| 494 | $this->assertEqual('X', $field->meta_type); | |
| 495 | $this->assertFalse($field->auto_increment); | |
| 496 | $this->assertFalse($field->has_default); | |
| 497 | $this->assertIdentical(null, $field->default_value); | |
| 498 | $this->assertFalse($field->not_null); | |
| 499 | ||
| 500 | $field = $columns['enumfield']; | |
| 501 | $this->assertEqual('C', $field->meta_type); | |
| 502 | $this->assertFalse($field->auto_increment); | |
| 503 | $this->assertIdentical('test2', $field->default_value); | |
| 504 | $this->assertTrue($field->not_null); | |
| 505 | ||
| 506 | $field = $columns['onenum']; | |
| 507 | $this->assertEqual('N', $field->meta_type); | |
| 508 | $this->assertFalse($field->auto_increment); | |
| 509 | $this->assertTrue($field->has_default); | |
| 510 | $this->assertEqual(200.0, $field->default_value); | |
| 511 | $this->assertFalse($field->not_null); | |
| 512 | ||
| bb78c788 | 513 | for ($i = 0; $i < count($columns); $i++) { |
| 514 | if ($i == 0) { | |
| 515 | $next_column = reset($columns); | |
| 516 | $next_field = reset($fields); | |
| 517 | } else { | |
| 518 | $next_column = next($columns); | |
| 519 | $next_field = next($fields); | |
| 520 | } | |
| 521 | ||
| 522 | $this->assertEqual($next_column->name, $next_field->name); | |
| 523 | } | |
| 524 | } | |
| 525 | ||
| 6903b52f PS |
526 | public function test_get_manager() { |
| 527 | $DB = $this->tdb; | |
| 528 | $dbman = $this->tdb->get_manager(); | |
| 529 | ||
| 530 | $this->assertTrue($dbman instanceof database_manager); | |
| 531 | } | |
| 532 | ||
| bb3f9a0a PS |
533 | public function test_setup_is_unicodedb() { |
| 534 | $DB = $this->tdb; | |
| 535 | $this->assertTrue($DB->setup_is_unicodedb()); | |
| 536 | } | |
| 537 | ||
| cc4ac9ff | 538 | public function test_set_debug() { //tests get_debug() too |
| 654f9f17 PS |
539 | $DB = $this->tdb; |
| 540 | $dbman = $this->tdb->get_manager(); | |
| 541 | ||
| 542 | $table = $this->get_test_table(); | |
| 543 | $tablename = $table->getName(); | |
| 544 | ||
| 545 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 546 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 547 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 548 | $dbman->create_table($table); | |
| 654f9f17 PS |
549 | |
| 550 | $sql = "SELECT * FROM {{$tablename}}"; | |
| 551 | ||
| 552 | $prevdebug = $DB->get_debug(); | |
| 553 | ||
| 554 | ob_start(); | |
| 555 | $DB->set_debug(true); | |
| cc4ac9ff | 556 | $this->assertTrue($DB->get_debug()); |
| 654f9f17 PS |
557 | $DB->execute($sql); |
| 558 | $DB->set_debug(false); | |
| cc4ac9ff | 559 | $this->assertFalse($DB->get_debug()); |
| 654f9f17 PS |
560 | $debuginfo = ob_get_contents(); |
| 561 | ob_end_clean(); | |
| 562 | $this->assertFalse($debuginfo === ''); | |
| 563 | ||
| 564 | ob_start(); | |
| 565 | $DB->execute($sql); | |
| 566 | $debuginfo = ob_get_contents(); | |
| 567 | ob_end_clean(); | |
| 568 | $this->assertTrue($debuginfo === ''); | |
| 569 | ||
| 570 | $DB->set_debug($prevdebug); | |
| 571 | } | |
| 572 | ||
| 6c5721ff | 573 | public function test_execute() { |
| 7f9f11b5 | 574 | $DB = $this->tdb; |
| 251387d0 | 575 | $dbman = $this->tdb->get_manager(); |
| a230012c | 576 | |
| 98cf713b | 577 | $table1 = $this->get_test_table('1'); |
| 6c5721ff PS |
578 | $tablename1 = $table1->getName(); |
| 579 | $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 580 | $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 581 | $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); | |
| 582 | $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); | |
| 583 | $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 584 | $dbman->create_table($table1); | |
| 6c5721ff | 585 | |
| 98cf713b | 586 | $table2 = $this->get_test_table('2'); |
| 6c5721ff PS |
587 | $tablename2 = $table2->getName(); |
| 588 | $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 589 | $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 590 | $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 591 | $dbman->create_table($table2); | |
| 6c5721ff PS |
592 | |
| 593 | $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa')); | |
| 594 | $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb')); | |
| 595 | $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc')); | |
| 596 | $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd')); | |
| 597 | ||
| 598 | // select results are ignored | |
| 599 | $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course"; | |
| 600 | $this->assertTrue($DB->execute($sql, array('course'=>3))); | |
| 601 | ||
| 602 | // throw exception on error | |
| 603 | $sql = "XXUPDATE SET XSSD"; | |
| 604 | try { | |
| 605 | $DB->execute($sql); | |
| 606 | $this->fail("Expecting an exception, none occurred"); | |
| 607 | } catch (Exception $e) { | |
| 608 | $this->assertTrue($e instanceof dml_write_exception); | |
| 609 | } | |
| 6807d2b3 | 610 | |
| 6c5721ff PS |
611 | // update records |
| 612 | $sql = "UPDATE {{$tablename1}} | |
| 613 | SET course = 6 | |
| 614 | WHERE course = ?"; | |
| 615 | $this->assertTrue($DB->execute($sql, array('3'))); | |
| 616 | $this->assertEqual($DB->count_records($tablename1, array('course' => 6)), 2); | |
| 6807d2b3 | 617 | |
| 6c5721ff PS |
618 | // insert from one into second table |
| 619 | $sql = "INSERT INTO {{$tablename2}} (course) | |
| bb78c788 | 620 | |
| 6c5721ff PS |
621 | SELECT course |
| 622 | FROM {{$tablename1}}"; | |
| 6c5721ff PS |
623 | $this->assertTrue($DB->execute($sql)); |
| 624 | $this->assertEqual($DB->count_records($tablename2), 4); | |
| bb78c788 | 625 | } |
| 626 | ||
| b579f0db | 627 | public function test_get_recordset() { |
| 628 | $DB = $this->tdb; | |
| 629 | $dbman = $DB->get_manager(); | |
| 630 | ||
| 9d833e93 | 631 | $table = $this->get_test_table(); |
| 3ff8bf26 | 632 | $tablename = $table->getName(); |
| 633 | ||
| f9ecb171 | 634 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 635 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 636 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); | |
| b579f0db | 637 | $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); |
| 638 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 639 | $dbman->create_table($table); | |
| b579f0db | 640 | |
| 641 | $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'), | |
| 642 | array('id' => 2, 'course' => 3, 'name' => 'record2'), | |
| 643 | array('id' => 3, 'course' => 5, 'name' => 'record3')); | |
| 22d77567 | 644 | |
| b579f0db | 645 | foreach ($data as $record) { |
| 3ff8bf26 | 646 | $DB->insert_record($tablename, $record); |
| b579f0db | 647 | } |
| 648 | ||
| 8efd8865 | 649 | // standard recordset iteration |
| 3ff8bf26 | 650 | $rs = $DB->get_recordset($tablename); |
| dec32529 | 651 | $this->assertTrue($rs instanceof moodle_recordset); |
| 8efd8865 PS |
652 | reset($data); |
| 653 | foreach($rs as $record) { | |
| 654 | $data_record = current($data); | |
| 655 | foreach ($record as $k => $v) { | |
| 656 | $this->assertEqual($data_record[$k], $v); | |
| 657 | } | |
| 658 | next($data); | |
| 659 | } | |
| 660 | $rs->close(); | |
| b579f0db | 661 | |
| 8efd8865 PS |
662 | // iterator style usage |
| 663 | $rs = $DB->get_recordset($tablename); | |
| 664 | $this->assertTrue($rs instanceof moodle_recordset); | |
| b579f0db | 665 | reset($data); |
| 8efd8865 PS |
666 | while ($rs->valid()) { |
| 667 | $record = $rs->current(); | |
| 668 | $data_record = current($data); | |
| 669 | foreach ($record as $k => $v) { | |
| 670 | $this->assertEqual($data_record[$k], $v); | |
| 671 | } | |
| 672 | next($data); | |
| 673 | $rs->next(); | |
| 674 | } | |
| 675 | $rs->close(); | |
| 676 | ||
| 677 | // make sure rewind is ignored | |
| 678 | $rs = $DB->get_recordset($tablename); | |
| 679 | $this->assertTrue($rs instanceof moodle_recordset); | |
| 680 | reset($data); | |
| 681 | $i = 0; | |
| b579f0db | 682 | foreach($rs as $record) { |
| 8efd8865 PS |
683 | $i++; |
| 684 | $rs->rewind(); | |
| 685 | if ($i > 10) { | |
| 686 | $this->fail('revind not ignored in recordsets'); | |
| 687 | break; | |
| 688 | } | |
| b579f0db | 689 | $data_record = current($data); |
| 690 | foreach ($record as $k => $v) { | |
| 691 | $this->assertEqual($data_record[$k], $v); | |
| 692 | } | |
| 693 | next($data); | |
| 694 | } | |
| 695 | $rs->close(); | |
| 5d91786a | 696 | |
| dec32529 PS |
697 | // notes: |
| 698 | // * limits are tested in test_get_recordset_sql() | |
| 699 | // * where_clause() is used internally and is tested in test_get_records() | |
| 7f9f11b5 | 700 | } |
| 0088bd31 | 701 | |
| b0cb2290 | 702 | public function test_get_recordset_iterator_keys() { |
| 703 | $DB = $this->tdb; | |
| 704 | $dbman = $DB->get_manager(); | |
| 705 | ||
| 9d833e93 | 706 | $table = $this->get_test_table(); |
| 3ff8bf26 | 707 | $tablename = $table->getName(); |
| 708 | ||
| f9ecb171 | 709 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 710 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 711 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0'); | |
| b0cb2290 | 712 | $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); |
| 713 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 714 | $dbman->create_table($table); | |
| b0cb2290 | 715 | |
| 22d77567 | 716 | $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'), |
| 717 | array('id'=> 2, 'course' => 3, 'name' => 'record2'), | |
| 718 | array('id'=> 3, 'course' => 5, 'name' => 'record3')); | |
| b0cb2290 | 719 | foreach ($data as $record) { |
| 3ff8bf26 | 720 | $DB->insert_record($tablename, $record); |
| b0cb2290 | 721 | } |
| 722 | ||
| dec32529 | 723 | // Test repeated numeric keys are returned ok |
| 3ff8bf26 | 724 | $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id'); |
| b0cb2290 | 725 | |
| 726 | reset($data); | |
| 727 | $count = 0; | |
| 728 | foreach($rs as $key => $record) { | |
| 729 | $data_record = current($data); | |
| 730 | $this->assertEqual($data_record['course'], $key); | |
| 731 | next($data); | |
| 732 | $count++; | |
| 733 | } | |
| 734 | $rs->close(); | |
| b0cb2290 | 735 | $this->assertEqual($count, 3); |
| 736 | ||
| dec32529 | 737 | // Test string keys are returned ok |
| 3ff8bf26 | 738 | $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id'); |
| b0cb2290 | 739 | |
| 740 | reset($data); | |
| 741 | $count = 0; | |
| 742 | foreach($rs as $key => $record) { | |
| 743 | $data_record = current($data); | |
| 744 | $this->assertEqual($data_record['name'], $key); | |
| 745 | next($data); | |
| 746 | $count++; | |
| 747 | } | |
| 748 | $rs->close(); | |
| b0cb2290 | 749 | $this->assertEqual($count, 3); |
| 750 | ||
| dec32529 | 751 | // Test numeric not starting in 1 keys are returned ok |
| 3ff8bf26 | 752 | $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name'); |
| b0cb2290 | 753 | |
| 754 | $data = array_reverse($data); | |
| 755 | reset($data); | |
| 756 | $count = 0; | |
| 757 | foreach($rs as $key => $record) { | |
| 758 | $data_record = current($data); | |
| 759 | $this->assertEqual($data_record['id'], $key); | |
| 760 | next($data); | |
| 761 | $count++; | |
| 762 | } | |
| 763 | $rs->close(); | |
| b0cb2290 | 764 | $this->assertEqual($count, 3); |
| 765 | } | |
| 766 | ||
| 0088bd31 | 767 | public function test_get_recordset_list() { |
| 768 | $DB = $this->tdb; | |
| 769 | $dbman = $DB->get_manager(); | |
| 770 | ||
| 9d833e93 | 771 | $table = $this->get_test_table(); |
| 3ff8bf26 | 772 | $tablename = $table->getName(); |
| 773 | ||
| f9ecb171 | 774 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 775 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 0088bd31 | 776 | $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); |
| 777 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 778 | $dbman->create_table($table); | |
| 0088bd31 | 779 | |
| 3ff8bf26 | 780 | $DB->insert_record($tablename, array('course' => 3)); |
| 781 | $DB->insert_record($tablename, array('course' => 3)); | |
| 782 | $DB->insert_record($tablename, array('course' => 5)); | |
| 783 | $DB->insert_record($tablename, array('course' => 2)); | |
| 0088bd31 | 784 | |
| 3ff8bf26 | 785 | $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2)); |
| 0088bd31 | 786 | |
| 0088bd31 | 787 | $counter = 0; |
| 788 | foreach ($rs as $record) { | |
| 789 | $counter++; | |
| 790 | } | |
| 791 | $this->assertEqual(3, $counter); | |
| c362878e | 792 | $rs->close(); |
| 793 | ||
| 794 | $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645 | |
| 795 | ||
| 796 | $counter = 0; | |
| 797 | foreach ($rs as $record) { | |
| 798 | $counter++; | |
| 799 | } | |
| c362878e | 800 | $rs->close(); |
| 5d91786a | 801 | $this->assertEqual(0, $counter); |
| 802 | ||
| dec32529 PS |
803 | // notes: |
| 804 | // * limits are tested in test_get_recordset_sql() | |
| 805 | // * where_clause() is used internally and is tested in test_get_records() | |
| 0088bd31 | 806 | } |
| 807 | ||
| 808 | public function test_get_recordset_select() { | |
| 809 | $DB = $this->tdb; | |
| 810 | $dbman = $DB->get_manager(); | |
| 811 | ||
| 9d833e93 | 812 | $table = $this->get_test_table(); |
| 3ff8bf26 | 813 | $tablename = $table->getName(); |
| 814 | ||
| f9ecb171 | 815 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 816 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 0088bd31 | 817 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 818 | $dbman->create_table($table); | |
| 0088bd31 | 819 | |
| 3ff8bf26 | 820 | $DB->insert_record($tablename, array('course' => 3)); |
| 821 | $DB->insert_record($tablename, array('course' => 3)); | |
| 822 | $DB->insert_record($tablename, array('course' => 5)); | |
| 823 | $DB->insert_record($tablename, array('course' => 2)); | |
| 0088bd31 | 824 | |
| 3ff8bf26 | 825 | $rs = $DB->get_recordset_select($tablename, ''); |
| 0088bd31 | 826 | $counter = 0; |
| 827 | foreach ($rs as $record) { | |
| 828 | $counter++; | |
| 829 | } | |
| 515cb653 | 830 | $rs->close(); |
| 0088bd31 | 831 | $this->assertEqual(4, $counter); |
| 832 | ||
| 3ff8bf26 | 833 | $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3')); |
| 0088bd31 | 834 | $counter = 0; |
| 835 | foreach ($rs as $record) { | |
| 836 | $counter++; | |
| 837 | } | |
| 515cb653 | 838 | $rs->close(); |
| 0088bd31 | 839 | $this->assertEqual(2, $counter); |
| 5d91786a | 840 | |
| dec32529 PS |
841 | // notes: |
| 842 | // * limits are tested in test_get_recordset_sql() | |
| 0088bd31 | 843 | } |
| 844 | ||
| 845 | public function test_get_recordset_sql() { | |
| 846 | $DB = $this->tdb; | |
| 847 | $dbman = $DB->get_manager(); | |
| 848 | ||
| 9d833e93 | 849 | $table = $this->get_test_table(); |
| 3ff8bf26 | 850 | $tablename = $table->getName(); |
| 851 | ||
| f9ecb171 | 852 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 853 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 0088bd31 | 854 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 855 | $dbman->create_table($table); | |
| 0088bd31 | 856 | |
| 5d91786a | 857 | $inskey1 = $DB->insert_record($tablename, array('course' => 3)); |
| 858 | $inskey2 = $DB->insert_record($tablename, array('course' => 5)); | |
| 859 | $inskey3 = $DB->insert_record($tablename, array('course' => 4)); | |
| 860 | $inskey4 = $DB->insert_record($tablename, array('course' => 3)); | |
| 861 | $inskey5 = $DB->insert_record($tablename, array('course' => 2)); | |
| 862 | $inskey6 = $DB->insert_record($tablename, array('course' => 1)); | |
| 863 | $inskey7 = $DB->insert_record($tablename, array('course' => 0)); | |
| 0088bd31 | 864 | |
| 76f8ddc0 | 865 | $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); |
| 0088bd31 | 866 | $counter = 0; |
| 867 | foreach ($rs as $record) { | |
| 868 | $counter++; | |
| 869 | } | |
| 515cb653 | 870 | $rs->close(); |
| 0088bd31 | 871 | $this->assertEqual(2, $counter); |
| 5d91786a | 872 | |
| 873 | // limits - only need to test this case, the rest have been tested by test_get_records_sql() | |
| 874 | // only limitfrom = skips that number of records | |
| 40d3df5c | 875 | $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); |
| 5d91786a | 876 | $records = array(); |
| 877 | foreach($rs as $key => $record) { | |
| 878 | $records[$key] = $record; | |
| 879 | } | |
| 880 | $rs->close(); | |
| 881 | $this->assertEqual(5, count($records)); | |
| 882 | $this->assertEqual($inskey3, reset($records)->id); | |
| 883 | $this->assertEqual($inskey7, end($records)->id); | |
| 884 | ||
| 885 | // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here | |
| 0088bd31 | 886 | } |
| 887 | ||
| 888 | public function test_get_records() { | |
| 889 | $DB = $this->tdb; | |
| 890 | $dbman = $DB->get_manager(); | |
| 891 | ||
| 9d833e93 | 892 | $table = $this->get_test_table(); |
| 3ff8bf26 | 893 | $tablename = $table->getName(); |
| 894 | ||
| f9ecb171 | 895 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 896 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 0088bd31 | 897 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 898 | $dbman->create_table($table); | |
| 0088bd31 | 899 | |
| 3ff8bf26 | 900 | $DB->insert_record($tablename, array('course' => 3)); |
| 901 | $DB->insert_record($tablename, array('course' => 3)); | |
| 902 | $DB->insert_record($tablename, array('course' => 5)); | |
| 903 | $DB->insert_record($tablename, array('course' => 2)); | |
| 0088bd31 | 904 | |
| 905 | // All records | |
| 3ff8bf26 | 906 | $records = $DB->get_records($tablename); |
| 0088bd31 | 907 | $this->assertEqual(4, count($records)); |
| 908 | $this->assertEqual(3, $records[1]->course); | |
| 909 | $this->assertEqual(3, $records[2]->course); | |
| 910 | $this->assertEqual(5, $records[3]->course); | |
| 911 | $this->assertEqual(2, $records[4]->course); | |
| 912 | ||
| 913 | // Records matching certain conditions | |
| 3ff8bf26 | 914 | $records = $DB->get_records($tablename, array('course' => 3)); |
| 0088bd31 | 915 | $this->assertEqual(2, count($records)); |
| 916 | $this->assertEqual(3, $records[1]->course); | |
| 917 | $this->assertEqual(3, $records[2]->course); | |
| 918 | ||
| 919 | // All records sorted by course | |
| 3ff8bf26 | 920 | $records = $DB->get_records($tablename, null, 'course'); |
| 0088bd31 | 921 | $this->assertEqual(4, count($records)); |
| 922 | $current_record = reset($records); | |
| 923 | $this->assertEqual(4, $current_record->id); | |
| 924 | $current_record = next($records); | |
| 925 | $this->assertEqual(1, $current_record->id); | |
| 926 | $current_record = next($records); | |
| 927 | $this->assertEqual(2, $current_record->id); | |
| 928 | $current_record = next($records); | |
| 929 | $this->assertEqual(3, $current_record->id); | |
| 930 | ||
| 931 | // All records, but get only one field | |
| 3ff8bf26 | 932 | $records = $DB->get_records($tablename, null, '', 'id'); |
| 76f8ddc0 PS |
933 | $this->assertFalse(isset($records[1]->course)); |
| 934 | $this->assertTrue(isset($records[1]->id)); | |
| 0088bd31 | 935 | $this->assertEqual(4, count($records)); |
| 3ff8bf26 | 936 | |
| 0e6e9051 PS |
937 | // Booleans into params |
| 938 | $records = $DB->get_records($tablename, array('course' => true)); | |
| 939 | $this->assertEqual(0, count($records)); | |
| 940 | $records = $DB->get_records($tablename, array('course' => false)); | |
| 941 | $this->assertEqual(0, count($records)); | |
| 942 | ||
| 5d91786a | 943 | // note: delegate limits testing to test_get_records_sql() |
| 0088bd31 | 944 | } |
| 945 | ||
| 946 | public function test_get_records_list() { | |
| 50a12c87 | 947 | $DB = $this->tdb; |
| 948 | $dbman = $DB->get_manager(); | |
| 949 | ||
| 9d833e93 | 950 | $table = $this->get_test_table(); |
| 3ff8bf26 | 951 | $tablename = $table->getName(); |
| 952 | ||
| f9ecb171 | 953 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 954 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 955 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 956 | $dbman->create_table($table); | |
| 50a12c87 | 957 | |
| 3ff8bf26 | 958 | $DB->insert_record($tablename, array('course' => 3)); |
| 959 | $DB->insert_record($tablename, array('course' => 3)); | |
| 960 | $DB->insert_record($tablename, array('course' => 5)); | |
| 961 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 962 | |
| 76f8ddc0 PS |
963 | $records = $DB->get_records_list($tablename, 'course', array(3, 2)); |
| 964 | $this->assertTrue(is_array($records)); | |
| 50a12c87 | 965 | $this->assertEqual(3, count($records)); |
| 966 | $this->assertEqual(1, reset($records)->id); | |
| 967 | $this->assertEqual(2, next($records)->id); | |
| 968 | $this->assertEqual(4, next($records)->id); | |
| 0088bd31 | 969 | |
| c362878e | 970 | $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645 |
| 971 | $this->assertEqual(0, count($records)); | |
| 972 | ||
| 5d91786a | 973 | // note: delegate limits testing to test_get_records_sql() |
| 0088bd31 | 974 | } |
| 975 | ||
| 5d91786a | 976 | public function test_get_records_sql() { |
| 5d91786a | 977 | $DB = $this->tdb; |
| 978 | $dbman = $DB->get_manager(); | |
| 979 | ||
| 980 | $table = $this->get_test_table(); | |
| 981 | $tablename = $table->getName(); | |
| 982 | ||
| 983 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 984 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 985 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 986 | $dbman->create_table($table); | |
| 5d91786a | 987 | |
| 988 | $inskey1 = $DB->insert_record($tablename, array('course' => 3)); | |
| 989 | $inskey2 = $DB->insert_record($tablename, array('course' => 5)); | |
| 990 | $inskey3 = $DB->insert_record($tablename, array('course' => 4)); | |
| 991 | $inskey4 = $DB->insert_record($tablename, array('course' => 3)); | |
| 992 | $inskey5 = $DB->insert_record($tablename, array('course' => 2)); | |
| 993 | $inskey6 = $DB->insert_record($tablename, array('course' => 1)); | |
| 994 | $inskey7 = $DB->insert_record($tablename, array('course' => 0)); | |
| 995 | ||
| 76f8ddc0 | 996 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)); |
| 50a12c87 | 997 | $this->assertEqual(2, count($records)); |
| 5d91786a | 998 | $this->assertEqual($inskey1, reset($records)->id); |
| 999 | $this->assertEqual($inskey4, next($records)->id); | |
| 0088bd31 | 1000 | |
| 16a2a412 | 1001 | // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test |
| ebdf7f7e | 1002 | $this->enable_debugging(); |
| 40d3df5c | 1003 | $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null); |
| ebdf7f7e | 1004 | $this->assertFalse($this->get_debugging() === ''); |
| 5d91786a | 1005 | $this->assertEqual(6, count($records)); |
| 5d91786a | 1006 | |
| 1007 | // negative limits = no limits | |
| 40d3df5c | 1008 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1); |
| 5d91786a | 1009 | $this->assertEqual(7, count($records)); |
| 1010 | ||
| 1011 | // zero limits = no limits | |
| 40d3df5c | 1012 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0); |
| 5d91786a | 1013 | $this->assertEqual(7, count($records)); |
| 1014 | ||
| 1015 | // only limitfrom = skips that number of records | |
| 40d3df5c | 1016 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0); |
| 5d91786a | 1017 | $this->assertEqual(5, count($records)); |
| 1018 | $this->assertEqual($inskey3, reset($records)->id); | |
| 1019 | $this->assertEqual($inskey7, end($records)->id); | |
| 1020 | ||
| 1021 | // only limitnum = fetches that number of records | |
| 40d3df5c | 1022 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3); |
| 5d91786a | 1023 | $this->assertEqual(3, count($records)); |
| 1024 | $this->assertEqual($inskey1, reset($records)->id); | |
| 1025 | $this->assertEqual($inskey3, end($records)->id); | |
| 1026 | ||
| 1027 | // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones | |
| 40d3df5c | 1028 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2); |
| 5d91786a | 1029 | $this->assertEqual(2, count($records)); |
| 1030 | $this->assertEqual($inskey4, reset($records)->id); | |
| 1031 | $this->assertEqual($inskey5, end($records)->id); | |
| 1032 | ||
| 1033 | // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here | |
| 0088bd31 | 1034 | } |
| 1035 | ||
| 1036 | public function test_get_records_menu() { | |
| 50a12c87 | 1037 | $DB = $this->tdb; |
| 1038 | $dbman = $DB->get_manager(); | |
| 1039 | ||
| 9d833e93 | 1040 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1041 | $tablename = $table->getName(); |
| 1042 | ||
| f9ecb171 | 1043 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1044 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1045 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1046 | $dbman->create_table($table); | |
| 50a12c87 | 1047 | |
| 3ff8bf26 | 1048 | $DB->insert_record($tablename, array('course' => 3)); |
| 1049 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1050 | $DB->insert_record($tablename, array('course' => 5)); | |
| 1051 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 1052 | |
| 76f8ddc0 PS |
1053 | $records = $DB->get_records_menu($tablename, array('course' => 3)); |
| 1054 | $this->assertTrue(is_array($records)); | |
| 50a12c87 | 1055 | $this->assertEqual(2, count($records)); |
| 1056 | $this->assertFalse(empty($records[1])); | |
| 1057 | $this->assertFalse(empty($records[2])); | |
| 1058 | $this->assertEqual(3, $records[1]); | |
| 1059 | $this->assertEqual(3, $records[2]); | |
| 0088bd31 | 1060 | |
| 5d91786a | 1061 | // note: delegate limits testing to test_get_records_sql() |
| 0088bd31 | 1062 | } |
| 1063 | ||
| 1064 | public function test_get_records_select_menu() { | |
| 50a12c87 | 1065 | $DB = $this->tdb; |
| 1066 | $dbman = $DB->get_manager(); | |
| 1067 | ||
| 9d833e93 | 1068 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1069 | $tablename = $table->getName(); |
| 1070 | ||
| f9ecb171 | 1071 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1072 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1073 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1074 | $dbman->create_table($table); | |
| 50a12c87 | 1075 | |
| 3ff8bf26 | 1076 | $DB->insert_record($tablename, array('course' => 3)); |
| 1077 | $DB->insert_record($tablename, array('course' => 2)); | |
| 1078 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1079 | $DB->insert_record($tablename, array('course' => 5)); | |
| 50a12c87 | 1080 | |
| 76f8ddc0 PS |
1081 | $records = $DB->get_records_select_menu($tablename, "course > ?", array(2)); |
| 1082 | $this->assertTrue(is_array($records)); | |
| 50a12c87 | 1083 | |
| 1084 | $this->assertEqual(3, count($records)); | |
| 1085 | $this->assertFalse(empty($records[1])); | |
| 1086 | $this->assertTrue(empty($records[2])); | |
| 1087 | $this->assertFalse(empty($records[3])); | |
| 1088 | $this->assertFalse(empty($records[4])); | |
| 1089 | $this->assertEqual(3, $records[1]); | |
| 1090 | $this->assertEqual(3, $records[3]); | |
| 1091 | $this->assertEqual(5, $records[4]); | |
| 0088bd31 | 1092 | |
| 5d91786a | 1093 | // note: delegate limits testing to test_get_records_sql() |
| 0088bd31 | 1094 | } |
| 1095 | ||
| 1096 | public function test_get_records_sql_menu() { | |
| 50a12c87 | 1097 | $DB = $this->tdb; |
| 1098 | $dbman = $DB->get_manager(); | |
| 1099 | ||
| 9d833e93 | 1100 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1101 | $tablename = $table->getName(); |
| 1102 | ||
| f9ecb171 | 1103 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1104 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1105 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1106 | $dbman->create_table($table); | |
| 50a12c87 | 1107 | |
| 3ff8bf26 | 1108 | $DB->insert_record($tablename, array('course' => 3)); |
| 1109 | $DB->insert_record($tablename, array('course' => 2)); | |
| 1110 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1111 | $DB->insert_record($tablename, array('course' => 5)); | |
| 50a12c87 | 1112 | |
| 76f8ddc0 PS |
1113 | $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2)); |
| 1114 | $this->assertTrue(is_array($records)); | |
| 50a12c87 | 1115 | |
| 1116 | $this->assertEqual(3, count($records)); | |
| 1117 | $this->assertFalse(empty($records[1])); | |
| 1118 | $this->assertTrue(empty($records[2])); | |
| 1119 | $this->assertFalse(empty($records[3])); | |
| 1120 | $this->assertFalse(empty($records[4])); | |
| 1121 | $this->assertEqual(3, $records[1]); | |
| 1122 | $this->assertEqual(3, $records[3]); | |
| 1123 | $this->assertEqual(5, $records[4]); | |
| 0088bd31 | 1124 | |
| 5d91786a | 1125 | // note: delegate limits testing to test_get_records_sql() |
| 0088bd31 | 1126 | } |
| 1127 | ||
| 1128 | public function test_get_record() { | |
| 50a12c87 | 1129 | $DB = $this->tdb; |
| 1130 | $dbman = $DB->get_manager(); | |
| 1131 | ||
| 9d833e93 | 1132 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1133 | $tablename = $table->getName(); |
| 1134 | ||
| f9ecb171 | 1135 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1136 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1137 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1138 | $dbman->create_table($table); | |
| 50a12c87 | 1139 | |
| 3ff8bf26 | 1140 | $DB->insert_record($tablename, array('course' => 3)); |
| 1141 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 1142 | |
| 76f8ddc0 PS |
1143 | $record = $DB->get_record($tablename, array('id' => 2)); |
| 1144 | $this->assertTrue($record instanceof stdClass); | |
| 0088bd31 | 1145 | |
| 50a12c87 | 1146 | $this->assertEqual(2, $record->course); |
| 76f8ddc0 PS |
1147 | $this->assertEqual(2, $record->id); |
| 1148 | } | |
| 1149 | ||
| 1150 | ||
| 1151 | public function test_get_record_select() { | |
| 1152 | $DB = $this->tdb; | |
| 1153 | $dbman = $DB->get_manager(); | |
| 1154 | ||
| 1155 | $table = $this->get_test_table(); | |
| 1156 | $tablename = $table->getName(); | |
| 1157 | ||
| 1158 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 1159 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 1160 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 1161 | $dbman->create_table($table); | |
| 76f8ddc0 PS |
1162 | |
| 1163 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1164 | $DB->insert_record($tablename, array('course' => 2)); | |
| 1165 | ||
| 1166 | $record = $DB->get_record_select($tablename, "id = ?", array(2)); | |
| 1167 | $this->assertTrue($record instanceof stdClass); | |
| 1168 | ||
| 1169 | $this->assertEqual(2, $record->course); | |
| 1170 | ||
| 1171 | // note: delegates limit testing to test_get_records_sql() | |
| 0088bd31 | 1172 | } |
| 1173 | ||
| 0088bd31 | 1174 | public function test_get_record_sql() { |
| 50a12c87 | 1175 | $DB = $this->tdb; |
| 1176 | $dbman = $DB->get_manager(); | |
| 1177 | ||
| 9d833e93 | 1178 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1179 | $tablename = $table->getName(); |
| 1180 | ||
| f9ecb171 | 1181 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1182 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1183 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1184 | $dbman->create_table($table); | |
| 50a12c87 | 1185 | |
| 3ff8bf26 | 1186 | $DB->insert_record($tablename, array('course' => 3)); |
| 1187 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 1188 | |
| 66556e7a PS |
1189 | // standard use |
| 1190 | $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2)); | |
| 1191 | $this->assertTrue($record instanceof stdClass); | |
| 50a12c87 | 1192 | $this->assertEqual(2, $record->course); |
| 66556e7a | 1193 | $this->assertEqual(2, $record->id); |
| 0088bd31 | 1194 | |
| e6c6531c | 1195 | // backwards compatibility with $ignoremultiple |
| 1196 | $this->assertFalse(IGNORE_MISSING); | |
| 1197 | $this->assertTrue(IGNORE_MULTIPLE); | |
| 1198 | ||
| 66556e7a PS |
1199 | // record not found - ignore |
| 1200 | $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING)); | |
| 1201 | $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE)); | |
| 1202 | ||
| 1203 | // record not found error | |
| af12ea93 | 1204 | try { |
| 40d3df5c | 1205 | $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST); |
| af12ea93 | 1206 | $this->fail("Exception expected"); |
| 1207 | } catch (dml_missing_record_exception $e) { | |
| 1208 | $this->assertTrue(true); | |
| 1209 | } | |
| 1210 | ||
| ebdf7f7e | 1211 | $this->enable_debugging(); |
| 66556e7a | 1212 | $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING)); |
| ebdf7f7e | 1213 | $this->assertFalse($this->get_debugging() === ''); |
| 66556e7a PS |
1214 | |
| 1215 | // multiple matches ignored | |
| 1216 | $this->assertTrue($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE)); | |
| 1217 | ||
| 1218 | // multiple found error | |
| af12ea93 | 1219 | try { |
| 40d3df5c | 1220 | $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST); |
| af12ea93 | 1221 | $this->fail("Exception expected"); |
| 1222 | } catch (dml_multiple_records_exception $e) { | |
| 1223 | $this->assertTrue(true); | |
| 1224 | } | |
| 0088bd31 | 1225 | } |
| 1226 | ||
| 1227 | public function test_get_field() { | |
| 50a12c87 | 1228 | $DB = $this->tdb; |
| 1229 | $dbman = $DB->get_manager(); | |
| 1230 | ||
| 9d833e93 | 1231 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1232 | $tablename = $table->getName(); |
| 1233 | ||
| f9ecb171 | 1234 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1235 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1236 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1237 | $dbman->create_table($table); | |
| 0088bd31 | 1238 | |
| d7f42e77 PS |
1239 | $id1 = $DB->insert_record($tablename, array('course' => 3)); |
| 1240 | $DB->insert_record($tablename, array('course' => 5)); | |
| 2b3ccae5 | 1241 | $DB->insert_record($tablename, array('course' => 5)); |
| bc4fd49d | 1242 | |
| 2b3ccae5 | 1243 | $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id1))); |
| bc4fd49d | 1244 | $this->assertEqual(3, $DB->get_field($tablename, 'course', array('course' => 3))); |
| 2b3ccae5 PS |
1245 | |
| 1246 | $this->assertIdentical(false, $DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING)); | |
| 1247 | try { | |
| 1248 | $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST); | |
| 1249 | $this->assertFail('Exception expected due to missing record'); | |
| 1250 | } catch (dml_exception $ex) { | |
| 1251 | $this->assertTrue(true); | |
| 1252 | } | |
| 1253 | ||
| ebdf7f7e | 1254 | $this->enable_debugging(); |
| 2b3ccae5 | 1255 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE)); |
| ebdf7f7e | 1256 | $this->assertIdentical($this->get_debugging(), ''); |
| 2b3ccae5 | 1257 | |
| ebdf7f7e | 1258 | $this->enable_debugging(); |
| 2b3ccae5 | 1259 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING)); |
| ebdf7f7e | 1260 | $this->assertFalse($this->get_debugging() === ''); |
| 0088bd31 | 1261 | } |
| 1262 | ||
| 1263 | public function test_get_field_select() { | |
| 50a12c87 | 1264 | $DB = $this->tdb; |
| 1265 | $dbman = $DB->get_manager(); | |
| 1266 | ||
| 9d833e93 | 1267 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1268 | $tablename = $table->getName(); |
| 1269 | ||
| f9ecb171 | 1270 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1271 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1272 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1273 | $dbman->create_table($table); | |
| 50a12c87 | 1274 | |
| 3ff8bf26 | 1275 | $DB->insert_record($tablename, array('course' => 3)); |
| 50a12c87 | 1276 | |
| 66556e7a | 1277 | $this->assertEqual(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1))); |
| 0088bd31 | 1278 | } |
| 1279 | ||
| 1280 | public function test_get_field_sql() { | |
| 50a12c87 | 1281 | $DB = $this->tdb; |
| 1282 | $dbman = $DB->get_manager(); | |
| 1283 | ||
| 9d833e93 | 1284 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1285 | $tablename = $table->getName(); |
| 1286 | ||
| f9ecb171 | 1287 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1288 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1289 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1290 | $dbman->create_table($table); | |
| 50a12c87 | 1291 | |
| 3ff8bf26 | 1292 | $DB->insert_record($tablename, array('course' => 3)); |
| 50a12c87 | 1293 | |
| 66556e7a | 1294 | $this->assertEqual(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1))); |
| 0088bd31 | 1295 | } |
| 1296 | ||
| 50a12c87 | 1297 | public function test_get_fieldset_select() { |
| 1298 | $DB = $this->tdb; | |
| 1299 | $dbman = $DB->get_manager(); | |
| 1300 | ||
| 9d833e93 | 1301 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1302 | $tablename = $table->getName(); |
| 1303 | ||
| f9ecb171 | 1304 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1305 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1306 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1307 | $dbman->create_table($table); | |
| 50a12c87 | 1308 | |
| 3ff8bf26 | 1309 | $DB->insert_record($tablename, array('course' => 1)); |
| 1310 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1311 | $DB->insert_record($tablename, array('course' => 2)); | |
| 1312 | $DB->insert_record($tablename, array('course' => 6)); | |
| 50a12c87 | 1313 | |
| 66556e7a PS |
1314 | $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)); |
| 1315 | $this->assertTrue(is_array($fieldset)); | |
| 50a12c87 | 1316 | |
| 1317 | $this->assertEqual(3, count($fieldset)); | |
| 1318 | $this->assertEqual(3, $fieldset[0]); | |
| 1319 | $this->assertEqual(2, $fieldset[1]); | |
| 1320 | $this->assertEqual(6, $fieldset[2]); | |
| 0088bd31 | 1321 | } |
| 1322 | ||
| 1323 | public function test_get_fieldset_sql() { | |
| 50a12c87 | 1324 | $DB = $this->tdb; |
| 1325 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 1326 | |
| 9d833e93 | 1327 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1328 | $tablename = $table->getName(); |
| 1329 | ||
| f9ecb171 | 1330 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1331 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1332 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1333 | $dbman->create_table($table); | |
| 50a12c87 | 1334 | |
| 3ff8bf26 | 1335 | $DB->insert_record($tablename, array('course' => 1)); |
| 1336 | $DB->insert_record($tablename, array('course' => 3)); | |
| 1337 | $DB->insert_record($tablename, array('course' => 2)); | |
| 1338 | $DB->insert_record($tablename, array('course' => 6)); | |
| 50a12c87 | 1339 | |
| 66556e7a PS |
1340 | $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1)); |
| 1341 | $this->assertTrue(is_array($fieldset)); | |
| 50a12c87 | 1342 | |
| 1343 | $this->assertEqual(3, count($fieldset)); | |
| 1344 | $this->assertEqual(2, $fieldset[0]); | |
| 1345 | $this->assertEqual(3, $fieldset[1]); | |
| 1346 | $this->assertEqual(4, $fieldset[2]); | |
| 0088bd31 | 1347 | } |
| 1348 | ||
| 1349 | public function test_insert_record_raw() { | |
| 50a12c87 | 1350 | $DB = $this->tdb; |
| 1351 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 1352 | |
| 9d833e93 | 1353 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1354 | $tablename = $table->getName(); |
| 1355 | ||
| f9ecb171 | 1356 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1357 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 7374c35c | 1358 | $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); |
| 50a12c87 | 1359 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1360 | $dbman->create_table($table); | |
| 50a12c87 | 1361 | |
| 8ca7b3df PS |
1362 | $record = (object)array('course' => 1, 'onechar' => 'xx'); |
| 1363 | $before = clone($record); | |
| 1364 | $result = $DB->insert_record_raw($tablename, $record); | |
| 4d7b0467 | 1365 | $this->assertIdentical(1, $result); |
| 8ca7b3df | 1366 | $this->assertIdentical($record, $before); |
| 7374c35c PS |
1367 | |
| 1368 | $record = $DB->get_record($tablename, array('course' => 1)); | |
| 1369 | $this->assertTrue($record instanceof stdClass); | |
| 1370 | $this->assertIdentical('xx', $record->onechar); | |
| 1371 | ||
| 1372 | $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false); | |
| 1373 | $this->assertIdentical(true, $result); | |
| 1374 | ||
| 1375 | // note: bulk not implemented yet | |
| 1376 | $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true); | |
| 1377 | $record = $DB->get_record($tablename, array('course' => 3)); | |
| 1378 | $this->assertTrue($record instanceof stdClass); | |
| 1379 | $this->assertIdentical('zz', $record->onechar); | |
| 1380 | ||
| 1381 | // custom sequence (id) - returnid is ignored | |
| 1382 | $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true); | |
| 1383 | $this->assertIdentical(true, $result); | |
| 1384 | $record = $DB->get_record($tablename, array('id' => 10)); | |
| 1385 | $this->assertTrue($record instanceof stdClass); | |
| 1386 | $this->assertIdentical('bb', $record->onechar); | |
| 4d7b0467 PS |
1387 | |
| 1388 | // custom sequence - missing id error | |
| 1389 | try { | |
| 1390 | $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true); | |
| 1391 | $this->assertFail('Exception expected due to missing record'); | |
| 1392 | } catch (coding_exception $ex) { | |
| 1393 | $this->assertTrue(true); | |
| 1394 | } | |
| 3b633796 PS |
1395 | |
| 1396 | // wrong column error | |
| 1397 | try { | |
| 1398 | $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb')); | |
| 1399 | $this->assertFail('Exception expected due to invalid column'); | |
| 1400 | } catch (dml_write_exception $ex) { | |
| 1401 | $this->assertTrue(true); | |
| 1402 | } | |
| 0088bd31 | 1403 | } |
| 1404 | ||
| 1405 | public function test_insert_record() { | |
| 628dff25 | 1406 | // All the information in this test is fetched from DB by get_recordset() so we |
| 1407 | // have such method properly tested against nulls, empties and friends... | |
| 1408 | ||
| 50a12c87 | 1409 | $DB = $this->tdb; |
| 1410 | $dbman = $DB->get_manager(); | |
| 1411 | ||
| 9d833e93 | 1412 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1413 | $tablename = $table->getName(); |
| 1414 | ||
| f9ecb171 | 1415 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1416 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 628dff25 | 1417 | $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100); |
| 1418 | $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); | |
| 1419 | $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); | |
| 1420 | $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); | |
| 1421 | $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); | |
| 50a12c87 | 1422 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1423 | $dbman->create_table($table); | |
| 50a12c87 | 1424 | |
| 4d7b0467 PS |
1425 | $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true)); |
| 1426 | $record = $DB->get_record($tablename, array('course' => 1)); | |
| 628dff25 | 1427 | $this->assertEqual(1, $record->id); |
| 1428 | $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied | |
| 1429 | $this->assertEqual(200, $record->onenum); | |
| 4d7b0467 | 1430 | $this->assertIdentical('onestring', $record->onechar); |
| 628dff25 | 1431 | $this->assertNull($record->onetext); |
| 1432 | $this->assertNull($record->onebinary); | |
| 1433 | ||
| 4d7b0467 PS |
1434 | // without returning id, bulk not implemented |
| 1435 | $result = $this->assertIdentical(true, $DB->insert_record($tablename, array('course' => 99), false, true)); | |
| 1436 | $record = $DB->get_record($tablename, array('course' => 99)); | |
| 1437 | $this->assertEqual(2, $record->id); | |
| 1438 | $this->assertEqual(99, $record->course); | |
| 1439 | ||
| 628dff25 | 1440 | // Check nulls are set properly for all types |
| 4d7b0467 | 1441 | $record = new object(); |
| 628dff25 | 1442 | $record->oneint = null; |
| 1443 | $record->onenum = null; | |
| 1444 | $record->onechar = null; | |
| 1445 | $record->onetext = null; | |
| 1446 | $record->onebinary = null; | |
| 1447 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 PS |
1448 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 1449 | $this->assertEqual(0, $record->course); | |
| 628dff25 | 1450 | $this->assertNull($record->oneint); |
| 1451 | $this->assertNull($record->onenum); | |
| 1452 | $this->assertNull($record->onechar); | |
| 1453 | $this->assertNull($record->onetext); | |
| 1454 | $this->assertNull($record->onebinary); | |
| 1455 | ||
| 1456 | // Check zeros are set properly for all types | |
| 4d7b0467 | 1457 | $record = new object(); |
| 628dff25 | 1458 | $record->oneint = 0; |
| 1459 | $record->onenum = 0; | |
| 1460 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1461 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1462 | $this->assertEqual(0, $record->oneint); |
| 1463 | $this->assertEqual(0, $record->onenum); | |
| 1464 | ||
| 1465 | // Check booleans are set properly for all types | |
| 4d7b0467 | 1466 | $record = new object(); |
| 628dff25 | 1467 | $record->oneint = true; // trues |
| 1468 | $record->onenum = true; | |
| 1469 | $record->onechar = true; | |
| 1470 | $record->onetext = true; | |
| 1471 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1472 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1473 | $this->assertEqual(1, $record->oneint); |
| 1474 | $this->assertEqual(1, $record->onenum); | |
| 1475 | $this->assertEqual(1, $record->onechar); | |
| 1476 | $this->assertEqual(1, $record->onetext); | |
| 1477 | ||
| 4d7b0467 | 1478 | $record = new object(); |
| 628dff25 | 1479 | $record->oneint = false; // falses |
| 1480 | $record->onenum = false; | |
| 1481 | $record->onechar = false; | |
| 1482 | $record->onetext = false; | |
| 1483 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1484 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1485 | $this->assertEqual(0, $record->oneint); |
| 1486 | $this->assertEqual(0, $record->onenum); | |
| 1487 | $this->assertEqual(0, $record->onechar); | |
| 1488 | $this->assertEqual(0, $record->onetext); | |
| 1489 | ||
| 1490 | // Check string data causes exception in numeric types | |
| 4d7b0467 | 1491 | $record = new object(); |
| 628dff25 | 1492 | $record->oneint = 'onestring'; |
| 1493 | $record->onenum = 0; | |
| 1494 | try { | |
| 1495 | $DB->insert_record($tablename, $record); | |
| 1496 | $this->fail("Expecting an exception, none occurred"); | |
| 1497 | } catch (exception $e) { | |
| 1498 | $this->assertTrue($e instanceof dml_exception); | |
| 1499 | } | |
| 4d7b0467 | 1500 | $record = new object(); |
| 628dff25 | 1501 | $record->oneint = 0; |
| 1502 | $record->onenum = 'onestring'; | |
| 1503 | try { | |
| 1504 | $DB->insert_record($tablename, $record); | |
| 1505 | $this->fail("Expecting an exception, none occurred"); | |
| 1506 | } catch (exception $e) { | |
| 1507 | $this->assertTrue($e instanceof dml_exception); | |
| 1508 | } | |
| 1509 | ||
| 2f9c1693 | 1510 | // Check empty string data is stored as 0 in numeric datatypes |
| 4d7b0467 | 1511 | $record = new object(); |
| fdc45ac3 EL |
1512 | $record->oneint = ''; // empty string |
| 1513 | $record->onenum = 0; | |
| 2f9c1693 | 1514 | $recid = $DB->insert_record($tablename, $record); |
| 4d7b0467 | 1515 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 2f9c1693 EL |
1516 | $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0); |
| 1517 | ||
| 4d7b0467 | 1518 | $record = new object(); |
| fdc45ac3 EL |
1519 | $record->oneint = 0; |
| 1520 | $record->onenum = ''; // empty string | |
| 2f9c1693 | 1521 | $recid = $DB->insert_record($tablename, $record); |
| 4d7b0467 | 1522 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 2f9c1693 | 1523 | $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0); |
| fdc45ac3 | 1524 | |
| 628dff25 | 1525 | // Check empty strings are set properly in string types |
| 4d7b0467 | 1526 | $record = new object(); |
| 628dff25 | 1527 | $record->oneint = 0; |
| 1528 | $record->onenum = 0; | |
| 1529 | $record->onechar = ''; | |
| 1530 | $record->onetext = ''; | |
| 1531 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1532 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1533 | $this->assertTrue($record->onechar === ''); |
| 1534 | $this->assertTrue($record->onetext === ''); | |
| 1535 | ||
| 1536 | // Check operation ((210.10 + 39.92) - 150.02) against numeric types | |
| 4d7b0467 | 1537 | $record = new object(); |
| 628dff25 | 1538 | $record->oneint = ((210.10 + 39.92) - 150.02); |
| 1539 | $record->onenum = ((210.10 + 39.92) - 150.02); | |
| 1540 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1541 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1542 | $this->assertEqual(100, $record->oneint); |
| 1543 | $this->assertEqual(100, $record->onenum); | |
| 1544 | ||
| 1545 | // Check various quotes/backslashes combinations in string types | |
| 1546 | $teststrings = array( | |
| 1547 | 'backslashes and quotes alone (even): "" \'\' \\\\', | |
| 1548 | 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', | |
| 1549 | 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', | |
| 1550 | 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); | |
| 1551 | foreach ($teststrings as $teststring) { | |
| 4d7b0467 | 1552 | $record = new object(); |
| 628dff25 | 1553 | $record->onechar = $teststring; |
| 1554 | $record->onetext = $teststring; | |
| 1555 | $recid = $DB->insert_record($tablename, $record); | |
| 4d7b0467 | 1556 | $record = $DB->get_record($tablename, array('id' => $recid)); |
| 628dff25 | 1557 | $this->assertEqual($teststring, $record->onechar); |
| 1558 | $this->assertEqual($teststring, $record->onetext); | |
| 1559 | } | |
| 1560 | ||
| 1561 | // Check LOBs in text/binary columns | |
| ebdf7f7e PS |
1562 | $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt'); |
| 1563 | $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary'); | |
| 4d7b0467 | 1564 | $record = new object(); |
| 628dff25 | 1565 | $record->onetext = $clob; |
| 1566 | $record->onebinary = $blob; | |
| 1567 | $recid = $DB->insert_record($tablename, $record); | |
| 1568 | $rs = $DB->get_recordset($tablename, array('id' => $recid)); | |
| 1569 | $record = $rs->current(); | |
| 1570 | $rs->close(); | |
| ff8c7b2a EL |
1571 | $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)'); |
| 1572 | $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)'); | |
| 0088bd31 | 1573 | |
| 628dff25 | 1574 | // And "small" LOBs too, just in case |
| 1575 | $newclob = substr($clob, 0, 500); | |
| 1576 | $newblob = substr($blob, 0, 250); | |
| 4d7b0467 | 1577 | $record = new object(); |
| 628dff25 | 1578 | $record->onetext = $newclob; |
| 1579 | $record->onebinary = $newblob; | |
| 1580 | $recid = $DB->insert_record($tablename, $record); | |
| 1581 | $rs = $DB->get_recordset($tablename, array('id' => $recid)); | |
| 1582 | $record = $rs->current(); | |
| 1583 | $rs->close(); | |
| ff8c7b2a EL |
1584 | $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)'); |
| 1585 | $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)'); | |
| 5d91786a | 1586 | $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing |
| e9fec42f | 1587 | |
| 4e999612 PS |
1588 | // And "diagnostic" LOBs too, just in case |
| 1589 | $newclob = '\'"\\;/ěščřžýáíé'; | |
| 1590 | $newblob = '\'"\\;/ěščřžýáíé'; | |
| 1591 | $record = new object(); | |
| 1592 | $record->onetext = $newclob; | |
| 1593 | $record->onebinary = $newblob; | |
| 1594 | $recid = $DB->insert_record($tablename, $record); | |
| 1595 | $rs = $DB->get_recordset($tablename, array('id' => $recid)); | |
| 1596 | $record = $rs->current(); | |
| 1597 | $rs->close(); | |
| 1598 | $this->assertIdentical($newclob, $record->onetext); | |
| 1599 | $this->assertIdentical($newblob, $record->onebinary); | |
| 1600 | $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing | |
| 1601 | ||
| e9fec42f | 1602 | // test data is not modified |
| 4d7b0467 PS |
1603 | $record = new object(); |
| 1604 | $record->id = -1; // has to be ignored | |
| 1605 | $record->course = 3; | |
| 1606 | $record->lalala = 'lalal'; // unused | |
| 1607 | $before = clone($record); | |
| e9fec42f | 1608 | $DB->insert_record($tablename, $record); |
| 4d7b0467 | 1609 | $this->assertEqual($record, $before); |
| 27323c97 PS |
1610 | |
| 1611 | // make sure the id is always increasing and never reuses the same id | |
| 1612 | $id1 = $DB->insert_record($tablename, array('course' => 3)); | |
| 1613 | $id2 = $DB->insert_record($tablename, array('course' => 3)); | |
| 1614 | $this->assertTrue($id1 < $id2); | |
| 1615 | $DB->delete_records($tablename, array('id'=>$id2)); | |
| 1616 | $id3 = $DB->insert_record($tablename, array('course' => 3)); | |
| 1617 | $this->assertTrue($id2 < $id3); | |
| 1618 | $DB->delete_records($tablename, array()); | |
| 1619 | $id4 = $DB->insert_record($tablename, array('course' => 3)); | |
| 1620 | $this->assertTrue($id3 < $id4); | |
| 0088bd31 | 1621 | } |
| 1622 | ||
| 94898738 | 1623 | public function test_import_record() { |
| 969cb35f PS |
1624 | // All the information in this test is fetched from DB by get_recordset() so we |
| 1625 | // have such method properly tested against nulls, empties and friends... | |
| 1626 | ||
| 94898738 | 1627 | $DB = $this->tdb; |
| 1628 | $dbman = $DB->get_manager(); | |
| 1629 | ||
| 9d833e93 | 1630 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1631 | $tablename = $table->getName(); |
| 1632 | ||
| f9ecb171 | 1633 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1634 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 969cb35f PS |
1635 | $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100); |
| 1636 | $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); | |
| 1637 | $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); | |
| 1638 | $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); | |
| 1639 | $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); | |
| 94898738 | 1640 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1641 | $dbman->create_table($table); | |
| 94898738 | 1642 | |
| 969cb35f PS |
1643 | $this->assertIdentical(1, $DB->insert_record($tablename, array('course' => 1), true)); |
| 1644 | $record = $DB->get_record($tablename, array('course' => 1)); | |
| 1645 | $this->assertEqual(1, $record->id); | |
| 1646 | $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied | |
| 1647 | $this->assertEqual(200, $record->onenum); | |
| 1648 | $this->assertIdentical('onestring', $record->onechar); | |
| 1649 | $this->assertNull($record->onetext); | |
| 1650 | $this->assertNull($record->onebinary); | |
| 94898738 | 1651 | |
| 8ca7b3df PS |
1652 | // ignore extra columns |
| 1653 | $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778); | |
| 1654 | $before = clone($record); | |
| 1655 | $this->assertIdentical(true, $DB->import_record($tablename, $record)); | |
| 1656 | $this->assertIdentical($record, $before); | |
| 3ff8bf26 | 1657 | $records = $DB->get_records($tablename); |
| 94898738 | 1658 | $this->assertEqual(2, $records[13]->course); |
| 969cb35f PS |
1659 | |
| 1660 | // Check nulls are set properly for all types | |
| 1661 | $record = new object(); | |
| 1662 | $record->id = 20; | |
| 1663 | $record->oneint = null; | |
| 1664 | $record->onenum = null; | |
| 1665 | $record->onechar = null; | |
| 1666 | $record->onetext = null; | |
| 1667 | $record->onebinary = null; | |
| 1668 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1669 | $record = $DB->get_record($tablename, array('id' => 20)); | |
| 1670 | $this->assertEqual(0, $record->course); | |
| 1671 | $this->assertNull($record->oneint); | |
| 1672 | $this->assertNull($record->onenum); | |
| 1673 | $this->assertNull($record->onechar); | |
| 1674 | $this->assertNull($record->onetext); | |
| 1675 | $this->assertNull($record->onebinary); | |
| 1676 | ||
| 1677 | // Check zeros are set properly for all types | |
| 1678 | $record = new object(); | |
| 1679 | $record->id = 23; | |
| 1680 | $record->oneint = 0; | |
| 1681 | $record->onenum = 0; | |
| 1682 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1683 | $record = $DB->get_record($tablename, array('id' => 23)); | |
| 1684 | $this->assertEqual(0, $record->oneint); | |
| 1685 | $this->assertEqual(0, $record->onenum); | |
| 1686 | ||
| 1687 | // Check string data causes exception in numeric types | |
| 1688 | $record = new object(); | |
| 1689 | $record->id = 32; | |
| 1690 | $record->oneint = 'onestring'; | |
| 1691 | $record->onenum = 0; | |
| 1692 | try { | |
| 1693 | $DB->import_record($tablename, $record); | |
| 1694 | $this->fail("Expecting an exception, none occurred"); | |
| 1695 | } catch (exception $e) { | |
| 1696 | $this->assertTrue($e instanceof dml_exception); | |
| 1697 | } | |
| 1698 | $record = new object(); | |
| 1699 | $record->id = 35; | |
| 1700 | $record->oneint = 0; | |
| 1701 | $record->onenum = 'onestring'; | |
| 1702 | try { | |
| 1703 | $DB->import_record($tablename, $record); | |
| 1704 | $this->fail("Expecting an exception, none occurred"); | |
| 1705 | } catch (exception $e) { | |
| 1706 | $this->assertTrue($e instanceof dml_exception); | |
| 1707 | } | |
| 1708 | ||
| 1709 | // Check empty strings are set properly in string types | |
| 1710 | $record = new object(); | |
| 1711 | $record->id = 44; | |
| 1712 | $record->oneint = 0; | |
| 1713 | $record->onenum = 0; | |
| 1714 | $record->onechar = ''; | |
| 1715 | $record->onetext = ''; | |
| 1716 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1717 | $record = $DB->get_record($tablename, array('id' => 44)); | |
| 1718 | $this->assertTrue($record->onechar === ''); | |
| 1719 | $this->assertTrue($record->onetext === ''); | |
| 1720 | ||
| 1721 | // Check operation ((210.10 + 39.92) - 150.02) against numeric types | |
| 1722 | $record = new object(); | |
| 1723 | $record->id = 47; | |
| 1724 | $record->oneint = ((210.10 + 39.92) - 150.02); | |
| 1725 | $record->onenum = ((210.10 + 39.92) - 150.02); | |
| 1726 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1727 | $record = $DB->get_record($tablename, array('id' => 47)); | |
| 1728 | $this->assertEqual(100, $record->oneint); | |
| 1729 | $this->assertEqual(100, $record->onenum); | |
| 1730 | ||
| 1731 | // Check various quotes/backslashes combinations in string types | |
| 1732 | $i = 50; | |
| 1733 | $teststrings = array( | |
| 1734 | 'backslashes and quotes alone (even): "" \'\' \\\\', | |
| 1735 | 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', | |
| 1736 | 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', | |
| 1737 | 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); | |
| 1738 | foreach ($teststrings as $teststring) { | |
| 1739 | $record = new object(); | |
| 1740 | $record->id = $i; | |
| 1741 | $record->onechar = $teststring; | |
| 1742 | $record->onetext = $teststring; | |
| 1743 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1744 | $record = $DB->get_record($tablename, array('id' => $i)); | |
| 1745 | $this->assertEqual($teststring, $record->onechar); | |
| 1746 | $this->assertEqual($teststring, $record->onetext); | |
| 1747 | $i = $i + 3; | |
| 1748 | } | |
| 1749 | ||
| 1750 | // Check LOBs in text/binary columns | |
| ebdf7f7e | 1751 | $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt'); |
| 969cb35f PS |
1752 | $record = new object(); |
| 1753 | $record->id = 70; | |
| 1754 | $record->onetext = $clob; | |
| 1755 | $record->onebinary = ''; | |
| 1756 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1757 | $rs = $DB->get_recordset($tablename, array('id' => 70)); | |
| 1758 | $record = $rs->current(); | |
| 1759 | $rs->close(); | |
| 1760 | $this->assertEqual($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)'); | |
| 1761 | ||
| ebdf7f7e | 1762 | $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary'); |
| 969cb35f PS |
1763 | $record = new object(); |
| 1764 | $record->id = 71; | |
| 1765 | $record->onetext = ''; | |
| 1766 | $record->onebinary = $blob; | |
| 1767 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1768 | $rs = $DB->get_recordset($tablename, array('id' => 71)); | |
| 1769 | $record = $rs->current(); | |
| 1770 | $rs->close(); | |
| 1771 | $this->assertEqual($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)'); | |
| 1772 | ||
| 1773 | // And "small" LOBs too, just in case | |
| 1774 | $newclob = substr($clob, 0, 500); | |
| 1775 | $newblob = substr($blob, 0, 250); | |
| 1776 | $record = new object(); | |
| 1777 | $record->id = 73; | |
| 1778 | $record->onetext = $newclob; | |
| 1779 | $record->onebinary = $newblob; | |
| 1780 | $this->assertTrue($DB->import_record($tablename, $record)); | |
| 1781 | $rs = $DB->get_recordset($tablename, array('id' => 73)); | |
| 1782 | $record = $rs->current(); | |
| 1783 | $rs->close(); | |
| 1784 | $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)'); | |
| 1785 | $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)'); | |
| 1786 | $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing | |
| 94898738 | 1787 | } |
| 1788 | ||
| 0088bd31 | 1789 | public function test_update_record_raw() { |
| 50a12c87 | 1790 | $DB = $this->tdb; |
| 1791 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 1792 | |
| 9d833e93 | 1793 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1794 | $tablename = $table->getName(); |
| 1795 | ||
| f9ecb171 | 1796 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1797 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 1798 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1799 | $dbman->create_table($table); | |
| 50a12c87 | 1800 | |
| 3ff8bf26 | 1801 | $DB->insert_record($tablename, array('course' => 1)); |
| 5ab41c27 PS |
1802 | $DB->insert_record($tablename, array('course' => 3)); |
| 1803 | ||
| 3ff8bf26 | 1804 | $record = $DB->get_record($tablename, array('course' => 1)); |
| 50a12c87 | 1805 | $record->course = 2; |
| 3ff8bf26 | 1806 | $this->assertTrue($DB->update_record_raw($tablename, $record)); |
| 5ab41c27 PS |
1807 | $this->assertEqual(0, $DB->count_records($tablename, array('course' => 1))); |
| 1808 | $this->assertEqual(1, $DB->count_records($tablename, array('course' => 2))); | |
| 1809 | $this->assertEqual(1, $DB->count_records($tablename, array('course' => 3))); | |
| 1810 | ||
| 1811 | $record = $DB->get_record($tablename, array('course' => 1)); | |
| 1812 | $record->xxxxx = 2; | |
| 1813 | try { | |
| 1814 | $DB->update_record_raw($tablename, $record); | |
| 1815 | $this->fail("Expecting an exception, none occurred"); | |
| 1816 | } catch (Exception $e) { | |
| 1817 | $this->assertTrue($e instanceof coding_exception); | |
| 1818 | } | |
| 1819 | ||
| 1820 | $record = $DB->get_record($tablename, array('course' => 3)); | |
| 1821 | unset($record->id); | |
| 1822 | try { | |
| 1823 | $DB->update_record_raw($tablename, $record); | |
| 1824 | $this->fail("Expecting an exception, none occurred"); | |
| 1825 | } catch (Exception $e) { | |
| 1826 | $this->assertTrue($e instanceof coding_exception); | |
| 1827 | } | |
| 0088bd31 | 1828 | } |
| 1829 | ||
| 1830 | public function test_update_record() { | |
| 628dff25 | 1831 | |
| 1832 | // All the information in this test is fetched from DB by get_record() so we | |
| 1833 | // have such method properly tested against nulls, empties and friends... | |
| 1834 | ||
| 50a12c87 | 1835 | $DB = $this->tdb; |
| 1836 | $dbman = $DB->get_manager(); | |
| 1837 | ||
| 9d833e93 | 1838 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1839 | $tablename = $table->getName(); |
| 1840 | ||
| f9ecb171 | 1841 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 1842 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 628dff25 | 1843 | $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100); |
| 1844 | $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200); | |
| 1845 | $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring'); | |
| 1846 | $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); | |
| 1847 | $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); | |
| 50a12c87 | 1848 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 1849 | $dbman->create_table($table); | |
| 0088bd31 | 1850 | |
| 3ff8bf26 | 1851 | $DB->insert_record($tablename, array('course' => 1)); |
| 1852 | $record = $DB->get_record($tablename, array('course' => 1)); | |
| 50a12c87 | 1853 | $record->course = 2; |
| 628dff25 | 1854 | |
| 3ff8bf26 | 1855 | $this->assertTrue($DB->update_record($tablename, $record)); |
| 1856 | $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1))); | |
| 1857 | $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2))); | |
| 628dff25 | 1858 | $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied |
| 1859 | $this->assertEqual(200, $record->onenum); | |
| 1860 | $this->assertEqual('onestring', $record->onechar); | |
| 1861 | $this->assertNull($record->onetext); | |
| 1862 | $this->assertNull($record->onebinary); | |
| c824e1e1 | 1863 | |
| 628dff25 | 1864 | // Check nulls are set properly for all types |
| 1865 | $record->oneint = null; | |
| 1866 | $record->onenum = null; | |
| 1867 | $record->onechar = null; | |
| 1868 | $record->onetext = null; | |
| 1869 | $record->onebinary = null; | |
| 1870 | $DB->update_record($tablename, $record); | |
| 1871 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1872 | $this->assertNull($record->oneint); | |
| 1873 | $this->assertNull($record->onenum); | |
| 1874 | $this->assertNull($record->onechar); | |
| 1875 | $this->assertNull($record->onetext); | |
| 1876 | $this->assertNull($record->onebinary); | |
| c824e1e1 | 1877 | |
| 628dff25 | 1878 | // Check zeros are set properly for all types |
| 1879 | $record->oneint = 0; | |
| 1880 | $record->onenum = 0; | |
| 1881 | $DB->update_record($tablename, $record); | |
| 1882 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1883 | $this->assertEqual(0, $record->oneint); | |
| 1884 | $this->assertEqual(0, $record->onenum); | |
| c824e1e1 | 1885 | |
| 628dff25 | 1886 | // Check booleans are set properly for all types |
| 1887 | $record->oneint = true; // trues | |
| 1888 | $record->onenum = true; | |
| 1889 | $record->onechar = true; | |
| 1890 | $record->onetext = true; | |
| 1891 | $DB->update_record($tablename, $record); | |
| 1892 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1893 | $this->assertEqual(1, $record->oneint); | |
| 1894 | $this->assertEqual(1, $record->onenum); | |
| 1895 | $this->assertEqual(1, $record->onechar); | |
| 1896 | $this->assertEqual(1, $record->onetext); | |
| 1897 | ||
| 1898 | $record->oneint = false; // falses | |
| 1899 | $record->onenum = false; | |
| 1900 | $record->onechar = false; | |
| 1901 | $record->onetext = false; | |
| 1902 | $DB->update_record($tablename, $record); | |
| 1903 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1904 | $this->assertEqual(0, $record->oneint); | |
| 1905 | $this->assertEqual(0, $record->onenum); | |
| 1906 | $this->assertEqual(0, $record->onechar); | |
| 1907 | $this->assertEqual(0, $record->onetext); | |
| c824e1e1 | 1908 | |
| 628dff25 | 1909 | // Check string data causes exception in numeric types |
| 1910 | $record->oneint = 'onestring'; | |
| 1911 | $record->onenum = 0; | |
| 1912 | try { | |
| 1913 | $DB->update_record($tablename, $record); | |
| 1914 | $this->fail("Expecting an exception, none occurred"); | |
| 1915 | } catch (exception $e) { | |
| 1916 | $this->assertTrue($e instanceof dml_exception); | |
| 1917 | } | |
| 1918 | $record->oneint = 0; | |
| 1919 | $record->onenum = 'onestring'; | |
| 1920 | try { | |
| 1921 | $DB->update_record($tablename, $record); | |
| 1922 | $this->fail("Expecting an exception, none occurred"); | |
| 1923 | } catch (exception $e) { | |
| 1924 | $this->assertTrue($e instanceof dml_exception); | |
| 1925 | } | |
| c824e1e1 | 1926 | |
| 2f9c1693 | 1927 | // Check empty string data is stored as 0 in numeric datatypes |
| fdc45ac3 EL |
1928 | $record->oneint = ''; // empty string |
| 1929 | $record->onenum = 0; | |
| 2f9c1693 EL |
1930 | $DB->update_record($tablename, $record); |
| 1931 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1932 | $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0); | |
| 1933 | ||
| fdc45ac3 EL |
1934 | $record->oneint = 0; |
| 1935 | $record->onenum = ''; // empty string | |
| 2f9c1693 EL |
1936 | $DB->update_record($tablename, $record); |
| 1937 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1938 | $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0); | |
| fdc45ac3 | 1939 | |
| 628dff25 | 1940 | // Check empty strings are set properly in string types |
| 1941 | $record->oneint = 0; | |
| 1942 | $record->onenum = 0; | |
| 1943 | $record->onechar = ''; | |
| 1944 | $record->onetext = ''; | |
| 1945 | $DB->update_record($tablename, $record); | |
| 1946 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1947 | $this->assertTrue($record->onechar === ''); | |
| 1948 | $this->assertTrue($record->onetext === ''); | |
| c824e1e1 | 1949 | |
| 628dff25 | 1950 | // Check operation ((210.10 + 39.92) - 150.02) against numeric types |
| 1951 | $record->oneint = ((210.10 + 39.92) - 150.02); | |
| 1952 | $record->onenum = ((210.10 + 39.92) - 150.02); | |
| 1953 | $DB->update_record($tablename, $record); | |
| 1954 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1955 | $this->assertEqual(100, $record->oneint); | |
| 1956 | $this->assertEqual(100, $record->onenum); | |
| 3ff8bf26 | 1957 | |
| 628dff25 | 1958 | // Check various quotes/backslashes combinations in string types |
| 1959 | $teststrings = array( | |
| 1960 | 'backslashes and quotes alone (even): "" \'\' \\\\', | |
| 1961 | 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', | |
| 1962 | 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', | |
| 1963 | 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); | |
| 1964 | foreach ($teststrings as $teststring) { | |
| 1965 | $record->onechar = $teststring; | |
| 1966 | $record->onetext = $teststring; | |
| 1967 | $DB->update_record($tablename, $record); | |
| 1968 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| 1969 | $this->assertEqual($teststring, $record->onechar); | |
| 1970 | $this->assertEqual($teststring, $record->onetext); | |
| 1971 | } | |
| c824e1e1 | 1972 | |
| 628dff25 | 1973 | // Check LOBs in text/binary columns |
| ebdf7f7e PS |
1974 | $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt'); |
| 1975 | $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary'); | |
| 628dff25 | 1976 | $record->onetext = $clob; |
| 1977 | $record->onebinary = $blob; | |
| 1978 | $DB->update_record($tablename, $record); | |
| 1979 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| ff8c7b2a EL |
1980 | $this->assertEqual($clob, $record->onetext, 'Test CLOB update (full contents output disabled)'); |
| 1981 | $this->assertEqual($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)'); | |
| 628dff25 | 1982 | |
| 1983 | // And "small" LOBs too, just in case | |
| d246cdd2 | 1984 | $newclob = substr($clob, 0, 500); |
| 1985 | $newblob = substr($blob, 0, 250); | |
| 628dff25 | 1986 | $record->onetext = $newclob; |
| 1987 | $record->onebinary = $newblob; | |
| 1988 | $DB->update_record($tablename, $record); | |
| 1989 | $record = $DB->get_record($tablename, array('course' => 2)); | |
| ff8c7b2a EL |
1990 | $this->assertEqual($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)'); |
| 1991 | $this->assertEqual($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)'); | |
| c824e1e1 | 1992 | } |
| 1993 | ||
| 0088bd31 | 1994 | public function test_set_field() { |
| 50a12c87 | 1995 | $DB = $this->tdb; |
| 1996 | $dbman = $DB->get_manager(); | |
| 1997 | ||
| 9d833e93 | 1998 | $table = $this->get_test_table(); |
| 3ff8bf26 | 1999 | $tablename = $table->getName(); |
| 2000 | ||
| f9ecb171 | 2001 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2002 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2003 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2004 | $dbman->create_table($table); | |
| 0088bd31 | 2005 | |
| f1276879 PS |
2006 | // simple set_field |
| 2007 | $id1 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2008 | $id2 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2009 | $id3 = $DB->insert_record($tablename, array('course' => 3)); | |
| 2010 | $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1))); | |
| 2011 | $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => $id1))); | |
| 2012 | $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2))); | |
| 2013 | $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3))); | |
| 2014 | $DB->delete_records($tablename, array()); | |
| 2015 | ||
| 2016 | // multiple fields affected | |
| 2017 | $id1 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2018 | $id2 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2019 | $id3 = $DB->insert_record($tablename, array('course' => 3)); | |
| 2020 | $DB->set_field($tablename, 'course', '5', array('course' => 1)); | |
| 2021 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1))); | |
| 2022 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2))); | |
| 2023 | $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3))); | |
| 2024 | $DB->delete_records($tablename, array()); | |
| 2025 | ||
| 2026 | // no field affected | |
| 2027 | $id1 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2028 | $id2 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2029 | $id3 = $DB->insert_record($tablename, array('course' => 3)); | |
| 2030 | $DB->set_field($tablename, 'course', '5', array('course' => 0)); | |
| 2031 | $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id1))); | |
| 2032 | $this->assertEqual(1, $DB->get_field($tablename, 'course', array('id' => $id2))); | |
| 2033 | $this->assertEqual(3, $DB->get_field($tablename, 'course', array('id' => $id3))); | |
| 2034 | $DB->delete_records($tablename, array()); | |
| 2035 | ||
| 2036 | // all fields - no condition | |
| 2037 | $id1 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2038 | $id2 = $DB->insert_record($tablename, array('course' => 1)); | |
| 2039 | $id3 = $DB->insert_record($tablename, array('course' => 3)); | |
| 2040 | $DB->set_field($tablename, 'course', 5, array()); | |
| 2041 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id1))); | |
| 2042 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id2))); | |
| 2043 | $this->assertEqual(5, $DB->get_field($tablename, 'course', array('id' => $id3))); | |
| bc4fd49d | 2044 | |
| fbabe35a | 2045 | // Note: All the nulls, booleans, empties, quoted and backslashes tests |
| 2046 | // go to set_field_select() because set_field() is just one wrapper over it | |
| 0088bd31 | 2047 | } |
| 2048 | ||
| 2049 | public function test_set_field_select() { | |
| 628dff25 | 2050 | |
| 2051 | // All the information in this test is fetched from DB by get_field() so we | |
| 2052 | // have such method properly tested against nulls, empties and friends... | |
| 2053 | ||
| 50a12c87 | 2054 | $DB = $this->tdb; |
| 2055 | $dbman = $DB->get_manager(); | |
| 2056 | ||
| 9d833e93 | 2057 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2058 | $tablename = $table->getName(); |
| 2059 | ||
| f9ecb171 | 2060 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2061 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| fbabe35a | 2062 | $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null); |
| 2063 | $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null); | |
| 2064 | $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null); | |
| 2065 | $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null); | |
| 2066 | $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null); | |
| 50a12c87 | 2067 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2068 | $dbman->create_table($table); | |
| 50a12c87 | 2069 | |
| 3ff8bf26 | 2070 | $DB->insert_record($tablename, array('course' => 1)); |
| 50a12c87 | 2071 | |
| 3ff8bf26 | 2072 | $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1))); |
| 2073 | $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1))); | |
| 0088bd31 | 2074 | |
| fbabe35a | 2075 | // Check nulls are set properly for all types |
| 628dff25 | 2076 | $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues |
| 2077 | $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1)); | |
| 2078 | $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1)); | |
| 2079 | $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1)); | |
| 2080 | $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1)); | |
| fbabe35a | 2081 | $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1))); |
| 2082 | $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1))); | |
| 2083 | $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1))); | |
| 2084 | $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1))); | |
| 2085 | $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1))); | |
| 2086 | ||
| 2087 | // Check zeros are set properly for all types | |
| 2088 | $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1)); | |
| 2089 | $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1)); | |
| 2090 | $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1))); | |
| 2091 | $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1))); | |
| 2092 | ||
| 2093 | // Check booleans are set properly for all types | |
| 2094 | $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues | |
| 2095 | $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1)); | |
| 2096 | $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1)); | |
| 2097 | $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1)); | |
| 2098 | $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1))); | |
| 2099 | $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1))); | |
| 2100 | $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1))); | |
| 2101 | $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1))); | |
| 2102 | ||
| 2103 | $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses | |
| 2104 | $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1)); | |
| 2105 | $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1)); | |
| 2106 | $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1)); | |
| 2107 | $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1))); | |
| 2108 | $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1))); | |
| 2109 | $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1))); | |
| 2110 | $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1))); | |
| 2111 | ||
| 2112 | // Check string data causes exception in numeric types | |
| 2113 | try { | |
| 2114 | $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1)); | |
| 2115 | $this->fail("Expecting an exception, none occurred"); | |
| 2116 | } catch (exception $e) { | |
| 2117 | $this->assertTrue($e instanceof dml_exception); | |
| 2118 | } | |
| 2119 | try { | |
| 2120 | $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1)); | |
| 2121 | $this->fail("Expecting an exception, none occurred"); | |
| 2122 | } catch (exception $e) { | |
| 2123 | $this->assertTrue($e instanceof dml_exception); | |
| 2124 | } | |
| 2125 | ||
| 2f9c1693 EL |
2126 | // Check empty string data is stored as 0 in numeric datatypes |
| 2127 | $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1)); | |
| 2128 | $field = $DB->get_field($tablename, 'oneint', array('id' => 1)); | |
| 2129 | $this->assertTrue(is_numeric($field) && $field == 0); | |
| 2130 | ||
| 2131 | $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1)); | |
| 2132 | $field = $DB->get_field($tablename, 'onenum', array('id' => 1)); | |
| 2133 | $this->assertTrue(is_numeric($field) && $field == 0); | |
| fdc45ac3 | 2134 | |
| fbabe35a | 2135 | // Check empty strings are set properly in string types |
| 2136 | $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1)); | |
| 2137 | $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1)); | |
| 2138 | $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === ''); | |
| 2139 | $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === ''); | |
| 2140 | ||
| 2141 | // Check operation ((210.10 + 39.92) - 150.02) against numeric types | |
| 2142 | $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1)); | |
| 2143 | $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1)); | |
| 2144 | $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1))); | |
| 2145 | $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1))); | |
| 2146 | ||
| 2147 | // Check various quotes/backslashes combinations in string types | |
| 2148 | $teststrings = array( | |
| 628dff25 | 2149 | 'backslashes and quotes alone (even): "" \'\' \\\\', |
| 2150 | 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\', | |
| 2151 | 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'', | |
| 2152 | 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\''); | |
| fbabe35a | 2153 | foreach ($teststrings as $teststring) { |
| 2154 | $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1)); | |
| 2155 | $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1)); | |
| 2156 | $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1))); | |
| 2157 | $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1))); | |
| 2158 | } | |
| 2159 | ||
| 2160 | // Check LOBs in text/binary columns | |
| ebdf7f7e PS |
2161 | $clob = file_get_contents(dirname(__FILE__).'/fixtures/clob.txt'); |
| 2162 | $blob = file_get_contents(dirname(__FILE__).'/fixtures/randombinary'); | |
| fbabe35a | 2163 | $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1)); |
| 2164 | $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1)); | |
| ff8c7b2a EL |
2165 | $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)'); |
| 2166 | $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)'); | |
| fbabe35a | 2167 | |
| 2168 | // And "small" LOBs too, just in case | |
| 2169 | $newclob = substr($clob, 0, 500); | |
| 2170 | $newblob = substr($blob, 0, 250); | |
| 2171 | $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1)); | |
| 2172 | $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1)); | |
| ff8c7b2a EL |
2173 | $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)'); |
| 2174 | $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)'); | |
| 0088bd31 | 2175 | } |
| 2176 | ||
| 2177 | public function test_count_records() { | |
| 2178 | $DB = $this->tdb; | |
| 2179 | ||
| 2180 | $dbman = $DB->get_manager(); | |
| 2181 | ||
| 9d833e93 | 2182 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2183 | $tablename = $table->getName(); |
| 2184 | ||
| f9ecb171 | 2185 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2186 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 0088bd31 | 2187 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2188 | $dbman->create_table($table); | |
| 0088bd31 | 2189 | |
| 3ff8bf26 | 2190 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 0088bd31 | 2191 | |
| 3ff8bf26 | 2192 | $DB->insert_record($tablename, array('course' => 3)); |
| 2193 | $DB->insert_record($tablename, array('course' => 4)); | |
| 2194 | $DB->insert_record($tablename, array('course' => 5)); | |
| 0088bd31 | 2195 | |
| 3ff8bf26 | 2196 | $this->assertEqual(3, $DB->count_records($tablename)); |
| 0088bd31 | 2197 | } |
| 2198 | ||
| 2199 | public function test_count_records_select() { | |
| 50a12c87 | 2200 | $DB = $this->tdb; |
| 0088bd31 | 2201 | |
| 50a12c87 | 2202 | $dbman = $DB->get_manager(); |
| 2203 | ||
| 9d833e93 | 2204 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2205 | $tablename = $table->getName(); |
| 2206 | ||
| f9ecb171 | 2207 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2208 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2209 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2210 | $dbman->create_table($table); | |
| 50a12c87 | 2211 | |
| 3ff8bf26 | 2212 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 50a12c87 | 2213 | |
| 3ff8bf26 | 2214 | $DB->insert_record($tablename, array('course' => 3)); |
| 2215 | $DB->insert_record($tablename, array('course' => 4)); | |
| 2216 | $DB->insert_record($tablename, array('course' => 5)); | |
| 50a12c87 | 2217 | |
| 3ff8bf26 | 2218 | $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3))); |
| 0088bd31 | 2219 | } |
| 2220 | ||
| 2221 | public function test_count_records_sql() { | |
| 50a12c87 | 2222 | $DB = $this->tdb; |
| 2223 | $dbman = $DB->get_manager(); | |
| 2224 | ||
| 9d833e93 | 2225 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2226 | $tablename = $table->getName(); |
| 2227 | ||
| f9ecb171 | 2228 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2229 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2230 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2231 | $dbman->create_table($table); | |
| 50a12c87 | 2232 | |
| 3ff8bf26 | 2233 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 50a12c87 | 2234 | |
| 3ff8bf26 | 2235 | $DB->insert_record($tablename, array('course' => 3)); |
| 2236 | $DB->insert_record($tablename, array('course' => 4)); | |
| 2237 | $DB->insert_record($tablename, array('course' => 5)); | |
| 0088bd31 | 2238 | |
| 40d3df5c | 2239 | $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3))); |
| 0088bd31 | 2240 | } |
| 2241 | ||
| 2242 | public function test_record_exists() { | |
| 50a12c87 | 2243 | $DB = $this->tdb; |
| 2244 | $dbman = $DB->get_manager(); | |
| 2245 | ||
| 9d833e93 | 2246 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2247 | $tablename = $table->getName(); |
| 2248 | ||
| f9ecb171 | 2249 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2250 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2251 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2252 | $dbman->create_table($table); | |
| 50a12c87 | 2253 | |
| 3ff8bf26 | 2254 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 50a12c87 | 2255 | |
| 3ff8bf26 | 2256 | $this->assertFalse($DB->record_exists($tablename, array('course' => 3))); |
| 2257 | $DB->insert_record($tablename, array('course' => 3)); | |
| 50a12c87 | 2258 | |
| 3ff8bf26 | 2259 | $this->assertTrue($DB->record_exists($tablename, array('course' => 3))); |
| 0088bd31 | 2260 | |
| 2261 | } | |
| 2262 | ||
| 2263 | public function test_record_exists_select() { | |
| 50a12c87 | 2264 | $DB = $this->tdb; |
| 2265 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 2266 | |
| 9d833e93 | 2267 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2268 | $tablename = $table->getName(); |
| 2269 | ||
| f9ecb171 | 2270 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2271 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2272 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2273 | $dbman->create_table($table); | |
| 50a12c87 | 2274 | |
| 3ff8bf26 | 2275 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 50a12c87 | 2276 | |
| 3ff8bf26 | 2277 | $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3))); |
| 2278 | $DB->insert_record($tablename, array('course' => 3)); | |
| 50a12c87 | 2279 | |
| 3ff8bf26 | 2280 | $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3))); |
| 0088bd31 | 2281 | } |
| 2282 | ||
| 2283 | public function test_record_exists_sql() { | |
| 50a12c87 | 2284 | $DB = $this->tdb; |
| 2285 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 2286 | |
| 9d833e93 | 2287 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2288 | $tablename = $table->getName(); |
| 2289 | ||
| f9ecb171 | 2290 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2291 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2292 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2293 | $dbman->create_table($table); | |
| 50a12c87 | 2294 | |
| 3ff8bf26 | 2295 | $this->assertEqual(0, $DB->count_records($tablename)); |
| 50a12c87 | 2296 | |
| 40d3df5c | 2297 | $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3))); |
| 3ff8bf26 | 2298 | $DB->insert_record($tablename, array('course' => 3)); |
| 50a12c87 | 2299 | |
| 40d3df5c | 2300 | $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3))); |
| 0088bd31 | 2301 | } |
| 2302 | ||
| 2303 | public function test_delete_records() { | |
| 50a12c87 | 2304 | $DB = $this->tdb; |
| 2305 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 2306 | |
| 9d833e93 | 2307 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2308 | $tablename = $table->getName(); |
| 2309 | ||
| f9ecb171 | 2310 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2311 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2312 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2313 | $dbman->create_table($table); | |
| 50a12c87 | 2314 | |
| 3ff8bf26 | 2315 | $DB->insert_record($tablename, array('course' => 3)); |
| 2316 | $DB->insert_record($tablename, array('course' => 2)); | |
| 2317 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 2318 | |
| 2319 | // Delete all records | |
| 3ff8bf26 | 2320 | $this->assertTrue($DB->delete_records($tablename)); |
| 2321 | $this->assertEqual(0, $DB->count_records($tablename)); | |
| 50a12c87 | 2322 | |
| 2323 | // Delete subset of records | |
| 3ff8bf26 | 2324 | $DB->insert_record($tablename, array('course' => 3)); |
| 2325 | $DB->insert_record($tablename, array('course' => 2)); | |
| 2326 | $DB->insert_record($tablename, array('course' => 2)); | |
| 50a12c87 | 2327 | |
| 3ff8bf26 | 2328 | $this->assertTrue($DB->delete_records($tablename, array('course' => 2))); |
| 2329 | $this->assertEqual(1, $DB->count_records($tablename)); | |
| a189ac81 PS |
2330 | |
| 2331 | // delete all | |
| 2332 | $this->assertTrue($DB->delete_records($tablename, array())); | |
| 2333 | $this->assertEqual(0, $DB->count_records($tablename)); | |
| 0088bd31 | 2334 | } |
| 2335 | ||
| 2336 | public function test_delete_records_select() { | |
| 50a12c87 | 2337 | $DB = $this->tdb; |
| 2338 | $dbman = $DB->get_manager(); | |
| 0088bd31 | 2339 | |
| 9d833e93 | 2340 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2341 | $tablename = $table->getName(); |
| 2342 | ||
| f9ecb171 | 2343 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2344 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 50a12c87 | 2345 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2346 | $dbman->create_table($table); | |
| 0088bd31 | 2347 | |
| 3ff8bf26 | 2348 | $DB->insert_record($tablename, array('course' => 3)); |
| 2349 | $DB->insert_record($tablename, array('course' => 2)); | |
| 2350 | $DB->insert_record($tablename, array('course' => 2)); | |
| 0088bd31 | 2351 | |
| 3ff8bf26 | 2352 | $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2))); |
| 2353 | $this->assertEqual(1, $DB->count_records($tablename)); | |
| 0088bd31 | 2354 | } |
| 1d861fce | 2355 | |
| c362878e | 2356 | public function test_delete_records_list() { |
| 2357 | $DB = $this->tdb; | |
| 2358 | $dbman = $DB->get_manager(); | |
| 2359 | ||
| 2360 | $table = $this->get_test_table(); | |
| 2361 | $tablename = $table->getName(); | |
| 2362 | ||
| f9ecb171 | 2363 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2364 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| c362878e | 2365 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2366 | $dbman->create_table($table); | |
| c362878e | 2367 | |
| 2368 | $DB->insert_record($tablename, array('course' => 1)); | |
| 2369 | $DB->insert_record($tablename, array('course' => 2)); | |
| 2370 | $DB->insert_record($tablename, array('course' => 3)); | |
| 2371 | ||
| 2372 | $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3))); | |
| 2373 | $this->assertEqual(1, $DB->count_records($tablename)); | |
| 2374 | ||
| 2375 | $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645 | |
| 2376 | $this->assertEqual(1, $DB->count_records($tablename)); | |
| 2377 | } | |
| 2378 | ||
| 082ae821 | 2379 | function test_sql_null_from_clause() { |
| 655bbf51 | 2380 | $DB = $this->tdb; |
| 082ae821 | 2381 | $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause(); |
| 2382 | $this->assertEqual($DB->get_field_sql($sql), 1); | |
| 2383 | } | |
| 655bbf51 | 2384 | |
| 082ae821 | 2385 | function test_sql_bitand() { |
| 2386 | $DB = $this->tdb; | |
| c72b9900 PS |
2387 | $dbman = $DB->get_manager(); |
| 2388 | ||
| 2389 | $table = $this->get_test_table(); | |
| 2390 | $tablename = $table->getName(); | |
| 2391 | ||
| 2392 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2393 | $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2394 | $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2395 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2396 | $dbman->create_table($table); | |
| 2397 | ||
| e586aa39 | 2398 | $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); |
| c72b9900 | 2399 | |
| 9d833e93 | 2400 | $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause(); |
| 082ae821 | 2401 | $this->assertEqual($DB->get_field_sql($sql), 2); |
| c72b9900 PS |
2402 | |
| 2403 | $sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}"; | |
| 2404 | $result = $DB->get_records_sql($sql); | |
| 2405 | $this->assertEqual(count($result), 1); | |
| 2406 | $this->assertEqual(reset($result)->res, 2); | |
| 2407 | ||
| 2408 | $sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}"; | |
| 2409 | $result = $DB->get_records_sql($sql, array(10)); | |
| 2410 | $this->assertEqual(count($result), 1); | |
| 2411 | $this->assertEqual(reset($result)->res, 2); | |
| 082ae821 | 2412 | } |
| 655bbf51 | 2413 | |
| 082ae821 | 2414 | function test_sql_bitnot() { |
| 2415 | $DB = $this->tdb; | |
| 655bbf51 | 2416 | |
| 082ae821 | 2417 | $not = $DB->sql_bitnot(2); |
| 2418 | $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT! | |
| 655bbf51 | 2419 | |
| 9d833e93 | 2420 | $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause(); |
| 082ae821 | 2421 | $this->assertEqual($DB->get_field_sql($sql), 5); |
| 2422 | } | |
| 655bbf51 | 2423 | |
| 082ae821 | 2424 | function test_sql_bitor() { |
| 2425 | $DB = $this->tdb; | |
| e586aa39 PS |
2426 | $dbman = $DB->get_manager(); |
| 2427 | ||
| 2428 | $table = $this->get_test_table(); | |
| 2429 | $tablename = $table->getName(); | |
| 2430 | ||
| 2431 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2432 | $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2433 | $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2434 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2435 | $dbman->create_table($table); | |
| 2436 | ||
| 2437 | $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); | |
| 2438 | ||
| 9d833e93 | 2439 | $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause(); |
| 082ae821 | 2440 | $this->assertEqual($DB->get_field_sql($sql), 11); |
| e586aa39 PS |
2441 | |
| 2442 | $sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}"; | |
| 2443 | $result = $DB->get_records_sql($sql); | |
| 2444 | $this->assertEqual(count($result), 1); | |
| 2445 | $this->assertEqual(reset($result)->res, 11); | |
| 2446 | ||
| 2447 | $sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}"; | |
| 2448 | $result = $DB->get_records_sql($sql, array(10)); | |
| 2449 | $this->assertEqual(count($result), 1); | |
| 2450 | $this->assertEqual(reset($result)->res, 11); | |
| 082ae821 | 2451 | } |
| 655bbf51 | 2452 | |
| 082ae821 | 2453 | function test_sql_bitxor() { |
| 2454 | $DB = $this->tdb; | |
| e586aa39 PS |
2455 | $dbman = $DB->get_manager(); |
| 2456 | ||
| 2457 | $table = $this->get_test_table(); | |
| 2458 | $tablename = $table->getName(); | |
| 2459 | ||
| 2460 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2461 | $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2462 | $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 2463 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2464 | $dbman->create_table($table); | |
| 2465 | ||
| 2466 | $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10)); | |
| 2467 | ||
| 9d833e93 | 2468 | $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause(); |
| 082ae821 | 2469 | $this->assertEqual($DB->get_field_sql($sql), 9); |
| e586aa39 PS |
2470 | |
| 2471 | $sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}"; | |
| 2472 | $result = $DB->get_records_sql($sql); | |
| 2473 | $this->assertEqual(count($result), 1); | |
| 2474 | $this->assertEqual(reset($result)->res, 9); | |
| 2475 | ||
| 2476 | $sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}"; | |
| 2477 | $result = $DB->get_records_sql($sql, array(10)); | |
| 2478 | $this->assertEqual(count($result), 1); | |
| 2479 | $this->assertEqual(reset($result)->res, 9); | |
| 082ae821 | 2480 | } |
| 2481 | ||
| e6df3734 | 2482 | function test_sql_modulo() { |
| 2483 | $DB = $this->tdb; | |
| 9d833e93 | 2484 | $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause(); |
| e6df3734 | 2485 | $this->assertEqual($DB->get_field_sql($sql), 3); |
| 2486 | } | |
| 2487 | ||
| 082ae821 | 2488 | function test_sql_ceil() { |
| 2489 | $DB = $this->tdb; | |
| 9d833e93 | 2490 | $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause(); |
| 082ae821 | 2491 | $this->assertEqual($DB->get_field_sql($sql), 666); |
| 655bbf51 | 2492 | } |
| 2493 | ||
| 6ff835b7 | 2494 | function test_cast_char2int() { |
| 2495 | $DB = $this->tdb; | |
| 2496 | $dbman = $DB->get_manager(); | |
| 2497 | ||
| 98cf713b | 2498 | $table1 = $this->get_test_table("1"); |
| 3ff8bf26 | 2499 | $tablename1 = $table1->getName(); |
| 2500 | ||
| f9ecb171 | 2501 | $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2502 | $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 6ff835b7 | 2503 | $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2504 | $dbman->create_table($table1); | |
| 6ff835b7 | 2505 | |
| 3ff8bf26 | 2506 | $DB->insert_record($tablename1, array('name'=>'100')); |
| 6ff835b7 | 2507 | |
| 98cf713b | 2508 | $table2 = $this->get_test_table("2"); |
| 3ff8bf26 | 2509 | $tablename2 = $table2->getName(); |
| f9ecb171 | 2510 | $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2511 | $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| 6ff835b7 | 2512 | $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2513 | $dbman->create_table($table2); | |
| 6ff835b7 | 2514 | |
| 9d833e93 | 2515 | $DB->insert_record($tablename2, array('res'=>100)); |
| 6ff835b7 | 2516 | |
| 2517 | try { | |
| 9d833e93 | 2518 | $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res "; |
| 6ff835b7 | 2519 | $records = $DB->get_records_sql($sql); |
| 2520 | $this->assertEqual(count($records), 1); | |
| 2521 | } catch (dml_exception $e) { | |
| 2522 | $this->fail("No exception expected"); | |
| 2523 | } | |
| 2524 | } | |
| 2525 | ||
| 2526 | function test_cast_char2real() { | |
| 2527 | $DB = $this->tdb; | |
| 2528 | $dbman = $DB->get_manager(); | |
| 2529 | ||
| 9d833e93 | 2530 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2531 | $tablename = $table->getName(); |
| 2532 | ||
| f9ecb171 | 2533 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2534 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2535 | $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null); | |
| 6ff835b7 | 2536 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2537 | $dbman->create_table($table); | |
| 6ff835b7 | 2538 | |
| 9d833e93 | 2539 | $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1)); |
| 2540 | $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666)); | |
| 2541 | $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1)); | |
| 6ff835b7 | 2542 | |
| 40d3df5c | 2543 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res"; |
| 6ff835b7 | 2544 | $records = $DB->get_records_sql($sql); |
| 2545 | $this->assertEqual(count($records), 2); | |
| 2546 | } | |
| 2547 | ||
| 082ae821 | 2548 | function sql_compare_text() { |
| 2549 | $DB = $this->tdb; | |
| 2550 | $dbman = $DB->get_manager(); | |
| 2551 | ||
| 9d833e93 | 2552 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2553 | $tablename = $table->getName(); |
| 2554 | ||
| f9ecb171 | 2555 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2556 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2557 | $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); | |
| 082ae821 | 2558 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2559 | $dbman->create_table($table); | |
| 082ae821 | 2560 | |
| 3ff8bf26 | 2561 | $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd')); |
| 2562 | $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef')); | |
| 2563 | $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc')); | |
| 082ae821 | 2564 | |
| 40d3df5c | 2565 | $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description'); |
| 082ae821 | 2566 | $records = $DB->get_records_sql($sql); |
| 2567 | $this->assertEqual(count($records), 1); | |
| 2568 | ||
| 40d3df5c | 2569 | $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description', 4); |
| 082ae821 | 2570 | $records = $DB->get_records_sql($sql); |
| 2571 | $this->assertEqual(count($records), 2); | |
| 2572 | } | |
| 2573 | ||
| d533e6d7 | 2574 | function test_unique_index_collation_trouble() { |
| 5a9c25e8 PS |
2575 | // note: this is a work in progress, we should probably move this to ddl test |
| 2576 | ||
| 2577 | $DB = $this->tdb; | |
| 2578 | $dbman = $DB->get_manager(); | |
| 2579 | ||
| 2580 | $table = $this->get_test_table(); | |
| 2581 | $tablename = $table->getName(); | |
| 2582 | ||
| 2583 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2584 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2585 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2586 | $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name')); | |
| 2587 | $dbman->create_table($table); | |
| 5a9c25e8 | 2588 | |
| d533e6d7 PS |
2589 | $DB->insert_record($tablename, array('name'=>'aaa')); |
| 2590 | ||
| 2591 | try { | |
| 2592 | $DB->insert_record($tablename, array('name'=>'AAA')); | |
| 2593 | } catch (Exception $e) { | |
| 2594 | //TODO: ignore case insensitive uniqueness problems for now | |
| 2595 | //$this->fail("Unique index is case sensitive - this may cause problems in some tables"); | |
| 2596 | } | |
| 2597 | ||
| 5a9c25e8 | 2598 | try { |
| 5a9c25e8 PS |
2599 | $DB->insert_record($tablename, array('name'=>'aäa')); |
| 2600 | $DB->insert_record($tablename, array('name'=>'aáa')); | |
| 2601 | $this->assertTrue(true); | |
| 2602 | } catch (Exception $e) { | |
| d533e6d7 PS |
2603 | $family = $DB->get_dbfamily(); |
| 2604 | if ($family === 'mysql' or $family === 'mssql') { | |
| 2605 | $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation."); | |
| 2606 | } else { | |
| 2607 | // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness. | |
| 2608 | $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages."); | |
| 2609 | } | |
| 5a9c25e8 PS |
2610 | throw($e); |
| 2611 | } | |
| 2612 | } | |
| 6055f89d PS |
2613 | |
| 2614 | function test_sql_binary_equal() { | |
| 2615 | $DB = $this->tdb; | |
| 2616 | $dbman = $DB->get_manager(); | |
| 2617 | ||
| 2618 | $table = $this->get_test_table(); | |
| 2619 | $tablename = $table->getName(); | |
| 2620 | ||
| 2621 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2622 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 6055f89d PS |
2623 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2624 | $dbman->create_table($table); | |
| 6055f89d | 2625 | |
| d533e6d7 PS |
2626 | $DB->insert_record($tablename, array('name'=>'aaa')); |
| 2627 | $DB->insert_record($tablename, array('name'=>'aáa')); | |
| 2628 | $DB->insert_record($tablename, array('name'=>'aäa')); | |
| 2629 | $DB->insert_record($tablename, array('name'=>'bbb')); | |
| 2630 | $DB->insert_record($tablename, array('name'=>'BBB')); | |
| 2631 | ||
| 2632 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa')); | |
| 2633 | $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be accent sensitive'); | |
| 2634 | ||
| 2635 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb')); | |
| 2636 | $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be case sensitive'); | |
| 6055f89d PS |
2637 | } |
| 2638 | ||
| 2639 | function test_sql_like() { | |
| 2640 | $DB = $this->tdb; | |
| 2641 | $dbman = $DB->get_manager(); | |
| 2642 | ||
| 2643 | $table = $this->get_test_table(); | |
| 2644 | $tablename = $table->getName(); | |
| 2645 | ||
| 2646 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2647 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2648 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2649 | $dbman->create_table($table); | |
| 6055f89d PS |
2650 | |
| 2651 | $DB->insert_record($tablename, array('name'=>'SuperDuperRecord')); | |
| 2652 | $DB->insert_record($tablename, array('name'=>'Nodupor')); | |
| 2653 | $DB->insert_record($tablename, array('name'=>'ouch')); | |
| 2654 | $DB->insert_record($tablename, array('name'=>'ouc_')); | |
| 2655 | $DB->insert_record($tablename, array('name'=>'ouc%')); | |
| 2656 | $DB->insert_record($tablename, array('name'=>'aui')); | |
| 2657 | $DB->insert_record($tablename, array('name'=>'aüi')); | |
| 2658 | $DB->insert_record($tablename, array('name'=>'aÜi')); | |
| 2659 | ||
| d533e6d7 | 2660 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false); |
| 6055f89d PS |
2661 | $records = $DB->get_records_sql($sql, array("%dup_r%")); |
| 2662 | $this->assertEqual(count($records), 2); | |
| 2663 | ||
| d533e6d7 | 2664 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); |
| 6055f89d PS |
2665 | $records = $DB->get_records_sql($sql, array("%dup%")); |
| 2666 | $this->assertEqual(count($records), 1); | |
| 2667 | ||
| d533e6d7 | 2668 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults |
| 6055f89d PS |
2669 | $records = $DB->get_records_sql($sql, array("%dup%")); |
| 2670 | $this->assertEqual(count($records), 1); | |
| 2671 | ||
| d533e6d7 | 2672 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); |
| 6055f89d PS |
2673 | $records = $DB->get_records_sql($sql, array("ouc\\_")); |
| 2674 | $this->assertEqual(count($records), 1); | |
| 2675 | ||
| 16114b9d | 2676 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|'); |
| 6055f89d PS |
2677 | $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|'))); |
| 2678 | $this->assertEqual(count($records), 1); | |
| 2679 | ||
| d533e6d7 | 2680 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true); |
| 6055f89d PS |
2681 | $records = $DB->get_records_sql($sql, array('aui')); |
| 2682 | $this->assertEqual(count($records), 1); | |
| 2683 | ||
| 16114b9d PS |
2684 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE |
| 2685 | $records = $DB->get_records_sql($sql, array("%o%")); | |
| 2686 | $this->assertEqual(count($records), 3); | |
| 2687 | ||
| 2688 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE | |
| 2689 | $records = $DB->get_records_sql($sql, array("%D%")); | |
| 2690 | $this->assertEqual(count($records), 6); | |
| 2691 | ||
| d533e6d7 PS |
2692 | // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors |
| 2693 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false); | |
| 6055f89d | 2694 | $records = $DB->get_records_sql($sql, array('aui')); |
| d533e6d7 PS |
2695 | //$this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); |
| 2696 | $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false); | |
| 6055f89d | 2697 | $records = $DB->get_records_sql($sql, array('aui')); |
| d533e6d7 | 2698 | //$this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); |
| 6055f89d PS |
2699 | } |
| 2700 | ||
| 2701 | function test_sql_ilike() { | |
| 2f8eea34 | 2702 | // note: this is deprecated, just make sure it does not throw error |
| 6ff835b7 | 2703 | $DB = $this->tdb; |
| 2704 | $dbman = $DB->get_manager(); | |
| 2705 | ||
| 9d833e93 | 2706 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2707 | $tablename = $table->getName(); |
| 2708 | ||
| f9ecb171 | 2709 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2710 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 6ff835b7 | 2711 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2712 | $dbman->create_table($table); | |
| 6ff835b7 | 2713 | |
| 3ff8bf26 | 2714 | $DB->insert_record($tablename, array('name'=>'SuperDuperRecord')); |
| 2715 | $DB->insert_record($tablename, array('name'=>'NoDupor')); | |
| 2716 | $DB->insert_record($tablename, array('name'=>'ouch')); | |
| 6ff835b7 | 2717 | |
| 2f8eea34 | 2718 | // make sure it prints debug message |
| ebdf7f7e | 2719 | $this->enable_debugging(); |
| 40d3df5c | 2720 | $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_ilike()." ?"; |
| 6ff835b7 | 2721 | $params = array("%dup_r%"); |
| ebdf7f7e | 2722 | $this->assertFalse($this->get_debugging() === ''); |
| 2f8eea34 PS |
2723 | |
| 2724 | // following must not throw exception, we ignore result | |
| 2725 | $DB->get_records_sql($sql, $params); | |
| 6ff835b7 | 2726 | } |
| 2727 | ||
| 71a71d59 | 2728 | function test_sql_concat() { |
| 6ff835b7 | 2729 | $DB = $this->tdb; |
| 71a71d59 | 2730 | $dbman = $DB->get_manager(); |
| 2731 | ||
| 2732 | /// Testing all sort of values | |
| 2733 | $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause(); | |
| 2734 | // string, some unicode chars | |
| 2735 | $params = array('name', 'áéíóú', 'name3'); | |
| 2736 | $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params)); | |
| 2737 | // string, spaces and numbers | |
| 2738 | $params = array('name', ' ', 12345); | |
| 2739 | $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params)); | |
| 2740 | // float, empty and strings | |
| 2741 | $params = array(123.45, '', 'test'); | |
| 2742 | $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params)); | |
| 2743 | // float, null and strings | |
| 2744 | $params = array(123.45, null, 'test'); | |
| 52a01626 | 2745 | $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 |
| 71a71d59 | 2746 | |
| 2747 | /// Testing fieldnames + values | |
| 2748 | $table = $this->get_test_table(); | |
| 2749 | $tablename = $table->getName(); | |
| 2750 | ||
| 2751 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2752 | $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); | |
| 2753 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2754 | $dbman->create_table($table); | |
| 71a71d59 | 2755 | |
| 2756 | $DB->insert_record($tablename, array('description'=>'áéíóú')); | |
| 2757 | $DB->insert_record($tablename, array('description'=>'dxxx')); | |
| 2758 | $DB->insert_record($tablename, array('description'=>'bcde')); | |
| 2759 | ||
| 2760 | $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}'; | |
| 2761 | $records = $DB->get_records_sql($sql, array(123.45, 'test')); | |
| 2762 | $this->assertEqual(count($records), 3); | |
| 2763 | $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test'); | |
| 6ff835b7 | 2764 | } |
| 2765 | ||
| 082ae821 | 2766 | function test_concat_join() { |
| 6ff835b7 | 2767 | $DB = $this->tdb; |
| 082ae821 | 2768 | $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause(); |
| 2769 | $params = array("name", "name2", "name3"); | |
| 2770 | $result = $DB->get_field_sql($sql, $params); | |
| 2771 | $this->assertEqual("name name2 name3", $result); | |
| 2772 | } | |
| 2773 | ||
| 2774 | function test_sql_fullname() { | |
| 2775 | $DB = $this->tdb; | |
| 2776 | $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause(); | |
| 2777 | $params = array('first'=>'Firstname', 'last'=>'Surname'); | |
| 2778 | $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params)); | |
| 2779 | } | |
| 2780 | ||
| 2781 | function sql_sql_order_by_text() { | |
| 2782 | $DB = $this->tdb; | |
| 2783 | $dbman = $DB->get_manager(); | |
| 2784 | ||
| 9d833e93 | 2785 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2786 | $tablename = $table->getName(); |
| 2787 | ||
| f9ecb171 | 2788 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2789 | $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null); | |
| 082ae821 | 2790 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2791 | $dbman->create_table($table); | |
| 082ae821 | 2792 | |
| 3ff8bf26 | 2793 | $DB->insert_record($tablename, array('description'=>'abcd')); |
| 2794 | $DB->insert_record($tablename, array('description'=>'dxxx')); | |
| 2795 | $DB->insert_record($tablename, array('description'=>'bcde')); | |
| 082ae821 | 2796 | |
| 40d3df5c | 2797 | $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_order_by_text('description'); |
| 082ae821 | 2798 | $records = $DB->get_records_sql($sql); |
| 258a66dc | 2799 | $first = array_shift($records); |
| 082ae821 | 2800 | $this->assertEqual(1, $first->id); |
| 258a66dc | 2801 | $second = array_shift($records); |
| 082ae821 | 2802 | $this->assertEqual(3, $second->id); |
| 258a66dc | 2803 | $last = array_shift($records); |
| 082ae821 | 2804 | $this->assertEqual(2, $last->id); |
| 2805 | } | |
| 2806 | ||
| 2807 | function test_sql_substring() { | |
| 2808 | $DB = $this->tdb; | |
| 2809 | $dbman = $DB->get_manager(); | |
| 2810 | ||
| 9d833e93 | 2811 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2812 | $tablename = $table->getName(); |
| 2813 | ||
| f9ecb171 | 2814 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2815 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 082ae821 | 2816 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2817 | $dbman->create_table($table); | |
| 082ae821 | 2818 | |
| 2819 | $string = 'abcdefghij'; | |
| 2820 | ||
| 3ff8bf26 | 2821 | $DB->insert_record($tablename, array('name'=>$string)); |
| 082ae821 | 2822 | |
| 40d3df5c | 2823 | $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {{$tablename}}"; |
| 082ae821 | 2824 | $record = $DB->get_record_sql($sql); |
| 2825 | $this->assertEqual(substr($string, 5-1), $record->name); | |
| 2826 | ||
| 40d3df5c | 2827 | $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {{$tablename}}"; |
| 082ae821 | 2828 | $record = $DB->get_record_sql($sql); |
| 2829 | $this->assertEqual(substr($string, 5-1, 2), $record->name); | |
| 2830 | ||
| 2831 | try { | |
| 2832 | // silence php warning ;-) | |
| 2833 | @$DB->sql_substr("name"); | |
| 2834 | $this->fail("Expecting an exception, none occurred"); | |
| 2835 | } catch (Exception $e) { | |
| 2836 | $this->assertTrue($e instanceof coding_exception); | |
| 2837 | } | |
| 6ff835b7 | 2838 | } |
| 2839 | ||
| 37d975e1 | 2840 | function test_sql_length() { |
| 2841 | $DB = $this->tdb; | |
| 2842 | $this->assertEqual($DB->get_field_sql( | |
| 2843 | "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5); | |
| 2844 | $this->assertEqual($DB->get_field_sql( | |
| 2845 | "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5); | |
| 2846 | } | |
| 2847 | ||
| 1d861fce | 2848 | function test_sql_position() { |
| 2849 | $DB = $this->tdb; | |
| 2850 | $this->assertEqual($DB->get_field_sql( | |
| 6ff835b7 | 2851 | "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2); |
| 1d861fce | 2852 | $this->assertEqual($DB->get_field_sql( |
| 6ff835b7 | 2853 | "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0); |
| 2854 | } | |
| 2855 | ||
| 082ae821 | 2856 | function test_sql_empty() { |
| 2857 | $DB = $this->tdb; | |
| 2858 | $dbman = $DB->get_manager(); | |
| 2859 | ||
| 9d833e93 | 2860 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2861 | $tablename = $table->getName(); |
| 2862 | ||
| f9ecb171 | 2863 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2864 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| cf874b29 | 2865 | $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value'); |
| 2866 | $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); | |
| 082ae821 | 2867 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2868 | $dbman->create_table($table); | |
| 082ae821 | 2869 | |
| cf874b29 | 2870 | $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>'')); |
| 3ff8bf26 | 2871 | $DB->insert_record($tablename, array('name'=>null)); |
| cf874b29 | 2872 | $DB->insert_record($tablename, array('name'=>'lalala')); |
| 3ff8bf26 | 2873 | $DB->insert_record($tablename, array('name'=>0)); |
| 082ae821 | 2874 | |
| 40d3df5c | 2875 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = '".$DB->sql_empty()."'"); |
| 082ae821 | 2876 | $this->assertEqual(count($records), 1); |
| 2877 | $record = reset($records); | |
| 2878 | $this->assertEqual($record->name, ''); | |
| cf874b29 | 2879 | |
| 40d3df5c | 2880 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnull = '".$DB->sql_empty()."'"); |
| cf874b29 | 2881 | $this->assertEqual(count($records), 1); |
| 2882 | $record = reset($records); | |
| 2883 | $this->assertEqual($record->namenotnull, ''); | |
| 2884 | ||
| 40d3df5c | 2885 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'"); |
| cf874b29 | 2886 | $this->assertEqual(count($records), 4); |
| 2887 | $record = reset($records); | |
| 2888 | $this->assertEqual($record->namenotnullnodeflt, ''); | |
| 082ae821 | 2889 | } |
| 2890 | ||
| 2891 | function test_sql_isempty() { | |
| 2892 | $DB = $this->tdb; | |
| 2893 | $dbman = $DB->get_manager(); | |
| 2894 | ||
| 9d833e93 | 2895 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2896 | $tablename = $table->getName(); |
| 2897 | ||
| f9ecb171 | 2898 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2899 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); | |
| 2900 | $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2901 | $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null); | |
| 2902 | $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null); | |
| 082ae821 | 2903 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2904 | $dbman->create_table($table); | |
| 082ae821 | 2905 | |
| 3ff8bf26 | 2906 | $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>'')); |
| 2907 | $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null)); | |
| 2908 | $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala')); | |
| 2909 | $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0)); | |
| 082ae821 | 2910 | |
| 40d3df5c | 2911 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'name', false, false)); |
| 082ae821 | 2912 | $this->assertEqual(count($records), 1); |
| 2913 | $record = reset($records); | |
| 2914 | $this->assertEqual($record->name, ''); | |
| 2915 | ||
| 40d3df5c | 2916 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false)); |
| 082ae821 | 2917 | $this->assertEqual(count($records), 1); |
| 2918 | $record = reset($records); | |
| 2919 | $this->assertEqual($record->namenull, ''); | |
| 2920 | ||
| 40d3df5c | 2921 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'description', false, true)); |
| 082ae821 | 2922 | $this->assertEqual(count($records), 1); |
| 2923 | $record = reset($records); | |
| 2924 | $this->assertEqual($record->description, ''); | |
| 2925 | ||
| 40d3df5c | 2926 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true)); |
| 082ae821 | 2927 | $this->assertEqual(count($records), 1); |
| 2928 | $record = reset($records); | |
| 2929 | $this->assertEqual($record->descriptionnull, ''); | |
| 2930 | } | |
| 2931 | ||
| 63b3d8ab EL |
2932 | function test_sql_isnotempty() { |
| 2933 | $DB = $this->tdb; | |
| 2934 | $dbman = $DB->get_manager(); | |
| 2935 | ||
| 2936 | $table = $this->get_test_table(); | |
| 2937 | $tablename = $table->getName(); | |
| 2938 | ||
| 2939 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); | |
| 2940 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null); | |
| 2941 | $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 2942 | $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null); | |
| 2943 | $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null); | |
| 2944 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); | |
| 2945 | $dbman->create_table($table); | |
| 63b3d8ab EL |
2946 | |
| 2947 | $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>'')); | |
| 2948 | $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null)); | |
| 2949 | $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala')); | |
| 2950 | $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0)); | |
| 2951 | ||
| 40d3df5c | 2952 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false)); |
| 63b3d8ab EL |
2953 | $this->assertEqual(count($records), 3); |
| 2954 | $record = reset($records); | |
| 2955 | $this->assertEqual($record->name, '??'); | |
| 2956 | ||
| 40d3df5c | 2957 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false)); |
| 63b3d8ab EL |
2958 | $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour |
| 2959 | $record = reset($records); | |
| 2960 | $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record | |
| 2961 | ||
| 40d3df5c | 2962 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true)); |
| 63b3d8ab EL |
2963 | $this->assertEqual(count($records), 3); |
| 2964 | $record = reset($records); | |
| 2965 | $this->assertEqual($record->description, '??'); | |
| 2966 | ||
| 40d3df5c | 2967 | $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true)); |
| 63b3d8ab EL |
2968 | $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour |
| 2969 | $record = reset($records); | |
| 2970 | $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record | |
| 082ae821 | 2971 | } |
| 2972 | ||
| 2973 | function test_sql_regex() { | |
| 6ff835b7 | 2974 | $DB = $this->tdb; |
| 2975 | $dbman = $DB->get_manager(); | |
| 2976 | ||
| 9d833e93 | 2977 | $table = $this->get_test_table(); |
| 3ff8bf26 | 2978 | $tablename = $table->getName(); |
| 2979 | ||
| f9ecb171 | 2980 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 2981 | $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); | |
| 6ff835b7 | 2982 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 2983 | $dbman->create_table($table); | |
| 6ff835b7 | 2984 | |
| 3ff8bf26 | 2985 | $DB->insert_record($tablename, array('name'=>'lalala')); |
| 2986 | $DB->insert_record($tablename, array('name'=>'holaaa')); | |
| 2987 | $DB->insert_record($tablename, array('name'=>'aouch')); | |
| 6ff835b7 | 2988 | |
| 40d3df5c | 2989 | $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex()." ?"; |
| 6ff835b7 | 2990 | $params = array('a$'); |
| dd1611a5 | 2991 | if ($DB->sql_regex_supported()) { |
| 2992 | $records = $DB->get_records_sql($sql, $params); | |
| 2993 | $this->assertEqual(count($records), 2); | |
| 2994 | } else { | |
| 2995 | $this->assertTrue(true, 'Regexp operations not supported. Test skipped'); | |
| 2996 | } | |
| 6ff835b7 | 2997 | |
| 40d3df5c | 2998 | $sql = "SELECT * FROM {{$tablename}} WHERE name ".$DB->sql_regex(false)." ?"; |
| 6ff835b7 | 2999 | $params = array('.a'); |
| dd1611a5 | 3000 | if ($DB->sql_regex_supported()) { |
| 3001 | $records = $DB->get_records_sql($sql, $params); | |
| 3002 | $this->assertEqual(count($records), 1); | |
| 3003 | } else { | |
| 3004 | $this->assertTrue(true, 'Regexp operations not supported. Test skipped'); | |
| 3005 | } | |
| 6ff835b7 | 3006 | |
| 1d861fce | 3007 | } |
| fb76304b | 3008 | |
| d5a8d9aa PS |
3009 | /** |
| 3010 | * Test some more complex SQL syntax which moodle uses and depends on to work | |
| 3011 | * useful to determine if new database libraries can be supported. | |
| 3012 | */ | |
| 3013 | public function test_get_records_sql_complicated() { | |
| fb76304b | 3014 | $DB = $this->tdb; |
| 3015 | $dbman = $DB->get_manager(); | |
| 3016 | ||
| 9d833e93 | 3017 | $table = $this->get_test_table(); |
| 3ff8bf26 | 3018 | $tablename = $table->getName(); |
| 3019 | ||
| f9ecb171 | 3020 | $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); |
| 3021 | $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0'); | |
| bc4fd49d | 3022 | $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL); |
| fb76304b | 3023 | $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); |
| 3024 | $dbman->create_table($table); | |
| fb76304b | 3025 | |
| bc4fd49d EL |
3026 | $DB->insert_record($tablename, array('course' => 3, 'content' => 'hello')); |
| 3027 | $DB->insert_record($tablename, array('course' => 3, 'content' => 'world')); | |
| 3028 | $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello')); | |
| 3029 | $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe')); | |
| d5a8d9aa PS |
3030 | |
| 3031 | // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example.. | |
| 40d3df5c PS |
3032 | $sql = "SELECT a.id AS id, a.course AS course |
| 3033 | FROM {{$tablename}} a | |
| 3034 | JOIN (SELECT * FROM {{$tablename}}) b ON a.id = b.id | |
| 3035 | WHERE a.c |
