pgsql/update_tables_0.5.x-0.6.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Mon, 26 Jul 2010 19:10:51 +0000
branchv0.6.x
changeset 316 31d8931dc535
parent 297 e21ceaabe871
child 382 5e6bcb2e010e
permissions -rw-r--r--
VMM/constants: Replaced the constants subpackage by a module.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
SET client_encoding = 'UTF8';
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
SET client_min_messages = warning;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
-- ---
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
-- Make room for sha512-crypt.hex hashed passwords
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     7
-- ---
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
DROP VIEW dovecot_password;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    10
ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    11
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
CREATE VIEW dovecot_password AS
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
    SELECT local_part || '@' || domain_name.domainname AS "user",
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
           passwd AS "password", smtp, pop3, imap, managesieve
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
      FROM users
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
           LEFT JOIN domain_name USING (gid);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
-- ---
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
-- Make room for different mailbox formats.
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
-- ---
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
DROP VIEW dovecot_user;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
DROP VIEW postfix_maildir;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
CREATE SEQUENCE mailboxformat_id;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
CREATE TABLE mailboxformat (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
    format      varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
-- Insert supported mailbox formats
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
INSERT INTO mailboxformat(format) VALUES ('maildir');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
INSERT INTO mailboxformat(format) VALUES ('mdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
INSERT INTO mailboxformat(format) VALUES ('sdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
-- Adjust maillocation table
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
ALTER TABLE maillocation
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
    ADD COLUMN fid bigint NOT NULL DEFAULT 1,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
    ADD COLUMN extra varchar(1024);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
    FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
CREATE VIEW dovecot_user AS
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
    SELECT local_part || '@' || domain_name.domainname AS userid,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
           uid, gid, domain_data.domaindir || '/' || uid AS home,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
           mailboxformat.format || ':~/' || maillocation.directory AS mail
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
      FROM users
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
           LEFT JOIN domain_data USING (gid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
           LEFT JOIN domain_name USING (gid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
           LEFT JOIN maillocation USING (mid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
           LEFT JOIN mailboxformat USING (fid);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
CREATE VIEW postfix_maildir AS
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
    SELECT local_part || '@' || domain_name.domainname AS address,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
           domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
           AS maildir
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
      FROM users
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
           LEFT JOIN domain_data USING (gid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
           LEFT JOIN domain_name USING (gid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
           LEFT JOIN maillocation USING (mid);