New things for a mail redirection service, with randomized local parts.

[odin-cgi] / sql / setup-mail.sql

diff --git a/sql/setup-mail.sql b/sql/setup-mail.sql
new file mode 100644 (file)
index 0000000..e1b535c
--- /dev/null
+++ b/sql/setup-mail.sql
@@ -0,0 +1,34 @@
+/* -*-sql-*-
+ *
+ * Plain old SQL for setting up the tables for Odin mail redirection.
+ */
+
+/* The various tools assume that the database is appropriate configured with
+ * the SERIALIZABLE isolation level.
+ */
+
+begin;
+
+drop view if exists odin_maildeliver;
+drop table if exists odin_mailredir;
+
+create table odin_mailredir
+       (lpart varchar(64) not null,
+        dom varchar(128) not null,
+        owner varchar(64) not null,
+        recip text not null default '',
+        expire bigint not null,
+        st varchar(16) not null default 'live',
+        comment text not null default '',
+        primary key (lpart, dom));
+create index odin_mailredir_by_owner_st on odin_mailredir (owner, st);
+create index odin_mailredir_by_expire_st on odin_mailredir (expire, st);
+create index odin_mailredir_by_recip on odin_mailredir (recip);
+
+create view odin_maildeliver as
+       select lpart, dom, recip from odin_mailredir
+       where st = 'live' and
+         (expire = -1 or
+          expire > extract(epoch from current_timestamp));
+
+commit;