bin/cleandb.sh

   1 #!/usr/bin/env php
   2 <?php
   3 /*
   4
   5  +-----------------------------------------------------------------------+
   6  | bin/cleandb.sh                                                        |
   7  |                                                                       |
   8  | This file is part of the Roundcube Webmail client                     |
   9  | Copyright (C) 2010, The Roundcube Dev Team                            |
  10  | Licensed under the GNU GPL                                            |
  11  |                                                                       |
  12  | PURPOSE:                                                              |
  13  |   Finally remove all db records marked as deleted some time ago       |
  14  |                                                                       |
  15  +-----------------------------------------------------------------------+
  16  | Author: Thomas Bruederli <roundcube@gmail.com>                        |
  17  +-----------------------------------------------------------------------+
  18
  19  $Id: cleandb.sh 4677 2011-04-20 13:10:45Z alec $
  20
  21 */
  22
  23 define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
  24
  25 require INSTALL_PATH.'program/include/clisetup.php';
  26
  27 // mapping for table name => primary key
  28 $primary_keys = array(
  29     'contacts' => "contact_id",
  30     'contactgroups' => "contactgroup_id",
  31 );
  32
  33 // connect to DB
  34 $RCMAIL = rcmail::get_instance();
  35 $db = $RCMAIL->get_dbh();
  36 $db->db_connect('w');
  37
  38 if (!$db->is_connected() || $db->is_error())
  39     die("No DB connection\n");
  40
  41 if (!empty($_SERVER['argv'][1]))
  42     $days = intval($_SERVER['argv'][1]);
  43 else
  44     $days = 7;
  45
  46 // remove all deleted records older than two days
  47 $threshold = date('Y-m-d 00:00:00', time() - $days * 86400);
  48
  49 foreach (array('contacts','contactgroups','identities') as $table) {
  50
  51     $sqltable = get_table_name($table);
  52
  53     // also delete linked records
  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');
  60
  61         $db->query(
  62             "DELETE FROM $memberstable".
  63             " WHERE $pk IN (".
  64                 "SELECT $pk FROM $sqltable".
  65                 " WHERE del=1 AND changed < ?".
  66             ")",
  67             $threshold);
  68
  69         echo $db->affected_rows() . " records deleted from '$memberstable'\n";
  70     }
  71
  72     // delete outdated records
  73     $db->query("DELETE FROM $sqltable WHERE del=1 AND changed < ?", $threshold);
  74
  75     echo $db->affected_rows() . " records deleted from '$table'\n";
  76 }
  77
  78 ?>