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