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