Aleksander Machniak
2015-12-03 b6b92c0dddf5247efc12656bbc351aab3cdc31d4
commit | author | age
a35062 1 #!/usr/bin/env php
T 2 <?php
3 /*
4  +-----------------------------------------------------------------------+
30aa4c 5  | bin/cleandb.sh                                                        |
a35062 6  |                                                                       |
e019f2 7  | This file is part of the Roundcube Webmail client                     |
f5e7b3 8  | Copyright (C) 2010, The Roundcube Dev Team                            |
7fe381 9  |                                                                       |
T 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.                     |
a35062 13  |                                                                       |
T 14  | PURPOSE:                                                              |
15  |   Finally remove all db records marked as deleted some time ago       |
16  |                                                                       |
17  +-----------------------------------------------------------------------+
18  | Author: Thomas Bruederli <roundcube@gmail.com>                        |
19  +-----------------------------------------------------------------------+
20 */
21
0ea079 22 define('INSTALL_PATH', realpath(__DIR__ . '/..') . '/' );
6cc3f5 23
A 24 require INSTALL_PATH.'program/include/clisetup.php';
a35062 25
T 26 // mapping for table name => primary key
27 $primary_keys = array(
60ff01 28     'contacts' => "contact_id",
A 29     'contactgroups' => "contactgroup_id",
a35062 30 );
T 31
32 // connect to DB
dcc446 33 $RCMAIL = rcube::get_instance();
a35062 34 $db = $RCMAIL->get_dbh();
d36646 35 $db->db_connect('w');
a35062 36
f23ef1 37 if (!$db->is_connected() || $db->is_error()) {
AM 38     rcube::raise_error("No DB connection", false, true);
39 }
a35062 40
5ba5fd 41 if (!empty($_SERVER['argv'][1]))
60ff01 42     $days = intval($_SERVER['argv'][1]);
5ba5fd 43 else
60ff01 44     $days = 7;
5ba5fd 45
a35062 46 // remove all deleted records older than two days
5ba5fd 47 $threshold = date('Y-m-d 00:00:00', time() - $days * 86400);
a35062 48
T 49 foreach (array('contacts','contactgroups','identities') as $table) {
50
34a090 51     $sqltable = $db->table_name($table, true);
a35062 52
60ff01 53     // also delete linked records
A 54     // could be skipped for databases which respect foreign key constraints
55     if ($db->db_provider == 'sqlite'
56         && ($table == 'contacts' || $table == 'contactgroups')
57     ) {
58         $pk = $primary_keys[$table];
315a0b 59         $memberstable = $db->table_name('contactgroupmembers');
a35062 60
60ff01 61         $db->query(
34a090 62             "DELETE FROM " . $db->quote_identifier($memberstable).
AM 63             " WHERE `$pk` IN (".
64                 "SELECT `$pk` FROM $sqltable".
65                 " WHERE `del` = 1 AND `changed` < ?".
60ff01 66             ")",
A 67             $threshold);
a35062 68
60ff01 69         echo $db->affected_rows() . " records deleted from '$memberstable'\n";
a35062 70     }
T 71
60ff01 72     // delete outdated records
34a090 73     $db->query("DELETE FROM $sqltable WHERE `del` = 1 AND `changed` < ?", $threshold);
60ff01 74
A 75     echo $db->affected_rows() . " records deleted from '$table'\n";
a35062 76 }
T 77
78 ?>