X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=SQL%2Fpostgres.update.sql;h=0ae8d3f7fda5cab464a9ac98e8e443546fd336e9;hb=fe4a852cd4335d3d2089b13e10d409fa2fa816d8;hp=e343dd440d34bf47c7bc76b2bea13ca2942d8845;hpb=e8a0682b96f5b7f297e58d101735ba20a0cc3a89;p=roundcube.git

diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index e343dd4..0ae8d3f 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -1,4 +1,4 @@
--- RoundCube Webmail update script for Postgres databases
+-- Roundcube Webmail update script for Postgres databases
 -- Updates from version 0.1-stable to 0.1.1
 
 CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key);
@@ -39,7 +39,53 @@ ALTER TABLE messages ALTER del TYPE smallint;
 
 -- Updates from version 0.3-stable
 
-CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
 TRUNCATE messages;
+CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
 DROP INDEX contacts_user_id_idx;
 CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
+
+-- Updates from version 0.3.1
+
+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;
+
+CREATE SEQUENCE contactgroups_ids
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE contactgroups (
+    contactgroup_id integer DEFAULT nextval('contactgroups_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 	NOT NULL DEFAULT 0,
+    name 	varchar(128) 	NOT NULL DEFAULT ''
+);
+
+CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
+
+CREATE TABLE contactgroupmembers (
+    contactgroup_id 	integer NOT NULL
+	REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    contact_id 		integer NOT NULL
+	REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    created timestamp with time zone DEFAULT now() NOT NULL,
+    PRIMARY KEY (contactgroup_id, contact_id)
+);
+
+-- Updates from version 0.4-beta
+
+ALTER TABLE users ALTER last_login DROP NOT NULL;
+ALTER TABLE users ALTER last_login SET DEFAULT NULL;
+
+-- Updates from version 0.4.2
+
+DROP INDEX users_username_id_idx;
+ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
+ALTER TABLE contacts ALTER email TYPE varchar(255);
+
+TRUNCATE messages;