Fix storing onf NULLs by set_field_xxx() into binary columns.
[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)));
fbabe35a 1307
1308 // Note: All the nulls, booleans, empties, quoted and backslashes tests
1309 // go to set_field_select() because set_field() is just one wrapper over it
0088bd31 1310 }
1311
1312 public function test_set_field_select() {
fbabe35a 1313 global $CFG;
1314
50a12c87 1315 $DB = $this->tdb;
1316 $dbman = $DB->get_manager();
1317
9d833e93 1318 $table = $this->get_test_table();
3ff8bf26 1319 $tablename = $table->getName();
1320
f9ecb171 1321 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1322 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fbabe35a 1323 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
1324 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
1325 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
1326 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1327 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
50a12c87 1328 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1329 $dbman->create_table($table);
3ff8bf26 1330 $this->tables[$tablename] = $table;
50a12c87 1331
3ff8bf26 1332 $DB->insert_record($tablename, array('course' => 1));
50a12c87 1333
3ff8bf26 1334 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
1335 $this->assertEqual(2, $DB->get_field($tablename, 'course', array('id' => 1)));
0088bd31 1336
fbabe35a 1337 // Check nulls are set properly for all types
1338 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
1339 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
1340 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
1341 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
1342 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
1343
1344 // Check zeros are set properly for all types
1345 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
1346 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
1347 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1348 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1349
1350 // Check booleans are set properly for all types
1351 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
1352 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
1353 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
1354 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
1355 $this->assertEqual(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1356 $this->assertEqual(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1357 $this->assertEqual(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1358 $this->assertEqual(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1359
1360 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
1361 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
1362 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
1363 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
1364 $this->assertEqual(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1365 $this->assertEqual(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1366 $this->assertEqual(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1367 $this->assertEqual(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1368
1369 // Check string data causes exception in numeric types
1370 try {
1371 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
1372 $this->fail("Expecting an exception, none occurred");
1373 } catch (exception $e) {
1374 $this->assertTrue($e instanceof dml_exception);
1375 }
1376 try {
1377 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
1378 $this->fail("Expecting an exception, none occurred");
1379 } catch (exception $e) {
1380 $this->assertTrue($e instanceof dml_exception);
1381 }
1382
1383 // Check empty strings are set properly in string types
1384 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
1385 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
1386 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
1387 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
1388
1389 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1390 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1391 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
1392 $this->assertEqual(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
1393 $this->assertEqual(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
1394
1395 // Check various quotes/backslashes combinations in string types
1396 $teststrings = array(
1397 'backslashes and quotes alone (even): "" \'\' \\\\',
1398 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1399 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1400 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1401 foreach ($teststrings as $teststring) {
1402 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
1403 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
1404 $this->assertEqual($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
1405 $this->assertEqual($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1406 }
1407
1408 // Check LOBs in text/binary columns
1409 $clob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/clob.txt');
1410 $blob = file_get_contents($CFG->libdir.'/dml/simpletest/fixtures/randombinary');
1411 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
1412 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
1413 $this->assertEqual($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1414 $this->assertEqual($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
1415
1416 // And "small" LOBs too, just in case
1417 $newclob = substr($clob, 0, 500);
1418 $newblob = substr($blob, 0, 250);
1419 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
1420 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
1421 $this->assertEqual($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)));
1422 $this->assertEqual($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)));
0088bd31 1423 }
1424
1425 public function test_count_records() {
1426 $DB = $this->tdb;
1427
1428 $dbman = $DB->get_manager();
1429
9d833e93 1430 $table = $this->get_test_table();
3ff8bf26 1431 $tablename = $table->getName();
1432
f9ecb171 1433 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1434 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
0088bd31 1435 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1436 $dbman->create_table($table);
3ff8bf26 1437 $this->tables[$tablename] = $table;
0088bd31 1438
3ff8bf26 1439 $this->assertEqual(0, $DB->count_records($tablename));
0088bd31 1440
3ff8bf26 1441 $DB->insert_record($tablename, array('course' => 3));
1442 $DB->insert_record($tablename, array('course' => 4));
1443 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1444
3ff8bf26 1445 $this->assertEqual(3, $DB->count_records($tablename));
0088bd31 1446 }
1447
1448 public function test_count_records_select() {
50a12c87 1449 $DB = $this->tdb;
0088bd31 1450
50a12c87 1451 $dbman = $DB->get_manager();
1452
9d833e93 1453 $table = $this->get_test_table();
3ff8bf26 1454 $tablename = $table->getName();
1455
f9ecb171 1456 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1457 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1458 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1459 $dbman->create_table($table);
3ff8bf26 1460 $this->tables[$tablename] = $table;
50a12c87 1461
3ff8bf26 1462 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1463
3ff8bf26 1464 $DB->insert_record($tablename, array('course' => 3));
1465 $DB->insert_record($tablename, array('course' => 4));
1466 $DB->insert_record($tablename, array('course' => 5));
50a12c87 1467
3ff8bf26 1468 $this->assertEqual(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
0088bd31 1469 }
1470
1471 public function test_count_records_sql() {
50a12c87 1472 $DB = $this->tdb;
1473 $dbman = $DB->get_manager();
1474
9d833e93 1475 $table = $this->get_test_table();
3ff8bf26 1476 $tablename = $table->getName();
1477
f9ecb171 1478 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1479 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1480 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1481 $dbman->create_table($table);
3ff8bf26 1482 $this->tables[$tablename] = $table;
50a12c87 1483
3ff8bf26 1484 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1485
3ff8bf26 1486 $DB->insert_record($tablename, array('course' => 3));
1487 $DB->insert_record($tablename, array('course' => 4));
1488 $DB->insert_record($tablename, array('course' => 5));
0088bd31 1489
3ff8bf26 1490 $this->assertEqual(2, $DB->count_records_sql("SELECT COUNT(*) FROM {".$tablename."} WHERE course > ?", array(3)));
0088bd31 1491 }
1492
1493 public function test_record_exists() {
50a12c87 1494 $DB = $this->tdb;
1495 $dbman = $DB->get_manager();
1496
9d833e93 1497 $table = $this->get_test_table();
3ff8bf26 1498 $tablename = $table->getName();
1499
f9ecb171 1500 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1501 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1502 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1503 $dbman->create_table($table);
3ff8bf26 1504 $this->tables[$tablename] = $table;
50a12c87 1505
3ff8bf26 1506 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1507
3ff8bf26 1508 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
1509 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1510
3ff8bf26 1511 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
0088bd31 1512
1513 }
1514
1515 public function test_record_exists_select() {
50a12c87 1516 $DB = $this->tdb;
1517 $dbman = $DB->get_manager();
0088bd31 1518
9d833e93 1519 $table = $this->get_test_table();
3ff8bf26 1520 $tablename = $table->getName();
1521
f9ecb171 1522 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1523 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1524 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1525 $dbman->create_table($table);
3ff8bf26 1526 $this->tables[$tablename] = $table;
50a12c87 1527
3ff8bf26 1528 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1529
3ff8bf26 1530 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
1531 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1532
3ff8bf26 1533 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
0088bd31 1534 }
1535
1536 public function test_record_exists_sql() {
50a12c87 1537 $DB = $this->tdb;
1538 $dbman = $DB->get_manager();
0088bd31 1539
9d833e93 1540 $table = $this->get_test_table();
3ff8bf26 1541 $tablename = $table->getName();
1542
f9ecb171 1543 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1544 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1545 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1546 $dbman->create_table($table);
3ff8bf26 1547 $this->tables[$tablename] = $table;
50a12c87 1548
3ff8bf26 1549 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1550
3ff8bf26 1551 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
1552 $DB->insert_record($tablename, array('course' => 3));
50a12c87 1553
3ff8bf26 1554 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {".$tablename."} WHERE course = ?", array(3)));
0088bd31 1555 }
1556
1557 public function test_delete_records() {
50a12c87 1558 $DB = $this->tdb;
1559 $dbman = $DB->get_manager();
0088bd31 1560
9d833e93 1561 $table = $this->get_test_table();
3ff8bf26 1562 $tablename = $table->getName();
1563
f9ecb171 1564 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1565 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1566 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1567 $dbman->create_table($table);
3ff8bf26 1568 $this->tables[$tablename] = $table;
50a12c87 1569
3ff8bf26 1570 $DB->insert_record($tablename, array('course' => 3));
1571 $DB->insert_record($tablename, array('course' => 2));
1572 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1573
1574 // Delete all records
3ff8bf26 1575 $this->assertTrue($DB->delete_records($tablename));
1576 $this->assertEqual(0, $DB->count_records($tablename));
50a12c87 1577
1578 // Delete subset of records
3ff8bf26 1579 $DB->insert_record($tablename, array('course' => 3));
1580 $DB->insert_record($tablename, array('course' => 2));
1581 $DB->insert_record($tablename, array('course' => 2));
50a12c87 1582
3ff8bf26 1583 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
1584 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1585 }
1586
1587 public function test_delete_records_select() {
50a12c87 1588 $DB = $this->tdb;
1589 $dbman = $DB->get_manager();
0088bd31 1590
9d833e93 1591 $table = $this->get_test_table();
3ff8bf26 1592 $tablename = $table->getName();
1593
f9ecb171 1594 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1595 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
50a12c87 1596 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1597 $dbman->create_table($table);
3ff8bf26 1598 $this->tables[$tablename] = $table;
0088bd31 1599
3ff8bf26 1600 $DB->insert_record($tablename, array('course' => 3));
1601 $DB->insert_record($tablename, array('course' => 2));
1602 $DB->insert_record($tablename, array('course' => 2));
0088bd31 1603
3ff8bf26 1604 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
1605 $this->assertEqual(1, $DB->count_records($tablename));
0088bd31 1606 }
1d861fce 1607
c362878e 1608 public function test_delete_records_list() {
1609 $DB = $this->tdb;
1610 $dbman = $DB->get_manager();
1611
1612 $table = $this->get_test_table();
1613 $tablename = $table->getName();
1614
f9ecb171 1615 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1616 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
c362878e 1617 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1618 $dbman->create_table($table);
1619 $this->tables[$tablename] = $table;
1620
1621 $DB->insert_record($tablename, array('course' => 1));
1622 $DB->insert_record($tablename, array('course' => 2));
1623 $DB->insert_record($tablename, array('course' => 3));
1624
1625 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
1626 $this->assertEqual(1, $DB->count_records($tablename));
1627
1628 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
1629 $this->assertEqual(1, $DB->count_records($tablename));
1630 }
1631
082ae821 1632 function test_sql_null_from_clause() {
655bbf51 1633 $DB = $this->tdb;
082ae821 1634 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
1635 $this->assertEqual($DB->get_field_sql($sql), 1);
1636 }
655bbf51 1637
082ae821 1638 function test_sql_bitand() {
1639 $DB = $this->tdb;
9d833e93 1640 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1641 $this->assertEqual($DB->get_field_sql($sql), 2);
1642 }
655bbf51 1643
082ae821 1644 function test_sql_bitnot() {
1645 $DB = $this->tdb;
655bbf51 1646
082ae821 1647 $not = $DB->sql_bitnot(2);
1648 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
655bbf51 1649
9d833e93 1650 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
082ae821 1651 $this->assertEqual($DB->get_field_sql($sql), 5);
1652 }
655bbf51 1653
082ae821 1654 function test_sql_bitor() {
1655 $DB = $this->tdb;
9d833e93 1656 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1657 $this->assertEqual($DB->get_field_sql($sql), 11);
1658 }
655bbf51 1659
082ae821 1660 function test_sql_bitxor() {
1661 $DB = $this->tdb;
9d833e93 1662 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
082ae821 1663 $this->assertEqual($DB->get_field_sql($sql), 9);
1664 }
1665
e6df3734 1666 function test_sql_modulo() {
1667 $DB = $this->tdb;
9d833e93 1668 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
e6df3734 1669 $this->assertEqual($DB->get_field_sql($sql), 3);
1670 }
1671
082ae821 1672 function test_sql_ceil() {
1673 $DB = $this->tdb;
9d833e93 1674 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
082ae821 1675 $this->assertEqual($DB->get_field_sql($sql), 666);
655bbf51 1676 }
1677
6ff835b7 1678 function test_cast_char2int() {
1679 $DB = $this->tdb;
1680 $dbman = $DB->get_manager();
1681
9d833e93 1682 $table1 = $this->get_test_table("testtable1");
3ff8bf26 1683 $tablename1 = $table1->getName();
1684
f9ecb171 1685 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1686 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1687 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1688 $dbman->create_table($table1);
3ff8bf26 1689 $this->tables[$tablename1] = $table1;
6ff835b7 1690
3ff8bf26 1691 $DB->insert_record($tablename1, array('name'=>'100'));
6ff835b7 1692
9d833e93 1693 $table2 = $this->get_test_table("testtable2");
3ff8bf26 1694 $tablename2 = $table2->getName();
f9ecb171 1695 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1696 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
6ff835b7 1697 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1698 $dbman->create_table($table2);
1699 $this->tables[$table2->getName()] = $table2;
1700
9d833e93 1701 $DB->insert_record($tablename2, array('res'=>100));
6ff835b7 1702
1703 try {
9d833e93 1704 $sql = "SELECT * FROM {".$tablename1."} t1, {".$tablename2."} t2 WHERE ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
6ff835b7 1705 $records = $DB->get_records_sql($sql);
1706 $this->assertEqual(count($records), 1);
1707 } catch (dml_exception $e) {
1708 $this->fail("No exception expected");
1709 }
1710 }
1711
1712 function test_cast_char2real() {
1713 $DB = $this->tdb;
1714 $dbman = $DB->get_manager();
1715
9d833e93 1716 $table = $this->get_test_table();
3ff8bf26 1717 $tablename = $table->getName();
1718
f9ecb171 1719 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1720 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1721 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
6ff835b7 1722 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1723 $dbman->create_table($table);
3ff8bf26 1724 $this->tables[$tablename] = $table;
6ff835b7 1725
9d833e93 1726 $DB->insert_record($tablename, array('name'=>'10.10', 'res'=>5.1));
1727 $DB->insert_record($tablename, array('name'=>'1.10', 'res'=>666));
1728 $DB->insert_record($tablename, array('name'=>'11.10', 'res'=>0.1));
6ff835b7 1729
9d833e93 1730 $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_cast_char2real('name')." > res";
6ff835b7 1731 $records = $DB->get_records_sql($sql);
1732 $this->assertEqual(count($records), 2);
1733 }
1734
082ae821 1735 function sql_compare_text() {
1736 $DB = $this->tdb;
1737 $dbman = $DB->get_manager();
1738
9d833e93 1739 $table = $this->get_test_table();
3ff8bf26 1740 $tablename = $table->getName();
1741
f9ecb171 1742 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1743 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1744 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1745 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1746 $dbman->create_table($table);
3ff8bf26 1747 $this->tables[$tablename] = $table;
082ae821 1748
3ff8bf26 1749 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
1750 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
1751 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
082ae821 1752
3ff8bf26 1753 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description');
082ae821 1754 $records = $DB->get_records_sql($sql);
1755 $this->assertEqual(count($records), 1);
1756
3ff8bf26 1757 $sql = "SELECT * FROM {".$tablename."} WHERE name = ".$DB->sql_compare_text('description', 4);
082ae821 1758 $records = $DB->get_records_sql($sql);
1759 $this->assertEqual(count($records), 2);
1760 }
1761
6ff835b7 1762 function test_ilike() {
1763 $DB = $this->tdb;
1764 $dbman = $DB->get_manager();
1765
9d833e93 1766 $table = $this->get_test_table();
3ff8bf26 1767 $tablename = $table->getName();
1768
f9ecb171 1769 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1770 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 1771 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1772 $dbman->create_table($table);
3ff8bf26 1773 $this->tables[$tablename] = $table;
6ff835b7 1774
3ff8bf26 1775 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
1776 $DB->insert_record($tablename, array('name'=>'NoDupor'));
1777 $DB->insert_record($tablename, array('name'=>'ouch'));
6ff835b7 1778
3ff8bf26 1779 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
6ff835b7 1780 $params = array("%dup_r%");
1781 $records = $DB->get_records_sql($sql, $params);
1782 $this->assertEqual(count($records), 2);
1783 }
1784
71a71d59 1785 function test_sql_concat() {
6ff835b7 1786 $DB = $this->tdb;
71a71d59 1787 $dbman = $DB->get_manager();
1788
1789 /// Testing all sort of values
1790 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
1791 // string, some unicode chars
1792 $params = array('name', 'áéíóú', 'name3');
1793 $this->assertEqual('nameáéíóúname3', $DB->get_field_sql($sql, $params));
1794 // string, spaces and numbers
1795 $params = array('name', ' ', 12345);
1796 $this->assertEqual('name 12345', $DB->get_field_sql($sql, $params));
1797 // float, empty and strings
1798 $params = array(123.45, '', 'test');
1799 $this->assertEqual('123.45test', $DB->get_field_sql($sql, $params));
1800 // float, null and strings
1801 $params = array(123.45, null, 'test');
52a01626 1802 $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 1803
1804 /// Testing fieldnames + values
1805 $table = $this->get_test_table();
1806 $tablename = $table->getName();
1807
1808 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1809 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
1810 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1811 $dbman->create_table($table);
1812 $this->tables[$tablename] = $table;
1813
1814 $DB->insert_record($tablename, array('description'=>'áéíóú'));
1815 $DB->insert_record($tablename, array('description'=>'dxxx'));
1816 $DB->insert_record($tablename, array('description'=>'bcde'));
1817
1818 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
1819 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
1820 $this->assertEqual(count($records), 3);
1821 $this->assertEqual($records[1]->result, 'áéíóúharcoded123.45test');
6ff835b7 1822 }
1823
082ae821 1824 function test_concat_join() {
6ff835b7 1825 $DB = $this->tdb;
082ae821 1826 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
1827 $params = array("name", "name2", "name3");
1828 $result = $DB->get_field_sql($sql, $params);
1829 $this->assertEqual("name name2 name3", $result);
1830 }
1831
1832 function test_sql_fullname() {
1833 $DB = $this->tdb;
1834 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
1835 $params = array('first'=>'Firstname', 'last'=>'Surname');
1836 $this->assertEqual("Firstname Surname", $DB->get_field_sql($sql, $params));
1837 }
1838
1839 function sql_sql_order_by_text() {
1840 $DB = $this->tdb;
1841 $dbman = $DB->get_manager();
1842
9d833e93 1843 $table = $this->get_test_table();
3ff8bf26 1844 $tablename = $table->getName();
1845
f9ecb171 1846 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1847 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1848 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1849 $dbman->create_table($table);
3ff8bf26 1850 $this->tables[$tablename] = $table;
082ae821 1851
3ff8bf26 1852 $DB->insert_record($tablename, array('description'=>'abcd'));
1853 $DB->insert_record($tablename, array('description'=>'dxxx'));
1854 $DB->insert_record($tablename, array('description'=>'bcde'));
082ae821 1855
3ff8bf26 1856 $sql = "SELECT * FROM {".$tablename."} ORDER BY ".$DB->sql_order_by_text('description');
082ae821 1857 $records = $DB->get_records_sql($sql);
1858 $first = array_unshift($records);
1859 $this->assertEqual(1, $first->id);
1860 $second = array_unshift($records);
1861 $this->assertEqual(3, $second->id);
1862 $last = array_unshift($records);
1863 $this->assertEqual(2, $last->id);
1864 }
1865
1866 function test_sql_substring() {
1867 $DB = $this->tdb;
1868 $dbman = $DB->get_manager();
1869
9d833e93 1870 $table = $this->get_test_table();
3ff8bf26 1871 $tablename = $table->getName();
1872
f9ecb171 1873 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1874 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
082ae821 1875 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1876 $dbman->create_table($table);
3ff8bf26 1877 $this->tables[$tablename] = $table;
082ae821 1878
1879 $string = 'abcdefghij';
1880
3ff8bf26 1881 $DB->insert_record($tablename, array('name'=>$string));
082ae821 1882
3ff8bf26 1883 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {".$tablename."}";
082ae821 1884 $record = $DB->get_record_sql($sql);
1885 $this->assertEqual(substr($string, 5-1), $record->name);
1886
3ff8bf26 1887 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {".$tablename."}";
082ae821 1888 $record = $DB->get_record_sql($sql);
1889 $this->assertEqual(substr($string, 5-1, 2), $record->name);
1890
1891 try {
1892 // silence php warning ;-)
1893 @$DB->sql_substr("name");
1894 $this->fail("Expecting an exception, none occurred");
1895 } catch (Exception $e) {
1896 $this->assertTrue($e instanceof coding_exception);
1897 }
6ff835b7 1898 }
1899
37d975e1 1900 function test_sql_length() {
1901 $DB = $this->tdb;
1902 $this->assertEqual($DB->get_field_sql(
1903 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
1904 $this->assertEqual($DB->get_field_sql(
1905 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
1906 }
1907
1d861fce 1908 function test_sql_position() {
1909 $DB = $this->tdb;
1910 $this->assertEqual($DB->get_field_sql(
6ff835b7 1911 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
1d861fce 1912 $this->assertEqual($DB->get_field_sql(
6ff835b7 1913 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
1914 }
1915
082ae821 1916 function test_sql_empty() {
1917 $DB = $this->tdb;
1918 $dbman = $DB->get_manager();
1919
9d833e93 1920 $table = $this->get_test_table();
3ff8bf26 1921 $tablename = $table->getName();
1922
f9ecb171 1923 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1924 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
cf874b29 1925 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
1926 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
082ae821 1927 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1928 $dbman->create_table($table);
3ff8bf26 1929 $this->tables[$tablename] = $table;
082ae821 1930
cf874b29 1931 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3ff8bf26 1932 $DB->insert_record($tablename, array('name'=>null));
cf874b29 1933 $DB->insert_record($tablename, array('name'=>'lalala'));
3ff8bf26 1934 $DB->insert_record($tablename, array('name'=>0));
082ae821 1935
3ff8bf26 1936 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE name = '".$DB->sql_empty()."'");
082ae821 1937 $this->assertEqual(count($records), 1);
1938 $record = reset($records);
1939 $this->assertEqual($record->name, '');
cf874b29 1940
1941 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnull = '".$DB->sql_empty()."'");
1942 $this->assertEqual(count($records), 1);
1943 $record = reset($records);
1944 $this->assertEqual($record->namenotnull, '');
1945
1946 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE namenotnullnodeflt = '".$DB->sql_empty()."'");
1947 $this->assertEqual(count($records), 4);
1948 $record = reset($records);
1949 $this->assertEqual($record->namenotnullnodeflt, '');
082ae821 1950 }
1951
1952 function test_sql_isempty() {
1953 $DB = $this->tdb;
1954 $dbman = $DB->get_manager();
1955
9d833e93 1956 $table = $this->get_test_table();
3ff8bf26 1957 $tablename = $table->getName();
1958
f9ecb171 1959 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1960 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
1961 $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
1962 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
1963 $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
082ae821 1964 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1965 $dbman->create_table($table);
3ff8bf26 1966 $this->tables[$tablename] = $table;
082ae821 1967
3ff8bf26 1968 $DB->insert_record($tablename, array('name'=>'', 'namenull'=>'', 'description'=>'', 'descriptionnull'=>''));
1969 $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
1970 $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
1971 $DB->insert_record($tablename, array('name'=>0, 'namenull'=>0, 'description'=>0, 'descriptionnull'=>0));
082ae821 1972
3ff8bf26 1973 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'name', false, false));
082ae821 1974 $this->assertEqual(count($records), 1);
1975 $record = reset($records);
1976 $this->assertEqual($record->name, '');
1977
3ff8bf26 1978 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'namenull', true, false));
082ae821 1979 $this->assertEqual(count($records), 1);
1980 $record = reset($records);
1981 $this->assertEqual($record->namenull, '');
1982
3ff8bf26 1983 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'description', false, true));
082ae821 1984 $this->assertEqual(count($records), 1);
1985 $record = reset($records);
1986 $this->assertEqual($record->description, '');
1987
3ff8bf26 1988 $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isempty($tablename, 'descriptionnull', true, true));
082ae821 1989 $this->assertEqual(count($records), 1);
1990 $record = reset($records);
1991 $this->assertEqual($record->descriptionnull, '');
1992 }
1993
1994 function sql_isnotempty() {
1995 //TODO
1996 }
1997
1998 function test_sql_regex() {
6ff835b7 1999 $DB = $this->tdb;
2000 $dbman = $DB->get_manager();
2001
9d833e93 2002 $table = $this->get_test_table();
3ff8bf26 2003 $tablename = $table->getName();
2004
f9ecb171 2005 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2006 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
6ff835b7 2007 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2008 $dbman->create_table($table);
3ff8bf26 2009 $this->tables[$tablename] = $table;
6ff835b7 2010
3ff8bf26 2011 $DB->insert_record($tablename, array('name'=>'lalala'));
2012 $DB->insert_record($tablename, array('name'=>'holaaa'));
2013 $DB->insert_record($tablename, array('name'=>'aouch'));
6ff835b7 2014
3ff8bf26 2015 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex()." ?";
6ff835b7 2016 $params = array('a$');
dd1611a5 2017 if ($DB->sql_regex_supported()) {
2018 $records = $DB->get_records_sql($sql, $params);
2019 $this->assertEqual(count($records), 2);
2020 } else {
2021 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2022 }
6ff835b7 2023
3ff8bf26 2024 $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_regex(false)." ?";
6ff835b7 2025 $params = array('.a');
dd1611a5 2026 if ($DB->sql_regex_supported()) {
2027 $records = $DB->get_records_sql($sql, $params);
2028 $this->assertEqual(count($records), 1);
2029 } else {
2030 $this->assertTrue(true, 'Regexp operations not supported. Test skipped');
2031 }
6ff835b7 2032
1d861fce 2033 }
fb76304b 2034
2035 function test_begin_sql() {
2036 $DB = $this->tdb;
2037 $dbman = $DB->get_manager();
2038
9d833e93 2039 $table = $this->get_test_table();
3ff8bf26 2040 $tablename = $table->getName();
2041
f9ecb171 2042 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2043 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2044 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2045 $dbman->create_table($table);
3ff8bf26 2046 $this->tables[$tablename] = $table;
fb76304b 2047
2048 $active = $DB->begin_sql();
2049 if ($active) {
2050 // test only if driver supports transactions
2051 $data = (object)array('course'=>3);
3ff8bf26 2052 $DB->insert_record($tablename, $data);
2053 $this->assertEqual(1, $DB->count_records($tablename));
fb76304b 2054 $DB->commit_sql();
167cbee8 2055 } else {
2056 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2057 }
2058 }
2059
2060 function test_commit_sql() {
2061 $DB = $this->tdb;
2062 $dbman = $DB->get_manager();
2063
9d833e93 2064 $table = $this->get_test_table();
3ff8bf26 2065 $tablename = $table->getName();
2066
f9ecb171 2067 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2068 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2069 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2070 $dbman->create_table($table);
3ff8bf26 2071 $this->tables[$tablename] = $table;
fb76304b 2072
2073 $active = $DB->begin_sql();
2074 if ($active) {
2075 // test only if driver supports transactions
2076 $data = (object)array('course'=>3);
3ff8bf26 2077 $DB->insert_record($tablename, $data);
fb76304b 2078 $DB->commit_sql();
3ff8bf26 2079 $this->assertEqual(1, $DB->count_records($tablename));
167cbee8 2080 } else {
2081 $this->assertTrue(true, 'BD Transactions not supported. Test skipped');
fb76304b 2082 }
2083 }
2084
2085 function test_rollback_sql() {
2086 $DB = $this->tdb;
2087 $dbman = $DB->get_manager();
2088
9d833e93 2089 $table = $this->get_test_table();
3ff8bf26 2090 $tablename = $table->getName();
2091
f9ecb171 2092 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2093 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
fb76304b 2094 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2095 $dbman->create_table($table);
3ff8bf26 2096 $this->tables[$tablename] = $table;
fb76304b 2097
2098 $active = $DB->begin_sql();
2099 if ($active) {
2100 // test only if driver supports transactions
2101 $data = (object)array('course'=>3);
3ff8bf26 2102 $DB->insert_record($tablename, $data);
fb76304b 2103 $DB->rollback_sql();
3ff8bf26 2104 $this->assertEqual(0, $DB->count_records($tablename));
167cbee8 2105 } else {
81f5be12 2106 $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
fb76304b 2107 }
2108 }
7f9f11b5 2109}
2110
2111/**
2112 * This class is not a proper subclass of moodle_database. It is
2113 * intended to be used only in unit tests, in order to gain access to the
2114 * protected methods of moodle_database, and unit test them.
2115 */
2116class moodle_database_for_testing extends moodle_database {
2117 protected $prefix = 'mdl_';
2118
2119 public function public_fix_table_names($sql) {
2120 return $this->fix_table_names($sql);
2121 }
2122
2123 public function driver_installed(){}
2124 public function get_dbfamily(){}
2125 protected function get_dbtype(){}
16a5642c 2126 protected function get_dblibrary(){}
7f9f11b5 2127 public function get_name(){}
37d975e1 2128 public function get_configuration_help(){}
7f9f11b5 2129 public function get_configuration_hints(){}
beaa43db 2130 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null){}
7f9f11b5 2131 public function get_server_info(){}
2132 protected function allowed_param_types(){}
2133 public function get_last_error(){}
117679db 2134 public function get_tables($usecache=true){}
7f9f11b5 2135 public function get_indexes($table){}
2136 public function get_columns($table, $usecache=true){}
2137 public function set_debug($state){}
2138 public function get_debug(){}
2139 public function set_logging($state){}
2140 public function change_database_structure($sql){}
2141 public function execute($sql, array $params=null){}
2142 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2143 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0){}
2144 public function get_fieldset_sql($sql, array $params=null){}
94898738 2145 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false){}
7f9f11b5 2146 public function insert_record($table, $dataobject, $returnid=true, $bulk=false){}
94898738 2147 public function import_record($table, $dataobject){}
7f9f11b5 2148 public function update_record_raw($table, $params, $bulk=false){}
2149 public function update_record($table, $dataobject, $bulk=false){}
2150 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null){}
2151 public function delete_records_select($table, $select, array $params=null){}
2152 public function sql_concat(){}
2153 public function sql_concat_join($separator="' '", $elements=array()){}
655bbf51 2154 public function sql_substr($expr, $start, $length=false){}
bb78c788 2155}