From a35062a1eba5c6c15f703686cd4fecc5536d74df Mon Sep 17 00:00:00 2001
From: thomascube <thomas@roundcube.net>
Date: Thu, 15 Apr 2010 02:33:30 -0400
Subject: [PATCH] Always set changed date when marking a DB record as deleted + provide a cleanup script
---
CHANGELOG | 1
bin/cleandb.php | 77 +++++++++++++++++++++++++
SQL/sqlite.update.sql | 2
SQL/postgres.update.sql | 2
program/include/rcube_user.php | 14 ++--
SQL/mysql.update.sql | 1
SQL/postgres.initial.sql | 1
SQL/mysql.initial.sql | 3
SQL/sqlite.initial.sql | 1
SQL/mssql.initial.sql | 1
program/include/rcube_contacts.php | 18 +----
11 files changed, 99 insertions(+), 22 deletions(-)
diff --git a/CHANGELOG b/CHANGELOG
index d78b999..6612dab 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 e97a9a7..dfbb935 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -23,6 +23,7 @@
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 459b266..3145ca5 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -124,6 +124,8 @@
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 @@
`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 969773e..f820415 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -82,6 +82,7 @@
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 eeeca32..5770dcc 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -68,6 +68,7 @@
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 ec7ce9b..dc22b90 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -49,6 +49,8 @@
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 8929c62..8bd43f4 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -65,6 +65,7 @@
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 1011f52..dd2887c 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -48,6 +48,8 @@
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 0000000..c40a1cd
--- /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 dd37972..b8307d4 100644
--- a/program/include/rcube_contacts.php
+++ b/program/include/rcube_contacts.php
@@ -403,16 +403,10 @@
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 @@
}
/**
- * 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 6ed16db..2d0eff2 100644
--- a/program/include/rcube_user.php
+++ b/program/include/rcube_user.php
@@ -194,7 +194,7 @@
$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 @@
$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 @@
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 @@
$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,
--
Gitblit v1.9.1