pgsql: Added new table `service_set'.
Moved columns `smtp', `pop3' `imap' and `sieve' from the `users' table to
the new `service_set' table.
--- a/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -410,11 +410,12 @@
BEGIN
FOR record IN
SELECT userid, passwd, smtp, pop3, imap, sieve
- FROM users
+ FROM users, service_set
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
+ AND service_set.ssid = users.ssid
LOOP
RETURN NEXT record;
END LOOP;
--- a/pgsql/create_optional_types_and_functions.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/create_optional_types_and_functions.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -288,7 +288,7 @@
mail text
);
-- ---
--- Data type for function dovecotquotauser(varchar, varchar)
+-- Data type for function dovecotquotauser(varchar, varchar)
-- ---
CREATE TYPE dovecotquotauser AS (
userid varchar(320),
@@ -410,11 +410,12 @@
BEGIN
FOR record IN
SELECT userid, passwd, smtp, pop3, imap, managesieve
- FROM users
+ FROM users, service_set
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
+ AND service_set.ssid = users.ssid
LOOP
RETURN NEXT record;
END LOOP;
--- a/pgsql/create_tables-dovecot-1.2.x.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -10,6 +10,8 @@
CREATE SEQUENCE quotalimit_id;
+CREATE SEQUENCE service_set_id;
+
CREATE SEQUENCE domain_gid
START WITH 70000
INCREMENT BY 1
@@ -67,16 +69,48 @@
-- Insert default (non) quota limit
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
+CREATE TABLE service_set (
+ ssid bigint NOT NULL DEFAULT nextval('service_set_id'),
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ sieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_service_set PRIMARY KEY (ssid),
+ CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
+);
+-- Insert all possible service combinations
+COPY service_set (smtp, pop3, imap, sieve) FROM stdin;
+TRUE TRUE TRUE TRUE
+FALSE TRUE TRUE TRUE
+TRUE FALSE TRUE TRUE
+FALSE FALSE TRUE TRUE
+TRUE TRUE FALSE TRUE
+FALSE TRUE FALSE TRUE
+TRUE FALSE FALSE TRUE
+FALSE FALSE FALSE TRUE
+TRUE TRUE TRUE FALSE
+FALSE TRUE TRUE FALSE
+TRUE FALSE TRUE FALSE
+FALSE FALSE TRUE FALSE
+TRUE TRUE FALSE FALSE
+FALSE TRUE FALSE FALSE
+TRUE FALSE FALSE FALSE
+FALSE FALSE FALSE FALSE
+\.
+
CREATE TABLE domain_data (
gid bigint NOT NULL DEFAULT nextval('domain_gid'),
+ qid bigint NOT NULL DEFAULT 1, -- default quota limit
+ ssid bigint NOT NULL DEFAULT 1, -- default service set
tid bigint NOT NULL DEFAULT 1, -- default transport
- qid bigint NOT NULL DEFAULT 1, -- default quota limit
domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
+ CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
+ REFERENCES quotalimit (qid),
+ CONSTRAINT fkey_domain_data_ssid_service_set FOREIGN KEY (ssid)
+ REFERENCES service_set (ssid),
CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid),
- CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
- REFERENCES quotalimit (qid)
+ REFERENCES transport (tid)
);
CREATE TABLE domain_name (
@@ -95,22 +129,21 @@
uid bigint NOT NULL DEFAULT nextval('users_uid'),
gid bigint NOT NULL,
mid bigint NOT NULL DEFAULT 1,
+ qid bigint NOT NULL DEFAULT 1,
+ ssid bigint NOT NULL DEFAULT 1,
tid bigint NOT NULL DEFAULT 1,
- qid bigint NOT NULL DEFAULT 1,
- smtp boolean NOT NULL DEFAULT TRUE,
- pop3 boolean NOT NULL DEFAULT TRUE,
- imap boolean NOT NULL DEFAULT TRUE,
- sieve boolean NOT NULL DEFAULT TRUE,
CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
CONSTRAINT ukey_users_uid UNIQUE (uid),
CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
REFERENCES domain_data (gid),
CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
REFERENCES maillocation (mid),
+ CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid)
+ REFERENCES quotalimit (qid),
+ CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid)
+ REFERENCES service_set (ssid),
CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid),
- CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid)
- REFERENCES quotalimit (qid)
+ REFERENCES transport (tid)
);
CREATE TABLE userquota (
@@ -144,7 +177,8 @@
SELECT local_part || '@' || domain_name.domainname AS "user",
passwd AS "password", smtp, pop3, imap, sieve
FROM users
- LEFT JOIN domain_name USING (gid);
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN service_set USING (ssid);
CREATE OR REPLACE VIEW dovecot_user AS
SELECT local_part || '@' || domain_name.domainname AS userid,
--- a/pgsql/create_tables.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/create_tables.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -10,6 +10,8 @@
CREATE SEQUENCE maillocation_id;
+CREATE SEQUENCE service_set_id;
+
CREATE SEQUENCE domain_gid
START WITH 70000
INCREMENT BY 1
@@ -67,16 +69,48 @@
-- Insert default (non) quota limit
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
+CREATE TABLE service_set (
+ ssid bigint NOT NULL DEFAULT nextval('service_set_id'),
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ managesieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_service_set PRIMARY KEY (ssid),
+ CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve)
+);
+-- Insert all possible service combinations
+COPY service_set (smtp, pop3, imap, managesieve) FROM stdin;
+TRUE TRUE TRUE TRUE
+FALSE TRUE TRUE TRUE
+TRUE FALSE TRUE TRUE
+FALSE FALSE TRUE TRUE
+TRUE TRUE FALSE TRUE
+FALSE TRUE FALSE TRUE
+TRUE FALSE FALSE TRUE
+FALSE FALSE FALSE TRUE
+TRUE TRUE TRUE FALSE
+FALSE TRUE TRUE FALSE
+TRUE FALSE TRUE FALSE
+FALSE FALSE TRUE FALSE
+TRUE TRUE FALSE FALSE
+FALSE TRUE FALSE FALSE
+TRUE FALSE FALSE FALSE
+FALSE FALSE FALSE FALSE
+\.
+
CREATE TABLE domain_data (
gid bigint NOT NULL DEFAULT nextval('domain_gid'),
+ qid bigint NOT NULL DEFAULT 1, -- default quota limit
+ ssid bigint NOT NULL DEFAULT 1, -- default service_set
tid bigint NOT NULL DEFAULT 1, -- default transport
- qid bigint NOT NULL DEFAULT 1, -- default quota limit
domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
+ CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
+ REFERENCES quotalimit (qid),
+ CONSTRAINT fkey_domain_data_ssid_service_set FOREIGN KEY (ssid)
+ REFERENCES service_set (ssid),
CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid),
- CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
- REFERENCES quotalimit (qid)
+ REFERENCES transport (tid)
);
CREATE TABLE domain_name (
@@ -95,22 +129,21 @@
uid bigint NOT NULL DEFAULT nextval('users_uid'),
gid bigint NOT NULL,
mid bigint NOT NULL DEFAULT 1,
+ qid bigint NOT NULL DEFAULT 1,
+ ssid bigint NOT NULL DEFAULT 1,
tid bigint NOT NULL DEFAULT 1,
- qid bigint NOT NULL DEFAULT 1,
- smtp boolean NOT NULL DEFAULT TRUE,
- pop3 boolean NOT NULL DEFAULT TRUE,
- imap boolean NOT NULL DEFAULT TRUE,
- managesieve boolean NOT NULL DEFAULT TRUE,
CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
CONSTRAINT ukey_users_uid UNIQUE (uid),
CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
REFERENCES domain_data (gid),
CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
REFERENCES maillocation (mid),
+ CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid)
+ REFERENCES quotalimit (qid),
+ CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid)
+ REFERENCES service_set (ssid),
CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid),
- CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid)
- REFERENCES quotalimit (qid)
+ REFERENCES transport (tid)
);
CREATE TABLE userquota_11 (
@@ -144,7 +177,8 @@
SELECT local_part || '@' || domain_name.domainname AS "user",
passwd AS "password", smtp, pop3, imap, managesieve
FROM users
- LEFT JOIN domain_name USING (gid);
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN service_set USING (ssid);
CREATE OR REPLACE VIEW dovecot_user AS
SELECT local_part || '@' || domain_name.domainname AS userid,
--- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -1,24 +1,45 @@
SET client_encoding = 'UTF8';
SET client_min_messages = warning;
+-- ---
+-- Create the new service_set table and insert all possible combinations
+-- --
+CREATE SEQUENCE service_set_id;
+
+CREATE TABLE service_set (
+ ssid bigint NOT NULL DEFAULT nextval('service_set_id'),
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ sieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_service_set PRIMARY KEY (ssid),
+ CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
+);
+
+COPY service_set (smtp, pop3, imap, sieve) FROM stdin;
+TRUE TRUE TRUE TRUE
+FALSE TRUE TRUE TRUE
+TRUE FALSE TRUE TRUE
+FALSE FALSE TRUE TRUE
+TRUE TRUE FALSE TRUE
+FALSE TRUE FALSE TRUE
+TRUE FALSE FALSE TRUE
+FALSE FALSE FALSE TRUE
+TRUE TRUE TRUE FALSE
+FALSE TRUE TRUE FALSE
+TRUE FALSE TRUE FALSE
+FALSE FALSE TRUE FALSE
+TRUE TRUE FALSE FALSE
+FALSE TRUE FALSE FALSE
+TRUE FALSE FALSE FALSE
+FALSE FALSE FALSE FALSE
+\.
-- ---
--- Make room for sha512-crypt.hex hashed passwords
--- ---
-DROP VIEW dovecot_password;
-
-ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
-
-CREATE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, sieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
--- ---
--- Make room for different mailbox formats.
+-- Make room for different mailbox formats and longer password hashes.
-- ---
DROP VIEW dovecot_user;
+DROP VIEW dovecot_password;
DROP VIEW postfix_maildir;
DROP VIEW vmm_domain_info;
@@ -45,6 +66,7 @@
ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
-- ---
-- Add quota stuff
@@ -59,7 +81,7 @@
-- Insert default (non) quota limit
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
--- Adjust tables
+-- Adjust tables (quota)
ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
FOREIGN KEY (qid) REFERENCES quotalimit (qid);
@@ -115,6 +137,28 @@
CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
FOR EACH ROW EXECUTE PROCEDURE merge_userquota();
+-- Adjust tables (services)
+ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
+ FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
+ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+-- save current service sets
+UPDATE users u
+ SET ssid = ss.ssid
+ FROM service_set ss
+ WHERE ss.smtp = u.smtp
+ AND ss.pop3 = u.pop3
+ AND ss.imap = u.imap
+ AND ss.sieve = u.sieve;
+
+ALTER TABLE users DROP COLUMN smtp;
+ALTER TABLE users DROP COLUMN pop3;
+ALTER TABLE users DROP COLUMN imap;
+ALTER TABLE users DROP COLUMN sieve;
+ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
+ FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
-- ---
-- Restore views
-- ---
@@ -128,6 +172,13 @@
LEFT JOIN maillocation USING (mid)
LEFT JOIN mailboxformat USING (fid);
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, sieve
+ FROM users
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN service_set USING (ssid);
+
CREATE VIEW postfix_maildir AS
SELECT local_part || '@' || domain_name.domainname AS address,
domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
--- a/pgsql/update_tables_0.5.x-0.6.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/update_tables_0.5.x-0.6.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -1,24 +1,45 @@
SET client_encoding = 'UTF8';
SET client_min_messages = warning;
+-- ---
+-- Create the new service_set table and insert all possible combinations
+-- --
+CREATE SEQUENCE service_set_id;
+
+CREATE TABLE service_set (
+ ssid bigint NOT NULL DEFAULT nextval('service_set_id'),
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ managesieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_service_set PRIMARY KEY (ssid),
+ CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve)
+);
+
+COPY service_set (smtp, pop3, imap, managesieve) FROM stdin;
+TRUE TRUE TRUE TRUE
+FALSE TRUE TRUE TRUE
+TRUE FALSE TRUE TRUE
+FALSE FALSE TRUE TRUE
+TRUE TRUE FALSE TRUE
+FALSE TRUE FALSE TRUE
+TRUE FALSE FALSE TRUE
+FALSE FALSE FALSE TRUE
+TRUE TRUE TRUE FALSE
+FALSE TRUE TRUE FALSE
+TRUE FALSE TRUE FALSE
+FALSE FALSE TRUE FALSE
+TRUE TRUE FALSE FALSE
+FALSE TRUE FALSE FALSE
+TRUE FALSE FALSE FALSE
+FALSE FALSE FALSE FALSE
+\.
-- ---
--- Make room for sha512-crypt.hex hashed passwords
--- ---
-DROP VIEW dovecot_password;
-
-ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
-
-CREATE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, managesieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
--- ---
--- Make room for different mailbox formats.
+-- Make room for different mailbox formats and longer password hashes.
-- ---
DROP VIEW dovecot_user;
+DROP VIEW dovecot_password;
DROP VIEW postfix_maildir;
DROP VIEW vmm_domain_info;
@@ -45,6 +66,7 @@
ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
-- ---
-- Add quota stuff
@@ -59,7 +81,7 @@
-- Insert default (non) quota limit
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
--- Adjust tables …
+-- Adjust tables (quota)
ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
FOREIGN KEY (qid) REFERENCES quotalimit (qid);
@@ -93,6 +115,28 @@
CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11
FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11();
+-- Adjust tables (services)
+ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
+ FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
+ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+-- save current service sets
+UPDATE users u
+ SET ssid = ss.ssid
+ FROM service_set ss
+ WHERE ss.smtp = u.smtp
+ AND ss.pop3 = u.pop3
+ AND ss.imap = u.imap
+ AND ss.managesieve = u.managesieve;
+
+ALTER TABLE users DROP COLUMN smtp;
+ALTER TABLE users DROP COLUMN pop3;
+ALTER TABLE users DROP COLUMN imap;
+ALTER TABLE users DROP COLUMN managesieve;
+ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
+ FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
-- ---
-- Restore views
-- ---
@@ -106,6 +150,13 @@
LEFT JOIN maillocation USING (mid)
LEFT JOIN mailboxformat USING (fid);
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, managesieve
+ FROM users
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN service_set USING (ssid);
+
CREATE VIEW postfix_maildir AS
SELECT local_part || '@' || domain_name.domainname AS address,
domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
--- a/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -70,11 +70,12 @@
BEGIN
FOR record IN
SELECT userid, passwd, smtp, pop3, imap, sieve
- FROM users
+ FROM users, service_set
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
+ AND service_set.ssid = users.ssid
LOOP
RETURN NEXT record;
END LOOP;
--- a/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Wed Oct 26 23:32:58 2011 +0000
+++ b/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Fri Oct 28 15:51:31 2011 +0000
@@ -70,11 +70,12 @@
BEGIN
FOR record IN
SELECT userid, passwd, smtp, pop3, imap, managesieve
- FROM users
+ FROM users, service_set
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
+ AND service_set.ssid = users.ssid
LOOP
RETURN NEXT record;
END LOOP;