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, Roundcube Dev. - Switzerland                      |
  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 4164 2010-10-31 10:38:16Z thomasb $
  20
  21 */
  22
  23 if (php_sapi_name() != 'cli') {
  24     die('Not on the "shell" (php-cli).');
  25 }
  26
  27 define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
  28 require INSTALL_PATH.'program/include/iniset.php';
  29
  30 // mapping for table name => primary key
  31 $primary_keys = array(
  32     'contacts' => "contact_id",
  33     'contactgroups' => "contactgroup_id",
  34 );
  35
  36 // connect to DB
  37 $RCMAIL = rcmail::get_instance();
  38 $db = $RCMAIL->get_dbh();
  39 $db->db_connect('w');
  40
  41 if (!$db->is_connected() || $db->is_error())
  42     die("No DB connection\n");
  43
  44 if (!empty($_SERVER['argv'][1]))
  45     $days = intval($_SERVER['argv'][1]);
  46 else
  47     $days = 7;
  48
  49 // remove all deleted records older than two days
  50 $threshold = date('Y-m-d 00:00:00', time() - $days * 86400);
  51
  52 foreach (array('contacts','contactgroups','identities') as $table) {
  53
  54     $sqltable = get_table_name($table);
  55
  56     // also delete linked records
  57     // could be skipped for databases which respect foreign key constraints
  58     if ($db->db_provider == 'sqlite'
  59         && ($table == 'contacts' || $table == 'contactgroups')
  60     ) {
  61         $pk = $primary_keys[$table];
  62         $memberstable = get_table_name('contactgroupmembers');
  63
  64         $db->query(
  65             "DELETE FROM $memberstable".
  66             " WHERE $pk IN (".
  67                 "SELECT $pk FROM $sqltable".
  68                 " WHERE del=1 AND changed < ?".
  69             ")",
  70             $threshold);
  71
  72         echo $db->affected_rows() . " records deleted from '$memberstable'\n";
  73     }
  74
  75     // delete outdated records
  76     $db->query("DELETE FROM $sqltable WHERE del=1 AND changed < ?", $threshold);
  77
  78     echo $db->affected_rows() . " records deleted from '$table'\n";
  79 }
  80
  81 ?>