49926145 |
1 | <?php |
2 | |
3 | // This file is part of Moodle - http://moodle.org/ |
4 | // |
5 | // Moodle is free software: you can redistribute it and/or modify |
6 | // it under the terms of the GNU General Public License as published by |
7 | // the Free Software Foundation, either version 3 of the License, or |
8 | // (at your option) any later version. |
9 | // |
10 | // Moodle is distributed in the hope that it will be useful, |
11 | // but WITHOUT ANY WARRANTY; without even the implied warranty of |
12 | // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
13 | // GNU General Public License for more details. |
14 | // |
15 | // You should have received a copy of the GNU General Public License |
16 | // along with Moodle. If not, see <http://www.gnu.org/licenses/>. |
17 | |
18 | |
19 | /** |
20 | * Native mysqli class representing moodle database interface. |
21 | * |
22 | * @package moodlecore |
23 | * @subpackage DML |
24 | * @copyright 2008 Petr Skoda (http://skodak.org) |
25 | * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later |
26 | */ |
7466a42f |
27 | |
28 | require_once($CFG->libdir.'/dml/moodle_database.php'); |
29 | require_once($CFG->libdir.'/dml/mysqli_native_moodle_recordset.php'); |
30 | |
31 | /** |
32 | * Native mysqli class representing moodle database interface. |
7466a42f |
33 | */ |
34 | class mysqli_native_moodle_database extends moodle_database { |
35 | |
0487f9e2 |
36 | protected $mysqli = null; |
7466a42f |
37 | |
30d2832d |
38 | /** |
39 | * Attempt to create the database |
40 | * @param string $dbhost |
41 | * @param string $dbuser |
42 | * @param string $dbpass |
43 | * @param string $dbname |
44 | * @return bool success |
45 | * @throws dml_exception if error |
46 | */ |
3b093310 |
47 | public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) { |
797ffad0 |
48 | $driverstatus = $this->driver_installed(); |
49 | |
50 | if ($driverstatus !== true) { |
51 | throw new dml_exception('dbdriverproblem', $driverstatus); |
52 | } |
53 | |
30d2832d |
54 | ob_start(); |
7f79aaea |
55 | $conn = new mysqli($dbhost, $dbuser, $dbpass); /// Connect without db |
30d2832d |
56 | $dberr = ob_get_contents(); |
57 | ob_end_clean(); |
58 | $errorno = @$conn->connect_errno; |
59 | |
60 | if ($errorno !== 0) { |
61 | throw new dml_connection_exception($dberr); |
62 | } |
63 | |
64 | $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"); |
65 | |
66 | $conn->close(); |
67 | |
68 | if (!$result) { |
69 | throw new dml_exception('cannotcreatedb'); |
70 | } |
71 | |
72 | return true; |
73 | } |
74 | |
7466a42f |
75 | /** |
76 | * Detects if all needed PHP stuff installed. |
77 | * Note: can be used before connect() |
78 | * @return mixed true if ok, string if something |
79 | */ |
80 | public function driver_installed() { |
81 | if (!extension_loaded('mysqli')) { |
82 | return get_string('mysqliextensionisnotpresentinphp', 'install'); |
83 | } |
84 | return true; |
85 | } |
86 | |
87 | /** |
88 | * Returns database family type - describes SQL dialect |
89 | * Note: can be used before connect() |
90 | * @return string db family name (mysql, postgres, mssql, oracle, etc.) |
91 | */ |
92 | public function get_dbfamily() { |
93 | return 'mysql'; |
94 | } |
95 | |
96 | /** |
97 | * Returns more specific database driver type |
98 | * Note: can be used before connect() |
99 | * @return string db type mysql, mysqli, postgres7 |
100 | */ |
101 | protected function get_dbtype() { |
102 | return 'mysqli'; |
103 | } |
104 | |
105 | /** |
106 | * Returns general database library name |
107 | * Note: can be used before connect() |
108 | * @return string db type adodb, pdo, native |
109 | */ |
110 | protected function get_dblibrary() { |
111 | return 'native'; |
112 | } |
113 | |
114 | /** |
115 | * Returns localised database type name |
116 | * Note: can be used before connect() |
117 | * @return string |
118 | */ |
119 | public function get_name() { |
2aeb3bcb |
120 | return get_string('nativemysqli', 'install'); |
7466a42f |
121 | } |
122 | |
3b093310 |
123 | /** |
124 | * Returns localised database configuration help. |
125 | * Note: can be used before connect() |
126 | * @return string |
127 | */ |
128 | public function get_configuration_help() { |
129 | return get_string('nativemysqlihelp', 'install'); |
130 | } |
131 | |
7466a42f |
132 | /** |
133 | * Returns localised database description |
134 | * Note: can be used before connect() |
135 | * @return string |
136 | */ |
137 | public function get_configuration_hints() { |
2aeb3bcb |
138 | return get_string('databasesettingssub_mysqli', 'install'); |
7466a42f |
139 | } |
140 | |
141 | /** |
142 | * Connect to db |
143 | * Must be called before other methods. |
144 | * @param string $dbhost |
145 | * @param string $dbuser |
146 | * @param string $dbpass |
147 | * @param string $dbname |
7466a42f |
148 | * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used |
149 | * @param array $dboptions driver specific options |
150 | * @return bool success |
151 | */ |
beaa43db |
152 | public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { |
ce152606 |
153 | $driverstatus = $this->driver_installed(); |
154 | |
155 | if ($driverstatus !== true) { |
156 | throw new dml_exception('dbdriverproblem', $driverstatus); |
157 | } |
158 | |
beaa43db |
159 | $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); |
3b093310 |
160 | unset($this->dboptions['dbsocket']); |
7466a42f |
161 | |
ce152606 |
162 | ob_start(); |
7466a42f |
163 | $this->mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); |
ce152606 |
164 | $dberr = ob_get_contents(); |
165 | ob_end_clean(); |
166 | $errorno = @$this->mysqli->connect_errno; |
167 | |
168 | if ($errorno !== 0) { |
169 | throw new dml_connection_exception($dberr); |
7466a42f |
170 | } |
ce152606 |
171 | |
a6283e91 |
172 | $this->query_start("--set_charset()", null, SQL_QUERY_AUX); |
7466a42f |
173 | $this->mysqli->set_charset('utf8'); |
a6283e91 |
174 | $this->query_end(true); |
175 | |
7466a42f |
176 | return true; |
177 | } |
178 | |
179 | /** |
180 | * Close database connection and release all resources |
181 | * and memory (especially circular memory references). |
182 | * Do NOT use connect() again, create a new instance if needed. |
183 | */ |
184 | public function dispose() { |
8fdb24f0 |
185 | parent::dispose(); // Call parent dispose to write/close session and other common stuff before clossing conn |
7466a42f |
186 | if ($this->mysqli) { |
187 | $this->mysqli->close(); |
188 | $this->mysqli = null; |
189 | } |
7466a42f |
190 | } |
191 | |
192 | /** |
193 | * Returns database server info array |
194 | * @return array |
195 | */ |
196 | public function get_server_info() { |
197 | return array('description'=>$this->mysqli->server_info, 'version'=>$this->mysqli->server_info); |
198 | } |
199 | |
200 | /** |
201 | * Returns supported query parameter types |
202 | * @return bitmask |
203 | */ |
204 | protected function allowed_param_types() { |
205 | return SQL_PARAMS_QM; |
206 | } |
207 | |
208 | /** |
209 | * Returns last error reported by database engine. |
210 | */ |
211 | public function get_last_error() { |
212 | return $this->mysqli->error; |
213 | } |
214 | |
215 | /** |
216 | * Return tables in database WITHOUT current prefix |
217 | * @return array of table names in lowercase and without prefix |
218 | */ |
117679db |
219 | public function get_tables($usecache=true) { |
220 | if ($usecache and $this->tables !== null) { |
221 | return $this->tables; |
222 | } |
223 | $this->tables = array(); |
a6283e91 |
224 | $sql = "SHOW TABLES"; |
225 | $this->query_start($sql, null, SQL_QUERY_AUX); |
226 | $result = $this->mysqli->query($sql); |
227 | $this->query_end($result); |
228 | if ($result) { |
7466a42f |
229 | while ($arr = $result->fetch_assoc()) { |
230 | $tablename = reset($arr); |
7fdcb779 |
231 | if ($this->prefix !== '') { |
232 | if (strpos($tablename, $this->prefix) !== 0) { |
233 | continue; |
234 | } |
235 | $tablename = substr($tablename, strlen($this->prefix)); |
7466a42f |
236 | } |
117679db |
237 | $this->tables[$tablename] = $tablename; |
7466a42f |
238 | } |
239 | $result->close(); |
240 | } |
117679db |
241 | return $this->tables; |
7466a42f |
242 | } |
243 | |
244 | /** |
245 | * Return table indexes - everything lowercased |
246 | * @return array of arrays |
247 | */ |
248 | public function get_indexes($table) { |
7466a42f |
249 | $indexes = array(); |
a6283e91 |
250 | $sql = "SHOW INDEXES FROM {$this->prefix}$table"; |
251 | $this->query_start($sql, null, SQL_QUERY_AUX); |
252 | $result = $this->mysqli->query($sql); |
253 | $this->query_end($result); |
254 | if ($result) { |
7466a42f |
255 | while ($res = $result->fetch_object()) { |
256 | if ($res->Key_name === 'PRIMARY') { |
257 | continue; |
258 | } |
259 | if (!isset($indexes[$res->Key_name])) { |
260 | $indexes[$res->Key_name] = array('unique'=>empty($res->Non_unique), 'columns'=>array()); |
261 | } |
262 | $indexes[$res->Key_name]['columns'][$res->Seq_in_index-1] = $res->Column_name; |
263 | } |
264 | $result->close(); |
265 | } |
266 | return $indexes; |
267 | } |
268 | |
269 | /** |
270 | * Returns datailed information about columns in table. This information is cached internally. |
271 | * @param string $table name |
272 | * @param bool $usecache |
273 | * @return array array of database_column_info objects indexed with column names |
274 | */ |
275 | public function get_columns($table, $usecache=true) { |
276 | if ($usecache and isset($this->columns[$table])) { |
277 | return $this->columns[$table]; |
278 | } |
279 | |
280 | $this->columns[$table] = array(); |
281 | |
a6283e91 |
282 | $sql = "SHOW COLUMNS FROM {$this->prefix}$table"; |
283 | $this->query_start($sql, null, SQL_QUERY_AUX); |
284 | $result = $this->mysqli->query($sql); |
285 | $this->query_end($result); |
286 | |
287 | if ($result === false) { |
7466a42f |
288 | return array(); |
289 | } |
290 | |
a6283e91 |
291 | while ($rawcolumn = $result->fetch_assoc()) { |
292 | $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER); |
293 | |
7466a42f |
294 | $info = new object(); |
295 | $info->name = $rawcolumn->field; |
296 | $matches = null; |
297 | |
298 | if (preg_match('/varchar\((\d+)\)/i', $rawcolumn->type, $matches)) { |
299 | $info->type = 'varchar'; |
300 | $info->meta_type = 'C'; |
301 | $info->max_length = $matches[1]; |
302 | $info->scale = null; |
303 | $info->not_null = ($rawcolumn->null === 'NO'); |
304 | $info->default_value = $rawcolumn->default; |
305 | $info->has_default = is_null($info->default_value) ? false : true; |
306 | $info->primary_key = ($rawcolumn->key === 'PRI'); |
307 | $info->binary = false; |
308 | $info->unsigned = null; |
309 | $info->auto_increment= false; |
310 | $info->unique = null; |
311 | |
312 | } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->type, $matches)) { |
313 | $info->type = $matches[1]; |
314 | $info->primary_key = ($rawcolumn->key === 'PRI'); |
315 | if ($info->primary_key) { |
316 | $info->meta_type = 'R'; |
317 | $info->max_length = $matches[2]; |
318 | $info->scale = null; |
319 | $info->not_null = ($rawcolumn->null === 'NO'); |
320 | $info->default_value = $rawcolumn->default; |
321 | $info->has_default = is_null($info->default_value) ? false : true; |
322 | $info->binary = false; |
323 | $info->unsigned = (stripos($rawcolumn->type, 'unsigned') !== false); |
324 | $info->auto_increment= true; |
325 | $info->unique = true; |
326 | } else { |
327 | $info->meta_type = 'I'; |
328 | $info->max_length = $matches[2]; |
329 | $info->scale = null; |
330 | $info->not_null = ($rawcolumn->null === 'NO'); |
331 | $info->default_value = $rawcolumn->default; |
332 | $info->has_default = is_null($info->default_value) ? false : true; |
333 | $info->binary = false; |
334 | $info->unsigned = (stripos($rawcolumn->type, 'unsigned') !== false); |
335 | $info->auto_increment= false; |
336 | $info->unique = null; |
337 | } |
338 | |
c65845cc |
339 | } else if (preg_match('/(decimal|double|float)\((\d+),(\d+)\)/i', $rawcolumn->type, $matches)) { |
340 | $info->type = $matches[1]; |
7466a42f |
341 | $info->meta_type = 'N'; |
c65845cc |
342 | $info->max_length = $matches[2]; |
343 | $info->scale = $matches[3]; |
7466a42f |
344 | $info->not_null = ($rawcolumn->null === 'NO'); |
345 | $info->default_value = $rawcolumn->default; |
346 | $info->has_default = is_null($info->default_value) ? false : true; |
347 | $info->primary_key = ($rawcolumn->key === 'PRI'); |
348 | $info->binary = false; |
349 | $info->unsigned = null; |
350 | $info->auto_increment= false; |
351 | $info->unique = null; |
352 | |
353 | } else if (preg_match('/([a-z]*text)/i', $rawcolumn->type, $matches)) { |
354 | $info->type = $matches[1]; |
355 | $info->meta_type = 'X'; |
356 | $info->max_length = -1; |
357 | $info->scale = null; |
358 | $info->not_null = ($rawcolumn->null === 'NO'); |
359 | $info->default_value = $rawcolumn->default; |
360 | $info->has_default = is_null($info->default_value) ? false : true; |
361 | $info->primary_key = ($rawcolumn->key === 'PRI'); |
362 | $info->binary = false; |
363 | $info->unsigned = null; |
364 | $info->auto_increment= false; |
365 | $info->unique = null; |
366 | |
75dfa4a3 |
367 | } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->type, $matches)) { |
368 | $info->type = $matches[1]; |
369 | $info->meta_type = 'B'; |
370 | $info->max_length = -1; |
371 | $info->scale = null; |
372 | $info->not_null = ($rawcolumn->null === 'NO'); |
373 | $info->default_value = $rawcolumn->default; |
374 | $info->has_default = is_null($info->default_value) ? false : true; |
375 | $info->primary_key = false; |
376 | $info->binary = true; |
377 | $info->unsigned = null; |
378 | $info->auto_increment= false; |
379 | $info->unique = null; |
380 | |
7466a42f |
381 | } else if (preg_match('/enum\((.*)\)/i', $rawcolumn->type, $matches)) { |
382 | $info->type = 'enum'; |
383 | $info->meta_type = 'C'; |
384 | $info->enums = array(); |
385 | $info->max_length = 0; |
386 | $values = $matches[1]; |
387 | $values = explode(',', $values); |
388 | $textlib = textlib_get_instance(); |
389 | foreach ($values as $val) { |
390 | $val = trim($val, "'"); |
391 | $length = $textlib->strlen($val); |
392 | $info->enums[] = $val; |
393 | $info->max_length = ($info->max_length < $length) ? $length : $info->max_length; |
394 | } |
395 | $info->scale = null; |
396 | $info->not_null = ($rawcolumn->null === 'NO'); |
397 | $info->default_value = $rawcolumn->default; |
398 | $info->has_default = is_null($info->default_value) ? false : true; |
399 | $info->primary_key = ($rawcolumn->key === 'PRI'); |
400 | $info->binary = false; |
401 | $info->unsigned = null; |
402 | $info->auto_increment= false; |
403 | $info->unique = null; |
404 | } |
405 | |
406 | $this->columns[$table][$info->name] = new database_column_info($info); |
407 | } |
408 | |
a6283e91 |
409 | $result->close(); |
410 | |
7466a42f |
411 | return $this->columns[$table]; |
412 | } |
413 | |
7466a42f |
414 | /** |
415 | * Is db in unicode mode? |
416 | * @return bool |
417 | */ |
418 | public function setup_is_unicodedb() { |
a6283e91 |
419 | $sql = "SHOW LOCAL VARIABLES LIKE 'character_set_database'"; |
420 | $this->query_start($sql, null, SQL_QUERY_AUX); |
421 | $result = $this->mysqli->query($sql); |
422 | $this->query_end($result); |
423 | |
424 | if ($result) { |
7466a42f |
425 | $result->close(); |
426 | return true; |
427 | } |
428 | return false; |
429 | } |
430 | |
7466a42f |
431 | /** |
432 | * Do NOT use in code, to be used by database_manager only! |
433 | * @param string $sql query |
22d77567 |
434 | * @return bool true |
435 | * @throws dml_exception if error |
7466a42f |
436 | */ |
437 | public function change_database_structure($sql) { |
117679db |
438 | $this->reset_caches(); |
a6283e91 |
439 | |
440 | $this->query_start($sql, null, SQL_QUERY_STRUCTURE); |
441 | $result = $this->mysqli->query($sql); |
442 | $this->query_end($result); |
443 | |
12e09c6b |
444 | return true; |
7466a42f |
445 | } |
446 | |
447 | /** |
448 | * Very ugly hack which emulates bound parameters in queries |
449 | * because prepared statements do not use query cache. |
450 | */ |
451 | protected function emulate_bound_params($sql, array $params=null) { |
452 | if (empty($params)) { |
453 | return $sql; |
454 | } |
455 | /// ok, we have verified sql statement with ? and correct number of params |
456 | $return = strtok($sql, '?'); |
457 | foreach ($params as $param) { |
458 | if (is_bool($param)) { |
459 | $return .= (int)$param; |
460 | } else if (is_null($param)) { |
461 | $return .= 'NULL'; |
c1a6529b |
462 | } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646 |
463 | $return .= $param; |
464 | } else if (is_float($param)) { |
7466a42f |
465 | $return .= $param; |
466 | } else { |
467 | $param = $this->mysqli->real_escape_string($param); |
468 | $return .= "'$param'"; |
469 | } |
470 | $return .= strtok('?'); |
471 | } |
472 | return $return; |
473 | } |
474 | |
475 | /** |
476 | * Execute general sql query. Should be used only when no other method suitable. |
477 | * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead! |
478 | * @param string $sql query |
479 | * @param array $params query parameters |
22d77567 |
480 | * @return bool true |
481 | * @throws dml_exception if error |
7466a42f |
482 | */ |
483 | public function execute($sql, array $params=null) { |
484 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
485 | |
486 | if (strpos($sql, ';') !== false) { |
22d77567 |
487 | throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); |
7466a42f |
488 | } |
489 | |
490 | $rawsql = $this->emulate_bound_params($sql, $params); |
491 | |
a6283e91 |
492 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); |
7466a42f |
493 | $result = $this->mysqli->query($rawsql); |
a6283e91 |
494 | $this->query_end($result); |
7466a42f |
495 | |
22d77567 |
496 | if ($result === true) { |
7466a42f |
497 | return true; |
498 | |
499 | } else { |
500 | $result->close(); |
501 | return true; |
502 | } |
503 | } |
504 | |
505 | /** |
506 | * Get a number of records as a moodle_recordset using a SQL statement. |
507 | * |
508 | * Since this method is a little less readable, use of it should be restricted to |
509 | * code where it's possible there might be large datasets being returned. For known |
510 | * small datasets use get_records_sql - it leads to simpler code. |
511 | * |
512 | * The return type is as for @see function get_recordset. |
513 | * |
514 | * @param string $sql the SQL select query to execute. |
515 | * @param array $params array of sql parameters |
516 | * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). |
517 | * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). |
22d77567 |
518 | * @return mixed an moodle_recordset object |
519 | * @throws dml_exception if error |
7466a42f |
520 | */ |
521 | public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { |
088a4df8 |
522 | $limitfrom = (int)$limitfrom; |
523 | $limitnum = (int)$limitnum; |
3ff8bf26 |
524 | $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom; |
525 | $limitnum = ($limitnum < 0) ? 0 : $limitnum; |
526 | |
7466a42f |
527 | if ($limitfrom or $limitnum) { |
7466a42f |
528 | if ($limitnum < 1) { |
529 | $limitnum = "18446744073709551615"; |
530 | } |
0487f9e2 |
531 | $sql .= " LIMIT $limitfrom, $limitnum"; |
7466a42f |
532 | } |
533 | |
0487f9e2 |
534 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
535 | $rawsql = $this->emulate_bound_params($sql, $params); |
536 | |
a6283e91 |
537 | $this->query_start($sql, $params, SQL_QUERY_SELECT); |
7466a42f |
538 | // no MYSQLI_USE_RESULT here, it would block write ops on affected tables |
e0eda209 |
539 | $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); |
540 | $this->query_end($result); |
7466a42f |
541 | |
e0eda209 |
542 | return $this->create_recordset($result); |
7466a42f |
543 | } |
544 | |
545 | protected function create_recordset($result) { |
546 | return new mysqli_native_moodle_recordset($result); |
547 | } |
548 | |
549 | /** |
550 | * Get a number of records as an array of objects using a SQL statement. |
551 | * |
552 | * Return value as for @see function get_records. |
553 | * |
554 | * @param string $sql the SQL select query to execute. The first column of this SELECT statement |
555 | * must be a unique value (usually the 'id' field), as it will be used as the key of the |
556 | * returned array. |
557 | * @param array $params array of sql parameters |
558 | * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). |
559 | * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). |
22d77567 |
560 | * @return mixed an array of objects, or empty array if no records were found |
561 | * @throws dml_exception if error |
7466a42f |
562 | */ |
563 | public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { |
088a4df8 |
564 | $limitfrom = (int)$limitfrom; |
565 | $limitnum = (int)$limitnum; |
3ff8bf26 |
566 | $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom; |
567 | $limitnum = ($limitnum < 0) ? 0 : $limitnum; |
568 | |
7466a42f |
569 | if ($limitfrom or $limitnum) { |
7466a42f |
570 | if ($limitnum < 1) { |
571 | $limitnum = "18446744073709551615"; |
572 | } |
0487f9e2 |
573 | $sql .= " LIMIT $limitfrom, $limitnum"; |
7466a42f |
574 | } |
575 | |
0487f9e2 |
576 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
577 | $rawsql = $this->emulate_bound_params($sql, $params); |
578 | |
a6283e91 |
579 | $this->query_start($sql, $params, SQL_QUERY_SELECT); |
7466a42f |
580 | $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); |
a6283e91 |
581 | $this->query_end($result); |
7466a42f |
582 | |
7466a42f |
583 | $return = array(); |
a6283e91 |
584 | |
7466a42f |
585 | while($row = $result->fetch_assoc()) { |
586 | $row = array_change_key_case($row, CASE_LOWER); |
587 | $id = reset($row); |
758ba89a |
588 | if (isset($return[$id])) { |
589 | $colname = key($row); |
590 | debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER); |
591 | } |
7466a42f |
592 | $return[$id] = (object)$row; |
593 | } |
594 | $result->close(); |
a6283e91 |
595 | |
7466a42f |
596 | return $return; |
597 | } |
598 | |
599 | /** |
600 | * Selects records and return values (first field) as an array using a SQL statement. |
601 | * |
602 | * @param string $sql The SQL query |
603 | * @param array $params array of sql parameters |
22d77567 |
604 | * @return mixed array of values |
605 | * @throws dml_exception if error |
7466a42f |
606 | */ |
607 | public function get_fieldset_sql($sql, array $params=null) { |
608 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
609 | $rawsql = $this->emulate_bound_params($sql, $params); |
610 | |
a6283e91 |
611 | $this->query_start($sql, $params, SQL_QUERY_SELECT); |
7466a42f |
612 | $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); |
a6283e91 |
613 | $this->query_end($result); |
7466a42f |
614 | |
7466a42f |
615 | $return = array(); |
a6283e91 |
616 | |
7466a42f |
617 | while($row = $result->fetch_assoc()) { |
618 | $return[] = reset($row); |
619 | } |
620 | $result->close(); |
a6283e91 |
621 | |
7466a42f |
622 | return $return; |
623 | } |
624 | |
625 | /** |
626 | * Insert new record into database, as fast as possible, no safety checks, lobs not supported. |
627 | * @param string $table name |
628 | * @param mixed $params data record as object or array |
629 | * @param bool $returnit return it of inserted record |
630 | * @param bool $bulk true means repeated inserts expected |
631 | * @param bool $customsequence true if 'id' included in $params, disables $returnid |
22d77567 |
632 | * @return true or new id |
633 | * @throws dml_exception if error |
7466a42f |
634 | */ |
635 | public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { |
636 | if (!is_array($params)) { |
637 | $params = (array)$params; |
638 | } |
639 | |
640 | if ($customsequence) { |
641 | if (!isset($params['id'])) { |
22d77567 |
642 | throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); |
7466a42f |
643 | } |
644 | $returnid = false; |
645 | } else { |
646 | unset($params['id']); |
647 | } |
648 | |
649 | if (empty($params)) { |
22d77567 |
650 | throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); |
7466a42f |
651 | } |
652 | |
7466a42f |
653 | $fields = implode(',', array_keys($params)); |
654 | $qms = array_fill(0, count($params), '?'); |
655 | $qms = implode(',', $qms); |
656 | |
657 | $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($qms)"; |
76b6daf2 |
658 | |
659 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
7466a42f |
660 | $rawsql = $this->emulate_bound_params($sql, $params); |
661 | |
a6283e91 |
662 | $this->query_start($sql, $params, SQL_QUERY_INSERT); |
7466a42f |
663 | $result = $this->mysqli->query($rawsql); |
b4154c2d |
664 | $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db |
a6283e91 |
665 | $this->query_end($result); |
7466a42f |
666 | |
b4154c2d |
667 | if (!$id) { |
22d77567 |
668 | throw new dml_write_exception('unknown error fetching inserted id'); |
7466a42f |
669 | } |
670 | |
671 | if (!$returnid) { |
672 | return true; |
673 | } else { |
674 | return (int)$id; |
675 | } |
676 | } |
677 | |
678 | /** |
679 | * Insert a record into a table and return the "id" field if required. |
680 | * |
681 | * Some conversions and safety checks are carried out. Lobs are supported. |
682 | * If the return ID isn't required, then this just reports success as true/false. |
683 | * $data is an object containing needed data |
684 | * @param string $table The database table to be inserted into |
685 | * @param object $data A data object with values for one or more fields in the record |
686 | * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned. |
22d77567 |
687 | * @return true or new id |
688 | * @throws dml_exception if error |
7466a42f |
689 | */ |
690 | public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { |
691 | if (!is_object($dataobject)) { |
692 | $dataobject = (object)$dataobject; |
693 | } |
694 | |
695 | $columns = $this->get_columns($table); |
696 | |
697 | unset($dataobject->id); |
698 | $cleaned = array(); |
699 | |
700 | foreach ($dataobject as $field=>$value) { |
701 | if (!isset($columns[$field])) { |
702 | continue; |
703 | } |
704 | $column = $columns[$field]; |
705 | if (is_bool($value)) { |
706 | $value = (int)$value; // prevent "false" problems |
707 | } |
708 | if (!empty($column->enums)) { |
709 | // workaround for problem with wrong enums in mysql |
710 | if (is_null($value) and !$column->not_null) { |
711 | // ok - nulls allowed |
712 | } else { |
713 | if (!in_array((string)$value, $column->enums)) { |
22d77567 |
714 | throw new dml_write_exception('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.'); |
7466a42f |
715 | } |
716 | } |
717 | } |
718 | $cleaned[$field] = $value; |
719 | } |
720 | |
7466a42f |
721 | return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); |
722 | } |
723 | |
724 | /** |
725 | * Import a record into a table, id field is required. |
726 | * Safety checks are NOT carried out. Lobs are supported. |
727 | * |
728 | * @param string $table name of database table to be inserted into |
729 | * @param object $dataobject A data object with values for one or more fields in the record |
22d77567 |
730 | * @return bool true |
731 | * @throws dml_exception if error |
7466a42f |
732 | */ |
733 | public function import_record($table, $dataobject) { |
734 | $dataobject = (object)$dataobject; |
735 | |
7466a42f |
736 | $columns = $this->get_columns($table); |
737 | $cleaned = array(); |
738 | |
739 | foreach ($dataobject as $field=>$value) { |
740 | if (!isset($columns[$field])) { |
741 | continue; |
742 | } |
743 | $cleaned[$field] = $value; |
744 | } |
745 | |
746 | return $this->insert_record_raw($table, $cleaned, false, true, true); |
747 | } |
748 | |
749 | /** |
750 | * Update record in database, as fast as possible, no safety checks, lobs not supported. |
751 | * @param string $table name |
752 | * @param mixed $params data record as object or array |
753 | * @param bool true means repeated updates expected |
22d77567 |
754 | * @return bool true |
755 | * @throws dml_exception if error |
7466a42f |
756 | */ |
757 | public function update_record_raw($table, $params, $bulk=false) { |
758 | if (!is_array($params)) { |
759 | $params = (array)$params; |
760 | } |
761 | if (!isset($params['id'])) { |
22d77567 |
762 | throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); |
7466a42f |
763 | } |
764 | $id = $params['id']; |
765 | unset($params['id']); |
766 | |
767 | if (empty($params)) { |
22d77567 |
768 | throw new coding_exception('moodle_database::update_record_raw() no fields found.'); |
7466a42f |
769 | } |
770 | |
7466a42f |
771 | $sets = array(); |
772 | foreach ($params as $field=>$value) { |
773 | $sets[] = "$field = ?"; |
774 | } |
775 | |
776 | $params[] = $id; // last ? in WHERE condition |
777 | |
778 | $sets = implode(',', $sets); |
779 | $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=?"; |
76b6daf2 |
780 | |
781 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
7466a42f |
782 | $rawsql = $this->emulate_bound_params($sql, $params); |
783 | |
a6283e91 |
784 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); |
7466a42f |
785 | $result = $this->mysqli->query($rawsql); |
a6283e91 |
786 | $this->query_end($result); |
7466a42f |
787 | |
7466a42f |
788 | return true; |
789 | } |
790 | |
791 | /** |
792 | * Update a record in a table |
793 | * |
794 | * $dataobject is an object containing needed data |
795 | * Relies on $dataobject having a variable "id" to |
796 | * specify the record to update |
797 | * |
798 | * @param string $table The database table to be checked against. |
799 | * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. |
800 | * @param bool true means repeated updates expected |
22d77567 |
801 | * @return bool true |
802 | * @throws dml_exception if error |
7466a42f |
803 | */ |
804 | public function update_record($table, $dataobject, $bulk=false) { |
805 | if (!is_object($dataobject)) { |
806 | $dataobject = (object)$dataobject; |
807 | } |
808 | |
7466a42f |
809 | $columns = $this->get_columns($table); |
810 | $cleaned = array(); |
811 | |
812 | foreach ($dataobject as $field=>$value) { |
813 | if (!isset($columns[$field])) { |
814 | continue; |
815 | } |
816 | if (is_bool($value)) { |
817 | $value = (int)$value; // prevent "false" problems |
818 | } |
819 | $cleaned[$field] = $value; |
820 | } |
821 | |
822 | return $this->update_record_raw($table, $cleaned, $bulk); |
823 | } |
824 | |
825 | /** |
826 | * Set a single field in every table record which match a particular WHERE clause. |
827 | * |
828 | * @param string $table The database table to be checked against. |
829 | * @param string $newfield the field to set. |
830 | * @param string $newvalue the value to set the field to. |
831 | * @param string $select A fragment of SQL to be used in a where clause in the SQL call. |
832 | * @param array $params array of sql parameters |
22d77567 |
833 | * @return bool true |
834 | * @throws dml_exception if error |
7466a42f |
835 | */ |
836 | public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { |
837 | if ($select) { |
838 | $select = "WHERE $select"; |
839 | } |
840 | if (is_null($params)) { |
841 | $params = array(); |
842 | } |
843 | list($select, $params, $type) = $this->fix_sql_params($select, $params); |
844 | |
845 | if (is_bool($newvalue)) { |
846 | $newvalue = (int)$newvalue; // prevent "false" problems |
847 | } |
848 | if (is_null($newvalue)) { |
849 | $newfield = "$newfield = NULL"; |
850 | } else { |
851 | $newfield = "$newfield = ?"; |
852 | array_unshift($params, $newvalue); |
853 | } |
854 | $sql = "UPDATE {$this->prefix}$table SET $newfield $select"; |
855 | $rawsql = $this->emulate_bound_params($sql, $params); |
856 | |
a6283e91 |
857 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); |
7466a42f |
858 | $result = $this->mysqli->query($rawsql); |
a6283e91 |
859 | $this->query_end($result); |
7466a42f |
860 | |
7466a42f |
861 | return true; |
862 | } |
863 | |
864 | /** |
865 | * Delete one or more records from a table which match a particular WHERE clause. |
866 | * |
867 | * @param string $table The database table to be checked against. |
868 | * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). |
869 | * @param array $params array of sql parameters |
22d77567 |
870 | * @return bool true |
871 | * @throws dml_exception if error |
7466a42f |
872 | */ |
873 | public function delete_records_select($table, $select, array $params=null) { |
874 | if ($select) { |
875 | $select = "WHERE $select"; |
876 | } |
877 | $sql = "DELETE FROM {$this->prefix}$table $select"; |
878 | |
879 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); |
880 | $rawsql = $this->emulate_bound_params($sql, $params); |
881 | |
a6283e91 |
882 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); |
7466a42f |
883 | $result = $this->mysqli->query($rawsql); |
a6283e91 |
884 | $this->query_end($result); |
7466a42f |
885 | |
7466a42f |
886 | return true; |
887 | } |
888 | |
889 | public function sql_cast_char2int($fieldname, $text=false) { |
890 | return ' CAST(' . $fieldname . ' AS SIGNED) '; |
891 | } |
892 | |
893 | public function sql_concat() { |
894 | $arr = func_get_args(); |
082ae821 |
895 | $s = implode(', ', $arr); |
7466a42f |
896 | if ($s === '') { |
d5d0890c |
897 | return "''"; |
7466a42f |
898 | } |
899 | return "CONCAT($s)"; |
900 | } |
901 | |
902 | public function sql_concat_join($separator="' '", $elements=array()) { |
082ae821 |
903 | $s = implode(', ', $elements); |
904 | |
7466a42f |
905 | if ($s === '') { |
d5d0890c |
906 | return "''"; |
7466a42f |
907 | } |
082ae821 |
908 | return "CONCAT_WS($separator, $s)"; |
7466a42f |
909 | } |
910 | |
7e0db2e2 |
911 | /** |
912 | * Returns the SQL text to be used to calculate the length in characters of one expression. |
913 | * @param string fieldname or expression to calculate its length in characters. |
914 | * @return string the piece of SQL code to be used in the statement. |
915 | */ |
916 | public function sql_length($fieldname) { |
917 | return ' CHAR_LENGTH(' . $fieldname . ')'; |
918 | } |
919 | |
7466a42f |
920 | /** |
921 | * Does this driver suppoer regex syntax when searching |
922 | */ |
923 | public function sql_regex_supported() { |
924 | return true; |
925 | } |
926 | |
927 | /** |
928 | * Return regex positive or negative match sql |
929 | * @param bool $positivematch |
930 | * @return string or empty if not supported |
931 | */ |
932 | public function sql_regex($positivematch=true) { |
933 | return $positivematch ? 'REGEXP' : 'NOT REGEXP'; |
934 | } |
fa76662b |
935 | |
adff97c5 |
936 | public function sql_cast_2signed($fieldname) { |
937 | return ' CAST(' . $fieldname . ' AS SIGNED) '; |
938 | } |
939 | |
7f79aaea |
940 | /// session locking |
5e9dd017 |
941 | public function session_lock_supported() { |
942 | return true; |
943 | } |
944 | |
3b1a9849 |
945 | public function get_session_lock($rowid) { |
5e9dd017 |
946 | parent::get_session_lock($rowid); |
3b1a9849 |
947 | $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; |
948 | $sql = "SELECT GET_LOCK('$fullname',120)"; |
949 | $this->query_start($sql, null, SQL_QUERY_AUX); |
950 | $result = $this->mysqli->query($sql); |
7f79aaea |
951 | $this->query_end($result); |
952 | |
953 | if ($result) { |
954 | $arr = $result->fetch_assoc(); |
955 | $result->close(); |
956 | |
957 | if (reset($arr) == 1) { |
5e9dd017 |
958 | return; |
3b1a9849 |
959 | } else { |
960 | // try again! |
5e9dd017 |
961 | $this->get_session_lock($rowid); |
7f79aaea |
962 | } |
963 | } |
7f79aaea |
964 | } |
965 | |
3b1a9849 |
966 | public function release_session_lock($rowid) { |
5e9dd017 |
967 | parent::release_session_lock($rowid); |
3b1a9849 |
968 | $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; |
969 | $sql = "SELECT RELEASE_LOCK('$fullname')"; |
970 | $this->query_start($sql, null, SQL_QUERY_AUX); |
971 | $result = $this->mysqli->query($sql); |
7f79aaea |
972 | $this->query_end($result); |
973 | |
974 | if ($result) { |
7f79aaea |
975 | $result->close(); |
7f79aaea |
976 | } |
7f79aaea |
977 | } |
978 | |
fa76662b |
979 | /// transactions |
980 | /** |
981 | * on DBs that support it, switch to transaction mode and begin a transaction |
982 | * you'll need to ensure you call commit_sql() or your changes *will* be lost. |
983 | * |
984 | * this is _very_ useful for massive updates |
985 | */ |
986 | public function begin_sql() { |
a1dda107 |
987 | if (!parent::begin_sql()) { |
988 | return false; |
989 | } |
a6283e91 |
990 | $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"; |
991 | $this->query_start($sql, NULL, SQL_QUERY_AUX); |
992 | $result = $this->mysqli->query($sql); |
993 | $this->query_end($result); |
994 | |
a6283e91 |
995 | $sql = "BEGIN"; |
996 | $this->query_start($sql, NULL, SQL_QUERY_AUX); |
997 | $result = $this->mysqli->query($sql); |
998 | $this->query_end($result); |
999 | |
fa76662b |
1000 | return true; |
1001 | } |
1002 | |
1003 | /** |
1004 | * on DBs that support it, commit the transaction |
1005 | */ |
1006 | public function commit_sql() { |
a1dda107 |
1007 | if (!parent::commit_sql()) { |
1008 | return false; |
1009 | } |
a6283e91 |
1010 | $sql = "COMMIT"; |
1011 | $this->query_start($sql, NULL, SQL_QUERY_AUX); |
1012 | $result = $this->mysqli->query($sql); |
1013 | $this->query_end($result); |
1014 | |
fa76662b |
1015 | return true; |
1016 | } |
1017 | |
1018 | /** |
1019 | * on DBs that support it, rollback the transaction |
1020 | */ |
1021 | public function rollback_sql() { |
a1dda107 |
1022 | if (!parent::rollback_sql()) { |
1023 | return false; |
1024 | } |
a6283e91 |
1025 | $sql = "ROLLBACK"; |
1026 | $this->query_start($sql, NULL, SQL_QUERY_AUX); |
1027 | $result = $this->mysqli->query($sql); |
1028 | $this->query_end($result); |
1029 | |
fa76662b |
1030 | return true; |
1031 | } |
7466a42f |
1032 | } |