# HG changeset patch # User Pascal Volk # Date 1278899612 0 # Node ID 62211b6a9b8e90153f50459680109ee09ae8854b # Parent 18086c6a2521034499b25f905faeb1ef72aad940 pgsql: Removed old update SQL scripts. diff -r 18086c6a2521 -r 62211b6a9b8e pgsql/update_tables_0.4.x-0.5.pgsql --- a/pgsql/update_tables_0.4.x-0.5.pgsql Sun Jul 04 16:41:34 2010 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,156 +0,0 @@ -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 pkye_users; -ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid); -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 DROP CONSTRAINT pkey_alias; -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); - -DROP VIEW postfix_alias; -DROP VIEW vmm_domain_info; -DROP VIEW vmm_alias_count; - -ALTER TABLE alias ALTER address TYPE varchar(64); -ALTER TABLE alias ADD CONSTRAINT pkey_alias - PRIMARY KEY (gid, address, destination); - -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); - -CREATE OR REPLACE VIEW vmm_domain_info AS - SELECT gid, domainname, transport, domaindir, - count(uid) AS accounts, - (SELECT count(DISTINCT address) - FROM alias - WHERE alias.gid = domain_data.gid) AS aliases, - (SELECT count(gid) - FROM relocated - WHERE relocated.gid = domain_data.gid) AS relocated, - (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) - WHERE domain_name.is_primary - GROUP BY gid, domainname, transport, domaindir; - - -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; - -DROP TRIGGER IF EXISTS primary_count ON domain_name; -CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name - FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); - -CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name - FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); diff -r 18086c6a2521 -r 62211b6a9b8e pgsql/update_tables_0.5.x_for_dovecot-1.2.x.pgsql --- a/pgsql/update_tables_0.5.x_for_dovecot-1.2.x.pgsql Sun Jul 04 16:41:34 2010 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,12 +0,0 @@ --- --- --- with Dovecot v1.2.x the service managesieve was renamed to sieve --- --- -ALTER TABLE users RENAME managesieve TO sieve; - -DROP VIEW dovecot_password; -CREATE OR REPLACE 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); - diff -r 18086c6a2521 -r 62211b6a9b8e pgsql/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql --- a/pgsql/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql Sun Jul 04 16:41:34 2010 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,51 +0,0 @@ --- --- --- Clean out the old stuff --- --- -DROP TYPE dovecotpassword CASCADE; - --- --- --- Data type for function dovecotpassword(varchar, varchar) --- --- -CREATE TYPE dovecotpassword AS ( - userid varchar(320), - password varchar(74), - smtp boolean, - pop3 boolean, - imap boolean, - sieve boolean -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotpassword records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users, domain_name TO dovecot; --- --- For more details see http://wiki.dovecot.org/AuthDatabase/SQL --- --- -CREATE OR REPLACE FUNCTION dovecotpassword( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword -AS $$ - DECLARE - record dovecotpassword; - userid varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT userid, passwd, smtp, pop3, imap, sieve - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; -