lib/dml - 'complicated sql' unit test MDL-19645
[moodle.git] / lib / dml / simpletest / testdml.php
CommitLineData
73f7ad71 1<?php
49926145 2
73f7ad71 3/**
3dce78e1 4 * Unit tests for dml
73f7ad71 5 */
6
7if (!defined('MOODLE_INTERNAL')) {
8 die('Direct access to this script is forbidden.'); /// It must be included from a Moodle page
9}
10
c7d306e1 11class dml_test extends UnitTestCase {
73f7ad71 12 private $tables = array();
251387d0 13 private $tdb;
b579f0db 14 private $data;
16a2a412 15 public static $includecoverage = array('lib/dml');
16 public static $excludecoverage = array('lib/dml/simpletest');
73f7ad71 17
18 function setUp() {
a230012c 19 global $CFG, $DB, $UNITTEST;
e6b4f00e 20
a230012c 21 if (isset($UNITTEST->func_test_db)) {
251387d0 22 $this->tdb = $UNITTEST->func_test_db;
e6b4f00e 23 } else {
251387d0 24 $this->tdb = $DB;
e6b4f00e 25 }
73f7ad71 26
73f7ad71 27 }
28
29 function tearDown() {
251387d0 30 $dbman = $this->tdb->get_manager();
809df0e2 31
32 foreach ($this->tables as $table) {
251387d0 33 if ($dbman->table_exists($table)) {
eee5d9bb 34 $dbman->drop_table($table);
73f7ad71 35 }
36 }
e6b4f00e 37 $this->tables = array();
73f7ad71 38 }
39
1d861fce 40 /**
41 * Get a xmldb_table object for testing, deleting any existing table
42 * of the same name, for example if one was left over from a previous test
43 * run that crashed.
44 *
45 * @param database_manager $dbman the database_manager to use.
46 * @param string $tablename the name of the table to create.
47 * @return xmldb_table the table object.
48 */
66e75f8d 49 private function get_test_table($tablename="") {
515cb653 50 $DB = $this->tdb;
9d833e93 51 $dbman = $this->tdb->get_manager();
52
515cb653 53 if ($tablename == '') {
d578c197 54 $tablename = "unit_table";
66e75f8d 55 }
56
1d861fce 57 $table = new xmldb_table($tablename);
58 if ($dbman->table_exists($table)) {
59 $dbman->drop_table($table);
60 }
3ff8bf26 61 return new xmldb_table($tablename);
1d861fce 62 }
63
73f7ad71 64 function test_fix_sql_params() {
7f9f11b5 65 $DB = $this->tdb;
b579f0db 66
9d833e93 67 $table = $this->get_test_table();
68 $tablename = $table->getName();
3ff8bf26 69
334ce026 70 // Correct table placeholder substitution
3ff8bf26 71 $sql = "SELECT * FROM {".$tablename."}";
334ce026 72 $sqlarray = $DB->fix_sql_params($sql);
66e75f8d 73 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
334ce026 74
75 // Conversions of all param types
76 $sql = array();
3ff8bf26 77 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
334ce026 78 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
79 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
80
81 $params = array();
3ff8bf26 82 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
334ce026 83 $params[SQL_PARAMS_QM] = array('first record', 1);
84 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
85
86 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
87 $this->assertEqual($rsql, $sql[$rtype]);
88 $this->assertEqual($rparams, $params[$rtype]);
89
90 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
91 $this->assertEqual($rsql, $sql[$rtype]);
92 $this->assertEqual($rparams, $params[$rtype]);
93
94 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
95 $this->assertEqual($rsql, $sql[$rtype]);
96 $this->assertEqual($rparams, $params[$rtype]);
97
98
73f7ad71 99 // Malformed table placeholder
100 $sql = "SELECT * FROM [testtable]";
e6b4f00e 101 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 102 $this->assertEqual($sql, $sqlarray[0]);
103
73f7ad71 104
73f7ad71 105 // Mixed param types (colon and dollar)
3ff8bf26 106 $sql = "SELECT * FROM {".$tablename."} WHERE name = :param1, course = \$1";
b579f0db 107 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 108 try {
e6b4f00e 109 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 110 $this->fail("Expecting an exception, none occurred");
73f7ad71 111 } catch (Exception $e) {
251387d0 112 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 113 }
114
115 // Mixed param types (question and dollar)
3ff8bf26 116 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = \$1";
b579f0db 117 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 118 try {
e6b4f00e 119 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 120 $this->fail("Expecting an exception, none occurred");
73f7ad71 121 } catch (Exception $e) {
251387d0 122 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 123 }
73f7ad71 124
125 // Too many params in sql
3ff8bf26 126 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = ?, id = ?";
b579f0db 127 $params = array('record2', 3);
73f7ad71 128 try {
e6b4f00e 129 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 130 $this->fail("Expecting an exception, none occurred");
73f7ad71 131 } catch (Exception $e) {
251387d0 132 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 133 }
73f7ad71 134
135 // Too many params in array: no error
136 $params[] = 1;
137 $params[] = time();
73f7ad71 138 $sqlarray = null;
139
140 try {
e6b4f00e 141 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 142 $this->pass();
73f7ad71 143 } catch (Exception $e) {
6ff835b7 144 $this->fail("Unexpected ".get_class($e)." exception");
73f7ad71 145 }
73f7ad71 146 $this->assertTrue($sqlarray[0]);
147
148 // Named params missing from array
3ff8bf26 149 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :course";
b579f0db 150 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 151 try {
e6b4f00e 152 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 153 $this->fail("Expecting an exception, none occurred");
73f7ad71 154 } catch (Exception $e) {
251387d0 155 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 156 }
73f7ad71 157
158 // Duplicate named param in query
3ff8bf26 159 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :name";
b579f0db 160 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 161 try {
e6b4f00e 162 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 163 $this->fail("Expecting an exception, none occurred");
73f7ad71 164 } catch (Exception $e) {
251387d0 165 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 166 }
73f7ad71 167
73f7ad71 168 }
169
bb78c788 170 public function testGetTables() {
7f9f11b5 171 $DB = $this->tdb;
251387d0 172 $dbman = $this->tdb->get_manager();
a230012c 173
1d861fce 174 // Need to test with multiple DBs
9d833e93 175 $table = $this->get_test_table();
3ff8bf26 176 $tablename = $table->getName();
1d861fce 177
b579f0db 178 $original_count = count($DB->get_tables());
179
f9ecb171 180 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
b579f0db 181 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
182 $dbman->create_table($table);
3ff8bf26 183 $this->tables[$tablename] = $table;
767172e4 184
b579f0db 185 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 186 }
187
767172e4 188 public function testDefaults() {
189 $DB = $this->tdb;
190 $dbman = $this->tdb->get_manager();
191
9d833e93 192 $table = $this->get_test_table();
3ff8bf26 193 $tablename = $table->getName();
194
f9ecb171 195 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
196 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
767172e4 197 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
198 $dbman->create_table($table);
3ff8bf26 199 $this->tables[$tablename] = $table;
767172e4 200
3ff8bf26 201 $columns = $DB->get_columns($tablename);
767172e4 202
203 $enumfield = $columns['enumfield'];
204 $this->assertEqual('test2', $enumfield->default_value);
205 $this->assertEqual('C', $enumfield->meta_type);
206
207 }
208
bb78c788 209 public function testGetIndexes() {
7f9f11b5 210 $DB = $this->tdb;
251387d0 211 $dbman = $this->tdb->get_manager();
a230012c 212
9d833e93 213 $table = $this->get_test_table();
3ff8bf26 214 $tablename = $table->getName();
215
f9ecb171 216 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
217 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
80ffbad3 218 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
b579f0db 219 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
767172e4 220 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
b579f0db 221 $dbman->create_table($table);
3ff8bf26 222 $this->tables[$tablename] = $table;
b579f0db 223
3ff8bf26 224 $this->assertTrue($indices = $DB->get_indexes($tablename));
66e75f8d 225 $this->assertEqual(count($indices), 2);
226 // we do not care about index names for now
80ffbad3 227 $first = array_shift($indices);
228 $second = array_shift($indices);
229 if (count($first['columns']) == 2) {
230 $composed = $first;
231 $single = $second;
232 } else {
233 $composed = $second;
234 $single = $first;
235 }
236 $this->assertFalse($single['unique']);
237 $this->assertTrue($composed['unique']);
238 $this->assertEqual(1, count($single['columns']));
239 $this->assertEqual(2, count($composed['columns']));
240 $this->assertEqual('course', $single['columns'][0]);
241 $this->assertEqual('course', $composed['columns'][0]);
242 $this->assertEqual('id', $composed['columns'][1]);
bb78c788 243 }
244
245 public function testGetColumns() {
7f9f11b5 246 $DB = $this->tdb;
251387d0 247 $dbman = $this->tdb->get_manager();
bb78c788 248
9d833e93 249 $table = $this->get_test_table();
3ff8bf26 250 $tablename = $table->getName();
251
f9ecb171 252 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
253 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
254 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 255 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
256 $dbman->create_table($table);
3ff8bf26 257 $this->tables[$tablename] = $table;
b579f0db 258
3ff8bf26 259 $this->assertTrue($columns = $DB->get_columns($tablename));
260 $fields = $this->tables[$tablename]->getFields();
bb78c788 261 $this->assertEqual(count($columns), count($fields));
262
263 for ($i = 0; $i < count($columns); $i++) {
264 if ($i == 0) {
265 $next_column = reset($columns);
266 $next_field = reset($fields);
267 } else {
268 $next_column = next($columns);
269 $next_field = next($fields);
270 }
271
272 $this->assertEqual($next_column->name, $next_field->name);
273 }
274 }
275
276 public function testExecute() {
7f9f11b5 277 $DB = $this->tdb;
251387d0 278 $dbman = $this->tdb->get_manager();
a230012c 279
9d833e93 280 $table = $this->get_test_table();
3ff8bf26 281 $tablename = $table->getName();
282
f9ecb171 283 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
284 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
285 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 286 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
287 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
288 $dbman->create_table($table);
3ff8bf26 289 $this->tables[$tablename] = $table;
b579f0db 290
3ff8bf26 291 $sql = "SELECT * FROM {".$tablename."}";
b579f0db 292
bb78c788 293 $this->assertTrue($DB->execute($sql));
73f7ad71 294
b579f0db 295 $params = array('course' => 1, 'name' => 'test');
6807d2b3 296
3ff8bf26 297 $sql = "INSERT INTO {".$tablename."} (".implode(',', array_keys($params)).")
6807d2b3 298 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
299
300
301 $this->assertTrue($DB->execute($sql, $params));
bb78c788 302
3ff8bf26 303 $record = $DB->get_record($tablename, array('id' => 1));
bb78c788 304
6807d2b3 305 foreach ($params as $field => $value) {
bb78c788 306 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
307 }
308 }
309
7f9f11b5 310 public function test_get_in_or_equal() {
311 $DB = $this->tdb;
312
313 // SQL_PARAMS_QM - IN or =
314
315 // Correct usage of multiple values
316 $in_values = array('value1', 'value2', 'value3', 'value4');
317 list($usql, $params) = $DB->get_in_or_equal($in_values);
318 $this->assertEqual("IN (?,?,?,?)", $usql);
319 $this->assertEqual(4, count($params));
320 foreach ($params as $key => $value) {
321 $this->assertEqual($in_values[$key], $value);
322 }
323
324 // Correct usage of single value (in an array)
325 $in_values = array('value1');
326 list($usql, $params) = $DB->get_in_or_equal($in_values);
327 $this->assertEqual("= ?", $usql);
328 $this->assertEqual(1, count($params));
329 $this->assertEqual($in_values[0], $params[0]);
330
331 // Correct usage of single value
332 $in_value = 'value1';
333 list($usql, $params) = $DB->get_in_or_equal($in_values);
334 $this->assertEqual("= ?", $usql);
335 $this->assertEqual(1, count($params));
336 $this->assertEqual($in_value, $params[0]);
337
338 // SQL_PARAMS_QM - NOT IN or <>
339
340 // Correct usage of multiple values
341 $in_values = array('value1', 'value2', 'value3', 'value4');
342 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
343 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
344 $this->assertEqual(4, count($params));
345 foreach ($params as $key => $value) {
346 $this->assertEqual($in_values[$key], $value);
347 }
348
349 // Correct usage of single value (in array()
350 $in_values = array('value1');
351 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
352 $this->assertEqual("<> ?", $usql);
353 $this->assertEqual(1, count($params));
354 $this->assertEqual($in_values[0], $params[0]);
355
356 // Correct usage of single value
357 $in_value = 'value1';
358 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
359 $this->assertEqual("<> ?", $usql);
360 $this->assertEqual(1, count($params));
361 $this->assertEqual($in_value, $params[0]);
362
363 // SQL_PARAMS_NAMED - IN or =
364
365 // Correct usage of multiple values
366 $in_values = array('value1', 'value2', 'value3', 'value4');
367 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
368 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
369 $this->assertEqual(4, count($params));
370 reset($in_values);
371 foreach ($params as $key => $value) {
372 $this->assertEqual(current($in_values), $value);
373 next($in_values);
374 }
375
376 // Correct usage of single values (in array)
377 $in_values = array('value1');
378 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
379 $this->assertEqual("= :param01", $usql);
380 $this->assertEqual(1, count($params));
381 $this->assertEqual($in_values[0], $params['param01']);
382
383 // Correct usage of single value
384 $in_value = 'value1';
385 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
386 $this->assertEqual("= :param01", $usql);
387 $this->assertEqual(1, count($params));
388 $this->assertEqual($in_value, $params['param01']);
389
390 // SQL_PARAMS_NAMED - NOT IN or <>
391
392 // Correct usage of multiple values
393 $in_values = array('value1', 'value2', 'value3', 'value4');
394 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
395 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
396 $this->assertEqual(4, count($params));
397 reset($in_values);
398 foreach ($params as $key => $value) {
399 $this->assertEqual(current($in_values), $value);
400 next($in_values);
401 }
402
403 // Correct usage of single values (in array)
404 $in_values = array('value1');
405 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
406 $this->assertEqual("<> :param01", $usql);
407 $this->assertEqual(1, count($params));
408 $this->assertEqual($in_values[0], $params['param01']);
409
410 // Correct usage of single value
411 $in_value = 'value1';
412 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
413 $this->assertEqual("<> :param01", $usql);
414 $this->assertEqual(1, count($params));
415 $this->assertEqual($in_value, $params['param01']);
416
417 }
418
419 public function test_fix_table_names() {
420 $DB = new moodle_database_for_testing();
421 $prefix = $DB->get_prefix();
422
423 // Simple placeholder
424 $placeholder = "{user}";
6ff835b7 425 $this->assertEqual($prefix."user", $DB->public_fix_table_names($placeholder));
7f9f11b5 426
427 // Full SQL
428 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
429 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
430 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
431
b579f0db 432
433 }
434
435 public function test_get_recordset() {
436 $DB = $this->tdb;
437 $dbman = $DB->get_manager();
438
9d833e93 439 $table = $this->get_test_table();
3ff8bf26 440 $tablename = $table->getName();
441
f9ecb171 442 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
443 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
444 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 445 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
446 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
447 $dbman->create_table($table);
3ff8bf26 448 $this->tables[$tablename] = $table;
b579f0db 449
450 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
451 array('id' => 2, 'course' => 3, 'name' => 'record2'),
452 array('id' => 3, 'course' => 5, 'name' => 'record3'));
22d77567 453
b579f0db 454 foreach ($data as $record) {
3ff8bf26 455 $DB->insert_record($tablename, $record);
b579f0db 456 }
457
3ff8bf26 458 $rs = $DB->get_recordset($tablename);
b579f0db 459 $this->assertTrue($rs);
460
461 reset($data);
462 foreach($rs as $record) {
463 $data_record = current($data);
464 foreach ($record as $k => $v) {
465 $this->assertEqual($data_record[$k], $v);
466 }
467 next($data);
468 }
469 $rs->close();
5d91786a 470
471 // note: delegate limits testing to test_get_recordset_sql()
7f9f11b5 472 }
0088bd31 473
b0cb2290 474 public function test_get_recordset_iterator_keys() {
475 $DB = $this->tdb;
476 $dbman = $DB->get_manager();
477
9d833e93 478 $table = $this->get_test_table();
3ff8bf26 479 $tablename = $table->getName();
480
f9ecb171 481 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
482 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
483 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b0cb2290 484 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
485 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
486 $dbman->create_table($table);
3ff8bf26 487 $this->tables[$tablename] = $table;
b0cb2290 488
22d77567 489 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
490 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
491 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 492 foreach ($data as $record) {
3ff8bf26 493 $DB->insert_record($tablename, $record);
b0cb2290 494 }
495
496 /// Test repeated numeric keys are returned ok
3ff8bf26 497 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 498
499 reset($data);
500 $count = 0;
501 foreach($rs as $key => $record) {
502 $data_record = current($data);
503 $this->assertEqual($data_record['course'], $key);
504 next($data);
505 $count++;
506 }
507 $rs->close();
508
509 /// Test record returned are ok
510 $this->assertEqual($count, 3);
511
512 /// Test string keys are returned ok
3ff8bf26 513 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 514
515 reset($data);
516 $count = 0;
517 foreach($rs as $key => $record) {
518 $data_record = current($data);
519 $this->assertEqual($data_record['name'], $key);
520 next($data);
521 $count++;
522 }
523 $rs->close();
524
525 /// Test record returned are ok
526 $this->assertEqual($count, 3);
527
528 /// Test numeric not starting in 1 keys are returned ok
3ff8bf26 529 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 530
531 $data = array_reverse($data);
532 reset($data);
533 $count = 0;
534 foreach($rs as $key => $record) {
535 $data_record = current($data);
536 $this->assertEqual($data_record['id'], $key);
537 next($data);
538 $count++;
539 }
540 $rs->close();
541
542 /// Test record returned are ok
543 $this->assertEqual($count, 3);
544 }
545
0088bd31 546 public function test_get_recordset_list() {
547 $DB = $this->tdb;
548 $dbman = $DB->get_manager();
549
9d833e93 550 $table = $this->get_test_table();
3ff8bf26 551 $tablename = $table->getName();
552
f9ecb171 553 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
554 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 555 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
556 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
557 $dbman->create_table($table);
3ff8bf26 558 $this->tables[$tablename] = $table;
0088bd31 559
3ff8bf26 560 $DB->insert_record($tablename, array('course' => 3));
561 $DB->insert_record($tablename, array('course' => 3));
562 $DB->insert_record($tablename, array('course' => 5));
563 $DB->insert_record($tablename, array('course' => 2));
0088bd31 564
3ff8bf26 565 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 566
567 $this->assertTrue($rs);
568
569 $counter = 0;
570 foreach ($rs as $record) {
571 $counter++;
572 }
573 $this->assertEqual(3, $counter);
c362878e 574 $rs->close();
575
576 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
577
578 $counter = 0;
579 foreach ($rs as $record) {
580 $counter++;
581 }
c362878e 582 $rs->close();
5d91786a 583 $this->assertEqual(0, $counter);
584
585 // note: delegate limits testing to test_get_recordset_sql()
0088bd31 586 }
587
588 public function test_get_recordset_select() {
589 $DB = $this->tdb;
590 $dbman = $DB->get_manager();
591
9d833e93 592 $table = $this->get_test_table();
3ff8bf26 593 $tablename = $table->getName();
594
f9ecb171 595 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
596 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 597 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
598 $dbman->create_table($table);
3ff8bf26 599 $this->tables[$tablename] = $table;
0088bd31 600
3ff8bf26 601 $DB->insert_record($tablename, array('course' => 3));
602 $DB->insert_record($tablename, array('course' => 3));
603 $DB->insert_record($tablename, array('course' => 5));
604 $DB->insert_record($tablename, array('course' => 2));
0088bd31 605
3ff8bf26 606 $rs = $DB->get_recordset_select($tablename, '');
0088bd31 607 $counter = 0;
608 foreach ($rs as $record) {
609 $counter++;
610 }
515cb653 611 $rs->close();
0088bd31 612 $this->assertEqual(4, $counter);
613
3ff8bf26 614 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 615 $counter = 0;
616 foreach ($rs as $record) {
617 $counter++;
618 }
515cb653 619 $rs->close();
0088bd31 620 $this->assertEqual(2, $counter);
5d91786a 621
622 // note: delegate limits testing to test_get_recordset_sql()
0088bd31 623 }
624
625 public function test_get_recordset_sql() {
626 $DB = $this->tdb;
627 $dbman = $DB->get_manager();
628
9d833e93 629 $table = $this->get_test_table();
3ff8bf26 630 $tablename = $table->getName();
631
f9ecb171 632 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
633 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 634 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
635 $dbman->create_table($table);
3ff8bf26 636 $this->tables[$tablename] = $table;
0088bd31 637
5d91786a 638 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
639 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
640 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
641 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
642 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
643 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
644 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
0088bd31 645
3ff8bf26 646 $this->assertTrue($rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 647 $counter = 0;
648 foreach ($rs as $record) {
649 $counter++;
650 }
515cb653 651 $rs->close();
0088bd31 652 $this->assertEqual(2, $counter);
5d91786a 653
654 // limits - only need to test this case, the rest have been tested by test_get_records_sql()
655 // only limitfrom = skips that number of records
656 $rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 2, 0);
657 $records = array();
658 foreach($rs as $key => $record) {
659 $records[$key] = $record;
660 }
661 $rs->close();
662 $this->assertEqual(5, count($records));
663 $this->assertEqual($inskey3, reset($records)->id);
664 $this->assertEqual($inskey7, end($records)->id);
665
666 // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here
0088bd31 667 }
668
669 public function test_get_records() {
670 $DB = $this->tdb;
671 $dbman = $DB->get_manager();
672
9d833e93 673 $table = $this->get_test_table();
3ff8bf26 674 $tablename = $table->getName();
675
f9ecb171 676 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
677 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 678 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
679 $dbman->create_table($table);
3ff8bf26 680 $this->tables[$tablename] = $table;
0088bd31 681
3ff8bf26 682 $DB->insert_record($tablename, array('course' => 3));
683 $DB->insert_record($tablename, array('course' => 3));
684 $DB->insert_record($tablename, array('course' => 5));
685 $DB->insert_record($tablename, array('course' => 2));
0088bd31 686
687 // All records
3ff8bf26 688 $records = $DB->get_records($tablename);
0088bd31 689 $this->assertEqual(4, count($records));
690 $this->assertEqual(3, $records[1]->course);
691 $this->assertEqual(3, $records[2]->course);
692 $this->assertEqual(5, $records[3]->course);
693 $this->assertEqual(2, $records[4]->course);
694
695 // Records matching certain conditions
3ff8bf26 696 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 697 $this->assertEqual(2, count($records));
698 $this->assertEqual(3, $records[1]->course);
699 $this->assertEqual(3, $records[2]->course);
700
701 // All records sorted by course
3ff8bf26 702 $records = $DB->get_records($tablename, null, 'course');
0088bd31 703 $this->assertEqual(4, count($records));
704 $current_record = reset($records);
705 $this->assertEqual(4, $current_record->id);
706 $current_record = next($records);
707 $this->assertEqual(1, $current_record->id);
708 $current_record = next($records);
709 $this->assertEqual(2, $current_record->id);
710 $current_record = next($records);
711 $this->assertEqual(3, $current_record->id);
712
713 // All records, but get only one field
3ff8bf26 714 $records = $DB->get_records($tablename, null, '', 'id');
0088bd31 715 $this->assertTrue(empty($records[1]->course));
716 $this->assertFalse(empty($records[1]->id));
717 $this->assertEqual(4, count($records));
3ff8bf26 718
5d91786a 719 // note: delegate limits testing to test_get_records_sql()
0088bd31 720 }
721
722 public function test_get_records_list() {
50a12c87 723 $DB = $this->tdb;
724 $dbman = $DB->get_manager();
725
9d833e93 726 $table = $this->get_test_table();
3ff8bf26 727 $tablename = $table->getName();
728
f9ecb171 729 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
730 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 731 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
732 $dbman->create_table($table);
3ff8bf26 733 $this->tables[$tablename] = $table;
50a12c87 734
3ff8bf26 735 $DB->insert_record($tablename, array('course' => 3));
736 $DB->insert_record($tablename, array('course' => 3));
737 $DB->insert_record($tablename, array('course' => 5));
738 $DB->insert_record($tablename, array('course' => 2));
50a12c87 739
3ff8bf26 740 $this->assertTrue($records = $DB->get_records_list($tablename, 'course', array(3, 2)));
50a12c87 741 $this->assertEqual(3, count($records));
742 $this->assertEqual(1, reset($records)->id);
743 $this->assertEqual(2, next($records)->id);
744 $this->assertEqual(4, next($records)->id);
0088bd31 745
c362878e 746 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
747 $this->assertEqual(0, count($records));
748
5d91786a 749 // note: delegate limits testing to test_get_records_sql()
0088bd31 750 }
751
5d91786a 752 public function test_get_record_select() {
50a12c87 753 $DB = $this->tdb;
754 $dbman = $DB->get_manager();
755
9d833e93 756 $table = $this->get_test_table();
3ff8bf26 757 $tablename = $table->getName();
758
f9ecb171 759 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
760 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 761 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
762 $dbman->create_table($table);
3ff8bf26 763 $this->tables[$tablename] = $table;
50a12c87 764
3ff8bf26 765 $DB->insert_record($tablename, array('course' => 3));
3ff8bf26 766 $DB->insert_record($tablename, array('course' => 2));
50a12c87 767
5d91786a 768 $this->assertTrue($record = $DB->get_record_select($tablename, "id = ?", array(2)));
769
770 $this->assertEqual(2, $record->course);
771
772 // note: delegates limit testing to test_get_records_sql()
773 }
774
775 public function test_get_records_sql() {
776 global $CFG;
777 $DB = $this->tdb;
778 $dbman = $DB->get_manager();
779
780 $table = $this->get_test_table();
781 $tablename = $table->getName();
782
783 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
784 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
785 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
786 $dbman->create_table($table);
787 $this->tables[$tablename] = $table;
788
789 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
790 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
791 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
792 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
793 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
794 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
795 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
796
3ff8bf26 797 $this->assertTrue($records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
50a12c87 798 $this->assertEqual(2, count($records));
5d91786a 799 $this->assertEqual($inskey1, reset($records)->id);
800 $this->assertEqual($inskey4, next($records)->id);
0088bd31 801
16a2a412 802 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
803 $olddebug = $CFG->debug; // Save current debug settings
804 $olddisplay = $CFG->debugdisplay;
805 $CFG->debug = DEBUG_DEVELOPER;
806 $CFG->debugdisplay = true;
758ba89a 807 ob_start(); // hide debug warning
3ff8bf26 808 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {".$tablename."}", null);
758ba89a 809 ob_end_clean();
16a2a412 810 $debuginfo = ob_get_contents();
d83bcdfc 811 $CFG->debug = $olddebug; // Restore original debug settings
812 $CFG->debugdisplay = $olddisplay;
758ba89a 813
5d91786a 814 $this->assertEqual(6, count($records));
758ba89a 815 $this->assertFalse($debuginfo === '');
5d91786a 816
817 // negative limits = no limits
818 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, -1, -1);
819 $this->assertEqual(7, count($records));
820
821 // zero limits = no limits
822 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 0, 0);
823 $this->assertEqual(7, count($records));
824
825 // only limitfrom = skips that number of records
826 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 2, 0);
827 $this->assertEqual(5, count($records));
828 $this->assertEqual($inskey3, reset($records)->id);
829 $this->assertEqual($inskey7, end($records)->id);
830
831 // only limitnum = fetches that number of records
832 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 0, 3);
833 $this->assertEqual(3, count($records));
834 $this->assertEqual($inskey1, reset($records)->id);
835 $this->assertEqual($inskey3, end($records)->id);
836
837 // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones
838 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} ORDER BY id", null, 3, 2);
839 $this->assertEqual(2, count($records));
840 $this->assertEqual($inskey4, reset($records)->id);
841 $this->assertEqual($inskey5, end($records)->id);
842
843 // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here
0088bd31 844 }
845
846 public function test_get_records_menu() {
50a12c87 847 $DB = $this->tdb;
848 $dbman = $DB->get_manager();
849
9d833e93 850 $table = $this->get_test_table();
3ff8bf26 851 $tablename = $table->getName();
852
f9ecb171 853 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
854 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 855 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
856 $dbman->create_table($table);
3ff8bf26 857 $this->tables[$tablename] = $table;
50a12c87 858
3ff8bf26 859 $DB->insert_record($tablename, array('course' => 3));
860 $DB->insert_record($tablename, array('course' => 3));
861 $DB->insert_record($tablename, array('course' => 5));
862 $DB->insert_record($tablename, array('course' => 2));
50a12c87 863
3ff8bf26 864 $this->assertTrue($records = $DB->get_records_menu($tablename, array('course' => 3)));
50a12c87 865 $this->assertEqual(2, count($records));
866 $this->assertFalse(empty($records[1]));
867 $this->assertFalse(empty($records[2]));
868 $this->assertEqual(3, $records[1]);
869 $this->assertEqual(3, $records[2]);
0088bd31 870
5d91786a 871 // note: delegate limits testing to test_get_records_sql()
0088bd31 872 }
873
874 public function test_get_records_select_menu() {
50a12c87 875 $DB = $this->tdb;
876 $dbman = $DB->get_manager();
877
9d833e93 878 $table = $this->get_test_table();
3ff8bf26 879 $tablename = $table->getName();
880
f9ecb171 881 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
882 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 883 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
884 $dbman->create_table($table);
3ff8bf26 885 $this->tables[$tablename] = $table;
50a12c87 886
3ff8bf26 887 $DB->insert_record($tablename, array('course' => 3));
888 $DB->insert_record($tablename, array('course' => 2));
889 $DB->insert_record($tablename, array('course' => 3));
890 $DB->insert_record($tablename, array('course' => 5));
50a12c87 891
3ff8bf26 892 $this->assertTrue($records = $DB->get_records_select_menu($tablename, "course > ?", array(2)));
50a12c87 893
894 $this->assertEqual(3, count($records));
895 $this->assertFalse(empty($records[1]));
896 $this->assertTrue(empty($records[2]));
897 $this->assertFalse(empty($records[3]));
898 $this->assertFalse(empty($records[4]));
899 $this->assertEqual(3, $records[1]);
900 $this->assertEqual(3, $records[3]);
901 $this->assertEqual(5, $records[4]);
0088bd31 902
5d91786a 903 // note: delegate limits testing to test_get_records_sql()
0088bd31 904 }
905
906 public function test_get_records_sql_menu() {
50a12c87 907 $DB = $this->tdb;
908 $dbman = $DB->get_manager();
909
9d833e93 910 $table = $this->get_test_table();
3ff8bf26 911 $tablename = $table->getName();
912
f9ecb171 913 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
914 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 915 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
916 $dbman->create_table($table);
3ff8bf26 917 $this->tables[$tablename] = $table;
50a12c87 918
3ff8bf26 919 $DB->insert_record($tablename, array('course' => 3));
920 $DB->insert_record($tablename, array('course' => 2));
921 $DB->insert_record($tablename, array('course' => 3));
922 $DB->insert_record($tablename, array('course' => 5));
50a12c87 923
3ff8bf26 924 $this->assertTrue($records = $DB->get_records_sql_menu("SELECT * FROM {".$tablename."} WHERE course > ?", array(2)));
50a12c87 925
926 $this->assertEqual(3, count($records));
927 $this->assertFalse(empty($records[1]));
928 $this->assertTrue(empty($records[2]));
929 $this->assertFalse(empty($records[3]));
930 $this->assertFalse(empty($records[4]));
931 $this->assertEqual(3, $records[1]);
932 $this->assertEqual(3, $records[3]);
933 $this->assertEqual(5, $records[4]);
0088bd31 934
5d91786a 935 // note: delegate limits testing to test_get_records_sql()
0088bd31 936 }
937
938 public function test_get_record() {
50a12c87 939 $DB = $this->tdb;
940 $dbman = $DB->get_manager();
941
9d833e93 942 $table = $this->get_test_table();
3ff8bf26 943 $tablename = $table->getName();
944
f9ecb171 945 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
946 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 947 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
948 $dbman->create_table($table);
3ff8bf26 949 $this->tables[$tablename] = $table;
50a12c87 950
3ff8bf26 951 $DB->insert_record($tablename, array('course' => 3));
952 $DB->insert_record($tablename, array('course' => 2));
50a12c87 953
3ff8bf26 954 $this->assertTrue($record = $DB->get_record($tablename, array('id' => 2)));
0088bd31 955
50a12c87 956 $this->assertEqual(2, $record->course);
0088bd31 957 }
958
0088bd31 959 public function test_get_record_sql() {
50a12c87 960 $DB = $this->tdb;
961 $dbman = $DB->get_manager();
962
9d833e93 963 $table = $this->get_test_table();
3ff8bf26 964 $tablename = $table->getName();
965
f9ecb171 966 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
967 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 968 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
969 $dbman->create_table($table);
3ff8bf26 970 $this->tables[$tablename] = $table;
50a12c87 971
3ff8bf26 972 $DB->insert_record($tablename, array('course' => 3));
973 $DB->insert_record($tablename, array('course' => 2));
50a12c87 974
3ff8bf26 975 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(2)));
50a12c87 976
977 $this->assertEqual(2, $record->course);
0088bd31 978
e6c6531c 979 // backwards compatibility with $ignoremultiple
980 $this->assertFalse(IGNORE_MISSING);
981 $this->assertTrue(IGNORE_MULTIPLE);
982
af12ea93 983 // record not found
e6c6531c 984 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MISSING));
985 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MULTIPLE));
af12ea93 986 try {
e6c6531c 987 $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), MUST_EXIST);
af12ea93 988 $this->fail("Exception expected");
989 } catch (dml_missing_record_exception $e) {
990 $this->assertTrue(true);
991 }
992
993 // multiple matches
994 ob_start(); // hide debug warning
e6c6531c 995 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MISSING));
af12ea93 996 ob_end_clean();
e6c6531c 997 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MULTIPLE));
af12ea93 998 try {
e6c6531c 999 $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), MUST_EXIST);
af12ea93 1000 $this->fail("Exception expected");
1001 } catch (dml_multiple_records_exception $e) {
1002 $this->assertTrue(true);
1003 }
0088bd31 1004 }
1005
1006 public function test_get_field() {
50a12c87 1007 $DB = $this->tdb;
1008 $dbman = $DB->get_manager();
1009
9d833e93 1010 $table = $this->get_test_table();
3ff8bf26 1011 $tablename = $table->getName();
1012
f9ecb171 1013 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1014 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1015 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1016 $dbman->create_table($table);
3ff8bf26 1017 $this->tables[$tablename] = $table;
0088bd31 1018
3ff8bf26 1019 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1020
3ff8bf26 1021 $this->assertTrue($course = $DB->get_field($tablename, 'course', array('id' => 1)));
50a12c87 1022 $this->assertEqual(3, $course);
0088bd31 1023 }
1024
1025 public function test_get_field_select() {
50a12c87 1026 $DB = $this->tdb;
1027 $dbman = $DB->get_manager();
1028
9d833e93 1029 $table = $this->get_test_table();
3ff8bf26 1030 $tablename = $table->getName();
1031
f9ecb171 1032 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1033 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1034 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1035 $dbman->create_table($table);
3ff8bf26 1036 $this->tables[$tablename] = $table;
50a12c87 1037
3ff8bf26 1038 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1039
3ff8bf26 1040 $this->assertTrue($course = $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
50a12c87 1041 $this->assertEqual(3, $course);
0088bd31 1042
1043 }
1044
1045 public function test_get_field_sql() {
50a12c87 1046 $DB = $this->tdb;
1047 $dbman = $DB->get_manager();
1048
9d833e93 1049 $table = $this->get_test_table();
3ff8bf26 1050 $tablename = $table->getName();
1051
f9ecb171 1052 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1053 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1054 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1055 $dbman->create_table($table);
3ff8bf26 1056 $this->tables[$tablename] = $table;
50a12c87 1057
3ff8bf26 1058 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1059
3ff8bf26 1060 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {".$tablename."} WHERE id = ?", array(1)));
50a12c87 1061 $this->assertEqual(3, $course);
0088bd31 1062
1063 }
1064
50a12c87 1065 public function test_get_fieldset_select() {
1066 $DB = $this->tdb;
1067 $dbman = $DB->get_manager();
1068
9d833e93 1069 $table = $this->get_test_table();
3ff8bf26 1070 $tablename = $table->getName();
1071
f9ecb171 1072 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1073 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1074 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1075 $dbman->create_table($table);
3ff8bf26 1076 $this->tables[$tablename] = $table;
50a12c87 1077
3ff8bf26 1078 $DB->insert_record($tablename, array('course' => 1));
1079 $DB->insert_record($tablename, array('course' => 3));
1080 $DB->insert_record($tablename, array('course' => 2));
1081 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1082
3ff8bf26 1083 $this->assertTrue($fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)));
50a12c87 1084
1085 $this->assertEqual(3, count($fieldset));
1086 $this->assertEqual(3, $fieldset[0]);
1087 $this->assertEqual(2, $fieldset[1]);
1088 $this->assertEqual(6, $fieldset[2]);
0088bd31 1089
1090 }
1091
1092 public function test_get_fieldset_sql() {
50a12c87 1093 $DB = $this->tdb;
1094 $dbman = $DB->get_manager();
0088bd31 1095
9d833e93 1096 $table = $this->get_test_table();
3ff8bf26 1097 $tablename = $table->getName();
1098
f9ecb171 1099 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1100 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1101 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1102 $dbman->create_table($table);
3ff8bf26 1103 $this->tables[$tablename] = $table;
50a12c87 1104
3ff8bf26 1105 $DB->insert_record($tablename, array('course' => 1));
1106 $DB->insert_record($tablename, array('course' => 3));
1107 $DB->insert_record($tablename, array('course' => 2));
1108 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1109
3ff8bf26 1110 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {".$tablename."} WHERE course > ?", array(1)));
50a12c87 1111
1112 $this->assertEqual(3, count($fieldset));
1113 $this->assertEqual(2, $fieldset[0]);
1114 $this->assertEqual(3, $fieldset[1]);
1115 $this->assertEqual(4, $fieldset[2]);
0088bd31 1116 }
1117
1118 public function test_insert_record_raw() {
50a12c87 1119 $DB = $this->tdb;
1120 $dbman = $DB->get_manager();
0088bd31 1121
9d833e93 1122 $table = $this->get_test_table();
3ff8bf26 1123 $tablename = $table->getName();
1124
f9ecb171 1125 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1126 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1127 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1128 $dbman->create_table($table);
3ff8bf26 1129 $this->tables[$tablename] = $table;
50a12c87 1130
3ff8bf26 1131 $this->assertTrue($DB->insert_record_raw($tablename, array('course' => 1)));
1132 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 1)));
50a12c87 1133 $this->assertEqual(1, $record->course);
0088bd31 1134 }
1135
1136 public function test_insert_record() {
628dff25 1137
1138 // All the information in this test is fetched from DB by get_recordset() so we
1139 // have such method properly tested against nulls, empties and friends...
1140
1141 global $CFG;
1142
50a12c87 1143 $DB = $this->tdb;
1144 $dbman = $DB->get_manager();
1145
9d833e93 1146 $table = $this->get_test_table();
3ff8bf26 1147 $tablename = $table->getName();
1148
f9ecb171 1149 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1150 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1151 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1152 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1153 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1154 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1155 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1156 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1157 $dbman->create_table($table);
3ff8bf26 1158 $this->tables[$tablename] = $table;
50a12c87 1159
628dff25 1160 $this->assertTrue($DB->insert_record($tablename, array('course' => 1), false)); // Without returning id
1161 $rs = $DB->get_recordset($tablename, array('course' => 1));
1162 $record = $rs->current();
1163 $rs->close();
1164 $this->assertEqual(1, $record->id);
1165 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1166 $this->assertEqual(200, $record->onenum);
1167 $this->assertEqual('onestring', $record->onechar);
1168 $this->assertNull($record->onetext);
1169 $this->assertNull($record->onebinary);
1170
1171 // Check nulls are set properly for all types
1172 $record->oneint = null;
1173 $record->onenum = null;
1174 $record->onechar = null;
1175 $record->onetext = null;
1176 $record->onebinary = null;
1177 $recid = $DB->insert_record($tablename, $record);
1178 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1179 $record = $rs->current();
1180 $rs->close();
1181 $this->assertNull($record->oneint);
1182 $this->assertNull($record->onenum);
1183 $this->assertNull($record->onechar);
1184 $this->assertNull($record->onetext);
1185 $this->assertNull($record->onebinary);
1186
1187 // Check zeros are set properly for all types
1188 $record->oneint = 0;
1189 $record->onenum = 0;
1190 $recid = $DB->insert_record($tablename, $record);
1191 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1192 $record = $rs->current();
1193 $rs->close();
1194 $this->assertEqual(0, $record->oneint);
1195 $this->assertEqual(0, $record->onenum);
1196
1197 // Check booleans are set properly for all types
1198 $record->oneint = true; // trues
1199 $record->onenum = true;
1200 $record->onechar = true;
1201 $record->onetext = true;
1202 $recid = $DB->insert_record($tablename, $record);
1203 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1204 $record = $rs->current();
1205 $rs->close();
1206 $this->assertEqual(1, $record->oneint);
1207 $this->assertEqual(1, $record->onenum);
1208 $this->assertEqual(1, $record->onechar);
1209 $this->assertEqual(1, $record->onetext);
1210
1211 $record->oneint = false; // falses
1212 $record->onenum = false;
1213 $record->onechar = false;
1214 $record->onetext = false;
1215 $recid = $DB->insert_record($tablename, $record);
1216 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1217 $record = $rs->current();
1218 $rs->close();
1219 $this->assertEqual(0, $record->oneint);
1220 $this->assertEqual(0, $record->onenum);
1221 $this->assertEqual(0, $record->onechar);
1222 $this->assertEqual(0, $record->onetext);
1223
1224 // Check string data causes exception in numeric types
1225 $record->oneint = 'onestring';
1226 $record->onenum = 0;
1227 try {
1228 $DB->insert_record($tablename, $record);
1229 $this->fail("Expecting an exception, none occurred");
1230 } catch (exception $e) {
1231 $this->assertTrue($e instanceof dml_exception);
1232 }
1233 $record->oneint = 0;
1234 $record->onenum = 'onestring';
1235 try {
1236 $DB->insert_record($tablename, $record);
1237 $this->fail("Expecting an exception, none occurred");
1238 } catch (exception $e) {
1239 $this->assertTrue($e instanceof dml_exception);
1240 }
1241
1242 // Check empty strings are set properly in string types
1243 $record->oneint = 0;
1244 $record->onenum = 0;
1245 $record->onechar = '';
1246 $record->onetext = '';
1247 $recid = $DB->insert_record($tablename, $record);
1248 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1249 $record = $rs->current();
1250 $rs->close();
1251 $this->assertTrue($record->onechar === '');
1252 $this->assertTrue($record->onetext === '');
1253
1254 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1255 $record->oneint = ((210.10 + 39.92) - 150.02);
1256 $record->onenum = ((210.10 + 39.92) - 150.02);
1257 $recid = $DB->insert_record($tablename, $record);
1258 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1259 $record = $rs->current();
1260 $rs->close();
1261 $this->assertEqual(100, $record->oneint);
1262 $this->assertEqual(100, $record->onenum);
1263
1264 // Check various quotes/backslashes combinations in string types
1265 $teststrings = array(
1266 'backslashes and quotes alone (even): "" \'\' \\\\',
1267 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1268 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1269 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1270 foreach ($teststrings as $teststring) {
1271 $record->onechar = $teststring;
1272 $record->onetext = $teststring;
1273 $recid = $DB->insert_record($tablename, $record);
1274 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1275 $record = $rs->current();
1276 $rs->close();
1277 $this->assertEqual($teststring, $record->onechar);
1278 $this->assertEqual($teststring, $record->onetext);
1279 }
1280
1281 // Check LOBs in text/binary columns
1282 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1283 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1284 $record->onetext = $clob;
1285 $record->onebinary = $blob;
1286 $recid = $DB->insert_record($tablename, $record);
1287 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1288 $record = $rs->current();
1289 $rs->close();
1290 $this->assertEqual($clob, $record->onetext);
1291 $this->assertEqual($blob, $record->onebinary);
0088bd31 1292
628dff25 1293 // And "small" LOBs too, just in case
1294 $newclob = substr($clob, 0, 500);
1295 $newblob = substr($blob, 0, 250);
1296 $record->onetext = $newclob;
1297 $record->onebinary = $newblob;
1298 $recid = $DB->insert_record($tablename, $record);
1299 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1300 $record = $rs->current();
1301 $rs->close();
1302 $this->assertEqual($newclob, $record->onetext);
1303 $this->assertEqual($newblob, $record->onebinary);
5d91786a 1304 $this->assertEqual(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
0088bd31 1305 }
1306
94898738 1307 public function test_import_record() {
1308 $DB = $this->tdb;
1309 $dbman = $DB->get_manager();
1310
9d833e93 1311 $table = $this->get_test_table();
3ff8bf26 1312 $tablename = $table->getName();
1313
f9ecb171 1314 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1315 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
94898738 1316 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1317 $dbman->create_table($table);
3ff8bf26 1318 $this->tables[$tablename] = $table;
94898738 1319
1320 $record = (object)array('id'=>666, 'course'=>10);
3ff8bf26 1321 $this->assertTrue($DB->import_record($tablename, $record));
1322 $records = $DB->get_records($tablename);
94898738 1323 $this->assertEqual(1, count($records));
1324 $this->assertEqual(10, $records[666]->course);
1325
1326 $record = (object)array('id'=>13, 'course'=>2);
3ff8bf26 1327 $this->assertTrue($DB->import_record($tablename, $record));
1328 $records = $DB->get_records($tablename);
94898738 1329 $this->assertEqual(2, $records[13]->course);
1330 }
1331
0088bd31 1332 public function test_update_record_raw() {
50a12c87 1333 $DB = $this->tdb;
1334 $dbman = $DB->get_manager();
0088bd31 1335
9d833e93 1336 $table = $this->get_test_table();
3ff8bf26 1337 $tablename = $table->getName();
1338
f9ecb171 1339 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1340 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1341 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1342 $dbman->create_table($table);
3ff8bf26 1343 $this->tables[$tablename] = $table;
50a12c87 1344
3ff8bf26 1345 $DB->insert_record($tablename, array('course' => 1));
1346 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1347 $record->course = 2;
3ff8bf26 1348 $this->assertTrue($DB->update_record_raw($tablename, $record));
1349 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1350 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1351 }
1352
1353 public function test_update_record() {
628dff25 1354
1355 // All the information in this test is fetched from DB by get_record() so we
1356 // have such method properly tested against nulls, empties and friends...
1357
1358 global $CFG;
1359
50a12c87 1360 $DB = $this->tdb;
1361 $dbman = $DB->get_manager();
1362
9d833e93 1363 $table = $this->get_test_table();
3ff8bf26 1364 $tablename = $table->getName();
1365
f9ecb171 1366 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1367 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1368 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1369 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1370 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1371 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1372 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1373 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1374 $dbman->create_table($table);
3ff8bf26 1375 $this->tables[$tablename] = $table;
0088bd31 1376
3ff8bf26 1377 $DB->insert_record($tablename, array('course' => 1));
1378 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1379 $record->course = 2;
628dff25 1380
3ff8bf26 1381 $this->assertTrue($DB->update_record($tablename, $record));
1382 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1383 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
628dff25 1384 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1385 $this->assertEqual(200, $record->onenum);
1386 $this->assertEqual('onestring', $record->onechar);
1387 $this->assertNull($record->onetext);
1388 $this->assertNull($record->onebinary);
c824e1e1 1389
628dff25 1390 // Check nulls are set properly for all types
1391 $record->oneint = null;
1392 $record->onenum = null;
1393 $record->onechar = null;
1394 $record->onetext = null;
1395 $record->onebinary = null;
1396 $DB->update_record($tablename, $record);
1397 $record = $DB->get_record($tablename, array('course' => 2));
1398 $this->assertNull($record->oneint);
1399 $this->assertNull($record->onenum);
1400 $this->assertNull($record->onechar);
1401 $this->assertNull($record->onetext);
1402 $this->assertNull($record->onebinary);
c824e1e1 1403
628dff25 1404 // Check zeros are set properly for all types
1405 $record->oneint = 0;
1406 $record->onenum = 0;
1407 $DB->update_record($tablename, $record);
1408 $record = $DB->get_record($tablename, array('course' => 2));
1409 $this->assertEqual(0, $record->oneint);
1410 $this->assertEqual(0, $record->onenum);
c824e1e1 1411
628dff25 1412 // Check booleans are set properly for all types
1413 $record->oneint = true; // trues
1414 $record->onenum = true;
1415 $record->onechar = true;
1416 $record->onetext = true;
1417 $DB->update_record($tablename, $record);
1418 $record = $DB->get_record($tablename, array('course' => 2));
1419 $this->assertEqual(1, $record->oneint);
1420 $this->assertEqual(1, $record->onenum);
1421 $this->assertEqual(1, $record->onechar);
1422 $this->assertEqual(1, $record->onetext);
1423
1424 $record->oneint = false; // falses
1425 $record->onenum = false;
1426 $record->onechar = false;
1427 $record->onetext = false;
1428 $DB->update_record($tablename, $record);
1429 $record = $DB->get_record($tablename, array('course' => 2));
1430 $this->assertEqual(0, $record->oneint);
1431 $this->assertEqual(0, $record->onenum);
1432 $this->assertEqual(0, $record->onechar);
1433 $this->assertEqual(0, $record->onetext);
c824e1e1 1434
628dff25 1435 // Check string data causes exception in numeric types
1436 $record->oneint = 'onestring';
1437 $record->onenum = 0;
1438 try {
1439 $DB->update_record($tablename, $record);
1440 $this->fail("Expecting an exception, none occurred");
1441 } catch (exception $e) {
1442 $this->assertTrue($e instanceof dml_exception);
1443 }
1444 $record->oneint = 0;
1445 $record->onenum = 'onestring';
1446 try {
1447 $DB->update_record($tablename, $record);
1448 $this->fail("Expecting an exception, none occurred");
1449 } catch (exception $e) {
1450 $this->assertTrue($e instanceof dml_exception);
1451 }
c824e1e1 1452
628dff25 1453 // Check empty strings are set properly in string types
1454 $record->oneint = 0;
1455 $record->onenum = 0;
1456 $record->onechar = '';
1457 $record->onetext = '';
1458 $DB->update_record($tablename, $record);
1459 $record = $DB->get_record($tablename, array('course' => 2));
1460 $this->assertTrue($record->onechar === '');
1461 $this->assertTrue($record->onetext === '');
c824e1e1 1462
628dff25 1463 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1464 $record->oneint = ((210.10 + 39.92) - 150.02);
1465 $record->onenum = ((210.10 + 39.92) - 150.02);
1466 $DB->update_record($tablename, $record);
1467 $record = $DB->get_record($tablename, array('course' => 2));
1468 $this->assertEqual(100, $record->oneint);
1469 $this->assertEqual(100, $record->onenum);
3ff8bf26 1470
628dff25 1471 // Check various quotes/backslashes combinations in string types
1472 $teststrings = array(
1473 'backslashes and quotes alone (even): "" \'\' \\\\',
1474 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1475 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1476 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1477 foreach ($teststrings as $teststring) {
1478 $record->onechar = $teststring;
1479 $record->onetext = $teststring;
1480 $DB->update_record($tablename, $record);
1481 $record = $DB->get_record($tablename, array('course' => 2));
1482 $this->assertEqual($teststring, $record->onechar);
1483 $this->assertEqual($teststring, $record->onetext);
1484 }
c824e1e1 1485
628dff25 1486 // Check LOBs in text/binary columns
6ff835b7 1487 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1488 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
628dff25 1489 $record->onetext = $clob;
1490 $record->onebinary = $blob;
1491 $DB->update_record($tablename, $record);
1492 $record = $DB->get_record($tablename, array('course' => 2));
1493 $this->assertEqual($clob, $record->onetext);
1494 $this->assertEqual($blob, $record->onebinary);
1495
1496 // And "small" LOBs too, just in case
d246cdd2 1497 $newclob = substr($clob, 0, 500);
1498 $newblob = substr($blob, 0, 250);
628dff25 1499 $record->onetext = $newclob;
1500 $record->onebinary = $newblob;
1501 $DB->update_record($tablename, $record);
1502 $record = $DB->get_record($tablename, array('course' => 2));
1503 $this->assertEqual($newclob, $record->onetext);
1504 $this->assertEqual($newblob, $record->onebinary);
c824e1e1 1505 }
1506
0088bd31 1507 public function test_set_field() {
50a12c87 1508 $DB = $this->tdb;
1509 $dbman = $DB->get_manager();
1510
9d833e93 1511 $table = $this->get_test_table();
3ff8bf26 1512 $tablename = $table->getName();
1513
f9ecb171 1514 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1515 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1516 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1517 $dbman->create_table($table);
3ff8bf26 1518 $this->tables[$tablename] = $table;
0088bd31 1519
3ff8bf26 1520 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1521
3ff8bf26 1522 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1523 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
fbabe35a 1524
1525 // Note: All the nulls, booleans, empties, quoted and backslashes tests
1526 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 1527 }
1528
1529 public function test_set_field_select() {
628dff25 1530
1531 // All the information in this test is fetched from DB by get_field() so we
1532 // have such method properly tested against nulls, empties and friends...
1533
fbabe35a 1534 global $CFG;
1535
50a12c87 1536 $DB = $this->tdb;
1537 $dbman = $DB->get_manager();
1538
9d833e93 1539 $table = $this->get_test_table();
3ff8bf26 1540 $tablename = $table->getName();
1541
f9ecb171 1542 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1543 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 1544 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
1545 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
1546 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
1547 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1548 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1549 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1550 $dbman->create_table($table);
3ff8bf26 1551 $this->tables[$tablename] = $table;
50a12c87 1552
3ff8bf26 1553 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1554
3ff8bf26 1555 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1556 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1557
fbabe35a 1558 // Check nulls are set properly for all types
628dff25 1559 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
1560 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
1561 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
1562 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
1563 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
fbabe35a 1564 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
1565 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
1566 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
1567 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
1568 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
1569
1570 // Check zeros are set properly for all types
1571 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
1572 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
1573 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1574 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1575
1576 // Check booleans are set properly for all types
1577 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
1578 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
1579 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
1580 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
1581 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1582 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1583 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1584 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1585
1586 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
1587 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
1588 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
1589 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
1590 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1591 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1592 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1593 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1594
1595 // Check string data causes exception in numeric types
1596 try {
1597 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
1598 $this->fail("Expecting an exception, none occurred");
1599 } catch (exception $e) {
1600 $this->assertTrue($e instanceof dml_exception);
1601 }
1602 try {
1603 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
1604 $this->fail("Expecting an exception, none occurred");
1605 } catch (exception $e) {
1606 $this->assertTrue($e instanceof dml_exception);
1607 }
1608
1609 // Check empty strings are set properly in string types
1610 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
1611 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
1612 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
1613 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
1614
1615 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1616 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1617 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1618 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1619 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1620
1621 // Check various quotes/backslashes combinations in string types
1622 $teststrings = array(
628dff25 1623 'backslashes and quotes alone (even): "" \'\' \\\\',
1624 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1625 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1626 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
fbabe35a 1627 foreach ($teststrings as $teststring) {
1628 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
1629 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
1630 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1631 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1632 }
1633
1634 // Check LOBs in text/binary columns
1635 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1636 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1637 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
1638 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
1639 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1640 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
1641
1642 // And "small" LOBs too, just in case
1643 $newclob = substr($clob, 0, 500);
1644 $newblob = substr($blob, 0, 250);
1645 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
1646 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
1647 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1648 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
0088bd31 1649 }
1650
1651 public function test_count_records() {
1652 $DB = $this->tdb;
1653
1654 $dbman = $DB->get_manager();
1655
9d833e93 1656 $table = $this->get_test_table();
3ff8bf26 1657 $tablename = $table->getName();
1658
f9ecb171 1659 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1660 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1661 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1662 $dbman->create_table($table);
3ff8bf26 1663 $this->tables[$tablename] = $table;
0088bd31 1664
3ff8bf26 1665 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1666
3ff8bf26 1667 $DB->insert_record($tablename, array('course' => 3));
1668 $DB->insert_record($tablename, array('course' => 4));
1669 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1670
3ff8bf26 1671 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1672 }
1673
1674 public function test_count_records_select() {
50a12c87 1675 $DB = $this->tdb;
0088bd31 1676
50a12c87 1677 $dbman = $DB->get_manager();
1678
9d833e93 1679 $table = $this->get_test_table();
3ff8bf26 1680 $tablename = $table->getName();
1681
f9ecb171 1682 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1683 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1684 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1685 $dbman->create_table($table);
3ff8bf26 1686 $this->tables[$tablename] = $table;
50a12c87 1687
3ff8bf26 1688 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1689
3ff8bf26 1690 $DB->insert_record($tablename, array('course' => 3));
1691 $DB->insert_record($tablename, array('course' => 4));
1692 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1693
3ff8bf26 1694 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1695 }
1696
1697 public function test_count_records_sql() {
50a12c87 1698 $DB = $this->tdb;
1699 $dbman = $DB->get_manager();
1700
9d833e93 1701 $table = $this->get_test_table();
3ff8bf26 1702 $tablename = $table->getName();
1703
f9ecb171 1704 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1705 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1706 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1707 $dbman->create_table($table);
3ff8bf26 1708 $this->tables[$tablename] = $table;
50a12c87 1709
3ff8bf26 1710 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1711
3ff8bf26 1712 $DB->insert_record($tablename, array('course' => 3));
1713 $DB->insert_record($tablename, array('course' => 4));
1714 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1715
3ff8bf26 1716 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1717 }
1718
1719 public function test_record_exists() {
50a12c87 1720 $DB = $this->tdb;
1721 $dbman = $DB->get_manager();
1722
9d833e93 1723 $table = $this->get_test_table();
3ff8bf26 1724 $tablename = $table->getName();
1725
f9ecb171 1726 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1727 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1728 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1729 $dbman->create_table($table);
3ff8bf26 1730 $this->tables[$tablename] = $table;
50a12c87 1731
3ff8bf26 1732 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1733
3ff8bf26 1734 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1735 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1736
3ff8bf26 1737 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1738
1739 }
1740
1741 public function test_record_exists_select() {
50a12c87 1742 $DB = $this->tdb;
1743 $dbman = $DB->get_manager();
0088bd31 1744
9d833e93 1745 $table = $this->get_test_table();
3ff8bf26 1746 $tablename = $table->getName();
1747
f9ecb171 1748 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1749 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1750 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1751 $dbman->create_table($table);
3ff8bf26 1752 $this->tables[$tablename] = $table;
50a12c87 1753
3ff8bf26 1754 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1755
3ff8bf26 1756 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1757 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1758
3ff8bf26 1759 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1760 }
1761
1762 public function test_record_exists_sql() {
50a12c87 1763 $DB = $this->tdb;
1764 $dbman = $DB->get_manager();
0088bd31 1765
9d833e93 1766 $table = $this->get_test_table();
3ff8bf26 1767 $tablename = $table->getName();
1768
f9ecb171 1769 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1770 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1771 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1772 $dbman->create_table($table);
3ff8bf26 1773 $this->tables[$tablename] = $table;
50a12c87 1774
3ff8bf26 1775 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1776
3ff8bf26 1777 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1778 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1779
3ff8bf26 1780 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1781 }
1782
1783 public function test_delete_records() {
50a12c87 1784 $DB = $this->tdb;
1785 $dbman = $DB->get_manager();
0088bd31 1786
9d833e93 1787 $table = $this->get_test_table();
3ff8bf26 1788 $tablename = $table->getName();
1789
f9ecb171 1790 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1791 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1792 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1793 $dbman->create_table($table);
3ff8bf26 1794 $this->tables[$tablename] = $table;
50a12c87 1795
3ff8bf26 1796 $DB->insert_record($tablename, array('course' => 3));
1797 $DB->insert_record($tablename, array('course' => 2));
1798 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1799
1800 // Delete all records
3ff8bf26 1801 $this->assertTrue($DB->delete_records($tablename));
1802 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1803
1804 // Delete subset of records
3ff8bf26 1805 $DB->insert_record($tablename, array('course' => 3));
1806 $DB->insert_record($tablename, array('course' => 2));
1807 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1808
3ff8bf26 1809 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1810 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1811 }
1812
1813 public function test_delete_records_select() {
50a12c87 1814 $DB = $this->tdb;
1815 $dbman = $DB->get_manager();
0088bd31 1816
9d833e93 1817 $table = $this->get_test_table();
3ff8bf26 1818 $tablename = $table->getName();
1819
f9ecb171 1820 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1821 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1822 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1823 $dbman->create_table($table);
3ff8bf26 1824 $this->tables[$tablename] = $table;
0088bd31 1825
3ff8bf26 1826 $DB->insert_record($tablename, array('course' => 3));
1827 $DB->insert_record($tablename, array('course' => 2));
1828 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1829
3ff8bf26 1830 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1831 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1832 }
1d861fce 1833
c362878e 1834 public function test_delete_records_list() {
1835 $DB = $this->tdb;
1836 $dbman = $DB->get_manager();
1837
1838 $table = $this->get_test_table();
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');
c362878e 1843 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1844 $dbman->create_table($table);
1845 $this->tables[$tablename] = $table;
1846
1847 $DB->insert_record($tablename, array('course' => 1));
1848 $DB->insert_record($tablename, array('course' => 2));
1849 $DB->insert_record($tablename, array('course' => 3));
1850
1851 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1852 $this->assertEqual(1, $DB->count_records($tablename));
1853
1854 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1855 $this->assertEqual(1, $DB->count_records($tablename));
1856 }
1857
082ae821 1858 function test_sql_null_from_clause() {
655bbf51 1859 $DB = $this->tdb;
082ae821 1860 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1861 $this->assertEqual($DB->get_field_sql($sql), 1);
1862 }
655bbf51 1863
082ae821 1864 function test_sql_bitand() {
1865 $DB = $this->tdb;
9d833e93 1866 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1867 $this->assertEqual($DB->get_field_sql($sql), 2);
1868 }
655bbf51 1869
082ae821 1870 function test_sql_bitnot() {
1871 $DB = $this->tdb;
655bbf51 1872
082ae821 1873 $not = $DB->sql_bitnot(2);
1874 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1875
9d833e93 1876 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1877 $this->assertEqual($DB->get_field_sql($sql), 5);
1878 }
655bbf51 1879
082ae821 1880 function test_sql_bitor() {
1881 $DB = $this->tdb;
9d833e93 1882 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1883 $this->assertEqual($DB->get_field_sql($sql), 11);
1884 }
655bbf51 1885
082ae821 1886 function test_sql_bitxor() {
1887 $DB = $this->tdb;
9d833e93 1888 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1889 $this->assertEqual($DB->get_field_sql($sql), 9);
1890 }
1891
e6df3734 1892 function test_sql_modulo() {
1893 $DB = $this->tdb;
9d833e93 1894 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1895 $this->assertEqual($DB->get_field_sql($sql), 3);
1896 }
1897
082ae821 1898 function test_sql_ceil() {
1899 $DB = $this->tdb;
9d833e93 1900 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1901 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1902 }
1903
6ff835b7 1904 function test_cast_char2int() {
1905 $DB = $this->tdb;
1906 $dbman = $DB->get_manager();
1907
9d833e93 1908 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1909 $tablename1 = $table1->getName();
1910
f9ecb171 1911 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1912 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1913 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1914 $dbman->create_table($table1);
3ff8bf26 1915 $this->tables[$tablename1] = $table1;
6ff835b7 1916
3ff8bf26 1917 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1918
9d833e93 1919 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1920 $tablename2 = $table2->getName();
f9ecb171 1921 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1922 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 1923 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1924 $dbman->create_table($table2);
1925 $this->tables[$table2->getName()] = $table2;
1926
9d833e93 1927 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 1928
1929 try {
9d833e93 1930 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 1931 $records = $DB->get_records_sql($sql);
1932 $this->assertEqual(count($records), 1);
1933 } catch (dml_exception $e) {
1934 $this->fail("No exception expected");
1935 }
1936 }
1937
1938 function test_cast_char2real() {
1939 $DB = $this->tdb;
1940 $dbman = $DB->get_manager();
1941
9d833e93 1942 $table = $this->get_test_table();
3ff8bf26 1943 $tablename = $table->getName();
1944
f9ecb171 1945 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1946 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1947 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 1948 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1949 $dbman->create_table($table);
3ff8bf26 1950 $this->tables[$tablename] = $table;
6ff835b7 1951
9d833e93 1952 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
1953 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
1954 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 1955
9d833e93 1956 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 1957 $records = $DB->get_records_sql($sql);
1958 $this->assertEqual(count($records), 2);
1959 }
1960
082ae821 1961 function sql_compare_text() {
1962 $DB = $this->tdb;
1963 $dbman = $DB->get_manager();
1964
9d833e93 1965 $table = $this->get_test_table();
3ff8bf26 1966 $tablename = $table->getName();
1967
f9ecb171 1968 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1969 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1970 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1971 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1972 $dbman->create_table($table);
3ff8bf26 1973 $this->tables[$tablename] = $table;
082ae821 1974
3ff8bf26 1975 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
1976 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
1977 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 1978
3ff8bf26 1979 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 1980 $records = $DB->get_records_sql($sql);
1981 $this->assertEqual(count($records), 1);
1982
3ff8bf26 1983 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 1984 $records = $DB->get_records_sql($sql);
1985 $this->assertEqual(count($records), 2);
1986 }
1987
6ff835b7 1988 function test_ilike() {
1989 $DB = $this->tdb;
1990 $dbman = $DB->get_manager();
1991
9d833e93 1992 $table = $this->get_test_table();
3ff8bf26 1993 $tablename = $table->getName();
1994
f9ecb171 1995 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1996 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1997 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1998 $dbman->create_table($table);
3ff8bf26 1999 $this->tables[$tablename] = $table;
6ff835b7 2000
3ff8bf26 2001 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2002 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2003 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2004
3ff8bf26 2005 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2006 $params = array("%dup_r%");
2007 $records = $DB->get_records_sql($sql, $params);
2008 $this->assertEqual(count($records), 2);
2009 }
2010
71a71d59 2011 function test_sql_concat() {
6ff835b7 2012 $DB = $this->tdb;
71a71d59 2013 $dbman = $DB->get_manager();
2014
2015 /// Testing all sort of values
2016 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2017 // string, some unicode chars
2018 $params = array('name', 'áéíóú', 'name3');
2019 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2020 // string, spaces and numbers
2021 $params = array('name', ' ', 12345);
2022 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2023 // float, empty and strings
2024 $params = array(123.45, '', 'test');
2025 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2026 // float, null and strings
2027 $params = array(123.45, null, 'test');
52a01626 2028 $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 2029
2030 /// Testing fieldnames + values
2031 $table = $this->get_test_table();
2032 $tablename = $table->getName();
2033
2034 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2035 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2036 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2037 $dbman->create_table($table);
2038 $this->tables[$tablename] = $table;
2039
2040 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2041 $DB->insert_record($tablename, array('description'=>'dxxx'));
2042 $DB->insert_record($tablename, array('description'=>'bcde'));
2043
2044 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2045 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2046 $this->assertEqual(count($records), 3);
2047 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2048 }
2049
082ae821 2050 function test_concat_join() {
6ff835b7 2051 $DB = $this->tdb;
082ae821 2052 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2053 $params = array("name", "name2", "name3");
2054 $result = $DB->get_field_sql($sql, $params);
2055 $this->assertEqual("name name2 name3", $result);
2056 }
2057
2058 function test_sql_fullname() {
2059 $DB = $this->tdb;
2060 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2061 $params = array('first'=>'Firstname', 'last'=>'Surname');
2062 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2063 }
2064
2065 function sql_sql_order_by_text() {
2066 $DB = $this->tdb;
2067 $dbman = $DB->get_manager();
2068
9d833e93 2069 $table = $this->get_test_table();
3ff8bf26 2070 $tablename = $table->getName();
2071
f9ecb171 2072 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2073 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2074 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2075 $dbman->create_table($table);
3ff8bf26 2076 $this->tables[$tablename] = $table;
082ae821 2077
3ff8bf26 2078 $DB->insert_record($tablename, array('description'=>'abcd'));
2079 $DB->insert_record($tablename, array('description'=>'dxxx'));
2080 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2081
3ff8bf26 2082 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2083 $records = $DB->get_records_sql($sql);
2084 $first = array_unshift($records);
2085 $this->assertEqual(1, $first->id);
2086 $second = array_unshift($records);
2087 $this->assertEqual(3, $second->id);
2088 $last = array_unshift($records);
2089 $this->assertEqual(2, $last->id);
2090 }
2091
2092 function test_sql_substring() {
2093 $DB = $this->tdb;
2094 $dbman = $DB->get_manager();
2095
9d833e93 2096 $table = $this->get_test_table();
3ff8bf26 2097 $tablename = $table->getName();
2098
f9ecb171 2099 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2100 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 2101 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2102 $dbman->create_table($table);
3ff8bf26 2103 $this->tables[$tablename] = $table;
082ae821 2104
2105 $string = 'abcdefghij';
2106
3ff8bf26 2107 $DB->insert_record($tablename, array('name'=>$string));
082ae821 2108
3ff8bf26 2109 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 2110 $record = $DB->get_record_sql($sql);
2111 $this->assertEqual(substr($string, 5-1), $record->name);
2112
3ff8bf26 2113 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 2114 $record = $DB->get_record_sql($sql);
2115 $this->assertEqual(substr($string, 5-1, 2), $record->name);
2116
2117 try {
2118 // silence php warning ;-)
2119 @$DB->sql_substr("name");
2120 $this->fail("Expecting an exception, none occurred");
2121 } catch (Exception $e) {
2122 $this->assertTrue($e instanceof coding_exception);
2123 }
6ff835b7 2124 }
2125
37d975e1 2126 function test_sql_length() {
2127 $DB = $this->tdb;
2128 $this->assertEqual($DB->get_field_sql(
2129 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
2130 $this->assertEqual($DB->get_field_sql(
2131 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
2132 }
2133
1d861fce 2134 function test_sql_position() {
2135 $DB = $this->tdb;
2136 $this->assertEqual($DB->get_field_sql(
6ff835b7 2137 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 2138 $this->assertEqual($DB->get_field_sql(
6ff835b7 2139 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
2140 }
2141
082ae821 2142 function test_sql_empty() {
2143 $DB = $this->tdb;
2144 $dbman = $DB->get_manager();
2145
9d833e93 2146 $table = $this->get_test_table();
3ff8bf26 2147 $tablename = $table->getName();
2148
f9ecb171 2149 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2150 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 2151 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
2152 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 2153 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2154 $dbman->create_table($table);
3ff8bf26 2155 $this->tables[$tablename] = $table;
082ae821 2156
cf874b29 2157 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 2158 $DB->insert_record($tablename, array('name'=>null));
cf874b29 2159 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 2160 $DB->insert_record($tablename, array('name'=>0));
082ae821 2161
3ff8bf26 2162 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 2163 $this->assertEqual(count($records), 1);
2164 $record = reset($records);
2165 $this->assertEqual($record->name, '');
cf874b29 2166
2167 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
2168 $this->assertEqual(count($records), 1);
2169 $record = reset($records);
2170 $this->assertEqual($record->namenotnull, '');
2171
2172 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
2173 $this->assertEqual(count($records), 4);
2174 $record = reset($records);
2175 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 2176 }
2177
2178 function test_sql_isempty() {
2179 $DB = $this->tdb;
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('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2187 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2188 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2189 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2190 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2191 $dbman->create_table($table);
3ff8bf26 2192 $this->tables[$tablename] = $table;
082ae821 2193
3ff8bf26 2194 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2195 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2196 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2197 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 2198
3ff8bf26 2199 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 2200 $this->assertEqual(count($records), 1);
2201 $record = reset($records);
2202 $this->assertEqual($record->name, '');
2203
3ff8bf26 2204 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 2205 $this->assertEqual(count($records), 1);
2206 $record = reset($records);
2207 $this->assertEqual($record->namenull, '');
2208
3ff8bf26 2209 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 2210 $this->assertEqual(count($records), 1);
2211 $record = reset($records);
2212 $this->assertEqual($record->description, '');
2213
3ff8bf26 2214 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 2215 $this->assertEqual(count($records), 1);
2216 $record = reset($records);
2217 $this->assertEqual($record->descriptionnull, '');
2218 }
2219
2220 function sql_isnotempty() {
2221 //TODO
2222 }
2223
2224 function test_sql_regex() {
6ff835b7 2225 $DB = $this->tdb;
2226 $dbman = $DB->get_manager();
2227
9d833e93 2228 $table = $this->get_test_table();
3ff8bf26 2229 $tablename = $table->getName();
2230
f9ecb171 2231 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2232 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2233 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2234 $dbman->create_table($table);
3ff8bf26 2235 $this->tables[$tablename] = $table;
6ff835b7 2236
3ff8bf26 2237 $DB->insert_record($tablename, array('name'=>'lalala'));
2238 $DB->insert_record($tablename, array('name'=>'holaaa'));
2239 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2240
3ff8bf26 2241 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2242 $params = array('a$');
dd1611a5 2243 if ($DB->sql_regex_supported()) {
2244 $records = $DB->get_records_sql($sql, $params);
2245 $this->assertEqual(count($records), 2);
2246 } else {
2247 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2248 }
6ff835b7 2249
3ff8bf26 2250 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2251 $params = array('.a');
dd1611a5 2252 if ($DB->sql_regex_supported()) {
2253 $records = $DB->get_records_sql($sql, $params);
2254 $this->assertEqual(count($records), 1);
2255 } else {
2256 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2257 }
6ff835b7 2258
1d861fce 2259 }
fb76304b 2260
2261 function test_begin_sql() {
2262 $DB = $this->tdb;
2263 $dbman = $DB->get_manager();
2264
9d833e93 2265 $table = $this->get_test_table();
3ff8bf26 2266 $tablename = $table->getName();
2267
f9ecb171 2268 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2269 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2270 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2271 $dbman->create_table($table);
3ff8bf26 2272 $this->tables[$tablename] = $table;
fb76304b 2273
2274 $active = $DB->begin_sql();
2275 if ($active) {
2276 // test only if driver supports transactions
2277 $data = (object)array('course'=>3);
3ff8bf26 2278 $DB->insert_record($tablename, $data);
2279 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2280 $DB->commit_sql();
167cbee8 2281 } else {
2282 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2283 }
2284 }
2285
2286 function test_commit_sql() {
2287 $DB = $this->tdb;
2288 $dbman = $DB->get_manager();
2289
9d833e93 2290 $table = $this->get_test_table();
3ff8bf26 2291 $tablename = $table->getName();
2292
f9ecb171 2293 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2294 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2295 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2296 $dbman->create_table($table);
3ff8bf26 2297 $this->tables[$tablename] = $table;
fb76304b 2298
2299 $active = $DB->begin_sql();
2300 if ($active) {
2301 // test only if driver supports transactions
2302 $data = (object)array('course'=>3);
3ff8bf26 2303 $DB->insert_record($tablename, $data);
fb76304b 2304 $DB->commit_sql();
3ff8bf26 2305 $this->assertEqual(1, $DB->count_records($tablename));
167cbee8 2306 } else {
2307 $this->assertTrue(true, 'BD Transactions not supported. Test skipped');
fb76304b 2308 }
2309 }
2310
2311 function test_rollback_sql() {
2312 $DB = $this->tdb;
2313 $dbman = $DB->get_manager();
2314
9d833e93 2315 $table = $this->get_test_table();
3ff8bf26 2316 $tablename = $table->getName();
2317
f9ecb171 2318 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2319 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2320 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2321 $dbman->create_table($table);
3ff8bf26 2322 $this->tables[$tablename] = $table;
fb76304b 2323
2324 $active = $DB->begin_sql();
2325 if ($active) {
2326 // test only if driver supports transactions
2327 $data = (object)array('course'=>3);
3ff8bf26 2328 $DB->insert_record($tablename, $data);
fb76304b 2329 $DB->rollback_sql();
3ff8bf26 2330 $this->assertEqual(0, $DB->count_records($tablename));
167cbee8 2331 } else {
81f5be12 2332 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2333 }
2334 }
8ee8780e
DP
2335
2336 /**
2337 * Test some more complex SQL syntax which moodle uses and depends on to work
2338 * useful to determine if new database libraries can be supported.
2339 */
2340 public function test_get_records_sql_complicated() {
2341 global $CFG;
2342 $DB = $this->tdb;
2343 $dbman = $DB->get_manager();
2344
2345 $table = $this->get_test_table();
2346 $tablename = $table->getName();
2347
2348 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2349 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2350 $dbman->create_table($table);
2351 $this->tables[$tablename] = $table;
2352
2353 $DB->insert_record($tablename, array('course' => 3));
2354 $DB->insert_record($tablename, array('course' => 3));
2355 $DB->insert_record($tablename, array('course' => 5));
2356 $DB->insert_record($tablename, array('course' => 2));
2357
2358 // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
2359 $sql = 'SELECT a.id AS id, a.course AS course
2360 FROM {'.$tablename.'} a
2361 JOIN (SELECT * FROM {'.$tablename.'}) b
2362 ON a.id = b.id
2363 WHERE a.course = ?';
2364
2365 $this->assertTrue($records = $DB->get_records_sql($sql, array(3)));
2366 $this->assertEqual(2, count($records));
2367 $this->assertEqual(1, reset($records)->id);
2368 $this->assertEqual(2, next($records)->id);
2369 }
7f9f11b5 2370}
2371
2372/**
2373 * This class is not a proper subclass of moodle_database. It is
2374 * intended to be used only in unit tests, in order to gain access to the
2375 * protected methods of moodle_database, and unit test them.
2376 */
2377class moodle_database_for_testing extends moodle_database {
2378 protected $prefix = 'mdl_';
2379
2380 public function public_fix_table_names($sql) {
2381 return $this->fix_table_names($sql);
2382 }
2383
2384 public function driver_installed(){}
2385 public function get_dbfamily(){}
2386 protected function get_dbtype(){}
16a5642c 2387 protected function get_dblibrary(){}
7f9f11b5 2388 public function get_name(){}
37d975e1 2389 public function get_configuration_help(){}
7f9f11b5 2390 public function get_configuration_hints(){}
beaa43db 2391 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2392 public function get_server_info(){}
2393 protected function allowed_param_types(){}
2394 public function get_last_error(){}
117679db 2395 public function get_tables($usecache=true){}
7f9f11b5 2396 public function get_indexes($table){}
2397 public function get_columns($table, $usecache=true){}
2398 public function set_debug($state){}
2399 public function get_debug(){}
2400 public function set_logging($state){}
2401 public function change_database_structure($sql){}
2402 public function execute($sql, array $params=null){}
2403 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2404 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2405 public function get_fieldset_sql($sql, array $params=null){}
94898738 2406 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 2407 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 2408 public function import_record($table, $dataobject){}
7f9f11b5 2409 public function update_record_raw($table, $params, $bulk=false){}
2410 public function update_record($table, $dataobject, $bulk=false){}
2411 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2412 public function delete_records_select($table, $select, array $params=null){}
2413 public function sql_concat(){}
2414 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2415 public function sql_substr($expr, $start, $length=false){}
bb78c788 2416}