Commit | Line | Data |
---|---|---|
c86aee46 MW |
1 | /* -*-sql-*- |
2 | * | |
3 | * Plain old SQL for setting up the tables for Odin mail redirection. | |
128543b0 MW |
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/>. | |
c86aee46 MW |
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, | |
c68a5549 | 40 | creator varchar(64) not null, |
c86aee46 MW |
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; |