|
0
|
1 <?php
|
|
|
2
|
|
|
3 /**
|
|
|
4 +-----------------------------------------------------------------------+
|
|
|
5 | program/include/rcmail_utils.php |
|
|
|
6 | |
|
|
|
7 | This file is part of the Roundcube PHP suite |
|
|
|
8 | Copyright (C) 2005-2015 The Roundcube Dev Team |
|
|
|
9 | |
|
|
|
10 | Licensed under the GNU General Public License version 3 or |
|
|
|
11 | any later version with exceptions for skins & plugins. |
|
|
|
12 | See the README file for a full license statement. |
|
|
|
13 | |
|
|
|
14 | CONTENTS: |
|
|
|
15 | Roundcube utilities |
|
|
|
16 | |
|
|
|
17 +-----------------------------------------------------------------------+
|
|
|
18 | Author: Thomas Bruederli <roundcube@gmail.com> |
|
|
|
19 | Author: Aleksander Machniak <alec@alec.pl> |
|
|
|
20 +-----------------------------------------------------------------------+
|
|
|
21 */
|
|
|
22
|
|
|
23 /**
|
|
|
24 * Roundcube utilities
|
|
|
25 *
|
|
|
26 * @package Webmail
|
|
|
27 * @subpackage Utils
|
|
|
28 */
|
|
|
29 class rcmail_utils
|
|
|
30 {
|
|
|
31 public static $db;
|
|
|
32
|
|
|
33 /**
|
|
|
34 * Initialize database object and connect
|
|
|
35 *
|
|
|
36 * @return rcube_db Database instance
|
|
|
37 */
|
|
|
38 public static function db()
|
|
|
39 {
|
|
|
40 if (self::$db === null) {
|
|
|
41 $rc = rcube::get_instance();
|
|
|
42 $db = rcube_db::factory($rc->config->get('db_dsnw'));
|
|
|
43
|
|
|
44 $db->set_debug((bool)$rc->config->get('sql_debug'));
|
|
|
45
|
|
|
46 // Connect to database
|
|
|
47 $db->db_connect('w');
|
|
|
48
|
|
|
49 if (!$db->is_connected()) {
|
|
|
50 rcube::raise_error("Error connecting to database: " . $db->is_error(), false, true);
|
|
|
51 }
|
|
|
52
|
|
|
53 self::$db = $db;
|
|
|
54 }
|
|
|
55
|
|
|
56 return self::$db;
|
|
|
57 }
|
|
|
58
|
|
|
59 /**
|
|
|
60 * Initialize database schema
|
|
|
61 *
|
|
|
62 * @param string $dir Directory with sql files
|
|
|
63 */
|
|
|
64 public static function db_init($dir)
|
|
|
65 {
|
|
|
66 $db = self::db();
|
|
|
67
|
|
|
68 $file = $dir . '/' . $db->db_provider . '.initial.sql';
|
|
|
69 if (!file_exists($file)) {
|
|
|
70 rcube::raise_error("DDL file $file not found", false, true);
|
|
|
71 }
|
|
|
72
|
|
|
73 echo "Creating database schema... ";
|
|
|
74
|
|
|
75 if ($sql = file_get_contents($file)) {
|
|
|
76 if (!$db->exec_script($sql)) {
|
|
|
77 $error = $db->is_error();
|
|
|
78 }
|
|
|
79 }
|
|
|
80 else {
|
|
|
81 $error = "Unable to read file $file or it is empty";
|
|
|
82 }
|
|
|
83
|
|
|
84 if ($error) {
|
|
|
85 echo "[FAILED]\n";
|
|
|
86 rcube::raise_error($error, false, true);
|
|
|
87 }
|
|
|
88 else {
|
|
|
89 echo "[OK]\n";
|
|
|
90 }
|
|
|
91 }
|
|
|
92
|
|
|
93 /**
|
|
|
94 * Update database schema
|
|
|
95 *
|
|
|
96 * @param string $dir Directory with sql files
|
|
|
97 * @param string $package Component name
|
|
|
98 * @param string $ver Optional current version number
|
|
|
99 * @param array $opts Parameters (errors, quiet)
|
|
|
100 *
|
|
|
101 * @return True on success, False on failure
|
|
|
102 */
|
|
|
103 public static function db_update($dir, $package, $ver = null, $opts = array())
|
|
|
104 {
|
|
|
105 // Check if directory exists
|
|
|
106 if (!file_exists($dir)) {
|
|
|
107 if ($opts['errors']) {
|
|
|
108 rcube::raise_error("Specified database schema directory doesn't exist.", false, true);
|
|
|
109 }
|
|
|
110 return false;
|
|
|
111 }
|
|
|
112
|
|
|
113 $db = self::db();
|
|
|
114
|
|
|
115 // Read DB schema version from database (if 'system' table exists)
|
|
|
116 if (in_array($db->table_name('system'), (array)$db->list_tables())) {
|
|
|
117 $db->query("SELECT `value`"
|
|
|
118 . " FROM " . $db->table_name('system', true)
|
|
|
119 . " WHERE `name` = ?",
|
|
|
120 $package . '-version');
|
|
|
121
|
|
|
122 $row = $db->fetch_array();
|
|
|
123 $version = preg_replace('/[^0-9]/', '', $row[0]);
|
|
|
124 }
|
|
|
125
|
|
|
126 // DB version not found, but release version is specified
|
|
|
127 if (!$version && $ver) {
|
|
|
128 // Map old release version string to DB schema version
|
|
|
129 // Note: This is for backward compat. only, do not need to be updated
|
|
|
130 $map = array(
|
|
|
131 '0.1-stable' => 1,
|
|
|
132 '0.1.1' => 2008030300,
|
|
|
133 '0.2-alpha' => 2008040500,
|
|
|
134 '0.2-beta' => 2008060900,
|
|
|
135 '0.2-stable' => 2008092100,
|
|
|
136 '0.2.1' => 2008092100,
|
|
|
137 '0.2.2' => 2008092100,
|
|
|
138 '0.3-stable' => 2008092100,
|
|
|
139 '0.3.1' => 2009090400,
|
|
|
140 '0.4-beta' => 2009103100,
|
|
|
141 '0.4' => 2010042300,
|
|
|
142 '0.4.1' => 2010042300,
|
|
|
143 '0.4.2' => 2010042300,
|
|
|
144 '0.5-beta' => 2010100600,
|
|
|
145 '0.5' => 2010100600,
|
|
|
146 '0.5.1' => 2010100600,
|
|
|
147 '0.5.2' => 2010100600,
|
|
|
148 '0.5.3' => 2010100600,
|
|
|
149 '0.5.4' => 2010100600,
|
|
|
150 '0.6-beta' => 2011011200,
|
|
|
151 '0.6' => 2011011200,
|
|
|
152 '0.7-beta' => 2011092800,
|
|
|
153 '0.7' => 2011111600,
|
|
|
154 '0.7.1' => 2011111600,
|
|
|
155 '0.7.2' => 2011111600,
|
|
|
156 '0.7.3' => 2011111600,
|
|
|
157 '0.7.4' => 2011111600,
|
|
|
158 '0.8-beta' => 2011121400,
|
|
|
159 '0.8-rc' => 2011121400,
|
|
|
160 '0.8.0' => 2011121400,
|
|
|
161 '0.8.1' => 2011121400,
|
|
|
162 '0.8.2' => 2011121400,
|
|
|
163 '0.8.3' => 2011121400,
|
|
|
164 '0.8.4' => 2011121400,
|
|
|
165 '0.8.5' => 2011121400,
|
|
|
166 '0.8.6' => 2011121400,
|
|
|
167 '0.9-beta' => 2012080700,
|
|
|
168 );
|
|
|
169
|
|
|
170 $version = $map[$ver];
|
|
|
171 }
|
|
|
172
|
|
|
173 // Assume last version before the 'system' table was added
|
|
|
174 if (empty($version)) {
|
|
|
175 $version = 2012080700;
|
|
|
176 }
|
|
|
177
|
|
|
178 $dir .= '/' . $db->db_provider;
|
|
|
179 if (!file_exists($dir)) {
|
|
|
180 if ($opts['errors']) {
|
|
|
181 rcube::raise_error("DDL Upgrade files for " . $db->db_provider . " driver not found.", false, true);
|
|
|
182 }
|
|
|
183 return false;
|
|
|
184 }
|
|
|
185
|
|
|
186 $dh = opendir($dir);
|
|
|
187 $result = array();
|
|
|
188
|
|
|
189 while ($file = readdir($dh)) {
|
|
|
190 if (preg_match('/^([0-9]+)\.sql$/', $file, $m) && $m[1] > $version) {
|
|
|
191 $result[] = $m[1];
|
|
|
192 }
|
|
|
193 }
|
|
|
194 sort($result, SORT_NUMERIC);
|
|
|
195
|
|
|
196 foreach ($result as $v) {
|
|
|
197 if (!$opts['quiet']) {
|
|
|
198 echo "Updating database schema ($v)... ";
|
|
|
199 }
|
|
|
200
|
|
|
201 // Ignore errors here to print the error only once
|
|
|
202 $db->set_option('ignore_errors', true);
|
|
|
203 $error = self::db_update_schema($package, $v, "$dir/$v.sql");
|
|
|
204 $db->set_option('ignore_errors', false);
|
|
|
205
|
|
|
206 if ($error) {
|
|
|
207 if (!$opts['quiet']) {
|
|
|
208 echo "[FAILED]\n";
|
|
|
209 }
|
|
|
210 if ($opts['errors']) {
|
|
|
211 rcube::raise_error("Error in DDL upgrade $v: $error", false, true);
|
|
|
212 }
|
|
|
213 return false;
|
|
|
214 }
|
|
|
215 else if (!$opts['quiet']) {
|
|
|
216 echo "[OK]\n";
|
|
|
217 }
|
|
|
218 }
|
|
|
219
|
|
|
220 return true;
|
|
|
221 }
|
|
|
222
|
|
|
223 /**
|
|
|
224 * Run database update from a single sql file
|
|
|
225 */
|
|
|
226 protected static function db_update_schema($package, $version, $file)
|
|
|
227 {
|
|
|
228 $db = self::db();
|
|
|
229
|
|
|
230 // read DDL file
|
|
|
231 if ($sql = file_get_contents($file)) {
|
|
|
232 if (!$db->exec_script($sql)) {
|
|
|
233 return $db->is_error();
|
|
|
234 }
|
|
|
235 }
|
|
|
236
|
|
|
237 // escape if 'system' table does not exist
|
|
|
238 if ($version < 2013011000) {
|
|
|
239 return;
|
|
|
240 }
|
|
|
241
|
|
|
242 $system_table = $db->table_name('system', true);
|
|
|
243
|
|
|
244 $db->query("UPDATE " . $system_table
|
|
|
245 . " SET `value` = ?"
|
|
|
246 . " WHERE `name` = ?",
|
|
|
247 $version, $package . '-version');
|
|
|
248
|
|
|
249 if (!$db->is_error() && !$db->affected_rows()) {
|
|
|
250 $db->query("INSERT INTO " . $system_table
|
|
|
251 ." (`name`, `value`) VALUES (?, ?)",
|
|
|
252 $package . '-version', $version);
|
|
|
253 }
|
|
|
254
|
|
|
255 return $db->is_error();
|
|
|
256 }
|
|
|
257
|
|
|
258 /**
|
|
|
259 * Removes all deleted records older than X days
|
|
|
260 *
|
|
|
261 * @param int $days Number of days
|
|
|
262 */
|
|
|
263 public static function db_clean($days)
|
|
|
264 {
|
|
|
265 // mapping for table name => primary key
|
|
|
266 $primary_keys = array(
|
|
|
267 'contacts' => 'contact_id',
|
|
|
268 'contactgroups' => 'contactgroup_id',
|
|
|
269 );
|
|
|
270
|
|
|
271 $db = self::db();
|
|
|
272
|
|
|
273 $threshold = date('Y-m-d 00:00:00', time() - $days * 86400);
|
|
|
274
|
|
|
275 foreach (array('contacts','contactgroups','identities') as $table) {
|
|
|
276 $sqltable = $db->table_name($table, true);
|
|
|
277
|
|
|
278 // also delete linked records
|
|
|
279 // could be skipped for databases which respect foreign key constraints
|
|
|
280 if ($db->db_provider == 'sqlite' && ($table == 'contacts' || $table == 'contactgroups')) {
|
|
|
281 $pk = $primary_keys[$table];
|
|
|
282 $memberstable = $db->table_name('contactgroupmembers');
|
|
|
283
|
|
|
284 $db->query(
|
|
|
285 "DELETE FROM " . $db->quote_identifier($memberstable)
|
|
|
286 . " WHERE `$pk` IN ("
|
|
|
287 . "SELECT `$pk` FROM $sqltable"
|
|
|
288 . " WHERE `del` = 1 AND `changed` < ?"
|
|
|
289 . ")",
|
|
|
290 $threshold);
|
|
|
291
|
|
|
292 echo $db->affected_rows() . " records deleted from '$memberstable'\n";
|
|
|
293 }
|
|
|
294
|
|
|
295 // delete outdated records
|
|
|
296 $db->query("DELETE FROM $sqltable WHERE `del` = 1 AND `changed` < ?", $threshold);
|
|
|
297
|
|
|
298 echo $db->affected_rows() . " records deleted from '$table'\n";
|
|
|
299 }
|
|
|
300 }
|
|
|
301
|
|
|
302 /**
|
|
|
303 * Reindex contacts
|
|
|
304 */
|
|
|
305 public static function indexcontacts()
|
|
|
306 {
|
|
|
307 $db = self::db();
|
|
|
308
|
|
|
309 // iterate over all users
|
|
|
310 $sql_result = $db->query("SELECT `user_id` FROM " . $db->table_name('users', true) . " ORDER BY `user_id`");
|
|
|
311 while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) {
|
|
|
312 echo "Indexing contacts for user " . $sql_arr['user_id'] . "...\n";
|
|
|
313
|
|
|
314 $contacts = new rcube_contacts($db, $sql_arr['user_id']);
|
|
|
315 $contacts->set_pagesize(9999);
|
|
|
316
|
|
|
317 $result = $contacts->list_records();
|
|
|
318 while ($result->count && ($row = $result->next())) {
|
|
|
319 unset($row['words']);
|
|
|
320 $contacts->update($row['ID'], $row);
|
|
|
321 }
|
|
|
322 }
|
|
|
323
|
|
|
324 echo "done.\n";
|
|
|
325 }
|
|
|
326
|
|
|
327 /**
|
|
|
328 * Modify user preferences
|
|
|
329 *
|
|
|
330 * @param string $name Option name
|
|
|
331 * @param string $value Option value
|
|
|
332 * @param int $userid Optional user identifier
|
|
|
333 * @param string $type Optional value type (bool, int, string)
|
|
|
334 */
|
|
|
335 public static function mod_pref($name, $value, $userid = null, $type = 'string')
|
|
|
336 {
|
|
|
337 $db = self::db();
|
|
|
338
|
|
|
339 if ($userid) {
|
|
|
340 $query = '`user_id` = ' . intval($userid);
|
|
|
341 }
|
|
|
342 else {
|
|
|
343 $query = '1=1';
|
|
|
344 }
|
|
|
345
|
|
|
346 $type = strtolower($type);
|
|
|
347
|
|
|
348 if ($type == 'bool' || $type == 'boolean') {
|
|
|
349 $value = rcube_utils::get_boolean($value);
|
|
|
350 }
|
|
|
351 else if ($type == 'int' || $type == 'integer') {
|
|
|
352 $value = (int) $value;
|
|
|
353 }
|
|
|
354
|
|
|
355 // iterate over all users
|
|
|
356 $sql_result = $db->query("SELECT * FROM " . $db->table_name('users', true) . " WHERE $query");
|
|
|
357
|
|
|
358 while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) {
|
|
|
359 echo "Updating prefs for user " . $sql_arr['user_id'] . "...";
|
|
|
360
|
|
|
361 $user = new rcube_user($sql_arr['user_id'], $sql_arr);
|
|
|
362 $prefs = $old_prefs = $user->get_prefs();
|
|
|
363
|
|
|
364 $prefs[$name] = $value;
|
|
|
365
|
|
|
366 if ($prefs != $old_prefs) {
|
|
|
367 $user->save_prefs($prefs, true);
|
|
|
368 echo "saved.\n";
|
|
|
369 }
|
|
|
370 else {
|
|
|
371 echo "nothing changed.\n";
|
|
|
372 }
|
|
|
373 }
|
|
|
374 }
|
|
|
375 }
|
