mdw@git.distorted.org.uk Git - odin-cgi/blob - sql/setup-mail.sql

   1 /* -*-sql-*-
   2  *
   3  * Plain old SQL for setting up the tables for Odin mail redirection.
   4  *
   5  * (c) 2015 Mark Wooding
   6  */
   7
   8 /*----- Licensing notice ----------------------------------------------------
   9  *
  10  * This file is part of the `odin.gg' service, `odin-cgi'.
  11  *
  12  * `odin-cgi' is free software; you can redistribute it and/or modify
  13  * it under the terms of the GNU Affero General Public License as
  14  * published by the Free Software Foundation; either version 3 of the
  15  * License, or (at your option) any later version.
  16  *
  17  * `odin-cgi' is distributed in the hope that it will be useful,
  18  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  19  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  20  * GNU Affero General Public License for more details.
  21  *
  22  * You should have received a copy of the GNU Affero General Public
  23  * License along with `odin-cgi'; if not, see
  24  * <http://www.gnu.org/licenses/>.
  25  */
  26
  27 /* The various tools assume that the database is appropriate configured with
  28  * the SERIALIZABLE isolation level.
  29  */
  30
  31 begin;
  32
  33 drop view if exists odin_maildeliver;
  34 drop table if exists odin_mailredir;
  35
  36 create table odin_mailredir
  37         (lpart varchar(64) not null,
  38          dom varchar(128) not null,
  39          owner varchar(64) not null,
  40          creator varchar(64) not null,
  41          recip text not null default '',
  42          expire bigint not null,
  43          st varchar(16) not null default 'live',
  44          comment text not null default '',
  45          primary key (lpart, dom));
  46 create index odin_mailredir_by_owner_st on odin_mailredir (owner, st);
  47 create index odin_mailredir_by_expire_st on odin_mailredir (expire, st);
  48 create index odin_mailredir_by_recip on odin_mailredir (recip);
  49
  50 create view odin_maildeliver as
  51         select lpart, dom, recip from odin_mailredir
  52         where st = 'live' and
  53           (expire = -1 or
  54            expire > extract(epoch from current_timestamp));
  55
  56 commit;