--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql	Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,64 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+-- ---
+-- Make room for sha512-crypt.hex hashed passwords
+-- ---
+DROP VIEW dovecot_password;
+
+ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
+
+CREATE 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);
+
+-- ---
+-- Make room for different mailbox formats.
+-- ---
+DROP VIEW dovecot_user;
+DROP VIEW postfix_maildir;
+
+CREATE SEQUENCE mailboxformat_id;
+
+CREATE TABLE mailboxformat (
+    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+    format      varchar(20) NOT NULL,
+    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
+    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
+);
+-- Insert supported mailbox formats
+INSERT INTO mailboxformat(format) VALUES ('maildir');
+INSERT INTO mailboxformat(format) VALUES ('mdbox');
+INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
+-- Adjust maillocation table
+ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
+ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
+ALTER TABLE maillocation
+    ADD COLUMN fid bigint NOT NULL DEFAULT 1,
+    ADD COLUMN extra varchar(1024);
+ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
+    FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+
+
+CREATE VIEW dovecot_user AS
+    SELECT local_part || '@' || domain_name.domainname AS userid,
+           uid, gid, domain_data.domaindir || '/' || uid AS home,
+           mailboxformat.format || ':~/' || maillocation.directory AS mail
+      FROM users
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN maillocation USING (mid)
+           LEFT JOIN mailboxformat USING (fid);
+
+CREATE VIEW postfix_maildir AS
+    SELECT local_part || '@' || domain_name.domainname AS address,
+           domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
+           AS maildir
+      FROM users
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN maillocation USING (mid);