From a35062a1eba5c6c15f703686cd4fecc5536d74df Mon Sep 17 00:00:00 2001 From: thomascube Date: Thu, 15 Apr 2010 06:33:30 +0000 Subject: Always set changed date when marking a DB record as deleted + provide a cleanup script --- CHANGELOG | 1 + SQL/mssql.initial.sql | 1 + SQL/mysql.initial.sql | 3 +- SQL/mysql.update.sql | 1 + SQL/postgres.initial.sql | 1 + SQL/postgres.update.sql | 2 + SQL/sqlite.initial.sql | 1 + SQL/sqlite.update.sql | 2 + bin/cleandb.php | 77 ++++++++++++++++++++++++++++++++++++++ program/include/rcube_contacts.php | 18 ++------- program/include/rcube_user.php | 14 +++---- 11 files changed, 99 insertions(+), 22 deletions(-) create mode 100755 bin/cleandb.php 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 + | + +-----------------------------------------------------------------------+ + + $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, -- cgit v1.2.3