Mercurial > ~dholland > hg > swallowtail > index.cgi
comparison database/schema/messages.sql @ 40:44c1509055c7
improvements in message handling
author | David A. Holland |
---|---|
date | Sun, 15 Jun 2014 17:54:11 -0400 |
parents | edf081d0b282 |
children | e1017d556437 |
comparison
equal
deleted
inserted
replaced
39:45bb7b516edb | 40:44c1509055c7 |
---|---|
6 | 6 |
7 -- all incoming mail in original form, for reference | 7 -- all incoming mail in original form, for reference |
8 -- (should be pruned periodically) | 8 -- (should be pruned periodically) |
9 CREATE TABLE rawmail ( | 9 CREATE TABLE rawmail ( |
10 id bigint primary key default nextval('next_rawmsgid'), | 10 id bigint primary key default nextval('next_rawmsgid'), |
11 posttime timestamp not null, | |
11 data text not null | 12 data text not null |
12 ) | 13 ) |
13 WITHOUT OIDS; | 14 WITHOUT OIDS; |
14 | 15 |
15 CREATE SEQUENCE next_msgid; | 16 CREATE SEQUENCE next_msgid; |
16 | 17 |
17 -- comments | 18 -- comments |
18 CREATE TABLE messages ( | 19 CREATE TABLE messages ( |
19 id bigint primary key default nextval('next_msgid'), | 20 id bigint primary key default nextval('next_msgid'), |
20 pr bigint not null references prs (id), | 21 pr bigint not null references prs (id), |
22 number_in_pr bigint not null, | |
21 who bigint not null references users (id), | 23 who bigint not null references users (id), |
22 parent_id bigint null references messages (id), | 24 parent_id bigint null references messages (id), |
23 posttime timestamp not null, | 25 posttime timestamp not null, |
24 contenttype text not null, | 26 mimetype text not null, |
25 body text not null, | 27 body text not null, |
26 | 28 |
27 -- we don't keep these directly, they go into an admin log entry | 29 -- we don't keep these directly, they go into an admin log entry |
30 -- XXX: we may need to keep the external message-id | |
28 --from_address text not null, | 31 --from_address text not null, |
29 --mail_subject text not null, | 32 --mail_subject text not null, |
30 --message_id text not null, | 33 --message_id text not null, |
31 rawid bigint null references rawmail (id) | 34 rawid bigint null references rawmail (id) |
32 | 35 |
33 check (parent_id != id) | 36 check (parent_id != id) |
34 ) | 37 ) |
35 WITHOUT OIDS; | 38 WITHOUT OIDS; |
36 | 39 |
40 CREATE SEQUENCE next_attachid; | |
41 | |
37 -- for patches and mime-attachments | 42 -- for patches and mime-attachments |
38 -- if msgid is null, attachment came with original PR | |
39 CREATE TABLE attachments ( | 43 CREATE TABLE attachments ( |
40 pr bigint not null references PRs (id), | 44 id bigint primary key default nextval('next_attachid'), |
41 msgid bigint null references messages (id), | 45 number_in_pr bigint not null, |
46 msgid bigint not null references messages (id), | |
42 mimetype text not null, | 47 mimetype text not null, |
43 body text not null | 48 body text not null |
44 ) | 49 ) |
45 WITHOUT OIDS; | 50 WITHOUT OIDS; |
46 | 51 |
47 -- intended constraint: | |
48 -- SELECT messages.pr, attachments.pr | |
49 -- FROM messages, attachments | |
50 -- WHERE messages.id = attachments.msgid AND messages.pr <> attachments.pr | |
51 -- should always be empty. | |
52 -- (XXX this is gross.) | |
53 |