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