pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 297 e21ceaabe871
child 382 5e6bcb2e010e
equal deleted inserted replaced
296:62211b6a9b8e 297:e21ceaabe871
       
     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, sieve
       
    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);