Thomas Bruederli
2016-04-17 cde7a9eb74b6fd6315885c30c0763e0ee5332499
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
22 define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
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
33 $RCMAIL = rcmail::get_instance();
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
6b2b2e 51     $sqltable = $db->table_name($table);
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];
59         $memberstable = get_table_name('contactgroupmembers');
a35062 60
60ff01 61         $db->query(
A 62             "DELETE FROM $memberstable".
63             " WHERE $pk IN (".
64                 "SELECT $pk FROM $sqltable".
65                 " WHERE del=1 AND changed < ?".
66             ")",
67             $threshold);
a35062 68
60ff01 69         echo $db->affected_rows() . " records deleted from '$memberstable'\n";
a35062 70     }
T 71
60ff01 72     // delete outdated records
A 73     $db->query("DELETE FROM $sqltable WHERE del=1 AND changed < ?", $threshold);
74
75     echo $db->affected_rows() . " records deleted from '$table'\n";
a35062 76 }
T 77
78 ?>