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