MDL-19055 two missed XHTML fixes forgotten in previous commit.
[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 */
66e75f8d 47 private function get_test_table($tablename="") {
9d833e93 48 $dbman = $this->tdb->get_manager();
49
66e75f8d 50 if ($tablename === "") {
51 if (defined('OCI_HACKERY')) {
52 static $i = 0;
53 $tablename = "unit_table".$i++;
54 } else {
55 $tablename = "unit_table";
56 }
57 }
58
1d861fce 59 $table = new xmldb_table($tablename);
60 if ($dbman->table_exists($table)) {
61 $dbman->drop_table($table);
62 }
3ff8bf26 63 return new xmldb_table($tablename);
1d861fce 64 }
65
73f7ad71 66 function test_fix_sql_params() {
7f9f11b5 67 $DB = $this->tdb;
b579f0db 68
9d833e93 69 $table = $this->get_test_table();
70 $tablename = $table->getName();
3ff8bf26 71
334ce026 72 // Correct table placeholder substitution
3ff8bf26 73 $sql = "SELECT * FROM {".$tablename."}";
334ce026 74 $sqlarray = $DB->fix_sql_params($sql);
66e75f8d 75 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
334ce026 76
77 // Conversions of all param types
78 $sql = array();
3ff8bf26 79 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
334ce026 80 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
81 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
82
83 $params = array();
3ff8bf26 84 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
334ce026 85 $params[SQL_PARAMS_QM] = array('first record', 1);
86 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
87
88 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
89 $this->assertEqual($rsql, $sql[$rtype]);
90 $this->assertEqual($rparams, $params[$rtype]);
91
92 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
93 $this->assertEqual($rsql, $sql[$rtype]);
94 $this->assertEqual($rparams, $params[$rtype]);
95
96 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
97 $this->assertEqual($rsql, $sql[$rtype]);
98 $this->assertEqual($rparams, $params[$rtype]);
99
100
73f7ad71 101 // Malformed table placeholder
102 $sql = "SELECT * FROM [testtable]";
e6b4f00e 103 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 104 $this->assertEqual($sql, $sqlarray[0]);
105
73f7ad71 106
73f7ad71 107 // Mixed param types (colon and dollar)
3ff8bf26 108 $sql = "SELECT * FROM {".$tablename."} WHERE name = :param1, course = \$1";
b579f0db 109 $params = array('param1' => 'record1', 'param2' => 3);
73f7ad71 110 try {
e6b4f00e 111 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 112 $this->fail("Expecting an exception, none occurred");
73f7ad71 113 } catch (Exception $e) {
251387d0 114 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 115 }
116
117 // Mixed param types (question and dollar)
3ff8bf26 118 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = \$1";
b579f0db 119 $params = array('param1' => 'record2', 'param2' => 5);
73f7ad71 120 try {
e6b4f00e 121 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 122 $this->fail("Expecting an exception, none occurred");
73f7ad71 123 } catch (Exception $e) {
251387d0 124 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 125 }
73f7ad71 126
127 // Too many params in sql
3ff8bf26 128 $sql = "SELECT * FROM {".$tablename."} WHERE name = ?, course = ?, id = ?";
b579f0db 129 $params = array('record2', 3);
73f7ad71 130 try {
e6b4f00e 131 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 132 $this->fail("Expecting an exception, none occurred");
73f7ad71 133 } catch (Exception $e) {
251387d0 134 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 135 }
73f7ad71 136
137 // Too many params in array: no error
138 $params[] = 1;
139 $params[] = time();
73f7ad71 140 $sqlarray = null;
141
142 try {
e6b4f00e 143 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 144 $this->pass();
73f7ad71 145 } catch (Exception $e) {
6ff835b7 146 $this->fail("Unexpected ".get_class($e)." exception");
73f7ad71 147 }
73f7ad71 148 $this->assertTrue($sqlarray[0]);
149
150 // Named params missing from array
3ff8bf26 151 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :course";
b579f0db 152 $params = array('wrongname' => 'record1', 'course' => 1);
73f7ad71 153 try {
e6b4f00e 154 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 155 $this->fail("Expecting an exception, none occurred");
73f7ad71 156 } catch (Exception $e) {
251387d0 157 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 158 }
73f7ad71 159
160 // Duplicate named param in query
3ff8bf26 161 $sql = "SELECT * FROM {".$tablename."} WHERE name = :name, course = :name";
b579f0db 162 $params = array('name' => 'record2', 'course' => 3);
73f7ad71 163 try {
e6b4f00e 164 $sqlarray = $DB->fix_sql_params($sql, $params);
b579f0db 165 $this->fail("Expecting an exception, none occurred");
73f7ad71 166 } catch (Exception $e) {
251387d0 167 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 168 }
73f7ad71 169
73f7ad71 170 }
171
bb78c788 172 public function testGetTables() {
7f9f11b5 173 $DB = $this->tdb;
251387d0 174 $dbman = $this->tdb->get_manager();
a230012c 175
1d861fce 176 // Need to test with multiple DBs
9d833e93 177 $table = $this->get_test_table();
3ff8bf26 178 $tablename = $table->getName();
1d861fce 179
b579f0db 180 $original_count = count($DB->get_tables());
181
b579f0db 182 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
b579f0db 183 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
184 $dbman->create_table($table);
3ff8bf26 185 $this->tables[$tablename] = $table;
767172e4 186
b579f0db 187 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
bb78c788 188 }
189
767172e4 190 public function testDefaults() {
191 $DB = $this->tdb;
192 $dbman = $this->tdb->get_manager();
193
9d833e93 194 $table = $this->get_test_table();
3ff8bf26 195 $tablename = $table->getName();
196
767172e4 197 $enumvalues = array('test','test2','test3');
198 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, XMLDB_ENUM, $enumvalues, 'test2');
199 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
200 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
201 $dbman->create_table($table);
3ff8bf26 202 $this->tables[$tablename] = $table;
767172e4 203
3ff8bf26 204 $columns = $DB->get_columns($tablename);
767172e4 205
206 $enumfield = $columns['enumfield'];
207 $this->assertEqual('test2', $enumfield->default_value);
208 $this->assertEqual('C', $enumfield->meta_type);
209
210 }
211
bb78c788 212 public function testGetIndexes() {
7f9f11b5 213 $DB = $this->tdb;
251387d0 214 $dbman = $this->tdb->get_manager();
a230012c 215
9d833e93 216 $table = $this->get_test_table();
3ff8bf26 217 $tablename = $table->getName();
218
b579f0db 219 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
220 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
80ffbad3 221 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
b579f0db 222 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
767172e4 223 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
b579f0db 224 $dbman->create_table($table);
3ff8bf26 225 $this->tables[$tablename] = $table;
b579f0db 226
3ff8bf26 227 $this->assertTrue($indices = $DB->get_indexes($tablename));
66e75f8d 228 $this->assertEqual(count($indices), 2);
229 // we do not care about index names for now
80ffbad3 230 $first = array_shift($indices);
231 $second = array_shift($indices);
232 if (count($first['columns']) == 2) {
233 $composed = $first;
234 $single = $second;
235 } else {
236 $composed = $second;
237 $single = $first;
238 }
239 $this->assertFalse($single['unique']);
240 $this->assertTrue($composed['unique']);
241 $this->assertEqual(1, count($single['columns']));
242 $this->assertEqual(2, count($composed['columns']));
243 $this->assertEqual('course', $single['columns'][0]);
244 $this->assertEqual('course', $composed['columns'][0]);
245 $this->assertEqual('id', $composed['columns'][1]);
bb78c788 246 }
247
248 public function testGetColumns() {
7f9f11b5 249 $DB = $this->tdb;
251387d0 250 $dbman = $this->tdb->get_manager();
bb78c788 251
9d833e93 252 $table = $this->get_test_table();
3ff8bf26 253 $tablename = $table->getName();
254
b579f0db 255 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
256 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
257 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
b579f0db 258 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
259 $dbman->create_table($table);
3ff8bf26 260 $this->tables[$tablename] = $table;
b579f0db 261
3ff8bf26 262 $this->assertTrue($columns = $DB->get_columns($tablename));
263 $fields = $this->tables[$tablename]->getFields();
bb78c788 264 $this->assertEqual(count($columns), count($fields));
265
266 for ($i = 0; $i < count($columns); $i++) {
267 if ($i == 0) {
268 $next_column = reset($columns);
269 $next_field = reset($fields);
270 } else {
271 $next_column = next($columns);
272 $next_field = next($fields);
273 }
274
275 $this->assertEqual($next_column->name, $next_field->name);
276 }
277 }
278
279 public function testExecute() {
7f9f11b5 280 $DB = $this->tdb;
251387d0 281 $dbman = $this->tdb->get_manager();
a230012c 282
9d833e93 283 $table = $this->get_test_table();
3ff8bf26 284 $tablename = $table->getName();
285
b579f0db 286 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
287 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
288 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
289 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
290 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
291 $dbman->create_table($table);
3ff8bf26 292 $this->tables[$tablename] = $table;
b579f0db 293
3ff8bf26 294 $sql = "SELECT * FROM {".$tablename."}";
b579f0db 295
bb78c788 296 $this->assertTrue($DB->execute($sql));
73f7ad71 297
b579f0db 298 $params = array('course' => 1, 'name' => 'test');
6807d2b3 299
3ff8bf26 300 $sql = "INSERT INTO {".$tablename."} (".implode(',', array_keys($params)).")
6807d2b3 301 VALUES (".implode(',', array_fill(0, count($params), '?')).")";
302
303
304 $this->assertTrue($DB->execute($sql, $params));
bb78c788 305
3ff8bf26 306 $record = $DB->get_record($tablename, array('id' => 1));
bb78c788 307
6807d2b3 308 foreach ($params as $field => $value) {
bb78c788 309 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
310 }
311 }
312
7f9f11b5 313 public function test_get_in_or_equal() {
314 $DB = $this->tdb;
315
316 // SQL_PARAMS_QM - IN or =
317
318 // Correct usage of multiple values
319 $in_values = array('value1', 'value2', 'value3', 'value4');
320 list($usql, $params) = $DB->get_in_or_equal($in_values);
321 $this->assertEqual("IN (?,?,?,?)", $usql);
322 $this->assertEqual(4, count($params));
323 foreach ($params as $key => $value) {
324 $this->assertEqual($in_values[$key], $value);
325 }
326
327 // Correct usage of single value (in an array)
328 $in_values = array('value1');
329 list($usql, $params) = $DB->get_in_or_equal($in_values);
330 $this->assertEqual("= ?", $usql);
331 $this->assertEqual(1, count($params));
332 $this->assertEqual($in_values[0], $params[0]);
333
334 // Correct usage of single value
335 $in_value = 'value1';
336 list($usql, $params) = $DB->get_in_or_equal($in_values);
337 $this->assertEqual("= ?", $usql);
338 $this->assertEqual(1, count($params));
339 $this->assertEqual($in_value, $params[0]);
340
341 // SQL_PARAMS_QM - NOT IN or <>
342
343 // Correct usage of multiple values
344 $in_values = array('value1', 'value2', 'value3', 'value4');
345 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
346 $this->assertEqual("NOT IN (?,?,?,?)", $usql);
347 $this->assertEqual(4, count($params));
348 foreach ($params as $key => $value) {
349 $this->assertEqual($in_values[$key], $value);
350 }
351
352 // Correct usage of single value (in array()
353 $in_values = array('value1');
354 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
355 $this->assertEqual("<> ?", $usql);
356 $this->assertEqual(1, count($params));
357 $this->assertEqual($in_values[0], $params[0]);
358
359 // Correct usage of single value
360 $in_value = 'value1';
361 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
362 $this->assertEqual("<> ?", $usql);
363 $this->assertEqual(1, count($params));
364 $this->assertEqual($in_value, $params[0]);
365
366 // SQL_PARAMS_NAMED - IN or =
367
368 // Correct usage of multiple values
369 $in_values = array('value1', 'value2', 'value3', 'value4');
370 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
371 $this->assertEqual("IN (:param01,:param02,:param03,:param04)", $usql);
372 $this->assertEqual(4, count($params));
373 reset($in_values);
374 foreach ($params as $key => $value) {
375 $this->assertEqual(current($in_values), $value);
376 next($in_values);
377 }
378
379 // Correct usage of single values (in array)
380 $in_values = array('value1');
381 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
382 $this->assertEqual("= :param01", $usql);
383 $this->assertEqual(1, count($params));
384 $this->assertEqual($in_values[0], $params['param01']);
385
386 // Correct usage of single value
387 $in_value = 'value1';
388 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', true);
389 $this->assertEqual("= :param01", $usql);
390 $this->assertEqual(1, count($params));
391 $this->assertEqual($in_value, $params['param01']);
392
393 // SQL_PARAMS_NAMED - NOT IN or <>
394
395 // Correct usage of multiple values
396 $in_values = array('value1', 'value2', 'value3', 'value4');
397 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
398 $this->assertEqual("NOT IN (:param01,:param02,:param03,:param04)", $usql);
399 $this->assertEqual(4, count($params));
400 reset($in_values);
401 foreach ($params as $key => $value) {
402 $this->assertEqual(current($in_values), $value);
403 next($in_values);
404 }
405
406 // Correct usage of single values (in array)
407 $in_values = array('value1');
408 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
409 $this->assertEqual("<> :param01", $usql);
410 $this->assertEqual(1, count($params));
411 $this->assertEqual($in_values[0], $params['param01']);
412
413 // Correct usage of single value
414 $in_value = 'value1';
415 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param01', false);
416 $this->assertEqual("<> :param01", $usql);
417 $this->assertEqual(1, count($params));
418 $this->assertEqual($in_value, $params['param01']);
419
420 }
421
422 public function test_fix_table_names() {
423 $DB = new moodle_database_for_testing();
424 $prefix = $DB->get_prefix();
425
426 // Simple placeholder
427 $placeholder = "{user}";
6ff835b7 428 $this->assertEqual($prefix."user", $DB->public_fix_table_names($placeholder));
7f9f11b5 429
430 // Full SQL
431 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
432 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
433 $this->assertEqual($expected, $DB->public_fix_table_names($sql));
434
b579f0db 435
436 }
437
438 public function test_get_recordset() {
439 $DB = $this->tdb;
440 $dbman = $DB->get_manager();
441
9d833e93 442 $table = $this->get_test_table();
3ff8bf26 443 $tablename = $table->getName();
444
b579f0db 445 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
446 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
447 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
448 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
449 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
450 $dbman->create_table($table);
3ff8bf26 451 $this->tables[$tablename] = $table;
b579f0db 452
453 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1'),
454 array('id' => 2, 'course' => 3, 'name' => 'record2'),
455 array('id' => 3, 'course' => 5, 'name' => 'record3'));
22d77567 456
b579f0db 457 foreach ($data as $record) {
3ff8bf26 458 $DB->insert_record($tablename, $record);
b579f0db 459 }
460
3ff8bf26 461 $rs = $DB->get_recordset($tablename);
b579f0db 462 $this->assertTrue($rs);
463
464 reset($data);
465 foreach($rs as $record) {
466 $data_record = current($data);
467 foreach ($record as $k => $v) {
468 $this->assertEqual($data_record[$k], $v);
469 }
470 next($data);
471 }
472 $rs->close();
7f9f11b5 473 }
0088bd31 474
b0cb2290 475 public function test_get_recordset_iterator_keys() {
476 $DB = $this->tdb;
477 $dbman = $DB->get_manager();
478
9d833e93 479 $table = $this->get_test_table();
3ff8bf26 480 $tablename = $table->getName();
481
b0cb2290 482 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
483 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
484 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, '0');
485 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
486 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
487 $dbman->create_table($table);
3ff8bf26 488 $this->tables[$tablename] = $table;
b0cb2290 489
22d77567 490 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
491 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
492 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
b0cb2290 493 foreach ($data as $record) {
3ff8bf26 494 $DB->insert_record($tablename, $record);
b0cb2290 495 }
496
497 /// Test repeated numeric keys are returned ok
3ff8bf26 498 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
b0cb2290 499
500 reset($data);
501 $count = 0;
502 foreach($rs as $key => $record) {
503 $data_record = current($data);
504 $this->assertEqual($data_record['course'], $key);
505 next($data);
506 $count++;
507 }
508 $rs->close();
509
510 /// Test record returned are ok
511 $this->assertEqual($count, 3);
512
513 /// Test string keys are returned ok
3ff8bf26 514 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
b0cb2290 515
516 reset($data);
517 $count = 0;
518 foreach($rs as $key => $record) {
519 $data_record = current($data);
520 $this->assertEqual($data_record['name'], $key);
521 next($data);
522 $count++;
523 }
524 $rs->close();
525
526 /// Test record returned are ok
527 $this->assertEqual($count, 3);
528
529 /// Test numeric not starting in 1 keys are returned ok
3ff8bf26 530 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
b0cb2290 531
532 $data = array_reverse($data);
533 reset($data);
534 $count = 0;
535 foreach($rs as $key => $record) {
536 $data_record = current($data);
537 $this->assertEqual($data_record['id'], $key);
538 next($data);
539 $count++;
540 }
541 $rs->close();
542
543 /// Test record returned are ok
544 $this->assertEqual($count, 3);
545 }
546
0088bd31 547 public function test_get_recordset_list() {
548 $DB = $this->tdb;
549 $dbman = $DB->get_manager();
550
9d833e93 551 $table = $this->get_test_table();
3ff8bf26 552 $tablename = $table->getName();
553
0088bd31 554 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
555 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
556 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
557 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
558 $dbman->create_table($table);
3ff8bf26 559 $this->tables[$tablename] = $table;
0088bd31 560
3ff8bf26 561 $DB->insert_record($tablename, array('course' => 3));
562 $DB->insert_record($tablename, array('course' => 3));
563 $DB->insert_record($tablename, array('course' => 5));
564 $DB->insert_record($tablename, array('course' => 2));
0088bd31 565
3ff8bf26 566 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
0088bd31 567
568 $this->assertTrue($rs);
569
570 $counter = 0;
571 foreach ($rs as $record) {
572 $counter++;
573 }
574 $this->assertEqual(3, $counter);
c362878e 575 $rs->close();
576
577 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
578
579 $counter = 0;
580 foreach ($rs as $record) {
581 $counter++;
582 }
583 $this->assertEqual(0, $counter);
584 $rs->close();
0088bd31 585 }
586
587 public function test_get_recordset_select() {
588 $DB = $this->tdb;
589 $dbman = $DB->get_manager();
590
9d833e93 591 $table = $this->get_test_table();
3ff8bf26 592 $tablename = $table->getName();
593
0088bd31 594 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
595 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
596 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
597 $dbman->create_table($table);
3ff8bf26 598 $this->tables[$tablename] = $table;
0088bd31 599
3ff8bf26 600 $DB->insert_record($tablename, array('course' => 3));
601 $DB->insert_record($tablename, array('course' => 3));
602 $DB->insert_record($tablename, array('course' => 5));
603 $DB->insert_record($tablename, array('course' => 2));
0088bd31 604
3ff8bf26 605 $rs = $DB->get_recordset_select($tablename, '');
0088bd31 606 $counter = 0;
607 foreach ($rs as $record) {
608 $counter++;
609 }
610 $this->assertEqual(4, $counter);
611
3ff8bf26 612 $this->assertTrue($rs = $DB->get_recordset_select($tablename, 'course = 3'));
0088bd31 613 $counter = 0;
614 foreach ($rs as $record) {
615 $counter++;
616 }
617 $this->assertEqual(2, $counter);
618 }
619
620 public function test_get_recordset_sql() {
621 $DB = $this->tdb;
622 $dbman = $DB->get_manager();
623
9d833e93 624 $table = $this->get_test_table();
3ff8bf26 625 $tablename = $table->getName();
626
0088bd31 627 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
628 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
629 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
630 $dbman->create_table($table);
3ff8bf26 631 $this->tables[$tablename] = $table;
0088bd31 632
3ff8bf26 633 $DB->insert_record($tablename, array('course' => 3));
634 $DB->insert_record($tablename, array('course' => 3));
635 $DB->insert_record($tablename, array('course' => 5));
636 $DB->insert_record($tablename, array('course' => 2));
0088bd31 637
3ff8bf26 638 $this->assertTrue($rs = $DB->get_recordset_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 639 $counter = 0;
640 foreach ($rs as $record) {
641 $counter++;
642 }
643 $this->assertEqual(2, $counter);
644 }
645
646 public function test_get_records() {
647 $DB = $this->tdb;
648 $dbman = $DB->get_manager();
649
9d833e93 650 $table = $this->get_test_table();
3ff8bf26 651 $tablename = $table->getName();
652
0088bd31 653 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
654 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
655 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
656 $dbman->create_table($table);
3ff8bf26 657 $this->tables[$tablename] = $table;
0088bd31 658
3ff8bf26 659 $DB->insert_record($tablename, array('course' => 3));
660 $DB->insert_record($tablename, array('course' => 3));
661 $DB->insert_record($tablename, array('course' => 5));
662 $DB->insert_record($tablename, array('course' => 2));
0088bd31 663
664 // All records
3ff8bf26 665 $records = $DB->get_records($tablename);
0088bd31 666 $this->assertEqual(4, count($records));
667 $this->assertEqual(3, $records[1]->course);
668 $this->assertEqual(3, $records[2]->course);
669 $this->assertEqual(5, $records[3]->course);
670 $this->assertEqual(2, $records[4]->course);
671
672 // Records matching certain conditions
3ff8bf26 673 $records = $DB->get_records($tablename, array('course' => 3));
0088bd31 674 $this->assertEqual(2, count($records));
675 $this->assertEqual(3, $records[1]->course);
676 $this->assertEqual(3, $records[2]->course);
677
678 // All records sorted by course
3ff8bf26 679 $records = $DB->get_records($tablename, null, 'course');
0088bd31 680 $this->assertEqual(4, count($records));
681 $current_record = reset($records);
682 $this->assertEqual(4, $current_record->id);
683 $current_record = next($records);
684 $this->assertEqual(1, $current_record->id);
685 $current_record = next($records);
686 $this->assertEqual(2, $current_record->id);
687 $current_record = next($records);
688 $this->assertEqual(3, $current_record->id);
689
690 // All records, but get only one field
3ff8bf26 691 $records = $DB->get_records($tablename, null, '', 'id');
0088bd31 692 $this->assertTrue(empty($records[1]->course));
693 $this->assertFalse(empty($records[1]->id));
694 $this->assertEqual(4, count($records));
3ff8bf26 695
696 // test limitfrom and limitnum
697 $records = $DB->get_records($tablename, null, '', 'id', 0, 0);
698 $this->assertEqual(4, count($records));
699 $records = $DB->get_records($tablename, null, '', 'id', -1, -1);
700 $this->assertEqual(4, count($records));
701 $records = $DB->get_records($tablename, null, '', 'id', 2, 2);
702 $this->assertEqual(2, count($records));
703 $first = reset($records);
704 $last = array_pop($records);
705 $this->assertEqual(3, $first->id);
706 $this->assertEqual(4, $last->id);
707
708// TODO: more LIMIT tests
0088bd31 709 }
710
711 public function test_get_records_list() {
50a12c87 712 $DB = $this->tdb;
713 $dbman = $DB->get_manager();
714
9d833e93 715 $table = $this->get_test_table();
3ff8bf26 716 $tablename = $table->getName();
717
50a12c87 718 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
719 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
720 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
721 $dbman->create_table($table);
3ff8bf26 722 $this->tables[$tablename] = $table;
50a12c87 723
3ff8bf26 724 $DB->insert_record($tablename, array('course' => 3));
725 $DB->insert_record($tablename, array('course' => 3));
726 $DB->insert_record($tablename, array('course' => 5));
727 $DB->insert_record($tablename, array('course' => 2));
50a12c87 728
3ff8bf26 729 $this->assertTrue($records = $DB->get_records_list($tablename, 'course', array(3, 2)));
50a12c87 730 $this->assertEqual(3, count($records));
731 $this->assertEqual(1, reset($records)->id);
732 $this->assertEqual(2, next($records)->id);
733 $this->assertEqual(4, next($records)->id);
0088bd31 734
c362878e 735 $this->assertIdentical(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
736 $this->assertEqual(0, count($records));
737
0088bd31 738 }
739
740 public function test_get_records_sql() {
50a12c87 741 $DB = $this->tdb;
742 $dbman = $DB->get_manager();
743
9d833e93 744 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 776 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 803 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 834 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 865 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 886 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 908 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 930 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 949 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 969 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 989 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1016 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1042 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1060 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1079 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1106 $table = $this->get_test_table();
3ff8bf26 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
9d833e93 1130 $table = $this->get_test_table();
3ff8bf26 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)));
66e75f8d 1149
c824e1e1 1150 }
1151
1152
0088bd31 1153 public function test_update_record_raw() {
50a12c87 1154 $DB = $this->tdb;
1155 $dbman = $DB->get_manager();
0088bd31 1156
9d833e93 1157 $table = $this->get_test_table();
3ff8bf26 1158 $tablename = $table->getName();
1159
50a12c87 1160 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1161 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1162 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1163 $dbman->create_table($table);
3ff8bf26 1164 $this->tables[$tablename] = $table;
50a12c87 1165
3ff8bf26 1166 $DB->insert_record($tablename, array('course' => 1));
1167 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1168 $record->course = 2;
3ff8bf26 1169 $this->assertTrue($DB->update_record_raw($tablename, $record));
1170 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1171 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1172 }
1173
1174 public function test_update_record() {
50a12c87 1175 $DB = $this->tdb;
1176 $dbman = $DB->get_manager();
1177
9d833e93 1178 $table = $this->get_test_table();
3ff8bf26 1179 $tablename = $table->getName();
1180
50a12c87 1181 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1182 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1183 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1184 $dbman->create_table($table);
3ff8bf26 1185 $this->tables[$tablename] = $table;
0088bd31 1186
3ff8bf26 1187 $DB->insert_record($tablename, array('course' => 1));
1188 $record = $DB->get_record($tablename, array('course' => 1));
50a12c87 1189 $record->course = 2;
3ff8bf26 1190 $this->assertTrue($DB->update_record($tablename, $record));
1191 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
1192 $this->assertTrue($record = $DB->get_record($tablename, array('course' => 2)));
0088bd31 1193 }
1194
c824e1e1 1195 public function test_update_record_clob() {
1196 global $CFG;
1197
1198 $DB = $this->tdb;
1199 $dbman = $DB->get_manager();
1200
9d833e93 1201 $table = $this->get_test_table();
3ff8bf26 1202 $tablename = $table->getName();
1203
c824e1e1 1204 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1205 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1206 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1207 $dbman->create_table($table);
3ff8bf26 1208 $this->tables[$tablename] = $table;
c824e1e1 1209
6ff835b7 1210 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
c824e1e1 1211
3ff8bf26 1212 $id = $DB->insert_record($tablename, array('description' => $clob));
1213 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1214 $record->description = substr($clob, 0, 500);
3ff8bf26 1215 $this->assertTrue($DB->update_record($tablename, $record));
c824e1e1 1216
3ff8bf26 1217 $record = $DB->get_record($tablename, array('id' => $id));
c824e1e1 1218 $this->assertEqual(substr($clob, 0, 500), $record->description);
1219 }
1220
9d833e93 1221 public function test_update_record_multiple_lobs() {
c824e1e1 1222 global $CFG;
1223
1224 $DB = $this->tdb;
1225 $dbman = $DB->get_manager();
1226
9d833e93 1227 $table = $this->get_test_table();
3ff8bf26 1228 $tablename = $table->getName();
1229
c824e1e1 1230 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1231 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1232 $table->add_field('image', XMLDB_TYPE_BINARY, 'big', null, null, null, null, null, null);
1233 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1234 $dbman->create_table($table);
3ff8bf26 1235 $this->tables[$tablename] = $table;
c824e1e1 1236
6ff835b7 1237 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1238 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
c824e1e1 1239
d246cdd2 1240 $newclob = substr($clob, 0, 500);
1241 $newblob = substr($blob, 0, 250);
1242
3ff8bf26 1243 $id = $DB->insert_record($tablename, array('description' => $clob, 'image' => $blob));
1244 $record = $DB->get_record($tablename, array('id' => $id));
d246cdd2 1245 $record->description = $newclob;
1246 $record->image = $newblob;
3ff8bf26 1247 $this->assertTrue($DB->update_record($tablename, $record));
c824e1e1 1248
3ff8bf26 1249 $record = $DB->get_record($tablename, array('id' => $id));
d246cdd2 1250 $this->assertEqual($newclob, $record->description);
1251 $this->assertEqual($newblob, $record->image);
3ff8bf26 1252 $this->assertEqual($newclob, $DB->get_field($tablename, 'description', array('id' => $id)));
1253 $this->assertEqual($newblob, $DB->get_field($tablename, 'image', array('id' => $id)));
c824e1e1 1254
1255 }
1256
0088bd31 1257 public function test_set_field() {
50a12c87 1258 $DB = $this->tdb;
1259 $dbman = $DB->get_manager();
1260
9d833e93 1261 $table = $this->get_test_table();
3ff8bf26 1262 $tablename = $table->getName();
1263
50a12c87 1264 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1265 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1266 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1267 $dbman->create_table($table);
3ff8bf26 1268 $this->tables[$tablename] = $table;
0088bd31 1269
3ff8bf26 1270 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1271
3ff8bf26 1272 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => 1)));
1273 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1274 }
1275
1276 public function test_set_field_select() {
50a12c87 1277 $DB = $this->tdb;
1278 $dbman = $DB->get_manager();
1279
9d833e93 1280 $table = $this->get_test_table();
3ff8bf26 1281 $tablename = $table->getName();
1282
50a12c87 1283 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1284 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1285 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1286 $dbman->create_table($table);
3ff8bf26 1287 $this->tables[$tablename] = $table;
50a12c87 1288
3ff8bf26 1289 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1290
3ff8bf26 1291 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1292 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1293
1294 }
1295
1296 public function test_count_records() {
1297 $DB = $this->tdb;
1298
1299 $dbman = $DB->get_manager();
1300
9d833e93 1301 $table = $this->get_test_table();
3ff8bf26 1302 $tablename = $table->getName();
1303
0088bd31 1304 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1305 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1306 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1307 $dbman->create_table($table);
3ff8bf26 1308 $this->tables[$tablename] = $table;
0088bd31 1309
3ff8bf26 1310 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1311
3ff8bf26 1312 $DB->insert_record($tablename, array('course' => 3));
1313 $DB->insert_record($tablename, array('course' => 4));
1314 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1315
3ff8bf26 1316 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1317 }
1318
1319 public function test_count_records_select() {
50a12c87 1320 $DB = $this->tdb;
0088bd31 1321
50a12c87 1322 $dbman = $DB->get_manager();
1323
9d833e93 1324 $table = $this->get_test_table();
3ff8bf26 1325 $tablename = $table->getName();
1326
50a12c87 1327 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1328 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1329 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1330 $dbman->create_table($table);
3ff8bf26 1331 $this->tables[$tablename] = $table;
50a12c87 1332
3ff8bf26 1333 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1334
3ff8bf26 1335 $DB->insert_record($tablename, array('course' => 3));
1336 $DB->insert_record($tablename, array('course' => 4));
1337 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1338
3ff8bf26 1339 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1340 }
1341
1342 public function test_count_records_sql() {
50a12c87 1343 $DB = $this->tdb;
1344 $dbman = $DB->get_manager();
1345
9d833e93 1346 $table = $this->get_test_table();
3ff8bf26 1347 $tablename = $table->getName();
1348
50a12c87 1349 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1350 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1351 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1352 $dbman->create_table($table);
3ff8bf26 1353 $this->tables[$tablename] = $table;
50a12c87 1354
3ff8bf26 1355 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1356
3ff8bf26 1357 $DB->insert_record($tablename, array('course' => 3));
1358 $DB->insert_record($tablename, array('course' => 4));
1359 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1360
3ff8bf26 1361 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1362 }
1363
1364 public function test_record_exists() {
50a12c87 1365 $DB = $this->tdb;
1366 $dbman = $DB->get_manager();
1367
9d833e93 1368 $table = $this->get_test_table();
3ff8bf26 1369 $tablename = $table->getName();
1370
50a12c87 1371 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1372 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1373 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1374 $dbman->create_table($table);
3ff8bf26 1375 $this->tables[$tablename] = $table;
50a12c87 1376
3ff8bf26 1377 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1378
3ff8bf26 1379 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1380 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1381
3ff8bf26 1382 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1383
1384 }
1385
1386 public function test_record_exists_select() {
50a12c87 1387 $DB = $this->tdb;
1388 $dbman = $DB->get_manager();
0088bd31 1389
9d833e93 1390 $table = $this->get_test_table();
3ff8bf26 1391 $tablename = $table->getName();
1392
50a12c87 1393 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1394 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1395 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1396 $dbman->create_table($table);
3ff8bf26 1397 $this->tables[$tablename] = $table;
50a12c87 1398
3ff8bf26 1399 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1400
3ff8bf26 1401 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1402 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1403
3ff8bf26 1404 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1405 }
1406
1407 public function test_record_exists_sql() {
50a12c87 1408 $DB = $this->tdb;
1409 $dbman = $DB->get_manager();
0088bd31 1410
9d833e93 1411 $table = $this->get_test_table();
3ff8bf26 1412 $tablename = $table->getName();
1413
50a12c87 1414 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1415 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1416 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1417 $dbman->create_table($table);
3ff8bf26 1418 $this->tables[$tablename] = $table;
50a12c87 1419
3ff8bf26 1420 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1421
3ff8bf26 1422 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1423 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1424
3ff8bf26 1425 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1426 }
1427
1428 public function test_delete_records() {
50a12c87 1429 $DB = $this->tdb;
1430 $dbman = $DB->get_manager();
0088bd31 1431
9d833e93 1432 $table = $this->get_test_table();
3ff8bf26 1433 $tablename = $table->getName();
1434
50a12c87 1435 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1436 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1437 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1438 $dbman->create_table($table);
3ff8bf26 1439 $this->tables[$tablename] = $table;
50a12c87 1440
3ff8bf26 1441 $DB->insert_record($tablename, array('course' => 3));
1442 $DB->insert_record($tablename, array('course' => 2));
1443 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1444
1445 // Delete all records
3ff8bf26 1446 $this->assertTrue($DB->delete_records($tablename));
1447 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1448
1449 // Delete subset of records
3ff8bf26 1450 $DB->insert_record($tablename, array('course' => 3));
1451 $DB->insert_record($tablename, array('course' => 2));
1452 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1453
3ff8bf26 1454 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1455 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1456 }
1457
1458 public function test_delete_records_select() {
50a12c87 1459 $DB = $this->tdb;
1460 $dbman = $DB->get_manager();
0088bd31 1461
9d833e93 1462 $table = $this->get_test_table();
3ff8bf26 1463 $tablename = $table->getName();
1464
50a12c87 1465 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1466 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1467 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1468 $dbman->create_table($table);
3ff8bf26 1469 $this->tables[$tablename] = $table;
0088bd31 1470
3ff8bf26 1471 $DB->insert_record($tablename, array('course' => 3));
1472 $DB->insert_record($tablename, array('course' => 2));
1473 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1474
3ff8bf26 1475 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1476 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1477 }
1d861fce 1478
c362878e 1479 public function test_delete_records_list() {
1480 $DB = $this->tdb;
1481 $dbman = $DB->get_manager();
1482
1483 $table = $this->get_test_table();
1484 $tablename = $table->getName();
1485
1486 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1487 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1488 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1489 $dbman->create_table($table);
1490 $this->tables[$tablename] = $table;
1491
1492 $DB->insert_record($tablename, array('course' => 1));
1493 $DB->insert_record($tablename, array('course' => 2));
1494 $DB->insert_record($tablename, array('course' => 3));
1495
1496 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1497 $this->assertEqual(1, $DB->count_records($tablename));
1498
1499 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1500 $this->assertEqual(1, $DB->count_records($tablename));
1501 }
1502
082ae821 1503 function test_sql_null_from_clause() {
655bbf51 1504 $DB = $this->tdb;
082ae821 1505 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1506 $this->assertEqual($DB->get_field_sql($sql), 1);
1507 }
655bbf51 1508
082ae821 1509 function test_sql_bitand() {
1510 $DB = $this->tdb;
9d833e93 1511 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1512 $this->assertEqual($DB->get_field_sql($sql), 2);
1513 }
655bbf51 1514
082ae821 1515 function test_sql_bitnot() {
1516 $DB = $this->tdb;
655bbf51 1517
082ae821 1518 $not = $DB->sql_bitnot(2);
1519 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1520
9d833e93 1521 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1522 $this->assertEqual($DB->get_field_sql($sql), 5);
1523 }
655bbf51 1524
082ae821 1525 function test_sql_bitor() {
1526 $DB = $this->tdb;
9d833e93 1527 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1528 $this->assertEqual($DB->get_field_sql($sql), 11);
1529 }
655bbf51 1530
082ae821 1531 function test_sql_bitxor() {
1532 $DB = $this->tdb;
9d833e93 1533 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1534 $this->assertEqual($DB->get_field_sql($sql), 9);
1535 }
1536
e6df3734 1537 function test_sql_modulo() {
1538 $DB = $this->tdb;
9d833e93 1539 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1540 $this->assertEqual($DB->get_field_sql($sql), 3);
1541 }
1542
082ae821 1543 function test_sql_ceil() {
1544 $DB = $this->tdb;
9d833e93 1545 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1546 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1547 }
1548
6ff835b7 1549 function test_cast_char2int() {
1550 $DB = $this->tdb;
1551 $dbman = $DB->get_manager();
1552
9d833e93 1553 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1554 $tablename1 = $table1->getName();
1555
6ff835b7 1556 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1557 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1558 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1559 $dbman->create_table($table1);
3ff8bf26 1560 $this->tables[$tablename1] = $table1;
6ff835b7 1561
3ff8bf26 1562 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1563
9d833e93 1564 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1565 $tablename2 = $table2->getName();
6ff835b7 1566 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
9d833e93 1567 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
6ff835b7 1568 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1569 $dbman->create_table($table2);
1570 $this->tables[$table2->getName()] = $table2;
1571
9d833e93 1572 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 1573
1574 try {
9d833e93 1575 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 1576 $records = $DB->get_records_sql($sql);
1577 $this->assertEqual(count($records), 1);
1578 } catch (dml_exception $e) {
1579 $this->fail("No exception expected");
1580 }
1581 }
1582
1583 function test_cast_char2real() {
1584 $DB = $this->tdb;
1585 $dbman = $DB->get_manager();
1586
9d833e93 1587 $table = $this->get_test_table();
3ff8bf26 1588 $tablename = $table->getName();
1589
6ff835b7 1590 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1591 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
9d833e93 1592 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null, null, null);
6ff835b7 1593 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1594 $dbman->create_table($table);
3ff8bf26 1595 $this->tables[$tablename] = $table;
6ff835b7 1596
9d833e93 1597 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
1598 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
1599 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 1600
9d833e93 1601 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 1602 $records = $DB->get_records_sql($sql);
1603 $this->assertEqual(count($records), 2);
1604 }
1605
082ae821 1606 function sql_compare_text() {
1607 $DB = $this->tdb;
1608 $dbman = $DB->get_manager();
1609
9d833e93 1610 $table = $this->get_test_table();
3ff8bf26 1611 $tablename = $table->getName();
1612
082ae821 1613 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1614 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1615 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1616 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1617 $dbman->create_table($table);
3ff8bf26 1618 $this->tables[$tablename] = $table;
082ae821 1619
3ff8bf26 1620 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
1621 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
1622 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 1623
3ff8bf26 1624 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 1625 $records = $DB->get_records_sql($sql);
1626 $this->assertEqual(count($records), 1);
1627
3ff8bf26 1628 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 1629 $records = $DB->get_records_sql($sql);
1630 $this->assertEqual(count($records), 2);
1631 }
1632
6ff835b7 1633 function test_ilike() {
1634 $DB = $this->tdb;
1635 $dbman = $DB->get_manager();
1636
9d833e93 1637 $table = $this->get_test_table();
3ff8bf26 1638 $tablename = $table->getName();
1639
6ff835b7 1640 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1641 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1642 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1643 $dbman->create_table($table);
3ff8bf26 1644 $this->tables[$tablename] = $table;
6ff835b7 1645
3ff8bf26 1646 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
1647 $DB->insert_record($tablename, array('name'=>'NoDupor'));
1648 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 1649
3ff8bf26 1650 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 1651 $params = array("%dup_r%");
1652 $records = $DB->get_records_sql($sql, $params);
1653 $this->assertEqual(count($records), 2);
1654 }
1655
1656 function test_concat() {
1657 $DB = $this->tdb;
082ae821 1658 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ".$DB->sql_null_from_clause();
1659 $params = array("name", "name2", "name3");
1660 $this->assertEqual("namename2name3", $DB->get_field_sql($sql, $params));
6ff835b7 1661 }
1662
082ae821 1663 function test_concat_join() {
6ff835b7 1664 $DB = $this->tdb;
082ae821 1665 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
1666 $params = array("name", "name2", "name3");
1667 $result = $DB->get_field_sql($sql, $params);
1668 $this->assertEqual("name name2 name3", $result);
1669 }
1670
1671 function test_sql_fullname() {
1672 $DB = $this->tdb;
1673 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
1674 $params = array('first'=>'Firstname', 'last'=>'Surname');
1675 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
1676 }
1677
1678 function sql_sql_order_by_text() {
1679 $DB = $this->tdb;
1680 $dbman = $DB->get_manager();
1681
9d833e93 1682 $table = $this->get_test_table();
3ff8bf26 1683 $tablename = $table->getName();
1684
082ae821 1685 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1686 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1687 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1688 $dbman->create_table($table);
3ff8bf26 1689 $this->tables[$tablename] = $table;
082ae821 1690
3ff8bf26 1691 $DB->insert_record($tablename, array('description'=>'abcd'));
1692 $DB->insert_record($tablename, array('description'=>'dxxx'));
1693 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 1694
3ff8bf26 1695 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 1696 $records = $DB->get_records_sql($sql);
1697 $first = array_unshift($records);
1698 $this->assertEqual(1, $first->id);
1699 $second = array_unshift($records);
1700 $this->assertEqual(3, $second->id);
1701 $last = array_unshift($records);
1702 $this->assertEqual(2, $last->id);
1703 }
1704
1705 function test_sql_substring() {
1706 $DB = $this->tdb;
1707 $dbman = $DB->get_manager();
1708
9d833e93 1709 $table = $this->get_test_table();
3ff8bf26 1710 $tablename = $table->getName();
1711
082ae821 1712 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1713 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1714 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1715 $dbman->create_table($table);
3ff8bf26 1716 $this->tables[$tablename] = $table;
082ae821 1717
1718 $string = 'abcdefghij';
1719
3ff8bf26 1720 $DB->insert_record($tablename, array('name'=>$string));
082ae821 1721
3ff8bf26 1722 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 1723 $record = $DB->get_record_sql($sql);
1724 $this->assertEqual(substr($string, 5-1), $record->name);
1725
3ff8bf26 1726 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 1727 $record = $DB->get_record_sql($sql);
1728 $this->assertEqual(substr($string, 5-1, 2), $record->name);
1729
1730 try {
1731 // silence php warning ;-)
1732 @$DB->sql_substr("name");
1733 $this->fail("Expecting an exception, none occurred");
1734 } catch (Exception $e) {
1735 $this->assertTrue($e instanceof coding_exception);
1736 }
6ff835b7 1737 }
1738
37d975e1 1739 function test_sql_length() {
1740 $DB = $this->tdb;
1741 $this->assertEqual($DB->get_field_sql(
1742 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
1743 $this->assertEqual($DB->get_field_sql(
1744 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
1745 }
1746
1d861fce 1747 function test_sql_position() {
1748 $DB = $this->tdb;
1749 $this->assertEqual($DB->get_field_sql(
6ff835b7 1750 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 1751 $this->assertEqual($DB->get_field_sql(
6ff835b7 1752 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
1753 }
1754
082ae821 1755 function test_sql_empty() {
1756 $DB = $this->tdb;
1757 $dbman = $DB->get_manager();
1758
9d833e93 1759 $table = $this->get_test_table();
3ff8bf26 1760 $tablename = $table->getName();
1761
082ae821 1762 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1763 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1764 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1765 $dbman->create_table($table);
3ff8bf26 1766 $this->tables[$tablename] = $table;
082ae821 1767
3ff8bf26 1768 $DB->insert_record($tablename, array('name'=>''));
1769 $DB->insert_record($tablename, array('name'=>null));
1770 $DB->insert_record($tablename, array('name'=>'lalalal'));
1771 $DB->insert_record($tablename, array('name'=>0));
082ae821 1772
3ff8bf26 1773 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 1774 $this->assertEqual(count($records), 1);
1775 $record = reset($records);
1776 $this->assertEqual($record->name, '');
1777 }
1778
1779 function test_sql_isempty() {
1780 $DB = $this->tdb;
1781 $dbman = $DB->get_manager();
1782
9d833e93 1783 $table = $this->get_test_table();
3ff8bf26 1784 $tablename = $table->getName();
1785
082ae821 1786 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1787 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null, null);
1788 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1789 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null, null, null);
1790 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null, null, null);
1791 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1792 $dbman->create_table($table);
3ff8bf26 1793 $this->tables[$tablename] = $table;
082ae821 1794
3ff8bf26 1795 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
1796 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
1797 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
1798 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 1799
3ff8bf26 1800 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 1801 $this->assertEqual(count($records), 1);
1802 $record = reset($records);
1803 $this->assertEqual($record->name, '');
1804
3ff8bf26 1805 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 1806 $this->assertEqual(count($records), 1);
1807 $record = reset($records);
1808 $this->assertEqual($record->namenull, '');
1809
3ff8bf26 1810 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 1811 $this->assertEqual(count($records), 1);
1812 $record = reset($records);
1813 $this->assertEqual($record->description, '');
1814
3ff8bf26 1815 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 1816 $this->assertEqual(count($records), 1);
1817 $record = reset($records);
1818 $this->assertEqual($record->descriptionnull, '');
1819 }
1820
1821 function sql_isnotempty() {
1822 //TODO
1823 }
1824
1825 function test_sql_regex() {
6ff835b7 1826 $DB = $this->tdb;
1827 $dbman = $DB->get_manager();
1828
1829 if (!$DB->sql_regex_supported()) {
1830 return;
1831 }
1832
9d833e93 1833 $table = $this->get_test_table();
3ff8bf26 1834 $tablename = $table->getName();
1835
6ff835b7 1836 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1837 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null, null, null);
1838 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1839 $dbman->create_table($table);
3ff8bf26 1840 $this->tables[$tablename] = $table;
6ff835b7 1841
3ff8bf26 1842 $DB->insert_record($tablename, array('name'=>'lalala'));
1843 $DB->insert_record($tablename, array('name'=>'holaaa'));
1844 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 1845
3ff8bf26 1846 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 1847 $params = array('a$');
1848 $records = $DB->get_records_sql($sql, $params);
1849 $this->assertEqual(count($records), 2);
1850
3ff8bf26 1851 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 1852 $params = array('.a');
1853 $records = $DB->get_records_sql($sql, $params);
1854 $this->assertEqual(count($records), 1);
1855
1d861fce 1856 }
fb76304b 1857
1858 function test_begin_sql() {
1859 $DB = $this->tdb;
1860 $dbman = $DB->get_manager();
1861
9d833e93 1862 $table = $this->get_test_table();
3ff8bf26 1863 $tablename = $table->getName();
1864
fb76304b 1865 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1866 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1867 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1868 $dbman->create_table($table);
3ff8bf26 1869 $this->tables[$tablename] = $table;
fb76304b 1870
1871 $active = $DB->begin_sql();
1872 if ($active) {
1873 // test only if driver supports transactions
1874 $data = (object)array('course'=>3);
3ff8bf26 1875 $DB->insert_record($tablename, $data);
1876 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 1877 $DB->commit_sql();
1878 }
1879 }
1880
1881 function test_commit_sql() {
1882 $DB = $this->tdb;
1883 $dbman = $DB->get_manager();
1884
9d833e93 1885 $table = $this->get_test_table();
3ff8bf26 1886 $tablename = $table->getName();
1887
fb76304b 1888 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1889 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1890 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1891 $dbman->create_table($table);
3ff8bf26 1892 $this->tables[$tablename] = $table;
fb76304b 1893
1894 $active = $DB->begin_sql();
1895 if ($active) {
1896 // test only if driver supports transactions
1897 $data = (object)array('course'=>3);
3ff8bf26 1898 $DB->insert_record($tablename, $data);
fb76304b 1899 $DB->commit_sql();
3ff8bf26 1900 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 1901 }
1902 }
1903
1904 function test_rollback_sql() {
1905 $DB = $this->tdb;
1906 $dbman = $DB->get_manager();
1907
9d833e93 1908 $table = $this->get_test_table();
3ff8bf26 1909 $tablename = $table->getName();
1910
fb76304b 1911 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1912 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
1913 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1914 $dbman->create_table($table);
3ff8bf26 1915 $this->tables[$tablename] = $table;
fb76304b 1916
1917 $active = $DB->begin_sql();
1918 if ($active) {
1919 // test only if driver supports transactions
1920 $data = (object)array('course'=>3);
3ff8bf26 1921 $DB->insert_record($tablename, $data);
fb76304b 1922 $DB->rollback_sql();
3ff8bf26 1923 $this->assertEqual(0, $DB->count_records($tablename));
fb76304b 1924 }
1925 }
7f9f11b5 1926}
1927
1928/**
1929 * This class is not a proper subclass of moodle_database. It is
1930 * intended to be used only in unit tests, in order to gain access to the
1931 * protected methods of moodle_database, and unit test them.
1932 */
1933class moodle_database_for_testing extends moodle_database {
1934 protected $prefix = 'mdl_';
1935
1936 public function public_fix_table_names($sql) {
1937 return $this->fix_table_names($sql);
1938 }
1939
1940 public function driver_installed(){}
1941 public function get_dbfamily(){}
1942 protected function get_dbtype(){}
16a5642c 1943 protected function get_dblibrary(){}
7f9f11b5 1944 public function get_name(){}
37d975e1 1945 public function get_configuration_help(){}
7f9f11b5 1946 public function get_configuration_hints(){}
beaa43db 1947 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 1948 public function get_server_info(){}
1949 protected function allowed_param_types(){}
1950 public function get_last_error(){}
117679db 1951 public function get_tables($usecache=true){}
7f9f11b5 1952 public function get_indexes($table){}
1953 public function get_columns($table, $usecache=true){}
1954 public function set_debug($state){}
1955 public function get_debug(){}
1956 public function set_logging($state){}
1957 public function change_database_structure($sql){}
1958 public function execute($sql, array $params=null){}
1959 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1960 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
1961 public function get_fieldset_sql($sql, array $params=null){}
94898738 1962 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 1963 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 1964 public function import_record($table, $dataobject){}
7f9f11b5 1965 public function update_record_raw($table, $params, $bulk=false){}
1966 public function update_record($table, $dataobject, $bulk=false){}
1967 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
1968 public function delete_records_select($table, $select, array $params=null){}
1969 public function sql_concat(){}
1970 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 1971 public function sql_substr($expr, $start, $length=false){}
bb78c788 1972}