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