From a35062a1eba5c6c15f703686cd4fecc5536d74df Mon Sep 17 00:00:00 2001
From: thomascube <thomas@roundcube.net>
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

---
 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 ++
 7 files changed, 10 insertions(+), 1 deletion(-)

(limited to 'SQL')

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',
-- 
cgit v1.2.3