Updates for new Hallinta and aliases

[misc/hallinta-vmail] / vmail.sql

diff --git a/vmail.sql b/vmail.sql
index e2442d0..664c98f 100644 (file)
--- a/vmail.sql
+++ b/vmail.sql
@@ -2,7 +2,8 @@ CREATE TABLE vmail_domain (
     id SERIAL,
     name character varying(100) NOT NULL,
     sys_user character varying(10) NOT NULL,
-    sys_edit timestamp without time zone NOT NULL
+    sys_edit timestamp without time zone NOT NULL,
+    UNIQUE(name)
 );
 
 CREATE UNIQUE INDEX vmail_domain_id ON vmail_domain USING btree (id);
@@ -12,15 +13,32 @@ CREATE TABLE vmail_user (
     vmail_domain_id integer NOT NULL,
     username character varying(50) NOT NULL,
     password character varying(50),
-    forward character varying(150),
     active integer DEFAULT 0 NOT NULL,
     sys_user character varying(10) NOT NULL,
-    sys_edit timestamp without time zone NOT NULL
+    sys_edit timestamp without time zone NOT NULL,
+    UNIQUE(vmail_domain_id,username)
 );
 
 CREATE UNIQUE INDEX vmail_user_id ON vmail_user USING btree (id);
 CREATE INDEX vmail_user_vmail_domain_id ON vmail_user USING btree (vmail_domain_id);
 
+-- ALTER TABLE ONLY vmail_user
+--     ADD CONSTRAINT vmail_user_vmail_domain_id_username_key UNIQUE (vmail_domain_id, username);
+
+CREATE TABLE vmail_alias (
+    id SERIAL,
+    vmail_domain_id integer NOT NULL,
+    username character varying(50) NOT NULL,
+    destination TEXT DEFAULT NULL,
+    active integer DEFAULT 0 NOT NULL,
+    sys_user character varying(10) NOT NULL,
+    sys_edit timestamp without time zone NOT NULL,
+    UNIQUE(vmail_domain_id,username)
+);
+
+CREATE UNIQUE INDEX vmail_alias_id ON vmail_alias USING btree (id);
+CREATE INDEX vmail_alias_vmail_domain_id ON vmail_alias USING btree (vmail_domain_id);
+
 REVOKE ALL ON TABLE vmail_user FROM PUBLIC;
 GRANT SELECT ON TABLE vmail_user TO "vmail";
 GRANT ALL ON TABLE vmail_user TO "hallinta";
@@ -28,6 +46,13 @@ GRANT ALL ON TABLE vmail_user TO "hallinta";
 REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM PUBLIC;
 GRANT ALL ON SEQUENCE vmail_user_id_seq TO "hallinta";
 
+REVOKE ALL ON TABLE vmail_alias FROM PUBLIC;
+GRANT SELECT ON TABLE vmail_alias TO "vmail";
+GRANT ALL ON TABLE vmail_alias TO "hallinta";
+
+REVOKE ALL ON SEQUENCE vmail_alias_id_seq FROM PUBLIC;
+GRANT ALL ON SEQUENCE vmail_alias_id_seq TO "hallinta";
+
 REVOKE ALL ON TABLE vmail_domain FROM PUBLIC;
 GRANT SELECT ON TABLE vmail_domain TO "vmail";
 GRANT ALL ON TABLE vmail_domain TO "hallinta";