· 7 years ago · Feb 28, 2019, 08:16 AM
1<?php
2/**
3 * ---------------------------------------------------------------------
4 * GLPI - Gestionnaire Libre de Parc Informatique
5 * Copyright (C) 2015-2018 Teclib' and contributors.
6 *
7 * http://glpi-project.org
8 *
9 * based on GLPI - Gestionnaire Libre de Parc Informatique
10 * Copyright (C) 2003-2014 by the INDEPNET Development Team.
11 *
12 * ---------------------------------------------------------------------
13 *
14 * LICENSE
15 *
16 * This file is part of GLPI.
17 *
18 * GLPI is free software; you can redistribute it and/or modify
19 * it under the terms of the GNU General Public License as published by
20 * the Free Software Foundation; either version 2 of the License, or
21 * (at your option) any later version.
22 *
23 * GLPI is distributed in the hope that it will be useful,
24 * but WITHOUT ANY WARRANTY; without even the implied warranty of
25 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26 * GNU General Public License for more details.
27 *
28 * You should have received a copy of the GNU General Public License
29 * along with GLPI. If not, see <http://www.gnu.org/licenses/>.
30 * ---------------------------------------------------------------------
31 */
32
33if (!defined('GLPI_ROOT')) {
34 die("Sorry. You can't access this file directly");
35}
36
37/**
38 * Database class for Mysql
39**/
40class DBmysql {
41
42 //! Database Host - string or Array of string (round robin)
43 public $dbhost = "";
44 //! Database User
45 public $dbuser = "";
46 //! Database Password
47 public $dbpassword = "";
48 //! Default Database
49 public $dbdefault = "";
50 //! Database Handler
51 private $dbh;
52 //! Database Error
53 public $error = 0;
54
55 // Slave management
56 public $slave = false;
57 /** Is it a first connection ?
58 * Indicates if the first connection attempt is successful or not
59 * if first attempt fail -> display a warning which indicates that glpi is in readonly
60 **/
61 public $first_connection = true;
62 // Is connected to the DB ?
63 public $connected = false;
64
65 //to calculate execution time
66 public $execution_time = false;
67
68 private $cache_disabled = false;
69
70 /**
71 * Constructor / Connect to the MySQL Database
72 *
73 * @param integer $choice host number (default NULL)
74 *
75 * @return void
76 */
77 function __construct($choice = null) {
78 $this->connect($choice);
79 }
80
81 /**
82 * Connect using current database settings
83 * Use dbhost, dbuser, dbpassword and dbdefault
84 *
85 * @param integer $choice host number (default NULL)
86 *
87 * @return void
88 */
89 function connect($choice = null) {
90 $this->connected = false;
91
92 if (is_array($this->dbhost)) {
93 // Round robin choice
94 $i = (isset($choice) ? $choice : mt_rand(0, count($this->dbhost)-1));
95 $host = $this->dbhost[$i];
96
97 } else {
98 $host = $this->dbhost;
99 }
100
101 $hostport = explode(":", $host);
102 if (count($hostport) < 2) {
103 // Host
104 $this->dbh = @new mysqli($host, $this->dbuser, rawurldecode($this->dbpassword),
105 $this->dbdefault);
106
107 } else if (intval($hostport[1])>0) {
108 // Host:port
109 $this->dbh = @new mysqli($hostport[0], $this->dbuser, rawurldecode($this->dbpassword),
110 $this->dbdefault, $hostport[1]);
111 } else {
112 // :Socket
113 $this->dbh = @new mysqli($hostport[0], $this->dbuser, rawurldecode($this->dbpassword),
114 $this->dbdefault, ini_get('mysqli.default_port'), $hostport[1]);
115 }
116
117 if ($this->dbh->connect_error) {
118 $this->connected = false;
119 $this->error = 1;
120 } else {
121 $this->dbh->set_charset(isset($this->dbenc) ? $this->dbenc : "utf8");
122
123 if (GLPI_FORCE_EMPTY_SQL_MODE) {
124 $this->dbh->query("SET SESSION sql_mode = ''");
125 }
126 $this->connected = true;
127 }
128 }
129
130 /**
131 * Escapes special characters in a string for use in an SQL statement,
132 * taking into account the current charset of the connection
133 *
134 * @since 0.84
135 *
136 * @param string $string String to escape
137 *
138 * @return string escaped string
139 */
140 function escape($string) {
141 return $this->dbh->real_escape_string($string);
142 }
143
144 /**
145 * Execute a MySQL query
146 *
147 * @param string $query Query to execute
148 *
149 * @var array $CFG_GLPI
150 * @var array $DEBUG_SQL
151 * @var integer $SQL_TOTAL_REQUEST
152 *
153 * @return mysqli_result|boolean Query result handler
154 *
155 * @throws GlpitestSQLError
156 */
157 function query($query) {
158 global $CFG_GLPI, $DEBUG_SQL, $SQL_TOTAL_REQUEST;
159
160 $is_debug = isset($_SESSION['glpi_use_mode']) && ($_SESSION['glpi_use_mode'] == Session::DEBUG_MODE);
161 if ($is_debug && $CFG_GLPI["debug_sql"]) {
162 $SQL_TOTAL_REQUEST++;
163 $DEBUG_SQL["queries"][$SQL_TOTAL_REQUEST] = $query;
164 }
165 if ($is_debug && $CFG_GLPI["debug_sql"] || $this->execution_time === true) {
166 $TIMER = new Timer();
167 $TIMER->start();
168 }
169
170 $res = @$this->dbh->query($query);
171 if (!$res) {
172 // no translation for error logs
173 $error = " *** MySQL query error:\n SQL: ".$query."\n Error: ".
174 $this->dbh->error."\n";
175 $error .= Toolbox::backtrace(false, 'DBmysql->query()', ['Toolbox::backtrace()']);
176
177 Toolbox::logSqlError($error);
178
179 if (($is_debug || isAPI()) && $CFG_GLPI["debug_sql"]) {
180 $DEBUG_SQL["errors"][$SQL_TOTAL_REQUEST] = $this->error();
181 }
182 }
183
184 if ($is_debug && $CFG_GLPI["debug_sql"]) {
185 $TIME = $TIMER->getTime();
186 $DEBUG_SQL["times"][$SQL_TOTAL_REQUEST] = $TIME;
187 }
188 if ($this->execution_time === true) {
189 $this->execution_time = $TIMER->getTime(0, true);
190 }
191 return $res;
192 }
193
194 /**
195 * Execute a MySQL query and die
196 * (optionnaly with a message) if it fails
197 *
198 * @since 0.84
199 *
200 * @param string $query Query to execute
201 * @param string $message Explanation of query (default '')
202 *
203 * @return mysqli_result Query result handler
204 */
205 function queryOrDie($query, $message = '') {
206 $res = $this->query($query);
207 if (!$res) {
208 //TRANS: %1$s is the description, %2$s is the query, %3$s is the error message
209 $message = sprintf(
210 __('%1$s - Error during the database query: %2$s - Error is %3$s'),
211 $message,
212 $query,
213 $this->error()
214 );
215 if (isCommandLine()) {
216 throw new \RuntimeException($message);
217 } else {
218 echo $message . "\n";
219 die(1);
220 }
221 }
222 return $res;
223 }
224
225 /**
226 * Prepare a MySQL query
227 *
228 * @param string $query Query to prepare
229 *
230 * @return mysqli_stmt|boolean statement object or FALSE if an error occurred.
231 *
232 * @throws GlpitestSQLError
233 */
234 function prepare($query) {
235 global $CFG_GLPI, $DEBUG_SQL, $SQL_TOTAL_REQUEST;
236
237 $res = @$this->dbh->prepare($query);
238 if (!$res) {
239 // no translation for error logs
240 $error = " *** MySQL prepare error:\n SQL: ".$query."\n Error: ".
241 $this->dbh->error."\n";
242 $error .= Toolbox::backtrace(false, 'DBmysql->prepare()', ['Toolbox::backtrace()']);
243
244 Toolbox::logInFile("sql-errors", $error);
245 if (class_exists('GlpitestSQLError')) { // For unit test
246 throw new GlpitestSQLError($error);
247 }
248
249 if (($_SESSION['glpi_use_mode'] == Session::DEBUG_MODE)
250 && $CFG_GLPI["debug_sql"]) {
251 $SQL_TOTAL_REQUEST++;
252 $DEBUG_SQL["errors"][$SQL_TOTAL_REQUEST] = $this->error();
253 }
254 }
255 return $res;
256 }
257
258 /**
259 * Give result from a sql result
260 *
261 * @param mysqli_result $result MySQL result handler
262 * @param int $i Row offset to give
263 * @param type $field Field to give
264 *
265 * @return mixed Value of the Row $i and the Field $field of the Mysql $result
266 */
267 function result($result, $i, $field) {
268 if ($result && ($result->data_seek($i))
269 && ($data = $result->fetch_array())
270 && isset($data[$field])) {
271 return $data[$field];
272 }
273 return null;
274 }
275
276 /**
277 * Number of rows
278 *
279 * @param mysqli_result $result MySQL result handler
280 *
281 * @return integer number of rows
282 */
283 function numrows($result) {
284 return $result->num_rows;
285 }
286
287 /**
288 * Fetch array of the next row of a Mysql query
289 * Please prefer fetch_row or fetch_assoc
290 *
291 * @param mysqli_result $result MySQL result handler
292 *
293 * @return string[]|null array results
294 */
295 function fetch_array($result) {
296 return $result->fetch_array();
297 }
298
299 /**
300 * Fetch row of the next row of a Mysql query
301 *
302 * @param mysqli_result $result MySQL result handler
303 *
304 * @return mixed|null result row
305 */
306 function fetch_row($result) {
307 return $result->fetch_row();
308 }
309
310 /**
311 * Fetch assoc of the next row of a Mysql query
312 *
313 * @param mysqli_result $result MySQL result handler
314 *
315 * @return string[]|null result associative array
316 */
317 function fetch_assoc($result) {
318 return $result->fetch_assoc();
319 }
320
321 /**
322 * Fetch object of the next row of an SQL query
323 *
324 * @param mysqli_result $result MySQL result handler
325 *
326 * @return object|null
327 */
328 function fetch_object($result) {
329 return $result->fetch_object();
330 }
331
332 /**
333 * Move current pointer of a Mysql result to the specific row
334 *
335 * @param mysqli_result $result MySQL result handler
336 * @param integer $num Row to move current pointer
337 *
338 * @return boolean
339 */
340 function data_seek($result, $num) {
341 return $result->data_seek($num);
342 }
343
344 /**
345 * Give ID of the last inserted item by Mysql
346 *
347 * @return mixed
348 */
349 function insert_id() {
350 return $this->dbh->insert_id;
351 }
352
353 /**
354 * Give number of fields of a Mysql result
355 *
356 * @param mysqli_result $result MySQL result handler
357 *
358 * @return int number of fields
359 */
360 function num_fields($result) {
361 return $result->field_count;
362 }
363
364 /**
365 * Give name of a field of a Mysql result
366 *
367 * @param mysqli_result $result MySQL result handler
368 * @param integer $nb ID of the field
369 *
370 * @return string name of the field
371 */
372 function field_name($result, $nb) {
373 $finfo = $result->fetch_fields();
374 return $finfo[$nb]->name;
375 }
376
377
378 /**
379 * List tables in database
380 *
381 * @param string $table table name condition (glpi_% as default to retrieve only glpi tables)
382 *
383 * @return mysqli_result list of tables
384 *
385 * @deprecated 9.3
386 */
387 function list_tables($table = "glpi_%") {
388 Toolbox::deprecated('list_tables is deprecated, use listTables');
389 return $this->query(
390 "SELECT TABLE_NAME FROM information_schema.`TABLES`
391 WHERE TABLE_SCHEMA = '{$this->dbdefault}'
392 AND TABLE_TYPE = 'BASE TABLE'
393 AND TABLE_NAME LIKE '$table'"
394 );
395 }
396
397 /**
398 * List tables in database
399 *
400 * @param string $table Table name condition (glpi_% as default to retrieve only glpi tables)
401 * @param array $where Where clause to append
402 *
403 * @return DBmysqlIterator
404 */
405 function listTables($table = 'glpi_%', array $where = []) {
406 $iterator = $this->request([
407 'SELECT' => 'TABLE_NAME',
408 'FROM' => 'information_schema.TABLES',
409 'WHERE' => [
410 'TABLE_SCHEMA' => $this->dbdefault,
411 'TABLE_TYPE' => 'BASE TABLE',
412 'TABLE_NAME' => ['LIKE', $table]
413 ] + $where
414 ]);
415 return $iterator;
416 }
417
418 public function getMyIsamTables() {
419 $iterator = $this->listTables('glpi_%', ['engine' => 'MyIsam']);
420 return $iterator;
421 }
422
423
424 /**
425 * List fields of a table
426 *
427 * @param string $table Table name condition
428 * @param boolean $usecache If use field list cache (default true)
429 *
430 * @return mixed list of fields
431 */
432 function list_fields($table, $usecache = true) {
433 static $cache = [];
434
435 if (!$this->cache_disabled && $usecache && isset($cache[$table])) {
436 return $cache[$table];
437 }
438 $result = $this->query("SHOW COLUMNS FROM `$table`");
439 if ($result) {
440 if ($this->numrows($result) > 0) {
441 $cache[$table] = [];
442 while ($data = $result->fetch_assoc()) {
443 $cache[$table][$data["Field"]] = $data;
444 }
445 return $cache[$table];
446 }
447 return [];
448 }
449 return false;
450 }
451
452 /**
453 * Get number of affected rows in previous MySQL operation
454 *
455 * @return int number of affected rows on success, and -1 if the last query failed.
456 */
457 function affected_rows() {
458 return $this->dbh->affected_rows;
459 }
460
461 /**
462 * Free result memory
463 *
464 * @param mysqli_result $result MySQL result handler
465 *
466 * @return boolean TRUE on success or FALSE on failure.
467 */
468 function free_result($result) {
469 return $result->free();
470 }
471
472 /**
473 * Returns the numerical value of the error message from previous MySQL operation
474 *
475 * @return int error number from the last MySQL function, or 0 (zero) if no error occurred.
476 */
477 function errno() {
478 return $this->dbh->errno;
479 }
480
481 /**
482 * Returns the text of the error message from previous MySQL operation
483 *
484 * @return string error text from the last MySQL function, or '' (empty string) if no error occurred.
485 */
486 function error() {
487 return $this->dbh->error;
488 }
489
490 /**
491 * Close MySQL connection
492 *
493 * @return boolean TRUE on success or FALSE on failure.
494 */
495 function close() {
496 if ($this->connected && $this->dbh) {
497 return $this->dbh->close();
498 }
499 return false;
500 }
501
502 /**
503 * is a slave database ?
504 *
505 * @return boolean
506 */
507 function isSlave() {
508 return $this->slave;
509 }
510
511 /**
512 * Execute all the request in a file
513 *
514 * @param string $path with file full path
515 *
516 * @return boolean true if all query are successfull
517 */
518 function runFile($path) {
519 $DBf_handle = fopen($path, "rt");
520 if (!$DBf_handle) {
521 return false;
522 }
523
524 $formattedQuery = "";
525 $lastresult = false;
526 while (!feof($DBf_handle)) {
527 // specify read length to be able to read long lines
528 $buffer = fgets($DBf_handle, 102400);
529
530 // do not strip comments due to problems when # in begin of a data line
531 $formattedQuery .= $buffer;
532 if ((substr(rtrim($formattedQuery), -1) == ";")
533 && (substr(rtrim($formattedQuery), -4) != ">")
534 && (substr(rtrim($formattedQuery), -4) != "160;")) {
535
536 $formattedQuerytorun = $formattedQuery;
537
538 // Do not use the $DB->query
539 if ($this->query($formattedQuerytorun)) { //if no success continue to concatenate
540 $formattedQuery = "";
541 $lastresult = true;
542 } else {
543 $lastresult = false;
544 }
545 }
546 }
547
548 return $lastresult;
549 }
550
551 /**
552 * Instanciate a Simple DBIterator
553 *
554 * Examples =
555 * foreach ($DB->request("select * from glpi_states") as $data) { ... }
556 * foreach ($DB->request("glpi_states") as $ID => $data) { ... }
557 * foreach ($DB->request("glpi_states", "ID=1") as $ID => $data) { ... }
558 * foreach ($DB->request("glpi_states", "", "name") as $ID => $data) { ... }
559 * foreach ($DB->request("glpi_computers",array("name"=>"SBEI003W","entities_id"=>1),array("serial","otherserial")) { ... }
560 *
561 * Examples =
562 * array("id"=>NULL)
563 * array("OR"=>array("id"=>1, "NOT"=>array("state"=>3)));
564 * array("AND"=>array("id"=>1, array("NOT"=>array("state"=>array(3,4,5),"toto"=>2))))
565 *
566 * FIELDS name or array of field names
567 * ORDER name or array of field names
568 * LIMIT max of row to retrieve
569 * START first row to retrieve
570 *
571 * @param string|string[] $tableorsql Table name, array of names or SQL query
572 * @param string|string[] $crit String or array of filed/values, ex array("id"=>1), if empty => all rows
573 * (default '')
574 * @param boolean $debug To log the request (default false)
575 *
576 * @return DBmysqlIterator
577 */
578 public function request ($tableorsql, $crit = "", $debug = false) {
579 $iterator = new DBmysqlIterator($this);
580 $iterator->execute($tableorsql, $crit, $debug);
581 return $iterator;
582 }
583
584 /**
585 * Optimize sql table
586 *
587 * @var DB $DB
588 *
589 * @param mixed $migration Migration class (default NULL)
590 * @param boolean $cron To know if optimize must be done (false by default)
591 *
592 * @deprecated 9.2.2
593 *
594 * @return int number of tables
595 */
596 static function optimize_tables($migration = null, $cron = false) {
597 global $DB;
598
599 Toolbox::deprecated();
600
601 $crashed_tables = self::checkForCrashedTables();
602 if (!empty($crashed_tables)) {
603 Toolbox::logError("Cannot launch automatic action : crashed tables detected");
604 return -1;
605 }
606
607 if (!is_null($migration) && method_exists($migration, 'displayMessage')) {
608 $migration->displayTitle(__('Optimizing tables'));
609 $migration->addNewMessageArea('optimize_table'); // to force new ajax zone
610 $migration->displayMessage(sprintf(__('%1$s - %2$s'), __('optimize'), __('Start')));
611 }
612 $result = $DB->listTables();
613 $nb = 0;
614
615 while ($line = $result->next()) {
616 $table = $line['TABLE_NAME'];
617
618 // For big database to reduce delay of migration
619 if ($cron
620 || (countElementsInTable($table) < 15000000)) {
621
622 if (!is_null($migration) && method_exists($migration, 'displayMessage')) {
623 $migration->displayMessage(sprintf(__('%1$s - %2$s'), __('optimize'), $table));
624 }
625
626 $query = "OPTIMIZE TABLE `".$table."`;";
627 $DB->query($query);
628 $nb++;
629 }
630 }
631
632 if (!is_null($migration)
633 && method_exists($migration, 'displayMessage') ) {
634 $migration->displayMessage(sprintf(__('%1$s - %2$s'), __('optimize'), __('End')));
635 }
636
637 return $nb;
638 }
639
640 /**
641 * Get information about DB connection for showSystemInformations
642 *
643 * @since 0.84
644 *
645 * @return string[] Array of label / value
646 */
647 public function getInfo() {
648 // No translation, used in sysinfo
649 $ret = [];
650 $req = $this->request("SELECT @@sql_mode as mode, @@version AS vers, @@version_comment AS stype");
651
652 if (($data = $req->next())) {
653 if ($data['stype']) {
654 $ret['Server Software'] = $data['stype'];
655 }
656 if ($data['vers']) {
657 $ret['Server Version'] = $data['vers'];
658 } else {
659 $ret['Server Version'] = $this->dbh->server_info;
660 }
661 if ($data['mode']) {
662 $ret['Server SQL Mode'] = $data['mode'];
663 } else {
664 $ret['Server SQL Mode'] = '';
665 }
666 }
667 $ret['Parameters'] = $this->dbuser."@".$this->dbhost."/".$this->dbdefault;
668 $ret['Host info'] = $this->dbh->host_info;
669
670 return $ret;
671 }
672
673 /**
674 * Is MySQL strict mode ?
675 * @since 0.90
676 *
677 * @var DB $DB
678 *
679 * @param string $msg Mode
680 *
681 * @return boolean
682 */
683 static function isMySQLStrictMode(&$msg) {
684 global $DB;
685
686 $msg = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER';
687 $req = $DB->request("SELECT @@sql_mode as mode");
688 if (($data = $req->next())) {
689 return (preg_match("/STRICT_TRANS/", $data['mode'])
690 && preg_match("/NO_ZERO_/", $data['mode'])
691 && preg_match("/ONLY_FULL_GROUP_BY/", $data['mode']));
692 }
693 return false;
694 }
695
696 /**
697 * Get a global DB lock
698 *
699 * @since 0.84
700 *
701 * @param string $name lock's name
702 *
703 * @return boolean
704 */
705 public function getLock($name) {
706 $name = addslashes($this->dbdefault.'.'.$name);
707 $query = "SELECT GET_LOCK('$name', 0)";
708 $result = $this->query($query);
709 list($lock_ok) = $this->fetch_row($result);
710
711 return $lock_ok;
712 }
713
714 /**
715 * Release a global DB lock
716 *
717 * @since 0.84
718 *
719 * @param string $name lock's name
720 *
721 * @return boolean
722 */
723 public function releaseLock($name) {
724 $name = addslashes($this->dbdefault.'.'.$name);
725 $query = "SELECT RELEASE_LOCK('$name')";
726 $result = $this->query($query);
727 list($lock_ok) = $this->fetch_row($result);
728
729 return $lock_ok;
730 }
731
732 /**
733 * Check for crashed MySQL Tables
734 *
735 * @since 0.90.2
736 *
737 * @deprecated 9.3.1
738 *
739 * @var DB $DB
740 *
741 * @return string[] array with supposed crashed table and check message
742 */
743 static public function checkForCrashedTables() {
744 global $DB;
745
746 Toolbox::deprecated();
747
748 $crashed_tables = [];
749
750 $result_tables = $DB->listTables();
751
752 while ($line = $result_tables->next()) {
753 $query = "CHECK TABLE `".$line['TABLE_NAME']."` FAST";
754 $result = $DB->query($query);
755 if ($DB->numrows($result) > 0) {
756 $row = $DB->fetch_array($result);
757 if ($row['Msg_type'] != 'status' && $row['Msg_type'] != 'note') {
758 $crashed_tables[] = ['table' => $row[0],
759 'Msg_type' => $row['Msg_type'],
760 'Msg_text' => $row['Msg_text']];
761 }
762 }
763 }
764 return $crashed_tables;
765 }
766
767 /**
768 * Check if a table exists
769 *
770 * @since 9.2
771 *
772 * @param string $tablename Table name
773 *
774 * @return boolean
775 **/
776 public function tableExists($tablename) {
777 // Get a list of tables contained within the database.
778 $result = $this->listTables("%$tablename%");
779
780 if (count($result)) {
781 while ($data = $result->next()) {
782 if ($data['TABLE_NAME'] === $tablename) {
783 return true;
784 }
785 }
786 }
787
788 return false;
789 }
790
791 /**
792 * Check if a field exists
793 *
794 * @since 9.2
795 *
796 * @param string $table Table name for the field we're looking for
797 * @param string $field Field name
798 * @param Boolean $usecache Use cache; @see DBmysql::list_fields(), defaults to true
799 *
800 * @return boolean
801 **/
802 public function fieldExists($table, $field, $usecache = true) {
803 if (!$this->tableExists($table)) {
804 trigger_error("Table $table does not exists", E_USER_WARNING);
805 return false;
806 }
807
808 if ($fields = $this->list_fields($table, $usecache)) {
809 if (isset($fields[$field])) {
810 return true;
811 }
812 return false;
813 }
814 return false;
815 }
816
817 /**
818 * Disable table cache globally; usefull for migrations
819 *
820 * @return void
821 */
822 public function disableTableCaching() {
823 $this->cache_disabled = true;
824 }
825
826 /**
827 * Quote field name
828 *
829 * @since 9.3
830 *
831 * @param string $name of field to quote (or table.field)
832 *
833 * @return string
834 */
835 public static function quoteName($name) {
836 //handle verbatim names
837 if ($name instanceof QueryExpression) {
838 return $name->getValue();
839 }
840 //handle aliases
841 $names = preg_split('/ AS /i', $name);
842 if (count($names) > 2) {
843 throw new \RuntimeException(
844 'Invalid field name ' . $name
845 );
846 }
847 if (count($names) == 2) {
848 $name = self::quoteName($names[0]);
849 $name .= ' AS ' . self::quoteName($names[1]);
850 return $name;
851 } else {
852 if (strpos($name, '.')) {
853 $n = explode('.', $name, 2);
854 $table = self::quoteName($n[0]);
855 $field = ($n[1] === '*') ? $n[1] : self::quoteName($n[1]);
856 return "$table.$field";
857 }
858 return ($name[0]=='`' ? $name : ($name === '*') ? $name : "`$name`");
859 }
860 }
861
862 /**
863 * Quote value for insert/update
864 *
865 * @param mixed $value Value
866 *
867 * @return mixed
868 */
869 public static function quoteValue($value) {
870 if ($value instanceof QueryParam || $value instanceof QueryExpression) {
871 //no quote for query parameters nor expressions
872 $value = $value->getValue();
873 } else if ($value === null || $value === 'NULL' || $value === 'null') {
874 $value = 'NULL';
875 } else if (!preg_match("/^`.*?`$/", $value)) { //`field` is valid only for mysql :/
876 //phone numbers may start with '+' and will be considered as numeric
877 $value = "'$value'";
878 }
879 return $value;
880 }
881
882 /**
883 * Builds an insert statement
884 *
885 * @since 9.3
886 *
887 * @param string $table Table name
888 * @param array $params Query parameters ([field name => field value)
889 *
890 * @return string
891 */
892 public function buildInsert($table, $params) {
893 $query = "INSERT INTO " . self::quoteName($table) . " (";
894
895 $fields = [];
896 foreach ($params as $key => &$value) {
897 $fields[] = $this->quoteName($key);
898 $value = $this->quoteValue($value);
899 }
900
901 $query .= implode(', ', $fields);
902 $query .= ") VALUES (";
903 $query .= implode(", ", $params);
904 $query .= ")";
905
906 return $query;
907 }
908
909 /**
910 * Insert a row in the database
911 *
912 * @since 9.3
913 *
914 * @param string $table Table name
915 * @param array $params Query parameters ([field name => field value)
916 *
917 * @return mysqli_result|boolean Query result handler
918 */
919 public function insert($table, $params) {
920 $result = $this->query(
921 $this->buildInsert($table, $params)
922 );
923 return $result;
924 }
925
926 /**
927 * Insert a row in the database and die
928 * (optionnaly with a message) if it fails
929 *
930 * @since 9.3
931 *
932 * @param string $table Table name
933 * @param array $params Query parameters ([field name => field value)
934 * @param string $message Explanation of query (default '')
935 *
936 * @return mysqli_result|boolean Query result handler
937 */
938 function insertOrDie($table, $params, $message = '') {
939 $insert = $this->buildInsert($table, $params);
940 $res = $this->query($insert);
941 if (!$res) {
942 //TRANS: %1$s is the description, %2$s is the query, %3$s is the error message
943 $message = sprintf(
944 __('%1$s - Error during the database query: %2$s - Error is %3$s'),
945 $message,
946 $insert,
947 $this->error()
948 );
949 if (isCommandLine()) {
950 throw new \RuntimeException($message);
951 } else {
952 echo $message . "\n";
953 die(1);
954 }
955 }
956 return $res;
957 }
958
959 /**
960 * Builds an update statement
961 *
962 * @since 9.3
963 *
964 * @param string $table Table name
965 * @param array $params Query parameters ([field name => field value)
966 * @param array $clauses Clauses to use. If not 'WHERE' key specified, will b the WHERE clause (@see DBmysqlIterator capabilities)
967 *
968 * @return string
969 */
970 public function buildUpdate($table, $params, $clauses) {
971 //when no explicit "WHERE", we only have a WHEre clause.
972 if (!isset($clauses['WHERE'])) {
973 $clauses = ['WHERE' => $clauses];
974 } else {
975 $known_clauses = ['WHERE', 'ORDER', 'LIMIT', 'START'];
976 foreach (array_keys($clauses) as $key) {
977 if (!in_array($key, $known_clauses)) {
978 throw new \RuntimeException(
979 str_replace(
980 '%clause',
981 $key,
982 'Trying to use an unknonw clause (%clause) building update query!'
983 )
984 );
985 }
986 }
987 }
988
989 if (!count($clauses['WHERE'])) {
990 throw new \RuntimeException('Cannot run an UPDATE query without WHERE clause!');
991 }
992
993 $query = "UPDATE ". self::quoteName($table) ." SET ";
994
995 foreach ($params as $field => $value) {
996 $query .= self::quoteName($field) . " = ".$this->quoteValue($value).", ";
997 }
998 $query = rtrim($query, ', ');
999
1000 $it = new DBmysqlIterator($this);
1001 $query .= " WHERE " . $it->analyseCrit($clauses['WHERE']);
1002
1003 // ORDER BY
1004 if (isset($clauses['ORDER']) && !empty($clauses['ORDER'])) {
1005 $query .= $it->handleOrderClause($clauses['ORDER']);
1006 }
1007
1008 if (isset($clauses['LIMIT']) && !empty($clauses['LIMIT'])) {
1009 $offset = (isset($clauses['START']) && !empty($clauses['START'])) ? $clauses['START'] : null;
1010 $query .= $it->handleLimits($clauses['LIMIT'], $offset);
1011 }
1012
1013 return $query;
1014 }
1015
1016 /**
1017 * Update a row in the database
1018 *
1019 * @since 9.3
1020 *
1021 * @param string $table Table name
1022 * @param array $params Query parameters ([:field name => field value)
1023 * @param array $where WHERE clause
1024 *
1025 * @return mysqli_result|boolean Query result handler
1026 */
1027 public function update($table, $params, $where) {
1028 $query = $this->buildUpdate($table, $params, $where);
1029 $result = $this->query($query);
1030 return $result;
1031 }
1032
1033 /**
1034 * Update a row in the database or die
1035 * (optionnaly with a message) if it fails
1036 *
1037 * @since 9.3
1038 *
1039 * @param string $table Table name
1040 * @param array $params Query parameters ([:field name => field value)
1041 * @param array $where WHERE clause
1042 * @param string $message Explanation of query (default '')
1043 *
1044 * @return mysqli_result|boolean Query result handler
1045 */
1046 function updateOrDie($table, $params, $where, $message = '') {
1047 $update = $this->buildUpdate($table, $params, $where);
1048 $res = $this->query($update);
1049 if (!$res) {
1050 //TRANS: %1$s is the description, %2$s is the query, %3$s is the error message
1051 $message = sprintf(
1052 __('%1$s - Error during the database query: %2$s - Error is %3$s'),
1053 $message,
1054 $update,
1055 $this->error()
1056 );
1057 if (isCommandLine()) {
1058 throw new \RuntimeException($message);
1059 } else {
1060 echo $message . "\n";
1061 die(1);
1062 }
1063 }
1064 return $res;
1065 }
1066
1067 /**
1068 * Builds a delete statement
1069 *
1070 * @since 9.3
1071 *
1072 * @param string $table Table name
1073 * @param array $params Query parameters ([field name => field value)
1074 * @param array $where WHERE clause (@see DBmysqlIterator capabilities)
1075 *
1076 * @return string
1077 */
1078 public function buildDelete($table, $where) {
1079
1080 if (!count($where)) {
1081 throw new \RuntimeException('Cannot run an DELETE query without WHERE clause!');
1082 }
1083
1084 $query = "DELETE FROM ". self::quoteName($table);
1085
1086 $it = new DBmysqlIterator($this);
1087 $query .= " WHERE " . $it->analyseCrit($where);
1088
1089 return $query;
1090 }
1091
1092 /**
1093 * Delete rows in the database
1094 *
1095 * @since 9.3
1096 *
1097 * @param string $table Table name
1098 * @param array $where WHERE clause
1099 *
1100 * @return mysqli_result|boolean Query result handler
1101 */
1102 public function delete($table, $where) {
1103 $query = $this->buildDelete($table, $where);
1104 $result = $this->query($query);
1105 return $result;
1106 }
1107
1108 /**
1109 * Delete a row in the database and die
1110 * (optionnaly with a message) if it fails
1111 *
1112 * @since 9.3
1113 *
1114 * @param string $table Table name
1115 * @param array $where WHERE clause
1116 * @param string $message Explanation of query (default '')
1117 *
1118 * @return mysqli_result|boolean Query result handler
1119 */
1120 function deleteOrDie($table, $where, $message = '') {
1121 $update = $this->buildDelete($table, $where);
1122 $res = $this->query($update);
1123 if (!$res) {
1124 //TRANS: %1$s is the description, %2$s is the query, %3$s is the error message
1125 $message = sprintf(
1126 __('%1$s - Error during the database query: %2$s - Error is %3$s'),
1127 $message,
1128 $update,
1129 $this->error()
1130 );
1131 if (isCommandLine()) {
1132 throw new \RuntimeException($message);
1133 } else {
1134 echo $message . "\n";
1135 die(1);
1136 }
1137
1138 }
1139 return $res;
1140 }
1141
1142
1143 /**
1144 * Get table schema
1145 *
1146 * @param string $table Table name,
1147 * @param string|null $structure Raw table structure
1148 *
1149 * @return array
1150 */
1151 public function getTableSchema($table, $structure = null) {
1152 if ($structure === null) {
1153 $structure = $this->query("SHOW CREATE TABLE `$table`")->fetch_row();
1154 $structure = $structure[1];
1155 }
1156
1157 //get table index
1158 $index = preg_grep(
1159 "/^\s\s+?KEY/",
1160 array_map(
1161 function($idx) { return rtrim($idx, ','); },
1162 explode("\n", $structure)
1163 )
1164 );
1165 //get table schema, without index, without AUTO_INCREMENT
1166 $structure = preg_replace(
1167 [
1168 "/\s\s+KEY .*/",
1169 "/AUTO_INCREMENT=\d+ /"
1170 ],
1171 "",
1172 $structure
1173 );
1174 $structure = preg_replace('/,(\s)?$/m', '', $structure);
1175 $structure = preg_replace('/ COMMENT \'(.+)\'/', '', $structure);
1176
1177 $structure = str_replace(
1178 [
1179 " COLLATE utf8_unicode_ci",
1180 " CHARACTER SET utf8",
1181 ', ',
1182 ], [
1183 '',
1184 '',
1185 ',',
1186 ],
1187 trim($structure)
1188 );
1189
1190 //do not check engine nor collation
1191 $structure = preg_replace(
1192 '/\) ENGINE.*$/',
1193 '',
1194 $structure
1195 );
1196
1197 //Mariadb 10.2 will return current_timestamp()
1198 //while older retuns CURRENT_TIMESTAMP...
1199 $structure = preg_replace(
1200 '/ CURRENT_TIMESTAMP\(\)/i',
1201 ' CURRENT_TIMESTAMP',
1202 $structure
1203 );
1204
1205 //Mariadb 10.2 allow default values on longblob, text and longtext
1206 preg_match_all(
1207 '/^.+ (longblob|text|longtext) .+$/m',
1208 $structure,
1209 $defaults
1210 );
1211 if (count($defaults[0])) {
1212 foreach ($defaults[0] as $line) {
1213 $structure = str_replace(
1214 $line,
1215 str_replace(' DEFAULT NULL', '', $line),
1216 $structure
1217 );
1218 }
1219 }
1220
1221 $structure = preg_replace("/(DEFAULT) ([-|+]?\d+)(\.\d+)?/", "$1 '$2$3'", $structure);
1222 //$structure = preg_replace("/(DEFAULT) (')?([-|+]?\d+)(\.\d+)(')?/", "$1 '$3'", $structure);
1223 $structure = preg_replace('/(BIGINT)\(\d+\)/i', '$1', $structure);
1224 $structure = preg_replace('/(TINYINT) /i', '$1(4) ', $structure);
1225
1226 return [
1227 'schema' => strtolower($structure),
1228 'index' => $index
1229 ];
1230
1231 }
1232
1233 /**
1234 * Get database raw version
1235 *
1236 * @return string
1237 */
1238 public function getVersion() {
1239 global $DB;
1240 $req = $DB->request('SELECT version()')->next();
1241 $raw = $req['version()'];
1242 return $raw;
1243 }
1244
1245 /**
1246 * Starts a transaction
1247 *
1248 * @return boolean
1249 */
1250 public function beginTransaction() {
1251 return $this->dbh->begin_transaction();
1252 }
1253
1254 /**
1255 * Commits a transaction
1256 *
1257 * @return boolean
1258 */
1259 public function commit() {
1260 return $this->dbh->commit();
1261 }
1262
1263 /**
1264 * Roolbacks a transaction
1265 *
1266 * @return boolean
1267 */
1268 public function rollBack() {
1269 return $this->dbh->rollback();
1270 }
1271}