# HG changeset patch # User Pascal Volk # Date 1319817091 0 # Node ID 9823548b2717c4bc01c7ecbd8c3dcec6fb206af7 # Parent d296a020f440427312871da7f4ef08245f5e6b6a pgsql: Added new table `service_set'. Moved columns `smtp', `pop3' `imap' and `sieve' from the `users' table to the new `service_set' table. diff -r d296a020f440 -r 9823548b2717 pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql --- 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; diff -r d296a020f440 -r 9823548b2717 pgsql/create_optional_types_and_functions.pgsql --- 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; diff -r d296a020f440 -r 9823548b2717 pgsql/create_tables-dovecot-1.2.x.pgsql --- 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, diff -r d296a020f440 -r 9823548b2717 pgsql/create_tables.pgsql --- 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, diff -r d296a020f440 -r 9823548b2717 pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- 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||'/' diff -r d296a020f440 -r 9823548b2717 pgsql/update_tables_0.5.x-0.6.pgsql --- 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||'/' diff -r d296a020f440 -r 9823548b2717 pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql --- 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; diff -r d296a020f440 -r 9823548b2717 pgsql/update_types_and_functions_0.5.x-0.6.pgsql --- 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;