1 SET client_encoding = 'UTF8';

     2 SET client_min_messages = warning;

     3 

     4 

     5 -- ---

     6 -- Make room for sha512-crypt.hex hashed passwords

     7 -- ---

     8 DROP VIEW dovecot_password;

     9 

    10 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);

    11 

    12 CREATE VIEW dovecot_password AS

    13     SELECT local_part || '@' || domain_name.domainname AS "user",

    14            passwd AS "password", smtp, pop3, imap, managesieve

    15       FROM users

    16            LEFT JOIN domain_name USING (gid);

    17 

    18 -- ---

    19 -- Make room for different mailbox formats.

    20 -- ---

    21 DROP VIEW dovecot_user;

    22 DROP VIEW postfix_maildir;

    23 

    24 CREATE SEQUENCE mailboxformat_id;

    25 

    26 CREATE TABLE mailboxformat (

    27     fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),

    28     format      varchar(20) NOT NULL,

    29     CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),

    30     CONSTRAINT  ukey_mailboxformat UNIQUE (format)

    31 );

    32 -- Insert supported mailbox formats

    33 INSERT INTO mailboxformat(format) VALUES ('maildir');

    34 INSERT INTO mailboxformat(format) VALUES ('mdbox');

    35 INSERT INTO mailboxformat(format) VALUES ('sdbox');

    36 

    37 -- Adjust maillocation table

    38 ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;

    39 ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;

    40 ALTER TABLE maillocation

    41     ADD COLUMN fid bigint NOT NULL DEFAULT 1,

    42     ADD COLUMN extra varchar(1024);

    43 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat

    44     FOREIGN KEY (fid) REFERENCES mailboxformat (fid);

    45 

    46 

    47 CREATE VIEW dovecot_user AS

    48     SELECT local_part || '@' || domain_name.domainname AS userid,

    49            uid, gid, domain_data.domaindir || '/' || uid AS home,

    50            mailboxformat.format || ':~/' || maillocation.directory AS mail

    51       FROM users

    52            LEFT JOIN domain_data USING (gid)

    53            LEFT JOIN domain_name USING (gid)

    54            LEFT JOIN maillocation USING (mid)

    55            LEFT JOIN mailboxformat USING (fid);

    56 

    57 CREATE VIEW postfix_maildir AS

    58     SELECT local_part || '@' || domain_name.domainname AS address,

    59            domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'

    60            AS maildir

    61       FROM users

    62            LEFT JOIN domain_data USING (gid)

    63            LEFT JOIN domain_name USING (gid)

    64            LEFT JOIN maillocation USING (mid);