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