Updated the HEAD build version to 20090830
[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="") {
9d833e93 50 $dbman = $this->tdb->get_manager();
51
66e75f8d 52 if ($tablename === "") {
53 if (defined('OCI_HACKERY')) {
54 static $i = 0;
55 $tablename = "unit_table".$i++;
56 } else {
57 $tablename = "unit_table";
58 }
59 }
60
1d861fce 61 $table = new xmldb_table($tablename);
62 if ($dbman->table_exists($table)) {
63 $dbman->drop_table($table);
64 }
3ff8bf26 65 return new xmldb_table($tablename);
1d861fce 66 }
67
73f7ad71 68 function test_fix_sql_params() {
7f9f11b5 69 $DB = $this->tdb;
b579f0db 70
9d833e93 71 $table = $this->get_test_table();
72 $tablename = $table->getName();
3ff8bf26 73
334ce026 74 // Correct table placeholder substitution
3ff8bf26 75 $sql = "SELECT * FROM {".$tablename."}";
334ce026 76 $sqlarray = $DB->fix_sql_params($sql);
66e75f8d 77 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
334ce026 78
79 // Conversions of all param types
80 $sql = array();
3ff8bf26 81 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
334ce026 82 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
83 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
84
85 $params = array();
3ff8bf26 86 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
334ce026 87 $params[SQL_PARAMS_QM] = array('first record', 1);
88 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
89
90 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
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_QM], $params[SQL_PARAMS_QM]);
95 $this->assertEqual($rsql, $sql[$rtype]);
96 $this->assertEqual($rparams, $params[$rtype]);
97
98 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
99 $this->assertEqual($rsql, $sql[$rtype]);
100 $this->assertEqual($rparams, $params[$rtype]);
101
102
73f7ad71 103 // Malformed table placeholder
104 $sql = "SELECT * FROM [testtable]";
e6b4f00e 105 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 106 $this->assertEqual($sql, $sqlarray[0]);
107
73f7ad71 108
73f7ad71 109 // Mixed param types (colon and dollar)
3ff8bf26 110 $sql = "SELECT * FROM {".$tablename."} WHERE name = :param1, course = \$1";
b579f0db 111 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 112 try {
e6b4f00e 113 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 114 $this->fail("Expecting an exception, none occurred");
73f7ad71 115 } catch (Exception $e) {
251387d0 116 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 117 }
118
119 // Mixed param types (question and dollar)
3ff8bf26 120 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = \$1";
b579f0db 121 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 122 try {
e6b4f00e 123 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 124 $this->fail("Expecting an exception, none occurred");
73f7ad71 125 } catch (Exception $e) {
251387d0 126 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 127 }
73f7ad71 128
129 // Too many params in sql
3ff8bf26 130 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = ?, id = ?";
b579f0db 131 $params = array('record2', 3);
73f7ad71 132 try {
e6b4f00e 133 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 134 $this->fail("Expecting an exception, none occurred");
73f7ad71 135 } catch (Exception $e) {
251387d0 136 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 137 }
73f7ad71 138
139 // Too many params in array: no error
140 $params[] = 1;
141 $params[] = time();
73f7ad71 142 $sqlarray = null;
143
144 try {
e6b4f00e 145 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 146 $this->pass();
73f7ad71 147 } catch (Exception $e) {
6ff835b7 148 $this->fail("Unexpected ".get_class($e)." exception");
73f7ad71 149 }
73f7ad71 150 $this->assertTrue($sqlarray[0]);
151
152 // Named params missing from array
3ff8bf26 153 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :course";
b579f0db 154 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 155 try {
e6b4f00e 156 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 157 $this->fail("Expecting an exception, none occurred");
73f7ad71 158 } catch (Exception $e) {
251387d0 159 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 160 }
73f7ad71 161
162 // Duplicate named param in query
3ff8bf26 163 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :name";
b579f0db 164 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 165 try {
e6b4f00e 166 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 167 $this->fail("Expecting an exception, none occurred");
73f7ad71 168 } catch (Exception $e) {
251387d0 169 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 170 }
73f7ad71 171
73f7ad71 172 }
173
bb78c788 174 public function testGetTables() {
7f9f11b5 175 $DB = $this->tdb;
251387d0 176 $dbman = $this->tdb->get_manager();
a230012c 177
1d861fce 178 // Need to test with multiple DBs
9d833e93 179 $table = $this->get_test_table();
3ff8bf26 180 $tablename = $table->getName();
1d861fce 181
b579f0db 182 $original_count = count($DB->get_tables());
183
f9ecb171 184 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
b579f0db 185 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
186 $dbman->create_table($table);
3ff8bf26 187 $this->tables[$tablename] = $table;
767172e4 188
b579f0db 189 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 190 }
191
767172e4 192 public function testDefaults() {
193 $DB = $this->tdb;
194 $dbman = $this->tdb->get_manager();
195
9d833e93 196 $table = $this->get_test_table();
3ff8bf26 197 $tablename = $table->getName();
198
f9ecb171 199 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
200 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
767172e4 201 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
202 $dbman->create_table($table);
3ff8bf26 203 $this->tables[$tablename] = $table;
767172e4 204
3ff8bf26 205 $columns = $DB->get_columns($tablename);
767172e4 206
207 $enumfield = $columns['enumfield'];
208 $this->assertEqual('test2', $enumfield->default_value);
209 $this->assertEqual('C', $enumfield->meta_type);
210
211 }
212
bb78c788 213 public function testGetIndexes() {
7f9f11b5 214 $DB = $this->tdb;
251387d0 215 $dbman = $this->tdb->get_manager();
a230012c 216
9d833e93 217 $table = $this->get_test_table();
3ff8bf26 218 $tablename = $table->getName();
219
f9ecb171 220 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
221 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
80ffbad3 222 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
b579f0db 223 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
767172e4 224 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
b579f0db 225 $dbman->create_table($table);
3ff8bf26 226 $this->tables[$tablename] = $table;
b579f0db 227
3ff8bf26 228 $this->assertTrue($indices = $DB->get_indexes($tablename));
66e75f8d 229 $this->assertEqual(count($indices), 2);
230 // we do not care about index names for now
80ffbad3 231 $first = array_shift($indices);
232 $second = array_shift($indices);
233 if (count($first['columns']) == 2) {
234 $composed = $first;
235 $single = $second;
236 } else {
237 $composed = $second;
238 $single = $first;
239 }
240 $this->assertFalse($single['unique']);
241 $this->assertTrue($composed['unique']);
242 $this->assertEqual(1, count($single['columns']));
243 $this->assertEqual(2, count($composed['columns']));
244 $this->assertEqual('course', $single['columns'][0]);
245 $this->assertEqual('course', $composed['columns'][0]);
246 $this->assertEqual('id', $composed['columns'][1]);
bb78c788 247 }
248
249 public function testGetColumns() {
7f9f11b5 250 $DB = $this->tdb;
251387d0 251 $dbman = $this->tdb->get_manager();
bb78c788 252
9d833e93 253 $table = $this->get_test_table();
3ff8bf26 254 $tablename = $table->getName();
255
f9ecb171 256 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
257 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
258 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 259 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
260 $dbman->create_table($table);
3ff8bf26 261 $this->tables[$tablename] = $table;
b579f0db 262
3ff8bf26 263 $this->assertTrue($columns = $DB->get_columns($tablename));
264 $fields = $this->tables[$tablename]->getFields();
bb78c788 265 $this->assertEqual(count($columns), count($fields));
266
267 for ($i = 0; $i < count($columns); $i++) {
268 if ($i == 0) {
269 $next_column = reset($columns);
270 $next_field = reset($fields);
271 } else {
272 $next_column = next($columns);
273 $next_field = next($fields);
274 }
275
276 $this->assertEqual($next_column->name, $next_field->name);
277 }
278 }
279
280 public function testExecute() {
7f9f11b5 281 $DB = $this->tdb;
251387d0 282 $dbman = $this->tdb->get_manager();
a230012c 283
9d833e93 284 $table = $this->get_test_table();
3ff8bf26 285 $tablename = $table->getName();
286
f9ecb171 287 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
288 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
289 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 290 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
291 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
292 $dbman->create_table($table);
3ff8bf26 293 $this->tables[$tablename] = $table;
b579f0db 294
3ff8bf26 295 $sql = "SELECT * FROM {".$tablename."}";
b579f0db 296
bb78c788 297 $this->assertTrue($DB->execute($sql));
73f7ad71 298
b579f0db 299 $params = array('course' => 1, 'name' => 'test');
6807d2b3 300
3ff8bf26 301 $sql = "INSERT INTO {".$tablename."} (".implode(',', array_keys($params)).")
6807d2b3 302 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
303
304
305 $this->assertTrue($DB->execute($sql, $params));
bb78c788 306
3ff8bf26 307 $record = $DB->get_record($tablename, array('id' => 1));
bb78c788 308
6807d2b3 309 foreach ($params as $field => $value) {
bb78c788 310 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
311 }
312 }
313
7f9f11b5 314 public function test_get_in_or_equal() {
315 $DB = $this->tdb;
316
317 // SQL_PARAMS_QM - IN or =
318
319 // Correct usage of multiple values
320 $in_values = array('value1', 'value2', 'value3', 'value4');
321 list($usql, $params) = $DB->get_in_or_equal($in_values);
322 $this->assertEqual("IN (?,?,?,?)", $usql);
323 $this->assertEqual(4, count($params));
324 foreach ($params as $key => $value) {
325 $this->assertEqual($in_values[$key], $value);
326 }
327
328 // Correct usage of single value (in an array)
329 $in_values = array('value1');
330 list($usql, $params) = $DB->get_in_or_equal($in_values);
331 $this->assertEqual("= ?", $usql);
332 $this->assertEqual(1, count($params));
333 $this->assertEqual($in_values[0], $params[0]);
334
335 // Correct usage of single value
336 $in_value = 'value1';
337 list($usql, $params) = $DB->get_in_or_equal($in_values);
338 $this->assertEqual("= ?", $usql);
339 $this->assertEqual(1, count($params));
340 $this->assertEqual($in_value, $params[0]);
341
342 // SQL_PARAMS_QM - NOT IN or <>
343
344 // Correct usage of multiple values
345 $in_values = array('value1', 'value2', 'value3', 'value4');
346 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
347 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
348 $this->assertEqual(4, count($params));
349 foreach ($params as $key => $value) {
350 $this->assertEqual($in_values[$key], $value);
351 }
352
353 // Correct usage of single value (in array()
354 $in_values = array('value1');
355 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
356 $this->assertEqual("<> ?", $usql);
357 $this->assertEqual(1, count($params));
358 $this->assertEqual($in_values[0], $params[0]);
359
360 // Correct usage of single value
361 $in_value = 'value1';
362 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
363 $this->assertEqual("<> ?", $usql);
364 $this->assertEqual(1, count($params));
365 $this->assertEqual($in_value, $params[0]);
366
367 // SQL_PARAMS_NAMED - IN or =
368
369 // Correct usage of multiple values
370 $in_values = array('value1', 'value2', 'value3', 'value4');
371 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
372 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
373 $this->assertEqual(4, count($params));
374 reset($in_values);
375 foreach ($params as $key => $value) {
376 $this->assertEqual(current($in_values), $value);
377 next($in_values);
378 }
379
380 // Correct usage of single values (in array)
381 $in_values = array('value1');
382 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
383 $this->assertEqual("= :param01", $usql);
384 $this->assertEqual(1, count($params));
385 $this->assertEqual($in_values[0], $params['param01']);
386
387 // Correct usage of single value
388 $in_value = 'value1';
389 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
390 $this->assertEqual("= :param01", $usql);
391 $this->assertEqual(1, count($params));
392 $this->assertEqual($in_value, $params['param01']);
393
394 // SQL_PARAMS_NAMED - NOT IN or <>
395
396 // Correct usage of multiple values
397 $in_values = array('value1', 'value2', 'value3', 'value4');
398 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
399 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
400 $this->assertEqual(4, count($params));
401 reset($in_values);
402 foreach ($params as $key => $value) {
403 $this->assertEqual(current($in_values), $value);
404 next($in_values);
405 }
406
407 // Correct usage of single values (in array)
408 $in_values = array('value1');
409 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
410 $this->assertEqual("<> :param01", $usql);
411 $this->assertEqual(1, count($params));
412 $this->assertEqual($in_values[0], $params['param01']);
413
414 // Correct usage of single value
415 $in_value = 'value1';
416 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
417 $this->assertEqual("<> :param01", $usql);
418 $this->assertEqual(1, count($params));
419 $this->assertEqual($in_value, $params['param01']);
420
421 }
422
423 public function test_fix_table_names() {
424 $DB = new moodle_database_for_testing();
425 $prefix = $DB->get_prefix();
426
427 // Simple placeholder
428 $placeholder = "{user}";
6ff835b7 429 $this->assertEqual($prefix."user", $DB->public_fix_table_names($placeholder));
7f9f11b5 430
431 // Full SQL
432 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
433 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
434 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
435
b579f0db 436
437 }
438
439 public function test_get_recordset() {
440 $DB = $this->tdb;
441 $dbman = $DB->get_manager();
442
9d833e93 443 $table = $this->get_test_table();
3ff8bf26 444 $tablename = $table->getName();
445
f9ecb171 446 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
447 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
448 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b579f0db 449 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
450 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
451 $dbman->create_table($table);
3ff8bf26 452 $this->tables[$tablename] = $table;
b579f0db 453
454 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
455 array('id' => 2, 'course' => 3, 'name' => 'record2'),
456 array('id' => 3, 'course' => 5, 'name' => 'record3'));
22d77567 457
b579f0db 458 foreach ($data as $record) {
3ff8bf26 459 $DB->insert_record($tablename, $record);
b579f0db 460 }
461
3ff8bf26 462 $rs = $DB->get_recordset($tablename);
b579f0db 463 $this->assertTrue($rs);
464
465 reset($data);
466 foreach($rs as $record) {
467 $data_record = current($data);
468 foreach ($record as $k => $v) {
469 $this->assertEqual($data_record[$k], $v);
470 }
471 next($data);
472 }
473 $rs->close();
7f9f11b5 474 }
0088bd31 475
b0cb2290 476 public function test_get_recordset_iterator_keys() {
477 $DB = $this->tdb;
478 $dbman = $DB->get_manager();
479
9d833e93 480 $table = $this->get_test_table();
3ff8bf26 481 $tablename = $table->getName();
482
f9ecb171 483 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
484 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
485 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
b0cb2290 486 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
487 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
488 $dbman->create_table($table);
3ff8bf26 489 $this->tables[$tablename] = $table;
b0cb2290 490
22d77567 491 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
492 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
493 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 494 foreach ($data as $record) {
3ff8bf26 495 $DB->insert_record($tablename, $record);
b0cb2290 496 }
497
498 /// Test repeated numeric keys are returned ok
3ff8bf26 499 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 500
501 reset($data);
502 $count = 0;
503 foreach($rs as $key => $record) {
504 $data_record = current($data);
505 $this->assertEqual($data_record['course'], $key);
506 next($data);
507 $count++;
508 }
509 $rs->close();
510
511 /// Test record returned are ok
512 $this->assertEqual($count, 3);
513
514 /// Test string keys are returned ok
3ff8bf26 515 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 516
517 reset($data);
518 $count = 0;
519 foreach($rs as $key => $record) {
520 $data_record = current($data);
521 $this->assertEqual($data_record['name'], $key);
522 next($data);
523 $count++;
524 }
525 $rs->close();
526
527 /// Test record returned are ok
528 $this->assertEqual($count, 3);
529
530 /// Test numeric not starting in 1 keys are returned ok
3ff8bf26 531 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 532
533 $data = array_reverse($data);
534 reset($data);
535 $count = 0;
536 foreach($rs as $key => $record) {
537 $data_record = current($data);
538 $this->assertEqual($data_record['id'], $key);
539 next($data);
540 $count++;
541 }
542 $rs->close();
543
544 /// Test record returned are ok
545 $this->assertEqual($count, 3);
546 }
547
0088bd31 548 public function test_get_recordset_list() {
549 $DB = $this->tdb;
550 $dbman = $DB->get_manager();
551
9d833e93 552 $table = $this->get_test_table();
3ff8bf26 553 $tablename = $table->getName();
554
f9ecb171 555 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
556 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 557 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
558 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
559 $dbman->create_table($table);
3ff8bf26 560 $this->tables[$tablename] = $table;
0088bd31 561
3ff8bf26 562 $DB->insert_record($tablename, array('course' => 3));
563 $DB->insert_record($tablename, array('course' => 3));
564 $DB->insert_record($tablename, array('course' => 5));
565 $DB->insert_record($tablename, array('course' => 2));
0088bd31 566
3ff8bf26 567 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 568
569 $this->assertTrue($rs);
570
571 $counter = 0;
572 foreach ($rs as $record) {
573 $counter++;
574 }
575 $this->assertEqual(3, $counter);
c362878e 576 $rs->close();
577
578 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
579
580 $counter = 0;
581 foreach ($rs as $record) {
582 $counter++;
583 }
584 $this->assertEqual(0, $counter);
585 $rs->close();
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 }
611 $this->assertEqual(4, $counter);
612
3ff8bf26 613 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 614 $counter = 0;
615 foreach ($rs as $record) {
616 $counter++;
617 }
618 $this->assertEqual(2, $counter);
619 }
620
621 public function test_get_recordset_sql() {
622 $DB = $this->tdb;
623 $dbman = $DB->get_manager();
624
9d833e93 625 $table = $this->get_test_table();
3ff8bf26 626 $tablename = $table->getName();
627
f9ecb171 628 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
629 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 630 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
631 $dbman->create_table($table);
3ff8bf26 632 $this->tables[$tablename] = $table;
0088bd31 633
3ff8bf26 634 $DB->insert_record($tablename, array('course' => 3));
635 $DB->insert_record($tablename, array('course' => 3));
636 $DB->insert_record($tablename, array('course' => 5));
637 $DB->insert_record($tablename, array('course' => 2));
0088bd31 638
3ff8bf26 639 $this->assertTrue($rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 640 $counter = 0;
641 foreach ($rs as $record) {
642 $counter++;
643 }
644 $this->assertEqual(2, $counter);
645 }
646
647 public function test_get_records() {
648 $DB = $this->tdb;
649 $dbman = $DB->get_manager();
650
9d833e93 651 $table = $this->get_test_table();
3ff8bf26 652 $tablename = $table->getName();
653
f9ecb171 654 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
655 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 656 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
657 $dbman->create_table($table);
3ff8bf26 658 $this->tables[$tablename] = $table;
0088bd31 659
3ff8bf26 660 $DB->insert_record($tablename, array('course' => 3));
661 $DB->insert_record($tablename, array('course' => 3));
662 $DB->insert_record($tablename, array('course' => 5));
663 $DB->insert_record($tablename, array('course' => 2));
0088bd31 664
665 // All records
3ff8bf26 666 $records = $DB->get_records($tablename);
0088bd31 667 $this->assertEqual(4, count($records));
668 $this->assertEqual(3, $records[1]->course);
669 $this->assertEqual(3, $records[2]->course);
670 $this->assertEqual(5, $records[3]->course);
671 $this->assertEqual(2, $records[4]->course);
672
673 // Records matching certain conditions
3ff8bf26 674 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 675 $this->assertEqual(2, count($records));
676 $this->assertEqual(3, $records[1]->course);
677 $this->assertEqual(3, $records[2]->course);
678
679 // All records sorted by course
3ff8bf26 680 $records = $DB->get_records($tablename, null, 'course');
0088bd31 681 $this->assertEqual(4, count($records));
682 $current_record = reset($records);
683 $this->assertEqual(4, $current_record->id);
684 $current_record = next($records);
685 $this->assertEqual(1, $current_record->id);
686 $current_record = next($records);
687 $this->assertEqual(2, $current_record->id);
688 $current_record = next($records);
689 $this->assertEqual(3, $current_record->id);
690
691 // All records, but get only one field
3ff8bf26 692 $records = $DB->get_records($tablename, null, '', 'id');
0088bd31 693 $this->assertTrue(empty($records[1]->course));
694 $this->assertFalse(empty($records[1]->id));
695 $this->assertEqual(4, count($records));
3ff8bf26 696
697 // test limitfrom and limitnum
698 $records = $DB->get_records($tablename, null, '', 'id', 0, 0);
699 $this->assertEqual(4, count($records));
700 $records = $DB->get_records($tablename, null, '', 'id', -1, -1);
701 $this->assertEqual(4, count($records));
702 $records = $DB->get_records($tablename, null, '', 'id', 2, 2);
703 $this->assertEqual(2, count($records));
704 $first = reset($records);
705 $last = array_pop($records);
706 $this->assertEqual(3, $first->id);
707 $this->assertEqual(4, $last->id);
708
709// TODO: more LIMIT tests
0088bd31 710 }
711
712 public function test_get_records_list() {
50a12c87 713 $DB = $this->tdb;
714 $dbman = $DB->get_manager();
715
9d833e93 716 $table = $this->get_test_table();
3ff8bf26 717 $tablename = $table->getName();
718
f9ecb171 719 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
720 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 721 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
722 $dbman->create_table($table);
3ff8bf26 723 $this->tables[$tablename] = $table;
50a12c87 724
3ff8bf26 725 $DB->insert_record($tablename, array('course' => 3));
726 $DB->insert_record($tablename, array('course' => 3));
727 $DB->insert_record($tablename, array('course' => 5));
728 $DB->insert_record($tablename, array('course' => 2));
50a12c87 729
3ff8bf26 730 $this->assertTrue($records = $DB->get_records_list($tablename, 'course', array(3, 2)));
50a12c87 731 $this->assertEqual(3, count($records));
732 $this->assertEqual(1, reset($records)->id);
733 $this->assertEqual(2, next($records)->id);
734 $this->assertEqual(4, next($records)->id);
0088bd31 735
c362878e 736 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
737 $this->assertEqual(0, count($records));
738
0088bd31 739 }
740
741 public function test_get_records_sql() {
16a2a412 742 global $CFG;
50a12c87 743 $DB = $this->tdb;
744 $dbman = $DB->get_manager();
745
9d833e93 746 $table = $this->get_test_table();
3ff8bf26 747 $tablename = $table->getName();
748
f9ecb171 749 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
750 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 751 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
752 $dbman->create_table($table);
3ff8bf26 753 $this->tables[$tablename] = $table;
50a12c87 754
3ff8bf26 755 $DB->insert_record($tablename, array('course' => 3));
756 $DB->insert_record($tablename, array('course' => 3));
757 $DB->insert_record($tablename, array('course' => 5));
758 $DB->insert_record($tablename, array('course' => 2));
50a12c87 759
3ff8bf26 760 $this->assertTrue($records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
50a12c87 761 $this->assertEqual(2, count($records));
762 $this->assertEqual(1, reset($records)->id);
763 $this->assertEqual(2, next($records)->id);
0088bd31 764
16a2a412 765 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
766 $olddebug = $CFG->debug; // Save current debug settings
767 $olddisplay = $CFG->debugdisplay;
768 $CFG->debug = DEBUG_DEVELOPER;
769 $CFG->debugdisplay = true;
758ba89a 770 ob_start(); // hide debug warning
3ff8bf26 771 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {".$tablename."}", null);
758ba89a 772 ob_end_clean();
16a2a412 773 $debuginfo = ob_get_contents();
d83bcdfc 774 $CFG->debug = $olddebug; // Restore original debug settings
775 $CFG->debugdisplay = $olddisplay;
758ba89a 776
777 $this->assertEqual(3, count($records));
778 $this->assertFalse($debuginfo === '');
0088bd31 779 }
780
781 public function test_get_records_menu() {
50a12c87 782 $DB = $this->tdb;
783 $dbman = $DB->get_manager();
784
9d833e93 785 $table = $this->get_test_table();
3ff8bf26 786 $tablename = $table->getName();
787
f9ecb171 788 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
789 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 790 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
791 $dbman->create_table($table);
3ff8bf26 792 $this->tables[$tablename] = $table;
50a12c87 793
3ff8bf26 794 $DB->insert_record($tablename, array('course' => 3));
795 $DB->insert_record($tablename, array('course' => 3));
796 $DB->insert_record($tablename, array('course' => 5));
797 $DB->insert_record($tablename, array('course' => 2));
50a12c87 798
3ff8bf26 799 $this->assertTrue($records = $DB->get_records_menu($tablename, array('course' => 3)));
50a12c87 800 $this->assertEqual(2, count($records));
801 $this->assertFalse(empty($records[1]));
802 $this->assertFalse(empty($records[2]));
803 $this->assertEqual(3, $records[1]);
804 $this->assertEqual(3, $records[2]);
0088bd31 805
806 }
807
808 public function test_get_records_select_menu() {
50a12c87 809 $DB = $this->tdb;
810 $dbman = $DB->get_manager();
811
9d833e93 812 $table = $this->get_test_table();
3ff8bf26 813 $tablename = $table->getName();
814
f9ecb171 815 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
816 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 817 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
818 $dbman->create_table($table);
3ff8bf26 819 $this->tables[$tablename] = $table;
50a12c87 820
3ff8bf26 821 $DB->insert_record($tablename, array('course' => 3));
822 $DB->insert_record($tablename, array('course' => 2));
823 $DB->insert_record($tablename, array('course' => 3));
824 $DB->insert_record($tablename, array('course' => 5));
50a12c87 825
3ff8bf26 826 $this->assertTrue($records = $DB->get_records_select_menu($tablename, "course > ?", array(2)));
50a12c87 827
828 $this->assertEqual(3, count($records));
829 $this->assertFalse(empty($records[1]));
830 $this->assertTrue(empty($records[2]));
831 $this->assertFalse(empty($records[3]));
832 $this->assertFalse(empty($records[4]));
833 $this->assertEqual(3, $records[1]);
834 $this->assertEqual(3, $records[3]);
835 $this->assertEqual(5, $records[4]);
0088bd31 836
837 }
838
839 public function test_get_records_sql_menu() {
50a12c87 840 $DB = $this->tdb;
841 $dbman = $DB->get_manager();
842
9d833e93 843 $table = $this->get_test_table();
3ff8bf26 844 $tablename = $table->getName();
845
f9ecb171 846 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
847 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 848 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
849 $dbman->create_table($table);
3ff8bf26 850 $this->tables[$tablename] = $table;
50a12c87 851
3ff8bf26 852 $DB->insert_record($tablename, array('course' => 3));
853 $DB->insert_record($tablename, array('course' => 2));
854 $DB->insert_record($tablename, array('course' => 3));
855 $DB->insert_record($tablename, array('course' => 5));
50a12c87 856
3ff8bf26 857 $this->assertTrue($records = $DB->get_records_sql_menu("SELECT * FROM {".$tablename."} WHERE course > ?", array(2)));
50a12c87 858
859 $this->assertEqual(3, count($records));
860 $this->assertFalse(empty($records[1]));
861 $this->assertTrue(empty($records[2]));
862 $this->assertFalse(empty($records[3]));
863 $this->assertFalse(empty($records[4]));
864 $this->assertEqual(3, $records[1]);
865 $this->assertEqual(3, $records[3]);
866 $this->assertEqual(5, $records[4]);
0088bd31 867
868 }
869
870 public function test_get_record() {
50a12c87 871 $DB = $this->tdb;
872 $dbman = $DB->get_manager();
873
9d833e93 874 $table = $this->get_test_table();
3ff8bf26 875 $tablename = $table->getName();
876
f9ecb171 877 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
878 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 879 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
880 $dbman->create_table($table);
3ff8bf26 881 $this->tables[$tablename] = $table;
50a12c87 882
3ff8bf26 883 $DB->insert_record($tablename, array('course' => 3));
884 $DB->insert_record($tablename, array('course' => 2));
50a12c87 885
3ff8bf26 886 $this->assertTrue($record = $DB->get_record($tablename, array('id' => 2)));
0088bd31 887
50a12c87 888 $this->assertEqual(2, $record->course);
0088bd31 889 }
890
891 public function test_get_record_select() {
50a12c87 892 $DB = $this->tdb;
893 $dbman = $DB->get_manager();
894
9d833e93 895 $table = $this->get_test_table();
3ff8bf26 896 $tablename = $table->getName();
897
f9ecb171 898 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
899 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 900 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
901 $dbman->create_table($table);
3ff8bf26 902 $this->tables[$tablename] = $table;
50a12c87 903
3ff8bf26 904 $DB->insert_record($tablename, array('course' => 3));
905 $DB->insert_record($tablename, array('course' => 2));
50a12c87 906
3ff8bf26 907 $this->assertTrue($record = $DB->get_record_select($tablename, "id = ?", array(2)));
50a12c87 908
909 $this->assertEqual(2, $record->course);
0088bd31 910
911 }
912
913 public function test_get_record_sql() {
50a12c87 914 $DB = $this->tdb;
915 $dbman = $DB->get_manager();
916
9d833e93 917 $table = $this->get_test_table();
3ff8bf26 918 $tablename = $table->getName();
919
f9ecb171 920 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
921 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 922 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
923 $dbman->create_table($table);
3ff8bf26 924 $this->tables[$tablename] = $table;
50a12c87 925
3ff8bf26 926 $DB->insert_record($tablename, array('course' => 3));
927 $DB->insert_record($tablename, array('course' => 2));
50a12c87 928
3ff8bf26 929 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(2)));
50a12c87 930
931 $this->assertEqual(2, $record->course);
0088bd31 932
e6c6531c 933 // backwards compatibility with $ignoremultiple
934 $this->assertFalse(IGNORE_MISSING);
935 $this->assertTrue(IGNORE_MULTIPLE);
936
af12ea93 937 // record not found
e6c6531c 938 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MISSING));
939 $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), IGNORE_MULTIPLE));
af12ea93 940 try {
e6c6531c 941 $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), MUST_EXIST);
af12ea93 942 $this->fail("Exception expected");
943 } catch (dml_missing_record_exception $e) {
944 $this->assertTrue(true);
945 }
946
947 // multiple matches
948 ob_start(); // hide debug warning
e6c6531c 949 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MISSING));
af12ea93 950 ob_end_clean();
e6c6531c 951 $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), IGNORE_MULTIPLE));
af12ea93 952 try {
e6c6531c 953 $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), MUST_EXIST);
af12ea93 954 $this->fail("Exception expected");
955 } catch (dml_multiple_records_exception $e) {
956 $this->assertTrue(true);
957 }
0088bd31 958 }
959
960 public function test_get_field() {
50a12c87 961 $DB = $this->tdb;
962 $dbman = $DB->get_manager();
963
9d833e93 964 $table = $this->get_test_table();
3ff8bf26 965 $tablename = $table->getName();
966
f9ecb171 967 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
968 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 969 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
970 $dbman->create_table($table);
3ff8bf26 971 $this->tables[$tablename] = $table;
0088bd31 972
3ff8bf26 973 $DB->insert_record($tablename, array('course' => 3));
50a12c87 974
3ff8bf26 975 $this->assertTrue($course = $DB->get_field($tablename, 'course', array('id' => 1)));
50a12c87 976 $this->assertEqual(3, $course);
0088bd31 977 }
978
979 public function test_get_field_select() {
50a12c87 980 $DB = $this->tdb;
981 $dbman = $DB->get_manager();
982
9d833e93 983 $table = $this->get_test_table();
3ff8bf26 984 $tablename = $table->getName();
985
f9ecb171 986 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
987 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 988 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
989 $dbman->create_table($table);
3ff8bf26 990 $this->tables[$tablename] = $table;
50a12c87 991
3ff8bf26 992 $DB->insert_record($tablename, array('course' => 3));
50a12c87 993
3ff8bf26 994 $this->assertTrue($course = $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
50a12c87 995 $this->assertEqual(3, $course);
0088bd31 996
997 }
998
999 public function test_get_field_sql() {
50a12c87 1000 $DB = $this->tdb;
1001 $dbman = $DB->get_manager();
1002
9d833e93 1003 $table = $this->get_test_table();
3ff8bf26 1004 $tablename = $table->getName();
1005
f9ecb171 1006 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1007 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1008 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1009 $dbman->create_table($table);
3ff8bf26 1010 $this->tables[$tablename] = $table;
50a12c87 1011
3ff8bf26 1012 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1013
3ff8bf26 1014 $this->assertTrue($course = $DB->get_field_sql("SELECT course FROM {".$tablename."} WHERE id = ?", array(1)));
50a12c87 1015 $this->assertEqual(3, $course);
0088bd31 1016
1017 }
1018
50a12c87 1019 public function test_get_fieldset_select() {
1020 $DB = $this->tdb;
1021 $dbman = $DB->get_manager();
1022
9d833e93 1023 $table = $this->get_test_table();
3ff8bf26 1024 $tablename = $table->getName();
1025
f9ecb171 1026 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1027 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1028 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1029 $dbman->create_table($table);
3ff8bf26 1030 $this->tables[$tablename] = $table;
50a12c87 1031
3ff8bf26 1032 $DB->insert_record($tablename, array('course' => 1));
1033 $DB->insert_record($tablename, array('course' => 3));
1034 $DB->insert_record($tablename, array('course' => 2));
1035 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1036
3ff8bf26 1037 $this->assertTrue($fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1)));
50a12c87 1038
1039 $this->assertEqual(3, count($fieldset));
1040 $this->assertEqual(3, $fieldset[0]);
1041 $this->assertEqual(2, $fieldset[1]);
1042 $this->assertEqual(6, $fieldset[2]);
0088bd31 1043
1044 }
1045
1046 public function test_get_fieldset_sql() {
50a12c87 1047 $DB = $this->tdb;
1048 $dbman = $DB->get_manager();
0088bd31 1049
9d833e93 1050 $table = $this->get_test_table();
3ff8bf26 1051 $tablename = $table->getName();
1052
f9ecb171 1053 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1054 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1055 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1056 $dbman->create_table($table);
3ff8bf26 1057 $this->tables[$tablename] = $table;
50a12c87 1058
3ff8bf26 1059 $DB->insert_record($tablename, array('course' => 1));
1060 $DB->insert_record($tablename, array('course' => 3));
1061 $DB->insert_record($tablename, array('course' => 2));
1062 $DB->insert_record($tablename, array('course' => 6));
50a12c87 1063
3ff8bf26 1064 $this->assertTrue($fieldset = $DB->get_fieldset_sql("SELECT * FROM {".$tablename."} WHERE course > ?", array(1)));
50a12c87 1065
1066 $this->assertEqual(3, count($fieldset));
1067 $this->assertEqual(2, $fieldset[0]);
1068 $this->assertEqual(3, $fieldset[1]);
1069 $this->assertEqual(4, $fieldset[2]);
0088bd31 1070 }
1071
1072 public function test_insert_record_raw() {
50a12c87 1073 $DB = $this->tdb;
1074 $dbman = $DB->get_manager();
0088bd31 1075
9d833e93 1076 $table = $this->get_test_table();
3ff8bf26 1077 $tablename = $table->getName();
1078
f9ecb171 1079 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1080 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1081 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1082 $dbman->create_table($table);
3ff8bf26 1083 $this->tables[$tablename] = $table;
50a12c87 1084
3ff8bf26 1085 $this->assertTrue($DB->insert_record_raw($tablename, array('course' => 1)));
1086 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 1)));
50a12c87 1087 $this->assertEqual(1, $record->course);
0088bd31 1088 }
1089
1090 public function test_insert_record() {
50a12c87 1091 $DB = $this->tdb;
1092 $dbman = $DB->get_manager();
1093
9d833e93 1094 $table = $this->get_test_table();
3ff8bf26 1095 $tablename = $table->getName();
1096
f9ecb171 1097 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1098 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1099 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1100 $dbman->create_table($table);
3ff8bf26 1101 $this->tables[$tablename] = $table;
50a12c87 1102
3ff8bf26 1103 $this->assertTrue($DB->insert_record($tablename, array('course' => 1)));
1104 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 1)));
50a12c87 1105 $this->assertEqual(1, $record->course);
0088bd31 1106
1107 }
1108
94898738 1109 public function test_import_record() {
1110 $DB = $this->tdb;
1111 $dbman = $DB->get_manager();
1112
9d833e93 1113 $table = $this->get_test_table();
3ff8bf26 1114 $tablename = $table->getName();
1115
f9ecb171 1116 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1117 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
94898738 1118 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1119 $dbman->create_table($table);
3ff8bf26 1120 $this->tables[$tablename] = $table;
94898738 1121
1122 $record = (object)array('id'=>666, 'course'=>10);
3ff8bf26 1123 $this->assertTrue($DB->import_record($tablename, $record));
1124 $records = $DB->get_records($tablename);
94898738 1125 $this->assertEqual(1, count($records));
1126 $this->assertEqual(10, $records[666]->course);
1127
1128 $record = (object)array('id'=>13, 'course'=>2);
3ff8bf26 1129 $this->assertTrue($DB->import_record($tablename, $record));
1130 $records = $DB->get_records($tablename);
94898738 1131 $this->assertEqual(2, $records[13]->course);
1132 }
1133
c824e1e1 1134 public function test_insert_record_clob() {
1135 global $CFG;
1136
1137 $DB = $this->tdb;
1138 $dbman = $DB->get_manager();
1139
9d833e93 1140 $table = $this->get_test_table();
3ff8bf26 1141 $tablename = $table->getName();
1142
f9ecb171 1143 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1144 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
c824e1e1 1145 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1146 $dbman->create_table($table);
3ff8bf26 1147 $this->tables[$tablename] = $table;
c824e1e1 1148
6ff835b7 1149 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
c824e1e1 1150
3ff8bf26 1151 $id = $DB->insert_record($tablename, array('description' => $clob));
1152 $this->assertEqual($id, 1);
1153 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1154 $this->assertEqual($clob, $record->description);
1155
1156 }
1157
3ff8bf26 1158 public function test_insert_record_multiple_lobs() {
c824e1e1 1159 global $CFG;
1160
1161 $DB = $this->tdb;
1162 $dbman = $DB->get_manager();
1163
9d833e93 1164 $table = $this->get_test_table();
3ff8bf26 1165 $tablename = $table->getName();
1166
f9ecb171 1167 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1168 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
1169 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null);
c824e1e1 1170 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1171 $dbman->create_table($table);
3ff8bf26 1172 $this->tables[$tablename] = $table;
c824e1e1 1173
6ff835b7 1174 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1175 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
c824e1e1 1176
3ff8bf26 1177 $this->assertTrue($id = $DB->insert_record($tablename, array('description' => $clob, 'image' => $blob)));
1178 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1179 $this->assertEqual($clob, $record->description);
1180 $this->assertEqual($blob, $record->image);
3ff8bf26 1181 $this->assertEqual($clob, $DB->get_field($tablename, 'description', array('id' => $id)));
1182 $this->assertEqual($blob, $DB->get_field($tablename, 'image', array('id' => $id)));
66e75f8d 1183
c824e1e1 1184 }
1185
1186
0088bd31 1187 public function test_update_record_raw() {
50a12c87 1188 $DB = $this->tdb;
1189 $dbman = $DB->get_manager();
0088bd31 1190
9d833e93 1191 $table = $this->get_test_table();
3ff8bf26 1192 $tablename = $table->getName();
1193
f9ecb171 1194 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1195 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1196 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1197 $dbman->create_table($table);
3ff8bf26 1198 $this->tables[$tablename] = $table;
50a12c87 1199
3ff8bf26 1200 $DB->insert_record($tablename, array('course' => 1));
1201 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1202 $record->course = 2;
3ff8bf26 1203 $this->assertTrue($DB->update_record_raw($tablename, $record));
1204 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1205 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1206 }
1207
1208 public function test_update_record() {
50a12c87 1209 $DB = $this->tdb;
1210 $dbman = $DB->get_manager();
1211
9d833e93 1212 $table = $this->get_test_table();
3ff8bf26 1213 $tablename = $table->getName();
1214
f9ecb171 1215 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1216 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1217 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1218 $dbman->create_table($table);
3ff8bf26 1219 $this->tables[$tablename] = $table;
0088bd31 1220
3ff8bf26 1221 $DB->insert_record($tablename, array('course' => 1));
1222 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1223 $record->course = 2;
3ff8bf26 1224 $this->assertTrue($DB->update_record($tablename, $record));
1225 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1226 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1227 }
1228
c824e1e1 1229 public function test_update_record_clob() {
1230 global $CFG;
1231
1232 $DB = $this->tdb;
1233 $dbman = $DB->get_manager();
1234
9d833e93 1235 $table = $this->get_test_table();
3ff8bf26 1236 $tablename = $table->getName();
1237
f9ecb171 1238 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1239 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
c824e1e1 1240 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1241 $dbman->create_table($table);
3ff8bf26 1242 $this->tables[$tablename] = $table;
c824e1e1 1243
6ff835b7 1244 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
c824e1e1 1245
3ff8bf26 1246 $id = $DB->insert_record($tablename, array('description' => $clob));
1247 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1248 $record->description = substr($clob, 0, 500);
3ff8bf26 1249 $this->assertTrue($DB->update_record($tablename, $record));
c824e1e1 1250
3ff8bf26 1251 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1252 $this->assertEqual(substr($clob, 0, 500), $record->description);
1253 }
1254
9d833e93 1255 public function test_update_record_multiple_lobs() {
c824e1e1 1256 global $CFG;
1257
1258 $DB = $this->tdb;
1259 $dbman = $DB->get_manager();
1260
9d833e93 1261 $table = $this->get_test_table();
3ff8bf26 1262 $tablename = $table->getName();
1263
f9ecb171 1264 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1265 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
1266 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null);
c824e1e1 1267 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1268 $dbman->create_table($table);
3ff8bf26 1269 $this->tables[$tablename] = $table;
c824e1e1 1270
6ff835b7 1271 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1272 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
c824e1e1 1273
d246cdd2 1274 $newclob = substr($clob, 0, 500);
1275 $newblob = substr($blob, 0, 250);
1276
3ff8bf26 1277 $id = $DB->insert_record($tablename, array('description' => $clob, 'image' => $blob));
1278 $record = $DB->get_record($tablename, array('id' => $id));
d246cdd2 1279 $record->description = $newclob;
1280 $record->image = $newblob;
3ff8bf26 1281 $this->assertTrue($DB->update_record($tablename, $record));
c824e1e1 1282
3ff8bf26 1283 $record = $DB->get_record($tablename, array('id' => $id));
d246cdd2 1284 $this->assertEqual($newclob, $record->description);
1285 $this->assertEqual($newblob, $record->image);
3ff8bf26 1286 $this->assertEqual($newclob, $DB->get_field($tablename, 'description', array('id' => $id)));
1287 $this->assertEqual($newblob, $DB->get_field($tablename, 'image', array('id' => $id)));
c824e1e1 1288
1289 }
1290
0088bd31 1291 public function test_set_field() {
50a12c87 1292 $DB = $this->tdb;
1293 $dbman = $DB->get_manager();
1294
9d833e93 1295 $table = $this->get_test_table();
3ff8bf26 1296 $tablename = $table->getName();
1297
f9ecb171 1298 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1299 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1300 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1301 $dbman->create_table($table);
3ff8bf26 1302 $this->tables[$tablename] = $table;
0088bd31 1303
3ff8bf26 1304 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1305
3ff8bf26 1306 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1307 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1308 }
1309
1310 public function test_set_field_select() {
50a12c87 1311 $DB = $this->tdb;
1312 $dbman = $DB->get_manager();
1313
9d833e93 1314 $table = $this->get_test_table();
3ff8bf26 1315 $tablename = $table->getName();
1316
f9ecb171 1317 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1318 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1319 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1320 $dbman->create_table($table);
3ff8bf26 1321 $this->tables[$tablename] = $table;
50a12c87 1322
3ff8bf26 1323 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1324
3ff8bf26 1325 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1326 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1327
1328 }
1329
1330 public function test_count_records() {
1331 $DB = $this->tdb;
1332
1333 $dbman = $DB->get_manager();
1334
9d833e93 1335 $table = $this->get_test_table();
3ff8bf26 1336 $tablename = $table->getName();
1337
f9ecb171 1338 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1339 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1340 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1341 $dbman->create_table($table);
3ff8bf26 1342 $this->tables[$tablename] = $table;
0088bd31 1343
3ff8bf26 1344 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1345
3ff8bf26 1346 $DB->insert_record($tablename, array('course' => 3));
1347 $DB->insert_record($tablename, array('course' => 4));
1348 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1349
3ff8bf26 1350 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1351 }
1352
1353 public function test_count_records_select() {
50a12c87 1354 $DB = $this->tdb;
0088bd31 1355
50a12c87 1356 $dbman = $DB->get_manager();
1357
9d833e93 1358 $table = $this->get_test_table();
3ff8bf26 1359 $tablename = $table->getName();
1360
f9ecb171 1361 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1362 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1363 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1364 $dbman->create_table($table);
3ff8bf26 1365 $this->tables[$tablename] = $table;
50a12c87 1366
3ff8bf26 1367 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1368
3ff8bf26 1369 $DB->insert_record($tablename, array('course' => 3));
1370 $DB->insert_record($tablename, array('course' => 4));
1371 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1372
3ff8bf26 1373 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1374 }
1375
1376 public function test_count_records_sql() {
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');
50a12c87 1385 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1386 $dbman->create_table($table);
3ff8bf26 1387 $this->tables[$tablename] = $table;
50a12c87 1388
3ff8bf26 1389 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1390
3ff8bf26 1391 $DB->insert_record($tablename, array('course' => 3));
1392 $DB->insert_record($tablename, array('course' => 4));
1393 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1394
3ff8bf26 1395 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1396 }
1397
1398 public function test_record_exists() {
50a12c87 1399 $DB = $this->tdb;
1400 $dbman = $DB->get_manager();
1401
9d833e93 1402 $table = $this->get_test_table();
3ff8bf26 1403 $tablename = $table->getName();
1404
f9ecb171 1405 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1406 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1407 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1408 $dbman->create_table($table);
3ff8bf26 1409 $this->tables[$tablename] = $table;
50a12c87 1410
3ff8bf26 1411 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1412
3ff8bf26 1413 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1414 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1415
3ff8bf26 1416 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1417
1418 }
1419
1420 public function test_record_exists_select() {
50a12c87 1421 $DB = $this->tdb;
1422 $dbman = $DB->get_manager();
0088bd31 1423
9d833e93 1424 $table = $this->get_test_table();
3ff8bf26 1425 $tablename = $table->getName();
1426
f9ecb171 1427 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1428 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1429 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1430 $dbman->create_table($table);
3ff8bf26 1431 $this->tables[$tablename] = $table;
50a12c87 1432
3ff8bf26 1433 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1434
3ff8bf26 1435 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1436 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1437
3ff8bf26 1438 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1439 }
1440
1441 public function test_record_exists_sql() {
50a12c87 1442 $DB = $this->tdb;
1443 $dbman = $DB->get_manager();
0088bd31 1444
9d833e93 1445 $table = $this->get_test_table();
3ff8bf26 1446 $tablename = $table->getName();
1447
f9ecb171 1448 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1449 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1450 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1451 $dbman->create_table($table);
3ff8bf26 1452 $this->tables[$tablename] = $table;
50a12c87 1453
3ff8bf26 1454 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1455
3ff8bf26 1456 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1457 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1458
3ff8bf26 1459 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1460 }
1461
1462 public function test_delete_records() {
50a12c87 1463 $DB = $this->tdb;
1464 $dbman = $DB->get_manager();
0088bd31 1465
9d833e93 1466 $table = $this->get_test_table();
3ff8bf26 1467 $tablename = $table->getName();
1468
f9ecb171 1469 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1470 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1471 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1472 $dbman->create_table($table);
3ff8bf26 1473 $this->tables[$tablename] = $table;
50a12c87 1474
3ff8bf26 1475 $DB->insert_record($tablename, array('course' => 3));
1476 $DB->insert_record($tablename, array('course' => 2));
1477 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1478
1479 // Delete all records
3ff8bf26 1480 $this->assertTrue($DB->delete_records($tablename));
1481 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1482
1483 // Delete subset of records
3ff8bf26 1484 $DB->insert_record($tablename, array('course' => 3));
1485 $DB->insert_record($tablename, array('course' => 2));
1486 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1487
3ff8bf26 1488 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1489 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1490 }
1491
1492 public function test_delete_records_select() {
50a12c87 1493 $DB = $this->tdb;
1494 $dbman = $DB->get_manager();
0088bd31 1495
9d833e93 1496 $table = $this->get_test_table();
3ff8bf26 1497 $tablename = $table->getName();
1498
f9ecb171 1499 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1500 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1501 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1502 $dbman->create_table($table);
3ff8bf26 1503 $this->tables[$tablename] = $table;
0088bd31 1504
3ff8bf26 1505 $DB->insert_record($tablename, array('course' => 3));
1506 $DB->insert_record($tablename, array('course' => 2));
1507 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1508
3ff8bf26 1509 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1510 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1511 }
1d861fce 1512
c362878e 1513 public function test_delete_records_list() {
1514 $DB = $this->tdb;
1515 $dbman = $DB->get_manager();
1516
1517 $table = $this->get_test_table();
1518 $tablename = $table->getName();
1519
f9ecb171 1520 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1521 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 1522 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1523 $dbman->create_table($table);
1524 $this->tables[$tablename] = $table;
1525
1526 $DB->insert_record($tablename, array('course' => 1));
1527 $DB->insert_record($tablename, array('course' => 2));
1528 $DB->insert_record($tablename, array('course' => 3));
1529
1530 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1531 $this->assertEqual(1, $DB->count_records($tablename));
1532
1533 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1534 $this->assertEqual(1, $DB->count_records($tablename));
1535 }
1536
082ae821 1537 function test_sql_null_from_clause() {
655bbf51 1538 $DB = $this->tdb;
082ae821 1539 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1540 $this->assertEqual($DB->get_field_sql($sql), 1);
1541 }
655bbf51 1542
082ae821 1543 function test_sql_bitand() {
1544 $DB = $this->tdb;
9d833e93 1545 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1546 $this->assertEqual($DB->get_field_sql($sql), 2);
1547 }
655bbf51 1548
082ae821 1549 function test_sql_bitnot() {
1550 $DB = $this->tdb;
655bbf51 1551
082ae821 1552 $not = $DB->sql_bitnot(2);
1553 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1554
9d833e93 1555 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1556 $this->assertEqual($DB->get_field_sql($sql), 5);
1557 }
655bbf51 1558
082ae821 1559 function test_sql_bitor() {
1560 $DB = $this->tdb;
9d833e93 1561 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1562 $this->assertEqual($DB->get_field_sql($sql), 11);
1563 }
655bbf51 1564
082ae821 1565 function test_sql_bitxor() {
1566 $DB = $this->tdb;
9d833e93 1567 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1568 $this->assertEqual($DB->get_field_sql($sql), 9);
1569 }
1570
e6df3734 1571 function test_sql_modulo() {
1572 $DB = $this->tdb;
9d833e93 1573 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1574 $this->assertEqual($DB->get_field_sql($sql), 3);
1575 }
1576
082ae821 1577 function test_sql_ceil() {
1578 $DB = $this->tdb;
9d833e93 1579 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1580 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1581 }
1582
6ff835b7 1583 function test_cast_char2int() {
1584 $DB = $this->tdb;
1585 $dbman = $DB->get_manager();
1586
9d833e93 1587 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1588 $tablename1 = $table1->getName();
1589
f9ecb171 1590 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1591 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1592 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1593 $dbman->create_table($table1);
3ff8bf26 1594 $this->tables[$tablename1] = $table1;
6ff835b7 1595
3ff8bf26 1596 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1597
9d833e93 1598 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1599 $tablename2 = $table2->getName();
f9ecb171 1600 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1601 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 1602 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1603 $dbman->create_table($table2);
1604 $this->tables[$table2->getName()] = $table2;
1605
9d833e93 1606 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 1607
1608 try {
9d833e93 1609 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 1610 $records = $DB->get_records_sql($sql);
1611 $this->assertEqual(count($records), 1);
1612 } catch (dml_exception $e) {
1613 $this->fail("No exception expected");
1614 }
1615 }
1616
1617 function test_cast_char2real() {
1618 $DB = $this->tdb;
1619 $dbman = $DB->get_manager();
1620
9d833e93 1621 $table = $this->get_test_table();
3ff8bf26 1622 $tablename = $table->getName();
1623
f9ecb171 1624 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1625 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1626 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 1627 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1628 $dbman->create_table($table);
3ff8bf26 1629 $this->tables[$tablename] = $table;
6ff835b7 1630
9d833e93 1631 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
1632 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
1633 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 1634
9d833e93 1635 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 1636 $records = $DB->get_records_sql($sql);
1637 $this->assertEqual(count($records), 2);
1638 }
1639
082ae821 1640 function sql_compare_text() {
1641 $DB = $this->tdb;
1642 $dbman = $DB->get_manager();
1643
9d833e93 1644 $table = $this->get_test_table();
3ff8bf26 1645 $tablename = $table->getName();
1646
f9ecb171 1647 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1648 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1649 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1650 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1651 $dbman->create_table($table);
3ff8bf26 1652 $this->tables[$tablename] = $table;
082ae821 1653
3ff8bf26 1654 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
1655 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
1656 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 1657
3ff8bf26 1658 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 1659 $records = $DB->get_records_sql($sql);
1660 $this->assertEqual(count($records), 1);
1661
3ff8bf26 1662 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 1663 $records = $DB->get_records_sql($sql);
1664 $this->assertEqual(count($records), 2);
1665 }
1666
6ff835b7 1667 function test_ilike() {
1668 $DB = $this->tdb;
1669 $dbman = $DB->get_manager();
1670
9d833e93 1671 $table = $this->get_test_table();
3ff8bf26 1672 $tablename = $table->getName();
1673
f9ecb171 1674 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1675 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1676 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1677 $dbman->create_table($table);
3ff8bf26 1678 $this->tables[$tablename] = $table;
6ff835b7 1679
3ff8bf26 1680 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
1681 $DB->insert_record($tablename, array('name'=>'NoDupor'));
1682 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 1683
3ff8bf26 1684 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 1685 $params = array("%dup_r%");
1686 $records = $DB->get_records_sql($sql, $params);
1687 $this->assertEqual(count($records), 2);
1688 }
1689
1690 function test_concat() {
1691 $DB = $this->tdb;
082ae821 1692 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ".$DB->sql_null_from_clause();
1693 $params = array("name", "name2", "name3");
1694 $this->assertEqual("namename2name3", $DB->get_field_sql($sql, $params));
6ff835b7 1695 }
1696
082ae821 1697 function test_concat_join() {
6ff835b7 1698 $DB = $this->tdb;
082ae821 1699 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
1700 $params = array("name", "name2", "name3");
1701 $result = $DB->get_field_sql($sql, $params);
1702 $this->assertEqual("name name2 name3", $result);
1703 }
1704
1705 function test_sql_fullname() {
1706 $DB = $this->tdb;
1707 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
1708 $params = array('first'=>'Firstname', 'last'=>'Surname');
1709 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
1710 }
1711
1712 function sql_sql_order_by_text() {
1713 $DB = $this->tdb;
1714 $dbman = $DB->get_manager();
1715
9d833e93 1716 $table = $this->get_test_table();
3ff8bf26 1717 $tablename = $table->getName();
1718
f9ecb171 1719 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1720 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1721 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1722 $dbman->create_table($table);
3ff8bf26 1723 $this->tables[$tablename] = $table;
082ae821 1724
3ff8bf26 1725 $DB->insert_record($tablename, array('description'=>'abcd'));
1726 $DB->insert_record($tablename, array('description'=>'dxxx'));
1727 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 1728
3ff8bf26 1729 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 1730 $records = $DB->get_records_sql($sql);
1731 $first = array_unshift($records);
1732 $this->assertEqual(1, $first->id);
1733 $second = array_unshift($records);
1734 $this->assertEqual(3, $second->id);
1735 $last = array_unshift($records);
1736 $this->assertEqual(2, $last->id);
1737 }
1738
1739 function test_sql_substring() {
1740 $DB = $this->tdb;
1741 $dbman = $DB->get_manager();
1742
9d833e93 1743 $table = $this->get_test_table();
3ff8bf26 1744 $tablename = $table->getName();
1745
f9ecb171 1746 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1747 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 1748 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1749 $dbman->create_table($table);
3ff8bf26 1750 $this->tables[$tablename] = $table;
082ae821 1751
1752 $string = 'abcdefghij';
1753
3ff8bf26 1754 $DB->insert_record($tablename, array('name'=>$string));
082ae821 1755
3ff8bf26 1756 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 1757 $record = $DB->get_record_sql($sql);
1758 $this->assertEqual(substr($string, 5-1), $record->name);
1759
3ff8bf26 1760 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 1761 $record = $DB->get_record_sql($sql);
1762 $this->assertEqual(substr($string, 5-1, 2), $record->name);
1763
1764 try {
1765 // silence php warning ;-)
1766 @$DB->sql_substr("name");
1767 $this->fail("Expecting an exception, none occurred");
1768 } catch (Exception $e) {
1769 $this->assertTrue($e instanceof coding_exception);
1770 }
6ff835b7 1771 }
1772
37d975e1 1773 function test_sql_length() {
1774 $DB = $this->tdb;
1775 $this->assertEqual($DB->get_field_sql(
1776 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
1777 $this->assertEqual($DB->get_field_sql(
1778 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
1779 }
1780
1d861fce 1781 function test_sql_position() {
1782 $DB = $this->tdb;
1783 $this->assertEqual($DB->get_field_sql(
6ff835b7 1784 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 1785 $this->assertEqual($DB->get_field_sql(
6ff835b7 1786 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
1787 }
1788
082ae821 1789 function test_sql_empty() {
1790 $DB = $this->tdb;
1791 $dbman = $DB->get_manager();
1792
9d833e93 1793 $table = $this->get_test_table();
3ff8bf26 1794 $tablename = $table->getName();
1795
f9ecb171 1796 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1797 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 1798 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1799 $dbman->create_table($table);
3ff8bf26 1800 $this->tables[$tablename] = $table;
082ae821 1801
3ff8bf26 1802 $DB->insert_record($tablename, array('name'=>''));
1803 $DB->insert_record($tablename, array('name'=>null));
1804 $DB->insert_record($tablename, array('name'=>'lalalal'));
1805 $DB->insert_record($tablename, array('name'=>0));
082ae821 1806
3ff8bf26 1807 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 1808 $this->assertEqual(count($records), 1);
1809 $record = reset($records);
1810 $this->assertEqual($record->name, '');
1811 }
1812
1813 function test_sql_isempty() {
1814 $DB = $this->tdb;
1815 $dbman = $DB->get_manager();
1816
9d833e93 1817 $table = $this->get_test_table();
3ff8bf26 1818 $tablename = $table->getName();
1819
f9ecb171 1820 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1821 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1822 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1823 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
1824 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1825 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1826 $dbman->create_table($table);
3ff8bf26 1827 $this->tables[$tablename] = $table;
082ae821 1828
3ff8bf26 1829 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
1830 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
1831 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
1832 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 1833
3ff8bf26 1834 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 1835 $this->assertEqual(count($records), 1);
1836 $record = reset($records);
1837 $this->assertEqual($record->name, '');
1838
3ff8bf26 1839 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 1840 $this->assertEqual(count($records), 1);
1841 $record = reset($records);
1842 $this->assertEqual($record->namenull, '');
1843
3ff8bf26 1844 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 1845 $this->assertEqual(count($records), 1);
1846 $record = reset($records);
1847 $this->assertEqual($record->description, '');
1848
3ff8bf26 1849 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 1850 $this->assertEqual(count($records), 1);
1851 $record = reset($records);
1852 $this->assertEqual($record->descriptionnull, '');
1853 }
1854
1855 function sql_isnotempty() {
1856 //TODO
1857 }
1858
1859 function test_sql_regex() {
6ff835b7 1860 $DB = $this->tdb;
1861 $dbman = $DB->get_manager();
1862
1863 if (!$DB->sql_regex_supported()) {
1864 return;
1865 }
1866
9d833e93 1867 $table = $this->get_test_table();
3ff8bf26 1868 $tablename = $table->getName();
1869
f9ecb171 1870 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1871 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1872 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1873 $dbman->create_table($table);
3ff8bf26 1874 $this->tables[$tablename] = $table;
6ff835b7 1875
3ff8bf26 1876 $DB->insert_record($tablename, array('name'=>'lalala'));
1877 $DB->insert_record($tablename, array('name'=>'holaaa'));
1878 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 1879
3ff8bf26 1880 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 1881 $params = array('a$');
1882 $records = $DB->get_records_sql($sql, $params);
1883 $this->assertEqual(count($records), 2);
1884
3ff8bf26 1885 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 1886 $params = array('.a');
1887 $records = $DB->get_records_sql($sql, $params);
1888 $this->assertEqual(count($records), 1);
1889
1d861fce 1890 }
fb76304b 1891
1892 function test_begin_sql() {
1893 $DB = $this->tdb;
1894 $dbman = $DB->get_manager();
1895
9d833e93 1896 $table = $this->get_test_table();
3ff8bf26 1897 $tablename = $table->getName();
1898
f9ecb171 1899 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1900 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 1901 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1902 $dbman->create_table($table);
3ff8bf26 1903 $this->tables[$tablename] = $table;
fb76304b 1904
1905 $active = $DB->begin_sql();
1906 if ($active) {
1907 // test only if driver supports transactions
1908 $data = (object)array('course'=>3);
3ff8bf26 1909 $DB->insert_record($tablename, $data);
1910 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 1911 $DB->commit_sql();
1912 }
1913 }
1914
1915 function test_commit_sql() {
1916 $DB = $this->tdb;
1917 $dbman = $DB->get_manager();
1918
9d833e93 1919 $table = $this->get_test_table();
3ff8bf26 1920 $tablename = $table->getName();
1921
f9ecb171 1922 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1923 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 1924 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1925 $dbman->create_table($table);
3ff8bf26 1926 $this->tables[$tablename] = $table;
fb76304b 1927
1928 $active = $DB->begin_sql();
1929 if ($active) {
1930 // test only if driver supports transactions
1931 $data = (object)array('course'=>3);
3ff8bf26 1932 $DB->insert_record($tablename, $data);
fb76304b 1933 $DB->commit_sql();
3ff8bf26 1934 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 1935 }
1936 }
1937
1938 function test_rollback_sql() {
1939 $DB = $this->tdb;
1940 $dbman = $DB->get_manager();
1941
9d833e93 1942 $table = $this->get_test_table();
3ff8bf26 1943 $tablename = $table->getName();
1944
f9ecb171 1945 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1946 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 1947 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1948 $dbman->create_table($table);
3ff8bf26 1949 $this->tables[$tablename] = $table;
fb76304b 1950
1951 $active = $DB->begin_sql();
1952 if ($active) {
1953 // test only if driver supports transactions
1954 $data = (object)array('course'=>3);
3ff8bf26 1955 $DB->insert_record($tablename, $data);
fb76304b 1956 $DB->rollback_sql();
3ff8bf26 1957 $this->assertEqual(0, $DB->count_records($tablename));
fb76304b 1958 }
1959 }
7f9f11b5 1960}
1961
1962/**
1963 * This class is not a proper subclass of moodle_database. It is
1964 * intended to be used only in unit tests, in order to gain access to the
1965 * protected methods of moodle_database, and unit test them.
1966 */
1967class moodle_database_for_testing extends moodle_database {
1968 protected $prefix = 'mdl_';
1969
1970 public function public_fix_table_names($sql) {
1971 return $this->fix_table_names($sql);
1972 }
1973
1974 public function driver_installed(){}
1975 public function get_dbfamily(){}
1976 protected function get_dbtype(){}
16a5642c 1977 protected function get_dblibrary(){}
7f9f11b5 1978 public function get_name(){}
37d975e1 1979 public function get_configuration_help(){}
7f9f11b5 1980 public function get_configuration_hints(){}
beaa43db 1981 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 1982 public function get_server_info(){}
1983 protected function allowed_param_types(){}
1984 public function get_last_error(){}
117679db 1985 public function get_tables($usecache=true){}
7f9f11b5 1986 public function get_indexes($table){}
1987 public function get_columns($table, $usecache=true){}
1988 public function set_debug($state){}
1989 public function get_debug(){}
1990 public function set_logging($state){}
1991 public function change_database_structure($sql){}
1992 public function execute($sql, array $params=null){}
1993 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1994 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1995 public function get_fieldset_sql($sql, array $params=null){}
94898738 1996 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 1997 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 1998 public function import_record($table, $dataobject){}
7f9f11b5 1999 public function update_record_raw($table, $params, $bulk=false){}
2000 public function update_record($table, $dataobject, $bulk=false){}
2001 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2002 public function delete_records_select($table, $select, array $params=null){}
2003 public function sql_concat(){}
2004 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2005 public function sql_substr($expr, $start, $length=false){}
bb78c788 2006}