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