|
0
|
1 <?php
|
|
|
2
|
|
|
3 /**
|
|
|
4 +-----------------------------------------------------------------------+
|
|
|
5 | This file is part of the Roundcube Webmail client |
|
|
|
6 | Copyright (C) 2005-2017, The Roundcube Dev Team |
|
|
|
7 | |
|
|
|
8 | Licensed under the GNU General Public License version 3 or |
|
|
|
9 | any later version with exceptions for skins & plugins. |
|
|
|
10 | See the README file for a full license statement. |
|
|
|
11 | |
|
|
|
12 | PURPOSE: |
|
|
|
13 | Database wrapper class that implements PHP PDO functions |
|
|
|
14 | for PostgreSQL database |
|
|
|
15 +-----------------------------------------------------------------------+
|
|
|
16 | Author: Aleksander Machniak <alec@alec.pl> |
|
|
|
17 +-----------------------------------------------------------------------+
|
|
|
18 */
|
|
|
19
|
|
|
20 /**
|
|
|
21 * Database independent query interface
|
|
|
22 * This is a wrapper for the PHP PDO
|
|
|
23 *
|
|
|
24 * @package Framework
|
|
|
25 * @subpackage Database
|
|
|
26 */
|
|
|
27 class rcube_db_pgsql extends rcube_db
|
|
|
28 {
|
|
|
29 public $db_provider = 'postgres';
|
|
|
30
|
|
|
31 /**
|
|
|
32 * Object constructor
|
|
|
33 *
|
|
|
34 * @param string $db_dsnw DSN for read/write operations
|
|
|
35 * @param string $db_dsnr Optional DSN for read only operations
|
|
|
36 * @param bool $pconn Enables persistent connections
|
|
|
37 */
|
|
|
38 public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
|
|
|
39 {
|
|
|
40 parent::__construct($db_dsnw, $db_dsnr, $pconn);
|
|
|
41
|
|
|
42 // use date/time input format with timezone spec.
|
|
|
43 $this->options['datetime_format'] = 'c';
|
|
|
44 }
|
|
|
45
|
|
|
46 /**
|
|
|
47 * Driver-specific configuration of database connection
|
|
|
48 *
|
|
|
49 * @param array $dsn DSN for DB connections
|
|
|
50 * @param PDO $dbh Connection handler
|
|
|
51 */
|
|
|
52 protected function conn_configure($dsn, $dbh)
|
|
|
53 {
|
|
|
54 $dbh->query("SET NAMES 'utf8'");
|
|
|
55 $dbh->query("SET DATESTYLE TO ISO");
|
|
|
56 }
|
|
|
57
|
|
|
58 /**
|
|
|
59 * Get last inserted record ID
|
|
|
60 *
|
|
|
61 * @param string $table Table name (to find the incremented sequence)
|
|
|
62 *
|
|
|
63 * @return mixed ID or false on failure
|
|
|
64 */
|
|
|
65 public function insert_id($table = null)
|
|
|
66 {
|
|
|
67 if (!$this->db_connected || $this->db_mode == 'r') {
|
|
|
68 return false;
|
|
|
69 }
|
|
|
70
|
|
|
71 if ($table) {
|
|
|
72 $table = $this->sequence_name($table);
|
|
|
73 }
|
|
|
74
|
|
|
75 $id = $this->dbh->lastInsertId($table);
|
|
|
76
|
|
|
77 return $id;
|
|
|
78 }
|
|
|
79
|
|
|
80 /**
|
|
|
81 * Return correct name for a specific database sequence
|
|
|
82 *
|
|
|
83 * @param string $table Table name
|
|
|
84 *
|
|
|
85 * @return string Translated sequence name
|
|
|
86 */
|
|
|
87 protected function sequence_name($table)
|
|
|
88 {
|
|
|
89 // Note: we support only one sequence per table
|
|
|
90 // Note: The sequence name must be <table_name>_seq
|
|
|
91 $sequence = $table . '_seq';
|
|
|
92
|
|
|
93 // modify sequence name if prefix is configured
|
|
|
94 if ($prefix = $this->options['table_prefix']) {
|
|
|
95 return $prefix . $sequence;
|
|
|
96 }
|
|
|
97
|
|
|
98 return $sequence;
|
|
|
99 }
|
|
|
100
|
|
|
101 /**
|
|
|
102 * Return SQL statement to convert a field value into a unix timestamp
|
|
|
103 *
|
|
|
104 * @param string $field Field name
|
|
|
105 *
|
|
|
106 * @return string SQL statement to use in query
|
|
|
107 * @deprecated
|
|
|
108 */
|
|
|
109 public function unixtimestamp($field)
|
|
|
110 {
|
|
|
111 return "EXTRACT (EPOCH FROM $field)";
|
|
|
112 }
|
|
|
113
|
|
|
114 /**
|
|
|
115 * Return SQL function for current time and date
|
|
|
116 *
|
|
|
117 * @param int $interval Optional interval (in seconds) to add/subtract
|
|
|
118 *
|
|
|
119 * @return string SQL function to use in query
|
|
|
120 */
|
|
|
121 public function now($interval = 0)
|
|
|
122 {
|
|
|
123 if ($interval) {
|
|
|
124 $add = ' ' . ($interval > 0 ? '+' : '-') . " interval '";
|
|
|
125 $add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
|
|
|
126 $add .= " seconds'";
|
|
|
127 }
|
|
|
128
|
|
|
129 return "now()" . $add;
|
|
|
130 }
|
|
|
131
|
|
|
132 /**
|
|
|
133 * Return SQL statement for case insensitive LIKE
|
|
|
134 *
|
|
|
135 * @param string $column Field name
|
|
|
136 * @param string $value Search value
|
|
|
137 *
|
|
|
138 * @return string SQL statement to use in query
|
|
|
139 */
|
|
|
140 public function ilike($column, $value)
|
|
|
141 {
|
|
|
142 return $this->quote_identifier($column) . ' ILIKE ' . $this->quote($value);
|
|
|
143 }
|
|
|
144
|
|
|
145 /**
|
|
|
146 * Get database runtime variables
|
|
|
147 *
|
|
|
148 * @param string $varname Variable name
|
|
|
149 * @param mixed $default Default value if variable is not set
|
|
|
150 *
|
|
|
151 * @return mixed Variable value or default
|
|
|
152 */
|
|
|
153 public function get_variable($varname, $default = null)
|
|
|
154 {
|
|
|
155 // There's a known case when max_allowed_packet is queried
|
|
|
156 // PostgreSQL doesn't have such limit, return immediately
|
|
|
157 if ($varname == 'max_allowed_packet') {
|
|
|
158 return rcube::get_instance()->config->get('db_' . $varname, $default);
|
|
|
159 }
|
|
|
160
|
|
|
161 $this->variables[$varname] = rcube::get_instance()->config->get('db_' . $varname);
|
|
|
162
|
|
|
163 if (!isset($this->variables)) {
|
|
|
164 $this->variables = array();
|
|
|
165
|
|
|
166 $result = $this->query('SHOW ALL');
|
|
|
167
|
|
|
168 while ($row = $this->fetch_array($result)) {
|
|
|
169 $this->variables[$row[0]] = $row[1];
|
|
|
170 }
|
|
|
171 }
|
|
|
172
|
|
|
173 return isset($this->variables[$varname]) ? $this->variables[$varname] : $default;
|
|
|
174 }
|
|
|
175
|
|
|
176 /**
|
|
|
177 * Returns list of tables in a database
|
|
|
178 *
|
|
|
179 * @return array List of all tables of the current database
|
|
|
180 */
|
|
|
181 public function list_tables()
|
|
|
182 {
|
|
|
183 // get tables if not cached
|
|
|
184 if ($this->tables === null) {
|
|
|
185 $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
|
|
|
186 . " WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema')"
|
|
|
187 . " ORDER BY TABLE_NAME");
|
|
|
188
|
|
|
189 $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array();
|
|
|
190 }
|
|
|
191
|
|
|
192 return $this->tables;
|
|
|
193 }
|
|
|
194
|
|
|
195 /**
|
|
|
196 * Returns PDO DSN string from DSN array
|
|
|
197 *
|
|
|
198 * @param array $dsn DSN parameters
|
|
|
199 *
|
|
|
200 * @return string DSN string
|
|
|
201 */
|
|
|
202 protected function dsn_string($dsn)
|
|
|
203 {
|
|
|
204 $params = array();
|
|
|
205 $result = 'pgsql:';
|
|
|
206
|
|
|
207 if ($dsn['hostspec']) {
|
|
|
208 $params[] = 'host=' . $dsn['hostspec'];
|
|
|
209 }
|
|
|
210 else if ($dsn['socket']) {
|
|
|
211 $params[] = 'host=' . $dsn['socket'];
|
|
|
212 }
|
|
|
213
|
|
|
214 if ($dsn['port']) {
|
|
|
215 $params[] = 'port=' . $dsn['port'];
|
|
|
216 }
|
|
|
217
|
|
|
218 if ($dsn['database']) {
|
|
|
219 $params[] = 'dbname=' . $dsn['database'];
|
|
|
220 }
|
|
|
221
|
|
|
222 if (!empty($params)) {
|
|
|
223 $result .= implode(';', $params);
|
|
|
224 }
|
|
|
225
|
|
|
226 return $result;
|
|
|
227 }
|
|
|
228
|
|
|
229 /**
|
|
|
230 * Parse SQL file and fix table names according to table prefix
|
|
|
231 */
|
|
|
232 protected function fix_table_names($sql)
|
|
|
233 {
|
|
|
234 if (!$this->options['table_prefix']) {
|
|
|
235 return $sql;
|
|
|
236 }
|
|
|
237
|
|
|
238 $sql = parent::fix_table_names($sql);
|
|
|
239
|
|
|
240 // replace sequence names, and other postgres-specific commands
|
|
|
241 $sql = preg_replace_callback(
|
|
|
242 '/((SEQUENCE |RENAME TO |nextval\()["\']*)([^"\' \r\n]+)/',
|
|
|
243 array($this, 'fix_table_names_callback'),
|
|
|
244 $sql
|
|
|
245 );
|
|
|
246
|
|
|
247 return $sql;
|
|
|
248 }
|
|
|
249 }
|
