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