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