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