MDL-14679 fixing limitfrom, limitnum queries under pgsql, mssql and oracle
[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();
7f9f11b5 470 }
0088bd31 471
b0cb2290 472 public function test_get_recordset_iterator_keys() {
473 $DB = $this->tdb;
474 $dbman = $DB->get_manager();
475
9d833e93 476 $table = $this->get_test_table();
3ff8bf26 477 $tablename = $table->getName();
478
f9ecb171 479 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
480 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
481 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b0cb2290 482 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
483 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
484 $dbman->create_table($table);
3ff8bf26 485 $this->tables[$tablename] = $table;
b0cb2290 486
22d77567 487 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
488 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
489 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 490 foreach ($data as $record) {
3ff8bf26 491 $DB->insert_record($tablename, $record);
b0cb2290 492 }
493
494 /// Test repeated numeric keys are returned ok
3ff8bf26 495 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 496
497 reset($data);
498 $count = 0;
499 foreach($rs as $key => $record) {
500 $data_record = current($data);
501 $this->assertEqual($data_record['course'], $key);
502 next($data);
503 $count++;
504 }
505 $rs->close();
506
507 /// Test record returned are ok
508 $this->assertEqual($count, 3);
509
510 /// Test string keys are returned ok
3ff8bf26 511 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 512
513 reset($data);
514 $count = 0;
515 foreach($rs as $key => $record) {
516 $data_record = current($data);
517 $this->assertEqual($data_record['name'], $key);
518 next($data);
519 $count++;
520 }
521 $rs->close();
522
523 /// Test record returned are ok
524 $this->assertEqual($count, 3);
525
526 /// Test numeric not starting in 1 keys are returned ok
3ff8bf26 527 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 528
529 $data = array_reverse($data);
530 reset($data);
531 $count = 0;
532 foreach($rs as $key => $record) {
533 $data_record = current($data);
534 $this->assertEqual($data_record['id'], $key);
535 next($data);
536 $count++;
537 }
538 $rs->close();
539
540 /// Test record returned are ok
541 $this->assertEqual($count, 3);
542 }
543
0088bd31 544 public function test_get_recordset_list() {
545 $DB = $this->tdb;
546 $dbman = $DB->get_manager();
547
9d833e93 548 $table = $this->get_test_table();
3ff8bf26 549 $tablename = $table->getName();
550
f9ecb171 551 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
552 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 553 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
554 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
555 $dbman->create_table($table);
3ff8bf26 556 $this->tables[$tablename] = $table;
0088bd31 557
3ff8bf26 558 $DB->insert_record($tablename, array('course' => 3));
559 $DB->insert_record($tablename, array('course' => 3));
560 $DB->insert_record($tablename, array('course' => 5));
561 $DB->insert_record($tablename, array('course' => 2));
0088bd31 562
3ff8bf26 563 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 564
565 $this->assertTrue($rs);
566
567 $counter = 0;
568 foreach ($rs as $record) {
569 $counter++;
570 }
571 $this->assertEqual(3, $counter);
c362878e 572 $rs->close();
573
574 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
575
576 $counter = 0;
577 foreach ($rs as $record) {
578 $counter++;
579 }
580 $this->assertEqual(0, $counter);
581 $rs->close();
0088bd31 582 }
583
584 public function test_get_recordset_select() {
585 $DB = $this->tdb;
586 $dbman = $DB->get_manager();
587
9d833e93 588 $table = $this->get_test_table();
3ff8bf26 589 $tablename = $table->getName();
590
f9ecb171 591 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
592 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 593 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
594 $dbman->create_table($table);
3ff8bf26 595 $this->tables[$tablename] = $table;
0088bd31 596
3ff8bf26 597 $DB->insert_record($tablename, array('course' => 3));
598 $DB->insert_record($tablename, array('course' => 3));
599 $DB->insert_record($tablename, array('course' => 5));
600 $DB->insert_record($tablename, array('course' => 2));
0088bd31 601
3ff8bf26 602 $rs = $DB->get_recordset_select($tablename, '');
0088bd31 603 $counter = 0;
604 foreach ($rs as $record) {
605 $counter++;
606 }
515cb653 607 $rs->close();
0088bd31 608 $this->assertEqual(4, $counter);
609
3ff8bf26 610 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 611 $counter = 0;
612 foreach ($rs as $record) {
613 $counter++;
614 }
515cb653 615 $rs->close();
0088bd31 616 $this->assertEqual(2, $counter);
617 }
618
619 public function test_get_recordset_sql() {
620 $DB = $this->tdb;
621 $dbman = $DB->get_manager();
622
9d833e93 623 $table = $this->get_test_table();
3ff8bf26 624 $tablename = $table->getName();
625
f9ecb171 626 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
627 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 628 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
629 $dbman->create_table($table);
3ff8bf26 630 $this->tables[$tablename] = $table;
0088bd31 631
3ff8bf26 632 $DB->insert_record($tablename, array('course' => 3));
633 $DB->insert_record($tablename, array('course' => 3));
634 $DB->insert_record($tablename, array('course' => 5));
635 $DB->insert_record($tablename, array('course' => 2));
0088bd31 636
3ff8bf26 637 $this->assertTrue($rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 638 $counter = 0;
639 foreach ($rs as $record) {
640 $counter++;
641 }
515cb653 642 $rs->close();
0088bd31 643 $this->assertEqual(2, $counter);
644 }
645
646 public function test_get_records() {
647 $DB = $this->tdb;
648 $dbman = $DB->get_manager();
649
9d833e93 650 $table = $this->get_test_table();
3ff8bf26 651 $tablename = $table->getName();
652
f9ecb171 653 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
654 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 655 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
656 $dbman->create_table($table);
3ff8bf26 657 $this->tables[$tablename] = $table;
0088bd31 658
3ff8bf26 659 $DB->insert_record($tablename, array('course' => 3));
660 $DB->insert_record($tablename, array('course' => 3));
661 $DB->insert_record($tablename, array('course' => 5));
662 $DB->insert_record($tablename, array('course' => 2));
0088bd31 663
664 // All records
3ff8bf26 665 $records = $DB->get_records($tablename);
0088bd31 666 $this->assertEqual(4, count($records));
667 $this->assertEqual(3, $records[1]->course);
668 $this->assertEqual(3, $records[2]->course);
669 $this->assertEqual(5, $records[3]->course);
670 $this->assertEqual(2, $records[4]->course);
671
672 // Records matching certain conditions
3ff8bf26 673 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 674 $this->assertEqual(2, count($records));
675 $this->assertEqual(3, $records[1]->course);
676 $this->assertEqual(3, $records[2]->course);
677
678 // All records sorted by course
3ff8bf26 679 $records = $DB->get_records($tablename, null, 'course');
0088bd31 680 $this->assertEqual(4, count($records));
681 $current_record = reset($records);
682 $this->assertEqual(4, $current_record->id);
683 $current_record = next($records);
684 $this->assertEqual(1, $current_record->id);
685 $current_record = next($records);
686 $this->assertEqual(2, $current_record->id);
687 $current_record = next($records);
688 $this->assertEqual(3, $current_record->id);
689
690 // All records, but get only one field
3ff8bf26 691 $records = $DB->get_records($tablename, null, '', 'id');
0088bd31 692 $this->assertTrue(empty($records[1]->course));
693 $this->assertFalse(empty($records[1]->id));
694 $this->assertEqual(4, count($records));
3ff8bf26 695
696 // test limitfrom and limitnum
697 $records = $DB->get_records($tablename, null, '', 'id', 0, 0);
698 $this->assertEqual(4, count($records));
699 $records = $DB->get_records($tablename, null, '', 'id', -1, -1);
700 $this->assertEqual(4, count($records));
701 $records = $DB->get_records($tablename, null, '', 'id', 2, 2);
702 $this->assertEqual(2, count($records));
703 $first = reset($records);
704 $last = array_pop($records);
705 $this->assertEqual(3, $first->id);
706 $this->assertEqual(4, $last->id);
707
708// TODO: more LIMIT tests
0088bd31 709 }
710
711 public function test_get_records_list() {
50a12c87 712 $DB = $this->tdb;
713 $dbman = $DB->get_manager();
714
9d833e93 715 $table = $this->get_test_table();
3ff8bf26 716 $tablename = $table->getName();
717
f9ecb171 718 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
719 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 720 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
721 $dbman->create_table($table);
3ff8bf26 722 $this->tables[$tablename] = $table;
50a12c87 723
3ff8bf26 724 $DB->insert_record($tablename, array('course' => 3));
725 $DB->insert_record($tablename, array('course' => 3));
726 $DB->insert_record($tablename, array('course' => 5));
727 $DB->insert_record($tablename, array('course' => 2));
50a12c87 728
3ff8bf26 729 $this->assertTrue($records = $DB->get_records_list($tablename, 'course', array(3, 2)));
50a12c87 730 $this->assertEqual(3, count($records));
731 $this->assertEqual(1, reset($records)->id);
732 $this->assertEqual(2, next($records)->id);
733 $this->assertEqual(4, next($records)->id);
0088bd31 734
c362878e 735 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
736 $this->assertEqual(0, count($records));
737
0088bd31 738 }
739
740 public function test_get_records_sql() {
16a2a412 741 global $CFG;
50a12c87 742 $DB = $this->tdb;
743 $dbman = $DB->get_manager();
744
9d833e93 745 $table = $this->get_test_table();
3ff8bf26 746 $tablename = $table->getName();
747
f9ecb171 748 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
749 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 750 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
751 $dbman->create_table($table);
3ff8bf26 752 $this->tables[$tablename] = $table;
50a12c87 753
3ff8bf26 754 $DB->insert_record($tablename, array('course' => 3));
755 $DB->insert_record($tablename, array('course' => 3));
756 $DB->insert_record($tablename, array('course' => 5));
757 $DB->insert_record($tablename, array('course' => 2));
50a12c87 758
3ff8bf26 759 $this->assertTrue($records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
50a12c87 760 $this->assertEqual(2, count($records));
761 $this->assertEqual(1, reset($records)->id);
762 $this->assertEqual(2, next($records)->id);
0088bd31 763
16a2a412 764 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
765 $olddebug = $CFG->debug; // Save current debug settings
766 $olddisplay = $CFG->debugdisplay;
767 $CFG->debug = DEBUG_DEVELOPER;
768 $CFG->debugdisplay = true;
758ba89a 769 ob_start(); // hide debug warning
3ff8bf26 770 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {".$tablename."}", null);
758ba89a 771 ob_end_clean();
16a2a412 772 $debuginfo = ob_get_contents();
d83bcdfc 773 $CFG->debug = $olddebug; // Restore original debug settings
774 $CFG->debugdisplay = $olddisplay;
758ba89a 775
776 $this->assertEqual(3, count($records));
777 $this->assertFalse($debuginfo === '');
0088bd31 778 }
779
780 public function test_get_records_menu() {
50a12c87 781 $DB = $this->tdb;
782 $dbman = $DB->get_manager();
783
9d833e93 784 $table = $this->get_test_table();
3ff8bf26 785 $tablename = $table->getName();
786
f9ecb171 787 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
788 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 789 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
790 $dbman->create_table($table);
3ff8bf26 791 $this->tables[$tablename] = $table;
50a12c87 792
3ff8bf26 793 $DB->insert_record($tablename, array('course' => 3));
794 $DB->insert_record($tablename, array('course' => 3));
795 $DB->insert_record($tablename, array('course' => 5));
796 $DB->insert_record($tablename, array('course' => 2));
50a12c87 797
3ff8bf26 798 $this->assertTrue($records = $DB->get_records_menu($tablename, array('course' => 3)));
50a12c87 799 $this->assertEqual(2, count($records));
800 $this->assertFalse(empty($records[1]));
801 $this->assertFalse(empty($records[2]));
802 $this->assertEqual(3, $records[1]);
803 $this->assertEqual(3, $records[2]);
0088bd31 804
805 }
806
807 public function test_get_records_select_menu() {
50a12c87 808 $DB = $this->tdb;
809 $dbman = $DB->get_manager();
810
9d833e93 811 $table = $this->get_test_table();
3ff8bf26 812 $tablename = $table->getName();
813
f9ecb171 814 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
815 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 816 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
817 $dbman->create_table($table);
3ff8bf26 818 $this->tables[$tablename] = $table;
50a12c87 819
3ff8bf26 820 $DB->insert_record($tablename, array('course' => 3));
821 $DB->insert_record($tablename, array('course' => 2));
822 $DB->insert_record($tablename, array('course' => 3));
823 $DB->insert_record($tablename, array('course' => 5));
50a12c87 824
3ff8bf26 825 $this->assertTrue($records = $DB->get_records_select_menu($tablename, "course > ?", array(2)));
50a12c87 826
827 $this->assertEqual(3, count($records));
828 $this->assertFalse(empty($records[1]));
829 $this->assertTrue(empty($records[2]));
830 $this->assertFalse(empty($records[3]));
831 $this->assertFalse(empty($records[4]));
832 $this->assertEqual(3, $records[1]);
833 $this->assertEqual(3, $records[3]);
834 $this->assertEqual(5, $records[4]);
0088bd31 835
836 }
837
838 public function test_get_records_sql_menu() {
50a12c87 839 $DB = $this->tdb;
840 $dbman = $DB->get_manager();
841
9d833e93 842 $table = $this->get_test_table();
3ff8bf26 843 $tablename = $table->getName();
844
f9ecb171 845 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
846 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 847 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
848 $dbman->create_table($table);
3ff8bf26 849 $this->tables[$tablename] = $table;
50a12c87 850
3ff8bf26 851 $DB->insert_record($tablename, array('course' => 3));
852 $DB->insert_record($tablename, array('course' => 2));
853 $DB->insert_record($tablename, array('course' => 3));
854 $DB->insert_record($tablename, array('course' => 5));
50a12c87 855
3ff8bf26 856 $this->assertTrue($records = $DB->get_records_sql_menu("SELECT * FROM {".$tablename."} WHERE course > ?", array(2)));
50a12c87 857
858 $this->assertEqual(3, count($records));
859 $this->assertFalse(empty($records[1]));
860 $this->assertTrue(empty($records[2]));
861 $this->assertFalse(empty($records[3]));
862 $this->assertFalse(empty($records[4]));
863 $this->assertEqual(3, $records[1]);
864 $this->assertEqual(3, $records[3]);
865 $this->assertEqual(5, $records[4]);
0088bd31 866
867 }
868
869 public function test_get_record() {
50a12c87 870 $DB = $this->tdb;
871 $dbman = $DB->get_manager();
872
9d833e93 873 $table = $this->get_test_table();
3ff8bf26 874 $tablename = $table->getName();
875
f9ecb171 876 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
877 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 878 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
879 $dbman->create_table($table);
3ff8bf26 880 $this->tables[$tablename] = $table;
50a12c87 881
3ff8bf26 882 $DB->insert_record($tablename, array('course' => 3));
883 $DB->insert_record($tablename, array('course' => 2));
50a12c87 884
3ff8bf26 885 $this->assertTrue($record = $DB->get_record($tablename, array('id' => 2)));
0088bd31 886
50a12c87 887 $this->assertEqual(2, $record->course);
0088bd31 888 }
889
890 public function test_get_record_select() {
50a12c87 891 $DB = $this->tdb;
892 $dbman = $DB->get_manager();
893
9d833e93 894 $table = $this->get_test_table();
3ff8bf26 895 $tablename = $table->getName();
896
f9ecb171 897 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
898 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 899 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
900 $dbman->create_table($table);
3ff8bf26 901 $this->tables[$tablename] = $table;
50a12c87 902
3ff8bf26 903 $DB->insert_record($tablename, array('course' => 3));
904 $DB->insert_record($tablename, array('course' => 2));
50a12c87 905
3ff8bf26 906 $this->assertTrue($record = $DB->get_record_select($tablename, "id = ?", array(2)));
50a12c87 907
908 $this->assertEqual(2, $record->course);
0088bd31 909
910 }
911
912 public function test_get_record_sql() {
50a12c87 913 $DB = $this->tdb;
914 $dbman = $DB->get_manager();
915
9d833e93 916 $table = $this->get_test_table();
3ff8bf26 917 $tablename = $table->getName();
918
f9ecb171 919 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
920 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 921 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
922 $dbman->create_table($table);
3ff8bf26 923 $this->tables[$tablename] = $table;
50a12c87 924
3ff8bf26 925 $DB->insert_record($tablename, array('course' => 3));
926 $DB->insert_record($tablename, array('course' => 2));
50a12c87 927
3ff8bf26 928 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(2)));
50a12c87 929
930 $this->assertEqual(2, $record->course);
0088bd31 931
e6c6531c 932 // backwards compatibility with $ignoremultiple
933 $this->assertFalse(IGNORE_MISSING);
934 $this->assertTrue(IGNORE_MULTIPLE);
935
af12ea93 936 // record not found
e6c6531c 937 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MISSING));
938 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MULTIPLE));
af12ea93 939 try {
e6c6531c 940 $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), MUST_EXIST);
af12ea93 941 $this->fail("Exception expected");
942 } catch (dml_missing_record_exception $e) {
943 $this->assertTrue(true);
944 }
945
946 // multiple matches
947 ob_start(); // hide debug warning
e6c6531c 948 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MISSING));
af12ea93 949 ob_end_clean();
e6c6531c 950 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MULTIPLE));
af12ea93 951 try {
e6c6531c 952 $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), MUST_EXIST);
af12ea93 953 $this->fail("Exception expected");
954 } catch (dml_multiple_records_exception $e) {
955 $this->assertTrue(true);
956 }
0088bd31 957 }
958
959 public function test_get_field() {
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;
0088bd31 971
3ff8bf26 972 $DB->insert_record($tablename, array('course' => 3));
50a12c87 973
3ff8bf26 974 $this->assertTrue($course = $DB->get_field($tablename, 'course', array('id' => 1)));
50a12c87 975 $this->assertEqual(3, $course);
0088bd31 976 }
977
978 public function test_get_field_select() {
50a12c87 979 $DB = $this->tdb;
980 $dbman = $DB->get_manager();
981
9d833e93 982 $table = $this->get_test_table();
3ff8bf26 983 $tablename = $table->getName();
984
f9ecb171 985 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
986 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 987 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
988 $dbman->create_table($table);
3ff8bf26 989 $this->tables[$tablename] = $table;
50a12c87 990
3ff8bf26 991 $DB->insert_record($tablename, array('course' => 3));
50a12c87 992
3ff8bf26 993 $this->assertTrue($course = $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
50a12c87 994 $this->assertEqual(3, $course);
0088bd31 995
996 }
997
998 public function test_get_field_sql() {
50a12c87 999 $DB = $this->tdb;
1000 $dbman = $DB->get_manager();
1001
9d833e93 1002 $table = $this->get_test_table();
3ff8bf26 1003 $tablename = $table->getName();
1004
f9ecb171 1005 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1006 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1007 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1008 $dbman->create_table($table);
3ff8bf26 1009 $this->tables[$tablename] = $table;
50a12c87 1010
3ff8bf26 1011 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1012
3ff8bf26 1013 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {".$tablename."} WHERE id = ?", array(1)));
50a12c87 1014 $this->assertEqual(3, $course);
0088bd31 1015
1016 }
1017
50a12c87 1018 public function test_get_fieldset_select() {
1019 $DB = $this->tdb;
1020 $dbman = $DB->get_manager();
1021
9d833e93 1022 $table = $this->get_test_table();
3ff8bf26 1023 $tablename = $table->getName();
1024
f9ecb171 1025 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1026 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1027 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1028 $dbman->create_table($table);
3ff8bf26 1029 $this->tables[$tablename] = $table;
50a12c87 1030
3ff8bf26 1031 $DB->insert_record($tablename, array('course' => 1));
1032 $DB->insert_record($tablename, array('course' => 3));
1033 $DB->insert_record($tablename, array('course' => 2));
1034 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1035
3ff8bf26 1036 $this->assertTrue($fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)));
50a12c87 1037
1038 $this->assertEqual(3, count($fieldset));
1039 $this->assertEqual(3, $fieldset[0]);
1040 $this->assertEqual(2, $fieldset[1]);
1041 $this->assertEqual(6, $fieldset[2]);
0088bd31 1042
1043 }
1044
1045 public function test_get_fieldset_sql() {
50a12c87 1046 $DB = $this->tdb;
1047 $dbman = $DB->get_manager();
0088bd31 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' => 1));
1059 $DB->insert_record($tablename, array('course' => 3));
1060 $DB->insert_record($tablename, array('course' => 2));
1061 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1062
3ff8bf26 1063 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {".$tablename."} WHERE course > ?", array(1)));
50a12c87 1064
1065 $this->assertEqual(3, count($fieldset));
1066 $this->assertEqual(2, $fieldset[0]);
1067 $this->assertEqual(3, $fieldset[1]);
1068 $this->assertEqual(4, $fieldset[2]);
0088bd31 1069 }
1070
1071 public function test_insert_record_raw() {
50a12c87 1072 $DB = $this->tdb;
1073 $dbman = $DB->get_manager();
0088bd31 1074
9d833e93 1075 $table = $this->get_test_table();
3ff8bf26 1076 $tablename = $table->getName();
1077
f9ecb171 1078 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1079 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1080 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1081 $dbman->create_table($table);
3ff8bf26 1082 $this->tables[$tablename] = $table;
50a12c87 1083
3ff8bf26 1084 $this->assertTrue($DB->insert_record_raw($tablename, array('course' => 1)));
1085 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 1)));
50a12c87 1086 $this->assertEqual(1, $record->course);
0088bd31 1087 }
1088
1089 public function test_insert_record() {
628dff25 1090
1091 // All the information in this test is fetched from DB by get_recordset() so we
1092 // have such method properly tested against nulls, empties and friends...
1093
1094 global $CFG;
1095
50a12c87 1096 $DB = $this->tdb;
1097 $dbman = $DB->get_manager();
1098
9d833e93 1099 $table = $this->get_test_table();
3ff8bf26 1100 $tablename = $table->getName();
1101
f9ecb171 1102 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1103 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1104 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1105 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1106 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1107 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1108 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1109 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1110 $dbman->create_table($table);
3ff8bf26 1111 $this->tables[$tablename] = $table;
50a12c87 1112
628dff25 1113 $this->assertTrue($DB->insert_record($tablename, array('course' => 1), false)); // Without returning id
1114 $rs = $DB->get_recordset($tablename, array('course' => 1));
1115 $record = $rs->current();
1116 $rs->close();
1117 $this->assertEqual(1, $record->id);
1118 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1119 $this->assertEqual(200, $record->onenum);
1120 $this->assertEqual('onestring', $record->onechar);
1121 $this->assertNull($record->onetext);
1122 $this->assertNull($record->onebinary);
1123
1124 // Check nulls are set properly for all types
1125 $record->oneint = null;
1126 $record->onenum = null;
1127 $record->onechar = null;
1128 $record->onetext = null;
1129 $record->onebinary = null;
1130 $recid = $DB->insert_record($tablename, $record);
1131 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1132 $record = $rs->current();
1133 $rs->close();
1134 $this->assertNull($record->oneint);
1135 $this->assertNull($record->onenum);
1136 $this->assertNull($record->onechar);
1137 $this->assertNull($record->onetext);
1138 $this->assertNull($record->onebinary);
1139
1140 // Check zeros are set properly for all types
1141 $record->oneint = 0;
1142 $record->onenum = 0;
1143 $recid = $DB->insert_record($tablename, $record);
1144 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1145 $record = $rs->current();
1146 $rs->close();
1147 $this->assertEqual(0, $record->oneint);
1148 $this->assertEqual(0, $record->onenum);
1149
1150 // Check booleans are set properly for all types
1151 $record->oneint = true; // trues
1152 $record->onenum = true;
1153 $record->onechar = true;
1154 $record->onetext = true;
1155 $recid = $DB->insert_record($tablename, $record);
1156 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1157 $record = $rs->current();
1158 $rs->close();
1159 $this->assertEqual(1, $record->oneint);
1160 $this->assertEqual(1, $record->onenum);
1161 $this->assertEqual(1, $record->onechar);
1162 $this->assertEqual(1, $record->onetext);
1163
1164 $record->oneint = false; // falses
1165 $record->onenum = false;
1166 $record->onechar = false;
1167 $record->onetext = false;
1168 $recid = $DB->insert_record($tablename, $record);
1169 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1170 $record = $rs->current();
1171 $rs->close();
1172 $this->assertEqual(0, $record->oneint);
1173 $this->assertEqual(0, $record->onenum);
1174 $this->assertEqual(0, $record->onechar);
1175 $this->assertEqual(0, $record->onetext);
1176
1177 // Check string data causes exception in numeric types
1178 $record->oneint = 'onestring';
1179 $record->onenum = 0;
1180 try {
1181 $DB->insert_record($tablename, $record);
1182 $this->fail("Expecting an exception, none occurred");
1183 } catch (exception $e) {
1184 $this->assertTrue($e instanceof dml_exception);
1185 }
1186 $record->oneint = 0;
1187 $record->onenum = 'onestring';
1188 try {
1189 $DB->insert_record($tablename, $record);
1190 $this->fail("Expecting an exception, none occurred");
1191 } catch (exception $e) {
1192 $this->assertTrue($e instanceof dml_exception);
1193 }
1194
1195 // Check empty strings are set properly in string types
1196 $record->oneint = 0;
1197 $record->onenum = 0;
1198 $record->onechar = '';
1199 $record->onetext = '';
1200 $recid = $DB->insert_record($tablename, $record);
1201 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1202 $record = $rs->current();
1203 $rs->close();
1204 $this->assertTrue($record->onechar === '');
1205 $this->assertTrue($record->onetext === '');
1206
1207 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1208 $record->oneint = ((210.10 + 39.92) - 150.02);
1209 $record->onenum = ((210.10 + 39.92) - 150.02);
1210 $recid = $DB->insert_record($tablename, $record);
1211 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1212 $record = $rs->current();
1213 $rs->close();
1214 $this->assertEqual(100, $record->oneint);
1215 $this->assertEqual(100, $record->onenum);
1216
1217 // Check various quotes/backslashes combinations in string types
1218 $teststrings = array(
1219 'backslashes and quotes alone (even): "" \'\' \\\\',
1220 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1221 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1222 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1223 foreach ($teststrings as $teststring) {
1224 $record->onechar = $teststring;
1225 $record->onetext = $teststring;
1226 $recid = $DB->insert_record($tablename, $record);
1227 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1228 $record = $rs->current();
1229 $rs->close();
1230 $this->assertEqual($teststring, $record->onechar);
1231 $this->assertEqual($teststring, $record->onetext);
1232 }
1233
1234 // Check LOBs in text/binary columns
1235 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1236 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1237 $record->onetext = $clob;
1238 $record->onebinary = $blob;
1239 $recid = $DB->insert_record($tablename, $record);
1240 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1241 $record = $rs->current();
1242 $rs->close();
1243 $this->assertEqual($clob, $record->onetext);
1244 $this->assertEqual($blob, $record->onebinary);
0088bd31 1245
628dff25 1246 // And "small" LOBs too, just in case
1247 $newclob = substr($clob, 0, 500);
1248 $newblob = substr($blob, 0, 250);
1249 $record->onetext = $newclob;
1250 $record->onebinary = $newblob;
1251 $recid = $DB->insert_record($tablename, $record);
1252 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1253 $record = $rs->current();
1254 $rs->close();
1255 $this->assertEqual($newclob, $record->onetext);
1256 $this->assertEqual($newblob, $record->onebinary);
0088bd31 1257 }
1258
94898738 1259 public function test_import_record() {
1260 $DB = $this->tdb;
1261 $dbman = $DB->get_manager();
1262
9d833e93 1263 $table = $this->get_test_table();
3ff8bf26 1264 $tablename = $table->getName();
1265
f9ecb171 1266 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1267 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
94898738 1268 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1269 $dbman->create_table($table);
3ff8bf26 1270 $this->tables[$tablename] = $table;
94898738 1271
1272 $record = (object)array('id'=>666, 'course'=>10);
3ff8bf26 1273 $this->assertTrue($DB->import_record($tablename, $record));
1274 $records = $DB->get_records($tablename);
94898738 1275 $this->assertEqual(1, count($records));
1276 $this->assertEqual(10, $records[666]->course);
1277
1278 $record = (object)array('id'=>13, 'course'=>2);
3ff8bf26 1279 $this->assertTrue($DB->import_record($tablename, $record));
1280 $records = $DB->get_records($tablename);
94898738 1281 $this->assertEqual(2, $records[13]->course);
1282 }
1283
c824e1e1 1284 public function test_insert_record_clob() {
1285 global $CFG;
1286
1287 $DB = $this->tdb;
1288 $dbman = $DB->get_manager();
1289
9d833e93 1290 $table = $this->get_test_table();
3ff8bf26 1291 $tablename = $table->getName();
1292
f9ecb171 1293 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1294 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
c824e1e1 1295 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1296 $dbman->create_table($table);
3ff8bf26 1297 $this->tables[$tablename] = $table;
c824e1e1 1298
6ff835b7 1299 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
c824e1e1 1300
3ff8bf26 1301 $id = $DB->insert_record($tablename, array('description' => $clob));
1302 $this->assertEqual($id, 1);
1303 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1304 $this->assertEqual($clob, $record->description);
1305
1306 }
1307
3ff8bf26 1308 public function test_insert_record_multiple_lobs() {
c824e1e1 1309 global $CFG;
1310
1311 $DB = $this->tdb;
1312 $dbman = $DB->get_manager();
1313
9d833e93 1314 $table = $this->get_test_table();
3ff8bf26 1315 $tablename = $table->getName();
1316
f9ecb171 1317 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1318 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
1319 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null);
c824e1e1 1320 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1321 $dbman->create_table($table);
3ff8bf26 1322 $this->tables[$tablename] = $table;
c824e1e1 1323
6ff835b7 1324 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1325 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
c824e1e1 1326
3ff8bf26 1327 $this->assertTrue($id = $DB->insert_record($tablename, array('description' => $clob, 'image' => $blob)));
1328 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1329 $this->assertEqual($clob, $record->description);
1330 $this->assertEqual($blob, $record->image);
3ff8bf26 1331 $this->assertEqual($clob, $DB->get_field($tablename, 'description', array('id' => $id)));
1332 $this->assertEqual($blob, $DB->get_field($tablename, 'image', array('id' => $id)));
66e75f8d 1333
c824e1e1 1334 }
1335
1336
0088bd31 1337 public function test_update_record_raw() {
50a12c87 1338 $DB = $this->tdb;
1339 $dbman = $DB->get_manager();
0088bd31 1340
9d833e93 1341 $table = $this->get_test_table();
3ff8bf26 1342 $tablename = $table->getName();
1343
f9ecb171 1344 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1345 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1346 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1347 $dbman->create_table($table);
3ff8bf26 1348 $this->tables[$tablename] = $table;
50a12c87 1349
3ff8bf26 1350 $DB->insert_record($tablename, array('course' => 1));
1351 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1352 $record->course = 2;
3ff8bf26 1353 $this->assertTrue($DB->update_record_raw($tablename, $record));
1354 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1355 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1356 }
1357
1358 public function test_update_record() {
628dff25 1359
1360 // All the information in this test is fetched from DB by get_record() so we
1361 // have such method properly tested against nulls, empties and friends...
1362
1363 global $CFG;
1364
50a12c87 1365 $DB = $this->tdb;
1366 $dbman = $DB->get_manager();
1367
9d833e93 1368 $table = $this->get_test_table();
3ff8bf26 1369 $tablename = $table->getName();
1370
f9ecb171 1371 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1372 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
628dff25 1373 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1374 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1375 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1376 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1377 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1378 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1379 $dbman->create_table($table);
3ff8bf26 1380 $this->tables[$tablename] = $table;
0088bd31 1381
3ff8bf26 1382 $DB->insert_record($tablename, array('course' => 1));
1383 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1384 $record->course = 2;
628dff25 1385
3ff8bf26 1386 $this->assertTrue($DB->update_record($tablename, $record));
1387 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1388 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
628dff25 1389 $this->assertEqual(100, $record->oneint); // Just check column defaults have been applied
1390 $this->assertEqual(200, $record->onenum);
1391 $this->assertEqual('onestring', $record->onechar);
1392 $this->assertNull($record->onetext);
1393 $this->assertNull($record->onebinary);
c824e1e1 1394
628dff25 1395 // Check nulls are set properly for all types
1396 $record->oneint = null;
1397 $record->onenum = null;
1398 $record->onechar = null;
1399 $record->onetext = null;
1400 $record->onebinary = null;
1401 $DB->update_record($tablename, $record);
1402 $record = $DB->get_record($tablename, array('course' => 2));
1403 $this->assertNull($record->oneint);
1404 $this->assertNull($record->onenum);
1405 $this->assertNull($record->onechar);
1406 $this->assertNull($record->onetext);
1407 $this->assertNull($record->onebinary);
c824e1e1 1408
628dff25 1409 // Check zeros are set properly for all types
1410 $record->oneint = 0;
1411 $record->onenum = 0;
1412 $DB->update_record($tablename, $record);
1413 $record = $DB->get_record($tablename, array('course' => 2));
1414 $this->assertEqual(0, $record->oneint);
1415 $this->assertEqual(0, $record->onenum);
c824e1e1 1416
628dff25 1417 // Check booleans are set properly for all types
1418 $record->oneint = true; // trues
1419 $record->onenum = true;
1420 $record->onechar = true;
1421 $record->onetext = true;
1422 $DB->update_record($tablename, $record);
1423 $record = $DB->get_record($tablename, array('course' => 2));
1424 $this->assertEqual(1, $record->oneint);
1425 $this->assertEqual(1, $record->onenum);
1426 $this->assertEqual(1, $record->onechar);
1427 $this->assertEqual(1, $record->onetext);
1428
1429 $record->oneint = false; // falses
1430 $record->onenum = false;
1431 $record->onechar = false;
1432 $record->onetext = false;
1433 $DB->update_record($tablename, $record);
1434 $record = $DB->get_record($tablename, array('course' => 2));
1435 $this->assertEqual(0, $record->oneint);
1436 $this->assertEqual(0, $record->onenum);
1437 $this->assertEqual(0, $record->onechar);
1438 $this->assertEqual(0, $record->onetext);
c824e1e1 1439
628dff25 1440 // Check string data causes exception in numeric types
1441 $record->oneint = 'onestring';
1442 $record->onenum = 0;
1443 try {
1444 $DB->update_record($tablename, $record);
1445 $this->fail("Expecting an exception, none occurred");
1446 } catch (exception $e) {
1447 $this->assertTrue($e instanceof dml_exception);
1448 }
1449 $record->oneint = 0;
1450 $record->onenum = 'onestring';
1451 try {
1452 $DB->update_record($tablename, $record);
1453 $this->fail("Expecting an exception, none occurred");
1454 } catch (exception $e) {
1455 $this->assertTrue($e instanceof dml_exception);
1456 }
c824e1e1 1457
628dff25 1458 // Check empty strings are set properly in string types
1459 $record->oneint = 0;
1460 $record->onenum = 0;
1461 $record->onechar = '';
1462 $record->onetext = '';
1463 $DB->update_record($tablename, $record);
1464 $record = $DB->get_record($tablename, array('course' => 2));
1465 $this->assertTrue($record->onechar === '');
1466 $this->assertTrue($record->onetext === '');
c824e1e1 1467
628dff25 1468 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1469 $record->oneint = ((210.10 + 39.92) - 150.02);
1470 $record->onenum = ((210.10 + 39.92) - 150.02);
1471 $DB->update_record($tablename, $record);
1472 $record = $DB->get_record($tablename, array('course' => 2));
1473 $this->assertEqual(100, $record->oneint);
1474 $this->assertEqual(100, $record->onenum);
3ff8bf26 1475
628dff25 1476 // Check various quotes/backslashes combinations in string types
1477 $teststrings = array(
1478 'backslashes and quotes alone (even): "" \'\' \\\\',
1479 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1480 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1481 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1482 foreach ($teststrings as $teststring) {
1483 $record->onechar = $teststring;
1484 $record->onetext = $teststring;
1485 $DB->update_record($tablename, $record);
1486 $record = $DB->get_record($tablename, array('course' => 2));
1487 $this->assertEqual($teststring, $record->onechar);
1488 $this->assertEqual($teststring, $record->onetext);
1489 }
c824e1e1 1490
628dff25 1491 // Check LOBs in text/binary columns
6ff835b7 1492 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1493 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
628dff25 1494 $record->onetext = $clob;
1495 $record->onebinary = $blob;
1496 $DB->update_record($tablename, $record);
1497 $record = $DB->get_record($tablename, array('course' => 2));
1498 $this->assertEqual($clob, $record->onetext);
1499 $this->assertEqual($blob, $record->onebinary);
1500
1501 // And "small" LOBs too, just in case
d246cdd2 1502 $newclob = substr($clob, 0, 500);
1503 $newblob = substr($blob, 0, 250);
628dff25 1504 $record->onetext = $newclob;
1505 $record->onebinary = $newblob;
1506 $DB->update_record($tablename, $record);
1507 $record = $DB->get_record($tablename, array('course' => 2));
1508 $this->assertEqual($newclob, $record->onetext);
1509 $this->assertEqual($newblob, $record->onebinary);
c824e1e1 1510 }
1511
0088bd31 1512 public function test_set_field() {
50a12c87 1513 $DB = $this->tdb;
1514 $dbman = $DB->get_manager();
1515
9d833e93 1516 $table = $this->get_test_table();
3ff8bf26 1517 $tablename = $table->getName();
1518
f9ecb171 1519 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1520 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1521 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1522 $dbman->create_table($table);
3ff8bf26 1523 $this->tables[$tablename] = $table;
0088bd31 1524
3ff8bf26 1525 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1526
3ff8bf26 1527 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1528 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
fbabe35a 1529
1530 // Note: All the nulls, booleans, empties, quoted and backslashes tests
1531 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 1532 }
1533
1534 public function test_set_field_select() {
628dff25 1535
1536 // All the information in this test is fetched from DB by get_field() so we
1537 // have such method properly tested against nulls, empties and friends...
1538
fbabe35a 1539 global $CFG;
1540
50a12c87 1541 $DB = $this->tdb;
1542 $dbman = $DB->get_manager();
1543
9d833e93 1544 $table = $this->get_test_table();
3ff8bf26 1545 $tablename = $table->getName();
1546
f9ecb171 1547 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1548 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 1549 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
1550 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
1551 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
1552 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1553 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1554 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1555 $dbman->create_table($table);
3ff8bf26 1556 $this->tables[$tablename] = $table;
50a12c87 1557
3ff8bf26 1558 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1559
3ff8bf26 1560 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1561 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1562
fbabe35a 1563 // Check nulls are set properly for all types
628dff25 1564 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
1565 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
1566 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
1567 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
1568 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
fbabe35a 1569 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
1570 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
1571 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
1572 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
1573 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
1574
1575 // Check zeros are set properly for all types
1576 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
1577 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
1578 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1579 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1580
1581 // Check booleans are set properly for all types
1582 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
1583 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
1584 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
1585 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
1586 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1587 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1588 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1589 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1590
1591 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
1592 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
1593 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
1594 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
1595 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1596 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1597 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1598 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1599
1600 // Check string data causes exception in numeric types
1601 try {
1602 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
1603 $this->fail("Expecting an exception, none occurred");
1604 } catch (exception $e) {
1605 $this->assertTrue($e instanceof dml_exception);
1606 }
1607 try {
1608 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
1609 $this->fail("Expecting an exception, none occurred");
1610 } catch (exception $e) {
1611 $this->assertTrue($e instanceof dml_exception);
1612 }
1613
1614 // Check empty strings are set properly in string types
1615 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
1616 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
1617 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
1618 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
1619
1620 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1621 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1622 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1623 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1624 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1625
1626 // Check various quotes/backslashes combinations in string types
1627 $teststrings = array(
628dff25 1628 'backslashes and quotes alone (even): "" \'\' \\\\',
1629 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1630 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1631 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
fbabe35a 1632 foreach ($teststrings as $teststring) {
1633 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
1634 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
1635 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1636 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1637 }
1638
1639 // Check LOBs in text/binary columns
1640 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1641 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1642 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
1643 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
1644 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1645 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
1646
1647 // And "small" LOBs too, just in case
1648 $newclob = substr($clob, 0, 500);
1649 $newblob = substr($blob, 0, 250);
1650 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
1651 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
1652 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1653 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
0088bd31 1654 }
1655
1656 public function test_count_records() {
1657 $DB = $this->tdb;
1658
1659 $dbman = $DB->get_manager();
1660
9d833e93 1661 $table = $this->get_test_table();
3ff8bf26 1662 $tablename = $table->getName();
1663
f9ecb171 1664 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1665 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1666 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1667 $dbman->create_table($table);
3ff8bf26 1668 $this->tables[$tablename] = $table;
0088bd31 1669
3ff8bf26 1670 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1671
3ff8bf26 1672 $DB->insert_record($tablename, array('course' => 3));
1673 $DB->insert_record($tablename, array('course' => 4));
1674 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1675
3ff8bf26 1676 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1677 }
1678
1679 public function test_count_records_select() {
50a12c87 1680 $DB = $this->tdb;
0088bd31 1681
50a12c87 1682 $dbman = $DB->get_manager();
1683
9d833e93 1684 $table = $this->get_test_table();
3ff8bf26 1685 $tablename = $table->getName();
1686
f9ecb171 1687 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1688 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1689 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1690 $dbman->create_table($table);
3ff8bf26 1691 $this->tables[$tablename] = $table;
50a12c87 1692
3ff8bf26 1693 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1694
3ff8bf26 1695 $DB->insert_record($tablename, array('course' => 3));
1696 $DB->insert_record($tablename, array('course' => 4));
1697 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1698
3ff8bf26 1699 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1700 }
1701
1702 public function test_count_records_sql() {
50a12c87 1703 $DB = $this->tdb;
1704 $dbman = $DB->get_manager();
1705
9d833e93 1706 $table = $this->get_test_table();
3ff8bf26 1707 $tablename = $table->getName();
1708
f9ecb171 1709 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1710 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1711 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1712 $dbman->create_table($table);
3ff8bf26 1713 $this->tables[$tablename] = $table;
50a12c87 1714
3ff8bf26 1715 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1716
3ff8bf26 1717 $DB->insert_record($tablename, array('course' => 3));
1718 $DB->insert_record($tablename, array('course' => 4));
1719 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1720
3ff8bf26 1721 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1722 }
1723
1724 public function test_record_exists() {
50a12c87 1725 $DB = $this->tdb;
1726 $dbman = $DB->get_manager();
1727
9d833e93 1728 $table = $this->get_test_table();
3ff8bf26 1729 $tablename = $table->getName();
1730
f9ecb171 1731 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1732 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1733 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1734 $dbman->create_table($table);
3ff8bf26 1735 $this->tables[$tablename] = $table;
50a12c87 1736
3ff8bf26 1737 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1738
3ff8bf26 1739 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1740 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1741
3ff8bf26 1742 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1743
1744 }
1745
1746 public function test_record_exists_select() {
50a12c87 1747 $DB = $this->tdb;
1748 $dbman = $DB->get_manager();
0088bd31 1749
9d833e93 1750 $table = $this->get_test_table();
3ff8bf26 1751 $tablename = $table->getName();
1752
f9ecb171 1753 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1754 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1755 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1756 $dbman->create_table($table);
3ff8bf26 1757 $this->tables[$tablename] = $table;
50a12c87 1758
3ff8bf26 1759 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1760
3ff8bf26 1761 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1762 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1763
3ff8bf26 1764 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1765 }
1766
1767 public function test_record_exists_sql() {
50a12c87 1768 $DB = $this->tdb;
1769 $dbman = $DB->get_manager();
0088bd31 1770
9d833e93 1771 $table = $this->get_test_table();
3ff8bf26 1772 $tablename = $table->getName();
1773
f9ecb171 1774 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1775 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1776 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1777 $dbman->create_table($table);
3ff8bf26 1778 $this->tables[$tablename] = $table;
50a12c87 1779
3ff8bf26 1780 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1781
3ff8bf26 1782 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1783 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1784
3ff8bf26 1785 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1786 }
1787
1788 public function test_delete_records() {
50a12c87 1789 $DB = $this->tdb;
1790 $dbman = $DB->get_manager();
0088bd31 1791
9d833e93 1792 $table = $this->get_test_table();
3ff8bf26 1793 $tablename = $table->getName();
1794
f9ecb171 1795 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1796 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1797 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1798 $dbman->create_table($table);
3ff8bf26 1799 $this->tables[$tablename] = $table;
50a12c87 1800
3ff8bf26 1801 $DB->insert_record($tablename, array('course' => 3));
1802 $DB->insert_record($tablename, array('course' => 2));
1803 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1804
1805 // Delete all records
3ff8bf26 1806 $this->assertTrue($DB->delete_records($tablename));
1807 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1808
1809 // Delete subset of records
3ff8bf26 1810 $DB->insert_record($tablename, array('course' => 3));
1811 $DB->insert_record($tablename, array('course' => 2));
1812 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1813
3ff8bf26 1814 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1815 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1816 }
1817
1818 public function test_delete_records_select() {
50a12c87 1819 $DB = $this->tdb;
1820 $dbman = $DB->get_manager();
0088bd31 1821
9d833e93 1822 $table = $this->get_test_table();
3ff8bf26 1823 $tablename = $table->getName();
1824
f9ecb171 1825 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1826 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1827 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1828 $dbman->create_table($table);
3ff8bf26 1829 $this->tables[$tablename] = $table;
0088bd31 1830
3ff8bf26 1831 $DB->insert_record($tablename, array('course' => 3));
1832 $DB->insert_record($tablename, array('course' => 2));
1833 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1834
3ff8bf26 1835 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1836 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1837 }
1d861fce 1838
c362878e 1839 public function test_delete_records_list() {
1840 $DB = $this->tdb;
1841 $dbman = $DB->get_manager();
1842
1843 $table = $this->get_test_table();
1844 $tablename = $table->getName();
1845
f9ecb171 1846 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1847 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 1848 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1849 $dbman->create_table($table);
1850 $this->tables[$tablename] = $table;
1851
1852 $DB->insert_record($tablename, array('course' => 1));
1853 $DB->insert_record($tablename, array('course' => 2));
1854 $DB->insert_record($tablename, array('course' => 3));
1855
1856 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1857 $this->assertEqual(1, $DB->count_records($tablename));
1858
1859 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1860 $this->assertEqual(1, $DB->count_records($tablename));
1861 }
1862
082ae821 1863 function test_sql_null_from_clause() {
655bbf51 1864 $DB = $this->tdb;
082ae821 1865 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1866 $this->assertEqual($DB->get_field_sql($sql), 1);
1867 }
655bbf51 1868
082ae821 1869 function test_sql_bitand() {
1870 $DB = $this->tdb;
9d833e93 1871 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1872 $this->assertEqual($DB->get_field_sql($sql), 2);
1873 }
655bbf51 1874
082ae821 1875 function test_sql_bitnot() {
1876 $DB = $this->tdb;
655bbf51 1877
082ae821 1878 $not = $DB->sql_bitnot(2);
1879 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1880
9d833e93 1881 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1882 $this->assertEqual($DB->get_field_sql($sql), 5);
1883 }
655bbf51 1884
082ae821 1885 function test_sql_bitor() {
1886 $DB = $this->tdb;
9d833e93 1887 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1888 $this->assertEqual($DB->get_field_sql($sql), 11);
1889 }
655bbf51 1890
082ae821 1891 function test_sql_bitxor() {
1892 $DB = $this->tdb;
9d833e93 1893 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1894 $this->assertEqual($DB->get_field_sql($sql), 9);
1895 }
1896
e6df3734 1897 function test_sql_modulo() {
1898 $DB = $this->tdb;
9d833e93 1899 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1900 $this->assertEqual($DB->get_field_sql($sql), 3);
1901 }
1902
082ae821 1903 function test_sql_ceil() {
1904 $DB = $this->tdb;
9d833e93 1905 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1906 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1907 }
1908
6ff835b7 1909 function test_cast_char2int() {
1910 $DB = $this->tdb;
1911 $dbman = $DB->get_manager();
1912
9d833e93 1913 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1914 $tablename1 = $table1->getName();
1915
f9ecb171 1916 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1917 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1918 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1919 $dbman->create_table($table1);
3ff8bf26 1920 $this->tables[$tablename1] = $table1;
6ff835b7 1921
3ff8bf26 1922 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1923
9d833e93 1924 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1925 $tablename2 = $table2->getName();
f9ecb171 1926 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1927 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 1928 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1929 $dbman->create_table($table2);
1930 $this->tables[$table2->getName()] = $table2;
1931
9d833e93 1932 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 1933
1934 try {
9d833e93 1935 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 1936 $records = $DB->get_records_sql($sql);
1937 $this->assertEqual(count($records), 1);
1938 } catch (dml_exception $e) {
1939 $this->fail("No exception expected");
1940 }
1941 }
1942
1943 function test_cast_char2real() {
1944 $DB = $this->tdb;
1945 $dbman = $DB->get_manager();
1946
9d833e93 1947 $table = $this->get_test_table();
3ff8bf26 1948 $tablename = $table->getName();
1949
f9ecb171 1950 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1951 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1952 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 1953 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1954 $dbman->create_table($table);
3ff8bf26 1955 $this->tables[$tablename] = $table;
6ff835b7 1956
9d833e93 1957 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
1958 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
1959 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 1960
9d833e93 1961 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 1962 $records = $DB->get_records_sql($sql);
1963 $this->assertEqual(count($records), 2);
1964 }
1965
082ae821 1966 function sql_compare_text() {
1967 $DB = $this->tdb;
1968 $dbman = $DB->get_manager();
1969
9d833e93 1970 $table = $this->get_test_table();
3ff8bf26 1971 $tablename = $table->getName();
1972
f9ecb171 1973 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1974 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1975 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1976 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1977 $dbman->create_table($table);
3ff8bf26 1978 $this->tables[$tablename] = $table;
082ae821 1979
3ff8bf26 1980 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
1981 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
1982 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 1983
3ff8bf26 1984 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 1985 $records = $DB->get_records_sql($sql);
1986 $this->assertEqual(count($records), 1);
1987
3ff8bf26 1988 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 1989 $records = $DB->get_records_sql($sql);
1990 $this->assertEqual(count($records), 2);
1991 }
1992
6ff835b7 1993 function test_ilike() {
1994 $DB = $this->tdb;
1995 $dbman = $DB->get_manager();
1996
9d833e93 1997 $table = $this->get_test_table();
3ff8bf26 1998 $tablename = $table->getName();
1999
f9ecb171 2000 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2001 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2002 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2003 $dbman->create_table($table);
3ff8bf26 2004 $this->tables[$tablename] = $table;
6ff835b7 2005
3ff8bf26 2006 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
2007 $DB->insert_record($tablename, array('name'=>'NoDupor'));
2008 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 2009
3ff8bf26 2010 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 2011 $params = array("%dup_r%");
2012 $records = $DB->get_records_sql($sql, $params);
2013 $this->assertEqual(count($records), 2);
2014 }
2015
71a71d59 2016 function test_sql_concat() {
6ff835b7 2017 $DB = $this->tdb;
71a71d59 2018 $dbman = $DB->get_manager();
2019
2020 /// Testing all sort of values
2021 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
2022 // string, some unicode chars
2023 $params = array('name', 'áéíóú', 'name3');
2024 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
2025 // string, spaces and numbers
2026 $params = array('name', ' ', 12345);
2027 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
2028 // float, empty and strings
2029 $params = array(123.45, '', 'test');
2030 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
2031 // float, null and strings
2032 $params = array(123.45, null, 'test');
52a01626 2033 $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 2034
2035 /// Testing fieldnames + values
2036 $table = $this->get_test_table();
2037 $tablename = $table->getName();
2038
2039 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2040 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
2041 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2042 $dbman->create_table($table);
2043 $this->tables[$tablename] = $table;
2044
2045 $DB->insert_record($tablename, array('description'=>'áéíóú'));
2046 $DB->insert_record($tablename, array('description'=>'dxxx'));
2047 $DB->insert_record($tablename, array('description'=>'bcde'));
2048
2049 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
2050 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
2051 $this->assertEqual(count($records), 3);
2052 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 2053 }
2054
082ae821 2055 function test_concat_join() {
6ff835b7 2056 $DB = $this->tdb;
082ae821 2057 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
2058 $params = array("name", "name2", "name3");
2059 $result = $DB->get_field_sql($sql, $params);
2060 $this->assertEqual("name name2 name3", $result);
2061 }
2062
2063 function test_sql_fullname() {
2064 $DB = $this->tdb;
2065 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
2066 $params = array('first'=>'Firstname', 'last'=>'Surname');
2067 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
2068 }
2069
2070 function sql_sql_order_by_text() {
2071 $DB = $this->tdb;
2072 $dbman = $DB->get_manager();
2073
9d833e93 2074 $table = $this->get_test_table();
3ff8bf26 2075 $tablename = $table->getName();
2076
f9ecb171 2077 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2078 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2079 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2080 $dbman->create_table($table);
3ff8bf26 2081 $this->tables[$tablename] = $table;
082ae821 2082
3ff8bf26 2083 $DB->insert_record($tablename, array('description'=>'abcd'));
2084 $DB->insert_record($tablename, array('description'=>'dxxx'));
2085 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 2086
3ff8bf26 2087 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 2088 $records = $DB->get_records_sql($sql);
2089 $first = array_unshift($records);
2090 $this->assertEqual(1, $first->id);
2091 $second = array_unshift($records);
2092 $this->assertEqual(3, $second->id);
2093 $last = array_unshift($records);
2094 $this->assertEqual(2, $last->id);
2095 }
2096
2097 function test_sql_substring() {
2098 $DB = $this->tdb;
2099 $dbman = $DB->get_manager();
2100
9d833e93 2101 $table = $this->get_test_table();
3ff8bf26 2102 $tablename = $table->getName();
2103
f9ecb171 2104 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2105 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 2106 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2107 $dbman->create_table($table);
3ff8bf26 2108 $this->tables[$tablename] = $table;
082ae821 2109
2110 $string = 'abcdefghij';
2111
3ff8bf26 2112 $DB->insert_record($tablename, array('name'=>$string));
082ae821 2113
3ff8bf26 2114 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 2115 $record = $DB->get_record_sql($sql);
2116 $this->assertEqual(substr($string, 5-1), $record->name);
2117
3ff8bf26 2118 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 2119 $record = $DB->get_record_sql($sql);
2120 $this->assertEqual(substr($string, 5-1, 2), $record->name);
2121
2122 try {
2123 // silence php warning ;-)
2124 @$DB->sql_substr("name");
2125 $this->fail("Expecting an exception, none occurred");
2126 } catch (Exception $e) {
2127 $this->assertTrue($e instanceof coding_exception);
2128 }
6ff835b7 2129 }
2130
37d975e1 2131 function test_sql_length() {
2132 $DB = $this->tdb;
2133 $this->assertEqual($DB->get_field_sql(
2134 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
2135 $this->assertEqual($DB->get_field_sql(
2136 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
2137 }
2138
1d861fce 2139 function test_sql_position() {
2140 $DB = $this->tdb;
2141 $this->assertEqual($DB->get_field_sql(
6ff835b7 2142 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 2143 $this->assertEqual($DB->get_field_sql(
6ff835b7 2144 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
2145 }
2146
082ae821 2147 function test_sql_empty() {
2148 $DB = $this->tdb;
2149 $dbman = $DB->get_manager();
2150
9d833e93 2151 $table = $this->get_test_table();
3ff8bf26 2152 $tablename = $table->getName();
2153
f9ecb171 2154 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2155 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 2156 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
2157 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 2158 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2159 $dbman->create_table($table);
3ff8bf26 2160 $this->tables[$tablename] = $table;
082ae821 2161
cf874b29 2162 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 2163 $DB->insert_record($tablename, array('name'=>null));
cf874b29 2164 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 2165 $DB->insert_record($tablename, array('name'=>0));
082ae821 2166
3ff8bf26 2167 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 2168 $this->assertEqual(count($records), 1);
2169 $record = reset($records);
2170 $this->assertEqual($record->name, '');
cf874b29 2171
2172 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
2173 $this->assertEqual(count($records), 1);
2174 $record = reset($records);
2175 $this->assertEqual($record->namenotnull, '');
2176
2177 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
2178 $this->assertEqual(count($records), 4);
2179 $record = reset($records);
2180 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 2181 }
2182
2183 function test_sql_isempty() {
2184 $DB = $this->tdb;
2185 $dbman = $DB->get_manager();
2186
9d833e93 2187 $table = $this->get_test_table();
3ff8bf26 2188 $tablename = $table->getName();
2189
f9ecb171 2190 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2191 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
2192 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
2193 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
2194 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 2195 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2196 $dbman->create_table($table);
3ff8bf26 2197 $this->tables[$tablename] = $table;
082ae821 2198
3ff8bf26 2199 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
2200 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
2201 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
2202 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 2203
3ff8bf26 2204 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 2205 $this->assertEqual(count($records), 1);
2206 $record = reset($records);
2207 $this->assertEqual($record->name, '');
2208
3ff8bf26 2209 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 2210 $this->assertEqual(count($records), 1);
2211 $record = reset($records);
2212 $this->assertEqual($record->namenull, '');
2213
3ff8bf26 2214 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 2215 $this->assertEqual(count($records), 1);
2216 $record = reset($records);
2217 $this->assertEqual($record->description, '');
2218
3ff8bf26 2219 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 2220 $this->assertEqual(count($records), 1);
2221 $record = reset($records);
2222 $this->assertEqual($record->descriptionnull, '');
2223 }
2224
2225 function sql_isnotempty() {
2226 //TODO
2227 }
2228
2229 function test_sql_regex() {
6ff835b7 2230 $DB = $this->tdb;
2231 $dbman = $DB->get_manager();
2232
9d833e93 2233 $table = $this->get_test_table();
3ff8bf26 2234 $tablename = $table->getName();
2235
f9ecb171 2236 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2237 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2238 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2239 $dbman->create_table($table);
3ff8bf26 2240 $this->tables[$tablename] = $table;
6ff835b7 2241
3ff8bf26 2242 $DB->insert_record($tablename, array('name'=>'lalala'));
2243 $DB->insert_record($tablename, array('name'=>'holaaa'));
2244 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2245
3ff8bf26 2246 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2247 $params = array('a$');
dd1611a5 2248 if ($DB->sql_regex_supported()) {
2249 $records = $DB->get_records_sql($sql, $params);
2250 $this->assertEqual(count($records), 2);
2251 } else {
2252 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2253 }
6ff835b7 2254
3ff8bf26 2255 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2256 $params = array('.a');
dd1611a5 2257 if ($DB->sql_regex_supported()) {
2258 $records = $DB->get_records_sql($sql, $params);
2259 $this->assertEqual(count($records), 1);
2260 } else {
2261 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2262 }
6ff835b7 2263
1d861fce 2264 }
fb76304b 2265
2266 function test_begin_sql() {
2267 $DB = $this->tdb;
2268 $dbman = $DB->get_manager();
2269
9d833e93 2270 $table = $this->get_test_table();
3ff8bf26 2271 $tablename = $table->getName();
2272
f9ecb171 2273 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2274 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2275 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2276 $dbman->create_table($table);
3ff8bf26 2277 $this->tables[$tablename] = $table;
fb76304b 2278
2279 $active = $DB->begin_sql();
2280 if ($active) {
2281 // test only if driver supports transactions
2282 $data = (object)array('course'=>3);
3ff8bf26 2283 $DB->insert_record($tablename, $data);
2284 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2285 $DB->commit_sql();
167cbee8 2286 } else {
2287 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2288 }
2289 }
2290
2291 function test_commit_sql() {
2292 $DB = $this->tdb;
2293 $dbman = $DB->get_manager();
2294
9d833e93 2295 $table = $this->get_test_table();
3ff8bf26 2296 $tablename = $table->getName();
2297
f9ecb171 2298 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2299 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2300 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2301 $dbman->create_table($table);
3ff8bf26 2302 $this->tables[$tablename] = $table;
fb76304b 2303
2304 $active = $DB->begin_sql();
2305 if ($active) {
2306 // test only if driver supports transactions
2307 $data = (object)array('course'=>3);
3ff8bf26 2308 $DB->insert_record($tablename, $data);
fb76304b 2309 $DB->commit_sql();
3ff8bf26 2310 $this->assertEqual(1, $DB->count_records($tablename));
167cbee8 2311 } else {
2312 $this->assertTrue(true, 'BD Transactions not supported. Test skipped');
fb76304b 2313 }
2314 }
2315
2316 function test_rollback_sql() {
2317 $DB = $this->tdb;
2318 $dbman = $DB->get_manager();
2319
9d833e93 2320 $table = $this->get_test_table();
3ff8bf26 2321 $tablename = $table->getName();
2322
f9ecb171 2323 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2324 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2325 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2326 $dbman->create_table($table);
3ff8bf26 2327 $this->tables[$tablename] = $table;
fb76304b 2328
2329 $active = $DB->begin_sql();
2330 if ($active) {
2331 // test only if driver supports transactions
2332 $data = (object)array('course'=>3);
3ff8bf26 2333 $DB->insert_record($tablename, $data);
fb76304b 2334 $DB->rollback_sql();
3ff8bf26 2335 $this->assertEqual(0, $DB->count_records($tablename));
167cbee8 2336 } else {
81f5be12 2337 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2338 }
2339 }
7f9f11b5 2340}
2341
2342/**
2343 * This class is not a proper subclass of moodle_database. It is
2344 * intended to be used only in unit tests, in order to gain access to the
2345 * protected methods of moodle_database, and unit test them.
2346 */
2347class moodle_database_for_testing extends moodle_database {
2348 protected $prefix = 'mdl_';
2349
2350 public function public_fix_table_names($sql) {
2351 return $this->fix_table_names($sql);
2352 }
2353
2354 public function driver_installed(){}
2355 public function get_dbfamily(){}
2356 protected function get_dbtype(){}
16a5642c 2357 protected function get_dblibrary(){}
7f9f11b5 2358 public function get_name(){}
37d975e1 2359 public function get_configuration_help(){}
7f9f11b5 2360 public function get_configuration_hints(){}
beaa43db 2361 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2362 public function get_server_info(){}
2363 protected function allowed_param_types(){}
2364 public function get_last_error(){}
117679db 2365 public function get_tables($usecache=true){}
7f9f11b5 2366 public function get_indexes($table){}
2367 public function get_columns($table, $usecache=true){}
2368 public function set_debug($state){}
2369 public function get_debug(){}
2370 public function set_logging($state){}
2371 public function change_database_structure($sql){}
2372 public function execute($sql, array $params=null){}
2373 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2374 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2375 public function get_fieldset_sql($sql, array $params=null){}
94898738 2376 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 2377 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 2378 public function import_record($table, $dataobject){}
7f9f11b5 2379 public function update_record_raw($table, $params, $bulk=false){}
2380 public function update_record($table, $dataobject, $bulk=false){}
2381 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2382 public function delete_records_select($table, $select, array $params=null){}
2383 public function sql_concat(){}
2384 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2385 public function sql_substr($expr, $start, $length=false){}
bb78c788 2386}