VMM/common: Replaced function is_dir() by lisdir().
VMM/{config,mailbox}: Adjusted to the above change.
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);