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