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