MDL-15250 "Adding a new function quiz_question_action_icons that outputs a string...
[moodle.git] / lib / dml / simpletest / testdml.php
CommitLineData
73f7ad71 1<?php
2/**
3dce78e1 3 * Unit tests for dml
4 * @package dml
73f7ad71 5 */
6
7if (!defined('MOODLE_INTERNAL')) {
8 die('Direct access to this script is forbidden.'); /// It must be included from a Moodle page
9}
10
3dce78e1 11class dml_test extends UnitTestCase {
73f7ad71 12 private $tables = array();
251387d0 13 private $tdb;
73f7ad71 14
15 function setUp() {
a230012c 16 global $CFG, $DB, $UNITTEST;
e6b4f00e 17
a230012c 18 if (isset($UNITTEST->func_test_db)) {
251387d0 19 $this->tdb = $UNITTEST->func_test_db;
e6b4f00e 20 } else {
251387d0 21 $this->tdb = $DB;
e6b4f00e 22 }
73f7ad71 23
251387d0 24 $dbman = $this->tdb->get_manager();
73f7ad71 25
26 $table = new xmldb_table("testtable");
e6b4f00e 27 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
28 $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
29 $table->add_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, XMLDB_ENUM,
73f7ad71 30 array('single', 'news', 'general', 'social', 'eachuser', 'teacher', 'qanda'), 'general');
e6b4f00e 31 $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null, null);
32 $table->add_field('intro', XMLDB_TYPE_TEXT, 'small', null, XMLDB_NOTNULL, null, null, null, null);
33 $table->add_field('logo', XMLDB_TYPE_BINARY, 'big', null, XMLDB_NOTNULL, null, null, null);
34 $table->add_field('assessed', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
35 $table->add_field('assesstimestart', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
36 $table->add_field('assesstimefinish', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
37 $table->add_field('scale', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, null, '0');
38 $table->add_field('maxbytes', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
39 $table->add_field('forcesubscribe', XMLDB_TYPE_INTEGER, '1', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
40 $table->add_field('trackingtype', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '1');
41 $table->add_field('rsstype', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
42 $table->add_field('rssarticles', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
43 $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
44 $table->add_field('grade', XMLDB_TYPE_NUMBER, '20,0', XMLDB_UNSIGNED, null, null, null, null, null);
45 $table->add_field('percent', XMLDB_TYPE_NUMBER, '5,2', null, null, null, null, null, null);
46 $table->add_field('warnafter', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
47 $table->add_field('blockafter', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
48 $table->add_field('blockperiod', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, null, '0');
49 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
50 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
73f7ad71 51 $table->setComment("This is a test'n drop table. You can drop it safely");
52
251387d0 53 if ($dbman->table_exists($table)) {
54 $dbman->drop_table($table, true, false);
e6b4f00e 55 }
251387d0 56 $dbman->create_table($table);
809df0e2 57 $this->tables[$table->getName()] = $table;
73f7ad71 58
73f7ad71 59 }
60
61 function tearDown() {
251387d0 62 $dbman = $this->tdb->get_manager();
809df0e2 63
64 foreach ($this->tables as $table) {
251387d0 65 if ($dbman->table_exists($table)) {
66 $dbman->drop_table($table, true, false);
73f7ad71 67 }
68 }
e6b4f00e 69 $this->tables = array();
73f7ad71 70 }
71
72 function test_fix_sql_params() {
251387d0 73 $DB = $this->tdb; // do not use global $DB!
74 $dbman = $this->tdb->get_manager();
e6b4f00e 75
73f7ad71 76 // Malformed table placeholder
77 $sql = "SELECT * FROM [testtable]";
e6b4f00e 78 $sqlarray = $DB->fix_sql_params($sql);
73f7ad71 79 $this->assertEqual($sql, $sqlarray[0]);
80
81 // Correct table placeholder substitution
82 $sql = "SELECT * FROM {testtable}";
e6b4f00e 83 $sqlarray = $DB->fix_sql_params($sql);
84 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable", $sqlarray[0]);
73f7ad71 85
86 // Malformed param placeholders
87 $sql = "SELECT * FROM {testtable} WHERE name = ?param1";
88 $params = array('param1' => 'first record');
e6b4f00e 89 $sqlarray = $DB->fix_sql_params($sql, $params);
90 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?param1", $sqlarray[0]);
73f7ad71 91
92 // Mixed param types (colon and dollar)
93 $sql = "SELECT * FROM {testtable} WHERE name = :param1, rsstype = \$1";
94 $params = array('param1' => 'first record', 'param2' => 1);
95 try {
e6b4f00e 96 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 97 $this->assertTrue(false);
73f7ad71 98 } catch (Exception $e) {
251387d0 99 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 100 }
101
102 // Mixed param types (question and dollar)
103 $sql = "SELECT * FROM {testtable} WHERE name = ?, rsstype = \$1";
104 $params = array('param1' => 'first record', 'param2' => 1);
73f7ad71 105 try {
e6b4f00e 106 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 107 $this->assertTrue(false);
73f7ad71 108 } catch (Exception $e) {
251387d0 109 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 110 }
73f7ad71 111
112 // Too many params in sql
113 $sql = "SELECT * FROM {testtable} WHERE name = ?, rsstype = ?, course = ?";
114 $params = array('first record', 1);
73f7ad71 115 try {
e6b4f00e 116 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 117 $this->assertTrue(false);
73f7ad71 118 } catch (Exception $e) {
251387d0 119 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 120 }
73f7ad71 121
122 // Too many params in array: no error
123 $params[] = 1;
124 $params[] = time();
73f7ad71 125 $sqlarray = null;
126
127 try {
e6b4f00e 128 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 129 $this->assertTrue(true);
73f7ad71 130 } catch (Exception $e) {
251387d0 131 $this->assertTrue(false);
73f7ad71 132 }
73f7ad71 133 $this->assertTrue($sqlarray[0]);
134
135 // Named params missing from array
136 $sql = "SELECT * FROM {testtable} WHERE name = :name, rsstype = :rsstype";
137 $params = array('wrongname' => 'first record', 'rsstype' => 1);
73f7ad71 138 try {
e6b4f00e 139 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 140 $this->assertTrue(false);
73f7ad71 141 } catch (Exception $e) {
251387d0 142 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 143 }
73f7ad71 144
145 // Duplicate named param in query
146 $sql = "SELECT * FROM {testtable} WHERE name = :name, rsstype = :name";
147 $params = array('name' => 'first record', 'rsstype' => 1);
73f7ad71 148 try {
e6b4f00e 149 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 150 $this->assertTrue(false);
73f7ad71 151 } catch (Exception $e) {
251387d0 152 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 153 }
73f7ad71 154
155 // Unsupported Bound params
156 $sql = "SELECT * FROM {testtable} WHERE name = $1, rsstype = $2";
157 $params = array('first record', 1);
73f7ad71 158 try {
e6b4f00e 159 $sqlarray = $DB->fix_sql_params($sql, $params);
251387d0 160 $this->assertTrue(false);
73f7ad71 161 } catch (Exception $e) {
251387d0 162 $this->assertTrue($e instanceof moodle_exception);
73f7ad71 163 }
73f7ad71 164
165 // Correct named param placeholders
166 $sql = "SELECT * FROM {testtable} WHERE name = :name, rsstype = :rsstype";
167 $params = array('name' => 'first record', 'rsstype' => 1);
e6b4f00e 168 $sqlarray = $DB->fix_sql_params($sql, $params);
169 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, rsstype = ?", $sqlarray[0]);
73f7ad71 170 $this->assertEqual(2, count($sqlarray[1]));
171
172 // Correct ? params
173 $sql = "SELECT * FROM {testtable} WHERE name = ?, rsstype = ?";
174 $params = array('first record', 1);
e6b4f00e 175 $sqlarray = $DB->fix_sql_params($sql, $params);
176 $this->assertEqual("SELECT * FROM {$DB->get_prefix()}testtable WHERE name = ?, rsstype = ?", $sqlarray[0]);
73f7ad71 177 $this->assertEqual(2, count($sqlarray[1]));
178
179 }
180
bb78c788 181 public function testGetTables() {
251387d0 182 $DB = $this->tdb; // do not use global $DB!
183 $dbman = $this->tdb->get_manager();
a230012c 184
bb78c788 185 // Need to test with multiple DBs
186 $this->assertTrue($DB->get_tables() > 2);
187 }
188
189 public function testGetIndexes() {
251387d0 190 $DB = $this->tdb; // do not use global $DB!
191 $dbman = $this->tdb->get_manager();
a230012c 192
bb78c788 193 $this->assertTrue($indices = $DB->get_indexes('testtable'));
194 $this->assertTrue(count($indices) == 1);
195
196 $xmldb_indexes = $this->tables['testtable']->getIndexes();
197 $this->assertEqual(count($indices), count($xmldb_indexes));
198
199 for ($i = 0; $i < count($indices); $i++) {
200 if ($i == 0) {
201 $next_index = reset($indices);
202 $next_xmldb_index = reset($xmldb_indexes);
203 } else {
204 $next_index = next($indices);
205 $next_xmldb_index = next($xmldb_indexes);
206 }
207
208 $this->assertEqual($next_index['columns'][0], $next_xmldb_index->name);
209 }
210 }
211
212 public function testGetColumns() {
251387d0 213 $DB = $this->tdb; // do not use global $DB!
214 $dbman = $this->tdb->get_manager();
bb78c788 215
216 $this->assertTrue($columns = $DB->get_columns('testtable'));
217 $fields = $this->tables['testtable']->getFields();
218 $this->assertEqual(count($columns), count($fields));
219
220 for ($i = 0; $i < count($columns); $i++) {
221 if ($i == 0) {
222 $next_column = reset($columns);
223 $next_field = reset($fields);
224 } else {
225 $next_column = next($columns);
226 $next_field = next($fields);
227 }
228
229 $this->assertEqual($next_column->name, $next_field->name);
230 }
231 }
232
233 public function testExecute() {
251387d0 234 $DB = $this->tdb; // do not use global $DB!
235 $dbman = $this->tdb->get_manager();
a230012c 236
bb78c788 237 $sql = "SELECT * FROM {testtable}";
238 $this->assertTrue($DB->execute($sql));
73f7ad71 239
bb78c788 240 $sql = "INSERT INTO {testtable}
241 SET course = :course,
242 type = :type,
243 name = :name,
244 intro = :intro,
245 assessed = :assessed,
246 assesstimestart = :assesstimestart,
247 assesstimefinish = :assesstimefinish,
248 scale = :scale,
249 maxbytes = :maxbytes,
250 forcesubscribe = :forcesubscribe,
251 trackingtype = :trackingtype,
252 rsstype = :rsstype,
253 rssarticles = :rssarticles,
254 timemodified = :timemodified,
255 warnafter = :warnafter,
256 blockafter = :blockafter,
257 blockperiod = :blockperiod";
258 $values = array('course' => 1,
259 'type' => 'news',
260 'name' => 'test',
261 'intro' => 'Simple news forum',
262 'assessed' => time(),
263 'assesstimestart' => time(),
264 'assesstimefinish' => time() + 579343,
265 'scale' => 1,
266 'maxbytes' => 512,
267 'forcesubscribe' => 1,
268 'trackingtype' => 1,
269 'rssarticles' => 1,
270 'rsstype' => 1,
271 'timemodified' => time(),
272 'warnafter' => time() + 579343,
273 'blockafter' => time() + 600000,
274 'blockperiod' => 5533);
275 $this->assertTrue($DB->execute($sql, $values));
276
277 $record = $DB->get_record('testtable', array('blockperiod' => 5533));
278
279 foreach ($values as $field => $value) {
280 $this->assertEqual($value, $record->$field, "Field $field in DB ({$record->$field}) is not equal to field $field in sql ($value)");
281 }
282 }
283
284}