# HG changeset patch # User Pascal Volk # Date 1218674011 0 # Node ID 9d10877e1c104e26017fdb82d2705cdffda396b9 # Parent fbcb7e314510e2ff89e9cbc67760bb3bde1de6ae * 'create_tables.pgsql' - modified database layout * 'update_tables_0.4.x-0.5.pgsql' - added to repository * 'UPGRADE' - updated diff -r fbcb7e314510 -r 9d10877e1c10 UPGRADE --- a/UPGRADE Thu Aug 07 22:27:29 2008 +0000 +++ b/UPGRADE Thu Aug 14 00:33:31 2008 +0000 @@ -1,8 +1,21 @@ If you still have installed vmm 0.3.x you have to proceed this step first: - * upgrade your vmm installation to version 0.4 + + * upgrade your vmm installation to version 0.4-r41 + + +If you have installed vmm 0.4/0.4-r41 you have to proceed this steps: -If you have installed vmm 0.4 you have to proceed this step: - * execute upgrade.sh + * stop Postfix and Dovecot + * backup/dump your database. + * backup/dump your database! + + * start psql and connect to the appropriate database (ex. psql mailsys) + * update the database: \i update_tables_0.4.x-0.5.pgsql + + * execute upgrade.sh + + * start Dovecot and Postfix + else * read INSTALL diff -r fbcb7e314510 -r 9d10877e1c10 create_tables.pgsql --- a/create_tables.pgsql Thu Aug 07 22:27:29 2008 +0000 +++ b/create_tables.pgsql Thu Aug 14 00:33:31 2008 +0000 @@ -1,26 +1,14 @@ -- $Id$ +SET client_encoding = 'UTF8'; +SET client_min_messages = warning; + + CREATE SEQUENCE transport_id; -CREATE TABLE transport ( - tid bigint NOT NULL DEFAULT nextval('transport_id'), - transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] - CONSTRAINT pkey_transport PRIMARY KEY (tid), - CONSTRAINT ukey_transport UNIQUE (transport) -); --- Insert default transport -INSERT INTO transport(transport) VALUES ('dovecot:'); CREATE SEQUENCE maillocation_id; -CREATE TABLE maillocation( - mid bigint NOT NULL DEFAULT nextval('maillocation_id'), - maillocation varchar(20) NOT NULL, - CONSTRAINT pkey_maillocation PRIMARY KEY (mid), - CONSTRAINT ukey_maillocation UNIQUE (maillocation) -); --- Insert default Maildir-folder name -INSERT INTO maillocation(maillocation) VALUES ('Maildir'); -CREATE SEQUENCE domains_gid +CREATE SEQUENCE domain_gid START WITH 70000 INCREMENT BY 1 MINVALUE 70000 @@ -34,17 +22,43 @@ MAXVALUE 4294967294 NO CYCLE; -CREATE TABLE domains ( - gid bigint NOT NULL DEFAULT nextval('domains_gid'), + +CREATE TABLE transport ( + tid bigint NOT NULL DEFAULT nextval('transport_id'), + transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] + CONSTRAINT pkey_transport PRIMARY KEY (tid), + CONSTRAINT ukey_transport UNIQUE (transport) +); +-- Insert default transport +INSERT INTO transport(transport) VALUES ('dovecot:'); + +CREATE TABLE maillocation( + mid bigint NOT NULL DEFAULT nextval('maillocation_id'), + maillocation varchar(20) NOT NULL, + CONSTRAINT pkey_maillocation PRIMARY KEY (mid), + CONSTRAINT ukey_maillocation UNIQUE (maillocation) +); +-- Insert default Maildir-folder name +INSERT INTO maillocation(maillocation) VALUES ('Maildir'); + +CREATE TABLE domain_data ( + gid bigint NOT NULL DEFAULT nextval('domain_gid'), tid bigint NOT NULL DEFAULT 1, -- defualt transport - domainname varchar(255) NOT NULL, domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 - CONSTRAINT pkey_domains PRIMARY KEY (gid), - CONSTRAINT ukey_domains UNIQUE (domainname), - CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid) + CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid) ); +CREATE TABLE domain_name ( + domainname varchar(255) NOT NULL, + gid bigint NOT NULL, + is_primary boolean NOT NULL, + CONSTRAINT pkey_domain_name PRIMARY KEY (domainname), + CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid) +); + CREATE TABLE users ( local_part varchar(64) NOT NULL,-- only localpart w/o '@' passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers @@ -57,13 +71,13 @@ pop3 boolean NOT NULL DEFAULT TRUE, imap boolean NOT NULL DEFAULT TRUE, managesieve boolean NOT NULL DEFAULT TRUE, - CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), - CONSTRAINT ukey_users_uid UNIQUE (uid), - CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) - REFERENCES domains (gid), - CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) + CONSTRAINT pkye_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_tid_transport FOREIGN KEY (tid) + CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid) ); @@ -71,66 +85,69 @@ gid bigint NOT NULL, address varchar(256) NOT NULL, destination varchar(320) NOT NULL, - CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), - CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid) - REFERENCES domains (gid) + CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), + CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid) ); CREATE TABLE relocated ( gid bigint NOT NULL, address varchar(64) NOT NULL, destination varchar(320) NOT NULL, - CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), - CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid) - REFERENCES domains (gid) + CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), + CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid) ); CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domains.domainname AS "user", + SELECT local_part || '@' || domain_name.domainname AS "user", passwd AS "password", smtp, pop3, imap, managesieve FROM users - LEFT JOIN domains USING (gid); + LEFT JOIN domain_name USING (gid); CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domains.domainname AS userid, - uid, gid, domains.domaindir || '/' || uid AS home, + SELECT local_part || '@' || domain_name.domainname AS userid, + uid, gid, domain_data.domaindir || '/' || uid AS home, '~/' || maillocation.maillocation AS mail FROM users - LEFT JOIN domains USING (gid) + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) LEFT JOIN maillocation USING (mid); CREATE OR REPLACE VIEW postfix_gid AS SELECT gid, domainname - FROM domains; + FROM domain_name; CREATE OR REPLACE VIEW postfix_uid AS - SELECT local_part || '@' || domains.domainname AS address, uid + SELECT local_part || '@' || domain_name.domainname AS address, uid FROM users - LEFT JOIN domains USING (gid); + LEFT JOIN domain_name USING (gid); CREATE OR REPLACE VIEW postfix_maildir AS - SELECT local_part || '@' || domains.domainname AS address, - domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir + SELECT local_part || '@' || domain_name.domainname AS address, + domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' + AS maildir FROM users - LEFT JOIN domains USING (gid) + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) LEFT JOIN maillocation USING (mid); CREATE OR REPLACE VIEW postfix_relocated AS - SELECT address || '@' || domains.domainname AS address, destination + SELECT address || '@' || domain_name.domainname AS address, destination FROM relocated - LEFT JOIN domains USING (gid); + LEFT JOIN domain_name USING (gid); CREATE OR REPLACE VIEW postfix_alias AS - SELECT address || '@' || domains.domainname AS address, destination, gid + SELECT address || '@' || domain_name.domainname AS address, destination, gid FROM alias - LEFT JOIN domains USING (gid); + LEFT JOIN domain_name USING (gid); CREATE OR REPLACE VIEW postfix_transport AS - SELECT local_part || '@' || domains.domainname AS address, + SELECT local_part || '@' || domain_name.domainname AS address, transport.transport FROM users LEFT JOIN transport USING (tid) - LEFT JOIN domains USING (gid); + LEFT JOIN domain_name USING (gid); CREATE OR REPLACE VIEW vmm_alias_count AS SELECT count(DISTINCT address) AS aliases, gid @@ -138,10 +155,42 @@ GROUP BY gid; CREATE OR REPLACE VIEW vmm_domain_info AS - SELECT gid, domainname, transport, domaindir, count(uid) AS accounts, - aliases - FROM domains + SELECT gid, domainname, transport, domaindir, + count(uid) AS accounts, + aliases, + (SELECT count(gid) + FROM domain_name + WHERE domain_name.gid = domain_data.gid + AND NOT domain_name.is_primary) AS aliasdomains + FROM domain_data + LEFT JOIN domain_name USING (gid) LEFT JOIN transport USING (tid) LEFT JOIN users USING (gid) LEFT JOIN vmm_alias_count USING (gid) + WHERE domain_name.is_primary GROUP BY gid, domainname, transport, domaindir, aliases; + + +CREATE LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ +DECLARE + primary_count bigint; +BEGIN + SELECT INTO primary_count count(gid) + NEW.is_primary::integer + FROM domain_name + WHERE domain_name.gid = NEW.gid + AND is_primary; + + IF (primary_count > 1) THEN + RAISE EXCEPTION 'There can only be one domain marked as primary.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql STABLE; + + +CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name + FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); diff -r fbcb7e314510 -r 9d10877e1c10 update_tables_0.4.x-0.5.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update_tables_0.4.x-0.5.pgsql Thu Aug 14 00:33:31 2008 +0000 @@ -0,0 +1,140 @@ +-- $Id$ + +SET client_encoding = 'UTF8'; +SET client_min_messages = warning; + +ALTER SEQUENCE domains_gid RENAME TO domain_gid; + + +CREATE TABLE domain_data ( + gid bigint NOT NULL DEFAULT nextval('domain_gid'), + tid bigint NOT NULL DEFAULT 1, + domaindir varchar(40) NOT NULL, + CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) + REFERENCES transport (tid) +); + +CREATE TABLE domain_name ( + domainname varchar(255) NOT NULL, + gid bigint NOT NULL, + is_primary boolean NOT NULL, + CONSTRAINT pkey_domain_name PRIMARY KEY (domainname), + CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid) +); + +INSERT INTO domain_data (gid, tid, domaindir) + SELECT gid, tid, domaindir + FROM domains; + +INSERT INTO domain_name (domainname, gid, is_primary) + SELECT domainname, gid, TRUE + FROM domains; + + +ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains; +ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid); + +ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains; +ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid); + +ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains; +ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data + FOREIGN KEY (gid) REFERENCES domain_data (gid); + + +CREATE OR REPLACE 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); + +CREATE OR REPLACE VIEW dovecot_user AS + SELECT local_part || '@' || domain_name.domainname AS userid, + uid, gid, domain_data.domaindir || '/' || uid AS home, + '~/' || maillocation.maillocation AS mail + FROM users + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) + LEFT JOIN maillocation USING (mid); + +CREATE OR REPLACE VIEW postfix_gid AS + SELECT gid, domainname + FROM domain_name; + +CREATE OR REPLACE VIEW postfix_uid AS + SELECT local_part || '@' || domain_name.domainname AS address, uid + FROM users + LEFT JOIN domain_name USING (gid); + +CREATE OR REPLACE VIEW postfix_maildir AS + SELECT local_part || '@' || domain_name.domainname AS address, + domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' + AS maildir + FROM users + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) + LEFT JOIN maillocation USING (mid); + +CREATE OR REPLACE VIEW postfix_relocated AS + SELECT address || '@' || domain_name.domainname AS address, destination + FROM relocated + LEFT JOIN domain_name USING (gid); + +CREATE OR REPLACE VIEW postfix_alias AS + SELECT address || '@' || domain_name.domainname AS address, destination, gid + FROM alias + LEFT JOIN domain_name USING (gid); + +CREATE OR REPLACE VIEW postfix_transport AS + SELECT local_part || '@' || domain_name.domainname AS address, + transport.transport + FROM users + LEFT JOIN transport USING (tid) + LEFT JOIN domain_name USING (gid); + +DROP VIEW vmm_domain_info; +CREATE OR REPLACE VIEW vmm_domain_info AS + SELECT gid, domainname, transport, domaindir, + count(uid) AS accounts, + aliases, + (SELECT count(gid) + FROM domain_name + WHERE domain_name.gid = domain_data.gid + AND NOT domain_name.is_primary) AS aliasdomains + FROM domain_data + LEFT JOIN domain_name USING (gid) + LEFT JOIN transport USING (tid) + LEFT JOIN users USING (gid) + LEFT JOIN vmm_alias_count USING (gid) + WHERE domain_name.is_primary + GROUP BY gid, domainname, transport, domaindir, aliases; + + +DROP TABLE domains; + + +CREATE LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ +DECLARE + primary_count bigint; +BEGIN + SELECT INTO primary_count count(gid) + NEW.is_primary::integer + FROM domain_name + WHERE domain_name.gid = NEW.gid + AND is_primary; + + IF (primary_count > 1) THEN + RAISE EXCEPTION 'There can only be one domain marked as primary.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql STABLE; + +CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name + FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();