1<?php 2 3/** 4 +-----------------------------------------------------------------------+ 5 | This file is part of the Roundcube Webmail client | 6 | | 7 | Copyright (C) The Roundcube Dev Team | 8 | | 9 | Licensed under the GNU General Public License version 3 or | 10 | any later version with exceptions for skins & plugins. | 11 | See the README file for a full license statement. | 12 | | 13 | PURPOSE: | 14 | Database wrapper class that implements PHP PDO functions | 15 | for MySQL database | 16 +-----------------------------------------------------------------------+ 17 | Author: Aleksander Machniak <alec@alec.pl> | 18 +-----------------------------------------------------------------------+ 19*/ 20 21/** 22 * Database independent query interface 23 * 24 * This is a wrapper for the PHP PDO 25 * 26 * @package Framework 27 * @subpackage Database 28 */ 29class rcube_db_mysql extends rcube_db 30{ 31 public $db_provider = 'mysql'; 32 33 /** 34 * {@inheritdoc} 35 */ 36 public function __construct($db_dsnw, $db_dsnr = '', $pconn = false) 37 { 38 parent::__construct($db_dsnw, $db_dsnr, $pconn); 39 40 // SQL identifiers quoting 41 $this->options['identifier_start'] = '`'; 42 $this->options['identifier_end'] = '`'; 43 } 44 45 /** 46 * Abstract SQL statement for value concatenation 47 * 48 * @return string SQL statement to be used in query 49 */ 50 public function concat(/* col1, col2, ... */) 51 { 52 $args = func_get_args(); 53 54 if (!empty($args) && is_array($args[0])) { 55 $args = $args[0]; 56 } 57 58 return 'CONCAT(' . implode(', ', $args) . ')'; 59 } 60 61 /** 62 * Returns PDO DSN string from DSN array 63 * 64 * @param array $dsn DSN parameters 65 * 66 * @return string Connection string 67 */ 68 protected function dsn_string($dsn) 69 { 70 $params = []; 71 $result = 'mysql:'; 72 73 if (isset($dsn['database'])) { 74 $params[] = 'dbname=' . $dsn['database']; 75 } 76 77 if (isset($dsn['hostspec'])) { 78 $params[] = 'host=' . $dsn['hostspec']; 79 } 80 81 if (isset($dsn['port'])) { 82 $params[] = 'port=' . $dsn['port']; 83 } 84 85 if (isset($dsn['socket'])) { 86 $params[] = 'unix_socket=' . $dsn['socket']; 87 } 88 89 $params[] = 'charset=' . (!empty($dsn['charset']) ? $dsn['charset'] : 'utf8mb4'); 90 91 if (!empty($params)) { 92 $result .= implode(';', $params); 93 } 94 95 return $result; 96 } 97 98 /** 99 * Returns driver-specific connection options 100 * 101 * @param array $dsn DSN parameters 102 * 103 * @return array Connection options 104 */ 105 protected function dsn_options($dsn) 106 { 107 $result = parent::dsn_options($dsn); 108 109 if (!empty($dsn['key'])) { 110 $result[PDO::MYSQL_ATTR_SSL_KEY] = $dsn['key']; 111 } 112 113 if (!empty($dsn['cipher'])) { 114 $result[PDO::MYSQL_ATTR_SSL_CIPHER] = $dsn['cipher']; 115 } 116 117 if (!empty($dsn['cert'])) { 118 $result[PDO::MYSQL_ATTR_SSL_CERT] = $dsn['cert']; 119 } 120 121 if (!empty($dsn['capath'])) { 122 $result[PDO::MYSQL_ATTR_SSL_CAPATH] = $dsn['capath']; 123 } 124 125 if (!empty($dsn['ca'])) { 126 $result[PDO::MYSQL_ATTR_SSL_CA] = $dsn['ca']; 127 } 128 129 if (isset($dsn['verify_server_cert'])) { 130 $result[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = rcube_utils::get_boolean($dsn['verify_server_cert']); 131 } 132 133 // Always return matching (not affected only) rows count 134 $result[PDO::MYSQL_ATTR_FOUND_ROWS] = true; 135 136 // Enable AUTOCOMMIT mode (#1488902) 137 $result[PDO::ATTR_AUTOCOMMIT] = true; 138 139 return $result; 140 } 141 142 /** 143 * Returns list of tables in a database 144 * 145 * @return array List of all tables of the current database 146 */ 147 public function list_tables() 148 { 149 // get tables if not cached 150 if ($this->tables === null) { 151 $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" 152 . " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'" 153 . " ORDER BY TABLE_NAME", $this->db_dsnw_array['database']); 154 155 $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : []; 156 } 157 158 return $this->tables; 159 } 160 161 /** 162 * Returns list of columns in database table 163 * 164 * @param string $table Table name 165 * 166 * @return array List of table cols 167 */ 168 public function list_cols($table) 169 { 170 $q = $this->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" 171 . " WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?", 172 $this->db_dsnw_array['database'], $table); 173 174 if ($q) { 175 return $q->fetchAll(PDO::FETCH_COLUMN, 0); 176 } 177 178 return []; 179 } 180 181 /** 182 * Get database runtime variables 183 * 184 * @param string $varname Variable name 185 * @param mixed $default Default value if variable is not set 186 * 187 * @return mixed Variable value or default 188 */ 189 public function get_variable($varname, $default = null) 190 { 191 if (!isset($this->variables)) { 192 $this->variables = []; 193 } 194 195 if (array_key_exists($varname, $this->variables)) { 196 return $this->variables[$varname]; 197 } 198 199 // configured value has higher prio 200 $conf_value = rcube::get_instance()->config->get('db_' . $varname); 201 if ($conf_value !== null) { 202 return $this->variables[$varname] = $conf_value; 203 } 204 205 $result = $this->query('SHOW VARIABLES LIKE ?', $varname); 206 207 while ($row = $this->fetch_array($result)) { 208 $this->variables[$row[0]] = $row[1]; 209 } 210 211 // not found, use default 212 if (!isset($this->variables[$varname])) { 213 $this->variables[$varname] = $default; 214 } 215 216 return $this->variables[$varname]; 217 } 218 219 /** 220 * INSERT ... ON DUPLICATE KEY UPDATE (or equivalent). 221 * When not supported by the engine we do UPDATE and INSERT. 222 * 223 * @param string $table Table name (should be already passed via table_name() with quoting) 224 * @param array $keys Hash array (column => value) of the unique constraint 225 * @param array $columns List of columns to update 226 * @param array $values List of values to update (number of elements 227 * should be the same as in $columns) 228 * 229 * @return PDOStatement|bool Query handle or False on error 230 * @todo Multi-insert support 231 */ 232 public function insert_or_update($table, $keys, $columns, $values) 233 { 234 $columns = array_map(function($i) { return "`$i`"; }, $columns); 235 $cols = implode(', ', array_map(function($i) { return "`$i`"; }, array_keys($keys))); 236 $cols .= ', ' . implode(', ', $columns); 237 $vals = implode(', ', array_map(function($i) { return $this->quote($i); }, $keys)); 238 $vals .= ', ' . rtrim(str_repeat('?, ', count($columns)), ', '); 239 $update = implode(', ', array_map(function($i) { return "$i = VALUES($i)"; }, $columns)); 240 241 return $this->query("INSERT INTO $table ($cols) VALUES ($vals)" 242 . " ON DUPLICATE KEY UPDATE $update", $values); 243 } 244} 245