Always set changed date when marking a DB record as deleted + provide a cleanup script

11 files changed, 99 insertions, 22 deletions

diff --git a/CHANGELOG b/CHANGELOG
index d78b9997f..6612dab5a 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,6 +1,7 @@
 CHANGELOG RoundCube Webmail
 ===========================
 
+- Always set changed date when flagging a DB record as deleted + provide a cleanup script
 - Fix address book/group selection (#1486619)
 - Assign newly created contacts to the active group (#1486626)
 - Added option not to mark messages as read when viewed in preview pane (#1485012)
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index e97a9a7b7..dfbb93560 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -23,6 +23,7 @@ GO
 CREATE TABLE [dbo].[identities] (

 	[identity_id] [int] IDENTITY (1, 1) NOT NULL ,

 	[user_id] [int] NOT NULL ,

+	[changed] [datetime] NOT NULL ,

 	[del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,

 	[standard] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,

 	[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 459b266e4..3145ca543 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -124,6 +124,8 @@ CREATE TABLE `contactgroupmembers` (
 
 CREATE TABLE `identities` (
  `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `standard` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
@@ -133,7 +135,6 @@ CREATE TABLE `identities` (
  `bcc` varchar(128) NOT NULL DEFAULT '',
  `signature` text,
  `html_signature` tinyint(1) NOT NULL DEFAULT '0',
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY(`identity_id`),
  CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index 969773ecb..f820415d3 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -82,6 +82,7 @@ ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
 ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
 
 ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);
+ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`;
 
 CREATE TABLE `contactgroups` (
   `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index eeeca32ce..5770dcc4c 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -68,6 +68,7 @@ CREATE TABLE identities (
     identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY,
     user_id integer NOT NULL
 	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    changed timestamp with time zone DEFAULT now() NOT NULL,
     del smallint DEFAULT 0 NOT NULL,
     standard smallint DEFAULT 0 NOT NULL,
     name varchar(128) NOT NULL,
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index ec7ce9b62..dc22b9030 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -49,6 +49,8 @@ CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
 DROP INDEX identities_user_id_idx;
 CREATE INDEX identities_user_id_idx ON identities (user_id, del);
 
+ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL AFTER user_id;
+
 CREATE SEQUENCE contactgroups_ids
     INCREMENT BY 1
     NO MAXVALUE
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 8929c62ea..8bd43f4b8 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -65,6 +65,7 @@ CREATE TABLE contactgroupmembers (
 CREATE TABLE identities (
   identity_id integer NOT NULL PRIMARY KEY,
   user_id integer NOT NULL default '0',
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
   del tinyint NOT NULL default '0',
   standard tinyint NOT NULL default '0',
   name varchar(128) NOT NULL default '',
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index 1011f52c2..dd2887ca2 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -48,6 +48,8 @@ CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
 DROP INDEX ix_identities_user_id;
 CREATE INDEX ix_identities_user_id ON identities (user_id, del);
 
+ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00';
+
 CREATE TABLE contactgroups (
   contactgroup_id integer NOT NULL PRIMARY KEY,
   user_id integer NOT NULL default '0',
diff --git a/bin/cleandb.php b/bin/cleandb.php
new file mode 100755
index 000000000..c40a1cd6b
--- /dev/null
+++ b/bin/cleandb.php
@@ -0,0 +1,77 @@
+#!/usr/bin/env php
+<?php
+/*
+
+ +-----------------------------------------------------------------------+
+ | bin/cleandb.php                                                       |
+ |                                                                       |
+ | This file is part of the RoundCube Webmail client                     |
+ | Copyright (C) 2010, RoundCube Dev. - Switzerland                      |
+ | Licensed under the GNU GPL                                            |
+ |                                                                       |
+ | PURPOSE:                                                              |
+ |   Finally remove all db records marked as deleted some time ago       |
+ |                                                                       |
+ +-----------------------------------------------------------------------+
+ | Author: Thomas Bruederli <roundcube@gmail.com>                        |
+ +-----------------------------------------------------------------------+
+
+ $Id$
+
+*/
+
+if (php_sapi_name() != 'cli') {
+    die('Not on the "shell" (php-cli).');
+}
+
+define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
+require INSTALL_PATH.'program/include/iniset.php';
+
+// mapping for table name => primary key
+$primary_keys = array(
+  'contacts' => "contact_id",
+  'contactgroups' => "contactgroup_id",
+);
+
+// connect to DB
+$RCMAIL = rcmail::get_instance();
+$db = $RCMAIL->get_dbh();
+
+if (!$db->is_connected() || $db->is_error)
+  die("No DB connection");
+
+// remove all deleted records older than two days
+$threshold = date('Y-m-d 00:00:00', time() - 2 * 86400);
+
+foreach (array('contacts','contactgroups','identities') as $table) {
+  // also delete linked records
+  // could be skipped for databases which respect foreign key constraints
+  if ($table == 'contacts' || $table == 'contactgroups') {
+    $ids = array();
+    $pk = $primary_keys[$table];
+
+    $result = $db->query(
+      "SELECT $pk FROM ".get_table_name($table)."
+       WHERE del=1 AND changed < ".$db->quote($threshold));
+
+    while ($result && ($sql_arr = $db->fetch_assoc($result)))
+      $ids[] = $sql_arr[$pk];
+
+    if (count($ids)) {
+      $db->query(
+        "DELETE FROM ".get_table_name('contactgroupmembers')."
+         WHERE $pk IN (".join(',', $ids).")");
+
+      echo $db->affected_rows() . " records deleted from '".get_table_name('contactgroupmembers')."'\n";
+    }
+  }
+
+  // delete outdated records
+  $db->query(
+    "DELETE FROM ".get_table_name($table)."
+     WHERE del=1 AND changed < ".$db->quote($threshold));
+
+  echo $db->affected_rows() . " records deleted from '$table'\n";
+}
+
+?>
diff --git a/program/include/rcube_contacts.php b/program/include/rcube_contacts.php
index dd37972c2..b8307d49a 100644
--- a/program/include/rcube_contacts.php
+++ b/program/include/rcube_contacts.php
@@ -403,16 +403,10 @@ class rcube_contacts extends rcube_addressbook
     if (is_array($ids))
       $ids = join(',', $ids);
 
-    // delete all group members linked with these contacts
-    if ($this->groups) {
-      $this->db->query(
-        "DELETE FROM ".get_table_name('contactgroupmembers')."
-         WHERE  contact_id IN (".$ids.")");
-    }
-
+    // flag record as deleted
     $this->db->query(
       "UPDATE ".$this->db_name."
-       SET    del=1
+       SET    del=1, changed=".$this->db->now()."
        WHERE  user_id=?
        AND    contact_id IN (".$ids.")",
       $this->user_id);
@@ -456,18 +450,14 @@ class rcube_contacts extends rcube_addressbook
   }
 
   /**
-   * Delete the given group and all linked group members
+   * Delete the given group (and all linked group members)
    *
    * @param string Group identifier
    * @return boolean True on success, false if no data was changed
    */
   function delete_group($gid)
   {
-    $sql_result = $this->db->query(
-      "DELETE FROM ".get_table_name('contactgroupmembers')."
-       WHERE  contactgroup_id=?",
-      $gid);
-    
+    // flag group record as deleted
     $sql_result = $this->db->query(
       "UPDATE ".get_table_name('contactgroups')."
        SET del=1, changed=".$this->db->now()."
diff --git a/program/include/rcube_user.php b/program/include/rcube_user.php
index 6ed16dbac..2d0eff2da 100644
--- a/program/include/rcube_user.php
+++ b/program/include/rcube_user.php
@@ -194,7 +194,7 @@ class rcube_user
     $query_params[] = $this->ID;
 
     $sql = "UPDATE ".get_table_name('identities')."
-       SET ".join(', ', $query_cols)."
+       SET    changed=".$this->db->now().", ".join(', ', $query_cols)."
        WHERE  identity_id=?
        AND    user_id=?
        AND    del<>1";
@@ -229,8 +229,8 @@ class rcube_user
     $insert_values[] = $this->ID;
 
     $sql = "INSERT INTO ".get_table_name('identities')."
-        (".join(', ', $insert_cols).")
-       VALUES (".join(', ', array_pad(array(), sizeof($insert_values), '?')).")";
+        (changed, ".join(', ', $insert_cols).")
+       VALUES (".$this->db->now().", ".join(', ', array_pad(array(), sizeof($insert_values), '?')).")";
 
     call_user_func_array(array($this->db, 'query'),
                         array_merge(array($sql), $insert_values));
@@ -250,9 +250,9 @@ class rcube_user
     if (!$this->ID)
       return false;
 
-    $sql_result = $this->db->query("SELECT count(*) AS ident_count FROM " .
-      get_table_name('identities') .
-      " WHERE user_id = ? AND del <> 1",
+    $sql_result = $this->db->query(
+      "SELECT count(*) AS ident_count FROM ".get_table_name('identities')."
+       WHERE user_id = ? AND del <> 1",
       $this->ID);
 
     $sql_arr = $this->db->fetch_assoc($sql_result);
@@ -261,7 +261,7 @@ class rcube_user
     
     $this->db->query(
       "UPDATE ".get_table_name('identities')."
-       SET    del=1
+       SET    del=1, changed=".$this->db->now()."
        WHERE  user_id=?
        AND    identity_id=?",
       $this->ID,