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