summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
authorthomascube <thomas@roundcube.net>2010-04-15 06:33:30 +0000
committerthomascube <thomas@roundcube.net>2010-04-15 06:33:30 +0000
commita35062a1eba5c6c15f703686cd4fecc5536d74df (patch)
treebde3c4df3b2cc4073cd6c2b4a6af0f8700f17305 /SQL
parent58892824a6ee7f9a545372730b615ec9abafbe5c (diff)
Always set changed date when marking a DB record as deleted + provide a cleanup script
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql1
-rw-r--r--SQL/mysql.initial.sql3
-rw-r--r--SQL/mysql.update.sql1
-rw-r--r--SQL/postgres.initial.sql1
-rw-r--r--SQL/postgres.update.sql2
-rw-r--r--SQL/sqlite.initial.sql1
-rw-r--r--SQL/sqlite.update.sql2
7 files changed, 10 insertions, 1 deletions
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',