surtr: ...

1 files changed, 13 insertions, 0 deletions

diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index 28ccd8c4..0352c27f 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -60,10 +60,23 @@ in {
           BEGIN;
           SELECT _v.register_patch('002-citext', ARRAY['000-base'], null);
 
+          DROP VIEW virtual_mailbox_domain;
+          DROP VIEW virtual_mailbox_mapping;
+          DROP VIEW imap_user;
+          DROP VIWE lmtp_mapping;
+
           CREATE EXTENSION citext;
+
           ALTER TABLE mailbox ALTER mailbox TYPE citext;
           ALTER TABLE mailbox_mapping ALTER local TYPE citext;
           ALTER TABLE mailbox_mapping ALTER domain TYPE citext;
+
+          CREATE VIEW mailbox_quote_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; 
+
+          CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
+          CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping;
+          CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule;
+          CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox;
           COMMIT;
         ''}
       '';