MDL-32149 PHPUnit test support - part 2
[moodle.git] / lib / dml / tests / dml_test.php
CommitLineData
a3d5830a
PS
1<?php
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
18 * DML layer tests
19 *
20 * @package core
21 * @subpackage dml
22 * @category phpunit
23 * @copyright 2008 Nicolas Connault
24 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
25 */
26
27defined('MOODLE_INTERNAL') || die();
28
29class dml_testcase extends database_driver_testcase {
30
31 /**
32 * Get a xmldb_table object for testing, deleting any existing table
33 * of the same name, for example if one was left over from a previous test
34 * run that crashed.
35 *
36 * @param string $suffix table name suffix, use if you need more test tables
37 * @return xmldb_table the table object.
38 */
39 private function get_test_table($suffix = '') {
40 $tablename = "test_table";
41 if ($suffix !== '') {
42 $tablename .= $suffix;
43 }
44
45 $table = new xmldb_table($tablename);
46 $table->setComment("This is a test'n drop table. You can drop it safely");
47 return new xmldb_table($tablename);
48 }
49
50 protected function enable_debugging() {
51 ob_start(); // hide debug warning
52 }
53
54 protected function get_debugging() {
55 $debuginfo = ob_get_contents();
56 ob_end_clean();
57
58 return $debuginfo;
59 }
60
61 // NOTE: please keep order of test methods here matching the order of moodle_database class methods
62
63 function test_diagnose() {
64 $DB = $this->tdb;
65 $result = $DB->diagnose();
66 $this->assertNull($result, 'Database self diagnostics failed %s');
67 }
68
69 function test_get_server_info() {
70 $DB = $this->tdb;
71 $result = $DB->get_server_info();
72 $this->assertTrue(is_array($result));
73 $this->assertTrue(array_key_exists('description', $result));
74 $this->assertTrue(array_key_exists('version', $result));
75 }
76
77 public function test_get_in_or_equal() {
78 $DB = $this->tdb;
79
80 // SQL_PARAMS_QM - IN or =
81
82 // Correct usage of multiple values
83 $in_values = array('value1', 'value2', '3', 4, null, false, true);
84 list($usql, $params) = $DB->get_in_or_equal($in_values);
85 $this->assertEquals('IN ('.implode(',',array_fill(0, count($in_values), '?')).')', $usql);
86 $this->assertEquals(count($in_values), count($params));
87 foreach ($params as $key => $value) {
88 $this->assertSame($in_values[$key], $value);
89 }
90
91 // Correct usage of single value (in an array)
92 $in_values = array('value1');
93 list($usql, $params) = $DB->get_in_or_equal($in_values);
94 $this->assertEquals("= ?", $usql);
95 $this->assertEquals(1, count($params));
96 $this->assertEquals($in_values[0], $params[0]);
97
98 // Correct usage of single value
99 $in_value = 'value1';
100 list($usql, $params) = $DB->get_in_or_equal($in_values);
101 $this->assertEquals("= ?", $usql);
102 $this->assertEquals(1, count($params));
103 $this->assertEquals($in_value, $params[0]);
104
105 // SQL_PARAMS_QM - NOT IN or <>
106
107 // Correct usage of multiple values
108 $in_values = array('value1', 'value2', 'value3', 'value4');
109 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
110 $this->assertEquals("NOT IN (?,?,?,?)", $usql);
111 $this->assertEquals(4, count($params));
112 foreach ($params as $key => $value) {
113 $this->assertEquals($in_values[$key], $value);
114 }
115
116 // Correct usage of single value (in array()
117 $in_values = array('value1');
118 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
119 $this->assertEquals("<> ?", $usql);
120 $this->assertEquals(1, count($params));
121 $this->assertEquals($in_values[0], $params[0]);
122
123 // Correct usage of single value
124 $in_value = 'value1';
125 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, null, false);
126 $this->assertEquals("<> ?", $usql);
127 $this->assertEquals(1, count($params));
128 $this->assertEquals($in_value, $params[0]);
129
130 // SQL_PARAMS_NAMED - IN or =
131
132 // Correct usage of multiple values
133 $in_values = array('value1', 'value2', 'value3', 'value4');
134 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
135 $this->assertEquals(4, count($params));
136 reset($in_values);
137 $ps = array();
138 foreach ($params as $key => $value) {
139 $this->assertEquals(current($in_values), $value);
140 next($in_values);
141 $ps[] = ':'.$key;
142 }
143 $this->assertEquals("IN (".implode(',', $ps).")", $usql);
144
145 // Correct usage of single values (in array)
146 $in_values = array('value1');
147 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
148 $this->assertEquals(1, count($params));
149 $value = reset($params);
150 $key = key($params);
151 $this->assertEquals("= :$key", $usql);
152 $this->assertEquals($in_value, $value);
153
154 // Correct usage of single value
155 $in_value = 'value1';
156 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', true);
157 $this->assertEquals(1, count($params));
158 $value = reset($params);
159 $key = key($params);
160 $this->assertEquals("= :$key", $usql);
161 $this->assertEquals($in_value, $value);
162
163 // SQL_PARAMS_NAMED - NOT IN or <>
164
165 // Correct usage of multiple values
166 $in_values = array('value1', 'value2', 'value3', 'value4');
167 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
168 $this->assertEquals(4, count($params));
169 reset($in_values);
170 $ps = array();
171 foreach ($params as $key => $value) {
172 $this->assertEquals(current($in_values), $value);
173 next($in_values);
174 $ps[] = ':'.$key;
175 }
176 $this->assertEquals("NOT IN (".implode(',', $ps).")", $usql);
177
178 // Correct usage of single values (in array)
179 $in_values = array('value1');
180 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
181 $this->assertEquals(1, count($params));
182 $value = reset($params);
183 $key = key($params);
184 $this->assertEquals("<> :$key", $usql);
185 $this->assertEquals($in_value, $value);
186
187 // Correct usage of single value
188 $in_value = 'value1';
189 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
190 $this->assertEquals(1, count($params));
191 $value = reset($params);
192 $key = key($params);
193 $this->assertEquals("<> :$key", $usql);
194 $this->assertEquals($in_value, $value);
195
196 // make sure the param names are unique
197 list($usql1, $params1) = $DB->get_in_or_equal(array(1,2,3), SQL_PARAMS_NAMED, 'param');
198 list($usql2, $params2) = $DB->get_in_or_equal(array(1,2,3), SQL_PARAMS_NAMED, 'param');
199 $params1 = array_keys($params1);
200 $params2 = array_keys($params2);
201 $common = array_intersect($params1, $params2);
202 $this->assertEquals(count($common), 0);
203
204 // Some incorrect tests
205
206 // Incorrect usage passing not-allowed params type
207 $in_values = array(1, 2, 3);
208 try {
209 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_DOLLAR, 'param', false);
210 $this->fail('An Exception is missing, expected due to not supported SQL_PARAMS_DOLLAR');
211 } catch (exception $e) {
212 $this->assertTrue($e instanceof dml_exception);
213 $this->assertEquals($e->errorcode, 'typenotimplement');
214 }
215
216 // Incorrect usage passing empty array
217 $in_values = array();
218 try {
219 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false);
220 $this->fail('An Exception is missing, expected due to empty array of items');
221 } catch (exception $e) {
222 $this->assertTrue($e instanceof coding_exception);
223 }
224
225 // Test using $onemptyitems
226
227 // Correct usage passing empty array and $onemptyitems = NULL (equal = true, QM)
228 $in_values = array();
229 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, NULL);
230 $this->assertEquals(' IS NULL', $usql);
231 $this->assertSame(array(), $params);
232
233 // Correct usage passing empty array and $onemptyitems = NULL (equal = false, NAMED)
234 $in_values = array();
235 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, NULL);
236 $this->assertEquals(' IS NOT NULL', $usql);
237 $this->assertSame(array(), $params);
238
239 // Correct usage passing empty array and $onemptyitems = true (equal = true, QM)
240 $in_values = array();
241 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, true);
242 $this->assertEquals('= ?', $usql);
243 $this->assertSame(array(true), $params);
244
245 // Correct usage passing empty array and $onemptyitems = true (equal = false, NAMED)
246 $in_values = array();
247 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, true);
248 $this->assertEquals(1, count($params));
249 $value = reset($params);
250 $key = key($params);
251 $this->assertEquals('<> :'.$key, $usql);
252 $this->assertSame($value, true);
253
254 // Correct usage passing empty array and $onemptyitems = -1 (equal = true, QM)
255 $in_values = array();
256 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, -1);
257 $this->assertEquals('= ?', $usql);
258 $this->assertSame(array(-1), $params);
259
260 // Correct usage passing empty array and $onemptyitems = -1 (equal = false, NAMED)
261 $in_values = array();
262 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, -1);
263 $this->assertEquals(1, count($params));
264 $value = reset($params);
265 $key = key($params);
266 $this->assertEquals('<> :'.$key, $usql);
267 $this->assertSame($value, -1);
268
269 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = true, QM)
270 $in_values = array();
271 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_QM, 'param', true, 'onevalue');
272 $this->assertEquals('= ?', $usql);
273 $this->assertSame(array('onevalue'), $params);
274
275 // Correct usage passing empty array and $onemptyitems = 'onevalue' (equal = false, NAMED)
276 $in_values = array();
277 list($usql, $params) = $DB->get_in_or_equal($in_values, SQL_PARAMS_NAMED, 'param', false, 'onevalue');
278 $this->assertEquals(1, count($params));
279 $value = reset($params);
280 $key = key($params);
281 $this->assertEquals('<> :'.$key, $usql);
282 $this->assertSame($value, 'onevalue');
283 }
284
285 public function test_fix_table_names() {
286 $DB = new moodle_database_for_testing();
287 $prefix = $DB->get_prefix();
288
289 // Simple placeholder
290 $placeholder = "{user_123}";
291 $this->assertSame($prefix."user_123", $DB->public_fix_table_names($placeholder));
292
293 // wrong table name
294 $placeholder = "{user-a}";
295 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
296
297 // wrong table name
298 $placeholder = "{123user}";
299 $this->assertSame($placeholder, $DB->public_fix_table_names($placeholder));
300
301 // Full SQL
302 $sql = "SELECT * FROM {user}, {funny_table_name}, {mdl_stupid_table} WHERE {user}.id = {funny_table_name}.userid";
303 $expected = "SELECT * FROM {$prefix}user, {$prefix}funny_table_name, {$prefix}mdl_stupid_table WHERE {$prefix}user.id = {$prefix}funny_table_name.userid";
304 $this->assertSame($expected, $DB->public_fix_table_names($sql));
305 }
306
307 function test_fix_sql_params() {
308 $DB = $this->tdb;
309
310 $table = $this->get_test_table();
311 $tablename = $table->getName();
312
313 // Correct table placeholder substitution
314 $sql = "SELECT * FROM {{$tablename}}";
315 $sqlarray = $DB->fix_sql_params($sql);
316 $this->assertEquals("SELECT * FROM {$DB->get_prefix()}".$tablename, $sqlarray[0]);
317
318 // Conversions of all param types
319 $sql = array();
320 $sql[SQL_PARAMS_NAMED] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = :param1, course = :param2";
321 $sql[SQL_PARAMS_QM] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, course = ?";
322 $sql[SQL_PARAMS_DOLLAR] = "SELECT * FROM {$DB->get_prefix()}testtable WHERE name = \$1, course = \$2";
323
324 $params = array();
325 $params[SQL_PARAMS_NAMED] = array('param1'=>'first record', 'param2'=>1);
326 $params[SQL_PARAMS_QM] = array('first record', 1);
327 $params[SQL_PARAMS_DOLLAR] = array('first record', 1);
328
329 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_NAMED], $params[SQL_PARAMS_NAMED]);
330 $this->assertSame($rsql, $sql[$rtype]);
331 $this->assertSame($rparams, $params[$rtype]);
332
333 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_QM], $params[SQL_PARAMS_QM]);
334 $this->assertSame($rsql, $sql[$rtype]);
335 $this->assertSame($rparams, $params[$rtype]);
336
337 list($rsql, $rparams, $rtype) = $DB->fix_sql_params($sql[SQL_PARAMS_DOLLAR], $params[SQL_PARAMS_DOLLAR]);
338 $this->assertSame($rsql, $sql[$rtype]);
339 $this->assertSame($rparams, $params[$rtype]);
340
341
342 // Malformed table placeholder
343 $sql = "SELECT * FROM [testtable]";
344 $sqlarray = $DB->fix_sql_params($sql);
345 $this->assertSame($sql, $sqlarray[0]);
346
347
348 // Mixed param types (colon and dollar)
349 $sql = "SELECT * FROM {{$tablename}} WHERE name = :param1, course = \$1";
350 $params = array('param1' => 'record1', 'param2' => 3);
351 try {
352 $DB->fix_sql_params($sql, $params);
353 $this->fail("Expecting an exception, none occurred");
354 } catch (Exception $e) {
355 $this->assertTrue($e instanceof dml_exception);
356 }
357
358 // Mixed param types (question and dollar)
359 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = \$1";
360 $params = array('param1' => 'record2', 'param2' => 5);
361 try {
362 $DB->fix_sql_params($sql, $params);
363 $this->fail("Expecting an exception, none occurred");
364 } catch (Exception $e) {
365 $this->assertTrue($e instanceof dml_exception);
366 }
367
368 // Too few params in sql
369 $sql = "SELECT * FROM {{$tablename}} WHERE name = ?, course = ?, id = ?";
370 $params = array('record2', 3);
371 try {
372 $DB->fix_sql_params($sql, $params);
373 $this->fail("Expecting an exception, none occurred");
374 } catch (Exception $e) {
375 $this->assertTrue($e instanceof dml_exception);
376 }
377
378 // Too many params in array: no error, just use what is necessary
379 $params[] = 1;
380 $params[] = time();
381 try {
382 $sqlarray = $DB->fix_sql_params($sql, $params);
383 $this->assertTrue(is_array($sqlarray));
384 $this->assertEquals(count($sqlarray[1]), 3);
385 } catch (Exception $e) {
386 $this->fail("Unexpected ".get_class($e)." exception");
387 }
388
389 // Named params missing from array
390 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
391 $params = array('wrongname' => 'record1', 'course' => 1);
392 try {
393 $DB->fix_sql_params($sql, $params);
394 $this->fail("Expecting an exception, none occurred");
395 } catch (Exception $e) {
396 $this->assertTrue($e instanceof dml_exception);
397 }
398
399 // Duplicate named param in query - this is a very important feature!!
400 // it helps with debugging of sloppy code
401 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :name";
402 $params = array('name' => 'record2', 'course' => 3);
403 try {
404 $DB->fix_sql_params($sql, $params);
405 $this->fail("Expecting an exception, none occurred");
406 } catch (Exception $e) {
407 $this->assertTrue($e instanceof dml_exception);
408 }
409
410 // Extra named param is ignored
411 $sql = "SELECT * FROM {{$tablename}} WHERE name = :name, course = :course";
412 $params = array('name' => 'record1', 'course' => 1, 'extrastuff'=>'haha');
413 try {
414 $sqlarray = $DB->fix_sql_params($sql, $params);
415 $this->assertTrue(is_array($sqlarray));
416 $this->assertEquals(count($sqlarray[1]), 2);
417 } catch (Exception $e) {
418 $this->fail("Unexpected ".get_class($e)." exception");
419 }
420
421 // Params exceeding 30 chars length
422 $sql = "SELECT * FROM {{$tablename}} WHERE name = :long_placeholder_with_more_than_30";
423 $params = array('long_placeholder_with_more_than_30' => 'record1');
424 try {
425 $DB->fix_sql_params($sql, $params);
426 $this->fail("Expecting an exception, none occurred");
427 } catch (Exception $e) {
428 $this->assertTrue($e instanceof coding_exception);
429 }
430
431 // Booleans in NAMED params are casting to 1/0 int
432 $sql = "SELECT * FROM {{$tablename}} WHERE course = ? OR course = ?";
433 $params = array(true, false);
434 list($sql, $params) = $DB->fix_sql_params($sql, $params);
435 $this->assertTrue(reset($params) === 1);
436 $this->assertTrue(next($params) === 0);
437
438 // Booleans in QM params are casting to 1/0 int
439 $sql = "SELECT * FROM {{$tablename}} WHERE course = :course1 OR course = :course2";
440 $params = array('course1' => true, 'course2' => false);
441 list($sql, $params) = $DB->fix_sql_params($sql, $params);
442 $this->assertTrue(reset($params) === 1);
443 $this->assertTrue(next($params) === 0);
444
445 // Booleans in DOLLAR params are casting to 1/0 int
446 $sql = "SELECT * FROM {{$tablename}} WHERE course = \$1 OR course = \$2";
447 $params = array(true, false);
448 list($sql, $params) = $DB->fix_sql_params($sql, $params);
449 $this->assertTrue(reset($params) === 1);
450 $this->assertTrue(next($params) === 0);
451
452 // No data types are touched except bool
453 $sql = "SELECT * FROM {{$tablename}} WHERE name IN (?,?,?,?,?,?)";
454 $inparams = array('abc', 'ABC', NULL, '1', 1, 1.4);
455 list($sql, $params) = $DB->fix_sql_params($sql, $inparams);
456 $this->assertSame(array_values($params), array_values($inparams));
457 }
458
459 public function test_strtok() {
460 // strtok was previously used by bound emulation, make sure it is not used any more
461 $DB = $this->tdb;
462 $dbman = $this->tdb->get_manager();
463
464 $table = $this->get_test_table();
465 $tablename = $table->getName();
466
467 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
468 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
469 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
470 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
471 $dbman->create_table($table);
472
473 $str = 'a?b?c?d';
474 $this->assertSame(strtok($str, '?'), 'a');
475
476 $DB->get_records($tablename, array('id'=>1));
477
478 $this->assertSame(strtok('?'), 'b');
479 }
480
481 public function test_tweak_param_names() {
482 // Note the tweak_param_names() method is only available in the oracle driver,
483 // hence we look for expected results indirectly, by testing various DML methods
484 // with some "extreme" conditions causing the tweak to happen.
485 $DB = $this->tdb;
486 $dbman = $this->tdb->get_manager();
487
488 $table = $this->get_test_table();
489 $tablename = $table->getName();
490
491 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
492 // Add some columns with 28 chars in the name
493 $table->add_field('long_int_columnname_with_28c', XMLDB_TYPE_INTEGER, '10');
494 $table->add_field('long_dec_columnname_with_28c', XMLDB_TYPE_NUMBER, '10,2');
495 $table->add_field('long_str_columnname_with_28c', XMLDB_TYPE_CHAR, '100');
496 // Add some columns with 30 chars in the name
497 $table->add_field('long_int_columnname_with_30cxx', XMLDB_TYPE_INTEGER, '10');
498 $table->add_field('long_dec_columnname_with_30cxx', XMLDB_TYPE_NUMBER, '10,2');
499 $table->add_field('long_str_columnname_with_30cxx', XMLDB_TYPE_CHAR, '100');
500
501 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
502
503 $dbman->create_table($table);
504
505 $this->assertTrue($dbman->table_exists($tablename));
506
507 // Test insert record
508 $rec1 = new stdClass();
509 $rec1->long_int_columnname_with_28c = 28;
510 $rec1->long_dec_columnname_with_28c = 28.28;
511 $rec1->long_str_columnname_with_28c = '28';
512 $rec1->long_int_columnname_with_30cxx = 30;
513 $rec1->long_dec_columnname_with_30cxx = 30.30;
514 $rec1->long_str_columnname_with_30cxx = '30';
515
516 // insert_record()
517 $rec1->id = $DB->insert_record($tablename, $rec1);
518 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
519
520 // update_record()
521 $DB->update_record($tablename, $rec1);
522 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
523
524 // set_field()
525 $rec1->long_int_columnname_with_28c = 280;
526 $DB->set_field($tablename, 'long_int_columnname_with_28c', $rec1->long_int_columnname_with_28c,
527 array('id' => $rec1->id, 'long_int_columnname_with_28c' => 28));
528 $rec1->long_dec_columnname_with_28c = 280.28;
529 $DB->set_field($tablename, 'long_dec_columnname_with_28c', $rec1->long_dec_columnname_with_28c,
530 array('id' => $rec1->id, 'long_dec_columnname_with_28c' => 28.28));
531 $rec1->long_str_columnname_with_28c = '280';
532 $DB->set_field($tablename, 'long_str_columnname_with_28c', $rec1->long_str_columnname_with_28c,
533 array('id' => $rec1->id, 'long_str_columnname_with_28c' => '28'));
534 $rec1->long_int_columnname_with_30cxx = 300;
535 $DB->set_field($tablename, 'long_int_columnname_with_30cxx', $rec1->long_int_columnname_with_30cxx,
536 array('id' => $rec1->id, 'long_int_columnname_with_30cxx' => 30));
537 $rec1->long_dec_columnname_with_30cxx = 300.30;
538 $DB->set_field($tablename, 'long_dec_columnname_with_30cxx', $rec1->long_dec_columnname_with_30cxx,
539 array('id' => $rec1->id, 'long_dec_columnname_with_30cxx' => 30.30));
540 $rec1->long_str_columnname_with_30cxx = '300';
541 $DB->set_field($tablename, 'long_str_columnname_with_30cxx', $rec1->long_str_columnname_with_30cxx,
542 array('id' => $rec1->id, 'long_str_columnname_with_30cxx' => '30'));
543 $this->assertEquals($rec1, $DB->get_record($tablename, array('id' => $rec1->id)));
544
545 // delete_records()
546 $rec2 = $DB->get_record($tablename, array('id' => $rec1->id));
547 $rec2->id = $DB->insert_record($tablename, $rec2);
548 $this->assertEquals(2, $DB->count_records($tablename));
549 $DB->delete_records($tablename, (array) $rec2);
550 $this->assertEquals(1, $DB->count_records($tablename));
551
552 // get_recordset()
553 $rs = $DB->get_recordset($tablename, (array) $rec1);
554 $iterations = 0;
555 foreach ($rs as $rec2) {
556 $iterations++;
557 }
558 $rs->close();
559 $this->assertEquals(1, $iterations);
560 $this->assertEquals($rec1, $rec2);
561
562 // get_records()
563 $recs = $DB->get_records($tablename, (array) $rec1);
564 $this->assertEquals(1, count($recs));
565 $this->assertEquals($rec1, reset($recs));
566
567 // get_fieldset_select()
568 $select = 'id = :id AND
569 long_int_columnname_with_28c = :long_int_columnname_with_28c AND
570 long_dec_columnname_with_28c = :long_dec_columnname_with_28c AND
571 long_str_columnname_with_28c = :long_str_columnname_with_28c AND
572 long_int_columnname_with_30cxx = :long_int_columnname_with_30cxx AND
573 long_dec_columnname_with_30cxx = :long_dec_columnname_with_30cxx AND
574 long_str_columnname_with_30cxx = :long_str_columnname_with_30cxx';
575 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_28c', $select, (array)$rec1);
576 $this->assertEquals(1, count($fields));
577 $this->assertEquals($rec1->long_int_columnname_with_28c, reset($fields));
578 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_28c', $select, (array)$rec1);
579 $this->assertEquals($rec1->long_dec_columnname_with_28c, reset($fields));
580 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_28c', $select, (array)$rec1);
581 $this->assertEquals($rec1->long_str_columnname_with_28c, reset($fields));
582 $fields = $DB->get_fieldset_select($tablename, 'long_int_columnname_with_30cxx', $select, (array)$rec1);
583 $this->assertEquals($rec1->long_int_columnname_with_30cxx, reset($fields));
584 $fields = $DB->get_fieldset_select($tablename, 'long_dec_columnname_with_30cxx', $select, (array)$rec1);
585 $this->assertEquals($rec1->long_dec_columnname_with_30cxx, reset($fields));
586 $fields = $DB->get_fieldset_select($tablename, 'long_str_columnname_with_30cxx', $select, (array)$rec1);
587 $this->assertEquals($rec1->long_str_columnname_with_30cxx, reset($fields));
588
589 // overlapping placeholders (progressive str_replace)
590 $overlapselect = 'id = :p AND
591 long_int_columnname_with_28c = :param1 AND
592 long_dec_columnname_with_28c = :param2 AND
593 long_str_columnname_with_28c = :param_with_29_characters_long AND
594 long_int_columnname_with_30cxx = :param_with_30_characters_long_ AND
595 long_dec_columnname_with_30cxx = :param_ AND
596 long_str_columnname_with_30cxx = :param__';
597 $overlapparams = array(
598 'p' => $rec1->id,
599 'param1' => $rec1->long_int_columnname_with_28c,
600 'param2' => $rec1->long_dec_columnname_with_28c,
601 'param_with_29_characters_long' => $rec1->long_str_columnname_with_28c,
602 'param_with_30_characters_long_' => $rec1->long_int_columnname_with_30cxx,
603 'param_' => $rec1->long_dec_columnname_with_30cxx,
604 'param__' => $rec1->long_str_columnname_with_30cxx);
605 $recs = $DB->get_records_select($tablename, $overlapselect, $overlapparams);
606 $this->assertEquals(1, count($recs));
607 $this->assertEquals($rec1, reset($recs));
608
609 // execute()
610 $DB->execute("DELETE FROM {{$tablename}} WHERE $select", (array)$rec1);
611 $this->assertEquals(0, $DB->count_records($tablename));
612 }
613
614 public function test_get_tables() {
615 $DB = $this->tdb;
616 $dbman = $this->tdb->get_manager();
617
618 // Need to test with multiple DBs
619 $table = $this->get_test_table();
620 $tablename = $table->getName();
621
622 $original_count = count($DB->get_tables());
623
624 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
625 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
626
627 $dbman->create_table($table);
628 $this->assertTrue(count($DB->get_tables()) == $original_count + 1);
629
630 $dbman->drop_table($table);
631 $this->assertTrue(count($DB->get_tables()) == $original_count);
632 }
633
634 public function test_get_indexes() {
635 $DB = $this->tdb;
636 $dbman = $this->tdb->get_manager();
637
638 $table = $this->get_test_table();
639 $tablename = $table->getName();
640
641 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
642 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
643 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
644 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
645 $table->add_index('course-id', XMLDB_INDEX_UNIQUE, array('course', 'id'));
646 $dbman->create_table($table);
647
648 $indices = $DB->get_indexes($tablename);
649 $this->assertTrue(is_array($indices));
650 $this->assertEquals(count($indices), 2);
651 // we do not care about index names for now
652 $first = array_shift($indices);
653 $second = array_shift($indices);
654 if (count($first['columns']) == 2) {
655 $composed = $first;
656 $single = $second;
657 } else {
658 $composed = $second;
659 $single = $first;
660 }
661 $this->assertFalse($single['unique']);
662 $this->assertTrue($composed['unique']);
663 $this->assertEquals(1, count($single['columns']));
664 $this->assertEquals(2, count($composed['columns']));
665 $this->assertEquals('course', $single['columns'][0]);
666 $this->assertEquals('course', $composed['columns'][0]);
667 $this->assertEquals('id', $composed['columns'][1]);
668 }
669
670 public function test_get_columns() {
671 $DB = $this->tdb;
672 $dbman = $this->tdb->get_manager();
673
674 $table = $this->get_test_table();
675 $tablename = $table->getName();
676
677 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
678 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
679 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, 'lala');
680 $table->add_field('description', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
681 $table->add_field('enumfield', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'test2');
682 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
683 $table->add_field('onefloat', XMLDB_TYPE_FLOAT, '10,2', null, null, null, 300);
684 $table->add_field('anotherfloat', XMLDB_TYPE_FLOAT, null, null, null, null, 400);
685 $table->add_field('negativedfltint', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '-1');
686 $table->add_field('negativedfltnumber', XMLDB_TYPE_NUMBER, '10', null, XMLDB_NOTNULL, null, '-2');
687 $table->add_field('negativedfltfloat', XMLDB_TYPE_FLOAT, '10', null, XMLDB_NOTNULL, null, '-3');
688 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
689 $dbman->create_table($table);
690
691 $columns = $DB->get_columns($tablename);
692 $this->assertTrue(is_array($columns));
693
694 $fields = $table->getFields();
695 $this->assertEquals(count($columns), count($fields));
696
697 $field = $columns['id'];
698 $this->assertEquals('R', $field->meta_type);
699 $this->assertTrue($field->auto_increment);
700 $this->assertTrue($field->unique);
701
702 $field = $columns['course'];
703 $this->assertEquals('I', $field->meta_type);
704 $this->assertFalse($field->auto_increment);
705 $this->assertTrue($field->has_default);
706 $this->assertEquals(0, $field->default_value);
707 $this->assertTrue($field->not_null);
708
709 $field = $columns['name'];
710 $this->assertEquals('C', $field->meta_type);
711 $this->assertFalse($field->auto_increment);
712 $this->assertEquals(255, $field->max_length);
713 $this->assertTrue($field->has_default);
714 $this->assertSame('lala', $field->default_value);
715 $this->assertFalse($field->not_null);
716
717 $field = $columns['description'];
718 $this->assertEquals('X', $field->meta_type);
719 $this->assertFalse($field->auto_increment);
720 $this->assertFalse($field->has_default);
721 $this->assertSame(null, $field->default_value);
722 $this->assertFalse($field->not_null);
723
724 $field = $columns['enumfield'];
725 $this->assertEquals('C', $field->meta_type);
726 $this->assertFalse($field->auto_increment);
727 $this->assertSame('test2', $field->default_value);
728 $this->assertTrue($field->not_null);
729
730 $field = $columns['onenum'];
731 $this->assertEquals('N', $field->meta_type);
732 $this->assertFalse($field->auto_increment);
733 $this->assertEquals(10, $field->max_length);
734 $this->assertEquals(2, $field->scale);
735 $this->assertTrue($field->has_default);
736 $this->assertEquals(200.0, $field->default_value);
737 $this->assertFalse($field->not_null);
738
739 $field = $columns['onefloat'];
740 $this->assertEquals('N', $field->meta_type);
741 $this->assertFalse($field->auto_increment);
742 $this->assertTrue($field->has_default);
743 $this->assertEquals(300.0, $field->default_value);
744 $this->assertFalse($field->not_null);
745
746 $field = $columns['anotherfloat'];
747 $this->assertEquals('N', $field->meta_type);
748 $this->assertFalse($field->auto_increment);
749 $this->assertTrue($field->has_default);
750 $this->assertEquals(400.0, $field->default_value);
751 $this->assertFalse($field->not_null);
752
753 // Test negative defaults in numerical columns
754 $field = $columns['negativedfltint'];
755 $this->assertTrue($field->has_default);
756 $this->assertEquals(-1, $field->default_value);
757
758 $field = $columns['negativedfltnumber'];
759 $this->assertTrue($field->has_default);
760 $this->assertEquals(-2, $field->default_value);
761
762 $field = $columns['negativedfltfloat'];
763 $this->assertTrue($field->has_default);
764 $this->assertEquals(-3, $field->default_value);
765
766 for ($i = 0; $i < count($columns); $i++) {
767 if ($i == 0) {
768 $next_column = reset($columns);
769 $next_field = reset($fields);
770 } else {
771 $next_column = next($columns);
772 $next_field = next($fields);
773 }
774
775 $this->assertEquals($next_column->name, $next_field->name);
776 }
777
778 // Test get_columns for non-existing table returns empty array. MDL-30147
779 $columns = $DB->get_columns('xxxx');
780 $this->assertEquals(array(), $columns);
781 }
782
783 public function test_get_manager() {
784 $DB = $this->tdb;
785 $dbman = $this->tdb->get_manager();
786
787 $this->assertTrue($dbman instanceof database_manager);
788 }
789
790 public function test_setup_is_unicodedb() {
791 $DB = $this->tdb;
792 $this->assertTrue($DB->setup_is_unicodedb());
793 }
794
795 public function test_set_debug() { //tests get_debug() too
796 $DB = $this->tdb;
797 $dbman = $this->tdb->get_manager();
798
799 $table = $this->get_test_table();
800 $tablename = $table->getName();
801
802 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
803 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
804 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
805 $dbman->create_table($table);
806
807 $sql = "SELECT * FROM {{$tablename}}";
808
809 $prevdebug = $DB->get_debug();
810
811 ob_start();
812 $DB->set_debug(true);
813 $this->assertTrue($DB->get_debug());
814 $DB->execute($sql);
815 $DB->set_debug(false);
816 $this->assertFalse($DB->get_debug());
817 $debuginfo = ob_get_contents();
818 ob_end_clean();
819 $this->assertFalse($debuginfo === '');
820
821 ob_start();
822 $DB->execute($sql);
823 $debuginfo = ob_get_contents();
824 ob_end_clean();
825 $this->assertTrue($debuginfo === '');
826
827 $DB->set_debug($prevdebug);
828 }
829
830 public function test_execute() {
831 $DB = $this->tdb;
832 $dbman = $this->tdb->get_manager();
833
834 $table1 = $this->get_test_table('1');
835 $tablename1 = $table1->getName();
836 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
837 $table1->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
838 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
839 $table1->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
840 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
841 $dbman->create_table($table1);
842
843 $table2 = $this->get_test_table('2');
844 $tablename2 = $table2->getName();
845 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
846 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
847 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
848 $dbman->create_table($table2);
849
850 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'aaa'));
851 $DB->insert_record($tablename1, array('course' => 1, 'name' => 'bbb'));
852 $DB->insert_record($tablename1, array('course' => 7, 'name' => 'ccc'));
853 $DB->insert_record($tablename1, array('course' => 3, 'name' => 'ddd'));
854
855 // select results are ignored
856 $sql = "SELECT * FROM {{$tablename1}} WHERE course = :course";
857 $this->assertTrue($DB->execute($sql, array('course'=>3)));
858
859 // throw exception on error
860 $sql = "XXUPDATE SET XSSD";
861 try {
862 $DB->execute($sql);
863 $this->fail("Expecting an exception, none occurred");
864 } catch (Exception $e) {
865 $this->assertTrue($e instanceof dml_exception);
866 }
867
868 // update records
869 $sql = "UPDATE {{$tablename1}}
870 SET course = 6
871 WHERE course = ?";
872 $this->assertTrue($DB->execute($sql, array('3')));
873 $this->assertEquals($DB->count_records($tablename1, array('course' => 6)), 2);
874
875 // update records with subquery condition
876 // confirm that the option not using table aliases is cross-db
877 $sql = "UPDATE {{$tablename1}}
878 SET course = 0
879 WHERE NOT EXISTS (
880 SELECT course
881 FROM {{$tablename2}} tbl2
882 WHERE tbl2.course = {{$tablename1}}.course
883 AND 1 = 0)"; // Really we don't update anything, but verify the syntax is allowed
884 $this->assertTrue($DB->execute($sql));
885
886 // insert from one into second table
887 $sql = "INSERT INTO {{$tablename2}} (course)
888
889 SELECT course
890 FROM {{$tablename1}}";
891 $this->assertTrue($DB->execute($sql));
892 $this->assertEquals($DB->count_records($tablename2), 4);
893 }
894
895 public function test_get_recordset() {
896 $DB = $this->tdb;
897 $dbman = $DB->get_manager();
898
899 $table = $this->get_test_table();
900 $tablename = $table->getName();
901
902 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
903 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
904 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
905 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
906 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
907 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
908 $dbman->create_table($table);
909
910 $data = array(array('id' => 1, 'course' => 3, 'name' => 'record1', 'onetext'=>'abc'),
911 array('id' => 2, 'course' => 3, 'name' => 'record2', 'onetext'=>'abcd'),
912 array('id' => 3, 'course' => 5, 'name' => 'record3', 'onetext'=>'abcde'));
913
914 foreach ($data as $record) {
915 $DB->insert_record($tablename, $record);
916 }
917
918 // standard recordset iteration
919 $rs = $DB->get_recordset($tablename);
920 $this->assertTrue($rs instanceof moodle_recordset);
921 reset($data);
922 foreach($rs as $record) {
923 $data_record = current($data);
924 foreach ($record as $k => $v) {
925 $this->assertEquals($data_record[$k], $v);
926 }
927 next($data);
928 }
929 $rs->close();
930
931 // iterator style usage
932 $rs = $DB->get_recordset($tablename);
933 $this->assertTrue($rs instanceof moodle_recordset);
934 reset($data);
935 while ($rs->valid()) {
936 $record = $rs->current();
937 $data_record = current($data);
938 foreach ($record as $k => $v) {
939 $this->assertEquals($data_record[$k], $v);
940 }
941 next($data);
942 $rs->next();
943 }
944 $rs->close();
945
946 // make sure rewind is ignored
947 $rs = $DB->get_recordset($tablename);
948 $this->assertTrue($rs instanceof moodle_recordset);
949 reset($data);
950 $i = 0;
951 foreach($rs as $record) {
952 $i++;
953 $rs->rewind();
954 if ($i > 10) {
955 $this->fail('revind not ignored in recordsets');
956 break;
957 }
958 $data_record = current($data);
959 foreach ($record as $k => $v) {
960 $this->assertEquals($data_record[$k], $v);
961 }
962 next($data);
963 }
964 $rs->close();
965
966 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
967 $conditions = array('onetext' => '1');
968 try {
969 $rs = $DB->get_recordset($tablename, $conditions);
970 if (debugging()) {
971 // only in debug mode - hopefully all devs test code in debug mode...
972 $this->fail('An Exception is missing, expected due to equating of text fields');
973 }
974 } catch (exception $e) {
975 $this->assertTrue($e instanceof dml_exception);
976 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
977 }
978
979 // notes:
980 // * limits are tested in test_get_recordset_sql()
981 // * where_clause() is used internally and is tested in test_get_records()
982 }
983
984 public function test_get_recordset_iterator_keys() {
985 $DB = $this->tdb;
986 $dbman = $DB->get_manager();
987
988 $table = $this->get_test_table();
989 $tablename = $table->getName();
990
991 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
992 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
993 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, '0');
994 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
995 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
996 $dbman->create_table($table);
997
998 $data = array(array('id'=> 1, 'course' => 3, 'name' => 'record1'),
999 array('id'=> 2, 'course' => 3, 'name' => 'record2'),
1000 array('id'=> 3, 'course' => 5, 'name' => 'record3'));
1001 foreach ($data as $record) {
1002 $DB->insert_record($tablename, $record);
1003 }
1004
1005 // Test repeated numeric keys are returned ok
1006 $rs = $DB->get_recordset($tablename, NULL, NULL, 'course, name, id');
1007
1008 reset($data);
1009 $count = 0;
1010 foreach($rs as $key => $record) {
1011 $data_record = current($data);
1012 $this->assertEquals($data_record['course'], $key);
1013 next($data);
1014 $count++;
1015 }
1016 $rs->close();
1017 $this->assertEquals($count, 3);
1018
1019 // Test string keys are returned ok
1020 $rs = $DB->get_recordset($tablename, NULL, NULL, 'name, course, id');
1021
1022 reset($data);
1023 $count = 0;
1024 foreach($rs as $key => $record) {
1025 $data_record = current($data);
1026 $this->assertEquals($data_record['name'], $key);
1027 next($data);
1028 $count++;
1029 }
1030 $rs->close();
1031 $this->assertEquals($count, 3);
1032
1033 // Test numeric not starting in 1 keys are returned ok
1034 $rs = $DB->get_recordset($tablename, NULL, 'id DESC', 'id, course, name');
1035
1036 $data = array_reverse($data);
1037 reset($data);
1038 $count = 0;
1039 foreach($rs as $key => $record) {
1040 $data_record = current($data);
1041 $this->assertEquals($data_record['id'], $key);
1042 next($data);
1043 $count++;
1044 }
1045 $rs->close();
1046 $this->assertEquals($count, 3);
1047 }
1048
1049 public function test_get_recordset_list() {
1050 $DB = $this->tdb;
1051 $dbman = $DB->get_manager();
1052
1053 $table = $this->get_test_table();
1054 $tablename = $table->getName();
1055
1056 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1057 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1058 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1059 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1060 $dbman->create_table($table);
1061
1062 $DB->insert_record($tablename, array('course' => 3));
1063 $DB->insert_record($tablename, array('course' => 3));
1064 $DB->insert_record($tablename, array('course' => 5));
1065 $DB->insert_record($tablename, array('course' => 2));
1066
1067 $rs = $DB->get_recordset_list($tablename, 'course', array(3, 2));
1068
1069 $counter = 0;
1070 foreach ($rs as $record) {
1071 $counter++;
1072 }
1073 $this->assertEquals(3, $counter);
1074 $rs->close();
1075
1076 $rs = $DB->get_recordset_list($tablename, 'course',array()); /// Must return 0 rows without conditions. MDL-17645
1077
1078 $counter = 0;
1079 foreach ($rs as $record) {
1080 $counter++;
1081 }
1082 $rs->close();
1083 $this->assertEquals(0, $counter);
1084
1085 // notes:
1086 // * limits are tested in test_get_recordset_sql()
1087 // * where_clause() is used internally and is tested in test_get_records()
1088 }
1089
1090 public function test_get_recordset_select() {
1091 $DB = $this->tdb;
1092 $dbman = $DB->get_manager();
1093
1094 $table = $this->get_test_table();
1095 $tablename = $table->getName();
1096
1097 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1098 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1099 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1100 $dbman->create_table($table);
1101
1102 $DB->insert_record($tablename, array('course' => 3));
1103 $DB->insert_record($tablename, array('course' => 3));
1104 $DB->insert_record($tablename, array('course' => 5));
1105 $DB->insert_record($tablename, array('course' => 2));
1106
1107 $rs = $DB->get_recordset_select($tablename, '');
1108 $counter = 0;
1109 foreach ($rs as $record) {
1110 $counter++;
1111 }
1112 $rs->close();
1113 $this->assertEquals(4, $counter);
1114
1115 $this->assertNotEmpty($rs = $DB->get_recordset_select($tablename, 'course = 3'));
1116 $counter = 0;
1117 foreach ($rs as $record) {
1118 $counter++;
1119 }
1120 $rs->close();
1121 $this->assertEquals(2, $counter);
1122
1123 // notes:
1124 // * limits are tested in test_get_recordset_sql()
1125 }
1126
1127 public function test_get_recordset_sql() {
1128 $DB = $this->tdb;
1129 $dbman = $DB->get_manager();
1130
1131 $table = $this->get_test_table();
1132 $tablename = $table->getName();
1133
1134 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1135 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1136 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1137 $dbman->create_table($table);
1138
1139 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1140 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1141 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1142 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1143 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1144 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1145 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1146
1147 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1148 $counter = 0;
1149 foreach ($rs as $record) {
1150 $counter++;
1151 }
1152 $rs->close();
1153 $this->assertEquals(2, $counter);
1154
1155 // limits - only need to test this case, the rest have been tested by test_get_records_sql()
1156 // only limitfrom = skips that number of records
1157 $rs = $DB->get_recordset_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1158 $records = array();
1159 foreach($rs as $key => $record) {
1160 $records[$key] = $record;
1161 }
1162 $rs->close();
1163 $this->assertEquals(5, count($records));
1164 $this->assertEquals($inskey3, reset($records)->id);
1165 $this->assertEquals($inskey7, end($records)->id);
1166
1167 // note: fetching nulls, empties, LOBs already tested by test_insert_record() no needed here
1168 }
1169
1170 public function test_get_records() {
1171 $DB = $this->tdb;
1172 $dbman = $DB->get_manager();
1173
1174 $table = $this->get_test_table();
1175 $tablename = $table->getName();
1176
1177 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1178 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1179 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1180 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1181 $dbman->create_table($table);
1182
1183 $DB->insert_record($tablename, array('course' => 3));
1184 $DB->insert_record($tablename, array('course' => 3));
1185 $DB->insert_record($tablename, array('course' => 5));
1186 $DB->insert_record($tablename, array('course' => 2));
1187
1188 // All records
1189 $records = $DB->get_records($tablename);
1190 $this->assertEquals(4, count($records));
1191 $this->assertEquals(3, $records[1]->course);
1192 $this->assertEquals(3, $records[2]->course);
1193 $this->assertEquals(5, $records[3]->course);
1194 $this->assertEquals(2, $records[4]->course);
1195
1196 // Records matching certain conditions
1197 $records = $DB->get_records($tablename, array('course' => 3));
1198 $this->assertEquals(2, count($records));
1199 $this->assertEquals(3, $records[1]->course);
1200 $this->assertEquals(3, $records[2]->course);
1201
1202 // All records sorted by course
1203 $records = $DB->get_records($tablename, null, 'course');
1204 $this->assertEquals(4, count($records));
1205 $current_record = reset($records);
1206 $this->assertEquals(4, $current_record->id);
1207 $current_record = next($records);
1208 $this->assertEquals(1, $current_record->id);
1209 $current_record = next($records);
1210 $this->assertEquals(2, $current_record->id);
1211 $current_record = next($records);
1212 $this->assertEquals(3, $current_record->id);
1213
1214 // All records, but get only one field
1215 $records = $DB->get_records($tablename, null, '', 'id');
1216 $this->assertFalse(isset($records[1]->course));
1217 $this->assertTrue(isset($records[1]->id));
1218 $this->assertEquals(4, count($records));
1219
1220 // Booleans into params
1221 $records = $DB->get_records($tablename, array('course' => true));
1222 $this->assertEquals(0, count($records));
1223 $records = $DB->get_records($tablename, array('course' => false));
1224 $this->assertEquals(0, count($records));
1225
1226 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1227 $conditions = array('onetext' => '1');
1228 try {
1229 $records = $DB->get_records($tablename, $conditions);
1230 if (debugging()) {
1231 // only in debug mode - hopefully all devs test code in debug mode...
1232 $this->fail('An Exception is missing, expected due to equating of text fields');
1233 }
1234 } catch (exception $e) {
1235 $this->assertTrue($e instanceof dml_exception);
1236 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
1237 }
1238
1239 // test get_records passing non-existing table
1240 // with params
1241 try {
1242 $records = $DB->get_records('xxxx', array('id' => 0));
1243 $this->fail('An Exception is missing, expected due to query against non-existing table');
1244 } catch (exception $e) {
1245 $this->assertTrue($e instanceof dml_exception);
1246 if (debugging()) {
1247 // information for developers only, normal users get general error message
1248 $this->assertEquals($e->errorcode, 'ddltablenotexist');
1249 }
1250 }
1251 // and without params
1252 try {
1253 $records = $DB->get_records('xxxx', array());
1254 $this->fail('An Exception is missing, expected due to query against non-existing table');
1255 } catch (exception $e) {
1256 $this->assertTrue($e instanceof dml_exception);
1257 if (debugging()) {
1258 // information for developers only, normal users get general error message
1259 $this->assertEquals($e->errorcode, 'ddltablenotexist');
1260 }
1261 }
1262
1263 // test get_records passing non-existing column
1264 try {
1265 $records = $DB->get_records($tablename, array('xxxx' => 0));
1266 $this->fail('An Exception is missing, expected due to query against non-existing column');
1267 } catch (exception $e) {
1268 $this->assertTrue($e instanceof dml_exception);
1269 if (debugging()) {
1270 // information for developers only, normal users get general error message
1271 $this->assertEquals($e->errorcode, 'ddlfieldnotexist');
1272 }
1273 }
1274
1275 // note: delegate limits testing to test_get_records_sql()
1276 }
1277
1278 public function test_get_records_list() {
1279 $DB = $this->tdb;
1280 $dbman = $DB->get_manager();
1281
1282 $table = $this->get_test_table();
1283 $tablename = $table->getName();
1284
1285 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1286 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1287 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1288 $dbman->create_table($table);
1289
1290 $DB->insert_record($tablename, array('course' => 3));
1291 $DB->insert_record($tablename, array('course' => 3));
1292 $DB->insert_record($tablename, array('course' => 5));
1293 $DB->insert_record($tablename, array('course' => 2));
1294
1295 $records = $DB->get_records_list($tablename, 'course', array(3, 2));
1296 $this->assertTrue(is_array($records));
1297 $this->assertEquals(3, count($records));
1298 $this->assertEquals(1, reset($records)->id);
1299 $this->assertEquals(2, next($records)->id);
1300 $this->assertEquals(4, next($records)->id);
1301
1302 $this->assertSame(array(), $records = $DB->get_records_list($tablename, 'course', array())); /// Must return 0 rows without conditions. MDL-17645
1303 $this->assertEquals(0, count($records));
1304
1305 // note: delegate limits testing to test_get_records_sql()
1306 }
1307
1308 public function test_get_records_sql() {
1309 $DB = $this->tdb;
1310 $dbman = $DB->get_manager();
1311
1312 $table = $this->get_test_table();
1313 $tablename = $table->getName();
1314
1315 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1316 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1317 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1318 $dbman->create_table($table);
1319
1320 $inskey1 = $DB->insert_record($tablename, array('course' => 3));
1321 $inskey2 = $DB->insert_record($tablename, array('course' => 5));
1322 $inskey3 = $DB->insert_record($tablename, array('course' => 4));
1323 $inskey4 = $DB->insert_record($tablename, array('course' => 3));
1324 $inskey5 = $DB->insert_record($tablename, array('course' => 2));
1325 $inskey6 = $DB->insert_record($tablename, array('course' => 1));
1326 $inskey7 = $DB->insert_record($tablename, array('course' => 0));
1327
1328 $table2 = $this->get_test_table("2");
1329 $tablename2 = $table2->getName();
1330 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1331 $table2->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1332 $table2->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
1333 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1334 $dbman->create_table($table2);
1335
1336 $DB->insert_record($tablename2, array('course'=>3, 'nametext'=>'badabing'));
1337 $DB->insert_record($tablename2, array('course'=>4, 'nametext'=>'badabang'));
1338 $DB->insert_record($tablename2, array('course'=>5, 'nametext'=>'badabung'));
1339 $DB->insert_record($tablename2, array('course'=>6, 'nametext'=>'badabong'));
1340
1341 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3));
1342 $this->assertEquals(2, count($records));
1343 $this->assertEquals($inskey1, reset($records)->id);
1344 $this->assertEquals($inskey4, next($records)->id);
1345
1346 // Awful test, requires debug enabled and sent to browser. Let's do that and restore after test
1347 $this->enable_debugging();
1348 $records = $DB->get_records_sql("SELECT course AS id, course AS course FROM {{$tablename}}", null);
1349 $this->assertFalse($this->get_debugging() === '');
1350 $this->assertEquals(6, count($records));
1351
1352 // negative limits = no limits
1353 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, -1, -1);
1354 $this->assertEquals(7, count($records));
1355
1356 // zero limits = no limits
1357 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 0);
1358 $this->assertEquals(7, count($records));
1359
1360 // only limitfrom = skips that number of records
1361 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 2, 0);
1362 $this->assertEquals(5, count($records));
1363 $this->assertEquals($inskey3, reset($records)->id);
1364 $this->assertEquals($inskey7, end($records)->id);
1365
1366 // only limitnum = fetches that number of records
1367 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 0, 3);
1368 $this->assertEquals(3, count($records));
1369 $this->assertEquals($inskey1, reset($records)->id);
1370 $this->assertEquals($inskey3, end($records)->id);
1371
1372 // both limitfrom and limitnum = skips limitfrom records and fetches limitnum ones
1373 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} ORDER BY id", null, 3, 2);
1374 $this->assertEquals(2, count($records));
1375 $this->assertEquals($inskey4, reset($records)->id);
1376 $this->assertEquals($inskey5, end($records)->id);
1377
1378 // both limitfrom and limitnum in query having subqueris
1379 // note the subquery skips records with course = 0 and 3
1380 $sql = "SELECT * FROM {{$tablename}}
1381 WHERE course NOT IN (
1382 SELECT course FROM {{$tablename}}
1383 WHERE course IN (0, 3))
1384 ORDER BY course";
1385 $records = $DB->get_records_sql($sql, null, 0, 2); // Skip 0, get 2
1386 $this->assertEquals(2, count($records));
1387 $this->assertEquals($inskey6, reset($records)->id);
1388 $this->assertEquals($inskey5, end($records)->id);
1389 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip 2, get 2
1390 $this->assertEquals(2, count($records));
1391 $this->assertEquals($inskey3, reset($records)->id);
1392 $this->assertEquals($inskey2, end($records)->id);
1393
1394 // test 2 tables with aliases and limits with order bys
1395 $sql = "SELECT t1.id, t1.course AS cid, t2.nametext
1396 FROM {{$tablename}} t1, {{$tablename2}} t2
1397 WHERE t2.course=t1.course
1398 ORDER BY t1.course, ". $DB->sql_compare_text('t2.nametext');
1399 $records = $DB->get_records_sql($sql, null, 2, 2); // Skip courses 3 and 6, get 4 and 5
1400 $this->assertEquals(2, count($records));
1401 $this->assertEquals('5', end($records)->cid);
1402 $this->assertEquals('4', reset($records)->cid);
1403
1404 // test 2 tables with aliases and limits with the highest INT limit works
1405 $records = $DB->get_records_sql($sql, null, 2, PHP_INT_MAX); // Skip course {3,6}, get {4,5}
1406 $this->assertEquals(2, count($records));
1407 $this->assertEquals('5', end($records)->cid);
1408 $this->assertEquals('4', reset($records)->cid);
1409
1410 // test 2 tables with aliases and limits with order bys (limit which is highest INT number)
1411 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, 2); // Skip all courses
1412 $this->assertEquals(0, count($records));
1413
1414 // test 2 tables with aliases and limits with order bys (limit which s highest INT number)
1415 $records = $DB->get_records_sql($sql, null, PHP_INT_MAX, PHP_INT_MAX); // Skip all courses
1416 $this->assertEquals(0, count($records));
1417
1418 // TODO: Test limits in queries having DISTINCT clauses
1419
1420 // note: fetching nulls, empties, LOBs already tested by test_update_record() no needed here
1421 }
1422
1423 public function test_get_records_menu() {
1424 $DB = $this->tdb;
1425 $dbman = $DB->get_manager();
1426
1427 $table = $this->get_test_table();
1428 $tablename = $table->getName();
1429
1430 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1431 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1432 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1433 $dbman->create_table($table);
1434
1435 $DB->insert_record($tablename, array('course' => 3));
1436 $DB->insert_record($tablename, array('course' => 3));
1437 $DB->insert_record($tablename, array('course' => 5));
1438 $DB->insert_record($tablename, array('course' => 2));
1439
1440 $records = $DB->get_records_menu($tablename, array('course' => 3));
1441 $this->assertTrue(is_array($records));
1442 $this->assertEquals(2, count($records));
1443 $this->assertFalse(empty($records[1]));
1444 $this->assertFalse(empty($records[2]));
1445 $this->assertEquals(3, $records[1]);
1446 $this->assertEquals(3, $records[2]);
1447
1448 // note: delegate limits testing to test_get_records_sql()
1449 }
1450
1451 public function test_get_records_select_menu() {
1452 $DB = $this->tdb;
1453 $dbman = $DB->get_manager();
1454
1455 $table = $this->get_test_table();
1456 $tablename = $table->getName();
1457
1458 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1459 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1460 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1461 $dbman->create_table($table);
1462
1463 $DB->insert_record($tablename, array('course' => 3));
1464 $DB->insert_record($tablename, array('course' => 2));
1465 $DB->insert_record($tablename, array('course' => 3));
1466 $DB->insert_record($tablename, array('course' => 5));
1467
1468 $records = $DB->get_records_select_menu($tablename, "course > ?", array(2));
1469 $this->assertTrue(is_array($records));
1470
1471 $this->assertEquals(3, count($records));
1472 $this->assertFalse(empty($records[1]));
1473 $this->assertTrue(empty($records[2]));
1474 $this->assertFalse(empty($records[3]));
1475 $this->assertFalse(empty($records[4]));
1476 $this->assertEquals(3, $records[1]);
1477 $this->assertEquals(3, $records[3]);
1478 $this->assertEquals(5, $records[4]);
1479
1480 // note: delegate limits testing to test_get_records_sql()
1481 }
1482
1483 public function test_get_records_sql_menu() {
1484 $DB = $this->tdb;
1485 $dbman = $DB->get_manager();
1486
1487 $table = $this->get_test_table();
1488 $tablename = $table->getName();
1489
1490 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1491 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1492 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1493 $dbman->create_table($table);
1494
1495 $DB->insert_record($tablename, array('course' => 3));
1496 $DB->insert_record($tablename, array('course' => 2));
1497 $DB->insert_record($tablename, array('course' => 3));
1498 $DB->insert_record($tablename, array('course' => 5));
1499
1500 $records = $DB->get_records_sql_menu("SELECT * FROM {{$tablename}} WHERE course > ?", array(2));
1501 $this->assertTrue(is_array($records));
1502
1503 $this->assertEquals(3, count($records));
1504 $this->assertFalse(empty($records[1]));
1505 $this->assertTrue(empty($records[2]));
1506 $this->assertFalse(empty($records[3]));
1507 $this->assertFalse(empty($records[4]));
1508 $this->assertEquals(3, $records[1]);
1509 $this->assertEquals(3, $records[3]);
1510 $this->assertEquals(5, $records[4]);
1511
1512 // note: delegate limits testing to test_get_records_sql()
1513 }
1514
1515 public function test_get_record() {
1516 $DB = $this->tdb;
1517 $dbman = $DB->get_manager();
1518
1519 $table = $this->get_test_table();
1520 $tablename = $table->getName();
1521
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');
1524 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1525 $dbman->create_table($table);
1526
1527 $DB->insert_record($tablename, array('course' => 3));
1528 $DB->insert_record($tablename, array('course' => 2));
1529
1530 $record = $DB->get_record($tablename, array('id' => 2));
1531 $this->assertTrue($record instanceof stdClass);
1532
1533 $this->assertEquals(2, $record->course);
1534 $this->assertEquals(2, $record->id);
1535 }
1536
1537
1538 public function test_get_record_select() {
1539 $DB = $this->tdb;
1540 $dbman = $DB->get_manager();
1541
1542 $table = $this->get_test_table();
1543 $tablename = $table->getName();
1544
1545 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1546 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1547 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1548 $dbman->create_table($table);
1549
1550 $DB->insert_record($tablename, array('course' => 3));
1551 $DB->insert_record($tablename, array('course' => 2));
1552
1553 $record = $DB->get_record_select($tablename, "id = ?", array(2));
1554 $this->assertTrue($record instanceof stdClass);
1555
1556 $this->assertEquals(2, $record->course);
1557
1558 // note: delegates limit testing to test_get_records_sql()
1559 }
1560
1561 public function test_get_record_sql() {
1562 $DB = $this->tdb;
1563 $dbman = $DB->get_manager();
1564
1565 $table = $this->get_test_table();
1566 $tablename = $table->getName();
1567
1568 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1569 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1570 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1571 $dbman->create_table($table);
1572
1573 $DB->insert_record($tablename, array('course' => 3));
1574 $DB->insert_record($tablename, array('course' => 2));
1575
1576 // standard use
1577 $record = $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(2));
1578 $this->assertTrue($record instanceof stdClass);
1579 $this->assertEquals(2, $record->course);
1580 $this->assertEquals(2, $record->id);
1581
1582 // backwards compatibility with $ignoremultiple
1583 $this->assertFalse((bool)IGNORE_MISSING);
1584 $this->assertTrue((bool)IGNORE_MULTIPLE);
1585
1586 // record not found - ignore
1587 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MISSING));
1588 $this->assertFalse($DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), IGNORE_MULTIPLE));
1589
1590 // record not found error
1591 try {
1592 $DB->get_record_sql("SELECT * FROM {{$tablename}} WHERE id = ?", array(666), MUST_EXIST);
1593 $this->fail("Exception expected");
1594 } catch (dml_missing_record_exception $e) {
1595 $this->assertTrue(true);
1596 }
1597
1598 $this->enable_debugging();
1599 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MISSING));
1600 $this->assertFalse($this->get_debugging() === '');
1601
1602 // multiple matches ignored
1603 $this->assertNotEmpty($DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), IGNORE_MULTIPLE));
1604
1605 // multiple found error
1606 try {
1607 $DB->get_record_sql("SELECT * FROM {{$tablename}}", array(), MUST_EXIST);
1608 $this->fail("Exception expected");
1609 } catch (dml_multiple_records_exception $e) {
1610 $this->assertTrue(true);
1611 }
1612 }
1613
1614 public function test_get_field() {
1615 $DB = $this->tdb;
1616 $dbman = $DB->get_manager();
1617
1618 $table = $this->get_test_table();
1619 $tablename = $table->getName();
1620
1621 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1622 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1623 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1624 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1625 $dbman->create_table($table);
1626
1627 $id1 = $DB->insert_record($tablename, array('course' => 3));
1628 $DB->insert_record($tablename, array('course' => 5));
1629 $DB->insert_record($tablename, array('course' => 5));
1630
1631 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id1)));
1632 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('course' => 3)));
1633
1634 $this->assertSame(false, $DB->get_field($tablename, 'course', array('course' => 11), IGNORE_MISSING));
1635 try {
1636 $DB->get_field($tablename, 'course', array('course' => 4), MUST_EXIST);
1637 $this->assertFail('Exception expected due to missing record');
1638 } catch (dml_exception $ex) {
1639 $this->assertTrue(true);
1640 }
1641
1642 $this->enable_debugging();
1643 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MULTIPLE));
1644 $this->assertSame($this->get_debugging(), '');
1645
1646 $this->enable_debugging();
1647 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('course' => 5), IGNORE_MISSING));
1648 $this->assertFalse($this->get_debugging() === '');
1649
1650 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
1651 $conditions = array('onetext' => '1');
1652 try {
1653 $DB->get_field($tablename, 'course', $conditions);
1654 if (debugging()) {
1655 // only in debug mode - hopefully all devs test code in debug mode...
1656 $this->fail('An Exception is missing, expected due to equating of text fields');
1657 }
1658 } catch (exception $e) {
1659 $this->assertTrue($e instanceof dml_exception);
1660 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
1661 }
1662 }
1663
1664 public function test_get_field_select() {
1665 $DB = $this->tdb;
1666 $dbman = $DB->get_manager();
1667
1668 $table = $this->get_test_table();
1669 $tablename = $table->getName();
1670
1671 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1672 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1673 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1674 $dbman->create_table($table);
1675
1676 $DB->insert_record($tablename, array('course' => 3));
1677
1678 $this->assertEquals(3, $DB->get_field_select($tablename, 'course', "id = ?", array(1)));
1679 }
1680
1681 public function test_get_field_sql() {
1682 $DB = $this->tdb;
1683 $dbman = $DB->get_manager();
1684
1685 $table = $this->get_test_table();
1686 $tablename = $table->getName();
1687
1688 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1689 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1690 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1691 $dbman->create_table($table);
1692
1693 $DB->insert_record($tablename, array('course' => 3));
1694
1695 $this->assertEquals(3, $DB->get_field_sql("SELECT course FROM {{$tablename}} WHERE id = ?", array(1)));
1696 }
1697
1698 public function test_get_fieldset_select() {
1699 $DB = $this->tdb;
1700 $dbman = $DB->get_manager();
1701
1702 $table = $this->get_test_table();
1703 $tablename = $table->getName();
1704
1705 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1706 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1707 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1708 $dbman->create_table($table);
1709
1710 $DB->insert_record($tablename, array('course' => 1));
1711 $DB->insert_record($tablename, array('course' => 3));
1712 $DB->insert_record($tablename, array('course' => 2));
1713 $DB->insert_record($tablename, array('course' => 6));
1714
1715 $fieldset = $DB->get_fieldset_select($tablename, 'course', "course > ?", array(1));
1716 $this->assertTrue(is_array($fieldset));
1717
1718 $this->assertEquals(3, count($fieldset));
1719 $this->assertEquals(3, $fieldset[0]);
1720 $this->assertEquals(2, $fieldset[1]);
1721 $this->assertEquals(6, $fieldset[2]);
1722 }
1723
1724 public function test_get_fieldset_sql() {
1725 $DB = $this->tdb;
1726 $dbman = $DB->get_manager();
1727
1728 $table = $this->get_test_table();
1729 $tablename = $table->getName();
1730
1731 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1732 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1733 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1734 $dbman->create_table($table);
1735
1736 $DB->insert_record($tablename, array('course' => 1));
1737 $DB->insert_record($tablename, array('course' => 3));
1738 $DB->insert_record($tablename, array('course' => 2));
1739 $DB->insert_record($tablename, array('course' => 6));
1740
1741 $fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
1742 $this->assertTrue(is_array($fieldset));
1743
1744 $this->assertEquals(3, count($fieldset));
1745 $this->assertEquals(2, $fieldset[0]);
1746 $this->assertEquals(3, $fieldset[1]);
1747 $this->assertEquals(4, $fieldset[2]);
1748 }
1749
1750 public function test_insert_record_raw() {
1751 $DB = $this->tdb;
1752 $dbman = $DB->get_manager();
1753
1754 $table = $this->get_test_table();
1755 $tablename = $table->getName();
1756
1757 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1758 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1759 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1760 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1761 $dbman->create_table($table);
1762
1763 $record = (object)array('course' => 1, 'onechar' => 'xx');
1764 $before = clone($record);
1765 $result = $DB->insert_record_raw($tablename, $record);
1766 $this->assertSame(1, $result);
1767 $this->assertEquals($record, $before);
1768
1769 $record = $DB->get_record($tablename, array('course' => 1));
1770 $this->assertTrue($record instanceof stdClass);
1771 $this->assertSame('xx', $record->onechar);
1772
1773 $result = $DB->insert_record_raw($tablename, array('course' => 2, 'onechar' => 'yy'), false);
1774 $this->assertSame(true, $result);
1775
1776 // note: bulk not implemented yet
1777 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'zz'), true, true);
1778 $record = $DB->get_record($tablename, array('course' => 3));
1779 $this->assertTrue($record instanceof stdClass);
1780 $this->assertSame('zz', $record->onechar);
1781
1782 // custom sequence (id) - returnid is ignored
1783 $result = $DB->insert_record_raw($tablename, array('id' => 10, 'course' => 3, 'onechar' => 'bb'), true, false, true);
1784 $this->assertSame(true, $result);
1785 $record = $DB->get_record($tablename, array('id' => 10));
1786 $this->assertTrue($record instanceof stdClass);
1787 $this->assertSame('bb', $record->onechar);
1788
1789 // custom sequence - missing id error
1790 try {
1791 $DB->insert_record_raw($tablename, array('course' => 3, 'onechar' => 'bb'), true, false, true);
1792 $this->assertFail('Exception expected due to missing record');
1793 } catch (coding_exception $ex) {
1794 $this->assertTrue(true);
1795 }
1796
1797 // wrong column error
1798 try {
1799 $DB->insert_record_raw($tablename, array('xxxxx' => 3, 'onechar' => 'bb'));
1800 $this->assertFail('Exception expected due to invalid column');
1801 } catch (dml_exception $ex) {
1802 $this->assertTrue(true);
1803 }
1804 }
1805
1806 public function test_insert_record() {
1807 // All the information in this test is fetched from DB by get_recordset() so we
1808 // have such method properly tested against nulls, empties and friends...
1809
1810 $DB = $this->tdb;
1811 $dbman = $DB->get_manager();
1812
1813 $table = $this->get_test_table();
1814 $tablename = $table->getName();
1815
1816 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1817 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
1818 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
1819 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
1820 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
1821 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
1822 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
1823 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1824 $dbman->create_table($table);
1825
1826 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
1827 $record = $DB->get_record($tablename, array('course' => 1));
1828 $this->assertEquals(1, $record->id);
1829 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied
1830 $this->assertEquals(200, $record->onenum);
1831 $this->assertSame('onestring', $record->onechar);
1832 $this->assertNull($record->onetext);
1833 $this->assertNull($record->onebinary);
1834
1835 // without returning id, bulk not implemented
1836 $result = $this->assertSame(true, $DB->insert_record($tablename, array('course' => 99), false, true));
1837 $record = $DB->get_record($tablename, array('course' => 99));
1838 $this->assertEquals(2, $record->id);
1839 $this->assertEquals(99, $record->course);
1840
1841 // Check nulls are set properly for all types
1842 $record = new stdClass();
1843 $record->oneint = null;
1844 $record->onenum = null;
1845 $record->onechar = null;
1846 $record->onetext = null;
1847 $record->onebinary = null;
1848 $recid = $DB->insert_record($tablename, $record);
1849 $record = $DB->get_record($tablename, array('id' => $recid));
1850 $this->assertEquals(0, $record->course);
1851 $this->assertNull($record->oneint);
1852 $this->assertNull($record->onenum);
1853 $this->assertNull($record->onechar);
1854 $this->assertNull($record->onetext);
1855 $this->assertNull($record->onebinary);
1856
1857 // Check zeros are set properly for all types
1858 $record = new stdClass();
1859 $record->oneint = 0;
1860 $record->onenum = 0;
1861 $recid = $DB->insert_record($tablename, $record);
1862 $record = $DB->get_record($tablename, array('id' => $recid));
1863 $this->assertEquals(0, $record->oneint);
1864 $this->assertEquals(0, $record->onenum);
1865
1866 // Check booleans are set properly for all types
1867 $record = new stdClass();
1868 $record->oneint = true; // trues
1869 $record->onenum = true;
1870 $record->onechar = true;
1871 $record->onetext = true;
1872 $recid = $DB->insert_record($tablename, $record);
1873 $record = $DB->get_record($tablename, array('id' => $recid));
1874 $this->assertEquals(1, $record->oneint);
1875 $this->assertEquals(1, $record->onenum);
1876 $this->assertEquals(1, $record->onechar);
1877 $this->assertEquals(1, $record->onetext);
1878
1879 $record = new stdClass();
1880 $record->oneint = false; // falses
1881 $record->onenum = false;
1882 $record->onechar = false;
1883 $record->onetext = false;
1884 $recid = $DB->insert_record($tablename, $record);
1885 $record = $DB->get_record($tablename, array('id' => $recid));
1886 $this->assertEquals(0, $record->oneint);
1887 $this->assertEquals(0, $record->onenum);
1888 $this->assertEquals(0, $record->onechar);
1889 $this->assertEquals(0, $record->onetext);
1890
1891 // Check string data causes exception in numeric types
1892 $record = new stdClass();
1893 $record->oneint = 'onestring';
1894 $record->onenum = 0;
1895 try {
1896 $DB->insert_record($tablename, $record);
1897 $this->fail("Expecting an exception, none occurred");
1898 } catch (exception $e) {
1899 $this->assertTrue($e instanceof dml_exception);
1900 }
1901 $record = new stdClass();
1902 $record->oneint = 0;
1903 $record->onenum = 'onestring';
1904 try {
1905 $DB->insert_record($tablename, $record);
1906 $this->fail("Expecting an exception, none occurred");
1907 } catch (exception $e) {
1908 $this->assertTrue($e instanceof dml_exception);
1909 }
1910
1911 // Check empty string data is stored as 0 in numeric datatypes
1912 $record = new stdClass();
1913 $record->oneint = ''; // empty string
1914 $record->onenum = 0;
1915 $recid = $DB->insert_record($tablename, $record);
1916 $record = $DB->get_record($tablename, array('id' => $recid));
1917 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
1918
1919 $record = new stdClass();
1920 $record->oneint = 0;
1921 $record->onenum = ''; // empty string
1922 $recid = $DB->insert_record($tablename, $record);
1923 $record = $DB->get_record($tablename, array('id' => $recid));
1924 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
1925
1926 // Check empty strings are set properly in string types
1927 $record = new stdClass();
1928 $record->oneint = 0;
1929 $record->onenum = 0;
1930 $record->onechar = '';
1931 $record->onetext = '';
1932 $recid = $DB->insert_record($tablename, $record);
1933 $record = $DB->get_record($tablename, array('id' => $recid));
1934 $this->assertTrue($record->onechar === '');
1935 $this->assertTrue($record->onetext === '');
1936
1937 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
1938 $record = new stdClass();
1939 $record->oneint = ((210.10 + 39.92) - 150.02);
1940 $record->onenum = ((210.10 + 39.92) - 150.02);
1941 $recid = $DB->insert_record($tablename, $record);
1942 $record = $DB->get_record($tablename, array('id' => $recid));
1943 $this->assertEquals(100, $record->oneint);
1944 $this->assertEquals(100, $record->onenum);
1945
1946 // Check various quotes/backslashes combinations in string types
1947 $teststrings = array(
1948 'backslashes and quotes alone (even): "" \'\' \\\\',
1949 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
1950 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
1951 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
1952 foreach ($teststrings as $teststring) {
1953 $record = new stdClass();
1954 $record->onechar = $teststring;
1955 $record->onetext = $teststring;
1956 $recid = $DB->insert_record($tablename, $record);
1957 $record = $DB->get_record($tablename, array('id' => $recid));
1958 $this->assertEquals($teststring, $record->onechar);
1959 $this->assertEquals($teststring, $record->onetext);
1960 }
1961
1962 // Check LOBs in text/binary columns
1963 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
1964 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
1965 $record = new stdClass();
1966 $record->onetext = $clob;
1967 $record->onebinary = $blob;
1968 $recid = $DB->insert_record($tablename, $record);
1969 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1970 $record = $rs->current();
1971 $rs->close();
1972 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
1973 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
1974
1975 // And "small" LOBs too, just in case
1976 $newclob = substr($clob, 0, 500);
1977 $newblob = substr($blob, 0, 250);
1978 $record = new stdClass();
1979 $record->onetext = $newclob;
1980 $record->onebinary = $newblob;
1981 $recid = $DB->insert_record($tablename, $record);
1982 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1983 $record = $rs->current();
1984 $rs->close();
1985 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
1986 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
1987 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
1988
1989 // And "diagnostic" LOBs too, just in case
1990 $newclob = '\'"\\;/ěščřžýáíé';
1991 $newblob = '\'"\\;/ěščřžýáíé';
1992 $record = new stdClass();
1993 $record->onetext = $newclob;
1994 $record->onebinary = $newblob;
1995 $recid = $DB->insert_record($tablename, $record);
1996 $rs = $DB->get_recordset($tablename, array('id' => $recid));
1997 $record = $rs->current();
1998 $rs->close();
1999 $this->assertSame($newclob, $record->onetext);
2000 $this->assertSame($newblob, $record->onebinary);
2001 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
2002
2003 // test data is not modified
2004 $record = new stdClass();
2005 $record->id = -1; // has to be ignored
2006 $record->course = 3;
2007 $record->lalala = 'lalal'; // unused
2008 $before = clone($record);
2009 $DB->insert_record($tablename, $record);
2010 $this->assertEquals($record, $before);
2011
2012 // make sure the id is always increasing and never reuses the same id
2013 $id1 = $DB->insert_record($tablename, array('course' => 3));
2014 $id2 = $DB->insert_record($tablename, array('course' => 3));
2015 $this->assertTrue($id1 < $id2);
2016 $DB->delete_records($tablename, array('id'=>$id2));
2017 $id3 = $DB->insert_record($tablename, array('course' => 3));
2018 $this->assertTrue($id2 < $id3);
2019 $DB->delete_records($tablename, array());
2020 $id4 = $DB->insert_record($tablename, array('course' => 3));
2021 $this->assertTrue($id3 < $id4);
2022
2023 // Test saving a float in a CHAR column, and reading it back.
2024 $id = $DB->insert_record($tablename, array('onechar' => 1.0));
2025 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2026 $id = $DB->insert_record($tablename, array('onechar' => 1e20));
2027 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2028 $id = $DB->insert_record($tablename, array('onechar' => 1e-4));
2029 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2030 $id = $DB->insert_record($tablename, array('onechar' => 1e-5));
2031 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2032 $id = $DB->insert_record($tablename, array('onechar' => 1e-300));
2033 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2034 $id = $DB->insert_record($tablename, array('onechar' => 1e300));
2035 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2036
2037 // Test saving a float in a TEXT column, and reading it back.
2038 $id = $DB->insert_record($tablename, array('onetext' => 1.0));
2039 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2040 $id = $DB->insert_record($tablename, array('onetext' => 1e20));
2041 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2042 $id = $DB->insert_record($tablename, array('onetext' => 1e-4));
2043 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2044 $id = $DB->insert_record($tablename, array('onetext' => 1e-5));
2045 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2046 $id = $DB->insert_record($tablename, array('onetext' => 1e-300));
2047 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2048 $id = $DB->insert_record($tablename, array('onetext' => 1e300));
2049 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2050 }
2051
2052 public function test_import_record() {
2053 // All the information in this test is fetched from DB by get_recordset() so we
2054 // have such method properly tested against nulls, empties and friends...
2055
2056 $DB = $this->tdb;
2057 $dbman = $DB->get_manager();
2058
2059 $table = $this->get_test_table();
2060 $tablename = $table->getName();
2061
2062 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2063 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2064 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
2065 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2066 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2067 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2068 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2069 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2070 $dbman->create_table($table);
2071
2072 $this->assertSame(1, $DB->insert_record($tablename, array('course' => 1), true));
2073 $record = $DB->get_record($tablename, array('course' => 1));
2074 $this->assertEquals(1, $record->id);
2075 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied
2076 $this->assertEquals(200, $record->onenum);
2077 $this->assertSame('onestring', $record->onechar);
2078 $this->assertNull($record->onetext);
2079 $this->assertNull($record->onebinary);
2080
2081 // ignore extra columns
2082 $record = (object)array('id'=>13, 'course'=>2, 'xxxx'=>788778);
2083 $before = clone($record);
2084 $this->assertSame(true, $DB->import_record($tablename, $record));
2085 $this->assertEquals($record, $before);
2086 $records = $DB->get_records($tablename);
2087 $this->assertEquals(2, $records[13]->course);
2088
2089 // Check nulls are set properly for all types
2090 $record = new stdClass();
2091 $record->id = 20;
2092 $record->oneint = null;
2093 $record->onenum = null;
2094 $record->onechar = null;
2095 $record->onetext = null;
2096 $record->onebinary = null;
2097 $this->assertTrue($DB->import_record($tablename, $record));
2098 $record = $DB->get_record($tablename, array('id' => 20));
2099 $this->assertEquals(0, $record->course);
2100 $this->assertNull($record->oneint);
2101 $this->assertNull($record->onenum);
2102 $this->assertNull($record->onechar);
2103 $this->assertNull($record->onetext);
2104 $this->assertNull($record->onebinary);
2105
2106 // Check zeros are set properly for all types
2107 $record = new stdClass();
2108 $record->id = 23;
2109 $record->oneint = 0;
2110 $record->onenum = 0;
2111 $this->assertTrue($DB->import_record($tablename, $record));
2112 $record = $DB->get_record($tablename, array('id' => 23));
2113 $this->assertEquals(0, $record->oneint);
2114 $this->assertEquals(0, $record->onenum);
2115
2116 // Check string data causes exception in numeric types
2117 $record = new stdClass();
2118 $record->id = 32;
2119 $record->oneint = 'onestring';
2120 $record->onenum = 0;
2121 try {
2122 $DB->import_record($tablename, $record);
2123 $this->fail("Expecting an exception, none occurred");
2124 } catch (exception $e) {
2125 $this->assertTrue($e instanceof dml_exception);
2126 }
2127 $record = new stdClass();
2128 $record->id = 35;
2129 $record->oneint = 0;
2130 $record->onenum = 'onestring';
2131 try {
2132 $DB->import_record($tablename, $record);
2133 $this->fail("Expecting an exception, none occurred");
2134 } catch (exception $e) {
2135 $this->assertTrue($e instanceof dml_exception);
2136 }
2137
2138 // Check empty strings are set properly in string types
2139 $record = new stdClass();
2140 $record->id = 44;
2141 $record->oneint = 0;
2142 $record->onenum = 0;
2143 $record->onechar = '';
2144 $record->onetext = '';
2145 $this->assertTrue($DB->import_record($tablename, $record));
2146 $record = $DB->get_record($tablename, array('id' => 44));
2147 $this->assertTrue($record->onechar === '');
2148 $this->assertTrue($record->onetext === '');
2149
2150 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2151 $record = new stdClass();
2152 $record->id = 47;
2153 $record->oneint = ((210.10 + 39.92) - 150.02);
2154 $record->onenum = ((210.10 + 39.92) - 150.02);
2155 $this->assertTrue($DB->import_record($tablename, $record));
2156 $record = $DB->get_record($tablename, array('id' => 47));
2157 $this->assertEquals(100, $record->oneint);
2158 $this->assertEquals(100, $record->onenum);
2159
2160 // Check various quotes/backslashes combinations in string types
2161 $i = 50;
2162 $teststrings = array(
2163 'backslashes and quotes alone (even): "" \'\' \\\\',
2164 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2165 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2166 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2167 foreach ($teststrings as $teststring) {
2168 $record = new stdClass();
2169 $record->id = $i;
2170 $record->onechar = $teststring;
2171 $record->onetext = $teststring;
2172 $this->assertTrue($DB->import_record($tablename, $record));
2173 $record = $DB->get_record($tablename, array('id' => $i));
2174 $this->assertEquals($teststring, $record->onechar);
2175 $this->assertEquals($teststring, $record->onetext);
2176 $i = $i + 3;
2177 }
2178
2179 // Check LOBs in text/binary columns
2180 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2181 $record = new stdClass();
2182 $record->id = 70;
2183 $record->onetext = $clob;
2184 $record->onebinary = '';
2185 $this->assertTrue($DB->import_record($tablename, $record));
2186 $rs = $DB->get_recordset($tablename, array('id' => 70));
2187 $record = $rs->current();
2188 $rs->close();
2189 $this->assertEquals($clob, $record->onetext, 'Test CLOB insert (full contents output disabled)');
2190
2191 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2192 $record = new stdClass();
2193 $record->id = 71;
2194 $record->onetext = '';
2195 $record->onebinary = $blob;
2196 $this->assertTrue($DB->import_record($tablename, $record));
2197 $rs = $DB->get_recordset($tablename, array('id' => 71));
2198 $record = $rs->current();
2199 $rs->close();
2200 $this->assertEquals($blob, $record->onebinary, 'Test BLOB insert (full contents output disabled)');
2201
2202 // And "small" LOBs too, just in case
2203 $newclob = substr($clob, 0, 500);
2204 $newblob = substr($blob, 0, 250);
2205 $record = new stdClass();
2206 $record->id = 73;
2207 $record->onetext = $newclob;
2208 $record->onebinary = $newblob;
2209 $this->assertTrue($DB->import_record($tablename, $record));
2210 $rs = $DB->get_recordset($tablename, array('id' => 73));
2211 $record = $rs->current();
2212 $rs->close();
2213 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB insert (full contents output disabled)');
2214 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB insert (full contents output disabled)');
2215 $this->assertEquals(false, $rs->key()); // Ensure recordset key() method to be working ok after closing
2216 }
2217
2218 public function test_update_record_raw() {
2219 $DB = $this->tdb;
2220 $dbman = $DB->get_manager();
2221
2222 $table = $this->get_test_table();
2223 $tablename = $table->getName();
2224
2225 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2226 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2227 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2228 $dbman->create_table($table);
2229
2230 $DB->insert_record($tablename, array('course' => 1));
2231 $DB->insert_record($tablename, array('course' => 3));
2232
2233 $record = $DB->get_record($tablename, array('course' => 1));
2234 $record->course = 2;
2235 $this->assertTrue($DB->update_record_raw($tablename, $record));
2236 $this->assertEquals(0, $DB->count_records($tablename, array('course' => 1)));
2237 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 2)));
2238 $this->assertEquals(1, $DB->count_records($tablename, array('course' => 3)));
2239
2240 $record = $DB->get_record($tablename, array('course' => 3));
2241 $record->xxxxx = 2;
2242 try {
2243 $DB->update_record_raw($tablename, $record);
2244 $this->fail("Expecting an exception, none occurred");
2245 } catch (Exception $e) {
2246 $this->assertTrue($e instanceof moodle_exception);
2247 }
2248
2249 $record = $DB->get_record($tablename, array('course' => 3));
2250 unset($record->id);
2251 try {
2252 $DB->update_record_raw($tablename, $record);
2253 $this->fail("Expecting an exception, none occurred");
2254 } catch (Exception $e) {
2255 $this->assertTrue($e instanceof coding_exception);
2256 }
2257 }
2258
2259 public function test_update_record() {
2260
2261 // All the information in this test is fetched from DB by get_record() so we
2262 // have such method properly tested against nulls, empties and friends...
2263
2264 $DB = $this->tdb;
2265 $dbman = $DB->get_manager();
2266
2267 $table = $this->get_test_table();
2268 $tablename = $table->getName();
2269
2270 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2271 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2272 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, 100);
2273 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
2274 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
2275 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2276 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2277 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2278 $dbman->create_table($table);
2279
2280 $DB->insert_record($tablename, array('course' => 1));
2281 $record = $DB->get_record($tablename, array('course' => 1));
2282 $record->course = 2;
2283
2284 $this->assertTrue($DB->update_record($tablename, $record));
2285 $this->assertFalse($record = $DB->get_record($tablename, array('course' => 1)));
2286 $this->assertNotEmpty($record = $DB->get_record($tablename, array('course' => 2)));
2287 $this->assertEquals(100, $record->oneint); // Just check column defaults have been applied
2288 $this->assertEquals(200, $record->onenum);
2289 $this->assertEquals('onestring', $record->onechar);
2290 $this->assertNull($record->onetext);
2291 $this->assertNull($record->onebinary);
2292
2293 // Check nulls are set properly for all types
2294 $record->oneint = null;
2295 $record->onenum = null;
2296 $record->onechar = null;
2297 $record->onetext = null;
2298 $record->onebinary = null;
2299 $DB->update_record($tablename, $record);
2300 $record = $DB->get_record($tablename, array('course' => 2));
2301 $this->assertNull($record->oneint);
2302 $this->assertNull($record->onenum);
2303 $this->assertNull($record->onechar);
2304 $this->assertNull($record->onetext);
2305 $this->assertNull($record->onebinary);
2306
2307 // Check zeros are set properly for all types
2308 $record->oneint = 0;
2309 $record->onenum = 0;
2310 $DB->update_record($tablename, $record);
2311 $record = $DB->get_record($tablename, array('course' => 2));
2312 $this->assertEquals(0, $record->oneint);
2313 $this->assertEquals(0, $record->onenum);
2314
2315 // Check booleans are set properly for all types
2316 $record->oneint = true; // trues
2317 $record->onenum = true;
2318 $record->onechar = true;
2319 $record->onetext = true;
2320 $DB->update_record($tablename, $record);
2321 $record = $DB->get_record($tablename, array('course' => 2));
2322 $this->assertEquals(1, $record->oneint);
2323 $this->assertEquals(1, $record->onenum);
2324 $this->assertEquals(1, $record->onechar);
2325 $this->assertEquals(1, $record->onetext);
2326
2327 $record->oneint = false; // falses
2328 $record->onenum = false;
2329 $record->onechar = false;
2330 $record->onetext = false;
2331 $DB->update_record($tablename, $record);
2332 $record = $DB->get_record($tablename, array('course' => 2));
2333 $this->assertEquals(0, $record->oneint);
2334 $this->assertEquals(0, $record->onenum);
2335 $this->assertEquals(0, $record->onechar);
2336 $this->assertEquals(0, $record->onetext);
2337
2338 // Check string data causes exception in numeric types
2339 $record->oneint = 'onestring';
2340 $record->onenum = 0;
2341 try {
2342 $DB->update_record($tablename, $record);
2343 $this->fail("Expecting an exception, none occurred");
2344 } catch (exception $e) {
2345 $this->assertTrue($e instanceof dml_exception);
2346 }
2347 $record->oneint = 0;
2348 $record->onenum = 'onestring';
2349 try {
2350 $DB->update_record($tablename, $record);
2351 $this->fail("Expecting an exception, none occurred");
2352 } catch (exception $e) {
2353 $this->assertTrue($e instanceof dml_exception);
2354 }
2355
2356 // Check empty string data is stored as 0 in numeric datatypes
2357 $record->oneint = ''; // empty string
2358 $record->onenum = 0;
2359 $DB->update_record($tablename, $record);
2360 $record = $DB->get_record($tablename, array('course' => 2));
2361 $this->assertTrue(is_numeric($record->oneint) && $record->oneint == 0);
2362
2363 $record->oneint = 0;
2364 $record->onenum = ''; // empty string
2365 $DB->update_record($tablename, $record);
2366 $record = $DB->get_record($tablename, array('course' => 2));
2367 $this->assertTrue(is_numeric($record->onenum) && $record->onenum == 0);
2368
2369 // Check empty strings are set properly in string types
2370 $record->oneint = 0;
2371 $record->onenum = 0;
2372 $record->onechar = '';
2373 $record->onetext = '';
2374 $DB->update_record($tablename, $record);
2375 $record = $DB->get_record($tablename, array('course' => 2));
2376 $this->assertTrue($record->onechar === '');
2377 $this->assertTrue($record->onetext === '');
2378
2379 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2380 $record->oneint = ((210.10 + 39.92) - 150.02);
2381 $record->onenum = ((210.10 + 39.92) - 150.02);
2382 $DB->update_record($tablename, $record);
2383 $record = $DB->get_record($tablename, array('course' => 2));
2384 $this->assertEquals(100, $record->oneint);
2385 $this->assertEquals(100, $record->onenum);
2386
2387 // Check various quotes/backslashes combinations in string types
2388 $teststrings = array(
2389 'backslashes and quotes alone (even): "" \'\' \\\\',
2390 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2391 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2392 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2393 foreach ($teststrings as $teststring) {
2394 $record->onechar = $teststring;
2395 $record->onetext = $teststring;
2396 $DB->update_record($tablename, $record);
2397 $record = $DB->get_record($tablename, array('course' => 2));
2398 $this->assertEquals($teststring, $record->onechar);
2399 $this->assertEquals($teststring, $record->onetext);
2400 }
2401
2402 // Check LOBs in text/binary columns
2403 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2404 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2405 $record->onetext = $clob;
2406 $record->onebinary = $blob;
2407 $DB->update_record($tablename, $record);
2408 $record = $DB->get_record($tablename, array('course' => 2));
2409 $this->assertEquals($clob, $record->onetext, 'Test CLOB update (full contents output disabled)');
2410 $this->assertEquals($blob, $record->onebinary, 'Test BLOB update (full contents output disabled)');
2411
2412 // And "small" LOBs too, just in case
2413 $newclob = substr($clob, 0, 500);
2414 $newblob = substr($blob, 0, 250);
2415 $record->onetext = $newclob;
2416 $record->onebinary = $newblob;
2417 $DB->update_record($tablename, $record);
2418 $record = $DB->get_record($tablename, array('course' => 2));
2419 $this->assertEquals($newclob, $record->onetext, 'Test "small" CLOB update (full contents output disabled)');
2420 $this->assertEquals($newblob, $record->onebinary, 'Test "small" BLOB update (full contents output disabled)');
2421
2422 // Test saving a float in a CHAR column, and reading it back.
2423 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2424 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1.0));
2425 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2426 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e20));
2427 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2428 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-4));
2429 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2430 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-5));
2431 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2432 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e-300));
2433 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2434 $DB->update_record($tablename, array('id' => $id, 'onechar' => 1e300));
2435 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2436
2437 // Test saving a float in a TEXT column, and reading it back.
2438 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2439 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1.0));
2440 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2441 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e20));
2442 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2443 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-4));
2444 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2445 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-5));
2446 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2447 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e-300));
2448 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2449 $DB->update_record($tablename, array('id' => $id, 'onetext' => 1e300));
2450 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2451 }
2452
2453 public function test_set_field() {
2454 $DB = $this->tdb;
2455 $dbman = $DB->get_manager();
2456
2457 $table = $this->get_test_table();
2458 $tablename = $table->getName();
2459
2460 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2461 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2462 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2463 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2464 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2465 $dbman->create_table($table);
2466
2467 // simple set_field
2468 $id1 = $DB->insert_record($tablename, array('course' => 1));
2469 $id2 = $DB->insert_record($tablename, array('course' => 1));
2470 $id3 = $DB->insert_record($tablename, array('course' => 3));
2471 $this->assertTrue($DB->set_field($tablename, 'course', 2, array('id' => $id1)));
2472 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => $id1)));
2473 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2474 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2475 $DB->delete_records($tablename, array());
2476
2477 // multiple fields affected
2478 $id1 = $DB->insert_record($tablename, array('course' => 1));
2479 $id2 = $DB->insert_record($tablename, array('course' => 1));
2480 $id3 = $DB->insert_record($tablename, array('course' => 3));
2481 $DB->set_field($tablename, 'course', '5', array('course' => 1));
2482 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2483 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2484 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2485 $DB->delete_records($tablename, array());
2486
2487 // no field affected
2488 $id1 = $DB->insert_record($tablename, array('course' => 1));
2489 $id2 = $DB->insert_record($tablename, array('course' => 1));
2490 $id3 = $DB->insert_record($tablename, array('course' => 3));
2491 $DB->set_field($tablename, 'course', '5', array('course' => 0));
2492 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id1)));
2493 $this->assertEquals(1, $DB->get_field($tablename, 'course', array('id' => $id2)));
2494 $this->assertEquals(3, $DB->get_field($tablename, 'course', array('id' => $id3)));
2495 $DB->delete_records($tablename, array());
2496
2497 // all fields - no condition
2498 $id1 = $DB->insert_record($tablename, array('course' => 1));
2499 $id2 = $DB->insert_record($tablename, array('course' => 1));
2500 $id3 = $DB->insert_record($tablename, array('course' => 3));
2501 $DB->set_field($tablename, 'course', 5, array());
2502 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id1)));
2503 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id2)));
2504 $this->assertEquals(5, $DB->get_field($tablename, 'course', array('id' => $id3)));
2505
2506 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2507 $conditions = array('onetext' => '1');
2508 try {
2509 $DB->set_field($tablename, 'onechar', 'frog', $conditions);
2510 if (debugging()) {
2511 // only in debug mode - hopefully all devs test code in debug mode...
2512 $this->fail('An Exception is missing, expected due to equating of text fields');
2513 }
2514 } catch (exception $e) {
2515 $this->assertTrue($e instanceof dml_exception);
2516 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
2517 }
2518
2519 // Test saving a float in a CHAR column, and reading it back.
2520 $id = $DB->insert_record($tablename, array('onechar' => 'X'));
2521 $DB->set_field($tablename, 'onechar', 1.0, array('id' => $id));
2522 $this->assertEquals(1.0, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2523 $DB->set_field($tablename, 'onechar', 1e20, array('id' => $id));
2524 $this->assertEquals(1e20, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2525 $DB->set_field($tablename, 'onechar', 1e-4, array('id' => $id));
2526 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2527 $DB->set_field($tablename, 'onechar', 1e-5, array('id' => $id));
2528 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2529 $DB->set_field($tablename, 'onechar', 1e-300, array('id' => $id));
2530 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2531 $DB->set_field($tablename, 'onechar', 1e300, array('id' => $id));
2532 $this->assertEquals(1e300, $DB->get_field($tablename, 'onechar', array('id' => $id)));
2533
2534 // Test saving a float in a TEXT column, and reading it back.
2535 $id = $DB->insert_record($tablename, array('onetext' => 'X'));
2536 $DB->set_field($tablename, 'onetext', 1.0, array('id' => $id));
2537 $this->assertEquals(1.0, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2538 $DB->set_field($tablename, 'onetext', 1e20, array('id' => $id));
2539 $this->assertEquals(1e20, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2540 $DB->set_field($tablename, 'onetext', 1e-4, array('id' => $id));
2541 $this->assertEquals(1e-4, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2542 $DB->set_field($tablename, 'onetext', 1e-5, array('id' => $id));
2543 $this->assertEquals(1e-5, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2544 $DB->set_field($tablename, 'onetext', 1e-300, array('id' => $id));
2545 $this->assertEquals(1e-300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2546 $DB->set_field($tablename, 'onetext', 1e300, array('id' => $id));
2547 $this->assertEquals(1e300, $DB->get_field($tablename, 'onetext', array('id' => $id)));
2548
2549 // Note: All the nulls, booleans, empties, quoted and backslashes tests
2550 // go to set_field_select() because set_field() is just one wrapper over it
2551 }
2552
2553 public function test_set_field_select() {
2554
2555 // All the information in this test is fetched from DB by get_field() so we
2556 // have such method properly tested against nulls, empties and friends...
2557
2558 $DB = $this->tdb;
2559 $dbman = $DB->get_manager();
2560
2561 $table = $this->get_test_table();
2562 $tablename = $table->getName();
2563
2564 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2565 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2566 $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null);
2567 $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null);
2568 $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null);
2569 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2570 $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
2571 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2572 $dbman->create_table($table);
2573
2574 $DB->insert_record($tablename, array('course' => 1));
2575
2576 $this->assertTrue($DB->set_field_select($tablename, 'course', 2, 'id = ?', array(1)));
2577 $this->assertEquals(2, $DB->get_field($tablename, 'course', array('id' => 1)));
2578
2579 // Check nulls are set properly for all types
2580 $DB->set_field_select($tablename, 'oneint', null, 'id = ?', array(1)); // trues
2581 $DB->set_field_select($tablename, 'onenum', null, 'id = ?', array(1));
2582 $DB->set_field_select($tablename, 'onechar', null, 'id = ?', array(1));
2583 $DB->set_field_select($tablename, 'onetext', null, 'id = ?', array(1));
2584 $DB->set_field_select($tablename, 'onebinary', null, 'id = ?', array(1));
2585 $this->assertNull($DB->get_field($tablename, 'oneint', array('id' => 1)));
2586 $this->assertNull($DB->get_field($tablename, 'onenum', array('id' => 1)));
2587 $this->assertNull($DB->get_field($tablename, 'onechar', array('id' => 1)));
2588 $this->assertNull($DB->get_field($tablename, 'onetext', array('id' => 1)));
2589 $this->assertNull($DB->get_field($tablename, 'onebinary', array('id' => 1)));
2590
2591 // Check zeros are set properly for all types
2592 $DB->set_field_select($tablename, 'oneint', 0, 'id = ?', array(1));
2593 $DB->set_field_select($tablename, 'onenum', 0, 'id = ?', array(1));
2594 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2595 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2596
2597 // Check booleans are set properly for all types
2598 $DB->set_field_select($tablename, 'oneint', true, 'id = ?', array(1)); // trues
2599 $DB->set_field_select($tablename, 'onenum', true, 'id = ?', array(1));
2600 $DB->set_field_select($tablename, 'onechar', true, 'id = ?', array(1));
2601 $DB->set_field_select($tablename, 'onetext', true, 'id = ?', array(1));
2602 $this->assertEquals(1, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2603 $this->assertEquals(1, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2604 $this->assertEquals(1, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2605 $this->assertEquals(1, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2606
2607 $DB->set_field_select($tablename, 'oneint', false, 'id = ?', array(1)); // falses
2608 $DB->set_field_select($tablename, 'onenum', false, 'id = ?', array(1));
2609 $DB->set_field_select($tablename, 'onechar', false, 'id = ?', array(1));
2610 $DB->set_field_select($tablename, 'onetext', false, 'id = ?', array(1));
2611 $this->assertEquals(0, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2612 $this->assertEquals(0, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2613 $this->assertEquals(0, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2614 $this->assertEquals(0, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2615
2616 // Check string data causes exception in numeric types
2617 try {
2618 $DB->set_field_select($tablename, 'oneint', 'onestring', 'id = ?', array(1));
2619 $this->fail("Expecting an exception, none occurred");
2620 } catch (exception $e) {
2621 $this->assertTrue($e instanceof dml_exception);
2622 }
2623 try {
2624 $DB->set_field_select($tablename, 'onenum', 'onestring', 'id = ?', array(1));
2625 $this->fail("Expecting an exception, none occurred");
2626 } catch (exception $e) {
2627 $this->assertTrue($e instanceof dml_exception);
2628 }
2629
2630 // Check empty string data is stored as 0 in numeric datatypes
2631 $DB->set_field_select($tablename, 'oneint', '', 'id = ?', array(1));
2632 $field = $DB->get_field($tablename, 'oneint', array('id' => 1));
2633 $this->assertTrue(is_numeric($field) && $field == 0);
2634
2635 $DB->set_field_select($tablename, 'onenum', '', 'id = ?', array(1));
2636 $field = $DB->get_field($tablename, 'onenum', array('id' => 1));
2637 $this->assertTrue(is_numeric($field) && $field == 0);
2638
2639 // Check empty strings are set properly in string types
2640 $DB->set_field_select($tablename, 'onechar', '', 'id = ?', array(1));
2641 $DB->set_field_select($tablename, 'onetext', '', 'id = ?', array(1));
2642 $this->assertTrue($DB->get_field($tablename, 'onechar', array('id' => 1)) === '');
2643 $this->assertTrue($DB->get_field($tablename, 'onetext', array('id' => 1)) === '');
2644
2645 // Check operation ((210.10 + 39.92) - 150.02) against numeric types
2646 $DB->set_field_select($tablename, 'oneint', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2647 $DB->set_field_select($tablename, 'onenum', ((210.10 + 39.92) - 150.02), 'id = ?', array(1));
2648 $this->assertEquals(100, $DB->get_field($tablename, 'oneint', array('id' => 1)));
2649 $this->assertEquals(100, $DB->get_field($tablename, 'onenum', array('id' => 1)));
2650
2651 // Check various quotes/backslashes combinations in string types
2652 $teststrings = array(
2653 'backslashes and quotes alone (even): "" \'\' \\\\',
2654 'backslashes and quotes alone (odd): """ \'\'\' \\\\\\',
2655 'backslashes and quotes sequences (even): \\"\\" \\\'\\\'',
2656 'backslashes and quotes sequences (odd): \\"\\"\\" \\\'\\\'\\\'');
2657 foreach ($teststrings as $teststring) {
2658 $DB->set_field_select($tablename, 'onechar', $teststring, 'id = ?', array(1));
2659 $DB->set_field_select($tablename, 'onetext', $teststring, 'id = ?', array(1));
2660 $this->assertEquals($teststring, $DB->get_field($tablename, 'onechar', array('id' => 1)));
2661 $this->assertEquals($teststring, $DB->get_field($tablename, 'onetext', array('id' => 1)));
2662 }
2663
2664 // Check LOBs in text/binary columns
2665 $clob = file_get_contents(__DIR__ . '/fixtures/clob.txt');
2666 $blob = file_get_contents(__DIR__ . '/fixtures/randombinary');
2667 $DB->set_field_select($tablename, 'onetext', $clob, 'id = ?', array(1));
2668 $DB->set_field_select($tablename, 'onebinary', $blob, 'id = ?', array(1));
2669 $this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
2670 $this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
2671
2672 // And "small" LOBs too, just in case
2673 $newclob = substr($clob, 0, 500);
2674 $newblob = substr($blob, 0, 250);
2675 $DB->set_field_select($tablename, 'onetext', $newclob, 'id = ?', array(1));
2676 $DB->set_field_select($tablename, 'onebinary', $newblob, 'id = ?', array(1));
2677 $this->assertEquals($newclob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test "small" CLOB set_field (full contents output disabled)');
2678 $this->assertEquals($newblob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test "small" BLOB set_field (full contents output disabled)');
2679
2680 // This is the failure from MDL-24863. This was giving an error on MSSQL,
2681 // which converts the '1' to an integer, which cannot then be compared with
2682 // onetext cast to a varchar. This should be fixed and working now.
2683 $newchar = 'frog';
2684 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2685 $params = array('onetext' => '1');
2686 try {
2687 $DB->set_field_select($tablename, 'onechar', $newchar, $DB->sql_compare_text('onetext') . ' = ?', $params);
2688 $this->assertTrue(true, 'No exceptions thrown with numerical text param comparison for text field.');
2689 } catch (dml_exception $e) {
2690 $this->assertFalse(true, 'We have an unexpected exception.');
2691 throw $e;
2692 }
2693
2694
2695 }
2696
2697 public function test_count_records() {
2698 $DB = $this->tdb;
2699
2700 $dbman = $DB->get_manager();
2701
2702 $table = $this->get_test_table();
2703 $tablename = $table->getName();
2704
2705 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2706 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2707 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2708 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2709 $dbman->create_table($table);
2710
2711 $this->assertEquals(0, $DB->count_records($tablename));
2712
2713 $DB->insert_record($tablename, array('course' => 3));
2714 $DB->insert_record($tablename, array('course' => 4));
2715 $DB->insert_record($tablename, array('course' => 5));
2716
2717 $this->assertEquals(3, $DB->count_records($tablename));
2718
2719 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2720 $conditions = array('onetext' => '1');
2721 try {
2722 $DB->count_records($tablename, $conditions);
2723 if (debugging()) {
2724 // only in debug mode - hopefully all devs test code in debug mode...
2725 $this->fail('An Exception is missing, expected due to equating of text fields');
2726 }
2727 } catch (exception $e) {
2728 $this->assertTrue($e instanceof dml_exception);
2729 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
2730 }
2731 }
2732
2733 public function test_count_records_select() {
2734 $DB = $this->tdb;
2735
2736 $dbman = $DB->get_manager();
2737
2738 $table = $this->get_test_table();
2739 $tablename = $table->getName();
2740
2741 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2742 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2743 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2744 $dbman->create_table($table);
2745
2746 $this->assertEquals(0, $DB->count_records($tablename));
2747
2748 $DB->insert_record($tablename, array('course' => 3));
2749 $DB->insert_record($tablename, array('course' => 4));
2750 $DB->insert_record($tablename, array('course' => 5));
2751
2752 $this->assertEquals(2, $DB->count_records_select($tablename, 'course > ?', array(3)));
2753 }
2754
2755 public function test_count_records_sql() {
2756 $DB = $this->tdb;
2757 $dbman = $DB->get_manager();
2758
2759 $table = $this->get_test_table();
2760 $tablename = $table->getName();
2761
2762 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2763 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2764 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2765 $dbman->create_table($table);
2766
2767 $this->assertEquals(0, $DB->count_records($tablename));
2768
2769 $DB->insert_record($tablename, array('course' => 3));
2770 $DB->insert_record($tablename, array('course' => 4));
2771 $DB->insert_record($tablename, array('course' => 5));
2772
2773 $this->assertEquals(2, $DB->count_records_sql("SELECT COUNT(*) FROM {{$tablename}} WHERE course > ?", array(3)));
2774 }
2775
2776 public function test_record_exists() {
2777 $DB = $this->tdb;
2778 $dbman = $DB->get_manager();
2779
2780 $table = $this->get_test_table();
2781 $tablename = $table->getName();
2782
2783 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2784 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2785 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2786 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2787 $dbman->create_table($table);
2788
2789 $this->assertEquals(0, $DB->count_records($tablename));
2790
2791 $this->assertFalse($DB->record_exists($tablename, array('course' => 3)));
2792 $DB->insert_record($tablename, array('course' => 3));
2793
2794 $this->assertTrue($DB->record_exists($tablename, array('course' => 3)));
2795
2796
2797 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2798 $conditions = array('onetext' => '1');
2799 try {
2800 $DB->record_exists($tablename, $conditions);
2801 if (debugging()) {
2802 // only in debug mode - hopefully all devs test code in debug mode...
2803 $this->fail('An Exception is missing, expected due to equating of text fields');
2804 }
2805 } catch (exception $e) {
2806 $this->assertTrue($e instanceof dml_exception);
2807 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
2808 }
2809 }
2810
2811 public function test_record_exists_select() {
2812 $DB = $this->tdb;
2813 $dbman = $DB->get_manager();
2814
2815 $table = $this->get_test_table();
2816 $tablename = $table->getName();
2817
2818 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2819 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2820 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2821 $dbman->create_table($table);
2822
2823 $this->assertEquals(0, $DB->count_records($tablename));
2824
2825 $this->assertFalse($DB->record_exists_select($tablename, "course = ?", array(3)));
2826 $DB->insert_record($tablename, array('course' => 3));
2827
2828 $this->assertTrue($DB->record_exists_select($tablename, "course = ?", array(3)));
2829 }
2830
2831 public function test_record_exists_sql() {
2832 $DB = $this->tdb;
2833 $dbman = $DB->get_manager();
2834
2835 $table = $this->get_test_table();
2836 $tablename = $table->getName();
2837
2838 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2839 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2840 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2841 $dbman->create_table($table);
2842
2843 $this->assertEquals(0, $DB->count_records($tablename));
2844
2845 $this->assertFalse($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
2846 $DB->insert_record($tablename, array('course' => 3));
2847
2848 $this->assertTrue($DB->record_exists_sql("SELECT * FROM {{$tablename}} WHERE course = ?", array(3)));
2849 }
2850
2851 public function test_recordset_locks_delete() {
2852 $DB = $this->tdb;
2853 $dbman = $DB->get_manager();
2854
2855 //Setup
2856 $table = $this->get_test_table();
2857 $tablename = $table->getName();
2858
2859 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2860 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2861 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2862 $dbman->create_table($table);
2863
2864 $DB->insert_record($tablename, array('course' => 1));
2865 $DB->insert_record($tablename, array('course' => 2));
2866 $DB->insert_record($tablename, array('course' => 3));
2867 $DB->insert_record($tablename, array('course' => 4));
2868 $DB->insert_record($tablename, array('course' => 5));
2869 $DB->insert_record($tablename, array('course' => 6));
2870
2871 // Test against db write locking while on an open recordset
2872 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}
2873 foreach ($rs as $record) {
2874 $cid = $record->course;
2875 $DB->delete_records($tablename, array('course' => $cid));
2876 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
2877 }
2878 $rs->close();
2879
2880 $this->assertEquals(4, $DB->count_records($tablename, array()));
2881 }
2882
2883 public function test_recordset_locks_update() {
2884 $DB = $this->tdb;
2885 $dbman = $DB->get_manager();
2886
2887 //Setup
2888 $table = $this->get_test_table();
2889 $tablename = $table->getName();
2890
2891 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2892 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2893 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2894 $dbman->create_table($table);
2895
2896 $DB->insert_record($tablename, array('course' => 1));
2897 $DB->insert_record($tablename, array('course' => 2));
2898 $DB->insert_record($tablename, array('course' => 3));
2899 $DB->insert_record($tablename, array('course' => 4));
2900 $DB->insert_record($tablename, array('course' => 5));
2901 $DB->insert_record($tablename, array('course' => 6));
2902
2903 // Test against db write locking while on an open recordset
2904 $rs = $DB->get_recordset($tablename, array(), null, 'course', 2, 2); // get courses = {3,4}
2905 foreach ($rs as $record) {
2906 $cid = $record->course;
2907 $DB->set_field($tablename, 'course', 10, array('course' => $cid));
2908 $this->assertFalse($DB->record_exists($tablename, array('course' => $cid)));
2909 }
2910 $rs->close();
2911
2912 $this->assertEquals(2, $DB->count_records($tablename, array('course' => 10)));
2913 }
2914
2915 public function test_delete_records() {
2916 $DB = $this->tdb;
2917 $dbman = $DB->get_manager();
2918
2919 $table = $this->get_test_table();
2920 $tablename = $table->getName();
2921
2922 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2923 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2924 $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
2925 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2926 $dbman->create_table($table);
2927
2928 $DB->insert_record($tablename, array('course' => 3));
2929 $DB->insert_record($tablename, array('course' => 2));
2930 $DB->insert_record($tablename, array('course' => 2));
2931
2932 // Delete all records
2933 $this->assertTrue($DB->delete_records($tablename));
2934 $this->assertEquals(0, $DB->count_records($tablename));
2935
2936 // Delete subset of records
2937 $DB->insert_record($tablename, array('course' => 3));
2938 $DB->insert_record($tablename, array('course' => 2));
2939 $DB->insert_record($tablename, array('course' => 2));
2940
2941 $this->assertTrue($DB->delete_records($tablename, array('course' => 2)));
2942 $this->assertEquals(1, $DB->count_records($tablename));
2943
2944 // delete all
2945 $this->assertTrue($DB->delete_records($tablename, array()));
2946 $this->assertEquals(0, $DB->count_records($tablename));
2947
2948 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2949 $conditions = array('onetext'=>'1');
2950 try {
2951 $DB->delete_records($tablename, $conditions);
2952 if (debugging()) {
2953 // only in debug mode - hopefully all devs test code in debug mode...
2954 $this->fail('An Exception is missing, expected due to equating of text fields');
2955 }
2956 } catch (exception $e) {
2957 $this->assertTrue($e instanceof dml_exception);
2958 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
2959 }
2960
2961 // test for exception throwing on text conditions being compared. (MDL-24863, unwanted auto conversion of param to int)
2962 $conditions = array('onetext' => 1);
2963 try {
2964 $DB->delete_records($tablename, $conditions);
2965 if (debugging()) {
2966 // only in debug mode - hopefully all devs test code in debug mode...
2967 $this->fail('An Exception is missing, expected due to equating of text fields');
2968 }
2969 } catch (exception $e) {
2970 $this->assertTrue($e instanceof dml_exception);
2971 $this->assertEquals($e->errorcode, 'textconditionsnotallowed');
2972 }
2973 }
2974
2975 public function test_delete_records_select() {
2976 $DB = $this->tdb;
2977 $dbman = $DB->get_manager();
2978
2979 $table = $this->get_test_table();
2980 $tablename = $table->getName();
2981
2982 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2983 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
2984 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
2985 $dbman->create_table($table);
2986
2987 $DB->insert_record($tablename, array('course' => 3));
2988 $DB->insert_record($tablename, array('course' => 2));
2989 $DB->insert_record($tablename, array('course' => 2));
2990
2991 $this->assertTrue($DB->delete_records_select($tablename, 'course = ?', array(2)));
2992 $this->assertEquals(1, $DB->count_records($tablename));
2993 }
2994
2995 public function test_delete_records_list() {
2996 $DB = $this->tdb;
2997 $dbman = $DB->get_manager();
2998
2999 $table = $this->get_test_table();
3000 $tablename = $table->getName();
3001
3002 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3003 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3004 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3005 $dbman->create_table($table);
3006
3007 $DB->insert_record($tablename, array('course' => 1));
3008 $DB->insert_record($tablename, array('course' => 2));
3009 $DB->insert_record($tablename, array('course' => 3));
3010
3011 $this->assertTrue($DB->delete_records_list($tablename, 'course', array(2, 3)));
3012 $this->assertEquals(1, $DB->count_records($tablename));
3013
3014 $this->assertTrue($DB->delete_records_list($tablename, 'course', array())); /// Must delete 0 rows without conditions. MDL-17645
3015 $this->assertEquals(1, $DB->count_records($tablename));
3016 }
3017
3018 public function test_object_params() {
3019 $DB = $this->tdb;
3020 $dbman = $DB->get_manager();
3021
3022 $table = $this->get_test_table();
3023 $tablename = $table->getName();
3024 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3025 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3026 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3027 $dbman->create_table($table);
3028
3029 $o = new stdClass(); // objects without __toString - never worked
3030 try {
3031 $DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o));
3032 $this->fail('coding_exception expected');
3033 } catch (Exception $e) {
3034 $this->assertTrue($e instanceof coding_exception);
3035 }
3036
3037 // objects with __toString() forbidden everywhere since 2.3
3038 $o = new dml_test_object_one();
3039 try {
3040 $DB->fix_sql_params("SELECT {{$tablename}} WHERE course = ? ", array($o));
3041 $this->fail('coding_exception expected');
3042 } catch (Exception $e) {
3043 $this->assertTrue($e instanceof coding_exception);
3044 }
3045
3046 try {
3047 $DB->execute("SELECT {{$tablename}} WHERE course = ? ", array($o));
3048 $this->fail('coding_exception expected');
3049 } catch (Exception $e) {
3050 $this->assertTrue($e instanceof coding_exception);
3051 }
3052
3053 try {
3054 $DB->get_recordset_sql("SELECT {{$tablename}} WHERE course = ? ", array($o));
3055 $this->fail('coding_exception expected');
3056 } catch (Exception $e) {
3057 $this->assertTrue($e instanceof coding_exception);
3058 }
3059
3060 try {
3061 $DB->get_records_sql("SELECT {{$tablename}} WHERE course = ? ", array($o));
3062 $this->fail('coding_exception expected');
3063 } catch (Exception $e) {
3064 $this->assertTrue($e instanceof coding_exception);
3065 }
3066
3067 try {
3068 $record = new stdClass();
3069 $record->course = $o;
3070 $DB->insert_record_raw($tablename, $record);
3071 $this->fail('coding_exception expected');
3072 } catch (Exception $e) {
3073 $this->assertTrue($e instanceof coding_exception);
3074 }
3075
3076 try {
3077 $record = new stdClass();
3078 $record->course = $o;
3079 $DB->insert_record($tablename, $record);
3080 $this->fail('coding_exception expected');
3081 } catch (Exception $e) {
3082 $this->assertTrue($e instanceof coding_exception);
3083 }
3084
3085 try {
3086 $record = new stdClass();
3087 $record->course = $o;
3088 $DB->import_record($tablename, $record);
3089 $this->fail('coding_exception expected');
3090 } catch (Exception $e) {
3091 $this->assertTrue($e instanceof coding_exception);
3092 }
3093
3094 try {
3095 $record = new stdClass();
3096 $record->id = 1;
3097 $record->course = $o;
3098 $DB->update_record_raw($tablename, $record);
3099 $this->fail('coding_exception expected');
3100 } catch (Exception $e) {
3101 $this->assertTrue($e instanceof coding_exception);
3102 }
3103
3104 try {
3105 $record = new stdClass();
3106 $record->id = 1;
3107 $record->course = $o;
3108 $DB->update_record($tablename, $record);
3109 $this->fail('coding_exception expected');
3110 } catch (Exception $e) {
3111 $this->assertTrue($e instanceof coding_exception);
3112 }
3113
3114 try {
3115 $DB->set_field_select($tablename, 'course', 1, "course = ? ", array($o));
3116 $this->fail('coding_exception expected');
3117 } catch (Exception $e) {
3118 $this->assertTrue($e instanceof coding_exception);
3119 }
3120
3121 try {
3122 $DB->delete_records_select($tablename, "course = ? ", array($o));
3123 $this->fail('coding_exception expected');
3124 } catch (Exception $e) {
3125 $this->assertTrue($e instanceof coding_exception);
3126 }
3127 }
3128
3129 function test_sql_null_from_clause() {
3130 $DB = $this->tdb;
3131 $sql = "SELECT 1 AS id ".$DB->sql_null_from_clause();
3132 $this->assertEquals($DB->get_field_sql($sql), 1);
3133 }
3134
3135 function test_sql_bitand() {
3136 $DB = $this->tdb;
3137 $dbman = $DB->get_manager();
3138
3139 $table = $this->get_test_table();
3140 $tablename = $table->getName();
3141
3142 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3143 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3144 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3145 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3146 $dbman->create_table($table);
3147
3148 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3149
3150 $sql = "SELECT ".$DB->sql_bitand(10, 3)." AS res ".$DB->sql_null_from_clause();
3151 $this->assertEquals($DB->get_field_sql($sql), 2);
3152
3153 $sql = "SELECT id, ".$DB->sql_bitand('col1', 'col2')." AS res FROM {{$tablename}}";
3154 $result = $DB->get_records_sql($sql);
3155 $this->assertEquals(count($result), 1);
3156 $this->assertEquals(reset($result)->res, 2);
3157
3158 $sql = "SELECT id, ".$DB->sql_bitand('col1', '?')." AS res FROM {{$tablename}}";
3159 $result = $DB->get_records_sql($sql, array(10));
3160 $this->assertEquals(count($result), 1);
3161 $this->assertEquals(reset($result)->res, 2);
3162 }
3163
3164 function test_sql_bitnot() {
3165 $DB = $this->tdb;
3166
3167 $not = $DB->sql_bitnot(2);
3168 $notlimited = $DB->sql_bitand($not, 7); // might be positive or negative number which can not fit into PHP INT!
3169
3170 $sql = "SELECT $notlimited AS res ".$DB->sql_null_from_clause();
3171 $this->assertEquals($DB->get_field_sql($sql), 5);
3172 }
3173
3174 function test_sql_bitor() {
3175 $DB = $this->tdb;
3176 $dbman = $DB->get_manager();
3177
3178 $table = $this->get_test_table();
3179 $tablename = $table->getName();
3180
3181 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3182 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3183 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3184 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3185 $dbman->create_table($table);
3186
3187 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3188
3189 $sql = "SELECT ".$DB->sql_bitor(10, 3)." AS res ".$DB->sql_null_from_clause();
3190 $this->assertEquals($DB->get_field_sql($sql), 11);
3191
3192 $sql = "SELECT id, ".$DB->sql_bitor('col1', 'col2')." AS res FROM {{$tablename}}";
3193 $result = $DB->get_records_sql($sql);
3194 $this->assertEquals(count($result), 1);
3195 $this->assertEquals(reset($result)->res, 11);
3196
3197 $sql = "SELECT id, ".$DB->sql_bitor('col1', '?')." AS res FROM {{$tablename}}";
3198 $result = $DB->get_records_sql($sql, array(10));
3199 $this->assertEquals(count($result), 1);
3200 $this->assertEquals(reset($result)->res, 11);
3201 }
3202
3203 function test_sql_bitxor() {
3204 $DB = $this->tdb;
3205 $dbman = $DB->get_manager();
3206
3207 $table = $this->get_test_table();
3208 $tablename = $table->getName();
3209
3210 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3211 $table->add_field('col1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3212 $table->add_field('col2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3213 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3214 $dbman->create_table($table);
3215
3216 $DB->insert_record($tablename, array('col1' => 3, 'col2' => 10));
3217
3218 $sql = "SELECT ".$DB->sql_bitxor(10, 3)." AS res ".$DB->sql_null_from_clause();
3219 $this->assertEquals($DB->get_field_sql($sql), 9);
3220
3221 $sql = "SELECT id, ".$DB->sql_bitxor('col1', 'col2')." AS res FROM {{$tablename}}";
3222 $result = $DB->get_records_sql($sql);
3223 $this->assertEquals(count($result), 1);
3224 $this->assertEquals(reset($result)->res, 9);
3225
3226 $sql = "SELECT id, ".$DB->sql_bitxor('col1', '?')." AS res FROM {{$tablename}}";
3227 $result = $DB->get_records_sql($sql, array(10));
3228 $this->assertEquals(count($result), 1);
3229 $this->assertEquals(reset($result)->res, 9);
3230 }
3231
3232 function test_sql_modulo() {
3233 $DB = $this->tdb;
3234 $sql = "SELECT ".$DB->sql_modulo(10, 7)." AS res ".$DB->sql_null_from_clause();
3235 $this->assertEquals($DB->get_field_sql($sql), 3);
3236 }
3237
3238 function test_sql_ceil() {
3239 $DB = $this->tdb;
3240 $sql = "SELECT ".$DB->sql_ceil(665.666)." AS res ".$DB->sql_null_from_clause();
3241 $this->assertEquals($DB->get_field_sql($sql), 666);
3242 }
3243
3244 function test_cast_char2int() {
3245 $DB = $this->tdb;
3246 $dbman = $DB->get_manager();
3247
3248 $table1 = $this->get_test_table("1");
3249 $tablename1 = $table1->getName();
3250
3251 $table1->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3252 $table1->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3253 $table1->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
3254 $table1->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3255 $dbman->create_table($table1);
3256
3257 $DB->insert_record($tablename1, array('name'=>'0100', 'nametext'=>'0200'));
3258 $DB->insert_record($tablename1, array('name'=>'10', 'nametext'=>'20'));
3259
3260 $table2 = $this->get_test_table("2");
3261 $tablename2 = $table2->getName();
3262 $table2->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3263 $table2->add_field('res', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3264 $table2->add_field('restext', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
3265 $table2->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3266 $dbman->create_table($table2);
3267
3268 $DB->insert_record($tablename2, array('res'=>100, 'restext'=>200));
3269
3270 // casting varchar field
3271 $sql = "SELECT *
3272 FROM {".$tablename1."} t1
3273 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.name")." = t2.res ";
3274 $records = $DB->get_records_sql($sql);
3275 $this->assertEquals(count($records), 1);
3276 // also test them in order clauses
3277 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('name');
3278 $records = $DB->get_records_sql($sql);
3279 $this->assertEquals(count($records), 2);
3280 $this->assertEquals(reset($records)->name, '10');
3281 $this->assertEquals(next($records)->name, '0100');
3282
3283 // casting text field
3284 $sql = "SELECT *
3285 FROM {".$tablename1."} t1
3286 JOIN {".$tablename2."} t2 ON ".$DB->sql_cast_char2int("t1.nametext", true)." = t2.restext ";
3287 $records = $DB->get_records_sql($sql);
3288 $this->assertEquals(count($records), 1);
3289 // also test them in order clauses
3290 $sql = "SELECT * FROM {{$tablename1}} ORDER BY ".$DB->sql_cast_char2int('nametext', true);
3291 $records = $DB->get_records_sql($sql);
3292 $this->assertEquals(count($records), 2);
3293 $this->assertEquals(reset($records)->nametext, '20');
3294 $this->assertEquals(next($records)->nametext, '0200');
3295 }
3296
3297 function test_cast_char2real() {
3298 $DB = $this->tdb;
3299 $dbman = $DB->get_manager();
3300
3301 $table = $this->get_test_table();
3302 $tablename = $table->getName();
3303
3304 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3305 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3306 $table->add_field('nametext', XMLDB_TYPE_TEXT, 'small', null, null, null, null);
3307 $table->add_field('res', XMLDB_TYPE_NUMBER, '12, 7', null, null, null, null);
3308 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3309 $dbman->create_table($table);
3310
3311 $DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1));
3312 $DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666));
3313 $DB->insert_record($tablename, array('name'=>'011.10','nametext'=>'011.10','res'=>10.1));
3314
3315 // casting varchar field
3316 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res";
3317 $records = $DB->get_records_sql($sql);
3318 $this->assertEquals(count($records), 2);
3319 // also test them in order clauses
3320 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('name');
3321 $records = $DB->get_records_sql($sql);
3322 $this->assertEquals(count($records), 3);
3323 $this->assertEquals(reset($records)->name, '10.10');
3324 $this->assertEquals(next($records)->name, '011.10');
3325 $this->assertEquals(next($records)->name, '91.10');
3326
3327 // casting text field
3328 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res";
3329 $records = $DB->get_records_sql($sql);
3330 $this->assertEquals(count($records), 2);
3331 // also test them in order clauses
3332 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_cast_char2real('nametext', true);
3333 $records = $DB->get_records_sql($sql);
3334 $this->assertEquals(count($records), 3);
3335 $this->assertEquals(reset($records)->nametext, '10.10');
3336 $this->assertEquals(next($records)->nametext, '011.10');
3337 $this->assertEquals(next($records)->nametext, '91.10');
3338 }
3339
3340 function sql_compare_text() {
3341 $DB = $this->tdb;
3342 $dbman = $DB->get_manager();
3343
3344 $table = $this->get_test_table();
3345 $tablename = $table->getName();
3346
3347 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3348 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3349 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3350 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3351 $dbman->create_table($table);
3352
3353 $DB->insert_record($tablename, array('name'=>'abcd', 'description'=>'abcd'));
3354 $DB->insert_record($tablename, array('name'=>'abcdef', 'description'=>'bbcdef'));
3355 $DB->insert_record($tablename, array('name'=>'aaaabb', 'description'=>'aaaacccccccccccccccccc'));
3356
3357 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description');
3358 $records = $DB->get_records_sql($sql);
3359 $this->assertEquals(count($records), 1);
3360
3361 $sql = "SELECT * FROM {{$tablename}} WHERE name = ".$DB->sql_compare_text('description', 4);
3362 $records = $DB->get_records_sql($sql);
3363 $this->assertEquals(count($records), 2);
3364 }
3365
3366 function test_unique_index_collation_trouble() {
3367 // note: this is a work in progress, we should probably move this to ddl test
3368
3369 $DB = $this->tdb;
3370 $dbman = $DB->get_manager();
3371
3372 $table = $this->get_test_table();
3373 $tablename = $table->getName();
3374
3375 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3376 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3377 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3378 $table->add_index('name', XMLDB_INDEX_UNIQUE, array('name'));
3379 $dbman->create_table($table);
3380
3381 $DB->insert_record($tablename, array('name'=>'aaa'));
3382
3383 try {
3384 $DB->insert_record($tablename, array('name'=>'AAA'));
3385 } catch (Exception $e) {
3386 //TODO: ignore case insensitive uniqueness problems for now
3387 //$this->fail("Unique index is case sensitive - this may cause problems in some tables");
3388 }
3389
3390 try {
3391 $DB->insert_record($tablename, array('name'=>'aäa'));
3392 $DB->insert_record($tablename, array('name'=>'aáa'));
3393 $this->assertTrue(true);
3394 } catch (Exception $e) {
3395 $family = $DB->get_dbfamily();
3396 if ($family === 'mysql' or $family === 'mssql') {
3397 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation.");
3398 } else {
3399 // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness.
3400 $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages.");
3401 }
3402 throw($e);
3403 }
3404 }
3405
3406 function test_sql_binary_equal() {
3407 $DB = $this->tdb;
3408 $dbman = $DB->get_manager();
3409
3410 $table = $this->get_test_table();
3411 $tablename = $table->getName();
3412
3413 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3414 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3415 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3416 $dbman->create_table($table);
3417
3418 $DB->insert_record($tablename, array('name'=>'aaa'));
3419 $DB->insert_record($tablename, array('name'=>'aáa'));
3420 $DB->insert_record($tablename, array('name'=>'aäa'));
3421 $DB->insert_record($tablename, array('name'=>'bbb'));
3422 $DB->insert_record($tablename, array('name'=>'BBB'));
3423
3424 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb'));
3425 $this->assertEquals(count($records), 1, 'SQL operator "=" is expected to be case sensitive');
3426
3427 $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa'));
3428 $this->assertEquals(count($records), 1, 'SQL operator "=" is expected to be accent sensitive');
3429 }
3430
3431 function test_sql_like() {
3432 $DB = $this->tdb;
3433 $dbman = $DB->get_manager();
3434
3435 $table = $this->get_test_table();
3436 $tablename = $table->getName();
3437
3438 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3439 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3440 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3441 $dbman->create_table($table);
3442
3443 $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
3444 $DB->insert_record($tablename, array('name'=>'Nodupor'));
3445 $DB->insert_record($tablename, array('name'=>'ouch'));
3446 $DB->insert_record($tablename, array('name'=>'ouc_'));
3447 $DB->insert_record($tablename, array('name'=>'ouc%'));
3448 $DB->insert_record($tablename, array('name'=>'aui'));
3449 $DB->insert_record($tablename, array('name'=>'aüi'));
3450 $DB->insert_record($tablename, array('name'=>'aÜi'));
3451
3452 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false);
3453 $records = $DB->get_records_sql($sql, array("%dup_r%"));
3454 $this->assertEquals(count($records), 2);
3455
3456 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
3457 $records = $DB->get_records_sql($sql, array("%dup%"));
3458 $this->assertEquals(count($records), 1);
3459
3460 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults
3461 $records = $DB->get_records_sql($sql, array("%dup%"));
3462 $this->assertEquals(count($records), 1);
3463
3464 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true);
3465 $records = $DB->get_records_sql($sql, array("ouc\\_"));
3466 $this->assertEquals(count($records), 1);
3467
3468 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');
3469 $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
3470 $this->assertEquals(count($records), 1);
3471
3472 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true);
3473 $records = $DB->get_records_sql($sql, array('aui'));
3474 $this->assertEquals(count($records), 1);
3475
3476 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE
3477 $records = $DB->get_records_sql($sql, array("%o%"));
3478 $this->assertEquals(count($records), 3);
3479
3480 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE
3481 $records = $DB->get_records_sql($sql, array("%D%"));
3482 $this->assertEquals(count($records), 6);
3483
3484 // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors
3485 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false);
3486 $records = $DB->get_records_sql($sql, array('aui'));
3487 //$this->assertEquals(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
3488 $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false);
3489 $records = $DB->get_records_sql($sql, array('aui'));
3490 //$this->assertEquals(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.');
3491 }
3492
3493 function test_coalesce() {
3494 $DB = $this->tdb;
3495
3496 // Testing not-null ocurrences, return 1st
3497 $sql = "SELECT COALESCE('returnthis', 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();
3498 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));
3499 $sql = "SELECT COALESCE(:paramvalue, 'orthis', 'orwhynotthis') AS test" . $DB->sql_null_from_clause();
3500 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3501
3502 // Testing null ocurrences, return 2nd
3503 $sql = "SELECT COALESCE(null, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();
3504 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));
3505 $sql = "SELECT COALESCE(:paramvalue, 'returnthis', 'orthis') AS test" . $DB->sql_null_from_clause();
3506 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));
3507 $sql = "SELECT COALESCE(null, :paramvalue, 'orthis') AS test" . $DB->sql_null_from_clause();
3508 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3509
3510 // Testing null ocurrences, return 3rd
3511 $sql = "SELECT COALESCE(null, null, 'returnthis') AS test" . $DB->sql_null_from_clause();
3512 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array()));
3513 $sql = "SELECT COALESCE(null, :paramvalue, 'returnthis') AS test" . $DB->sql_null_from_clause();
3514 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => null)));
3515 $sql = "SELECT COALESCE(null, null, :paramvalue) AS test" . $DB->sql_null_from_clause();
3516 $this->assertEquals('returnthis', $DB->get_field_sql($sql, array('paramvalue' => 'returnthis')));
3517
3518 // Testing all null ocurrences, return null
3519 // Note: under mssql, if all elements are nulls, at least one must be a "typed" null, hence
3520 // we cannot test this in a cross-db way easily, so next 2 tests are using
3521 // different queries depending of the DB family
3522 $customnull = $DB->get_dbfamily() == 'mssql' ? 'CAST(null AS varchar)' : 'null';
3523 $sql = "SELECT COALESCE(null, null, " . $customnull . ") AS test" . $DB->sql_null_from_clause();
3524 $this->assertNull($DB->get_field_sql($sql, array()));
3525 $sql = "SELECT COALESCE(null, :paramvalue, " . $customnull . ") AS test" . $DB->sql_null_from_clause();
3526 $this->assertNull($DB->get_field_sql($sql, array('paramvalue' => null)));
3527
3528 // Check there are not problems with whitespace strings
3529 $sql = "SELECT COALESCE(null, '', null) AS test" . $DB->sql_null_from_clause();
3530 $this->assertEquals('', $DB->get_field_sql($sql, array()));
3531 $sql = "SELECT COALESCE(null, :paramvalue, null) AS test" . $DB->sql_null_from_clause();
3532 $this->assertEquals('', $DB->get_field_sql($sql, array('paramvalue' => '')));
3533 }
3534
3535 function test_sql_concat() {
3536 $DB = $this->tdb;
3537 $dbman = $DB->get_manager();
3538
3539 /// Testing all sort of values
3540 $sql = "SELECT ".$DB->sql_concat("?", "?", "?")." AS fullname ". $DB->sql_null_from_clause();
3541 // string, some unicode chars
3542 $params = array('name', 'áéíóú', 'name3');
3543 $this->assertEquals('nameáéíóúname3', $DB->get_field_sql($sql, $params));
3544 // string, spaces and numbers
3545 $params = array('name', ' ', 12345);
3546 $this->assertEquals('name 12345', $DB->get_field_sql($sql, $params));
3547 // float, empty and strings
3548 $params = array(123.45, '', 'test');
3549 $this->assertEquals('123.45test', $DB->get_field_sql($sql, $params));
3550 // only integers
3551 $params = array(12, 34, 56);
3552 $this->assertEquals('123456', $DB->get_field_sql($sql, $params));
3553 // float, null and strings
3554 $params = array(123.45, null, 'test');
3555 $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
3556
3557 /// Testing fieldnames + values and also integer fieldnames
3558 $table = $this->get_test_table();
3559 $tablename = $table->getName();
3560
3561 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3562 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3563 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3564 $dbman->create_table($table);
3565
3566 $DB->insert_record($tablename, array('description'=>'áéíóú'));
3567 $DB->insert_record($tablename, array('description'=>'dxxx'));
3568 $DB->insert_record($tablename, array('description'=>'bcde'));
3569
3570 // fieldnames and values mixed
3571 $sql = 'SELECT id, ' . $DB->sql_concat('description', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
3572 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
3573 $this->assertEquals(count($records), 3);
3574 $this->assertEquals($records[1]->result, 'áéíóúharcoded123.45test');
3575 // integer fieldnames and values
3576 $sql = 'SELECT id, ' . $DB->sql_concat('id', "'harcoded'", '?', '?') . ' AS result FROM {' . $tablename . '}';
3577 $records = $DB->get_records_sql($sql, array(123.45, 'test'));
3578 $this->assertEquals(count($records), 3);
3579 $this->assertEquals($records[1]->result, '1harcoded123.45test');
3580 // all integer fieldnames
3581 $sql = 'SELECT id, ' . $DB->sql_concat('id', 'id', 'id') . ' AS result FROM {' . $tablename . '}';
3582 $records = $DB->get_records_sql($sql, array());
3583 $this->assertEquals(count($records), 3);
3584 $this->assertEquals($records[1]->result, '111');
3585
3586 }
3587
3588 function test_concat_join() {
3589 $DB = $this->tdb;
3590 $sql = "SELECT ".$DB->sql_concat_join("' '", array("?", "?", "?"))." AS fullname ".$DB->sql_null_from_clause();
3591 $params = array("name", "name2", "name3");
3592 $result = $DB->get_field_sql($sql, $params);
3593 $this->assertEquals("name name2 name3", $result);
3594 }
3595
3596 function test_sql_fullname() {
3597 $DB = $this->tdb;
3598 $sql = "SELECT ".$DB->sql_fullname(':first', ':last')." AS fullname ".$DB->sql_null_from_clause();
3599 $params = array('first'=>'Firstname', 'last'=>'Surname');
3600 $this->assertEquals("Firstname Surname", $DB->get_field_sql($sql, $params));
3601 }
3602
3603 function sql_sql_order_by_text() {
3604 $DB = $this->tdb;
3605 $dbman = $DB->get_manager();
3606
3607 $table = $this->get_test_table();
3608 $tablename = $table->getName();
3609
3610 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3611 $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
3612 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3613 $dbman->create_table($table);
3614
3615 $DB->insert_record($tablename, array('description'=>'abcd'));
3616 $DB->insert_record($tablename, array('description'=>'dxxx'));
3617 $DB->insert_record($tablename, array('description'=>'bcde'));
3618
3619 $sql = "SELECT * FROM {{$tablename}} ORDER BY ".$DB->sql_order_by_text('description');
3620 $records = $DB->get_records_sql($sql);
3621 $first = array_shift($records);
3622 $this->assertEquals(1, $first->id);
3623 $second = array_shift($records);
3624 $this->assertEquals(3, $second->id);
3625 $last = array_shift($records);
3626 $this->assertEquals(2, $last->id);
3627 }
3628
3629 function test_sql_substring() {
3630 $DB = $this->tdb;
3631 $dbman = $DB->get_manager();
3632
3633 $table = $this->get_test_table();
3634 $tablename = $table->getName();
3635
3636 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3637 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3638 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3639 $dbman->create_table($table);
3640
3641 $string = 'abcdefghij';
3642
3643 $DB->insert_record($tablename, array('name'=>$string));
3644
3645 $sql = "SELECT id, ".$DB->sql_substr("name", 5)." AS name FROM {{$tablename}}";
3646 $record = $DB->get_record_sql($sql);
3647 $this->assertEquals(substr($string, 5-1), $record->name);
3648
3649 $sql = "SELECT id, ".$DB->sql_substr("name", 5, 2)." AS name FROM {{$tablename}}";
3650 $record = $DB->get_record_sql($sql);
3651 $this->assertEquals(substr($string, 5-1, 2), $record->name);
3652
3653 try {
3654 // silence php warning ;-)
3655 @$DB->sql_substr("name");
3656 $this->fail("Expecting an exception, none occurred");
3657 } catch (Exception $e) {
3658 $this->assertTrue($e instanceof coding_exception);
3659 }
3660 }
3661
3662 function test_sql_length() {
3663 $DB = $this->tdb;
3664 $this->assertEquals($DB->get_field_sql(
3665 "SELECT ".$DB->sql_length("'aeiou'").$DB->sql_null_from_clause()), 5);
3666 $this->assertEquals($DB->get_field_sql(
3667 "SELECT ".$DB->sql_length("'áéíóú'").$DB->sql_null_from_clause()), 5);
3668 }
3669
3670 function test_sql_position() {
3671 $DB = $this->tdb;
3672 $this->assertEquals($DB->get_field_sql(
3673 "SELECT ".$DB->sql_position("'ood'", "'Moodle'").$DB->sql_null_from_clause()), 2);
3674 $this->assertEquals($DB->get_field_sql(
3675 "SELECT ".$DB->sql_position("'Oracle'", "'Moodle'").$DB->sql_null_from_clause()), 0);
3676 }
3677
3678 function test_sql_empty() {
3679 $DB = $this->tdb;
3680 $dbman = $DB->get_manager();
3681
3682 $table = $this->get_test_table();
3683 $tablename = $table->getName();
3684
3685 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
3686 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
3687 $table->add_field('namenotnull', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, 'default value');
3688 $table->add_field('namenotnullnodeflt', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
3689 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
3690 $dbman->create_table($table);
3691
3692 $DB->insert_record($tablename, array('name'=>'', 'namenotnull'=>''));
3693 $DB->insert_record($tablename, array('name'=>null));
3694 $DB->insert_record($tablename, array('name'=>'lalala'));
3695 $DB->insert_record($tablename, array('name'=>0));